QQ 1640076782

2013年06月22日

SQL优化方法

Filed under: phper — 标签:, , — liphp @ 3:36 上午

(1 )注释使用

在语句中多写注释,注释不影响SQL语句的执行效率。增加代码的可读性。

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

select id from test where num is null

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

select id from test where num=0

(3) 应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:

select name  from test where id=1 or id=2

可以这样查询:

select name from test where id=1

union all

select name from test where id=2

(4)尽量使用count(1)

count函数只有在统计表中所有行数时使用,而且count(1)比count(*)更有效率。

(5) 应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:

select id from test where num/2=10

应改为:

select id from test where num=10*2

(6)尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

(7)in 和 not in 也要慎用,否则会导致全表扫描,如:

select id from test where num in(1,2,3)

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

select id from test where num in(select num from b)

用下面的语句替换:

select id from test where exists(select 1 from b where num=test.num)

对于连续的数值,能用 between 就不要用 in 了:

select id from test where num between 1 and 2

(8)  没有必要时不要用DISTINCT和ORDER BY

这些动作可以改在客户端执行,它们增加了额外的开销。

(9) 尽量少用视图

对视图操作比直接对表操作慢,可以用stored procedure来代替她。特别的是不要用视图嵌套,嵌套视图增加了寻找原始资料的难度。我们看视图的本质:它是存放在服务器上的被优化好了的已经产生了查询规划的SQL。对单个表检索数据时,不要使用指向多个表的视图,直接从表检索或者仅仅包含这个表的视图上读,否则增加了不必要的开销,查询受到干扰。

(10) 索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有 必要.

(11) 任何地方都不要使用 select * from test ,用具体的字段列表代替“*”,不要返回用不到的任何字段,那样会增加查询的量。

(12) 尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引).

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

(14) 应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。例如:

select id from test where substring(name,1,3)=’abc’–name      以abc开头的id

select id from test where id(id,12)=0–12     生成的id

应改为:

select id from test where name like ‘%hello%’

select id from test where id>=12 and id<56