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/) Powered by Discuz! X3.2