發現的一些問題

• 問題1

在過去的半年時間里，研發團隊內部嘗試抓了一波兒慢查詢SQL跟進處理率。發現有些同學對于慢查詢處理的思路就是看看有沒有用到索引，沒有用到就試圖加一個，實在不行就甩鍋給這種情況是歷史設計問題或者自行判定為用戶特殊操作下觸發的小概率事件，隨即便申請豁免掉...   其實問題沒有根本上解決。

• 問題2

還有就是網絡上經常可以看到一些類似這樣的文章：

“慢SQL性能優化大全”

“慢SQL性能優化看這篇就夠了”...  

其實內容大同小異，要么建議加索引，要么建議重寫SQL....

怎么說呢？知識點是對的，但不全面，這個很容易誤導新同學，哈哈哈。

本文初衷

在業務項目發展過程中，我們常常會面對要處理 MySQL 慢查詢問題，那我們應該如何分析解決問題呢？

部分同學在處理MySQL慢查詢時候主要思路是加索引來解決，確實加索引是一個很好的解決問題的手段，但不是全部。既然慢查詢作為問題，那就需要明確問題發生原因，和解決問題路徑分析， 授人以魚不如授人以漁，讓我們一起來解鎖 ?? 下MySQL處理慢查詢的正確姿勢。

本文計劃主要讓大家搞明白查詢SQL為什么會變慢，廢話不多說，直接開干~

寫在前面

在業務項目發展過程中，我們常常會面對要處理 MySQL 慢查詢問題，那我們應該如何分析解決問題呢？

部分同學在處理MySQL慢查詢時候主要思路是加索引來解決，確實加索引是一個很好的解決問題的手段，但不是全部。既然慢查詢是問題，那就需要明確問題發生原因，和解決問題路徑分析。我們一起來get下MySQL慢查詢的正確姿勢。

一、查詢SQL執行到底經歷了什么？

首先需要明確：一個查詢SQL的執行到底經歷了什么？

圖片

數據庫執行SQL的大致流程如下：

• 建立與MySQL服務器連接（基礎）
• 客戶端發送查詢SQL到數據庫，數據庫驗證是否有執行的權限
• MySQL服務器先檢查查詢緩存，如果命中了緩存，則立即返回存儲在緩存中的結果，否則繼續流轉；
• MySQL服務器語法解析器，進行詞法與語法分析，預處理
• 流轉至查詢優化器生成執行計劃
• 根據生成的執行計劃，調用存儲引擎暴露的API來執行查詢
• 將查詢執行結果返回給客戶端
• 關閉MySQL連接

具體執行過程可能會因MySQL服務器具體配置和執行場景有一些差異。

1）如未開啟應用查詢緩存，則直接忽略查詢緩存的檢查；

2）執行過程中，如同時對于被掃描的行可能加鎖，同時也可能會被其他sql阻塞

二、查詢SQL為什么會慢？

我們可以把查詢SQL執行看做是一個任務的話，那它是由一些列子任務組成的，每個子任務都存在一定的時間消耗。通常情況下，導致慢查詢最根本的問題就是需要訪問的數據太多，導致查詢不可避免的需要篩選大量的數據。

面對慢查詢，我們需要注意以下兩點：

1）查詢了過多不需要的數據

2）掃描了額外的記錄

2.1 查詢了過多不需要的數據

MySQL并不是只返回需要的數據，實際上會返回全部結果集再進行計算。

尤其是多表關聯查詢 select * 的情況，我們是不是真的需要全部的列呢？如果不是，那我們直接指定對應字段就好了。

例如我們要查詢用戶關聯訂單下的商品信息，如下所示：

SELECT *
FROM users
  LEFT JOIN orders ON orders.user_id = users.user_id
  LEFT JOIN goods ON goods.good_id = orders.good_id
WHERE users.name = 'zhangsan';

這將返回三個表的全部數據列，可以調整為僅取需要的列：

SELECT goods.title, goods.description
FROM users
  LEFT JOIN orders ON orders.user_id = users.user_id
  LEFT JOIN goods ON goods.good_id = orders.good_id
WHERE users.name = 'zhangsan';

取出全部列，會讓優化器無法完成索引覆蓋掃描這類優化，還會為服務器帶來額外的I/O、內存和CPU的消耗。

2.2 掃描了額外的記錄

此種情況大部分屬于索引應用不當造成的（包括：該建的索引沒有建，或者未應用到最佳索引）。

示例表結構如下：

CREATE TABLE `test_table` (
  `name` varchar(32) DEFAULT NULL,
  `desc` varchar(32) DEFAULT NULL,
  `age` int(16) DEFAULT NULL,
  `id` bigint(11) DEFAULT NULL,
  KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

存在索引 `idx_age` 的情況下，查詢執行計劃如下：

EXPLAIN SELECT * FROM test_table WHERE age = 10;

圖片

預估訪問1行數據即可命中數據，如刪除有效索引 `idx_age` 后則會變成全表掃描（ALL），預估需要掃描121524條記錄才能完成這個查詢，如下圖所示：

圖片

三、如何定位問題呢？

通過梳理 MySQL中的 SQL執行過程我們發現，任何流程的執行都存在其執行環境和規則，主要導致慢查詢最根本的問題就是需要訪問的數據太多，導致查詢不可避免的需要篩選大量的數據。

如果將MySQL慢查詢作為一個問題來拆解分析的話，以上內容算是問題分析，那接下來開始問題定位和問題解決。

圖片

發現了慢查詢之后，關于如何定位問題發生原因，最常用的方法就是利用EXPLAIN關鍵字模擬查詢優化器執行查詢SQL，從而知道MySQL是如何處理你的查詢SQL，通過執行計劃來分析性能瓶頸。

通常我們使用EXPLAIN，會得到如下下的執行計劃信息：

圖片

關于各字段含義，大家可以通過檢索自行了解，在此就不再過多贅述。

關于定位分析問題，關鍵看如下幾點：

1）select_type

表示查詢類型，用于區別普通查詢、聯合查詢、子查詢等復雜查詢。

2）type

顯示查詢使用類型，從好到差依次為：system > const > eq_ref > ref > range > index > all

3）possible_keys 和 key

分別指可能應用的索引和實際應用的索引。

注意：查詢中若使用了覆蓋索引（select 后要查詢的字段剛好和創建的索引字段完全相同），則該索引僅出現在key列表中。

4）rows

大致估算出找到所需記錄所需要讀取的行數（從效率上來講，數值越小越好）

5）Extra

重要的額外信息。包含MySQL解決查詢的詳細信息，也是關鍵參考項之一。

四、幾種實用解決方案

我們通過EXPLAIN關鍵字模擬查詢優化器執行查詢SQL，發現了慢查詢問題原因，那看看如何才能有效解決呢？

推進幾種較為實用的解決方案給大家。

4.1 優化數據結構

4.1.1 選擇索引的數據類型

MySQL支持很多數據類型，選擇合適的數據類型存儲數據對性能有很大的影響。

通常來說，可以遵循以下一些指導原則：

(1)越小的數據類型通常更好：越小的數據類型通常在磁盤、內存和CPU緩存中都需要更少的空間，處理起來更快。

(2)簡單的數據類型更好：整型數據比起字符，處理開銷更小，因為字符串的比較更復雜。在MySQL中，應該用內置的日期和時間數據類型，而不是用字符串來存儲時間；以及用整型數據類型存儲IP地址。

(3)盡量避免NULL：應該指定列為NOT NULL，除非你想存儲NULL。在MySQL中，含有空值的列很難進行查詢優化，因為它們使得索引、索引的統計信息以及比較運算更加復雜。你應該用0、一個特殊的值或者一個空串代替空值。

4.1.2 范式與反范式

• 范式化

范式化模型要求滿足下面三大范式：

1）數據庫表中每個字段只包含最小的信息屬性，不能再進行細化分解；

2）（在滿足1的基礎上）模型含有主鍵，非主鍵字段依賴主鍵；

比如用戶這個模型，它的主鍵是用戶ID，那么用戶模型其它字段都應該依賴于用戶ID。；

如商品ID和用戶沒有直接關系，則這個屬性不應該放到用戶模型而應該放到“用戶-商品”中間表。

3）（在滿足2的基礎上）模型非主鍵字段不能相互依賴。

訂單表(訂單編號，訂購日期，顧客編號，顧客姓名，……)；

初看該表沒有問題，滿足第二范式，每列都和主鍵列”訂單編號”相關。

再細看你會發現“顧客姓名”和“顧客編號”相關，“顧客編號”和“訂單編號”又相關，最后經過傳遞依賴，“顧客姓名”也和“訂單編號”相關。

為了滿足第三范式，應去掉“顧客姓名”列，放入客戶表中。

• 反范式化

反范式化模型即不滿足范式化的模型。主要是為了性能和效率的考慮適當的違反范式化設計要求，允許存在少量的數據冗余，即以空間換時間。

4.1.3 小結

可見一個良好而實用的數據模型往往是依賴于具體的需求場景的，在設計數據模型之前，仔細分析需求場景，不僅能提高效率，也能有效規避后期可能遇到的一些意外麻煩。

范式化設計和反范式化設計的優劣對比如下：

1、范式化可以盡量的減少數據冗余；

2、范式化的更新操作比反范式化更快；

3、范式化的表通常比反范式化的表要小；

4、反范式化減少表的關聯；

5、反范式化相比范式化可以更好的對索引進行優化，例如使用覆蓋索引。

關于數據庫范式與反范式設計，詳情可參考我之前的一篇文章：數據庫范式與反范式設計，是一門藝術。

4.2 應用索引策略

索引（MySQL中也被稱為“鍵Key”），是存儲引擎用于快速找到記錄的一種數據結構。索引對于良好的性能非常關鍵，尤其當表中的數據量越來越大時，索引對性能的影響愈發重要（不恰當的索引對會隨數據量增大時，性能急劇下降）。

舉例如下情況：

假設數據庫中一個表有10^6條記錄，DBMS的頁面大小為4K（約可存儲100條記錄）。

如果沒有索引，查詢將對整個表進行掃描，最壞的情況下，如果所有數據頁都不在內存，需要讀取10^4個頁面，如果這10^4個頁面在磁盤上隨機分布，需要進行10^4次I/O，假設磁盤每次I/O時間為10ms(忽略數據傳輸時間)，則總共需要100s(但實際上要好很多很多)。

如果對之建立B-Tree索引，則只需要進行log100(10^6)=3次頁面讀取，最壞情況下耗時30ms。這就是索引帶來的效果。

了解了索引的優點之后，其實正確的創建和使用索引是實現高性能查詢的基礎。

可以利用B-Tree索引進行全關鍵字、關鍵字范圍和關鍵字前綴查詢，當然，如果想使用索引，必須保證按索引的最左邊前綴(leftmost prefix of the index)來進行查詢。

4.2.1 最左邊前綴主要規則

• 匹配全值(Match the full value)：對索引中的所有列都指定具體的值。例如，上圖中索引可以幫助你查找出生于1960-01-01的Cuba Allen。
• 匹配最左前綴(Match a leftmost prefix)：你可以利用索引查找last name為Allen的人，僅僅使用索引中的第1列。
• 匹配列前綴(Match a column prefix)：例如，你可以利用索引查找last name以J開始的人，這僅僅使用索引中的第1列。
• 匹配值的范圍查詢(Match a range of values)：可以利用索引查找last name在Allen和Barrymore之間的人，僅僅使用索引中第1列。
• 匹配部分精確而其它部分進行范圍匹配(Match one part exactly and match a range on another part)：可以利用索引查找last name為Allen，而first name以字母K開始的人。
• 僅對索引進行查詢(Index-only queries)：如果查詢的列都位于索引中，則不需要讀取元組的值。

由于B-樹中的節點都是順序存儲的，所以可以利用索引進行查找(找某些值)，也可以對查詢結果進行ORDER BY。

當然，使用B-tree索引有以下一些限制：

• 查詢必須從索引的最左邊的列開始。關于這點已經提了很多遍了。例如你不能利用索引查找在某一天出生的人。
• 不能跳過某一索引列。例如，你不能利用索引查找last name為Smith且出生于某一天的人。
• 存儲引擎不能使用索引中范圍條件右邊的列。例如，如果你的查詢語句為WHERE lastname="Smith" AND firstname LIKE 'J%' AND dob='1976-12-23'，則該查詢只會使用索引中的前兩列，因為LIKE是范圍查詢。

4.2.2 聚簇索引

聚簇索引保證關鍵字的值相近的元組存儲的物理位置也相同（所以字符串類型不宜建立聚簇索引，特別是隨機字符串，會使得系統進行大量的移動操作），且一個表只能有一個聚簇索引。因為由存儲引擎實現索引，所以，并不是所有的引擎都支持聚簇索引。目前，只有solidDB和InnoDB支持。

InnoDB對主鍵建立聚簇索引。如果你不指定主鍵，InnoDB會用一個具有唯一且非空值的索引來代替。如果不存在這樣的索引，InnoDB會定義一個隱藏的主鍵，然后對其建立聚簇索引。

4.3 查詢緩存

MySQL查詢緩存會保存查詢返回的完整結果。當查詢命中緩存，MySQL會立刻返回結果，而跳過了后續解析、優化以及執行階段，會有效提升查詢性能。

但是查詢緩存不是銀彈，它也會存在一些問題。

4.3.1 查詢緩存注意事項

1）緩存情況嚴格

存在一些不確定函數情況無法使用查詢緩存，如：NOW()、CURRENT_DATE() 等類似的函數；

超過 query_cache_size （設置查詢緩存空間大小）的查詢結果無法被緩存；

同時大小寫敏感，只有字符串相等情況下查詢SQL才使用相同緩存。

-- 不會使用同一個緩存
select name from users where id = 1;
SELECT name FROM users WHERE id = 1;

2）緩存易失效

假如緩存過查詢結果，但是由于查詢緩存設置內存不足，新緩存加入時MySQL會將某些緩存逐出，導致后續查詢未命中。同時數據結構及數據修改，內存不足，緩存碎片都會導致緩存失效。

4.3.2 小結

查詢緩存對應用程序完全透明，應用程序無需關心MySQL是通過查詢緩存返回的還是實際執行返回的結果。但隨著目前服務器性能越來越強，查詢緩存被發現是一個影響服務器擴展性的因素，它很可能成為整個服務器的資源競爭點，大家采用生產環境開啟應用時候一定要慎重考量。

4.4 重構查詢方式

優化慢查詢時候，我們可以轉換下思路，我們的目標是找到一個更優的方法獲取時間需要的結果，而不是一定從MySQL獲取一模一樣的結果集。重構查詢的技巧很有必要。

4.4.1 復雜查詢拆分

將一個復雜查詢拆分多個簡單查詢，考慮是否需要將一個復雜查詢拆分為多個簡單查詢。

實際開發過程中，大家往往會強調數據庫層完成盡可能多的工作，這樣做的初衷是認為網絡通信、查詢解析和優化是一件代價很高的事情，其實MySQL從設計上讓連接和斷開都很輕量級，同時在返回一個小查詢結果方面很高效。況且目前網絡速度也比之前快很多，無論是帶寬還是延遲。

對于大查詢我們要“分而治之”，將大查詢切分成多個小查詢。不過在一次查詢能夠勝任的情況下還拆成多個獨立查詢就不明智了。

例如：做數據庫做10次查詢，每次返回一行記錄。

4.4.2 分解關聯查詢

將關聯查詢進行分解，對每一個表進行一次單表查詢，然后將結果在應用程序中進行關聯。

例如：

SELECT *
FROM users
LEFT JOIN orders ON orders.user_id = users.user_id
LEFT JOIN goods ON goods.good_id = orders.good_id
WHERE users.name = 'zhangsan';

以上查詢可以分解成下面的查詢來代替：

SELECT * FROM users WHERE users.name = 'zhangsan';
SELECT * FROM orders WHERE orders.user_id = 103;
SELECT * FROM goods WHERE goods.good_id IN  （123, 456, 789）;

為什么要這樣做呢？看起來好像沒有什么好處，而且返回數據結果也是一致的。實際上利用分解查詢的方式來重構查詢有很大的優勢，主要表現為：

• 將查詢分解后，執行單個查詢可減少鎖的競爭；
• 應用層做關聯，更容易對數據庫進行拆分，更易于做到高性能和可擴展；
• 減少冗余記錄的查詢（在應用層做關聯，表示對某條記錄應用只需要查詢一次，而在數據庫中做關聯查詢，則可能需要重復訪問一部分數據。）

五、高性能查詢難題優化總結

如果將MySQL慢查詢作為一個問題來拆解分析的話，之前內容算是問題分析、問題定位和解決，那現在來收下尾，聊聊MySQL慢查詢問題解決經驗總結。

圖片

廢話不多說，直接開干~

我們來總結一下，應該如何處理高性能查詢難題？

假如把高性能查詢比作一個“難題”，它其實是包括多個子難題在內，共同作用的結果。

今天我們來歸納總結下，主要包括以下幾類：

5.1 數據結構優化

良好的schema設計原則是普遍適用的，但是MySQL有他自己的實現細節要注意，概況來講，盡可能保持任何東西小而簡單總是好的。

主要有以下簡單的原則值得你去考慮使用：

• 盡量避免過度設計
• 使用小而簡單的合適數據類型，盡可能避免使用null
• 盡量使用相同的數據類型存儲相似或者相關的值
• 注意可變長字符串，其在臨時表和排序時可能按最大長度分配內存
• 盡量使用整形定義標識符

5.2 索引設計優化

常見的B-Tree索引，按照順序存儲數據，所以MySQL可以用來做ORDER BY 和 GROUP BY操作。因為數據是有序的，所以便于將相關的列值都存儲在一起。由于索引中存儲了實際的列值，所以一些查詢只通過索引就能夠完成查詢（如：聚簇索引）。

根據索引的特性，總結索引的優點有如下幾點：

• 減少服務器需要掃描的數據量；
• 幫助服務器避免排序和臨時表；
• 將隨機I/O變為順序I/O。

編寫查詢語句時候應該注意盡可能選擇合適的索引，以避免單行查找，盡可能使用索引覆蓋。

根據執行計劃依次掃描相關表中的行，不在數據緩沖區的走IO存儲引擎掃描表的性能消耗參考下面的list，消耗從大到小：

全表掃描>全索引掃描>部分索引掃描>索引查找>唯一索引/主鍵查找>常量/null

5.3 應用查詢優化

應用查詢優化是建立在良好的數據結構和合理的索引設計之上的。

它主要包括以下幾種情況：

5.3.1 重構查詢方式

優化慢查詢時，目標應該是找到一個更優的方案來達到我們獲取結果數據的目的。其中可以存在多樣的權衡方案：

1）從數據庫中查詢計算直接獲取到結果數據；

2）拆分多條子查詢來逐步得到結果數據；

3）從數據庫獲取到基礎數據，然后應用代碼邏輯加工后獲得結果數據。

5.3.2 讓SQL盡量符合查詢優化器的執行要求

MySQL 查詢優化器并不是對所有查詢都適用的，我們可以通過改寫查詢 SQL 來讓數據庫更高效的完成工作。

常見查詢應用優化建議匯總如下：

1）對于任何查詢，應盡量避免全表掃描

    首先應考慮在 where 及 order by 涉及的列上建立并應用索引；

2）盡量避免在 where 子句中進行操作

    使用 or 來連接條件、對字段進行 null 值判斷、匹配查詢 '%abc%'、!= 或 <> 操作符，否則將導致引擎放棄使用索引而進行全表掃描；

    對字段進行表達式、函數操作，這將導致引擎放棄使用索引而進行全表掃描；

3）盡量應用索引

    使用索引字段作為條件時，如果是復合索引，那么必須使用到該索引中的第一個字段作為條件時才能保證系統使用該索引，否則該索引將不會被使用，并且應盡可能的讓字段順序與索引順序相一致；

4）索引字段要注意慎重選取

    索引盡量避開區分度不大的字段，如：sex、male、female

    這種五五開的索引列有大量數據重復時，那么即使在 sex 上建了索引也對查詢效率起不了作用。

5）一個表的索引數最好不要超過 6 個

    索引并不是越多越好，索引固然可以提高相應的 select 的效率，但同時也降低了 insert 及 update 的效率， 因為 insert 或 update 時有可能會重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。

6）盡量使用數字型字段

    若只含數值信息的字段盡量不要設計為字符型，這會降低查詢和連接的性能，并會增加存儲開銷。 這是因為引擎在處理查詢和連接時會逐個比較字符串中每一個字符，而對于數字型而言只需要比較一次就夠了。

7）盡量避免使用 *

    select * from table ，用具體的字段列表代替 *，不要返回用不到的任何字段，尤其是多表關聯查詢的情況。

MySQL v5.6版本以后，消除了很多MySQL原本的限制，讓更多的查詢能夠以盡可能高的效率完成。

5.4 小結

根據梳理 MySQL中的 SQL執行過程我們發現，任何流程的執行都存在其執行環境和規則，其實產生慢SQL的本質是：我們沒有按照數據庫的要求方式來執行SQL。

主要導致慢查詢最根本的問題就是需要訪問的數據太多，導致查詢不可避免的需要篩選大量的數據。

MySQL慢查詢問題細數起來，林林總總太多了，但行之有效的無外乎這幾種：

• 優化數據結構
• 應用索引策略
• 查詢緩存
• 重構查詢方式

良好的表結構設計是高性能查詢的基石，恰當的索引設計是高性能查詢的助推器，同時合理的查詢應用也是必不可少的。數據結構優化、索引設計優化及應用查詢優化猶如三叉戟一般，齊頭并進，在高性能查詢應用中缺一不可。

寫在最后

全文總結一下，其實就是我們要學會用數據庫的要求方式來執行SQL。

即要寫好應用查詢SQL，必須要結合良好的數據結構和合理的索引設計才可以。

其實MySQL查詢優化中的每一項拆開講都可以是很大的章節，在此主要是將解決問題的思路分享給大家，希望能對大家今后的工作中能有所幫助。