数据库性能优化-摘录

1. 树表设计，建议在树表中增加字段TreePath

　　2. 记录超过一百万的表要考虑是否需要使用分区表。对于能够明确确定分区字段，并且经常通过分区访问记录的。分区表会提高查询性能。

3. 反范式

　　　　　　在第三范式设计的基础上，针对具体的数据情况和应用访问数据情况进行的设计，使用适当的数据冗余和数据不一致风险，提供更高的数据访问性能，以保障应用的性能要求。

4. 避免使用Select *

5. 使用Set代替Select进行赋值

6. 必要的时候，使用关键字”NOLOCK”避免锁定

7. 不需要排序的时候，不要使用关键字”Order

8. 字符列字节长度小于等于10时，使用char或者nchar

9. 字符列字节长度大于10时，使用varchar或者nvarchar（实际长度设置需要根据业务需要仔细规划，长度关系到存储空间，关系到缓存占用）

10. 需要使用小数时，不要使用数据类型float，而使用decimal

11. 日期存储时，使用datetime，不要使用字符类型

12. 请不要使用ntext，text，image数据类型，而使用nvarchar(max)，varchar(max)，varbinary(max)类型

13. 考虑自增长列的长度（例如：部件关系表数据量会溢出int类型数据的范围）

14. 索引

*组合或者引用关系的子表（数据量较大的时候），需要在关联主表的列上建立非聚集索引（如订单明细表中的产品ID字段、订单明细表中关联的订单ID字段）

*索引键的大小不能超过900个字节，当列表的大小超过900个字节或者若干列的和超过900个字节时，数据库将报错。

*表中如果建有大量索引将会影响INSERT、UPDATE和DELETE语句的性能，因为在表中的数据更改时，所有的索引都将必须进行适当的调整。需要避免对经常更新的表进行过多的索引，并且索引应保持较窄，就是说：列要尽可能的少。

*为经常用于查询的谓词创建索引，如用于下拉参照快速查找的code、name等。在平台现有下拉参照的查询sql语句中的like条件语句要改成不带前置通配符。还有需要关注Order By和Group By谓词的索引设计，Order By和Group By的谓词是需要排序的，某些情况下为Order By和Group By的谓词建立索引，会避免查询时的排序动作。

*对于内容基本重复的列，比如只有1和0，禁止建立索引，因为该索引选择性极差，在特定的情况下会误导优化器做出错误的选择，导致查询速度极大下降。

*当一个索引有多个列构成时，应注意将选择性强的列放在前面。仅仅前后次序的不同，性能上就可能出现数量级的差异。

*对小表进行索引可能不能产生优化效果，因为查询优化器在遍历用于搜索数据的索引时，花费的时间可能比执行简单的表扫描还长，设计索引时需要考虑表的大小。记录数不大于100的表不要建立索引。频繁操作的小数量表不建议建立索引（记录数不大于5000条）

阻塞原因

在默认事务隔离情况下，数据库事务越长，一方面独占锁被持有的时间越长，写操作阻塞读操作的机会就越多；另一方面，在默认的读提交隔离模式下，读操作使用共享锁与独占锁不兼容，读操作也会阻塞写操作。

阻塞也是死锁产生的基本条件，改善了阻塞就能有效减少死锁。

在软件开发后期，在对大数据量的集成测试工程中，通过活动查看器可以观察到阻塞情况，主要产生阻塞的原因就是读和写相互阻塞在对同一个大表的操作上。因此对于读写阻塞问题需要加以足够考虑。

减少阻塞一些指导原则

整体原则

*归结起来也依赖于代码、sql的优化，一方面要使逻辑代码优化到最快。另一方面，一个耗时较长的sql语句将会阻塞全部用户等待几十秒甚至几分钟，针对查询sql语句的优化也是最重要的。

*修改批量操作的需求，批量操作耗时和记录数量是成正比的。为此设计时要避免在同一个自动事务服务方法中做批量的循环操作，可以将循环操作放到UI控制端，这一就使一个长事务变成多个短小的事务，将减少阻塞的机会。

*减少读写操作使用锁的数量，比如减少批更新操作中修改行的数量，保证行锁定少，同时减少锁升级至表锁的机会。

*一些耗时大、锁定数据多的操作需要避免和正常业务操作冲突，可以使用调度计划在系统闲置的时候来运行，或者使用互斥机制来保证其它用户暂退出操作独立运行，视业务情况而定。

读写锁阻塞的处理原则

*减少读操作需要的共享锁

1、将事务隔离级别由默认读提交（ReadCommited）修改成读未提交（ReadUnCommited）,将不会有读写阻塞，但是会造成读取其它事务未提交的数据。

方法一：如果服务方法为自动事务，则在服务方法特性SerivceMethod指定IsolationLevel属性为ReadUnCommited

方法二：对于使用手工事务的情况，使用DBSession接口带隔离级别参数的方法session.beginTransaction(IsolationLevel), level值为 ReadUnCommited.

2、在select语句中带NoLock提示，事务内无锁提示也不会有读写阻塞，与上面一样也会有脏读。

Select上加无锁控制，在执行select操作时加 with(nolock)。如：SELECT * FROM person WITH(NoLock)

上面两种方式需要平台和业务涉及人员根据情况使用。事务隔离级别控制粒度较粗，使用时需要考虑对多个select语句的影响，NoLock提示控制单个select语句，粒度更细。

*在sqlserver2005中使用基于行版本的快照隔离模式

在快照模式下，读取数据不再使用共享锁，阻塞的现象能大大减少。

方法：在建立数据库后执行下面命令：

ALTER DATABASE 替换的数据库名 SET READ_COMMITIED_SNAPSHOT ON;

ALTER DATABASE 替换的数据库名 SET ALLOW_SNAPSHOT_ISOLATION ON;

注意：sqlserver2005和with(no lock)语句，这两种方案都能够避免阻塞，但是这两种方式是有区别的。

举个小例子说明一下：比如数据库表T1中有两个字段Col1且其默认值为1.此时恰好有个A事务通过Update语句修改表T1的Col字段值为2，但还未提交，如下：

A事务：

{

Begion tran

Update T1 set Col1=2

//Commit;//注释掉此句，模拟A事务未提交。

}

这时如果另一个事务如果使用Sql server2005的快照模式获取T1表Col1字段的值则取到的值是之前默认的1；而使用with（no lock）的方式获取到Col1字段的值为2.

其实不管使用哪种方式，得到的数据都不确保是准确的，这要取决于A事务是否执行并提交成功。

==============================================================================================

死锁原因：

死锁是由两个相互阻塞的线程组成，它们互相等待对方完成，一般死锁情况下两个数据库事务之间存在着相反的操作。sqlserver中死锁监视器定时检查死锁，如果发现死锁，将选择其中回滚消耗最小的任务，这时候发生1025数据库错误。可以通过启用sqlserver2005快照模式，避免一些读/写的逆向阻塞造成的死锁.但是对于一些写/写阻塞的死锁可能无法解决，很多时候需要从业务的角度来避免一些写/写的逆向操作阻塞情况。

死锁问题的解决很困难，但是可以通过一些手段来使死锁最小化。

死锁最小化方法：

从理论上讲，数据库死锁无法避免，但是可以遵循一定原则使死锁发生的概率尽量降低。

写/写死锁

*用相同的顺序访问对象，如果涉及到多于一张表的操作，要保证事务中都按照相同的顺序访问这些表。

*减少一个事务中的大批量更新的操作，大批量操作写操作涉及记录独占锁太多而且一直到事务结束才能释放，更容易与其它事务造成死锁。

读/写死锁（原则上与前面提到的减少读/写阻塞方式一致）

*去掉读操作的共享锁

最佳方式是使用sql2005的快照模式，其次方式是使用读未提交隔离模式或使用NOLock提示，需要平台和业务设计时依据情况进行sql组织的设计。

按照相同的顺序访问对象可以避免相互持有对方请求资源的情况发生。例如一个操作主从表的处理流程，涉及查询和修改两个步骤。如果查询时是先查主表再查从表，则修改也应先修改主表再修改从表。

另一个降低事务大小的一个主要手段，是将查询操作尽可能地提前（包括使用一些中间变量记录下查询结果提供后续使用），而把插入、修改等操作集中在方法靠后的部分。这样，可以让一个事务需要持有独占锁的时间尽可能缩短，减少死锁的发生概率。

==============================================================================================

1、参数化sql

对于一般简单查询，数据库能自动参数啊以重用计划缓存，如：

在sqlserver内部能自动参数化这个查询，SELECT * FROM table WHERE id=@1

但是一旦sql语句中带有join、union、top……等关键字，sqlserver内部将不会自动参数化。

在sql2005中，通过alter database XXX set paramenterization forced的强制参数化命令能够将所有sql中的常量参数化，但是强制参数化会因为常量类型不一致造成查询结果误差。

2、使用查询中的索引有效

a）、单列索引使用原则

单列索引能响应大部分的简单比较，包括等价和不等价。对于like操作无前置通配符也是有效的。如：

能有效使用索引的条件语句：

[col1]=1 [col1]>100 [col1] between 0 and 100 [col1] like 'abc%'

下列条件语句不会有效利用索引:

ABS([col1])=1 [col1]+1>100 [col1]+10 between 0 and 100 [col1] like '%abc%'

b)、避免在WHERE字句中对字段进行函数或表达式操作

看一下下面效率低下的例子和其解决方法

效率低下的写法：

SELECT * FROM Production.Product WHERE SafetystockLevel/2=100

高效写法：

SELECT * FROM Production.Product WHERE SafetystockLevel=2*100

下面是SQLServer2005的优化报告

类似的例子：

效率低下的写法	高效的写法
SELECT * FROM Production.Product WHERE SubString(name,1,3)=’abc’	SELECT * FROM Production.Product WHERE name like ’abc%’
SELECT * FROM Production.Product WHERE datediff(day ,modifiedDate,’2005-11-30’)=0	SELECT * FROM Production.Product WHERE modifiedDate>=’2005-11-30’ andmodifiedDate<’2005-12-1’

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

避免使用！=或<>、is null 或is not null、 in、not in等这样的操作符，因为这会是系统无法使用索引，而只能直接搜索表中数据。

例如：

select id from employee where id!=’B%’

优化器将无法通过索引来确定将要命中的行数，因此需要搜索该表的所有行。

c)、多列索引使用原则

则应考虑列的顺序。用于等于(=)、大于(>)、小于(<)或between搜索条件的where 字句或者参与联接的列应该放在最前面。其它列应该基于其非重要级别进行排序，就是说，从最不重复的列到最重复的列。

例如：

如果表中存在索引定义为LastName、FirstName,则该索引在搜索条件为where LastName=’Smith’或where LastName=Smith and FirstName like ’j%’时将很有用。不过，查询优化器不会将此索引用于基于FirstName(where FirstName=’Jane’)而搜索的查询。

==============================================================================================

一、SQL拼写建议

1、查询时不返回不需要的行、列

业务代码要根据实际情况尽量减少对表的访问行数，最小化结果集，在查询时，不要过多地使用通配符如：select * from table1语句，要用到几列就选择几列，如：select col1,col2 from table1;在可能的情况下尽量限制结果集行数如：select top 100 col1,col2,col3 from talbe2,因为某些情况下用户是不需要那么多的数据的。

2、合理使用EXISTS, NOT EXISTS字句

如下所示：

SELECT SUM(T1.C1) FROM T1 WHERE ((SELECT COUNT(*) FROM T2 WHERE T2.C2=T1.C2)>0)

SELECT SUM(T1.C1) FROM T1 WHERE EXISTS(SELECT * FROM T2 WHERE T2.C2=T1.C2)

两种产生相同的结果，但是后者的效率显然要高过于前者。银行后者不会产生大量锁定的表扫描或是索引扫描。

经常需要些一个T_SQLL语句比较一个父结果集和子结果集，从而找到是否存在在父结果集中有而在子结果集中乜嘢的记录，如：

SELECT _a.hdr_key FROM hdr_tb1 a -----------tb1 a 表示tb1用别名a代替

WHERE NOT EXISTS (SELECT * FROM dt1_tb1 b WHERE a.hdr_key = b.hdr_key)

SELECT _a.hdr_key FROM hdr_tb1 a -----------tb1 a 表示tb1用别名a代替

LEFT JION dt1_tb1 b ON a.hdr_key = b.hdr_key WHERE b.hdr_key IS NULL

SELECT hdr_key FROM hdr_tb1

WHERE hdr_key NOT IN (SELECT hdr_key FROM dt1_tb1)

三种写法都可以得到同样的结果集，但是效率是依次降低

3、充分利用连接条件

在某种情况下，两个表之间可能不止一个的连接条件，这时在where 字句中将谅解条件完整的写上，有可能大大提高查询速度。

例：

a)、SELECT SUM(A.AMOUNT) FROM ACCOUNT A left jion CARD B on A.CARD_NO = B.CARD_NO

b)、SELECT SUM(A.AMOUNT) FROM ACCOUNT A left jion CARD B on A.CARD_NO = B.CARD_NO AND A.ACCOUNT_NO = B.ACCOUNT_NO

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

4、WHERE 字句中关系运算符的选择

a)、在关系运算中，尽量使用=，尽量不要使用<>。

b)、WHERE字句中尽量不要使用NOT运算符，如：NOT IN ,NOT EXISTS, NOT>、NOT<等等NOT运算符一般可以去除。如NOT SALARY >10000K可以改为：salary<=100,如避免使用NOT IN,可以使用 left outer jion代替它。

c)、where 字句中条件表达式间逻辑关系为AND时，将条件为假的概率高的放在前面，概率相同、条件计算简单的放在前面。

d)、尽可能不要用Order by字句。使用Order by时，尽量减少列数、尽量减少排序数据行数、排序字段尽量是数字型（尽量不要是字符型）。GROUP BY、 SELECT DITINCT、UNION等字句，也经常导致Order运算。

e)、不要使用Select count(*)方式来判断记录是否存在，建议使用Select top 1 from table1 where ……。

f)、不要使用Group by而没有聚合列。

g)、避免Select 语句的Where 字句条件用于假。如：where 1=0;

h)、如果有多表连接时，应该有主从表之分，并尽量从一个表读取数，如select a.col1,a.col2 from a jion b on a.col3=b.col4 where b.col5=’a’.

i)、在where 字句中，如果有多个过滤条件，应将所有列或过滤记录数量最多的条件应该放在前面。

二、使用Truncate清空表

Truncate会将表中记录全部清空，而不能有选择性的删除指定记录。而DELETE可以指定删除的记录。由于Truncate操作在TransactionLog中只记录被Truncate的页号，而DELETE需要记载被删除记录的详细内容，因此Truncate会比DELETE更迅速。对大数据表使用Truncate，效果更加明显。Truncate Table只会删除表中记录。而不会对表的索引和结构造成影响。

三、Union和Union all

Union将两个结果集合并后，会消除重复记录，而Union all不会消除重复记录，而是直接将两个结果集直接合并。明确得知两个结果集中没有重复记录或者重复记录不影响使用，建议使用Union all 代替Union。因为Union在消除重复记录的过程中需要进行排序过滤操作，对大结果集这种排序操作会非常影响性能。下面是Union 和Union all的简单性能比较：

---------------Union

select * from table1 where code=’01’

Union

select * from table1 where code=’02’

---------------Union all

select * from table1 where code=’01’

union all

select * from talbe1 where code=’02’

==============================================================================================