MySQL SQL最佳化之覆蓋索引，mysqlsql最佳化索引

MySQL SQL最佳化之覆蓋索引，mysqlsql最佳化索引

內容概要

利用主索引提升SQL的查詢效率是我們經常使用的一個技巧，但是有些時候MySQL給出的執行計畫卻完全出乎我們的意料，我們預想MySQL會通過索引掃描完成查詢，但是MySQL給出的執行計畫卻是通過全表掃描完成查詢的，其中的某些情境我們可以利用覆蓋索引進行最佳化。

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

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

最佳化前的表結構、資料量、SQL、執行計畫、執行時間

表結構

CREATE TABLE `t_order` (  

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`order_code` char(12) NOT NULL,  

`order_amount` decimal(12,2) NOT NULL,

PRIMARY KEY (`id`),  

UNIQUE KEY `uni_order_code` (`order_code`) USING BTREE )

ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

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

資料量：316977

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

SQL

select order_code,

order_amount from t_order order by order_code limit 1000;

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

執行計畫

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

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

執行時間：260ms

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

全表掃描、檔案排序與索引掃描、索引排序的區別

全表掃描、檔案排序：

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

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

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

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

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

有，覆蓋索引。

最佳化後的索引、執行計畫、執行時間

建立索引

ALTER TABLE `t_order`

ADD INDEX `idx_ordercode_orderamount` USING BTREE (`order_code` ASC, `order_amount` ASC);

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

執行計畫

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

執行時間：13ms

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

總結

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

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

轉載自：Mr船長

原文：https://my.oschina.net/loujinhe/blog/1528233#comment-list

相關閱讀：

遠端資料庫的表超過20個索引的影響

如何理解並正確使用MySql索引

CBO對於Cost值相同索引的選擇

Oracle索引分支塊的結構

複合索引與綁定變數

資源下載

關注公眾號：資料和雲（OraNews）回複關鍵字擷取

‘2017DTC’，2017DTC大會PPT

‘DBALIFE’，“DBA的一天”海報

‘DBA04’，DBA手記4經典篇章電子書

‘INTERNALS’，Oracle RAC PPT

‘122ARCH’，Oracle 12.2體繫結構圖

‘2017OOW’，Oracle OpenWorld資料

‘PRELECTION’，大講堂講師課程資料