【MySQL进阶之路】MySQL为什么顺序插入行更快？覆盖索引、索引扫描？

欢迎关注公众号（通过文章导读关注：【11来了】），及时收到 AI 前沿项目工具及新技术的推送！

在我后台回复 「资料」 可领取编程高频电子书！
在我后台回复「面试」可领取硬核面试笔记！

文章导读地址：点击查看文章导读！

感谢你的关注！

为什么在 InnoDB 表中按逐渐顺序插入行速度更快呢？

向表里插入数据，主键可以选择整数自增 ID 或者 UUID。

• 如果选择自增 ID 作为主键

那么在向表中插入数据时，插入的每一条新数据都在上一条数据的后边，当达到页的最大填充因子（InnoDB 默认的最大填充因子是页大小的 15/16，留出部分空间用于以后修改）时，下一条记录就会被写入到新的页中。

• 如果选择 UUID 作为主键

在插入数据时，由于新插入的数据的主键的不一定比之前的大，所以 InnoDB 需要为新插入的数据找到一个合适的位置——通常是已有数据的中间位置，有以下缺点：

1. 写入的目标也可能已经刷到磁盘上并从内存中删除，或者还没有被加载到内存中，那么 InnoDB 在插入之前，需要先将目标页读取到内存中。这会导致大量随机 IO
2. 写入数据是乱序的，所以 InnoDB 会频繁执行页分裂操作
3. 由于频繁的页分裂，页会变得稀疏并且被不规则地填充，最终数据会有碎片

什么时候使用自增 ID 作为主键反而更糟？

在高并发地工作负载中，并发插入可能导致间隙锁竞争。

了解覆盖索引吗？

覆盖索引：一个索引包含（或说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。

覆盖索引是非常有用的工具，能够极大的提高性能，只需要查询索引而不需要回表，好处有很多：

MySQL 回表指的是在InnoDB存储引擎下，二级索引查询到的索引列，如果需要查找所有列的数据，则需要到主键索引里面去取出数据。这个过程就称为回表

• 索引条目通常远小于数据行的大小，如果只需要读取索引，mysql可以大幅减少数据访问量。 对缓存的负载很重要，可以减少数据拷贝花费的时间。覆盖索引对IO密集型应用也很有帮助，索引比数据更小，放到内存中更节省空间。
• 因为索引是按照顺序存放的（至少在单个页内是如此），所以对于IO密集型的范围查询，会比随机从磁盘读取每一行数据的IO要少得多。
• 由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级索引能够覆盖查询，则可以避免对主键索引的二次查询。

了解索引扫描吗？

MySQL有两种方法生成有序结果：

• 通过排序操作
• 按照索引顺序扫描

如果 explain 出来的 type 列值为 “index” 的话，说明是按照索引扫描了。

索引扫描本身的速度是很快的。但是如果索引不能覆盖查询所需的全部列的话，那在每次查询索引时都需要回表再查询其他字段，这样的话，按索引顺序读取的速度通常比顺序地全表扫描要慢。如下图，select *时没有使用索引，select age时使用了索引。

explain select age from user order by age; # 结果1
explain select * from user order by age; # 结果2

设计： 设计的时候，尽可能让同一个索引既满足排序，又用于查找行，这样是最好的。

只有当索引的列顺序和order by子句的顺序完全一致时，MySQL才能使用索引来对结果进行排序，如果查询需要关联多张表时，只有order by子句引用的字段全部为第一个表时，才能使用索引做排序。

order by查询时，需要满足索引的最左前缀要求，否则MySQL需要执行排序操作，无法利用索引进行排序。

order by有一种情况可以不满足索引的最左前缀的要求：前导列为常量。（即如果age,name为索引列，那么select * from user where age = 30 order by name，使用where将age指定为常量，这时也是可以使用索引排序的）