1. 數(shù)據(jù)庫(kù)服務(wù)器的優(yōu)化步驟

當(dāng)我們遇到數(shù)據(jù)庫(kù)調(diào)優(yōu)問(wèn)題的時(shí)候，該如何思考呢？這里把思考的流程整理成下面這張圖。

整個(gè)流程劃分成了 觀察（Show status） 和 行動(dòng)（Action） 兩個(gè)部分。字母 S 的部分代表觀察（會(huì)使用相應(yīng)的分析工具），字母 A 代表的部分是行動(dòng)（對(duì)應(yīng)分析可以采取的行動(dòng)）。

主要的優(yōu)化手段：

1. 加緩存，例如redis
2. 調(diào)整服務(wù)器參數(shù)
3. 索引的設(shè)計(jì)優(yōu)化，SQL語(yǔ)句是否使用到索引？
4. 讀寫，主從分離，分庫(kù)分表（垂直分庫(kù)，垂直分表，水平分表）

2. 系統(tǒng)參數(shù)

在MySQL中，可以使用 SHOW STATUS 語(yǔ)句查詢一些MySQL數(shù)據(jù)庫(kù)服務(wù)器的 性能參數(shù) 、 執(zhí)行頻率 。SHOW STATUS語(yǔ)句語(yǔ)法如下：

SHOW [GLOBAL|SESSION] STATUS LIKE '參數(shù)';

global：全局
session：當(dāng)前會(huì)話

一些常用的性能參數(shù)如下：

1. Connections：連接MySQL服務(wù)器的次數(shù)。
2. Uptime：MySQL服務(wù)器的上線時(shí)間。
3. Slow_queries：慢查詢的次數(shù)。
4. Innodb_rows_read：Select查詢返回的行數(shù)
5. Innodb_rows_inserted：執(zhí)行INSERT操作插入的行數(shù)
6. Innodb_rows_updated：執(zhí)行UPDATE操作更新的行數(shù)
7. Innodb_rows_deleted：執(zhí)行DELETE操作刪除的行數(shù)
8. Com_select：查詢操作的次數(shù)。
9. Com_insert：插入操作的次數(shù)。對(duì)于批量插入的 INSERT 操作，只累加一次。
10. Com_update：更新操作的次數(shù)。
11. Com_delete：刪除操作的次數(shù)。

show status like 'last_query_cost'查詢最近一次查詢涉及到的頁(yè)數(shù)

3. 慢查詢?nèi)罩?/h1>

具體允許時(shí)長(zhǎng)超過(guò)long_query_time值的sql會(huì)被記錄到慢查詢?nèi)罩局?#xff0c;默認(rèn)為10，注意不包含10秒，需要超過(guò)10秒才會(huì)被記錄到慢查詢?nèi)罩局?/p>

默認(rèn)情況下是不開啟慢查詢?nèi)罩镜?#xff0c;需要手動(dòng)打開

show variables like '%slow_query_log'

開啟慢查詢?nèi)罩?#xff1a;

set global slow_query_log='ON';

查看慢日志文件位置

show variables like '%slow_query_log%'

查詢慢查詢的時(shí)間閾值

show variables like '%long_query_time%';

設(shè)置慢查詢閾值
set global long_query_time = 1;

設(shè)置global的方式對(duì)當(dāng)前session的long_query_time失效。對(duì)新連接的客戶端有效。所以可以一并執(zhí)行下述語(yǔ)句

set global long_query_time = 1;
set long_query_time = 1;

mysql服務(wù)器重啟后，設(shè)置的慢查詢時(shí)間閾值就會(huì)失效，可以通過(guò)配置文件的方式進(jìn)行永久設(shè)置

slow_query_log=ON
slow_query_log_file=/var/lib/mysql/xxxx-slow.log
long_query_time=1
log_output=FILE

如果不指定存儲(chǔ)路徑，則默認(rèn)將慢查詢?nèi)罩敬鎯?chǔ)到mysql數(shù)據(jù)庫(kù)的數(shù)據(jù)文件夾下，不指定文件名的話，默認(rèn)文件名為hostname-slow.log

查看慢查詢數(shù)目
SHOW GLOBAL STATUS LIKE '%Slow_queries%';

4. 定位慢sql

4.1 開啟慢查詢?nèi)兆?/h2>

set global slow_query_log='ON';

4.2 設(shè)置慢查詢閾值

set global long_query_time = 1;

4.3 查看慢查詢數(shù)目

查詢當(dāng)前系統(tǒng)中有多少條慢查詢記錄
SHOW GLOBAL STATUS LIKE '%Slow_queries%';

4.4 min_examined_row_limit

除了 long_query_time外，控制慢查詢?nèi)罩镜倪€有一個(gè)系統(tǒng)變量：min_examined_row_limit。這個(gè)變量的意思是，查詢掃描過(guò)的最少記錄數(shù)。這個(gè)變量和查詢執(zhí)行時(shí)間共同組成了判別一個(gè)查詢是否是慢查詢的條件，如果查詢掃描過(guò)的記錄數(shù)大于等于這個(gè)變量的值，并且查詢執(zhí)行時(shí)間超過(guò)了 long_query_time的值，那么這個(gè)查詢就被記錄到慢查詢?nèi)罩局?#xff0c;反之，則不會(huì)被記錄到慢查詢?nèi)罩局?/p>

這個(gè)值默認(rèn)是0，可以通過(guò)set命令來(lái)修改或者修改my.ini文件來(lái)進(jìn)行修改

4.5 具體分析

以上表所示，數(shù)據(jù)量4000000。

執(zhí)行兩條sql，查詢時(shí)長(zhǎng)在1s以上

現(xiàn)在我們的慢sql有5條了（之前有過(guò)別的操作增加了慢sql數(shù)量）

4.6 慢查詢?nèi)罩痉治龉ぞ?#xff1a;mysqldumpslow

在生產(chǎn)環(huán)境中，如果要手工分析日志，查找、分析SQL，顯然是個(gè)體力活，MySQL提供了日志分析工具 mysqldumpslow 。

查看mysqldumpslow的幫助信息 mysqldumpslow --help

mysqldumpslow 命令的具體參數(shù)如下：
-a: 不將數(shù)字抽象成N，字符串抽象成S （不加-a，參數(shù)就會(huì)以N和S替代）
-s: 是表示按照何種方式排序：
c: 訪問(wèn)次數(shù)
l: 鎖定時(shí)間
r: 返回記錄
t: 查詢時(shí)間
al:平均鎖定時(shí)間
ar:平均返回記錄數(shù)
at:平均查詢時(shí)間 （默認(rèn)方式）
ac:平均查詢次數(shù)
-t: 即為返回前面多少條的數(shù)據(jù)；
6 rows in set (2.39 sec)
show status like ‘slow_queries’;
mysqldumpslow --help
-g: 后邊搭配一個(gè)正則匹配模式，大小寫不敏感的；

常用舉例：

#得到返回記錄集最多的10個(gè)SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log
#得到訪問(wèn)次數(shù)最多的10個(gè)SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
#得到按照時(shí)間排序的前10條里面含有左連接的查詢語(yǔ)句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log
#另外建議在使用這些命令時(shí)結(jié)合 | 和more 使用 ，否則有可能出現(xiàn)爆屏情況
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

分析慢日志

mysqldumpslow -s -t -t 10 /var/lib/mysql/master-slow.log

可以觀察到我們的慢sql語(yǔ)句，整個(gè)順序是按照耗時(shí)降序的

開發(fā)中，盡量不打開慢查詢?nèi)罩?#xff0c;會(huì)耗費(fèi)一些性能

如何刪除慢查詢?nèi)罩疚募?rm命令進(jìn)行刪除

如何重置慢查詢?nèi)罩疚募?#xff1f;mysqladmin -uroot -p flush-logs slow

注意：mysqladmin -uroot -p flush-logs 如果不加slow，是重置所有日志文件，包括redo，undo等等。

5 explain

定位了慢查詢?nèi)罩竞?#xff0c;我們可以通過(guò)explain或describe工具對(duì)SQL語(yǔ)句進(jìn)行分析。
describe的使用方法與explain一樣，并且分析結(jié)果也是一樣。

mysql中會(huì)為查詢語(yǔ)句提供它認(rèn)為最優(yōu)的執(zhí)行計(jì)劃，但是并不一定是最優(yōu)的

版本情況
MySQL 5.6.3以前只能 EXPLAIN SELECT ；MYSQL 5.6.3以后就可以 EXPLAIN SELECT，UPDATE，DELETE

在5.7以前的版本中，想要顯示 partitions 需要使用 explain partitions 命令；想要顯示filtered 需要使用 explain extended 命令。在5.7版本后，默認(rèn)explain直接顯示partitions和filtered中的信息。

5.1 語(yǔ)法

EXPLAIN 或 DESCRIBE語(yǔ)句的語(yǔ)法形式如下：

EXPLAIN SELECT select_options
DESCRIBE SELECT select_options

例如


explain的具體各個(gè)結(jié)果的意義

5.2 explain各列作用

準(zhǔn)備表結(jié)構(gòu)與數(shù)據(jù)：每張表含10000條數(shù)據(jù)

5.2.1 table

table：表名

注意：這里的table的個(gè)數(shù)不一定是我們sql語(yǔ)句里的個(gè)數(shù)，還有可能包含臨時(shí)表等。查詢的每一行記錄都對(duì)應(yīng)著一個(gè)單表，臨時(shí)表也會(huì)對(duì)應(yīng)一個(gè)記錄。

5.2.2 id

1. 在一個(gè)大的查詢中，每個(gè)select關(guān)鍵字都對(duì)應(yīng)一個(gè)id
2. 該語(yǔ)句的唯一標(biāo)識(shí)。如果explain的結(jié)果包括多個(gè)id值，則數(shù)字越大越先執(zhí)行
3. 而對(duì)于相同id的行，則表示從上往下依次執(zhí)行。
4. id號(hào)的每個(gè)號(hào)碼代表一趟獨(dú)立的查詢，一個(gè)sql語(yǔ)句查詢趟數(shù)越少越好

舉例：一個(gè)select

SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a';

兩個(gè)select

 EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2

虛表的情況

EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

特殊情況：

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a');

為什么上述sql有兩個(gè)select，但是id就只有1呢？因?yàn)閙ysql優(yōu)化器對(duì)sql語(yǔ)句進(jìn)行了重寫，原sql復(fù)雜度是n*n，優(yōu)化器給優(yōu)化為外連接的sql，即2n復(fù)雜度。

5.2.3 select_type

5.2.4 partitions

代表分區(qū)的命中情況，非分區(qū)表，該值為null，一般情況下，查詢語(yǔ)句的partitions列都為null

5.2.5 type

1. 性能由好到最壞依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
2. SQL 性能優(yōu)化的目標(biāo)：至少要達(dá)到 range 級(jí)別，要求是 ref 級(jí)別，最好是 consts級(jí)別。（阿里巴巴開發(fā)手冊(cè)要求）

5.2.5.1 system

當(dāng)表中只有一條記錄，并且該表使用的存儲(chǔ)引擎的統(tǒng)計(jì)數(shù)據(jù)是精確的，比如 MyISAM，Memory，那么對(duì)該表訪問(wèn)方法就是system

CREATE TABLE t(i int) Engine=MyISAM;INSERT INTO t VALUES(1);EXPLAIN SELECT * FROM t;

5.2.5.2 const

當(dāng)我們根據(jù)主鍵或者唯一二級(jí)索引列與常數(shù)進(jìn)行等值匹配時(shí)，對(duì)單表的訪問(wèn)方法就是 const

EXPLAIN SELECT * FROM s1 WHERE id = 10005;

5.2.5.3 eq_ref

在連接查詢時(shí)，如果被驅(qū)動(dòng)表時(shí)通過(guò)主鍵或者唯一二級(jí)索引列等值匹配的方式進(jìn)行訪問(wèn)的（如果該主鍵或者唯一二級(jí)索引是聯(lián)合索引的話，所有的索引列都必須進(jìn)行等值比較），則對(duì)該被驅(qū)動(dòng)表的訪問(wèn)方法就是eq_ref

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;

5.2.5.4 ref_or_null

當(dāng)通過(guò)普通的二級(jí)索引進(jìn)行等值匹配查詢，該索引列的值也可以是null值時(shí)，那么對(duì)該表的訪問(wèn)方法就可能是ref_or_null

explain select * from s1 where key1 = 'a' or key1 is null

5.2.5.5 index_merge

在某些場(chǎng)景下，可以使用索引合并的方法來(lái)執(zhí)行查詢

explain select * from s1 where key1 = 'a' or key3 = 'a'

但是如果把or換成and

explain select * from s1 where key1 = 'a' and key3 = 'a'

5.2.5.6 unique_subquery

它是針對(duì)在一些包含in子查詢的查詢語(yǔ)句中，如果查詢優(yōu)化器決定將in子查詢轉(zhuǎn)為exists子查詢，并且子查詢可以使用到主鍵進(jìn)行等值匹配的話，那么該子查詢的type就是unique_subquery

EXPLAIN SELECT * FROM s1 WHERE key2 IN (SELECT id FROM s2 where s1.key1 =
s2.key1) OR key3 = 'a';

5.2.5.7 range

如果使用索引獲取某些范圍的記錄，那么有可能使用到range

EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');

5.2.5.8 index

當(dāng)我們可以使用索引覆蓋，但是需要掃描全部索引記錄時(shí)，訪問(wèn)方法就是index

EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';

按照最左匹配原則來(lái)看，應(yīng)該是用不上索引的，但是由于我們查詢的列和篩選條件都在聯(lián)合索引中，所以就用上了索引

例如我們?cè)诓樵冏侄沃卸嗉右涣?/p>

EXPLAIN SELECT key1,key_part2 FROM s1 WHERE key_part3 = 'a';

5.2.5.9 all

EXPLAIN SELECT * FROM s1;

5.2.6 key和possible_keys

possible_keys：代表可能用到的索引

key：實(shí)際使用的索引

5.2.7 key_len

實(shí)際使用到的索引長(zhǎng)度（即字節(jié)數(shù)），值越大越好（主要針對(duì)于聯(lián)合索引）

5.2.8 ref

當(dāng)使用索引列進(jìn)行等值查詢時(shí)，與索引進(jìn)行等值匹配的對(duì)象的信息

select * from s1 where key1 = 'a'

select * from s1 inner join s2 on s1.id = s2.id

select * from s1 inner join s2 on s2.key1 = upper(s1.key1)

5.2.9 rows

預(yù)估需要讀取的記錄的條目數(shù)，該值越小越好

SELECT * FROM s1 WHERE key1 > 'z';

5.2.10 filtered

某個(gè)表經(jīng)過(guò)搜索條件過(guò)濾后剩余記錄數(shù)的百分比

對(duì)于單表查詢來(lái)說(shuō)，這個(gè)filtered沒什么意義，例如

SELECT * FROM s1 WHERE key1 > 'z';

rows是359，filtered是100，那么查詢的數(shù)據(jù)也是359

在連接查詢中，驅(qū)動(dòng)表對(duì)應(yīng)的執(zhí)行計(jì)劃記錄的filtered值，它決定了被驅(qū)動(dòng)表要執(zhí)行的次數(shù)（即 rows*filtered）

SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a';

即key1>'z’的rows有359條數(shù)據(jù)，然后執(zhí)行and common_field=‘a(chǎn)’時(shí)滿足的數(shù)據(jù)量在 359*10%，

5.2.11 Extra

用來(lái)說(shuō)明一些額外信息，包含不適合在其他列中顯示，但十分重要的額外信息，我們可以通過(guò)這些額外信息來(lái)更準(zhǔn)確的理解Mysql到底如何執(zhí)行給定的查詢語(yǔ)句。

例如：

5.2.11.1. No Tables used

select 1

5.2.11.2. Impossible WHERE

SELECT * FROM s1 WHERE 1 != 1;

5.2.11.3. Using where

當(dāng)我們使用全表掃描來(lái)執(zhí)行對(duì)某個(gè)表的查詢，并且該語(yǔ)句的where子句中有針對(duì)該表的搜索條件時(shí)，在Extra中會(huì)提示Using where

SELECT * FROM s1 WHERE common_field = 'a';

5.2.11.4. No matching min/max row

當(dāng)查詢列表處有min或者max聚合函數(shù)，但是并沒有符合where自居中的搜索條件記錄時(shí)，就會(huì)提示該信息

SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg';

那如果不使用函數(shù)呢？

SELECT key1 FROM s1 WHERE key1 = 'abcdefg';

5.2.11.5. Using index

當(dāng)我們的查詢列表以及搜索條件中只包含屬于某個(gè)索引的列，也就是在可以使用覆蓋索引的情況下，在Extra列將會(huì)提示Using index

SELECT key1 FROM s1 WHERE key1 = 'a';

5.2.11.6. Using index condition

有些搜索條件雖然使用到了索引列，但是卻不能使用到索引

SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';

如果查詢語(yǔ)句中的執(zhí)行過(guò)程將要使用索引下推的特性，則extra會(huì)顯示Using index condition

5.2.11.7. join buffer

當(dāng)被驅(qū)動(dòng)表不能有效的利用索引來(lái)加快訪問(wèn)速度，mysql會(huì)為其分配一塊 join buffer的內(nèi)存塊來(lái)加快查詢速度，也就是基于塊的嵌套循環(huán)算法

SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;

5.2.11.8. Using where; Not exists

當(dāng)我們使用左外連接時(shí)，如果where子句中包含要求被驅(qū)動(dòng)表的某個(gè)列等于null的搜索條件，但是那個(gè)列又是不允許為null的，那么在該表的執(zhí)行計(jì)劃的extra列就會(huì)提示 not exists

SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL;

5.2.11.9. Using intersect(…) 、 Using union(…) 和 Using sort_union(…)

就是索引合并的意思，

SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';

5.2.11.10. Zero limit

SELECT * FROM s1 LIMIT 0;

5.2.11.11. Using filesort

 SELECT * FROM s1 ORDER BY common_field LIMIT 10;

因?yàn)槲覀兊腸ommon_field沒有索引，但是如果要做排序，則只能讀取到內(nèi)存中進(jìn)行排序 using filesort。mysql把這種在內(nèi)存中或者磁盤上進(jìn)行排序的方式統(tǒng)稱為文件排序filesort。

如果列有索引，例如key1

 SELECT * FROM s1 ORDER BY key1 LIMIT 10;

5.2.11.12. Using temporary

使用臨時(shí)表。在許多查詢的執(zhí)行過(guò)程中，mysql可能會(huì)借助臨時(shí)表來(lái)完成一些功能，比如去重，排序等，比如許多查詢中包含distinct，groupby，union等子句的查詢過(guò)程中，如果不能有效利用索引來(lái)完成查詢，mysql很有可能尋求建立內(nèi)部臨時(shí)表來(lái)執(zhí)行查詢，如果使用到了臨時(shí)表，則extra會(huì)顯示using temporary

 SELECT DISTINCT common_field FROM s1;

SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field;

例如有索引的情況下

SELECT key1, COUNT(*) AS amount FROM s1 GROUP BY key1;

5.2.11.14 總結(jié)

1. EXPLAIN不考慮各種Cache
2. EXPLAIN不能顯示MySQL在執(zhí)行查詢時(shí)所作的優(yōu)化工作
3. EXPLAIN不會(huì)告訴你關(guān)于觸發(fā)器、存儲(chǔ)過(guò)程的信息或用戶自定義函數(shù)對(duì)查詢的影響情況
4. 部分統(tǒng)計(jì)信息是估算的，并非精確值,例如rows

5.3 explain的進(jìn)一步使用

5.3.1. 傳統(tǒng)格式

傳統(tǒng)格式簡(jiǎn)單明了，輸出是一個(gè)表格形式，概要說(shuō)明查詢計(jì)劃。

5.3.2 json格式

json提示的信息量會(huì)更全面一些。例如查詢成本 query_cost

EXPLAIN FORMAT=JSON SELECT ....

 EXPLAIN FORMAT=JSON SELECT key1, COUNT(*) AS amount FROM s1 GROUP BY key1;

{"query_block": {"select_id": 1,"cost_info": {"query_cost": "1013.75"},"grouping_operation": {"using_filesort": false,"table": {"table_name": "s1","access_type": "index","possible_keys": ["idx_key1"],"key": "idx_key1","used_key_parts": ["key1"],"key_length": "303","rows_examined_per_scan": 9895,"rows_produced_per_join": 9895,"filtered": "100.00","using_index": true,"cost_info": {"read_cost": "24.25","eval_cost": "989.50","prefix_cost": "1013.75","data_read_per_join": "17M"},"used_columns": ["id","key1"]}}}
}

eval_cost 是這樣計(jì)算的：
檢測(cè) rows × filter 條記錄的成本。
prefix_cost 就是單獨(dú)查詢 s1 表的成本，也就是：read_cost + eval_cost
data_read_per_join 表示在此次查詢中需要讀取的數(shù)據(jù)量。

如果是針對(duì)多表連接查詢
被驅(qū)動(dòng)表，可能被讀取多次，這里的 read_cost 和 eval_cost 是訪問(wèn)多次被驅(qū)動(dòng)表后累加起來(lái)的值，大家主要關(guān)注里邊兒的 prefix_cost 的值代表的是整個(gè)連接查詢預(yù)計(jì)的成本，也就是單次查詢驅(qū)動(dòng)表和被驅(qū)動(dòng)表后的成本的和

5.3.3 TREE格式

EXPLAIN FORMAT=tree SELECT ....

TREE格式是8.0.16版本之后引入的新格式，主要根據(jù)查詢的 各個(gè)部分之間的關(guān)系和各部分的執(zhí)行順序來(lái)描述如何查詢。

5.4 索引下推

Using index condition

有些搜索條件雖然使用到了索引列，但是卻不能使用到索引

SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';

key1>'z’可以使用到索引，但是key1 like '%a’卻無(wú)法使用到索引，在以前的mysql版本中，是按照如下步驟進(jìn)行查詢

1. 根據(jù)key1>'z’來(lái)獲取滿足條件的二級(jí)索引記錄
2. 根據(jù)步驟1的結(jié)果得到主鍵值進(jìn)行回表
3. 回表中進(jìn)行l(wèi)ike操作

但是like操作也只涉及到了key1列，所以mysql對(duì)上述進(jìn)行了優(yōu)化

1. 根據(jù)key1>'z’獲取二級(jí)索引
2. 從獲取的二級(jí)索引來(lái)進(jìn)行l(wèi)ike比對(duì)過(guò)濾（如果有值則回表，如果沒有就結(jié)束）
3. 回表

mysql將這個(gè)改進(jìn)成為索引下推

如果查詢語(yǔ)句中的執(zhí)行過(guò)程將要使用索引下推的特性，則extra會(huì)顯示Using index condition