SELECT * Persons.LastName A-D SELECT * Persons.LastName E-L SELECT * Persons.LastName M-R SELECT * Persons.LastName S-Z
我正在使用以下非常低效且难以索引的内容：
WHERE LastName LIKE '[a-d]%'
有什么想法可以更好地解决这个问题吗？我认为对于Filtered Index来说这可能是一个很好的场景,但是where子句需要更加可靠.
谢谢
解决方法
示例#1：如果在AdventureWorks2008R2数据库中运行此查询
SET STATISTICS IO ON; SET NOCOUNT ON; PRINT 'Example #1:'; SELECT p.BusinessEntityID,p.LastName FROM Person.Person p WHERE p.LastName LIKE '[a-a]%'
然后,您将获得基于Index Seek运算符的执行计划(优化谓词：绿色矩形,非优化谓词：红色矩形)：
SET STATISTICS IO的输出是
Example #1: Table 'Person'. Scan count 1,logical reads 7
这意味着服务器必须从缓冲池中读取7个页面.此外,在这种情况下,索引IX_Person_LastName_FirstName_MiddleName包括SELECT,FROM和WHERE子句所需的所有列：LastName和BusinessEntityID.如果表具有聚簇索引,则所有非聚簇索引将包括聚簇索引键中的列(BusinessEntityID是PK_Person_BusinessEntityID聚簇索引的键).
但：
1)您的查询必须显示所有列,因为SELECT *(这是一个不好的做法)：BusinessEntityID,LastName,FirstName,MiddleName,PersonType,…,ModifiedDate.
2)索引(前一示例中的IX_Person_LastName_FirstName_MiddleName)不包括所有必需的列.这就是为什么对于此查询,此索引是非覆盖索引的原因.
现在,如果您执行下一个查询,那么您将获得差异. [实际]执行计划(SSMS,Ctrl M)：
SET STATISTICS IO ON; SET NOCOUNT ON; PRINT 'Example #2:'; SELECT p.* FROM Person.Person p WHERE p.LastName LIKE '[a-a]%'; PRINT @@ROWCOUNT; PRINT 'Example #3:'; SELECT p.* FROM Person.Person p WHERE p.LastName LIKE '[a-z]%'; PRINT @@ROWCOUNT; PRINT 'Example #4:'; SELECT p.* FROM Person.Person p WITH(FORCESEEK) WHERE p.LastName LIKE '[a-z]%'; PRINT @@ROWCOUNT;
结果：
Example #2: Table 'Person'. Scan count 1,logical reads 2805,lob logical reads 0 911 Example #3: Table 'Person'. Scan count 1,logical reads 3817,lob logical reads 0 19972 Example #4: Table 'Person'. Scan count 1,logical reads 61278,lob logical reads 0 19972
执行计划：
另外：查询将为您提供在’Person.Person’上创建的每个索引的页数：
SELECT i.name,i.type_desc,f.alloc_unit_type_desc,f.page_count,f.index_level FROM sys.dm_db_index_physical_stats(
DB_ID(),OBJECT_ID('Person.Person'),DEFAULT,'DETAILED' ) f
INNER JOIN sys.indexes i ON f.object_id = i.object_id AND f.index_id = i.index_id
ORDER BY i.type
name type_desc alloc_unit_type_desc page_count index_level
--------------------------------------- ------------ -------------------- ---------- -----------
PK_Person_BusinessEntityID CLUSTERED IN_ROW_DATA 3808 0
PK_Person_BusinessEntityID CLUSTERED IN_ROW_DATA 7 1
PK_Person_BusinessEntityID CLUSTERED IN_ROW_DATA 1 2
PK_Person_BusinessEntityID CLUSTERED ROW_OVERFLOW_DATA 1 0
PK_Person_BusinessEntityID CLUSTERED LOB_DATA 1 0
IX_Person_LastName_FirstName_MiddleName NONCLUSTERED IN_ROW_DATA 103 0
IX_Person_LastName_FirstName_MiddleName NONCLUSTERED IN_ROW_DATA 1 1
...
现在,如果您比较示例#1和示例#2(两者都返回911行)
`SELECT p.BusinessEntityID,p.LastName ... p.LastName LIKE '[a-a]%'` vs. `SELECT * ... p.LastName LIKE '[a-a]%'`
然后你会看到两个差异：
a)7个逻辑读取与2805个逻辑读取和
b)索引查找(#1)与索引查找密钥查找(#2).
您可以看到SELECT *(#2)查询的性能最差(7页对2805页).
现在,如果您比较示例#3和示例#4(两者都返回19972行)
`SELECT * ... LIKE '[a-z]%` vs. `SELECT * ... WITH(FORCESEEK) LIKE '[a-z]%`
然后你会看到两个差异：
a)3817个逻辑读取(#3)与61278个逻辑读取(#4)和
b)聚类索引扫描(PK_Person_BusinessEntityID具有3808 7 1 1 1 = 3818页)与索引查找密钥查找.
您可以看到Index Seek Key Lookup(#4)查询的性能最差(3817页对61278页).在这种情况下,您可以看到和IX_Person_LastName_FirstName_MiddleName上的索引查找以及PK_Person_BusinessEntityID(聚簇索引)上的键查找将使您的性能低于“聚簇索引扫描”.
由于SELECT *,所有这些糟糕的执行计划都是可能的.
