数据库的优化

数据库的优化

1）.SQL语句优化

a应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

b应尽量避免在 where 子句中对字段进行 null值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

selectid from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

selectid from t where num=0

c很多时候用 exists 代替 in 是一个好的选择

d用Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤

 

2).索引优化

a避免对列的操作

任何对列的操作都可能导致全表扫描，这里所谓的操作包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等式的右边，甚至去掉函数。如：30万行数据情况下

select * from record wheresubstrb(CardNo,1,4)='5378'(13秒)

优化后：select* from record where CardNo like '5378%'（<1秒）
select * from record where amount/30< 1000（11秒）

优化后：select * from record where amount< 1000*30（< 1秒）
select * from record where to_char(ActionTime,'yyyymmdd')='19991201'（10秒）

优化后：select * from record whereActionTime= to_date ('19991201' ,'yyyymmdd')（<1秒）

b避免不必要的类型转换

需要注意的是，尽量避免潜在的数据类型转换。如将字符型数据与数值型数据比较，ORACLE会自动将字符型用to_number()函数进行转换，从而导致全表扫描。

例如：表tab1中的列col1是字符型（char)，则以下语句存在类型转换：

select col1,col2from tab1 where col1>10，

应该写为： select col1,col2 from tab1 where col1>'10'。

c增加查询的范围限制

增加查询的范围限制，避免全范围的搜索。

例如以下查询表record 中时间ActionTime小于2001年3月1日的数据：

select * fromrecord where ActionTime < to_date ('20010301' ,'yyyymm')

查询计划表明，上面的查询对表进行全表扫描，如果我们知道表中的最早的数据为2001年1月1日，那么，可以增加一个最小时间，使查询在一个完整的范围之内。

修改如下： select * from record where

ActionTime <to_date ('20010301' ,'yyyymm')

and  ActionTime > to_date ('20010101' ,'yyyymm')

后一种SQL语句将利用上ActionTime字段上的索引，从而提高查询效率。把'20010301'换成一个变量，根据取值的机率，可以有一半以上的 机会提高效率。同理，对于大于某个值的查询，如果知道当前可能的最大值，也可以在Where子句中加上 “AND 列名<MAX(最大值)”

 

3）.数据库结构优化

a）范式优化： 比如消除冗余（节省空间。。）

b）反范式优化：比如适当加冗余等（减少join）

c）拆分表： 分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O，一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时表可采取此方法。可按月自动建表分区。

拆分其实又分垂直拆分和水平拆分

 在这举个例子：简单购物系统暂设涉及如下表： 1.产品表（数据量10w，稳定） 2.订单表（数据量200w，且有增长趋势） 3.用户表（数据量100w，且有增长趋势），以mysql为例讲述下水平拆分和垂直拆分，mysql能容忍的数量级在百万静态数据可以到千万

垂直拆分： 解决表与表之间的io竞争的问题

把产品表和用户表放到一个server上，订单表单独放到一个server上

水平拆分： 解决单表中数据量增长出现的压力问题

用户表通过性别拆分为男用户表和女用户表，订单表拆分为已完成订单和未完成订单，产品表未完成订单放一个server上，已完成订单表和男用户表放一个server上，已完成订单表和女用户表放一个server上(女的爱购物哈哈)

4）.服务器硬件优化

这个么多花钱咯！