MySQL知识点汇总

单机QPS

单机 QPS 为 4k 左右。

MySQL select语句执行

• prepare 阶段，检查查询语句中的表或字段是否存在，将 * 拓展为表上的所有列。
• optimize 阶段，优化器决定使用哪个索引。
• execute 阶段，执行器，索引下推。

表空间文件结构

组成：段（默认256MB） -> 区（默认1MB） -> 页（默认16KB） -> 行

页：InnoDB的数据按页为单位读写，默认每个页大小为16KB，意味着一次最少从磁盘读取16KB的内容到内存（Buffer Poll）中，一次最少把内存中的16KB内容刷新到磁盘中。表中的记录存储在数据页中。B+树的每个节点都是一个数据页。

区：数据量大的时候，索引分配空间则是按区为单位分配，每个区的大小为1MB，对于16KB的页来说，连续（物理连续）64个页会被划分到一个区，这样，B+树中节点所构成的链表中相邻页的物理位置也相邻，便能顺序IO。

段：段是数据库中的分配单位，不同类型的数据库对象以不同的段形式存在。当创建数据表、索引的时候，就会相应创建对应的段，比如创建一张表时会创建一个表段，创建一个索引时会创建一个索引段。在段中不要求区与区之间是相邻的。

• 索引段：存放B+树非叶子结点的区的集合
• 数据段：存放B+树叶子结点的区的集合
• 回滚段：存放的是回滚数据的区的集合，MVCC利用回滚段实现了多版本查询数据

区存在的意义

数据页都是以双向链表的形式存在，如果以页分配存储空间，双向链表相邻的两个页之间的物理位置可能距离非常远，会产生随机 IO，影响性能

段存在的意义

对于范围查询，本质是对B+树叶子节点中的记录进行顺序扫描，但如果不区分叶子节点和非叶子节点，把节点代表的页都申请到区中，范围查找效率大打折扣。一个索引会产生两个段，一个叶子节点段（数据段）和一个非叶子节点段（索引段）。

行格式

• Redundant
• Compact，一条完整的记录分为“记录的额外信息”和“记录的真实数据”，额外信息中又分为变长字段长度列表、NULL值列表和记录头数据。变长字段长度列表和NULL值列表都是倒序保存，并且不是必须的，只要没有变长字段或NULL值字段即可。8个字段值可以为NULL，那么NULL值列表空间为1字节，9字段则2字节。
• Dynamic
• Compressed

Varchar(n) n最大取多少？

MySQL 规定除了 TEXT、BLOBs 这种大对象类型之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过 65535 个字节。注意，是一行数据的最大字节数 65535，其实是包含「变长字段长度列表」和 「NULL 值列表」所占用的字节数的。

行格式溢出后，数据存放到溢出页。

text 各个类型对比

• TINYTEXT 最大长度是 255 (2^8 – 1) 个字符。
• TEXT 最大长度是 65535 (2^16 – 1) 个字符。
• MEDIUMTEXT 最大长度是 16777215 (2^24 – 1) 个字符。
• LONGTEXT 最大长度是 4294967295 (2^32 – 1) 个字符
• Varchar 对每个英文(ASCII)字符都占用2个字节，对一个汉字也只占用两个字节
• Char 对英文(ASCII)字符占用1个字节，对一个汉字占用2个字节

数据的检索效率是：char>varchar>text

char: 定长，会用空格补齐 varchar：变长

B树和B+树

• B树（多路的平衡搜索树），节点可以存储多个数据，并且每个节点存放索引和数据，一个节点的关键字等于该节点子节点个数减一。查询的最好情况是O（1），一般高度为3。
• B+树，非叶子节点关键字个数等于子节点个数
  1. 叶子节点保存数据（数据页），非叶子节点保存索引，查询固定为O（logn），并且由于这个特点更适合外部存储。
  2. 叶子节点有指向下一个叶子节点的指针，双向链表，可顺序访问，因此区间访问的性能较好。
  3. 存在重复元素，因为非叶子结点不保存数据。

与红黑树的比较，B+树的优势：

1. 更少的查询次数，因为B+树的树高一般小
2. 利用计算机的预读特性，因为B+树具有类似链表的特点，因此相邻的节点也能被预先载入

为什么不使用 B 树：B+ 树的非叶子节点只存储索引，单个节点可以存储更多的索引，计算机一次性加载更多的索引数据到内存中。并且B树不适合范围查找。

为什么不使用跳表：MySQL 一次数据页加载都需要一次磁盘IO。并且磁盘IO的次数和树高有关系，又因为跳表的高度一般比B+树高，所以查询速度会大大降低，因此不适合。

为什么 Redis 使用了跳表：因为 Redis 不存在磁盘IO。重点在于磁盘IO次数。

MySQL索引

给表添加索引时，是会对表加锁，因此在生产环境中不能直接添加索引。InnoDB 存储引擎默认会创建一个主键索引，也就是聚簇索引，其它索引都属于二级索引。当没有显式定义主键索引时，MySQL 会选择第一个唯一索引，并自动设置非空约束，当作聚簇索引。

数据结构维度：

1. B+树索引，适合范围查询，复杂度为O（logn）。
2. 哈希索引，能以O（1）时间进行查找，但失去有序性，无法用于排序和分组，只支持精确查找（等值查找）。
3. 全文索引，使用倒排索引实现，记录关键词到所在文档的映射，一般在text, varchar上创建。
4. R-Trees索引，MyISAM支持的索引类型，和空间地理数据有关。

物理存储维度（InnoDB）：

1. 聚簇索引，叶节点存放一整行记录。一个表只能拥有一个聚簇索引。并非完全等价于主键索引。
2. 非聚簇索引（二级索引），叶节点只存放主键信息，所以一般需要回表查询。

逻辑维度

1. 主键索引，不允许空值
2. 普通索引，没任何限制
3. 联合索引，多个字段创建的索引，使用时遵循左前缀原则。
4. 唯一索引，索引列中的值必须是唯一的，但可以为空值。
5. 空间索引

索引失效（触发全盘扫描）主要看是否回表或者是查询的数据量过大

一定失效：

1. 索引列参与运算
2. 索引列使用函数
3. 两列做比较
4. 类型隐式转换

可能失效：

1. 不满足最左匹配（覆盖索引，索引下推，索引跳跃扫描）
2. 尽可能明确查询列，而不是select *，即使不满足最左匹配（可以用于性能优化）
3. 错误的like使用(当模糊匹配的占位符位于条件的首部，并且要看数据库中的字段)
4. 错误的or使用（切记两个条件都要添加索引，否则会导致索引失效，or两边同时使用 < 和 >，也会失效）
5. 错误的 <> 和 != 使用（查询结果集占比较大时索引会失效）
6. is not null(is null 走索引)
7. not in(条件列是主键时走索引)
8. not exists
9. orderby（部分会失效）
10. 范围过大的 between and

索引跳跃扫描

最左缀原则可以通过跳跃扫描的方式打破，当第一列索引的唯一值较少时，即使where条件没有第一列索引，查询的时候也可以用到联合索引。

覆盖索引

指查询列被所建的索引覆盖，覆盖索引是 select 的数据列只需要从索引中就能取到，不必回表。例如对于联合索引(col1,col2,col3)，查询语句SELECT col1,col2,col3 FROM test WHERE col2=2

哪些情况下不适合建索引

• 数据量少
• 更新频繁
• 区分度低
• where, groupby, orderby 后没有使用到的字段
• 联合索引中的索引

索引下推

指将部分上层（服务层）负责index filter的事情，交给了下层（引擎层）去处理。它能减少二级索引再查询时的回表查询次数，提高查询效率。只适用于二级索引。

EXPLAIN 命令的extra一栏中有Using index condition，表明使用了索引下推

举例：select * from table1 where b like '3%' and c = 3

5.6 之前：

• 先通过 联合索引 查询到 开头为 3 的数据 然后拿到主键
• 然后通过主键去主键索引里面去回表查询 二级索引里面查询出来几个 3 开头的就回表几次

5.6 之后：

• 先通过 二级索引 查询到开头为 3 的数据 然后 再找到 c = 3 的数据进行过滤 之后拿到主键
• 通过主键进行回表查询，减少了回表次数

建索引的原则

1. 选择唯一性索引
2. 为经常需要orderby，groupby操作的字段建立索引
3. 经常作为查询条件的字段建立索引
4. 限制索引数量
5. 尽量使用数据量少的索引
6. 尽量使用最左前缀匹配原则
7. 使用区分度高的列作为索引
8. 扩展索引而不是新建索引

强制索引

深度分页

有分页需求时，一般会用limit实现，但是当偏移量特别大的时候，查询效率就变得低下。

select * from table limit 10 和 select * from table limit 10000, 10 的查询时间是不一样的，后者是查询出 10010 条，再取最后 10 条记录。

本质原因就是：偏移量（offset）越大，mysql就会扫描越多的行，然后再抛弃掉。这样就导致查询性能的下降。

解决方案：

1. 标签记录法，使用 select * from table where id > 10000 limit 10，
2. 范围查询，使用 select * from table where id between 100000 and 100010 order by id desc;
3. 使用子查询，把条件转移到主键索引树，使用 select * from table where id >= (select a.id from table a where a.update_time >= xxx limit 100000, 1) limit 10
4. INNER JOIN 延迟关联，与上述的子查询思路类似，将条件转移到主键索引树
5. 尽量满足索引覆盖，效率低下是因为回表次数过多，如果可以满足索引覆盖，则就不需要回表

ACID 原则

• Atomicity（原子性），每次事务是原子的，事务包含的所有操作要么全部成功，要么全部不执行。一旦有操作失败，则需要回退状态到执行事务之前；通过 undo log 来保证。
• Consistency（一致性），数据库的状态在事务执行前后的状态是一致的和完整的，无中间状态。即只能处于成功事务提交后的状态；例如转账前后，两用户的余额总和一定是不变的。通过原子性 + 隔离性 + 持久性保证的。
• Isolation（隔离性），各种事务可以并发执行，但彼此之间互相不影响。按照标准 SQL 规范，从弱到强可以分为读未提交、读已提交、可重复读和串行化四种隔离等级；通过 MVCC 或锁机制保证的。
• Durability（持久性），状态的改变是持久的，不会失效。一旦某个事务提交，则它造成的状态变更就是永久性的，即便系统故障也不会丢失。通过 redo log 来保证。

事务的隔离

MVCC 用于解决脏读和不可重复读的问题，在读已提交和可重复读两种隔离级别生效。读已提交是每次 select 都会重新生成一次 Read View，而可重复读是一次事务只会创建一次且在第一次查询时创建 Read View。

MVCC: Read view + undo log + 两个隐藏字段，基于乐观锁的理论为了解决读写冲突，可以在读已提交和可重复读的隔离级别下使用。

• Read View 是一个事务快照，保存当前事务开启时所有活跃的事务列表。通过与版本链的配合可以实现对数据的 “快照读”。其中也有四个字段，分别是creater_trx_id, m_ids, min_trx_id, max_trx_id。
• undo log 是存放更新前的数据（快照），保存了历史快照
• 隐藏字段：row_trx_id 和 roll_pointer，前者表示更新行数据的事务id，后者是上一次修改之前保存在 undo log 中的记录位置（指针），使每行记录变化前后形成了一条版本链

MVCC(Multiversion Concurrency Control) + 间隙锁在RR的隔离级别下能解决大部分幻读情况

• 仅快照读的情况下（普通 select 语句），通过 MVCC 的方式，在执行第一个查询语句后，会创建一个 Read View，后续的查询语句利用这个 Read View，通过这个 Read View 就可以在 undo log 版本链找到事务开始时的数据，多次查询获取的是同一个快照数据。
• 仅当前读的情况下（select … for update 等语句），通过 Next-Lock Key（临键锁），其他事务的插入操作会被阻塞。
  • 查询条件不走索引，退化成表锁。
  • 查询条件走普通索引，锁住查询条件附近的间隙，等于间隙锁
  • 查询条件走唯一索引，只锁一条数据，等价于记录锁
• 解决不了的幻读情况：快照读和当前读发生在同一个事务中（更新、删除等操作本质也需要进行当前读）
  1. 事务A快照读，事务B新增数据并提交，接着事务A修改事务B新增的数据并快照读，此时发生幻读。
  2. 事务A快照读，事务B新增数据并提交，接着事务A当前读，此时发生幻读。

若要尽可能避免幻读现象的发生，尽量在开启时候后马上执行当前读。

三种问题：

1. 脏读，指读取未提交数据，事务的数据可能回滚，导致数据不一致。
2. 不可重复读，指在同一事务内读到的数据是不一致的
3. 幻读，指同一事务在查询的过程中，有另外一个事务对范围内新增了记录，导致范围查询的结果条数不一致的现象。

SQL的四种隔离级别：

1. 读未提交，指一个事务还没提交时，它做的变更就能被其他事务看到。可能出现脏读
2. 读已提交，指一个事务提交之后，它做的变更才能被其他事务看到。可能出现不可重复读
3. 可重复读，指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的。可能出现幻读，但使用MVCC + 间隙锁能解决大部分幻读现象，事务开始读操作的时候，不允许其他事务对读的数据做修改
4. 串行化，对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行；

WAL 机制

MySQL 保证数据不丢失： 两阶段提交 + WAL(Write-ahead logging)

WAL 核心：指 MySQL 在执行写操作时先记录在日志中，后更新到磁盘中。将随机写（磁盘的写操作是随机IO，耗性能）转变成顺序写和组提交机制，降低客户端延迟，提高吞吐量。

三大日志binlog, redolog, undolog:

1. Buffer Pool是MySQL进程管理的一块内存空间，有减少磁盘IO次数的作用。
2. redo log重做日志是InnoDB存储引擎的一种物理格式的日志，用来实现事务持久性，主要有两部分文件组成，在内存中的重做日志缓冲（redo log buffer）以及磁盘中的重做日志文件（redo log），（循环写，数据会被覆盖）。使用场景：崩溃恢复，在发生故障的时间点，尚有脏页未写入磁盘，在重启 mysql 服务的时候，根据 redo log 进行重做，从而达到事务的持久性这一特性。
3. undo log回滚日志是InnoDB存储引擎的一种逻辑格式的日志，记录的是数据的逻辑变化，保证的是数据库的原子性，比如一条insert语句对应的是一条delete的undo log，在发生事务错误时，就能回滚到事务之前的数据状态；MVCC，事务未提交前，undo log 保存了未提交的版本数据，作为旧版本的快照数据，类似于做备份。
4. binlog是MySQL Server层的一种逻辑格式的日志，用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。使用场景：主从复制 ：采用异步复制方式，在 Master 端开启 binlog ，然后将 binlog 发送到各个 Slave 端， Slave 端重放 binlog 从而达到主从数据一致；数据恢复，通过使用 mysqlbinlog 工具再结合 binlog 文件，可以将数据恢复到过去的某一时刻。

主从复制

如果对数据一致性和可靠性要求较高，可以考虑使用半同步复制；如果对延迟和主服务器性能要求较高，可以继续使用异步复制，根据实际需求调整复制模式。

异步复制

1. master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中；
2. slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/OThread请求master二进制事件
3. 主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，
4. 从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒。

半同步复制

主服务器将数据修改操作记录到二进制日志，并等待至少一个slave服务器确认已接收到并应用了这些日志后才继续执行后续操作。

Binlog

Binlog 有三种格式：

1. Row，记录操作语句对具体行的操作和操作前的整行信息。缺点是占用空间大，优点是保证数据安全
2. Statement，记录修改的 sql 语句。缺点是在mysql集群时的一些操作会导致数据不一致（例如 Now() 的时间不同）。
3. Mixed

写入到磁盘的操作：

1. write：从 Buffer pool 写到 page cache。
2. fsync：将数据持久化到磁盘。

Binlog 的持久化：

1. sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；
2. sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；
3. sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

Redolog

写入流程：

1. 写入 redo log buffer
2. 写入（write）到 page cache
3. 持久化（fsync）到磁盘中

刷盘时机：

1. MySQL 正常关闭
2. redo log buffer 的写入量大于 redo log buffer 内存空间（默认8MB）的一半时，write 到 page cache 中。
3. 后台线程每隔一秒调用 write 将 redo log buffer 写到 page cache，然后 fsync 持久化到磁盘。
4. innodb_flush_log_at_trx_commit 参数
5. 当innodb_flush_log_at_trx_commit=0时，InnoDB会每秒钟将log buffer的数据write到文件系统缓存中，并调用fsync操作将数据缓存更新至磁盘中，与事务的执行与否无关。因此，在实例崩溃恢复场景中，可能会出现丢失1秒钟的事务。
6. 当innodb_flush_log_at_trx_commit=1时，InnoDB将在每次事务提交时将log buffer的数据write到文件系统缓存中，并调用fsync操作将数据缓存更新至磁盘中。此种方式下，数据库完全遵守ACID特性，安全性较高。
7. 当innodb_flush_log_at_trx_commit=2时，InnoDB将在每次事务提交时将log buffer中的数据write到文件系统缓存中，每秒钟将文件系统缓存中的数据fsync到磁盘中。不能完全保证每秒更新磁盘一次，没有被更新到磁盘中的事务可能会因宕机而丢失。

Buffer Pool

在 MySQL 启动时，向操作系统申请一片连续的空间，大小为 128MB。Buffer Pool中维护的数据结构是缓存页（16 KB 的数据页），而且每个缓存页都有它对应的描述信息。还有存在三个双向链表，分别是FreeList、LRUList以及FlushList。这三个双向链表中维护着缓存页的描述信息。

读写数据流程：当读取数据时，先读 Buffer Pool 中的数据，再去磁盘中读；写数据时，修改 Buffer Pool 中的页，设置为脏页，最后由后台线程写入到磁盘；

双向链表

1. FreeList，存放空闲的缓存页的描述信息
2. LRUList，用于提高缓存命中率
3. FlushList，存放脏页的描述信息

为了解决预读失效（被提前加载进来的数据页并没有被访问）的问题，链表做了冷热数据分离优化，5/8的区域是热数据区域，3/8的区域算是冷数据区域

为了解决 Buffer Pool 污染（扫描大量数据把热数据淘汰掉）的问题，进入热数据区域设置一个时间判断，如果数据被访问并且在冷数据区域的停留时间超过 1 秒，才会被放入热数据区域，否则仍然被放入冷数据区域。

脏页刷新

1. 当 redo log 日志满了的情况下，会主动触发脏页刷新到磁盘；
2. Buffer Pool 空间不足时，需要将一部分数据页淘汰掉，如果淘汰的是脏页，需要先将脏页同步到磁盘；
3. MySQL 认为空闲时，后台线程会定期将适量的脏页刷入到磁盘；
4. MySQL 正常关闭之前，会把所有的脏页刷入到磁盘；

Crash-Safe 能力，两阶段提交

前提：innodb_flush_log_at_trx_commit 设置为1，sync_binlog 设置大于0

1. 先将数据修改写入redo log，并将其标记为 prepare 状态
2. 将相应的 sql 语句写入 binlog
3. commit阶段：将 redo log 标记为 commit 状态，然后根据 sync_binlog 参数的设置，决定是否将 binlog 刷盘

如果发生崩溃，先检查 redo log 记录的事务操作是否为 commit 状态。

1. 如果是 commit 状态说明没有数据丢失，判断下一个。
2. 如果是 prepare 状态，检查 binlog 记录的对应事务操作（redo log 与 binlog 记录的事务操作有一个共同字段 XID，redo log 就是通过这个字段找到 binlog 中对应的事务的）是否完整（这点在前面 binlog 三种格式分析过，每种格式记录的事务结尾都有特定的标识），如果完整就将 redo log 设为 commit 状态，然后结束；不完整就回滚 redo log 的事务，结束。

为什么需要两阶段提交？

• 以基本的事务为单位，redo log 在事务执行过程中可以不断写入，而 binlog 只有在提交事务时才写入，所以redo log 与 binlog 的写入时机不一样。为了解决两份日志之间的逻辑一致问题，将redo log的写入拆成了两个步骤 prepare 和 commit，即两阶段提交。如果只有 redo log 或者只有 binlog，那么事务就不需要两阶段提交。

执行顺序

1. from
2. on
3. join
4. where
5. group by
6. having
7. select
8. distinct
9. order by
10. limit

每一步执行时都会产生一个虚拟表，都会被用作下一个步骤的输入。

锁

乐观锁、悲观锁

• 乐观锁：不能解决脏读问题
• 悲观锁：select … for update

全局锁、表级锁、行锁

• 全局锁：对整个数据库实例加锁，处于只读状态，其命令是Flush tables with read lock（FTWRL），用于全库逻辑备份。
• 表级锁：开销小，加锁快，不会出现死锁，发生锁冲突的概率最高，并发度最低。适合以查询为主。
  • 表锁，可以添加表级别的共享锁或排他锁，锁的粒度较粗。
  • 元数据锁（MDL），CRUD时，数据库自动给表加上MDL读锁；变更表结构时，自动加MDL写锁。事务提交后才会释放MDL锁。
  • 意向锁，在执行插入、更新、删除需要加独占锁之前，需要对表先加上意向独占锁，其目的是快速判断表里是否有记录被加锁。
  • AUTO-INC锁，自增锁，插入数据时获取该锁，插入语句完成后，释放锁。
• 行锁：开销大，加锁慢，会出现死锁，发生锁冲突的概率最低，并发读也高。只有InnoDB引擎支持。行锁依赖于索引，如果加锁操作没有使用到索引，那么会退化成表锁。
  • 记录锁：锁定一条记录，存在 S 型记录锁和 X 型记录锁。加了 S 型记录锁后可以继续加 S 型记录锁，但不能加 X 型记录锁；加了 X 型记录锁后不能加 S 型或 X 型记录锁。存在于包含主键索引的唯一索引中。
  • 间隙锁：锁定一个范围，前开后开，不包含记录本身，存在于可重复读和串行化这两种隔离级别，为了解决可重复读隔离级别下幻读的现象。插入意向锁是一种表明插入意向的间隙锁。
  • 临键锁（next-key lock）：加锁的基本单位，锁定一个范围，前开后闭，存在于非唯一索引中。

共享锁（读锁）、排他锁（写锁）

• 共享锁：读锁，其他事务可以读，但不能写，select lock in share mode
• 排他锁：写锁，其他事务不能读，也不能写，select for update

mysql 数据实时同步到Es

常见的数据同步方案主要有以下三种：

1. 同步调用。在实现增删改的同时，通过调用ES所在服务提供的接口。
2. 异步调用。增删改服务和搜索服务分别通过MQ进行发送和监听消息，有效降低业务的耦合度，但较为依赖MQ的性能；
3. binlog监听。给MySQL开启binlog功能，搜索服务基于canal监听binlog变化，但开启binlog会增加数据库负担、同时实现复杂度较高。

但也有异步调用和binlog监听结合在一起的例子，用canal作slave节点。

分库/分表

分为 水平切分（又称为 Sharding） 和 垂直切分。

切分策略

• 范围切分，例如每一千万条数据一张表
• 哈希切分，对分表键进行哈希运算，主流方法
• 映射表，将分表键和数据库表对映射关系记录在表上

分布式ID

建议使用雪花算法，其中序列号一直保持递增，不会因为时间戳的不同而归零，防止被猜测上下文ID。

注入攻击

通过客户端向应用程序输入数据来插入或注入一个 SQL 查询/操作。

防止注入攻击

1. 参数化查询
2. 使用正则化验证输入
3. 最小权限原则
4. 字符串过滤关键字

MyBatis # 和 $ 的区别

• # 将传入的数据都当成一个字符串，会对自动传入的数据加一个双引号。很大程度防止sql注入
• $ 将传入的数据直接显示生成在sql中。无法防止Sql注入。