MySQL 慢查询优化实战：从 8 秒到 0.03 秒，我做了这 7 件事

上线一年的业务系统，数据库突然开始慢查询告警，查询耗时从毫秒级飙升到 8 秒以上。这篇文章记录了我的完整排查和优化过程。

---

问题背景

一个运行了一年的用户行为分析系统，随着数据积累到 3000 万行，核心查询接口从 200ms 变成了 8 秒以上，偶尔还会把数据库连接池耗尽，导致服务不可用。

系统配置：
– 腾讯云 MySQL 8.0（4核8G，通用型）
– 核心表：user_events，3000 万行记录
– 业务：实时统计、报表分析、用户行为回放

---

第一步：开启慢查询日志，定位问题 SQL

不知道慢在哪，一切优化都是瞎猜。先把慢查询日志开起来。

-- 查看当前慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询（超过 1 秒的 SQL 会被记录）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 记录没有使用索引的查询（强烈推荐开启）
SET GLOBAL log_queries_not_using_indexes = 'ON';

云上 RDS 可以在控制台直接配置，不需要手动改参数。

用 mysqldumpslow 分析慢查询日志：

# 按执行时间排序，显示 top 10
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 按出现次数排序（频繁出现的 SQL 更值得优化）
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

找到了我们的”头号嫌疑犯”：

-- 这条 SQL 平均耗时 7.8 秒，每分钟执行 20+ 次
SELECT user_id, event_type, COUNT(*) as cnt, MAX(created_at) as last_time
FROM user_events
WHERE created_at >= '2026-01-01'
  AND tenant_id = 123
  AND event_type IN ('click', 'view', 'purchase')
GROUP BY user_id, event_type
ORDER BY cnt DESC
LIMIT 100;

---

第二步：EXPLAIN 分析执行计划

EXPLAIN SELECT user_id, event_type, COUNT(*) as cnt, MAX(created_at) as last_time
FROM user_events
WHERE created_at >= '2026-01-01'
  AND tenant_id = 123
  AND event_type IN ('click', 'view', 'purchase')
GROUP BY user_id, event_type
ORDER BY cnt DESC
LIMIT 100;

输出结果：

id | select_type | table       | type | possible_keys | key  | rows      | Extra
1  | SIMPLE      | user_events | ALL  | NULL          | NULL | 30187264  | Using where; Using filesort

关键问题：
– type = ALL：全表扫描，扫描了 3000 万行！
– key = NULL：没有命中任何索引
– Using filesort：内存排序，额外消耗
– rows = 30187264：预估扫描行数 3000 万

---

第三步：建立合适的索引

分析查询特征

这条 SQL 的 WHERE 条件是：tenant_id = 123 AND event_type IN (...) AND created_at >= '...'

索引建立原则：区分度高的列放前面，范围查询列放最后。

tenant_id 区分度：假设有 500 个租户，选择性 = 500/3000万 = 1/60000，非常高
event_type 区分度：假设有 20 种事件类型，中等
created_at 是范围查询，放最后

-- 方案一：联合索引（推荐）
ALTER TABLE user_events 
ADD INDEX idx_tenant_event_time (tenant_id, event_type, created_at);

-- 方案二：覆盖索引（把 SELECT 的列也加进来，避免回表）
ALTER TABLE user_events 
ADD INDEX idx_tenant_event_time_cover (tenant_id, event_type, created_at, user_id);

加完索引再次 EXPLAIN：

id | type  | key                      | rows   | Extra
1  | range | idx_tenant_event_time    | 89234  | Using index condition; Using filesort

• type = range：范围扫描，扫描行数从 3000 万降到 8.9 万
• 命中了索引
• 仍有 Using filesort：因为 ORDER BY cnt 是计算字段，无法通过索引消除

执行时间：8.2 秒 → 1.4 秒，大幅提升，但还不够快。

---

第四步：优化 GROUP BY + ORDER BY

ORDER BY cnt DESC 中的 cnt 是聚合计算的结果，MySQL 必须计算完所有 GROUP BY 的结果才能排序，这就是 Using filesort 的根因。

方法：利用子查询减少数据集

-- 优化前：全量 GROUP BY 后再 ORDER BY
SELECT user_id, event_type, COUNT(*) as cnt, MAX(created_at) as last_time
FROM user_events
WHERE created_at >= '2026-01-01'
  AND tenant_id = 123
  AND event_type IN ('click', 'view', 'purchase')
GROUP BY user_id, event_type
ORDER BY cnt DESC
LIMIT 100;

-- 优化后：先缩小范围，再聚合
SELECT e.user_id, e.event_type, 
       COUNT(*) as cnt, 
       MAX(e.created_at) as last_time
FROM user_events e
WHERE e.tenant_id = 123
  AND e.event_type IN ('click', 'view', 'purchase')
  AND e.created_at >= DATE_SUB(NOW(), INTERVAL 90 DAY)  -- 限制时间范围
GROUP BY e.user_id, e.event_type
ORDER BY cnt DESC
LIMIT 100;

执行时间：1.4 秒 → 0.3 秒

---

第五步：分区表——彻底解决历史数据积压问题

真正的问题是：随着数据增长，再好的索引也会变慢。分区表是终极方案。

-- 将 user_events 改造为按月分区
CREATE TABLE user_events_new (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id   INT NOT NULL,
    user_id     BIGINT NOT NULL,
    event_type  VARCHAR(50) NOT NULL,
    event_data  JSON,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, created_at),          -- 分区键必须在主键中
    INDEX idx_tenant_event (tenant_id, event_type, created_at)
)
PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
    PARTITION p202401 VALUES LESS THAN (202402),
    PARTITION p202402 VALUES LESS THAN (202403),
    PARTITION p202403 VALUES LESS THAN (202404),
    -- ... 以此类推
    PARTITION p202501 VALUES LESS THAN (202502),
    PARTITION p_future VALUES LESS THAN MAXVALUE   -- 兜底分区
);

分区后，查询近 3 个月的数据，MySQL 会自动分区裁剪（Partition Pruning），只扫描对应月份的分区，而不是整张表。

-- 验证分区裁剪是否生效
EXPLAIN PARTITIONS SELECT ...;
-- 看 partitions 列，只出现 p202503,p202504,p202504 则说明裁剪生效

执行时间：0.3 秒 → 0.05 秒

---

第六步：读写分离 + 查询缓存

分析类查询往往是重度读操作，可以通过以下方式进一步降低压力：

配置 MySQL 主从读写分离

# SQLAlchemy 读写分离配置
from sqlalchemy import create_engine

# 写操作走主库
master_engine = create_engine(
    "mysql+pymysql://user:pass@master-host:3306/dbname",
    pool_size=10,
    max_overflow=20,
)

# 读操作走从库（腾讯云 MySQL 只读实例）
slave_engine = create_engine(
    "mysql+pymysql://user:pass@slave-host:3306/dbname",
    pool_size=20,      # 读库连接池更大
    max_overflow=40,
)

def get_db_engine(readonly=False):
    return slave_engine if readonly else master_engine

Redis 缓存统计结果

import redis
import json
import hashlib
from datetime import timedelta

redis_client = redis.Redis(host='your-redis-host', port=6379, db=0)

def get_user_event_stats(tenant_id: int, event_types: list, days: int = 90):
    """带缓存的统计查询"""

    # 生成缓存 key
    cache_key = f"event_stats:{tenant_id}:{','.join(sorted(event_types))}:{days}"

    # 尝试从缓存获取
    cached = redis_client.get(cache_key)
    if cached:
        return json.loads(cached)

    # 缓存未命中，查数据库
    results = query_from_db(tenant_id, event_types, days)

    # 写入缓存，5分钟过期（统计数据可接受轻微延迟）
    redis_client.setex(
        cache_key,
        timedelta(minutes=5),
        json.dumps(results, ensure_ascii=False, default=str)
    )

    return results

执行时间（缓存命中）：0.05 秒 → 0.003 秒

---

第七步：定期维护，防止性能退化

建立一个定期维护机制，避免问题重复出现：

-- 每周执行：更新统计信息（影响查询计划准确性）
ANALYZE TABLE user_events;

-- 每月执行：重建碎片化索引
ALTER TABLE user_events ENGINE=InnoDB;  -- 重建整张表（有锁，业务低峰期执行）
-- 或使用 pt-online-schema-change 在线重建

-- 删除 3 个月前的历史分区（配合分区表使用）
ALTER TABLE user_events DROP PARTITION p202301;

设置云监控告警（腾讯云 MySQL 控制台）：
– 慢查询次数 > 10次/分钟 → 告警
– CPU 使用率 > 80% → 告警
– 连接数使用率 > 90% → 告警
– InnoDB 缓冲池命中率 < 95% → 告警

---

优化效果汇总

优化手段	执行时间	降幅
优化前（全表扫描）	8.2 秒	–
添加联合索引	1.4 秒	↓ 83%
优化 SQL 语句	0.3 秒	↓ 79%
分区表裁剪	0.05 秒	↓ 83%
Redis 缓存命中	0.003 秒	↓ 94%
总体提升	0.003 秒	↓ 99.96%

从 8 秒到 3 毫秒，接近 2700 倍 的性能提升。

---

经验总结

做 MySQL 优化，我有几条实践原则：

1. 先 EXPLAIN，再优化：不要凭感觉，看执行计划说话
2. 索引不是越多越好：索引会拖慢写入，慎重选择，优先联合索引
3. 时间范围必须有边界：业务层强制限制查询时间窗口（30天/90天/1年）
4. 分区表解决数据量问题：索引优化有上限，分区没有
5. 缓存是最后一道防线：统计报表类查询必须加缓存，接受轻微延迟
6. 监控是优化的保障：建立慢查询告警，问题早发现早处理

---

关于作者

长期关注大模型应用落地与云服务器实战，专注技术在企业场景中的落地实践。

个人博客：y1cloud.cn —— 持续更新云计算、AI大模型实战教程，欢迎访问交流。