神奇的 SQL 之擦肩而过 → 真的用到索引了吗
作者：青石路
cnblogs.com/youzhibing/p/14175374.html
索引的數(shù)據(jù)結構
什么是數(shù)據(jù)庫索引 ，相信大家都能答上來，索引就是為了加速對表中數(shù)據(jù)行的檢索而創(chuàng)建的一種分散存儲的數(shù)據(jù)結構（索引是一種數(shù)據(jù)結構）
但具體是什么樣的數(shù)據(jù)結構，很多小伙伴可能就不知道了
索引的數(shù)據(jù)結構包括 哈希表、B樹、B+樹 等，而用的最多的就是?B+樹
我們以 MySQL 為例，來看看 B+樹 結構的索引到底是什么樣的
表：tbl_index
CREATE?TABLE?tbl_index?(c1?INT,c2?INT,c3?CHAR(1),PRIMARY?KEY(c1),KEY?idx_c2?(c2) );c1?上有聚簇索引，?c2?上有二級索引（即非聚簇索引）
InnoDB 的索引
InnoDB 下的聚簇索引 和 二級索引還是有區(qū)別的
MyISAM 的索引
MyISAM 聚簇索引和二級索引結構基本一致，只是聚簇索引有個唯一性約束
B+樹 就是如上圖中的那樣一個倒立的樹結構
B+樹 有很多特性，這里就不細講了，有興趣的可以去查閱相關資料
組合索引的列順序
單列索引的列順序好說，它就一列，不存在列先后順序的問題，按這個列的值進行順序排序，存儲到 B+樹 中就好，上面兩圖都是單列索引
但在實際應用中，更多的還是用到組合索引（在多列上建一個索引），既然有多列，那就存在列與列之間的順序問題了
那組合索引的的結構具體是什么樣的了？
我們有表：tbl_group_index ，在 c2 列和 c3 列上建一個組合索引 idx_c2_c3
CREATE?TABLE?tbl_group_index?(c1?INT,c2?INT,c3?CHAR(1),c4?INT,PRIMARY?KEY(c1),KEY?idx_c2_c3?(c2,c3) );那么，索引?idx_c2_c3?的結構如下
先按?c1?列排序，若 c1 列相等了再按 c2 列排序
抽象化就是，按組合索引指定的列，從左往右逐個排序；整體上先按第一列排序，第一列相等的數(shù)據(jù)整體按第二列排序，第一列相等且第二列相等的數(shù)據(jù)整體按第三列排序，以此類推
索引的擦肩而過
有的小伙伴可能急了：“樓主，前戲太多了，我要看主角！！！”
樓主：“你怕是個杠精吧，前戲不寫長點，怎么湊夠篇幅？你去看看現(xiàn)在的動漫，哪個不是正戲不夠前戲來扣？（更可惡的是還有一大截尾戲拼湊）”
好了，不多扯了（再扯樓主怕是有生命危險了），我們一起來看看今天的主角們！
環(huán)境準備
MySQL 版本：5.7.30-log ，存儲引擎：InnoDB
準備表：??tbl_customer_recharge_record??，并初始化 7 條數(shù)據(jù)
DROP?TABLE?IF?EXISTS?tbl_customer_recharge_record; CREATE?TABLE?tbl_customer_recharge_record?(id?INT(11)?UNSIGNED?NOT?NULL?AUTO_INCREMENT?COMMENT?'自增主鍵',customer_id?INT(11)?NOT?NULL?COMMENT?'顧客ID',customer_name?VARCHAR(50)?NOT?NULL?COMMENT?'顧客姓名',recharge_type?TINYINT(2)?NOT?NULL?COMMENT?'充值方式?1:支付寶,?2:微信,3:QQ,4:京東,5:銀聯(lián),6:信用卡,7:其他',recharge_amount?DECIMAL(15,2)?NOT?NULL?COMMENT?'充值金額,?單位元',recharge_time?DATETIME?NOT?NULL?COMMENT?'充值時間',remark?VARCHAR(500)?NOT?NULL?DEFAULT?'remark'?COMMENT?'備注',PRIMARY?KEY?(id),KEY?idx_c_id(customer_id),KEY?idx_name_type_time(customer_name,recharge_type,recharge_time) )?ENGINE=InnoDB?DEFAULT?CHARSET=utf8?COMMENT='顧客充值記錄表';INSERT?INTO?tbl_customer_recharge_record(customer_id,?customer_name,recharge_type,recharge_amount,recharge_time)?VALUES (1,'李小龍',1,10000,NOW()), (2,'李連杰',2,20000,NOW()), (1,'李小龍',2,10000,NOW()), (1,'李小龍',3,10000,NOW()), (2,'李連杰',7,20000,NOW()), (3,'成龍',3,15000,NOW()), (1,'李小龍',1,10000,NOW());一共有 3 個索引：
1、id?列上的聚簇索引
2、customer_id?列上的二級索引：?idx_c_id
3、以及?customer_name,recharge_type,recharge_time?列上的組合索引：?idx_name_type_time
后面我們會用?EXPLAIN?來查看執(zhí)行計劃，查看索引使用情況，對它還不熟的小伙伴，趕緊點進去先看看
全表掃描更優(yōu)
這是什么意思了，就是說優(yōu)化器在進行優(yōu)化的時候，會從眾多可選的執(zhí)行計劃中選擇它認為最優(yōu)的那一個
當優(yōu)化器計算得出通過全表查詢比通過索引查詢更優(yōu)時，它會選擇全表掃描的方式進行查詢
SQL：
explain?select?*?from?tbl_customer_recharge_record?where?customer_id?=?2;?相信大家對這個沒什么異議，通過?idx_c_id?來完成查詢，跟我們預想的一樣
對于?explain select * from tbl_customer_recharge_record where customer_id = 1;大家睜大眼睛看清楚了啊！
能用的索引包括：?idx_c_id?，但實際沒用它，而是走的全表查詢；因為優(yōu)化器認為走全表查詢成本更低，查詢更快
MySQL5.6 新引入的一項跟蹤功能：?OPTIMIZER_TRACE?，可以跟蹤優(yōu)化器做出的各種決策（比如訪問表的方法、各種開銷計算、各種轉換等）
并將跟蹤結果記錄到?INFORMATION_SCHEMA.OPTIMIZER_TRACE?中
跟蹤功能默認是關閉的，我們要用它的話，需要將其開啟：set optimizer_trace='enabled=on';
查看優(yōu)化器優(yōu)化步驟：?select?*?from?information_schema.OPTIMIZER_TRACE;
優(yōu)化器對?select * from tbl_customer_recharge_record where customer_id = 1；?優(yōu)化步驟如下
{"steps":[{"join_preparation":{"select#":1,"steps":[{"expanded_query":"/*?select#1?*/?select?`tbl_customer_recharge_record`.`id`?AS?`id`,`tbl_customer_recharge_record`.`customer_id`?AS?`customer_id`,`tbl_customer_recharge_record`.`customer_name`?AS?`customer_name`,`tbl_customer_recharge_record`.`recharge_type`?AS?`recharge_type`,`tbl_customer_recharge_record`.`recharge_amount`?AS?`recharge_amount`,`tbl_customer_recharge_record`.`recharge_time`?AS?`recharge_time`,`tbl_customer_recharge_record`.`remark`?AS?`remark`?from?`tbl_customer_recharge_record`?where?(`tbl_customer_recharge_record`.`customer_id`?=?1)"}]}},{"join_optimization":{"select#":1,"steps":[{"condition_processing":{"condition":"WHERE","original_condition":"(`tbl_customer_recharge_record`.`customer_id`?=?1)","steps":[{"transformation":"equality_propagation","resulting_condition":"multiple?equal(1,?`tbl_customer_recharge_record`.`customer_id`)"},{"transformation":"constant_propagation","resulting_condition":"multiple?equal(1,?`tbl_customer_recharge_record`.`customer_id`)"},{"transformation":"trivial_condition_removal","resulting_condition":"multiple?equal(1,?`tbl_customer_recharge_record`.`customer_id`)"}]}},{"substitute_generated_columns":{}},{"table_dependencies":[{"table":"`tbl_customer_recharge_record`","row_may_be_null":false,"map_bit":0,"depends_on_map_bits":[]}]},{"ref_optimizer_key_uses":[{"table":"`tbl_customer_recharge_record`","field":"customer_id","equals":"1","null_rejecting":false}]},{"rows_estimation":[{"table":"`tbl_customer_recharge_record`","range_analysis":{"table_scan":{"rows":7,"cost":4.5},"potential_range_indexes":[{"index":"PRIMARY","usable":false,"cause":"not_applicable"},{"index":"idx_c_id","usable":true,"key_parts":["customer_id","id"]},{"index":"idx_name_type_time","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":"idx_c_id","ranges":["1?<=?customer_id?<=?1"],"index_dives_for_eq_ranges":true,"rowid_ordered":true,"using_mrr":false,"index_only":false,"rows":4,"cost":5.81,"chosen":false,"cause":"cost"}],"analyzing_roworder_intersect":{"usable":false,"cause":"too_few_roworder_scans"}}}}]},{"considered_execution_plans":[{"plan_prefix":[],"table":"`tbl_customer_recharge_record`","best_access_path":{"considered_access_paths":[{"access_type":"ref","index":"idx_c_id","rows":4,"cost":2.8,"chosen":true},{"rows_to_scan":7,"access_type":"scan","resulting_rows":7,"cost":2.4,"chosen":true}]},"condition_filtering_pct":100,"rows_for_plan":7,"cost_for_plan":2.4,"chosen":true}]},{"attaching_conditions_to_tables":{"original_condition":"(`tbl_customer_recharge_record`.`customer_id`?=?1)","attached_conditions_computation":[],"attached_conditions_summary":[{"table":"`tbl_customer_recharge_record`","attached":"(`tbl_customer_recharge_record`.`customer_id`?=?1)"}]}},{"refine_plan":[{"table":"`tbl_customer_recharge_record`"}]}]}},{"join_execution":{"select#":1,"steps":[]}}] }內(nèi)容有點多，我們只關注
{"considered_execution_plans":[{"plan_prefix":[],"table":"`tbl_customer_recharge_record`","best_access_path":{"considered_access_paths":[{//?走索引?idx_c_id，花費成本?2.8"access_type":"ref","index":"idx_c_id","rows":4,"cost":2.8,"chosen":true},{//?走全表，花費成本?2.4"rows_to_scan":7,"access_type":"scan","resulting_rows":7,"cost":2.4,"chosen":true}]},//?對比下來，最終選擇花費成本更低的全表掃描"condition_filtering_pct":100,"rows_for_plan":7,"cost_for_plan":2.4,"chosen":true}] }相比于使用索引，全表掃描效率更高，那為什么還選擇索引呢？
LIKE 進行后方一致或中間一致的匹配
說的更通俗一點，就是以 % 開頭進行匹配
如果 LIKE 進行前方一致匹配，索引還是會生效的
SQL：
?explain?select?*?from?tbl_customer_recharge_record?where?customer_name?like?'成%';?如果以 % 開頭進行匹配，則不會用到索引
SQL：
explain?select?*?from?tbl_customer_recharge_record?where?customer_name?like?'%杰';?OR?前后未同時使用索引
數(shù)據(jù)量太少，優(yōu)化器會選擇全表掃描，而不走索引了，我們再加點數(shù)據(jù)
INSERT?INTO?tbl_customer_recharge_record(customer_id,?customer_name,recharge_type,recharge_amount,recharge_time)?VALUES (1,'李小龍',1,10000,NOW()), (2,'李連杰',2,20000,NOW()), (3,'成龍',3,15000,NOW()), (4,'吳京',5,500,NOW()), (5,'吳越',4,200,NOW()), (6,'張晉',6,100,NOW()), (7,'梁小龍',7,2000,NOW()), (8,'釋小龍',1,3000,NOW()), (9,'甄子丹',2,4000,NOW()), (10,'元彪',3,5000,NOW()), (11,'錢嘉樂',4,5000,NOW()), (12,'錢小豪',5,5000,NOW()), (13,'洪金寶',6,5000,NOW()), (14,'劉家良',6,5000,NOW()), (15,'劉家輝',5,5000,NOW()), (16,'鄒兆龍',4,5000,NOW()), (17,'林國斌',3,5000,NOW()), (18,'趙文卓',2,5000,NOW()), (19,'于榮光',1,5000,NOW()), (20,'楊紫瓊',1,5000,NOW()), (1,'李小龍',1,5000,NOW()), (2,'李連杰',2,5000,NOW()), (3,'成龍',2,5000,NOW()), (13,'洪金寶',2,5000,NOW()), (9,'甄子丹',1,5000,NOW()), (20,'楊紫瓊',1,5000,NOW()), (18,'趙文卓',1,5000,NOW()), (11,'錢嘉樂',1,5000,NOW()), (16,'鄒兆龍',1,5000,NOW()), (19,'于榮光',1,5000,NOW());OR 前后都能用到索引的話，還是會走索引查詢的
只要 OR 前后有一個走不了索引，那就會全表掃描了
組合索引，未遵循最左匹配原則
最左匹配指的是，按組合索引指定的列順序，從左往右逐個列匹配，像這樣
不能直接跨過前面的列，否則就不能用到索引了
強烈建議：組合索引中的第一列必須寫在查詢條件的開頭，而且索引中列的順序不能顛倒
雖說有些數(shù)據(jù)庫（例如 MySQL）里順序顛倒后也能使用索引（優(yōu)化器會優(yōu)化列順序來適配索引），但是性能還是比順序正確時差一些
至于為什么要遵從最左匹配原則，大家可以結合前面講過的組合索引的數(shù)據(jù)結構來分析（還覺得我前戲太多嗎，啊！）
使用否定形式
否定形式包括：<>, !=, NOT IN,NOT EXIST，會導致全表掃描
索引列上進行運算
說的更準確點，是在查詢條件的左側進行運算，這種情況就不能用索引了
在查詢條件的右側進行計算，還是能用到索引的
索引列上使用函數(shù)
說的更準確點，是在查詢條件的左側使用函數(shù)，這種情況就不能用索引了
在右側使用函數(shù)，還是能用到索引的
強烈建議：使用索引時，條件表達式的左側應該是原始列
進行默認的類型轉換
新建表：tbl_char ，并初始化 7 條數(shù)據(jù)
DROP?TABLE?IF?EXISTS?tbl_char; CREATE?TABLE?tbl_char?(id?INT(11)?UNSIGNED?NOT?NULL?AUTO_INCREMENT?COMMENT?'自增主鍵',type?CHAR(1)?NOT?NULL,name?VARCHAR(50),PRIMARY?KEY?(id),KEY?idx_type(type),KEY?idx_name(name) )?ENGINE=InnoDB?DEFAULT?CHARSET=utf8;INSERT?INTO?tbl_char(type,?name)?VALUES ('1',null), ('2','1'), ('3','2'), ('4',null), ('5','5'), ('6','6'), ('7','7');type?的類型是?char
SQL：explain select * from tbl_char where type = 2;
默認的類型轉換不僅會增加額外的性能開銷，還會導致索引不可用，可以說是有百害而無一利
（對于 int 類型的列，傳字符串類型的值是可以走索引的，MySQL 內(nèi)部自動做了隱式類型轉換；相反，對于 char 或 varchar 類型的列，傳入 int 值是無法走索引的）
強烈建議：使用索引時，條件表達式的右側常數(shù)的類型應該與列類型保持一致
IS NULL 與 IS NOT NULL
我做個簡單的測試，就不下結論了
SQL：explain select * from tbl_char where name is not null;
SQL：explain select * from tbl_char where name is null;
強烈建議：所有列都指定 NOT NULL 和默認值
NULL 的陷阱太多，詳情可查看：神奇的 SQL 之溫柔的陷阱 → 三值邏輯 與 NULL ！
不走索引的情況，文中只列舉了常見的部分，還有其他的場景未列舉，歡迎小伙伴們補充
總結
「1、索引數(shù)據(jù)結構」
索引的數(shù)據(jù)結構包括 哈希表、B樹、B+樹 等，而用的最多的就是 B+數(shù)
「2、未走索引的常見場景」
全表掃描優(yōu)于索引掃描
LIKE 進行后方一致或中間一致的匹配
OR 前后未同時使用索引
組合索引，未遵循最左匹配原則
進行默認的類型轉換
使用否定形式
索引列上進行運算
索引列上使用函數(shù)
「3、推薦做法」
使用組合索引時，組合索引中的第一列必須寫在查詢條件的開頭，而且索引中列的順序不能顛倒
使用索引時，條件表達式的左側應該是原始列，右側是常數(shù)且類型與左側列一致，左右側都不參與計算、使用函數(shù)（計算、函數(shù)運算、邏輯處理都交由專門的開發(fā)語言去實現(xiàn)）
所有列都指定 NOT NULL 和默認值，避免 NULL 的陷阱
參考
《SQL進階教程》
神奇的 SQL 之 ICP → 索引條件下推
神奇的 SQL 之溫柔的陷阱 → 三值邏輯 與 NULL ！
推薦文章今天給大家推薦 6 個 Spring Boot 項目，拿來就可以賺錢！
分享一套基于SpringBoot和Vue的企業(yè)級中后臺開源項目，這個項目有點哇塞！
圈子哥推薦一款基于 Spring Boot 開發(fā) OA 開源產(chǎn)品，學習/搞外快都是不二選擇！
硬剛一周，3W字總結，一年的經(jīng)驗告訴你如何準備校招！
總結
以上是生活随笔為你收集整理的神奇的 SQL 之擦肩而过 → 真的用到索引了吗的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 新同事上来就把项目性能优化了一遍，瑟瑟发
- 下一篇： 21 岁理工男开源的这个编辑器火了！