关注51Testing
会引起全表扫描的几种SQL
发表于： 12:55 &作者：linkyou & 来源：51Testing软件测试网采编
推荐标签：
　　1、模糊查询效率很低：　　原因：like本身效率就比较低，应该尽量避免查询条件使用like；对于like ‘%...%’（全模糊）这样的条件，是无法使用索引的，全表扫描自然效率很低；另外，由于匹配算法的关系，模糊查询的字段长度越大，模糊查询效率越低。　　解决办法：首先尽量避免模糊查询，如果因为业务需要一定要使用模糊查询，则至少保证不要使用全模糊查询，对于右模糊查询，即like ‘…%’，是会使用索引的；左模糊like　　‘%...’无法直接使用索引，但可以利用reverse + function index 的形式，变化成 like ‘…%’；全模糊是无法优化的，一定要的话考虑用搜索引擎。出于降低服务器的负载考虑，尽可能地减少数据库模糊查询。　　2、查询条件中含有is null的select语句执行慢　　原因： 9i中，查询字段is null时单索引失效，引起全表扫描。　　解决方法：语法中使用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、使用组合索引，如果查询条件中没有前导列，那么索引不起作用，会引起全表扫描；但是从Oracle9i开始，引入了索引跳跃式扫描的特性，可以允许优化器使用组合索引，即便索引的前导列没有出现在WHERE子句中。例如：create index skip1 on emp5(job,empno);&& 全索引扫描 select count(*) from emp5 where empno=7900;&& 索引跳跃式扫描 select /*+ index(emp5 skip1)*/ count(*) from emp5 where empno=7900; 前一种是全表扫描，后一种则会使用组合索引。　　5、or语句使用不当会引起全表扫描　　原因：where子句中比较的两个条件，一个有索引，一个没索引，使用or则会引起全表扫描。例如：where A=：1 or B=：2，A上有索引，B上没索引，则比较B=：2时会重新开始全表扫描。　　6、组合索引，排序时应按照组合索引中各列的顺序进行排序，即使索引中只有一个列是要排序的，否则排序性能会比较差。例如：create index skip1 on emp5(job,empno，date);& select job，empno from emp5 where job=’manager’and empno=’10’ order by job,empno, 实际上只是查询出符合job=’manager’and empno=’10’条件的记录并按date降序排列，但是写成order by date desc性能较差。　　7、Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量。　　8、对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。　　9、select count(*) from table；这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。　　10、sql的where条件要绑定变量，比如where column=：1，不要写成where column=‘aaa’，这样会导致每次执行时都会重新分析，浪费CPU和内存资源。
搜索风云榜
51Testing官方微信
51Testing官方微博
测试知识全知道Posts - 736,
Articles - 0,
Comments - 1442
18:31 by 潇湘隐者, ... 阅读,
在SQL SERVER的查询语句中使用OR是否会导致不走索引查找（Index Seek）或索引失效（堆表走全表扫描 （Table Scan）、聚集索引表走聚集索引扫描（Clustered Index Scan））呢？是否所有情况都是如此？又该如何优化呢？ 下面我们通过一些简单的例子来分析理解这些现象。下面的实验环境为SQL SERVER 2008，如果在不同版本有所区别，欢迎指正。
& 堆表单索引
首先我们构建我们测试需要实验环境，具体情况如下所示：
DROP&TABLE TEST&& CREATE&TABLE TEST (OBJECT_ID& INT, NAME VARCHAR(32));&CREATE&INDEX PK_TEST ON TEST(OBJECT_ID)&& DECLARE @Index&INT =0;&WHILE @Index & 500000BEGIN&&& INSERT INTO TEST&&& SELECT @Index, 'kerry'+CAST(@Index&AS&VARCHAR(6));&& &&& SET @Index = @Index +1;END&&UPDATE&STATISTICS TEST WITH FULLSCAN
& 场景1：如下所示，并不是所有的OR条件都会导致SQL走全表扫描。具体情况具体分析，不要套用教条。
SELECT * FROM TEST WHERE (OBJECT_ID =5 OR OBJECT_ID = 105)
& 场景2：加了条件1=1后，执行计划从索引查找（Index Seek）变为全表扫描（Table Scan），为什么会如此呢？个人理解为优化器将OR运算拆分为两个子集处理，由于一些原因，1=1这个条件导致优化器认定需要全表扫描才能完成1=1条件子集的计算处理（为了理解这个，煞费苦心，鉴于理论薄弱，如有错误或不足，敬请指出）。所以优化器在权衡代价后生成的执行计划最终选择了全表扫描（Table Scan）
SELECT * FROM TEST WHERE (1=1 OR OBJECT_ID =105);
& 场景3: 下面场景比较好理解，因为下面需要从500000条记录中取出499700条记录，而全表扫描（Table Scan）肯定是最优的选择，代价（Cost）最低。
SELECT * FROM TEST WHERE (OBJECT_ID &300 OR OBJECT_ID =105);
& 场景4：这种场景跟场景2的情况本质是一样的。所以在此略过。其实类似这种写法也是实际情况中最常出现的情况，还在迷糊的同学，赶紧抛弃这种写法吧
DECLARE @OBJECT_ID INT =150;&SELECT * FROM TEST WHERE (@OBJECT_ID IS&NULL&OR OBJECT_ID );
& 聚集索引表单索引
在聚集索引表中，我们也依葫芦画瓢，准备实验测试的数据环境。
DROP&TABLE TEST&& CREATE&TABLE TEST (OBJECT_ID& INT, NAME VARCHAR(32));&CREATE&CLUSTERED&INDEX PK_TEST ON TEST(OBJECT_ID)&& DECLARE @Index&INT =0;&WHILE @Index & 500000BEGIN&&& INSERT INTO TEST&&& SELECT @Index, 'kerry'+CAST(@Index&AS&VARCHAR(6));&& &&& SET @Index = @Index +1;END&&UPDATE&STATISTICS TEST WITH FULLSCAN
& 场景1 ：索引查找（Index Seek）
SELECT * FROM TEST WHERE (OBJECT_ID =5 OR OBJECT_ID = 105)
& 场景2：聚集索引扫描（Clustered Index Scan）
& 场景3：似乎与堆表有所不同。聚集索引表居然还是走聚集索引查找。
& 场景4：OR导致聚集索引扫描
& 如果堆表或聚集索引表上建立有联合索引,情况也大致如此，在此不做过多案例讲解。下面仅仅讲述一两个案例场景。
DROP&TABLE test1; &CREATE&TABLE test1 & ( &&&& a INT, &&&& b INT, &&&& c INT, &&&& d INT, &&&& e INT&& ) &DECLARE @Index&INT =0; &WHILE @Index & 10000 & BEGIN&&&&&& INSERT INTO test1 &&&&& SELECT @Index, &&&&&&&&&&&& @Index, &&&&&&&&&&&& @Index, &&&&&&&&&&&& @Index, &&&&&&&&&&&& @Index&&&&&&& SET @Index = @Index + 1; & END&&CREATE&INDEX idx_test_n1 & ON test1(a, b, c, d) &UPDATE&STATISTICS test1 WITH
SELECT * FROM TEST1 WHERE A=12 OR B& 500 OR C &100000
& 因为结果集是几个条件的并集，最多只能在查找A=12的数据时用索引，其它几个条件都需要表扫描，那优化器就会选择直接走一遍表扫描，以最低的代价COST完成，所以索引就失效了。
& 那么如何优化查询语句含有的OR的SQL语句呢？方法无外乎有三种：
1：通过索引覆盖，使包含OR的SQL走索引查找（Index Seek）。但是这个只能满足部分场景，并不能解决所有这类SQL。这个Solution具有一定的局限性。
SELECT * FROM TEST1 WHERE A=12 OR B=500
如果我们通过索引覆盖，在字段B上面也建立索引，那么下面OR查询也会走索引查找。
CREATE&INDEX IDX_TEST1_B ON TEST1(B);&SELECT * FROM TEST1 WHERE A=12 OR B=500
& 2：使用IN替换OR。 但是这个Solution也有很多局限性。在此不做过多阐述。
& 3：一般将OR的字句分解成多个查询，并且通过UNION ALL 或UNION连接起来。在联合索引或有索引覆盖的场景下。大部分情况下，UNION ALL的效率更高。但是并不是所有的UNION ALL都会比OR的SQL的代价（COST），特殊的情况或特殊的数据分布也会出现UNION ALL比OR代价要高的情况。例如，上面特殊的要求，从全表中取两条记录，如下所示
SELECT * FROM TEST1 WHERE A=12&UNION&ALL&SELECT * FROM TEST1 WHERE B=500
& UNON ALL语句的代价（Cost）要高与OR是因为它做了两次索引查找（Index Seek），而OR语句只做一次索引查找（Index Seek）就完成了。开销明显小一些，但是实际情况这类特殊情况比较少，实际情况的取数条件、数据都比这个简单案例要复杂得多。所以在大部分情况下，拆分为UNION ALL语句的效率要高于OR语句
另外一个案例，就是最上面实验的堆表TEST， 在字段OBJECT_ID上建有索引
SELECT * FROM TEST WHERE (OBJECT_ID &300 OR OBJECT_ID =105);&SELECT * FROM TEST WHERE OBJECT_ID &300&UNION&ALL&SELECT * FROM TEST WHERE OBJECT_ID =105;
可以从下面看出两者开销不同的地方在于IO方面，两者开销之所以有区别，是因为第二个SQL多了一次扫描（索引查找）
&&& 在实际开发环境中，OR这种写法确实会带来很多不确定性，尽量使用UNION 或IN替换OR。我们需要遵循一些规则，但是也不能认为它就是一成不变的，永为真理。具体场景、具体环境具体分析。要知其然知其所以然。在微软亚太区数据库技术支持组的官方博客中就有一个案例也是OR引起的性能案例。 博客中有个观点，我觉得挺赞的：”需要注意的是，对于OR或UNION，并没有确定的孰优孰劣，使用时要进行测试才能确定。“ 。[SQL]会引起全表扫描的10种SQL语句
[SQL]会引起全表扫描的10种SQL语句
发布时间： 18:54:55
编辑：www.fx114.net
本篇文章主要介绍了"[SQL]会引起全表扫描的10种SQL语句 "，主要涉及到[SQL]会引起全表扫描的10种SQL语句 方面的内容，对于[SQL]会引起全表扫描的10种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、使用组合索引，如果查询条件中没有前导列，那么索引不起作用，会引起全表扫描；但是从Oracle9i开始，引入了索引跳跃式扫描的特性，可以允许优化器使用组合索引，即便索引的前导列没有出现在WHERE子句中。例如：create index skip1 on emp5(job,empno); 全索引扫描 select count(*) from emp5 where empno=7900; 索引跳跃式扫描 select /*+ index(emp5 skip1)*/ count(*) from emp5 where empno=7900; 前一种是全表扫描，后一种则会使用组合索引。
　　5、or语句使用不当会引起全表扫描
　　原因：where子句中比较的两个条件，一个有索引，一个没索引，使用or则会引起全表扫描。例如：where A=：1 or B=：2，A上有索引，B上没索引，则比较B=：2时会重新开始全表扫描。
　　6、组合索引，排序时应按照组合索引中各列的顺序进行排序，即使索引中只有一个列是要排序的，否则排序性能会比较差。例如：create index skip1 on emp5(job,empno，date); select job，empno from emp5 where job=&manager&and empno=&10& order by job,empno, 实际上只是查询出符合job=&manager&and empno=&10&条件的记录并按date降序排列，但是写成order by date desc性能较差。
　　7、Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。
　　8、对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。
　　9、select count(*) from table；这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。
　　10、sql的where条件要绑定变量，比如where column=：1，不要写成where column=&aaa&，这样会导致每次执行时都会重新分析，浪费CPU和内存资源。
一、不得利用本站危害国家安全、泄露国家秘密，不得侵犯国家社会集体的和公民的合法权益，不得利用本站制作、复制和传播不法有害信息！
二、互相尊重，对自己的言论和行为负责。
本文标题：
本页链接：