MySQL SQL 優化之覆蓋索引

前些天，有個同事跟我說：“我寫了個SQL，SQL很簡單，但是查詢速度很慢，並且針對查詢條件建立了索引，然而索引卻不起作用，你幫我看看有沒有辦法優化？”。

我對他提供的case進行了優化，並將優化過程整理了下來。

我們先來看看優化前的表結構、資料量、SQL、執行計劃、執行時間等。

1. 表結構：

隱藏了部分不相關欄位後，可以看到表足夠簡單， 並且在order_code上建立了唯一性索引uni_order_code。

2. 資料量：316977

這個資料量還是比較小的，不過如果SQL足夠差，一樣會查詢很慢。

3. SQL：

哇，SQL足夠簡單，不過有時候越簡單也越難優化。

4. 執行計劃：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t_order	ALL	NULL	NULL	NULL	NULL	316350	Using filesort

全表掃描、檔案排序，註定查詢慢！

那為什麼MySQL沒有利用索引（uni_order_code）掃描完成查詢呢？因為MySQL認為這個場景利用索引掃描並非最優的結果。我們先來看下執行時間，然後再來分析為什麼沒有利用索引掃描。

5. 執行時間：260ms

的確，執行時間太長了，如果表資料量繼續增長下去，效能會越來越差。

我們來分析下MySQL為什麼使用全表掃描、檔案排序，而沒有使用索引掃描、利用索引順序：

1. 全表掃描、檔案排序：

雖然是全表掃描，但是掃描是順序的（不管機械硬碟還是SSD順序讀寫效能都是高的），並且資料量不是特別大，所以這部分消耗的時間應該不是特別大，主要的消耗應該是在排序上。

2. 利用索引掃描、利用索引順序：

uni_order_code是二級索引，索引上儲存了（order_code,id），每掃描一條索引需要根據索引上的id定位（隨機IO）到資料行上讀取order_amount，需要1000次隨機IO才能完成查詢，而機械硬碟隨機IO的效率是極低的（機械硬碟每秒定址幾百次）。

根據我們自己的分析選擇全表掃描相對更優。如果把limit 1000改成limit 10，則執行計劃會完全不一樣。

既然我們已經知道是因為隨機IO導致無法利用索引，那麼有沒有辦法消除隨機IO呢？

有，覆蓋索引。

我們來看看利用覆蓋索引優化後的索引、執行計劃、執行時間。

1. 建立索引：

建立了複合索引idx_ordercode_orderamount(order_code,order_amount)，將select的列order_amount也放到索引中。

2. 執行計劃：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t_order	index	NULL	idx_ordercode_ orderamount	42	NULL	1000	Using index

執行計劃顯示查詢會利用覆蓋索引，並且只掃描了1000行資料，查詢的效能應該是非常好的。

3. 執行時間：13ms

從執行時間來看，SQL的執行時間提升到原來的1/20，已經達到我們的預期。

總結：

覆蓋索引是select的資料列只用從索引中就能夠取得，不必讀取資料行，換句話說查詢列要被所建的索引覆蓋。索引的欄位不只包含查詢列，還包含查詢條件、排序等。

要寫出效能很好的SQL不僅需要學習SQL，還要能看懂資料庫執行計劃，瞭解資料庫執行過程、索引的資料結構等。