sql 优化建议 - HelloWorld开发者社区

1、查询语句，尽量避免查询全部，避免写 select * from table，查哪个写哪个，提高效率；
更新语句，能update具体字段的，不要update所有字段，提升效率。
要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引

2、一张表中添加索引太少不行，索引的添加可以让查询速度变快，但是索引太多，新增操作会变慢，
一般一张表中索引不要超过6个

3、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，
如：select id from t where num is null
最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.
备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。
不要以为 NULL 不需要空间，
如：char(100) 型，在字段建立时，空间就固定了，不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询select id from t where num = 0

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

5、应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num=10 or Name = ‘admin’
可以这样查询：
select id from t where num = 10
union all
select id from t where Name = ‘admin’

6、避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描
select id from t where num/2 = 100
可以改为:
select id from t where num = 100*2

7、避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描
select id from t where substring(name,1,3) = ’abc’ -–name以abc开头的id
select id from t where datediff(day,createdate,’2005-11-30′) = 0 -–‘2005-11-30’ –生成的id
可以改为:
select id from t where name like ‘abc%’
select id from t where createdate >= ‘2005-11-30’ and createdate < ‘2005-12-1’

8、 in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3

用 exists 代替 in 是一个好的选择：
select num from a where num in(select num from b)
可以改为：
select num from a where exists(select 1 from b where num=a.num)

9、 like模糊查询也将导致全表扫描：
select id from t where name like ‘%abc%’
可以在界面上使用下拉菜单，让用户选择的方式，或者ajax弹出匹配的关键字，然后再查询。
若要提高效率，可以考虑全文检索（Lucene或者solr）。

10、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，
并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

11、在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

12、如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

13、尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。（至于游标呢，可以把游标当作一个指针，它可以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理）

14、在可以使用UNION ALL的语句里，尽量使用，最好不要使用了UNION。

15、用>=替代> 往往会高效
高效：SELECT * FROM EMP WHERE DEPTNO >=4
低效: SELECT * FROM EMP WHERE DEPTNO >3
两者的区别在于，前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。

16、多数数据库都是从左到右的顺序处理条件,把能过滤更多数据的条件放在前面,过滤少的条件放后面
select * from employee
where salary >1000 –条件1,过滤的数据较少
and dept_id=’01’ –条件2,过滤的数据比条件1多
上面的SQL就不符合我们的原则了,应该把过滤数据更多的条件放在前面,因此改为下面这样更好
select * from employee
where dept_id=’01’ –过滤更多数据的条件放在前面
and salary > 1000
当然，Oracle数据库的where条件处理顺序是从右向左的。

17、实际案例分析：拆分大的 DELETE 或INSERT 语句，批量提交SQL语句
　　如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询，你需要非常小心，要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的，表一锁住了，别的操作都进不来了。Apache 会有很多的子进程或线程。所以，其工作起来相当有效率，而我们的服务器也不希望有太多的子进程，线程和数据库链接，这是极大的占服务器资源的事情，尤其是内存。
　　如果你把你的表锁上一段时间，比如30秒钟，那么对于一个有很高访问量的站点来说，这30秒所积累的访问进程/线程，数据库链接，打开的文件数，可能不仅仅会让你的WEB服务崩溃，还可能会让你的整台服务器马上挂掉。
　　所以，如果你有一个大的处理，你一定把其拆分，使用 LIMIT oracle(rownum),sqlserver(top)条件是一个好的方法。下面是一个mysql示例：

sql 优化建议