数据库的优化方法

常见的数据库优化方法：索引（数据库），缓存，分表，分库，sql优化。

 

索引：创建索引一般有以下两个目的：维护被索引列的唯一性和提供快速访问表中数据的策略。95%的数据库能问题都可以采用索引技术得到解决。索引有助于提高检索性能，但过多或不当的索引也会导致系统低效。因为用户在表中每加进一个索引，数据库就要做更多的工作。过多的索引甚至会导致索引碎片。 

 

缓存：hibernate，spring3有缓存模块

 

分表：针对每个时间周期产生大量的数据，可以考虑采用一定的策略将数据存到多个数据表中。

 

分库：就是将系统按照模块相关的特征分布到不同的数据中，以提高系统整体负载能力。

 

sql优化：

 1.in 和not in也要慎用，因为IN会使系统无法使用索引,而只能直接搜索表中的数据。如：

select id from t where num in(1,2,3)对于连续的数值，能用 between就不要用 in 了：

select id from t where num between 1 and3

2.当判断真假是，如果带and或者or :

（当存在 “where条件1and 条件2” 时，数据库先执行右边的语句）

and尽量把假的放到右边（一个为假就为假）  Or尽量把为真的放到右边（一个为真就为真）

 

3.应尽量避免在 where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

SELECT * FROM T1 WHERE F1/2=100 

应改为: 

SELECT * FROM T1 WHERE F1=100*2

SELECT * FROM RECORD WHERESUBSTRING(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()) 

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

 

4.很多时候用 exists是一个好的选择：

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

用下面的语句替换：

select num from a where exists(select 1from b where num=a.num)

SELECT SUM(T1.C1)FROM T1 WHERE( 

(SELECT COUNT(*)FROM T2 WHERET2.C2=T1.C2>0) 

SELECT SUM(T1.C1) FROM T1WHEREEXISTS( 

SELECT * FROM T2 WHERET2.C2=T1.C2) 

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

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

IF (SELECT COUNT(*) FROM table_nameWHERE column_name = 'xxx') 

可以写成： 

IF EXISTS (SELECT * FROM table_nameWHERE column_name = 'xxx')

5.充分利用连接条件，在某种情况下，两个表之间可能不只一个的连接条件，这时在 WHERE子句中将连接条件完整的写上，有可能大大提高查询速度。 

例： 

SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARDB WHERE A.CARD_NO = B.CARD_NO 

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

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

6.使用视图加速查询 

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

SELECT cust.name，rcvbles.balance，……othercolumns 

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，……othercolumns 

FROM cust，rcvbles 

WHERE cust.customer_id =rcvlbes.customer_id 

AND rcvblls.balance>0 

ORDER BY cust.name 

然后以下面的方式在视图中查询： 

SELECT ＊ FROMV_CUST_RCVLBES 

WHERE postcode>“98000” 

视图中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘I/O，所以查询工作量可以得到大幅减少。