排查慢SQL

慢SQL

1.如何定义慢SQL

​      所谓慢 SQL，就是执行特别慢的 SQL 语句。什么样的 SQL 语句是慢 SQL？多慢才算是慢SQL？并没有一个非常明确的标准或者说是界限。但并不是说，我们就很难区分正常的 SQL和慢 SQL，在大多数实际的系统中，慢 SQL 消耗掉的数据库资源，往往是正常 SQL 的几倍、几十倍甚至几百倍，所以还是非常容易区分的。

​      到底多慢的 SQL 才算慢 SQL。这里面这个“慢”，衡量的单位本来是执行时长，但是时长这个东西，我们在编写 SQL 的时候并不好去衡量。那我们可以用执行 SQL 查询时，需要遍历的数据行数替代时间作为衡量标准，因为查询的执行时长基本上是和遍历的数据行数正相关的。

​      慢SQL 我感觉也没有个人标准，个人的标准也要分场景，业务复杂度等；如果作为常规的用户业务系统，超过1秒就是慢SQL；但是如果是类似生成报表的服务，选择在业务低峰期，从库执行等策略，时间长点也不是不能接受。

2.清除慢SQL对数据库的必要性

​       影响 MySQL 处理能力的因素很多，比如：服务器的配置、数据库中的数据量大小、MySQL的一些参数配置、数据库的繁忙程度等等。但是，通常情况下，这些因素对于 MySQL 性能和处理能力影响范围，大概在几倍的性能差距。所以，我们不需要精确的性能数据，只要掌握一个大致的量级，就足够指导我们的开发工作了。

​        一台 MySQL 数据库，大致处理能力的极限是，每秒一万条左右的简单 SQL，这里的“简单SQL”，指的是类似于主键查询这种不需要遍历很多条记录的 SQL。根据服务器的配置高低，可能低端的服务器只能达到每秒几千条，高端的服务器可以达到每秒钟几万条，所以这里给出的一万 TPS 是中位数的经验值。考虑到正常的系统不可能只有简单 SQL，所以实际的 TPS 还要打很多折扣。

​        我的经验数据，一般一台 MySQL 服务器，平均每秒钟执行的 SQL 数量在几百左右，就已经是非常繁忙了，即使看起来 CPU 利用率和磁盘繁忙程度没那么高，你也需要考虑给数据库“减负”了。

      ​ 你在编写一条查询语句的时候，可以依据你要查询数据表的数据总量，估算一下这条查询大致需要遍历多少行数据。

​        如果遍历行数在百万以内的，只要不是每秒钟都要执行几十上百次的频繁查询，可以认为是安全的。

​        遍历数据行数在几百万的，查询时间最少也要几秒钟，你就要仔细考虑有没有优化的办法。

​        遍历行数达到千万量级和以上的，我只能告诉你，这种查询就不应该出现在你的系统中。

      ​ 当然我们这里说的都是在线交易系统，离线分析类系统另说。

​       遍历行数在千万左右，是 MySQL 查询的一个坎儿。MySQL 中单个表数据量，也要尽量控制在一千万条以下，最多不要超过二三千万这个量级。原因也很好理解，对一个千万级别的表执行查询，加上几个 WHERE 条件过滤一下，符合条件的数据最多可能在几十万或者百万量级，这还可以接受。但如果再和其他的表做一个联合查询，遍历的数据量很可能就超过千万级别了。所以，每个表的数据量最好小于千万级别。
       因此，若出现慢SQL，一个慢 SQL 可以拖垮整个数据库。即使出现慢 SQL，数据库也可以在至多 1 分钟内自动恢复，避免数据库长时间不可用。代价是，可能会有些功能，之前运行是正常的，这个脚本上线后，就会出现问题。

示例

​        我们一起来看一下这个案例。每一个做电商的公司都梦想着做社交引流，每一个做社交的公司都梦想着做电商将流量变现。我的一个朋友他们公司做社交电商，当年很有前途的一个创业方向，当时也是有很多创业公司在做。

​        有一天他找到我，让我帮他分析一下他们系统的问题。这个系统从圣诞节那天晚上开始，每天晚上固定十点多到十一点多这个时段，大概瘫痪一个小时左右的时间，过了这个时段系统自动就恢复了。系统瘫痪时的现象就是，网页和 App 都打不开，请求超时。

​        这个系统的架构是一个非常典型的小型创业公司的微服务架构。系统的架构如下图：整个系统托管在公有云上，Nginx 作为前置网关承接前端所有请求，后端按照业务，划分了若干个微服务分别部署。数据保存在 MySQL 中，部分数据用 Memcached 做了前置缓存。数据并没有按照微服务最佳实践的要求，做严格的划分和隔离，而是为了方便，存放在了一起。这样的存储设计，对于一个业务变化极快的创业公司来说，是合理的。因为它的每个微服务，随时都在随着业务改变，如果做了严格的数据隔离，反而不利于应对需求变化。

      ​ 听了我朋友对问题的描述，我的第一反应是，每天晚上十点到十一点这个时段，是绝大多数内容类 App 的访问量高峰，因为这个时候大家都躺在床上玩儿手机。初步判断，这个故障是和访问量有关系的，看下面这个系统每天的访问量的图，可以印证这个判断。

​        **基于这个判断，排查问题的重点应该放在那些服务于用户访问的功能上。**比如说，首页、商品列表页、内容推荐这些功能。

​        在访问量峰值的时候，请求全部超时，随着访问量减少，系统能自动恢复，基本可以排除后台服务被大量请求打死的可能性，因为如果进程被打死了，一般是不会自动恢复的。排查问题的

​        重点应该放在 MySQL 上。观察下面这个 MySQL 的 CPU 利用率图，发现问题：从监控图上可以看出来，故障时段 MySQL 的 CPU 利用率一直是 100%。这种情况下，MySQL 基本上处于一个不可用的状态，执行所有的 SQL 都会超时。MySQL 这种 CPU 利用率高的现象，绝大多数情况都是由慢 SQL 导致的，所以我们优先排查慢 SQL。

​        MySQL 和各大云厂商提供的 RDS 都能提供慢 SQL 日志，分析慢 SQL 日志，是查找类似问题原因最有效的方法。

​        一般来说，慢 SQL 的日志中，会有这样一些信息：SQL、执行次数、执行时长。通过分析慢

​        SQL 找问题，并没有什么标准的方法，主要还是依靠经验。

​        首先，你需要知道的一点是，当数据库非常忙的时候，它执行任何一个 SQL 都很慢。所以，并不是说，慢 SQL 日志中记录的这些慢 SQL 都是有问题的 SQL。大部分情况下，导致问题的 SQL 只是其中的一条或者几条。不能简单地依据执行次数和执行时长进行判断，但是，单次执行时间特别长的 SQL，仍然是应该重点排查的对象。

MySQL 干什么的时候，CPU 会 100%

​       从前文的分析来看，MySQL 主要是两类线程占用 CPU：系统线程和用户线程。因此 MySQL 独占的服务器上，只需要留意一下这两类线程的情况，就能 Cover 住绝大部分的问题场景。

系统线程

​        在实际的环境中，系统线程遇到问题的情况会比较少，一般来说，多个系统线程很少会同时跑满，只要服务器的可用核心数大于等于 4 的话，一般也不会遇到 CPU 100%，当然有一些 bug 可能会有影响，比如这个：

MySQL BUG

虽然情况比较少，但是在面对问题的常规排查过程中，系统线程的问题也是需要关注的。

用户线程

      ​ 提到用户线程繁忙，很多时候肯定会第一时间凭经验想到慢查询。确实 90% 以上的时候都是“慢查询”引起的，不过作为方法论，还是要根据分析再去得出结论的~

      ​ 参考 us% 的定义，是指用户线程占用 CPU 的时间多少，这代表着用户线程占用了大量的时间。

      ​ 一方面是在进行长时间的计算，例如：order by，group by，临时表，join 等。这一类问题可能是查询效率不高，导致单个 SQL 语句长时间占用 CPU 时间，也有可能是单纯的数据量比较多，导致计算量巨大。另一方面是单纯的 QPS 压力高，所以 CPU 的时间被用满了，比如 4 核的服务器用来支撑 20k 到 30k 的点查询，每个 SQL 占用的 CPU 时间并不多，但是因为整体的 QPS 很高，所以 CPU 的时间被占满了。

3.如何排查慢SQL，如何进行定位分析

数据库服务器的优化步骤

s1 观察数据库服务器

      ​ 观察服务器的状态是否存在周期性的波动。如果存在周期性波动，有可能是周期性节点的原因，比如双十一、促销活动等。这样的话，我们可以通过 A1 这一步骤解决，也就是加缓存，或者更改缓存失效策略。

      ​ 如果缓存策略没有解决，或者不是周期性波动的原因，我们就需要进一步分析查询延迟和卡顿的原因。接下来进入 S2 这一步。

s2 开启慢查询

​        开启慢查询。慢查询可以帮我们定位执行慢的 SQL语句。我们可以通过设置long_query_time参数定义“慢”的阈值，如果 SQL 执行时间超long_query_time，则会认为是慢查询。当收集上来这些慢查询之后，我们就可以通过分析工具对慢查询日志进行分析。

s3 EXPLAIN

​        我们就知道了执行慢的 SQL 语句，这样就可以针对性地用 EXPLAIN 查看对应 SQL 语句的执行计划，或者使用 SHOW PROFILE 查看 SQL 中每一个步骤的时间成本。这样我们就可以了解 SQL 查询慢是因为执行时间长，还是等待时间长。

A2 调优服务器参数【SQL等待时间长】

      ​ 如果是 SQL 等待时间长，我们进入 A2 步骤。在这一步骤中，我们可以调优服务器的参数，比如适当增加数据库缓冲池等。

A3 索引优化设计等【SQL执行时间长】

      如果是 SQL 执行时间长，就进入 A3 步骤，这一步中我们需要考虑是索引设计的问题？还是查询关联的数据表过多？还是因为数据表的字段设计问题导致了这一现象。然后在这些维度上进行对应的调整。

A4 性能瓶颈

​        如果 A2 和 A3 都不能解决问题，我们需要考虑数据库自身的 SQL 查询性能是否已经达到了瓶颈，如果确认没有达到性能瓶颈，就需要重新检查，重复以上的步骤。如果已经达到了性能瓶颈，进入 A4 阶段，需要考虑增加服务器，采用读写分离的架构，或者考虑对数据库分库分表，比如垂直分库、垂直分表和水平分表等。

​        以上就是数据库调优的流程思路。当我们发现执行 SQL 时存在不规则延迟或卡顿的时候，就可以采用分析工具帮我们定位有问题的 SQL，这三种分析工具你可以理解是 SQL 调优的三个步骤：慢查询、EXPLAIN 和 SHOW PROFILE。

使用慢查询定位执行慢的SQL

​        慢查询可以帮我们找到执行慢的 SQL，在使用前，我们需要先看下慢查询是否已经开启，使用下面这条命令即可：

 mysql > show variables like'%slow_query_log';
1

​
​        我们能看到slow_query_log=OFF，也就是说慢查询日志此时是关上的。我们可以把慢查询日志打开，注意设置变量值的时候需要使用 global，否则会报错：

mysql > set global slow_query_log='ON';
1

​
​        然后我们再来查看下慢查询日志是否开启，以及慢查询日志文件的位置：

​       你能看到这时慢查询分析已经开启，同时文件保存在 DESKTOP-4BK02RP-slow 文件中。

​       接下来我们来看下慢查询的时间阈值设置，使用如下命令：

 mysql > show variables like '%long_query_time%';
1

​       这里如果我们想把时间缩短，比如设置为 3 秒，可以这样设置：

 mysql > set global long_query_time = 3;
1

我们可以使用 MySQL 自带的 mysqldumpslow 工具统计慢查询日志（这个工具是个 Perl 脚本，你需要先安装好 Perl）。

mysqldumpslow 命令的具体参数如下：

-s：采用 order 排序的方式，排序方式可以有以下几种。分别是 c（访问次数）、t（查询

时间）、l（锁定时间）、r（返回记录）、ac（平均查询次数）、al（平均锁定时间）、ar

（平均返回记录数）和 at（平均查询时间）。其中 at 为默认排序方式。

-t：返回前 N 条数据 。

-g：后面可以是正则表达式，对大小写不敏感。

比如我们想要按照查询时间排序，查看前两条 SQL 语句，这样写即可：

perl mysqldumpslow.pl -s t -t 2 "C:\ProgramData\MySQL\MySQL Server 8.0\Data\DESKT
1

​
​        你能看到开启了慢查询日志，并设置了相应的慢查询时间阈值之后，只要查询时间大于这个阈值的 SQL 语句都会保存在慢查询日志中，然后我们就可以通过 mysqldumpslow 工具提取想要查找的 SQL 语句了。

先检查设备

​
​        生产中要看表的状态再做explain，如果表的DML过高的话，explain的操作完全没有价值。

​
​        如果一张表的自增跑到了100万，数据量只有10万；说明这张表可能已经损坏了，第一步就是修复表而不是一开始explain。就像我们拿到一台设备不是先去测功能，首先应当坚持设备是否完全OK再去测试，数据库不可能拿到的是一张全新的表；首先应当是表的性能评估，然后再说相关的检查吧。

如何使用 EXPLAIN 查看执行计划

​
​        定位了查询慢的 SQL 之后，我们就可以使用 EXPLAIN 工具做针对性的分析，比如我们想要了解 product_comment 和 user 表进行联查的时候所采用的的执行计划，可以使用下面这条语句：

​
​        EXPLAIN 可以帮助我们了解数据表的读取顺序、SELECT 子句的类型、数据表的访问类型、可使用的索引、实际使用的索引、使用的索引长度、上一个表的连接匹配条件、被优化器查询的行的数量以及额外的信息（比如是否使用了外部排序，是否使用了临时表等）等。

​
​        SQL 执行的顺序是根据 id 从大到小执行的，也就是 id 越大越先执行，当 id 相同时，从上到下执行。

​
​        数据表的访问类型所对应的 type 列是我们比较关注的信息。type 可能有以下几种情况：

type列

​
​        在这些情况里，all 是最坏的情况，因为采用了全表扫描的方式。index 和 all 差不多，只不过index 对索引表进行全扫描，这样做的好处是不再需要对数据进行排序，但是开销依然很大。

如果我们在 Extral 列中看到 Using index，说明采用了索引覆盖，也就是索引可以覆盖所需的SELECT 字段，就不需要进行回表，这样就减少了数据查找的开销。

比如我们对 product_comment 数据表进行查询，设计了联合索引composite_index(user_id, comment_text)，然后对数据表中的comment_id、comment_text、user_id这三个字段进行查询，最后用 EXPLAIN 看下执行计划：

​
​        你能看到这里的访问方式采用了 index 的方式，key 列采用了联合索引，进行扫描。Extral 列为 Using index，告诉我们索引可以覆盖 SELECT 中的字段，也就不需要回表查询了。

​
​        range 表示采用了索引范围扫描，这里不进行举例，从这一级别开始，索引的作用会越来越明显，因此我们需要尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式。

​
​        index_merge 说明查询同时使用了两个或以上的索引，最后取了交集或者并集。比如想要对comment_id=500000 或者user_id=500000的数据进行查询，数据表中 comment_id 为主键，user_id 是普通索引，我们可以查看下执行计划：

​
​        你能看到这里同时使用到了两个索引，分别是主键和 user_id，采用的数据表访问类型是index_merge，通过 union 的方式对两个索引检索的数据进行合并。

​
​        ref 类型表示采用了非唯一索引，或者是唯一索引的非唯一性前缀。比如我们想要对user_id=500000的评论进行查询，这里 user_id 为普通索引（因为 user_id 在商品评论表中可能是重复的），因此采用的访问类型是 ref，同时在 ref 列中显示 const，表示连接匹配条件是常量，用于索引列的查找。

​
​        eq_ref 类型是使用主键或唯一索引时产生的访问方式，通常使用在多表联查中。假设我们对product_comment表和 user 表进行联查，关联条件是两张表的 user_id 相等，使用

​
​        const 类型表示我们使用了主键或者唯一索引（所有的部分）与常量值进行比较，比如我们想要查看comment_id=500000，查看执行计划：

​
​        需要说明的是 const 类型和 eq_ref 都使用了主键或唯一索引，不过这两个类型有所区别，

​       ​ const 是与常量进行比较，查询效率会更快，而 eq_ref 通常用于多表联查中。

​
​        system 类型一般用于 MyISAM 或 Memory 表，属于 const 类型的特例，当表只有一行时连接类型为 system（我在 GitHub 上上传了 test_myisam 数据表，该数据表只有一行记录，下你能看到除了 all 类型外，其他类型都可以使用到索引，但是不同的连接方式的效率也会有所不同，效率从低到高依次为

all < index < range < index_merge < ref < eq_ref <const/system。我们在查看执行计划的时候，通常希望执行计划至少可以使用到 range 级别以上的连接方式，如果只使用到了 all 或者 index 连接方式，我们可以从 SQL 语句和索引设计的角度上进行改进。

使用 SHOW PROFILE 查看 SQL 的具体执行成本

​
​        SHOW PROFILE 相比 EXPLAIN 能看到更进一步的执行解析，包括 SQL 都做了什么、所花费的时间等。默认情况下，profiling 是关闭的，我们可以在会话级别开启这个功能。

mysql > show variables like 'profiling';
1

通过设置profiling='ON’来开启 show profile：

 mysql > set profiling = 'ON'
1

我们可以看下当前会话都有哪些 profiles，使用下面这条命令：

mysql > show profiles;
1

你能看到当前会话一共有 2 个查询，如果我们想要查看上一个查询的开销，可以使用：

mysql > show profile;
1

我们也可以查看指定的 Query ID 的开销，比如show profile for query 2查询结果是一样的。在 SHOW PROFILE 中我们可以查看不同部分的开销，比如 cpu、block.io 等：

​
​        通过上面的结果，我们可以弄清楚每一步骤的耗时，以及在不同部分，比如 CPU、block.io的执行时间，这样我们就可以判断出来 SQL 到底慢在哪里。

​
​        不过 SHOW PROFILE 命令将被弃用，我们可以从 information_schema 中的 profiling 数据表进行查看。

总结

​
​        我今天梳理了 SQL 优化的思路，从步骤上看，我们需要先进行观察和分析，分析工具的使用在日常工作中还是很重要的。今天只介绍了常用的三种分析工具，实际上可以使用的分析工具还有很多。

​
​        我在这里总结一下今天文章里提到的三种分析工具。我们可以通过慢查询日志定位执行慢的SQL，然后通过 EXPLAIN 分析该 SQL 语句是否使用到了索引，以及具体的数据表访问方式是怎样的。

​
​        我们也可以使用 SHOW PROFILE 进一步了解 SQL 每一步的执行时间，包括 I/O 和CPU 等资源的使用情况。

​
​        今天我介绍了 EXPLAIN 和 SHOW PROFILE 这两个工具，你还使用过哪些分析工具呢？

​
​        另外我们在进行数据表连接的时候，会有多种访问类型，你可以讲一下 ref、eq_ref 和 const这三种类型的区别吗？查询

4.如何避免慢SQL

1.在编写 SQL 的时候，一定要小心谨慎地仔细评估。先问自己几个问题：

​ 你的 SQL 涉及到的表，它的数据规模是多少？

​ 你的 SQL 可能会遍历的数据量是多少？

​ 尽量地避免写出慢 SQL。

2.能不能利用缓存减少数据库查询次数？在使用缓存的时候，还需要特别注意的就是缓存命中率，要尽量避免请求命中不了缓存，穿透到数据库上。

今天我介绍了 EXPLAIN 和 SHOW PROFILE 这两个工具，你还使用过哪些分析工具呢？

​ 另外我们在进行数据表连接的时候，会有多种访问类型，你可以讲一下 ref、eq_ref 和 const这三种类型的区别吗？