现象
新建了一张员工表，插入了少量数据，索引中所有的字段均在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`.`age`))" }, { "transformation": "constant_propagation", "resulting_condition": "((`staffs`.`name` = 'July') and multiple equal(23, `staffs`.`age`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`staffs`.`name` = 'July') and multiple equal(23, `staffs`.`age`))" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`staffs`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ { "table": "`staffs`", "field": "name", "equals": "'July'", "null_rejecting": false }, { "table": "`staffs`", "field": "age", "equals": "23", "null_rejecting": false } ] }, { "rows_estimation": [ { "table": "`staffs`", "range_analysis": { "table_scan": { "rows": 6, "cost": 4.3 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "idx_nap", "usable": true, "key_parts": [ "name", "age", "pos", "id" ] } ], "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "idx_nap", "ranges": [ "July <= name <= July AND 23 <= age <= 23" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 3, "cost": 4.61, "chosen": false, "cause": "cost" } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`staffs`", "best_access_path": { "considered_access_paths": [ { //可以看到这边MySQL计算得到使用索引的成本为2.6 "access_type": "ref", "index": "idx_nap", "rows": 3, "cost": 2.6, "chosen": true }, { //而全表扫描计算所得的成本为2.2 "rows_to_scan": 6, "access_type": "scan", "resulting_rows": 6, "cost": 2.2, "chosen": true } ] }, //因此选择了成本更低的scan "condition_filtering_pct": 100, "rows_for_plan": 6, "cost_for_plan": 2.2, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "((`staffs`.`age` = 23) and (`staffs`.`name` = 'July'))", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`staffs`", "attached": "((`staffs`.`age` = 23) and (`staffs`.`name` = 'July'))" } ] } }, { "refine_plan": [ { "table": "`staffs`" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ ] } } ] }
增加表数据量 -- 接下来增大表的数据量 INSERT INTO `staffs` (`name`, `age`, `pos`, `add_time`) VALUES ('July', 25, 'dev', '2018-06-04 16:30:17'), ('July', 23, 'dev1', '2018-06-04 16:02:02'), ('July', 23, 'dev2', '2018-06-04 16:02:02'), ('July', 23, 'dev3', '2018-06-04 16:02:02'), ('July', 23, 'dev4', '2018-06-04 16:02:02'), ('July', 23, 'dev6', '2018-06-04 16:02:02'), ('July', 23, 'dev5', '2018-06-04 16:02:02'), ('July', 23, 'dev7', '2018-06-04 16:02:02'), ('July', 23, 'dev8', '2018-06-04 16:02:02'), ('July', 23, 'dev9', '2018-06-04 16:02:02'), ('July', 23, 'dev10', '2018-06-04 16:02:02'), ('Clive', 23, 'dev1', '2018-06-04 16:02:02'), ('Clive', 23, 'dev2', '2018-06-04 16:02:02'), ('Clive', 23, 'dev3', '2018-06-04 16:02:02'), ('Clive', 23, 'dev4', '2018-06-04 16:02:02'), ('Clive', 23, 'dev6', '2018-06-04 16:02:02'), ('Clive', 23, 'dev5', '2018-06-04 16:02:02'), ('Clive', 23, 'dev7', '2018-06-04 16:02:02'), ('Clive', 23, 'dev8', '2018-06-04 16:02:02'), ('Clive', 23, 'dev9', '2018-06-04 16:02:02'), ('Clive', 23, 'dev10', '2018-06-04 16:02:02');
执行Explain -- 再次执行同样的查询语句，会发现走到索引上了 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 staffs NULL ref idx_nap idx_nap 78 const,const 13 100.00 NULL
查看新的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`.`age`))" }, { "transformation": "constant_propagation", "resulting_condition": "((`staffs`.`name` = 'July') and multiple equal(23, `staffs`.`age`))" }, { "transformation": "trivial_condition_removal", "resulting_condition": "((`staffs`.`name` = 'July') and multiple equal(23, `staffs`.`age`))" } ] } }, { "substitute_generated_columns": { } }, { "table_dependencies": [ { "table": "`staffs`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] } ] }, { "ref_optimizer_key_uses": [ { "table": "`staffs`", "field": "name", "equals": "'July'", "null_rejecting": false }, { "table": "`staffs`", "field": "age", "equals": "23", "null_rejecting": false } ] }, { "rows_estimation": [ { "table": "`staffs`", "range_analysis": { "table_scan": { "rows": 27, "cost": 8.5 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": false, "cause": "not_applicable" }, { "index": "idx_nap", "usable": true, "key_parts": [ "name", "age", "pos", "id" ] } ], "setup_range_conditions": [ ], "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" }, "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "idx_nap", "ranges": [ "July <= name <= July AND 23 <= age <= 23" ], "index_dives_for_eq_ranges": true, "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 13, "cost": 16.61, "chosen": false, "cause": "cost" } ], "analyzing_roworder_intersect": { "usable": false, "cause": "too_few_roworder_scans" } } } } ] }, { "considered_execution_plans": [ { "plan_prefix": [ ], "table": "`staffs`", "best_access_path": { "considered_access_paths": [ { //使用索引的成本变为了5.3 "access_type": "ref", "index": "idx_nap", "rows": 13, "cost": 5.3, "chosen": true }, { //scan的成本变为了6.4 "rows_to_scan": 27, "access_type": "scan", "resulting_rows": 27, "cost": 6.4, "chosen": false } ] }, //使用索引查询的成本更低，因此选择了走索引 "condition_filtering_pct": 100, "rows_for_plan": 13, "cost_for_plan": 5.3, "chosen": true } ] }, { "attaching_conditions_to_tables": { "original_condition": "((`staffs`.`age` = 23) and (`staffs`.`name` = 'July'))", "attached_conditions_computation": [ ], "attached_conditions_summary": [ { "table": "`staffs`", "attached": null } ] } }, { "refine_plan": [ { "table": "`staffs`" } ] } ] } }, { "join_execution": { "select#": 1, "steps": [ ] } } ] }
结论
MySQL表数据量的大小，会影响索引的选择，具体的情况还是通过Explain和Optimizer Trace来查看与分析。
