MSSQL 索引优化原理

数据查询方式：

扫描：一页一页的查询符合条件的数据，该动作需要遍历索引（聚集，非聚集，堆）上的索引页面，是一种相对低效的操作。

查询：根据条件，逐步缩小范围的查询，是一种相对高效的查询。

假定：

表名：t_student列：ID 主键+聚集索引列：name 索引(包括note字段)列：age列：note

一， 聚集索引扫描

该表是一张聚集表，我们来看下面这个查询。这是一个查询索引数据的语句，这种查询语句只能是一页一页的扫描，从 C1，C2 页面一直扫描到 C6 页面。当然我们要尽量避免这种查询。

select * from t_student

接下来，我们来看下面的查询语句，这个查询语句是有 where 条件的查询，查询条件是 age=28，该查询如何执行呢？

age 字段上没有索引，也没有任何非聚集索引包含 age 字段，所以该查询也只能一页一页的扫描。

先扫描 C1 页面，看是否有符合条件 age=28 的数据，然后根据链表的顺序，依次查询 C2，C3，C4，C5，C6 页面，把符合条件的数据合并返回。如图上的绿色箭头。

select * from t_student where age=28

二，聚集索引查找

接下来，我们来看下面的查询。

这是一个包含 where 条件的查询，条件是 ID=100，ID 就是我们的聚集索引键。

所以，我们使用可以使用查询操作。

从根节点开始，在根节点查询 ID=100（有可能根节点的键值中没有 100，没关系，查询大于等于 100 的最小值的键值对应的指针）对应的指针所指向的页面。

所以，查询完毕根节点之后，我们就能确定符合我们条件的数据，在 B1，B2，B3 或者 B4，

假如在 B1，我们在使用 ID=100 的条件，在 B1 上查询，就能确定符合我们要求的数据在第三层的哪个页面，

假如在 C1 页面，我们就去 C1 页面查询 id=100 的数据

从 C1 页面查询出该数据（也可能没有该数据，也可能有多条符合条件的数据）返回客户端

如图，绿色交投所示。

select * from t_student where ID=100

小结，对比如下两个包含查询条件的查询语句

select * from t_student where age=28select * from t_student where ID=100

因为 age 上没有索引，所以需要扫描所有页面，才能得到结果。

因为 ID 上有索引，所以只需要查询少量页面，就能得到结果。

三，索引扫描

我们先看下面的图，该图可以分成两个部分，上面是聚集索引（就是包含所有列的数据），下面是非聚集索引（name 列上创建的索引，并且该索引上又包括了 note 列）。

大家注意右下方的箭头，这时这时索引中的指针指，他指向了真正的数据所在的位置。

索引叶子节点的每一条数据，都有一个指针，指向它代表的全部数据所在的位置（即聚集索引中叶子节点的某一行）

也就是说，对于非聚集索引叶子节点的某一行，一定有聚集索引上叶子节点的某一行与之对应。

其实，表中添加一行数据时（即聚集索引中叶子节点添加一行），非聚集索引的叶子节点也会添加一行

并且两个动作是事务性的，所以当创建的非聚集索引太多时，会影响数据的写入速度

接下来，我们来看下面两个查询，

select name from t_studentselect name,note from t_student where note='great job'

对于第一个查询，没有 where 条件，且只查询 name 列。

因为没有 where 条件，所以我们要扫描聚集索引叶子节点或者扫描非聚集索引叶子节点（包含 name 列），都能实现我们的需求。

实际中，会去扫描非聚集索引，因为扫描非聚集索引的效率更高。

为什么扫描非聚集索引的效率更高呢？

1，非聚集索引的页面节点上的数据量和聚集索引叶子节点的数据量是相同的

2，非聚集索引只包含两列（name 和 note），所以每条数据占用的空间更少

3，因为占用空间少，所以需要的页面就少，所以可能早更少的时间内扫描完毕

对于第二个查询，该查询包括了一个 where 条件，并且该字段上没有索引，需要返回的字段是 name 和 note。

看我们的条件，name 列的索引中是包括 note 列的，所以上面的第一个查询类似，也是使用非聚集索引扫描来完成此操作。

四，索引查找

我们在看下面的查询，有 where 条件，name='kate'， 需要返回时 name 和 note 列。

where 条件的列有非聚集索引，需要返回的两个字段，都包括在非聚集中，所以使用索引查找。

从非聚集索引的根节点开始，确定符合 name='kate'条件的数据在第二层（我们例子第二层即使叶子层，实际中，也有可能第三层或者更深才是叶子节点）的哪一个页面，

从该子页面查询，返回数据给客户端

select name,note from t_student where name='kate'

五，书签查找

接下来，我们来看下面的语句。

该语句有 where 条件，name='jim'，该列上有索引，我们可以使用索引查找。

该语句非要返回所有列（name 索引上，只包括了 name 和 note 列），所以我们要从聚集索引的叶子节点得到某一行的所有列的数据。

所以该查询中，非聚集索引查询完毕之后，还需要有一个根据指针回表的操作，回表拿到所有列的数据

select * from t_student where name='jim'

总结，这里主要从单张表查询方式入手，分析索引原理及优化。多张表查询时，会涉及到复杂一些的连接方式，主要有循环连接， merge 和 hash 三种方式。敬请期待。