MySQL 查询重复数据优化：避免函数索引失效与临时表方案

原方案的性能瓶颈分析

在处理如 staticcatalogue 表中的 Source 字段时，常见的原始写法如下：

select * from staticcatalogue 
WHERE UPPER(Source) IN (
    SELECT UPPER(Source) FROM staticcatalogue 
    GROUP BY UPPER(Source) having count(UPPER(Source))>1
) ORDER BY upper(Source) DESC;

该语句存在两个核心问题：

1. 索引失效：在查询条件中直接对列使用 UPPER() 函数，使得 MySQL 无法利用 Source 字段上的任何现有索引（包括普通索引或唯一索引），被迫进行全表扫描。
2. 重复计算与排序开销：外层查询和内层子查询均需反复调用 UPPER() 函数进行字符串转换，且在大数据量下 ORDER BY 也会消耗大量 CPU 资源。

若数据量超过十万级，执行时间可能长达数十分钟，甚至导致数据库连接超时或服务器负载过高。

优化方案：基于临时表的索引加速

优化的核心思路是：将“去重聚合”的结果物化为一张小规模的临时表，并在其上建立索引，从而将复杂的多表关联或子查询转化为简单的索引查找。

第1步：准备测试环境

为确保操作安全，建议先在测试库中验证。执行前请备份相关数据，特别是生产环境的数据表结构。

第2步：创建聚合结果临时表

首先创建一个临时表，只存储已确认存在重复的 Source 值。此步骤仅执行一次，将复杂的分组统计转化为简单的数据插入操作。

CREATE TABLE staticcatalogue_tmp 
SELECT UPPER(Source) AS Source 
FROM staticcatalogue 
GROUP BY UPPER(Source) 
having count(UPPER(Source))>1;

完成状态判断：检查 staticcatalogue_tmp 表行数，应等于重复组数，远小于原表总行数。

第3步：为临时表建立索引

由于临时表规模小且查询频繁，为其 Source 字段添加索引可极大提升后续检索速度。

ALTER TABLE staticcatalogue_tmp add INDEX TX_1 (Source);

完成状态判断：执行 SHOW INDEX FROM staticcatalogue_tmp; 确认 TX_1 索引已存在。

第4步：执行最终查询

现在，外层查询只需从大表中提取数据，并通过临时表进行快速匹配。虽然外层仍使用了 UPPER()，但子查询部分已变为指向带索引的临时表，整体执行计划更优。

select s.* from staticcatalogue s 
WHERE UPPER(s.Source) IN (
    SELECT st.Source FROM staticcatalogue_tmp st
) 
ORDER BY UPPER(s.Source) DESC ;

注意：如果追求极致性能，且业务允许，可在原表 Source 上建立一个虚拟列（Virtual Column）并加索引，或使用 BINARY 比较代替 UPPER，但这需结合具体版本和业务需求调整。当前方案兼容性好，实施简单。

简要总结

通过引入临时表隔离“去重逻辑”与“明细查询”，我们成功规避了函数导致的索引失效问题。对于几十万至百万级数据，该方案可将查询响应时间从分钟级降低至秒级。以上就是 MySQL 查询重复数据 SQL 优化方案的详细内容，更多关于 SQL 性能调优的资料请关注本站其它相关文章！