我对数据库索引的理解

理解扫描和查找

以新华字典为例，找一个字，如果用扫描的方式，就是一页一页地翻过去。查找就是知道这个字的拼音，直接跳跃式的翻到相应的页面（翻几次）。

或者不知道拼音就在前面的目录里面按部首、笔画查找，然后再根据提示的页码翻到相应的页数。

--- 聚集索引查找 – 非聚集索引查找(可能存在书签查找)

 

 

理解聚集索引和非聚集索引

没有聚集索引的表是一个堆表，有聚集索引的表是一个聚集索引表。因为聚集索引和数据是混在一起的，数据是聚集索引的一部分。索引是一个平衡树结构（根结点、中间结点和叶子结点）。

如果是聚集索引表，数据就放在聚积索引的叶子结点中。

 

非聚集索引查找和书签查找

因为一个表只有能创建一个聚集索引，而实际情况中作为条件的字段很多。所以非聚集索引查找出现的次数更多。非聚集索引查找发生时，往往伴随书签操作。

这是很正常的。非聚集索引中包含指向记录的指针，也就是书签。当返回结果中包含其它列时，书签查找就出现了。

只有在使用非聚集索引进行数据查找时才会产生书签查找。如果所选择的列或查询条件中的列只部分包含在使用的非聚集索引和聚集索引中时，

就需要一个查找（lookup）来 检索其他字段来满足请求。对一个有聚簇索引的表来说是一个键查找（key lookup），对一个堆表来说是一个RID查找（RID lookup）。统称书签查找。

 

索引跟表数据一样，也是占用磁盘空间

建立聚集索引需要至少相当于源表120%的附加空间，以存放源表的副本和索引中间页！

------------------------------------------- 这是错误的

 

更新数据时，需要额外的维护索引操作

 

这就是常说的为什么索引会使得修改变得更慢。

 

增加一条新记录或删除一条记录时会引起所有索引的维护操作。

 

修改任何字段时，都会引起聚集索引的维护操作。修改字段上存在非积聚索引时，才会引起非聚集索引的维护操作。

 

修改操作同样会用到索引

索引创建的目的就是优化查询。修改操作也同样用到索引。因为修改操作有个查找的过程，索引可以缩短查找的时间，但索引会增加修改的时间。

如果表数据量大的情况下，适合创建索引。

 

 

 

语句执行过程中，每个表只能用到一个索引

    曾经就天真地创建好几个索引，以为一次查询都用得上！！

 

优化引擎对索引的控制

一个表有多个索引，用哪个索引、用索引查找还是索引扫描，优化引擎自己会选择一个更优的、代价更低的来执行语句。

我们要做的其实是创建必要的索引，以提供优化引擎选择。相反，如果你没有任何索引，优化引擎只能选择效率最差的表扫描或聚集索引扫描来执行所有语句。

 

系统在运行一段时间后，哪些表的字段缺乏相应的索引、哪些表的索引是多余的，可以根据sql server提供的视图信息权衡判断。

优化顾问、执行计划(sql2008)和管理视图提示创建的相应索引，只能做个参考。

 

聚集索引健值类型是INT跟VARCHAR的比较

小类型的字段作为聚集索引时，索引存储空间更小。

小类型的字段作为聚集索引时，索引检索更快。

字段设计避免太大，主键尽可能的短，能用tinyint的就不要用int，能用char(5)的就不要用成varchar(50)，能用varchar的就不要用nvarchar。这在大数量的时候就越能体现优势。

 

聚积索引没有建立在表的顺序列上，该表容易发生页分裂(导致索引碎片增加过快)。

 

表扫描或索引扫描

表扫描、聚积索引扫描和索引扫描都会引起整个表锁定。

即共享锁放置在整张表上，这时候不允许修改任何已有数据（但可以插入新的数据）同样插入新记录时放置的排它锁会阻塞扫描整张表的读取操作。(但insert 操作只会锁定新insert的行)

例如：当插入的新值是最大的时候，事务未提交时。

SELECT COUNT(1)  FROM dbo.t_test; -- 阻塞

SELECT MIN(id)   FROM dbo.t_test; -- 不阻塞

SELECT MAX(id)   FROM dbo.t_test; -- 阻塞