一.建表
创建测试表foo,创建联合索引,顺序abc
CREATE TABLE foo (
a VARCHAR ( 20 ) DEFAULT '' NULL,
b VARCHAR ( 20 ) DEFAULT '' NULL,
c VARCHAR ( 20 ) DEFAULT '' NULL,
d VARCHAR ( 20 ) DEFAULT '' NULL
);
CREATE INDEX foo_index_a_b_c ON foo ( a, b, c );
Mysql的联合索引,根据最左原则, 有a,ab,abc三个最左索引前缀可供命中;
即:只要查询条件中带有a字段条件,或者同时带有ab两个字段的条件,或者同时带有abc三个字段的条件,都会使用到联合索引abc
二.前七种情况
下面给出八种情况示例,前三种能用到索引,4 5 6没有用到,第7种只有索引最左边的字段能用到
第一条的执行结果
-- 1 组成abc索引前缀,完整使用了abc联合索引
explain select * from foo where d='kk' and a='uu' and b='xx' and c='mm'
第二三条的执行结果
-- 2 组成ab索引前缀,使用了abc联合索引的ab部分
explain select * from foo where a='uu' and b='xx' and d='kk'
-- 3 组成a索引前缀,使用了abc联合索引的 a部分
explain select * from foo where a='uu' and d='kk'
第四条执行结果
-- 4 无法组成最左索引前缀
explain select * from foo where b='xx' and d='kk' and c='mm'
第五六条执行结果
-- 5 全or情况
explain select * from foo where a='uu' or b='xx' or c='mm'
-- 6 部分or情况
explain select * from foo where a='uu' and b='xx' or c='mm'
注意:where后面的搜索条件顺序是没有影响的,mysql的查询优化器会自动优化查询;
第七条执行结果
-- 7 范围查询-只有索引最左边的列进行范围查找才能用到索引,因为只有a相同时才能用到b的排序;
-- 所以,除非a是精确查询,b是范围查询,此时就可以用到ab部分的索引
explain select * from foo where a>'uu' and a<'xx' and b>'kk'
InnoDB的索引虽然采用的B+tree结构,大原则上也遵循最左匹配;但是真正执行查询的时候,会根据实际情况,不一定会走索引,比如使用order by(字段符合索引顺序,不符合再多数据也不走)数据太少时,不会走索引。
三.关于or导致索引失效?
而第8种情况,是针对or是否会使索引失效;
此时我们对foo表增加一条索引
ALTER TABLE foo ADD KEY index_c ('c')
此时表结构
CREATE TABLE `foo` (
`a` varchar(20) DEFAULT '',
`b` varchar(20) DEFAULT '',
`c` varchar(20) DEFAULT '',
`d` varchar(20) DEFAULT '',
KEY `foo_index_a_b_c` (`a`,`b`,`c`),
KEY `index_c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
执行第6条语句
explain select * from foo where a='uu' and b='xx' or c='mm'
此时可以看到是用到了索引的;
我们在以上前提下,再执行第5条语句
explain select * from foo where a='uu' or b='xx' or c='mm'
可以发现没有用到索引;
总结:将or左右条件拆开时,这两部分只要其中之一独立使用用不到索引,那么这整条or语句就不会进索引;