【大数据课堂0008】会引起全表扫描的几种SQL 以及sql优化
查询语句的时候尽量避免全表扫描,使用全扫描,索引扫描!会引起全表扫描的几种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上建了索引也对查询效率起不了作用。