现象
新建了一张员工表，插入了少量数据，索引中所有的字段均在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来查看与分析。
继续阅读与本文标签相同的文章
下一篇 :
人工智能引擎可以识别虚假社交网络
-
中国移动向携号转网妥协，必须满足这一条件，网友：套路太深！
2026-05-18栏目： 教程
-
老板让我对比word文档差异，我用了2小时，同事1分钟就搞定了
2026-05-18栏目： 教程
-
光明卓越质量，引领中国乳企闪耀世界舞台
2026-05-18栏目： 教程
-
9GB一个估值10亿的区块链云存储项目
2026-05-18栏目： 教程
-
尘埃落定！美盟友明确表示不排除华为5G：特朗普颜面尽失！
2026-05-18栏目： 教程