MySQL性能优化：从慢查询到毫秒级响应

前言

去年双十一，我们的数据库成了最大的瓶颈。一个简单的订单查询需要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 }

十、性能对比

十一、最佳实践总结

1. 合理使用索引：为高频查询字段创建索引；
2. **避免SELECT ***：只查询需要的字段；
3. 优化分页：使用游标分页代替LIMIT深度分页；
4. 引入缓存：减少数据库访问压力；
5. 读写分离：分散数据库负载；
6. 分库分表：降低单表数据量；
7. 监控告警：及时发现性能问题。

十二、常见陷阱

陷阱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：轻量级数据库管理工具

十四、结语

数据库优化是一个持续的过程，需要根据业务场景不断调整。没有银弹，只有适合自己业务的方案。

希望这篇文章能帮助你解决数据库性能问题。如果你有其他优化经验，欢迎在评论区分享！