sql-server – 基于字典的查询的查询索引

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 *,所有这些糟糕的执行计划都是可能的.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!