MySQL 高级 -- 性能优化之索引使用

👨‍🎓作者：Java 学术趴

🏦仓库：Github、Gitee

✏️博客：CSDN、掘金、InfoQ、云+社区

💌公众号：Java 学术趴

🚫特别声明：原创不易，未经授权不得转载或抄袭，如需转载可联系小编授权。

🙏版权声明：文章里的部分文字或者图片来自于互联网以及百度百科，如有侵权请尽快联系小编。微信搜索公众号 Java 学术趴联系小编。

☠️每日毒鸡汤：一件事你犹豫去不去做，那就是该立即动身做的。

3.6 关联表如何加索引

• 单表： 可以任意加索引，不用区分表。

• 两张表关联： 左关联索引加在右表，右关联加在左表。当进行左右关联时为了不修改表的索引，可以对调两个表的关联位置，使存在索引表的索引生效。

• 多张表关联： 多张表的连接方式和两张表的连接方式一致。都是左连接在右表建索引....

3.7 索引失效

• 全值匹配

查询条件越加的细致所要付出的代价就要大一些，长度会变长等等...

• 最佳左前前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最前列并且不跳过索引中的列。

生效原则：带头大哥不能死，中间兄弟不能断

使用聚合索引的时候，起始的查询字段必须使用索引对应的第一个字段，否则索引失效。

当聚合索引出现断层的时候，这个时候只有部分索引生效。

• 不在索引列上做任何操作(计算、函数、类型转换等) ，会导致索引失效而转为全表扫描。

在存在索引的字段上使用函数之后索引会失效。

• 存储引擎不能使用索引中范围条件右边的列。

范围索引之前的索引生效(包括范围的索引)，范围之后的索引会失效。

• 尽量使用覆盖索引(只使用索引中存在的字段，减少 SELECT * 的使用)。

• MySQL 在使用不等于(!= 或者 <>)的时候无法使用索引会导致全表扫描。

• IS NULL ，IS NOT NULL 也无法使用索引。

• LIKE 以通配符开头('%abc...')MySQL 索引失效会变成全表扫描的操作。

只有模糊查询的占位符放到右边的时候索引才会生效。

当给定的需求就是模糊查询左右都需要占位符（like '%张三 %'）的时候该如何让索引生效?????

这个时候就需要使用覆盖索引来弥补占位符的失效，也就是查询的字段都是创建索引的字段，不能包含没有创建索引的字段，并且聚合索引需要满足最左匹配原则。

• 字符串不加单引号索引失效。

• 少用 or，用它来连接时会索引失效。

3.8 索引使用列题

例题 1：全值匹配我最爱

假设：index(c1,c2,c3,c4)

在 MySQL 中存在优化器，他会自动把我们输入 4,3,2,1 的顺序转换为 1,2,3,4

此时生效的索引：c1,c2,c3,c4

例题 2：范围查询

如果按照聚合索引的顺序查询，范围查询前的字段(包括范围字段)索引会生效，范围后边的索引失效

此时生效的索引：c1,c2,c3

如果不按照聚合索引的顺序查询，MySQL 会使用自带的优化器把查询条件按照索引顺序进行排序，之后在进行筛选查询。

此时生效的索引：c1,c2,c3,c4

例题 3：排序查询

排序时索引也用到了，只不过此时该索引不是用来查询，而是用来排序，explain 没有展示出来

此时生效的索引：c1,c2

c1,c2 索引都用到了，直接使用 c3 进行排序，此时和 c4 没有什么关系

此时生效的索引：c1,c2

c1,c2 索引都用到了，此时直接使用 c4 排序，导致 c3 出现断层，MySQL 优化器不能直接进行排序，在内部进行了一次 filesort 内排序 ，使 SQL 的性能下降。

c1 索引用到了，c2,c3 是按照顺序进行排序的，没有发生内排序，c2,c3 索引也用到了，只不过用在了排序上，而不是用在查询上。

c1 索引用到了，但是排序的索引顺序反了，发生了内排序。

第一个因为在条件中已经声明了索引 c2，所以在排序的时候会忽略这个 c2 索引，不会发生内排序现象。

第二个因为在条件中没有声明 c2，所以直接在排序中使用 c2 不会被忽略，c2、c3 索引顺序相反会发生内排序现象。

排序默认是升序排序，但是此时非要实现降序排序，这就会导致 MySQL 发生内排序(filesort)

结论：

• MySQL 支持两种方式的排序，FileSort 以及 Index，Index 效率高，它指 MySQL 扫描索引本身完成排序。FoleSort 方式效率较低。

ORDER BY 满足两种情况，会使用 Index 排序

• Order by 语句使用索引最左前列。

• 使用 Where 子句与 Order by 子句条件列组合满足索引最左前列。

• 尽可能在索引列上完成排序操作，遵照索引建的最左前缀。

如果不在索引列上，filesort 有两种算法：

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

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

例题：分组查询

• 索引的作用是用于查询和排序，而不用于分组。但是分组的前提是排序，所以说分组和排序使用索引的法则差不多。

• group by 基本上都要进行排序，会有临时表的产生。

只用到了 c1 索引

以下虽然使用带了 c1 索引，但是分组出现了非常大的错误，既发生了内排序，还产生了临时表，此时的性能非常的差。

例题：模糊查询

c1,c2,c3 索引使用到了，like 也属于范围查询，但是他区别于大于...查询的优势在于，如果 like 后面的条件是按照索引顺序来的，那么这个索引也会生效(前提是 %通配符在左侧)

c1 索引使用到了，模糊匹配以通配符开头，后边的索引全部失效。

c1 索引被使用了

c1,c2,c3 都使用到了，因为不是使用通配符开头的。

【索引优化口诀】全值匹配我最爱，最左前缀要遵守；带头大哥不能死，中间兄弟不能断；索引列上少计算，范围之后全失效；LIKE百分写最右，覆盖索引不写星；

3.9 索引优化总结

• 对于单键索引，尽量选择针对当前查询过滤性更好的索引。

• 在选择组合索引的时候，当前查询中过滤性最好的字段在索引字段顺序中，位置越靠前越好。

• 在选择组合索引的时候，尽量选择可以包含当前查询中的 where 中更多字段的索引。

• 尽量通过分析统计西悉尼和调整查询的写法来达到选择合适索引的目的。