数据库sql优化总结

参考地址  http://www.cnblogs.com/yunfeifei/p/3850440.html

 

1.对查询进行优化，需要尽量避免 全表扫描  ,首先  应该考虑在 where 和 order by  涉及的列字段上建立索引。

2.应该尽量避免在 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 ;

3.应该尽量 避免 在 where 字句中使用 != 或 <> 不等于操作符， 否则 引擎放弃使用索引 而 进行全表扫描。

4.应该尽量 避免 在 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';

5. 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 t where num  in (select num from b)

用下面的语句替换：

select num from a where exists (select 1 from b where num = a.num)

 

6.下面的查询也将导致全表扫描：

select id from t where name like '%abc%'

like '%xxx' 是会导致全表扫描的，而  like 'xxx%' 是可以使用索引 的。

但要想提高查询效率，最好使用全文检索。

7.如果在 where 字句中使用参数  ，也会导致全表扫描 。

因为 SQL 只有在运行时才会解析 局部变量 ，但 优化程序 不能将 访问计划的选择 推迟到运行时 ；

它必须在编译时 进行选择。

 然而 ，如果在编译时 建立访问计划 ，变量的值 还是未知的， 因为无法作为索引选择的输入项。

下面语句将进行全表扫描：

select id from t where num = @num ;

可以 改为 强制查询使用索引

select id from t with (index(索引名)) where  num = @num ; 

8.应该尽量避免  在 where 字句中 对 字段 进行 表达式操作 ， 这将导致 引擎放弃使用索引 而进行 全表扫描 。 如：

select id from t where num/2 = 100  ;

应该改为：

select id from t where num = 100 * 2 ;

9.应该避免在  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'

10.不要在 where 子句中的 “ = ” 左边 进行 函数、算术运算或其他表达式运算， 否则 系统 将 可能 无法正确使用索引。

11.在使用 索引字段  作为 条件时，如果  该索引 是  复合索引，那么 必须使用到  该索引中的 第一个字段 作为条件时 才能保证 系统使用该索引，否则  该索引  将不会被使用， 并且 应尽可能的 让 字段顺序  与 索引顺序  相一致。

12.不要写一些没有意义的查询，如果需要生成一个空表结构：

select col1 ,col2 into #t from where 1=0 ;

这类代码不会返回任何结果集，但是会消耗系统资源的，应该使用表创建语句

create table #t(
col1 int,
col2 varchar(20)
)

13.如果update 语句只更新少数字段， 就不要 Update 全部字段，否则频繁调用会引起明显的性能消耗，同时会产生大量的日志。

14.对于大数据量（这里几百条就算大了）的表 JOIN ,要  先分页   再 JOIN ,否则逻辑读会很高，性能会很差。

15. select count(*) from table ;  这种不带任何条件的 count 会引起全表扫描 ，并且没有任何业务意义， 一定要避免这种情况的发生。

16.索引并不是越多越好，使用索引在 select 的情况下固然加快了查询速度，但是 在进行 insert 和 update 的时候需要对索引进行维护，重建索引 了，所以怎么样创建索引是需要慎重考虑的， 要视具体情况而定 。 一般来说， 一个表的索引 最好不要超过 6 个 ，若太多则应该考虑一些不经常使用到的列上建的索引是否有必要。

17.应尽可能的避免更新 clustered 索引数据列 ，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦改变该列的值 将会导致整个表记录的顺序的调整 ，会消耗相当大的资源。若 应用系统 需要频繁更新 clustered 索引数据列 ，那么需要考虑 是否 要将该索引 建为 clustered 索引。

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

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

20.在任何时候，都不要使用 select * from t , 要 用 具体的字段代替 “*” ，不要返回用不到的任何字段。

21.尽量 使用 表变量 来 代替 临时表。如果 表变量 包含了 大量数据， 请注意 索引非常有限 （只有主键索引）。

22.避免频繁 创建 和 删除 临时表 ，以减少系统表资源的消耗 。 临时表并不是不可以使用 ，适当的使用它， 可以使某些例程更有效，例如，当 需要 重复 引用 大型表 或者 常用表 中的 某个数据 时。但是，对于 一次性事件 最好 使用 导出表。

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

24.如果使用了临时表，那么在存储过程的 最后 务必加上 对临时表的显示删除 ，先 truncate table 然后 drop table ，这样可以避免系统表的长时间锁定。

25.尽量避免使用游标， 因为 游标的效率较差 ，当游标操作数据超过 1万行的时候 ，就应该考虑改写

26.使用基于游标的方法 或 临时表 方法之前，应该先寻找基于  集 的解决方案来解决问题 ，基于 集 的方法通常更有效。

27.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标 通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中 包括“合计” 的例程通常要比使用游标执行的速度快。

28。在所有的存储过程和触发器的开始处设置 set nocount on ,在结束时设置 set nocount off .无需在执行存储过程和触发器的每个语句后向客户发送  DONE_IN_PROC 消息。

29.尽量避免大事务操作，提高系统并发能力，

30.尽量避免向客户端返回大数据量，如果数据量 过大 ， 就应该考虑相应需求是否合理。

实际案例分析： 拆分大的 DELETE 或 INSERT ，批量提交sql语句

    如果你需要在一个在线的网站上去执行一个大的DELETE或 INSERT 查询 ，你需要非常小心。 要避免你的操作让你的整个网站停止响应。 因为这两个操作会是锁表的， 表一旦锁住了，别的操作进不来了。

    apche 会有很多的子进程 或者 线程 。所以 其工作起来会相当有效率，而我们的服务器也不希望有太多的子进程，线程 和 数据库连接， 这是极大占用服务器资源的事情，尤其是内存/

    如果 你把你的表 锁上一段时间 ，比如 30 秒钟 ，那么对于一个有很高访问量的站点来说 ，这30秒所积累的访问进程/线程， 数据库连接 ，打开的文件数 ， 可能不仅仅会让你的WEB服务器崩溃 ， 还可能会让 你的整台服务器马上挂掉。

    所以，如果 你有一个 大的处理，你一定要把其拆分 ，使用 LIMIT oracle(rownum)  ,  sqlsever(top) 条件是一个好的方法。 下面是一个mysql 示例：

while(1){
    // 每次只做1000条
    mysql_query("delete from logs where log_date <= '2012-11-01' limit 1000");
    if(mysql_affected_rows() == 0){
        //删除完成， 退出！
        brack;
    }
    //每次暂停一段时间，释放表让其他进程/访问。
usleep(50000)

}