SQL優(yōu)化的原則有哪些

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

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

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

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

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

性能優(yōu)化原則

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

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

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

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

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

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

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

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

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

 

問題 1：單條 SQL 運行慢

問題分析

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

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

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

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

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

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

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

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

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

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

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

1.垂直拆分

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

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

2.水平拆分

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

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

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

問題 2：部分 SQL 運行慢 

問題分析

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

解決方案：慢查詢分析

MySQL 中自帶了慢查詢?nèi)罩镜墓δ?，開啟它就可以用來記錄在 MySQL 中響應時間超過閥值的語句，具體指運行時間超過 long_query_time 值的 SQL，則會被記錄到慢查詢?nèi)罩局小ong_query_time 的默認值為 10，意思是運行 10S 以上的語句。默認情況下，MySQL 數(shù)據(jù)庫并不啟動慢查詢?nèi)罩荆枰覀兪謩觼碓O置這個參數(shù)，如果不是調(diào)優(yōu)需要的話，一般不建議啟動該參數(shù)，因為開啟慢查詢?nèi)罩緯o MySQL 服務器帶來一定的性能影響。慢查詢?nèi)罩局С謱⑷罩居涗泴懭胛募?，也支持將日志記錄寫入?shù)據(jù)庫表。使用 mysql> show variables like '%slow_query_log%'; 來查詢慢查詢?nèi)罩臼欠耖_啟，執(zhí)行效果如下圖所示：slow_query_log 的值為 OFF 時，表示未開啟慢查詢?nèi)罩尽?nbsp;

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

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

mysql> set global slow_query_log=1

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

slow_query_log =1 slow_query_log_file=/tmp/mysql_slow.log

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

問題 3：整個 SQL 運行慢

問題分析

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

解決方案：讀寫分離

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

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

1.應用層解決方案

可以通過應用層對數(shù)據(jù)源做路由來實現(xiàn)讀寫分離，比如，使用 SpringMVC + MyBatis，可以將 SQL 路由交給 Spring，通過 AOP 或者 Annotation 由代碼顯示的控制數(shù)據(jù)源。優(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 — 輸出結(jié)果集的表；
• partitions — 匹配的分區(qū)；
• type — 表示表的連接類型；
• possible_keys — 表示查詢時，可能使用的索引；
• key — 表示實際使用的索引；
• key_len — 索引字段的長度；
• ref—  列與索引的比較；
• rows — 大概估算的行數(shù)；
• filtered — 按表條件過濾的行百分比；
• Extra — 執(zhí)行情況的描述和說明。

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

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