慢查詢 SQL 治理方案

一、SQL 性能下降的原因

在對 SQL 進行分析之前，需要明確可能導致 SQL 執行性能下降的原因進行分析，執行性能下降可以體現在很多方面：

1. 查詢語句寫的爛
2. 索引沒加好
3. 表數據過大
4. 數據庫連接數不夠
5. 查詢的數據量過大
6. 被其他慢sql拖累
7. 硬件配置過低
   …

二、慢查詢優化思路

1. 優先選擇優化高并發執行的 SQL，因為高并發的 SQL 發生問題帶來后果更嚴重。
2. 定位優化對象的性能瓶頸（在優化之前了解性能瓶頸在哪，IO、CPU、網絡帶寬）。
3. 明確優化目標。
4. 從 explain 執行計劃入手。
5. 永遠用小的結果集驅動大的結果集。
6. 盡可能在索引中完成排序。
7. 只獲取自己需要的列，不要使用 select *。
8. 只使用最有效的過濾條件。
9. 盡可能避免復雜的 join 和子查詢，可以將相關的連接組裝操作放到業務代碼中進行。
10. 合理設計并利用索引。
11. 考慮加一層緩存。
12. 如果業務允許，數據不太重要可以考慮寫腳本刪除歷史數據，或者對數據進行歸檔存進歷史表中。
13. 數據量模糊查詢或者特別復雜查詢可以考慮 ES。
14. 單表數據過大，數據庫連接不夠可以考慮分庫分表。
15. 如果業務允許，比如凌晨跑的定時腳本，造成的慢 SQL 可以忽略。
16. 優化硬件配置，比如將固態硬盤換成 SSD。
    …

1、索引

數據庫的查詢性能變慢，一般我們第一時間想到的就是增加索引或者優化索引。

什么情況需要添加索引？

以下情況我們可以考慮在表的字段上增加索引優化性能：

1. 查詢頻繁、更新較少的字段。
2. 經常排序、分組的字段。
3. 用于連接的字段。

怎么對索引進行優化？

如果已經建立了索引查詢性能還是慢，這個時候就需要考慮優化索引：

1. 如果是單字段索引可以根據查詢條件更改索引字段為過濾性更強的字段。
2. 如果查詢條件包含多個字段，可以考慮將單字段索引修改成聯合索引增強過濾性。
3. 索引列最好設置為 NOT NULL。
4. 唯一性太差的字段謹慎建單索引，比如性別字段，回表過多。
5. 在 JOIN、EXIST、IN 的小表驅動大表中給被驅動的大表連接匹配的字段建立索引。

索引覆蓋

1. 如果索引增加一兩個字段可以利用上索引覆蓋可以考慮加上，減少大量回表造成的開銷。
2. like '%xxx’ 很可能會導致索引失效，可以通過索引覆蓋對其優化。

優化排序

1. 只查詢用于排序的索引字段，可以利用索引進行排序，最左原則。
2. 排序字段在多個索引中，無法使用索引排序。
3. 只查詢用于排序的索引字段和主鍵，可以利用索引進行排序。
4. 查詢主鍵之外的沒有添加索引的字段，不會利用索引排序。
5. where 條件是范圍查詢時，會使 order by 索引失效。
6. 對同一聯合索引字段排序的升降序不一致，無法利用索引排序。

最左前綴

1. 聯合索引需符合最左前綴原則，過濾性強的字段往左邊放，在范圍查詢的字段后的字段無法匹配索引。
2. 對于字符串的查詢需要符合最左前綴原則，
3. 使用前綴索引。（count(distinct leftIndex(列名, 索引長度))/count(*) 來計算前綴索引的區分度）

避免索引失效

明明建了索引了，結果 explain 卻發現索引并未命中。
需要注意以下幾種情況：

1. 不滿足最左前綴原則，聯合索引或者字符串索引不滿足從左到右的字段匹配。（索引覆蓋可能還會走索引，全索引掃描要比全表掃描快）
2. 索引的列上進行計算或者使用了函數或者隱式類型轉換。
3. where 的 or 字段里面有一個是非索引列。
4. 索引的唯一性太差，比如性別字段。
5. is null, is not null, != 也無法使用索引。

核心重點就是，盡量利用一兩個復雜的多字段聯合索引，抗下你80%以上的查詢，然后用一兩個輔助索引抗下剩余20%的非典型查詢，保證你99%以上的查詢都能充分利用索引，就能保證你的查詢速度和性能！

2、緩存

既然數據庫的查詢如此緩慢，我們可以考慮減輕它的壓力，不必要的時候就不去查詢數據庫，而是去查詢緩存。

何時考慮引入緩存？

讀取頻繁，數據能接受一定延遲或者更新不那么頻繁，就可以把數據放到分布式或者本地緩存中提升讀取性能，減少數據庫的查詢壓力。

緩存方案簡介

數據一致性
緩存數據與數據庫數據的一致性是非常重要的。在更新數據庫數據時，需要及時更新緩存，或者設置合適的過期時間，以確保下次訪問時可以回源獲取最新的數據，回源數據的過程也需要考慮并發安全等。

緩存擊穿
當某個熱點數據突然失效，且有大量并發請求同時訪問該數據時，可能導致大量請求直接打到數據庫上，引起數據庫壓力劇增。可以采用互斥鎖、預先加載等方式來避免緩存擊穿。

緩存雪崩
當多個緩存數據同時失效，導致大量請求直接打到數據庫上，從而引起數據庫壓力劇增。可以通過設置不同的失效時間、使用分布式緩存、限流等方式來避免緩存雪崩。

緩存穿透
查詢一個不存在的數據，導致每次請求都會直接訪問后端系統，從而引起后端系統負載過高。為了避免緩存穿透，可以采用布隆過濾器、緩存空對象等方式。

緩存大小和淘汰策略
需要考慮緩存的大小限制以及緩存淘汰策略，以確保緩存系統不會占用過多內存或存儲資源，同時能保證高命中率。

緩存訪問壓力
緩存系統本身也可能成為性能瓶頸，需要考慮合理配置緩存服務器的數量、性能以及負載均衡策略，以滿足高并發訪問需求。

熱點數據處理
對于熱點數據，需要考慮緩存預熱、預加載等策略，以及合適的緩存失效策略，確保熱點數據的及時更新。

3、數據清理

何時考慮數據歸檔？

當表的數據出現冷熱現象的時候，可以對數據進行冷熱隔離，把更新和查詢都不頻繁的數據放到歷史表或者是離線數倉里，減少表中的數據量來提升效率，這個方案也叫數據歸檔。

數據歸檔方案簡介

1. 確定歸檔策略：

• 定義歸檔的目的和需求：明確為什么需要進行數據歸檔以及歸檔后的數據如何使用。
• 確定歸檔的數據范圍：根據業務需求，確定需要歸檔的數據類型、表或者文件。
• 設置歸檔頻率：確定歸檔操作的時間間隔，例如每日、每周或每月執行一次歸檔任務。
• 確定數據保留期限：根據法規要求和業務需求，定義歸檔數據需要保留的時間期限。

2. 備份歸檔數據：

• 在執行歸檔操作之前，對即將歸檔的數據進行備份，確保數據的安全性。
• 使用數據庫備份工具或者文件系統級別的備份工具來創建備份副本。
• 將備份數據存儲在可靠的位置，確保能夠在需要時進行恢復。

3. 創建歸檔表或文件：

• 根據歸檔的數據類型，創建相應的歸檔表或者文件。如果是數據庫數據歸檔，可以在同一數據庫實例中的不同 schema，或者在獨立的歸檔數據庫中創建歸檔表。
• 歸檔表的結構應該與原始表保持一致，可以添加額外的字段來標記數據的歸檔信息。
• 如果是文件歸檔，可以根據需要創建獨立的文件夾或者存儲位置來存放歸檔數據。

4. 編寫歸檔腳本或程序：

• 根據歸檔策略，編寫相應的歸檔腳本或者程序來執行歸檔操作。
• 腳本或程序應包括以下主要步驟：
  1）查詢待歸檔的數據：根據歸檔策略，編寫查詢語句或者邏輯來獲取待歸檔的數據。
  2）將數據插入到歸檔表中：根據歸檔策略，使用 INSERT 語句或者邏輯將數據插入到歸檔表中。可以使用批量插入的方式來提高效率。
  3）刪除原始表中的歸檔數據：在將數據插入到歸檔表之后，使用 DELETE 語句或者邏輯來刪除原始表中的歸檔數據。
  4）更新歸檔標記字段：在完成歸檔操作后，更新原始表中的歸檔標記字段，以便后續查詢和管理。

5. 定時執行歸檔任務：

• 根據歸檔策略，設置定時任務來執行歸檔操作。可以使用數據庫自帶的定時任務功能（如 MySQL 的事件調度器），也可以使用操作系統的定時任務工具（如 crontab）來定期執行歸檔腳本或程序。
• 確保在執行歸檔操作期間，對數據庫和系統資源進行監控，以確保歸檔操作不會對其他業務造成負面影響。

6. 監控歸檔進度：

• 在執行歸檔操作期間，監控數據庫的性能指標，例如 CPU、內存和磁盤使用率等，以確保歸檔操作不會對數據庫的正常運行產生過大影響。
• 使用數據庫監控工具或者日志記錄來跟蹤歸檔操作的進度和性能表現，及時發現并解決潛在問題。

7. 清理歸檔數據：

• 根據歸檔策略中定義的數據保留期限，定期進行歸檔數據的清理工作。
• 可以根據刪除標記字段或者數據的時間戳來判斷是否需要清理歸檔數據。
• 清理操作可以是物理刪除，也可以是將數據遷移到長期存儲設施，如磁帶庫或云存儲。

8. 測試和驗證：

• 完成歸檔操作后，進行全面的測試和驗證，確保歸檔操作達到了預期的效果，并且沒有引起其他問題。
• 進行性能測試、功能測試和回歸測試等，以驗證歸檔數據的完整性和可用性。

何時考慮數據刪除？

當表的數據量巨大且都是無用數據的時候，可以考慮直接把數據刪除。

數據刪除方案簡介

在執行刪除操作之前，務必仔細評估刪除數據對業務的影響，確保不會誤刪重要數據。另外，刪除操作可能導致數據庫事務日志膨脹，需要在合適的時間段進行操作，以減少對其他業務的干擾。如果刪除數據的表中存在約束關系，需要確保刪除操作不會破壞約束關系。

1. 備份數據：在執行刪除操作之前，務必對即將刪除的數據進行備份。這樣可以在意外情況下恢復數據。備份可以通過數據庫備份工具或手動備份完成。如果數據量較大，可以考慮采用增量備份的方式，減少備份耗時。
2. 確定刪除條件：仔細審查業務需求，確定要刪除的數據范圍和條件。通常可以使用 SQL 的 DELETE 語句結合 WHERE 子句來指定刪除條件，以確保只刪除目標數據。還可以使用 ORDER BY 和 LIMIT 子句來控制刪除的順序和數量。例如：

 DELETE FROM your_table WHERE condition ORDER BY id LIMIT 1000;

上述 SQL 語句將按照id升序刪除滿足條件的前 1000 條數據。如果需要按照其他字段排序，可以將 ORDER BY 后面的字段改成目標字段。

3. 分批刪除數據：如果需要刪除大量數據，建議采用分批刪除的方式，以避免一次性刪除操作對數據庫性能造成嚴重影響。可以結合使用 LIMIT 子句限制每次刪除的行數，例如每次刪除 1000 條數據。同時，在每個批次之間需要留出足夠的時間來讓數據庫處理其他請求，以避免鎖定其他事務。
4. 監控刪除進度：在執行刪除操作期間，應該監控數據庫的性能指標，確保刪除操作不會對其他業務產生負面影響。特別是在生產環境中，需要格外謹慎。可以通過數據庫性能監測工具、日志記錄等方式進行監控。如果發現刪除操作對性能造成了較大影響，可以考慮暫停刪除操作并進行優化。
5. 清理垃圾數據：刪除數據后，可能需要進行一些清理工作，例如重新構建索引、更新統計信息、釋放空間等，以確保數據庫性能得到有效提升。可以通過數據庫維護工具完成這些操作，也可以手動執行相應的 SQL 語句。
6. 測試和驗證：刪除數據后，務必進行全面的測試和驗證，確保刪除操作達到了預期的優化效果，并且未引起其他問題。測試和驗證可以包括性能測試、功能測試、回歸測試等等。

4、ES

何時考慮引入ES？

當數據量很大，搜索條件比較復雜比如模糊配置 like ‘%×××’ 等情況出現或者搜索條件靈活多變，SQL 查詢比較慢的時候，此時就可以考慮使用 ES 來代替 MySQL 執行檢索功能。

MySQL 架構天生不適合海量數據查詢，它只適合海量數據存儲，但無法應對海量數據下各種復雜條件的查詢，有人說加索引不是可以避免全表掃描，提升查詢速度嗎，為啥說它不適合海量數據查詢呢，有兩個原因：

1. 加索引確實可以提升查詢速度，但在 MySQL 中加多個索引最終在執行 SQL 的時候它只會選擇成本最低的那個索引，如果沒有索引滿足搜索條件，就會觸發全表掃描，而且即便你使用了組合索引，也要符合最左前綴原則才能命中索引，但在海量數據多種查詢條件下很有可能不符合最左前綴原則而導致索引失效，而且我們知道存儲都是需要成本的，如果你針對每一種情況都加索引，以 innoDB 為例，每加一個索引，就會創建一顆 B+ 樹，如果是海量數據，將會增加很大的存儲成本，之前就有人反饋說他們公司的某個表實際內容的大小才 10G, 而索引大小卻有 30G！這是多么巨大的成本！所以千萬不要覺得索引建得越多越好。并且索引越多數據庫的寫入性能也會收到影響。

2. 有些查詢條件是 MySQL 加索引都解決不了的，比如我要查詢商品中所有 title 帶有「格力空調」的關鍵詞，如果你用 MySQL 寫，會寫出如下代碼

SELECT * FROM product WHERE title like '%格力空調%'

這樣的話無法命中任何索引，會觸發全表掃描，而且你不能指望所有人都能輸對他想要的商品，是人就會犯錯誤，我們經常會犯類似把「格力空調」記成「格空調」的錯誤，那么 SQL 語句就會變成：

SELECT * FROM product WHERE title like '%格空調%'

這種情況下就算你觸發了全表掃描也無法查詢到任何商品，綜上所述，MySQL 的查詢確實能力有限。

ES 簡介

與其說上面列的這些點是 MySQL 的不足，倒不如說 MySQL 本身就不是為海量數據查詢而設計的，MySQL是關系型數據庫，它在表現數據間的關系還是很可以的，而檢索數據的能力就相對貧瘠，尤其是非精準的數據檢索。術業有專攻，海量數據查詢還得用專門的搜索引擎，這其中 ES 是其中當之無愧的王者，它是分布式的搜索分析引擎，可以提供針對 PB 數據的近實時查詢，廣泛用在全文檢索、日志分析、監控分析等場景。

它主要有以下三個特點：

• 輕松支持各種復雜的查詢條件: 它是分布式實時文件存儲，會把每一個字段都編入索引（倒排索引），利用高效的倒排索引，以及自定義打分、排序能力與豐富的分詞插件等，能實現任意復雜查詢條件下的全文檢索需求。
• 可擴展性強：天然支持分布式存儲，通過極其簡單的配置實現幾百上千臺服務器的分布式橫向擴容，輕松處理 PB 級別的結構化或非結構化數據。
• 高可用，容災性能好：通過使用主備節點，以及故障的自動探測與恢復，有力地保障了高可用。

那么 ES 中的索引為何如此高效，能在海量數據下達到秒級的效果呢？它采用了多種優化手段，最主要的原因是它采用了一種叫做倒排索引的方式來生成索引，避免了全文檔掃描，那么什么是倒排索引呢，通過文檔來查找關鍵詞等數據的我們稱為正排索引，返之，通過關鍵詞來查找文檔的形式我們稱之為倒排索引。

要在其中找到含有 comming 的文檔，如果要正排索引，那么要把每個文檔的內容拿出來查找是否有此單詞，毫無疑問這樣的話會導致全表掃描，那么用倒排索引會怎么查找呢，它首先會將每個文檔內容進行分詞，小寫化等，然后建立每個分詞與包含有此分詞的文檔之前的映射關系，如果有多個文檔包含此分詞，那么就會按重要程度即文檔的權重將文檔進行排序，于是我們可以得到如下關系：

這樣的話我們我要查找所有帶有 comming 的文檔，就只需查一次，而且這種情況下查詢多個單詞性能也是很好的，只要查詢多個條件對應的文檔列表，再取交集即可，極大地提升了查詢效率。

除了倒排索引外，ES 的分布式架構也天然適合海量數據查詢，來看下 ES 的架構：

一個 ES 集群由多個 node 節點組成，每個 index 是以分片（Shard，index 子集）的數據存在于多個 node 節點上的，這樣的話當一個查詢請求進來，分別在各個 node 查詢相應的結果并整合后即可，將查詢壓力分散到多個節點，避免了單個節點 CPU，磁盤，內存等處理能力的不足。

另外當新節點加入后，會自動遷移部分分片至新節點，實現負載均衡，這個功能是 ES 自動完成的，對比一個下 MySQL 的分庫分表需要開發人員引入 Mycat 等中間件并指定分庫分表規則等繁瑣的流程是不是一個巨大的進步？這也就意味著 ES 有非常強大的水平擴展的能力，集群可輕松擴展致幾百上千個節點，輕松支持 PB 級的數據查詢。

當然 ES 的強大不止于此，它還采用了比如主備分片提升搜索吞率，使用節點故障探測，Raft 選主機制等提升了容災能力等等，這些不是本文重點，讀者可自行查閱，總之經過上面的簡單總結大家只需要明白一點：ES 的分布式架構設計天生支持海量數據查詢。

5、讀寫分離

什么是讀寫分離？

讀寫分離，基本的原理是讓主數據庫處理事務性增、改、刪操作（INSERT、UPDATE、DELETE），而從數據庫處理SELECT查詢操作。數據庫復制被用來把事務性操作導致的變更同步到集群中的從數據庫。

何時考慮讀寫分離？

如果程序使用數據庫較多時，而更新少，查詢多的情況下會考慮使用，利用數據庫主從同步 。可以減少數據庫壓力，提高性能。

為什么要讀寫分離呢？

因為數據庫的“寫”（寫10000條數據到 MySQL 可能要3分鐘）操作是比較耗時的。
但是數據庫的“讀”（從 MySQL 讀10000條數據可能只要5秒鐘）。
所以讀寫分離，解決的是，數據庫的寫入，影響了查詢的效率。
一臺主、多臺從，主提供寫操作，從提供讀操作，讀取壓力大的時候可以考慮增加從庫提升讀取性能。

6、分庫分表

什么是分庫分表？

分表指的是在數據庫數量不變的情況下對表進行拆分。
比如我們將 SPU 表從一張拆成四張。

分庫指的是在表數量不變的情況下對數據庫進行拆分。
比如我們一個庫里面放了兩張表，一張 SPU，一張 SKU，我們將兩張表拆到不同的庫里面去。

分庫分表指的是數據庫的數量還有表的數據都發生變更。
比如我們一個數據庫里面有一張 SPU 表，我們把它拆成四張表，并且放到兩個數據庫里面。

拆分方式

表層面：
水平拆分指的是表結構不發生變更的情況下將一張表的數據拆分成多張表，因為當一張表的數據量越來越大的時候這張表的查詢跟寫入性能會越來越差，通過拆成多張表使每張表數據量變小，從而提供更好的讀寫性能。

垂直拆分指的是將一張表的字段拆分到多張表中，一般來說，會將較少的訪問頻率很高的字段放到一個表里去，然后將較多的訪問頻率很低的字段放到另外一個表里去。因為數據庫是有緩存的，你訪問頻率高的行字段越少，就可以在緩存里緩存更多的行，性能就越好。

例如我們將 pic 字段單獨拆分出來，剩下的三個字段還保留在原表里面，但是隨著業務發展我們發現 pic 字段可能會越來越大，從而影響我們商品信息的查詢，這個時候我們可以將 pic 字段單獨拆分出去，一般都需要 id 關聯到原來那張表上。

庫層面：
庫層面水平垂直拆分也是一樣的，意義在于將數據均勻放更多的庫里，然后用多個庫來扛更高的并發，還有就是用多個庫的存儲容量來進行擴容。

為什么需要分庫分表？

單臺 MySQL 的硬件資源是有限的，隨著我們業務發展，我們的請求量和數據量都會不斷增加，數據庫的壓力會越來越大，到了某一時刻數據庫的讀寫性能開始出現下降，數據庫就會成為我們請求鏈路中的一個瓶頸，此時可能需要我們對數據庫進行優化，在業務初期我們可能使用一些諸如增加優化索引、讀寫分離、增加從庫的手段優化，隨著數據量的增加，這些手段的效果可能變得越來越小，此時可能就需要分庫分表來進行優化，對數據進行切分，將單庫單表的數據控制在一個合理的范圍內，以保證數據庫提供一個高效的讀寫能力。

分庫分表的優點

1. 并發支撐：沒分之前，單機部署，扛不住高并發；分了之后，承受的并發增加了多倍。
2. 磁盤使用：沒分之前單機磁盤容量幾乎撐滿；分了之后，磁盤使用率大大降低。
3. SQL 性能：沒分之前單表數據量大，SQL 越跑越慢；分了之后單表數據量減少，SQL 效率提升。

何時進行分庫分表？

數據庫出現瓶頸通常有兩個維度，第一個是單表出現瓶頸，一般是單表的數據量大導致表的讀寫性能都慢；另外一種數據庫整體都出現瓶頸，一般表現為 QPS 過高，導致磁盤、IO、網絡、CPU、內存負載過高或者表很多數據量很大，存儲空間都幾近于耗盡。

只出現表維度瓶頸就只分表，只出現庫維度瓶頸就只分庫，表和庫維度的瓶頸都出現就既分庫又分表。

分庫分表需注意

分庫分表的方案設計需要考慮分表字段、分表算法、全局ID的選擇，還會帶來跨表查詢、跨表事務的問題，而且分庫分表之后基本上也就告別了分頁查詢和排序，當然這些問題也有辦法解決，不過還是強烈建議不到萬不得已不要上分庫分表，可以先嘗試基本優化，建好索引、減少多表join、適量的字段冗余其實大多數情況是能扛得住的，其次是可以想辦法減少數據庫的壓力，比如上一層緩存，把能接受延遲或者更新不那么頻繁的放到分布式或者本地緩存中，第三是冷熱數據的隔離，把更新和查詢都不頻繁的數據放到歷史表或者是離線數倉里，減少表中的數據量來提升效率，這個方案也叫數據歸檔；第四個是考慮上分布式數據庫。
總之就是優化、緩存、歸檔先嘗試一遍都搞不定且沒有預算上分布式數據庫的話再考慮分庫分表。

總的來說就是數據庫讀寫性能出現瓶頸，通過其他手段已經沒辦法很好解決了，這是最終的解決手段，優先使用其他手段。數據達到千萬級別就分庫分表是不準確的，需要結合情況分析，千萬這個數字只是一個參考。分庫分表應當以未來3-5年的業務情況評估，不能只以當前數據業務量評估，否則可能會出現頻繁的分庫分表，因為分庫分表的代價是比較大的，所以最好是充分評估，能支撐未來3-5年的增長。