51Testing软件测试论坛

 找回密码
 (注-册)加入51Testing

QQ登录

只需一步,快速开始

微信登录,快人一步

手机号码,快捷登录

查看: 4044|回复: 0
打印 上一主题 下一主题

[转贴] MySQL慢查询语句分析总结

[复制链接]
  • TA的每日心情
    擦汗
    昨天 09:02
  • 签到天数: 1046 天

    连续签到: 4 天

    [LV.10]测试总司令

    跳转到指定楼层
    1#
    发表于 2021-3-19 09:59:40 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式
    我们经常会接触到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:表示查询的列有非索引的列
    分享到:  QQ好友和群QQ好友和群 QQ空间QQ空间 腾讯微博腾讯微博 腾讯朋友腾讯朋友
    收藏收藏
    回复

    使用道具 举报

    本版积分规则

    关闭

    站长推荐上一条 /1 下一条

    小黑屋|手机版|Archiver|51Testing软件测试网 ( 沪ICP备05003035号 关于我们

    GMT+8, 2024-11-15 00:44 , Processed in 0.066670 second(s), 23 queries .

    Powered by Discuz! X3.2

    © 2001-2024 Comsenz Inc.

    快速回复 返回顶部 返回列表