面试题 MySQL的慢查询、如何监控、如何排查？

1. 慢查询和慢查询日志

慢查询，顾名思义就是很慢的查询。MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10s。默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志或多或少会带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

要使用慢查询日志，首先要检查慢查询日志是否开启，如果没有，将其开启，并设置慢查询阈值即慢查询文件存储位置等属性。通过下面的命令查看相关属性

show variables like '%query%';

主要看这三个属性：

• long_query_time ：10.000000：查询超过10秒被定义为慢语句
• slow_query_log ：OFF：是否打开慢查询日志
• slow_query_log_file ： /usr/local/mysql/data/slow.log：慢查询日志文件所在位置

使用以下命令设置这些属性值：

set global slow_query_log = ON; # 打开慢查询日志
set global long_query_time = 1; # 超过1秒的语句被定义为慢语句，注意设置了之后需要重新连接才有效

2. 查看慢查询的执行情况（监控慢查询）

2.1 查看曾经执行完成的慢查

使用日志分析工具mysqldumpslow来分析慢查询日志。

2.2 查看正在进行的慢查SQL

使用show processlist命令显示用户正在运行的线程。需要注意的是，除了 root 用户能看到所有正在运行的线程外，其他用户都只能看到自己正在运行的线程。show processlist 显示的信息都是来自MySQL系统库 information_schema 中的 processlist 表。这个表中有这些信息：

• Id：就是这个线程的唯一标识，当我们发现这个线程有问题的时候，可以通过 kill 命令，加上这个Id值将这个线程杀掉。是这个表的主键。
• User：就是指启动这个线程的用户。
• Host：记录了发送请求的客户端的 IP 和 端口号。通过这些信息在排查问题的时候，我们可以定位到是哪个客户端的哪个进程发送的请求。
• DB：当前执行的命令是在哪一个数据库上。如果没有指定数据库，则该值为 NULL 。
• Command：是指此刻该线程正在执行的命令。这个很复杂，下面单独解释
• Time：表示该线程处于当前状态的时间，单位是秒。
• State：线程的状态，和 Command 对应，下面单独解释。
• Info：一般记录的是线程执行的语句。默认只显示前100个字符，也就是你看到的语句可能是截断了的，要看全部信息，需要使用 show full processlist。

我们可以在processlist 中查询运行时间超所某值的线程，如：

select * from information_schem.processlist where Command != 'Sleep' and Time > 300 order by Time desc;

2.3 通过在SQL语句前加上explain命令，来显示这句SQL语句的执行计划。

3. 如何优化慢查询

当通过排查定位到慢查询sql后，就需要通过explain命令分析sql的执行计划并进行相应的优化

• 如果是因为没走索引，就要建合适的索引
• 因为mysql查询优化器会误使用非预期索引导致语句查询缓慢，这时候需要修改sql逻辑引导优化器使用正确的索引，或者强制（force index）使用我们预期的索引
• 如果是因为数据表太大，即使走了索引也依然很慢，这时要考虑分表