淺談MySQL中使用IN會(huì)走索引嗎
更新時(shí)間：2025年02月21日 10:49:32 作者：cxSmiles
本文主要介紹了淺談MySQL中使用IN會(huì)走索引嗎,通過三個(gè)案例分析了MySQL 5.7.34版本中IN操作符的執(zhí)行情況,感興趣的可以了解一下
結(jié)論: MySQL優(yōu)化器在發(fā)現(xiàn)執(zhí)行全表掃描效率 > 索引的效率時(shí),會(huì)選擇全表掃描。
- 至于IN的數(shù)據(jù)量占全表的20%或30%以內(nèi)會(huì)走索引，沒有明確的答案。
- 根據(jù)優(yōu)化器分析來選擇查詢成本更低的執(zhí)行方式。
MySQL IN流程驗(yàn)證
mysql版本為5.7.34
CREATE TABLE `_default` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵id', `default_name` varchar(100) NOT NULL COMMENT '默認(rèn)名稱', `default_code` varchar(50) NOT NULL COMMENT '默認(rèn)編碼', `default_type` tinyint(3) unsigned NOT NULL COMMENT '默認(rèn)類型', `start_time` datetime NOT NULL COMMENT '開始時(shí)間', `end_time` datetime NOT NULL COMMENT '結(jié)束時(shí)間', `status` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT '狀態(tài)(1:未發(fā)布, 2:已發(fā)布, 3:已生效, 4:已失效, 5:已作廢)', `deleted` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '是否刪除 0:否 1:是', `create_by` varchar(50) NOT NULL COMMENT '創(chuàng)建人', `create_time` datetime NOT NULL COMMENT '創(chuàng)建時(shí)間', `update_by` varchar(50) DEFAULT NULL COMMENT '更新人', `update_time` datetime DEFAULT NULL COMMENT '更新時(shí)間', 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';
-- 測(cè)試數(shù)據(jù) 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);
從上面三個(gè)案例可以看出案例一、案例二走了索引，案例三沒有走索引。why?
MySQL TRACE解析
-- step1:查詢mysql optimizer_trace是否開啟,on為開啟 show variables like 'optimizer_trace'; -- step2:若未開啟,設(shè)置為開啟 set optimizer_trace = 'enabled=on'; -- step3:需要注意查詢sql和TRACE一起查詢, 如果單獨(dú)查詢完再查詢TRAC,查詢結(jié)果為空 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優(yōu)化階段
"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": [ -- 預(yù)估表的訪問成本
{
"table": "`_default`",
"range_analysis": {
"table_scan": { -- 全表掃描的分析
"rows": 26, -- 掃描行數(shù)
"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": { -- 分析各個(gè)索引使用成本
"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, -- 掃描行數(shù)
"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
當(dāng)索引使用成本 > 全表掃描的成本時(shí)就會(huì)選擇全表掃描，全表rows為26,索引rows為7,為什么不用索引？
- 如果是查所有數(shù)據(jù)，存在回表的情況，IN的越多回表成本越高
- 如果是查詢條件和返回字段相同并且存在索引的情況(覆蓋索引)，這種情況可能優(yōu)化器是可能選擇索引
system > const> eq_ref > ref > range > index > all
system：只有一行記錄。const：索引一次就找到了,主鍵和唯一索引。eq_ref：唯一的索引，表與表之間關(guān)聯(lián)，關(guān)聯(lián)條件為主鍵或唯一索引。ref：非唯一的索引，根據(jù)某個(gè)字段查詢(有二級(jí)索引)，存在多行數(shù)據(jù)。range：范圍查詢。index：查詢索引樹(覆蓋索引的場(chǎng)景)。all：查詢所有數(shù)據(jù)(與index的區(qū)別在于index只遍歷索引樹，all會(huì)在磁盤中查找)。
小結(jié)
到此這篇關(guān)于淺談MySQL中使用IN會(huì)走索引嗎的文章就介紹到這了,更多相關(guān)MySQL IN索引內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
日常開發(fā)中,數(shù)據(jù)庫建表是必不可少的一個(gè)環(huán)節(jié),建表的時(shí)候通常會(huì)看到設(shè)定某個(gè)字段的長(zhǎng)度為varchar(50),那么你知道是什么意思嗎,感興趣的可以了解一下2024-01-01
本文帶大家一起來看一看MySQL中都有哪些常用的控制流函數(shù)，以及控制流函數(shù)的使用場(chǎng)景都有哪些，非常具有實(shí)用價(jià)值，需要的朋友可以參考下2023-05-05
MySql 視圖索引是一種基于視圖的索引,它允許在視圖上創(chuàng)建索引以提高查詢性能,本文主要介紹了MySql之視圖索引的具體使用,感興趣的可以了解一下2023-08-08
這篇文章主要介紹了Mysql獲取當(dāng)前日期的前幾天日期的方法,本文直接給出實(shí)現(xiàn)代碼,需要的朋友可以參考下2015-03-03
本文講述了在MySQL中識(shí)別和終止導(dǎo)致死鎖的SQL語句,通過SHOWENGINEINNODBSTATUS和INFORMATION_SCHEMA表,可以找到死鎖的具體事務(wù),感興趣的可以了解一下2024-11-11
MySQL實(shí)現(xiàn)replace函數(shù)的幾種實(shí)用場(chǎng)景
這篇文章主要介紹了MySQL實(shí)現(xiàn)replace函數(shù)的幾種實(shí)用場(chǎng)景，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-02-02
這篇文章主要介紹了mybatis中的三種批量插入方式對(duì)比,Mybatis是一款流行的Java持久化框架,它提供了三種不同的批量插入方式,分別為普通循環(huán)插入、BatchExecutor和JDBC批處理,普通循環(huán)插入方式適用于數(shù)據(jù)量較小的情況,但隨著數(shù)據(jù)量的增大會(huì)影響性能,需要的朋友可以參考下2023-10-10
Linux環(huán)境下設(shè)置MySQL表名忽略大小寫的方法小結(jié)
在MySQL中,表名的大小寫敏感性取決于操作系統(tǒng)和MySQL的配置,在Unix/Linux系統(tǒng)上,表名通常是區(qū)分大小寫的,由于之前MySQL未設(shè)置忽略表名大小寫導(dǎo)致數(shù)據(jù)查詢失敗等問題,所以本文給大家介紹了Linux環(huán)境下設(shè)置MySQL表名忽略大小寫的方法,需要的朋友可以參考下2024-06-06
這篇文章主要介紹了mysql更改引擎(InnoDB,MyISAM)的方法,實(shí)例講述了比較常見的幾種更改引擎的方法,非常具有實(shí)用價(jià)值,需要的朋友可以參考下2014-11-11
MySQL主從同步機(jī)制與同步延時(shí)問題追查過程
這篇文章主要給大家介紹了關(guān)于MySQL主從同步機(jī)制與同步延時(shí)問題追查的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-02-02