在SQL SERVER中通过索引进行查询优化

索引算是查询优化中最常见，也算是知名度最高的一种手段了，不少于计算机沾边的专业甚至在大学的时候就已经接触过了索引，甚至有了很多使用索引优化查询的实际操作经验。用一种不是很严谨的方式去理解，索引可以认为是把索引所在的数据表中的一部分（即索引本身的字段以及其包含列）进行排序，再保存到额外的存储空间之中。由于索引本身是具有一定顺序的，而且索引中包含的字段通常比实际数据表中的字段要少，数据库引擎在进行查找的时候可以用更少的IO次数查找更多的记录，因此大多数情况下，通过索引进行查询的效率会比不使用索引的查询快很多。

可以用一个实际的例子来体验一下索引带来的查询性能的提升。

1、建立一张结构简单的数据表，并初始化一定量的数据：

CREATE TABLE [TestTable] (
    id INT NOT NULL IDENTITY (1, 1),
    val INT NOT NULL DEFAULT 0,
    createtime DATETIME NOT NULL DEFAULT GETDATE(),
    PRIMARY KEY (id)
) ON [PRIMARY];

2、为我们的测试数据表初始化约1600万条数据：

DECLARE @i INT;
    SET @i = 0;
WHILE @i < 1000
BEGIN
    INSERT INTO TestTable (val) VALUES (@i);
    SET @i = @i + 1;
END;

DECLARE @ct INT;
    SET @ct = 0;
WHILE @ct < 14
BEGIN
    INSERT INTO [TestTable] (val)
    SELECT val
    FROM [TestTable] WITH (NOLOCK);
    
    SET @ct = @ct + 1;
END;

3、将数据表的表结构及数据复制一份儿，做为比对：

SELECT * INTO TestTableNoIndex
FROM [TestTable] WITH (NOLOCK);

此时，两张表的数据结构及数据量完全相同，但是表TestTable的id列有聚集索引（主键），而TestTableNoIndex表没有任何索引，如下图所示：

接下来我们尝试随机抽取1个id，并在两张表中根据id对记录进行查找，来比对两张表中相同记录的查找速度。以TestTable表为例，SQL如下：

--清除SQL SERVER的缓存
DBCC DROPCLEANBUFFERS; 
DBCC FREEPROCCACHE;

DECLARE @beginIndex DATETIME
    SET @beginIndex = GETDATE();
SELECT id, val, createtime
FROM [TestTable]
WHERE id = 695523;
SELECT DATEDIFF(MS, @beginIndex, GETDATE()) AS '查询时间（有索引）'; 

执行结果方面，对于相同的记录，TestTable表的执行时间为30毫秒，而TestTableNoIndex表需要18260毫秒，可以看到差距已经相当明显了。具体分析两张表的实际查询计划，可以看出对TestTable的执行计划为“聚集索引查找”

而对TestTableNoIndex表的执行计划中大部分的开销来源于“表扫描”，主要原因就是对于没有索引的表，SQL SERVER引擎只好使用最笨的方法，根据表中的记录一条一条的进行查找。可以想象一下我们在一个已经从小到大排好序的数组里查找一条记录，只要当前元素大于待检索的元素就可以结束循环了，从而节省不少的开销。此处说明的情况和这种场景有类似之处。

 

需要说明的是，由于机器的硬件配置差异，或者用于测试时表中数据量的差异，都可能导致结果与文中描述不同。此外，如果将上述查询中条件进行变更，如根据val字段进行查询，那么针对这两张表的查询相应时间都会出现变化，这涉及到包括索引覆盖、聚集索引与非聚集索引等多方面的问题，日后视具体情况继续。