MySQL IN会不会走索引?
结论: MySQL优化器在发现执行全表扫描效率 > 索引的效率时,会选择全表扫描。
- 至于IN的数据量占全表的20%或30%以内会走索引,没有明确的答案。
- 根据优化器分析来选择查询成本更低的执行方式。
MySQL IN流程验证
mysql版本为5.7.34
CREATE TABLE `_default` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`default_name` varchar(100) NOT NULL COMMENT '默认名称',
`default_code` varchar(50) NOT NULL COMMENT '默认编码',
`default_type` tinyint(3) unsigned NOT NULL COMMENT '默认类型',
`start_time` datetime NOT NULL COMMENT '开始时间',
`end_time` datetime NOT NULL COMMENT '结束时间',
`status` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '状态(1:未发布, 2:已发布, 3:已生效, 4:已失效, 5:已作废)',
`deleted` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '是否删除 0:否 1:是',
`create_by` varchar(50) NOT NULL COMMENT '创建人',
`create_time` datetime NOT NULL COMMENT '创建时间',
`update_by` varchar(50) DEFAULT NULL COMMENT '更新人',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_default_code` (`default_code`) USING BTREE,
KEY `idx_status` (`status`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='_default';
-- 测试数据
INSERT INTO `_default` VALUES (1, 'test2024-07-29 13:56:03', 'DEFAULT23121410204', 1, '2023-08-11 14:35:41', '2023-08-11 14:35:41', 2, 0, '1', '2023-12-14 16:25:39', NULL, NULL);
INSERT INTO `_default` VALUES (2, 'demoData', 'DEFAULT23121410205', 1, '2023-08-11 14:35:41', '2023-08-11 14:35:41', 2, 0, '1', '2023-12-14 16:25:40', NULL, NULL);
INSERT INTO `_default` VALUES (3, 'demoData', 'DEFAULT23121410206', 1, '2023-08-11 14:35:41', '2023-08-11 14:35:41', 2, 0, '2', '2023-12-14 16:25:41', NULL, NULL);
INSERT INTO `_default` VALUES (4, 'demoData', 'DEFAULT23121410207', 1, '2023-08-11 14:35:41', '2023-08-11 14:35:41', 2, 0, '1', '2023-12-14 16:25:42', NULL, NULL);
INSERT INTO `_default` VALUES (5, 'demoData', 'DEFAULT23121410208', 1, '2023-08-11 14:35:41', '2023-08-11 14:35:41', 2, 0, '1', '2023-12-14 16:25:43', NULL, NULL);
INSERT INTO `_default` VALUES (6, 'demoData', 'DEFAULT23121410209', 0, '2023-08-11 14:35:41', '2023-08-11 14:35:41', 2, 0, '1', '2023-12-14 16:27:09', NULL, NULL);
INSERT INTO `_default` VALUES (7, 'demoData', 'DEFAULT23121410210', 0, '2023-08-11 14:35:41', '2023-08-11 14:35:41', 2, 0, '1', '2023-12-14 16:27:10', NULL, NULL);
INSERT INTO `_default` VALUES (8, 'demoData', 'DEFAULT23121410211', 0, '2023-08-11 14:35:41', '2023-08-11 14:35:41', 2, 0, '1', '2023-12-14 16:27:11', NULL, NULL);
INSERT INTO `_default` VALUES (9, 'demoData', 'DEFAULT23121410212', 0, '2023-08-11 14:35:41', '2023-08-11 14:35:41', 1, 0, '1', '2023-12-14 16:27:12', NULL, NULL);
INSERT INTO `_default` VALUES (10, 'demoData', 'DEFAULT23121410213', 0, '2023-08-11 14:35:41', '2023-08-11 14:35:41', 1, 1, '1', '2023-12-14 16:27:13', NULL, NULL);
案例一
:
explain select * from _default where id in (1);
案例二
:
explain select * from _default where id in (1,2,3);
案例三
:
explain select * from _default where id in (1,2,3,4,5,6,7);
从上面三个案例可以看出案例一、案例二走了索引,案例三没有走索引。why?
MySQL TRACE解析
-- step1:查询mysql optimizer_trace是否开启,on为开启
show variables like 'optimizer_trace';
-- step2:若未开启,设置为开启
set optimizer_trace = 'enabled=on';
-- step3:需要注意查询sql和TRACE一起查询, 如果单独查询完再查询TRAC,查询结果为空
select * from _default where id in (1,2,3,4,5,6,7);
select TRACE from `information_schema`.`OPTIMIZER_TRACE`
案例一
{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `_default`.`id` AS `id`,`_default`.`default_name` AS `default_name`,`_default`.`default_code` AS `default_code`,`_default`.`default_type` AS `default_type`,`_default`.`start_time` AS `start_time`,`_default`.`end_time` AS `end_time`,`_default`.`status` AS `status`,`_default`.`deleted` AS `deleted`,`_default`.`create_by` AS `create_by`,`_default`.`create_time` AS `create_time`,`_default`.`update_by` AS `update_by`,`_default`.`update_time` AS `update_time` from `_default` where (`_default`.`id` = 1)"
}
]
}
},
{
"join_optimization": { -- sql优化阶段
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`_default`.`id` = 1)",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "multiple equal(1, `_default`.`id`)"
},
{
"transformation": "constant_propagation",
"resulting_condition": "multiple equal(1, `_default`.`id`)"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "multiple equal(1, `_default`.`id`)"
}
]
}
},
{
"substitute_generated_columns": {}
},
{
"table_dependencies": [
{
"table": "`_default`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": []
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`_default`",
"field": "id",
"equals": "1",
"null_rejecting": false
}
]
},
{
"rows_estimation": [
{
"table": "`_default`",
"rows": 1,
"cost": 1,
"table_type": "const",
"empty": false
}
]
},
{
"condition_on_constant_tables": "1",
"condition_value": true
},
{
"attaching_conditions_to_tables": {
"original_condition": "1",
"attached_conditions_computation": [],
"attached_conditions_summary": []
}
},
{
"refine_plan": []
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": []
}
}
]
}
案例二
{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"IN_uses_bisection": true
},
{
"expanded_query": "/* select#1 */ select `_default`.`id` AS `id`,`_default`.`default_name` AS `default_name`,`_default`.`default_code` AS `default_code`,`_default`.`default_type` AS `default_type`,`_default`.`start_time` AS `start_time`,`_default`.`end_time` AS `end_time`,`_default`.`status` AS `status`,`_default`.`deleted` AS `deleted`,`_default`.`create_by` AS `create_by`,`_default`.`create_time` AS `create_time`,`_default`.`update_by` AS `update_by`,`_default`.`update_time` AS `update_time` from `_default` where (`_default`.`id` in (1,2,3))"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`_default`.`id` in (1,2,3))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`_default`.`id` in (1,2,3))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`_default`.`id` in (1,2,3))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`_default`.`id` in (1,2,3))"
}
]
}
},
{
"substitute_generated_columns": {}
},
{
"table_dependencies": [
{
"table": "`_default`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": []
}
]
},
{
"ref_optimizer_key_uses": []
},
{
"rows_estimation": [
{
"table": "`_default`",
"range_analysis": {
"table_scan": {
"rows": 26,
"cost": 8.3
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": true,
"key_parts": [
"id"
]
},
{
"index": "uk_default_code",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_status",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_default_name",
"usable": false,
"cause": "not_applicable"
}
],
"setup_range_conditions": [],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "PRIMARY",
"ranges": [
"1 <= id <= 1",
"2 <= id <= 2",
"3 <= id <= 3"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 3,
"cost": 3.6153,
"chosen": true
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "PRIMARY",
"rows": 3,
"ranges": [
"1 <= id <= 1",
"2 <= id <= 2",
"3 <= id <= 3"
]
},
"rows_for_plan": 3,
"cost_for_plan": 3.6153,
"chosen": true
}
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [],
"table": "`_default`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 3,
"access_type": "range",
"range_details": {
"used_index": "PRIMARY"
},
"resulting_rows": 3,
"cost": 4.2153,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 3,
"cost_for_plan": 4.2153,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`_default`.`id` in (1,2,3))",
"attached_conditions_computation": [],
"attached_conditions_summary": [
{
"table": "`_default`",
"attached": "(`_default`.`id` in (1,2,3))"
}
]
}
},
{
"refine_plan": [
{
"table": "`_default`"
}
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": []
}
}
]
}
案例三
{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"IN_uses_bisection": true
},
{
"expanded_query": "/* select#1 */ select `_default`.`id` AS `id`,`_default`.`default_name` AS `default_name`,`_default`.`default_code` AS `default_code`,`_default`.`default_type` AS `default_type`,`_default`.`start_time` AS `start_time`,`_default`.`end_time` AS `end_time`,`_default`.`status` AS `status`,`_default`.`deleted` AS `deleted`,`_default`.`create_by` AS `create_by`,`_default`.`create_time` AS `create_time`,`_default`.`update_by` AS `update_by`,`_default`.`update_time` AS `update_time` from `_default` where (`_default`.`id` in (1,2,3,4,5,6,7))"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`_default`.`id` in (1,2,3,4,5,6,7))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`_default`.`id` in (1,2,3,4,5,6,7))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`_default`.`id` in (1,2,3,4,5,6,7))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`_default`.`id` in (1,2,3,4,5,6,7))"
}
]
}
},
{
"substitute_generated_columns": {}
},
{
"table_dependencies": [
{
"table": "`_default`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": []
}
]
},
{
"ref_optimizer_key_uses": []
},
{
"rows_estimation": [ -- 预估表的访问成本
{
"table": "`_default`",
"range_analysis": {
"table_scan": { -- 全表扫描的分析
"rows": 26, -- 扫描行数
"cost": 8.3 -- 查询成本
},
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": true,
"key_parts": [
"id"
]
},
{
"index": "uk_default_code",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_status",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_default_name",
"usable": false,
"cause": "not_applicable"
}
],
"setup_range_conditions": [],
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
},
"analyzing_range_alternatives": { -- 分析各个索引使用成本
"range_scan_alternatives": [
{
"index": "PRIMARY",
"ranges": [ -- 索引使用范围
"1 <= id <= 1",
"2 <= id <= 2",
"3 <= id <= 3",
"4 <= id <= 4",
"5 <= id <= 5",
"6 <= id <= 6",
"7 <= id <= 7"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 7, -- 扫描行数
"cost": 8.4224, -- 索引使用成本
"chosen": false, -- 是否使用索引
"cause": "cost"
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
}
}
}
]
},
{
"considered_execution_plans": [
{
"plan_prefix": [],
"table": "`_default`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 26,
"access_type": "scan",
"resulting_rows": 26,
"cost": 6.2,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 26,
"cost_for_plan": 6.2,
"chosen": true
}
]
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`_default`.`id` in (1,2,3,4,5,6,7))",
"attached_conditions_computation": [],
"attached_conditions_summary": [
{
"table": "`_default`",
"attached": "(`_default`.`id` in (1,2,3,4,5,6,7))"
}
]
}
},
{
"refine_plan": [
{
"table": "`_default`"
}
]
}
]
}
},
{
"join_execution": {
"select#": 1,
"steps": []
}
}
]
}
join_optimization.rows_estimation.range_analysis.table_scan
和 join_optimization.rows_estimation.range_analysis.analyzing_range_alternatives
当索引使用成本 > 全表扫描的成本时就会选择全表扫描,全表rows为26,索引rows为7,为什么不用索引?
- 如果是查所有数据,存在回表的情况,IN的越多回表成本越高
- 如果是查询条件和返回字段相同并且存在索引的情况(覆盖索引),这种情况可能优化器是可能选择索引
system > const> eq_ref > ref > range > index > all
system
:只有一行记录。const
:索引一次就找到了,主键和唯一索引。eq_ref
:唯一的索引,表与表之间关联,关联条件为主键或唯一索引。ref
:非唯一的索引,根据某个字段查询(有二级索引),存在多行数据。range
:范围查询。index
:查询索引树(覆盖索引的场景
)。all
:查询所有数据(与index的区别在于index只遍历索引树,all会在磁盘中查找
)。
小结
到这里就结束啦,祝大家生活愉快!