板砖大叔(Q13:请教 mssql 数据如何合理创建索引,是分开建还是合在一起建


石沫(A13:我曾经整理的关于索引的设计与规范,可以供你参考:  
———————————————————————–  
索引设计与规范  
1.1   使用索引  
SQL SERVER没有索引也可以检索数据,只不过检索数据时扫描这个表而异。存储数据的目的,绝大多数都是为了再次使用,而一般数据检索都是带条件的检索,数据查询在数据库操作中会占用较大的比例,提高查询的效率往往意味着整个数据库性能的提升。索引是特定列的有序集合。索引使用B-树结构,最小优化了定位所需要的键值的访问页面量,包含聚集索引和非聚集索引两大类。聚集索引与数据存放在一起,它决定表中数据存储的物理顺序,其叶子节点为数据行。  
1.2   聚集索引  
1.2.1   关于聚集索引  
没聚集索引的表叫堆。堆是一种没有加工的数据,以行标示符作为指向数据存储位置的指针,数据没有顺序。聚集索引的叶子页面和表的数据页面相同,因此表行物理上按照聚集索引列排序,表数据的物理顺序只有一种,所以一个表只有一个聚集索引。  
1.2.2   与非聚集索引关系  
非聚集索引的一个索引行包含指向表对应行的指针,这个指针称为行定位器,行定位器的值取决于数据页保存为堆还是被聚集。若是堆,行定位器指向的堆中数据行的行号指针,若是聚集索引表,行定位器是聚集索引键值。  
1.2.3   设计聚集索引注意事项  
    首先创建聚集索引  
    聚集索引上的列需要足够短  
    一步重建索引,不要使用先DROPCREATE,可使用DROP_EXISTING  
    检索一定范围和预先排序数据时使用,因为聚集索引的叶子与数据页面相同,索引顺序也是数据物理顺序,读取数据时,磁头是按照顺序读取,而不是随机定位读取数据。  
    在频繁更新的列上不要设计聚集索引,他将导致所有的非聚集所有的更新,阻塞非聚集索引的查询  
    不要使用太长的关键字,因为非聚集索引实际包含了聚集索引值  
    不要在太多并发度高的顺序插入,这将导致页面分割,设置合理的填充因子是个不错的选择  
1.3   非聚集索引  
1.3.1   关于非聚集索引  
非聚集索引不影响表页面中数据的顺序,其叶子页面和表的数据页面时分离的,需要一个行定位器来导航数据,在将聚集索引时已经有说明,非聚集索引在读取少量数据行时特别有效。非聚集索引所有可以有多个。同时非聚集有很多其他衍生出来的索引类型,比如覆盖索引,过滤索引等。  
1.3.2
   
设计非聚集索引  
    频繁更新的列,不适合做聚集索引,但可以做非聚集索引  
    宽关键字,例如很宽的一列或者一组列,不适合做聚集索引的列可作非聚集索引列  
    检索大量的行不宜做非聚集索引,但是可以使用覆盖索引来消除这种影响  
1.3.3   优化书签查找  
书签会访问索引之外的数据,在堆表,书签查找会根据RID号去访问数据,若是聚集索引表,一般根据聚集索引去查找。在查询数据时,要分两个部分来完成,增加了读取数据的开销,增加了CPU的压力。在大表中,索引页面和数据页面一般不会临近,若数据只存在磁盘,产生直接随机从磁盘读取,这导致更多的消耗。因此,根据实际需要优化书签查找。解决书签查找有如下方法:  
    使用聚集索引避免书签查找  
    使用覆盖索引避免书签查找  
    使用索引连接避免数据查找  
1.4   聚集与非聚集之比较  
1.4.1   检索的数据行  
一般地,检索数据量大的一般使用聚集索引,因为聚集索引的叶子页面与数据页面在相同。相反,检索少量的数据可能非聚集索引更有利,但注意书签查找消耗资源的力度,不过可考虑覆盖索引解决这个问题  
1.4.2   数据是否排序  
如果数据需要预先排序,需要使用聚集索引,若不需要预先排序就那就选择聚集索引。  
1.4.3   索引键的宽度  
索引键如果太宽,不仅会影响数据查询性能,还影响非聚集索引,因此,若索引键比较小,可以作为聚集索引,如果索引键够大,考虑非聚集索引,如果很大的话,可以用INCLUDE创建覆盖索引。  
1.4.4   列更新的频度  
列更新频率高的话,应该避免考虑所用非聚集索引,否则可考虑聚集索引。  
1.4.5   书签查找开销  
如果书签查找开销较大,应该考虑聚集索引,否则可使用非聚集索引,更佳是使用覆盖索引,不过得根据具体的查询语句而看。  
1.5   覆盖索引  
覆盖索引可显著减少查询的逻辑读次数,使用INCLUDE语句添加列的方式更容易实现,他不仅减小索引中索引列的数据,还可以减少索引键的大小,原因是包含列只保存在索引的叶子级别上,而不是索引的叶子页面。覆盖索引充当一个伪的聚集索引。覆盖索引还能够有效的减少阻塞和死锁的发生,与聚集索引类似,因为聚集索引值发生一次锁,非覆盖索引可能发生两次,一次锁数据,一次锁索引,以确保数据的一致性。覆盖索引相当于数据的一个拷贝,与数据页面隔离,因此也只发生一次锁。  
1.6   索引交叉  
如果一个表有多个索引,那么可以拥有多个索引来执行一个查询,根据每个索引检索小的结果集,然后就将子结果集做一个交叉,得到满足条件的那些数据行。这种技术可以解决覆盖索引中没有包含的数据。  
1.7   索引连接  
几乎是跟索引交叉类似,是一个衍生品种。他将覆盖索引应用到交叉索引。如果没有单个覆盖索引查询的索引而多个索引一起覆盖查询,SQL SERVER可以使用索引连接来完全满足查询而不需要查询基础表。  
1.8   过滤索引  
用来在可能没有好的选择性的一个或者多个列上创建一个高选择性的关键字组。例如在处理NULL问题比较有效,创建索引时,可以像写T-SQL语句一样加个WHERE条件,以排除某部分数据而检索。  
1.9   索引视图  
索引视图在OLAP系统上可能有胜算,在OLTP会产生过大的开销和不可操作性,比如索引视图要求引用当前数据库的表。索引视图需要绑定基础表的架构,索引视图要求企业版,这些限制导致不可操作性。  
1.10   索引设计建议  
1.10.1   检查WHERE字句和连接条件列  
检查WHERE条件列的可选择性和数据密度,根据条件创建索引。一般地,连接条件上应当考虑创建索引,这个涉及到连接技术,暂时不说明。  
1.10.2   使用窄的索引  
窄的索引有可减少IO开销,读取更少量的数据页。并且缓存更少的索引页面,减少内存中索引页面的逻辑读取大小。当然,磁盘空间也会相应地减少。  
1.10.3   检查列的唯一性  
数据分布比较集中的列,种类比较少的列上创建索引的有效性比较差,如果性别只有男女之分,最多还有个UNKNOWN,单独在上面创建索引可能效果不好,但是他们可以为覆盖索引做出贡献。  
1.10.4   检查列的数据类型  
索引的数据类型是很重要的,在整数类型上创建的索引比在字符类型上创建索引更有效。同一类型,在数据长度较小的类型上创建又比在长度较长的类型上更有效。  
1.10.5   考虑列的顺序  
对于包含多个列的索引,列顺序很重要。索引键值在索引上的第一上排序,然后在前一列的每个值的下一列做子排序,符合索引的第一列通常为该索引的前沿。同时要考虑列的唯一性,列宽度,列的数据类型来做权衡。  
1.10.6   考虑索引的类型  

使用索引类型前面已经有较多的介绍,怎么选择已经给出。不再累述。

发表评论

电子邮件地址不会被公开。 必填项已用*标注