Sql server 性能优化基础(二)

索引

• 聚集索引：唯一，并保持表中记录的物理顺序；

• 非聚集索引：不一定唯一，与物理顺序无关，聚集索引的引用。

当表有无索引、聚集索引时候在查询分析器中的迭代器：

	扫描	查找
堆(Heap)	表扫描(Table Scan)	表扫描(Table Scan)
聚集索引(Clustered Index)	聚集索引扫描(Clustered Index Scan)	聚集索引查找(Clustered Index Seek)
非聚集索引(Nonclustered Index)	索引扫描(Index Scan)	索引查找(Index Seek)

单列索引命中(we have a single-column index on a column Col1)：

[Col1] = 3.14
[Col1] > 100
[Col1] BETWEEN 0 AND 99
[Col1] LIKE 'abc%'
[Col1] IN (2, 3, 5, 7)

单列索引不能命中的：

ABS([Col1]) = 1
[Col1] + 1 = 9
[Col1] LIKE '%abc'

多列索引可命中(we have a two-column index on columns Col1 and Col2)：

[Col1] = 3.14 AND [Col2] = 'pi'
[Col1] = 'xyzzy' AND [Col2] <= 0

多列存在剩余谓词：

[Col1] > 100 AND [Col2] > 100
[Col1] LIKE 'abc%' AND [Col2] = 2

多列不能命中：

[Col2] = 0
[Col1] + 1 = 9 AND [Col2] BETWEEN 1 AND 9
[Col1] LIKE '%abc' AND [Col2] IN (1, 3, 5)

索引覆盖测试：

CREATE TABLE T_heap (a int, b int, c int, d int, e int, f int)
CREATE INDEX T_heap_a ON T_heap (a)
CREATE INDEX T_heap_bc ON T_heap (b, c)
CREATE INDEX T_heap_d ON T_heap (d) INCLUDE (e)
CREATE UNIQUE INDEX T_heap_f ON T_heap (f)

CREATE TABLE T_clu (a int, b int, c int, d int, e int, f int)
CREATE UNIQUE CLUSTERED INDEX T_clu_a ON T_clu (a)
CREATE INDEX T_clu_b ON T_clu (b)
CREATE INDEX T_clu_ac ON T_clu (a, c)
CREATE INDEX T_clu_d ON T_clu (d) INCLUDE (e)
CREATE UNIQUE INDEX T_clu_f ON T_clu (f)

书签查找：

当非聚集索引命中，却不包含全部的输出列

The nonclustered index OrderDate only covers the OrderId column (which also happens to be the clustering key for the Orders table in the Northwind2 database).

SELECT [OrderId], [CustomerId] FROM [Orders] WHERE [OrderDate] = '1998-02-26'

从迭代器上看就是1个嵌套循环+1个索引查找+1个聚集索引查找(或RID查找)

待续...