神奇的 SQL 之擦肩而過 → 真的用到索引了嗎

開心一刻

　　今天下班，騎著青桔電動車高高興興的哼著曲回家，感覺整個世界都是我的

　　剛到家門口，還未下車，老媽就氣沖沖的走過來對我說道：“你表哥就比你大一歲，人家都買了賓士了，50 多萬！”

　　我：“表哥那車，舅舅出了 40 多萬”

　　老媽：“那不還有 10 多萬是他自己出的”

　　我：“那 10 幾萬是他丈母孃出的”

　　老媽：“你還有臉說，你連個物件都沒有！”

　　老媽走回家，回頭望了眼我胯下的青桔，一臉不屑地說道：“連個自行車都沒有”

　　我：“這是電動車！”

　　老媽：“那也不是你的”

　　我低頭望了望青桔，差點一腳把它踹開，可想了想，踹壞了賠不起，趕緊找個停車點把車停好吧，不然又多扣五毛錢了

索引的資料結構

　　什麼是資料庫索引 ，相信大家都能答上來，索引就是為了加速對錶中資料行的檢索而建立的一種分散儲存的資料結構（索引是一種資料結構）

　　但具體是什麼樣的資料結構，很多小夥伴可能就不知道了

　　索引的資料結構包括 雜湊表、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 下的聚簇索引 和 二級索引還是有區別的

　　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 列排序

　　　　抽象化就是，按組合索引指定的列，從左往右逐個排序；整體上先按第一列排序，第一列相等的資料整體按第二列排序，第一列相等且第二列相等的資料整體按第三列排序，以此類推

索引的擦肩而過

　　有的小夥伴可能急了：“樓主，前戲太多了，我要看主角！！！”

　　樓主：“你怕是個槓精吧，前戲不寫長點，怎麼湊夠篇幅？ 你去看看現在的動漫，哪個不是正戲不夠前戲來扣？（更可惡的是還有一大截尾戲拼湊）”

　　好了，不多扯了（再扯樓主怕是有生命危險了），我們一起來看看今天的主角們！

　　環境準備

　　　　MySQL 版本： 5.7.30-log ，儲存引擎： InnoDB 

　　　　準備表：  tbl_customer_recharge_record  ，並初始化 7 條資料

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:銀聯,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 個索引：

　　　　　　 id 列上的聚簇索引

　　　　　　 customer_id 列上的二級索引： idx_c_id 

　　　　　　以及 customer_name,recharge_type,recharge_time 列上的組合索引： idx_name_type_time 

　　　　後面我們會用 EXPLAIN 來檢視執行計劃，檢視索引使用情況，對它還不熟的小夥伴，趕緊點進去先看看

　　全表掃描更優

　　　　這是什麼意思了，就是說優化器在進行優化的時候，會從眾多可選的執行計劃中選擇它認為最優的那一個

　　　　當優化器計算得出通過全表查詢比通過索引查詢更優時，它會選擇全表掃描的方式進行查詢

　　　　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 ，但實際沒用它，而是走的全表查詢；因為優化器認為走全表查詢成本更低，查詢更快

　　　　 MySQL5.6 新引入的一項跟蹤功能： OPTIMIZER_TRACE ，可以跟蹤優化器做出的各種決策（比如訪問表的方法、各種開銷計算、各種轉換等）

　　　　並將跟蹤結果記錄到 INFORMATION_SCHEMA.OPTIMIZER_TRACE 中

　　　　跟蹤功能預設是關閉的，我們要用它的話，需要將其開啟： set optimizer_trace='enabled=on'; 

　　　　檢視優化器優化步驟： select * from information_schema.OPTIMIZER_TRACE; 

　　　　優化器對 select * from tbl_customer_recharge_record where customer_id = 1； 優化步驟如下

{
    "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":[

                ]
            }
        }
    ]
}

　　　　內容有點多，我們只關注

{
    "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 前後未同時使用索引

　　　　資料量太少，優化器會選擇全表掃描，而不走索引了，我們再加點資料

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 前後有一個走不了索引，那就會全表掃描了

　　組合索引，未遵循最左匹配原則

　　　　最左匹配指的是，按組合索引指定的列順序，從左往右逐個列匹配，像這樣

　　　　不能直接跨過前面的列，否則就不能用到索引了

　　　　強烈建議：組合索引中的第一列必須寫在查詢條件的開頭，而且索引中列的順序不能顛倒

　　　　雖說有些資料庫（例如 MySQL）裡順序顛倒後也能使用索引（優化器會優化列順序來適配索引），但是效能還是比順序正確時差一些

　　　　至於為什麼要遵從最左匹配原則，大家可以結合前面講過的組合索引的資料結構來分析（還覺得我前戲太多嗎，啊！）

　　使用否定形式

　　　　否定形式包括：<>, !=, NOT IN,NOT EXIST，會導致全表掃描

　　索引列上進行運算

　　　　說的更準確點，是在查詢條件的左側進行運算，這種情況就不能用索引了

　　　　在查詢條件的右側進行計算，還是能用到索引的

　　索引列上使用函式

　　　　說的更準確點，是在查詢條件的左側使用函式，這種情況就不能用索引了

　　　　在右側使用函式，還是能用到索引的

　　　　強烈建議：使用索引時，條件表示式的左側應該是原始列

　　進行預設的型別轉換

　　　　新建表： tbl_char ，並初始化 7 條資料

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 內部自動做了隱式型別轉換；相反，對於 char 或 varchar 型別的列，傳入 int 值是無法走索引的）

　　　　強烈建議：使用索引時，條件表示式的右側常數的型別應該與列型別保持一致

　　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、索引資料結構

　　　　索引的資料結構包括 雜湊表、B樹、B+樹 等，而用的最多的就是 B+數

　　2、未走索引的常見場景

　　　　全表掃描優於索引掃描

　　　　LIKE 進行後方一致或中間一致的匹配

　　　　OR 前後未同時使用索引

　　　　組合索引，未遵循最左匹配原則

　　　　進行預設的型別轉換

　　　　使用否定形式

　　　　索引列上進行運算

　　　　索引列上使用函式

　　3、推薦做法

　　　　使用組合索引時，組合索引中的第一列必須寫在查詢條件的開頭，而且索引中列的順序不能顛倒

　　　　使用索引時，條件表示式的左側應該是原始列，右側是常數且型別與左側列一致，左右側都不參與計算、使用函式（計算、函式運算、邏輯處理都交由專門的開發語言去實現）

　　　　所有列都指定 NOT NULL 和預設值，避免 NULL 的陷阱

參考

　　《SQL進階教程》

　　神奇的 SQL 之 ICP → 索引條件下推

　　神奇的 SQL 之溫柔的陷阱 → 三值邏輯 與 NULL ！