现象
新建了一张员工表,插入了少量数据,索引中所有的字段均在where条件出现时,正确走到了idx_nap索引,但是where出现部分自左开始的索引时,却进行全表扫描,与MySQL官方所说的最左匹配原则“相悖”。
数据背景
CREATE TABLE `staffs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
`pos` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
`add_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (`id`),
KEY `idx_nap` (`name`,`age`,`pos`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
表中数据如下:
id name age pos add_time
1 July 23 dev 2018-06-04 16:02:02
2 Clive 22 dev 2018-06-04 16:02:32
3 Cleva 24 test 2018-06-04 16:02:38
4 July 23 test 2018-06-04 16:12:22
5 July 23 pre 2018-06-04 16:12:37
6 Clive 22 pre 2018-06-04 16:12:48
7 July 25 dev 2018-06-04 16:30:17
Explain语句看下执行计划
-- 全匹配走了索引
explain select * from staffs where name = 'July' and age = 23 and pos = 'dev';
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE staffs NULL ref idx_nap idx_nap 140 const,const,const 1 100.00 NULL
开启优化器跟踪优化过程
-- 左侧部分匹配却没有走索引,全表扫描
explain select * from staffs where name = 'July' and age = 23;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE staffs2 NULL ALL idx_nap NULL NULL NULL 6 50.00 Using where
-- 开启优化器跟踪
set session optimizer_trace='enabled=on';
-- 在执行完查询语句后,在执行以下的select语句可以查看具体的优化器执行过程
select * from information_schema.optimizer_trace;
Trace部分的内容
{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `staffs`.`id` AS `id`,`staffs`.`name` AS `name`,`staffs`.`age` AS `age`,`staffs`.`pos` AS `pos`,`staffs`.`add_time` AS `add_time` from `staffs` where ((`staffs`.`name` = 'July') and (`staffs`.`age` = 23))"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`staffs`.`name` = 'July') and (`staffs`.`age` = 23))",
"steps": [
{