神奇的 SQL 之擦肩而過 → 真的用到索引了嗎
索引的數(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)基本一致，只是聚簇索引有個唯一性約束
B+樹 就是如上圖中的那樣一個倒立的樹結(jié)構(gòu)
B+樹 有很多特性，這里就不細講了，有興趣的可以去查閱相關(guān)資料
組合索引的列順序
單列索引的列順序好說，它就一列，不存在列先后順序的問題，按這個列的值進行順序排序，存儲到 B+樹 中就好，上面兩圖都是單列索引
但在實際應(yīng)用中，更多的還是用到組合索引（在多列上建一個索引），既然有多列，那就存在列與列之間的順序問題了
那組合索引的的結(jié)構(gòu)具體是什么樣的了？
我們有表：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?的結(jié)構(gòu)如下
先按?c1?列排序，若 c1 列相等了再按 c2 列排序
抽象化就是，按組合索引指定的列，從左往右逐個排序；整體上先按第一列排序，第一列相等的數(shù)據(jù)整體按第二列排序，第一列相等且第二列相等的數(shù)據(jù)整體按第三列排序，以此類推
索引的擦肩而過
有的小伙伴可能急了：“樓主，前戲太多了，我要看主角！?。 ?/p>
樓主：“你怕是個杠精吧，前戲不寫長點，怎么湊夠篇幅？你去看看現(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?'充值時間',
??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)的那一個
當(dāng)優(yōu)化器計算得出通過全表查詢比通過索引查詢更優(yōu)時，它會選擇全表掃描的方式進行查詢
SQL：
explain?select?*?from?tbl_customer_recharge_record?where?customer_id?=?2;?
相信大家對這個沒什么異議，通過?idx_c_id?來完成查詢，跟我們預(yù)想的一樣
對于?explain select * from tbl_customer_recharge_record where customer_id = 1;大家睜大眼睛看清楚了?。?/p>
能用的索引包括：?idx_c_id?，但實際沒用它，而是走的全表查詢；因為優(yōu)化器認為走全表查詢成本更低，查詢更快
MySQL5.6 新引入的一項跟蹤功能：?OPTIMIZER_TRACE?，可以跟蹤優(yōu)化器做出的各種決策（比如訪問表的方法、各種開銷計算、各種轉(zhuǎn)換等）
并將跟蹤結(jié)果記錄到?INFORMATION_SCHEMA.OPTIMIZER_TRACE?中
跟蹤功能默認是關(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)容有點多，我們只關(guān)注
{
????"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)化列順序來適配索引），但是性能還是比順序正確時差一些
至于為什么要遵從最左匹配原則，大家可以結(jié)合前面講過的組合索引的數(shù)據(jù)結(jié)構(gòu)來分析（還覺得我前戲太多嗎，啊?。?/p>
使用否定形式
否定形式包括：<>, !=, NOT IN,NOT EXIST，會導(dǎo)致全表掃描
索引列上進行運算
說的更準(zhǔn)確點，是在查詢條件的左側(cè)進行運算，這種情況就不能用索引了
在查詢條件的右側(cè)進行計算，還是能用到索引的
索引列上使用函數(shù)
說的更準(zhǔn)確點，是在查詢條件的左側(cè)使用函數(shù)，這種情況就不能用索引了
在右側(cè)使用函數(shù)，還是能用到索引的
強烈建議：使用索引時，條件表達式的左側(cè)應(yīng)該是原始列
進行默認的類型轉(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;
默認的類型轉(zhuǎn)換不僅會增加額外的性能開銷，還會導(dǎo)致索引不可用，可以說是有百害而無一利
（對于 int 類型的列，傳字符串類型的值是可以走索引的，MySQL 內(nèi)部自動做了隱式類型轉(zhuǎn)換；相反，對于 char 或 varchar 類型的列，傳入 int 值是無法走索引的）
強烈建議：使用索引時，條件表達式的右側(cè)常數(shù)的類型應(yīng)該與列類型保持一致
IS NULL 與 IS NOT NULL
我做個簡單的測試，就不下結(jié)論了
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 ！
不走索引的情況，文中只列舉了常見的部分，還有其他的場景未列舉，歡迎小伙伴們補充
總結(jié)
「1、索引數(shù)據(jù)結(jié)構(gòu)」
索引的數(shù)據(jù)結(jié)構(gòu)包括 哈希表、B樹、B+樹 等，而用的最多的就是 B+數(shù)
「2、未走索引的常見場景」
全表掃描優(yōu)于索引掃描 LIKE 進行后方一致或中間一致的匹配 OR 前后未同時使用索引 組合索引，未遵循最左匹配原則
進行默認的類型轉(zhuǎn)換
使用否定形式
索引列上進行運算
索引列上使用函數(shù)
「3、推薦做法」
使用組合索引時，組合索引中的第一列必須寫在查詢條件的開頭，而且索引中列的順序不能顛倒
使用索引時，條件表達式的左側(cè)應(yīng)該是原始列，右側(cè)是常數(shù)且類型與左側(cè)列一致，左右側(cè)都不參與計算、使用函數(shù)（計算、函數(shù)運算、邏輯處理都交由專門的開發(fā)語言去實現(xiàn)）
所有列都指定 NOT NULL 和默認值，避免 NULL 的陷阱
參考
《SQL進階教程》
神奇的 SQL 之 ICP → 索引條件下推
神奇的 SQL 之溫柔的陷阱 → 三值邏輯 與 NULL ！
-END-
?關(guān)注公眾號：Java后端編程，回復(fù)下面關(guān)鍵字?
要Java學(xué)習(xí)完整路線，回復(fù)??路線?
缺Java入門視頻，回復(fù)：?視頻?
要Java面試經(jīng)驗，回復(fù)??面試?
缺Java項目，回復(fù)：?項目?
進Java粉絲群：?加群?
PS：如果覺得我的分享不錯，歡迎大家隨手點贊、在看。
（完） 加我"微信"?獲取一份 最新Java面試題資料 請備注：666，不然不通過～
最近好文
最近面試BAT，整理一份面試資料《Java面試BAT通關(guān)手冊》，覆蓋了Java核心技術(shù)、JVM、Java并發(fā)、SSM、微服務(wù)、數(shù)據(jù)庫、數(shù)據(jù)結(jié)構(gòu)等等。 獲取方式：關(guān)注公眾號并回復(fù)?java?領(lǐng)取，更多內(nèi)容陸續(xù)奉上。 明天見(??ω??)??