手把手教你 SQL 優(yōu)化的正確姿勢(shì)！

全文內(nèi)容預(yù)覽：

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

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

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

• 主動(dòng)優(yōu)化
• 被動(dòng)優(yōu)化

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

而被動(dòng)優(yōu)化剛好與主動(dòng)優(yōu)化相反，它是指在發(fā)現(xiàn)了服務(wù)器卡頓、服務(wù)異?；蛘呶锢碇笜?biāo)異常的情況下，才去優(yōu)化的這種行為。

性能優(yōu)化原則

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

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

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

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

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

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

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

本文我們會(huì)從問題入手，然后考慮這個(gè)問題產(chǎn)生的原因以及相應(yīng)的優(yōu)化方案。我們?cè)趯?shí)際開發(fā)中，通常會(huì)遇到以下 3 個(gè)問題：

1. 單條 SQL 運(yùn)行慢；
2. 部分 SQL 運(yùn)行慢；
3. 整個(gè) SQL 運(yùn)行慢。

問題 1：?jiǎn)螚l SQL 運(yùn)行慢

問題分析

造成單條 SQL 運(yùn)行比較慢的常見原因有以下兩個(gè)：

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

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

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

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

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

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

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

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

解決方案 2：數(shù)據(jù)拆分

當(dāng)表中數(shù)據(jù)量太大時(shí) SQL 的查詢會(huì)比較慢，你可以考慮拆分表，讓每張表的數(shù)據(jù)量變小，從而提高查詢效率。

1.垂直拆分

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

• 把不常用的字段單獨(dú)放在一張表；
• 把 text，blob 等大字段拆分出來(lái)放在附表中；
• 經(jīng)常組合查詢的列放在一張表中。

2.水平拆分

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

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

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

問題 2：部分 SQL 運(yùn)行慢

問題分析

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

解決方案：慢查詢分析

MySQL 中自帶了慢查詢?nèi)罩镜墓δ埽_啟它就可以用來(lái)記錄在 MySQL 中響應(yīng)時(shí)間超過閥值的語(yǔ)句，具體指運(yùn)行時(shí)間超過 long_query_time 值的 SQL，則會(huì)被記錄到慢查詢?nèi)罩局?。long_query_time 的默認(rèn)值為 10，意思是運(yùn)行 10S 以上的語(yǔ)句。默認(rèn)情況下，MySQL 數(shù)據(jù)庫(kù)并不啟動(dòng)慢查詢?nèi)罩荆枰覀兪謩?dòng)來(lái)設(shè)置這個(gè)參數(shù)，如果不是調(diào)優(yōu)需要的話，一般不建議啟動(dòng)該參數(shù)，因?yàn)殚_啟慢查詢?nèi)罩緯?huì)給 MySQL 服務(wù)器帶來(lái)一定的性能影響。慢查詢?nèi)罩局С謱⑷罩居涗泴懭胛募仓С謱⑷罩居涗泴懭霐?shù)據(jù)庫(kù)表。使用 mysql> show variables like '%slow_query_log%'; 來(lái)查詢慢查詢?nèi)罩臼欠耖_啟，執(zhí)行效果如下圖所示：slow_query_log 的值為 OFF 時(shí)，表示未開啟慢查詢?nèi)罩尽?/p>

開啟慢查詢?nèi)罩?span style="display: none;">

開啟慢查詢?nèi)罩荆梢允褂萌缦?MySQL 命令：

mysql> set global slow_query_log=1

不過這種設(shè)置方式，只對(duì)當(dāng)前數(shù)據(jù)庫(kù)生效，如果 MySQL 重啟也會(huì)失效，如果要永久生效，就必須修改 MySQL 的配置文件 my.cnf，配置如下：

slow_query_log =1 slow_query_log_file=/tmp/mysql_slow.log

當(dāng)你開啟慢查詢?nèi)罩局?，所有的慢查?SQL 都會(huì)被記錄在 slow_query_log_file 參數(shù)配置的文件內(nèi)，默認(rèn)是 /tmp/mysql_slow.log 文件，此時(shí)我們就可以打開日志查詢到所有慢 SQL 進(jìn)行逐個(gè)優(yōu)化。

問題 3：整個(gè) SQL 運(yùn)行慢

問題分析

當(dāng)出現(xiàn)整個(gè) SQL 都運(yùn)行比較慢就說明目前數(shù)據(jù)庫(kù)的承載能力已經(jīng)到了峰值，因此我們需要使用一些數(shù)據(jù)庫(kù)的擴(kuò)展手段來(lái)緩解 MySQL 服務(wù)器了。

解決方案：讀寫分離

一般情況下對(duì)數(shù)據(jù)庫(kù)而言都是“讀多寫少”，換言之，數(shù)據(jù)庫(kù)的壓力多數(shù)是因?yàn)榇罅康淖x取數(shù)據(jù)的操作造成的，我們可以采用數(shù)據(jù)庫(kù)集群的方案，使用一個(gè)庫(kù)作為主庫(kù)，負(fù)責(zé)寫入數(shù)據(jù)；其他庫(kù)為從庫(kù)，負(fù)責(zé)讀取數(shù)據(jù)。這樣可以緩解對(duì)數(shù)據(jù)庫(kù)的訪問壓力。

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

1.應(yīng)用層解決方案

可以通過應(yīng)用層對(duì)數(shù)據(jù)源做路由來(lái)實(shí)現(xiàn)讀寫分離，比如，使用 SpringMVC + MyBatis，可以將 SQL 路由交給 Spring，通過 AOP 或者 Annotation 由代碼顯示的控制數(shù)據(jù)源。優(yōu)點(diǎn)：路由策略的擴(kuò)展性和可控性較強(qiáng)。缺點(diǎn)：需要在 Spring 中添加耦合控制代碼。

2.中間件解決方案

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

擴(kuò)展知識(shí)：SQL 語(yǔ)句分析

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

explain select * from t where id=5;

如下圖所示：

其中：

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

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

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

總結(jié)

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

• 單條 SQL 運(yùn)行慢；
• 部分 SQL 運(yùn)行慢；
• 整個(gè) SQL 運(yùn)行慢。

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