本文作者:心月

数据库中那些会引起全表扫描的SQL语句

心月IT博客 2019-03-09
数据库中那些会引起全表扫描的SQL语句摘要:相对于索引范围扫描来说,全表扫描的成本是衡定不变的。无论你的查询是想访问一笔数据还是访问整个表的所有数据,全表扫描都会把整张表读出来。而索引范围扫描就不同,因为有了索引这个有序的数据结构做支持,可以只访问需要的索引块和需要的数据块,而不是访问整张表。

    所谓全表扫描是指,在数据库中,对无索引的表进行查询一般称为全表扫描。全表扫描是数据库服务器用来搜寻表的每一条记录的过程,直到所有符合给定条件的记录返回为止。

    一般说来数据库在进行全表扫描时进行的是多块读取,也就是说每次从buffer cache中读取多块,而不是一次一块的读。另外,全表扫描的成本中并不包括对数据进行过滤和计算的成本。也就是说全表扫描的成本仅仅是读取数据的成本,而不包含对数据进行计算,过滤的CPU成本。

    相对于索引范围扫描来说,全表扫描的成本是衡定不变的。无论你的查询是想访问一笔数据还是访问整个表的所有数据,全表扫描都会把整张表读出来。而索引范围扫描就不同,因为有了索引这个有序的数据结构做支持,可以只访问需要的索引块和需要的数据块,而不是访问整张表。

    也就是说全表扫描有可能会对数据库的性能产生影响,尤其扫描的表数据非常大的情况下更应该避免使用全表扫描。哪些SQL语句的执行会引起全表扫描呢?

1、模糊查询

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

    解决办法:首先尽量避免模糊查询,如果因为业务需要一定要使用模糊查询,则至少保证不要使用全模糊查询:

    对于右模糊查询,即like ‘…%’,是会使用索引的;

    左模糊like‘%...’无法直接使用索引,但可以利用reverse + function index 的形式,变化成 like ‘…%’;

    全模糊是无法优化的,一定要的话考虑用搜索引擎。出于降低数据库服务器的负载考虑,尽可能地减少数据库模糊查询。


2、查询条件中含有is null

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

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


3、查询条件中有不等于操作符(<>、!=)

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

    解决方法:通过把不等于操作符改成or,可以使用索引,避免全表扫描。例如,把column<>’aaa’,改成column<’aaa’ or column>’aaa’,就可以使用索引了。 


4、or使用不当

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


5、组合索引

    排序时应按照组合索引中各列的顺序进行排序,即使索引中只有一个列是要排序的,否则排序性能会比较差。

例如:create index skip1 on emp5(job,empno,date); 
select job,empno from emp5 where job=’manager’and empno=’10’ order by job,empno,date desc;

实际上只是查询出符合job=’manager’and empno=’10’条件的记录并按date降序排列,但是写成order by date desc性能较差。


6、在where子句中使用参数

    因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然 而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:

select id from t where num = @num

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

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

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

select id from t where num/2 = 100

应改为:

select id from t where num = 100*2


7、在where子句中对字段进行函数操作

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


8、不带任何条件的count

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


9、Update 语句

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


什么情况可以使用全表扫描?

1. 单表查询

①表很小,索引可能就比表还大。

②访问的数据占全表数据的百分比很大,索引访问的总成本大于全表扫描的成本。

③相对于索引来说,表中的数据排列过于凌乱,表现出来就是索引的 clustering_factor 很大,导致索引的访问成本剧增。

2. 多表连接

①hash join的时候,内层表和外层表都可以使用全表扫描(对于某个单独的表的访问是否适用全表扫描,这个又回到了单表查询的情况)。

②nest loop的时候,外层表可以使用全表扫描,内层表一般不用全表扫描。

文章版权及转载声明:

本文由 心月IT技术博客 博主整理于 2019-03-09
若转载请注明原文及出处:https://www.xinyueseo.com/mysql/168.html

分享到:
赞(
发表评论
快捷输入:

验证码

    评论列表 (有 0 条评论,人围观)参与讨论