MYSQL 索引（三）--- SQL日志分析

慢查询日志

Mysql 的慢查询日志是 Myql 提供的一种日志记录，用来记录在 Myql 中响应时间查过阈值的语句，具体指运行时间超过 long_query_time 值的 SQL，则会被记录在日志中。long_query_time 默认为 10，单位为秒。

默认情况下，Mysql 数据库没有开启慢查询日志，需要我们手动来设置这个参数。如果不是调优需要，不建议开启慢查询日志，多少会影响性能。Mysql 慢查询日志支持写入到文件。

查看开启慢查询

SHOW VARIABLES LIKE '%slow_query_log%'; # 查看慢查询状态

默认情况：

开启

set global slow_query_log=1;

如果要永久生效，必须修改配置文件 my.cnf：

在 [mysqld] 下添加：

slow_query_log = 1

slow_query_log_file=/var/lib/mysql/localhost-slow.log

long_query_time = 3

log_output = FILE
mysql中 判断大于long_query_time，而非大于等于

SHOW VARIABLES LIKE 'long_query_time%';

set global long_query_time=3; # 修改后需要重新打开会话或打开新会话才会生效

mysql日志分析工具

mysqldumpslow --help

参数	含义
s	按何种方式排序
c	访问次数
l	锁定时间
r	返回记录
t	查询时间
al	平均锁定事件
ar	平均返回记录时间
at	平均查询时间
t	返回前面多少条的数据
g	后面搭配正则匹配模式，大小写不敏感

# 得到返回记录集最多的十个 SQL,/var/lib/mysql/localhost-slow.log 是慢查询日志路径
mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log

# 得到访问次数最多的十个 SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/localhost-slow.log

# 得到按照时间排序的前十条里面包含左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/localhost-slow.log

# 建议在使用这些命令时结合 | 和 more 使用 ，否则有可能爆屏
mysqldumpslow -s r -t 10 "left join" /var/lib/mysql/localhost-slow.log | more

show profile

show profile 是 mysql 提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于 SQL 的调优测量。

默认情况下，参数处于关闭状态，并保存最近 15 次的运行结果。

分析步骤

1. 是否支持

2. 开启功能，默认关闭

   show variables like 'profiling'; # 查看
   
   set profiling=on; # 开启
   

   

3. 运行 SQL

4. 查看结果：

   show profiles;
   

   

5. 诊断 SQL

   show profile cpu,block io for query 4;
   

   

   | 参数 | 含义 |
   | - | - |
   | ALL | 所有的开销信息 |
   | BLOCK IO | 块 IO 相关开销 |
   | CONTEXT SWITCHES | 上下文切换相关开销 |
   | CPU | CPU相关开销 |
   | IPC | 发送和接收相关开销 |
   | MEMORY | 内存相关开销 |
   | PAGE FAULTS | 页面错误相关开销 |
   | SOURCE | 显示和Source_function, Source_file, Source_line 相关开销 |
   | SWAPS | 交换次数相关开销 |

6. 注意

   • converting HEAP to MyISAM 查询结果太大，内存不够用，往磁盘上搬了
   • Creating tmp table 创建临时表：拷贝数据到临时表，用完再删除
   • Copying to tmp on disk 把内存中临时表复制到磁盘，危险！！！
   • locked

全局查询日志

永远不要在生产环境开启此功能。

文件配置：

# 开启
general_log =1 1
# 记录日志文件路径
general_log_file=/path/logfile
#输出格式
log_output=FILE

参数配置（一次有效）：

set global general_log = 1;
set global log_output='TABLE';