Mysql联合索引最左匹配原则

联合索引又叫复合索引,对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部份,但只能是最左侧部分。例如索引是key index(a,b,c),可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c进行查找 。当最左侧字段是常量引用时,索引就十分有效。

最左匹配原则:在通过联合索引检索数据时,从索引中最左边的列开始,一直向右匹配,如果遇到范围查询(>、<、between、like等),当前字段会使用索引(算是断点),就停止后边的匹配

举例:对字段 (a, b, c) 建立联合索引,现在有这样一条查询语句:

where a > xxx and b=yyy and c=zzz
where a like 'xxx%' and b=yyy and c=zzz

在这个条件语句中,只有a用到了索引,后面的b,c就不会用到索引。这就是“如果遇到范围查询(>、<、between、like等),就停止后边的匹配”的意思。

假如对字段 (a, b, c) 建立联合索引,以下情况:

1.如下查询语句可以使用到索引:

复制代码
where a = xxx
where a = xxx and b = xxx
where a = xxx and b = xxx and c = xxx
where a like 'xxx%'
where a > xxx
where a = xxx order by b
where a = xxx and b = xxx order by c group by a
复制代码

2.如下查询条件也会使用索引:

where b = xxx and a = xxx
where a = xxx and c = xxx and b = xxx

虽然b和a的顺序换了,但是mysql中的优化器会调整顺序。

3.如下查询条件只用到联合索引的一部分:

where a = xxx and c = xxx 可以用到 a 列的索引,用不到 c 列索引。
where a like 'xxx%' and b = xxx 可以用到 a 列的索引,用不到 b 列的索引。
where a > xxx and b = xxx 可以用到 a 列的索引,用不到 b 列的索引。

4.如下查询条件完全用不到索引

where b = xxx
where c = xxx
where a like '%xxx' -- 不满足最左前缀
where d = xxx order by a -- 出现非排序使用到的索引列 d
where a + 1 = xxx -- 使用函数、运算表达式及类型隐式转换等

如:建立姓名、年龄、性别的复合索引:

复制代码
 create table myTest(
         a int,
         b int,
         c int,
         KEY a(a,b,c)
    );
(
1)select * from myTest where a=3 and b=5 and c=4; ---- abc顺序 abc三个索引都在where条件里面用到了,而且都发挥了作用 (2)select * from myTest where c=4 and b=6 and a=3; where里面的条件顺序在查询之前会被mysql自动优化(查询优化器),效果跟(1)一样 (3)select * from myTest where a=3 and c=7; a用到索引,没有用到b,所以c没有用到索引效果 (4)select * from myTest where a=3 and b>7 and c=3; ---- b范围值,断点,阻塞了c的索引 a用到了,b也用到了,这个地方b是范围值,算断点,只不过自身用到了索引,c没有用到
【mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,本身字段也用到了索引,但后面的字段就失效】
(5)select * from myTest  where b=3 and c=4;   --- 联合索引必须按照顺序使用,并且需要全部使用
因为a索引没有使用,所以这里bc都没有用上索引效果
(6)select * from myTest  where a>4 and b=7 and c=9;
a用到了,b、c均没有使用
(7)select * from myTest  where a=3 order by b;
a用到了索引,b在结果排序中也用到了索引的效果,a下面任意一段的b是排好序的
(8)select * from myTest  where a=3 order by c;
a用到了索引,但是c失效(没有发挥排序效果),因为中间断点了,使用 explain 可以看到 filesort
(9)select * from mytable where b=3 order by a;
b没有用到索引,排序中a也没有发挥索引效果

(10)不能使用索引进行排序
- ORDERE BY a ASC,b DESC,c DESC 排序不一致【排序规律是相同的(要么同时升序,要么同时降序)】
- WHERE d=const ORDER BY b,c 丢失a索引
- WHERE a=const ORDER BY C 丢失b索引
- WHERE a=const ORDER BY d d不是索引的一部分
(11)如果where使用了索引的最左前缀定义为常量,则order by能使用索引

- WHERE a=const order by b,c
- WHERE a=const AND b=const ORDRE BY c
- WHERE a=const AND b>const ORDER BY b,c

复制代码

如何选择合适的联合索引

1、如果是 where a = xxx and b = xxx and c = xxx 建索引时,就可以考虑将选择性高的列放在索引的最前列,选择性低的放后边。

2、如果是 where a > xxx and b = xxx 或 where a like ‘xxx%’ and b = xxx 这样的语句,可以对 (b, a) 建立索引。

3、如果是 where a = xxx order by b 这样的语句,可以对 (a, b) 建立索引。

posted @   李若盛开  阅读(1421)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
· DeepSeek 开源周回顾「GitHub 热点速览」
点击右上角即可分享
微信分享提示