mysql優化必會-優化利器EXPLAIN

大家好，我是熱心的大肚皮，皮哥。

EXPLAIN

上篇，我們說了下成本計算規則，基于成本會生成一個執行計劃。如下。

列名	描述
id	查詢中，每個select都對應一個唯一id
select_type	select關鍵字對應的查詢類型
table	表名
partitions	比對的分區資訊
type	單表通路方法
possible_keys	可能用到的主鍵
key	實際用到的主鍵
key_len	實際使用的索引長度
ref	使用索引列等值查詢時，與索引列進行比對的對象資訊
rows	預估讀取的行數
filtered	過濾後剩餘資料的百分比
Extra	額外資訊

我們由淺入深，挨個講解一下。

是單表的表名，不管sql多麼複雜，執行計劃的每一步也是對單表通路。

• 每出現一個select ，則會為它配置設定一個唯一id。
• 如果連接配接查詢時，id值會相同，在前面的表是驅動表，後面的是被驅動表。
• 如果使用union all，則會出現id為null的情況，第三步代表在内部建立了一個名字是<union 1,2>的臨時表，這個null就是代表為了合并結果集去重時建立的。

複雜的查詢中select_type 标志着每一個步驟在大查詢中扮演什麼角色。

• simple：不包含union或者子查詢的查詢都算simple類型
• primary：對于包含union、union all 或者子查詢的大查詢來說，它由幾個小查詢組成，最左邊的查詢select_type就是primary。
• union：對于包含union、union all 或者子查詢的大查詢來說，它由幾個小查詢組成，除了最左邊的查詢select_type是primary外，其餘的是union。
• union result：mysql使用臨時表完成union的去重工作，這個操作的類型就是union result。
• subquery：如果包含子查詢的sql不能轉換成半連接配接形式，并且是不相關的子查詢，查詢采用物化方案執行時，那麼子查詢的第一個select查詢的select_type就是subquery。小知識：物化通過将子查詢結果作為一個臨時表來加快查詢執行速度，正常來說是在記憶體中的，如果資料量過大則會落到磁盤中。
• dependent subquery：如果包含子查詢的sql不能轉換成半連接配接形式，并且子查詢可以轉換為相關的子查詢，那麼子查詢的第一個select查詢的select_type就是dependent subquery。需要注意，dependent subquery的子查詢會執行多次。
• dependent union：包含union或者union all的大查詢中，如果大查詢依賴各個小查詢的結果，那麼除了最左面的小查詢外，其餘的是dependent union。
• derived：使用物化表的方式查詢。
• materialized：不常用，不說了。
• uncacheable subquery：不常用，不說了。
• uncacheable union：不常用，不說了。

分區我們用的少，不多說了。

這個代表着每一條記錄執行查詢時的通路方法。性能依次遞減。

• system：當表中僅一條記錄且該表使用的存儲引擎（MyISAM，MEMORY）的統計資料是精确的，那麼就是system。
• const：根據主鍵或者唯一二級索引列與常數進行等值比對。
• eq_ref：連接配接查詢時，如果被驅動表通過主鍵或者不存儲null的唯一二級索引進行等值比對；如果是聯合索引，則必須全部索引列都進行等值比對。
• ref：根據普通的二級索引列與常量進行等值比對。
• fulltext：全文索引。
• ref_or_null：對普通二級索引列進行等值比對且索引列值可以為null。
• index_merge：某些場景會使用索引合并的方式查詢，後續會詳細說。
• unique_subquery：類似于eq_ref，unique_subquery針對包含in子查詢的sql，查詢優化器将in子查詢轉換為exists查詢，且轉換後使用主鍵或者非null的唯一二級索引進行等值比對。
• index_subquery：與unique_subquery類型，差別是子查詢的是普通索引。
• range：使用索引擷取單點掃描區間的記錄或者範圍掃描。
• index：可以使用索引覆寫，但需要掃描全部的索引記錄。索引覆寫：舉個例子，我們在使用者表根據name,age建個聯合索引，select name, age from user where name ='1' 使用這句sql查詢時，則是索引覆寫。
• all：全表掃描。

possible_keys和key

可能用到的索引，和實際用到的索引。

實際用到的索引的長度。

展示的與索引列等值比對的是什麼，例如一個常數或者一個函數。

如果是全表掃描，就代表是表的估計行數；如果是索引查詢，則代表預計掃描的索引行數。

根據目前的查詢的條件過濾後，符合要求的記錄所占的百分比。

說明本次執行的額外資訊，簡單說一個。其餘的可以看看官網。

• Using index：使用覆寫索引時，會提示。

索引合并

我們簡單Mysql 一般情況下隻會為單個索引生成掃描區間，特殊情況下可以為多個索引生成掃描區間，這是使用多個索引完成一次查詢的方式也就是index merge（索引合并）。主要有3種。

1. Intersection索引合并

select * from single_table where key1 ='a' and key3='b';           

上面這個sql正常情況下，會根據idx_key1 與idx_key3兩個索引的執行成本挑選最優方案，除了這個還會有新的方案，具體如下。

1. 在idx_key1種掃描key1在['a','a']區間的二級索引，同時在idx_key3中掃描key3值在['b','b']區間的二級索引。
2. 從兩者的結果中找出id相同的記錄。
3. 根據id進行回表操作。注意：在查詢過程中，是找到一條就進行比較一次，如果兩者的id相同，則回表查詢全部資訊。

2.Union索引合并

select * from single_table where key1='a' or key3='b'           

上面這句sql，我們能使用idx_key1或者idx_key3查詢嗎？不行！以idx_key1舉例，對應的掃描區間是全部，需要每一條二級索引都要回表查詢。針對這種情況，有一個新的方案，如下。

1. 根據idx_key1掃描值在['a','a']之間的二級索引記錄，同時根據idx_key3掃描值在['b','b']的二級索引記錄。
2. 将兩個結果集進行Union去重，擷取出重複的id。
3. 在根據id回表查詢資料。注意：Union索引合并的話，用到的索引都是二級索引的話，則要求每個索引擷取到的二級索引記錄都是按照主鍵值排序的。

3. Sort-Union索引合并

select * from single_table where key1<'a' or key3>'z'           

Union索引合并使用的條件太苛刻了，那麼我們可以這樣操作，如下。

1. 根據key1<'a'從idx_key1中擷取二級索引記錄，并根據主鍵排序。
2. 根據key3>'z'從idx_key3中擷取二級索引記錄，并根據主鍵排序。
3. 上面已經排好序了，剩下的操作就與union一緻了。

更多幹貨可搜尋gz号【程式猿日常筆記】