二、SQL语句深度调优
2.1 执行计划全面解读
EXPLAIN FORMAT=JSON
SELECT o.id, o.amount, u.name
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE o.create_time > '2024-01-01'
AND o.amount > 100
ORDER BY o.id DESC
LIMIT 100;
执行计划各列含义
key_len计算示例
CREATE TABLE `test` (
`id` int NOT NULL,
`name` varchar(50) DEFAULT NULL,
`age` int DEFAULT NULL,
`score` decimal(10,2) DEFAULT NULL,
INDEX idx_name_age (name, age)
);
-- key_len计算规则：
-- name: varchar(50) 变长 + 允许NULL → 50*3 + 1 + 2 = 153字节
-- age: int + 允许NULL → 4 + 1 = 5字节
-- 复合索引总key_len = 153 + 5 = 158
EXPLAIN SELECT * FROM test WHERE name = 'Alice' AND age = 25;
-- 输出 key_len = 158，表示用了索引的全部两列
EXPLAIN SELECT * FROM test WHERE name = 'Alice';
-- 输出 key_len = 153，表示只用了索引的第一列
2.2 深分页优化（百页后性能问题）
-- 问题SQL（offset越大越慢）
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;
-- 原理：需要扫描1000020行，丢弃前1000000行
-- 优化方案1：记住上一页的最大ID（游标分页）
SELECT * FROM orders
WHERE id > 999999 -- 上一页的最大ID
ORDER BY id
LIMIT 20;
-- 优化方案2：子查询优化
SELECT * FROM orders
WHERE id >= (
SELECT id FROM orders ORDER BY id LIMIT 1000000, 1
)
ORDER BY id
LIMIT 20;
-- 优化方案3：延迟关联（适合需要查询多列的场景）
SELECT o.*
FROM orders o
INNER JOIN (
SELECT id FROM orders ORDER BY id LIMIT 1000000, 20
) AS tmp ON o.id = tmp.id;
2.3 JOIN优化策略
JOIN算法对比
-- 强制使用指定JOIN顺序
SELECT /*+ JOIN_ORDER(users, orders) */ *
FROM users
INNER JOIN orders ON users.id = orders.user_id
WHERE users.status = 1;
-- 优化小表驱动大表
-- 好的做法（users表小，orders表大）
SELECT * FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.status = 1;
-- 原理：以users为驱动表，循环次数少
-- 避免的做法（如果users是大表）
SELECT * FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE o.status = 1;
2.4 GROUP BY / DISTINCT / ORDER BY 优化
-- 问题查询：统计每个用户的订单总金额，按金额倒序
SELECT user_id, SUM(amount) as total
FROM orders
GROUP BY user_id
ORDER BY total DESC
LIMIT 10;
-- 分析：无法使用索引，需要临时表和文件排序
-- 优化方案1：创建覆盖索引 (user_id, amount)
CREATE INDEX idx_user_amount ON orders(user_id, amount);
-- 优化方案2：使用汇总表（空间换时间）
CREATE TABLE user_order_stats (
user_id bigint PRIMARY KEY,
order_count int DEFAULT 0,
total_amount decimal(12,2) DEFAULT 0,
last_order_time datetime
);
-- 通过触发器或定时任务更新汇总表
INSERT INTO user_order_stats (user_id, order_count, total_amount)
SELECT user_id, COUNT(*), SUM(amount)
FROM orders
GROUP BY user_id
ON DUPLICATE KEY UPDATE
order_count = VALUES(order_count),
total_amount = VALUES(total_amount);
2.5 批量操作优化
-- 错误做法：循环单条插入（1000条耗时约500ms）
for (Order order : orders) {
jdbcTemplate.update("INSERT INTO orders (...) VALUES (?)", ...);
}
-- 正确做法：批量插入（1000条耗时约50ms）
INSERT INTO orders (user_id, order_no, amount, create_time) VALUES
(1, 'ORD001', 100.00, NOW()),
(2, 'ORD002', 200.00, NOW()),
...;
-- MySQL参数：max_allowed_packet=64M, bulk_insert_buffer_size=8M
-- 批量更新使用CASE WHEN
UPDATE orders SET status = CASE id
WHEN 1 THEN 2
WHEN 2 THEN 3
WHEN 3 THEN 4
END
WHERE id IN (1,2,3);
