MySQL多条件查询与排序性能优化实战：联合索引与UNION ALL技巧

问题分析：单列索引下的排序瓶颈

假设存在一个包含 category（分类表）和 article（文章表）的数据库。其中 article 表约有20万条记录，大小约为1.3GB，且已为关联字段 article_category 建立了单列索引。此时执行以下查询：

SELECT * FROM `article` WHERE article_category=11 ORDER BY article_id DESC LIMIT 5

尽管有索引，该查询仍需约5秒。这是因为虽然索引能快速定位 article_category=11 的行，但结果集并非按 article_id 有序，MySQL必须进行额外的文件排序（Filesort）操作才能满足 ORDER BY 的要求。

解决方案一：构建覆盖排序的联合索引

要消除文件排序，最有效的方法是创建一个联合索引，同时包含 过滤条件字段 和 排序字段。这样，MySQL可以在利用索引进行过滤的同时，直接获取有序的排序结果。

CREATE INDEX idx_u ON article (article_category, article_id);

建立该联合索引后，重新执行上述查询，执行时间骤降至 0.0027秒。索引结构 (article_category, article_id) 使得数据库能够先按分类分组，再在每组内按ID有序读取，完美契合了“WHERE + ORDER BY”的需求。

解决方案二：使用 UNION ALL 优化 IN/OR 查询

当查询条件变为多个分类时（如使用 IN 或 OR），性能再次恶化：

SELECT * FROM article WHERE article_category IN (2,3) ORDER BY article_id DESC LIMIT 5

现象：执行时间高达11.28秒。使用 OR 写法：WHERE article_category=2 OR article_category=3，时间仍为11.07秒。
原因：在基于 (article_category, article_id) 的联合索引下，IN 或 OR 会导致索引失效或效率极低，MySQL可能回表扫描整个表或使用临时表，导致巨大的性能开销。

优化策略：将合并查询拆分为多次简单查询，分别利用索引获取局部最优解，最后合并。推荐使用 UNION ALL 而非 UNION，因为 UNION 需要进行去重和全局排序，耗时更长；而 UNION ALL 仅做简单拼接。

(SELECT * FROM article WHERE article_category=2 ORDER BY article_id DESC LIMIT 5)
UNION ALL
(SELECT * FROM article WHERE article_category=3 ORDER BY article_id DESC LIMIT 5)
ORDER BY article_id DESC
LIMIT 5

结果：执行时间降至 0.0261秒。每个子查询都能独立命中联合索引并快速完成局部排序，外层再进行少量结果的合并与最终排序，效率极高。

注意事项：UNION 与 UNION ALL 及子查询排序

1. UNION vs UNION ALL
UNION 会自动去除重复记录并进行排序，如果数据量大且确认无重复，应优先使用 UNION ALL 以提升性能。

2. UNION 中的 ORDER BY 陷阱
在 MySQL 中，不能直接在 UNION 组合的两个 SELECT 语句末尾添加 ORDER BY 来单独控制各部分顺序。例如，试图对两个子查询分别排序后合并，若语法不当可能导致排序方向相反或无效。

正确用法：将需要独立排序的子查询包裹在括号内，赋予别名，然后再进行 UNION 操作。外层的 ORDER BY 作用于合并后的最终结果。

-- 错误示例：可能导致逻辑混乱或报错
SELECT * FROM T WHERE stime > 'now' ORDER BY stime DESC
UNION ALL
SELECT * FROM T WHERE stime <= 'now' ORDER BY stime ASC;

-- 推荐示例：子查询内定义局部行为（视具体SQL版本和逻辑需求而定），外层控制最终输出
SELECT * FROM (
    SELECT * FROM T WHERE stime > 'now' 
) AS temp1
UNION ALL
SELECT * FROM (
    SELECT * FROM T WHERE stime <= 'now' 
) AS temp2
ORDER BY stime DESC;

总结而言，在处理大规模数据表的查询优化时，应根据查询条件的类型选择合适的索引策略。对于单一维度的排序过滤，联合索引是首选；对于多维度离散值的排序合并，UNION ALL 拆分查询往往是更高效的替代方案。

以上就是MySQL索引用法实例分析与性能优化的详细内容，更多关于MySQL优化与SQL技巧的资料请关注本站其它相关文章！