前言

2024年Q4，我们的一个核心业务系统突然出现了严重的性能问题：原本100ms的查询，变成了5000ms。用户投诉激增，公司高层开始关注这个问题。

经过一周的排查，我们发现：问题不在应用代码，而在数据库设计和查询优化上。

这次事件让我们重新审视整个数据库架构。经过三个月的优化和重构，我们不仅解决了慢查询问题，还建立了一套完整的数据库优化体系。

这篇文章，我想分享我们从"慢查询地狱"中走出来的全过程。

一、问题的根源：一个看似简单的查询

让我从一个具体的例子开始。

问题场景

这是我们的一个"用户订单列表"查询：

sql

SELECT o.order_id, o.user_id, o.order_amount, o.create_time, u.user_name, u.user_level, (SELECT COUNT(*) FROM order_items WHERE order_id = o.order_id) as item_count, (SELECT SUM(price) FROM order_items WHERE order_id = o.order_id) as total_price, (SELECT status FROM order_status WHERE order_id = o.order_id ORDER BY update_time DESC LIMIT 1) as latest_status FROM orders o LEFT JOIN users u ON o.user_id = u.user_id WHERE o.create_time > DATE_SUB(NOW(), INTERVAL 30 DAY) AND o.order_amount > 100 ORDER BY o.create_time DESC LIMIT 100;

看起来很正常，对吧？但这个查询的执行时间是5000ms。

问题分析

我们用EXPLAIN命令分析了这个查询：

+----+-------------+---------------+----------+-------+---------+------+----------+-------+| id | select_type | table | type | rows | key | rows | filtered | Extra |+----+-------------+---------------+----------+-------+---------+------+----------+-------+| 1 | PRIMARY | orders | ALL | 5M | NULL | 5M | 2% | ... || 2 | DEPENDENT | order_items | ALL | 50M | NULL | 50M | 0.01% | ... || 3 | DEPENDENT | order_items | ALL | 50M | NULL | 50M | 0.01% | ... || 4 | DEPENDENT | order_status | ALL | 10M | NULL | 10M | 0.01% | ... |+----+-------------+---------------+----------+-------+---------+------+----------+-------+

问题清晰地暴露了出来：

全表扫描：orders表没有用到索引，扫描了500万条记录；相关子查询：三个子查询都是DEPENDENT类型，意味着对于orders表中的每一条记录，都要扫描整个order_items和order_status表；复杂度爆炸：实际扫描的行数 = 500万 × 50万 × 50万 × 10万 = 天文数字。

为什么会这样？

回顾代码提交历史，我们发现这个查询是在两年前写的。当时数据量很小（只有几万条订单），查询速度没问题。但随着业务增长，数据量增加了100倍，查询性能急剧下降。

这是一个很常见的问题：代码在小数据量下表现良好，但在大数据量下性能崩溃。

二、第一步：索引优化2.1 添加必要的索引

首先，我们为orders表添加了索引：

sql

-- 为create_time和order_amount添加联合索引 ALTER TABLE orders ADD INDEX idx_create_time_amount (create_time, order_amount); -- 为user_id添加索引（用于JOIN） ALTER TABLE orders ADD INDEX idx_user_id (user_id);

添加索引后，查询时间从5000ms降到了2000ms。进步不小，但还不够。

2.2 分析索引使用情况

我们继续用EXPLAIN分析：

+----+-------------+---------------+-------+---------+------+----------+-------+| id | select_type | table | type | rows | key | rows | Extra |+----+-------------+---------------+-------+---------+------+----------+-------+| 1 | PRIMARY | orders | range | 100K | idx | 100K | ... || 2 | DEPENDENT | order_items | ALL | 50M | NULL | 50M | ... |+----+-------------+---------------+-------+---------+------+----------+-------+

好消息：orders表现在用上了索引，扫描的行数从500万降到了10万。

坏消息：子查询仍然在全表扫描。

三、第二步：消除子查询

相关子查询是性能的大敌。我们需要用JOIN或GROUP BY来替代它们。

3.1 重写查询sql

SELECT o.order_id, o.user_id, o.order_amount, o.create_time, u.user_name, u.user_level, COUNT(DISTINCT oi.item_id) as item_count, COALESCE(SUM(oi.price), 0) as total_price, os.status as latest_status FROM orders o LEFT JOIN users u ON o.user_id = u.user_id LEFT JOIN order_items oi ON o.order_id = oi.order_id LEFT JOIN ( -- 子查询：获取最新的订单状态 SELECT order_id, status FROM order_status WHERE (order_id, update_time) IN ( SELECT order_id, MAX(update_time) FROM order_status GROUP BY order_id ) ) os ON o.order_id = os.order_id WHERE o.create_time > DATE_SUB(NOW(), INTERVAL 30 DAY) AND o.order_amount > 100 GROUP BY o.order_id, o.user_id, o.order_amount, o.create_time, u.user_name, u.user_level, os.status ORDER BY o.create_time DESC LIMIT 100;

执行时间：从2000ms降到了1200ms。

3.2 进一步优化

但我们发现，这个查询仍然有问题：LEFT JOIN order_items导致结果集膨胀（一个订单可能有多个商品，所以一条订单记录会被重复）。

我们改用了一个更优雅的方案：

sql

SELECT o.order_id, o.user_id, o.order_amount, o.create_time, u.user_name, u.user_level, oi_stats.item_count, oi_stats.total_price, os.status as latest_status FROM orders o LEFT JOIN users u ON o.user_id = u.user_id LEFT JOIN ( -- 聚合子查询：统计商品数量和总价 SELECT order_id, COUNT(*) as item_count, SUM(price) as total_price FROM order_items GROUP BY order_id ) oi_stats ON o.order_id = oi_stats.order_id LEFT JOIN ( -- 获取最新状态 SELECT order_id, status FROM order_status os1 WHERE update_time = ( SELECT MAX(update_time) FROM order_status os2 WHERE os2.order_id = os1.order_id ) ) os ON o.order_id = os.order_id WHERE o.create_time > DATE_SUB(NOW(), INTERVAL 30 DAY) AND o.order_amount > 100 ORDER BY o.create_time DESC LIMIT 100;

执行时间：从1200ms降到了350ms。

四、第三步：物化视图和缓存

即使优化到350ms，对于高并发场景（比如秒级的流量波动）仍然不够。

4.1 引入缓存

我们在应用层添加了缓存：

python

# 伪代码 def get_user_orders(user_id, page=1): cache_key = f"user_orders:{user_id}:{page}" # 先查缓存 result = redis.get(cache_key) if result: return result # 缓存未命中，查询数据库 result = db.query(""" SELECT ... FROM orders WHERE user_id = ? LIMIT ?, ? """, user_id, (page-1)*100, 100) # 缓存结果（5分钟过期） redis.setex(cache_key, 300, result) return result

这个优化让90%的请求都能在10ms内完成。

4.2 物化视图

对于一些复杂的统计查询（比如"用户订单统计"），我们建立了物化视图：

sql

-- 创建物化视图 CREATE TABLE user_order_stats AS SELECT u.user_id, u.user_name, COUNT(DISTINCT o.order_id) as total_orders, SUM(o.order_amount) as total_amount, AVG(o.order_amount) as avg_amount, MAX(o.create_time) as last_order_time FROM users u LEFT JOIN orders o ON u.user_id = o.user_id WHERE o.create_time > DATE_SUB(NOW(), INTERVAL 90 DAY) GROUP BY u.user_id, u.user_name; -- 添加索引 ALTER TABLE user_order_stats ADD PRIMARY KEY (user_id); -- 定时更新（每小时一次） -- 使用事件调度器或外部cron任务

五、第四步：分库分表

随着数据量的继续增长，单个数据库的性能开始下降。我们决定实施分库分表。

5.1 分库分表策略原架构：┌─────────────────┐│ 单个数据库 ││ (500万订单) │└─────────────────┘新架构：┌──────────────────────────────────────────┐│ 分库分表中间件 │├──────────────────────────────────────────┤│ DB1 │ DB2 │ DB3 │ DB4 │ DB5 ││(100万)│(100万)│(100万)│(100万)│(100万) │└──────────────────────────────────────────┘

分库分表的键：user_id % 5

python

# 分库分表逻辑 def get_db_shard(user_id): shard_id = user_id % 5 # 5个分片 return f"db_{shard_id}" def get_table_shard(order_id): shard_id = order_id % 10 # 10个分片 return f"orders_{shard_id}"

5.2 分库分表的收益

指标

优化前

优化后

提升

单库数据量

500万

100万

-80%

查询耗时

350ms

80ms

-77%

吞吐量

5000 req/s

15000 req/s

+200%

六、第五步：查询优化的最佳实践

经过这一系列优化，我们总结了一套"数据库查询优化的最佳实践"：

6.1 避免全表扫描sql

-- ❌ 坏的做法：全表扫描 SELECT * FROM orders WHERE YEAR(create_time) = 2025; -- ✅ 好的做法：使用范围查询 SELECT * FROM orders WHERE create_time >= '2025-01-01' AND create_time < '2026-01-01';

6.2 避免函数调用sql

-- ❌ 坏的做法：函数调用，无法使用索引 SELECT * FROM orders WHERE YEAR(create_time) = 2025; -- ✅ 好的做法：直接比较，可以使用索引 SELECT * FROM orders WHERE create_time >= '2025-01-01' AND create_time < '2026-01-01';

6.3 避免NULL比较sql

-- ❌ 坏的做法：NULL比较 SELECT * FROM orders WHERE deleted_at = NULL; -- ✅ 好的做法：使用IS NULL SELECT * FROM orders WHERE deleted_at IS NULL;

6.4 避免OR条件sql

-- ❌ 坏的做法：多个OR条件，难以优化 SELECT * FROM orders WHERE status = 'pending' OR status = 'processing' OR status = 'completed'; -- ✅ 好的做法：使用IN SELECT * FROM orders WHERE status IN ('pending', 'processing', 'completed');

6.5 避免SELECT *sql

-- ❌ 坏的做法：查询所有列 SELECT * FROM orders WHERE user_id = 123; -- ✅ 好的做法：只查询需要的列 SELECT order_id, user_id, order_amount, create_time FROM orders WHERE user_id = 123;

6.6 使用EXPLAIN分析查询sql

-- 分析查询执行计划 EXPLAIN SELECT ... FROM orders WHERE ...; -- 查看详细的执行统计 EXPLAIN FORMAT=JSON SELECT ... FROM orders WHERE ...;

七、可观测性：监控和告警

优化查询只是第一步，更重要的是能够及时发现性能问题。

7.1 慢查询日志sql

-- 启用慢查询日志 SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; -- 1秒以上的查询记录 -- 查看慢查询日志 SHOW VARIABLES LIKE 'slow_query%';

7.2 性能监控指标

我们建立了一个监控系统，追踪以下指标：

数据库性能仪表板├─ QPS（每秒查询数）├─ TPS（每秒事务数）├─ 平均查询时间├─ P95/P99查询时间├─ 慢查询数量├─ 连接数├─ 缓冲池使用率└─ 复制延迟（如果有主从复制）7.3 告警规则yaml

告警规则： - 平均查询时间 > 500ms → 警告 - P99查询时间 > 2000ms → 严重告警 - 慢查询数量 > 100/分钟 → 警告 - 连接数 > 80% → 警告

八、跨团队协作中的数据库文档

在优化过程中，我们发现数据库设计文档的清晰性对团队协作至关重要。特别是在跨国团队中，数据库设计文档的多语言化变得很重要。

我们使用了**同言翻译（Transync AI）**等工具来自动生成数据库设计文档的多语言版本，确保全球团队都能理解数据库结构和查询最佳实践。

九、性能优化的成果

经过五个月的优化，我们的数据库性能实现了显著提升：

9.1 查询性能

查询类型

优化前

优化后

提升

订单列表

5000ms

50ms

-99%

用户统计

3000ms

100ms

-97%

订单详情

1500ms

20ms

-99%

搜索查询

8000ms

200ms

-98%

9.2 系统指标

指标

优化前

优化后

提升

QPS

5000

25000

+400%

平均响应时间

800ms

100ms

-88%

P99响应时间

3000ms

300ms

-90%

数据库CPU使用率

85%

35%

-59%

数据库内存使用率

90%

55%

-39%

9.3 业务影响用户体验提升：页面加载时间从3秒降到0.5秒；服务成本下降：由于查询效率提升，所需的数据库实例数从5个降到2个，成本下降60%；可靠性提升：慢查询导致的超时问题消失，系统可用性从99.5%提升到99.95%。十、给其他团队的建议10.1 不要等到问题出现才优化

很多团队都是在"性能崩溃"后才开始优化。更好的做法是在设计阶段就考虑性能。

10.2 建立性能基准

在优化前，先建立一个"性能基准"。这样可以清楚地看到优化的效果。

10.3 监控和告警比优化更重要

优化是一次性的，但监控和告警是持续的。建立完善的监控体系，能够及时发现问题。

10.4 不要过度优化

有时候，简单的缓存就能解决问题，不一定需要分库分表。根据实际情况选择合适的优化方案。

10.5 文档和知识共享

将优化过程中的经验总结成文档，分享给团队。这样可以避免重复的坑。

十一、常见的数据库优化误区误区1：索引越多越好sql

-- ❌ 坏的做法：为每一列都创建索引 ALTER TABLE orders ADD INDEX idx_user_id (user_id); ALTER TABLE orders ADD INDEX idx_status (status); ALTER TABLE orders ADD INDEX idx_amount (order_amount); ALTER TABLE orders ADD INDEX idx_create_time (create_time); -- ... 更多索引 -- 问题： -- 1. 索引占用空间 -- 2. INSERT/UPDATE/DELETE变慢（需要更新索引） -- 3. 优化器需要选择索引，可能选错 -- ✅ 好的做法：根据查询模式创建联合索引 ALTER TABLE orders ADD INDEX idx_composite (create_time, status, order_amount);

误区2：JOIN表越多越好sql

-- ❌ 坏的做法：一个查询JOIN 10个表 SELECT * FROM orders o JOIN users u ON ... JOIN products p ON ... JOIN categories c ON ... JOIN inventory i ON ... JOIN ... -- 更多JOIN

误区3：忽视数据量增长2023年：数据量1万条，查询100ms2024年：数据量100万条，查询1000ms（10倍）2025年：数据量1000万条，查询10000ms（100倍）这不是线性增长，而是指数增长！十二、结语

从"慢查询地狱"到"毫秒级响应"，这是一个系统的、循序渐进的过程。没有银弹，每一步优化都需要深入的分析和细致的实施。

2025年，数据量的增长速度超过了硬件性能的提升速度。作为后端开发者，掌握数据库优化的技能，已经成为了必备能力，而不是"加分项"。

希望这篇文章能帮助你避免我们踩过的坑，以及提供一些可行的优化思路。

如果你也在做数据库优化，欢迎在评论区分享你的经验和遇到的挑战！