索引带来的SQL优化

索引失效的场景，可以查看我的另一篇博客一蓑烟雨任平生yuuu博客http://www.yuuu.online/#/DetailArticle?aid=21

下面也会提到一些，不过还是建议两者结合使用

最左匹配原则

简述最左匹配原则

最左匹配原则就是指在联合索引中，如果你的 SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配。以最左边的为起点任何连续的索引都能匹配上，同时遇到范围查询(>、<、between、like)就会停止匹配。

假设现在有一个（name，age）的索引，如果查询条件是 where age = 18，那么这条查询就不会走索引。但是如果我们加上name，此时查询条件变成 where name = ? and age = 18，又或者是where age = ? and name = ?，这样的查询都会走索引（优化器会自动调整name,age的顺序）。

为什么联合索引要满足最左匹配原则呢？

这就要说到MySQL底层的数据结构了。众所周知，MySQL底层是B+树，非叶子节点存索引，叶子节点存数据。联合索引也是如此，只不过联合索引的B+树节点存储的是键值。由于构建一棵B+树只能根据一个值来确定索引关系，所以数据库依赖联合索引最左的字段来构建。

简单来说就是，以(A,B)创建的联合索引，在B+树中，A是有序的，B是散列的；如果A是等值的，那么B就是有序的。

(1,2) (1,3) (2,1) (3,4) (3,6) 整个存储就是类似这样的数据，前为A，后为B，当A都为1，那么B就是有序的。否则单看B：2、3、1、4、6就是无序的。

补充

MySQL在8.0有了优化，最左缀原则可以通过跳跃扫描的方式打破，当第一列索引的唯一值较少时，即使where条件没有最左索引，查询的时候也可以用到联合索引。 比如我们使用的联合索引是 bcd 但是b中字段比较少 我们在使用联合索引的时候没有 使用 b 但是依然可以使用联合索引

跳过扫描访问方法适用于以下情况（8个条件）：

1. 表T具有至少一个复合索引，其关键部分的形式为（[A_1，...，A_k]B_1, ..., B_m, C [, D_1, ..., D_n])关键部分A和D可以为空，但B和C必须为非空
2. 该查询仅引用一个表
3. 查询不使用GROUP BY 或 DISTINCT
4. 该查询仅引用索引中的列
5. A_1，...，A_k上的谓词必须是相等谓词，并且它们必须是常量。这包括

   in()操作

6. 该查询必须是一个联合查询。即，AND或OR条件

7. C上必须有范围条件

8. D列上的条件是允许的。D上的条件必须与C上的范围条件结合使用

Select *

• select * 会走索引

• 范围查找有概率索引失效但是在特定的情况下会生效 范围小就会使用 也可以理解为 返回结果集小就会使用索引

• mysql中连接查询的原理是先对驱动表进行查询操作，然后再用从驱动表得到的数据作为条件，逐条的到被驱动表进行查询

• 每次驱动表加载一条数据到内存中，然后被驱动表所有的数据都需要往内存中加载一遍进行比较。效率很低，所以mysql中可以指定一个缓冲池的大小，缓冲池大的话可以同时加载多条驱动表的数据进行比较，放的数据条数越多性能io操作就越少，性能也就越好。所以，如果此时使用select * 放一些无用的列，只会白白的占用缓冲空间。浪费本可以提高性能的机会

•  select * 不是造成索引失效的直接原因 大部分原因是 where 后边条件的问题 但是还是尽量少去使用select * 多少还是会有影响的

使用Or导致索引失效

这个的优化方式就是 在Or的时候两边都加上索引

in使用不当

in 在结果集大于30%的时候索引失效，走全表扫描。所以使用in时，要注意查询的数据量

ORDER BY优化

1. SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中 避免全表扫描 ，在 ORDER BY 子句 避免使用 FileSort 排序 。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。

2. 尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列； 如果不同就使用联合索引。

3. 无法使用 Index 时，需要对 FileSort 方式进行调优。

INDEX a_b_c(a,b,c)
order by /*能使用索引最左前缀*/
- ORDER BY a
- ORDER BY a,b
- ORDER BY a,b,c
- ORDER BY a DESC,b DESC,c DESC
/* 如果WHERE使用索引的最左前缀定义为常量，则order by 能使用索引*/
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b = const ORDER BY c
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b > const ORDER BY b,c
/*不能使用索引进行排序*/
- ORDER BY a ASC,b DESC,c DESC /* 排序不一致 */
- WHERE g = const ORDER BY b,c /*丢失a索引*/
- WHERE a = const ORDER BY c /*丢失b索引*/
- WHERE a = const ORDER BY a,d /*d不是索引的一部分*/
- WHERE a in (...) ORDER BY b,c /*对于排序来说，多个相等条件也是范围查询*/

filesort算法：双路排序和单路排序

双路排序 （慢）

• MySQL 4.1之前是使用双路排序 ，字面意思就是两次扫描磁盘，最终得到数据， 读取行指针和 order by列 ，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
• 从磁盘取排序字段，在buffer进行排序，再从 磁盘取其他字段 。

取一批数据，要对磁盘进行两次扫描，众所周知， IO 是很耗时的，所以在 mysql4.1 之后，出现了第二种改进的算法，就是单路排序。

单路排序 （快）

从磁盘读取查询需要的 所有列 ，按照 order by 列在 buffer 对它们进行排序，然后扫描排序后的列表进行输出， 它的效率更快一些，避免了第二次读取数据。并且把随机IO 变成了顺序 IO ，但是它会使用更多的空间， 因为它把每一行都保存在内存中了。

结论及引申出的问题

• 由于单路是后出的，总体而言好过双路
• 但是用单路有问题

优化策略

1. 尝试提高 sort_buffer_size

2. 尝试提高 max_length_for_sort_data

3. Order by 时 select * 是一个大忌。最好只 Query 需要的字段。

GROUP BY优化

• 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会很慢

优先考虑覆盖索引

覆盖索引，简单来说就是你要查询的数据恰好包含在联合索引中，这样就避免了回表操作，减小IO。

举个例子，现在有一个用户表（User）：

• id (主键)
• name
• age
• email
• address
• phone

如果我们需要查询用户表中30岁以上的用户的姓名和邮件地址，传统的索引只能定位到满足条件的用户行，然后需要再次访问数据表以获取姓名和邮件地址的值。这将导致额外的IO操作，可能会降低查询性能，尤其是当表很大时。

使用覆盖索引，我们可以创建一个包含（age, name, email）三个列的索引。这个索引将包含查询所需的所有数据，因此数据库可以直接从索引中获取姓名和邮件地址的值，无需额外的表访问。

索引下推

Index Condition Pushdown(ICP) 是 MySQL 5.6 中新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式。ICP 可以减少存储引擎访问基表的次数以及 MySQL 服务器访问存储引擎的次数。

使用前后的扫描过程

在不使用 ICP 索引扫描的过程：

storage 层：只将满足 index key 条件的索引记录对应的整行记录取出，返回给 server 层

server 层：对返回的数据，使用后面的 where 条件过滤，直至返回最后一行

使用 ICP 扫描的过程：

storage 层： 首先将index key 条件满足的索引记录区间确定，然后在索引上使用 index filter 进行过滤。将满足的 index filter条件的索引记录才去回表取出整行记录返回 server 层。不满足 index filter 条件的索引记录丢弃，不回表、也不会返回server 层。

server 层： 对返回的数据，使用table filter 条件做最后的过滤。

还是举个例子，还是那 个用户表（User）：

• id (主键)
• name
• age
• email

我们根据age创建索引，此时要查询where age > 18 and email = "123@qq.com"，没有ICP的话，就会将这两步分开，先查出来age>18的数据返回，再对查出的数据进行判断，看email是否为123@qq.com；而有了ICP，就会将age>18的数据再进行判断，筛选出符合条件的返回。注意，两者的返回时机是不一样的。

使用前后的成本差别

使用前，存储层多返回了需要被 index filter 过滤掉的整行记录

使用 ICP 后，直接就去掉了不满足 index filter 条件的记录，省去了他们回表和传递到 server 层的成本。

ICP 的 加速效果 取决于在存储引擎内通过 ICP 筛选 掉的数据的比例。

ICP的使用条件

① 只能用于二级索引 (secondary index)

② explain 显示的执行计划中 type 值（join 类型）为 range 、 ref 、 eq_ref 或者 ref_or_null 。

③ 并非全部 where 条件都可以用 ICP 筛选，如果 where 条件的字段不在索引列中，还是要读取整表的记录到server 端做 where 过滤。

④ ICP 可以用于 MyISAM 和 InnnoDB 存储引擎

⑤ MySQL 5.6 版本的不支持分区表的 ICP 功能， 5.7 版本的开始支持。

⑥ 当 SQL 使用覆盖索引时，不支持 ICP 优化方法。

内容就写到这里了，觉得有帮助的话麻烦您点亮小小的爱心。

参考

MySQL8.0优化（一）松散索引扫描/索引跳跃扫描ISS - 掘金 (juejin.cn)

MySQl 索引之道 - 掘金 (juejin.cn)