1、问题

如果遇到 MySQL 慢的话，你的第一印象是什么，MySQL 数据库如果性能不行，又该是如何处理的？
一些反馈如下：

• 第一反应是再试一次
• 第二个反应是优化一下 SQL
• 第三个反应是调大 buffer pool，然后开始换硬件了，换一下 SSD

分析一下这些现象背后隐藏的意义：

• 如果再试一次能够成功的话, 意味着你可能碰到了不可复现的外界因素的影响，导致 MySQL 会慢。
• 如果优化 SQL 能解决，就意味着 SQL 的执行复杂度远远大于它的需求复杂度。
• 如果调大 buffer pool 能解决，就意味着 MySQL 碰到了自身的某些限制。
• 如果换 SSD 能解决，那么意味着服务器资源受到了一定的限制。

2、MySQL 慢的诊断思路

MySQL 慢的诊断思路，一般会从三个方向来做：

• MySQL 内部的观测
• 外部资源的观测
• 外部需求的改造

2.1、MySQL 内部观测

常用的 MySQL 内部观测手段是这样的：

• 第一步是 Processlist，看一下哪个 SQL 压力不太正常；
• 第二步是 explain，解释一下它的执行计划；
• 第三步要做 Profilling，如果这个 SQL 能再执行一次的话, 就做一个 Profilling；
• 高级的 DBA 会直接动用 performance_schema ，MySQL 5.7 以后直接动用 sys_schema，sys_schema 是一个视图，里面有便捷的各类信息，帮助大家来诊断性能；
• 再高级一点，会动用 innodb_metrics 进行一个对引擎的诊断。

2.2、外部资源观测

这里引用国外一个大神写的文章，标题是《60 秒的快速巡检》。我们来看一下它在 60 秒之内对服务器到底做了一个什么样的巡检。一共十条命令，下面一条一条来看一下。

• uptime，uptime 告诉我们这个机器活了多久，以及它的平均负载是多少。
• dmesg -T | tail，告诉我们系统日志里边有没有什么报错。
• vmstat 1，告诉我们虚拟内存的状态，页的换进换出有没有问题，swap 有没有使用。
• mpstat -P ALL 1，告诉我们 CPU 压力在各个核上是不是均匀的。
• pidstat 1，告诉我们各个进程的对资源的占用大概是什么样子。
• iostat-xz 1，查看 IO 的问题。
• free-m 内存使用率；
• sar-n DVE 1，
• sar-n TCP, ETCP 1，8 和 9 两条按设备网卡设备的维度，看一下网络的消耗状态，以及总体看 TCP 的使用率和错误率是多少。
• top，看一下大概的进程和线程的问题。
  这个就是对于外部资源的诊断，这十条命令揭示了应该去诊断哪些外部资源。

2.3、外部需求改造

第三个诊断思路是外部的需求改造，在这里引用了 MySQL 官方文档中的一章，《Examples of Common Queries 》（ https://dev.mysql.com/doc/mysql-tutorial-excerpt/5.5/en/examples.html），文档中介绍了常规的 SQL 怎么写, 给出了一些例子。
下面看一下它其中提到的一个例子。
sql脚本：

CREATE TABLE shop (
    article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
    dealer  CHAR(20)                 DEFAULT ''     NOT NULL,
    price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL,
    PRIMARY KEY(article, dealer));
INSERT INTO shop VALUES
    (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
    (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);

执行结果;

shop表
需求：
从这个表里选取每个作者最贵的商品列在结果集中

sql语句：

SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article);

执行结果：

最贵商品结果集
这是它最原始的 SQL，非常符合业务的写法，但是它是个关联子查询。 关联子查询成本是很贵的，所以上面的文档会教你快速地把它转成一个非关联子查询。

改造sql语句：

SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN (
  SELECT article, MAX(price) AS price
  FROM shop
  GROUP BY article) AS s2
  ON s1.article = s2.article AND s1.price = s2.price;

大家可以看到中间的子查询和外边的查询之间是没有关联性的。
改造后的执行结果：

最贵商品结果集
最后，会教大家直接把子查询拿掉，然后转成这样一个 SQL，这个就叫业务改造，前后三个 SQL 的成本都不一样，把关联子查询拆掉的成本，拆掉以后 SQL 会跑得非常好，但这个 SQL 已经不能良好表义了，只有在诊断到 SQL 成本比较高的情况下才建议大家使用这种方式。

改造sql语句

SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
WHERE s2.article IS NULL;

执行结果和之前一样不在贴图
为什么它能够把一个关联子查询拆掉？
这背后的原理是关系代数，所有的 SQL 都可以被表达成等价的关系代数式，关系代数式之间有等价关系，这个等价关系通过变换可以把关联子查询拆掉。

总结一下，对于 MySQL 慢的诊断思路如下：
第一，MySQL 本身提供了很多命令来观察 MySQL 自身的各类状态，从上往下检一般能检到 SQL 的问题或者服务器的问题。
第二，从服务器的角度，我们从巡检的脚本角度入手，服务器的资源就这几种，观测手法也就那么几种，把服务器的资源全部都观察一圈就可以了。
第三，如果实在搞不定，需求方一定要按照数据库容易接受的方式去写 SQL，这个成本会下降的非常快，这个是常规的 MySQL 慢的诊断思路。