看不懂执行计划还谈什么SQL优化

执行计划

也叫查询计划，它是数据库执行SQL语句语句的具体步骤和过程。

SQL查询语句的执行计划主要包括：

1. 访问表的方式：数据库通过索引或者全盘扫描等方式访问表中的数据。
2. 多表连接的方式：数据库使用什么方法实现表和表之间的连接，包括多个表的先后访问顺序。
3. 分组聚合及排序等操作的实现。

我们在执行每一条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语句在执行时，访问表的方式。

1. TABLE ACCESS FULL（全表扫描）

   Oracle会读取表中所有的行，并检查每一行是否满足SQL语句中的 Where 限制条件；
   全表扫描时可以使用多块读（即一次I/O读取多块数据块）操作，提升吞吐量；
   使用建议：数据量太大的表不建议使用全表扫描，除非本身需要取出的数据较多，占到表数据总量的 5% ~ 10% 或以上
   

2. TABLE ACCESS BY ROWID（通过ROWID的表存取）

   行的ROWID指出了该行所在的数据文件、数据块以及行在该块中的位置，所以通过ROWID可以快速定位到目标数据上，这也是Oracle中存取单行数据最快的方法；
   

3. TABLE ACCESS BY INDEX SCAN（索引扫描）

   在索引块中，既存储每个索引的键值，也存储具有该键值的行的ROWID。
   所以索引扫描其实分为两步：
   	1：扫描索引得到对应的ROWID
   	2：通过ROWID定位到具体的行读取数据
   

4. TABLE ACCESS BY INDEX ROWID

   该操作是数据库为了从索引中获取一些rowid，然后拿着这些rowid去块中读取数据集
   

索引扫描

1. INDEX UNIQUE SCAN（索引唯一扫描）

   针对唯一性索引（UNIQUE INDEX）的扫描，每次至多只返回一条记录；
   表中某字段存在 UNIQUE、PRIMARY KEY 约束时，Oracle常实现唯一性扫描；
   

2. INDEX RANGE SCAN（索引范围扫描）

   使用一个索引存取多行数据；
   发生索引范围扫描的三种情况：
   在唯一索引列(unique索引)上使用了范围操作符（如：> < <> >= <= between）
   在组合索引上，只使用部分列进行查询（查询时必须包含前导列，否则会走全表扫描）
   对非唯一索引列(非unique)上进行的任何查询 
   

3. INDEX FULL SCAN（索引全扫描）

   进行全索引扫描时，查询出的数据都必须从索引中可以直接得到
   

4. INDEX FAST FULL SCAN（索引快速扫描）

   扫描索引中的所有的数据块，与 INDEX FULL SCAN 类似，但是一个显著的区别是它不对查询出的数据进行排序（即数据不是以排序顺序被返回
   

5. 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' 的条目；
    最后合并查询到的来自两个入口的结果集。
   

表连接的方式：

1. 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对其进行排序
    最后将俩组数据进行合并操作
   使用建议：因为排序是非常耗费资源的，所以尽量在两张表关联前就对其建立索引让其预先排好序，这样可以大幅度提升连接速度
   

2. NESTED LOOPS（嵌套循环）

   JOIN 关键字用于将两张表作连接，一次只能连接两张表，JOIN 操作的各步骤一般是串行的;
   表之间的连接顺序对于查询效率有很大的影响，对首先存取的表（驱动表）先应用某些限制条件（Where过滤条件）以得到一个较小的表，可以使得连接效率提高。
   

3. HASH JOIN（哈希连接）

   哈希连接只适用于等值连接（即连接条件为 = ）
    HASH JOIN对两个表做连接时并不一定是都进行全表扫描，其并不限制表访问方式；
    内部连接过程简述：
    a) 取出 表a（驱动表，在HASH JOIN中又称为Build Table） 的数据集，然后将其构建成内存中的一个 Hash Table（Hash函数的Hash KEY就是连接操作关联列），创建Hash位图（bitmap）
    b) 取出 表b（匹配表）的数据集，对其中的每一条数据的连接操作关联列使用相同的Hash函数并找到对应的 a) 里的数据在 Hash Table 中的位置，在该位置上检查能否找到匹配的数据
   

4. CARTESIAN PRODUCT（笛卡尔积）

   不多解释，直接避免这种操作；