代码之家 › 专栏 › 技术社区 › zerkms

带有意外索引扫描的慢速查询

indexing performance sql

zerkms · 技术社区 · 16 年前

我有这个问题:

SELECT *
FROM sample
   INNER JOIN test ON sample.sample_number = test.sample_number
   INNER JOIN result ON test.test_number = result.test_number
   WHERE sampled_date BETWEEN '2010-03-17 09:00' AND '2010-03-17 12:00'

这里最大的表格是result,包含11.1百万条记录。左边两张桌子大约1米。

此查询工作缓慢(超过10分钟),返回大约800条记录。执行计划显示 聚集索引扫描 (超过它的主键(result.result_number,实际上不参与查询))超过所有11m记录。 result.test_号是群集主键。

如果我把2010-03-17 09:00改成2010-03-17 10:00,我会得到大约40条记录。执行300毫秒,计划显示 索引查找 (超过result.test_number index)

如果我将select子句中的*替换为result.test_number(包含在索引中),那么在第一种情况下,它们都会变快。这指出了HDD IO问题,但没有澄清更改计划。

那么,有什么想法吗?

更新: 抽样日期在表格样本中,并包含在索引中。此查询的其他字段:test.sample_number也包含在index和result.test_number中。

更新2: 显然,SQL Server在任何原因下都不想使用索引。

我做了一个小实验:我删除了内部连接和结果,选择所有test.test_编号,然后做了

SELECT * FROM RESULT WHERE TEST_NUMBER IN (...)

当然,这很快就能奏效。但是我不能理解区别是什么,以及为什么查询优化器在第一种情况下选择这样不合适的方式来选择数据。

更新3: 在备份数据库并用新名称还原到数据库之后-这两个请求都能像预期的那样快速工作,甚至在更多的范围内也能工作…

那么-是否有任何特殊的命令来清理或优化,无论什么,都与此相关?:

3 回复 | 直到 16 年前

Gabe Timothy Khouri 16 年前

有几件事要尝试:

更新统计信息
向查询中添加有关要使用的索引的提示(在SQL Server中,您可能会说 WITH (INDEX(myindex)) 指定表格后)

编辑:您注意到复制数据库使其工作正常,这告诉我索引统计数据已过期。你可以用类似 UPDATE STATISTICS mytable 定期进行。

使用 EXEC sp_updatestats 更新整个数据库。

Robert Wagner 16 年前

我要做的第一件事是指定我想要的确切列,看看问题是否仍然存在。我怀疑你需要这三张桌子上的所有列。

听起来很难从结果表中取出所有行。一排有多大?看看表中的所有数据有多大,然后除以行数。右键单击表->属性…,存储选项卡。

尝试将WHERE子句放入子查询中以强制它首先执行该操作?

SELECT *
FROM 
    (SELECT * FROM sample 
     WHERE sampled_date 
     BETWEEN '2010-03-17 09:00' AND '2010-03-17 12:00') s
     INNER JOIN test ON s.sample_number = test.sample_number
     INNER JOIN result ON test.test_number = result.test_number

或者,如果您希望得到少量的样本,这可能会更好。

SELECT *
FROM sample
   INNER JOIN test ON sample.sample_number = test.sample_number
   INNER JOIN result ON test.test_number = result.test_number
WHERE sample.sample_ID in (
    SELECT sample_ID
    FROM sample
    WHERE sampled_date BETWEEN '2010-03-17 09:00' AND '2010-03-17 12:00'
)

marc_s MisterSmith 16 年前

如果你做了 SELECT * ,您需要表中的所有数据。表的数据位于聚集索引中-聚集索引的叶节点是数据页。

所以,如果你想要所有这些数据页,而且因为你加入了一百万。行到1100万。行(11行中的1行对SQL Server来说不是很有选择性),使用索引查找行,然后对找到的每一行的实际数据页进行书签查找,可能效率不高,因此SQL Server使用聚集索引扫描。

因此,长话短说:只选择那些你真正需要的行!因此,您可以给SQL Server一个使用索引的机会,在那里进行查找,并找到必要的数据。

如果您只选择三列、四列,那么SQL Server查找和使用包含这些列的索引的可能性比从所有涉及的表中请求所有数据的可能性要高得多。

另一种选择是尝试找到一种表示子查询的方法,例如使用一个公共表表达式,它可以从两个较小的表中获取数据,并进一步减少行数,并将希望非常小的结果与主表相结合。如果有一个只有40或800个结果的小结果集(而不是两个具有1 mio的表)。然后,SQL Server可能更倾向于使用聚集索引查找,并对40或800行执行书签查找,而不是执行完全聚集索引扫描。