讀薄《高性能MySql》(一)MySql基本知識
讀薄《高性能MySql》(二)Scheme與數據優化
讀薄《高性能MySql》(三)索引優化
讀薄《高性能MySql》(四)查詢性能優化

對 MySql 進行優化，必須對 Scheme，索引，查詢語句一同優化。

通過前面的章節我們掌握了 Scheme 和 索引的優化，最后我們來看一下查詢優化。

為了優化查詢，我們必須先了解查詢是怎樣執行的，然后探討優化器在哪些方面做得還不足，以幫助 MySql 更有效的執行查詢。

優化數據訪問

在一條 Sql 語句執行的很慢的時候，可以從以下兩個方面來分析：

• 是否在檢索的時候訪問了太多的行或者列
• MySql 服務器是否在分析大量超過需要的行

請求了不需要的數據

萬惡之源 SELECT *

一個很好用的觀點就是在每次使用 SELECT * 取出全部行的時候都要審視一下自己是否需要全部數據。

取出所有列可能使得索引覆蓋無效，一些 DBA 是嚴格禁止 SELECT * 的寫法的。

重復查詢數據

有些地方可能會不小心的重復查詢了相同的數據。比如在論壇中，如果一個人回復多次，很有可能會一不小心每次都去請求這個人的資料，一個有效的方法就是使用緩存。

掃描額外的記錄

確定查詢只返回需要的數據以后，接下來該看一下為了返回需要的記錄是否掃描了太多行了。有兩個指標我們需要關注，一個是掃描的行數和返回行數的比值，另外一個是掃描的訪問類型。

掃描行數和返回行數的比值如果過低，則需要掃描大量的數據才能返回結果，通常可以使用如下的方法來保存數據：

• 使用索引覆蓋，把所有的列放入索引中，就無需掃描表了
• 改變數據庫結構，比如采用單獨的表匯總表
• 重新寫這個 SQL 語句

在 EXPLAIN 語句中的 type 列中反應了訪問類型，從慢到快分別是：

全表掃描，索引掃描，范圍掃描，唯一索引查詢，常數引用。

如果查詢沒有使用合適的類型，可以合理的添加索引。

重構查詢方式

將一個復雜查詢拆分成多個查詢

MySql 從設計上讓連接和斷開都很快。如果只是返回一個小小的結果，MySql 非常高效。

當然能一個查詢就解決的要盡量寫成一個查詢，只是告訴大家不要太懼怕把查詢拆分開來會帶來性能損失。

切分查詢

有時候一個大查詢會占用表鎖很久，影響業務。這時候可以將大查詢分為小查詢，每次執行這個查詢的一小部分。

比如定期清除大量數據的時候，如果有一個大的語句一次性完成，則可能會占住很多資源，影響其他查詢。

將刪除改寫成一次刪除一小部分數據，分散開來在不同時間執行，可以將服務器壓力分散到很長的一個時間段中。

分解關聯查詢

很多高性能應用會將一個大的關聯查詢分解成多個單表查詢。

• 讓緩存效率更高，許多應用可以緩存單表查詢的結果，那么下次查詢的時候可以減少這次查詢
• 單個查詢減少 鎖的競爭
• 更容易對數據庫進行拆分
• 減少冗余記錄查詢

MySql 查詢過程

MySQL 通信協議

MySQL 客戶端和服務端的通信是半雙工的，這意味著同一個時刻內，客戶端和服務端只有一方在發送數據。一旦一方開始發送數據，另外一端必須接受完整個消息才能進行響應。

這就是為什么當查詢語句特別長的時候，max_allowed_packet 特別重要了。所以在必要的時候需要添加 LIMIT 限制。

查詢狀態

對于一個 MySQL 連接，任何時刻都有一個狀態，該狀態表示了 MySQL 當前正在做什么，用 SHOW FULL PROCESSLIST 命令即可。

Sleep

線程正在等待客戶端發送新的請求。

Query

線程正在執行查詢或者在將結果發送給客戶端

Locked

該線程在等待表鎖

Analyzing and statistics

線程正在收集存儲引擎的統計信息，并且生成執行計劃。

Copying to tmp table

線程正在把數據復制到一個臨時表中，一般在 Group By 或者排序的時候會出現這個狀態。

Sorting result

線程正在排序數據

Sending data

線程可能在多個狀態之間傳送數據，或者在向客戶端返回數據。

MySQL 對關聯表順序優化

MySQL 的優化器會對查詢進行靜態和動態優化，期中我們只挑最重要的優化講，也就是對關聯表順序的優化。

我們先來看一個 UNION 的例子，對于 UNION 查詢，MySQL 會將單個查詢結構放入一個臨時表（注意臨時表是沒有索引的）中，然后再重新讀出臨時表數據來完成 UNION 查詢。

MySQL 關聯執行策略很簡單，對于任何關聯都執行嵌套循環關聯操作，即先從一個表讀出數據，然后嵌套循環到下一個表中取出匹配的行，依次下去，直到找到所有的表中匹配的行為止。然后根據各個表匹配的行，返回查詢中需要的各個列。MySQL 會嘗試在最后一個關聯表中找到所有匹配的行，如果最后一個關聯表無法找到更多的行后，MySQL 返回到上一層次關聯表，看是否能找到更多的匹配記錄，依次類推迭代查詢。

關聯查詢優化器

MySQL 優化器決定了多個表關聯的順序，關聯優化器可以選擇一個代價最小的關聯順序。

有時候優化器選擇的不是最優的順序，這時候可以使用 STRAUGHT_JOIN 關鍵字進行查詢，讓優化器按照你認為最優的順序查詢，但是一般來說人判斷的都沒有優化器好。

優化器會嘗試在所有的順序中選擇一個成本最小的關聯順序，但是當表非常多的時候，比如有 n 張表進行關聯，就要進行 n！ 次比較。當表超過 optimizer_search_depth 的時候，就會選擇貪婪搜索模式了。

MySQL 查詢優化器限制

子查詢

MySQL 的子查詢優化的相當糟糕，最糟糕的一類是子查詢中 WHERE 條件包含了 IN() 的子查詢。比如用下面的語句查詢

SELECT * FROM film WHERE film.id in (SELECT file_id from film_actor WHERE actor_id = 1)

我們可能會認為 MySQL 會執行后面的語句選擇出 id 后才執行前面的查詢，但是 MySQL 會將外層查詢壓入子查詢中

SELECT * FORM film WHERE EXISTS(SELECT * FROM film_actor WHERE actor_id = 1 AND film_actor.film.id = film.id)

這個查詢會對 film 進行全表掃描，性能非常糟糕。

所以我們最好用聯合查詢來代替這個查詢。

這個問題直到 MySQL 5.5 還存在，MySQL 另外一個分支 MariaDB 在原有的基礎上做了大量的改進，例如這里帶 IN 的子查詢。

當一個查詢能被寫成子查詢和聯合查詢的時候，最好通過一些測試來判斷哪個寫法更快一些

UNION

有時候 MySQL 無法將閑置條件由外層推到內層，這使得本能限制掃描行數的 LIMIT 在內層查詢中不起作用。

如果希望 UNION 的各個子句能根據 LIMIT 只取出部分結果集，或者希望能先排好序再分別使用這些子句，那么需要分別對這些查詢使用 LIMIT 和 ORDER BY。

(SELECT * FROM XXX LIMIT 20) UNION ALL (SELECT * FROM XXX LIMIT 20)

并發執行

MySQL 無法利用多核特性來并發執行查詢。

最大值和最小值

對于 MIN 和 MAX 查詢，MySQL 的優化做的不是很好，

SELECT MIN(id) FROM actor

因為 id 是遞增的，所以只需要掃描一行即可，但是 MySQL 仍然會做全表掃描。可以改下面的寫法

SELECT id FROM actor LIMIT 1

特定優化查詢

一般來說，使用 Percona Toolkit 中的 pt-query-advisor 能夠解析查詢日志，分析查詢模式，然后給出詳細的建議來幫助你優化 SQL 語句。

優化 COUNT 查詢

當 COUNT 的值不可能為空的時候，MySQL 會轉向統計行數。如果我們想要統計行數的時候，最好直接使用 COUNT(*)。

使用近似值

有時候某些業務不需要精確值，此時可以用近似值來代替，EXPLAIN 出來的優化器估算的行數就是一個不錯的近似值，執行 EXPLAIN 不需要去真正的執行查詢，效率高很多。

優化關聯查詢

• 確保 ON 或者 USING 上的列有索引，在創建索引的時候需要考慮到關聯列的順序，比如說表 A，B 用列 c 進行關聯的時候，如果優化器的關聯順序是 B，A，則只需要在 A 上建立索引即可。
• 確保任何的 GROUP BY 和 ORDER BY 只涉及到一個表中的列

優化子查詢

關于子查詢給出的最主要的優化方法是：盡量使用關聯查詢代替子查詢，因為 MySQL 的子查詢優化的非常爛。不過這條意見只在舊版本有用，在 MySQL 5.6 以上和 MariaDB 中，可以忽略掉這條優化。

優化 GROUP BY 和 DISTINCT

MySQL 經常用同樣的方法來優化這兩個查詢，它們都會用索引來優化，這也是最有效的優化辦法。

當無法使用索引的時候，MySQL 會用臨時表或者文件排序來執行 GROUP BY。

如果需要對關聯查詢做分組，那么通常采用標識列來進行分組效率會比較高。

優化 LIMIT 分頁

當系統需要進行分頁操作的時候通常會使用 LIMIT 加 偏移量的操作，同時加上合適的 ORDER BY 語句。如果有對應的索引，效率通常會不錯。

但是當偏移量非常大的時候，LIMIT 10000,20，這種語句會導致掃描了10020 行，但是只返回 20 行。

優化這種查詢的方法有：

• 使用索引覆蓋，只搜索索引覆蓋的行然后通過一次查詢把所有需要的數據查找出來
• 通過延遲關聯，后面會討論這個方法

優化 SQL_CALC_FOUND_ROWS

分頁的時候有時候會通過在 LIMIT 語句中加上 SQL_CALC_FOUND_ROWS。這樣就可以獲取去掉 LIMIT 條件后查詢的行數，加上這個提示以后，不管是否需要，都會把全部的行都掃描一遍，而不是在滿足了 LIMIT 的大小后停止掃描，這樣會帶來很大開銷。

解決這個問題有兩個方法

• 采用 EXPLAIN ROW 中的近似值，有時候不需要那么精準的數據
• 先獲得比較多的緩存集，比如設置一個 100 頁和一個 100 頁以后的按鈕，當用戶需要 100 頁后的按鈕再去獲取。

優化 UNION 查詢

除非確實需要服務器消除重復的行，否則必須要使用 UNION ALL。

如果沒有 ALL 關鍵字，MySQL 會給臨時表加上 DISTINCT 選項，然后做一次查重操作，這將帶來極大的開銷。

轉載于:https://www.cnblogs.com/zjmeow/p/10104769.html

總結

以上是生活随笔為你收集整理的读薄《高性能MySql》(四)查询性能优化的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。