MySQL“被動(dòng)”性能優(yōu)化匯總！
2022-02-14 14:16:09

年少不知優(yōu)化苦，遇坑方知優(yōu)化難。 ——村口王大爺

本文內(nèi)容導(dǎo)圖如下：

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

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

所以，在開始之前（MySQL 優(yōu)化），咱們先來聊聊性能優(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)化原則

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

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

以上原則看似都是些廢話，但卻給了我們一個(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)化方案。我們在實(shí)際開發(fā)中，通常會(huì)遇到以下 3 個(gè)問題：

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

問題 1：單條 SQL 運(yùn)行慢

問題分析

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

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

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

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

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

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

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

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

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

解決方案 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 等大字段拆分出來放在附表中；
• 經(jīng)常組合查詢的列放在一張表中。
  • 
    

2.水平拆分

指的是將數(shù)據(jù)表行進(jìn)行拆分，表的行數(shù)超過200萬行時(shí)，就會(huì)變慢，這時(shí)可以把一張的表的數(shù)據(jù)拆成多張表來存放。

通常情況下，我們使用取模的方式來進(jìn)行表的拆分，比如，一張有 400W 的用戶表 users，為提高其查詢效率我們把其分成 4 張表 users1，users2，users3，users4，然后通過用戶 ID 取模的方法，同時(shí)查詢、更新、刪除也是通過取模的方法來操作。

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

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

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

問題分析

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

解決方案：慢查詢分析

MySQL 中自帶了慢查詢?nèi)罩镜墓δ埽_啟它就可以用來記錄在 MySQL 中響應(yīng)時(shí)間超過閥值的語句，具體指運(yùn)行時(shí)間超過 long_query_time 值的 SQL，則會(huì)被記錄到慢查詢?nèi)罩局小ong_query_time 的默認(rèn)值為 10，意思是運(yùn)行 10S 以上的語句。默認(rèn)情況下，MySQL 數(shù)據(jù)庫并不啟動(dòng)慢查詢?nèi)罩?，需要我們手?dòng)來設(shè)置這個(gè)參數(shù)，如果不是調(diào)優(yōu)需要的話，一般不建議啟動(dòng)該參數(shù)，因?yàn)殚_啟慢查詢?nèi)罩緯?huì)給 MySQL 服務(wù)器帶來一定的性能影響。慢查詢?nèi)罩局С謱⑷罩居涗泴懭胛募?，也支持將日志記錄寫入?shù)據(jù)庫表。

使用 ??mysql> show variables like '%slow_query_log%';?? 來查詢慢查詢?nèi)罩臼欠耖_啟，執(zhí)行效果如下圖所示：

slow_query_log 的值為 OFF 時(shí)，表示未開啟慢查詢?nèi)罩尽?/p>

開啟慢查詢?nèi)罩?/h5>

開啟慢查詢?nèi)罩?，可以使用如?MySQL 命令：

mysql> set global slow_query_log=1

不過這種設(shè)置方式，只對(duì)當(dāng)前數(shù)據(jù)庫生效，如果 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ù)庫的承載能力已經(jīng)到了峰值，因此我們需要使用一些數(shù)據(jù)庫的擴(kuò)展手段來緩解 MySQL 服務(wù)器了。

解決方案：讀寫分離

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

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

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

可以通過應(yīng)用層對(duì)數(shù)據(jù)源做路由來實(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)定性待測試，需要支持代碼強(qiáng)制主從和事務(wù)。

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

在 MySQL 中我們可以使用 explain 命令來分析 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 — 索引字段的長度；
• 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)化，但無論何種優(yōu)化都要保證服務(wù)的正確性、安全性和穩(wěn)定性。它帶給我們的啟發(fā)是應(yīng)該采用：預(yù)防 + 被動(dòng)優(yōu)化的方案來確保 MySQL 服務(wù)器的穩(wěn)定性，而被動(dòng)優(yōu)化常見的問題是：

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

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

關(guān)注下面二維碼，訂閱更多精彩內(nèi)容。

作者： 王磊的博客