sql-server - 更改查询的比较值时，执行时间非常令人绝望

Question

我遇到一个令我困惑的查询执行时间问题。我知道一些方法来解决问题并获得更好且可接受的执行时间，但仍然不知道为什么会出现问题。

示例表

我们有两个表，通过外键关联。

表1

| Id | IdTable2 |
|:--:|:--------:|
|  1 |     4    |
|  2 |     7    |
|  3 |     8    |
|  4 |     6    |
|  5 |     4    |
|  6 |     1    |
|  7 |     1    |
|  8 |     6    |
|  9 |     7    |
| 10 |     1    |

表2

| Id | ValueField |
|:--:|:----------:|
|  1 |      0     |
|  2 |      0     |
|  3 |      0     |
|  4 |      1     |
|  5 |      0     |
|  6 |      1     |
|  7 |      0     |

查询

SELECT * FROM Table1 WHERE IdTable2 IN (SELECT Id FROM Table2 WHERE ValueField = ?);

在哪里？可以是0或1

真实数据计数

以上表格只是一个简化示例，但该表格的实际行数如下:

• 表 1:60420 行

• 表 2:62 行

• 带有 ValueField 0 的表 2:51 行

• 带有 ValueField 1 的表 2:11 行

• Table1、IdTable2 和 ValueField 0:599 行

• Table1、IdTable2 和 ValueField 1:59821 行

问题

SELECT * FROM Table1 WHERE IdTable2 IN (SELECT Id FROM Table2 WHERE ValueField = 0);
-- Execution time LOW/INSTANT
SELECT * FROM Table1 WHERE IdTable2 IN (SELECT Id FROM Table2 WHERE ValueField = 1);
-- Execution time HIGH

嗯，首先我认为子查询是一个难题，但是如果子查询是问题所在，那么不同的值就不会在如此绝望的时间内执行，所以我猜想可能是检索的数据量有问题，所以我试试这个:

SELECT * FROM Table1 WHERE IdTable2 IN (1,2,3,5,7); -- Equivalent of ValueField 0
-- Execution time LOW/INSTANT
SELECT * FROM Table1 WHERE IdTable2 IN (4,6); -- Equivalent of ValueField 1
-- Execution time LOW/INSTANT

好吧...检索到的数据也不是这样，让我们​​尝试其他方法:

SELECT * FROM Table1 WHERE IdTable2 IN (SELECT Id FROM Table2 WHERE ValueField = 0);
-- Execution time LOW/INSTANT
SELECT * FROM Table1 WHERE IdTable2 NOT IN (SELECT Id FROM Table2 WHERE ValueField = 0);
-- Execution time LOW/INSTANT

如果我反转会发生什么？

SELECT * FROM Table1 WHERE IdTable2 NOT IN (SELECT Id FROM Table2 WHERE ValueField = 1);
-- Execution time LOW/INSTANT
SELECT * FROM Table1 WHERE IdTable2 IN (SELECT Id FROM Table2 WHERE ValueField = 0);
-- Execution time LOW/INSTANT

嗯......这几乎告诉我问题不在于子查询，也不在于数据，而是为什么与 ValueField = 1 进行比较并使用 IN 导致了问题，并且没有任何替代方案可以复制高执行时间？

执行计划

对于 SQL IN ValueField 1:

SELECT * FROM Incidencias WHERE EstadoWorkflow in (SELECT IdEstadoWorkflow FROM EstadosWorkflows WHERE Final = 1);

http://s000.tinyupload.com/index.php?file_id=19036217708532467879

对于 SQL IN ValueField 0:

SELECT * FROM Incidencias WHERE EstadoWorkflow in (SELECT IdEstadoWorkflow FROM EstadosWorkflows WHERE Final = 0);

http://s000.tinyupload.com/index.php?file_id=49593927895920014301

对于不在 ValueField 0 中的 SQL:

SELECT * FROM Incidencias WHERE EstadoWorkflow not in (SELECT IdEstadoWorkflow FROM EstadosWorkflows WHERE Final = 0);

http://s000.tinyupload.com/index.php?file_id=03901091628843565847

对于不在 ValueField 1 中的 SQL:

SELECT * FROM Incidencias WHERE EstadoWorkflow not in (SELECT IdEstadoWorkflow FROM EstadosWorkflows WHERE Final = 1);

http://s000.tinyupload.com/index.php?file_id=69996775965382534356

查询与我在示例中发布的查询相同，但具有其他名称，这是示例查询与实际查询的等价字典。

• 表 1:事件
• 表2:EstadosWorkflows
• IdTable2:EstadoWorkflow
• Table2.Id:IdEstadoWorkflow
• 值字段:最终

相反，为了更好的阅读:

• 事件:表1
• EstadosWorkflows:表2
• EstadoWorkflow:IdTable2
• IdEstadoWorkflow:Table2.Id
• 最终:ValueField

实际生产查询

此查询与查询计划显示了相同的问题，但由于额外的成本高昂的操作(如巨大的存在和联接)，问题变得更糟。我真的希望我的简化示例没有误导您。

查询IN，值为0

SELECT distinct top 15 this_.IdIncidencia as y0_, this_.Fecha as y1_ 
FROM Incidencias this_ inner join Usuarios usuario1_ on this_.Usuario=usuario1_.IdUsuario inner join Usuarios_Perfiles perfiles5_ on usuario1_.IdUsuario=perfiles5_.Usuario and (perfiles5_.perfil in (select perfiles.idperfil from perfiles where perfiles.borrado = 0)) inner join Perfiles prf2_ on perfiles5_.Perfil=prf2_.IdPerfil 
WHERE 
this_.Instancia = 4 and 
this_.EstadoWorkflow in (SELECT this_0_.IdEstadoWorkflow as y0_ FROM EstadosWorkflows this_0_ WHERE this_0_.Final = 0) and 
exists (SELECT this_0_.IdPerfilPermiso as y0_ FROM Perfiles_Permisos this_0_ inner join Permisos prm1_ on this_0_.Permiso=prm1_.IdPermiso WHERE this_0_.IdPerfilPermiso in (206558, 206559, 209393, 209394) and (this_0_.PerfilAutorizado = prf2_.IdPerfil and this_0_.TipologiaAutorizada = this_.Tipologia and prm1_.Controlador = 'Incidencias' and prm1_.Accion = 'Index')) 
ORDER BY this_.Fecha desc

执行时间:266ms。执行计划:http://s000.tinyupload.com/index.php?file_id=36115325682943356233

查询IN，值为1

SELECT distinct top 15 this_.IdIncidencia as y0_, this_.Fecha as y1_ 
FROM Incidencias this_ inner join Usuarios usuario1_ on this_.Usuario=usuario1_.IdUsuario inner join Usuarios_Perfiles perfiles5_ on usuario1_.IdUsuario=perfiles5_.Usuario and (perfiles5_.perfil in (select perfiles.idperfil from perfiles where perfiles.borrado = 0)) inner join Perfiles prf2_ on perfiles5_.Perfil=prf2_.IdPerfil 
WHERE 
this_.Instancia = 4 and 
this_.EstadoWorkflow in (SELECT this_0_.IdEstadoWorkflow as y0_ FROM EstadosWorkflows this_0_ WHERE this_0_.Final = 1) and 
exists (SELECT this_0_.IdPerfilPermiso as y0_ FROM Perfiles_Permisos this_0_ inner join Permisos prm1_ on this_0_.Permiso=prm1_.IdPermiso WHERE this_0_.IdPerfilPermiso in (206558, 206559, 209393, 209394) and (this_0_.PerfilAutorizado = prf2_.IdPerfil and this_0_.TipologiaAutorizada = this_.Tipologia and prm1_.Controlador = 'Incidencias' and prm1_.Accion = 'Index')) 
ORDER BY this_.Fecha desc

执行时间:28506ms。执行计划:http://s000.tinyupload.com/index.php?file_id=72827687005228029776

查询NOT IN，值为0

SELECT distinct top 15 this_.IdIncidencia as y0_, this_.Fecha as y1_ 
FROM Incidencias this_ inner join Usuarios usuario1_ on this_.Usuario=usuario1_.IdUsuario inner join Usuarios_Perfiles perfiles5_ on usuario1_.IdUsuario=perfiles5_.Usuario and (perfiles5_.perfil in (select perfiles.idperfil from perfiles where perfiles.borrado = 0)) inner join Perfiles prf2_ on perfiles5_.Perfil=prf2_.IdPerfil 
WHERE 
this_.Instancia = 4 and 
this_.EstadoWorkflow not in (SELECT this_0_.IdEstadoWorkflow as y0_ FROM EstadosWorkflows this_0_ WHERE this_0_.Final = 0) and 
exists (SELECT this_0_.IdPerfilPermiso as y0_ FROM Perfiles_Permisos this_0_ inner join Permisos prm1_ on this_0_.Permiso=prm1_.IdPermiso WHERE this_0_.IdPerfilPermiso in (206558, 206559, 209393, 209394) and (this_0_.PerfilAutorizado = prf2_.IdPerfil and this_0_.TipologiaAutorizada = this_.Tipologia and prm1_.Controlador = 'Incidencias' and prm1_.Accion = 'Index')) 
ORDER BY this_.Fecha desc

执行时间:498ms。执行计划:http://s000.tinyupload.com/index.php?file_id=35554889075362686964

查询NOT IN，值为1

SELECT distinct top 15 this_.IdIncidencia as y0_, this_.Fecha as y1_ 
FROM Incidencias this_ inner join Usuarios usuario1_ on this_.Usuario=usuario1_.IdUsuario inner join Usuarios_Perfiles perfiles5_ on usuario1_.IdUsuario=perfiles5_.Usuario and (perfiles5_.perfil in (select perfiles.idperfil from perfiles where perfiles.borrado = 0)) inner join Perfiles prf2_ on perfiles5_.Perfil=prf2_.IdPerfil 
WHERE 
this_.Instancia = 4 and 
this_.EstadoWorkflow not in (SELECT this_0_.IdEstadoWorkflow as y0_ FROM EstadosWorkflows this_0_ WHERE this_0_.Final = 1) and 
exists (SELECT this_0_.IdPerfilPermiso as y0_ FROM Perfiles_Permisos this_0_ inner join Permisos prm1_ on this_0_.Permiso=prm1_.IdPermiso WHERE this_0_.IdPerfilPermiso in (206558, 206559, 209393, 209394) and (this_0_.PerfilAutorizado = prf2_.IdPerfil and this_0_.TipologiaAutorizada = this_.Tipologia and prm1_.Controlador = 'Incidencias' and prm1_.Accion = 'Index')) 
ORDER BY this_.Fecha desc

执行时间:386ms。执行计划:http://s000.tinyupload.com/index.php?file_id=11500314236594795220

Answer 1

导致该问题的原因是SQL Server在优化时无法知道in语句返回的确切值，因此无法使用统计信息。

当您在 in 子句中获得准确的值时，可以将它们与统计信息进行比较，并且 SQL Server 很可能非常准确地估计将有多少行，然后可以选择最佳的执行计划。

我自己没有尝试过，但您可以尝试为 id 创建一个过滤统计信息，分别为值字段 0 和 1，也许这会改善情况。

更新

从最新的图片中可以清楚地看到，与估计相差很大，行数估计为1，但嵌套循环后实际上是59851:

这个错误的估计似乎会导致大量的表扫描，因为它预计只会执行一次:

由于这是表扫描而不是聚集索引扫描，因此看起来该表没有聚集索引，也没有可以使用的其他索引。你能做点什么吗？不知道数据量，但包含或普通列 idperfil 的 borrado 索引可能会有所帮助。这也是 in value 0 计划中发生的情况，但由于行数只有 605，因此 605 个表扫描并不会花费那么多时间，但是当您执行几乎 100 倍的操作时，它就开始花费时间。

看看 not in -plan，那么搜索的结构完全不同，很可能是因为估计的行数更接近实际的行数，而 SQL Server 使用这种计划:

因此，另一个解决方案可能是从 Usuarios_Perfiles 创建一个临时表(带有 perfiles 限制)可能会有所帮助，因为它只有 1179 行。

如果没有统计 IO 输出，就不能 100% 确定时间花在哪里，但看起来很像是由表扫描引起的。