如何用非聚集索引搜索或聚集索引搜索替换聚集索引扫描？

Question

下面是我的create table脚本：

CREATE TABLE [dbo].[PatientCharts](
[PatientChartId] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
[FacilityId] [uniqueidentifier] NOT NULL,
[VisitNumber] [varchar](200) NOT NULL,
[MRNNumber] [varchar](100) NULL,
[TimeIn] [time](7) NULL,
[TimeOut] [time](7) NULL,
[DateOfService] [date] NULL,
[DateOut] [date] NULL),

我在PatientChartId上有一个聚集索引，在VisitNumber和MRNNumber上有两个非聚集索引。这个表有数百万条记录。

以下查询正在执行聚集索引扫描：-

SELECT  *
FROM    dbo.PatientCharts
INNER JOIN ( SELECT FacilityID
                 FROM   Facilities
                 WHERE  RemoteClientDB IN (
                        SELECT  SiteID
                        FROM    RemoteClient WITH ( NOLOCK )
                        WHERE   Code = 'IN-ESXI-EDISC14'
                                )
               ) AS Filter ON dbo.PatientCharts.FacilityId = Filter.FacilityID   

由于数据量的原因，这种聚集索引扫描在生产中占用了大量时间。

执行计划如下：

​

​

我甚至尝试在FacilityID上添加一个非聚集索引，并包含PatientChartID，但仍然是相同的执行计划。

我每次都在执行DBCC FREEPROCCACHE，以指示sql server每次都使用新的计划。

还有什么我应该做的，以防止集群索引扫描？

Answer 1

由于没有索引支持您的查询，因此将进行群集扫描。即使你索引了FacilityID和PatientChartID，你仍然可能需要足够的数据量来扫描，因为已经超过了临界点(Google Kimberly Tripp tipping )

没有简单的方法来说下一部分，但是对于一个有数百万条记录的系统，但是一个如此琐碎的查询给您带来了问题，您必须对索引以及SQL计划引擎的行为有更多的了解。我会推荐Kalen Delany的SQL内部，如果你在这里搜索书籍推荐，这里有一些很好的可靠建议的问题。

Answer 2

您是否尝试过将其实现为具有内部连接的直接查询，而不是在每个步骤中使用子查询？

如果您将查询更改为以下形式，我将很乐意查看生成的执行计划：

select * from patientschart...
inner join facilities...
inner join remoteclientdb....
where...

我认为，一旦去除子查询，优化器就会选择正确的索引。尝试一下，并分享执行计划。

另外，您是否需要结果集中的所有字段？在select列表中切换到特定列而不是*，可能会对您有所帮助。

我希望这能帮到你。

Answer 3

正如Andrew提到的，您的聚集索引在这里对您没有帮助，也没有坏处-如果您没有聚集索引，您将看到一个表扫描(我向您保证，这不会比聚集索引扫描更有趣)。

假设这是该表上最重要的查询，我建议您应该更改表设计，以便聚集索引位于FacilityID上。这将大大加快速度。