mysql調優之——執行計劃explain

singed cat發表於2019-03-24

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 輸出執行計劃:

mysql調優之——執行計劃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 檢視 執行計劃:

mysql調優之——執行計劃explain

發現:type 由 ALL 變為 range ,訂單日期索引得以利用,被掃描的記錄由 15萬 降為 3.3萬左右。

4.2.另一個優化點在 店員欄位的過濾

為 店員欄位建立索引:

CREATE INDEX i_o_clerk ON orders(o_clerk);
複製程式碼

再次輸出執行計劃:

mysql調優之——執行計劃explain

發現:基本上並沒有什麼變化,新建的索引沒有被利用,原因在於 該欄位是 模糊查詢,過濾指定字尾的 店員資訊。但是索引對於字尾過濾會失效(儘管索引對於字首有效果)。

修改sql,全量過濾店員欄位:

SELECT * FROM orders
WHERE o_orderdate BETWEEN '1992-04-01' AND '1992-04-30'
AND o_clerk LIKE 'Clerk#000000223';
複製程式碼

再次輸出執行計劃:

mysql調優之——執行計劃explain

發現:可用索引增加,真正使用的索引變為 店員欄位上的索引,被掃描的行由 3.3萬降為 1546。

4.3.對於多條件查詢,可以考慮使用組合索引

建立如下索引:

CREATE INDEX io_clerk_date ON orders(o_clerk, o_orderdate)
複製程式碼

  ** :這裡將 o_clerk 放在 o_orderdate 之前,因為 o_orderdate 使用了 範圍,最左字首索引原則。

再次輸出執行計劃:

mysql調優之——執行計劃explain

發現:使用了組合索引,被掃描記錄即為輸出的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

歡迎關注 程式設計那點事兒,隨時隨地,想學就學~

mysql調優之——執行計劃explain

相關文章