淺談MySQL中使用IN會走索引嗎
更新時間：2025年02月21日 10:49:32 作者：cxSmiles
本文主要介紹了淺談MySQL中使用IN會走索引嗎,通過三個案例分析了MySQL 5.7.34版本中IN操作符的執(zhí)行情況,感興趣的可以了解一下
結(jié)論: MySQL優(yōu)化器在發(fā)現(xiàn)執(zhí)行全表掃描效率 > 索引的效率時,會選擇全表掃描。
- 至于IN的數(shù)據(jù)量占全表的20%或30%以內(nèi)會走索引，沒有明確的答案。
- 根據(jù)優(yōu)化器分析來選擇查詢成本更低的執(zhí)行方式。
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 '結(jié)束時間', `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)建時間', `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';
-- 測試數(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);
從上面三個案例可以看出案例一、案例二走了索引，案例三沒有走索引。why?
MySQL TRACE解析
-- step1:查詢mysql optimizer_trace是否開啟,on為開啟 show variables like 'optimizer_trace'; -- step2:若未開啟,設(shè)置為開啟 set optimizer_trace = 'enabled=on'; -- step3:需要注意查詢sql和TRACE一起查詢, 如果單獨查詢完再查詢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": [ -- 預估表的訪問成本
{
"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": { -- 分析各個索引使用成本
"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
當索引使用成本 > 全表掃描的成本時就會選擇全表掃描，全表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ù)某個字段查詢(有二級索引)，存在多行數(shù)據(jù)。range：范圍查詢。index：查詢索引樹(覆蓋索引的場景)。all：查詢所有數(shù)據(jù)(與index的區(qū)別在于index只遍歷索引樹，all會在磁盤中查找)。
小結(jié)
到此這篇關(guān)于淺談MySQL中使用IN會走索引嗎的文章就介紹到這了,更多相關(guān)MySQL IN索引內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要為大家介紹了mysql主從同步原理及應用場景示例詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪2022-08-08
MYSQL無法啟動提示： Default storage engine (InnoDB) is not availabl
自己用的MYSQL都是用MYISAM數(shù)據(jù)庫，還沒涉及到需要INNODB，因此打算直接不加載INNODB引擎。2011-05-05
這篇文章主要給大家介紹了關(guān)于MySQL中CASE?WHEN語句用法、示例與解析的相關(guān)資料,case when語句用于計算條件列表并返回多個可能結(jié)果表達式之一,文中通過代碼介紹的非常詳細,需要的朋友可以參考下2024-05-05
MySQL是中小型網(wǎng)站普遍使用的數(shù)據(jù)庫之一，然而，很多人并不清楚MySQL到底能支持多大的數(shù)據(jù)量，再加上某些國內(nèi)CMS廠商把數(shù)據(jù)承載量的責任推給它，導致很多不了解MySQL的站長對它產(chǎn)生了很多誤解2008-12-12
mysql正確刪除數(shù)據(jù)的方法(drop,delete,truncate)
這篇文章主要給大家介紹了關(guān)于mysql正確刪除數(shù)據(jù)的相關(guān)資料,DELETE語句是MySQL中最常用的刪除數(shù)據(jù)的方式之一,但也有幾種其他方法來實現(xiàn),需要的朋友可以參考下2023-10-10
SQL Server索引設(shè)計基礎(chǔ)知識詳解使用
為了使索引的使用效率更高，在創(chuàng)建索引時，必須考慮在哪些字段上創(chuàng)建索引和創(chuàng)建什么類型的索引。索引設(shè)計不合理或者缺少索引都會對數(shù)據(jù)庫和應用程序的性能造成障礙。高效的索引對于獲得良好的性能非常重要。設(shè)計索引時，應該考慮相應準則2023-04-04
MySQL使用Partition功能實現(xiàn)水平分區(qū)的策略
這篇文章主要介紹了MySQL使用Partition功能實現(xiàn)水平分區(qū),給大家提到了水平分區(qū)的5種策略，通過sql語句給大家介紹的非常詳細，需要的朋友可以參考下2021-12-12
在SQL中對同一個字段不同值,進行數(shù)據(jù)統(tǒng)計操作
這篇文章主要介紹了在SQL中對同一個字段不同值,進行數(shù)據(jù)統(tǒng)計操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-10-10
mysql 8.0.15 安裝配置方法圖文教程（Windows10 X64）
這篇文章主要為大家詳細介紹了Windows10 X64 mysql 8.0.15 安裝配置方法圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2019-03-03
MySQL8.0 GA版本發(fā)布了，展現(xiàn)了眾多新特性，下面這篇文章主要給大家介紹了關(guān)于MySQL8新特性：自增主鍵的持久化的相關(guān)資料，文中通過示例代碼介紹的非常詳細，需要的朋友可以參考借鑒，下面隨著小編來一起學習學習吧2018-07-07