数据库优化的手段

一般优化手段：

---


配置：
1、日志文件、索引数据文件和数据文件分磁盘放，提高IO效率
2、多CPU环境下的配置
Sybase：max online engine/min online engine，引擎数和具体某个特定的CPU没有直接关系，只是告诉Sybase：用多少个进程来运行。至于OS会分配多少个CPU来运行进程，则由OS决定。
Oracle：安装9i时Oracle会自动侦测CPU的个数，并设置参数cpu_count的默认值。
3、Oracle：合理配置回滚段的大小，避免snapshot too old的错误
4、合理设置DB内存参数和UNIX内存参数
DB参数：
Sybase：max memory参数就是一个ASE进程使用的最大内存
Oracle：就是共享池等那几个参数：
java_pool_size=20971520
large_pool_size=8388608
shared_pool_size=33554432
db_cache_size=33554432
OS参数：
安装Sybase和Oracle都需要修改/etc/system文件的参数，参数详解如下：
一个共享存储器段的最大尺寸( SHMMAX ）
共享存储器段的最小尺寸 ( SHMMIN ）
系统中共享存储器标识符的最大数量 ( SHMMNI ）
一个用户进程能附带的共享存储器段的最大数量 ( SHMSEG ）
在系统中信号灯标识符的最大数量 ( SEMMNI ）
在一个集合中的信号灯的最大数量 ( SEMMSL ）
在系统中信号灯的最大数量 ( SEMM ）
按semop调用操作的最大数量 ( SEMOPM ）
信号灯最大值 ( SEMVMX ）
全部允许的共享存储器由下列公式确定：
SHMMAX * SHMSEG
5、Direct IO
Direct IO绕过OS的缓存，不同的数据库都可以设置这个选项。
对于Sybase来说就是DSYNC和directio参数，这两个参数是互斥的。
如果在裸设备上创建数据库，DSYNC不起作用。如果在文件系统上创建数据库，DSYNC=FALSE表示直接写磁盘，不通过OS缓冲，会很慢；设置为true会比较快，但由于OS的缓冲机制不能保证服务器宕机的时候数据写入物理磁盘的一致性，因此会有数据库无法完全恢复的风险，对于关键数据，不能使用DSYNC=true。对临时设备/表空间则一定要禁用DSYNC。

索引
1、增加索引，缩短查询时间
2、删除索引，缩短更新时间
3、更换索引字段的排列顺序，使索引更高效
4、重建索引，提高效率（rebuild index、reorg）
5、新建或重建索引时，对已经排序的表使用nosort关键字（Oracle），with sorted_data (sybase)
6、指定索引的ignore_dup_row属性，提高插入效率
7、更新索引的统计信息，提高索引效率

更新
1、小事务合并为大事务，提高效率
2、大事务分割为小事务，减少日志增长
3、TRUNCATE代替DELETE TABLE
4、合理设置表的extent大小和增长策略
5、指定表、索引的fillfactor或PCTFREE/PCTUSED
6、行级锁代替页面锁，减少死锁
Sybase有3种锁：行级、页面、所有页，默认为页面锁，修改为行级锁可以避免多个UPDATE线程冲突产生死锁。
7、为大事务和小事务分配多个回滚段
8、Sybase：用varchar代替text，减少磁盘空间
9、按时间分表，减少查询时间和更新时间。
10、Oracle Direct Path Insert：直接将数据更新到物理磁盘中，bypass data buffer缓存，不生成重做日志。可以使用直接路径选项进行插入的方式是：
  insert into … select from …
  CTAS (使用APPEND HINT)
  SQLLoader
  EXP导出数据（注意，这个比较特殊，不是IMP）
11、Oracle直接路径插入不写日志（NOLOGGING）、使用并行选项（PARALLEL）
12、Sybase：插入数据使用快速bcp。

查询
1、UNION ALL代替UNION
2、避免使用’%xxx’的模糊匹配查询
3、编写合理的SQL语句，合理利用索引
4、WHERE代替HAVING
5、针对查询计划进行优化（Oracle：explain plan for， Sybase： set showplan on, set noexec on）
6、临时表代替永久表，提高更新时间


高级，不常用优化手段：

---

 

查询
Table的cache，nocache属性
使用通用的SQL语句并绑定变量，代替字符串拼装
使用HINT指定索引
创建物化视图提高效率
Oracle并行选项

索引
倒排索引（主要适用于并行服务器，不支持范围查找）
Hash索引
位图索引

配置
索引和数据文件分磁盘存放
设置数据库页面大小
BUFFER调整和高速缓存命中率

更新
分区表
设置表的INITTRAN和MAXTRAN
约束有几种状态：使能/禁止、校验/不校验。通过这几种状态的组合，可以实现数据的快速更新。
排除row chaining