Mysql查詢優化——Explain使用

Mysql查詢優化——Explain使用

The palest ink is better than best memory——好記性不如爛筆頭。2012補記


一、關鍵詞

SQL優化、執行計劃、explain、using filesort、using temporary

二、摘要

開發中,為滿足一業務功能,使用mysql書寫sql時,一條sql往往有多種寫法,那麼我們就需要選擇執行效率比較高的sql。
因此要比較分析sql的執行過程,且同一條sql我們要比較選擇使用最優索引。
通過explain命令可以得到:
這裡寫圖片描述

整體執行順序(子查詢)、查詢型別、使用什麼表(表 泛指)、資料訪問/讀取操作型別、使用到索引等。

先整體瞭解下SQL查詢的基本執行過程:

1)應用通過MySQL API把查詢命令傳送給MySQL伺服器,然後被解析

2)檢查許可權、MySQL optimizer進行優化,經過解析和優化後的查詢命令被編譯為CPU可執行的二進位制形式的查詢計劃(query
plan),並可以被快取

3)如果存在索引,那麼先掃描索引,如果資料被索引覆蓋,那麼不需要額外的查詢,如果不是,根據索引查詢和讀取對應的記錄

4)如果有關聯查詢,查詢次序是掃描第一張表找到滿足條件的記錄,按照第一張表和第二張表的關聯鍵值,掃描第二張表查詢滿足條件的記錄,按此順序迴圈

5)輸出查詢結果,並記錄binary logs

三、Explain返回資訊:

列名說明
id執行編號,標識select所屬的行。如果在語句中沒子查詢或關聯查詢,只有唯一的select,每行都將顯示1。否則,內層的select語句一般會順序編號,對應於其在原始語句中的位置
select_type顯示本行是簡單或複雜select。如果查詢有任何複雜的子查詢,則最外層標記為PRIMARY(DERIVED、UNION、UNION RESUlT)
table訪問引用哪個表(引用某個查詢,如“derived3”)
type資料訪問/讀取操作型別(ALL、index、range、ref、eq_ref、const/system、NULL)
possible_keys揭示哪一些索引可能有利於高效的查詢
key顯示mysql決定採用哪個索引來優化查詢
key_len顯示mysql在索引裡使用的位元組數
ref顯示了之前的表在key列記錄的索引中查詢值所用的列或常量
rows為了找到所需的行而需要讀取的行數,估算值,不精確。通過把所有rows列值相乘,可粗略估算整個查詢會檢查的行數
Extra額外資訊,如using index、filesort等

  • select_type列:
select_type型別說明
SUBQUERY在select列表中的子查詢,如SELECT *,(SELECT id FROM product_info) AS id FROM product_info
DERIVED在from子語句中子查詢,如SELECT * FROM product_info p1 ,(SELECT * FROM product_info) p2.Mysql會遞迴執行,並把結果放到臨時表中
UNION在UNION中第二個和隨後的SELECT被標記為UNION
UNION RESULT用來從UNION的匿名臨時表檢索結果的SELECT被標記為UNION RESULT

  • type列(依次從最差到最優):
type型別說明
All最壞的情況,從頭到尾全表掃描
index和全表掃描一樣。只是掃描表的時候按照索引次序進行而不是行。主要優點就是避免了排序, 但是開銷仍然非常大。如在Extra列看到Using index,說明正在使用覆蓋索引,只掃描索引的資料,它比按索引次序全表掃描的開銷要小很多
range範圍掃描,一個有限制的索引掃描。key 列顯示使用了哪個索引。當使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比較關鍵字列時,可以使用 range
ref一種索引訪問,它返回所有匹配某個單個值的行。此類索引訪問只有當使用非唯一性索引或唯一性索引非唯一性字首時才會發生
eq_ref最多隻返回一條符合條件的記錄。使用唯一性索引或主鍵查詢時會發生 (高效)
const/system當主鍵放入where子句時,mysql把這個查詢轉為一個常量(高效)
Null意味說mysql能在優化階段分解查詢語句,在執行階段甚至用不到訪問表或索引(高效)

  • Extra列常見情況(需要優化):
Extra情況說明
Using temporary表示 MySQL 在對查詢結果排序時使用臨時表。常見於排序 order by 和分組查詢 group by
Using filesort表示 MySQL 會對結果使用一個外部索引排序,而不是從表裡按索引次序讀到相關內容。可能在記憶體或者磁碟上進行排序。MySQL 中無法利用索引完成的排序操作稱為“檔案排序”

四、事例(2012公司專案):

案例1:

EXPLAIN
SELECT e1.*,0 AS 'hasCount' FROM eticketdetail e1 WHERE e1.brandUserId =5303 AND e1.status =1 AND e1.parentId =0 
UNION  
SELECT e2.*,0 AS 'hasCount' FROM eticketdetail e2 WHERE e2.status =1 AND  e2.id IN (SELECT e3.parentId FROM eticketdetail e3 WHERE  e3.parentId >0 AND e3.brandUserId = 5303)
ORDER BY createtime LIMIT 0,10

這裡寫圖片描述

EXPLAIN
SELECT e1.*,0 AS 'hasCount' FROM eticketdetail e1 WHERE e1.brandUserId =5303 AND e1.status =1 AND e1.parentId =0 
UNION  
SELECT e2.*,0 AS 'hasCount' FROM eticketdetail e2 WHERE e2.status =1 AND  EXISTS (SELECT 1 FROM eticketdetail e3 WHERE  e3.parentId >0 AND e3.brandUserId = 5303)
ORDER BY createtime LIMIT 0,10

這裡寫圖片描述

EXPLAIN
SELECT e1.*,0 AS 'hasCount' FROM eticketdetail e1 WHERE e1.brandUserId =5303 AND e1.status =1 AND e1.parentId =0 
UNION  
SELECT e2.*,0 AS 'hasCount' FROM eticketdetail e2 INNER JOIN (SELECT e3.parentId FROM eticketdetail e3 WHERE  e3.parentId >0 AND e3.brandUserId = 5303) tab ON e2.id = tab.parentid  WHERE e2.status =1 
ORDER BY createtime LIMIT 0,10

這裡寫圖片描述


案例2:

第一種寫法:
SELECT 
mp.*
FROM memberpromotion mp
INNER JOIN memberpromotionlocation mpl
ON mpl.MemberPromotionID = mp.ID
WHERE mp.status = 1
AND mp.BrandUserID = 2402
AND mpl.shopId = 0
第二種寫法:
SELECT 
mp.*
FROM (SELECT * FROM memberpromotion memb WHERE memb.BrandUserID = 2402) mp
INNER JOIN memberpromotionlocation mpl
ON mpl.MemberPromotionID = mp.ID
WHERE mp.status = 1
AND mpl.shopId = 0

這裡寫圖片描述


案例3:

第一幅圖語句:
SELECT SQL_NO_CACHE
e.*,
(SELECT
COUNT(id)
FROM eticketdetail
WHERE userId = e.userId
AND orderId = e.orderId
AND promotionId = e.promotionId
AND promotionProductId = e.promotionProductId
AND STATUS != 3
AND STATUS = 0 ) AS 'hasCount'
FROM eticketdetail e
WHERE userId = 5302
AND isGift = 0 AND isReal = 0 AND STATUS = 0
GROUP BY userId,orderId,promotionId,promotionProductId 
UNION 
SELECT e.*, 1    AS 'hasCount' FROM eticketdetail e
WHERE userId = 5302  AND isGift = 1 AND STATUS = 0
ORDER BY CreateTime DESC
第二幅圖語句:(UNION改為UNION ALL)
SELECT SQL_NO_CACHE
e.*,
(SELECT
COUNT(id)
FROM eticketdetail
WHERE userId = e.userId
AND orderId = e.orderId
AND promotionId = e.promotionId
AND promotionProductId = e.promotionProductId
AND STATUS != 3
AND STATUS = 0 ) AS 'hasCount'
FROM eticketdetail e
WHERE userId = 5302
AND isGift = 0
AND isReal = 0
AND STATUS = 0
GROUP BY userId,orderId,promotionId,promotionProductId 
UNION ALL
SELECT e.*, 1    AS 'hasCount' FROM eticketdetail e
WHERE userId = 5302  AND isGift = 1 AND STATUS = 0
ORDER BY CreateTime DESC
第三幅圖: 增加userId索引後
第四幅圖:
SELECT 
e.*,
tab.t AS 'hasCount'
FROM eticketdetail e 
INNER JOIN (
SELECT id,COUNT(id) AS t FROM eticketdetail WHERE userid=5302  AND isGift = 0 AND isReal = 0 AND STATUS != 3 AND STATUS = 0 GROUP BY orderId,promotionId,promotionProductId ORDER BY NULL
) tab ON tab.id = e.ID
WHERE userId = 5302
AND isGift = 0
AND isReal = 0
AND STATUS = 0
GROUP BY orderId,promotionId,promotionProductId 
UNION ALL
SELECT e.*, 1    AS 'hasCount' FROM eticketdetail e
WHERE userId =5302  AND isGift = 1 AND STATUS = 0
ORDER BY CreateTime DESC

這裡寫圖片描述

這玩意,只有動手多折騰比較幾下~

五、參考:

1.《高效能MySQL》第3版
2.官網:http://dev.mysql.com/doc/refman/5.7/en/explain-output.html