51Testing软件测试论坛

标题: MySQL慢查询语句分析总结 [打印本页]

作者: lsekfe 时间: 2021-3-19 09:59
标题: MySQL慢查询语句分析总结
我们经常会接触到MySQL，也经常会遇到一些MySQL的性能问题。我们可以借助慢查询日志和explain命令初步分析出SQL语句存在的性能问题。
　　通过SHOW FULL PROCESSLIST查看问题
　　SHOW FULL PROCESSLIST相当于select * from information_schema.processlist可以列出正在运行的连接线程。

processlist
　　说明：
　　·id 连接id，可以使用kill+连接id的方式关闭连接(kill 9339)
　　·user显示当前用户
　　·host显示连接的客户端IP和端口
　　·db显示进程连接的数据库
　　·command显示当前连接的当前执行的状态，sleep、query、connect
　　·time显示当前状态持续的时间(秒)
　　·state显示当前连接的sql语句的执行状态，copying to tmp table、sorting result、sending data等
　　·info显示sql语句,如果发现比较耗时的语句可以复制出来使用explain分析。
　　慢查询日志
　　慢查询日志是MySQL用于记录响应时间超过设置阈值(long_query_time)的SQL语句，默认情况下未开启慢查询日志，需要手动配置。
　　下面我们要记住几个常用的属性:
　　·slow_query_log:是否开启慢查询(ON为开启，OFF则为关闭)。
　　·long_query_time:慢查询阀值，表示SQL语句执行时间超过这个值就会记录,默认为10s。
　　·slow_query_log_file:慢查询日志存储的文件路径。
　　·log_queries_not_using_indexes: 记录没有使用索引查询语句(ON为开启,OFF为关闭)。
　　·log_output:日志存储方式(FILE表示将日志写入文件,TABLE表示写入数据库中，默认值为FILE,如果存入数据库中，我们可以通过select * from mysql.slow_log的方式去查询，一般性能要求相对较高的建议存文件)。
　　我们可以通过show variables like ‘%关键字%’的方式查询我们设置的属性值。

slow
　　我们有两种方式设置我们的属性,一种是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包含了很多实用强大的mysql工具包，pt-qurey-digest只是其中一个用于分析慢查询日志是工具。需要去官网下载,使用方法也很简单:

　　./pt-query-digest slow2.log >> slow2.txt

　　即可得出一个分析结果:

　　# 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语句
　　上面几点大概的介绍到了几种获取慢查询SQL语句的方式，现在，我们就需要借助explain来分析查找SQL语句慢的原因。explain使用也很简单，直接在SELECT|UPDATE等语句前加上EXPLAIN即可。

explain
　　id
　　表的执行顺序，复制的sql语句往往会分为很多步,序号越大越先执行,id相同执行顺序从上往下。
　　select_type
　　数据读取操作的操作类型:
　　·SIMPLE(简单SELECT，不使用UNION或子查询等)
　　·PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)
　　·UNION(UNION中的第二个或后面的SELECT语句)
　　·DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
　　·UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)
　　·SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)
　　·DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)
　　·DERIVED(派生表的SELECT, FROM子句的子查询)
　　·UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
　　table
　　数据来源于那张表，关联等复杂查询时会用临时虚拟表。
　　type
　　检索数据的方式：
　　·system:表只有一行记录
　　·const:通过索引查找并且一次性找到
　　·eq_ref:唯一性索引扫描
　　·ref:非唯一行索引扫描
　　·range:按范围查找
　　·index:遍历索引树
　　·all:全表扫描
　　possible_keys
　　·显示可能使用的索引
　　Key
　　·实际使用的索引
　　key_len
　　·索引的长度，一般来说，长度越短越好
　　ref
　　·列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
　　rows
　　·估算查找的结果记录条数
　　Extra
　　SQL查询的详细信息：
　　·Using where:表示使用where条件过滤
　　·Using temporary:使用了临时表暂存结果
　　·Using filesort:说明mysql对数据使用一个外部索引排序。未按照表内的索引顺序进行读取。
　　·Using index:表示select语句中使用了覆盖索引，直接从索引中取值
　　·Using join buffer:使用了连接缓存
　　·Using index condition:表示查询的列有非索引的列

欢迎光临 51Testing软件测试论坛 (http://bbs.51testing.com/)