mysql 慢查询 超时_mysql查询优化 - 慢查询

mysql查询速度优化思路

开启慢查询日志

查看导致阻塞的sql语句

重现场景, 单独测试

mysql 慢查询分析

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

slow_query_log = ON

slow_query_log_file = /usr/bin/mysql_data/slow.log

long_query_time = 1

检查是否设置成功

show variables like 'slow_query%';

show variables like 'long_query_time';

查看最近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

分析慢查询日志格式 :

# 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) ;被慢查询检测到, 但是没有锁时间, 符合实际情况