MySql 慢SQL配置，查询，处理

一.慢SQL配置相关 

1.查看慢SQL是否开启

执行下面命令查看是否开启慢SQL

show variables like '%slow_query_log';
复制代码

• OFF: 未开启
• ON:

2.打开慢SQL配置

执行下面的命令开启慢查询日志

 set global slow_query_log='ON';
复制代码

3.修改慢查询阈值

前面介绍了SQL执行到达了制定的时间阈值后记录到慢查询日志中，那么如何设置呢？

set global long_query_time = N;set long_query_time = N
复制代码

• 设置global的方式对当前session的long_query_time失效。对新连接的客户端有效。所以可以一并执行下述语句
• N表示设置的阈值，单位为秒

• 这里的show global variables like '%long_query_time%';可以查看阈值大小

1. 如何设置永久生效

前面是通过命令行的方式设置，如果MySQL重启，那么配置就会重置。我们可以通过修改MySQL的配置my.cfg或者my.ini永久生效。

[mysqld]
slow_query_log=ON  # 开启慢查询日志开关
slow_query_log_file=/var/lib/mysql/alvin-slow.log  # 慢查询日志的目录和文件名信息
long_query_time=3  # 设置慢查询的阈值为3秒，超出此设定值的SQL即被记录到慢查询日志
log_output=FILE
复制代码

4.慢查询日志在哪里呢？

前面讲解了如何开启MySQL的慢查询日志，那么它把日志记录在哪里了呢?

   1.查看慢查询日志位置

通过show variables like '%slow_query_log_file%';命令可以查看慢SQL文件位置，如下图所示：

   2.修改慢查询日志位置

也很简单，执行下面的命令即可：

set global slow_query_log_file = '/usr/local/mysql/data/alvin-slow-slow.log';
复制代码

5.怎么查看慢SQL内容？

现在我们已经知道慢查询日志在哪里了，那么如何查看里面的内容呢？我们这里用一个例子演示下吧。

1. 执行一个查询的SQL

• 执行花了1秒多，超过了前面设置的阈值1s

1. 查看慢查询数目

执行下面命令查询当前系统中有多少条慢查询记录

SHOW GLOBAL STATUS LIKE '%Slow_queries%';
复制代码

1. 查看日志内容

通过cat命令查看文件内容，可以看到对应的慢SQL。

6.慢查询日志分析工具mysqldumpslow

果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具 mysqldumpslow 。

mysqldumpslow 命令的具体参数如下：

• -a: 不将数字抽象成N，字符串抽象成S
• -s: 是表示按照何种方式排序：
  • c: 访问次数
  • l: 锁定时间
  • r: 返回记录
  • t: 查询时间
  • al:平均锁定时间
  • ar:平均返回记录数
  • at:平均查询时间 （默认方式）
  • ac:平均查询次数
• -t: 即为返回前面多少条的数据；
• -g: 后边搭配一个正则匹配模式，大小写不敏感的；

可mysqldumpslow位置mysql的bin目录下，以通过执行 mysqldumpslow --help命令查看使用。

举例： 我们想要按照查询时间排序，查看前五条 SQL 语句，这样写即可：

mysqldumpslow -s t -t 5 /usr/local/mysql/data/alvin-slow-slow.log
复制代码

7.线上死锁分析&处理 

1.查看被锁住的表 以及 对应的线程id

SELECT l.*, t.trx_mysql_thread_id FROM INFORMATION_SCHEMA.INNODB_LOCKS l JOIN INFORMATION_SCHEMA.INNODB_TRX t ON l.lock_trx_id = t.trx_id;

2.查看正在执行的线程 超过1秒钟 且 不为休眠的线程

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME > 1 and Command <> 'Sleep'

 3.查询是否锁表

show OPEN TABLES where In_use > 0;

4.查看被锁住的表和资源

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

5.查看等待被锁住的表和资源 

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

6.杀掉锁表进程

kill thread_id;