MySQL 效能優化技巧

原文地址：MySQL 效能優化技巧
部落格地址：www.extlight.com

一、背景

最近公司專案新增新功能，上線後發現有些功能的列表查詢時間很久。原因是新功能用到舊功能的介面，而這些舊介面的 SQL 查詢語句關聯5,6張表且編寫不夠規範，導致 MySQL 在執行 SQL 語句時索引失效，進行全表掃描。原本負責優化的同事有事請假回家，因此優化查詢資料的問題落在筆者手中。筆者在查閱網上 SQL 優化的資料後成功解決了問題，在此從==全域性角度==記錄和總結 MySQL 查詢優化相關技巧。

二、優化思路

資料查詢慢，不代表 SQL 語句寫法有問題。 首先，我們需要找到問題的源頭才能“對症下藥”。筆者用一張流程圖展示 MySQL 優化的思路：

無需更多言語，從圖中可以清楚地看出，導致資料查詢慢的原因有多種，如：快取失效，在此一段時間內由於高併發訪問導致 MySQL 伺服器崩潰；SQL 語句編寫問題；MySQL 伺服器引數問題；硬體配置限制 MySQL 服務效能問題等。

三、檢視 MySQL 伺服器執行的狀態值

如果系統的併發請求數不高，且查詢速度慢，可以忽略該步驟直接進行 SQL 語句調優步驟。

執行命令：

show status複製程式碼

由於返回結果太多，此處不貼出結果。其中，再返回的結果中，我們主要關注 “Queries”、“Threads_connected” 和 “Threads_running” 的值，即查詢次數、執行緒連線數和執行緒執行數。

我們可以通過執行如下指令碼監控 MySQL 伺服器執行的狀態值

#!/bin/bash
while true
do
mysqladmin -uroot -p"密碼" ext | awk '/Queries/{q=$4}/Threads_connected/{c=$4}/Threads_running/{r=$4}END{printf("%d %d %d\n",q,c,r)}' >> status.txt
sleep 1
done複製程式碼

執行該指令碼 24 小時，獲取 status.txt 裡的內容，再次通過 awk 計算==每秒請求 MySQL 服務的次數==

awk '{q=$1-last;last=$1}{printf("%d %d %d\n",q,$2,$3)}' status.txt複製程式碼

複製計算好的內容到 Excel 中生成圖表觀察資料週期性。

如果觀察的資料有周期性的變化，如上圖的解釋，需要修改快取失效策略。

例如：

通過隨機數在[3,6,9] 區間獲取其中一個值作為快取失效時間，這樣分散了快取失效時間，從而節省了一部分記憶體的消耗。

當訪問高峰期時，一部分請求分流到未失效的快取，另一部分則訪問 MySQL 資料庫，這樣減少了 MySQL 伺服器的壓力。

四、獲取需要優化的 SQL 語句

4.1 方式一：檢視執行的執行緒

執行命令：

show processlist複製程式碼

返回結果：

mysql> show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host      | db   | Command | Time | State    | Info             |
+----+------+-----------+------+---------+------+----------+------------------+
|  9 | root | localhost | test | Query   |    0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+------------------+
1 row in set (0.00 sec)複製程式碼

從返回結果中我們可以瞭解該執行緒執行了什麼命令/SQL 語句以及執行的時間。實際應用中，查詢的返回結果會有 N 條記錄。

其中，返回的 State 的值是我們判斷效能好壞的關鍵，其值出現如下內容，則該行記錄的 SQL 語句需要優化：

Converting HEAP to MyISAM # 查詢結果太大時，把結果放到磁碟，嚴重
Create tmp table #建立臨時表，嚴重
Copying to tmp table on disk  #把記憶體臨時表複製到磁碟，嚴重
locked #被其他查詢鎖住，嚴重
loggin slow query #記錄慢查詢
Sorting result #排序複製程式碼

State 欄位有很多值，如需瞭解更多，可以參看文章末尾提供的連結。

4.2 方式二：開啟慢查詢日誌

在配置檔案 my.cnf 中的 [mysqld] 一行下邊新增兩個引數：

slow_query_log = 1
slow_query_log_file=/var/lib/mysql/slow-query.log
long_query_time = 2

log_queries_not_using_indexes = 1複製程式碼

其中，slow_query_log = 1 表示開啟慢查詢；slow_query_log_file 表示慢查詢日誌存放的位置；
long_query_time = 2 表示查詢 >=2 秒才記錄日誌；log_queries_not_using_indexes = 1 記錄沒有使用索引的 SQL 語句。

注意：slow_query_log_file 的路徑不能隨便寫，否則 MySQL 伺服器可能沒有許可權將日誌檔案寫到指定的目錄中。建議直接複製上文的路徑。

修改儲存檔案後，重啟 MySQL 服務。在 /var/lib/mysql/ 目錄下會建立 slow-query.log 日誌檔案。連線 MySQL 服務端執行如下命令可以檢視配置情況。

show variables like 'slow_query%';

show variables like 'long_query_time';複製程式碼

測試慢查詢日誌：

mysql> select sleep(2);
+----------+
| sleep(2) |
+----------+
|        0 |
+----------+
1 row in set (2.00 sec)複製程式碼

開啟慢查詢日誌檔案

[root@localhost mysql]# vim /var/lib/mysql/slow-query.log
/usr/sbin/mysqld, Version: 5.7.19-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2017-10-05T04:39:11.408964Z
# User@Host: root[root] @ localhost []  Id:     3
# Query_time: 2.001395  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use test;
SET timestamp=1507178351;
select sleep(2);複製程式碼

我們可以看到剛才執行了 2 秒的 SQL 語句被記錄下來了。

雖然在慢查詢日誌中記錄查詢慢的 SQL 資訊，但是日誌記錄的內容密集且不易查閱。因此，我們需要通過工具將 SQL 篩選出來。

MySQL 提供 mysqldumpslow 工具對日誌進行分析。我們可以使用 mysqldumpslow --help 檢視命令相關用法。

常用引數如下：

    -s：排序方式，後邊接著如下引數
        c：訪問次數
        l：鎖定時間
        r：返回記錄
        t：查詢時間
    al：平均鎖定時間
    ar：平均返回記錄書
    at：平均查詢時間
    -t：返回前面多少條的資料
    -g：翻遍搭配一個正規表示式，大小寫不敏感複製程式碼

案例：

獲取返回記錄集最多的10個sql
mysqldumpslow -s r -t 10 /var/lib/mysql/slow-query.log

獲取訪問次數最多的10個sql
mysqldumpslow -s c -t 10 /var/lib/mysql/slow-query.log

獲取按照時間排序的前10條裡面含有左連線的查詢語句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow-query.log複製程式碼

五、分析 SQL 語句

5.1 方式一：explain

篩選出有問題的 SQL，我們可以使用 MySQL 提供的 explain 檢視 SQL 執行計劃情況（關聯表，表查詢順序、索引使用情況等）。

用法：

explain select * from category;複製程式碼

返回結果：

mysql> explain select * from category;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | category | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)複製程式碼

欄位解釋：
1) id：select 查詢序列號。id相同，執行順序由上至下；id不同，id值越大優先順序越高，越先被執行

2) select_type：查詢資料的操作型別，其值如下：

simple：簡單查詢，不包含子查詢或 union
primary:包含複雜的子查詢，最外層查詢標記為該值
subquery：在 select 或 where 包含子查詢，被標記為該值
derived：在 from 列表中包含的子查詢被標記為該值，MySQL 會遞迴執行這些子查詢，把結果放在臨時表
union：若第二個 select 出現在 union 之後，則被標記為該值。若 union 包含在 from 的子查詢中，外層 select 被標記為 derived    
union result：從 union 表獲取結果的 select複製程式碼

3) table：顯示該行資料是關於哪張表

4) partitions：匹配的分割槽

5) type：表的連線型別，其值，效能由高到底排列如下：

system：表只有一行記錄，相當於系統表
const：通過索引一次就找到，只匹配一行資料
eq_ref：唯一性索引掃描，對於每個索引鍵，表中只有一條記錄與之匹配。常用於主鍵或唯一索引掃描
ref：非唯一性索引掃描，返回匹配某個單獨值的所有行。用於=、< 或 > 操作符帶索引的列
range：只檢索給定範圍的行，使用一個索引來選擇行。一般使用between、>、<情況
index：只遍歷索引樹
ALL：全表掃描，效能最差複製程式碼

注：前5種情況都是理想情況的索引使用情況。通常優化至少到range級別，最好能優化到 ref

6) possible_keys：指出 MySQL 使用哪個索引在該表找到行記錄。如果該值為 NULL，說明沒有使用索引，可以建立索引提高效能

7) key：顯示 MySQL 實際使用的索引。如果為 NULL，則沒有使用索引查詢

8) key_len：表示索引中使用的位元組數，通過該列計算查詢中使用的索引的長度。在不損失精確性的情況下，長度越短越好
顯示的是索引欄位的最大長度，並非實際使用長度

9) ref：顯示該表的索引欄位關聯了哪張表的哪個欄位

10) rows：根據表統計資訊及選用情況，大致估算出找到所需的記錄或所需讀取的行數，數值越小越好

11) filtered：返回結果的行數佔讀取行數的百分比，值越大越好

12) extra： 包含不合適在其他列中顯示但十分重要的額外資訊，常見的值如下：

using filesort：說明 MySQL 會對資料使用一個外部的索引排序，而不是按照表內的索引順序進行讀取。出現該值，應該優化 SQL
using temporary：使用了臨時表儲存中間結果，MySQL 在對查詢結果排序時使用臨時表。常見於排序 order by 和分組查詢 group by。出現該值，應該優化 SQL 
using index：表示相應的 select 操作使用了覆蓋索引，避免了訪問表的資料行，效率不錯
using where：where 子句用於限制哪一行
using join buffer：使用連線快取
distinct：發現第一個匹配後，停止為當前的行組合搜尋更多的行複製程式碼

注意：出現前 2 個值，SQL 語句必須要優化。

5.2 方式二：profiling

使用 profiling 命令可以瞭解 SQL 語句消耗資源的詳細資訊（每個執行步驟的開銷）。

5.2.1 檢視 profile 開啟情況

select @@profiling;複製程式碼

返回結果：

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)複製程式碼

0 表示關閉狀態,1 表示開啟

5.2.2 啟用 profile

set profiling = 1;複製程式碼

返回結果：

mysql> set profiling = 1;  
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
1 row in set, 1 warning (0.00 sec)複製程式碼

在連線關閉後，profiling 狀態自動設定為關閉狀態。

5.2.3 檢視執行的 SQL 列表

show profiles;複製程式碼

返回結果：

mysql> show profiles;
+----------+------------+------------------------------+
| Query_ID | Duration   | Query                        |
+----------+------------+------------------------------+
|        1 | 0.00062925 | select @@profiling           |
|        2 | 0.00094150 | show tables                  |
|        3 | 0.00119125 | show databases               |
|        4 | 0.00029750 | SELECT DATABASE()            |
|        5 | 0.00025975 | show databases               |
|        6 | 0.00023050 | show tables                  |
|        7 | 0.00042000 | show tables                  |
|        8 | 0.00260675 | desc role                    |
|        9 | 0.00074900 | select name,is_key from role |
+----------+------------+------------------------------+
9 rows in set, 1 warning (0.00 sec)複製程式碼

該命令執行之前，需要執行其他 SQL 語句才有記錄。

5.2.4 查詢指定 ID 的執行詳細資訊

show profile for query Query_ID;複製程式碼

返回結果：

mysql> show profile for query 9;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000207 |
| checking permissions | 0.000010 |
| Opening tables       | 0.000042 |
| init                 | 0.000050 |
| System lock          | 0.000012 |
| optimizing           | 0.000003 |
| statistics           | 0.000011 |
| preparing            | 0.000011 |
| executing            | 0.000002 |
| Sending data         | 0.000362 |
| end                  | 0.000006 |
| query end            | 0.000006 |
| closing tables       | 0.000006 |
| freeing items        | 0.000011 |
| cleaning up          | 0.000013 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)複製程式碼

每行都是狀態變化的過程以及它們持續的時間。Status 這一列和 show processlist 的 State 是一致的。因此，需要優化的注意點與上文描述的一樣。

其中，Status 欄位的值同樣可以參考末尾連結。

5.2.5 獲取 CPU、 Block IO 等資訊

show profile block io,cpu for query Query_ID;

show profile cpu,block io,memory,swaps,context switches,source for query Query_ID;

show profile all for query Query_ID;複製程式碼

六、優化手段

主要以查詢優化、索引使用和表結構設計方面進行講解。

6.1 查詢優化

1) 避免 SELECT *，需要什麼資料，就查詢對應的欄位。

2) 小表驅動大表，即小的資料集驅動大的資料集。如：以 A，B 兩表為例，兩表通過 id 欄位進行關聯。

當 B 表的資料集小於 A 表時，用 in 優化 exist；使用 in ，兩表執行順序是先查 B 表，再查 A 表
select * from A where id in (select id from B)

當 A 表的資料集小於 B 表時，用 exist 優化 in；使用 exists，兩表執行順序是先查 A 表，再查 B 表
select * from A where exists (select 1 from B where B.id = A.id)複製程式碼

3) 一些情況下，可以使用連線代替子查詢，因為使用 join，MySQL 不會在記憶體中建立臨時表。

4) 適當新增冗餘欄位，減少表關聯。

5) 合理使用索引（下文介紹）。如：為排序、分組欄位建立索引，避免 filesort 的出現。

6.2 索引使用

6.2.1 適合使用索引的場景

1) 主鍵自動建立唯一索引

2) 頻繁作為查詢條件的欄位

3) 查詢中與其他表關聯的欄位

4) 查詢中排序的欄位

5) 查詢中統計或分組欄位

6.2.2 不適合使用索引的場景

1) 頻繁更新的欄位

2) where 條件中用不到的欄位

3) 表記錄太少

4) 經常增刪改的表

5) 欄位的值的差異性不大或重複性高

6.2.3 索引建立和使用原則

1) 單表查詢：哪個列作查詢條件，就在該列建立索引

2) 多表查詢：left join 時，索引新增到右表關聯欄位；right join 時，索引新增到左表關聯欄位

3) 不要對索引列進行任何操作（計算、函式、型別轉換）

4) 索引列中不要使用 !=，<> 非等於

5) 索引列不要為空，且不要使用 is null 或 is not null 判斷

6) 索引欄位是字串型別，查詢條件的值要加''單引號,避免底層型別自動轉換

違背上述原則可能會導致索引失效，具體情況需要使用 explain 命令進行檢視

6.2.4 索引失效情況

除了違背索引建立和使用原則外，如下情況也會導致索引失效：

1) 模糊查詢時，以 % 開頭

2) 使用 or 時，如：欄位1（非索引）or 欄位2（索引）會導致索引失效。

3) 使用複合索引時，不使用第一個索引列。

index(a,b,c) ，以欄位 a,b,c 作為複合索引為例：

語句	索引是否生效
where a = 1	是，欄位 a 索引生效
where a = 1 and b = 2	是，欄位 a 和 b 索引生效
where a = 1 and b = 2 and c = 3	是，全部生效
where b = 2 或 where c = 3	否
where a = 1 and c = 3	欄位 a 生效，欄位 c 失效
where a = 1 and b > 2 and c = 3	欄位 a，b 生效，欄位 c 失效
where a = 1 and b like 'xxx%' and c = 3	欄位 a，b 生效，欄位 c 失效

6.3 資料庫表結構設計

6.3.1 選擇合適的資料型別

1) 使用可以存下資料最小的資料型別

2) 使用簡單的資料型別。int 要比 varchar 型別在mysql處理簡單

3) 儘量使用 tinyint、smallint、mediumint 作為整數型別而非 int

4) 儘可能使用 not null 定義欄位，因為 null 佔用4位元組空間

5) 儘量少用 text 型別,非用不可時最好考慮分表

6) 儘量使用 timestamp 而非 datetime

7) 單表不要有太多欄位，建議在 20 以內

6.3.2 表的拆分

當資料庫中的資料非常大時，查詢優化方案也不能解決查詢速度慢的問題時，我們可以考慮拆分表，讓每張表的資料量變小，從而提高查詢效率。

1) 垂直拆分：將表中多個列分開放到不同的表中。例如使用者表中一些欄位經常被訪問，將這些欄位放在一張表中，另外一些不常用的欄位放在另一張表中。
插入資料時，使用事務確保兩張表的資料一致性。

2) 水平拆分：按照行進行拆分。例如使用者表中，使用使用者ID，對使用者ID取10的餘數，將使用者資料均勻的分配到0~9的10個使用者表中。查詢時也按照這個規則查詢資料。

6.3.3 讀寫分離

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

七、伺服器引數調優

7.1 記憶體相關

sort_buffer_size 排序緩衝區記憶體大小

join_buffer_size 使用連線緩衝區大小

read_buffer_size 全表掃描時分配的緩衝區大小

7.2 IO 相關

Innodb_log_file_size 事務日誌大小

Innodb_log_files_in_group 事務日誌個數

Innodb_log_buffer_size 事務日誌緩衝區大小

Innodb_flush_log_at_trx_commit 事務日誌重新整理策略 ，其值如下：

0：每秒進行一次 log 寫入 cache，並 flush log 到磁碟

1：在每次事務提交執行 log 寫入 cache，並 flush log 到磁碟

2：每次事務提交，執行 log 資料寫到 cache，每秒執行一次 flush log 到磁碟

7.3 安全相關

expire_logs_days 指定自動清理 binlog 的天數

max_allowed_packet 控制 MySQL 可以接收的包的大小

skip_name_resolve 禁用 DNS 查詢

read_only 禁止非 super 許可權使用者寫許可權

skip_slave_start 級你用 slave 自動恢復

7.4 其他

max_connections 控制允許的最大連線數

tmp_table_size 臨時表大小

max_heap_table_size 最大記憶體表大小

筆者並沒有使用這些引數對 MySQL 伺服器進行調優，具體詳情介紹和效能效果請參考文章末尾的資料或另行百度。

八、硬體選購和引數優化

硬體的效能直接決定 MySQL 資料庫的效能。硬體的效能瓶頸，直接決定 MySQL 資料庫的執行資料和效率。

作為軟體開發程式設計師，我們主要關注軟體方面的優化內容，以下硬體方面的優化作為了解即可

8.1 記憶體相關

記憶體的 IO 比硬碟的速度快很多，可以增加系統的緩衝區容量，使資料在記憶體停留的時間更長，以減少磁碟的 IO

8.2 磁碟 I/O 相關

1) 使用 SSD 或 PCle SSD 裝置，至少獲得數百倍甚至萬倍的 IOPS 提升

2) 購置陣列卡同時配備 CACHE 及 BBU 模組，可以明顯提升 IOPS

3) 儘可能選用 RAID-10，而非 RAID-5

8.3 配置 CUP 相關

在伺服器的 BIOS 設定中，調整如下配置：

1) 選擇 Performance Per Watt Optimized（DAPC）模式，發揮 CPU 最大效能

2) 關閉 C1E 和 C States 等選項，提升 CPU 效率

3) Memory Frequency（記憶體頻率）選擇 Maximum Performance

九、參考資料

• dev.mysql.com/doc/refman/… show status 語法
• dev.mysql.com/doc/refman/… show processlist 語法
• dev.mysql.com/doc/refman/… 執行緒狀態
• dev.mysql.com/doc/refman/… explain 語法
• dev.mysql.com/doc/refman/… show profile 語法
• blog.csdn.net/nightelve/a… MySQL 伺服器引數調優
• blog.csdn.net/qq_22929803… MySQL 伺服器引數調優
• blog.chinaunix.net/uid-1164064…
• segmentfault.com/a/119000000…
• blog.csdn.net/gzh0222/art…