《复合索引和效率.doc》由会员分享，可在线阅读，更多相关《复合索引和效率.doc（19页珍藏版）》请在金锄头文库上搜索。
1、复合索引和效率SQL Server的复合索引学习概要什么是单一索引,什么又是复合索引呢? 何时新建复合索引，复合索引又需要注意些什么呢？本篇文章主要是对网上一些讨论的总结。一.概念单一索引是指索引列为一列的情况,即新建索引的语句只实施在一列上。用户可以在多个列上建立索引，这种索引叫做复合索引(组合索引)。复合索引的创建方法与创建单一索引的方法完全一样。但复合索引在数据库操作期间所需的开销更小，可以代替多个单一索引。当表的行数远远大于索引键的数目时，使用这种方式可以明显加快表的查询速度。同时有两个概念叫做窄索引和宽索引，窄索引是指索引列为1-2列的索引,如果不特殊说明的话一般是指单一索引。宽索引
2、也就是索引列超过2列的索引。设计索引的一个重要原则就是能用窄索引不用宽索引，因为窄索引往往比组合索引更有效。拥有更多的窄索引，将给优化程序提供更多的选择余地，这通常有助于提高性能。二.使用创建索引create index idx1 on table1(col1,col2,col3)查询select * from table1 where col1= A and col2= B and col3 = C这时候查询优化器，不在扫描表了，而是直接的从索引中拿数据，因为索引中有这些数据，这叫覆盖式查询，这样的查询速度非常快。三.注意事项1.何时是用复合索引在where条件中字段用索引，如果用多字段就用
3、复合索引。一般在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如果是
4、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(fa
5、riqi,neibuyonghu) （1）select gid,fariqi,neibuyonghu,title from Tgongwenwhere fariqi2004-5-5查询速度：2513毫秒（2）select gid,fariqi,neibuyonghu,title from Tgongwenwhere fariqi2004-5-5 and neibuyonghu=办公室查询速度：2516毫秒（3）select gid,fariqi,neibuyonghu,title from Tgongwenwhere neibuyonghu=办公室查询速度：60280毫秒从以上试验中，我们可以
6、看到如果仅用聚集索引的起始列作为查询条件和同时用到复合聚集索引的全部列的查询速度是几乎一样的，甚至比用上全部的复合索引列还要略快（在查询结果集数目一样的情况下）；而如果仅用复合聚集索引的非起始列作为查询条件的话，这个索引是不起任何作用的。当然，语句1、2的查询速度一样是因为查询的条目数一样，如果复合索引的所有列都用上，而且查询结果少的话，这样就会形成“索引覆盖”，因而性能可以达到最优。同时，请记住：无论您是否经常使用聚合索引的其他列，但其前导列一定要是使用最频繁的列。参考: 查询优化及分页算法方案 http:/ sysbase 5.0 表table1 字段：col1,col2,col3试验步骤
7、：（1）建立索引idx1 on col1 执行select * from table1 where col1=A 使用idx1 执行select * from table1 where col1=A and col2=B 也使用idx1（2）删除索引idx1,然后建立idx2 on (col1,col2)复合索引 执行以上两个查询，也都使用idx2（3）如果两个索引idx1,idx2都存在 并不是 where col1=A用idx1；where col1=A and col2=B 用idx2。 其查询优化器使用其中一个以前常用索引。要么都用idx1,要么都用idx2.由此可见，（1）对一张表来
8、说，如果有一个复合索引 on (col1,col2)，就没有必要同时建立一个单索引 on col1。（2）如果查询条件需要，可以在已有单索引 on col1的情况下，添加复合索引on (col1,col2)，对于效率有一定的提高。（3）同时建立多字段(包含5、6个字段)的复合索引没有特别多的好处，相对而言，建立多个窄字段(仅包含一个，或顶多2个字段)的索引可以达到更好的效率和灵活性。5. 一定需要覆盖性查询么？通常最好不要采用一个强调完全覆盖查询的策略。如果Select子句中的所有列都被一个非群集索引覆盖，优化程序会识别出这一点，并提供很好的性能。不过，这通常会导致索引过宽，并会过度依赖于优化
9、程序使用该策略的可能性。通常，是用数量更多的窄索引，这对于大量查询来说可以提供更好的性能查询效率分析：子查询为确保消除重复值，必须为外部查询的每个结果都处理嵌套查询。在这种情况下可以考虑用联接查询来取代。如果要用子查询，那就用EXISTS替代IN、用NOT EXISTS替代NOT IN。因为EXISTS引入的子查询只是测试是否存在符合子查询中指定条件的行，效率较高。无论在哪种情况下,NOT IN都是最低效的。因为它对子查询中的表执行了一个全表遍历。建立合理的索引,避免扫描多余数据，避免表扫描！几百万条数据，照样几十毫秒完成查询.SQL语句效率1. SQL优化的原则是：将一次操作需要读取的BLO
10、CK数减到最低,即在最短的时间达到最大的数据吞吐量。调整不良SQL通常可以从以下几点切入：检查不良的SQL，考虑其写法是否还有可优化内容检查子查询 考虑SQL子查询是否可以用简单连接的方式进行重新书写检查优化索引的使用考虑数据库的优化器2. 避免出现SELECT * FROM table 语句，要明确查出的字段。3. 在一个SQL语句中，如果一个where条件过滤的数据库记录越多，定位越准确，则该where条件越应该前移。4. 查询时尽可能使用索引覆盖。即对SELECT的字段建立复合索引，这样查询时只进行索引扫描，不读取数据块。5. 在判断有无符合条件的记录时建议不要用SELECT COUNT
11、 （*）和select top 1 语句。6. 使用内层限定原则，在拼写SQL语句时，将查询条件分解、分类，并尽量在SQL语句的最里层进行限定，以减少数据的处理量。7. 应绝对避免在order by子句中使用表达式。8. 如果需要从关联表读数据，关联的表一般不要超过7个。9. 小心使用 IN 和 OR，需要注意In集合中的数据量。建议集合中的数据不超过200个。10. 用 代替，用=代替，用1000)。应该用如下语句代替：select name from customer inner join order on customer.customer_id=order.customer_id wh
12、ere order.money100。15. 在WHERE 子句中，避免对列的四则运算，特别是where 条件的左边，严禁使用运算与函数对列进行处理。比如有些地方 substring 可以用like代替。16. 如果在语句中有not in（in）操作，应考虑用not exist*（*ists）来重写,最好的办法是使用外连接实现。17. 对一个业务过程的处理，应该使事物的开始与结束之间的时间间隔越短越好，原则上做到数据库的读操作在前面完成，数据库写操作在后面完成，避免交叉。18. 请小心不要对过多的列使用列函数和order by,group by等，谨慎使用disti软件开发t。19. 用union all 代替 union，数据库执行union操作，首先先分别执行union两端的查询，将其放在临时表中，然后在对其进行排序，过滤重复的记录。当已知的业务逻辑决定query A和query B中不会有重复记录时，应该用union all代替union，以提高查询效率个人经验总结：处理海量数据的经验和技巧 发布时间：2008.08.05 08:54 来源：赛迪网 作者：安娜 【赛迪网IT技术报道】在实际
