MySQL「被動」性能優化匯總！

2020 年 8 月 10 日
筆記
core-java, JAVA

年少不知優化苦，遇坑方知優化難。 ——村口王大爺

本文內容導圖如下：

我之前有很多文章都在講性能優化的問題，比如下面這些：

當然，本篇也是關於性能優化的，那性能優化就應該一把梭子嗎？還是要符合一些規範和原則呢？

所以，在開始之前（MySQL 優化），咱們先來聊聊性能優化的一些原則。

性能優化原則和分類

性能優化一般可以分為：

主動優化
被動優化

所謂的主動優化是指不需要外力的推動而自發進行的一種行為，比如當服務沒有明顯的卡頓、宕機或者硬體指標異常的情況下，自我出發去優化的行為，就可以稱之為主動優化。

而被動優化剛好與主動優化相反，它是指在發現了伺服器卡頓、服務異常或者物理指標異常的情況下，才去優化的這種行為。

性能優化原則
無論是主動優化還是被動優化都要符合以下性能優化的原則：

優化不能改變服務運行的邏輯，要保證服務的正確性；
優化的過程和結果都要保證服務的安全性；
要保證服務的穩定性，不能為了追求性能犧牲程式的穩定性。比如不能為了提高 Redis 的運行速度，而關閉持久化的功能，因為這樣在 Redis 伺服器重啟或者掉電之後會丟失存儲的數據。

以上原則看似都是些廢話，但卻給了我們一個啟發，那就是我們性能優化手段應該是：預防性能問題為主+被動優化為輔。

也就是說，我們應該以預防性能問題為主，在開發階段儘可能的規避性能問題，而在正常情況下，應盡量避免主動優化，以防止未知的風險（除非是為了 KPI，或者是閑的沒事），尤其對生產環境而言更是如此，最後才是考慮被動優化。

PS：當遇到性能緩慢下降、或硬體指標緩慢增加的情況，如今天記憶體的佔用率是 50%，明天是 70%，後天是 90% ，並且絲毫沒有收回的跡象時，我們應該提早發現並處理此類問題（這種情況也屬於被動優化的一種）。

MySQL 被動性能優化

所以我們本文會重點介紹 MySQL 被動性能優化的知識，根據被動性能優化的知識，你就可以得到預防性能問題發生的一些方法，從而規避 MySQL 的性能問題。

本文我們會從問題入手，然後考慮這個問題產生的原因以及相應的優化方案。我們在實際開發中，通常會遇到以下 3 個問題：

單條 SQL 運行慢；
部分 SQL 運行慢；
整個 SQL 運行慢。

問題 1：單條 SQL 運行慢

問題分析

造成單條 SQL 運行比較慢的常見原因有以下兩個：

未正常創建或使用索引；
表中數據量太大。

解決方案 1：創建並正確使用索引

索引是一種能幫助 MySQL 提高查詢效率的主要手段，因此一般情況下我們遇到的單條 SQL 性能問題，通常都是由於未創建或為正確使用索引而導致的，所以在遇到單條 SQL 運行比較慢的情況下，你首先要做的就是檢查此表的索引是否正常創建。

如果表的索引已經創建了，接下來就要檢查一下此 SQL 語句是否正常觸發了索引查詢，如果發生以下情況那麼 MySQL 將不能正常的使用索引：

在 where 子句中使用 != 或者 <> 操作符，查詢引用會放棄索引而進行全表掃描；
不能使用前導模糊查詢，也就是 ‘%XX’ 或 ‘%XX%’，由於前導模糊不能利用索引的順序，必須一個個去找，看是否滿足條件，這樣會導致全索引掃描或者全表掃描；
如果條件中有 or 即使其中有條件帶索引也不會正常使用索引，要想使用 or 又想讓索引生效，只能將 or 條件中的每個列都加上索引才能正常使用；
在 where 子句中對欄位進行表達式操作。

因此你要盡量避免以上情況，除了正常使用索引之外，我們也可以使用以下技巧來優化索引的查詢速度：

盡量使用主鍵查詢，而非其他索引，因為主鍵查詢不會觸發回表查詢；
查詢語句儘可能簡單，大語句拆小語句，減少鎖時間；
盡量使用數字型欄位，若只含數值資訊的欄位盡量不要設計為字元型；
用 exists 替代 in 查詢；
避免在索引列上使用 is null 和 is not null。

回表查詢：普通索引查詢到主鍵索引後，回到主鍵索引樹搜索的過程，我們稱為回表查詢。

解決方案 2：數據拆分

當表中數據量太大時 SQL 的查詢會比較慢，你可以考慮拆分表，讓每張表的數據量變小，從而提高查詢效率。

1.垂直拆分

指的是將表進行拆分，把一張列比較多的表拆分為多張表。比如，用戶表中一些欄位經常被訪問，將這些欄位放在一張表中，另外一些不常用的欄位放在另一張表中，插入數據時，使用事務確保兩張表的數據一致性。
垂直拆分的原則：

把不常用的欄位單獨放在一張表；
把 text，blob 等大欄位拆分出來放在附表中；
經常組合查詢的列放在一張表中。

2.水平拆分

指的是將數據錶行進行拆分，表的行數超過200萬行時，就會變慢，這時可以把一張的表的數據拆成多張表來存放。
通常情況下，我們使用取模的方式來進行表的拆分，比如，一張有 400W 的用戶表 users，為提高其查詢效率我們把其分成 4 張表 users1，users2，users3，users4，然後通過用戶 ID 取模的方法，同時查詢、更新、刪除也是通過取模的方法來操作。

表的其他優化方案：

使用可以存下數據最小的數據類型；
使用簡單的數據類型，int 要比 varchar 類型在 MySQL 處理簡單；
盡量使用 tinyint、smallint、mediumint 作為整數類型而非 int；
儘可能使用 not null 定義欄位，因為 null 佔用 4 位元組空間；
盡量少用 text 類型，非用不可時最好考慮分表；
盡量使用 timestamp，而非 datetime；
單表不要有太多欄位，建議在 20 個欄位以內。

問題 2：部分 SQL 運行慢

問題分析

部分 SQL 運行比較慢，我們首先要做的就是先定位出這些 SQL，然後再看這些 SQL 是否正確創建並使用索引。也就是說，我們先要使用慢查詢工具定位出具體的 SQL，然後再使用問題 1 的解決方案處理慢 SQL。

解決方案：慢查詢分析

MySQL 中自帶了慢查詢日誌的功能，開啟它就可以用來記錄在 MySQL 中響應時間超過閥值的語句，具體指運行時間超過 long_query_time 值的 SQL，則會被記錄到慢查詢日誌中。long_query_time 的默認值為 10，意思是運行 10S 以上的語句。默認情況下，MySQL 資料庫並不啟動慢查詢日誌，需要我們手動來設置這個參數，如果不是調優需要的話，一般不建議啟動該參數，因為開啟慢查詢日誌會給 MySQL 伺服器帶來一定的性能影響。慢查詢日誌支援將日誌記錄寫入文件，也支援將日誌記錄寫入資料庫表。
使用 mysql> show variables like '%slow_query_log%'; 來查詢慢查詢日誌是否開啟，執行效果如下圖所示：

slow_query_log 的值為 OFF 時，表示未開啟慢查詢日誌。

開啟慢查詢日誌

開啟慢查詢日誌，可以使用如下 MySQL 命令：

mysql> set global slow_query_log=1

不過這種設置方式，只對當前資料庫生效，如果 MySQL 重啟也會失效，如果要永久生效，就必須修改 MySQL 的配置文件 my.cnf，配置如下：

slow_query_log =1
slow_query_log_file=/tmp/mysql_slow.log

當你開啟慢查詢日誌之後，所有的慢查詢 SQL 都會被記錄在 slow_query_log_file 參數配置的文件內，默認是 /tmp/mysql_slow.log 文件，此時我們就可以打開日誌查詢到所有慢 SQL 進行逐個優化。

問題 3：整個 SQL 運行慢

問題分析

當出現整個 SQL 都運行比較慢就說明目前資料庫的承載能力已經到了峰值，因此我們需要使用一些資料庫的擴展手段來緩解 MySQL 伺服器了。

解決方案：讀寫分離

一般情況下對資料庫而言都是「讀多寫少」，換言之，資料庫的壓力多數是因為大量的讀取數據的操作造成的，我們可以採用資料庫集群的方案，使用一個庫作為主庫，負責寫入數據；其他庫為從庫，負責讀取數據。這樣可以緩解對資料庫的訪問壓力。

MySQL 常見的讀寫分離方案有以下兩種：

1.應用層解決方案

可以通過應用層對數據源做路由來實現讀寫分離，比如，使用 SpringMVC + MyBatis，可以將 SQL 路由交給 Spring，通過 AOP 或者 Annotation 由程式碼顯示的控制數據源。
優點：路由策略的擴展性和可控性較強。
缺點：需要在 Spring 中添加耦合控制程式碼。

2.中間件解決方案

通過 MySQL 的中間件做主從集群，比如：Mysql Proxy、Amoeba、Atlas 等中間件都能符合需求。
優點：與應用層解耦。
缺點：增加一個服務維護的風險點，性能及穩定性待測試，需要支援程式碼強制主從和事務。

擴展知識：SQL 語句分析

在 MySQL 中我們可以使用 explain 命令來分析 SQL 的執行情況，比如：

explain select * from t where id=5;

如下圖所示：

其中：

id — 選擇標識符，id 越大優先順序越高，越先被執行；
select_type — 表示查詢的類型；
table — 輸出結果集的表；
partitions — 匹配的分區；
type — 表示表的連接類型；
possible_keys — 表示查詢時，可能使用的索引；
key — 表示實際使用的索引；
key_len — 索引欄位的長度；
ref— 列與索引的比較；
rows — 大概估算的行數；
filtered — 按表條件過濾的行百分比；
Extra — 執行情況的描述和說明。

其中最重要的就是 type 欄位，type 值類型如下：

all — 掃描全表數據；
index — 遍歷索引；
range — 索引範圍查找；
index_subquery — 在子查詢中使用 ref；
unique_subquery — 在子查詢中使用 eq_ref；
ref_or_null — 對 null 進行索引的優化的 ref；
fulltext — 使用全文索引；
ref — 使用非唯一索引查找數據；
eq_ref — 在 join 查詢中使用主鍵或唯一索引關聯；
const — 將一個主鍵放置到 where 後面作為條件查詢， MySQL 優化器就能把這次查詢優化轉化為一個常量，如何轉化以及何時轉化，這個取決於優化器，這個比 eq_ref 效率高一點。

總結

本文我們介紹了 MySQL 性能優化的原則和分類，MySQL 的性能優化可分為：主動優化和被動優化，但無論何種優化都要保證服務的正確性、安全性和穩定性。它帶給我們的啟發是應該採用：預防 + 被動優化的方案來確保 MySQL 伺服器的穩定性，而被動優化常見的問題是：

單條 SQL 運行慢；
部分 SQL 運行慢；
整個 SQL 運行慢。

因此我們給出了每種被動優化方案的問題分析和解決方案，希望本文可以幫助到你。

Tags: core-java JAVA

MySQL「被動」性能優化匯總！

性能優化原則和分類