数据库查询优化中的去重（Distinct）操作优化技术

1. 去重操作的基本概念

去重（DISTINCT） 是SQL中用于消除查询结果中重复行的关键字。例如：

SELECT DISTINCT department FROM employees;  

此操作会扫描满足条件的记录，并过滤掉重复值。但如果没有优化，去重可能带来较高的计算开销，尤其是处理大量数据时。

2. 去重操作的执行原理

数据库通常通过以下方式实现去重：

1. 排序去重（Sort-Based Distinct）：

   • 先将所有数据按照去重字段排序，使相同值的行相邻。
   • 然后遍历排序后的数据，跳过重复值（仅保留每组的第一个值）。
   • 缺点：排序可能消耗大量内存和I/O资源。

2. 哈希去重（Hash-Based Distinct）：

   • 构建一个哈希表，将去重字段作为键。
   • 逐行处理数据，若键已存在于哈希表中，则丢弃当前行；否则插入哈希表并保留该行。
   • 缺点：哈希表可能占用大量内存，但通常比排序效率更高（尤其适合大数据集）。

3. 去重操作的常见性能问题

1. 全表扫描：如果未使用索引，去重需扫描全部数据。
2. 中间结果过大：去重前可能需要先处理多表连接或复杂过滤，产生大量中间数据。
3. 不必要的去重：某些场景下，去重可能被误用（例如数据本已唯一）。

4. 优化去重操作的技术

4.1 利用索引避免排序

如果去重字段上有索引（尤其是有序索引如B+树），数据库可能直接通过索引扫描获取有序数据，省去显式排序步骤。
示例：

-- 假设department字段有索引  
SELECT DISTINCT department FROM employees;  

优化器可能选择索引扫描+去重，而非全表扫描+排序。

4.2 将DISTINCT转换为GROUP BY

某些数据库中，DISTINCT可被等价改写为GROUP BY，利用分组聚合优化：

-- 原始查询  
SELECT DISTINCT department, hire_date FROM employees;  

-- 改写后  
SELECT department, hire_date FROM employees GROUP BY department, hire_date;  

数据库可能对GROUP BY采用更高效的哈希聚合或流式聚合。

4.3 避免多层去重

如果子查询中已去重，外层查询应避免重复去重：

-- 不优化写法  
SELECT DISTINCT * FROM (  
    SELECT DISTINCT department FROM employees  
) t;  

-- 优化后  
SELECT DISTINCT department FROM employees;  

4.4 提前过滤数据

通过WHERE条件减少去重前的数据量：

-- 先过滤再去重，减少处理行数  
SELECT DISTINCT department FROM employees WHERE salary > 10000;  

4.5 使用窗口函数替代

某些场景下，可通过窗口函数仅标记首条重复数据，避免全局去重：

-- 仅保留每组重复数据中的第一条  
SELECT * FROM (  
    SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY id) AS rn  
    FROM employees  
) t WHERE rn = 1;  

此法可结合分区字段的索引进一步提升效率。

5. 优化器如何选择去重策略？

数据库优化器会根据以下因素选择去重算法：

1. 数据量：小数据可能用排序，大数据倾向哈希。
2. 内存限制：哈希表受内存大小制约。
3. 索引是否存在：有序索引可避免显式排序。
4. 数据分布：若重复值多，哈希去重可能更高效。

6. 实际案例说明

假设表orders有100万条记录，需去重查询customer_id：

• 无优化：全表扫描 → 排序去重（耗时高）。
• 优化后：
  • 若customer_id有索引，直接索引扫描去重。
  • 若重复值较少，采用哈希去重（内存允许情况下）。
  • 结合WHERE order_date > '2023-01-01'先过滤数据，再去重。

7. 总结

去重操作的优化核心是减少处理的数据量和选择高效算法。关键点包括：

• 优先利用索引避免排序；
• 通过查询重写（如转GROUP BY）提升效率；
• 提前过滤无效数据；
• 根据数据特征选择排序或哈希策略。