数据库性能优化

影响性能的因素:

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，1 的方式来表示某一列为null

          比如：charchar(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL也包含在内）， 都是占用 100个字符的空间的

            如果是varchar这样的变长字段， null 不占用

当为null的字段在一条记录的最后，就是说他后边没有非null的字段值时，是不占空的   
  当为null的字段在一条记录的中间，就是说他后边还有非null的字段值时，他就好占用空间(oracle)

          Null 这个类型 和 Empty 很类似, 但不同点在于 Empty 代表一个变量尚未被初始化, 也就是还没有被赋予任何的值, 而一个变量为 Null 只有在你指定它为 Null 之后。最常遇到 Null 的机会应该是在处理数据库的时候, 当一个字段没有资料时, 便是 Null 

Problem：table T包括a、b、c、d三个字段。
问题1：假设某行记录的d字段为null，则该行记录d字段应该不占用任何空间。对吗？
问题2：假设b字段数据类型为varchar2（10），c字段not null。则不论b字段是否为null，都将占用存储空间。那它该占多少呢？是10个字节吗？
最后还有另外一个小问题：假设b字段数据类型为varchar2（10）且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循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。 
如果查询的两个表大小相当，那么用in和exists差别不大。 
如果两个表中一个较小，一个是大表，则子查询表大的用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         采用连接操作，避免过多的子查询，产生的CPU和IO开销

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

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

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