MySQL 慢的诊断思路

2018-09-06 本文已影响0人 MeteorCat

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 慢的诊断思路。