安基网 首页 系统 数据库 查看内容

mysql慢查询语句分析总结

2020-9-14 09:28| 投稿: xiaotiger |来自: 互联网


免责声明:本站系公益性非盈利IT技术普及网,本文由投稿者转载自互联网的公开文章,文末均已注明出处,其内容和图片版权归原网站或作者所有,文中所述不代表本站观点,若有无意侵权或转载不当之处请从网站右下角联系我们处理,谢谢合作!

摘要: 我们经常会接触到MySQL,也经常会遇到一些MySQL的性能问题。我们可以借助慢查询日志和explain命令初步分析出SQL语句存在的性能问题通过SHOW FULL PROCESSLIST查看问题SHOW FULL PROCESSLIST相当于select * from information_schema.processlist可以列出正在运行的连接线程,processlist说明:id 连接id ...

我们经常会接触到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:表示查询的列有非索引的列

[参考]
MySQL Explain详解



小编推荐:欲学习电脑技术、系统维护、网络管理、编程开发和安全攻防等高端IT技术,请 点击这里 注册账号,公开课频道价值万元IT培训教程免费学,让您少走弯路、事半功倍,好工作升职加薪!

本文出自:https://www.toutiao.com/a6871593947861516814/

免责声明:本站系公益性非盈利IT技术普及网,本文由投稿者转载自互联网的公开文章,文末均已注明出处,其内容和图片版权归原网站或作者所有,文中所述不代表本站观点,若有无意侵权或转载不当之处请从网站右下角联系我们处理,谢谢合作!


鲜花

握手

雷人

路过

鸡蛋

相关阅读

最新评论

 最新
返回顶部