mysql集合查询性能为什么慢_mysql集合操作优化思路

union 比 OR 或单表查询慢的根本原因是默认去重+排序，触发 using temporary; Using filesort；应优先用 UNION ALL、只选必要列、确保列类型与 collation 严格一致。

为什么 UNION 比 OR 或单表查询慢得多

根本原因不是“集合操作本身慢”，而是 mysql 在执行 UNION（尤其是 UNION ALL 以外的）时默认会做去重 + 排序，即使你没写 ORDER BY。它会把两个结果集全量拉到临时表，用 Using temporary; Using filesort 处理，I/O 和内存开销陡增。

实操建议：

• 确认是否真需要去重：能用 UNION ALL 就别用 UNION —— 它跳过去重逻辑，性能常提升 2–5 倍
• 避免在 UNION 各分支里都查全字段：select * 会让临时表体积膨胀，只选必要列
• 各分支的对应列类型要严格一致，否则 MySQL 会隐式转换并放弃索引，比如一边是 int、一边是 VARCHAR，就可能触发全表扫描

IN 子查询 vs JOIN vs 临时表：哪种集合匹配更快

当你要“查 A 表中 id 在 B 表结果里的记录”，三种写法性能差异极大，取决于数据量和索引情况。

实操建议：

• IN (SELECT ...) 在 MySQL 5.6+ 有半连接优化，但若子查询返回超 1000 行，容易退化为 N+1 查询；更糟的是子查询无索引时，外层每行都执行一次子查询
• JOIN 通常最稳：只要 ON 字段有索引，就能走 ref 或 eq_ref，执行计划干净
• 大数据量集合匹配（比如几万 ID）：先 CREATE TEMPORARY table 插入 ID 列，加索引，再 JOIN —— 比长 IN (1,2,3,...) 字符串拼接快一个数量级

MySQL 8.0+ 的 CTE 能不能替代 UNION 提升性能

不能一概而论。CTE 是语法糖，不自动优化执行计划；递归 CTE 还可能比临时表慢。

实操建议：

• 非递归 CTE（如 WITH t AS (SELECT ...) SELECT * FROM t UNION SELECT ...）只是把子查询命名了，执行计划和手写子查询几乎一样
• 如果 CTE 被多次引用，MySQL 8.0 默认会物化（即执行一次、缓存结果），这反而比重复执行原查询快；但若只引用一次，物化就是额外开销
• 想确认是否物化，看 EXPLaiN 输出里有没有 materialized 字样；不想物化可加 NOT MATERIALIZED 提示（MySQL 8.0.22+）

WITH user_ids AS (   SELECT id FROM users WHERE status = 1   UNION ALL   SELECT user_id FROM logs WHERE created_at > '2024-01-01' ) SELECT COUNT(*) FROM orders WHERE user_id IN (SELECT id FROM user_ids);

最容易被忽略的集合查询性能陷阱

不是语法或写法，而是字符集和排序规则（collation）不一致。

比如一张表用 utf8mb4_0900_as_cs，另一张用 utf8mb4_general_ci，哪怕只是 UNION 两列字符串，MySQL 就必须逐行做 collation 转换，无法用索引，且临时表变大。这种问题在跨库或历史表迁移后特别常见。

检查方法：SHOW CREATE TABLE 看 COLLATE，再用 SELECT COLLATION(col) 核对字段实际规则。

修复成本低但效果显著：统一 COLLATE utf8mb4_0900_as_cs（推荐），或至少保证参与集合操作的列 collation 完全一致。