执行计划
也叫查询计划,它是数据库执行SQL语句语句的具体步骤和过程。
SQL查询语句的执行计划主要包括:
- 访问表的方式:数据库通过索引或者全盘扫描等方式访问表中的数据。
- 多表连接的方式:数据库使用什么方法实现表和表之间的连接,包括多个表的先后访问顺序。
- 分组聚合及排序等操作的实现。
我们在执行每一条SQL语句时都需要经过分析器、优化器以及执行器的处理,然后再返回最终结果。
简单的说,我们在客户端执行一条SQL语句,这条语句首先进入分析器(解析SQL语句的各个组成部分,进行语法分析,并检查SQL语句的语法是否符合规范),然后进入优化器(优化器会利用数据库收集到的统计信息决定SQL语句的最佳执行方式。例如:是通过索引还是通过全盘扫描的方式访问一张表或者多张表),最后,执行器会根据优化之后的执行计划调用对应的执行模板来获取数据,并将结果返回给客户端。
查看执行计划:
EXPLAIN PLAN FOR
要查看的SQL语句;
SELECT * FROM TABLE(DBMS_XPLAN.display);
执行计划的常用列字段解释:
- Id:序号
- Operation:当前操作的内容
- Name:对象名
- Rows:基数,Oracle 估计当前操作的返回结果集
- Bytes:字节
- Cost:SQL执行的代价
- Time:Oracle估计的操作时间
执行顺序:
根据Operation缩进来判断,缩进最多的最先执行;(缩进相同时,最上面的最先执行)
示例1:非索引列where条件
EXPLAIN PLAN FOR
SELECT * FROM emp WHERE ENAME = 'SMITH';
SELECT * FROM TABLE(dbms_xplan.display);
名词解释:
- TABLE ACCESS FULL:全盘扫描(Oracle会读取表中所有的行,并检查每一行是否满足SQL语句中的 Where 限制条件)
- filter:把所有的数据都访问了,然后过滤掉不需要的数据
使用建议:数据量太大的表不建议使用全表扫描,过多的I/O操作会浪费资源。
示例2:索引列的where条件
EXPLAIN PLAN FOR
SELECT * FROM emp WHERE empno = 7369;
SELECT * FROM TABLE(dbms_xplan.display);
名词解释:
- INDEX UNIQUE SCAN:索引唯一扫描,针对唯一性索引(UNIQUE INDEX)的扫描,每次至多只返回一条记录;表中某字段存在 UNIQUE、PRIMARY KEY 约束时,Oracle常实现唯一性扫描
- TABLE ACCESS BY INDEX ROWID:该操作是数据库为了从索引中获取一些rowid,然后拿着这些rowid去块中读取数据
- access:通过某种方式定位了需要的数据,然后读取出这些结果集
使用建议:利用索引列作为查询条件,避免了全盘扫描,节省了资源。
示例3:索引列的模糊查询
EXPLAIN PLAN FOR
SELECT * FROM emp WHERE EMPNO LIKE '%9';
SELECT * FROM TABLE(dbms_xplan.display);
使用建议:避免在索引列上使用模糊查询,这样会使索引失效。
示例4:索引列上面的计算
EXPLAIN PLAN FOR
SELECT * FROM emp WHERE empno + 1 = 7370;
SELECT * FROM TABLE(dbms_xplan.display);
如上图所示:该SQL语句在执行时并没有走索引
使用建议:避免在索引列上做计算,因为在索引列上做计算会使索引失效
但我们可以这样写:
EXPLAIN PLAN FOR
SELECT * FROM emp WHERE empno = 7370 - 1;
SELECT * FROM TABLE(dbms_xplan.display);
示例5:索引上的函数
EXPLAIN PLAN FOR
SELECT * FROM emp WHERE FLOOR(empno) = 7369;
SELECT * FROM TABLE(dbms_xplan.display);
使用建议:避免在索引列上使用函数
执行计划中的检索方式
SQL语句在执行时,访问表的方式。
-
TABLE ACCESS FULL(全表扫描)
Oracle会读取表中所有的行,并检查每一行是否满足SQL语句中的 Where 限制条件; 全表扫描时可以使用多块读(即一次I/O读取多块数据块)操作,提升吞吐量; 使用建议:数据量太大的表不建议使用全表扫描,除非本身需要取出的数据较多,占到表数据总量的 5% ~ 10% 或以上
-
TABLE ACCESS BY ROWID(通过ROWID的表存取)
行的ROWID指出了该行所在的数据文件、数据块以及行在该块中的位置,所以通过ROWID可以快速定位到目标数据上,这也是Oracle中存取单行数据最快的方法;
-
TABLE ACCESS BY INDEX SCAN(索引扫描)
在索引块中,既存储每个索引的键值,也存储具有该键值的行的ROWID。 所以索引扫描其实分为两步: 1:扫描索引得到对应的ROWID 2:通过ROWID定位到具体的行读取数据
-
TABLE ACCESS BY INDEX ROWID
该操作是数据库为了从索引中获取一些rowid,然后拿着这些rowid去块中读取数据集
索引扫描
-
INDEX UNIQUE SCAN(索引唯一扫描)
针对唯一性索引(UNIQUE INDEX)的扫描,每次至多只返回一条记录; 表中某字段存在 UNIQUE、PRIMARY KEY 约束时,Oracle常实现唯一性扫描;
-
INDEX RANGE SCAN(索引范围扫描)
使用一个索引存取多行数据; 发生索引范围扫描的三种情况: 在唯一索引列(unique索引)上使用了范围操作符(如:> < <> >= <= between) 在组合索引上,只使用部分列进行查询(查询时必须包含前导列,否则会走全表扫描) 对非唯一索引列(非unique)上进行的任何查询
-
INDEX FULL SCAN(索引全扫描)
进行全索引扫描时,查询出的数据都必须从索引中可以直接得到
-
INDEX FAST FULL SCAN(索引快速扫描)
扫描索引中的所有的数据块,与 INDEX FULL SCAN 类似,但是一个显著的区别是它不对查询出的数据进行排序(即数据不是以排序顺序被返回
-
INDEX SKIP SCAN(索引跳跃扫描)
Oracle 9i后提供,有时候复合索引的前导列(索引包含的第一列)没有在查询语句中出现, oralce也会使用该复合索引,这时候就使用的INDEX SKIP SCAN;什么时候会触发INDEX SKIP SCAN 呢? 前提条件:表有一个复合索引,且在查询时有除了前导列(索引中第一列)外的其他列作为条件,并且优化器模式为CBO时当Oracle发现前导列的唯一值个数很少时,会将每个唯一值都作为常规扫描的入口,在此基础上做一次查找,最后合并这些查询; 例如: 假设表emp有ename(雇员名称)、job(职位名)、sex(性别)三个字段,并且建立了如 create index idx_emp on emp (sex, ename, job) 的复合索引;因为性别只有 '男' 和 '女' 两个值,所以为了提高索引的利用率,Oracle可将这个复合索引拆成('男', ename, job),('女', ename, job) 这两个复合索引;当查询 select * from emp where job= 'Programmer' 时,该查询发出后:Oracle先进入sex为'男'的入口,这时候使用到了 ('男', ename,job) 这条复合索引,查找 job = 'Programmer' 的条目;再进入sex为'女'的入口,这时候使用到了('女', ename, job) 这条复合索引,查找 job = 'Programmer' 的条目; 最后合并查询到的来自两个入口的结果集。
表连接的方式:
-
SORT MERGE JOIN(排序-合并连接)
假设查询:select a.name,b.name from a join b on a.id = b.id; 首先拿到a.name,按照关联列也就是a.id对其进行排序 其次拿到b.name,将符合条件的数据按照a.id对其进行排序 最后将俩组数据进行合并操作 使用建议:因为排序是非常耗费资源的,所以尽量在两张表关联前就对其建立索引让其预先排好序,这样可以大幅度提升连接速度
-
NESTED LOOPS(嵌套循环)
JOIN 关键字用于将两张表作连接,一次只能连接两张表,JOIN 操作的各步骤一般是串行的; 表之间的连接顺序对于查询效率有很大的影响,对首先存取的表(驱动表)先应用某些限制条件(Where过滤条件)以得到一个较小的表,可以使得连接效率提高。
-
HASH JOIN(哈希连接)
哈希连接只适用于等值连接(即连接条件为 = ) HASH JOIN对两个表做连接时并不一定是都进行全表扫描,其并不限制表访问方式; 内部连接过程简述: a) 取出 表a(驱动表,在HASH JOIN中又称为Build Table) 的数据集,然后将其构建成内存中的一个 Hash Table(Hash函数的Hash KEY就是连接操作关联列),创建Hash位图(bitmap) b) 取出 表b(匹配表)的数据集,对其中的每一条数据的连接操作关联列使用相同的Hash函数并找到对应的 a) 里的数据在 Hash Table 中的位置,在该位置上检查能否找到匹配的数据
-
CARTESIAN PRODUCT(笛卡尔积)
不多解释,直接避免这种操作;