SQL优化实战复盘：一次慢查询导致的数据库雪崩故障

凌晨两点，监控告警骤响：数据库 CPU 飙升到 98%，连接池瞬间被打满，应用开始大面积报超时错误。值班工程师被迫从床上爬起来，面对的却是一个已经在快速扩散的生产环境灾难。这就是典型的数据库雪崩，而罪魁祸首，往往只是一条平时并不起眼的慢 SQL。本文将完整复盘一次真实故障，从发现问题、锁定根因到彻底解决的排查与优化全过程。

1 故障现象描述

1.1 监控告警

凌晨 2:15，监控系统疯狂推送以下告警：

[CRITICAL] Database CPU: 98%
[CRITICAL] Database Connections: 500/500 (MAX)
[WARNING] Response Time P99: 8000ms
[CRITICAL] MySQL Error Log: Too many connections

1.2 用户反馈

• App 首页加载超时
• 搜索功能完全不可用
• 订单页面直接报错 "Connection timeout"
• 不少用户甚至无法登录

1.3 影响范围

• 订单系统：彻底瘫痪
• 用户系统：功能部分可用
• 商品系统：响应严重超时
• 所有涉及数据库的操作，不是慢得惊人就是直接卡死

2 初步排查

2.1 检查数据库状态

试着连上数据库，先看看它到底有多忙。

-- 连接数据库
mysql -h db-master -u app_user -p

-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';
-- 当前值: 500（最大值）

SHOW STATUS LIKE 'Max_used_connections';
-- 历史最大值: 523

-- 查看当前所有连接
SHOW PROCESSLIST;
-- 发现大量连接处于 "Sending data" 状态
-- 大量连接来自同一个 IP（应用服务器）

2.2 检查数据库负载

从 PROCESSLIST 里发现了更多线索。

-- 查看当前正在执行的所有查询
SHOW FULL PROCESSLIST;

-- 输出（部分）：
+----+-------------+-----------------+------+---------+------+------------+---------------------+
| Id | User        | Host            | db   | Command | Time | State      | Info                |
+----+-------------+-----------------+------+---------+------+------------+---------------------+
|  1 | app_user    | 192.168.1.10:xxxx | app  | Sleep   | 3005 |            | NULL                |
|  2 | app_user    | 192.168.1.10:xxxx | app  | Sleep   | 2890 |            | NULL                |
|  3 | app_user    | 192.168.1.10:xxxx | app  | Sleep   | 2500 |            | NULL                |
|  4 | system_user | localhost       | NULL | Daemon  | 0    |            | NULL                |
+----+-------------+-----------------+-----------------+------+------------+---------------------+

-- 看到大量 Sleep 状态的连接，连接时间 2000-3000 秒
-- 这是典型的连接池连接泄漏或者长事务问题

2.3 检查 InnoDB 状态

再看下 InnoDB 引擎的内部情况。

SHOW ENGINE INNODB STATUS\G

-- 关键输出：
--------
FILE I/O
--------
I/O thread 0: pending writes
I/O thread 1: pending writes
...
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------
I/O thread 0: pending writes
...
---
Transaction id: 123456789
Current transaction: 123456789
SQL thread: 1
...
History list length: 15890

问题发现：History list length: 15890，这个值高得离谱，正常应该在几百以内。这说明有大量事务未提交或者有大事务正在执行，历史数据无法被清理。

2.4 检查慢查询日志

最近的慢查询日志揭露了更直接的问题。

# 查看慢查询日志
tail -100 /var/log/mysql/slow-query.log

# Time: 2025-01-15T02:10:15.123456Z
# User@Host: app_user[app_user] @ app-server [192.168.1.10]
# Query_time: 45.234561  Lock_time: 0.000123 Rows_sent: 0  Rows_examined: 1589234
SET @old_price = (SELECT price FROM products WHERE sku = 'SKU123456');

# Time: 2025-01-15T02:10:16.234567Z
# Query_time: 38.123456  Lock_time: 0.000234 Rows_sent: 0  Rows_examined: 2345678
UPDATE orders SET status = 'shipped', ship_time = NOW() WHERE order_id IN (....);

发现：从凌晨 2:10 开始，突然涌现大量慢查询，执行时间动辄 30 到 60 秒，而且涉及 UPDATE 和 SELECT 语句。

3 根因定位

3.1 确认慢查询

直接查询当前正在运行的“长跑选手”。

-- 查看当前执行时间最长的 10 条查询
SELECT
  id,
  user,
    host,
    db,
    command,
  time,
    state,
  LEFT(info, 100) AS query
FROM information_schema.processlist
WHERE command != 'Daemon'
ORDER BY time DESC
LIMIT 10;

-- 输出：
+----+-------------+------------------+------+---------+------+----------------------------------+---------------------+
| id | user        | host             | db   | command | time | state                            | query               |
+----+-------------+------------------+------+---------+------+----------------------------------+---------------------+
| 50 | app_user    | 192.168.1.10    | app  | Query   | 156  | update                           | UPDATE products...   |
| 51 | app_user    | 192.168.1.10    | app  | Query   | 145  | User sleep                       | SELECT * FROM...    |
| 52 | app_user    | 192.168.1.10    | app  | Query   | 134  | update                           | UPDATE orders...    |
+----+-------------+------------------+------+---------+------+----------------------------------+---------------------+

发现：竟然有查询已经跑了超过 150 秒还在运行！

3.2 分析慢查询

揪出那个执行时间最长（id=50）的 SQL 来看看。

-- 查看当前执行的完整查询
SELECT * FROM information_schema.processlist WHERE id = 50\G

-- 完整 SQL：
UPDATE products p
INNER JOIN order_items oi ON p.id = oi.product_id
SET p.stock = p.stock - oi.quantity,
    p.sales_count = p.sales_count + oi.quantity,
    p.last_sale_time = NOW()
WHERE oi.order_id IN (
  SELECT id FROM orders
  WHERE status = 'pending'
  AND created_at < DATE_SUB(NOW(), INTERVAL 1 HOUR)
);

问题分析：这是一个批量更新产品库存和销量的语句。问题出在使用了 IN (SELECT ...) 子查询。当 orders 表中符合条件的待发货订单非常多时，这个子查询会导致大量扫描，每一行外部的 order_items 都可能触发一次内部的子查询。

3.3 查看执行计划

EXPLAIN 一下，看看MySQL优化器是怎么想的。

EXPLAIN UPDATE products p
INNER JOIN order_items oi ON p.id = oi.product_id
SET p.stock = p.stock - oi.quantity,
    p.sales_count = p.sales_count + oi.quantity,
    p.last_sale_time = NOW()
WHERE oi.order_id IN (
  SELECT id FROM orders
  WHERE status = 'pending'
  AND created_at < DATE_SUB(NOW(), INTERVAL 1 HOUR)
);

-- 输出：
+----+--------------------+----------------------+------------+--------+---------------------+---------------------+
| id | select_type        | table                | type       | key    | rows                | Extra               |
+----+--------------------+----------------------+------------+--------+---------------------+---------------------+
|  1 | UPDATE             | p                    | ALL        | NULL   | 50000               | Using where         |
|  1 | UPDATE             | oi                   | ref        | idx1   | 15                  | Using index         |
|  2 | DEPENDENT SUBQUERY | orders               | index      | PRIMARY| 5000                | Using where         |
+----+--------------------+----------------------+------------+--------+---------------------+---------------------+

-- 问题一目了然：
-- 1. products 表是全表扫描 (type=ALL)
-- 2. 子查询对 orders 表也是全索引扫描 (type=index)，实际上几乎是全表扫了
-- 3. 预估总共扫描数万行

3.4 查看表结构

再看看 orders 表的索引是怎么建的。

-- 查看 orders 表结构
SHOW CREATE TABLE orders\G

-- 输出：
CREATE TABLE `orders` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `order_no` varchar(32) NOT NULL,
  `status` varchar(20) NOT NULL DEFAULT 'pending',
  `user_id` bigint(20) NOT NULL,
  `total_amount` decimal(10,2) NOT NULL,
  `created_at` datetime NOT NULL,
  `ship_time` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_order_no` (`order_no`),
  KEY `idx_status` (`status`)  -- 问题：这是一个低区分度的索引
) ENGINE=InnoDB;

再看下 status 的数据分布，就明白为什么索引失效了。

-- 查看索引区分度
SELECT status, COUNT(*) FROM orders GROUP BY status;
-- pending: 500000
-- shipped: 1000000
-- completed: 5000000
-- 问题：status 字段区分度很低，pending 状态的订单就占了 50 万条，MySQL优化器认为全表扫描可能比走索引再回表更快。

3.5 查看表数据量

最后确认一下各表的数据规模。

-- 查看各表数据量
SELECT
    table_name,
    table_rows,
    data_length / 1024 / 1024 AS data_mb,
    index_length / 1024 / 1024 AS index_mb
FROM information_schema.tables
WHERE table_schema = 'app'
ORDER BY data_length DESC;

-- 输出：
+-----------------+------------+-----------+-----------+
| table_name      | table_rows | data_mb   | index_mb  |
+-----------------+------------+-----------+-----------+
| orders          | 6500000    | 1200 MB   | 150 MB    |
| order_items     | 15000000   | 800 MB    | 200 MB    |
| products        | 50000      | 50 MB     | 10 MB     |
| users           | 1000000    | 200 MB    | 30 MB     |
+-----------------+------------+-----------+-----------+

3.6 根因总结

直接原因：

1. 批量更新 SQL 使用了低效的 IN (SELECT ...) 子查询，导致对 650 万行的 orders 表进行全表扫描。
2. orders 表上的 idx_status 索引区分度极低，优化器放弃了使用它而选择了全表扫描。

间接原因：

1. orders 表缺少一个覆盖查询和排序的复合索引 (status, created_at)。
2. 应用层没有对这类重操作的定时任务做任何限流或分批处理。
3. 慢查询的告警阈值设得太高（5秒），导致没能在这个问题萌芽期被发现。

故障链条：慢查询执行 -> 全表扫描 -> 长时间锁等待 -> 连接池耗尽 -> 应用超时 -> 用户请求堆积 -> 数据库雪崩

4 紧急处理

4.1 终止慢查询

当务之急是止血，先把那些长时间运行的查询杀掉。

-- 先查看所有长时间运行的查询，生成 kill 语句
SELECT CONCAT('KILL ', id, ';') AS kill_cmd
FROM information_schema.processlist
WHERE command = 'Query'
  AND time > 30;

-- 确认后，执行 kill（将上述查询结果复制执行）
-- KILL 50;
-- KILL 51;
-- KILL 52;

注意：千万不要图省事执行 KILL ALL，必须对症下药，只终止那些有问题的查询。

4.2 优化查询临时方案

如果情况紧急，可以先尝试让查询走得更快一点。一个快速的“创可贴”方案是用 JOIN 重写子查询。

-- 临时方案：使用 JOIN 替代 IN (SELECT...)
-- 注意：这只是临时方案，需要进一步优化

UPDATE products p
INNER JOIN (
  SELECT oi.product_id, SUM(oi.quantity) AS total_qty, COUNT(DISTINCT oi.order_id) AS order_count
  FROM order_items oi
  INNER JOIN orders o ON oi.order_id = o.id
  WHERE o.status = 'pending'
    AND o.created_at < DATE_SUB(NOW(), INTERVAL 1 HOUR)
  GROUP BY oi.product_id
) sub ON p.id = sub.product_id
SET p.stock = p.stock - sub.total_qty,
    p.sales_count = p.sales_count + sub.order_count,
    p.last_sale_time = NOW();

4.3 添加索引（紧急）

为查询创建它最需要的索引。在生产环境操作要小心，这可能导致短暂锁表。

-- 紧急添加索引（生产环境谨慎操作，可能导致短暂锁表）
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

4.4 验证修复效果

加了索引后，再看看查询计划，立竿见影。

-- 再次查看执行计划
EXPLAIN SELECT id FROM orders WHERE status = 'pending' AND created_at < DATE_SUB(NOW(), INTERVAL 1 HOUR);

-- 输出：
+----+-------------+-------+------+----------------------+------------------+---------+-------------+------+-------------+
| id | select_type| table | type | key                  | ref              | rows    | Extra       |
+----+-------------+-------+------+----------------------+------------------+---------+-------------+------+-------------+
|  1 | SIMPLE     | orders| range| idx_status_created   | NULL             | 5000    | Using where |
+----+-------------+-------+------+----------------------+------------------+---------+-------------+------+-------------+
-- 扫描行数从 50000 降到 5000，使用了高效的 range 扫描

5 完整优化方案

应急处理后，我们需要一个能长治久安的方案。

5.1 SQL 语句优化

治本之策是将一次性的海量操作，改成分批次的小事务处理。

优化前（问题 SQL）：

UPDATE products p
INNER JOIN order_items oi ON p.id = oi.product_id
SET p.stock = p.stock - oi.quantity,
    p.sales_count = p.sales_count + oi.quantity,
    p.last_sale_time = NOW()
WHERE oi.order_id IN (
  SELECT id FROM orders
  WHERE status = 'pending'
  AND created_at < DATE_SUB(NOW(), INTERVAL 1 HOUR)
);

优化后（分批处理）：

-- 1. 先查询需要更新的订单 ID
SELECT oi.order_id
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.id
WHERE o.status = 'pending'
  AND o.created_at < DATE_SUB(NOW(), INTERVAL 1 HOUR)
LIMIT 1000;

-- 2. 分批更新（每次处理 1000 条订单）
UPDATE products p
INNER JOIN (
  SELECT oi.product_id,
    SUM(oi.quantity) AS total_qty,
    COUNT(DISTINCT oi.order_id) AS order_count
  FROM order_items oi
  INNER JOIN orders o ON oi.order_id = o.id
  WHERE o.status = 'pending'
    AND o.created_at < DATE_SUB(NOW(), INTERVAL 1 HOUR)
    AND oi.order_id IN (/* 1000 个 order_id */)
  GROUP BY oi.product_id
) sub ON p.id = sub.product_id
SET p.stock = p.stock - sub.total_qty,
    p.sales_count = p.sales_count + sub.order_count,
    p.last_sale_time = NOW();

-- 3. 更新订单状态
UPDATE orders
SET status = 'processing', updated_at = NOW()
WHERE status = 'pending'
  AND created_at < DATE_SUB(NOW(), INTERVAL 1 HOUR)
LIMIT 1000;

5.2 添加合适索引

为查询量身打造高效的索引。

-- 优化后的索引设计
-- 1. orders 表：复合索引覆盖查询条件
ALTER TABLE orders
ADD INDEX idx_pending_orders (status, created_at, id);

-- 2. order_items 表：复合索引覆盖 JOIN 条件
ALTER TABLE order_items
ADD INDEX idx_order_product (order_id, product_id);

-- 3. products 表：确保主键查询高效
-- products.id 是主键，已有主键索引

-- 验证索引
SHOW INDEX FROM orders;
SHOW INDEX FROM order_items;

5.3 应用层优化

在代码层面实现分批处理和短暂的休眠，避免对数据库造成持续性冲击。

# Python 批量处理示例
def process_pending_orders(batch_size=1000):
    while True:
        # 1. 获取待处理订单
        order_ids = get_pending_order_ids(limit=batch_size)
        if not order_ids:
            break

        # 2. 分批更新产品库存
        update_product_sales(order_ids)

        # 3. 分批更新订单状态
        update_order_status(order_ids)

        # 4. 提交事务
        commit()

        # 5. 记录进度
        logger.info(f"Processed {len(order_ids)} orders")

        # 6. 适当休眠，避免对数据库造成压力
        time.sleep(0.1)

def get_pending_order_ids(limit):
    sql = """
        SELECT o.id
        FROM orders o
        WHERE o.status = 'pending'
        AND o.created_at < DATE_SUB(NOW(), INTERVAL 1 HOUR)
        LIMIT %s
    """
    return [row[0] for row in db.query(sql, (limit,))]

5.4 配置优化

调整 MySQL 的部分配置，让它更健壮，对慢查询更敏感。

# my.cnf 优化

# 慢查询配置
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 1  # 从5秒调成1秒，让慢查询无处遁形
log_queries_not_using_indexes = 1

# 连接配置
max_connections = 1000
wait_timeout = 60
interactive_timeout = 60

# InnoDB 配置
innodb_buffer_pool_size = 16G
innodb_log_file_size = 2G
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 30

5.5 监控告警优化

增加一个主动监控慢查询的脚本，变被动告警为主动发现。

#!/bin/bash
# filename: mysql_slow_monitor.sh
# MySQL 慢查询监控

MYSQL_HOST="db-master"
MYSQL_USER="monitor"
MYSQL_PASS="monitor_password"
ALERT_EMAIL="ops@example.com"
THRESHOLD=5  # 秒

# 查找最近 5 分钟的慢查询
SLOW_QUERIES=$(mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASS -e "
    SELECT COUNT(*)
    FROM mysql.slow_log
    WHERE start_time > DATE_SUB(NOW(), INTERVAL 5 MINUTE)
    AND query_time > $THRESHOLD;
" 2>/dev/null | tail -1)

if [ "$SLOW_QUERIES" -gt 10 ]; then
    echo "警告: 最近5分钟有 $SLOW_QUERIES 条慢查询" | mail -s "[WARNING] MySQL 慢查询告警" $ALERT_EMAIL
fi

6 验证与总结

6.1 优化效果验证

优化后的查询，性能有了质的飞跃。

-- 优化后的执行时间对比
-- 优化前：45 秒
-- 优化后：0.3 秒

-- 查看优化后的执行计划
EXPLAIN UPDATE products p
INNER JOIN (
  SELECT oi.product_id,
    SUM(oi.quantity) AS total_qty,
    COUNT(DISTINCT oi.order_id) AS order_count
  FROM order_items oi
  INNER JOIN orders o ON oi.order_id = o.id
  WHERE o.status = 'pending'
    AND o.created_at < DATE_SUB(NOW(), INTERVAL 1 HOUR)
  GROUP BY oi.product_id
) sub ON p.id = sub.product_id
SET p.stock = p.stock - sub.total_qty,
    p.sales_count = p.sales_count + sub.order_count,
    p.last_sale_time = NOW();

-- 输出：
+----+-------------+----------------------+--------+-------------------------+-------------------------+--------+-------------------------+
| id | select_type | table                | type   | key                     | ref                     | rows   | Extra                   |
+----+-------------+----------------------+--------+-------------------------+-------------------------+--------+-------------------------+
|  1 | UPDATE      | p                    | range  | PRIMARY                 | sub.product_id          | 100    | Using where             |
|  1 | PRIMARY     | <derived2>           | ALL    | NULL                    | NULL                    | 5000   |                         |
|  2 | DERIVED     | oi                   | ref    | idx_order_product       | const                   | 5000   | Using index condition   |
|  2 | DERIVED     | o                    | range  | idx_pending_orders       | const                   | 5000   | Using where; Using index|
+----+-------------+----------------------+--------+-------------------------+-------------------------+--------+-------------------------+

改进：

• 子查询中的 orders 表成功使用了 idx_pending_orders 索引进行范围扫描。
• order_items 表也利用了 idx_order_product 索引进行高效关联。
• 最终，执行计划预估扫描的行数和实际执行时间都得到了数量级的优化。

6.2 故障复盘

故障时间线：

02:00 - 定时任务启动，执行批量更新
02:10 - 慢查询开始积累
02:12 - 慢查询超过 30 秒
02:14 - 连接池开始耗尽
02:15 - 监控系统告警
02:17 - 工程师介入
02:20 - 终止慢查询
02:25 - 添加索引
02:30 - 服务恢复

损失评估：

• 服务中断时间：约 15 分钟
• 影响订单数：约 3000 单
• 影响用户数：约 2000 用户
• 直接损失：约 10 万元

6.3 改进措施

改进项	优先级	负责人	完成日期
SQL 优化	P0	开发组	2025-01-16
添加索引	P0	DBA	2025-01-16
慢查询阈值调整	P1	DevOps	2025-01-17
监控告警优化	P1	DevOps	2025-01-17
批量操作限流	P2	开发组	2025-01-20
定期巡检	P2	DBA	每周

7 SQL 优化方法论总结

7.1 排查步骤

下次再遇到类似问题，可以遵循这个排查路径：

1. 监控告警发现异常
   ↓
2. 查看数据库状态（连接数、CPU、内存）
   ↓
3. 查看 PROCESSLIST，找到长时间运行的查询
   ↓
4. 分析慢查询日志
   ↓
5. 使用 EXPLAIN 分析执行计划
   ↓
6. 查看表结构和索引
   ↓
7. 确定根因
   ↓
8. 制定优化方案
   ↓
9. 实施优化
   ↓
10. 验证效果

7.2 执行计划分析要点

type 列（访问类型），性能从差到好：

type	说明	性能
ALL	全表扫描	最差
index	全索引扫描	差
range	范围扫描	中
ref	索引查找	良
eq_ref	唯一索引查找	优
const	常量查找	最优

Extra 列（重要提示）：

• Using filesort：需要额外排序，通常是ORDER BY没走索引，建议优化。
• Using temporary：用到了临时表，可能是GROUP BY、DISTINCT等操作带来的，建议优化。
• Using index：用到了覆盖索引，数据直接从索引获取，无需回表，这是最优的。
• Using where：需要在存储引擎返回数据后，再由Server层进行过滤。

7.3 常见优化手段

场景	优化方法
全表扫描	添加合适的索引
子查询	改为 JOIN 或 EXISTS
ORDER BY	确保使用索引或减少排序数据量
GROUP BY	确保使用索引或减少分组数据量
DISTINCT	考虑使用索引覆盖
LIKE 前导通配	使用全文索引或调整查询方式
OR 条件	拆分为 UNION 或确保有复合索引
NULL 判断	使用 IS NULL/IS NOT NULL，注意其索引情况

7.4 预防措施

1. SQL 审查：所有新上线的 SQL 都必须进行审查，并执行 EXPLAIN 检查执行计划。
2. 慢查询监控：设置合理的慢查询阈值（比如1秒），让它能及时暴露问题。
3. 索引审计：定期检查无用索引和缺失的必要索引。
4. 连接池管理：配置合理的超时时间，防止连接泄漏。
5. 批量操作限流：任何大批量读写操作都应分批进行，并增加延时控制。
6. 定期巡检：制度化地检查数据库核心性能指标和慢查询趋势。

7.5 常用排查命令

这份命令清单，或许能在关键时刻帮到你。

-- 查看当前所有连接
SHOW FULL PROCESSLIST;

-- 查看所有慢查询
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

-- 查看未使用索引的查询
SELECT * FROM mysql.general_log
WHERE command_type = 'Query'
  AND argument LIKE '%SELECT%'
  AND argument NOT LIKE '%USE INDEX%'
  AND start_time > DATE_SUB(NOW(), INTERVAL 1 HOUR);

-- 查看表统计信息
SELECT * FROM information_schema.tables
WHERE table_schema = 'your_database';

-- 查看索引使用情况
SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_database';

-- 查看锁等待
SELECT * FROM information_schema.innodb_lock_waits;

-- 查看事务状态
SELECT * FROM information_schema.innodb_trx WHERE trx_state = 'LOCK WAIT';

数据库雪崩，很多时候就是由一条慢查询引发的蝴蝶效应。技术团队能从这次事故中学到的，不仅仅是几条 SQL 的优化技巧，更是一种敬畏：敬畏每一行代码在生产环境的力量。在云栈社区的技术讨论中，我们也经常看到，做好日常巡检、优化好每一条 SQL、设置好前瞻性的监控告警，才是避免“凌晨两点电话”的唯一方法。