MySQL慢查询日志

小度do 2021-10-10

453

在WEB应用开发的过程中，由于初期的数据量比较少，有的开发人人员在书写SQL语句时往往更加重视功能上的实现，而忽略了性能的问题。但是当系统上线后，随着生产数据量的急剧增长，很多SQL语句开始暴露出性能问题，对生产的影响也越来越大，此时这些有问题的SQL语句就会成为系统的性能瓶颈。如何对这些SQL语句进行优化提升性能就成为了我们必须要解决的问题。

如果想要针对SQL语句层面展开优化，首先我们需要做的是找出哪些查询语句慢，如果通过编写代码去一个一个的测试会很耗时耗力，幸运的是MySQL为我们提供了慢查询日志，下面就来了解一些MySQL慢查询日志的基本内容。

1. MySQL慢查询日志简介

慢查询日志是MySQL提供的一种日志记录，主要用来记录查询时长超过指定时间的查询语句。开发人员可以通过慢查询日志找出执行时间较长、执行效率低的SQL语句，从而有针对性的进行优化。

2.慢查询日志配置操作

2.1查看是否开启慢查询日志，1/on表示开启，0/off表示关闭。

  show VARIABLES like 'slow_query_log';

2.2未使用索引的查询也被记录到慢查询日志中，on表示开启，off表示关闭(默认值)。

show VARIABLES like 'log_queries_not_using_indexes';

2.3 慢查询阈值（秒级），当查询时间大于设定的阈值时，记录日志。

show VARIABLES like 'long_query_time';

2.4慢查询日志存储路径

show variables like 'slow_query_log_file';

2.5 自定义慢查询日志配置

set global slow_query_log = on;


set global log_queries_not_using_indexes = on;


set global long_query_time = 0;

3.安装慢查询日志分析工具

1.下载安装工具
wget percona.com/get/pt-query-digest


2.授予用户执行权限
chmod u+x pt-query-digest


3.移动位置,目的是方便pt-query-digest的使用
mv pt-query-digest usr/bin/


4.安装与Perl相关的模块
yum install perl-DBI perl-Digest-MD5 perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-ExtUtils-Embed perl-Time-HiRes -y


或依次执行下面命令
yum install perl-DBI -y
yum install perl-Digest-MD5 -y
yum install perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker -y
yum install perl-ExtUtils-Embed -y
yum install perl-Time-HiRes -y

分析慢查询日志中SQL的统计结果:

# 70ms user time, 20ms system time, 20.12M rss, 165.41M vsz
# Current date: Sun Dec  1 21:20:22 2019
# Hostname: javaInit01
# Files: var/run/mysqld/mysqld-slow.log
//总共有多少条查询
# Overall: 2 total, 1 unique, 0.00 QPS, 0.02x concurrency ________________
//查询日志记录的时间范围
# Time range: 2018-11-01 20:58:47 to 21:35:46
//属性                 总计     最小     最大     平均    95%     标准     中等
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
//执行时间
# Exec time            39s     14s     24s     19s     24s      7s     19s
//锁占用时间
# Lock time          233us    69us   164us   116us   164us    67us   116us
//发送到客户端的行数
# Rows sent              6       1       5       3       5    2.83       3
//select语句扫描行数
# Rows examine      38.15M  19.07M  19.07M  19.07M  19.07M    2.83  19.07M
//查询的字符数
# Query size            84      42      42      42      42       0      42


/**注释：
Rank：所有语句的排名，默认按查询时间降序排列，通过--order-by指定
Query ID：语句的ID，（去掉空格和查询条件中的文本值，计算hash值）
Response：总的响应时间
time：该查询在本次分析中总的时间占比
calls：执行次数，即本次分析总共有多少条这种类型的查询语句
R/Call：平均每次执行的响应时间
V/M：方差均值比（Variance-to-mean），也就是常说的离差指数。
Item：查询对象
*/
# Profile
# Rank Query ID                           Response time  Calls R/Call  V/M
# ==== ================================== ============== ===== ======= ===
#    1 0x3992D6F8E9C2C994AC70C8DFD95C72CE 38.7578 100.0%     2 19.3789  2.68 SELECT card


/**注释：
ID：查询的ID号，和上面的Query ID对应
*/
# Query 1: 0.00 QPS, 0.02x concurrency, ID 0x3992D6F8E9C2C994AC70C8DFD95C72CE at byte 404
# This item is included in the report because it matches --limit.
# Scores: V/M = 2.68
# Time range: 2019-12-01 20:58:47 to 21:35:46
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count        100       2
# Exec time    100     39s     14s     24s     19s     24s      7s     19s
# Lock time    100   233us    69us   164us   116us   164us    67us   116us
# Rows sent    100       6       1       5       3       5    2.83       3
# Rows examine 100  38.15M  19.07M  19.07M  19.07M  19.07M    2.83  19.07M
# Query size   100      84      42      42      42      42       0      42
# String:
//数据库名
# Databases    test
//服务器地址IP
# Hosts        192.168.246.1
//各个用户执行的次数（占比)
# Users        root
//查询时间分布, 长短体现区间占比，本例中1s-10s之间查询数量是10s以上的两倍。
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+  ################################################################
//查询中涉及到的表
# Tables
#    SHOW TABLE STATUS FROM `test` LIKE 'card'\G
#    SHOW CREATE TABLE `test`.`card`\G
//SQL语句
# EXPLAIN /*!50100 PARTITIONS*/
select * from card order by rand() limit 5\G

找到慢查询SQL之后我们就可以通过explain分析语句，然后进行优化。

mysql

文章转载自小度do，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL慢查询日志

评论