MySQL 慢查询优化实战

-- 查看当前配置
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- 2秒
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 问题查询
SELECT * FROM orders 
WHERE user_id = 123 
  AND status = 'completed' 
  AND created_at > '2026-01-01';

-- 解决方案：添加复合索引
CREATE INDEX idx_user_status_date ON orders(user_id, status, created_at);

-- 问题：使用函数导致索引失效
SELECT * FROM users 
WHERE DATE(created_at) = '2026-03-19';

-- 解决方案：避免在列上使用函数
SELECT * FROM users 
WHERE created_at >= '2026-03-19' 
  AND created_at < '2026-03-20';

-- 问题：OR 条件导致全表扫描
SELECT * FROM products 
WHERE category_id = 1 
   OR price > 1000;

-- 解决方案：使用 UNION
SELECT * FROM products WHERE category_id = 1
UNION
SELECT * FROM products WHERE price > 1000;

-- 分析索引使用情况
EXPLAIN SELECT * FROM orders WHERE user_id = 123;

-- 创建合适的索引
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_created_at ON orders(created_at);

-- 删除无用索引
DROP INDEX idx_unused ON orders;

-- 优化前：子查询
SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

-- 优化后：JOIN 查询
SELECT u.* FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 1000;

-- 优化前：OFFSET 分页
SELECT * FROM orders 
ORDER BY id 
LIMIT 1000 OFFSET 100000;  -- 性能差

-- 优化后：游标分页
SELECT * FROM orders 
WHERE id > 100000 
ORDER BY id 
LIMIT 1000;

-- 查看当前连接和查询
SHOW PROCESSLIST;

-- 查看锁信息
SHOW ENGINE INNODB STATUS;

-- 查看索引统计
SHOW INDEX FROM orders;

-- 查询缓存命中率
SHOW STATUS LIKE 'Qcache%';

-- InnoDB 缓冲池命中率
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- 表扫描率
SHOW STATUS LIKE 'Handler_read%';