【MySQL深入详解】第22篇：JOIN与排序优化——让关联查询飞起来

开篇引入

JOIN和排序是查询优化的两大难点。JOIN写错了，轻则性能差，重则OOM崩溃；排序没做好，百万数据排序能把数据库卡死。

《高性能MySQL》第8章讲了查询优化的高级技巧，这篇文章帮你把JOIN和排序优化彻底搞懂。

JOIN的原理

嵌套循环JOIN（NLJ）

-- 最基本的JOIN算法
SELECT * FROM orders o JOIN users u ON o.user_id = u.id;

-- 执行过程（伪代码）
for each order in orders:           -- 外循环（驱动表）
    for each user in users:        -- 内循环（被驱动表）
        if order.user_id == user.id:
            return join(order, user)

问题：驱动表每行都要扫描被驱动表 → O(n×m)复杂度

使用索引的JOIN

-- 如果被驱动表有索引
CREATE INDEX idx_user_id ON users(id);

-- 执行过程变为：
for each order in orders:
    user = index_lookup(users, id=order.user_id)
    if user exists:
        return join(order, user)

优化：索引查找O(log n)，总复杂度O(n×log m)

MRR（Multi-Range Read）

-- MySQL 8.0+的优化
-- 先收集所有需要查找的主键
-- 排序后批量查询（顺序读，减少随机IO）

EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
-- Extra: Using MRR

驱动表的选择

小表驱动大表

-- orders: 10万行
-- users: 1000行
-- products: 100行

-- 好的JOIN顺序：products → users → orders
SELECT o.*, u.name, p.name
FROM products p
JOIN users u ON p.sales_id = u.id
JOIN orders o ON u.id = o.user_id;

-- 优化器通常会自动选择小表作为驱动表
-- 但不是绝对的，取决于WHERE条件

手动指定驱动表

-- STRAIGHT_JOIN强制按顺序JOIN
SELECT * FROM orders STRAIGHT_JOIN users ON orders.user_id = users.id;

-- 告诉MySQL：orders是驱动表

JOIN的常见问题

问题1：ON条件缺失

-- 错误：没有ON条件
SELECT o.*, u.name FROM orders o, users u
WHERE o.user_id = u.id;  -- 这是隐式JOIN，已废弃

-- 正确：显式JOIN
SELECT o.*, u.name FROM orders o JOIN users u ON o.user_id = u.id;

问题2：外键没有索引

-- orders.user_id 外键关联 users.id
-- 如果users.id没有索引

SELECT o.*, u.name FROM orders o JOIN users u ON o.user_id = u.id;
-- 每次匹配都要全表扫描users

-- 解决：确保主键有索引（通常都有）
-- 或者在外键列建索引
CREATE INDEX idx_order_user ON orders(user_id);

问题3：ON条件放WHERE

-- 错误：ON条件放在WHERE
SELECT o.*, u.name
FROM orders o
JOIN users u
WHERE o.user_id = u.id AND u.status = 1;
-- 这会导致笛卡尔积（先JOIN再过滤）

-- 正确：ON中放JOIN条件，WHERE中放过滤条件
SELECT o.*, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.status = 1;

问题4：大表JOIN

-- 大表JOIN容易OOM
-- 100万行 × 100万行 = 爆炸

-- 解决1：分解查询
-- 查orders，再查users，再应用层JOIN
SELECT * FROM orders WHERE status = 'paid';
-- 应用层根据user_id查users

-- 解决2：分批处理
SELECT * FROM orders WHERE user_id BETWEEN 1 AND 10000;
-- 处理完再处理下一批

-- 解决3：用临时表
CREATE TEMPORARY TABLE temp_orders AS 
SELECT * FROM orders WHERE status = 'paid';
-- 然后JOIN临时表

JOIN类型的选择

INNER JOIN vs LEFT JOIN

-- INNER JOIN: 只保留匹配的行
SELECT o.*, u.name
FROM orders o
INNER JOIN users u ON o.user_id = u.id;
-- 只返回有用户的订单

-- LEFT JOIN: 保留左表所有行
SELECT o.*, u.name
FROM orders o
LEFT JOIN users u ON o.user_id = u.id;
-- 订单即使没有用户也会返回（user字段为NULL）

LEFT JOIN的优化

-- LEFT JOIN中右表加条件要谨慎
SELECT o.*, u.name
FROM orders o
LEFT JOIN users u ON o.user_id = u.id AND u.status = 1;
-- 条件写在AND中，全匹配
-- 如果想排除不匹配的行，应该用INNER JOIN

-- 如果需要过滤右表，用子查询
SELECT o.*, u.name
FROM orders o
LEFT JOIN (
    SELECT * FROM users WHERE status = 1
) u ON o.user_id = u.id;

排序优化

filesort算法

MySQL有两种排序算法：

1. 两次传输排序（老版本）

1. 读取所有需要排序的行到sort buffer
2. 按排序列排序
3. 返回排序结果

2. 单次传输排序（新版本）

1. 读取所有列（包括排序列）到sort buffer
2. 按排序列排序
3. 直接返回结果

EXPLAIN看排序

-- Using filesort = 需要额外排序步骤
EXPLAIN SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;
-- Extra: Using filesort

-- Using index = 利用索引排序（最快）
CREATE INDEX idx_created ON orders(created_at DESC);
EXPLAIN SELECT created_at FROM orders ORDER BY created_at DESC LIMIT 10;
-- Extra: Using index

覆盖索引解决排序

-- 查询：按created_at排序，返回id, user_id, status, created_at
SELECT id, user_id, status, created_at 
FROM orders 
ORDER BY created_at DESC 
LIMIT 10;

-- 创建覆盖索引
CREATE INDEX idx_covering ON orders(created_at DESC, id, user_id, status);

-- EXPLAIN验证
EXPLAIN SELECT id, user_id, status, created_at 
FROM orders 
ORDER BY created_at DESC 
LIMIT 10;
-- Extra: Using index (覆盖索引)

分页优化

大偏移量分页的问题

-- 经典慢查询
SELECT * FROM orders ORDER BY id DESC LIMIT 100000, 10;
-- 问题：MySQL要跳过前10万行

-- 分析：
-- 扫描id索引到100010行
-- 返回10行
-- 前10万行白扫了

解决方案1：游标分页

-- 记录上次查询的最大ID
-- 第一页
SELECT * FROM orders ORDER BY id DESC LIMIT 10;
-- 返回最后ID: 99990

-- 第二页（使用上次最大ID）
SELECT * FROM orders 
WHERE id < 99990 
ORDER BY id DESC 
LIMIT 10;

-- 利用索引排序，直接定位

解决方案2：延迟JOIN

-- 只查ID，再JOIN获取完整数据
SELECT o.*
FROM orders o
INNER JOIN (
    SELECT id FROM orders 
    ORDER BY id DESC 
    LIMIT 100000, 10
) t ON o.id = t.id;

解决方案3：记录总数

-- 前端分页不需要精确总数
-- 显示总页数：使用估算或上限

SELECT COUNT(*) FROM orders WHERE status = 'paid';
-- 慢

-- 改用估算
SELECT TABLE_ROWS FROM information_schema.tables 
WHERE table_name = 'orders' AND table_schema = 'db_name';

子查询优化

相关子查询 vs 非相关子查询

-- 非相关子查询：子查询只执行一次
SELECT * FROM orders 
WHERE user_id IN (SELECT id FROM users WHERE status = 1);

-- 相关子查询：子查询每行都执行一次
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id AND u.status = 1);

改写为JOIN

-- 子查询改写为JOIN（通常更快）
SELECT DISTINCT u.*
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 1;

-- IN改写为EXISTS/INNER JOIN
SELECT * FROM orders 
WHERE user_id IN (SELECT id FROM users WHERE status = 1);

-- 等价于
SELECT DISTINCT o.*
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.status = 1;

物化子查询（MySQL 8.0+）

-- MySQL 8.0会自动物化子查询
EXPLAIN SELECT * FROM orders 
WHERE user_id IN (SELECT id FROM users WHERE status = 1);

-- 观察Extra列：
-- Using join buffer (materialize): 使用了物化
-- Single-row subquery: 子查询返回一行

实战案例

案例：订单+用户+商品报表

-- 原始查询
SELECT o.*, u.name, u.email, p.name as product_name
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id
WHERE o.status = 'paid'
  AND o.created_at >= '2024-01-01'
ORDER BY o.created_at DESC
LIMIT 100;

-- 分析EXPLAIN
EXPLAIN SELECT o.*, u.name, u.email, p.name as product_name
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id
WHERE o.status = 'paid'
  AND o.created_at >= '2024-01-01'
ORDER BY o.created_at DESC
LIMIT 100;

-- 优化1：添加合适索引
CREATE INDEX idx_status_created ON orders(status, created_at DESC);
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_product_id ON orders(product_id);

-- 优化2：覆盖索引减少回表
CREATE INDEX idx_covering ON orders(status, created_at DESC, id, user_id, product_id);

优化建议清单

-- JOIN优化：
-- 1. 确保JOIN列有索引
-- 2. 小表作为驱动表
-- 3. 避免大表JOIN，考虑分解
-- 4. 用EXPLAIN验证JOIN顺序

-- 排序优化：
-- 1. 利用索引排序，避免filesort
-- 2. 覆盖索引可以同时排序和返回数据
-- 3. 减少排序列，降低排序开销

-- 分页优化：
-- 1. 游标分页代替OFFSET
-- 2. 延迟JOIN先查ID
-- 3. 不需要精确总数时用估算

小结

1. 小表驱动大表：减少扫描次数
2. JOIN列必须有索引：否则O(n×m)复杂度
3. 用EXPLAIN验证JOIN顺序：确保优化器选对
4. 覆盖索引解决排序：减少回表和filesort
5. 游标分页代替OFFSET：避免扫描已跳过的行
6. 子查询可改写为JOIN：通常性能更好
7. 大表JOIN要谨慎：考虑分解或临时表
8. ON和WHERE要分清：ON是JOIN条件，WHERE是过滤条件

JOIN和排序是SQL优化的硬骨头，理解原理才能举一反三。

---

延伸阅读

• 《高性能MySQL》第8章 查询性能优化
• MySQL 8.0 Reference Manual: Optimizing Queries
• MySQL 8.0 Reference Manual: ORDER BY Optimization
• MySQL 8.0 Reference Manual: JOIN Optimization