MySQL慢查詢語句分析總結(jié)
我們經(jīng)常會(huì)接觸到MySQL，也經(jīng)常會(huì)遇到一些MySQL的性能問題。我們可以借助慢查詢?nèi)罩竞蚭xplain命令初步分析出SQL語句存在的性能問題
通過SHOW FULL PROCESSLIST查看問題
SHOW FULL PROCESSLIST相當(dāng)于select * from information_schema.processlist可以列出正在運(yùn)行的連接線程，
processlist
說明：
- id 連接id，可以使用kill+連接id的方式關(guān)閉連接(kill 9339)
- user顯示當(dāng)前用戶
- host顯示連接的客戶端IP和端口
- db顯示進(jìn)程連接的數(shù)據(jù)庫
- command顯示當(dāng)前連接的當(dāng)前執(zhí)行的狀態(tài)，sleep、query、connect
- time顯示當(dāng)前狀態(tài)持續(xù)的時(shí)間(秒)
- state顯示當(dāng)前連接的sql語句的執(zhí)行狀態(tài)，copying to tmp table、sorting result、sending data等
- info顯示sql語句,如果發(fā)現(xiàn)比較耗時(shí)的語句可以復(fù)制出來使用explain分析。
慢查詢?nèi)罩?/strong>
慢查詢?nèi)罩臼荕ySQL用于記錄響應(yīng)時(shí)間超過設(shè)置閾值(long_query_time)的SQL語句，默認(rèn)情況下未開啟慢查詢?nèi)罩?，需要手?dòng)配置。
下面我們要記住幾個(gè)常用的屬性:
- slow_query_log:是否開啟慢查詢(ON為開啟，OFF則為關(guān)閉)
- long_query_time:慢查詢閥值，表示SQL語句執(zhí)行時(shí)間超過這個(gè)值就會(huì)記錄,默認(rèn)為10s
- slow_query_log_file:慢查詢?nèi)罩敬鎯?chǔ)的文件路徑
- log_queries_not_using_indexes: 記錄沒有使用索引查詢語句(ON為開啟,OFF為關(guān)閉)
- log_output:日志存儲(chǔ)方式(FILE表示將日志寫入文件,TABLE表示寫入數(shù)據(jù)庫中，默認(rèn)值為FILE,如果存入數(shù)據(jù)庫中，我們可以通過select * from mysql.slow_log的方式去查詢，一般性能要求相對(duì)較高的建議存文件)
我們可以通過show variables like ‘%關(guān)鍵字%’的方式查詢我們?cè)O(shè)置的屬性值
slow
我們有兩種方式設(shè)置我們的屬性,一種是set global 屬性=值的方式(重啟失效)，另一種是配置文件(重啟生效)
命令方式:
- set global slow_query_log=1;
- set global long_query_time=1;
- set global slow_query_log_file='mysql-slow.log'
配置文件方式:
- slow_query_log = 'ON'
- slow_query_log_file = D:/Tools/mysql-8.0.16/slow.log
- long_query_time = 1
- log-queries-not-using-indexes
pt-qurey-digest分析慢查詢語句
percona-toolkit包含了很多實(shí)用強(qiáng)大的mysql工具包，pt-qurey-digest只是其中一個(gè)用于分析慢查詢?nèi)罩臼枪ぞ?。需要去官網(wǎng)下載,使用方法也很簡(jiǎn)單:
- ./pt-query-digest slow2.log >> slow2.txt
即可得出一個(gè)分析結(jié)果:
- # Query 9: 0.00 QPS, 0.00x concurrency, ID 0xF914D8CC2938CE6CAA13F8E57DF04B2F at byte 499246
- # This item is included in the report because it matches --limit.# Scores: V/M = 0.22
- # Time range: 2019-07-08T03:56:12 to 2019-07-12T00:46:28
- # Attribute pct total min max avg 95% stddev median
- # ============ === ======= ======= ======= ======= ======= ======= =======# Count 8 69
- # Exec time 1 147s 1s 3s 2s 3s 685ms 2s
- # Lock time 0 140ms 2ms 22ms 2ms 3ms 2ms 2ms
- # Rows sent 0 0 0 0 0 0 0 0
- # Rows examine 0 23.96M 225.33k 482.77k 355.65k 462.39k 81.66k 345.04k
- # Query size 2 17.72k 263 263 263 263 0 263
- # String:# Databases xxxx# Hosts xx.xxx.xxx.xxx# Users root# Query_time distribution# 1us
- # 10us
- # 100us
- # 1ms
- # 10ms
- # 100ms
- # 1s ################################################################
- # 10s+
- # Tables# SHOW TABLE STATUS FROM `xxxx` LIKE 'xxxxx_track_exec_channel'\G
- # SHOW CREATE TABLE `xxxx`.`xxxxxxxx_exec_channel`\G
- # SHOW TABLE STATUS FROM `xxx` LIKE 'xxxxx_TRACK_ASSIGN'\G
- # SHOW CREATE TABLE `xxxx`.`xxxxx_EFFECTIVE_TRACK_ASSIGN`\G
- # SHOW TABLE STATUS FROM `xxx` LIKE 'xxxx_task_exec'\G
- # SHOW CREATE TABLE `xxxx`.`xxxxx_task_exec`\G
- UPDATExxxxxx_effective_track_exec_channel a SET EXEC_CHANNEL_CODE=(SELECT GROUP_CONCAT(DISTINCT(channel_id)) FROM xxxxxx_EFFECTIVE_TRACK_ASSIGN WHERE status in (1,2,4) AND id IN (SELECT assgin_id FROM xxxxxx_task_exec WHERE task_id=a.task_id))\G
explain分析SQL語句
上面幾點(diǎn)大概的介紹到了幾種獲取慢查詢SQL語句的方式，現(xiàn)在，我們就需要借助explain來分析查找SQL語句慢的原因。explain使用也很簡(jiǎn)單，直接在SELECT|UPDATE等語句前加上EXPLAIN即可
explain
id
表的執(zhí)行順序，復(fù)制的sql語句往往會(huì)分為很多步,序號(hào)越大越先執(zhí)行,id相同執(zhí)行順序從上往下
select_type
數(shù)據(jù)讀取操作的操作類型:
- SIMPLE(簡(jiǎn)單SELECT，不使用UNION或子查詢等)
- PRIMARY(子查詢中最外層查詢，查詢中若包含任何復(fù)雜的子部分，最外層的select被標(biāo)記為PRIMARY)
- UNION(UNION中的第二個(gè)或后面的SELECT語句)
- DEPENDENT UNION(UNION中的第二個(gè)或后面的SELECT語句，取決于外面的查詢)
- UNION RESULT(UNION的結(jié)果，union語句中第二個(gè)select開始后面所有select)
- SUBQUERY(子查詢中的第一個(gè)SELECT，結(jié)果不依賴于外部查詢)
- DEPENDENT SUBQUERY(子查詢中的第一個(gè)SELECT，依賴于外部查詢)
- DERIVED(派生表的SELECT, FROM子句的子查詢)
- UNCACHEABLE SUBQUERY(一個(gè)子查詢的結(jié)果不能被緩存，必須重新評(píng)估外鏈接的第一行)
table
數(shù)據(jù)來源于那張表，關(guān)聯(lián)等復(fù)雜查詢時(shí)會(huì)用臨時(shí)虛擬表
type
檢索數(shù)據(jù)的方式
- system:表只有一行記錄
- const:通過索引查找并且一次性找到
- eq_ref:唯一性索引掃描
- ref:非唯一行索引掃描
- range:按范圍查找
- index:遍歷索引樹
- all:全表掃描
possible_keys
顯示可能使用的索引
Key
實(shí)際使用的索引
key_len
索引的長(zhǎng)度，一般來說，長(zhǎng)度越短越好
ref
列與索引的比較，表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值
rows
估算查找的結(jié)果記錄條數(shù)
Extra
SQL查詢的詳細(xì)信息
- Using where:表示使用where條件過濾
- Using temporary:使用了臨時(shí)表暫存結(jié)果
- Using filesort:說明mysql對(duì)數(shù)據(jù)使用一個(gè)外部索引排序。未按照表內(nèi)的索引順序進(jìn)行讀取。
- Using index:表示select語句中使用了覆蓋索引，直接從索引中取值
- Using join buffer:使用了連接緩存
- Using index condition:表示查詢的列有非索引的列
