MySQL联合索引的命中规则

一.建表

创建测试表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语句就不会进索引；