复合索引和效率.doc

复合索引和效率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 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毫秒从以上试验中，我们可以看到如果仅用聚集索引的起始列作为查询条件和同时用到复合聚集索引的全部列的查询速度是几乎一样的，甚至比用上全部的复合索引列还要略快（在查询结果集数目一样的情况下）；而如果仅用复合聚集索引的非起始列作为查询条件的话，这个索引是不起任何作用的。当然，语句1、2的查询速度一样是因为查询的条目数一样，如果复合索引的所有列都用上，而且查询结果少的话，这样就会形成“索引覆盖”，因而性能可以达到最优。同时，请记住：无论您是否经常使用聚合索引的其他列，但其前导列一定要是使用最频繁的列。参考: 查询优化及分页算法方案 /chiefsailor/archive/2007/05/28/1628339.aspx4.需要在同一列上同时建单一索引和复合索引么?试验: sysbase 5.0 表table1 字段：col1,col2,col3试验步骤：（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）对一张表来说，如果有一个复合索引 on (col1,col2)，就没有必要同时建立一个单索引 on col1。（2）如果查询条件需要，可以在已有单索引 on col1的情况下，添加复合索引on (col1,col2)，对于效率有一定的提高。（3）同时建立多字段(包含5、6个字段)的复合索引没有特别多的好处，相对而言，建立多个窄字段(仅包含一个，或顶多2个字段)的索引可以达到更好的效率和灵活性。5. 一定需要覆盖性查询么？通常最好不要采用一个强调完全覆盖查询的策略。如果Select子句中的所有列都被一个非群集索引覆盖，优化程序会识别出这一点，并提供很好的性能。不过，这通常会导致索引过宽，并会过度依赖于优化程序使用该策略的可能性。通常，是用数量更多的窄索引，这对于大量查询来说可以提供更好的性能查询效率分析：子查询为确保消除重复值，必须为外部查询的每个结果都处理嵌套查询。在这种情况下可以考虑用联接查询来取代。如果要用子查询，那就用EXISTS替代IN、用NOT EXISTS替代NOT IN。因为EXISTS引入的子查询只是测试是否存在符合子查询中指定条件的行，效率较高。无论在哪种情况下,NOT IN都是最低效的。因为它对子查询中的表执行了一个全表遍历。建立合理的索引,避免扫描多余数据，避免表扫描！几百万条数据，照样几十毫秒完成查询.SQL语句效率1. SQL优化的原则是：将一次操作需要读取的BLOCK数减到最低,即在最短的时间达到最大的数据吞吐量。调整不良SQL通常可以从以下几点切入：检查不良的SQL，考虑其写法是否还有可优化内容检查子查询 考虑SQL子查询是否可以用简单连接的方式进行重新书写检查优化索引的使用考虑数据库的优化器2. 避免出现SELECT * FROM table 语句，要明确查出的字段。3. 在一个SQL语句中，如果一个where条件过滤的数据库记录越多，定位越准确，则该where条件越应该前移。4. 查询时尽可能使用索引覆盖。即对SELECT的字段建立复合索引，这样查询时只进行索引扫描，不读取数据块。5. 在判断有无符合条件的记录时建议不要用SELECT COUNT （*）和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 where 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技术报道】在实际的工作环境下，许多人会遇到海量数据这个复杂而艰巨的问题，它的主要难点有以下几个方面：一、数据量过大，数据中什么情况都可能存在。如果说有10条数据，那么大不了每条去逐一检查，人为处理，如果有上百条数据，也可 以考虑，如果数据上到千万级别，甚至过亿，那不是手工能解决的了，必须通过工具或者程序进行处理，尤其海量的数据中，什么情况都可能存在，例如，数据中某 处格式出了问题，尤其在程序处理时，前面还能正常处理，突然到了某个地方问题出现了，程序终止了。二、软硬件要求高，系统资源占用率高。对海量的数据进行处理，除了好的方法，最重要的就是合理使用工具，合理分配系统资源。一般情况，如果处理的数据过TB级，小型机是要考虑的，普通的机子如果有好的方法可以考虑，不过也必须加大CPU和内存，就象面对着千军万马，光有勇气没有一兵一卒是很难取胜的。三、要求很高的处理方法和技巧。这也是本文的写作目的所在，好的处理方法是一位工程师长期工作经验的积累，也是个人的经验的总结。没有通用的处理方法，但有通用的原理和规则。下面我们来详细介绍一下处理海量数据的经验和技巧：一、选用优秀的数据库工具现在的数据库工具厂家比较多，对海量数据的处理对所使用的数据库工具要求比较高，一 般使用Oracle或者DB2，微软公司最近发布的SQL Server 2005性能也不错。另外在BI领域：数据库，数据仓库，多维数据库，数据挖掘等相关工具也要进行选择，象好的ETL工具和好的OLAP工具都十分必要， 例如Informatic，Eassbase等。笔者在实际数据分析项目中，对每天6000万条的日志数据进行处理，使用SQL Server 2000需要花费6小时，而使用SQL Server 2005则只需要花费3小时。二、编写优良的程序代码处理数据离不开优秀的程序代码，尤其在进行复杂数据处理时，必须使用程序。好的程序代码对数据的处理至关重要，这不仅仅是数据处理准确度的问题，更是数据处理效率的问题。良好的程序代码应该包含好的算法，包含好的处理流程，包含好的效率，包含好的异常处理机制等。三、对海量数据进行分区操作对海量数据进行分区操作十分必要，例如针对按年份存取的数据，我们可以按年进行分 区，不同的数据库有不同的分区方式，不过处理机制大体相同。例如SQL Server的数据库分区是将不同的数据存于不同的文件组下，而不同的文件组存于不同的磁盘分区下，这样将数据分散开，减小磁盘I/O，减小了系统负荷， 而且还可以将日志，索引等放于不同的分区下。四、建立广泛的索引对海量的数据处理，对大表建立索引是必行的，建立索引要考虑到具体情况，例如针对大 表的分组、排序等字段，都要建立相应索引，一般还可以建立复合索引，对经常插入的表则建立索引时要小心，笔者在处理数据时，曾经在一个ETL流程中，当插 入表时，首先删除索引，然后插入完毕，建立索引，并实施聚合操作，聚合完成后，再次插入前还是删除索引，所以索引要用到好的时机，索引的填充因子和聚集、 非聚集索引都要考虑。五、建立缓存机制当数据量增加时，一般的处理工具都要考虑到缓存问题。缓存大小设置的好差也关系到数据处理的成败，例如，笔者在处理2亿条数据聚合操作时，缓存设置为100000条/Buffer，这对于这个级别的数据量是可行的。六、加大虚拟内存如果系统资源有限，内存提示不足，则可以靠增加虚拟内存来解决。笔者在实际项目中曾 经遇到针对18亿条的数据进行处理，内存为1GB，1个P4 2.4G的CPU，对这么大的数据量进行聚合操作是有问题的，提示内存不足，那么采用了加大虚拟内存的方法来解决，在6块磁盘分区上分别建立了6个 4096M的磁盘分区，用于虚拟内存，这样虚拟的内存则增加为 4096*6 + 1024 = 25600 M，解决了数据处理中的内存不足问题。七、分批处理海量数据处理难因为数据量大，那么解决海量数据处理难的问题其中一个技巧是减少数据 量。可以对海量数据分批处理，然后处理后的数据再进行合并操作，这样逐个击破，有利于小数据量的处理，不至于面对大数据量带来的问题，不过这种方法也要因 时因势进行，如果不允许拆分数据，还需要另想办法。不过一般的数据按天、按月、按年等存储的，都可以采用先分后合的方法，对数据进行分开处理。八、使用临时表和中间表数据量增加时，处理中要考虑提前汇总。这样做的目的是化整为零，大表变小表，分块处 理完成后，再利用一定的规则进行合并，处理过程中的临时表的使用和中间结果的保存都非常重要，如果对于超海量的数据，大表处理不了，只能拆分为多个小表。 如果处理过程中需要多步汇总操作，可按汇总步骤一步步来，不要一条语句完成，一口气吃掉一个胖子。九、优化查询SQL语句在对海量数据进行查询处理过程中，查询的SQL语句的性能对查询效率的影响是非常大 的，编写高效优良的SQL脚本和存储过程是数据库工作人员的职责，也是检验数据库工作人员水平的一个标准，在对SQL语句的编写过程中，例如减少关联，少 用或不用游标，设计好高效的数据库表结构等都十分必要。笔者在工作中试着对1亿行的数据使用游标，运行3个小时没有出结果，这是一定要改用程序处理了。十、使用文本格式进行处理对一般的数据处理可以使用数据库，如果对复杂的数据处理，必须借助程序，那么在程序 操作数据库和程序操作文本之间选择，是一定要选择程序操作文本的，原因为：程序操作文本速度快；对文本进行处理不容易出错；文本的存储不受限制等。例如一 般的海量的网络日志都是文本格式或者csv格式（文本格式），对它进行处理牵扯到数据清洗，是要利用程序进行处理的，而不建议导入数据库再做清洗。十一、定制强大的清洗规则和出错处理机制海量数据中存在着不一致性，极有可能出现某处的瑕疵。例如，同样的数据中的时间字段，有的可能为非标准的时间，出现的原因可能为应用程序的错误，系统的错误等，这是在进行数据处理时，必须制定强大的数据清洗规则和出错处理机制。十二、建立视图或者物化视图视图中的数据来源于基表，对海量数据的处理，可以将数据按一定的规则分散到各个基表中，查询或处理过程中可以基于视图进行，这样分散了磁盘I/O，正如10根绳子吊着一根柱子和一根吊着一根柱子的区别。十三、避免使用32位机子（极端情况）目前的计算机很多都是32位的，那么编写的程序对内存的需要便受限制，而很多的海量数据处理是必须大量消耗内存的，这便要求更好性能的机子，其中对位数的限制也十分重要。十四、考虑操作系统问题海量数据处理过程中，除了对数据库，处理程序等要求比较高以外，对操作系统的要求也放到了重要的位置，一般是必须使用服务器的，而且对系统的安全性和稳定性等要求也比较高。尤其对操作系统自身的缓存机制，临时空间的处理等问题都需要综合考虑。十五、使用数据仓库和多维数据库存储数据量加大是一定要考虑OLAP的，传统的报表可能5、6个小时出来结果，而基于Cube的查询可能只需要几分钟，因此处理海量数据的利器是OLAP多维分析，即建立数据仓库，建立多维数据集，基于多维数据集进行报表展现和数据挖掘等。十六、使用采样数据，进行数据挖掘基于海量数据的数据挖掘正在逐步兴起，面对着超海量的数据，一般的挖掘软件或算法往 往采用数据抽样的方式进行处理，这样的误差不会很高，大大提高了处理效率和处理的成功率。一般采样时要注意数据的完整性和，防止过大的偏差。笔者曾经对1 亿2千万行的表数据进行采样，抽取出400万行，经测试软件测试处理的误差为千分之五，客户可以接受。还有一些方法，需要在不同的情况和场合下运用，例如使用代理键等操作，这样的好处是加快了聚合时间，因为对数值型的聚合比对字符型的聚合快得多。类似的情况需要针对不同的需求进行处理。海量数据是发展趋势，对数据分析和挖掘也越来越重要，从海量数据中提取有用信息重要而紧迫，这便要求处理要准确，精度要高，而且处理时间要短，得到有价值信息要快，所以，对海量数据的研究很有前途，也很值得进行广泛深入的研究。 （责任编辑：卢兆多年以前提高分页查询效率的一个实例2004年数据库查询优化实例情况：sqlserver 2000，资源表，记录近30万条。资源有一个整数的id字段，自动增量，但是资源可以被删除。所以，id并不连续。用.netSqlDataAdapter进行分页查询，由于Fill（）方法的机制问题，使得在DataGrid里面进行翻页的时候延时非常大。数据库服务器和web服务器同在局域网，延时大约有3-4秒。经过分析，对此进行优化。首先得知数据库的select count(id) from resource where idn这样的查询速度非常快。所以利用这一点进行优化，以达到准确翻页假设，记录的id是连续的，那么如果每页10条，那么我们要得到第12页，就需要第十一页最后一个记录的id。11 x 10 = 110，这个记录id就是110。所以第12页这么得到，selecttop 10 * from resource where id110，这条语句的查询效率是非常高的。但是如果id不连续，怎么办呢？这里进行估算。因为cpu进行数值运算的耗时比起数据库查询可以忽略不计。假设，id是连续的，那么先统计一个数字。如果id连续，那么第m页需要的前一个记录id可以这么得到：(m 1) * 10。所以先统计：Select count(id) from resource where id=(m-1)*10假设统计得到的值为p，p=(m-1)*10。如果p(m-1)*10查询完毕，取最后的10条记录就可以了。这种方法对于实际的应用已经足够了。因为，一个几十万条记录的表一般是不经常删除操作的。如果有极端情况，那么可以连续估算2-3次，就可以比较准确了。也就是(m-1)*10 p很大的情况下，为了避免查询出太多的记录，进行二次估算。既然p太小了，再次假设id为(m-1)*10后的记录号是连续的。我们需要的id是(m-1)*10 + (m-1)*10 p) = 2*(m-1)*10 p。查询：Select count(id) from resource where id2*(m-1)*10 p取查询结果的最后10条记录。这种优化可以用存储过程来实现，获得更好的效果。经过这样的优化以后，查询速度从原来的超过3秒多变成了瞬间，不足1秒，当时尚未使用存储过程。这是在 1.1的时候，利用DataGrid绑定数据的时候解决sqlserver 2000查询效率的一个实例。在此提供出来只是提供一个思路，方案也许有用，也许已经过时了。Tag标签: sql server,数据库优化,,DataAdapter优化,分页查询优化posted on 2008-07-07 22:19 信息加油站义工 阅读(2024) 评论(13) 编辑 收藏 网摘 所属分类: 8. 信息技术 、5. 数据结构与算法 、1. dotnet 、7. 数据库 、9. 程序设点滴评论#1楼 2008-07-07 23:32 谦虚的天下利用估算，妙！ 回复引用查看#2楼 2008-07-08 07:23 金色海洋（jyk）大哥，30w就估算了，服了。我的客户是绝对不会接受“估算”的。我这里有一个精确定位的方法，而且一点也不比你的这个方法慢。公式：declare col intselect top PageSize * (PageIndex-1)+1 col = 排序字段from 表名|视图名 where 查询条件 order by 排序字段 asc|descselect top PageSize 需要显示的字段from 表名|视图名where 排序字段 = col and 查询条件 order by 排序字段 asc|desc第二个select 和你的思路是一样的，区别在于第一个select ，就是在id不连续的时候的定位方法，例如计算排在第30位的记录的 ID值。select top 30 col = ID from tableName order by ID.select col这时这个 col 的值是什么？就是你需要的那个精确的值。换成Max的方式也是一样的。建议多研究一下索引。http:/ww