mysql联合索引的最左匹配原则实验及验证

• 表结构

/*
MySQL - 5.5.62 
*********************************************************************
*/

create table `student` (
	`id` int (10),
	`user_name` varchar (30),
	`age` int (10),
	`score` int (10),
	`item` varchar (30)
); 
insert into `student` (`id`, `user_name`, `age`, `score`, `item`) values('1','lx','18','100','数学');
insert into `student` (`id`, `user_name`, `age`, `score`, `item`) values('2','gj','19','90','英语');
insert into `student` (`id`, `user_name`, `age`, `score`, `item`) values('3','gj','19','80','数学');
insert into `student` (`id`, `user_name`, `age`, `score`, `item`) values('4','lx','18','90','英语');

• 创建索引：

/*
创建联合索引，user_name,age,score
*/
CREATE INDEX index_userName_age_score ON student(`user_name`,`age`,`score`);

• 查询表的索引：

/*
查询表的索引
*/
SHOW INDEX FROM student

分析语句是否能用到索引，使用explain语句来分析sql语句是否能用到索引。

能用到执行结果的key列为创建时命名的key，如下图

不能用到的结果，key为null，如下图:

• 执行以下语句即可直观的看到最左匹配的规则：

/*
根据索引条件查询顺序的最基本查询，可以用到索引
*/
EXPLAIN SELECT * FROM student WHERE user_name = "lx" AND age = 18 AND score = 100

/*
不符合最左匹配的情况，不能用到该联合索引。
*/

EXPLAIN SELECT * FROM student WHERE age = 18

EXPLAIN SELECT * FROM student WHERE score = 100

EXPLAIN SELECT * FROM student WHERE age = 18 AND score = 100

/*
符合最左匹配的情况，以下查询都可以用到索引。
*/

EXPLAIN SELECT * FROM student WHERE user_name = "lx" 

EXPLAIN SELECT * FROM student WHERE user_name = "lx" AND age = 18

EXPLAIN SELECT * FROM student WHERE user_name = "lx" AND score = 100

EXPLAIN SELECT * FROM student WHERE user_name = "lx" AND score = 100 AND age = 18

 

 

 

总结：

联合索引，只要最左边的条件存在，后面的索引条件不限制顺序，即就可以用到索引，不存在就用不到该联合索引。

索引很重要，当数据量大的时候，查询所消耗的时间相差很大，没有索引是有索引的几百倍耗时。根据开发情况，能加的必须加。