不要一把梭了，這才是SQL優(yōu)化的正確姿勢！|原創(chuàng)干貨

這是我的第?83?篇原創(chuàng)文章

作者 | 王磊

來源 | Java中文社群（ID：javacn666）

年少不知優(yōu)化苦，遇坑方知優(yōu)化難?！蹇谕醮鬆?br>

全文內容預覽：

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

1. 《switch 的性能提升了 3 倍，我只用了這一招！》
2. 《String性能提升10倍的幾個方法！(源碼+原理分析)》
3. 《局部變量竟然比全局變量快 5 倍？》
4. 《池化技術到達有多牛？看了線程和線程池的對比嚇我一跳！》
5. 《鏈表竟然比數組慢了1000多倍？(動圖+性能評測)》
6. 《HashMap 的 7 種遍歷方式與性能分析！》
7. 更多性能優(yōu)化文章

當然，本篇也是關于性能優(yōu)化的，那性能優(yōu)化就應該一把梭子嗎？還是要符合一些規(guī)范和原則呢？

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

性能優(yōu)化原則和分類

性能優(yōu)化一般可以分為：

• 主動優(yōu)化
• 被動優(yōu)化

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

而被動優(yōu)化剛好與主動優(yōu)化相反，它是指在發(fā)現了服務器卡頓、服務異?；蛘呶锢碇笜水惓５那闆r下，才去優(yōu)化的這種行為。

性能優(yōu)化原則

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

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

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

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

PS：當遇到性能緩慢下降、或硬件指標緩慢增加的情況，如今天內存的占用率是 50%，明天是 70%，后天是 90% ，并且絲毫沒有收回的跡象時，我們應該提早發(fā)現并處理此類問題（這種情況也屬于被動優(yōu)化的一種）。

MySQL 被動性能優(yōu)化

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

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

1. 單條 SQL 運行慢；
2. 部分 SQL 運行慢；
3. 整個 SQL 運行慢。

問題 1：單條 SQL 運行慢

問題分析

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

1. 未正常創(chuàng)建或使用索引；
2. 表中數據量太大。

解決方案 1：創(chuàng)建并正確使用索引

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

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

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

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

1. 盡量使用主鍵查詢，而非其他索引，因為主鍵查詢不會觸發(fā)回表查詢；
2. 查詢語句盡可能簡單，大語句拆小語句，減少鎖時間；
3. 盡量使用數字型字段，若只含數值信息的字段盡量不要設計為字符型；
4. 用 exists 替代 in 查詢；
5. 避免在索引列上使用 is null 和 is not null。

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

解決方案 2：數據拆分

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

1.垂直拆分

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

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

2.水平拆分

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

表的其他優(yōu)化方案：

1. 使用可以存下數據最小的數據類型；
2. 使用簡單的數據類型，int 要比 varchar 類型在 MySQL 處理簡單；
3. 盡量使用 tinyint、smallint、mediumint 作為整數類型而非 int；
4. 盡可能使用 not null 定義字段，因為 null 占用 4 字節(jié)空間；
5. 盡量少用 text 類型，非用不可時最好考慮分表；
6. 盡量使用 timestamp，而非 datetime；
7. 單表不要有太多字段，建議在 20 個字段以內。

問題 2：部分 SQL 運行慢

問題分析

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

解決方案：慢查詢分析

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

問題 3：整個 SQL 運行慢

問題分析

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

解決方案：讀寫分離

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

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

1.應用層解決方案

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

2.中間件解決方案

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

擴展知識：SQL 語句分析

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

explain select * from t where id=5;

如下圖所示：

其中：

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

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

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

總結

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

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

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