相近SQL语句（记录数接近），在不同库中，查询速度一个快一个慢查，查询快的执行步骤返回的行数和实际行数不一致

[zhendemo_13]

环境：windows 2008 r2 +sqlserver 2008 sp2
问题：相近SQL语句（记录数接近），在同一服务器上的不同数据库中(两个库的表结构，索引均一致)，均使用到了索引，（Index Seek），但查询速度一个快一个慢查，查询快的执行计划中的步骤返回的行数和实际行数不一致，
查询速度快执行步骤和索引信息的如下图：




查询速度慢的执行步骤和索引信息如下图：


帮忙分析一下SET STATISTICS PROFILE ON返回的ROW为何和查询条件下实际的行数不一致？
查询快和慢的原因是什么？需要如何优化？

[zhendemo_13]

补充一下：服务器IBM 3650 16核心 16G内存，执行快的不到1秒，慢的要8秒左右

[专注or全面]

两个库的索引统计信息更新时间不一致，上面那个索引统计信息是2-28号的，建议更新一下索引统计信息 update statistics tableName（indexname）

[还在加载中灬]

这个是EXISTS的短路功能，你快的那个查询，搜索到62行的数据，就找到了，EXISTS为真，条件不会继续执行 你慢的那个，EXISTS并没有搜索到相关数据，整个子查询会全表扫描 EXISTS不适合用于在匹配率低的大表作子查询

[zhendemo_13]

引用 13 楼 ky_min 的回复:

从你同表的情况就很明显可以看出，这个是EXISTS的原因了。 这就是EXISTS的短路功能，EXISTS作为相关子查询时，会一直扫描子表，直到为真，即有符合的行记录时。 你那个扫描了62行，就扫描出EXISTS为真，自然就不用继续扫描了，这时实际的扫描行数为62， 如果没有匹配的情况，EXISTS一直扫描子表，直到全表扫描完，才发现没有，这时的扫描行数就是全表记录数 假设，有一个查询，符合值在靠后，这时实际的扫描行数也会很大，就如你#11楼的情况。 而你普通的聚合查询，这个查询一定是扫描全表，或整个索引的 相同条件的查询，作为相关子查询放至EXISTS中和单独聚合查询，两个的扫描行数和数据有很大的关系。 因此，EXISTS不适合用于在匹配率低的大表作子查询，万一匹配很低，那你每个相关子查询都是全表扫描，那时间小伙伴会惊呆的。

感谢回复 针对这种情况，一个大表(匹配率低)，一个小表，如何连接？想听听你的建议？

select top 1 rr.id
from rr,ai
where rr.userid=ai.userid and rr.createtime between '2015-2-15' and '2015-2-16'
order by rr.createtime desc

这样写吗？该写法无论TOP 1是否有值,ai和rr的逻辑读次数和exists写法是一样的

[zhendemo_13]

select top 1 rr.id
from rr,ai
where  rr.createtime between '2015-2-15' and '2015-2-16'  and  rr.userid=ai.userid and ai.a='nice'
order by rr.createtime desc

[zhendemo_13]

另外，针对没有匹配值的查询，逻辑读次数是如何计算出来的?希望赐教 比如：rr总行数835879,每行600字节，索引400字节 ai总行数600,每行1586字节，索引282字节 ai的逻辑读1724009,rr逻辑读11644有公式能计算出来吗？

[专注or全面]

我验证了，ky_min说的是对的 你那个扫描了62行，就扫描出EXISTS为真，自然就不用继续扫描了，这时实际的扫描行数为62， 如果没有匹配的情况，EXISTS一直扫描子表，直到全表扫描完，才发现没有，这时的扫描行数就是全表记录数 但是楼主的问题，一个大表(匹配率低)，一个小表，如何连接？ 好似乎inner join连接代价要比exists大，楼主可以写一下比较一下

[还在加载中灬]

关于，逻辑读的算法，有高人算在前面了，我就不再赘述了，你可以了解下 http://www.cnblogs.com/CareySon/archive/2011/12/23/2299127.html 其实也只能是算出个大概 你可以大概对比下你11楼两次的行数与逻辑读的比例是一样的。 另外，SQL优化分析语句，我想看下#15楼语句的执行计划。

[zhendemo_13]

引用 18 楼 ky_min 的回复:

关于，逻辑读的算法，有高人算在前面了，我就不再赘述了，你可以了解下
http://www.cnblogs.com/CareySon/archive/2011/12/23/2299127.html
其实也只能是算出个大概
你可以大概对比下你11楼两次的行数与逻辑读的比例是一样的。

另外，SQL优化分析语句，我想看下#15楼语句的执行计划。

如下图

[还在加载中灬]

从执行计划上，可以看出，这个查询和EXISTS的前两步执行的扫描方法是一样的，要提高效率，把EXISTS换成INNERJOIN 貌似是不可行的 你的截图中，从没挡住的条件上看，你是不是要查询一条userid有存在于ai表的rr表的id 如果有存在的情况，速度应该都不会太慢， 但是不存在的话，相当于要扫描了整个表后才知道， 这个时候，我想应该只有提高Nested Loops连接效率了

[zhendemo_13]

引用 20 楼 ky_min 的回复:

从执行计划上，可以看出，这个查询和EXISTS的前两步执行的扫描方法是一样的，要提高效率，把EXISTS换成INNERJOIN 貌似是不可行的 你的截图中，从没挡住的条件上看，你是不是要查询一条userid有存在于ai表的rr表的id 如果有存在的情况，速度应该都不会太慢， 但是不存在的话，相当于要扫描了整个表后才知道， 这个时候，我想应该只有提高Nested Loops连接效率了

是的，一个大表，一个小表，SQLSERVER 查询分析器生成执行计划时会使用Nested Loops，那就是没办法优化了的意思？

[还在加载中灬]

感觉，还可以考虑用覆盖索引的方式试下~~

[专注or全面]

仔细看了一下，楼主说的“SET STATISTICS PROFILE ON返回的ROW为何和查询条件下实际的行数不一致” 查看资料显示SET STATISTICS PROFILE ON返回的ROWS 是执行计划的每一步返回的实际行数 对于执行快（第一个图片）的Rows=【62】，但和Count()统计出来的行数【940300】不一致，但是另外一个库(第三个图片)的却是一致的【835879】 实际上楼主误解了，rows列确实是返回的列， 你的查询，一个是top 1 id，一个是count(1)，最后返回的当然都是1行数据 之前返回的数据行数是聚合操作之前的行数，因为是并行执行的 最开始的索引seek因为是并行执行的，所以executes是12，返回的行数是835879，然后对12个并行的并行的结果集分别作做聚合运算，依旧是executes12次，返回12行（rows）数据（并行），然后对这个12行做聚合运算，返回的是1行（rows）数据

[zhendemo_13]

感谢回复


查看资料显示SET STATISTICS PROFILE ON返回的ROWS 是执行计划的每一步返回的实际行数
对于执行快（第一个图片）的Rows=【62】，但和Count()统计出来的行数【940300】不一致，但是另外一个库(第三个图片)的却是一致的【835879】

我解释一下，拿第一张图片来说，我的理解是这样的：

从下面往上看执行计划：
1.先从外部表 ai(600行) 拿出一行数据
2.然后和内部表 rr(940300行 查询条件WHERE rr.CreateTime BETWEEN '2015-2-16' AND '2015-2-18' ) 的记录去匹配，然后取外部表下一条记录再次匹配
3.将所有为真的记录排序
4. 取TOP1
5.返回结果集

第2步(即图片第四行)使用WHERE rr.CreateTime BETWEEN '2015-2-16' AND '2015-2-18' 查询 rr表时查找步骤返回的Rows=【62】行，但是实际上这个条件下的记录有【940300】行，那么问题来了，为何第四行查找步骤返回的Rows 不是等于【940300】行

5楼朋友说是因为EXISTS短路功能导致的，我不理解的地方是，不是首先要将内部表的数据取出来(第二步)，再匹配(第三步)吗？就算是短路了，但是看索引的情况，16-18号数据可是有【940300】行，为何就搜索【62】行就匹配完了

[还在加载中灬]

你这是一个TOP 1有值和一个TOP 1没有值的比对情况，我想借用你的数据，看一下，两个都为没有值的情况，看下执行计划又是怎么样一个情况。

[专注or全面]

第2步(即图片第四行)使用WHERE rr.CreateTime BETWEEN '2015-2-16' AND '2015-2-18' 查询 rr表时查找步骤返回的Rows=【62】行，但是实际上这个条件下的记录有【940300】行，那么问题来了，为何第四行查找步骤返回的Rows 不是等于【940300】行 你弄错了吧？ 你七楼的截图，查询结果ID是71911940的执行计划是截图中的，940300的无名列是count(*)出来的结果吧

[专注or全面]

看你的截图，快的，慢的sql的执行计划都是一样的，数据量也是相差不大，甚至慢的那么数据量还小一点 我怀疑你说的快的是不是在测试环境或者是开发环境测出来的结果，而慢的是在生产环境测试出来的？ 你说的快的，第一个截图，统计信息明显没有实时更新，而后一个截图，统计信息就是相对比较新的 要确认快慢是每次都有明显的差异，还是第一次执行差异比较明显，后面再执行快慢差异就不明显了

[zhendemo_13]

引用 8 楼 ky_min 的回复:

你这是一个TOP 1有值和一个TOP 1没有值的比对情况，我想借用你的数据，看一下，两个都为没有值的情况，看下执行计划又是怎么样一个情况。

你需要什么数据，我可以提供给你
刚才我在查询慢的数据库里，把查询条件调整了一下，即同一数据库，一个有值，一个没有值，具体如下：
首先是TOP1没有值的情况



接下来是TOP1有值得情况



一个奇怪的现象是，当TOP1没值得时候，ai表逻辑读的次数170多万次，一行数据600字节，这个逻辑读次数为何这么高？
有值的情况比没值的情况少读了100多W次？

[zhendemo_13]

引用 10 楼 x_wy46 的回复:

看你的截图，快的，慢的sql的执行计划都是一样的，数据量也是相差不大，甚至慢的那么数据量还小一点 我怀疑你说的快的是不是在测试环境或者是开发环境测出来的结果，而慢的是在生产环境测试出来的？ 你说的快的，第一个截图，统计信息明显没有实时更新，而后一个截图，统计信息就是相对比较新的 要确认快慢是每次都有明显的差异，还是第一次执行差异比较明显，后面再执行快慢差异就不明显了

是同一台服务器的同一个数据库实例中的两个不同数据库，统计信息是自动更新的，我也手动更新过，索引的碎片也刚整理过，相对新的索引是为了确保索引一致，拿执行的快的库的索引语句重建的

[还在加载中灬]

从你同表的情况就很明显可以看出，这个是EXISTS的原因了。 这就是EXISTS的短路功能，EXISTS作为相关子查询时，会一直扫描子表，直到为真，即有符合的行记录时。 你那个扫描了62行，就扫描出EXISTS为真，自然就不用继续扫描了，这时实际的扫描行数为62， 如果没有匹配的情况，EXISTS一直扫描子表，直到全表扫描完，才发现没有，这时的扫描行数就是全表记录数 假设，有一个查询，符合值在靠后，这时实际的扫描行数也会很大，就如你#11楼的情况。 而你普通的聚合查询，这个查询一定是扫描全表，或整个索引的 相同条件的查询，作为相关子查询放至EXISTS中和单独聚合查询，两个的扫描行数和数据有很大的关系。 因此，EXISTS不适合用于在匹配率低的大表作子查询，万一匹配很低，那你每个相关子查询都是全表扫描，那时间小伙伴会惊呆的。