大数据量高并发訪问的数据库优化方法

7.应尽量避免在 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())?
即：不论什么对列的操作都将导致表扫描，它包含数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。

8.应尽量避免在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‘

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

10.在使用索引字段作为条件时，假设该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才干保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

11.非常多时候用 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(*)那样效率非常低，并且浪费server资源。能够用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)?
三种写法都能够得到同样正确的结果，可是效率依次降低。

12.尽量使用表变量来取代暂时表。假设表变量包含大量数据，请注意索引非常有限（仅仅有主键索引）。

13.避免频繁创建和删除暂时表。以降低系统表资源的消耗。

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

15.在新建暂时表时，假设一次性插入数据量非常大。那么能够使用 select into 取代 create table。避免造成大量 log ，以提高速度。假设数据量不大。为了缓和系统表的资源。应先create table。然后insert。

16.假设使用到了暂时表，在存储过程的最后务必将全部的暂时表显式删除。先 truncate table 。然后 drop table ，这样能够避免系统表的较长时间锁定。?
17.在全部的存储过程和触发器的開始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。

无需在运行存储过程和触发器的每一个语句后向client发送 DONE_IN_PROC 消息。

18.尽量避免大事务操作，提高系统并发能力。

19.尽量避免向client返回大数据量，若数据量过大。应该考虑相应需求是否合理。

?
20. 避免使用不兼容的数据类型。比如float和int、char和varchar、binary和varbinary是不兼容的。数据类型的不兼容可能使优化器无法运行一些本来能够进行的优化操作。

比如:?
SELECT name FROM employee WHERE salary > 60000?
在这条语句中,如salary字段是money型的,则优化器非常难对其进行优化,由于60000是个整型数。我们应当在编程时将整型转化成为钱币型,而不要等到运行时转化。

21.充分利用连接条件，在某种情况下。两个表之间可能不仅仅一个的连接条件，这时在 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?
第二句将比第一句运行快得多。

22、使用视图加速查询?
把表的一个子集进行排序并创建视图，有时能加速查询。它有助于避免多重排序 操作，并且在其它方面还能简化优化器的工作。比如：
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。所以查询工作量能够得到大幅降低。

23、能用DISTINCT的就不用GROUP BY?
SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID?
可改为：?
SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10

24.能用UNION ALL就不要用UNION?
UNION ALL不运行SELECT DISTINCT函数。这样就会降低非常多不必要的资源?
35.尽量不要用SELECT INTO语句。

?
SELECT INOT 语句会导致表锁定。阻止其它用户訪问该表。

????上面我们提到的是一些主要的提高查询速度的注意事项,可是在很多其它的情况下,往往须要重复试验比較不同的语句以得到最佳方案。

最好的方法当然是測试，看实现同样功能的SQL语句哪个运行时间最少，可是数据库中假设数据量非常少。是比較不出来的，这时能够用查看运行计划。即：把实现同样功能的多条SQL语句考到查询分析器，按CTRL+L看查所利用的索引，表扫描次数（这两个对性能影响最大），整体上看询成本百分比就可以。?
三、算法的优化

尽量避免使用游标，由于游标的效率较差。假设游标操作的数据超过1万行。那么就应该考虑改写。.使用基于游标的方法或暂时表方法之前。应先寻找基于集的解决方式来解决这个问题，基于集的方法通常更有效。

与暂时表一样，游标并非不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其它逐行处理方法，尤其是在必须引用几个表才干获得所需的数据时。

在结果集中包含“合计”的例程通常要比使用游标运行的速度快。假设开发时间同意，基于游标的方法和基于集的方法都能够尝试一下，看哪一种方法的效果更好。
　　游标提供了对特定集合中逐行扫描的手段。一般使用游标逐行遍历数据，依据取出的数据不同条件进行不同的操作。尤其对多表和大表定义的游标（大的数据集合）循环非常easy使程序进入一个漫长的等特甚至死机。?
　 在有些场合，有时也非得使用游标。此时也可考虑将符合条件的数据行转入暂时表中，再对暂时表定义游标进行操作。可时性能得到明显提高。
（比如：对内统计第一版）
封装存储过程

四、建立高效的索引

　 创建索引一般有以下两个目的：维护被索引列的唯一性和提供高速訪问表中数据的策略。

大型数据库有两种索引即簇索引和非簇索引。一个没有簇索引的表是按堆结构存储数据。全部的数据均加入在表的尾部。而建立了簇索引的表。其数据在物理上会依照簇索引键的顺序存储，一个表仅仅同意有一个簇索引。因此。依据B树结构，能够理解加入不论什么一种索引均能提高按索引列查询的速度。但会降低插入、更新、删除操作的性能，尤其是当填充因子（Fill Factor）较大时。所以对索引较多的表进行频繁的插入、更新、删除操作，建表和索引时因设置较小的填充因子，以便在各数据页中留下较多的自由空间，降低页切割及又一次组织的工作。

?
索引是从数据库中获取数据的最高效方式之中的一个。95% 的数据库性能问题都能够採用索引技术得到解决。作为一条规则，我通常对逻辑主键使用唯一的成组索引，对系统键（作为存储过程）採用唯一的非成组索引，对不论什么外键列[字段]採用非成组索引。

只是，索引就象是盐，太多了菜就咸了。

你得考虑数据库的空间有多大，表怎样进行訪问，还有这些訪问是否主要用作读写。?
实际上。您能够把索引理解为一种特殊的文件夹。微软的SQL SERVER提供了两种索引：聚集索引（clustered index。也称聚类索引、簇集索引）和非聚集索引（nonclustered index，也称非聚类索引、非簇集索引）。

以下，我们举例来说明一下聚集索引和非聚集索引的区别：?
其实。我们的汉语字典的正文本身就是一个聚集索引。比方，我们要查“安”字，就会非常自然地翻开字典的前几页。由于“安”的拼音是“an”。而依照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的。那么“安”字就自然地排在字典的前部。

假设您翻完了全部以“a”开头的部分仍然找不到这个字。那么就说明您的字典中没有这个字；同样的，假设查“张”字，那您也会将您的字典翻到最后部分，由于“张”的拼音是“zhang”。也就是说，字典的正文部分本身就是一个文件夹，您不须要再去查其它文件夹来找到您须要找的内容。?
我们把这样的正文内容本身就是一种依照一定规则排列的文件夹称为“聚集索引”。?
假设您认识某个字，您能够高速地从自己主动中查到这个字。但您也可能会遇到您不认识的字。不知道它的发音，这时候，您就不能依照刚才的方法找到您要查的字，而须要去依据“偏旁部首”查到您要找的字。然后依据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首文件夹”和“检字表”而查到的字的排序并非真正的正文的排序方法。比方您查“张”字，我们能够看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63页，“张”的以下是“弩”字，页面是390页。非常显然，这些字并非真正的分别位于“张”字的上下方，如今您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序。是字典正文中的字在非聚集索引中的映射。我们能够通过这样的方式来找到您所须要的字，但它须要两个过程，先找到文件夹中的结果。然后再翻到您所须要的页码。

?
我们把这样的文件夹纯粹是文件夹，正文纯粹是正文的排序方式称为“非聚集索引”。

?
进一步引申一下，我们能够非常easy的理解：每一个表仅仅能有一个聚集索引。由于文件夹仅仅能依照一种方法进行排序。

（一）何时使用聚集索引或非聚集索引?
以下的表总结了何时使用聚集索引或非聚集索引（非常重要）。?
动作描写叙述 使用聚集索引 使用非聚集索引?
列常常被分组排序 应 应?
返回某范围内的数据 应 不应?
一个或极少不同值 不应 不应?
小数目的不同值 应 不应?
大数目的不同值 不应 应?
频繁更新的列 不应 应?
外键列 应 应?
主键列 应 应?
频繁改动索引列 不应 应

其实，我们能够通过前面聚集索引和非聚集索引的定义的样例来理解上表。如：返回某范围内的数据一项。比方您的某个表有一个时间列。恰好您把聚合索引建立在了该列，这时您查询2004年1月1日至2004年10月1日之间的全部数据时。这个速度就将是非常快的，由于您的这本字典正文是按日期进行排序的，聚类索引仅仅须要找到要检索的全部数据中的开头和结尾数据就可以；而不像非聚集索引，必须先查到文件夹中查到每一项数据相应的页码。然后再依据页码查到详细内容。

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

理论的目的是应用。尽管我们刚才列出了何时应使用聚集索引或非聚集索引，但在实践中以上规则却非常easy被忽视或不能依据实际情况进行综合分析。以下我们将依据在实践中遇到的实际问题来谈一下索引使用的误区，以便于大家掌握索引建立的方法。

?
1、主键就是聚集索引?
这样的想法笔者觉得是极端错误的，是对聚集索引的一种浪费。

尽管SQL SERVER默认是在主键上建立聚集索引的。

?
通常，我们会在每一个表中都建立一个ID列，以区分每条数据，并且这个ID列是自己主动增大的，步长一般为1。我们的这个办公自己主动化的实例中的列Gid就是如此。

此时。假设我们将这个列设为主键，SQL SERVER会将此列默觉得聚集索引。

这样做有优点。就是能够让您的数据在数据库中依照ID进行物理排序，但笔者觉得这样做意义不大。

?
显而易见，聚集索引的优势是非常明显的，而每一个表中仅仅能有一个聚集索引的规则。这使得聚集索引变得更加珍贵。

?
从我们前面谈到的聚集索引的定义我们能够看出，使用聚集索引的最大优点就是能够依据查询要求，迅速缩小查询范围，避免全表扫描。

在实际应用中，由于ID号是自己主动生成的。我们并不知道每条记录的ID号，所以我们非常难在实践中用ID号来进行查询。

这就使让ID号这个主键作为聚集索引成为一种资源浪费。

其次，让每一个ID号都不同的字段作为聚集索引也不符合“大数目的不同值情况下不应建立聚合索引”规则；当然，这样的情况仅仅是针对用户常常改动记录内容，特别是索引项的时候会负作用。但对于查询速度并没有影响。?
在办公自己主动化系统中，不管是系统首页显示的须要用户签收的文件、会议还是用户进行文件查询等不论什么情况下进行数据查询都离不开字段的是“日期”还实用户本身的“username”。

?
通常，办公自己主动化的首页会显示每一个用户尚未签收的文件或会议。尽管我们的where语句能够仅仅限制当前用户尚未签收的情况，但假设您的系统已建立了非常长时间。并且数据量非常大，那么，每次每一个用户打开首页的时候都进行一次全表扫描，这样做意义是不大的，绝大多数的用户1个月前的文件都已经浏览过了，这样做仅仅能徒增数据库的开销而已。

其实，我们全然能够让用户打开系统首页时，数据库仅仅查询这个用户近3个月来未阅览的文件。通过“日期”这个字段来限制表扫描，提高查询速度。假设您的办公自己主动化系统已经建立的2年，那么您的首页显示速度理论上将是原来速度8倍。甚至更快。

2、仅仅要建立索引就能显著提高查询速度?
其实。我们能够发现上面的样例中，第2、3条语句全然同样，且建立索引的字段也同样。不同的仅是前者在fariqi字段上建立的是非聚合索引。后者在此字段上建立的是聚合索引。但查询速度却有着天差地别。

所以，并非是在不论什么字段上简单地建立索引就能提高查询速度。
从建表的语句中。我们能够看到这个有着1000万数据的表中fariqi字段有5003个不同记录。

在此字段上建立聚合索引是再合适只是了。在现实中，我们每天都会发几个文件。这几个文件的发文日期就同样，这全然符合建立聚集索引要求的：“既不能绝大多数都同样，又不能仅仅有极少数同样”的规则。

由此看来，我们建立“适当”的聚合索引对于我们提高查询速度是非常重要的。

3、把全部须要提高查询速度的字段都加进聚集索引，以提高查询速度?
上面已经谈到：在进行数据查询时都离不开字段的是“日期”还实用户本身的“username”。既然这两个字段都是如此的重要，我们能够把他们合并起来，建立一个复合索引（compound index）。?
非常多人觉得仅仅要把不论什么字段加进聚集索引，就能提高查询速度。也有人感到迷惑：假设把复合的聚集索引字段分开查询。那么查询速度会减慢吗？带着这个问题，我们来看一下以下的查询速度（结果集都是25万条数据）：（日期列fariqi首先排在复合聚集索引的起始列，usernameneibuyonghu排在后列）?
我们能够看到假设仅用聚集索引的起始列作为查询条件和同一时候用到复合聚集索引的全部列的查询速度是差点儿一样的，甚至比用上全部的复合索引列还要略快（在查询结果集数目一样的情况下）；而假设仅用复合聚集索引的非起始列作为查询条件的话。这个索引是不起不论什么作用的。当然，语句1、2的查询速度一样是由于查询的条目数一样。假设复合索引的全部列都用上。并且查询结果少的话。这样就会形成“索引覆盖”，因而性能能够达到最优。

同一时候，请记住：不管您是否常常使用聚合索引的其它列，但其前导列一定要是使用最频繁的列。

（三）其它注意事项?
“水可载舟。亦可覆舟”。索引也一样。索引有助于提高检索性能，但过多或不当的索引也会导致系统低效。由于用户在表中每加进一个索引。数据库就要做很多其它的工作。

过多的索引甚至会导致索引碎片。?
所以说，我们要建立一个“适当”的索引体系，特别是对聚合索引的创建，更应精益求精，以使您的数据库能得到高性能的发挥

大数据量高并发訪问的数据库优化方法

标签：edit   用户   run   简单   举例   占用   creat   性能   实现