1、 调优大方向可分为硬件和软件,常见硬件调优方式有:使用高频率的CPU(频率高了弊端也多,发热,耗电)、使用多处理器、加大内存容量、增加Cache、提高网络速度等。
2、 下面主要总结针对软件方面的优化。
3、 索引。
首先想到的是创建索引,索引是数据库调优的最根本的优化方法,很多优化手法都是围绕索引展开的。合理的创建索引,会得到很好的预期效果。但是也会有一些弊端,索引也会降低数据插入、更新和删除的速度。索引还会造成存储碎片的问题,这时就需要对表索引进行rebuild。
4、使用预编译查询。
程序中通常是根据用户的输入来动态执行SQL 语句,这时应该尽量使用参数化SQL,这样不仅可以避免SQL注入漏洞攻击,最重要数据库会对这些参数化SQL执行预编译,这样第一次执行的时候DBMS会为这个SQL语句进行查询优化并且执行预编译,这样以后再执行这个SQL 的时候就直接使用预编译的结果,这样可以大大提高执行的速度。
调整WHERE 子句中的连接顺序。
Select fielda from tablea where fielda >1
and fieldb < (select count(*) from tableb
where fieldc=2);
DBMS 一般采用自下而上的顺序解析WHERE 子句,根据这个原理,表连接最好写
在其他WHERE条件之前,那些可以过滤掉最大数量记录。
所以改为一下效果会好点
Select fielda from tablea where fieldb < (select count(*) from tableb
where fieldc=2)
and fielda >1
5、不偷懒使用select *
SELECT *比较简单,但是除非确实需要检索所有的列,否则将会检索出不需要的
列,这回增加网络的负载和服务器的资源消耗;即使确实需要检索所有列,也不要使用
SELECT *,因为这是一个非常低效的方法,DBMS在解析的过程中,会将*依次转换成
所有的列名,这意味着将耗费更多的时间。
6、尽量将多条SQL语句压缩到一句SQL中
每次执行SQL的时候都要建立网络连接、进行权限校验、进行SQL语句的查询优
化、发送执行结果,这个过程是非常耗时的,因此应该尽量避免过多的执行SQL语句,
能够压缩到一句SQL执行的语句就不要用多条来执行。
7、用Where子句替换HAVING 子句
避免使用HAVING 子句,因为HAVING 只会在检索出所有记录之后才对结果集
进行过滤。如果能通过WHERE 子句限制记录的数目,那就能减少这方面的开销。
HAVING 中的条件一般用于聚合函数的过滤,除此而外,应该将条件写在WHERE 子
句中。
8、使用表的别名
当在 SQL 语句中连接多个表时,请使用表的别名并把别名前缀于每个列名上。这
样就可以减少解析的时间并减少那些由列名歧义引起的语法错误。
9、用EXISTS替代IN。这一条不是绝对的,要根据查询表的数据量来定使用EXISTS还是IN
10、尽量用表连接代替EXISTS。
11、避免在索引列上使用计算,除非建有索引函数。
12、用UNION ALL 替换UNION。
当 SQL 语句需要UNION 两个查询结果集合时,即使检索结果中不会有重复的记
录,如果使用UNION这两个结果集同样会尝试进行合并,然后在输出最终结果前进行
排序。
因此,如果检索结果中不会有重复的记录的话,应该用UNION ALL替代UNION,这
样效率就会因此得到提高。
13、避免隐式类型转换造成的全表扫描
T_Person 表的字符串类型字段FLevel 为人员的级别,在FAge 字段上建有索引。
我们执行下面的SQL语句用于检索所有级别等于10的员工:
SELECT FId,FAge,FName
FROM T_Person
WHERE FAge=10
在这个SQL 语句中,将字符串类型字段FLevel 与数值10 进行比较,由于在大部
分数据库中隐式转换类型中数值类型的优先级高于字符串类型,因此DBMS会对FAge字段进行隐式类型转换,相当于执行了下面的SQL语句:
SELECT FId,FAge,FName
FROM T_Person
WHERE TO_INT(FAge)=10
由于在索引字段上进行了计算,所以造成了索引失效而使用全表扫描。因此应将
SQL语句做如下修改:
SELECT FId,FAge,FName
FROM T_Person
WHERE FAge='10'
14、防止检索范围过宽
如果DBMS 优化器认为检索范围过宽,那么它将放弃索引查找而使用全表扫描。
下面是几种可能造成检索范围过宽的情况:
使用IS NOT NULL或者不等于判断,可能造成优化器假设匹配的记录数太多。
使用LIKE 运算符的时候,"a%"将会使用索引,而"a%c"和"%c"则会使用全表扫描,因
此"a%c"和"%c"不能被有效的评估匹配的数量。