SQL Server中按照数据的组织方式,可以分为:聚集索引、非聚集索引。
共同点是,本质上都是B树索引。
(1)聚集索引
在默认的情况下,当在创建表时,指定了主键字段(primary key),那么sql server会自动给这个字段创建聚集索引。
聚集索引的特点是,包含了 索引 + 数据。
也就是说 在这个B树索引中,根节点、分支节点都是存的索引,而到了叶子节点,存的是数据。
比如,A表,字段包括:ID(主键),name,age,memo,birth 。
聚集索引中的根节点、分之节点只会存储 ID字段的值,而到了叶子节点,会存 ID,name,age,memo,birth 所有这些字段的值。
这种存储方式,好处是不用回表(回表在sql server的执行计划中表现为 bookmark lookup、key lookup )。
坏处是因为包含了所有数据,所以索引的体积会很大,如果扫描整个索引,速度会很慢。
由于一般表,主键字段很多都是代理键,也就是没有实际意义的数字(比如:自增列),所以给主键字段创建聚集索引时,并不能充分发挥聚集索引的优点。
建议把查询语句中经常放到where里做条件的字段,创建聚集索引,而主键则创建唯一索引(要给字段设置not null)。
(2)非聚集索引
非聚集索引就是普通的B树索引(可能是唯一索引),在根节点、分支、叶子节点,都存的是创建索引的字段值,不会包含任何表中的明细数据。
好处是索引体积小,占用磁盘少,就算是对整个索引进行扫描操作,速度也是很快的。
坏处是,如果需要访问索引之外的字段,就有个回表操作,而回表操作的开销,会根据索引中数据的顺序 和 表中数据的顺序,之间对应关系的杂乱程度,有所不同。
比如:表中的数据,是按照业务日期字段顺序存放的,而索引是按照发生业务的客户门店存放的,由于两者存放数据的顺序不一致,会导致回表的开销很大。