SQL优化

使用索引的优化

索引一般可以加速数据的检索速度，加速表与表之间的链接，提高性能，所以在对海量数据进行处理时，考虑到信息量比较大，应该对表建立索引，包括在主键上建立聚簇索引，将聚合索引建立在日期上等。索引的优点有很多，但是对于索引的建立，还需要考虑实际情况，而不是对每一个列建立一个索引，比如针对大表的分组、排序等字段，都要建立相应索引，同时还应该考虑建立符合索引。增加索引的同时也有很多不好的方面，首先，创建索引和维护索引都耗费时间，且随着数据量的增加而增加；其次，索引占据物理空间，聚簇索引的空间更大。最后，当对表进行增加、删除和修改的时候，索引也要动态的维护。所以索引的引用有以下几个原则：

（1）当插入数据为表中数据的百分之10以上时，首先需要删除该表的索引来提高数据的插入效率，然后再重新建立索引；

（2）避免在索引列上使用函数或计算或类型转换，在where字句中，如果索引是函数的一部分，优化器将不再使用索引，将使用全表扫描；例如：

select * from table where hh*10>1000;//低效
select * from table where hh>1000/10;//更高效

（3）尽量避免在索引列上使用not，!=和<>，索引只能告诉什么在表中，而不能告诉什么不在表中，当数据库遇上以上几种符号时，将不再使用索引，使用全表扫描

（4）最佳左前缀法则：最佳左前缀法则指的是查询从索引的最左侧开始，并且不跳过中间列，用通俗的话来说就是：带头大哥不能死、中间兄弟不能断。比如我们建立了id\name\class三个字段的复合索引，但是在查询中，并没有查询id,只查询了name或class，这时索引失效；或者我们在查询中查询了id和class字段却没有使用name字段，也会使索引部分失效。

（5）避免在索引列上使用IS NULL 或 IS NOT NULL。避免在索引列上使用任何可以为空的列，这样将无法使用此索引。因为空值不存在于索引列中，所以当where字句中对索引进行空值比较，将无法使用该索引。

（6）在索引列上，使用>=代替>，比如：

select * from table where hh>10;//低效
select * from table where hh>=10.0000001//相对高效

（7）应尽量避免在 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'

(8) 在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有“%”不在第一个位置索引才会起作用。如：

select id from t where Name like '%abc%' //低效

select id from t where Name like 'abc%' //高效

sql的优化

(1)select的时候要将需要的字段全部列出，而不是使用*代替,例如：

select * from student//低效

select id,name,class from student   //高效

(2)多表查询时最好给表取别名，这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误

(3）有条件的使用union-all代替union提高效率

(4）用exists代替in，可以提高查询的效率，对于连续的数值，能用 between 就不要用 in。例如：

SELECT * FROM ACCOUNT
    WHERE AC_CODE 
    NOT IN (
              SELECT CODE
              FROM GOODS
              WHERE NUM='001')       //低效

SELECT * FROM ACCOUNT
    WHERE NOT EXISTS
       （SELECT CODE
          FROM GOODS
          WHERE CODE=ACCOUNT.AC_CODE
           AND NUM='001')             //更高效
           

（5）where字句中的链接顺序

oracle采用自下而上的顺序解析where字句，所以表之间的链接必须写在其他where条件之前，那些可以滤过大量纪录的条件必须写在where字句的末尾，例如：

select * from table e
      where h>500     
                  and d='001'
                  and 25<(select count(*) 
                               from table 
                               where count=e.count);  //低效

select * from table e
      where  25<(select count(*) 
                          from table 
                          where count=e.count);    
                   and  h>500
                   and d='001';//更高效     

（6）where条件里尽量不要进行null值的判断，null的判断也会造成全表扫描。如下：

SELECT * FROM t_order WHERE score IS NULL

优化：

给字段添加默认值，对默认值进行判断。如：

SELECT * FROM t_order WHERE score = 0