MySQL慢查询风险指数模型设计（1）

一、背景

所谓 MySQL 慢查询，是指在 MySQL 中执行时间超过指定阈值的语句将被记录到慢查询文件中，它是我们 DBA 经常讨论的话题。

但在慢查询方面，做得更多的工作，基本都是集中做一个慢查询平台，可以很好的把慢查询收集起来，然后管理起来，方便查看各种信息，方便和开发沟通，方便看慢查询的发展趋势等等。但这些工作，对于解决慢查询来讲，作用比较小，因为久而久之，当我们成功地把慢查询平台变为慢查询海洋时，不管是开发，还是 DBA ，都不知道我应该要去解决哪个慢查询了，再加上，解决一个慢查询，本身其周期非常长，比如涉及到发现慢查询、分析并优化慢查询、测试优化效果、修改业务代码、发布上线以及观察效果等等。这么长的流程，这么长的周期，很明显给我们解决慢查询造成了非常大的阻力。

慢查询太多，对于业务而言，是有很大风险的，可能随时都会因为某种原因而被触发，并且根据我们的经验，数据库最常出现的问题，都是因为慢查询导致数据库慢了，进而导致整个实例雪崩，从而导致了线上故障。

从另外一个角度来考虑，解决慢查询，是业务和 DBA 双方面的问题，但通常情况下，业务并不关心自己使用的数据库是不是有慢查询，只关心数据库是不是能返回正确的数据，对数据库造成什么影响，并不太关注。而这个时候， DBA 只能去“被动接受”，并且只能是在问题出现之后，再去讨论解决相应的问题。

可能有人会问，有慢查询，难道 DBA 不知道吗？为什么不提前解决，非要等到出了问题才解决，这个问题，就是本文今天的主题，我们如何把被动解决，变为主动。

二、分析

根据上面的背景讲述，我们其实知道，为什么不能提前把问题发现并解决呢？主要原因是， DBA 面对慢查询的海洋时，并不能有效地知道，每个慢查询对业务影响的严重程度，再加上解决慢查询的周期很长，可能针对一个慢查询，从开始到解决完成，需要跟踪半个月都不止，从而造成了慢查询的被动解决，成为 DBA 内心的痛。

所以，其实最根本的原因是慢查询太多，同时慢查询没有明确的优先级，不知道我们最先应该要解决哪个慢查询，业务同学也是不知道的。虽然有平台可查，但他们在面对大量的慢查询时，解决的意愿就不是太高，最终慢查询也越积来越多，直到最后影响业务运行。

所以，最有效的解决办法就是，需要建立一种评分机制，将当前慢查询系统中的慢查询进行评分，按照分数给出优先级，然后根据优先级，将慢查询信息推送给对应的业务方，要求他优先解决可能会对线上产生严重问题的慢查询，再逐步解决次优先级的慢查询，以此类推。

三、解决思路

通过建立一套评分的模型，给定任何一个慢查询，根据慢查询的关键属性，计算出分数。假定总分数为100，分数越高则风险指数越高。

评分模型可以简单描述为：

score=func(x)

四、设计模型

选取评分项

慢查询主要因素是由查询次数( QueryCount )和查询其他各项指标(例如锁等待时间、扫描行数、查询时间、发送数据等)组成。

查询次数

一个慢查询如果执行时间为 1s ，查询次数(QueryCount)为 1 和查询次数为 1000 时，对系统的影响不同，次数越多危害越大，量变会引发质变。QueryCount 最正确的值是这个慢查询当天执行的的最大执行次数。但是预测未来并不可靠，对于线上业务没有人会准确知道下一时刻的查询次数会有多少，故我们使用昨天的数据，通过计算出单个时间窗口内的执行次数的最大值，来计算出这个慢查询对当前系统的影响。单个时间窗口选取太小，比如 10s 、1min 等计算出来的 QueryCount 会太小，并不能清楚的反应指标的危害程度；如果选取太大，比如 30min，1hour 会造成计算出来的 QueryCount 太大，显得指标的危害程度非常高。故我们选取 10min 作为一个参考值，通过以 10min 为窗口，滑动计算出 QueryCount 的最大值，作为慢查询评分模型的指标之一。

查询其他各项指标

慢查询各项因素主要是由慢查询日志中记录的各项指标。

mysql的慢查询说明，慢查询示例

# Time: 210818  9:54:25
# User@Host: fangyuan.qian[fangyuan.qian] @  [127.0.0.1]  Id: 316538768
# Schema:   Last_errno: 0  Killed: 0
# Query_time: 3.278988  Lock_time: 0.001516  Rows_sent: 284  Rows_examined: 1341  Rows_affected: 0
# Bytes_sent: 35600
SET timestamp=1629251665;
SELECT
               a.ts_min                                      AS slowlog_time,
               a.checksum,
               SUM(a.ts_cnt)                                 AS d_ts_cnt,
               ROUND(SUM(a.Query_time_sum), 2)               AS d_query_time,
               ROUND(SUM(a.Query_time_sum) / SUM(a.ts_cnt), 2) AS d_query_time_avg,
               a.host_max                                    AS host_ip,
               a.db_max                                      AS db_name,
               a.user_max                                    AS user_name,
               b.first_seen                                  AS first_seen_time
           FROM mysql_slowlog_192_168_0_84_3306.query_history a force index(idx_ts_min),
                mysql_slowlog_192_168_0_84_3306.query_review b
           WHERE a.checksum = b.checksum
               AND length(a.checksum)>=15
               AND ts_min >= '2021-06-04'
               AND ts_min < '2021-06-21'
           GROUP BY a.checksum;

慢查询字段说明

选取评分项

其中 Time、User@Host 、Id、Schema 、Last_errno 都是描述性的信息不会造成查询变成慢查询；

Query_time 是真实记录慢查询的查询时间，查询时间越长对系统的影响越大；

Lock_time 是当前查询获取数据时获取记录锁而等待的时间，等待时间越长，越可能造成慢查询；

Rows_sent 是发送多少行数据给 client ，同一个查询语句发送的数据行数越大，越可能会造成慢查询；

Rows_examined 是 server 层检索的数据，检索的数据越多，需要的IO和cpu资源也就越多，越可能造成慢查询，并影响服务稳定性；

Rows_affected 只针对修改请求，由于绝大部分慢查询都是 select ，并不会修改数据，故此值可以忽略；

Bytes_sent 是发送多少字节数据给 client ，发送的数据量越多，越可能会造成慢查询；

由于不同的表行大小不同，并且并不是所有列都需要返回，所以一个发送 10 行的数据，可能会比一个发送 100 行数据的查询更慢，Rows_sent 不如 Bytes_sent 更为直观，故我们选取 Bytes_sent ，忽略 Rows_sent 。

所以，慢查询指标中 Query_time、Lock_time 、 Bytes_sent 、 Rows_examined 作为慢查询评分模型中的指标。

综上所述，慢查询评分项共有五项，分别是QueryCount、Query_time、Lock_time、Bytes_sent、Rows_examined。

# Time: 210818  9:54:25# User@Host: fangyuan.qian[fangyuan.qian] @  [127.0.0.1]  Id: 316538768# Schema:   Last_errno: 0  Killed: 0# Query_time: 3.278988  Lock_time: 0.001516  Rows_sent: 284  Rows_examined: 1341  Rows_affected: 0# Bytes_sent: 35600SET timestamp=1629251665;SELECT               a.ts_min                                      AS slowlog_time,               a.checksum,               SUM(a.ts_cnt)                                 AS d_ts_cnt,               ROUND(SUM(a.Query_time_sum), 2)               AS d_query_time,               ROUND(SUM(a.Query_time_sum) / SUM(a.ts_cnt), 2) AS d_query_time_avg,               a.host_max                                    AS host_ip,               a.db_max                                      AS db_name,               a.user_max                                    AS user_name,               b.first_seen                                  AS first_seen_time           FROM mysql_slowlog_192_168_0_84_3306.query_history a force index(idx_ts_min),                mysql_slowlog_192_168_0_84_3306.query_review b           WHERE a.checksum = b.checksum               AND length(a.checksum)>=15               AND ts_min >= '2021-06-04'               AND ts_min < '2021-06-21'           GROUP BY a.checksum;

慢查询字段说明

选取评分项

其中 Time、User@Host 、Id、Schema 、Last_errno 都是描述性的信息不会造成查询变成慢查询；

Query_time 是真实记录慢查询的查询时间，查询时间越长对系统的影响越大；

Lock_time 是当前查询获取数据时获取记录锁而等待的时间，等待时间越长，越可能造成慢查询；

Rows_sent 是发送多少行数据给 client ，同一个查询语句发送的数据行数越大，越可能会造成慢查询；

Rows_examined 是 server 层检索的数据，检索的数据越多，需要的IO和cpu资源也就越多，越可能造成慢查询，并影响服务稳定性；

Rows_affected 只针对修改请求，由于绝大部分慢查询都是 select ，并不会修改数据，故此值可以忽略；

Bytes_sent 是发送多少字节数据给 client ，发送的数据量越多，越可能会造成慢查询；

由于不同的表行大小不同，并且并不是所有列都需要返回，所以一个发送 10 行的数据，可能会比一个发送 100 行数据的查询更慢，Rows_sent 不如 Bytes_sent 更为直观，故我们选取 Bytes_sent ，忽略 Rows_sent 。

所以，慢查询指标中 Query_time、Lock_time 、 Bytes_sent 、 Rows_examined 作为慢查询评分模型中的指标。

综上所述，慢查询评分项共有五项，分别是QueryCount、Query_time、Lock_time、Bytes_sent、Rows_examined。