二、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);

来源：http://yvyus.cn/