sql-server - SQL Server什么时候使用复合索引和覆盖索引？

Question

我有 2 张 table tb_player1和tb_player2 .

CREATE TABLE tb_player1 
(
    pk_id INT PRIMARY KEY IDENTITY (1,1) NOT NULL,
    first_name CHAR(16),
    last_name CHAR(16),
    age INT
)

CREATE NONCLUSTERED INDEX ix_nonclustered_name ON tb_player1(first_name, last_name)

CREATE TABLE tb_player2 
(
    pk_id INT PRIMARY KEY IDENTITY (1,1) NOT NULL,
    first_name CHAR(16),
    last_name CHAR(16),
    age INT
)

CREATE NONCLUSTERED INDEX ix_nonclustered_name ON tb_player2(first_name)
INCLUDE (last_name)

tb_player1有一个综合索引和 tb_player2已包含列(覆盖索引)。

我针对 tb_player1 运行以下 SQL 语句和tb_player2 ，但实际的执行计划是tb_player1和tb_player2是一样的。

INSERT INTO tb_player1 VALUES('kenny', 'lee', 29)
INSERT INTO tb_player1 VALUES('rose', 'chao', 27)
INSERT INTO tb_player1 VALUES('mark', 'chao', 25)

INSERT INTO tb_player2 VALUES('kenny', 'lee', 29)
INSERT INTO tb_player2 VALUES('rose', 'chao', 27)
INSERT INTO tb_player2 VALUES('mark', 'chao', 25)



select first_name, last_name from tb_player1 where first_name = 'kenny'
select first_name, last_name from tb_player2 where first_name = 'kenny'

select first_name, last_name from tb_player1 where last_name = 'lee'
select first_name, last_name from tb_player2 where last_name = 'lee'

select first_name, last_name from tb_player1 where first_name = 'kenny' AND last_name = 'lee'
select first_name, last_name from tb_player2 where first_name = 'kenny' AND last_name = 'lee'

select first_name, last_name from tb_player2 where last_name = 'lee' AND  first_name = 'kenny'
select first_name, last_name from tb_player1 where last_name = 'lee' AND  first_name = 'kenny'

SQL Server什么时候使用复合索引和覆盖索引？它们之间有什么区别呢？为什么他们的执行计划看起来没有什么不同。

Answer 1

1. 复合索引(其中所有列都是“关键”列)将携带索引所有级别的数据； INCLUDE 索引将仅携带叶节点中的“非键”列。要点:INCLUDE 索引将小于等效的复合索引。

2. INCLUDE 列(非键)不会计入最大索引大小(900 字节)。

3. 对 INCLUDE 索引中非键列的更新不会导致索引碎片；对复合索引中未使用的“键”列的更新将导致碎片......在我看来，这是一个大问题。

4. 假设索引碎片较低，复合索引和 INCLUDE 索引的性能相当。复合索引中的所有列不必都在查询谓词中。