MySQL优化--00----慢SQL定位、慢查询日志_mysql慢日志开启

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

慢查询SQL 解决步骤

1. 根据慢查询日志定位慢查询sql

2. 使用explain等工具分析sql执行计划

MySQL优化–01—看懂explain

3. 修改sql或者尽量让sql走索引

慢查询日志

步骤

1. 开启慢查询日志
2. 设置慢查询阈值
3. 确定慢查询日志的文件名和路径

1.开启慢查询日志

• 首先开启慢查询日志，由参数slow_query_log决定是否开启
• 默认环境下，慢查询日志是关闭的，所以这里开启。

set global slow_query_log=on;

2.设置慢查询阈值

• 只要你的SQL实际执行时间超过了这个阈值，就会被记录到慢查询日志里面。这个阈值默认是10s
• 线上业务一般建议把long_query_time设置为1s

set global long_query_time=1;

3.确定慢查询日志的文件名和路径

show global variables like ‘slow_query_log_file’

结果会发现慢日志默认路径就是MySQL的数据目录

查看 参数设置

show variables like ‘%quer%’;

• 但是重启mysql客户端设置和统计慢查询日志条数就会清零，即所有配置修改会还原
• 命令修改配置之后，在命令行net stop mysql关闭MySQL服务，再net start mysql开启MySQL服务，接着执行show global variables like ‘%quer%’;会发现配置还原了。
• 在配置文件修改才能永久改变，否则重启数据库就还原了

慢查询案例

表结构

CREATE TABLE `person_info_large` (  
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  
    `account` VARCHAR (10),   
    `name` VARCHAR (20),  
    `area` VARCHAR (20),  
    `title` VARCHAR (20), 
    `motto` VARCHAR (50),
    PRIMARY KEY (`id`),  
    UNIQUE(`account`),
    KEY `index_area_title`(`area`,`title`) 
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8  

1
2
3
4
5
6
7
8
9
10
11
12

run

• 这里的数据是200W条。请注意表结构，记住哪几个字段有索引即可，后续围绕这个表进行分析。
  
  这个3.36s并不是实际执行时间，实际执行时间得去慢查询日志去看Query_time参数

慢查询日志文件

可以看到Query_time: 6.337729s，超过了1s，所以会被记录

• 注意：有的慢查询正在执行，结果已经导致数据库负载过高，而由于慢查询还没执行完，因此慢查询日志看不到任何语句，此时可以使用show processlist命令查看正在执行的慢查询。show processlist显示哪些线程正在运行，如果有PROCESS权限，则可以看到所有线程。否则，只能看到当前会话线程。

参数说明

• Time：慢查询发生的时间
• Query_time：查询时间
• Lock_time：等待锁表的时间
• Rows_sent：语句返回的行数
• Rows_exanined：语句执行期间从存储引擎读取的行数

关注点

关注: Rows_exanined 扫描行数 / Rows_sent 返回行数

其他分析工具:

上面这种方式是用系统自带的慢查询日志查看的，如果觉得系统自带的慢查询日志不方便查看，可以使用pt-query-digest或者mysqldumpslow等工具对慢查询日志进行分析。

1、使用mysqldumpslow进行分析【第一种方式】

mysqldumpslow -t 10 /data/mysql/mysql-slow.log #显示出慢查询日志中最慢的10条sql

注：mysqldumpslow工具还有其他参数，以提供其他功能，这里，只以最基本的-t做了介绍。

2、使用pt-query-digest工具进行分析

mysqldumpslow是mysql安装后就自带的工具，用于分析慢查询日志，但是pt-query-digest却不是mysql自带的，如果想使用pt-query-digest进行慢查询日志的分析，则需要自己安装pt-query-digest。pt-query-digest工具相较于mysqldumpslow功能多一点。