mysql联合索引深入

前言

记住了mysql联合索引的最左匹配规则，自以为就了解了其原理，等到面试被问到细节问题才发现遗漏了些东西，这里自己整理一下这方面的内容。

以下内容使用表底部users表为例，底部有表创建的sql

最左前缀匹配原则

在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，示例：
对列col1、col2、col3建一个联合索引

alter table `users` add INDEX `union_index` (`col1`,`col2`,`col3`);

联合索引 union_index 实际建立了(col1)、(col1,col2)、(col,col2,col3)三个索引。

select * from users where col1='a' and col2='b' and col4='d';

上面这个查询语句执行时会依照最左前缀匹配原则，检索时会使用索引(col1,col2)进行数据匹配。

其中：col1和col2可以任意顺序，即下面两条语句都可以走索引：

select * from users where col1='a' and col2='b';

-- 经过mysql的查询分析器的优化 还是会走索引 col1,col2
select * from users where col2='b' and col1='a';

有的文章说下面 select * from users where col2='b'; 也能走索引，我实际测试发现并不可用。

 

users表数据：

-- 创建表
create table `users`(
`id` int(11) auto_increment,
`col1` varchar(20) not null,
`col2` varchar(20) not null,
`col3` varchar(20) not null,
`col4` varchar(20) not null,
primary key (`id`)
)engine=innodb charset=utf8;

-- 添加索引
alter table `users` add INDEX `union_index` (`col1`,`col2`,`col3`);

-- 插入数据
insert into `users` (`col1`,`col2`,`col3`,`col4`)VALUES('a','b','c','d'),('a','c','b','d'),('b','a','c','d'),('b','c','a','d'),('c','a','b','d'),('c','b','a','d');
-- 重复插件数据，好观察分析
insert into `users` (`col1`,`col2`,`col3`,`col4`)VALUES('a','b','c','d'),('a','c','b','d'),('b','a','c','d'),('b','c','a','d'),('c','a','b','d'),('c','b','a','d');

 

总结：联合索引的最左原则不一定按照字段顺序来匹配，个人理解最左原则的最左是按照从左到右组合的以下三种组合（col1）、(col1,col2)、(col1、col2、col3)，组合内部任意顺序，