神奇的 SQL 之擦肩而過 → 真的用到索引了嗎
點(diǎn)擊上方?好好學(xué)java?，選擇?星標(biāo)?公眾號
重磅資訊、干貨，第一時(shí)間送達(dá) 今日推薦：分享一套基于SpringBoot和Vue的企業(yè)級中后臺開源項(xiàng)目，這個(gè)項(xiàng)目有點(diǎn)哇塞！
個(gè)人原創(chuàng)100W+訪問量博客：點(diǎn)擊前往，查看更多
作者：青石路
cnblogs.com/youzhibing/p/14175374.html
索引的數(shù)據(jù)結(jié)構(gòu)
什么是數(shù)據(jù)庫索引 ，相信大家都能答上來，索引就是為了加速對表中數(shù)據(jù)行的檢索而創(chuàng)建的一種分散存儲的數(shù)據(jù)結(jié)構(gòu)（索引是一種數(shù)據(jù)結(jié)構(gòu)）
但具體是什么樣的數(shù)據(jù)結(jié)構(gòu)，很多小伙伴可能就不知道了
索引的數(shù)據(jù)結(jié)構(gòu)包括 哈希表、B樹、B+樹 等，而用的最多的就是?B+樹
我們以 MySQL 為例，來看看 B+樹 結(jié)構(gòu)的索引到底是什么樣的
表：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 聚簇索引和二級索引結(jié)構(gòu)基本一致，只是聚簇索引有個(gè)唯一性約束
B+樹 就是如上圖中的那樣一個(gè)倒立的樹結(jié)構(gòu)
B+樹 有很多特性，這里就不細(xì)講了，有興趣的可以去查閱相關(guān)資料
組合索引的列順序
單列索引的列順序好說，它就一列，不存在列先后順序的問題，按這個(gè)列的值進(jìn)行順序排序，存儲到 B+樹 中就好，上面兩圖都是單列索引
但在實(shí)際應(yīng)用中，更多的還是用到組合索引（在多列上建一個(gè)索引），既然有多列，那就存在列與列之間的順序問題了
那組合索引的的結(jié)構(gòu)具體是什么樣的了？
我們有表：tbl_group_index ，在 c2 列和 c3 列上建一個(gè)組合索引 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?的結(jié)構(gòu)如下
先按?c1?列排序，若 c1 列相等了再按 c2 列排序
抽象化就是，按組合索引指定的列，從左往右逐個(gè)排序；整體上先按第一列排序，第一列相等的數(shù)據(jù)整體按第二列排序，第一列相等且第二列相等的數(shù)據(jù)整體按第三列排序，以此類推
索引的擦肩而過
有的小伙伴可能急了：“樓主，前戲太多了，我要看主角?。?！”
樓主：“你怕是個(gè)杠精吧，前戲不寫長點(diǎn)，怎么湊夠篇幅？你去看看現(xiàn)在的動漫，哪個(gè)不是正戲不夠前戲來扣？（更可惡的是還有一大截尾戲拼湊）”
好了，不多扯了（再扯樓主怕是有生命危險(xiǎn)了），我們一起來看看今天的主角們！
環(huán)境準(zhǔn)備
MySQL 版本：5.7.30-log ，存儲引擎：InnoDB
準(zhǔn)備表：??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?'充值時(shí)間',
??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 個(gè)索引：
1、id?列上的聚簇索引
2、customer_id?列上的二級索引：?idx_c_id
3、以及?customer_name,recharge_type,recharge_time?列上的組合索引：?idx_name_type_time
后面我們會用?EXPLAIN?來查看執(zhí)行計(jì)劃，查看索引使用情況，對它還不熟的小伙伴，趕緊點(diǎn)進(jìn)去先看看
全表掃描更優(yōu)
這是什么意思了，就是說優(yōu)化器在進(jìn)行優(yōu)化的時(shí)候，會從眾多可選的執(zhí)行計(jì)劃中選擇它認(rèn)為最優(yōu)的那一個(gè)
當(dāng)優(yōu)化器計(jì)算得出通過全表查詢比通過索引查詢更優(yōu)時(shí)，它會選擇全表掃描的方式進(jìn)行查詢
SQL：
explain?select?*?from?tbl_customer_recharge_record?where?customer_id?=?2;?
相信大家對這個(gè)沒什么異議，通過?idx_c_id?來完成查詢，跟我們預(yù)想的一樣
對于?explain select * from tbl_customer_recharge_record where customer_id = 1;大家睜大眼睛看清楚了??！
能用的索引包括：?idx_c_id?，但實(shí)際沒用它，而是走的全表查詢；因?yàn)閮?yōu)化器認(rèn)為走全表查詢成本更低，查詢更快
MySQL5.6 新引入的一項(xiàng)跟蹤功能：?OPTIMIZER_TRACE?，可以跟蹤優(yōu)化器做出的各種決策（比如訪問表的方法、各種開銷計(jì)算、各種轉(zhuǎn)換等）
并將跟蹤結(jié)果記錄到?INFORMATION_SCHEMA.OPTIMIZER_TRACE?中
跟蹤功能默認(rèn)是關(guān)閉的，我們要用它的話，需要將其開啟：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)容有點(diǎn)多，我們只關(guān)注
{
????"considered_execution_plans":[
????????{
????????????"plan_prefix":[
????????????],
????????????"table":"`tbl_customer_recharge_record`",
????????????"best_access_path":{
????????????????"considered_access_paths":[
????????????????????{
????????????????????????//?走索引?idx_c_id，花費(fèi)成本?2.8
????????????????????????"access_type":"ref",
????????????????????????"index":"idx_c_id",
????????????????????????"rows":4,
????????????????????????"cost":2.8,
????????????????????????"chosen":true
????????????????????},
????????????????????{
????????????????????????//?走全表，花費(fèi)成本?2.4
????????????????????????"rows_to_scan":7,
????????????????????????"access_type":"scan",
????????????????????????"resulting_rows":7,
????????????????????????"cost":2.4,
????????????????????????"chosen":true
????????????????????}
????????????????]
????????????},
????????????//?對比下來，最終選擇花費(fèi)成本更低的全表掃描
????????????"condition_filtering_pct":100,
????????????"rows_for_plan":7,
????????????"cost_for_plan":2.4,
????????????"chosen":true
????????}
????]
}
相比于使用索引，全表掃描效率更高，那為什么還選擇索引呢？
LIKE 進(jìn)行后方一致或中間一致的匹配
說的更通俗一點(diǎn)，就是以 % 開頭進(jìn)行匹配
如果 LIKE 進(jìn)行前方一致匹配，索引還是會生效的
SQL：
?explain?select?*?from?tbl_customer_recharge_record?where?customer_name?like?'成%';?
如果以 % 開頭進(jìn)行匹配，則不會用到索引
SQL：
explain?select?*?from?tbl_customer_recharge_record?where?customer_name?like?'%杰';?
OR?前后未同時(shí)使用索引
數(shù)據(jù)量太少，優(yōu)化器會選擇全表掃描，而不走索引了，我們再加點(diǎn)數(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 前后有一個(gè)走不了索引，那就會全表掃描了
組合索引，未遵循最左匹配原則
最左匹配指的是，按組合索引指定的列順序，從左往右逐個(gè)列匹配，像這樣
不能直接跨過前面的列，否則就不能用到索引了
強(qiáng)烈建議：組合索引中的第一列必須寫在查詢條件的開頭，而且索引中列的順序不能顛倒
雖說有些數(shù)據(jù)庫（例如 MySQL）里順序顛倒后也能使用索引（優(yōu)化器會優(yōu)化列順序來適配索引），但是性能還是比順序正確時(shí)差一些
至于為什么要遵從最左匹配原則，大家可以結(jié)合前面講過的組合索引的數(shù)據(jù)結(jié)構(gòu)來分析（還覺得我前戲太多嗎，?。。?/p>
使用否定形式
否定形式包括：<>, !=, NOT IN,NOT EXIST，會導(dǎo)致全表掃描
索引列上進(jìn)行運(yùn)算
說的更準(zhǔn)確點(diǎn)，是在查詢條件的左側(cè)進(jìn)行運(yùn)算，這種情況就不能用索引了
在查詢條件的右側(cè)進(jìn)行計(jì)算，還是能用到索引的
索引列上使用函數(shù)
說的更準(zhǔn)確點(diǎn)，是在查詢條件的左側(cè)使用函數(shù)，這種情況就不能用索引了
在右側(cè)使用函數(shù)，還是能用到索引的
強(qiáng)烈建議：使用索引時(shí)，條件表達(dá)式的左側(cè)應(yīng)該是原始列
進(jìn)行默認(rèn)的類型轉(zhuǎn)換
新建表：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;
默認(rèn)的類型轉(zhuǎn)換不僅會增加額外的性能開銷，還會導(dǎo)致索引不可用，可以說是有百害而無一利
（對于 int 類型的列，傳字符串類型的值是可以走索引的，MySQL 內(nèi)部自動做了隱式類型轉(zhuǎn)換；相反，對于 char 或 varchar 類型的列，傳入 int 值是無法走索引的）
強(qiáng)烈建議：使用索引時(shí)，條件表達(dá)式的右側(cè)常數(shù)的類型應(yīng)該與列類型保持一致
IS NULL 與 IS NOT NULL
我做個(gè)簡單的測試，就不下結(jié)論了
SQL：explain select * from tbl_char where name is not null;
SQL：explain select * from tbl_char where name is null;
強(qiáng)烈建議：所有列都指定 NOT NULL 和默認(rèn)值
NULL 的陷阱太多，詳情可查看：神奇的 SQL 之溫柔的陷阱 → 三值邏輯 與 NULL ！
不走索引的情況，文中只列舉了常見的部分，還有其他的場景未列舉，歡迎小伙伴們補(bǔ)充
總結(jié)
「1、索引數(shù)據(jù)結(jié)構(gòu)」
索引的數(shù)據(jù)結(jié)構(gòu)包括 哈希表、B樹、B+樹 等，而用的最多的就是 B+數(shù)
「2、未走索引的常見場景」
全表掃描優(yōu)于索引掃描 LIKE 進(jìn)行后方一致或中間一致的匹配 OR 前后未同時(shí)使用索引 組合索引，未遵循最左匹配原則
進(jìn)行默認(rèn)的類型轉(zhuǎn)換
使用否定形式
索引列上進(jìn)行運(yùn)算
索引列上使用函數(shù)
「3、推薦做法」
使用組合索引時(shí)，組合索引中的第一列必須寫在查詢條件的開頭，而且索引中列的順序不能顛倒
使用索引時(shí)，條件表達(dá)式的左側(cè)應(yīng)該是原始列，右側(cè)是常數(shù)且類型與左側(cè)列一致，左右側(cè)都不參與計(jì)算、使用函數(shù)（計(jì)算、函數(shù)運(yùn)算、邏輯處理都交由專門的開發(fā)語言去實(shí)現(xiàn)）
所有列都指定 NOT NULL 和默認(rèn)值，避免 NULL 的陷阱
參考
《SQL進(jìn)階教程》
神奇的 SQL 之溫柔的陷阱 → 三值邏輯 與 NULL ！
推薦文章
分享一套基于SpringBoot和Vue的企業(yè)級中后臺開源項(xiàng)目，這個(gè)項(xiàng)目有點(diǎn)哇塞！
圈子哥推薦一款基于 Spring Boot 開發(fā) OA 開源產(chǎn)品，學(xué)習(xí)/搞外快都是不二選擇！
原創(chuàng)電子書
歷時(shí)整整一年總結(jié)的?Java 面試 + Java 后端技術(shù)學(xué)習(xí)指南，這是本人這幾年及校招的總結(jié)，各種高頻面試題已經(jīng)全部進(jìn)行總結(jié)，按照章節(jié)復(fù)習(xí)即可，已經(jīng)拿到了大廠offer。
原創(chuàng)思維導(dǎo)圖
掃碼或者微信搜?程序員的技術(shù)圈子?回復(fù)?面試?領(lǐng)取原創(chuàng)電子書和思維導(dǎo)圖。
