慢查询

1. 定义

界定什么是慢查问题

慢查询正如它的名字说的，比较慢的查询，那什么是算是慢查询呢？在贝壳为了方便管理统一把超过1秒的查询定义为慢查询。本章我们会重点讨论慢查询问题，这里说的慢查询问题是指由于数据库设计或者使用不当，导致查询效率低下，进而引起的线上服务不可用的问题。

2. 背景知识

慢查询相关的背景知识，可以总结概括，附上相关详细文章

2.1 慢查询引发问题的技术原理是什么？

性能问题特别是系统容量导致问题，基本上都可以概括为某一项资源被占满从而导致该项资源不能够及时被获取，上游系统被动排队等待，从而引发整个系统不可用。

下面我们将深入剖析，介绍如何观测MySQL运行饱和度指标，以及对应的深层次的技术原理

2.1.1 怎么衡量MySQL的运行的饱和程度？

每秒查询次数（Queries per second，QPS）可以衡量数据库的吞吐量，但不能反映MySQL的工作强度。后者由Threads_running度量，表示为量表（而QPS是速率）。在讨论Threads_running之前，让我们考虑一个类比：

QPS 和 Threads_running 也因许多因素而有很大差异：硬件规格（CPU内核和速度，存储类型和IOPs等），查询类型（读或写），查询计划，表大小，行大小，表模式（列类型，索引-特别是索引），数据访问模式（全读或全写或混合），“嘈杂的邻居”，一天中的时间（例如营业时间），一年中的时间（例如纳税季节），特殊促销，糟糕的演员，等等-随便你说吧！因此，与汽车一样，相同的QPS可以在不同的Threads_running上实现，因此这是一个重要的指标。

img-11

2.1.2 MySQL连接池的实现原理

连接池通常实现在Client端，是指应用(客户端)创建预先创建一定的连接，利用这些连接服务于客户端所有的DB请求。如果某一个时刻，空闲的连接数小于DB的请求数，则需要将请求排队，等待空闲连接处理。通过连接池可以复用连接，避免连接的频繁创建和释放，从而减少请求的平均响应时间，并且在请求繁忙时，通过请求排队，可以缓冲应用对DB的冲击。线程池实现在server端，通过创建一定数量的线程服务DB请求，相对于one-conection-per-thread的一个线程服务一个连接的方式，线程池服务的最小单位是语句，即一个线程可以对应多个活跃的连接。通过线程池，可以将server端的服务线程数控制在一定的范围，减少了系统资源的竞争和线程上下文切换带来的消耗，同时也避免出现高连接数导致的高并发问题。连接池和线程池相辅相成，通过连接池可以减少连接的创建和释放，提高请求的平均响应时间，并能很好地控制一个应用的DB连接数，但无法控制整个应用集群的连接数规模，从而导致高连接数，通过线程池则可以很好地应对高连接数，保证server端能提供稳定的服务。如图2所示，每个web-server端维护了3个连接的连接池，对于连接池的每个连接实际不是独占db-server的一个worker，而是可能与其他连接共享。这里假设db-server只有3个group，每个group只有一个worker，每个worker处理了2个连接的请求。

img-12

查看线程相关的状态变量：SHOW STATUS LIKE 'Threads%'

img-13

输出说明：

  • Threads_cached：缓存中的线程连接数，该变量在嵌入式服务器(libmysqld)无意义
  • Threads_connected：当前打开的连接数，该值和SHOW PROCESSLIST;输出记录总数一样
  • Threads_created：为处理连接而创建的线程数。如果该值很大，可能要增加thread_cache_size。缓存未命中率=Theads_created/Connections
  • Thread_cache_size：但客户端断开时，如果缓存中的线程连接数即Threads_cached，比thread_cache_size小，那么这个客户端线程会被放入缓存中，如果可能，通过重用缓存中线程来满足线程请求。如果有许多新连接，可以增加该项值来提高性能。
  • Threads_running：非睡眠状态的连接数，通常指并发连接数

3. 现象

结合故障说明可能观测到的系统和监控的表现

如何界定一个线上问题，是否是由慢查询引起，是一件复杂的事情。因为慢查询问题爆发时，系统从整体看，会呈现出请求超时、线程数激增、数据库连接数激增等现象。根据以上现象，并不能直接断定系统是因为慢查询导致整体性能的下降。因此，我们需要通过监控日志系统，快速排除是否是慢查询问题。

3.1 Java服务

通过查看故障期间的 Hawk 监控日志，如果看到高频的 SQL 超时的异常，可断定大概率为慢查询引起的问题。进入异常详情，可以快速定位到具体的 SQL。

3.1 非Java服务

如果在事前，通过记录服务的 SQL 日志，并且将日志接入天眼，可以通过天眼的日志检索能力查看是否存在某个 SQL 的大量超时情况。同时，可以配置日志报警，提前发现问题。

如果没有 SQL 日志，可以通过访问 XMEN 系统，查询故障所在服务关联的 MySQL 资源在故障期间是否存在慢查询，如下图所示：

img-10

4. 可能引起的原因

结合故障分析可能导致慢查询原因

4.1 索引设计不当

索引缺少是导致慢查询的常见原因，也是大家最容易犯的错误。

参考故障：2020-03-03【D级-44分】CA部分用户查看数据看板无数据

故障链接：https://wiki.lianjia.com/pages/viewpage.action?pageId=570980824

故障原因：CA数据看板中的人店关系表缺少索引，导致出现大量慢查询，服务异常

4.2 流量上升导致查询堆积

业务的访问量上升，导致MySQL的压力上升，部分性能不高的查询被拖慢

参考故障：2020-05-24【D级-59.5分】新房置业报告生成失败

故障链接：https://wiki.lianjia.com/pages/viewpage.action?pageId=622687302

故障原因：下载新房置业报告需要调用新房客源，置业报告下载的请求中存在一个慢查询，一个高并发导致数据库SQL堆积，无法处理其他请求。导致下载新房置业报告失败。

5. 处理方法

如何快速止损、如何解决问题

5.1 快速止损

通常线上的慢查询爆发有两种情况：

  1. 上线新功能，包含产生线上慢查询的 SQL。
  2. 设计不当的 SQL 由于数据量或者流量的增加导致达到系统承载的瓶颈点。

第一种情况，务必快速执行回滚操作。

第二种情况，如果能够进行业务降级，优先考虑业务降级。如果不能，可以通过添加或者优化索引的方式，进行故障恢复。注意：不要在故障发生期间尝试优化SQL！

5.2 解决慢查询问题

5.2.1 事前预防

通过事前的预防，可以消除很多线上慢查询问题。对数据库比较熟悉的同学，一定知道，可以通过开启慢查日志来了解数据库具体有哪些慢查，具体多长时间算为慢查，也可以设置的。在贝壳的同学，可以通过 XMEN系统 来查询，我们的数据库有哪些慢查询。我们的 DBA 为了我们更好的了解慢查的产生原因，给我们做了专业的慢查页面，来我们对每一个慢查，产生的原因深度的了解，而且还会给我们优化的建议，让我们更快、更好的解决掉慢查询。我们可能通过点击上面的链接中的 SQL 跳到分析页面。

img-1

img-2

大家可以看到，从这个页面，我们可以从很多维度，了解到我们的慢查询。

除此之外，我们 XMEN 的数据库的负责人，每天都会收到对应库的慢查情况。群里我们也可以通过配置机器人的方式方法，来查看我们团队的慢查情况，详情可以查看：慢查消息机器人使用手册 来每天了解我们团队的慢查情况。

5.2.2 事后处理

事后，我们需要针对引起故障的 SQL 做性能优化。优化的核心就是把一个查询比较慢的 SQL 变为一个比较快的 SQL，提升查询速度，那提升查询速度的方式方法，有哪些呢？

索引方式优化

其实，非常多的情况，是由于 SQL 没有走适当的索引导致的。但是不是加上适当的索引就可以了呢？远远不是这么简单，很多情况是由于，加了索引，但使用不正确，导致的无法走索引，有哪些不走索引的情况呢？对此进行了一些整理，需要大家多多注意，具体如下：

  1. WHERE 条件中的 1=1，查询条件永远为真，可能导致 WHERE 条件失效进行全表查询。
  2. 使用函数或者隐式转化会导致不走索引。
  3. LIKE 匹配通配符号在前面的时候，不走索引。
  4. 使用了否定条件。
  5. OR 其中一个有索引，另一个没有的情况。
  6. 多列索引需要满足最左匹配原则。
  7. 两张表字符集不一样或者编码不一样，连表查询时。
  8. IN 的内容过多，会不走索引。
  9. 低区分度的列上索引，可能不会补使用。
拆解分批

如果无法用索引的方式进行优化，比如数据量就是达到特别大的量，那就需要通过拆解、分批的方式，来解决，具体如下：

  1. 多层嵌套查询改为多次查询。
  2. IN 子查询影响查询性能，用 JOIN 方式代替。
  3. 一次查询数量过于庞大，拆成多次查询、拼装。
  4. 用了反向查询（比如 NOT IN）或者 IN 语句参数集太多，可能会导致全表扫描，这种情况尽量拆分语句，或者内存中过滤解决。
  5. 将⼤字段、访问频率低的字段拆分到单独的表中存储，分离冷热数据。
  6. 分库分表。
  7. 归档。
高效写法

有的时候，有些高效的写法，可以很有效的改善我们的 SQL，让我们的 SQL 效率得到大大的提升。

  1. 用了 LIMIT 没有 ORDER BY 可能会导致结果不确定性，一定要加 ORDER BY。
  2. 跑批任务使用 LIMIT 扫描表会导致慢查询，在 LIMIT 前加上 WHERE startId > 条件，减少 DB 的 IO。
  3. 为 GROUY BY 显示添加 ORDER BY 条件，如果没有，会导致无谓的排序产生，实在不需要排序的，都加上了 ORDER BY NULL。
  4. 有别名的，都显示的加上 AS。

6、案例解析

6.1 过程复盘

我们用一个例子，来更好的帮助大家理解，慢查询如何解决定位，让大家更好的快速解决慢查，也会让大家对查询有更好的认知。

我们以加盟商 【贝壳】故障报告 2020-12-02【C级-73分】加盟商主数据平台等多域名报错 的一个慢查事故为例子。大家可以看到，这个问题现在看来非常简单，加上索引就可以搞定，但是却持续了43分钟，最后成为一个 C 级事故，这是为什么呢？

首先，我们发生事故之初，会收到各种各样的报警，比如：

img-3

天眼报警一般是最快的，这时，我们就要紧张起来了。如果还有下面的报警：

img-4

那我们就要点击详情后面的链接，去看里面的细节了

img-5

默认打开是在 Problem 里面，大家可以点进去看一下细节，可能会看到超时原因，如果找不到超时原因，那就点一下下面的菜单看一下：

img-6

我们可能很快发现，有 mapper 执行出问题。而且点进去，可能直接定位到具体的 SQL 如下：

img-7

这样，我们就很快能定位到具体的 SQL 问题，然后，就可以想办法解决这个慢 SQL 就好了，相信大家看了理论篇，这个都难不倒大家。

其实有的时候，还会有一些其它报警，比如：

img-8

或者

img-9

或者在望火楼报警查看MySQL报警红绿灯，看到 MySQL_threads_conned_high 或者 MySQL_threads_running_high 这两个字样的红灯 img-15

再或者DBA老板们通常会帮助研发同学进一步提供当前的负载情况 img-16

这些内容，都说明了，当前我们所负责的系统依赖的MySQL资源已经饱和。

如果大家收到这样的报警，就不要太浪费在别的上面，尽快重启，同时快速解决这些慢查。你的服务就很快恢复啦。

6.2 经验总结

我们再一起回顾一下，为什么主数据的事故，持续了43分钟之久？

首先，事故发生了，相关人员进行了重启，这个是非常好的，很多问题，有的时候，可以通过重启解决，快速恢复线上，是我们的第一目标。

但是，重启没有生效之后，发现了 thread_running_High 的问题，此时，没有正确理解到有慢查询，去扩展了连接池数量，这个又需要重启，又需要改代码，再打包发线上，明显浪费了很多时间。

所以，大家一定要从表象找本质，找到根本的原因去解决。如果只治标不治本，那是解决不了问题的，而且还会浪费时间。

results matching ""

    No results matching ""