我要评论结论: 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会在磁盘中查找)。
小结
到此这篇关于浅谈mysql中使用in会走索引吗的文章就介绍到这了,更多相关mysql in索引内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论