1.what is explain(explain 是個什麼東東)
explain(解釋)，在 Mysql 中 作為一個關鍵詞，用來解釋 Mysql 是如何執行語句，可以連線 select 、delete、insert、update 語句。
通常我們使用 explain 連線 一條 select 語句，檢視執行狀態，判斷是否需要優化。
2.how to use explain（如何使用呢）
栗子：
explain select s.name,s.id,s.age,s.create_time from student s;
輸出：
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | s | NULL | ALL | NULL | NULL | NULL | NULL | 7 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
複製程式碼
官方：
EXPLAIN [explain_type] explainable_stmt
explain_type: {
EXTENDED
| PARTITIONS
| FORMAT = format_name
}
explainable_stmt: {
SELECT statement
| DELETE statement
| INSERT statement
| REPLACE statement
| UPDATE statement
}
複製程式碼輸出的列名：
- id : select 識別符號
- select_type:select 型別
- table：輸出行對應的表
- partitions：匹配的分割槽
- type：join 型別
- possible_keys：可選的索引，可以通過 show index from tbl_name 檢視錶有哪些索引。
- key：實際選擇的索引
- key_len：實際使用索引的長度
- ref：與索引比較的列
- rows：掃描行數的預估值
- filtered：按表條件篩選的行的百分比
- Extra：額外資訊
3.重點關注的列
3.1 type 列
type 列描述了表的 join 型別，以下以 查詢的最優到最差的排序列出了可能值：
- system ：當表只有一條資料（= system table）時，為 system 型別，是 const 型別的 特例。
- const：當表最多隻有一條資料相匹配時，為 const 型別。因為只有一行，所以優化器的其餘部分可以將此行列中的值視為常量（constant）。const表非常快，因為它們只讀一次。在使用 主鍵 或者 唯一索引 和常量比較時，即為 const 型別。 如下的查詢，tbl_name 可以被用作 const 表：
SELECT * FROM tbl_name WHERE primary_key=1;
SELECT * FROM tbl_name
WHERE primary_key_part1=1 AND primary_key_part2=2;
複製程式碼栗子：
explain select s.* from student s where s.id = 1
複製程式碼輸出：
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | s | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
複製程式碼- eq_ref：通常出現在多表 join 查詢，並且 關聯的欄位是 主鍵 或者 唯一非空索引，即後表 只能匹配一條資料。 下面的示例，可以使用 eq_ref join 來處理 ref_table：
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
複製程式碼- ref：通常出現在多表 join 查詢，關聯使用了 最左字首原則的索引 或者 關聯的是非主鍵 或者 非 唯一索引（也就是說，join 不能根據索引選擇 單行資料） 下面的示例，Mysql 可以使用 ref join 來處理 ref_table:
SELECT * FROM ref_table WHERE key_column=expr;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
複製程式碼- fulltext：使用全文索引執行 join
- ref_or_null：在 ref 的基礎上 ， 另外還搜尋了包含空值的行 下面的示例，Mysql 可以使用 ref_or_null join 來處理 ref_table:：
SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;
複製程式碼- index_merge: 合併索引優化
- unique_subquery：子查詢返回唯一主鍵。 形如下面的示例：
value IN (SELECT primary_key FROM single_table WHERE some_expr)
複製程式碼- index_subquery：和 unique_subquery 類似，只不過在子查詢中使用非唯一索引 形如下面的形式：
value IN (SELECT key_column FROM single_table WHERE some_expr)
複製程式碼- range：當 索引和常量 使用 諸如=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN() 進行比較時 ，可以使用 range. 例如：
SELECT * FROM tbl_name
WHERE key_column = 10;
SELECT * FROM tbl_name
WHERE key_column BETWEEN 10 and 20;
SELECT * FROM tbl_name
WHERE key_column IN (10,20,30);
SELECT * FROM tbl_name
WHERE key_part1 = 10 AND key_part2 IN (10,20,30);
複製程式碼- index：和 all 類似 ，只不過 掃描的是 索引樹
- all：全表掃描，可以通過增加 索引避免 全表掃描
3.2 keys 列：真正使用的索引
3.3 rows 列：掃描的記錄數
4.使用 explain 提升查詢效能案例分析
假設有如下的 sql：根據訂單日期 和 店員id 查詢 訂單資訊（已建立了訂單日期的索引），查詢結果返回 18條記錄。
SELECT * FROM orders
WHERE YEAR(o_orderdate) = 1992 AND MONTH(o_orderdate) = 4
AND o_clerk LIKE '%0223';
複製程式碼Explain 輸出執行計劃：
問題所在：
- 根據 type 為 ALL ， 查詢進行了全表掃描，被掃描的記錄 rows 為 150萬。
- possible_keys 和 ky 均為空 ，訂單日期索引完全失效，原因在於被索引的欄位使用了處理函式導致索引失效
4.1.修改sql 保證 訂單日期索引正常
SELECT * FROM orders
WHERE o_orderdate BETWEEN '1992-04-01' AND '1992-04-30'
AND o_clerk LIKE '%0223';
複製程式碼重新使用 Explain 檢視 執行計劃：
發現：type 由 ALL 變為 range ，訂單日期索引得以利用，被掃描的記錄由 15萬 降為 3.3萬左右。
4.2.另一個優化點在 店員欄位的過濾
為 店員欄位建立索引：
CREATE INDEX i_o_clerk ON orders(o_clerk);
複製程式碼再次輸出執行計劃：
發現：基本上並沒有什麼變化，新建的索引沒有被利用，原因在於 該欄位是 模糊查詢，過濾指定字尾的 店員資訊。但是索引對於字尾過濾會失效（儘管索引對於字首有效果）。
修改sql，全量過濾店員欄位：
SELECT * FROM orders
WHERE o_orderdate BETWEEN '1992-04-01' AND '1992-04-30'
AND o_clerk LIKE 'Clerk#000000223';
複製程式碼再次輸出執行計劃：
發現：可用索引增加，真正使用的索引變為 店員欄位上的索引，被掃描的行由 3.3萬降為 1546。
4.3.對於多條件查詢，可以考慮使用組合索引
建立如下索引：
CREATE INDEX io_clerk_date ON orders(o_clerk, o_orderdate)
複製程式碼** ：這裡將 o_clerk 放在 o_orderdate 之前，因為 o_orderdate 使用了 範圍,最左字首索引原則。
再次輸出執行計劃：
發現：使用了組合索引，被掃描記錄即為輸出的18條記錄。效率已最優化。
多次優化的總結：
| Type | Possible keys | Key | Rows Scanned | Duration (seconds) | Extra info | Rows returned |
|---|---|---|---|---|---|---|
| all | NULL | NULL | 1.50M | 1.201 | Using where | 18 |
| range | i_o_orderdate | i_o_orderdate | 32642 | 0.281 | Using index condition; Using where | 18 |
| range | i_o_orderdate, i_o_clerk | i_o_clerk | 1546 | 0.234 | Using index condition; Using where | 18 |
| range | i_o_orderdate, i_o_clerk, i_o_clerk_date | i_o_clerk_date | 18 | 0.234 | Using index condition | 18 |
歡迎關注 程式設計那點事兒，隨時隨地，想學就學~
