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 语句，如果两个字段其中一个不是索引字段，就会走全表扫描，这一点很容易理解。

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