数据库性能优化

关系型数据库性能优化总结

 

对于web应用开发,多数性能瓶颈均出现在数据库上,除了采用分布式架构或云处理(大公司基本上都是),更重要的是平时程序设计时要遵照一些规则,从根本上提高系统的性能,以下总结了一些常用的规则方法,

1、 把数据、日志、索引放到不同的I/O设备上,增加读取速度。数据量(尺寸)越大,提高I/O越重要。

2、 纵向、横向分割表,减少表的尺寸,如:可以把大数据量的字段拆分表。

3、 根据查询条件,建立索引,优化索引、优化访问方式,限制结果集的数据量。注意填充因子要适当(最好是使用默认值0)。索引应该尽量小,尽量使用字节数小的列建索引,不要对有限的几个值的列建单一索引。

4、 OR的字句可以分解成多个查询,并且通过UNION链接多个查询。它们的速度只与是否使用索引有关,如果查询需要用到联合索引,用UNION all执行的效率更高。

5、 在查询SELECT语句中用WHERE子句限制返回的行数,避免表扫描如果返回不必要的数据,则浪费了服务器的I/O资源,加重了网络的负担,降低了性能。如果表很大,在表扫描期间将表锁住,禁止其他的联结访问表,后果很严重。

6、 注意使用DISTINCT,在没有必要时不要用,它同UNION一样会使查询变慢。

7、 IN后面值的列表中,将出现最频繁的值放在最前面,出现最少的放在最后面,减少判断的次数。

8、 一般在GROUP BYHAVING子句之前就能剔除多余的行,所以尽量不要用它们来做剔除行的工作,也就是说尽可能在WHERE中过滤数据。

9、 尽量将数据的处理工作放在服务器上,减少网络的开销,如使用存储过程。存储过程是编译、优化过,并且被组织到一个执行规划里,且存储在数据库中的SQL语句(存储过程是数据库服务器端的一段程序),是控制流语言的集合,速度当然快。

10 不要在一句话里再三地使用相同的函数,浪费资源,将结果放在变量里再调用更快。

11针对大量只读查询操作进行优化的方法:

1)  数据量小的数据,可以考虑不存储在数据库中,而是通过程序常量的方式解决。

当然也可以放在memcache或者redis等缓存中。还有就是hibernate,spring的缓存。

2)    需要存储在数据库中的数据,可以考虑采用物化视图(索引视图)。当DBA在视图上创建索引时,这个视图就被物化(执行)了,并且结果集被永久地保存在唯一索引中,保存方式与一个有聚簇索引的表的保存方式相同。物化视图减除了为引用视图的查询动态建立结果集的开销,优化人员可以在查询中使用视图索引,而不需要在FROM子句中直接指定视图。

3)    数据存储时可以考虑适当的数据冗余,以减少数据库表之间的链接操作,提高查询效率。

4)  针对数据的特点,采取特定的索引类型。例如,位图索引等。

索引注意事项:

<1>.索引是建立在常用的列上,比如你经常根据某一列(用户名,密码)去查数据

<2>.索引相当于字典前面的目录。

<3>.加不加索引要权衡一下是查询多,还是update,insert,delete多!!!

 虽然使用索引能得到查询效率的提高,但是我们也必须注意到他的代价。索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改。这意味着每条记录的INSERTDELETE UPDATE将为此多付出45次的磁盘I/O 。因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢。

 

《4》并不是所有的表都适合加索引的!!

 

①记录少是不要加索引!

②每次查询的数据占总的数量很大时,不要加索引。

③多列索引:对查询频率高的列放在前面。

④数据库量很大,并且每次查询的数据占总量比较少时,加索引谁体会到明显的效率!

 

12对于SQL语句书写时的一些建议:

1)         写语句时能够确定数据库对象所有者的,尽可能把所有者带上,如:

SELECT * FROM dbo.Users

2)         存储过程中,参数定义最好放在最前面,尽可能一次定义,如:

DECLARE @USER_ID INT

       ,@USER_NAME   VARCHAR(50)

       ,@PASSWORD VARCHAR(50)

3)         为参数赋值时,尽可能一次赋值,如:

SELECT @USER_ID = 1001

       ,@USER_NAME = ‘xiaojun.liu‘

4)         尽量少用游标

5)      不使用*,少使用连接查询,少用like操作等

 

 

二:其他的参考http://blog.sina.com.cn/s/blog_76c0a1c50101d0gq.html

 

常见的数据库优化方法:索引(数据库),缓存,分表,分库,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 and 3

 

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

(当存在 “where 条件1 and 条件2” 时,数据库先执行右边的语句)

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

 

3.应尽量避免在 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()) 

 

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

 

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

select 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 T1 WHERE 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‘)

 

5.充分利用连接条件,在某种情况下,两个表之间可能不只一个的连接条件,这时在 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 

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

 

6.使用视图加速查询 

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

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,所以查询工作量可以得到大幅减少。

 

 

Mysql分区和分表:http://blog.51yip.com/mysql/1029.html


本文出自 “我的JAVA世界” 博客,请务必保留此出处http://hanchaohan.blog.51cto.com/2996417/1535507

数据库性能优化,布布扣,bubuko.com

数据库性能优化

上一篇:MySQL binlog 日志


下一篇:构建一个基于容器的开发环境