Oracle-Sql优化

Mr.LR2022年11月30日

Oracle

Oracle
Sql优化

大约 24 分钟

Oracle-Sql优化

Sql优化对于开发人员来说，必不可少。但是我通过网上查阅资料发现，针对Oracle优化的文章相对较少，大多数都是针对Mysql的优化。由于我工作中以Oracle为主，因此这里结合索引数据结构、执行计划，给出一些sql优化的思路

索引的数据结构

索引的数据结构有：B+树、位图索引、散列(hash)、kd树等。其中B+树最为经典。

我们平常开发，定义的主键索引、普通索引、唯一索引等。默认都是B+树，因此本文重点讲解下B+树的数据结构。

B+树，官方资料简介：索引和按索引组织的表 (oracle.com)open in new window

B+trees 是平衡树的缩写，是最常见的数据库索引类型。B+tree 索引是一个有序的值列表，分为多个范围。通过将键与行或行范围相关联，B+树为广泛的查询提供了出色的检索性能，包括精确匹配和范围搜索。

索引案例推演

这里以一个简单的案例推演B+树的形成过程

1、假设对如下的表进行B+树的构建，这里假设对主键索引C1列进行构建

create table BTree(
    c1 number primary key ,
    c2 varchar2(100),
    c3 varchar2(100)
)

2、索引底层数据结构是存储在数据块上的，假设一条数据的详细信息如下

B+树的rowid：在oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，rowid确定了每条记录是在oracle中的哪一个数据文件、块、行上。

存储在数据库中的每一行都有一个地址。Oracle 数据库使用一种ROWID数据类型来存储数据库中每一行的地址（rowid）。

访问数据库表行的最快方法是通过其地址或rowid来唯一标识它。

3、这里假设一个数据块可以存放3条数据，那么一个数据块的数据案例如下

其中数据从小到大排列，采用单链表的形式连接

4、如果构建两层B+树，案例如下

叶子节点的块与块之间通过双链表连接

父亲节点的每条数据存储叶子节点的块号，和该块的最小值，并且也是从小到大排列

结合这个两层的B+树，当我们查找C1=5的数据时，可以很快的在块4中确定具体的数据在块02中

5、如果构建三层B+树，案例如下

同理，如果是三层的B+数据，父亲节点每条数据存储叶子节点的块号，和该块的最小值，并且也是从小到大排列

结合这个三层的B+树，当我们查找C1=15的数据时，可以根据块21知道该数据在块12的孩子中，再根据块12知道该数据在块05中

从这里也可以看出，如果通过索引查询数据，可以通过三个数据块即9条数据，就可以查询到结果，如果不通过索引，则需要查询9个数据块，27条数据查询。说明了通过索引查询效率是远高于全表查询。

6、结合上图的B+树，很容易理解为什么通过索引查询数据时非常快

真实场景中，一个数据块存储1000条数据是没有问题的，这时如果我们的B+树也是三层，那么它的总数据量就是 1000*1000*1000=10亿，

即如果我们用索引查询数据时，最少可以通过3个数据块(3000条数据)查询，如果不用索引，则需要10亿条数据，查询效率差异是非常明显的。

结论

B+树索引有两种类型的块：用于搜索的分支块和用于存储键值的**叶块。**B+树索引的上层分支块包含指向下层索引块的索引数据。
B+树索引是平衡的，因为所有叶块自动保持在相同的深度。
分支块存储在两个键之间做出分支决策所需的最小键前缀。这种技术使数据库能够在每个分支块上容纳尽可能多的数据。分支块包含指向包含键的子块的指针。键和指针的数量受块大小的限制。
叶块包含每个索引数据值和用于定位实际行的相应 rowid。每个条目都按 (key, rowid) 排序。在叶块中，键和 rowid 链接到其左右兄弟条目。叶块本身也是双重链接的。

索引的缺点

了解索引的数据结构后，我们知道，索引的优点就是加快查询速度，那么索引的缺点有哪些？

创建索引和维护索引要耗费时间，并且随着数据量的增加，所耗费的时间也会增加。
索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间存储在磁盘上，如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸。
虽然索引大大提高了查询速度，同时却会降低更新表的速度。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

索引的设计原则

哪些情况适合创建索引？

1. 字段的数值有唯一性的限制

索引本身可以起到约束的作用，比如唯一索引、主键索引都可以起到唯一性约束的，因此在我们的数据表中，如果某个字段是唯一的，就可以直接创建唯一性索引，或者主键索引。这样可以更快速地通过该索引来确定某条记录。

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。（来源：Alibaba）
说明：不要以为唯一索引影响了insert速度，这个速度损耗可以忽略，但提高查找速度是明显的。

2. 频繁作为 WHERE 查询条件的字段

某个字段在SELECT语句的 WHERE 条件中经常被使用到，那么就需要给这个字段创建索引了。尤其是在数据量大的情况下，创建普通索引就可以大幅提升数据查询的效率。

3. 经常 GROUP BY 和 ORDER BY 的列

索引就是让数据按照某种顺序进行存储或检索，因此当我们使用 GROUP BY 对数据进行分组查询，或者使用 ORDER BY 对数据进行排序的时候，就需要对分组或者排序的字段进行索引。如果待排序的列有多个，那么可以在这些列上建立组合索引。

4. UPDATE、DELETE 的 WHERE 条件列

对数据按照某个条件进行查询后再进行 UPDATE 或 DELETE 的操作，如果对 WHERE 字段创建了索引，就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录，然后再对它进行更新或删除。如果进行更新的时候，更新的字段是非索引字段，提升的效率会更明显，这是因为非索引字段更新不需要对索引进行维护。

5.DISTINCT 字段需要创建索引

有时候我们需要对某个字段进行去重，使用 DISTINCT，那么对这个字段创建索引，也会提升查询效率。

6. 多表 JOIN 连接操作时，创建索引注意事项

首先，连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。

其次，对 WHERE 条件创建索引，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。

最后，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。

7. 使用列的类型小的创建索引

我们这里所说的类型大小指的就是该类型表示的数据范围的大小。

数据类型越小，在查询时进行的比较操作越快
数据类型越小，索引占用的存储空间就越少，在一个数据块内就可以放下更多的记录，从而减少磁盘I/O带来的性能损耗，也就意味着可以把更多的数据块缓存在内存中，从而加快读写效率。

8. 使用最频繁的列放到联合索引的左侧

9. 在多个字段都要创建索引的情况下，联合索引优于单值索引

哪些情况不适合创建索引？

1. 在where中使用不到的字段，不要设置索引

2. 数据量小的表最好不要使用索引

3. 有大量重复数据的列上不要建立索引

在条件表达式中经常用到的不同值较多的列上建立索引，但字段中如果有大量重复数据，也不用创建索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值，因此无须建立索引。如果建立索引，不但不会提高查询效率，反而会严重降低数据更新速度。

举例1：要在100万行数据中查找其中的50万行（比如性别为男的数据），一旦创建了索引，你需要先访问50万次索引，然后再访问50万次数据表，这样加起来的开销比不使用索引可能还要大。举例2：假设有一个学生表，学生总数为100万人，男性只有10个人，也就是占总人口的10万分之1。这样加索引才有意义

结论：当数据重复度大，比如高于10%,也不需要对这个字段使用索引

4. 避免对经常更新的表创建过多的索引

第一层含义：频繁更新的字段不一定要创建索引。因为更新数据的时候，也需要更新索引，如果索引太多，在更新索引的时候也会造成负担，从而影响效率。

第二层含义：避免对经常更新的表创建过多的索引，并且索引中的列尽可能少。此时，虽然提高了查询速度，同时却会降低更新表的速度。|

5. 不建议用无序的值作为索引

例如身份证、UUID(在索引比较时需要转为ASCII，并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等。

6. 删除不再使用或者很少使用的索引

7. 不要定义冗余或重复的索引

冗余：一个字段定义了多种索引，有联合索引，又定义普通索引

执行计划

执行计划描述数据库引擎执行sql语句时实施的操作，那么如何获取执行计划？

display函数

display函数返回计划表中存储的执行计划，返回值是dbms_xplan_type_table集合的实例。此函数有以下输入参数。

--针对 sql 生成对应的执行计划，并存入 计划表
explain plan set statement_id='nTOhnPUxPgL17qc1g6BHZA==' for select * from STUDENT;
--从计划取出执行计划
select * from table(DBMS_XPLAN.DISPLAY('PLAN_TABLE','nTOhnPUxPgL17qc1g6BHZA=='));

table_name：指定计划表的名称。默认是plan_table。如果指定null，则使用默认值
statement_id：指定sql语句的名称，当执行explain plan语句的时候，作为可选参数给出。默认为null。如果使用了默认值，则显示最近一次插入执行计划表的执行计划。
format：format指定在输出中提供哪些信息。可以使用基本值(basic、typical....),想要精细控制，可以向它们添加额外的修饰符(adaptive、alias、bytes.......)。如果有应该添加的信息，可以选择使用+这个字符作为前缀的修饰符（例如，basic+predicate)。如果有应该移除的信息，可以选择使用-这个字符作为前缀的修饰符（例如，typical-bytes)。可以同时指定多个修饰符（例如，typical+alias-bytes-cost)。表10-2和表10-3分别完整描述了基本值和修饰符。默认值是typical。

filter_preds：指定查询计划表时应用的限制条件。此限制条件为基于执行计划表中的一个列的常规谓词(例如：statement_id=‘123’)。默认是null。如果是null，则会显示最近一次插入到计划表的执行计划。

display_cursor函数

display_cursor函数返回库缓存中存储的执行计划。所以使用该方法，必须实际执行sql才可以。

--设置当前会话数据库和操作系统统计信息的收集级别
ALTER SESSION SET STATISTICS_LEVEL=all;--代表 系统收集所有的统计信息  basic 关闭了所有性能数据的收集  typical 部分收集 （默认为typical）

select * from V$SQL where  sql_text LIKE '%select * from STUDENT where AGE = 24%';

select * from STUDENT where AGE = 24;

select * from table(dbms_xplan.display_cursor(sql_id=>'3k8xft0cbvmqf', format=>'iostats'));

sql_id：指定返回的执行计划的父游标。默认值是null。如果使用了默认值，就会返回当前会话执行的最后一个sql语句的执行计划。（默认值的方式，只在sqlplus中适用，在一些客户端工具中不适用。例如：plsql developer每执行完一个select后，就会默认开启一个新的事务，会执行begin :id := sys.dbms_transaction.local_transaction_id; end;，所以这才是在plsql developer中的最后一条语句）
cursor_chile_no：指定子游标号，它与sql_id一起，确定返回哪个子游标的执行计划。默认是0，如果指定null ,则会返回sql_id参数指定的父游标下的所有子游标
format：支持的值与display相同。注：如果要看实际的行数参考如下。

执行计划字段详解

关于 PLAN_TABLE 的 OPERATION 和 OPTION 列详细信息，参考

https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/optimizer-ref.html#GUID-CD61854E-639B-4F06-8CD2-C1980D6975DEopen in new window

如何看执行计划？

根据执行计划，我们可以看出一个复杂sql的真正执行顺序是怎样的，那么面对执行计划，我们的阅读顺序是什么？

口诀：最右最上先执行，

从上往下，第一个没有子节点的步骤先执行
对于兄弟节点，即靠上的节点先执行
所有兄弟节点执行完以后，执行父节点

并且执行计划可以看做一颗树，用来描述sql的执行顺序

一个父操作可以拥有多个子操作
一个子操作只有一个父操作
唯一没有父操作的是树的根操作
子操作跟随着它们的父操作，在右侧缩进排列。依赖于显示执行计划使用的方法，缩进可以是一个空格字符、两个空格或其他什么。这真的不重要。关键是同一个父操作下的所有子操作都拥有相同的缩进。
父操作在子操作之前出现（父操作的D比子操作的D要小）。如果一个子操作前面有多个与父操作一样缩进的操作，则距离最近的操作为父操作

例如：

CREATE TABLE t (
  id NUMBER,
  val NUMBER,
  pad VARCHAR2(1000),
  CONSTRAINT t_pk PRIMARY KEY (id)
);

CREATE INDEX i ON t (val);

UPDATE t
SET val = (SELECT /*+ index(t) */ max(val) FROM t WHERE id BETWEEN 6 AND 19),
    pad = (SELECT pad FROM t WHERE id = 6)
WHERE id IN (SELECT id FROM t WHERE id BETWEEN 6 AND 19);

操作0是根操作
操作1有三个子操作：2,3,6
操作2没有子操作
操作3有一个子操作
操作4有一个子操作
操作5没有子操作
操作6有一个子操作
操作7没有子操作

了解父-子关系对于理解执行计划执行各个操作的顺序十分关键。实际上，为了完成它们的任务，父操作需要由它们的子操作提供的数据。因此，虽然执行是从树的根部开始的，第一个被完全执行的操作是没有子操作的那个。

父操作调用子操作
子操作在它们的父操作之前被完全执行。
子操作向它们的父操作传递数据。

例如：下面的场景，可以明显看到操作1返回的行数最多，但是我们优化的思路是在操作2和操作3上，因为操作1返回的行数是由操作2和操作3叠加得来的。

索引优化

查询优化器

发送到数据库的每个SQL语句在由SQL引擎处理之前都要转化成执行计划。事实上，应用程序只是通过SQL语句指定了什么样的数据必须处理，而未指定如何处理。查询优化器的目标不仅是提供执行计划来描述如何处理数据，同时最重要的是，交付高效的执行计划。如果做不到这一点可能会导致槽糕的性能。

在Oracle数据库中，提供两个主要的查询优化器：基于规则的优化器(RBO)和基于成本的优化器(CBO)。从Oracle Database 10g开始，已经不再支持基于规则的优化器，所以谈到查询优化器这个术语时，始终指的是基于成本的优化器。

说白了就是一个sql的执行计划，是由查询优化器提供的，但是执行计划的生成并没有固定的规律，查询优化器器需要根据最优访问路径、连接顺序、是否需要并行等各方面因素，来提供执行计划。即我们写的sql不是说用到索引字段，就一定会走索引，由于sql的书写不规范，会出现索引失效的场景。

下面列出一些常见的索引失效的案例，索引失效的规则并非百分之百，具体情况还需要具体分析

准备数据

create table class
(
    id        number(10) primary key,
    className varchar2(30),
    address   varchar2(40),
    monitor   number
);

create table student
(
    id      number(10) primary key,
    stuno   number(20),
    name    varchar2(30),
    age     number(4),
    classId number(20),
    year date
);
--  随机产生字符串
select dbms_random.string('u',20) from dual;
-- 随机数
SELECT ABS(MOD(DBMS_RANDOM.RANDOM,30)) FROM DUAL;
--随机时间 视图
create or  replace  view getRamDate as
  SELECT to_date(TRUNC(DBMS_RANDOM.VALUE(

to_number(to_char(to_date('20000101','yyyymmdd'),'J')),

to_number(to_char(to_date('20200501','yyyymmdd')+1,'J')))),'J')+

DBMS_RANDOM.VALUE(1,3600)/3600
prize_time
FROM dual;

--往 student 表插入1W数据
DECLARE
    TEMP number(20) := 10000;
BEGIN
    for i in 1..TEMP
        loop
            INSERT INTO STUDENT (ID, STUNO, NAME, AGE, CLASSID,year)
            VALUES (i, 10000 + i, (select dbms_random.string('u', 10) from dual), (SELECT ABS(MOD(DBMS_RANDOM.RANDOM,30)) FROM DUAL), (SELECT ABS(MOD(DBMS_RANDOM.RANDOM,50000)) FROM DUAL),(select prize_time from GETRAMDATE));
        end loop;
END;

--往 class 表插入50 W数据
DECLARE
    TEMP number(20) := 500000;
BEGIN
    for i in 1..TEMP
        loop
            INSERT INTO CLASS (ID, CLASSNAME, ADDRESS, MONITOR) VALUES (i, (select dbms_random.string('u', 8) from dual), (select dbms_random.string('u', 8) from dual),(SELECT ABS(MOD(DBMS_RANDOM.RANDOM,1000)) FROM DUAL) );
        end loop;
END;

索引失效案例

用没有索引的列查询

--查询索引
select * from all_indexes WHERE table_name in ('STUDENT','CLASS');
--普通的查询，没有用到索引字段 
select * from student;
select * from  student where STUNO = 10002;

最佳左前缀法则

建立联合索引时会遵守最佳左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

CREATE INDEX idx_age_classid_name ON student(age,classId,NAME);
--虽然我们创建的联合索引中有classId，但根据最佳左前缀法则，下面的sql没有最左字段age。因此，这个联合索引用不上
SELECT  * FROM student WHERE student.classid=1 AND student.name = 'abcd';
--或者我们只用了第一个和第三个字段，那么最终索引也只能用到第一个age,而name不走索引
SELECT  * FROM student WHERE student.age=30 AND student.name = 'abcd';
--删除本例索引，为后面测试做准备
drop index idx_age_classid_name;

结论：可以为多个字段创建索引。对于多列索引，**过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。**如果查询条件中没有使用这些字段中第1个字段时，多列（或联合）索引不会被使用。

计算、函数、类型转换(自动或手动)导致索引失效

CREATE INDEX idx_sno ON student(stuno);
SELECT  id, stuno, NAME FROM student WHERE stuno+1 = 900001;--做了+1运算，用不上索引
SELECT  id, stuno, NAME FROM student WHERE stuno = 900000;

CREATE INDEX idx_year ON student(YEAR);
--不走索引
select * from STUDENT where YEAR+1 = sysdate;
select * from STUDENT where to_char(YEAR,'yyyy-MM-dd') = '2020-01-01';
--走索引
select * from STUDENT where YEAR = to_date('2020-01-01','yyyy-MM-dd');--走索引，原因是
select * from STUDENT where YEAR = sysdate+1;

如果一定要用函数查询，则创建函数索引

drop index idx_year;
CREATE INDEX idx_year_tochar ON student(to_char(YEAR,'yyyy-MM-dd'));
--这样就又走索引了，
select * from STUDENT where to_char(YEAR,'yyyy-MM-dd') = '2020-01-01';
--但是注意，使用函数时需要和创建索引的一致，否则不走索引
select * from STUDENT where to_char(YEAR,'YYYY-MM-dd') = '2020-01-01';

--删除本例索引，为后面测试做准备
drop index idx_sno ;
drop index idx_year_tochar ;

不等于(!= 或者<>)索引失效

很好理解，不等于，只能全表扫描了。

CREATE INDEX idx_sno ON student(stuno);

select * from STUDENT where STUNO != '123'
--删除本例索引，为后面测试做准备
drop index idx_sno ;

is null和is not null无法使用索引

create index idx_name on STUDENT(NAME);

SELECT  * FROM student WHERE NAME IS NULL;
SELECT  * FROM student WHERE NAME IS not  NULL;
--删除本例索引
drop index idx_name;

注：MySql在使用is null 也会走索引
结论：最好在设计数据表的时候就将字段设置为 NOT NULL 约束，比如你可以将INT类型的字段，默认值设置为0。将字符类型的默认值设置为空字符串('')

like以通配符%开头索引失效

create index idx_name on STUDENT(NAME);

SELECT  * FROM student WHERE NAME LIKE 'ab%';--使用索引
SELECT  * FROM student WHERE NAME LIKE '%ab%';--不使用索引
--删除本例索引
drop index idx_name;

拓展：Alibaba《Java开发手册》
【强制】页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

OR 前后存在非索引的列，索引失效

CREATE INDEX idx_age ON student(age);
CREATE INDEX idx_cid ON student(classid);
--如果只建立一个字段的索引，无法走索引，必须建立两个字段
SELECT  * FROM student WHERE age = 10 OR classid = 100;
--删除本例索引
drop index idx_age;
drop index idx_cid;

总结：

对于单列索引，尽量选择针对当前query过滤性更好的索引
在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
在选择组合索引的时候，尽量选择能够包含当前query中的where子句中更多字段的索引。
在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。

总之，书写SQL 语句时，尽量避免造成索引失效的情况。

多表查询优化

结论：
尽量在连接字段建立索引
多表关联最好不要超过3张表
减少驱动表需要查询的自动个数(不要用select *)

多表联接分类：Joins (oracle.com)open in new window

嵌套循环联接

由嵌套循环联接处理的两组数据称作外循环（也称作驱动行源）和内循环。外循环是左输人，而内循环则是右输入。

嵌套循环联接拥有的具体特征如下所示

左输入（外循环）只执行一次。右输入（内循环）可能会执行很多次。
它们能够在处理完所有数据之前就返回结果集的第一行。
它们既可以利用索引应用于限制条件上，也可以应用于联接条件上。
它们支持所有类型的联接。

select * from CLASS cc left join STUDENT ss on cc.ID = ss.CLASSID
where ss.ID in (1) ;

类比，具体哪一张表为外层循环(驱动表)，由oracle自动选择，取决于表的大小，以及联接字段的索引情况

for( id : STUDENT ){
	for(id :class){
		.....
	}
}

缓冲区缓存预取

当索引或表块不在缓冲区缓存中并且需要处理连接时，需要物理 I/O。数据库可以批处理多个物理 I/O 请求，并使用矢量 I/O（数组）而不是一次处理一个请求。数据库将 rowid 数组发送到操作系统，操作系统执行读取。

哈希连接

数据库使用哈希联接来联接较大的数据集。

优化程序使用两个数据集中较小的一个在内存中的联接键上构建哈希表，使用确定性哈希函数指定哈希表中存储每一行的位置。然后，数据库扫描较大的数据集，探测哈希表以查找满足连接条件的行。

Hash Join是做大数据集连接时的常用方式，优化器使用两个表中较小（相对较小）的表利用Join Key在内存中建立散列值，然后扫描较大的表并探测散列值，找出与Hash表匹配的行。

select * from CLASS cc left join STUDENT ss on cc.ID = ss.CLASSID;

排序合并连接

排序合并连接是嵌套循环连接的变种。

如果两个数据集还没有排序，那么数据库会先对它们进行排序，这就是所谓的sort join操作。

对于数据集里的每一行，数据库会从上一次匹配到数据的位置开始探查第二个数据集，这一步就是Merge join操作。

select * from CLASS cc left join STUDENT ss on cc.ID > ss.CLASSID;

优化器什么时候会考虑使用排序合并连接？

连接条件是不等式，比如：<，<=，>或者>=，相对应的，哈希连接要求是等式条件。
因为其他的一些操作要求排序，优化器会认为使用排序合并连接的成本更低。同时，如果有索引的话，那么第一个数据集就能够避免使用排序。但是，第二个数据集不论有没有索引，都会要求排序

合并联接的使用并不是很频繁。原因是，在大多数的情况下，无论是嵌套循环联接还是散列联接，都执行得比合并联接要更好。

特殊场景的优化

覆盖索引

简单说就是，索引列+主键包含SELECT 到 FROM之间查询的列。

CREATE INDEX idx_cid_n ON student(NAME,CLASSID);

SELECT  * FROM student WHERE NAME ='123';
--索引快速扫描
SELECT  CLASSID,NAME FROM student WHERE NAME !='123';
SELECT  CLASSID,NAME FROM student WHERE NAME like '%123%';

in和exists

首先要了解in是否走索引也分情况，如果in的取值范围特别大时，也会导致索引失效。

select * from CLASS where id in (1,2,3,4,5,998);

select * from CLASS where id in (select CLASSID from STUDENT);

参考别人的说法

如果两张表大小差不多，那么exists和in的效率差不多。
若子查询结果集比较小，优先使用in
若外层查询比子查询小，优先使用exists。

not in 和 not exists

推荐使用not exists

并且如果子查询有null字段，两者的查询结果不同，参考下面的例子

select * from t1;

select * from t2;

create table t1(c1 int,c2 int);
create table t2(c1 int,c2 int);
insert into t1 values(1,2);
insert into t1 values(1,3);
insert into t2 values(1,2);
insert into t2 values(1,null);
select * from t1 where c2 not in(select c2 from t2);-->执行结果：无
select * from t1 where not exists(select 1 from t2 where t2.c2 = t1.c2)

参考资料

《Oracle性能诊断艺术》

Oracle-Sql优化

# Oracle-Sql优化

# 索引的数据结构

# 索引案例推演

# 结论

# 索引的缺点

# 索引的设计原则

# 哪些情况适合创建索引？

# 哪些情况不适合创建索引？

# 执行计划

# display函数

# display_cursor函数

# 执行计划字段详解

# 如何看执行计划？

# 索引优化

# 查询优化器

# 索引失效案例

# 用没有索引的列查询

# 最佳左前缀法则

# 计算、函数、类型转换(自动或手动)导致索引失效

# 不等于(!= 或者<>)索引失效

# is null和is not null无法使用索引

# like以通配符%开头索引失效

# OR 前后存在非索引的列，索引失效

# 总结：

# 多表查询优化

# 嵌套循环联接

# 哈希连接

# 排序合并连接

# 特殊场景的优化

# 覆盖索引

# in和exists

# not in 和 not exists

# 参考资料

Oracle-Sql优化

索引的数据结构

索引案例推演

结论

索引的缺点

索引的设计原则

哪些情况适合创建索引？

哪些情况不适合创建索引？

执行计划

display函数

display_cursor函数

执行计划字段详解

如何看执行计划？

索引优化

查询优化器

索引失效案例

用没有索引的列查询

最佳左前缀法则

计算、函数、类型转换(自动或手动)导致索引失效

不等于(!= 或者<>)索引失效

is null和is not null无法使用索引

like以通配符%开头索引失效

OR 前后存在非索引的列，索引失效

总结：

多表查询优化

嵌套循环联接

哈希连接

排序合并连接

特殊场景的优化

覆盖索引

in和exists

not in 和 not exists

参考资料