一、前言

上一篇咱们说了 腾讯一面：说一说 MySQL 中索引的底层原理，置信你对索引有个很清晰的意识了，这一篇咱们来说一说慢 SQL 的排查以及调优。为啥面试官要问这个问题，其实跟上一篇的索引底层原理有肯定关联关系的，个别慢 SQL 很大一部分起因对索引底层原理不够特地理解导致的，比方没建索引、索引生效、索引没满足最左前缀匹配准则导致慢 SQL，像腾讯这样数据量很大的公司，人家必定有专门的 DBA 去做优化的，面试官考查的次要是你排查问题的能力以及晓得索引的底层原理、以及晓得优化的方向，不至于让你进来把人家规规矩矩的数据库搞乱了。

咱们上面间接进入正题了，首先来说下怎么排查慢 SQL 的。

二、开启慢查问日志

MySQL 中与慢 SQL 无关的几个重要零碎变量如下：

执行如下语句看是否启用了慢查问日志，ON 为启用，OFF 为未启用，默认为 OFF。

SHOW VARIABLES LIKE '%slow_query_log%';

能够看到，我这里是曾经开启了的。如果你的没有开启，能够应用如下两种形式来开启慢查问。

2.1 批改配置文件

批改配置文件 vim /etc/my.cnf，在 [mysqld] 段落在退出如下配置：

[mysqld]
slow_query_log=1
slow_query_log_file=/var/lib/mysql/data/slow.log
long_query_time=3
log_output=FILE,TABLE

须要重启 MySQL 才能够失效，命令为 service mysqld restart

2.2 设置全局变量

如下关上慢查问日志，设置超时工夫为 3 秒，并且将日志记录到文件以及 mysql.show_log 表中。

SET GLOBAL slow_query_log = 1;
SET GLOBAL slow_query_log_file=’/var/lib/mysql/data/slow.log’;
SET GLOBAL long_query_time=3;
SET GLOBAL log_output=’FILE,TABLE’;

想要永恒失效得用下面那个配置文件里配置，否则数据库重启后，这些配置生效。

三、剖析慢查问日志

3.1 获取慢 SQL 信息

查看慢查问日志记录数：

SHOW GLOBAL STATUS LIKE '%Slow_queries%';

模仿语句：

select sleep(5);

查看日志：

cat /var/lib/mysql/data/slow.log

3.2 mysqldumpslow

MySQL 内置了 mysqldumpslow 这个工具来帮咱们剖析慢查问日志。

# 失去返回记录集最多的 10 个 SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/data/slow.log
#失去拜访次数最多的 10 个 SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/data/slow.log
#失去依照工夫排序的前 10 条外面含有左连贯的查问语句
mysqldumpslow -s t -t 10 -g "LEFT JOIN" /var/lib/mysql/data/slow.log
#联合 | more 应用，避免爆屏状况
mysqldumpslow -s r -t 10 /var/lib/mysql/data/slow.log | more

s：示意按何种形式排序
c：拜访次数
l：锁定工夫
r：返回记录
t：查问工夫
al：均匀锁定工夫
ar：均匀返回记录数
at：均匀查问工夫
t：返回后面多少条的数据
g：后边搭配一个正则匹配模式，大小写不敏感 

3.3 pt-query-digest

pt-query-digest 是一款很弱小的慢查问日志剖析工具，能够剖析 MySQL 数据库的 binary log、general log 日志，同时也能够应用 show processlist 或从 tcpdump 抓取的 MySQL 协定数据来进行剖析。

这里老周不带大家搭建以及应用了哈，想进一步理解的能够看这份文档：pt-query-digest

四、Explain 执行打算剖析慢 SQL

上一篇咱们十分具体的去介绍了，一文读懂 MySQL Explain 执行打算

五、Show Profile 剖析慢 SQL

Show Profile 也能够剖析慢 SQL，比 explain 获取的信息更具体，比方能剖析以后会话中语句执行的资源耗费状况，能剖析这条 SQL 整个生命周期的耗时。但没有下面 pt-query-digest 那款慢查问日志剖析工具弱小，但 pt-query-digest 是外置的须要独自下载，如果你想用内置的话，可能满足你的需要的话，抉择 Show Profile 就行。

5.1 如何开启

默认敞开。开启后，会在后盾保留最近 15 次的运行后果，而后通过 Show Profile 命令查看后果。

-- 开启
SET profiling = ON;
-- 查看
SHOW VARIABLES LIKE 'profiling%';

5.2 SHOW profiles 查看 SQL 的耗时

5.3 SQL 整个生命周期的耗时

通过 Query_ID 能够失去具体 SQL 从连贯——服务——引擎——存储四层构造残缺生命周期的耗时

SHOW profile CPU, BLOCK IO FOR QUERY 4;

可用参数 type:
ALL # 显示所有的开销信息
BLOCK IO # 显示块 IO 相干开销
CONTEXT SWITCHES # 上下文切换相干开销
CPU # 显示 CPU 相干开销信息
IPC # 显示发送和接管相干开销信息
MEMORY # 显示内存相干开销信息
PAGE FAULTS # 显示页面谬误相干开销信息
SOURCE # 显示和 Source_function，Source_file，Source_line 相干的开销信息
SWAPS # 显示替换次数相干开销的信息

5.4 危险状态

SHOW profile CPU, BLOCK IO FOR QUERY 2;

GROUP BY 可能创立了长期表

危险状态：

converting HEAP to MyISAM # 查问后果太大，内存不够用了，在往磁盘上搬。
Creating tmp table # 创立了长期表，回先把数据拷贝到长期表，用完后再删除长期表。
Copying to tmp table on disk # 把内存中长期表复制到磁盘
locked # 记录被锁了

看到这些危险状态能够进行相应的调优，而后咱们线上也会针对慢 SQL 进行监控，存在慢 SQL 的话会触发告警机制，告诉相应的人员疾速定位慢 SQL 并优化。