mysql数据量对索引的影响_表数据量影响MySQL索引选择

现象

新建了一张员工表，插入了少量数据，索引中所有的字段均在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": [

{

"transformation": "equality_propagation",

"resulting_condition": "((`staffs`.`name` = 'July') and multiple equal(23, `staffs`.`a