博主辛苦了，我要打赏银两给博主，犒劳犒劳站长。
【摘要】在进行多条件查询时，我们不可避免的会用到联合索引，在实际开发的使用是非常频繁的。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 种组合进行查询。
create table union_index(
id int auto_increment primary key,
a int,
b int,
c int,
d int,
index abc(a,b,c)
);
我们可以通过如下一下 sql 来了解最左前缀匹配的原则是什么样的。
-- 查询效率最高,索引全部覆盖（口诀：全值匹配我最爱）
select * from union_index where a = 1 and b = 1 and c = 1;
-- 覆盖索引 a 和 b
select * from union_index where a = 1 and b = 1;
-- 经过 mysql 查询分析器的优化，覆盖索引 a 和 b
select * from union_index where b = 1 and a = 1;
-- 只覆盖最左的索引字段 a
select * from union_index where a = 1;
-- 没有走索引,最左字段 a 不存在（口诀：带头大哥不能死）
select * from union_index where b = 1;
select * from union_index where b = 1 and c = 1; -- 同上
select * from union_index where c = 1; -- 同上
-- 覆盖索引 a （c 没有覆盖，因为中间的 b 没使用到）（口诀：中间兄弟不能断）
select * from union_index where a = 1 and c = 1;
-- 覆盖索引 a,非联合索引字段 d 不涉及
select * from union_index where d = 1 and a = 1;
-- （口诀：范围之后全失效）
-- 只覆盖索引 a 和 b,但 b 列是范围查询,所以字段 c 不走索引
select * from union_index where a = 1 and (b between 1 and 10) and c = 1;
-- 覆盖索引 a ,a 是范围查找,因此 b 和 c 无法使用索引
select * from union_index where a > 1 and b = 1 and c = 1;
-- b 是范围查询,但 b,c 都不走索引，因为最左字段 a 没用到
select * from union_index where b > 1 and c = 1;
-- 经过 mysql 查询分析器的优化：a = 1 and b = 1 and c > 1,因此覆盖索引 a 和 b 和 c
explain select * from union_index where a = 1 and c > 1 and b = 1;
-- 覆盖索引 a,d 是非联合索引字段,无关紧要
select * from union_index where d > 1 and a = 1;
三、多列索引在排序中的使用
联合索引在 order by 的排序中，符合条件的情况下也是会走索引的，不过同样是需要遵守最左前缀原则。可以看成是继承 where 的条件顺序，但是需要 where 先作好前置铺垫，若没有 where 语句，在联合索引中，单纯的 order by 是不会使用任何索引的。
-- order by 排序中用到联合索引最左字段 a,但没有 where 条件做铺垫,故不走任何索引,会出现 Using filesort
select * from union_index order by a asc;
-- 覆盖索引 a
select * from union_index where a = 1 order by a asc;
-- 覆盖索引 a 和 b,想象成字段 b 是继承了 where 条件中 a 后面
select * from union_index where a = 1 order by b asc;
-- 只使用索引 a,字段 b 中断了，故无法使用索引 c,(同样遵守最左优先)，会出现 Using filesort
select * from union_index where a = 1 order by c asc;
-- 覆盖索引 a 和 b 和 c,不会出现 Using filesort
select * from union_index where a = 1 and b = 1 order by c asc;
-- 覆盖索引 a 和 b 和 c,不会出现 Using filesort
select * from union_index where a = 1 order by b,c asc;
-- 不走任何索引,会出现 Using filesort
select * from union_index where b = 1 order by c asc;
-- 覆盖索引 a 和 b,b 和 c 的排序顺序不同,所以不走索引 c，会出现 Using filesort
select * from union_index where a = 1 order by b desc,c asc;
在 group by 的排序中，也跟 order by 的类似，但是有一个不同点在于：
-- 这种情况在 order by 中是不走索引的，但是在 group by 中是利用索引了
select * from union_index group by a;
最后给大家送上索引优化六句必背口诀（全网一致）（当然需要在理解的基础上熟背以下的口诀）：
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE百分写最右,覆盖索引不写星(*)；
不等空值还有or,索引失效要少用；
字符引号不能丢，SQL优化有诀窍。
版权归 马富天个人博客 所有
本文标题：《MySQL 中的联合索引介绍【最左前缀匹配原则】》
本文链接地址：http://www.mafutian.com/447.html
转载请务必注明出处，小生将不胜感激，谢谢! 喜欢本文或觉得本文对您有帮助，请分享给您的朋友 ^_^
顶0
踩0
| 评论审核未开启 |
|
||
|