主页 / dba / 问题 / 199487
Accepted
Kevin
Asked: 2018-03-07 05:49:08 +0800 CST

未使用非聚集索引

  • 772

我有一个包含 321 行的表（在下面创建）。

我希望下面的最后一个查询使用非聚集索引，然后使用键查找。但是，它使用聚集索引扫描。仅按预期返回一行。

为什么它进行扫描而不是使用非聚集索引？是因为表只包含 321 行吗？

CREATE TABLE dbo.TestIndexSample
(
    Code char(4) NOT NULL,
    Name nvarchar(200) NOT NULL,
    ModifiedDate datetime NOT NULL CONSTRAINT [DF_TestIndexSample_ModifiedDate] DEFAULT GETDATE(),
    CONSTRAINT [PK_TestIndexSample_Code] PRIMARY KEY CLUSTERED(Code)
);
GO
CREATE NONCLUSTERED INDEX IX_TestIndexSample_Name  
    ON dbo.TestIndexSample(Name);   
GO

INSERT INTO dbo.TestIndexSample(Code, Name)
select CodeName, FullName 
from dbo.SourceTest 
GO

SELECT * FROM dbo.TestIndexSample

SELECT * FROM dbo.TestIndexSample where Code = 'X132EY'

SELECT * FROM dbo.TestIndexSample where Name = 'User A'
sql-server sql-server-2008-r2

2 个回答

  1. Best Answer

    您可以强制 SQL Server 使用非聚集索引：

    SELECT Code, Name, ModifiedDate 
FROM   dbo.TestIndexSample WITH(INDEX (IX_TestIndexSample_Name))
WHERE  Name = 'NAME10';

    在此处输入图像描述

    dbfiddle在这里，计划在这里

    如果没有提示，查询优化器会认为带有索引的计划对于少量数据来说成本更高，但是您可以通过增加表中的行数来获得此计划。我设法用 600 行来实现它：

    SELECT Code, Name, ModifiedDate 
FROM   dbo.TestIndexSample 
WHERE  Name = 'NAME10';

    dbfiddle在这里

    如果您只想获取 Index Seek，您的查询应该返回 only Name，以便仅从索引中提取数据。

    SELECT Name 
FROM   dbo.TestIndexSample  
WHERE  Name = 'NAME10';

    在此处输入图像描述

    dbfiddle在这里，计划在这里

    但如果还需要返回其他列，则可以使用覆盖索引：

    CREATE NONCLUSTERED INDEX IX_TestIndexSample_Name  
    ON dbo.TestIndexSample(Name) INCLUDE (Code, ModifiedDate);

    显然索引大小会增加，但对于这个行数来说并不重要。

    现在，您当前的查询将使用 Index Seek：

    SELECT Code, Name, ModifiedDate 
FROM   dbo.TestIndexSample 
WHERE  Name = 'NAME10';

    在此处输入图像描述

    dbfiddle在这里，计划在这里

    • 8

  2. 是因为表只包含 321 行吗？

    是的，这应该是原因。

    你的表有多少页？它很可能只有一个数据页，所以聚集索引扫描意味着服务器只能读取 2 页来完成这项工作，而查找意味着 2 页非聚集索引 + 2 页聚集索引。

    您可以使用以下代码查看页数：

    select index_id, total_pages
from sys.allocation_units au join sys.partitions p
        on au.container_id = p.hobt_id
where p.object_id = object_id('dbo.TestIndexSample');

    这里index_id= 1 对应于聚集索引，index_id= 2 对应于非聚集索引。

    • 5

相关问题