三种SQL查询（嵌套/WHERE/JOIN ON）的性能对比与选型测试方法

现有三个返回结果一致的SQL查询，分别为旧式WHERE连接、JOIN ON连接、IN子查询嵌套写法，请问它们是否存在性能差异？

数据表结构 #

• tests表：包含unique_id、allele、mfi_valeur字段，unique_id为主键，同时是关联id_tests表unique_id的外键；
• id_tests表：包含unique_id、type_test字段，unique_id为主键。

三个SQL查询语句 #

旧式WHERE连接写法 #

SELECT i.type_test
  FROM [DATA_CQE].[dbo].[tests] as t, [DATA_CQE].[dbo].[id_tests] as i
  WHERE t.unique_id = i.unique_id
  AND t.allele = 'A*01:01'
  AND t.mfi_valeur > 10000;

JOIN ON连接写法 #

SELECT i.type_test
  FROM [DATA_CQE].[dbo].[tests] as t
  JOIN [DATA_CQE].[dbo].[id_tests] as i
  ON t.unique_id = i.unique_id
  WHERE t.allele = 'A*01:01'
  AND t.mfi_valeur > 10000;

IN子查询嵌套写法 #

SELECT type_test 
FROM [DATA_CQE].[dbo].[id_tests]
WHERE unique_id IN (
  SELECT unique_id
  FROM [DATA_CQE].[dbo].[tests]
  WHERE allele = 'A*01:01'
  AND mfi_valeur > 10000
);

当前测试数据库已截断数据（仅数万行，原数据数千万行），查询速度极快，现有执行计划但无法完全解读。请问如何确定这类查询的最优写法，以及如何可靠测试不同查询间的差异？

性能差异分析 #

在现代SQL优化器（比如SQL Server的查询优化器）中，这三种写法大部分场景下不会有性能差异：

• 旧式WHERE连接和JOIN ON连接本质逻辑等价，优化器会将它们解析为完全相同的执行计划，二者仅语法风格不同，JOIN ON写法更符合ANSI标准，可读性更强。
• IN子查询写法，当子查询返回的是主键/唯一键（此处unique_id是tests表主键）时，优化器通常会自动将其重写为JOIN逻辑，生成与前两种写法一致的执行计划。

仅在极端场景下可能出现差异：比如子查询返回大量重复数据、或表的统计信息过时导致优化器误判执行路径，此时IN子查询可能生成效率较低的计划。

确定最优写法的方法 #

1. 分析执行计划：
   • 重点关注执行计划中的运算符类型（哈希匹配、嵌套循环、合并连接等）、逻辑读取次数、CPU时间、执行时间指标。若三个查询的执行计划完全一致，说明性能无差异。
   • 逻辑读是衡量IO开销的核心指标，大数据量下IO通常是性能瓶颈的主要来源。
2. 更新统计信息：
   • 过时的统计信息会导致优化器生成错误计划，执行以下语句确保统计信息最新：

     UPDATE STATISTICS [DATA_CQE].[dbo].[tests];
     UPDATE STATISTICS [DATA_CQE].[dbo].[id_tests];
     

3. 优先考虑可读性：
   • 若性能无差异，优先选择JOIN ON写法，其逻辑清晰，符合现代SQL编码规范，后续维护成本更低；IN子查询写法在表达"筛选存在于某集合的数据"时逻辑更直观，但复杂场景下可读性不如JOIN。

可靠测试性能差异的方法 #

1. 模拟真实数据规模：
   • 测试环境仅数万行数据无法反映数千万行的真实性能，需生成与生产环境数据量、数据分布（如allele取值占比、mfi_valeur数值范围）一致的测试数据，可借助bcp工具或第三方数据生成工具实现。
2. 清空缓存后测试：
   • 每次测试前清空缓冲区，避免缓存干扰结果（仅在测试环境执行）：

     DBCC DROPCLEANBUFFERS;
     DBCC FREEPROCCACHE;
     

3. 多次执行取平均值：
   • 单次执行时间受系统负载影响，建议每个查询执行3-5次，取平均执行时间、平均逻辑读作为对比指标。
4. 监控资源消耗：
   • 测试时监控CPU、磁盘IO、内存占用，尤其关注磁盘IO。可通过动态视图查看查询资源消耗：

     SELECT 
       total_logical_reads,
       total_worker_time,
       total_elapsed_time
     FROM sys.dm_exec_query_stats
     WHERE sql_handle = (SELECT sql_handle FROM sys.dm_exec_requests WHERE session_id = @@SPID);
     

内容的提问来源于stack exchange，提问作者FluidMechanics Potential Flows