跳到主要内容

查询重写优化

问题​

有哪些常见的 SQL 查询重写技巧？如何优化 JOIN、子查询、UNION 等复杂查询？

答案​

JOIN 优化​

MySQL JOIN 算法​

MySQL 主要使用以下 JOIN 算法：

算法说明版本
Nested Loop Join (NLJ)嵌套循环，逐行匹配所有版本
Block Nested Loop (BNL)缓存驱动表数据，批量匹配5.6+
Hash Join构建哈希表，等值匹配8.0.18+

JOIN 优化建议​

1. 小表驱动大表

-- MySQL 优化器通常会自动选择小表作为驱动表
-- 但有时需要手动指定
-- STRAIGHT_JOIN 强制左表为驱动表
SELECT /*+ STRAIGHT_JOIN */ 
    u.name, o.order_no
FROM users u  -- 小表作为驱动表
JOIN orders o ON u.id = o.user_id;

2. 被驱动表的 JOIN 列必须有索引

-- ❌ 慢：orders.user_id 没有索引，每次循环都全表扫描
SELECT u.name, o.order_no
FROM users u JOIN orders o ON u.id = o.user_id;

-- ✅ 快：给被驱动表的 JOIN 列建索引
ALTER TABLE orders ADD INDEX idx_user_id(user_id);

3. 避免 JOIN 过多表

-- ❌ JOIN 5 张以上表，优化器难以找到最优计划
SELECT * FROM a 
JOIN b ON ... JOIN c ON ... JOIN d ON ... JOIN e ON ...;

-- ✅ 拆分为多次查询，应用层组装
-- 或者使用中间表、冗余字段减少 JOIN
阿里巴巴规范

阿里巴巴 Java 开发手册建议：超过三个表禁止 JOIN，需要 JOIN 的字段必须有索引。

4. 减少 JOIN 返回的数据量

-- ❌ 先 JOIN 再过滤
SELECT * FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.create_time > '2024-01-01';

-- ✅ 先过滤再 JOIN（MySQL 优化器通常会自动优化，但复杂查询需手动）
SELECT * FROM (
    SELECT * FROM orders WHERE create_time > '2024-01-01'
) o JOIN order_items oi ON o.id = oi.order_id;

子查询优化​

子查询转 JOIN​

-- ❌ 相关子查询：外层每一行都执行一次内层查询
SELECT * FROM users u
WHERE u.id IN (
    SELECT user_id FROM orders WHERE amount > 1000
);

-- ✅ 改写为 JOIN
SELECT DISTINCT u.* FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 1000;

-- ✅ 或用 EXISTS（大数据量时通常比 IN 快）
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id AND o.amount > 1000
);

IN vs EXISTS 选择​

场景推荐原因
子查询结果集小IN子查询只执行一次
子查询结果集大，外层表小EXISTS外层循环次数少
子查询表有索引EXISTS可利用索引
-- 子查询结果集小（如只有几百个）→ 用 IN
SELECT * FROM users WHERE id IN (SELECT user_id FROM vip_users);

-- 外层表小，子查询表大 → 用 EXISTS
SELECT * FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);
MySQL 8.0 优化

MySQL 8.0+ 的优化器会自动将很多 IN 子查询转换为半连接（semi-join），性能差异减小。但复杂子查询仍建议手动改写。

避免 SELECT 中的子查询​

-- ❌ SELECT 中的子查询，每行执行一次
SELECT 
    u.name,
    (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count
FROM users u;

-- ✅ 改写为 LEFT JOIN + 聚合
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

UNION 优化​

-- ❌ UNION 会去重（需要排序），除非确实需要去重
SELECT name FROM table_a
UNION
SELECT name FROM table_b;

-- ✅ UNION ALL 不去重，性能更好
SELECT name FROM table_a
UNION ALL
SELECT name FROM table_b;

ORDER BY 优化​

-- ❌ ORDER BY 非索引列，产生 filesort
SELECT * FROM orders WHERE user_id = 100 ORDER BY amount;

-- ✅ 利用联合索引的有序性
-- 索引 (user_id, create_time) 上 ORDER BY create_time 可以直接利用索引
SELECT * FROM orders WHERE user_id = 100 ORDER BY create_time;

-- ❌ 排序方向不一致（MySQL 8.0 以前）
SELECT * FROM orders ORDER BY user_id ASC, create_time DESC;

-- ✅ MySQL 8.0+ 支持降序索引
ALTER TABLE orders ADD INDEX idx_user_time(user_id ASC, create_time DESC);

GROUP BY 优化​

-- ❌ GROUP BY 产生临时表
SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;

-- ✅ 利用索引（如果 user_id 有索引则可以避免临时表）
-- EXPLAIN Extra 中不出现 Using temporary 就说明成功利用了索引

-- ❌ GROUP BY + ORDER BY 不同列
SELECT user_id, SUM(amount) AS total
FROM orders 
GROUP BY user_id 
ORDER BY total DESC;

-- ✅ 如果不需要排序，用 ORDER BY NULL 禁用默认排序（MySQL 5.7）
SELECT user_id, COUNT(*) FROM orders GROUP BY user_id ORDER BY NULL;

COUNT 优化​

-- COUNT(*) vs COUNT(1) vs COUNT(column)
-- COUNT(*)：统计总行数（包含 NULL），MySQL 会自动选择最小索引
-- COUNT(1)：等价于 COUNT(*)
-- COUNT(column)：统计该列非 NULL 的行数

-- ❌ 大表 COUNT(*) 很慢（InnoDB 需要逐行统计）
SELECT COUNT(*) FROM orders;  -- 千万级数据可能要几秒

-- ✅ 方案 1：近似值
SHOW TABLE STATUS LIKE 'orders';  -- Rows 字段（不精确）

-- ✅ 方案 2：缓存计数
-- 用 Redis 维护计数，增删时同步更新

-- ✅ 方案 3：额外计数表
-- 在任务行为时更新统计表

其他优化技巧​

LIMIT 1 优化：

-- ❌ 只需要判断是否存在，却返回所有
SELECT * FROM users WHERE email = 'test@example.com';

-- ✅ 加 LIMIT 1，找到就立即返回
SELECT * FROM users WHERE email = 'test@example.com' LIMIT 1;

避免 SELECT *：

-- ❌ 返回所有列，增加 IO 和网络传输
SELECT * FROM orders WHERE user_id = 100;

-- ✅ 只查需要的列，还可能命中覆盖索引
SELECT id, order_no, amount FROM orders WHERE user_id = 100;

用 WHERE 代替 HAVING：

-- ❌ HAVING 在分组后过滤
SELECT user_id, COUNT(*) AS cnt FROM orders
GROUP BY user_id HAVING user_id > 100;

-- ✅ WHERE 在分组前过滤，减少数据量
SELECT user_id, COUNT(*) AS cnt FROM orders
WHERE user_id > 100
GROUP BY user_id;

常见面试问题​

Q1: IN 和 EXISTS 哪个性能更好？​

答案：

取决于数据分布：

  • 子查询结果集小 → IN 好（子查询只执行一次，用结果集做等值判断）
  • 外层表小，子查询表大且有索引 → EXISTS 好（外层循环次数少，内层走索引）

MySQL 8.0+ 优化器会自动将 IN 子查询转 semi-join，两者差距缩小。但对于复杂子查询仍建议测试对比。

Q2: 如何优化大表的 COUNT(*)？​

答案：

InnoDB 的 COUNT(*) 需要逐行扫描（因为 MVCC 每个事务看到的行数可能不同），大表很慢。优化方案：

  1. 近似值：SHOW TABLE STATUS 中的 Rows（有 5%-10% 误差）
  2. 缓存：Redis 维护计数，增删时更新
  3. 计数表：单独的统计表，定时或事务中更新
  4. 估算：EXPLAIN SELECT COUNT(*) ... 中的 rows 字段

Q3: 为什么建议不超过 3 张表 JOIN？​

答案：

  1. 优化器负担：N 张表 JOIN，优化器需评估 N! 种连接顺序
  2. 锁竞争：多表 JOIN 锁定更多资源
  3. 维护性差：SQL 可读性下降，改动风险大
  4. 替代方案：冗余字段、中间表、应用层组装

但这不是绝对的，如果数据量小且有合适索引，多表 JOIN 也可以很快。

相关链接​