sql怎么读_关于对于sql常见的错误理解

create table test3(id int not null PRIMARY KEY,

cust_name varchar2(10),

type1 varchar2(10) not null,

value1 varchar2(10),

value2 varchar2(10),

value3 varchar2(10),

value4 varchar2(10),

value5 varchar2(10),

value6 varchar2(10)

);

begin

for i in 1..10000 loop

insert into test3 values(i,'zry'||i,'aaaa','value1','value2','value3','value4','value5','value6');

end loop;

commit;

end;

/

begin

for i in 10001..11000 loop

insert into test3 values(i,'zry'||i,'bbbb','value1','value2','value3','value4','value5','value6');

end loop;

commit;

end;

/

begin

for i in 11001..12000 loop

insert into test3 values(i,'zry'||i,'cccc','value1','value2','value3','value4','value5','value6');

end loop;

commit;

end;

/

begin

for i in 12001..13000 loop

insert into test3 values(i,'zry'||i,'dddd','value1','value2','value3','value4','value5','value6');

end loop;

commit;

end;

/

begin

for i in 13001..14000 loop

insert into test3 values(i,'zry'||i,'eeee','value1','value2','value3','value4','value5','value6');

end loop;

commit;

end;

/

begin

for i in 14001..15000 loop

insert into test3 values(i,'zry'||i,'ffff','value1','value2','value3','value4','value5','value6');

end loop;

commit;

end;

/

insert into test3(id,type1,value1,value2,value3,value4,value5,value6) values(15001,'gggg','value1','value2','value3','value4','value5','value6');

在开始问题之前先介绍oracle几种看执行计划的方式，当然方式很多，为了不浪费时间我只介绍三种常用的吧，只有了解了执行计划才能知道sql是如何真正执行的

（1）第一种，explain plan的方式-这种方式sql没有真正执行，只是根据统计信息等优化器估算的一种执行计划，一般来说都是跟真正的执行计划是一样的。----也就是说这种不是真正的执行计划，虽然概率小，但是有可能有出入

SQL> explain plan for select * from test3 where id=10003;

（2）第二种，这种方式的执行计划其实也是取自v$sql的基表，跟第一种是一样的，但是sql是执行过的，下方的statistics信息是正确的，这种方式可以看到真正的消耗，包括物理读、逻辑读等信息 ----这种方式看到的也不是真正的执行计划，但是可以看到真实的消耗

（3）第三种，这种需要先执行sql才能看到，这种执行计划才是真正的执行计划，a-rows是真实的行数

SQL> ALTER SESSION SET STATISTICS_LEVEL=ALL;

下面来介绍下通常听说到的错误言论

1.空值列可不可以建索引

SQL> create index idx_name1 on test3(cust_name);

Index created.

一般的索引是不保存null值，并不是不能在有null值的列上创建索引

SQL> select id,cust_name from test3 where cust_name='zry10004';

2.where column is null能不能走索引，以及为什么有时我建了索引没有变快（1.索引不合理 2.索引失效或没有走索引）

这种说法是见的比较多的，甚至有的人直接说null，<>,like不能走索引。

①null值也可以走索引

SQL> create index idx_name2 on test3(cust_name,0);

通过这种方式可以让索引保存null值，下面可以看到图片中真实消耗的逻辑读降低很多，也走了索引，但是执行计划只有第三张的显示是完全正确的。但是基本信息都是对的。

如果走索引是效率比较高的方式，但是建上索引没有走，是什么原因。很有可能是统计信息失效，可以先收集下统计信息，如下图所示

②<>是不会走索引的

通过这张图可以看到是通过索引先筛选id再去回表筛选<>的条件，<>并没有走索引

③like 'x%'是可以走索引的，其他的'%x%'’和'%x'是不走索引的

SQL> create index idx_type1 on test3(type1);

3.消除回表（因为有人疑惑为什么我的sql走索引了啊，怎么出现效率问题了，有可能是回表造成的）

这儿很多朋友并不知道回表是什么意思，简单解释下，一条sql的where条件列有索引，并且筛选性不错，那么执行的时候一般都会走索引，但是查询出的列可能有好几列不在索引中，这时候通过索引筛选完，还要回到表中去取其他的数据，就造成了又一次消耗，称为回表。

有回表并不一定要去消除，有时在不修改sql语句的时候也无法消除。只有造成性能压力的时候才去考虑。

最常用有效的方式就是建立联合索引

4.where后条件先后顺序，有人说where后面要先写筛选性高的条件，因为在前面的先执行（是有多看不起优化器啊）

SQL> create index idx_type on test3(type1,0);

Index created.

SQL> select * from test3 where type1='cccc' and id between 11001 and 11013;

SQL> select * from test3 where id between 11001 and 11013 and type1='cccc' ;

可以看出执行计划一样，而且消耗也是一样，所以sql执行顺序跟where后条件位置无关，一般优化器会选择更高效的方式

强制走索引

select /*+ index(t idx_type)*/ * from test3 t where t.id between 11001 and 11013 and t.type1='cccc' ;

可以看到强制走其他索引的消耗变高了

5. in 、exits 和join，not in、not exists和join

create table test4(id int not null PRIMARY KEY,

cust_name varchar2(10)

);

begin

for i in 1..1000 loop

insert into test4 values(i,'zry'||i);

end loop;

commit;

end;

/

①in 、exits 和join

②not in、not exists和join

begin

for i in 1001..14999 loop

insert into test4 values(i,'zry'||i);

end loop;

commit;

end;

/

可以看到in和exists、not in和not exists的执行计划一样，join的执行计划改变了，所以真正情况下谁的效率高低也没有确定的结论。要考虑多种情况，像上面这种执行计划一样的效率肯定一样，所以直接说not exists比not in效率高的都是错误的。

ps：生产中一般用not exists替带not in 在大数据量的处理中、和不同的数据库的优化器选择中，not exists确实效率会比not in高，但是一般exists和in的效率差不多。