在配置文件 my.cnf 中的 [mysqld] 一行下邊添加兩個(gè)參數(shù)： 

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

其中，slowquerylog = 1 表示開(kāi)啟慢查詢(xún)；slowquerylogfile 表示慢查詢(xún)?nèi)罩敬娣诺奈恢?；longquerytime = 2 表示查詢(xún) >=2 秒才記錄日志；logqueriesnotusing_indexes = 1 記錄沒(méi)有使用索引的 SQL 語(yǔ)句。

注意：slowquerylog_file 的路徑不能隨便寫(xiě)，否則 MySQL 服務(wù)器可能沒(méi)有權(quán)限將日志文件寫(xiě)到指定的目錄中。建議直接復(fù)制上文的路徑。

修改保存文件后，重啟 MySQL 服務(wù)。在 /var/lib/mysql/ 目錄下會(huì)創(chuàng)建 slow-query.log 日志文件。連接 MySQL 服務(wù)端執(zhí)行如下命令可以查看配置情況。 

show variables like 'slow_query%';  
show variables like 'long_query_time'; 

測(cè)試慢查詢(xún)?nèi)罩荆?nbsp;

mysql> select sleep(2);  
+----------+  
| sleep(2) |  
+----------+  
|        0 |  
+----------+  
1 row in set (2.00 sec) 

打開(kāi)慢查詢(xún)?nèi)罩疚募?nbsp;

[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); 

我們可以看到剛才執(zhí)行了 2 秒的 SQL 語(yǔ)句被記錄下來(lái)了。

雖然在慢查詢(xún)?nèi)罩局杏涗洸樵?xún)慢的 SQL 信息，但是日志記錄的內(nèi)容密集且不易查閱。因此，我們需要通過(guò)工具將 SQL 篩選出來(lái)。

MySQL 提供 mysqldumpslow 工具對(duì)日志進(jìn)行分析。我們可以使用 mysqldumpslow --help 查看命令相關(guān)用法。

常用參數(shù)如下：   

-s：排序方式，后邊接著如下參數(shù)  
       c：訪問(wèn)次數(shù)  
       l：鎖定時(shí)間  
       r：返回記錄  
       t：查詢(xún)時(shí)間  
   al：平均鎖定時(shí)間  
   ar：平均返回記錄書(shū)  
   at：平均查詢(xún)時(shí)間  
   -t：返回前面多少條的數(shù)據(jù)  
   -g：翻遍搭配一個(gè)正則表達(dá)式，大小寫(xiě)不敏感 

案例： 

獲取返回記錄集最多的10個(gè)sql  
mysqldumpslow -s r -t 10 /var/lib/mysql/slow-query.log  
獲取訪問(wèn)次數(shù)最多的10個(gè)sql  
mysqldumpslow -s c -t 10 /var/lib/mysql/slow-query.log 
獲取按照時(shí)間排序的前10條里面含有左連接的查詢(xún)語(yǔ)句  
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow-query.log 

五、分析 SQL 語(yǔ)句

5.1 方式一：explain

篩選出有問(wèn)題的 SQL，我們可以使用 MySQL 提供的 explain 查看 SQL 執(zhí)行計(jì)劃情況（關(guān)聯(lián)表，表查詢(xún)順序、索引使用情況等）。

用法： 

explain select * from category; 

返回結(jié)果： 

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) 

字段解釋?zhuān)?) id：select 查詢(xún)序列號(hào)。id相同，執(zhí)行順序由上至下；id不同，id值越大優(yōu)先級(jí)越高，越先被執(zhí)行

2) select_type：查詢(xún)數(shù)據(jù)的操作類(lèi)型，其值如下：

•  simple：簡(jiǎn)單查詢(xún)，不包含子查詢(xún)或 union
•  primary:包含復(fù)雜的子查詢(xún)，最外層查詢(xún)標(biāo)記為該值
•  subquery：在 select 或 where 包含子查詢(xún)，被標(biāo)記為該值
•  derived：在 from 列表中包含的子查詢(xún)被標(biāo)記為該值，MySQL 會(huì)遞歸執(zhí)行這些子查詢(xún)，把結(jié)果放在臨時(shí)表
•  union：若第二個(gè) select 出現(xiàn)在 union 之后，則被標(biāo)記為該值。若 union 包含在 from 的子查詢(xún)中，外層 select 被標(biāo)記為 derived
•  union result：從 union 表獲取結(jié)果的 select

3) table：顯示該行數(shù)據(jù)是關(guān)于哪張表

4) partitions：匹配的分區(qū)

5) type：表的連接類(lèi)型，其值，性能由高到底排列如下：

•  system：表只有一行記錄，相當(dāng)于系統(tǒng)表
•  const：通過(guò)索引一次就找到，只匹配一行數(shù)據(jù)
•  eq_ref：唯一性索引掃描，對(duì)于每個(gè)索引鍵，表中只有一條記錄與之匹配。常用于主鍵或唯一索引掃描
•  ref：非唯一性索引掃描，返回匹配某個(gè)單獨(dú)值的所有行。用于=、< 或 > 操作符帶索引的列
•  range：只檢索給定范圍的行，使用一個(gè)索引來(lái)選擇行。一般使用between、>、<情況
•  index：只遍歷索引樹(shù)
•  ALL：全表掃描，性能最差

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

6) possible_keys：指出 MySQL 使用哪個(gè)索引在該表找到行記錄。如果該值為 NULL，說(shuō)明沒(méi)有使用索引，可以建立索引提高性能

7) key：顯示 MySQL 實(shí)際使用的索引。如果為 NULL，則沒(méi)有使用索引查詢(xún)

8) key_len：表示索引中使用的字節(jié)數(shù)，通過(guò)該列計(jì)算查詢(xún)中使用的索引的長(zhǎng)度。在不損失精確性的情況下，長(zhǎng)度越短越好 顯示的是索引字段的最大長(zhǎng)度，并非實(shí)際使用長(zhǎng)度

9) ref：顯示該表的索引字段關(guān)聯(lián)了哪張表的哪個(gè)字段

10) rows：根據(jù)表統(tǒng)計(jì)信息及選用情況，大致估算出找到所需的記錄或所需讀取的行數(shù)，數(shù)值越小越好

11) filtered：返回結(jié)果的行數(shù)占讀取行數(shù)的百分比，值越大越好

12) extra：包含不合適在其他列中顯示但十分重要的額外信息，常見(jiàn)的值如下：

•  using filesort：說(shuō)明 MySQL 會(huì)對(duì)數(shù)據(jù)使用一個(gè)外部的索引排序，而不是按照表內(nèi)的索引順序進(jìn)行讀取。出現(xiàn)該值，應(yīng)該優(yōu)化 SQL
•  using temporary：使用了臨時(shí)表保存中間結(jié)果，MySQL 在對(duì)查詢(xún)結(jié)果排序時(shí)使用臨時(shí)表。常見(jiàn)于排序 order by 和分組查詢(xún) group by。出現(xiàn)該值，應(yīng)該優(yōu)化 SQL
•  using index：表示相應(yīng)的 select 操作使用了覆蓋索引，避免了訪問(wèn)表的數(shù)據(jù)行，效率不錯(cuò)
•  using where：where 子句用于限制哪一行
•  using join buffer：使用連接緩存
•  distinct：發(fā)現(xiàn)第一個(gè)匹配后，停止為當(dāng)前的行組合搜索更多的行

注意：出現(xiàn)前 2 個(gè)值，SQL 語(yǔ)句必須要優(yōu)化。

5.2 方式二：profiling

使用 profiling 命令可以了解 SQL 語(yǔ)句消耗資源的詳細(xì)信息（每個(gè)執(zhí)行步驟的開(kāi)銷(xiāo)）。

5.2.1 查看 profile 開(kāi)啟情況 

select @@profiling; 

返回結(jié)果： 

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

0 表示關(guān)閉狀態(tài),1 表示開(kāi)啟

5.2.2 啟用 profile 

set profiling = 1;   

返回結(jié)果： 

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) 

在連接關(guān)閉后，profiling 狀態(tài)自動(dòng)設(shè)置為關(guān)閉狀態(tài)。

5.2.3 查看執(zhí)行的 SQL 列表 

show profiles; 

返回結(jié)果： 

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) 

該命令執(zhí)行之前，需要執(zhí)行其他 SQL 語(yǔ)句才有記錄。

5.2.4 查詢(xún)指定 ID 的執(zhí)行詳細(xì)信息

show profile for query Query_ID; 

返回結(jié)果： 

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) 

每行都是狀態(tài)變化的過(guò)程以及它們持續(xù)的時(shí)間。Status 這一列和 show processlist 的 State 是一致的。因此，需要優(yōu)化的注意點(diǎn)與上文描述的一樣。

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; 

六、優(yōu)化手段

主要以查詢(xún)優(yōu)化、索引使用和表結(jié)構(gòu)設(shè)計(jì)方面進(jìn)行講解。

6.1 查詢(xún)優(yōu)化

1) 避免 SELECT *，需要什么數(shù)據(jù)，就查詢(xún)對(duì)應(yīng)的字段。

2) 小表驅(qū)動(dòng)大表，即小的數(shù)據(jù)集驅(qū)動(dòng)大的數(shù)據(jù)集。如：以 A，B 兩表為例，兩表通過(guò) id 字段進(jìn)行關(guān)聯(lián)。

當(dāng) B 表的數(shù)據(jù)集小于 A 表時(shí)，用 in 優(yōu)化 exist；使用 in ，兩表執(zhí)行順序是先查 B 表，再查 A 表 

select * from A where id in (select id from B) 

當(dāng) A 表的數(shù)據(jù)集小于 B 表時(shí)，用 exist 優(yōu)化 in；使用 exists，兩表執(zhí)行順序是先查 A 表，再查 B 表 

select * from A where exists (select 1 from B where B.id = A.id) 

3) 一些情況下，可以使用連接代替子查詢(xún)，因?yàn)槭褂?join，MySQL 不會(huì)在內(nèi)存中創(chuàng)建臨時(shí)表。

4) 適當(dāng)添加冗余字段，減少表關(guān)聯(lián)。

5) 合理使用索引（下文介紹）。如：為排序、分組字段建立索引，避免 filesort 的出現(xiàn)。更多：來(lái)一份MySQL索引數(shù)據(jù)結(jié)構(gòu)及優(yōu)化清單

6.2 索引使用

6.2.1 適合使用索引的場(chǎng)景

1) 主鍵自動(dòng)創(chuàng)建唯一索引

2) 頻繁作為查詢(xún)條件的字段

3) 查詢(xún)中與其他表關(guān)聯(lián)的字段

4) 查詢(xún)中排序的字段

5) 查詢(xún)中統(tǒng)計(jì)或分組字段

6.2.2 不適合使用索引的場(chǎng)景

1) 頻繁更新的字段

2) where 條件中用不到的字段

3) 表記錄太少

4) 經(jīng)常增刪改的表

5) 字段的值的差異性不大或重復(fù)性高

6.2.3 索引創(chuàng)建和使用原則

1) 單表查詢(xún)：哪個(gè)列作查詢(xún)條件，就在該列創(chuàng)建索引

2) 多表查詢(xún)：left join 時(shí)，索引添加到右表關(guān)聯(lián)字段；right join 時(shí)，索引添加到左表關(guān)聯(lián)字段

3) 不要對(duì)索引列進(jìn)行任何操作（計(jì)算、函數(shù)、類(lèi)型轉(zhuǎn)換）

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

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

6) 索引字段是字符串類(lèi)型，查詢(xún)條件的值要加''單引號(hào),避免底層類(lèi)型自動(dòng)轉(zhuǎn)換

違背上述原則可能會(huì)導(dǎo)致索引失效，具體情況需要使用 explain 命令進(jìn)行查看

6.2.4 索引失效情況

除了違背索引創(chuàng)建和使用原則外，如下情況也會(huì)導(dǎo)致索引失效：

1) 模糊查詢(xún)時(shí)，以 % 開(kāi)頭

2) 使用 or 時(shí)，如：字段1（非索引）or 字段2（索引）會(huì)導(dǎo)致索引失效。

3) 使用復(fù)合索引時(shí)，不使用第一個(gè)索引列。

index(a,b,c) ，以字段 a,b,c 作為復(fù)合索引為例：

6.3.1 選擇合適的數(shù)據(jù)類(lèi)型6.3 數(shù)據(jù)庫(kù)表結(jié)構(gòu)設(shè)計(jì)

1) 使用可以存下數(shù)據(jù)最小的數(shù)據(jù)類(lèi)型

2) 使用簡(jiǎn)單的數(shù)據(jù)類(lèi)型。int 要比 varchar 類(lèi)型在mysql處理簡(jiǎn)單

3) 盡量使用 tinyint、smallint、mediumint 作為整數(shù)類(lèi)型而非 int

4) 盡可能使用 not null 定義字段，因?yàn)?null 占用4字節(jié)空間

5) 盡量少用 text 類(lèi)型,非用不可時(shí)最好考慮分表

6) 盡量使用 timestamp 而非 datetime

7) 單表不要有太多字段，建議在 20 以?xún)?nèi)

6.3.2 表的拆分

當(dāng)數(shù)據(jù)庫(kù)中的數(shù)據(jù)非常大時(shí)，查詢(xún)優(yōu)化方案也不能解決查詢(xún)速度慢的問(wèn)題時(shí)，我們可以考慮拆分表，讓每張表的數(shù)據(jù)量變小，從而提高查詢(xún)效率。

1) 垂直拆分：將表中多個(gè)列分開(kāi)放到不同的表中。例如用戶(hù)表中一些字段經(jīng)常被訪問(wèn)，將這些字段放在一張表中，另外一些不常用的字段放在另一張表中。插入數(shù)據(jù)時(shí)，使用事務(wù)確保兩張表的數(shù)據(jù)一致性。

2) 水平拆分：按照行進(jìn)行拆分。例如用戶(hù)表中，使用用戶(hù)ID，對(duì)用戶(hù)ID取10的余數(shù)，將用戶(hù)數(shù)據(jù)均勻的分配到0~9的10個(gè)用戶(hù)表中。查找時(shí)也按照這個(gè)規(guī)則查詢(xún)數(shù)據(jù)。

6.3.3 讀寫(xiě)分離

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

七、服務(wù)器參數(shù)調(diào)優(yōu)

7.1 內(nèi)存相關(guān)

sortbuffersize 排序緩沖區(qū)內(nèi)存大小

joinbuffersize 使用連接緩沖區(qū)大小

readbuffersize 全表掃描時(shí)分配的緩沖區(qū)大小

7.2 IO 相關(guān)

Innodblogfile_size 事務(wù)日志大小

Innodblogfilesingroup 事務(wù)日志個(gè)數(shù)

Innodblogbuffer_size 事務(wù)日志緩沖區(qū)大小

Innodbflushlogattrx_commit 事務(wù)日志刷新策略 ，其值如下：

0：每秒進(jìn)行一次 log 寫(xiě)入 cache，并 flush log 到磁盤(pán)

1：在每次事務(wù)提交執(zhí)行 log 寫(xiě)入 cache，并 flush log 到磁盤(pán)

2：每次事務(wù)提交，執(zhí)行 log 數(shù)據(jù)寫(xiě)到 cache，每秒執(zhí)行一次 flush log 到磁盤(pán)

7.3 安全相關(guān)

expirelogsdays 指定自動(dòng)清理 binlog 的天數(shù)

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

skipnameresolve 禁用 DNS 查找

read_only 禁止非 super 權(quán)限用戶(hù)寫(xiě)權(quán)限

skipslavestart 級(jí)你用 slave 自動(dòng)恢復(fù)

7.4 其他

max_connections 控制允許的最大連接數(shù)

tmptablesize 臨時(shí)表大小

maxheaptable_size 最大內(nèi)存表大小

筆者并沒(méi)有使用這些參數(shù)對(duì) MySQL 服務(wù)器進(jìn)行調(diào)優(yōu)，具體詳情介紹和性能效果請(qǐng)參考文章末尾的資料或另行百度。

八、硬件選購(gòu)和參數(shù)優(yōu)化

硬件的性能直接決定 MySQL 數(shù)據(jù)庫(kù)的性能。硬件的性能瓶頸，直接決定 MySQL 數(shù)據(jù)庫(kù)的運(yùn)行數(shù)據(jù)和效率。

作為軟件開(kāi)發(fā)程序員，我們主要關(guān)注軟件方面的優(yōu)化內(nèi)容，以下硬件方面的優(yōu)化作為了解即可

8.1 內(nèi)存相關(guān)

內(nèi)存的 IO 比硬盤(pán)的速度快很多，可以增加系統(tǒng)的緩沖區(qū)容量，使數(shù)據(jù)在內(nèi)存停留的時(shí)間更長(zhǎng)，以減少磁盤(pán)的 IO

8.2 磁盤(pán) I/O 相關(guān)

1) 使用 SSD 或 PCle SSD 設(shè)備，至少獲得數(shù)百倍甚至萬(wàn)倍的 IOPS 提升

2) 購(gòu)置陣列卡同時(shí)配備 CACHE 及 BBU 模塊，可以明顯提升 IOPS

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

8.3 配置 CUP 相關(guān)

在服務(wù)器的 BIOS 設(shè)置中，調(diào)整如下配置：

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

2) 關(guān)閉 C1E 和 C States 等選項(xiàng)，提升 CPU 效率

3) Memory Frequency（內(nèi)存頻率）選擇 Maximum Performance