SQL语句的优化
firepig 于 2008-07-01 17:30:58这些天因为要准备下个项目，老大叫我好好看一下Hibernate3.2，所以想先复习一些sql的东西，总结一些经验。其实在SSH这样架构的项目中，我觉得最重要的就是Hibernate了，因为它是负责持久化的。那么就牵扯到很多东西了。计算机世界万物都有一个本，而对数据库的操作，最基本就是sql，然后jdbc，然后。。。
所有从本开始，下面是一些优化的方案，有的会配合一些小例子。（强烈建议小苏小郑他们赶快把代码插件搞起啊，这样在网页附代码的时候就方便和美观了）
1.避免扫描整个表。
扫描整个表就好像在一本厚厚的书里去一页页的寻找关键字，会把你晕死的。通常的解决办法，我们会根据SQL的WHERE子句建立索引。在数据库中建立的是一个B树索引，关于B树索引就不介绍了，离散数学和数据结构中都有详细的说明。 有时候，扫描全表是不可避免的，比如说，要选择所有的行，update所有的行，这个时候索引是没有效率的。怎么样合理的建立索引，就需要丰富的业务背景知识，和一定的项目经验了。但有些东西是有规律寻的，当以下条件时候，你就毫不犹豫的建立索引吧。一，返回的数据行数少于总行数的10%的时候。二，限定条件经常使用的时候。三，列经常被order by和group by的时候。
2.查询中where子句的安排。
使返回最少记录的索引可以限制SQL语句返回的结果，称为最严格限制条件。通常把最严格的条件放在where的最后。查询优化器会从最严格的条件开始检索。
比如（已建索引）说 一， select * from mytable where name = wenjg’ and age > 24;
二， select * from mytable where age > 24 and name = ”wenjg”;
简单的从语法上看上去这两条简单的sql语句没有什么差异。可是从性能上，一个是要先全表检索‘wenjg’然后再找age大于24。还一个是利用B树索引的快速锁定age>24的那些叫的人，在从这个小范围找‘wenjg’这个人。如果这是张大表的话，速度可能会差上个几十秒，甚至几分钟（可怕）。
3.使用存储过程，触发器和嵌入式SQL语言
记得上一个项目，老大说做统计报表的时候不要用Hibernate处理数据，直接用存储过程，这样性能会好很多因为这些过程是数据库引擎编译的，然后执行，所以相对单个查询而已，过程的优势太明显了。而Hibrnate对于统计性能不行。。。
4.避免使用OR。
有一个很经典的例子：
select * from fact_table
where prod_cd like ‘AB%’
OR prod_cd like ‘AC%’
OR prod_cd like ‘BB%’
OR prod_cd like ‘BC%’
OR prod_cd like ‘CC%’
ORDER BY prod_cd;
这个语句，执行的效率是很低的，可是大家在写sql的时候也是最常用的。
改写后的sql，效率大增(substr用的太经典了)：
select * from fact_table
where substr(proc_cd,1,2) IN (’AB’,'AC’,'BB’,'BC’,'CC’)
ORDER BY prode_cd;
5.在事务处理和成批装入时要注意的
我们经常会编写一些批处理的文件，如备份数据库等。这个时候要选择好时间，在用户访问最少的时候去处理。在处理过程竞争资源的时候，系统资源不是在用户之间分配的，而是被批量数据操作独占。用户的处理进程是争不过批量处理的。而且在处理的时候，锁住某个表后，用户进程就不能访问了。
而在事物的控制中，要记得COMMIT。数据库中有一个叫做ROLLBACK SEGMENT的保留区，那是个事务进行的时候临时存储事务操作结果的一个地方，当我们rollback的时候，清空这里的内容，目标表不改变。当commit的时候，结果写入目标表，清空ROLLBACK SEGMENT的内容。
但是在SSH，有了SPRING的面向切面的事务管理，哈哈哈哈。。。就不要我们去管这些东西了。这也是AOP伟大之处吧，要不然每个DAO的操作都要来上那么一段，靠，一定会晕的。
6.减少对数据库函数的依赖
像AVG COUNT MAX MIN SUM这样的函数用起来总是很爽，但是这样无疑是给了数据库负担，想这样的事情不要让数据库去做，直接交给应用程序去处理。
7.最后删除索引
一看这个你可能会好奇，我辛苦建立的索引干什么要删除呢。这个删除不是要你彻底的删除，而是加速批量更新的一个方法，等操作完成最后在重建表的索引。
对于sql的优化，还有很多的内容，剩下的还需要更多的学习～～～特别是数据库的优化，汗，那好像是DBA做的事。