如何使用DISTINCT优化现有SQL查询？

嘿，我来帮你梳理下怎么用DISTINCT优化这条查询，顺便给你几个额外的性能小建议。

首先，你的原查询因为两次左连接，很可能会产生重复的routeNumber结果——毕竟一条交易记录可能关联到多条route_map或station数据。直接用DISTINCT就能快速过滤掉这些重复值，调整后的查询如下：

SELECT DISTINCT rm.`routeNumber` 
FROM transactions tr 
LEFT OUTER JOIN route_map rm 
  ON rm.`idStation` = tr.`idStation` 
  AND rm.`routeNumber` = CONCAT( 
    tr.`routeNumber`, 
    IF( tr.`letterOfRoute` REGEXP '[0-9]+' OR tr.`letterOfRoute` IS NULL, '', tr.`letterOfRoute` ) 
  ) 
LEFT OUTER JOIN station s 
  ON s.idStation = rm.`idStation` 
WHERE tr.`operationType` = 2 
  AND tr.`routeNumber` != 0 
  AND tr.idStation != 0 
  AND tr.`idFiles` IN (
    SELECT fin.`idFiles` FROM files_in fin WHERE fin.`id...` -- 补全你的子查询条件
  )

为什么这么改？ #

DISTINCT会对最终结果集的routeNumber做去重处理，刚好解决左连接带来的重复数据问题。不过这里还有个替代方案，用GROUP BY有时候性能会更优（取决于数据库引擎和索引情况），写法是把DISTINCT去掉，在查询末尾加GROUP BY rm.routeNumber：

SELECT rm.`routeNumber` 
FROM transactions tr 
LEFT OUTER JOIN route_map rm 
  ON rm.`idStation` = tr.`idStation` 
  AND rm.`routeNumber` = CONCAT( 
    tr.`routeNumber`, 
    IF( tr.`letterOfRoute` REGEXP '[0-9]+' OR tr.`letterOfRoute` IS NULL, '', tr.`letterOfRoute` ) 
  ) 
LEFT OUTER JOIN station s 
  ON s.idStation = rm.`idStation` 
WHERE tr.`operationType` = 2 
  AND tr.`routeNumber` != 0 
  AND tr.idStation != 0 
  AND tr.`idFiles` IN (
    SELECT fin.`idFiles` FROM files_in fin WHERE fin.`id...`
  )
GROUP BY rm.`routeNumber`

如果rm.routeNumber有索引，GROUP BY会利用索引来优化排序和去重的过程，比DISTINCT可能更高效。

额外的性能优化点 #

1. 清理多余的连接：你只需要rm.routeNumber，station表的左连接完全没用到它的字段，直接删掉这个连接能减少数据处理量，提升速度。
2. 优化索引：给transactions表建个联合索引(operationType, routeNumber, idStation, idFiles)，这样WHERE子句和JOIN操作能快速定位数据；同时给files_in.idFiles加索引，提升子查询的效率。
3. 替换IN子查询为JOIN：有些数据库对IN子查询的优化不如JOIN，你可以把子查询改成JOIN形式试试：

SELECT DISTINCT rm.`routeNumber` 
FROM transactions tr 
JOIN files_in fin ON tr.`idFiles` = fin.`idFiles`
LEFT OUTER JOIN route_map rm 
  ON rm.`idStation` = tr.`idStation` 
  AND rm.`routeNumber` = CONCAT( 
    tr.`routeNumber`, 
    IF( tr.`letterOfRoute` REGEXP '[0-9]+' OR tr.`letterOfRoute` IS NULL, '', tr.`letterOfRoute` ) 
  ) 
WHERE tr.`operationType` = 2 
  AND tr.`routeNumber` != 0 
  AND tr.idStation != 0 
  -- 这里添加fin的过滤条件

内容的提问来源于stack exchange，提问作者Ksenia