Question

索引失效的几种情况

参考: mysql 索引失效的常见原因和如何用好索引 - 问题大白 - 博客园 (cnblogs.com)

• 不满足最佳左前缀原则
• where 条件后使用计算、函数、类型转换
• 范围条件右边索引失效（指的是 联合索引 中的范围 列 ）
• 使用了 select * （会引起回表）
• 字段不允许为空，则 is null 和 is not null 都失效；若字段允许为空，则 is null 走 ref 类型的索引，而 is not null 走 range 类型的索引。
• like 查询左边有 %
• OR 前后存在非索引的列
• 使用 not in
• 使用 != 、 < 、 >

回表问题和索引覆盖

参考: 避免写出致命 SQL，搞懂 MySQL 回表 - 掘金 (juejin.cn)

在 InnoDB 中，索引分为聚簇索引和普通索引。

• 聚簇索引的叶子节点存储的是完整的行记录
• 普通索引的叶子节点存储的是主键

可见， InnoDB 的聚簇索引负责存储完整的行数据，而普通索引只能帮助找到主键。因此， InnoDB 必须也只能有一个聚簇索引。

1. 如果表定义的主键，那么主键就是聚簇索引
2. 如果表没有定义主键，那么第一个 not null 的 unique 列就是聚簇索引
3. 否则， InnoDB 会创建一个隐藏的 DB_ROW_ID 作为聚簇索引

回表

利用普通索引查询到某条数据的主键后，又返回到聚簇索引，重新定位该数据。

回表查询的性能比扫一遍索引树低。

索引覆盖

如果一个索引包含（覆盖）了所需要查询的字段的值，那么就称为 索引覆盖

在 InnoDB 中，普通索引树的叶子节点存储的都是主键+索引列值。

为了避免回表，可以对需要查询的数据建立联合索引。

连接查询优化

参考: MySQL 查询优化——连接以及连接原理 - 简书 (jianshu.com)

文章中有一处有误，MySQL 从 8.0.18 版本开始添加 hash join，并从 8.0.20 开始移除 BNLJ

连接查询原理

在 MySQL 中， A left join B on condition 的执行过程如下:

1. 以 table_A 为驱动表，检索 table_B
2. 根据 on 条件过滤 table_B 的数据 ，构建 table_A 结果集，并且添加外部行。
3. 对结果集执行 where 条件过滤。如果 A 中有一行匹配 where 子句但是 B 中没有一行匹配 on 条件，则生成另一个 B 行，其中所有列设置为 NULL。

驱动表:用来最先获得数据，并以此表为依据，逐步获得其他表的数据，直至最终查询到所有符合条件的数据的第一个表。外连接:根据连接方向，主表是驱动表；内连接:小表驱动大表（where 条件后的小的结果集将作为驱动表）。

在上述过程中，根据 on 条件过滤 table_B 的数据的算法，就被称为 join 算法。

• SNLJ（Simple Nested Loop Join）:
  1. 从驱动表中选取数据，遍历被驱动表，匹配到的数据放入结果集
  2. 重复选取驱动表中的其他数据，直到遍历完驱动表
  3. 匹配效率非常低，并且还伴随着大量的 IO
• INLJ（Index Nested Loop Join）
  1. 相比于 SNLJ ，从驱动表中选取出的数据和被驱动表中的匹配时，使用索引进行遍历
  2. 因此要求 on 的匹配条件是被驱动表的索引
  3. 提高了匹配效率
• BNLJ（Block Nested Loop Join）
  1. 相比于 SNLJ ，在遍历被驱动表时，会读取整块的数据并将其缓存在内存的 join buffer 中
  2. 是被驱动表没有索引情况下的默认算法（8.0.18 以前）
  3. 减少 IO 次数

总体性能:INLJ > BNLJ > SNLJ

优化

1. 为被驱动表的 on 匹配条件添加索引
2. 增加 join buffer 的大小（通过增加缓存数据的大小，减少 IO 次数）
3. 减少驱动表不必要的字段查询（字段越少，join buffer 缓存的数据就越多）

MySQL 从 8.0.18 版本开始添加 Hash Join，并从 8.0.20 开始移除 BNLJ

• Nested Loop:对于被连接的 数据子集较小 的情况下，Nested Loop 是个较好的选择。
• Hash Join 是做 大数据集连接 时的常用方式，优化器使用两个表中较小（相对较小）的表利用 Join Key 在内存中建立散列值，然后扫描较大的表并探测散列值，找出与 Hash 表匹配的行。
  • 这种方式适用于较小的表完全可以放入内存中的情况，这样总成本就是访问两个表的成本之和。
  • 在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要求有较大的临时段从而尽量提高 I/O 的性能。
  • 它能够很好的工作于没有索引的大表和并行查询的环境中，并提供最好的性能。Hash Join 只能应用于等值连接，这是由 Hash 的特点决定的。

子查询优化

参考: MySQL 子查询优化 - 简书 (jianshu.com)

子查询执行慢的原因:

• 执行子查询时，MySQL 需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的 CPU 和 IO 资源，产生大量的慢查询。
• 子查询的临时表中不会存在索引。

优化方法:用连接查询（join）替代子查询

排序优化

MySQL 两种排序方法:

• index:直接使用索引，因为索引是有序的，所以不需要额外的排序。效率高。
• filesort:通过相应的排序算法，将取得的数据在系统变量设置的内存排序区（sort_buffer_size）中进行排序，如果内存装载不下，会将磁盘上的数据进行分块，再对各个数据块进行排序，再将各个块合并成有序的结果集。效率低。

优化排序的思路就是尽量使用 index，迫不得已的情况下也要使用优化的 filesort。

然而，并非是 只要 order by 的列上有索引，排序就一定会用 index ，需要分情况来看。

几种常见的排序情况

参考: MySQL 优化篇:排序分组优化

• order by 的列上建立了索引并且使用了 limit 关键字，则会使用索引:

  select * from student order by age limit 100;
  

  如果没有使用 limit ，排序就不会走索引。因为如果使用了索引， select * 会引起大量回表，执行器会判断这种行为得不偿失，就将其优化成了 filesort。

  但是如果实现了索引覆盖，那么不加 limit 关键字，也会走索引:

  select age from student order by age limit 100;
  

• 排序的列不满足联合索引的最左前缀原则，不使用索引；排序的顺序和索引相反，不使用索引。
• 没有 where 条件过滤，不使用索引

filesort 的两种排序法

• 双路排序（慢）

  扫描两次磁盘最终得到数据。第一次扫描获取到排序列和主键，将其放入 sort_buffer 中按照 order by 排序后，再进行第二次扫描，获取到完整的行数据返回。

• 单路排序（快）

  扫描一次最终得到数据。直接从磁盘读取查询需要的所有列，按照 order by 在 sort_buffer 中排序，然后返回。

整体来看，单路排序各方面优于双路排序，但是会占用更多的内存空间，并且需要适当提高 sort_buffer_size 。

优化思路:

1. 提高 sort_buffer_size
2. 提高 max_length_for_sort_data （数据总量超过这个值则会用双路，低于这个值用单路）
3. select 尽量只选择必要的字段

分组优化

• group by 使用索引的原则几乎跟 order by 一致 ，group by 即使没有过滤条件用到索引，也可以直接使用索引。
• group by 先排序再分组，遵照索引建的最佳左前缀法则
• 当无法使用索引列，可以增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置
• where 效率高于 having，能写在 where 限定的条件就不要写在 having 中了
• 减少使用 order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct 这些语句较为耗费 CPU，数据库的 CPU 资源是极其宝贵的。
• 包含了 order by、group by、distinct 这些查询的语句，where 条件过滤出来的结果集请保持在 1000 行以内，否则 SQL 会很慢。

分页优化

参考: 数据量很大，分页查询很慢，怎么优化？ - 简书 (jianshu.com)

分页查询例子:

select * from student limit 10000,100;
# 查询 10000-10100 条数据，默认是按照主键排序

上述例子虽然只查询出了 100 条数据，但实际上是先读取 10100 条数据，再抛弃前 10000 条数据。如果偏移量或者查询数量较多，那么查询的效率是很低的。

优化:

• 先用子查询，检索出指定范围内的主键

  select * from student t,(select id from student limit 10000,100) a where t.id = a.id;
  

  由于子查询满足了索引覆盖，所以查询的效率很高

• 如果主键是连续递增的，可以用 id 的范围

  select * from student where id between 10000 and 10100;
  

索引条件下推

参考: MySQL--索引条件下推优化 - zengkefu - 博客园 (cnblogs.com)

简称 ICP (Index Condition Pushdown)

假设有联合索引 idx(class_id,student_name,address) :

select * from student
  where class_id = "1"
  and student_name like "%王%"
  and address like "%江苏%";
# like 条件不走索引

• 不开启 ICP 执行过程:先根据 class_id 回表找到数据，然后将数据与 like 条件匹配。数据量大时有大量回表。
• 开启 ICP 执行过程:先在索引树中判断是否符合 like 条件，再将筛选完的数据回表（由于创建的是联合索引，所以索引树的叶子结点上有完整的 class_id、student_name、address）。减少回表次数。

ICP 默认开启，也可以用 set optimizer_switch='index_condition_pushdown=off/on' 手动开启或关闭

其他优化建议

• exist 和 in 的区别

  select * from A where A_id exists (select A_id from B)
  # 以 A 为主表
  
  select * from A where A_id in (select A_id from B)
  # 以 B 为主表
  

  根据小表驱动大表的原则，A 表大则用 exists，B 表大则用 in

• 如果确定查询的结果只有一条，可以加上 limit 1 来提高效率（避免找到结果后继续扫描）