【大数据课堂0008】会引起全表扫描的几种SQL 以及sql优化

时间:2021-10-31 02:20:30

查询语句的时候尽量避免全表扫描,使用全扫描,索引扫描!会引起全表扫描的几种SQL如下

1、模糊查询效率很低:

  原因:like本身效率就比较低,应该尽量避免查询条件使用like;对于like ‘%...%’(全模糊)这样的条件,是无法使用索引的,全表扫描自然效率很低;另外,由于匹配算法的关系,模糊查询的字段长度越大,模糊查询效率越低。

  解决办法:首先尽量避免模糊查询,如果因为业务需要一定要使用模糊查询,则至少保证不要使用全模糊查询,对于右模糊查询,即like ‘…%’,是会使用索引的;左模糊like

  ‘%...’无法直接使用索引,但可以利用reverse function index 的形式,变化成 like ‘…%’;全模糊是无法优化的,一定要的话考虑用搜索引擎。出于降低数据库服务器的负载考虑,尽可能地减少数据库模糊查询。

 

2、查询条件中含有is null的select语句执行慢

  原因Oracle 9i中,查询字段is null时单索引失效,引起全表扫描。

  解决方法SQL语法中使用NULL会有很多麻烦,最好索引列都是NOT NULL的;对于is null,可以建立组合索引,nvl(字段,0),对表和索引analyse后,is null查询时可以重新启用索引查找,但是效率还不是值得肯定;is not null 时永远不会使用索引。一般数据量大的表不要用is null查询。

 

3、查询条件中使用了不等于操作符(<>、!=)的select语句执行慢

  原因:SQL中,不等于操作符会限制索引,引起全表扫描,即使比较的字段上有索引

  解决方法:通过把不等于操作符改成or,可以使用索引,避免全表扫描。

column<>’aaa’ //全表扫描
column<’aaa’ OR column>’aaa’ //使用索引

 

4、组合索引使用不当

 查询条件中没有前导列,导致索引不起作用;

create index skip1 on emp5(job,empno); 
select count(*) from emp5 where empno=7900; //全表扫描 
select /*  index(emp5 skip1)*/ count(*) from emp5 where empno=7900; //使用组合索引

 使用组合索引时,在排序时应按照组合索引中各列顺序进行排序(即使只有一个列需要排序),否则性能较差。

create index skip1 on emp5(job,empno,date); 
select job,empno from emp5 where job=’manager’and empno=’10’ ORDER BY date desc; //性能较差
select job,empno from emp5 where job=’manager’and empno=’10’ ORDER BY job,empno,date desc; //使用组合索引

 

5、OR语句使用不当

 OR语句连接的条件中包含的列没有全部建立索引。

 例如:where子句中比较的两个条件,一个有索引,一个没索引,使用or则会引起全表扫描。例如:where A=:1 or B=:2,A上有索引,B上没索引,则比较B=:2时会重新开始全表扫描。

 

6、UPDATA语句updata了全部字段

  如果只更改1、2个字段,不要Update全部字段,否则频繁调用会引起明显的性能消耗,同时带来大量日志

 

7、对于多张大数据量的表JOIN

 原因:没有先分页,导致逻辑读很高;
 解决方法:先分页再JOIN

 

8、select count(*) from table;

 这样不带任何条件的count会引起全表扫描,并且没有任何业务意义,是一定要杜绝的。

 

9、对于反复执行的查询,WHERE子句中使用变量绑定可以降低解析时间,提高性能

 

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

 如:

select id from t where num is null

 NULL对于大多数数据库都需要特殊处理,MySQL也不例外,它需要更多的代码,更多的检查和特殊的索引逻辑,有些开发人员完全没有意识到,创建表时NULL是默认值,但大多数时候应该使用NOT NULL,或者使用一个特殊的值,如0,-1作为默  认值。
 
     不能用null作索引,任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下,只要这些列中有一列含有null,该列    就会从索引中排除。也就是说如果某列存在空值,即使对该列建索引也不会提高性能。 任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。
 
      此例可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:

 select id from t where num=0;

 

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

 如:

select id from t where num=10 or num=20;

可以这样查询:

select id from t where num=10 union all select id from t where num=20;

 

12、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;

 

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

 如:

select id from t where num/2=100;

 应改为:

select id from t where num=100*2

 

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

 如:

select id from t where substring(name,1,3)=‘abc‘; --name
 
select id from t where datediff(day,createdate,‘2005-11-30‘)=0;  --‘2005-11-30’

 应改为:

select id from t where name like ‘abc%‘
 
select id from t where createdate>=‘2005-11-30‘ and createdate<‘2005-12-1‘

 

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

 

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

 

17、尽量避免大事务操作,提高系统并发能力。

 

18、尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。

 

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

 

20、并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。