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) 建立索引。
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
· DeepSeek 开源周回顾「GitHub 热点速览」