一、数据库结构的设计 如果不能设计一个合理的数据库模型,不仅会增加客户端和服务器段程序的编程和维护的难度,而且 将会影响系统实际运行的性能。所以,在一个系统开始实施之前,完备的数据库模型的设计是必须的。 在一个系统分析、设计阶段,因为数据量较小,负荷较低。我们往往只注意到功能的实现,而很难注 意到性能的薄弱之处,等到系统投入实际运行一段时间后,才发现系统的性能在降低,这时再来考虑提高 系统性能则要花费更多的人力物力,而整个系统也不可避免的形成了一个打补丁工程。 所以在考虑整个系统的流程的时候,我们必须要考虑,在高并发大数据量的访问情况下,我们的系统 会不会出现极端的情况。(例如:对外统计系统在7月16日出现的数据异常的情况,并发大数据量的的访 问造成,数据库的响应时间不能跟上数据刷新的速度造成。具体情况是:在日期临界时(00:00:00), 判断数据库中是否有当前日期的记录,没有则插入一条当前日期的记录。在低并发访问的情况下,不会发 生问题,但是当日期临界时的访问量相当大的时候,在做这一判断的时候,会出现多次条件成立,则数据 库里会被插入多条当前日期的记录,从而造成数据错误。),数据库的模型确定下来之后,我们有必要做 一个系统内数据流向图,分析可能出现的瓶颈。 为了保证数据库的一致性和完整性,在逻辑设计的时候往往会设计过多的表间关联,尽可能的降低数 据的冗余。(例如用户表的地区,我们可以把地区另外存放到一个地区表中)如果数据冗余低,数据的完 整性容易得到保证,提高了数据吞吐速度,保证了数据的完整性,清楚地表达数据元素之间的关系。而对 于多表之间的关联查询(尤其是大数据表)时,其性能将会降低,同时也提高了客户端程序的编程难度, 因此,物理设计需折衷考虑,根据业务规则,确定对关联表的数据量大小、数据项的访问频度,对此类数 据表频繁的关联查询应适当提高数据冗余设计但增加了表间连接查询的操作,也使得程序的变得复杂,为 了提高系统的响应时间,合理的数据冗余也是必要的。设计人员在设计阶段应根据系统操作的类型、频度 加以均衡考虑。 映射关系丢失(******************)。 原来的表格必须可以通过由它分离出去的表格重新构建。使用这个规定的好处是,你可以确保不会在 分离的表格中引入多余的列,所有你创建的表格结构都与它们的实际需要一样大。应用这条规定是一个好 习惯,不过除非你要处理一个非常大型的数据,否则你将不需要用到它。(例如一个通行证系统,我可以 将USERID,USERNAME,USERPASSWORD,单独出来作个表,再把USERID作为其他表的外键) 表的设计具体注意的问题: 1、数据行的长度不要超过8020字节,如果超过这个长度的话在物理页中这条数据会占用两行从而造 成存储碎片,降低查询效率。 接的性能,并会增加存储开销。这是因为引擎在处理查询和连接回逐个比较字符串中每一个字符,而对于 数字型而言只需要比较一次就够了。 3、对于不可变字符类型char和可变字符类型varchar 都是8000字节,char查询快,但是耗存储空间, varchar查询相对慢一些但是节省存储空间。在设计字段的时候可以灵活选择,例如用户名、密码等长度 变化不大的字段可以选择CHAR,对于评论等长度变化大的字段可以选择VARCHAR。 4、字段的长度在最大限度的满足可能的需要的前提下,应该尽可能的设得短一些,这样可以提高查 询的效率,而且在建立索引的时候也可以减少资源的消耗。
保证在实现功能的基础上,尽量减少对数据库的访问次数;通过搜索参数,尽量减少对表的访问行数,最 小化结果集,从而减轻网络负担;能够分开的操作尽量分开处理,提高每次的响应速度;在数据窗口使用 SQL时,尽量把使用的索引放在选择的首列;算法的结构尽量简单;在查询时,不要过多地使用通配符如 SELECT * FROM T1语句,要用到几列就选择几列如:SELECT COL1,COL2 FROM T1;在可能的情况下尽量限 制尽量结果集行数如:SELECT TOP 300 COL1,COL2,COL3 FROM T1,因为某些情况下用户是不需要那么多的 数据的。 找出符合条件的记录。在数据量比较小的情况下,也许看不出明显的差别,但是当数据量大的情况下,这 种情况就是极为糟糕的了。 一样,如果tID是一个聚合索引,那么后一句仅仅从表的10000条以后的记录中查找就行了;而前一句则要 先从全表中查找看有几个name='zhangsan'的,而后再根据限制条件条件tID>10000来提出查询结果。 的搜索条件并确定哪个索引能缩小表扫描的搜索空间,也就是说,它能实现自动优化。虽然查询优化器可 以根据where子句自动的进行查询优化,但有时查询优化器就会不按照您的本意进行快速查询。 可以被用作一个扫描参数(SARG),那么就称之为可优化的,并且可以利用索引快速获得所需数据。 个以上条件的AND连接。形式如下: 判断它是否满足WHERE子句中的所有条件。所以一个索引对于不满足SARG形式的表达式来说是无用的。 具体要注意的: 1.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描 ,如: 2.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。优化器将 无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。 3.应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如 : 4.in 和 not in 也要慎用,因为IN会使系统无法使用索引,而只能直接搜索表中的数据。如: 5.尽量避免在索引过的字符数据中,使用非打头字母搜索。这也使得引擎无法利用索引。 作来完成任务。而第三个查询能够使用索引来加快操作。 6.必要时强制查询优化器使用某个索引,如在 where 子句中使用参数,也会导致全表扫描。因为SQL只有 在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择 。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语 句将进行全表扫描: 7.应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如 : SELECT * FROM RECORD WHERE SUBSTRING(CARD_NO,1,4)=’5378’ SELECT member_number, first_name, last_name FROM members 等号右边。 8.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如: 9.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使 用索引。 10.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条 件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致 。 11.很多时候用 exists是一个好的选择: SELECT SUM(T1.C1)FROM T1 WHERE( 描。 如果你想校验表里是否存在某条纪录,不要用count(*)那样效率很低,而且浪费服务器资源。可以用 EXISTS代替。如: 经常需要写一个T_SQL语句比较一个父结果集和子结果集,从而找到是否存在在父结果集中有而在子结果 集中没有的记录,如: 12.尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。 13.避免频繁创建和删除临时表,以减少系统表资源的消耗。 14.临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常 用表中的某个数据集时。但是,对于一次性事件,最好使用导出表。 15.在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避 免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后 insert。 16.如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。 17.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。无需 在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。 18.尽量避免大事务操作,提高系统并发能力。 19.尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。 20. 避免使用不兼容的数据类型。例如float和int、char和varchar、binary和varbinary是不兼容的。数 据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作。例如: 在编程时将整型转化成为钱币型,而不要等到运行时转化。 21.充分利用连接条件,在某种情况下,两个表之间可能不只一个的连接条件,这时在 WHERE 子句中将连 接条件完整的写上,有可能大大提高查询速度。 A.ACCOUNT_NO=B.ACCOUNT_NO 22、使用视图加速查询 还能简化优化器的工作。例如: 如果这个查询要被执行多次而不止一次,可以把所有未付款的客户找出来放在一个视图中,并按客户的名 字进行排序: 得到大幅减少。 23、能用DISTINCT的就不用GROUP BY 24.能用UNION ALL就不要用UNION 35.尽量不要用SELECT INTO语句。 上面我们提到的是一些基本的提高查询速度的注意事项,但是在更多的情况下,往往需要反复试验比较 不同的语句以得到最佳方案。最好的方法当然是测试,看实现相同功能的SQL语句哪个执行时间最少,但 是数据库中如果数据量很少,是比较不出来的,这时可以用查看执行计划,即:把实现相同功能的多条 SQL语句考到查询分析器,按CTRL+L看查所利用的索引,表扫描次数(这两个对性能影响最大),总体上 看询成本百分比即可。 三、算法的优化 尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。.使用 基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。 与临时表一样,游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方 法,尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标 执行的速度快。如果开发时间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效 果更好。 行不同的操作。尤其对多表和大表定义的游标(大的数据集合)循环很容易使程序进入一个漫长的等特甚 至死机。 义游标进行操作,可时性能得到明显提高。 四、建立高效的索引 创建索引一般有以下两个目的:维护被索引列的唯一性和提供快速访问表中数据的策略。大型数据库 有两种索引即簇索引和非簇索引,一个没有簇索引的表是按堆结构存储数据,所有的数据均添加在表的尾 部,而建立了簇索引的表,其数据在物理上会按照簇索引键的顺序存储,一个表只允许有一个簇索引,因 此,根据B树结构,可以理解添加任何一种索引均能提高按索引列查询的速度,但会降低插入、更新、删 除操作的性能,尤其是当填充因子(Fill Factor)较大时。所以对索引较多的表进行频繁的插入、更新 、删除操作,建表和索引时因设置较小的填充因子,以便在各数据页中留下较多的自由空间,减少页分割 及重新组织的工作。 为一条规则,我通常对逻辑主键使用唯一的成组索引,对系统键(作为存储过程)采用唯一的非成组索引 ,对任何外键列[字段]采用非成组索引。不过,索引就象是盐,太多了菜就咸了。你得考虑数据库的空间 有多大,表如何进行访问,还有这些访问是否主要用作读写。 (clustered index,也称聚类索引、簇集索引)和非聚集索引(nonclustered index,也称非聚类索引 、非簇集索引)。下面,我们举例来说明一下聚集索引和非聚集索引的区别: 的前几页,因为“安”的拼音是“an”,而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结 尾的,那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字 ,那么就说明您的字典中没有这个字;同样的,如果查“张”字,那您也会将您的字典翻到最后部分,因 为“张”的拼音是“zhang”。也就是说,字典的正文部分本身就是一个目录,您不需要再去查其他目录 来找到您需要找的内容。 音,这时候,您就不能按照刚才的方法找到您要查的字,而需要去根据“偏旁部首”查到您要找的字,然 后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字 的排序并不是真正的正文的排序方法,比如您查“张”字,我们可以看到在查部首之后的检字表中“张” 的页码是672页,检字表中“张”的上面是“驰”字,但页码却是63页,“张”的下面是“弩”字,页面 是390页。很显然,这些字并不是真正的分别位于“张”字的上下方,现在您看到的连续的“驰、张、弩 ”三字实际上就是他们在非聚集索引中的排序,是字典正文中的字在非聚集索引中的映射。我们可以通过 这种方式来找到您所需要的字,但它需要两个过程,先找到目录中的结果,然后再翻到您所需要的页码。 排序。 (一)何时使用聚集索引或非聚集索引
项。比如您的某个表有一个时间列,恰好您把聚合索引建立在了该列,这时您查询2004年1月1日至2004年 10月1日之间的全部数据时,这个速度就将是很快的,因为您的这本字典正文是按日期进行排序的,聚类 索引只需要找到要检索的所有数据中的开头和结尾数据即可;而不像非聚集索引,必须先查到目录中查到 每一项数据对应的页码,然后再根据页码查到具体内容。
理论的目的是应用。虽然我们刚才列出了何时应使用聚集索引或非聚集索引,但在实践中以上规则却很容 易被忽视或不能根据实际情况进行综合分析。下面我们将根据在实践中遇到的实际问题来谈一下索引使用 的误区,以便于大家掌握索引建立的方法。 引的。 。我们的这个办公自动化的实例中的列Gid就是如此。此时,如果我们将这个列设为主键,SQL SERVER会 将此列默认为聚集索引。这样做有好处,就是可以让您的数据在数据库中按照ID进行物理排序,但笔者认 为这样做意义不大。 加珍贵。 缩小查询范围,避免全表扫描。在实际应用中,因为ID号是自动生成的,我们并不知道每条记录的ID号, 所以我们很难在实践中用ID号来进行查询。这就使让ID号这个主键作为聚集索引成为一种资源浪费。其次 ,让每个ID号都不同的字段作为聚集索引也不符合“大数目的不同值情况下不应建立聚合索引”规则;当 然,这种情况只是针对用户经常修改记录内容,特别是索引项的时候会负作用,但对于查询速度并没有影 响。 况下进行数据查询都离不开字段的是“日期”还有用户本身的“用户名”。 前用户尚未签收的情况,但如果您的系统已建立了很长时间,并且数据量很大,那么,每次每个用户打开 首页的时候都进行一次全表扫描,这样做意义是不大的,绝大多数的用户1个月前的文件都已经浏览过了 ,这样做只能徒增数据库的开销而已。事实上,我们完全可以让用户打开系统首页时,数据库仅仅查询这 个用户近3个月来未阅览的文件,通过“日期”这个字段来限制表扫描,提高查询速度。如果您的办公自 动化系统已经建立的2年,那么您的首页显示速度理论上将是原来速度8倍,甚至更快。 2、只要建立索引就能显著提高查询速度 者在fariqi字段上建立的是非聚合索引,后者在此字段上建立的是聚合索引,但查询速度却有着天壤之别 。所以,并非是在任何字段上简单地建立索引就能提高查询速度。 立聚合索引是再合适不过了。在现实中,我们每天都会发几个文件,这几个文件的发文日期就相同,这完 全符合建立聚集索引要求的:“既不能绝大多数都相同,又不能只有极少数相同”的规则。由此看来,我 们建立“适当”的聚合索引对于我们提高查询速度是非常重要的。 3、把所有需要提高查询速度的字段都加进聚集索引,以提高查询速度 段都是如此的重要,我们可以把他们合并起来,建立一个复合索引(compound index)。 字段分开查询,那么查询速度会减慢吗?带着这个问题,我们来看一下以下的查询速度(结果集都是25万 条数据):(日期列fariqi首先排在复合聚集索引的起始列,用户名neibuyonghu排在后列) 乎一样的,甚至比用上全部的复合索引列还要略快(在查询结果集数目一样的情况下);而如果仅用复合 聚集索引的非起始列作为查询条件的话,这个索引是不起任何作用的。当然,语句1、2的查询速度一样是 因为查询的条目数一样,如果复合索引的所有列都用上,而且查询结果少的话,这样就会形成“索引覆盖 ”,因而性能可以达到最优。同时,请记住:无论您是否经常使用聚合索引的其他列,但其前导列一定要 是使用最频繁的列。 (三)其他注意事项 效。因为用户在表中每加进一个索引,数据库就要做更多的工作。过多的索引甚至会导致索引碎片。 库能得到高性能的发挥 |
|