sql-server - 让SQL Server使用索引查找+键查找代替聚集索引扫描，不使用WITH (FORCESEEK)

Question

版本:SQL Server 2008 R2

数据库:AdventureWorks 2008R2，来自 http://msftdbprodsamples.codeplex.com/releases/view/55926

查询:

SELECT TOP 10
    *
FROM
    Person.Person --WITH (FORCESEEK)
WHERE
    LastName like 'Max%'
    OR EXISTS (
        SELECT
            1
        FROM
            Person.PersonPhone
        WHERE
            Person.PersonPhone.BusinessEntityID = Person.Person.BusinessEntityID
            AND Person.PersonPhone.PhoneNumber LIKE '122%'
    )
ORDER BY Person.Person.BusinessEntityID DESC

在没有任何查询提示的情况下，SQL Server将使用聚集索引扫描，这是IO密集型的:

Table 'PersonPhone'. Scan count 14170, logical reads 28446, physical reads 15, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Person'. Scan count 1, logical reads 2844, physical reads 3, read-ahead reads 3215, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

使用 WITH (FORCESEEK) 查询提示，SQL Server 将选择索引查找 + 键查找，这样完成速度更快，并且对 IO 友好 500 倍:

Table 'Person'. Scan count 1, logical reads 59, physical reads 22, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PersonPhone'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

我的问题是，有没有办法让 SQL Server 在没有任何查询提示的情况下使用更好的计划？也许添加索引？或者改变配置参数？还是 SQL Server 的查询优化器就那么无能？

这是来自 http://msdn.microsoft.com/en-us/library/bb510478.aspx 的 gem :

Query optimizer rules and poor cardinality estimation can also cause the optimizer to perform a table or index scan operation rather than an index seek when a query uses IN or LIKE as search predicates.

Answer 1

这是一个在不诉诸 FORCESEEK 的情况下展示“体面”IO 数据的版本。有趣的是，这个看起来可怕的查询执行得“更好”。

免责声明:这对于“Max%”和“122%”可能会表现更好，但如果该计划重复用于“M%”和“%”，它的表现则是另一回事......

SELECT TOP 10 *
FROM Person.Person P
INNER JOIN (
SELECT BusinessEntityID
FROM    Person.Person --WITH (FORCESEEK)
WHERE    LastName like 'Max%'    

UNION

SELECT BusinessEntityID
FROM    Person.Person --WITH (FORCESEEK)
WHERE    EXISTS (        SELECT            *        FROM        
    Person.PersonPhone        WHERE            Person.PersonPhone.BusinessEntityID = Person.Person.BusinessEntityID            AND Person.PersonPhone.PhoneNumber LIKE '122%'    )
) NOTNICE
ON NOTNICE.BusinessEntityID = P.BusinessEntityID
ORDER BY P.BusinessEntityID DESC