复合索引与效率.docVIP

SQL Server的复合索引学习 概要 什么是单一索引,什么又是复合索引呢? 何时新建复合索引，复合索引又需要注意些什么呢？本篇文章主要是对网上一些讨论的总结。 一.概念 单一索引是指索引列为一列的情况,即新建索引的语句只实施在一列上。 用户可以在多个列上建立索引，这种索引叫做复合索引(组合索引)。复合索引的创建方法与创建单一索引的方法完全一样。但复合索引在数据库操作期间所需的开销更小，可以代替多个单一索引。当表的行数远远大于索引键的数目时，使用这种方式可以明显加快表的查询速度。 同时有两个概念叫做窄索引和宽索引，窄索引是指索引列为1-2列的索引,如果不特殊说明的话一般是指单一索引。宽索引也就是索引列超过2列的索引。 设计索引的一个重要原则就是能用窄索引不用宽索引，因为窄索引往往比组合索引更有效。拥有更多的窄索引，将给优化程序提供更多的选择余地，这通常有助于提高性能。 二.使用 创建索引 create index idx1 on table1(col1,col2,col3)? 查询 select * from table1 where col1= A and col2= B and col3 = C 这时候查询优化器，不在扫描表了，而是直接的从索引中拿数据，因为索引中有这些数据，这叫覆盖式查询，这样的查询速度非常快。?? 三.注意事项 1.何时是用复合索引 在where条件中字段用索引，如果用多字段就用复合索引。一般在select的字段不要建什么索引(如果是要查询select col1 ,col2, col3 from mytable,就不需要上面的索引了)。根据where条件建索引是极其重要的一个原则。注意不要过多用索引，否则对表更新的效率有很大的影响，因为在操作表的时候要化大量时间花在创建索引中. 2.对于复合索引，在查询使用时，最好将条件顺序按找索引的顺序，这样效率最高。如：? ? IDX1:create?? index?? idx1?? on?? table1(col2,col3,col5)? ? select?? *?? from?? table1?? where?? col2=A?? and?? col3=B?? and?? col5=D? 如果是select?? *?? from?? table1?? where?? col3=B?? and?? col2=A?? and?? col5=D 或者是select?? *?? from?? table1?? where?? col3=B将不会使用索引，或者效果不明显 3.复合索引会替代单一索引么? 很多人认为只要把任何字段加进聚集索引，就能提高查询速度，也有人感到迷惑：如果把复合的聚集索引字段分开查询，那么查询速度会减慢吗？带着这个问题，我们来看一下以下的查询速度（结果集都是25万条数据）：（日期列fariqi首先排在复合聚集索引的起始列，用户名neibuyonghu排在后列） IDX1:create?? index?? idx1?? on?? Tgongwen(fariqi,neibuyonghu)? （1）select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi2004-5-5 查询速度：2513毫秒 （2）select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi2004-5-5 and neibuyonghu=办公室 查询速度：2516毫秒 （3）select gid,fariqi,neibuyonghu,title from Tgongwen where neibuyonghu=办公室 查询速度：60280毫秒 从以上试验中，我们可以看到如果仅用聚集索引的起始列作为查询条件和同时用到复合聚集索引的全部列的查询速度是几乎一样的，甚至比用上全部的复合索引列还要略快（在查询结果集数目一样的情况下）；而如果仅用复合聚集索引的非起始列作为查询条件的话，这个索引是不起任何作用的。当然，语句1、2的查询速度一样是因为查询的条目数一样，如果复合索引的所有列都用上，而且查询结果少的话，这样就会形成“索引覆盖”，因而性能可以达到最优。同时，请记住：无论您是否经常使用聚合索引的其他列，但其前导列一定要是使用最频繁的列。 [参考: 查询优化及分页算法方案 /chiefsailor/archive/2007/05/28/1628339.aspx] 4.需要在同一列上同时建单一索引和复合索引么? 试验: sysbase?? 5.0?? 表table1?? 字段：col1,col2,col3? 试验步骤：? （1）建立索引idx1?? on?? col