MySQL 中的联合索引介绍【最左前缀匹配原则】

学习笔记 马富天 2020-06-08 16:53:31 121 0

【摘要】在进行多条件查询时,我们不可避免的会用到联合索引,在实际开发的使用是非常频繁的。mysql 的联合索引有称为复合索引、组合索引,而讲到联合索引一定是离不开最左前缀匹配原则的,本文通过举些简单的例子,来讲解联合索引中的最左匹配原则。联合索引优化的目标就是覆盖索引,即在使用 sql 查询的时候要尽快能的使所有的字段都能够被利用。

所谓最左匹配原则指的是如果在 SQL 查询语句中用到了联合索引,那么这条 SQL 语句可以利用联合索引去匹配,但必须遵守最左优先匹配原则,否则索引会失效,需要注意的是,当遇到范围查询(>,<,between,like)就会停止匹配。

假设有一张表 union_index ,其中有一个联合索引 abc(a,b,c),注意这里的索引顺序 a、b、c 是很重要的,最左优先也就是在这里突出的,并不是指的是条件中的顺序如: where a = 1 and c = 1 and b = 1 这里的顺序是 a、c、b,两者是不同的。

遵循最左侧原则,指从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是 key abc(a,b,c)。 只能支持a a,b a,b,c 这 3 种组合进行查询。

  1. create table union_index(
  2. 	id int auto_increment primary key,
  3. 	a int,
  4. 	b int,
  5. 	c int,
  6. 	d int,
  7. 	index abc(a,b,c)
  8. );

我们可以通过如下一下 sql 来了解最左前缀匹配的原则是什么样的。

一、多列索引在 and 查询中的使用

  1. -- 查询效率最高,索引全部覆盖(口诀:全值匹配我最爱)
  2. select * from union_index where a = 1 and b = 1 and c = 1; 
  3. -- 覆盖索引 a 和 b
  4. select * from union_index where a = 1 and b = 1; 
  5. -- 经过 mysql 查询分析器的优化,覆盖索引 a 和 b
  6. select * from union_index where b = 1 and a = 1; 
  7. -- 只覆盖最左的索引字段 a
  8. select * from union_index where a = 1; 
  9. -- 没有走索引,最左字段 a 不存在(口诀:带头大哥不能死)
  10. select * from union_index where b = 1; 
  11. select * from union_index where b = 1 and c = 1; -- 同上
  12. select * from union_index where c = 1; -- 同上
  13. -- 覆盖索引 a (c 没有覆盖,因为中间的 b 没使用到)(口诀:中间兄弟不能断)
  14. select * from union_index where a = 1 and c = 1; 
  15. -- 覆盖索引 a,非联合索引字段 d 不涉及
  16. select * from union_index where d = 1 and a = 1; 

二、多列索引在范围查询中的使用

  1. -- (口诀:范围之后全失效)
  2. -- 只覆盖索引 a 和 b,但 b 列是范围查询,所以字段 c 不走索引
  3. select * from union_index where a = 1 and (b between 1 and 10) and c = 1; 
  4. -- 覆盖索引 a ,a 是范围查找,因此 b 和 c 无法使用索引
  5. select * from union_index where a > 1 and b = 1 and c = 1; 
  6. -- b 是范围查询,但 b,c 都不走索引,因为最左字段 a 没用到
  7. select * from union_index where b > 1 and c = 1; 
  8. -- 经过 mysql 查询分析器的优化:a = 1 and b = 1 and c > 1,因此覆盖索引 a 和 b 和 c
  9. explain select * from union_index where a = 1 and c > 1 and b = 1; 
  10. -- 覆盖索引 a,d 是非联合索引字段,无关紧要
  11. select * from union_index where d > 1 and a = 1; 

三、多列索引在排序中的使用

联合索引在 order by 的排序中,符合条件的情况下也是会走索引的,不过同样是需要遵守最左前缀原则。可以看成是继承 where 的条件顺序,但是需要 where 先作好前置铺垫,若没有 where 语句,在联合索引中,单纯的 order by 是不会使用任何索引的。

  1. -- order by 排序中用到联合索引最左字段 a,但没有 where 条件做铺垫,故不走任何索引,会出现 Using filesort
  2. select * from union_index order by a asc; 
  3. -- 覆盖索引 a
  4. select * from union_index where a = 1 order by a asc; 
  5. -- 覆盖索引 a 和 b,想象成字段 b 是继承了 where 条件中 a 后面
  6. select * from union_index where a = 1 order by b asc; 
  7. -- 只使用索引 a,字段 b 中断了,故无法使用索引 c,(同样遵守最左优先),会出现 Using filesort
  8. select * from union_index where a = 1 order by c asc; 
  9. -- 覆盖索引 a 和 b 和 c,不会出现 Using filesort
  10. select * from union_index where a = 1 and b = 1 order by c asc; 
  11. -- 覆盖索引 a 和 b 和 c,不会出现 Using filesort
  12. select * from union_index where a = 1 order by b,c asc; 
  13. -- 不走任何索引,会出现 Using filesort
  14. select * from union_index where b = 1 order by c asc; 
  15. -- 覆盖索引 a 和 b,b 和 c 的排序顺序不同,所以不走索引 c,会出现 Using filesort
  16. select * from union_index where a = 1 order by b desc,c asc; 

在 group by 的排序中,也跟 order by 的类似,但是有一个不同点在于:

  1. -- 这种情况在 order by 中是不走索引的,但是在 group by 中是利用索引了
  2. select * from union_index group by a; 

最后给大家送上索引优化六句必背口诀(全网一致)(当然需要在理解的基础上熟背以下的口诀):

全值匹配我最爱,最左前缀要遵守;

带头大哥不能死,中间兄弟不能断;

索引列上少计算,范围之后全失效;

LIKE百分写最右,覆盖索引不写星(*);

不等空值还有or,索引失效要少用;

字符引号不能丢,SQL优化有诀窍。

版权归 马富天PHP博客 所有

本文标题:《MySQL 中的联合索引介绍【最左前缀匹配原则】》

本文链接地址:http://www.numberer.net/447.html

转载请务必注明出处,小生将不胜感激,谢谢! 喜欢本文或觉得本文对您有帮助,请分享给您的朋友 ^_^

0

0

上一篇《 在 MySQL 执行计划中 Using filesort 指的是什么意思? 》 下一篇《 linux 文件锁 flock 解决定时任务超时重复执行问题 》

暂无评论

评论审核未开启
表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情 表情
验证码

TOP10

  • 浏览最多
  • 评论最多