我的PICK

Appearance

Mysql ​

Mysql 是一个关系型数据库管理系统

问题 ​

慢 SQL 查询日志如何调优？ ​

慢查询日志用来记录响应时间超过指定阈值的 SQL 语句，阈值使用 long_query_time 设置，默认 10 秒，超过该值则会记录到慢查询日志中，另默认情况下，数据库没有开启慢查询日志开关 slow_query_log

开启慢查询日志 ​

  • 方式1：修改 my.cnf 配置文件，增加如下参数
my.cnf

# 开启慢查询日志开关(1 或 ON,0 或 OFF)
slow_query_log=ON
# 慢查询日志目录和文件名(默认存储到 MySQL 数据库的数据文件夹下，文件名为 hostname-slow.log)
slow_query_log_file=/var/lib/mysql/mysql-slow.log
# 设置执行时间阈值为3秒，超出此值的 SQL 会被记录到慢查询日志中
long_query_time=3
# 日志输出格式，一种是输出到文件 FILE 中，一种是写入数据表格 TABLE 中，会保存到数据库的 slow_log 表中
log_output=FILE 
# 最少记录数，超过该值且查询时间超过 long_query_time 则会记录到慢日志中（默认0）
min_examined_row_limit
# 慢查询中记录 bytes_received 和 bytes_sent，默认未开启
log_slow_extra=OFF

# 开启慢查询日志开关(1 或 ON,0 或 OFF)
slow_query_log=ON
# 慢查询日志目录和文件名(默认存储到 MySQL 数据库的数据文件夹下，文件名为 hostname-slow.log)
slow_query_log_file=/var/lib/mysql/mysql-slow.log
# 设置执行时间阈值为3秒，超出此值的 SQL 会被记录到慢查询日志中
long_query_time=3
# 日志输出格式，一种是输出到文件 FILE 中，一种是写入数据表格 TABLE 中，会保存到数据库的 slow_log 表中
log_output=FILE 
# 最少记录数，超过该值且查询时间超过 long_query_time 则会记录到慢日志中（默认0）
min_examined_row_limit
# 慢查询中记录 bytes_received 和 bytes_sent，默认未开启
log_slow_extra=OFF
  • 方式2：使用命令进行设置（重开连接生效，重启后失效）
sql
# 查看慢查询日志开关、执行时间阈值、最少记录数、慢查询额外字段
show variables like '%slow_query_log%';
show variables like '%long_query_time%';
show variables like '%min_examined_row_limit %';
show variables like '%log_slow_extra %';

# 打开慢查询日志开关并设置阈值为1秒
set global slow_query_log='ON';
set global long_query_time = 1;
# 查看慢查询日志开关、执行时间阈值、最少记录数、慢查询额外字段
show variables like '%slow_query_log%';
show variables like '%long_query_time%';
show variables like '%min_examined_row_limit %';
show variables like '%log_slow_extra %';

# 打开慢查询日志开关并设置阈值为1秒
set global slow_query_log='ON';
set global long_query_time = 1;

记录未使用索引的 SQL ​

  • log-queries-not-using-indexes 记录未使用索引的 SQL,默认未开启
  • log_throttle_queries_not_using_indexes 限制每分钟写入慢日志中的不走索引的 SQL 语句个数，默认0，不做条数限制

查询当前系统的慢 SQL 条数 ​

sql
show global status like '%Slow_queries%';
show global status like '%Slow_queries%';

慢 SQL 分析工具 ​

开启慢查询后可通过语句 select sleep(5); 测试是否正常记录慢日志

  • Mysql 自带分析调优工具 mysqldumpslow
sql
mysqldumpslow -s t /var/lib/mysql/mysql-slow.log
mysqldumpslow -s t /var/lib/mysql/mysql-slow.log
  • Mysql 自带高级分析调优工具 show profile，默认关闭
sql
# 查看 show profile
show variables like 'profiling%';

# 查看最近15次的运行结果
show profiles;

# 诊断 SQL
SHOW PROFILE cpu,block io FOR QUERY query id;
# 查看 show profile
show variables like 'profiling%';

# 查看最近15次的运行结果
show profiles;

# 诊断 SQL
SHOW PROFILE cpu,block io FOR QUERY query id;
  • Percona Toolkit（DBAs 高级开源命令行工具集） 中的 pt-query-digest，分析慢查询的一个工具
sql
pt-query-digest /var/log/mysql/mysql-slow.log > report.txt
pt-query-digest /var/log/mysql/mysql-slow.log > report.txt

删除慢查询日志文件 ​

  • 手动删除文件
  • 使用命令 mysqladmin -u root -p flush-logs

死锁如何处理？ ​

死锁指的是在两个或两个以上不同的进程或线程中,因争夺资源而造成的一种互相等待的现象

死锁查看 ​

sql
# 查看正在进行中的事务
SELECT * FROM information_schema.INNODB_TRX
# 查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
# 查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
# 查询是否锁表
SHOW OPEN TABLES where In_use > 0;
# 查看最近死锁的日志
show engine innodb status
# 查看正在进行中的事务
SELECT * FROM information_schema.INNODB_TRX
# 查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
# 查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
# 查询是否锁表
SHOW OPEN TABLES where In_use > 0;
# 查看最近死锁的日志
show engine innodb status

解除死锁 ​

sql
# 查看所有进程
show processlist
# 杀死进程
kill id
# 查看所有进程
show processlist
# 杀死进程
kill id

为什么开启慢查询后没有记录日志？ ​

前提确保已开启慢查询，再判断具体原因

配置目录及文件不存在 ​

markdown
创建目录及文件
创建目录及文件

配置目录及文件权限不足 ​

shell
chmod 777 mysql-slow.log
chmod 777 mysql-slow.log