MYSQL查詢效能最佳化

標籤：

查詢的基礎知識

MySQL查詢過程如所示：

 

MySQL是通過查詢語句的雜湊尋找來命中緩衝的，需要注意的是如果查詢語句大小寫不一致或者有多餘的空格，是不會命中緩衝的。

一個查詢通常有很多執行方式，查詢最佳化工具通過計算開銷（隨機讀取次數）來選擇最優的查詢。

MySQL把所以的查詢都當做聯結來處理，聯結是按照迴圈嵌套的策略來執行的，如所示：

 

查詢的最佳化和限制

我們需要知道查詢最佳化工具會做哪些最佳化，這樣在寫查詢的時候就可以不需要考慮手動來做這些最佳化，把這些事情交給查詢最佳化工具去做是更好的選擇，查詢最佳化工具的最佳化類型如下：

1. 聯結最佳化

（1）對聯結中的表重新排序

 

 

（2）將外聯結轉換為內聯結

2.  排序最佳化

（1）使用索引排序

（2）記憶體快速排序

（3）檔案排序

3. 最佳化COUNT()

（1）沒有Where子句的COUNT(*)：記錄表的行數

（2）COUNT(column) 統計column非NULL的行數，column不可能為NULL時，COUNT(column)最佳化為COUNT(*)

4. 最佳化IN()

（1）對IN()裡面的資料排序，進行二分尋找，對IN()子查詢不會使用這種最佳化，如：

 

5. 代數等價最佳化

（1）簡化並正常化代數運算式

(a<b AND b=c) AND a=5  =>  b>5 AND b=c AND a=5

6. 早期終結

早期終結是指一旦滿足查詢的條件，MySQL就會立即停止處理該查詢。

（1）LIMIT

（2）不可能的查詢條件

 

（3）取得唯一值或值不存在

 

7. 其它最佳化

（1）索引最佳化MIN()和MAX()

（2）覆蓋索引

（3）相等傳遞

 

查詢最佳化工具最佳化時存在一定的限制，在這些查詢最佳化工具不能很好地最佳化的地方，需要我們手動進行最佳化：

（1）關聯子查詢。如IN()可能最佳化得很差，如下面是個不好的最佳化

 

 

（2）聯合（UNION）。有時不能把UNION外的條件應用到內部

（3）索引合并。排序和合并的開銷可能很大

（4）相等傳遞。大IN表導致較慢的最佳化

（5）並存執行。不能在多CPU並存執行一個查詢

（6）對同一個表SELECT和UPDATE

 

寫出高效的查詢

查詢效率低的原因：

（1）應用程式擷取了超過需要的資料

（2）MySQL伺服器分析了超過需要的行

重構查詢的方式：

（1）複雜查詢和多個查詢的權衡。用儘可能少的查詢做儘可能多的事情有時候是不對的。

（2）縮短查詢，防止查詢長時間佔用表

 

 

（3）分解聯結

寫查詢時考慮以下的最佳化方法：

1. 最佳化聯結

（1）ON或USING使用的列上有索引

（2）GROUP BY或ORDER BY只引用一個表的列

2. 最佳化COUNT()

（1）總是使用COUNT(*)

（2）對索引的小部分進行統計

 

 

（3）儲存統計結果

3. 最佳化LIMIT和OFFSET

在覆蓋索引上進行位移

4. 最佳化子查詢

儘可能使用聯結代替IN、EXISTS、NOT EXISTS

5. 最佳化聯合

（1）始終使用UNION ALL

（2）條件下推到UNION

6. 最佳化GROUP BY和DISTINCT

（1）索引最佳化

（2）ORDER BY NULL跳過自動排序

以下是常用的查詢最佳化提示：

 

MYSQL查詢效能最佳化