一、慢查询为什么让人头疼
你有没有遇到过这种情况？打开一个网页，转圈转了十几秒才显示内容；提交一个表单，卡在那里半天没反应。很多时候，这些问题的罪魁祸首就是数据库慢查询。作为一个常年和MySQL打交道的DBA，我见过太多因为慢查询导致的性能问题。
慢查询就像高速公路上的堵车点，不仅影响当前请求，还会拖累整个系统的吞吐量。更可怕的是，很多慢查询刚开始并不明显，随着数据量增长才逐渐暴露，等到发现时往往已经造成了严重影响。
二、如何识别慢查询
MySQL很贴心地提供了慢查询日志功能，这是我们的第一道防线。让我们看看怎么配置：
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
-- 设置慢查询阈值(单位：秒)，这里设为1秒
SET GLOBAL long_query_time = 1;
-- 记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = 'ON';
-- 指定慢查询日志文件位置
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
配置好后，MySQL会自动记录执行时间超过1秒的查询。但光记录还不够，我们需要分析这些日志。MySQL自带的mysqldumpslow工具就很实用：
# 分析慢查询日志，按出现次数排序
mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log
# 按平均查询时间排序
mysqldumpslow -s at -t 10 /var/log/mysql/mysql-slow.log
三、常见的慢查询优化手段
3.1 索引优化
索引是解决慢查询最有效的手段之一，但要用对地方。我见过太多乱加索引反而拖慢性能的案例。来看一个典型例子：
-- 假设有个用户表，经常需要按用户名和注册时间查询
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL,
`email` varchar(100) NOT NULL,
`register_time` datetime NOT NULL,
`status` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_username` (`username`)
) ENGINE=InnoDB;
-- 问题查询：查找特定时间段注册的活跃用户
SELECT * FROM users
WHERE register_time BETWEEN '2023-01-01' AND '2023-12-31'
AND status = 1;
这个查询会很慢，因为它需要全表扫描。优化方案是添加复合索引：
-- 添加复合索引
ALTER TABLE users ADD INDEX idx_register_status (register_time, status);
-- 更好的做法是包含查询的所有字段(覆盖索引)
ALTER TABLE users ADD INDEX idx_cover (register_time, status, username, email);
3.2 SQL语句优化
很多慢查询其实是因为SQL写得不够好。来看几个常见问题：
-- 反例1：使用SELECT *
SELECT * FROM orders WHERE user_id = 100;
-- 正例：只查询需要的列
SELECT order_id, order_amount, create_time FROM orders WHERE user_id = 100;
-- 反例2：在WHERE子句中对字段使用函数
SELECT * FROM users WHERE DATE(register_time) = '2023-01-01';
-- 正例：使用范围查询
SELECT * FROM users
WHERE register_time >= '2023-01-01'
AND register_time < '2023-01-02';
3.3 分页查询优化
分页查询是另一个性能杀手，特别是大偏移量的分页：
-- 反例：偏移量很大时性能很差
SELECT * FROM products ORDER BY create_time DESC LIMIT 10000, 20;
-- 正例1：使用子查询优化
SELECT * FROM products
WHERE id >= (SELECT id FROM products ORDER BY create_time DESC LIMIT 10000, 1)
ORDER BY create_time DESC LIMIT 20;
-- 正例2：使用游标分页(适合无限滚动)
SELECT * FROM products
WHERE create_time < '2023-06-01 00:00:00'
ORDER BY create_time DESC LIMIT 20;
四、高级优化技巧
4.1 使用EXPLAIN分析执行计划
EXPLAIN是我们的秘密武器，它能告诉我们MySQL如何执行查询：
EXPLAIN SELECT u.username, o.order_amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 1 AND o.create_time > '2023-01-01';
关键要看这些列：
- type：最好看到const、eq_ref、ref，避免看到ALL(全表扫描)
- key：实际使用的索引
- rows：预估需要检查的行数
- Extra：注意Using filesort和Using temporary，它们通常意味着性能问题
4.2 表结构设计优化
有时候慢查询的根源在于糟糕的表设计。来看一个常见的反模式：
-- 反例：把所有数据塞进一个大表
CREATE TABLE `product_data` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`product_id` int(11) NOT NULL,
`name` varchar(100) NOT NULL,
`description` text,
`price` decimal(10,2) NOT NULL,
`category_id` int(11) NOT NULL,
`category_name` varchar(50) NOT NULL,
`brand_id` int(11) NOT NULL,
`brand_name` varchar(50) NOT NULL,
`attribute1` varchar(50) DEFAULT NULL,
`attribute2` varchar(50) DEFAULT NULL,
-- ... 还有20多个各种属性字段
PRIMARY KEY (`id`),
KEY `idx_product` (`product_id`)
) ENGINE=InnoDB;
更好的设计是规范化表结构：
-- 正例：规范化设计
CREATE TABLE `products` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`description` text,
`price` decimal(10,2) NOT NULL,
`category_id` int(11) NOT NULL,
`brand_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE `product_attributes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`product_id` int(11) NOT NULL,
`attribute_name` varchar(50) NOT NULL,
`attribute_value` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_product_attribute` (`product_id`,`attribute_name`)
) ENGINE=InnoDB;
4.3 使用缓存减轻数据库压力
对于频繁访问但很少变化的数据，使用缓存可以显著减轻数据库压力。虽然Redis是常见选择，但MySQL的查询缓存(注意：MySQL 8.0已移除)和应用程序缓存也很重要。
-- 示例：使用应用程序缓存
-- 伪代码
function getProductDetails(productId) {
// 先尝试从缓存获取
let product = cache.get('product_' + productId);
if (!product) {
// 缓存未命中，查询数据库
product = db.query('SELECT * FROM products WHERE id = ?', [productId]);
// 存入缓存，设置过期时间
cache.set('product_' + productId, product, { ttl: 3600 });
}
return product;
}
五、实战案例分析
让我们看一个真实的优化案例。假设有一个电商系统，用户抱怨"我的订单"页面加载很慢。原始查询是这样的：
SELECT o.*, p.name as product_name, p.price as product_price
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.user_id = 12345
ORDER BY o.create_time DESC
LIMIT 0, 10;
通过EXPLAIN分析发现：
- 没有使用到user_id的索引
- 对create_time排序导致了filesort
- 查询了不必要的列
优化后的方案：
-- 1. 添加索引
ALTER TABLE orders ADD INDEX idx_user_create (user_id, create_time);
-- 2. 优化查询语句
SELECT
o.id, o.order_no, o.total_amount, o.create_time, o.status,
oi.product_id, oi.quantity, oi.price,
p.name as product_name
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.user_id = 12345
ORDER BY o.create_time DESC
LIMIT 10;
优化后，查询时间从原来的2.3秒降到了0.05秒，提升了46倍！
六、预防胜于治疗
与其等问题出现后再优化，不如提前预防。以下是一些预防措施：
- 新上线的SQL必须经过EXPLAIN审核
- 建立数据库监控，关注慢查询数量变化
- 定期进行数据库健康检查
- 对大表操作(如ALTER TABLE)要在低峰期进行
- 考虑使用pt-query-digest等工具定期分析慢查询
记住，数据库优化是一个持续的过程，不是一劳永逸的工作。随着业务发展，数据量增长，需要不断调整优化策略。
评论