1、数据库结构的设计

一、数据行的长度不要超过8020字节，若是超过这个长度的话在物理页中这条数据会占用两行从而形成存储碎片，下降查询效率。html

二、可以用数字类型的字段尽可能选择数字类型而不用字符串类型的（电话号码），这会下降查询和链接的性能，并会增长存储开销。这是由于引擎在处理查询和链接会逐个比较字符串中每个字符，而对于数字型而言只须要比较一次就够了。程序员

三、对于不可变字符类型char和可变字符类型varchar 都是8000字节,char查询快，可是耗存储空间，varchar查询相对慢一些可是节省存储空间。在设计字段的时候能够灵活选择，例如用户名、密码等长度变化不大的字段能够选择CHAR，对于评论等长度变化大的字段能够选择VARCHAR。面试

四、字段的长度在最大限度的知足可能的须要的前提下，应该尽量的设得短一些，这样能够提升查询的效率，并且在创建索引的时候也能够减小资源的消耗。算法

2、查询的优化

保证在实现功能的基础上，尽可能减小对数据库的访问次数(能够用缓存保存查询结果，减小查询次数)；经过搜索参数，尽可能减小对表的访问行数,最小化结果集，从而减轻网络负担；可以分开的操做尽可能分开处理，提升每次的响应速度；在数据窗口使用SQL时，尽可能把使用的索引放在选择的首列；算法的结构尽可能简单；在查询时，不要过多地使用通配符如SELECT * FROM T1语句，要用到几列就选择几列如：SELECTCOL1,COL2 FROM T1；在可能的状况下尽可能限制尽可能结果集行数如：SELECT TOP 300 COL1,COL2,COL3 FROM T1,由于某些状况下用户是不须要那么多的数据的。数据库

在没有建索引的状况下，数据库查找某一条数据，就必须进行全表扫描了，对全部数据进行一次遍历，查找出符合条件的记录。在数据量比较小的状况下，也许看不出明显的差异，可是当数据量大的状况下，这种状况就是极为糟糕的了。编程

一、应尽可能避免在 where 子句中对字段进行 null 值判断，不然将致使引擎放弃使用索引而进行全表扫描，如：缓存

select id from t where num is null

能够在num上设置默认值0，确保表中num列没有null值，而后这样查询：

select id from t where num = 0

二、应尽可能避免在 where 子句中使用!=或<>操做符，不然将引擎放弃使用索引而进行全表扫描。优化器将没法经过索引来肯定将要命中的行数,所以须要搜索该表的全部行。服务器

三、应尽可能避免在 where 子句中使用 or 来链接条件，不然将致使引擎放弃使用索引而进行全表扫描，如：网络

select id from t where num = 10 or num = 20

能够这样查询：

select id from t where num = 10
union all
select id from t where num = 20

四、in 和 not in 也要慎用，由于IN会使系统没法使用索引,而只能直接搜索表中的数据。如：并发

select id from t where num in (1, 2, 3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

五、尽可能避免在索引过的字符数据中，使用非打头字母搜索。这也使得引擎没法利用索引。

见以下例子：

SELECT * FROM T1 WHERE NAME LIKE‘ % L % ’ SELECT * FROM T1 WHERE SUBSTING(NAME, 2, 1) = ’L’ SELECT * FROM T1 WHERE NAME LIKE‘ L % ’

即便NAME字段建有索引，前两个查询依然没法利用索引完成加快操做，引擎不得不对全表全部数据逐条操做来完成任务。而第三个查询可以使用索引来加快操做。

六、必要时强制查询优化器使用某个索引，如在 where 子句中使用参数，也会致使全表扫描。由于SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，若是在编译时创建访问计划，变量的值仍是未知的，于是没法做为索引选择的输入项。以下面语句将进行全表扫描：

select id from t where num = @num

能够改成强制查询使用索引：

select id from t with(index(索引名)) where num = @num

七、应尽可能避免在 where 子句中对字段进行表达式操做，这将致使引擎放弃使用索引而进行全表扫描。如：

SELECT * FROM T1 WHERE F1 / 2 = 100

应改成:

SELECT * FROM T1 WHERE F1 = 100 * 2
SELECT * FROM RECORD WHERE SUBSTRING(CARD_NO, 1, 4) = ’5378’

应改成:

SELECT * FROM RECORD WHERE CARD_NO LIKE‘ 5378 % ’ SELECT member_number, first_name, last_name FROM members WHERE DATEDIFF(yy, datofbirth, GETDATE()) > 21

应改成:

SELECT member_number, first_name, last_name FROM members WHERE dateofbirth < DATEADD(yy, -21, GETDATE())

即：任何对列的操做都将致使表扫描，它包括数据库函数、计算表达式等等，查询时要尽量将操做移至等号右边。

八、应尽可能避免在where子句中对字段进行函数操做，这将致使引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name, 1, 3) = 'abc'--name以abc开头的id
select id from t where datediff(day, createdate, '2005-11-30') = 0--‘2005 - 11 - 30’ 生成的id

应改成:

select id from t where name like 'abc%'
select id from t where createdate >= '2005-11-30'
and createdate < '2005-12-1'

九、不要在 where 子句中的“=”左边进行函数、算术运算或其余表达式运算，不然系统将可能没法正确使用索引。

十、在使用索引字段做为条件时，若是该索引是复合索引，那么必须使用到该索引中的第一个字段做为条件时才能保证系统使用该索引，不然该索引将不会被使用，而且应尽量的让字段顺序与索引顺序相一致。

十一、不少时候用 exists是一个好的选择：

elect num from a where num in (select num from b)

用下面的语句替换：

select num from a where exists(select 1 from b where num = a.num) SELECT SUM(T1.C1) FROM T1 WHERE( (SELECT COUNT( * ) FROM T2 WHERE T2.C2 = T1.C2 > 0) SELECT SUM(T1.C1) FROM T1WHERE EXISTS( SELECT * FROM T2 WHERE T2.C2 = T1.C2)

二者产生相同的结果，可是后者的效率显然要高于前者。由于后者不会产生大量锁定的表扫描或是索引扫描。

若是你想校验表里是否存在某条纪录，不要用count(*)那样效率很低，并且浪费服务器资源。能够用EXISTS代替。如：

IF(SELECT COUNT( * ) FROM table_name WHERE column_name = 'xxx')

能够写成：

IF EXISTS(SELECT * FROM table_name WHERE column_name = 'xxx')

常常须要写一个T_SQL语句比较一个父结果集和子结果集，从而找到是否存在在父结果集中有而在子结果集中没有的记录，如：

SELECT a.hdr_key FROM hdr_tbl a-- --tbl a 表示tbl用别名a代替
WHERE NOT EXISTS(SELECT * FROM dtl_tbl b WHERE a.hdr_key = b.hdr_key) SELECT a.hdr_key FROM hdr_tbl a LEFT JOIN dtl_tbl b ON a.hdr_key = b.hdr_key WHERE b.hdr_key IS NULL
SELECT hdr_key FROM hdr_tbl WHERE hdr_key NOT IN(SELECT hdr_key FROM dtl_tbl)

三种写法均可以获得一样正确的结果，可是效率依次下降。

十二、尽可能使用表变量来代替临时表。若是表变量包含大量数据，请注意索引很是有限（只有主键索引）。

1三、避免频繁建立和删除临时表，以减小系统表资源的消耗。

1四、临时表并非不可以使用，适当地使用它们可使某些例程更有效，例如，当须要重复引用大型表或经常使用表中的某个数据集时。可是，对于一次性事件，最好使用导出表。

1五、在新建临时表时，若是一次性插入数据量很大，那么可使用 select into 代替 create table，避免形成大量 log ，以提升速度；若是数据量不大，为了缓和系统表的资源，应先create table，而后insert。

1六、若是使用到了临时表，在存储过程的最后务必将全部的临时表显式删除，先 truncate table ，而后 drop table ，这样能够避免系统表的较长时间锁定。

1七、在全部的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每一个语句后向客户端发送 DONE_IN_PROC 消息。

1八、尽可能避免大事务操做，提升系统并发能力。

1九、尽可能避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

20、避免使用不兼容的数据类型。例如float和int、char和varchar、binary和varbinary是不兼容的（条件判断时）。数据类型的不兼容可能使优化器没法执行一些原本能够进行的优化操做。例如:

SELECT name FROM employee WHERE salary > 60000

在这条语句中,如salary字段是money型的,则优化器很难对其进行优化,由于60000是个整型数。咱们应当在编程时将整型转化成为钱币型,而不要等到运行时转化。

2一、充分利用链接条件（条件越多越快），在某种状况下，两个表之间可能不仅一个的链接条件，这时在 WHERE 子句中将链接条件完整的写上，有可能大大提升查询速度。

例：

SELECT SUM(A.AMOUNT) FROM ACCOUNT A, CARD B WHERE A.CARD_NO = B.CARD_NO SELECT SUM(A.AMOUNT) FROM ACCOUNT A, CARD B WHERE A.CARD_NO = B.CARD_NO AND A.ACCOUNT_NO = B.ACCOUNT_NO

第二句将比第一句执行快得多。

2二、使用视图加速查询

把表的一个子集进行排序并建立视图，有时能加速查询。它有助于避免多重排序操做，并且在其余方面还能简化优化器的工做。例如：

SELECT cust.name， rcvbles.balance，…… other columns FROM cust， rcvbles WHERE cust.customer_id = rcvlbes.customer_id AND rcvblls.balance > 0
AND cust.postcode > “98000” ORDER BY cust.name

若是这个查询要被执行屡次而不止一次，能够把全部未付款的客户找出来放在一个视图中，并按客户的名字进行排序：

CREATE VIEW DBO.V_CUST_RCVLBES AS
SELECT cust.name， rcvbles.balance，…… other columns FROM cust， rcvbles WHERE cust.customer_id = rcvlbes.customer_id AND rcvblls.balance > 0
ORDER BY cust.name

而后如下面的方式在视图中查询：

SELECT＊ FROM V_CUST_RCVLBES WHERE postcode > “98000”

视图中的行要比主表中的行少，并且物理顺序就是所要求的顺序，减小了磁盘I/O，因此查询工做量能够获得大幅减小。

2三、能用DISTINCT的就不用GROUP BY （group by 操做特别慢）

SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID

可改成：

SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10

2四、能用UNION ALL就不要用UNION

UNION ALL不执行SELECT DISTINCT函数，这样就会减小不少没必要要的资源

2五、尽可能不要用SELECT INTO语句。

SELECT INOT 语句会致使表锁定，阻止其余用户访问该表。

上面咱们提到的是一些基本的提升查询速度的注意事项,可是在更多的状况下,每每须要反复试验比较不一样的语句以获得最佳方案。最好的方法固然是测试，看实现相同功能的SQL语句哪一个执行时间最少，可是数据库中若是数据量不多，是比较不出来的，这时能够用查看执行计划，即：把实现相同功能的多条SQL语句考到查询分析器，按CTRL+L看查所利用的索引，表扫描次数（这两个对性能影响最大），整体上看询成本百分比便可。

3、算法的优化

尽可能避免使用游标，由于游标的效率较差，若是游标操做的数据超过1万行，那么就应该考虑改写。.使用基于游标的方法或临时表方法以前，应先寻找基于集的解决方案来解决问题，基于集的方法一般更有效。与临时表同样，游标并非不可以使用。对小型数据集使用 FAST_FORWARD 游标一般要优于其余逐行处理方法，尤为是在必须引用几个表才能得到所需的数据时。在结果集中包括“合计”的例程一般要比使用游标执行的速度快。若是开发时间容许，基于游标的方法和基于集的方法均可以尝试一下，看哪种方法的效果更好。

游标提供了对特定集合中逐行扫描的手段，通常使用游标逐行遍历数据，根据取出的数据不一样条件进行不一样的操做。尤为对多表和大表定义的游标（大的数据集合）循环很容易使程序进入一个漫长的等特甚至死机。

在有些场合，有时也非得使用游标，此时也可考虑将符合条件的数据行转入临时表中，再对临时表定义游标进行操做，可时性能获得明显提升。

（例如：对内统计初版）

封装存储过程

4、创建高效的索引

建立索引通常有如下两个目的：维护被索引列的惟一性和提供快速访问表中数据的策略。大型数据库有两种索引即簇索引和非簇索引，一个没有簇索引的表是按堆结构存储数据，全部的数据均添加在表的尾部，而创建了簇索引的表，其数据在物理上会按照簇索引键的顺序存储，一个表只容许有一个簇索引，所以，根据B树结构，能够理解添加任何一种索引均能提升按索引列查询的速度，但会下降插入、更新、删除操做的性能，尤为是当填充因子（Fill Factor）较大时。因此对索引较多的表进行频繁的插入、更新、删除操做，建表和索引时因设置较小的填充因子，以便在各数据页中留下较多的自由空间，减小页分割及从新组织的工做。

索引是从数据库中获取数据的最高效方式之一。95% 的数据库性能问题均可以采用索引技术获得解决。做为一条规则，我一般对逻辑主键使用惟一的成组索引，对系统键（做为存储过程）采用惟一的非成组索引，对任何外键列[字段]采用非成组索引。不过，索引就象是盐，太多了菜就咸了。你得考虑数据库的空间有多大，表如何进行访问，还有这些访问是否主要用做读写。

实际上，您能够把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引：汇集索引（clustered index，也称聚类索引、簇集索引）和非汇集索引（nonclustered index，也称非聚类索引、非簇集索引）。下面，咱们举例来讲明一下汇集索引和非汇集索引的区别：

其实，咱们的汉语字典的正文自己就是一个汇集索引。好比，咱们要查“安”字，就会很天然地翻开字典的前几页，由于“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就天然地排在字典的前部。若是您翻完了全部以“a”开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字；一样的，若是查“张”字，那您也会将您的字典翻到最后部分，由于“张”的拼音是“zhang”。也就是说，字典的正文部分自己就是一个目录，您不须要再去查其余目录来找到您须要找的内容。

咱们把这种正文内容自己就是一种按照必定规则排列的目录称为“汇集索引”。

若是您认识某个字，您能够快速地从自动中查到这个字。但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而须要去根据“偏旁部首”查到您要找的字，而后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并非真正的正文的排序方法，好比您查“张”字，咱们能够看到在查部首以后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码倒是63页，“张”的下面是“弩”字，页面是390页。很显然，这些字并非真正的分别位于“张”字的上下方，如今您看到的连续的“驰、张、弩”三字实际上就是他们在非汇集索引中的排序，是字典正文中的字在非汇集索引中的映射。咱们能够经过这种方式来找到您所须要的字，但它须要两个过程，先找到目录中的结果，而后再翻到您所须要的页码。

咱们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非汇集索引”。

进一步引伸一下，咱们能够很容易的理解：每一个表只能有一个汇集索引，由于目录只能按照一种方法进行排序。

（一）什么时候使用汇集索引或非汇集索引

下面的表总结了什么时候使用汇集索引或非汇集索引（很重要）。

事实上，咱们能够经过前面汇集索引和非汇集索引的定义的例子来理解上表。如：返回某范围内的数据一项。好比您的某个表有一个时间列，刚好您把聚合索引创建在了该列，这时您查询2004年1月1日至2004年10月1日之间的所有数据时，这个速度就将是很快的，由于您的这本字典正文是按日期进行排序的，聚类索引只须要找到要检索的全部数据中的开头和结尾数据便可；而不像非汇集索引，必须先查到目录中查到每一项数据对应的页码，而后再根据页码查到具体内容。

（二）结合实际，谈索引使用的误区

理论的目的是应用。虽然咱们刚才列出了什么时候应使用汇集索引或非汇集索引，但在实践中以上规则却很容易被忽视或不能根据实际状况进行综合分析。下面咱们将根据在实践中遇到的实际问题来谈一下索引使用的误区，以便于你们掌握索引创建的方法。

一、主键就是汇集索引

这种想法笔者认为是极端错误的，是对汇集索引的一种浪费。虽然SQL SERVER默认是在主键上创建汇集索引的。

一般，咱们会在每一个表中都创建一个ID列，以区分每条数据，而且这个ID列是自动增大的，步长通常为1。咱们的这个办公自动化的实例中的列Gid就是如此。此时，若是咱们将这个列设为主键，SQL SERVER会将此列默认为汇集索引。这样作有好处，就是可让您的数据在数据库中按照ID进行物理排序，但笔者认为这样作意义不大。

显而易见，汇集索引的优点是很明显的，而每一个表中只能有一个汇集索引的规则，这使得汇集索引变得更加珍贵。

从咱们前面谈到的汇集索引的定义咱们能够看出，使用汇集索引的最大好处就是可以根据查询要求，迅速缩小查询范围，避免全表扫描。在实际应用中，由于ID号是自动生成的，咱们并不知道每条记录的ID号，因此咱们很难在实践中用ID号来进行查询。这就使让ID号这个主键做为汇集索引成为一种资源浪费。其次，让每一个ID号都不一样的字段做为汇集索引也不符合“大数目的不一样值状况下不该创建聚合索引”规则；固然，这种状况只是针对用户常常修改记录内容，特别是索引项的时候会负做用，但对于查询速度并无影响。

在办公自动化系统中，不管是系统首页显示的须要用户签收的文件、会议仍是用户进行文件查询等任何状况下进行数据查询都离不开字段的是“日期”还有用户自己的“用户名”。

一般，办公自动化的首页会显示每一个用户还没有签收的文件或会议。虽然咱们的where语句能够仅仅限制当前用户还没有签收的状况，但若是您的系统已创建了很长时间，而且数据量很大，那么，每次每一个用户打开首页的时候都进行一次全表扫描，这样作意义是不大的，绝大多数的用户1个月前的文件都已经浏览过了，这样作只能徒增数据库的开销而已。事实上，咱们彻底可让用户打开系统首页时，数据库仅仅查询这个用户近3个月来未阅览的文件，经过“日期”这个字段来限制表扫描，提升查询速度。若是您的办公自动化系统已经创建的2年，那么您的首页显示速度理论上将是原来速度8倍，甚至更快。

二、只要创建索引就能显著提升查询速度

事实上，咱们能够发现上面的例子中，第二、3条语句彻底相同，且创建索引的字段也相同；不一样的仅是前者在fariqi字段上创建的是非聚合索引，后者在此字段上创建的是聚合索引，但查询速度却有着天壤之别。因此，并不是是在任何字段上简单地创建索引就能提升查询速度。

从建表的语句中，咱们能够看到这个有着1000万数据的表中fariqi字段有5003个不一样记录。在此字段上创建聚合索引是再合适不过了。在现实中，咱们天天都会发几个文件，这几个文件的发文日期就相同，这彻底符合创建汇集索引要求的：“既不能绝大多数都相同，又不能只有极少数相同”的规则。由此看来，咱们创建“适当”的聚合索引对于咱们提升查询速度是很是重要的。

三、把全部须要提升查询速度的字段都加进汇集索引，以提升查询速度

上面已经谈到：在进行数据查询时都离不开字段的是“日期”还有用户自己的“用户名”。既然这两个字段都是如此的重要，咱们能够把他们合并起来，创建一个复合索引（compound index）。

不少人认为只要把任何字段加进汇集索引，就能提升查询速度，也有人感到迷惑：若是把复合的汇集索引字段分开查询，那么查询速度会减慢吗？带着这个问题，咱们来看一下如下的查询速度（结果集都是25万条数据）：（日期列fariqi首先排在复合汇集索引的起始列，用户名neibuyonghu排在后列）

咱们能够看到若是仅用汇集索引的起始列做为查询条件和同时用到复合汇集索引的所有列的查询速度是几乎同样的，甚至比用上所有的复合索引列还要略快（在查询结果集数目同样的状况下）；而若是仅用复合汇集索引的非起始列做为查询条件的话，这个索引是不起任何做用的。固然，语句一、2的查询速度同样是由于查询的条目数同样，若是复合索引的全部列都用上，并且查询结果少的话，这样就会造成“索引覆盖”，于是性能能够达到最优。同时，请记住：不管您是否常用聚合索引的其余列，但其前导列必定要是使用最频繁的列。

（三）其余注意事项

“水可载舟，亦可覆舟”，索引也同样。索引有助于提升检索性能，但过多或不当的索引也会致使系统低效。由于用户在表中每加进一个索引，数据库就要作更多的工做。过多的索引甚至会致使索引碎片。

因此说，咱们要创建一个“适当”的索引体系，特别是对聚合索引的建立，更应精益求精，以使您的数据库能获得高性能的发挥

【推荐阅读】