MySQL的一条慢SQL查询导致整个网站宕机的解决方法

在寻常情况下，慢查询一般不会直接导致网站挂掉，它们通常只会使应用响应变得缓慢。今天，我恰好碰到了一种情况，一个慢查询竟然让整个网站陷入了瘫痪。

让我带你深入了解一下这个慢查询的细节。

SQL查询的细节展现

这个查询来自MySQL的慢查询日志，查询时间竟然长达70秒！我们来看看这个查询的具体内容：

```sql

SELECT

ss_id, ss_sa_id, ss_si_id, ss_av_zid, ss_av_fid, ss_artno,

ss_av_zvalue, ss_av_fvalue, ss_av_zpic, ss_av_fpic, ss_number,

ss_sales, ss_cprice, ss_price, ss_stock, ss_orderid, ss_status,

ss_add_time, ss_lastmodify

FROM js_sgoods_sku

WHERE ss_si_id = 0 AND ss_status > 0

ORDER BY

ss_orderid DESC, ss_av_fid ASC;

```

当看到这样的慢查询时，我们通常会首先检查这个SQL语句是否有效地使用了索引。于是，我们查看了执行计划：

```sql

explain SELECT ...

```

从执行计划的输出看，这个查询似乎用到了索引`ss_si_id`，但是为什么扫描的行数还是那么多呢？这个问题值得我们深入。

深入执行计划

尽管查询使用了索引`ss_si_id`，但“Using filesort”这一部分的Extra字段值告诉我们，MySQL还需要进行额外的文件排序操作，这可能是导致查询性能下降的原因。“Using where”也表明MySQL在评估条件`ss_status > 0`时也可能产生了额外的开销。

为了优化这个查询，我们需要进一步分析数据分布、索引的选择性以及可能的硬件瓶颈。考虑是否可以通过调整查询逻辑、添加更多的索引或优化表结构来提高查询效率。这需要深入的数据库性能分析和优化工作。

让我们一同深入表结构的世界，希望能从中发掘出有价值的宝藏。我们看到这样的信息呈现在我们面前：

KEY `ss_si_id` (`ss_si_id`,`ss_av_zid`,`ss_av_fid`) USING BTREE, `ss_si_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '对应js_sgoods_info.si_id'。我们看到索引的结构似乎挺合理的，这个`ss_si_id`字段在`goods_info`表中作为主键，也就意味着它的区分度应该很大。我们观察到表中有许多`ss_si_id=0`的记录，这引发了我们的好奇和猜想。

为了验证我们的猜想，我们可以采取以下步骤：

我们要确认统计信息的准确性。只有当我们确信统计信息无误时，我们才能进行下一步的分析。

接下来，我们要计算`ss_si_id=0`的记录数量，这有助于我们进一步验证自己的猜想。我们可以通过执行特定的查询语句来获取这个数据。

我们还可以通过查看索引的统计信息来获取更多线索。比如，我们可以使用以下命令来查看`js_sgoods_sku`表的索引信息：

```sql

xiean@localhost:js_sku 03:27:42> show index from js_sgoods_sku;

```

查看结果后，我们发现`ss_si_id`字段的Cardinality值并没有接近PRIMARY的值，这与我们的预期不符。难道索引的统计信息不准确吗？这个问题值得我们进一步深入。

当我们试图重新收集和数据库表`js_sgoods_sku`的索引统计信息时，我们发现了一些有趣的数据。通过执行`analyze table js_sgoods_sku`命令，我们确认了表的状态分析成功。为了更好地理解索引的性能和行为，我们需要更深入地查看索引的统计信息。

在深入我们的统计数据时，我们发现一个引人注目的现象。在大量的数据中，存在一个特定的标识“ss_si_id=0”的情况数量惊人，占据了整个数据表的41%！

仔细查看，我们发现了一个隐藏在背后的重大问题。有一个SQL查询语句，因为涉及到了大量的“ss_si_id=0”的数据，导致了我们的“goods_service”服务出现异常。服务器的CPU长期占用率高达100%，内存堆栈信息无法直接获取，只能通过强制手段进行dump。更糟糕的是，服务中的所有线程都处于BLOCKED状态，FGC（Full Garbage Collection）频繁发生，每隔10秒就进行一次，内存占用更是超过了分配的内存限额。

这个慢查询成为了问题的罪魁祸首，它查询了过多的数据，最多达到了700万行，直接导致“goods_service”服务的内存暴涨，服务无法及时响应，最终导致服务挂掉。

了解问题所在，我们自然要寻找解决方案。既然问题源于查询“ss_si_id=0”，那么我们自然要从这个SQL语句入手。我们可以采取以下措施：

我们可以在程序逻辑中加入判断，如果查询涉及到“ss_si_id=0”，则直接封杀这个查询。我们可以修改SQL配置文件，优化SQL语句，避免查询过多的数据。

我们发现DB服务器上的这个慢查询大量存在，服务器负载从原本的0.飙升到50以上，连接数也急剧增加。如果不及时处理，DB服务器也可能面临崩溃。

我们采取了以下具体解决方案：

1. 运维团队与研发合作，修改了SQL语句。我们在WHERE条件中增加了一个条件“AND ss_si_id <> 0”，在MySQL的执行计划层面屏蔽了这个SQL。

2. DBA使用pt-kill工具立即终止了这个查询，防止DB服务器因负载过大而崩溃。

通过这个事件，我们得到了两个经验教训：

1. 对于查询默认值的SQL，我们应该从源头上避免这类查询。

2. 我们应该限制查询结果集的大小，避免因查询结果集过大导致服务崩溃。这样，我们就能确保服务的稳定性和数据的流畅处理。