mysql查询优化 - 慢查询

mysql查询速度优化思路

1. 开启慢查询日志
2. 查看导致阻塞的sql语句
3. 重现场景, 单独测试

mysql 慢查询分析

1. 编辑/etc/my.cnf数据库配置文件 , [mysqld]标签下增加以下内容

slow_query_log = ON
slow_query_log_file = /usr/bin/mysql_data/slow.log
long_query_time = 1

2. 检查是否设置成功

show variables like 'slow_query%';
show variables like 'long_query_time';

3. 查看最近15条记录

tail -n15 /usr/bin/mysql_data/slow.log
# 输出以下内容 :


# User@Host: laravel[laravel] @ localhost [127.0.0.1]  Id: 17733
# Query_time: 4.402049  Lock_time: 0.997900 Rows_sent: 1  Rows_examined: 1
SET timestamp=1591262329;
select * from `posts` where `id` = '1' limit 1;
# Time: 2020-06-04T09:19:18.946805Z
# User@Host: laravel[laravel] @ localhost [127.0.0.1]  Id: 17735
# Query_time: 4.091529  Lock_time: 1.595749 Rows_sent: 1  Rows_examined: 1
SET timestamp=1591262354;
select * from `posts` where `id` = '1' limit 1;
/usr/sbin/mysqld, Version: 8.0.20 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
/usr/sbin/mysqld, Version: 8.0.20 (MySQL Community Server - GPL). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument

4. 分析慢查询日志格式 :

# Time: 2020-06-04T09:19:18.946805Z             # 发生问题的时间
# User@Host: laravel[laravel] @ localhost [127.0.0.1]  Id: 17735        # 由哪个用户登录, 主机号
# Query_time: 4.091529  Lock_time: 1.595749 Rows_sent: 1  Rows_examined: 1   # 总数查询时间 , 锁阻塞时间 , 执行行数
SET timestamp=1591262354;
select * from `posts` where `id` = '1' limit 1;     # 发生问题点sql语句
/usr/sbin/mysqld, Version: 8.0.20 (MySQL Community Server - GPL). started with: # 数据库版本号端口等
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock

[ 测试 ] 使用慢查询日志

思路: 在mysql配置中设置超时1s记录, 执行时长超过1s的语句看是否被记录, 如何被记录

select sleep(2) ;

查看最近8条记录

tail -n8 /usr/bin/mysql_data/slow.log

# Time: 2020-06-15T02:28:54.547045Z
# User@Host: root[root] @  [113.65.33.146]  Id: 26724
# Query_time: 2.000273  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
use myBlog;
SET timestamp=1592188132;
SELECT sleep(2);

执行select sleep(2) ;被慢查询检测到, 但是没有锁时间, 符合实际情况