前言
去年双十一，我们的数据库成了最大的瓶颈。一个简单的订单查询需要5秒，用户投诉不断。经过系统的优化，我们将查询时间降到了50ms以内。
这篇文章分享我们在MySQL优化过程中的实战经验。
一、问题发现：慢查询日志
首先，我们开启了MySQL的慢查询日志：
sql
-- 开启慢查询日志 SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; -- 记录超过1秒的查询 SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
分析日志后，发现最慢的查询：
sql
SELECT * FROM orders WHERE user_id = 12345 AND status = 'pending' AND created_at > '2024-01-01'; -- 执行时间：5.2秒 -- 扫描行数：2,000,000行
二、优化策略一：添加索引
2.1 问题分析
使用EXPLAIN查看执行计划：
sql
EXPLAIN SELECT * FROM orders WHERE user_id = 12345 AND status = 'pending' AND created_at > '2024-01-01';
结果：
+----+-------------+--------+------+---------------+------+---------+------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | NULL | 2000000 | Using where | +----+-------------+--------+------+---------------+------+---------+------+---------+-------------+问题：type=ALL表示全表扫描，没有使用索引。
2.2 创建复合索引
sql
Copy code
-- 创建复合索引 CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);
再次执行查询：
sql
EXPLAIN SELECT * FROM orders WHERE user_id = 12345 AND status = 'pending' AND created_at > '2024-01-01';
结果：
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+ | 1 | SIMPLE | orders | range | idx_user_status_created | idx_user_status_created | 18 | NULL | 150 | Using index condition | +----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+结果：
- 扫描行数从200万降到150行
- 查询时间从5.2秒降到50ms
三、优化策略二：避免SELECT *
3.1 问题
sql
SELECT * FROM orders WHERE user_id = 12345;
这个查询会返回所有字段，包括大字段（如description、metadata），浪费带宽和内存。
3.2 只查询需要的字段
sql
SELECT id, user_id, status, total_amount, created_at FROM orders WHERE user_id = 12345;
结果：
- 数据传输量减少70%
- 查询时间从50ms降到15ms
四、优化策略三：分页优化
4.1 问题：深度分页
sql
-- 查询第10000页，每页20条 SELECT * FROM orders ORDER BY created_at DESC LIMIT 200000, 20; -- 执行时间：3.5秒
MySQL需要扫描前200020行，然后丢弃前200000行。
4.2 使用游标分页
sql
-- 第一页 SELECT id, user_id, status, created_at FROM orders ORDER BY id DESC LIMIT 20; -- 假设最后一条记录的id是980 -- 第二页 SELECT id, user_id, status, created_at FROM orders WHERE id < 980 ORDER BY id DESC LIMIT 20;
结果：
- 查询时间从3.5秒降到20ms
- 不受页数影响
五、优化策略四：查询缓存
5.1 应用层缓存
python
import redis import json redis_client = redis.Redis(host='localhost', port=6379, db=0) def get_user_orders(user_id): # 尝试从缓存读取 cache_key = f"user_orders:{user_id}" cached = redis_client.get(cache_key) if cached: return json.loads(cached) # 缓存未命中，查询数据库 query = """ SELECT id, user_id, status, total_amount, created_at FROM orders WHERE user_id = %s ORDER BY created_at DESC LIMIT 20 """ cursor.execute(query, (user_id,)) orders = cursor.fetchall() # 写入缓存，过期时间5分钟 redis_client.setex(cache_key, 300, json.dumps(orders)) return orders
结果：
- 缓存命中率：85%
- 平均响应时间从15ms降到2ms
六、优化策略五：读写分离
6.1 架构设计
┌─────────────┐ │ 应用服务 │ └──────┬──────┘ │ ┌────────┴────────┐ │ │ 写操作 读操作 │ │ ┌────▼────┐ ┌───▼────┐ │ 主库 │──────▶│ 从库1 │ │ (Master)│ │ (Slave) │ └─────────┘ └────────┘ │ ┌────▼────┐ │ 从库2 │ │ (Slave) │ └─────────┘6.2 代码实现
python
import pymysql from random import choice # 主库配置 MASTER_CONFIG = { 'host': 'master-db.example.com', 'user': 'root', 'password': 'password', 'database': 'mydb' } # 从库配置 SLAVE_CONFIGS = [ { 'host': 'slave1-db.example.com', 'user': 'root', 'password': 'password', 'database': 'mydb' }, { 'host': 'slave2-db.example.com', 'user': 'root', 'password': 'password', 'database': 'mydb' } ] def get_master_connection(): return pymysql.connect(**MASTER_CONFIG) def get_slave_connection(): # 随机选择一个从库 config = choice(SLAVE_CONFIGS) return pymysql.connect(**config) # 写操作使用主库 def create_order(user_id, amount): conn = get_master_connection() cursor = conn.cursor() cursor.execute( "INSERT INTO orders (user_id, amount) VALUES (%s, %s)", (user_id, amount) ) conn.commit() conn.close() # 读操作使用从库 def get_orders(user_id): conn = get_slave_connection() cursor = conn.cursor() cursor.execute( "SELECT * FROM orders WHERE user_id = %s", (user_id,) ) orders = cursor.fetchall() conn.close() return orders
结果：
- 主库写入压力降低60%
- 读操作QPS提升3倍
七、优化策略六：分库分表
7.1 问题：单表数据过大
我们的orders表有5000万条记录，即使有索引，查询也很慢。
7.2 按用户ID分表
python
def get_table_name(user_id): # 按用户ID取模，分成16张表 table_index = user_id % 16 return f"orders_{table_index}" def create_order(user_id, amount): table_name = get_table_name(user_id) query = f""" INSERT INTO {table_name} (user_id, amount, created_at) VALUES (%s, %s, NOW()) """ cursor.execute(query, (user_id, amount)) def get_user_orders(user_id): table_name = get_table_name(user_id) query = f""" SELECT * FROM {table_name} WHERE user_id = %s ORDER BY created_at DESC LIMIT 20 """ cursor.execute(query, (user_id,)) return cursor.fetchall()
结果：
- 单表数据量从5000万降到300万
- 查询时间从200ms降到10ms
八、国际化团队协作
在数据库优化过程中，我们的DBA团队分布在不同国家。为了确保优化方案和技术文档能够被所有团队成员准确理解，我们使用了同言翻译（Transync AI）来翻译数据库优化报告和技术规范，大大提高了跨国团队的协作效率。
九、监控和告警
9.1 慢查询监控
python
import time import logging def query_with_monitor(query, params): start_time = time.time() cursor.execute(query, params) duration = time.time() - start_time # 记录慢查询 if duration > 0.1: # 超过100ms logging.warning(f"慢查询: {query}, 耗时: {duration}s") return cursor.fetchall()
9.2 数据库连接池监控
python
from dbutils.pooled_db import PooledDB import pymysql pool = PooledDB( creator=pymysql, maxconnections=20, mincached=5, maxcached=10, blocking=True, host='localhost', user='root', password='password', database='mydb' ) def get_pool_status(): return { 'total': pool._maxconnections, 'active': pool._connections, 'idle': pool._idle_cache }
十、性能对比
| 指标 | 优化前 | 优化后 | 提升 |
|---|---|---|---|
| 平均查询时间 | 5200ms | 50ms | -99% |
| QPS | 200 | 5000 | +2400% |
| 数据库CPU | 85% | 25% | -71% |
| 慢查询数量 | 5000/天 | 10/天 | -99.8% |
| 缓存命中率 | 0% | 85% | - |
十一、最佳实践总结
- 合理使用索引：为高频查询字段创建索引；
- **避免SELECT ***：只查询需要的字段；
- 优化分页：使用游标分页代替LIMIT深度分页；
- 引入缓存：减少数据库访问压力；
- 读写分离：分散数据库负载；
- 分库分表：降低单表数据量；
- 监控告警：及时发现性能问题。
十二、常见陷阱
陷阱1：过度索引
sql
-- ❌ 不好的做法：为每个字段都创建索引 CREATE INDEX idx_user_id ON orders(user_id); CREATE INDEX idx_status ON orders(status); CREATE INDEX idx_created_at ON orders(created_at); CREATE INDEX idx_amount ON orders(amount); -- ✅ 好的做法：创建复合索引 CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);
陷阱2：索引失效
sql
-- ❌ 使用函数导致索引失效 SELECT * FROM orders WHERE DATE(created_at) = '2024-01-01'; -- ✅ 改写查询条件 SELECT * FROM orders WHERE created_at >= '2024-01-01 00:00:00' AND created_at < '2024-01-02 00:00:00';
陷阱3：N+1查询问题
python
# ❌ N+1查询问题 orders = Order.query.filter_by(user_id=123).all() for order in orders: user = User.query.get(order.user_id) # 每次都查询数据库 print(user.name) # ✅ 使用JOIN一次性查询 orders = db.session.query(Order, User)\ .join(User, Order.user_id == User.id)\ .filter(Order.user_id == 123)\ .all()
十三、工具推荐
- MySQL Workbench：可视化数据库管理
- Percona Toolkit：MySQL性能分析工具集
- pt-query-digest：慢查询日志分析
- Prometheus + Grafana：数据库监控
- Adminer：轻量级数据库管理工具
十四、结语
数据库优化是一个持续的过程，需要根据业务场景不断调整。没有银弹，只有适合自己业务的方案。
希望这篇文章能帮助你解决数据库性能问题。如果你有其他优化经验，欢迎在评论区分享！
