数据库性能优化

影响性能的因素:

1.数据库设计

2查询

3硬件(c up,内存,io, 处理器速度跟不上,内存容量不足,I/O吞吐量小,形成瓶颈效应)

4事务管理

5数据分布

6网络

7操作系统

优化方式:

l       设计合理的数据表结构:

要在良好的数据库方案中实现最优的性能,最关键的是要有1个很好的数据库设计方案。在实际工作中,许多数据库方案往往是由于数据库设计得不好导致性能很差。所以,要实现良好的数据库设计就必须考虑这些问题。

一般来说,逻辑数据库设计会满足规范化的前3级标准:

a.       三大范式1. 原子性(既不能在细分) 2. 每列必须和组件相关,即要求一个表只描述一个事情 3.各列必须和组件相关,不能间接相关,不依赖传递(e.g: 订单表:顾客姓名→ 顾客编号→订单编号)

遵守这些规则的设计会产生较少的列和更多的表,因而也就减少了数据冗余,也减少了用于存储数据的页。但表关系也许需要通过复杂的合并来处理,这样会降低系统的性能。某种程度上的非规范化可以改善系统的性能,非规范化过程可以根据性能方面不同的考虑用多种不同的方法进行

b.       为了避免联表查询,有时候可以适当的数据冗余, 有效的控制冗余有助于提高数据库的性能

c.       选择合适的数据类型:如果能够定长尽量定长

d.       不要使用无法加索引的类型作为关键字段,比如 text类型

e. 设计出的表要具有较好的使用性,主要体现在查询时是否需要关联多张表且还需使用复杂的SQL技巧

l       对数据表建立合适有效的数据库索引

索引并不一定就是给主键或是唯一的字段。如果在你的表中,有某个字段你总要会经常用来做搜索,那么,请为其建立索引吧


搜索字串 “last_name LIKE ‘a%’”一个是建了索引,一个是没有索引,性能差了4倍左右。

注意:(使用 LIKE �c% 不能使用索引,使用 LIKE ‘abc%’ 将能够使用索引)

l       永远为每张表设置一个ID

我们应该为数据库里的每张表都设置一个ID做为其主键,而且最好的是一个INT型的。就算是你 users表有一个主键叫 “email”的字段,你也别让它成为主键。使用 VARCHAR 类型来当主键会使用得性能下降。另外,在你的程序中,你应该使用表的ID来构造你的数据结构。

还有一些操作需要使用主键,在这些情况下,主键的性能和设置变得非常重要,比如,集群,分区

l         最好不要给数据库留NULL,尽可能的使用 NOT NULL填充数据库

           备注、描述、评论之类的可以设置为 NULL,其他的,最好不要。

           要以为 NULL 不需要空间,其需要额外的空间 。在 Oracle 里,NULL 和Empty 的字符串是一样的!)

如果非要用null值,default是一个办法,可以多加一列bit 0的方式来表示某一列为null
          比如:charchar(100) 型,在字段建立时,空间就固定了, 不管是否插入值(NULL也包含在内), 都是占用 100个字符的空间的
            如果是varchar这样的变长字段, null 不占用
当为null的字段在一条记录的最后,就是说他后边没有非null的字段值时,是不占空的   
  当为null的字段在一条记录的中间,就是说他后边还有非null的字段值时,他就好占用空间(oracle)
          Null 这个类型  Empty 很类似但不同点在于 Empty 代表一个变量尚未被初始化也就是还没有被赋予任何的值而一个变量为 Null 只有在你指定它为 Null 之后。最常遇到 Null 的机会应该是在处理数据库的时候当一个字段没有资料时便是 Null 
Problemtable T包括abcd三个字段。
问题1:假设某行记录的d字段为null,则该行记录d字段应该不占用任何空间。对吗?
问题2:假设b字段数据类型为varchar210),c字段not null。则不论b字段是否为null,都将占用存储空间。那它该占多少呢?是10个字节吗?
最后还有另外一个小问题:假设b字段数据类型为varchar210)且not null,则记录中b字段实际占用的空间应该为实际数据的长度(不大于10)。但如果某行记录b字段数据的长度为5,现在要update后长度变为8,该行记录的现有空间肯定无法存放,必定会引起整行记录的位置变动。这肯定会影响系统的性能,该如何避免这样的问题发生呢?
 

数据列定义成不能为空(NOT NULL)会使处理速度更快,需要的存储更少。有时还会简化查询,因为在某些情况下不需要检查值的NULL属性。

 
 

l       数据查询:编写简洁高效的SQL语句  (进行全表扫描,返回了不必要的行和列)                                    

a.避免不恰当的使用“SELECT *”

除非真的需要读取表中的所有列,否则基于提高查询性能的考虑,在写 SQL 语句的时候应该尽量避免使用“SELECT *”这样的情况。这是一条很简单却常常被用户忽略的最佳实践。

Problem:数据库连接查询没用join条件会导致什么情况

select sum(project_o.danjia*project_o.mianji) from project_o,project_t where project_o.zhuangtai='no' and project_o.project_id=30

语句其实只是sql语句的一部分,问题是另一部分用到了表project_t,所以from中有project_t,但是上面的这部分语句完全没有用到project_t,但是不设置条件就导致了笛卡尔乘积。

 

l 笛卡尔乘积
笛卡尔乘积通俗的说,就是两个集合中的每一个成员,都与对方集合中的任意一个成员有关联。关系数据库中的笛卡尔积的结果就是两个表中行数的乘积

        解决方法:

使用LEFT JOIN

select sum(project_o.danjia*project_o.mianji) from project_o LEFT JOIN project_t ON project_o.id=project_t.project_id    

where project_o.zhuangtai='no' and project_o.project_id=30 

 

SQL查询中没有JOIN条件导致的笛卡尔乘积从而影响性能的一个案例

 
 
l  在子查询中慎重使用IN或者NOT IN语句. Not exist 效率高于not in
in是把外表和内表作hash 连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询。一直以来认为existsin效率高的说法是不准确的。 
如果查询的两个表大小相当,那么用inexists差别不大。 
如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in
not in not exists如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;而not extsts 的子查询依然能用到表上的索引。所以无论那个表大,用not exists都比not in要快。
 
 
 
问题:其中一部分是删除大量数据一部分是往数据库中添加大量数据.
目前看来相互的影响太大(几十倍的性能降低), 请问这样的并行处理的一般优化方案是什么~

 

l         拆分大的 DELETE 或INSERT 语句,批量提交SQL语句
如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询,你需要非常小心,要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的,表一锁住了,别的操作都进不来了。
Apache
 会有很多的子进程或线程。所以,其工作起来相当有效率,而我们的服务器也不希望有太多的子进程,线程和数据库链接,这是极大的占服务器资源的事情,尤其是内存。
如果你把你的表锁上一段时间,比如30秒钟,那么对于一个有很高访问量的站点来说,这30秒所积累的访问进程/线程,数据库链接,打开的
文件数,可能不仅仅会让你的WEB服务崩溃,还可能会让你的整台服务器马上挂了。
所以,如果你有一个大的处理,你一定把其拆分,使用 LIMIT oracle(
rownum),sqlserver(top)条件是一个好的方法。下面是一个mysql示例:

while(1){

 //每次只做1000

 mysql_query(“delete from logs where log_date<=’2012-11-01’limit 1000”);

 if(mysql_affected_rows()==0){

//没得可删了,退出!

break

}

//每次都要休息一会儿

usleep(50000)

}

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

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

l         采用连接操作,避免过多的子查询,产生的CPUIO开销

l         对于连续的数值,使用between代替in

l         尽量不用触发器,特别是在大数据表上

l         当只要一行数据时使用 LIMIT

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值