SQL Server覆盖索引--有无包含列对数据库查询性能的影响分析

　　“覆盖索引使您能够避免返回到表中以满足请求的所有列，因为所有请求的列都已经存在于非聚集索引中。这意味着您还可以避免返回到表中进行任何逻辑或物理的信息读取。”

　　然而，以上这不是我想要传达的全部意思，因为他不仅仅是避免逻辑或物理的读取的问题。在“非聚集索引”中的列和需要在表中查找的列之间，还需要考虑“将数据放在一起”的必要工作。为了说明这个问题，让我们创建两个完全一模一样的表，即：相同的结构，相同的数据且都是10000条：

CREATE TABLE Beach.dbo.cruiser_1
(
  pkid INT IDENTITY(1,1) NOT NULL,
  registration_id VARCHAR(20) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  first_name VARCHAR(50) NOT NULL,
  address_1 VARCHAR(100) NULL,
  address_2 VARCHAR(100) NULL,
  city VARCHAR(100) NULL,
  state_region VARCHAR(100) NULL,
  postal_code VARCHAR(10) NULL,
  company_id VARCHAR(20) NULL,
  plus_one_notes VARCHAR(200) NULL,
  registration_notes VARCHAR(200) NULL
);

CREATE TABLE Beach.dbo.cruiser_2
(
  pkid INT IDENTITY(1,1) NOT NULL,
  registration_id VARCHAR(20) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  first_name VARCHAR(50) NOT NULL,
  address_1 VARCHAR(100) NULL,
  address_2 VARCHAR(100) NULL,
  city VARCHAR(100) NULL,
  state_region VARCHAR(100) NULL,
  postal_code VARCHAR(10) NULL,
  company_id VARCHAR(20) NULL,
  plus_one_notes VARCHAR(200) NULL,
  registration_notes VARCHAR(200) NULL
);

View Code

　　请相信我，两个表中的数据是完全一样的。在此我们就暂时不把每个表10000条的数据展示了。同时，我已经在每个表上创建了完全一样的两个聚集索引，且都在pkid列上，

因为该列为自动增长列，我也把pkid列上的填充因子设置为了100%.

ALTER TABLE dbo.cruiser_1 ADD CONSTRAINT PK_cruiser_1_pkid PRIMARY KEY CLUSTERED ( pkid ) WITH ( PAD_INDEX = OFF , FILLFACTOR = 100 , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]; GO ALTER TABLE dbo.cruiser_2 ADD CONSTRAINT PK_cruiser_1_pkid PRIMARY KEY CLUSTERED ( pkid ) WITH ( PAD_INDEX = OFF , FILLFACTOR = 100 , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]; GO

CREATE NONCLUSTERED INDEX [IX_cruiser_1_last_name] ON [dbo].[cruiser_1] ( [last_name] ASC, [first_name] ASC ) INCLUDE ( [registration_id] ) WITH ( PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , SORT_IN_TEMPDB = OFF , DROP_EXISTING = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON , FILLFACTOR = 80 ) GO CREATE NONCLUSTERED INDEX [IX_cruiser_2_last_name] ON [dbo].[cruiser_2] ( [last_name] ASC, [first_name] ASC ) WITH ( PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , SORT_IN_TEMPDB = OFF , DROP_EXISTING = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON , FILLFACTOR = 80 ) GO

SQL Server覆盖索引--有无包含列对数据库查询性能的影响分析

相关推荐