《高性能MySQL》讀書筆記（下）

目錄

Mysql查詢性能的優化

慢查詢基礎

優化資料通路

是否向資料庫請求了不需要的資料

查詢了不需要的記錄

多表聯查中傳回全部列

MySQL是否在掃描額外的記錄

重寫查詢的方式

切分查詢（重點）

分解連接配接查詢（重點）

MySQL如何執行聯接查詢

查詢優化器

排序優化（重點）

MySQL查詢優化器的局限性

優化特定類型的查詢

優化count()查詢（重點）

優化聯接查詢

優化大量資料的limit分頁（重點）

優化union查詢（重點）

Mysql查詢性能的優化

首先要清楚為什麼查詢速度會慢？如果我們把一個查詢看作一個任務，那麼這個任務它就是由一系列子任務組成，每個子任務在執行的 時候都會消耗一定的時間，如果要優化查詢的執行效率，實際上就是要優化其子任務，要麼是消除一些子任務，要麼減少子任務的執行次數，要麼讓子任務執行更快。

簡單的來說，一個查詢的生命周期如下：從用戶端到伺服器，然後在伺服器上進行文法解析，生成執行計劃，執行，并給用戶端傳回結果。其中執行可以被認為是整個生命周期中最重要的階段，其中包含了大量為了檢索資料對存儲引擎的調用以及調用後的資料處理，包含排序，分組等。

在每一次消耗大量時間的查詢案例中，我們都可以看到一些不必要的操作，比如某些操作被額外地執行重複了很多次，某些操作執行得太慢等，優化查詢的目的就是消除這些操作花費的時間。

慢查詢基礎

優化資料通路

一條查詢語句，如果性能很差，最常見的原因就是通路的資料太多，對于低效的查詢，我們通常可以從下面兩個步驟來進行分析：

• 确認應用程式是否在檢索大量且不必要的資料（後面會講怎麼判斷）。這通常意味着通路了太多行，但有時候也可能是通路了太多的列。
• 确認MySQL伺服器是否在分析（比如：排序，分組）大量不需要的資料行。

是否向資料庫請求了不需要的資料

一些查詢會請求超過實際需要的資料，然後這些多餘的資料會被應用程式丢棄，并且這個過程會增加網絡開銷，同時這也會消耗應用伺服器的CPU和記憶體資源。

查詢了不需要的記錄

MySQL查詢是會先傳回全部的結果集然後再進行計算，比如，一條查詢語句查詢出100條資料，但是隻需要在頁面上顯示前10條資料，實際情況來說，MySQL會先查詢出這100條資料，然後把這100條資料全部傳回給用戶端，然後再抛棄其中的大部分資料。

多表聯查中傳回全部列

能不要select * 就不要select * 。需要什麼就查詢什麼列。如果取出全部列，會讓優化器無法完成索引覆寫掃描這類優化，還會為伺服器帶來額外的IO，記憶體和CPU消耗。

MySQL是否在掃描額外的記錄

在确定查詢隻傳回需要的資料後，接下來就需要看看查詢為了傳回我們需要的結果是否掃描了過多的資料，對于MySQL來說，最簡單衡量查詢開銷的三個名額如下：

• 響應時間（具體的響應時間難以估計，隻能通過一些經驗方法大概估計，這裡不再贅述）
• 掃描的行數
• 傳回的行數

這三個名額會被記錄到MySQL的慢日志中。需要注意的是：掃描的行數與傳回的行數的比率通常很低，一般是在1:1或者是1:10。

這個type的值，除了All和const之外的其他種類都是走索引的，all表示全表掃描，const表示的是常量查詢。

一般來說，MySQL能夠使用如下三種方式應用where條件，從好到壞如下：

1. 在索引中使用where條件來過濾不比對的記錄。這是在存儲引擎中完成的。
2. 使用索引覆寫（在Extra列中出現了Using index）來傳回記錄，直接從索引中過濾不需要的記錄并傳回命中的結果。這是在MySQL伺服器層完成的，不需要再進行回表操作。
3. 從資料表中傳回資料，然後過濾不滿足條件的記錄（在Extra列中出現了Using where）。這在MySQL伺服器層完成，MySQL需要從表中讀取資料然後再進行過濾。

重寫查詢的方式

設計查詢的時候，一個需要考慮的重要問題是，是否需要将一個複雜的查詢分成多個簡單的查詢。MySQL從設計上讓連接配接和斷開連接配接都是非常輕量的，傳回一個小的查詢結果是很高效的，而且現代的網絡速度比以前要快很多，能在很大程度上降低延遲，是以運作多個小的查詢現在已經不是大問題了。在其他條件都相同的情況下，使用盡可能少的查詢當然是更好的。但是有時候，将一個大的查詢分解為多個小的查詢是很有必要的。

切分查詢（重點）

有時候對于一個大查詢，我們需要将大查詢切分成小查詢，每個查詢的功能完全一樣，隻完成一小部分，每次隻傳回一小部分查詢結果。最常見的案例就是：删除舊的資料，比如需要定期清楚大量資料時，如果用一個大的語句一次性完成的話，則可能需要一次鎖住很多資料，占滿整個事務日志，耗盡系統資源，阻塞很多小的但重要的查詢。此時我們可以将一個大的delete 語句切分成多個較小的查詢（通過使用limit來限制每一次處理的資料量，然後通過程式中的循環來進行控制），可以盡可能小的影響MySQL的性能。

-- 比如需要每個月運作下面的SQL語句 data_sub()是一個函數,可以通過時間偏移量來進行運算，now()傳回配置的時區的目前日期和時間，Interval用于添加和減去日期和時間值
delete from TableA where created < data_sub(now(),3 month);

-- 可以把上面的邏輯改寫成如下
rows_affected = 0;
do{
 -- do_query是一個執行SQL語句的方法 
 rows_affected = do_query(
 "delete from TableA where created < data_sub(now(),3 month) limit 10000")
}while rows_affected > 0;
           

這樣删除資料還有一個好處：将伺服器上原本一次性的壓力分散到一個很長的時間段中，可以大大減低對伺服器的影響，還可以大大的減少删除時鎖的持有時間。

分解連接配接查詢（重點）

如果多張表的連接配接查詢速度非常的慢，我們可以對每一個表進行一次單表查詢，然後将結果在應用程式（比如在java代碼中進行多次的單表查詢，自己在實習的時候就這樣幹過）中進行連接配接。使用多個單表查詢的優勢如下：

• 可以讓緩存的效率更加高
• 将查詢分解後，執行單個查詢可以減少鎖的競争。
• 可以減少對備援記錄的通路。因為在應用程式中（代碼中）做連接配接查詢，意味着對某條記錄的通路隻需要查詢一次，而在資料庫進行連接配接查詢的話，則可能需要重複發通路一些資料。
• 而且在對多個值進行查詢的時候，我們可以控制其通路順序，比如在in()中使用順序讀來通路MySQL中的資料，這比随機讀的效率要高很多。
• 在應用程式中進行聯接處理可以更加容易的對資料進行拆分，可以更加容易的擴充程式。

注意：并不是所有的聯接查詢都需要進行拆分的，不要為了拆分而進行拆分，下面的一些場景使用在應用程式中進行聯接查詢可能速度更加快：

• 可以利用緩存和重用之前的查詢結果時
• 當能夠使用in（）清單代替聯接查詢大型表時
• 當一次查詢中多次引用同一張表時
• 在多台伺服器分發資料的時候

注意：即便在使用聯接查詢，如果聯接查詢是同名字段作為聯接條件，那麼使用using（）函數進行聯接效果比on的效果更加好。比如Using(id) <=> on A.id = B.id。（因為在同樣的聯接查詢語句中，使用on來進行連接配接比使用using進行連接配接要多出幾次聯接字段的掃描，比如使用on聯接id，可能id會在執行計劃中出現兩次，但是如果使用using來進行id的聯接，那麼id在執行計劃中隻會出現一次）

MySQL如何執行聯接查詢

（這裡涉及到一個重要的概念：臨時表）

對于union查詢，MySQL會先将一系列的單個查詢結果放到一個臨時表中，然後再嵌套循環到下一個表中尋找比對的行，依次下去，直到找到所有表中比對的行為止。最後根據各個表比對的行，傳回查詢中需要的各個列。

 查詢執行的基礎（大概）：

1. 用戶端給伺服器發生一條SQL語句。
2. 伺服器端進行SQL語句的解析，預處理，然後再由優化器生成對應的執行計劃。
3. MySQL根據優化器生成的執行計劃，調用儲存引擎的API來執行查詢。
4. 将查詢的結果傳回給用戶端。

MySQL的用戶端和伺服器之間的通信協定是“半雙工”的，這意味着，任何時刻，要麼是由伺服器向用戶端發送資料，要麼是用戶端向伺服器發送資料，這兩個動作是不能同時發生的。這種模式的通信也意味着，一旦一端開始發送消息了，另一端要接收完整個消息才會響應它，同時也意味着MySQL是沒法進行流量控制的。

是以在查詢的時候，如果我們隻是想擷取查詢結果中的前幾條資料或者是後幾條資料，那麼此時最好的做法就是使用【limit】來進行限制，否則我們可能隻是需要10條資料，但是由于沒有使用limit進行限制，那麼MySQL伺服器是會把所有的查詢結果都傳回給用戶端的。

查詢優化器

下面這個語句可以用來查詢目前會話的一個大概成本  查詢結果中的value值表示的時候執行上面的SQL語句大概需要做value個資料頁的随機查找才能完成上面需要的查詢，這個得到的value中隻是一個簡單的參考值，因為優化器不會考慮緩存帶來的影響，而且此時的MySQL也不知道哪些資料在記憶體，哪些資料在磁盤等。

show status like 'Last_query_cost';
           

下面是MySQL優化器能夠處理的一些優化類型：

1. 重新定義聯接表的順序
2. 将外聯接轉換成内聯接
3. 用代數等價變化規則進行替換
4. 優化count（），Min（），MAX（），因為最值一般是索引中的最右邊或者是最左邊一列的值，是以MySQL在優化器的時候會把這個最值當做常數來進行處理。
5. 預估并且轉化為常數表達式，比如在對主鍵列進行where條件的通路，那麼優化器就會知道這個值已經是确定的，那麼通路的類型就會轉化為const（在explain解析中的type列也可以看到其值為const）。
6. 覆寫索引掃描，當索引中的列包含所有查詢中需要使用的列的時候，MySQL就可以使用索引傳回需要的資料，而無須查詢對應的資料行（就是不需要進行回表了）；
7. 子查詢優化
8. 提前終止查詢。比如，早就已經查詢到需要的結果了，那麼MySQL會立刻終止查詢，一個典型的案例就是limit查詢。
9. 等值傳播
10. 清單in()的比較。在其他資料庫中in()和or幾乎是等效的，但是在MySQL這裡不一樣，因為MySQL将in（）清單中的資料先進行排序，然後通過二分查找的方式來确定清單中的值是否滿足條件，這是一個o(logn)的複雜度，等價轉化為OR查詢的話複雜度變成了O(n)，是以在對于清單中有大量取值的時候，MySQL使用in()的效率會更加高。

排序優化（重點）

排序本身就是一個成本很高的操作，是以從性能角度考慮，應該盡可能的避免排序或者是盡可能避免對大量資料進行排序。

當不能使用索引生成排序結果的時候，MySQL需要自己進行排序，如果資料小于‘’排序緩沖區“則在記憶體中進行快速排序，如果資料量大于‘’排序緩沖區“，那麼MySQL會先對資料進行分塊，對每個獨立的塊使用”快速排序“進行排序，并将各個塊的排序結果存放在磁盤上，然後将各個排好序的塊進行合并，最後傳回排序結果，這一個過程統稱為檔案排序。

在聯接查詢的時候如果需要進行排序，MySQL會分兩種情況來處理：

• 如果order by子句中的所有列都是來自聯接的第一個表，那麼MySQL在聯接處理第一個表的時候就會進行檔案排序，這時就可以在explain中看到extra列中有using filesort；
• 除此之外的所有情況，MySQL都會先将聯接的結果存儲到一個臨時表中，然後在所有聯接都結束後，再進行檔案排序，這時就可以在explain中看到extra列中有“using temporary；using filesort"；如果查詢語句中有limit的話，limit也會在檔案排序之後進行應用。

MySQL查詢優化器的局限性

union的限制：MySQL無法将限制條件從union外層下推到内層。

如果你希望union的各個子句能夠根據limit隻取部分結果集，或者是希望先排好序再合并結果集的話，就需要在union的各個子句分别使用這些子句。案例：你想要将兩個子查詢的結果聯合起來，然後再取前20條記錄。（這個需求：MySQL會将兩個表存放到同一個臨時表中，然後再取出前20行的記錄），下面将展示兩種寫法：

(select first_name,last_name from actor order by last_name)
union all
(select first_name,last_name from customer order by last_name)
limit 20;
這種寫法會先把actor表中的記錄和customer表中的記錄存放到一個臨時表中，然後再從臨時表中取出前20條資料。假設兩張連接配接表中都有1000條資料，那麼此時這個臨時表就會有2000條左右的資料。
           

(select first_name,last_name from actor order by last_name limit 20)
union all
(select first_name,last_name from customer order by last_name limit 20)
limit 20;
如果是按照這種寫法，那麼臨時表中隻會存在40條資料，大大的減少了不必要的資料的掃描，這裡需要注意一下，從臨時表中取出的資料并不是一定的，如果想要擷取到正确的順序，那麼還需要在limit之前加一個全局的排序操作。
           

優化特定類型的查詢

優化count()查詢（重點）

首先我們要先了解一下count（）這個函數的作用：

• 統計某列的值的數量，也可以統計行數；count函數中可以是列名也可以是列的表達式。

在統計列值時不統計null，比如 select count(od.setmeal_id) from order_detail od 輸出的結果就是2；也就是說如果在count（）的括号中指定了列或者是列的表達式，那麼統計的就是這個表達式有值的結果數。

• count（）還可以統計【結果集】的【行】數；當MySQL确認括号内的表達式不為空時，實際上就是在統計行數。比如當我們使用

  count（*）時，這種情況下通配符 * 不會去統計所有的列，它會忽略所有列而直接去統計滿足結果集的行數。

-- 統計輸出結果是11 為這張表的所有資料
select count(*) from order_detail od 

-- 統計輸出結果為3 為結果集的行數
select count(*) from order_detail od  where od.order_id  ='1522581871770824706'
           

簡單優化count（）：

案例：如何在一個查詢中統計同一列的不同值的數量。（這個是比較常見的案例），比如統計價格大于500的數量和價格小于100的數量：

下面兩條 SQL語句的結果是一樣的；

select sum(if(od.amount > 500,1,0)) as expensive_goods ,sum(if(od.amount < 100,1,0)) as fair_goods
from order_detail od 

select count(od.amount > 500 or null) as expensive_goods, count(od.amount < 100 or null) as fair_goods
from order_detail od 
           

需要注意的是，在進行統計的時候，如果使用的是sum函數，那麼條件為真就是進行加一，條件為加就是進行加0，而使用count函數來進行這樣的統計的話，條件為真的不需要進行處理，條件為假需要對其設定為null，是以後面的or null是一定要加的。

優化聯接查詢

注意的是，MySQL的查詢優化器會幫我們調整聯接查詢的表的順序；通常在進行多表聯查的時候，可以有多種不同的聯接順序來獲得相同的執行結果，MySQL的聯查優化器通過評估不同順序時的聯查成本來選擇一個成本最低的聯接順序。一句話概括就是：小表驅動大表（小表作為驅動表，表中的每條資料隻查詢一次，而被驅動表中的資料會被多次重複查詢）。這樣可以讓查詢進行更少的回溯和重讀操作。如果你不想使用MySQL優化器提供的順序，那麼可以使用straight_join關鍵字來重寫查詢。

• 確定on或者是using子句中的列有索引。在建立索引的時候就要考慮到連接配接的順序。當A表和B表通過c列來進行聯接的時候，如果優化器的聯接順序是B,A ，那麼就不需要在B表的對應列建立索引。沒用用到的索引隻會帶來額外的負擔。一般來說，隻需要在聯接順序（這裡的聯接順序指的是優化器的聯接順序）中的第二個表的相應列建立索引。
• 確定任何group by和order by中的表達式隻涉及到一個表中的列，這樣MySQL才可能使用索引來優化這一個過程。

優化大量資料的limit分頁（重點）

在偏移量很大的時候，比如limit 10000，20，這是MySQL需要查詢10020條資料最後隻傳回20條資料，前面的10000條記錄都将被抛棄，這樣代價非常高。如果這個表的資料非常大，那麼我們可以盡可能的使用索引覆寫來進行掃描，而不是查詢全部的行。

-- 一般的查詢
select film.film_id,film.description from sakila.film order by title limit 50,5;

-- 優化後的查詢  通過延遲連接配接來進行優化
select film.film_id,film.description from sakila.film
inner join (
    select film.film_id from sakila.film order by title limit 50,5;
)as lim using(film_id);
           

優化之後的SQL查詢之是以有效，是因為它允許伺服器在不通路行的情況下檢查索引中盡可能少的資料。（裡面的子查詢隻是查詢了一個film_id，這樣可以直接走索引進行查找資料）然後，一旦找到需要的行，就将它們與整個表聯接，以從該行中檢索其他我們需要的列。

優化union查詢（重點）

MySQL總是通過建立并填充臨時表的方式來執行union查詢，如果你不需要MySQL幫我們消除重複的行，那麼一定要使用union all來進行連接配接查詢。如果沒有all關鍵字，MySQL會給臨時表加上distinct選型，這會導緻對整個臨時表的資料進行去重檢查。雖然即便有all關鍵字，MySQL仍然會使用到臨時表來存儲結果。

UNION 語句：用于将不同表中相同列中查詢的資料展示出來；（不包括重複資料）

UNION ALL 語句：用于将不同表中相同列中查詢的資料展示出來；（包括重複資料）