SQL碎片知识

本篇笔记目的在于记录常见的 MySQL 相关的问题。

1. 慢查询怎么处理？

查看慢查询日志，通过 explain 分析慢查询的 SQL 语句，查看是否走的全表扫描，或者没有利用索引，随后添加合适的索引，同时查询需要遵循最左匹配原则。

另外也可以进行简化查询，只查询必要的字段，或者使用覆盖索引，或者减少通配符的使用；使用 JOIN 替代嵌套的 SELECT 减少子查询

如果发现这个查询确实走了索引，但是依旧很慢，可能需要分析锁。

2. 为啥 MySQL 选择了 B+ 树而不是平衡二叉树或者 B 树？

普通的二叉搜索树在顺序插入索引的时候会导致树退化成链表，这种情况下就相当于全表扫描。

而平衡二叉树或者红黑树性能差异并不大，但是由于每一个节点仅能存储一个索引，所以存储效率很低，在存储成千上万的索引时，树的高度很大，而每一次查找都是一次磁盘 I/O ，所以我们期望高度会比较低的数据结构。

B 树和 B+ 树的高度都比较低，为什么没有选择 B 树？B 树的数据是分布于整个树中的，除了叶子节点，还分布于内节点上面，不利于我们走范围查询，并且查询的速度波动很大，除此之外，这样的性质使得 B+ 树可以存放更多的索引，查询效率更高，磁盘的 I/O 也会更少。

而我们的 B+ 树，它的数据都存储在叶子节点上，而内节点都用于放索引，同时，我们的叶子节点相邻之间构成了双向链表，这种设计非常利于范围查询。 B+ 树存在大量的冗余节点（内节点）这就是的插入和删除的效率很高，树形结构的变化很小，而 B 树由于没有冗余节点，所以删除就会非常复杂，树形变化较大。

3. 聚簇索引和二级索引？

一张表只有一个聚簇索引，聚簇索引的叶子节点上存储着完整的数据，而二级索引存储的是对应的“主键”字段，当我们通过二级索引去查询数据时，就会先通过二级索引拿到主键信息，然后根据主键回到聚簇索引之中去查询具体的数据，也就是说，要执行两次 B+ 树查询，这种情况就是回表。

当然，如果我们查询的数据已经包含在了索引之中，就无需回表查询，这种情况就是索引覆盖。

4. MyISAM 和 InnoDB 的区别

MyISAM 的索引是非聚集索引，数据和索引分离，每一个数据查询都需要经过回表的步骤，而 InnoDB 有聚集索引，通过主键检索的效率很高。

MyISAM 最细粒度的锁是表锁，而 InnoDB 支持行锁，并发更强。

MyISAM 不支持外键和事务。

5. 为什么会有最左前缀原则？

我们建立联合索引的时候，往往会有一个顺序，比如联合索引（name，age，email），我们的索引只有一个，如何根据这个联合索引去建立这唯一的索引呢？我们是先按照 name 排序，再按照 age，最后按照 email 来排序的顺序，所以如果我们直接使用 SELECT * FROM users WHERE age = 12 的时候，我们直接去寻找年龄为 12 的用户，但是此时我们并没有根据 name 去查找，而是直接根据 age，那么问题来了，如果我们真的去走这个联合索引，我们找到了一个 age = 12 的用户，但是这个是唯一的 age = 12 的用户吗？很有可能不是，我们根本不可能根据 age = 12 这个条件从这个联合索引中去找到所有的 age = 12 的用户，所以此时，只能走全表扫描，这就是最左前缀的由来。

6. B+ 树里面的数据是怎么存储的？怎么查找的？

B+ 树中的每个节点是以页为单位的，不论是叶子节点的数据还是内节点的索引，都是以页为单位的，都是一个数据页， MySQL 中的页的单位是 16 kb ，这也是和很多操作系统不一样的地方，这也是为什么 B+ 树可以存储比 B 树更多的索引，变得更矮胖，查询效率更高，磁盘 I/O 次数更少。

我们的索引在页中也是链表的形式串联的，但是会进行分组，每个组仅有几个元素，每个组都有一个目录，我们的目录会记录每个组的最后一个节点的数据和指针，而页目录可以帮助我们做二分查找，我们可以进行一次磁盘 I/O ，将这一页加载到内存中，然后通过二分查找，从而实现快速的查询，然后找到下一层节点的索引，进行下一步加载。

最后，我们进入到了叶子节点，也就是挂着数据的节点，每个行数据都是和对应的索引绑定在一起，也就是说，结构是一样的，还是通过链表绑在一起，我们可以先将叶子节点加载到磁盘中，然后通过页目录进行二分查找，最终找到对应的行。而在分组内，肯定只能实现遍历查找了。

这里可以引出另一个问题，**为什么是 B+ 树？**我们根据刚刚提到的，我们可以直接使用利用页目录进行二分查找，为什么不把所有的数据组织成一个链表呢？这是因为我们希望加载很方便，一次只需要加载一个页，便于维护，而我们的每个页只需要一个页目录就可以了，然而，一个页的数据量优先，不可能承受无限的页目录，所以我们需要多个页，这个时候又会出现一个问题，我们应该先在哪一个页进行二分？先将哪个页加载到磁盘？所以我们只能向上扩展一层作为上一层索引，一次来判断去加载哪一个页，而此时的结构，就和我们的 B+ 树很像了，随着数据的增长，我们的 B+ 树的高度也会长高，而为了数据检索更快，变高是不可避免的，这不就是我们刚刚说的模型吗？这就是 MySQL 使用 B+ 树的由来。

并且，如果对于二叉树，我们每次加载一页到内存，这个页对应的节点可能并不连续，可能导致多次磁盘IO，效率也很低下。相比之下 B+ 树稳定的最多三次磁盘 I/O ，性能和稳定性都更好。

7. 索引下推优化是什么?

对于我们的联合索引 index(a, b) ，在执行 SELECT * FROM USERS WHERE a > 1 AND b < 2 的时候，如果没有索引下推，则会先找到符合 a > 1 的数据，然后回表回到数据页去判断 'b < 2' 的条件，这就导致了有一些数据没能利用到第二个 b 索引进行过滤，所以在新的 MySQL 版本中，引入了索引下推，使得我们的 sql 语句在第一步索引检索期间，回表之前就可以通过 a 和 b 两个字段直接进行过滤，以此来减少回表操作。当然，前提是索引中包含对应的字段。

8. 什么时候适合/不适合用索引？

索引也有一些缺点：

1. 占用物理空间。

2. 创建和维护索引耗时，尤其是数据量很大的时候。

3. 增删改的效率降低，因为 B+ 树需要进行动态维护。

在这些场景下，并不适合建立索引：

1. 在 WHERE, ORDER BY，GROUP BY 中很少用到的字段，数据重复多的，比如性别这种字段，但是像 uid 这种唯一字段，或者商品编码比较适合建立索引。

2. 表数据少的时候。

3. 经常更新的字段，比如账户余额。（因为会频繁的更改，影响数据库性能）

9. 索引有什么优化的办法？

前缀索引优化

• 前缀索引优化其实就是使用字符串的前几个字符建立索引，使用前缀索引可以避免使用整个很长的字符串进行索引，从而节省我们的索引的空间，使得可以在相同的磁盘空间中建立更多的索引，同时因为字符变短，检索也更快。但是需要你的前缀有一定的区分度。前缀索引可以用来优化模糊匹配和加速 WHERE 查询。

覆盖索引优化

• 代表我们通过 SELECT 选择的字段直接在二级索引的叶子节点上就可以找到而无需回表操作。

主键索引自增：

• 这样在插入数据时可以减少 B+ 树的自适应旋转，由于我们都是插入自增的数据，所以主键索引都是以追加的形式放在 B+ 树中，而减少了结构的变化导致的性能问题。而如果插入的是非自增主键，每次的索引值都是随机的，需要插入现有的数据到索引的中间，我们就不得不去复制移动其他的数据到另一个页，这种情况叫做页分裂，这也有可能造成大量的内存碎片，导致索引结构不紧凑，影响查询效率。

索引设置为 NOT NULL：

• 虽然感觉没人会设置为 NULL，但是这样做有两个原因，第一是因为索引列存在 NULL 值导致优化器做选择的时候会更复杂，更难以优化，比如索引的统计和值都很复杂，因为是 NULL ，没人知道你是在干嘛。二是 NULL 没有意义，并且会占用物理空间。

防止索引失效：

• 当我们使用左或者左右模糊匹配会导致索引失效，因为我们只能根据前缀去匹配字符串。

• 使用函数也会导致索引失效，因为我们索引保存的是原始值，而不是经过函数计算的值。

• 对于索引进行表达式计算也会导致索引失效，这里和使用函数差不多，比如：SELECT * FROM users WHERE id + 1 = 10 此时如果我们将 +1 移动到右侧，索引就可以生效了。

• 值得一提的例子，MySQL 会自动把字符串转换为数字，如果我们的索引项是字符串的时候，我们传入一个数字，则会导致索引失效，因为此时是对索引项进行类型转换，也就是使用了函数，但是如果索引项是数字，我们传入字符串，索引并不会失效，因为此时是对我们传入的字符串进行类型转换，并没有对索引做操作。

• 另外对于 OR 语句，如果两个字段其中一个不是索引字段，就会走全表扫描，这一点很容易理解。

• 需要遵循最左匹配原则。

10. count(1)，count(*)，count(其他字段)，到底谁性能更好？

答案是：count(*) = count(1) > count(主键) > count(其他)

count() 的作用是统计不为 NULL 的字段个数，我们的 count(*) 并不是去读取所有字段，我们的 MySQL 会将 count(*) 转换成 count(0) 来处理， 他和 count(1) 一样，都是通过循环遍历聚簇索引而不会读取任何字段的值，相比于 count(主键) 少了一个读取字段值的步骤，而其他的都需要读取字段值，确认是否为 NULL ，才能对 count + 1，但是如果表中存在二级索引，我们就会去循环遍历二级索引，因为二级索引占的空间更小，I/O 成本也就更小。

11. 事务的性质？

1. 原子性：一个事务中的所有操作，要么全部完成，要么全部失败。（undo log 实现）

2. 一致性：事务在提交前和提交后，需要满足一致性约束。（其他三者实现）

3. 隔离性：事务应该可以防止多个事务并发执行时导致的数据不一致，不会相互干扰。（MVCC 实现）

4. 持久性：事务执行完成之后，数据持久保存。（redo log 实现）

12. 事务会引发哪些问题？

由于 MySQL 可以同时处理多个事务，此时就会有三个问题：

1. 脏读：读取别人还没有提交的事务中更改的数据。

2. 不可重复读：在同一事务中前后读取的数据不一致，也就是说，在第一次读取之后，另一条事务修改这个数据并提交之后，再次读取，发现两次数据不一致，这就是不可重复读。

3. 幻读：同一事务中读取到的结果集的记录数量不一致。

对于这三种问题， MySQL 有四种隔离级别：

1. 读未提交（read uncommitted）：一个事务还未提交的时候，它的变更能被其他事务看到，相当于直接读取最新的数据。此时，三个问题都有。

2. 读已提交（read committed）：一个事务只能看见另一个事务已经提交的数据，相当于创建了一个 read view ，每次事务提交都会创建一个 read view 快照。这里仅仅解决了脏读的问题。

3. 可重复读（repeatable read）：在一个事务中读取的数据总是和之前读取的数据一致，相当于仅在事务启动时候的数据做了 read view快照。可重复读解决了脏读和不可重复读的问题，但是存在幻读，这也是 MySQL 默认的事务隔离级别。

4. 串行化（serializable）：加锁，同一时刻只能有一个事务。解决了所有的问题，缺点是没有并行度，性能差得不行。

其中，可重复读可以一定程度上避免幻读，但是无法完全避免：

• 快照读：通过 MVCC 的方式解决了幻读，事务执行过程中看到的数据总是一致的。

• 当前读：通过 next-key lock 阻止其他执行向这个范围插入数据，一般可以通过 select ... for update 加锁。

13. MVCC 里面的 read view 是什么，怎么工作的？

我们在讲事务的时候提过 read view ，通过 read view 我们可以实现不同的隔离级别。

我们的 read view 有四个字段：

• m_ids：是一个事务的 id 列表，其中包含的事务已经启动，但是还没有提交。

• min_trx_id：创建 read view 的时候，m_ids 中的 id 最小值，最早创建的事务。

• max_trx_id：跟上面相反，表示 m_ids 中的最大值的下一个id，也就是最大值 +1。

• creator_trx_id：创建这个 read view 的事务的 id。

除此之外，我们的 MySQL 中还有两个隐藏列：

• trx_id：当一个事务对聚集索引记录进行改动的时候，就会把这个事务的 id 记录在这个隐藏列里面。

• roll_pointer：每次对索引记录进行改动时，都会把旧的版本记录写到 undo log 里面，这个指针就指向旧的版本记录，所有的旧版本都以链表的形式串联起来，这样利于我们后续选择合适的版本来进行读取。

当一个事务去访问一条记录的时候：

• 如果这个记录的 trx_id 小于 read view 的 min_trx_id ，则说明这个版本的记录是在这个事务创建之前已经提交的事务生成的，可见。

• 如果大于 max_trx_id ，则是这个 read view 创建之后才启动的事务改变的，不可见，所以进而去沿着 undo log 链路去访问这个记录的下一个版本。

• 如果在这之间，则还需要判断这个 trx_id 是否在 m_ids 列表中，如果在，则说明这个事务还没有提交，不可见，访问旧版本。如果不在，则说明已经提交，可见。

这就叫做多版本并发控制MVCC，尽管串行化才能完美解决所有的事务并行引发的问题，但是性能非常差，而可重复读尽管无法完全避免幻读，但是仍可以很大程度避免幻读现象，所以最好还是采取可重复读的隔离级别。

除了与可重复读有关，我们的 undo log 还可以实现事务回滚。

14. buffer pool 是啥，有啥用？

当我们从磁盘中读取数据，更新一条记录之后，并不会将他直接写入磁盘中，而是在 buffer pool 中将他缓存起来，相当于在磁盘前面加了一层 cache ，使得读取速度更快。

MySQL 中的数据是以页为单位存储的，buffer pool 也一样，所有的数据都是按照页来缓存的。当我们写入数据时，如果 buffer pool 中存在这个页，就直接写入 buffer pool ，标记为脏页，后台线程选择合适的时机写入磁盘。

它不仅会缓存数据页和索引页，还会缓存 undo 页等等页。

深入理解，buffer pool 是一段连续的内存空间，运行了一段时间之后，肯定既有空闲的，也有缓存数据的页，此时我们会维护一段空闲内存的 Free 链表，就和 xv6 做的一样。

同时，对于脏页，我们也会维护一个 Flush 链表，来快速的知道哪些页是脏页，这样有利于后台线程快速地将脏页写入到磁盘。

如果想要提高缓存击中率，我们可能会想到 LRU 策略，但是 MySQL 并没有采取原始的 LRU 缓存策略，因为单纯的 LRU 会引起预读失效和 buffer pool 污染：

1. 问题：MySQL 在加载数据页的时候会将相邻的数据页加载进 buffer pool ，这就叫做预读，而对于预读，我们使用 LRU 算法的话，可能会导致频繁读取的数据反而被替换，而很少读取的页很久才被替换，但是我们并不能直接去掉预读的机制，我们可以选择将预读的页留在缓冲区的时间尽可能短。

   解决：MySQL 将 LRU 划分为两个区域，old 区域和 young 区域，young 区域处于链表的前面一部分，而 old 在后面，我们预读的页就会加载到 old 的头部，而真正被访问的页就会插入 young 的头部，以此来实现预读停留时间少，减少预读失效的情况。

2. 问题：buffer pool 污染也是一个问题，当我们 MySQL 扫描了大量的数据，而 buffer pool 有限，导致了大量的热数据被替换出去，此时再次访问这些热数据，就会产生大量的磁盘 I/O，比如全表扫描，很多缓冲页只会访问一次，但是却直接进入了 young 区域，而替换了热点数据，所以我们可以提高进入 young 区域的门槛。

   解决：为进入到 young 区域的页提高门槛，将第一次访问 old 中的某一个节点的时候，和后续访问节点的时间做对比，如果超出一个阈值，就会被放入 young 区域，这是因为，这样就表示了这页是持久被访问的，而不是某一时刻才会访问，这样就过滤了一些不会被持久访问的数据，也就是说，满足被访问和停留的时间超过一秒才会被放入 young 区域！

我们修改数据的时候，是直接在 buffer pool 中进行修改，然后设置为脏页，并不会直接刷入磁盘，同时我们会引入 redo log 来防止 MySQL 宕机的时候导致数据丢失，

15. redo log 是什么？

为了防止还没有落盘的 buffer pool 中的脏页数据在断电等故障的时候丢失，在我们更新了 buffer pool 之后，我们就会将做的修改操作记录到 redo log 文件里面。这里虽然也是写磁盘，但是这里并不是随机写，而是顺序写，直接将数据追加到磁盘中，所以速度很快，但是其实 redo log 也有一层 redo log buffer 定期将 redo log 顺序写入磁盘中，目的是减少磁盘 I/O ，尽管顺序写很快，但是我们依旧需要减少不必要的磁盘 I/O 来提升性能。

我们的 redo log 采取的是循环写模式，默认由两个 redo log file 组成，我们会先写满 file1，写满后再写 file2 ，然后又写 file1 这样的顺序。相当于一个环形。我们会用 write pos 表示当前写的位置，checkpoint 表示当前需要擦除的位置，如果 write pos == checkpoint 则无法写入 log ，会陷入阻塞。

16. binlog 是什么？

上面的 redo log 用于故障恢复，而 binlog 则是用于备份和主从复制的。

同时 redo log 是循环写，数据库的日志并不会全量保存，而 binlog 则是将日志全量保存，即便我们将数据库的数据都删除了，也可以通过 binlog 恢复。

binlog 有三种文件格式 STATEMENT，ROW，MIXED ，statement 是默认文件格式，会记录 SQL 语句，而 ROW 会记录行数据的修改记录，我们的 ROW 在频繁的更新中会记录多个修改记录，而 statement 只会记录一个，所以第一种的占用更小，而第三种则是混合模式，根据不同的情况来做选择。

那么是如何实现主从复制的？ 首先是主库写 binlog 日志，提交事务，更新本地存储数据，然后我们主库的 binlog 会复制到从库，将 binlog 写入到暂存日志，最后回放 binlog ，更新存储数据。对于主库来说，我们在主从复制的时候会创建 log dump 线程来处理复制的请求，同时也有网络带宽的限制，所以从库并不是越多越好。

这里的主从复制其实有三种模式，和 ack 机制很像：

1. 同步复制：从节点全部复制完才会返回客户端结果，缺点是可用性差。

2. 异步复制：无需等待，如果一旦出故障，数据就会丢失。

3. 半同步模式：同步到任意一个从库上就返回结果，兼顾了可用性和可靠性。

而我们的 binlog 也不是直接写入文件，而存在着一个内存缓冲区 binlog cache ，事务提交的时候，就会把对应的 binlog cache 写入到 binlog 文件，并清空 cache。

我们的 binlog 写入磁盘的流程如下（借用小林coding的图）：

binlog cach

我们的线程会将自己的 binlog cache 通过 write 系统调用写入到内核，此时是缓存在内核的 page cache 中，然后才会通过 fsync 持久化到磁盘，这里就是真正的磁盘 I/O 。

通过修改 sync_binlog 参数，我们可以决定磁盘 I/O 的时机：

1. 参数为 0 ：提交事务只 write ，而不调用 fsync，后续由操作系统决定何时将数据持久化。性能最好，但是风险最大。

2. 参数为 1 ：每次提交事务都会 write ，然后立刻调用 fsync。性能最差，最安全。

3. 参数为 n ：每次提交都 write ，事务累计 n 次就会 fsync。自定义。

17. 两阶段提交是什么？

在我们仅仅使用 redo log 和 binlog 的时候，有可能回导致主从数据不一致，比如 binlog 已经持久化，但是 redo log 还没有持久化的时候宕机了，主节点的数据就延后了这种情况。

而解决这个问题的方法就是二阶段提交，通过使用 XA 事务来维护 redo log 和 binlog ，事实上，就是利用了 XA 事务的 ID 来进行判断，保证数据一致性：

Prepare 阶段：我们会将 XID 写入到 redo log 中，然后持久化到磁盘（需要设置 redo log 为提交事务就写入磁盘）

Commit 阶段：将 XID 写入到 binlog 中，然后持久化到磁盘，接着调用提交事务接口，将 redo log 设置为 commit ，但是只要当我们的 XID 写入 binlog 然后被持久化其实就已经算成功了，之后尽管是故障重启，也会认为事务执行成功。

当故障重启的时候，我们会读取 redo log 来恢复数据，此时如果读取到 prepare 状态的数据，我们会拿着这个数据对应的 XID 回到 binlog 去查看是否有相同的 XID ，如果有，就会恢复这部分数据，如果没有，就不会恢复，也就是说，在 binlog 持久化之前，如果故障了，此时的事务就会回滚，意味着事务失败，只要 binlog 成功了，那么就会恢复，这样就保证了一致性。这就是两阶段提交能够解决这个问题的原因。

但是，我们为了实现这个两阶段提交，必须要保证早点刷盘，将数据写入到磁盘里，如果频繁的进行磁盘 I/O ，性能肯定会非常差，同时，我们为了保证事务提交顺序，还需要加锁，这会导致激烈的锁竞争。

MySQL 为了应对这个问题，引入了组提交机制，当有多个事务提交的时候，将多个 binlog 刷盘合并成一个，从而减少磁盘 I/O 的次数，此时的 commit 会分为三个阶段，每个阶段有一个队列，有一个锁，这样可以减少了锁的粒度，提高性能并且保证了顺序性：

• flush ：将 binlog 写入文件（不是刷盘）

• sync ：刷盘操作

• commit ：事务更新 commit 状态。

对于 redo log 其实也有组提交，它的组提交会延后到 flush 阶段进行刷盘，也就是 prepare 阶段和 flush 阶段合并了。