数据库开发规范Ver2.0.doc

数据库开发规范创建者：阿福版本记录版本修改者修改时间内容V1.0 起草阿福2005-03-17建立最初版本V1.1阿福2005-03-31增加数据库物理优化方案V2.0阿福2005-05-23增加数据访问模式根据网友意见，修改部分错误感谢勇者无敌()提供BUG说明 本文档整理于一些SQL Server开发中的经验，可能对于其他类型的数据库并不合适。笔者会在稍后继续增加其他数据库的规范部分。本文档的很多知识来源于网络，如果发现其中有涉及版权的问题，请与我联系，我将在第一时间删除非法引用的内容。本文档旨在和大家交流经验，任何人均可对此文档进行使用、修改或增加。欢迎大家积极参与，共同交流经验。 我叫阿福，我的MSN是：ah_，我的QQ是：12304685。欢迎大家和我一起讨论，无论是批评还是交流，我都会非常乐意。目录说明2目录31、命名规范51.1、命名总规则51.2、表命名规范51.3、字段命名规范62、SQL语句规范63、存储过程编码规范73.1、只允许应用程序通过存储过程访问数据库73.2、命名规范93.3、存储过程的参数命名93.4、存储过程返回的记录集93.5、格式约定93.6、事务使用约定103.7、游标使用约定103.8、表锁定114、数据库设计规范134.1数据完整性规范（编码期）134.2、设计工具和版本划分146、数据库优化146.1、数据库性能优化规范（运行期）146.2、拆分表示例146.3、冗余字段建立示例156.4、冗余表建立示例156.5、存储过程中分页方案157、整体优化和物理优化167.1、查询优化方法167.1.1、原因167.1.2、查询优化方法168、数据访问模式（Data Access Patterns）238.1、解耦模式238.1.1、数据访问器248.1.2、主动域对象248.1.3、对象/关系映射248.1.4、层248.2、资源模式248.2.1、资源修饰器248.2.2、资源池248.2.3、资源定时器248.2.4、资源描述器258.2.5、重试器258.3、输入输出模式258.3.1、选择工厂258.3.2、域对象工厂258.3.3、更新工厂258.3.4、域对象装配器258.3.5、分页迭代器258.4、缓存模式258.4.1、缓存访问器268.4.2、即时缓存268.4.3、填装缓存268.4.4、缓存查找序列268.4.5、缓存收集器268.4.6、缓存复制器268.4.7、缓存统计268.5、并发模式268.5.1、事务278.5.2、乐观锁定278.5.3、悲观锁定278.5.4、补偿事务27附录27附录1：T-SQL 编码标准271、命名规范1.1、命名总规则1、 所有名称的字符范围为：A-Z, a-z, 0-9 和_(下划线)。不允许使用其他字符作为名称。2、 采用英文单词或英文短语（包括缩写）作为名称，不能使用无意义的字符或汉语拼音。3、 名称应该清晰明了，能够准确表达事物的含义，最好可读，遵循“见名知义”的原则。1.2、表命名规范1、不使用tab或tbl作为表前缀（本来就是一个表，为什么还要说明）2、表名以代表表内的内容的一个和多个名词组成，以下划线分隔，每个名词的第一个字母大写。3、使用表的内容分类作为表名的前缀：如，与用户信息相关的表使用前缀User_，与内容相关的信息使用前缀Content_。4、表的前缀以后，是表的具体内容的描述。如：用户登录信息的表名为：User_Login，用户在论坛中的信息的表名为：User_BBS_Info5、一些作为多对多连接的表，可以使用两个表的前缀作为表名：如：用户登录表User_Login，用户分组表Group_Info，这两个表建立多对多关系的表名为：User_Group_Relation6、当系统中有一些少量的，重复出现的值时，使用字典表来节约存储空间和优化查询。如地区、系统中用户类型的代号等。这类值不会在程序的运行期变化，但是需要存储在数据库中。 就地区而言，如果我们要查询某个地区的记录，则数据库需要通过字符串匹配的方式来查询；如果将地区改为一个地区的代号保存在表中，查询时通过地区的代号来查询，则查询的效率将大大提高。程序中宜大量的使用字典表来表示这类值。字典表中保存这类值的代号和实体的集合，以外键的方式关联到使用这类值的表中。然而，在编码阶段，程序员并不使用字典表，因为首先查询字典表中实体的代号，违背了提高查询效率的初衷。程序员在数据字典的帮助下，直接使用代号来代表实体，从而提高效率。虽然字典表在实际上并不使用，但是仍应该保留在数据库中（起码是在开发期内保留）。字典表作为另一种形式上的“数据字典文档”出现，以说明数据库中哪些表的哪些字段是使用了字典表的。为了提高数据库的数据完整性，在开发阶段可以保留完整的字典表和普通表的外键约束。但是在数据库的运行阶段，应该将普通表和字典表的外键删除，以提高运行效率，特别是某些表使用了很多字典表的情况。 案例：某数据库中有百万条用户信息，应用系统中常常需要按照地区要查询用户的信息。用户信息表以前是按照具体的地区名称来保存的，现在将具体的名称改为字典表中的地区代号，查询效率大大提高。 字典表统一以Dic_作为前缀。1.3、字段命名规范1、字段不使用任何前缀（表名代表了一个名称空间，字段前面再加前缀显得罗嗦）2、字典名也避免采用过于普遍过于简单的名称：例如，用户表中，用户名的字段为UserName比Name更好。3、布尔型的字段，以一些助动词开头，更加直接生动：如，用户是否有留言HasMessage，用户是否通过检查IsChecked等。4、字段名为英文短语、形容词+名词或助动词+动词时态的形式表示，大小写混合，遵循“见名知义”的原则。2、SQL语句规范1、不允许写SELECT * FROM ，必须指明需要读取的具体字段。2、不允许在应用程序代码中直接写SQL语句访问数据库。3、避免在一行内写太长的SQL语句，在SQL关键字的地方将SQL语句分成多行会更加清晰。 如：SELECT UserID,UserName,UserPwd FROM User_Login WHERE AreaID=20修改成：SELECT UserID,UserName,UserPwdFROM User_LoginWHERE AreaID=20更加直观4、在一些块形式的SQL语句中，就算只有一行代码，也要加上BEGINEND块。 如：IF EXISTS()SET nVar = 100应该写成：IF EXISTS()BEGINSET nVar = 100END5、SQL批处理语句的空行和缩进与一般的结构化程序语言一致，应该保持良好的代码格式。6、所有的SQL关键字大写3、存储过程编码规范3.1、只允许应用程序通过存储过程访问数据库 只允许应用程序通过存储过程访问数据库，而不允许直接在代码中写SQL语句访问数据库。在数据库开发项目中，大量使用存储过程有很多的好处，首先看微软提供信息：使用 SQL Server 中的存储过程而不使用存储在客户计算机本地的 Transact-SQL 程序的优势有： 允许模块化程序设计： 只需创建过程一次并将其存储在数据库中，以后即可在程序中调用该过程任意次。存储过程可由在数据库编程方面有专长的人员创建，并可独立于程序源代码而单独修改。允许更快执行： 如果某操作需要大量 Transact-SQL 代码或需重复执行，存储过程将比 Transact-SQL 批代码的执行要快。将在创建存储过程时对其进行分析和优化，并可在首次执行该过程后使用该过程的内存中版本。每次运行 Transact-SQL 语句时，都要从客户端重复发送，并且在 SQL Server 每次执行这些语句时，都要对其进行编译和优化。减少网络流量： 一个需要数百行 Transact-SQL 代码的操作由一条执行过程代码的单独语句就可实现，而不需要在网络中发送数百行代码。可作为安全机制使用： 即使对于没有直接执行存储过程中语句的权限的用户，也可授予他们执行该存储过程的权限。 除此以外，使用存储过程的好处还有：1、 在逻辑上，存储过程将应用程序层和数据库物理结构分离开来。存储过程形成了一个应用程序和数据库之间的接口。这样的接口抽象了复杂的数据库结构，符合极限编程中“基于接口编程”的思想。2、 将主要的业务逻辑封装在存储过程中，能够避免在应用程序层写大量的代码（在应用程序中通过字符串插入太长的SQL语句影响效率，而且维护困难）。有助于提高开发效率，并且直接在查询分析器中调试存储过程，能够更早的发现系统中的逻辑问题，从而提高代码的质量。3、 在网站一类的应用系统中，SQL注入式漏洞一直是难以完全杜绝的漏洞。如果只通过存储过程来访问数据库，能够大大减少这类安全性问题。（因此，就算是简单的只有一句的SQL语句，也应该写成存储过程。）4、 由于采用存储过程，应用程序的层面可以不关心具体的数据库结构，而只关心存储过程的接口调用。因此，在以下一些情况，存储过程的优势非常明显：需求变更，表的结构必须要改变。使用存储过程，只要参数不变，我们就只需要修改相应的存储过程，而不需要修改应用程序的代码。这样的设计将减小需求变更对项目的影响。为提高效率，使部分字段冗余：一些经常性访问的字段，我们可以在相关的表中进行冗余存储。这样既提高了效率，又通过存储过程屏蔽了冗余细节。为提高效率，使用冗余表（拆分表）：一些大的表，为了提高查询效率，可能需要将记录分别保存到多个表中去。使用存储过程，有存储过程来决定从哪些拆分的表中获取或插入数据。这样提高了效率，又不必在应用程序层面关心具体的拆分规则。5、 使用存储过程，便于在项目后期或者运行中集中优化系统性能。在项目开发过程中，由于各种原因，往往无法编写高效的代码，这个问题常常在项目后期或者在运行期体现出来。通过存储过程来封装对数据库的访问，可以在项目集成以后，通过试运行观察系统的运行效率，从而很容易找出系统的瓶颈，并能够通过优化存储过程的代码来提高系统的运行效率。这样的优化，比在运用程序中优化更有效，更容易。6、在开发中的人员分工上，能够更加发挥团队中个人的专长。可以把存储过程看成多层开发中的一个层面，这个层面可以由数据库开发经验丰富的（一个或多个）程序员担任，其他程序员就可以专心处理系统中的其他环节，只需要关系存储过程的调用接口即可。这样，能够使程序员的思维集中在某个专一的问题上，能够发挥更好的分工合作和效率。同时，过多的使用存储过程，也存在以下一些疑虑：问题一：存储过程编译后，将作为数据库的全局对象保存，太多的存储过程将占用大量的数据库服务器的内存。问题二：在存储过程中实现大量的逻辑，将使大量的运算在数据库服务器上完成，而不是在应用服务器上完成。当访问量很大的时候，会大大消耗数据库服务器的CPU占用率。在此还存在这个一个案例：有一个访问量巨大的网站，有多台WEB服务器构成一个负载均衡的服务器群集，但是只有一台中心的数据库服务器。当访问量持续增加的时候，接入更多的WEB服务器来满足高并发量的访问；但是数据库服务器却没办法一直增加。因此，就需要尽量在WEB服务器上完成业务逻辑，尽量避免消耗数据库服务器的资源。 对于这两个担心，我的想法是：问题一的解决：存储过程是经过编译后的SQL语句，在内存中是二进制的代码，并不会消耗太多内存。并且，存储过程比起直接使用SQL语句来说，效率大大提高。换个角度来说，这是一个“以空间换时间”的方案，多消耗一点内存来换取效率的提高，是值得的。问题二的解决：首先，在实现业务逻辑的问题上，在存储过程中实现比在应用程序中实现更容易；其次，从开发效率上，存储过程的开发比应用程序更简单（就完成相同逻辑而言）。在高访问量的系统中，应用服务器和数据库服务器的资源分配的问题，应该从成本的角度来考虑：软件开发中的成本，人工支出的费用远远高于硬件支出的成本。我们可以很容易花钱购买更好的服务器，但是很难花钱让开发人员使程序性能有大幅度的提高。使用存储过程来封装业务逻辑，首先节省的是大量的开发时间和调试时间，并能够大大提高代码的质量。因此，从成本来说，应该使用存储过程。对于大访问量的情况，最简单的办法是投入更多的硬件成本：更快的硬盘，更大的内存和更多的CPU，还有更好的网卡等等。其次，在应用程序的层面，可以大量的使用静态文件缓存的办法来减轻数据库的压力。如：不经常变化的信息，可以从数据库服务器中读取，保存为应用服务器上的XML静态文件等。实在不行的话，应该在系统设计之初，考虑可能的访问量，将系统设计成分布式的。这样就能从根本上解决大访问量的问题。3.2、命名规范1、存储过程的前缀和表名的前缀类似：把一系列表看成一个对象，字段为对象的属性，存储过程则为访问对象的方法。如：添加用户的存储过程取名为：User_AddUser2、存储过程使用模块的前缀来命名。如，用户管理的存储过程使用前缀user_。3、存储过程的前缀之后，是动词+名词形式的存储过程名（也可以是动词短语）。3.3、存储过程的参数命名1、参数名采用匈牙利命名法，使用类型的前缀2、每个存储过程都有：errno int和errmsg varchar(255)两个输出参数。应用程序中可以根据这两个参数得到存储过程执行的情况。(这两个参数使用默认值，可以忽略)errno为整型的错误信息代码，执行成功返回0。Errno的值的具体含义通过errmsg参数说明，或者通过代码中的注释或文档。Errmsg为错误信息的字符串描述，这个参数主要用于调试期作为说明，避免在应用程序中使用该值。同时，要注意英文版系统和中文版系统中，信息的语言选择对程序的影响。3.4、存储过程返回的记录集1、存储过程的输出记录集：为程序的结构清晰，存储过程最好只返回一个记录集。但在某些为了提高性能的场合，还是可以输出多个记录集2、记录集中，每个输出的字段最后都指定字段的别名，以免真实的字段名信息流失到客户端，从而加大黑客找到系统漏洞的可能。3.5、格式约定1、 所有SQL关键字大写2、 使用良好的变量命名规范3、 保持良好的结构，包括空行、缩进和空格等。4、 块状的语句，一定要写上BEGINEND5、 在每个存储过程的开头加上详细的注释：包括存储过程名称、参数说明、功能说明、返回数据集说明、以及作者和版权声明。6、 每个存储过程内的代码前后必须加上SET NOCOUNT ON 和SET NOCOUNT OFF。7、 存储过程格式的示例如下：/* Name: User_AddUser* Purpose: Add a user to system* Parameters: strUserName the users login name* strPwd the users password, encrypted by MD5* errno the error number, its a “output” param* errmsg the error message, its a “output” param* Returns: the users ID, as a result set.* Author: ahfu, ah_* Copyright: CopyRights By SyNet 2005.All Rights Reserved.* Create At: 2005-03-18 Last Modify: 2005-03-19*/CREATE PROCEDURE User_AddUser(strUserName varchar(20),strPwd varchar(50),errno int = 0 OUTPUT,errmsg varchar(255)=NULL OUTPUT)ASBEGINSET NOCOUNT ON/*以下是存储过程的代码*/SET NOCOUNT OFFEND3.6、事务使用约定1、如果对记录的更改操作不止一次，则需要把这些操作写在事务中。2、事务必须显式提交或者取消。3.7、游标使用约定1、 若无必要，不要使用游标2、 包含游标的存储过程，必须对性能进行认真测试。3.8、表锁定对于依赖于SQL的客户而言，如果不对同时使用数据库的用户数量进行检查，可能会对数据完整性造成严重的破坏。使用这些锁提示，你可以确保能够合理地处理数据修改。在过去的几年中，SQL Server从一个小的办公数据存储逐渐发展为企业级的数据库服务器。用户同时访问数据库的数量也随着它的升级而递增。SQL Server 2000的锁资源标准似乎不是非常有效，但是，感谢上帝的是，它提供了可以重设标准锁的功能。锁提示可以告诉服务器该如何锁资源，但是在我们重设它们之前先来检查一下锁。1、什么是锁 像SQL server一样的关系数据库使用锁来防止用户“互相踩到对方的脚趾头”。也就是说，锁可以防止用户造成修改数据时的碰撞。当一个用户锁住一段代码时候，其它的用户都不能修改这段数据。另外，一个锁阻止了用户观看未被授权的数据修改。用户必须等待到数据修改并保存之后才能够查看它。数据必须使用不同的方法来加锁。SQL Server 2000使用锁来实现多用户同时修改数据库同一数据时的同步控制。2、死锁 一个数据库的死锁是发生在两个或多于两个访问一些资源的数据库会话中的，并且这些会话相互之间有依赖关系。死锁是可以在任意一个多线程的系统成出现的一个情况，不仅仅局限于关系数据库管理系统。一个多线程系统中的线程可能需要一个或多个资源(例如，锁)。如果申请的资源正在被另外一个线程所使用，那么第一个线程就需要等待持有该资源的线程的释放它所需要的资源。假设等待线程持有一个那个正拥有线程所依赖的资源。下面的这一段代码就可以造成死锁异常现象的发生：System.Data.SqlClient.SqlException: Transaction (Process ID 12) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.当一个SQL Server的调用和另外一个资源发生冲突时就会抛出异常，这个资源持有一个必要的资源。结果是，一个进程就被终止了。当进程的ID号成为系统的唯一标识的时候，这会是一个很平常死锁的消息错误。3、锁的类型 一个数据库系统在许多情况下都有可能锁数据项。其可能性包括：Rows数据库表中的一整行 Pages行的集合（通常为几kb） Extents通常是几个页的集合 Table整个数据库表 Database被锁的整个数据库表 除非有其它的说明，数据库根据情况自己选择最好的锁方式。不过值得感谢的是，SQL Server提供了一种避免默认行为的方法。这是由锁提示来完成的。4、提示 或许你有过许多如下的经历：需要重设SQL Server的锁计划，并且加强数据库表中锁范围。TansactSQL提供了一系列不同级别的锁提示，你可以在SELECT,INSERT,UPDATE和DELETE中使用它们来告诉SQL Server你需要如何通过重设任何的系统或事务级别来锁表格。可以实现的提示包括：FASTFIRSTROW选取结果集中的第一行，并将其优化 HOLDLOCK持有一个共享锁直至事务完成 NOLOCK不允许使用共享锁或独享锁。这可能会造成数据重写或者没有被确认就返回的情况；因此，就有可能使用到脏数据。这个提示只能在SELECT中使用。 PAGLOCK锁表格 READCOMMITTED只读取被事务确认的数据。这就是SQL Server的默认行为。 READPAST跳过被其它进程锁住的行，所以返回的数据可能会忽略行的内容。这也只能在SELECT中使用。 READUNCOMMITTED等价于NOLOCK. REPEATABLEREAD在查询语句中，对所有数据使用锁。这可以防止其它的用户更新数据，但是新的行可能被其它的用户插入到数据中，并且被最新访问该数据的用户读取。 ROWLOCK按照行的级别来对数据上锁。SQL Server通常锁到页或者表级别来修改行，所以当开发者使用单行的时候，通常要重设这个设置。 SERIALIZABLE等价于HOLDLOCK. TABLOCK按照表级别上锁。在运行多个有关表级别数据操作的时候，你可能需要使用到这个提示。 UPDLOCK当读取一个表的时候，使用更新锁来代替共享锁，并且保持一直拥有这个锁直至事务结束。它的好处是，可以允许你在阅读数据的时候可以不需要锁，并且以最快的速度更新数据。 XLOCK给所有的资源都上独享锁，直至事务结束。 微软将提示分为两类：granularity和isolation-level。Granularity提示包括PAGLOCK, NOLOCK, ROWLOCK和TABLOCK。而isolation-level提示包括HOLDLOCK, NOLOCK, READCOMMITTED, REPEATABLEREAD和SERIALIZABLE。这些提示允许管理者通过使用SQL Server来控制锁定，并且，在Transact-SQL声明中这包含有这些提示。它们被放在声明的FROM部分中，位于WITH之后。WITH声明在SQL Server 2000中是可选部分，但是微软强烈要求将它包含在内。这就使得许多人都认为在未来的SQL Server发行版中，就可能会包含这个声明。下面是提示应用于FROM从句中的例子： FROM ,.n :=table_name AS table_alias WITH ( ,.n ) := INDEX ( index_val ,.n )| FASTFIRSTROW| HOLDLOCK| NOLOCK| PAGLOCK| READCOMMITTED| READPAST| READUNCOMMITTED| REPEATABLEREAD| ROWLOCK| SERIALIZABLE| TABLOCK| TABLOCKX| UPDLOCK| XLOCK 下面是Transact-SQL声明中从Northwind数据库的Emplyees表中选择所有的数据的语句： SELECT *FROM Employees WITH (nolock)这个语句就提供出了所有的数据，包括正在被其它处理器使用的数据，所以，得出的数据可能是脏数据，但是对于任务而言并没有很大的影响。另外一个例子是更新所有表中的行，并设定一个特定值。UPDATEEmployees WITH (tablock)SET Title=Test这个例子就是更新表中所有的行，所以使用了一个表锁。5、题外话我必须强调一个事实，就是即使在代码中使用了一个表提示，查询优化器也有可能会忽略这个提示。如果当查询优化器表提示没有选择该表，而是使用在后来的查询中，就会忽略这个表提示。同样，查询优化器通常会选择表的索引视图而不是表。在表包含有计算出的列的时候，表提示也有可能被忽略。6、使用你的判断力 在你的应用程序中使用表提示依赖于你需要查询什么。所以，是否使用这些提示将完全依赖于你的需要。例如，许多管理者喜欢使用FASTFIRSTROW提示来快速返回第一行的东西。这就使得在查询完成后，可以提供给他们其所需要的东西。当数据不可能被修改的时候（例如，存档数据），使用NOLOCK 提示是一个很好的选择，因为数据基本上是静态的。然而，从另一方面说，在使用到财务方面等对精度要求高的方面的时候，这种方法就不适用。4、数据库设计规范4.1数据完整性规范（编码期）1、为便于在程序的编码期查错，可以在设计数据库的时候尽可能多的加上约束(check)。如，整型的字段的取值范围等，常常为field0。2、同理，尽可能地在开发期间使用触发器来验证数据的完整性。3、如果字段之间存在冗余，应该编写触发器来管理冗余的字段3、在开发阶段保存完整的主键、外键和唯一索引的约束。4、原则：编码期间，数据完整性优先于性能。在保障系统正确运行的前提下尽可能的提高效率。4.2、设计工具和版本划分1、使用ER-WIN设计数据库，同时设计逻辑视图和物理视图（也可以是PowerDesigner等工具，通过ER图形表示出数据表之间的关系）2、开发过程中尽量保证设计文档和物理表结构的一致变化3、数据库分开发期和运行期两个版本：开发期版本主要体现完整的约束，运行期版本体现效率的优化。6、数据库优化6.1、数据库性能优化规范（运行期）1、在运行阶段删除不必要的约束(check)。2、尽量不要使用触发器3、尽量保留主键约束4、适当删除外键，以提高性能5、在运行期间，通过分析系统的访问量，创建索引来优化性能6、分析每个表可能的数据增长量，定义自动拆分表规则。将大表进行拆分来提高性能。7、预先考虑数据清理规则：在什么情况下删除数据库中的旧数据，以此来提高性能。8、制定数据库备份和灾难恢复计划。9、为效率考虑，可以在系统测试阶段适当增加冗余字段，或者冗余表。10、分页的记录输出必须通过存储过程来实现，不能使用API游标来分页，这样可以提高分页的效率。6.2、拆分表示例案例：网站有200万用户，有很多模块围绕用户提供服务。 为提高效率，每个表最多只保存与用户有关的10万记录，200万条记录拆分到20个表中。编号为1-10万的用户将记录保存到表一，100001-200000编号的记录保存到表二，以此类推。建立一个拆分信息表，表中保存了哪些表是经过拆分的，拆分到什么程度，拆分规则是什么。当插入记录的时候，首先判断插入这条记录的用户的ID。存储过程根据ID的范围，自动把数据插入到相应的拆分表中去。当按照条件查询，存储过程自动连接所有的拆分表，丛中筛选出记录。（一般情况下：同类型的查询远远大于按照条件的全体查询）6.3、冗余字段建立示例案例：留言本表中，要保存用户的ID作为外键。通常，通过连接留言表和用户表来得知是哪个用户发布了留言。为提高效率，在留言本表中增建用户名的字段。插入记录的时候，同时保存用户ID和用户名。这样，当查询时，就不必连接两个表，使效率大大提高。但是，当用户修改用户名时，要吗更新其他表中的用户名，要吗忽略这种用户名不一致的影响。如何处理取决于用户名在模块中的重要程度。6.4、冗余表建立示例案例：有用户表和分组表，两个表之间是多对多的关系，建立一个用户与组的关系表来实现这种关系。用户表中有百万条记录，组表中几千条记录。如果每个用户都属于多个组的化，关联表中将存在几百万条记录。现在将用户表和关联表进行拆分，拆分规则为用户的ID范围。当查询某用户的组时，效率大大提高。但是当查询某组下的用户时，需要关联所有的拆分表，效率很低。为提高效率，建立一个冗余的用户和组的关系表，这个关系表中保存第一个关系表中统一的内容，但是拆分规则为组ID的范围。这样，当查询组中的用户时，丛第二个关系表中查询，效率大大提高。6.5、存储过程中分页方案方案一：1、首先统计得到符合条件的记录数2、定义表变量：表变量的第一个字段为自增长类型，第二个字段为记录集中的唯一值字段（一般是主键）3、使用INSERT () SELECT 语句将符合条件的记录的唯一值字段保存在表变量中。4、使用WHERE ID IN (SELECT ID FROM 表变量 WHERE ) 的方法从表变量中读出需要的唯一值字段。方案二：1、首先统计符合条件的记录数，并根据页大小计算页数2、如果读取第一页，直接使用TOP子句读取3、如果页数在前一半：结果集1：SELECT TOP CurPage*PageSize Fields FROM Table ORDER BY ID ASC结果集2：SELECT TOP PageSize * FROM (结果集1) ORDER BY ID DESC最终结果：SELECT * FROM (结果集2) ORDER BY ID ASC4、如果页数在后一半：结果集1：SELECT TOP (PageCount-CurPage + 1)*PageSize Fields FROM Table ORDER BY ID DESC最终结果：SELECT TOP PageSize * FROM Table ORDER BY ID ASC7、整体优化和物理优化7.1、查询优化方法7.1.1、原因查询速度慢的原因很多，常见如下几种： 1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷)。2、I/O吞吐量小，形成了瓶颈效应。 3、没有创建计算列导致查询不优化。 4、内存不足。5、网络速度慢。6、查询出的数据量过大（可以采用多次查询，其他的方法降低数据量）。7、锁或者死锁(这也是查询慢最常见的问题，是程序设计的缺陷)。8、sp_lock,sp_who,活动的用户查看,原因是读写竞争资源。9、返回了不必要的行和列。10、查询语句不好，没有优化。7.1.2、查询优化方法1、把数据、日志、索引放到不同的I/O设备上，增加读取速度，以前可以将Tempdb应放在RAID0上，SQL2000不在支持。数据量（尺寸）越大，提高I/O越重要。2、纵向、横向分割表，减少表的尺寸(sp_spaceuse) 。3、升级硬件。4、根据查询条件，建立索引，优化索引、优化访问方式，限制结果集的数据量。注意填充因子要适当（最好是使用默认值0）。索引应该尽量小，使用字节数小的列建索引好（参照索引的创建）,不要对有限的几个值的字段建单一索引如性别字段。5、提高网速。6、扩大服务器的内存,Windows 2000和SQL server 2000能支持4-8G的内存。配置虚拟内存：虚拟内存大小应基于计算机上并发运行的服务进行配置。运行 Microsoft SQL Server 2000 时，可考虑将虚拟内存大小设置为计算机中安装的物理内存的 1.5 倍。如果另外安装了全文检索功能，并打算运行 Microsoft 搜索服务以便执行全文索引和查询，可考虑：将虚拟内存大小配置为至少是计算机中安装的物理内存的 3 倍。将 SQL Server max server memory 服务器配置选项配置为物理内存的 1.5 倍（虚拟内存大小设置的一半）。7、增加服务器CPU个数；但是必须明白并行处理串行处理更需要资源例如内存。使用并行还是串行程是MsSQL自动评估选择的。单个任务分解成多个任务，就可以在处理器上运行。例如耽搁查询的排序、连接、扫描和GROUP BY字句同时执行，SQL SERVER根据系统的负载情况决定最优的并行等级，复杂的需要消耗大量的CPU的查询最适合并行处理。但是更新操作UPDATE，INSERT，DELETE还不能并行处理。8、如果是使用like进行查询的话，简单的使用index是不行的，但是全文索引，耗空间。 like a% 使用索引；like %a 不使用索引；用 like %a% 查询时，查询耗时和字段值总长度成正比，所以不能用CHAR类型，而是VARCHAR。对于字段的值很长的建全文索引。 9、DB Server 和APPLication Server 分离；OLTP和OLAP分离。10、分布式分区视图可用于实现数据库服务器联合体。联合体是一组分开管理的服务器，但它们相互协作分担系统的处理负荷。这种通过分区数据形成数据库服务器联合体的机制能够扩大一组服务器，以支持大型的多层 Web 站点的处理需要。有关更多信息，参见设计联合数据库服务器。（参照SQL帮助文件分区视图）：a、在实现分区视图之前，必须先水平分区表。b、在创建成员表后，在每个成员服务器上定义一个分布式分区视图，并且每个视图具有相同的名称。这样，引用分布式分区视图名的查询可以在任何一个成员服务器上运行。系统操作如同每个成员服务器上都有一个原始表的复本一样，但其实每个服务器上只有一个成员表和一个分布式分区视图。数据的位置对应用程序是透明的。 11、重建索引DBCC REINDEX，DBCC INDEXDEFRAG，收缩数据和日志 DBCC SHRINKDB，DBCC SHRINKFILE设置自动收缩日志。对于大的数据库不要设置数据库自动增长，它会降低服务器的性能。在T-sql的写法上有很大的讲究，下面列出常见的要点：首先，DBMS处理查询计划的过程是这样的： a、 查询语句的词法、语法检查； b、 将语句提交给DBMS的查询优化器； c、 优化器做代数优化和存取路径的优化； d、 由预编译模块生成查询规划； e、 然后在合适的时间提交给系统处理执行； f、 最后将执行结果返回给用户其次，看一下SQL SERVER的数据存放的结构：一个页面的大小为8K(8060)字节，8个页面为一个盘区，按照B树存放。 12、Commit和rollback的区别：Rollback:回滚所有的事务。 Commit:提交当前的事务。没有必要在动态SQL里写事务，如果要写请写在外面如： BEGIN TRANS EXEC(s) COMMIT TRANS或者将动态SQL 写成函数或者存储过程。 13、在查询SELECT语句中用WHERE字句限制返回的行数，避免表扫描，如果返回不必要的数据，浪费了服务器的I/O资源，加重了网络的负担降低性能。如果表很大，在表扫描的期间将表锁住，禁止其他的联接访问表，后果严重。 14、SQL的注释申明对执行没有任何影响。15、尽可能不使用光标，它占用大量的资源。如果需要row-by-row地执行，尽量采用非光标技术，如：在客户端循环，用临时表，Table变量，用子查询，用CASE语句等等。游标可以按照它所支持的提取选项进行分类： 只进：必须按照从第一行到最后一行的顺序提取行。FETCH NEXT 是唯一允许的提取操作,也是默认方式。可滚动性：可以在游标中任何地方随机提取任意行。游标的技术在SQL2000下变得功能很强大，他的目的是支持循环。有四个并发选项 READ_ONLY：不允许通过游标定位更新(Update)，且在组成结果集的行中没有锁。 OPTIMISTIC WITH valueS：乐观并发控制是事务控制理论的一个标准部分。乐观并发控制用于这样的情形，即在打开游标及更新行的间隔中，只有很小的机会让第二个用户更新某一行。当某个游标以此选项打开时，没有锁控制其中的行，这将有助于最大化其处理能力。如果用户试图修改某一行，则此行的当前值会与最后一次提取此行时获取的值进行比较。如果任何值发生改变，则服务器就会知道其他人已更新了此行，并会返回一个错误。如果值是一样的，服务器就执行修改。选择这个并发选项。OPTIMISTIC WITH ROW VERSIONING：此乐观并发控制选项基于行版本控制。使用行版本控制，其中的表必须具有某种版本标识符，服务器可用它来确定该行在读入游标后是否有所更改。在 SQL Server 中，这个性能由 timestamp 数据类型提供，它是一个二进制数字，表示数据库中更改的相对顺序。每个数据库都有一个全局当前时间戳值：DBTS。每次以任何方式更改带有 timestamp 列的行时，SQL Server 先在时间戳列中存储当前的 DBTS 值，然后增加 DBTS 的值。如果某 个表具有 timestamp 列，则时间戳会被记到行级。服务器就可以比较某行的当前时间戳值和上次提取时所存储的时间戳值，从而确定该行是否已更新。服务器不必比较所有列的值，只需比较 timestamp 列即可。如果应用程序对没有 timestamp 列的表要求基于行版本控制的乐观并发，则游标默认为基于数值的乐观并发控制。 SCROLL LOCKS：这个选项实现悲观并发控制。在悲观并发控制中，在把数据库的行读入游标结果集时，应用程序将试图锁定数据库行。在使用服务器游标时，将行读入游标时会在其上放置一个更新锁。如果在事务内打开游标，则该事务更新锁将一直保持到事务被提交或回滚；当提取下一行时，将除去游标锁。如果在事务外打开游标，则提取下一行时，锁就被丢弃。因此，每当用户需要完全的悲观并发控制时，游标都应在事务内打开。更新锁将阻止任何其它任务获取更新锁或排它锁，从而阻止其它任务更新该行。然而，更新锁并不阻止共享锁，所以它不会阻止其它任务读取行，除非第二个任务也在要求带更新锁的读取。滚动锁根据在游标定义的 SELECT 语句中指定的锁提示，这些游标并发选项可以生成滚动锁。滚动锁在提取时在每行上获取，并保持到下次提取或者游标关闭，以先发生者为准。下次提取时，服务器为新提取中的行获取滚动锁，并释放上次提取中行的滚动锁。滚动锁独立于事务锁，并可以保持到一个提交或回滚操作之后。如果提交时关闭游标的选项为关，则 COMMIT 语句并不关闭任何打开的游标，而且滚动锁被保留到提交之后，以维护对所提取数据的隔离。所获取滚动锁的类型取决于游标并发选项和游标 SELECT 语句中的锁提示。16、用Profiler来跟踪查询，得到查询所需的时间，找出SQL的问题所在;用索引优化器优化索引。17、注意UNION和UNION ALL 的区别。UNION ALL好。18、注意使用DISTINCT，在没有必要时不要用，它同UNION一样会使查询变慢。重复的记录在查询里是没有问题的。19、查询时不要返回不需要的行、列。20、用sp_configure query governor cost limit或者SET QUERY_GOVERNOR_COST_LIMIT来限制查询消耗的资源。当评估查询消耗的资源超出限制时，服务器自动取消查询,在查询之前就扼杀掉。SET LOCKTIME设置锁的时间。21、用SELECT TOP 100 / 10 PERCENT 来限制用户返回的行数或者SET ROWCOUNT来限制操作的行。22、在SQL2000以前，一般不要用如下的字句: IS NULL, , !=, !, !, NOT, NOT EXISTS, NOT IN, NOT LIKE, and LIKE %500，因为他们不走索引全是表扫描。也不要在WHere字句中的列名加函数，如Convert，substring等,如果必须用函数的时候，创建计算列再创建索引来替代.还可以变通写法：WHERE SUBSTRING(firstname,1,1) = m改为WHERE firstname like m%（索引扫描），一定要将函数和列名分开。并且索引不能建得太多和太大。NOT IN会多次扫描表，使用EXISTS、NOT EXISTS ，IN , LEFT OUTER JOIN 来替代，特别是左连接,而Exists比IN更快，最慢的是NOT操作.如果列的值含有空，以前它的索引不起作用，现在2000的优化器能够处理了。相同的是IS NULL，“NOT, NOT EXISTS, NOT IN能优化她，而”等还是不能优化，用不到索引。 23、使用Query Analyzer，查看SQL语句的查询计划和评估分析是否是优化的SQL。一般的20%的代码占据了80%的资源，我们优化的重点是这些慢的地方。 24、如果使用了IN或者OR等时发现查询没有走索引，使用显示申明指定索引： SELECT * FROM PersonMember (INDEX = IX_Title) WHERE processid IN (男，女)25、将需要查询的结果预先计算好放在表中，查询的时候再SELECT。这在SQL7.0以前是最重要的手段。例如医院的住院费计算。 26、MIN() 和 MAX()能使用到合适的索引。 27、数据库有一个原则是代码离数据越近越好，所以优先选择Default,依次为Rules,Triggers, Constraint（约束如外健主健CheckUNIQUE,数据类型的最大长度等等都是约束）,Procedure.这样不仅维护工作小，编写程序质量高，并且执行的速度快。 28、如果要插入大的二进制值到Image列，使用存储过程，千万不要用内嵌INsert来插入(不知JAVA是否)。因为这样应用程序首先将二进制值转换