执行计划(更新中)

执行计划

一. Oracle

1.1 什么是执行计划?

执行计划是一条查询语句在Oracle中的执行过程或访问路径的描述。

执行计划描述了SQL引擎为执行SQL语句进行的操作;分析SQL语句相关的性能问题或仅仅质疑查询优化器的决定时,必须知道执行计划;所以执行计划常用于sql调优。

相关概念:

  • Rowid:r伪列,不是用户定义,而是系统加的。 每个表都有一个rowid,可以像使用其它列那样使用它,但是不能删除改列,也不能对该列的值进行修改、插入。一旦一行数据插入数据库,则rowid在该行的生命周期内是唯一的,即即使该行产生行迁移,行的rowid也不会改变。

  • Recursive SQL:有时为了执行用户发出的一个sql语句,Oracle必须执行一些额外的语句称之为’’recursive calls’’或’’recursive SQL statements’’。

    如当一个DDL语句发出后,ORACLE总是隐含的发出一些recursive SQL语句,来修改数据字典信息,以便用户可以成功的执行该DDL语句。

    当需要的数据字典信息没有在共享内存中时,经常会发生Recursive calls,这些Recursive calls会将数据字典信息从硬盘读入内存中。

    用户不比关心这些recursive SQL语句的执行情况,在需要的时候,ORACLE会自动的在内部执行这些语句。DML语句与SELECT都可能引起recursive SQL,简单的说,我们可以将触发器视为recursive SQL。

  • Row Source(行源):在查询中,由上一操作返回的符合条件的行的集合,可以是表的全部行数据的集合,也可以是表的部分行数据的集合,也可以为对上2个row source进行连接操作(如join连接)后得到的行数据集合。

  • Predicate(谓词):一个查询中的WHERE限制条件。

  • Driving Table(驱动表):又称为外层表(OUTER TABLE)。用于嵌套与HASH连接中。

    如果该row source返回较多的行数据,则对所有的后续操作有负面影响。

    一般说来,是应用查询的限制条件后,返回较少行源的表作为驱动表,所以如果一个大表在WHERE条件有有限制条件(如等值限 制),则该大表作为驱动表也是合适的,正确说法应该为应用查询的限制条件后,返回较少行源的表作为驱动表。

  • Probed Table(被探查表):又称为内层表(INNER TABLE)。在我们从驱动表中得到具体一行的数据后,在该表中寻找符合连接条件的行。所以该表应当为大表(实际上应该为返回较大row source的表)且相应的列上应该有索引。

  • 组合索引(concatenated index):由多个列构成的索引。

    如create index idx_emp on emp(col1, col2, col3, ……),则我们称idx_emp索引为组合索引。

    在组合索引中有一个重要的概念:引导列(leading column),在上面的例子中,col1列为引导列。

    当我们进行查询时可以使用“where col1 = ? ”,也可以使用“where col1 = ? and col2 = ?”,这样的限制条件都会使用索引,但是“where col2 = ? ”查询就不会使用该索引。

    所以限制条件中包含先导列时,该限制条件才会使用该组合索引。

  • 可选择性(selectivity):比较一下列中唯一键的数量和表中的行数,就可以判断该列的可选择性。 如果该列的“唯一键的数量/表中的行数”的比值越接近1,则该列的可选择性越高,该列就越适合创建索引,同样索引的可选择性也越高。在可选择性高的列上进 行查询时,返回的数据就较少,比较适合使用索引查询。

1.2 如何查看执行计划?

oracle要使用执行计划一般在sqlplus执行sql:

1
explain plan for select 1 from t

不过如果是使用PLSQL的话,那就可以使用PLSQL提供的查询执行计划了,也就是按F5打开PLSQL工具 -> 首选项 -> 窗口类型 -> 计划窗口 ,在这里加入执行计划需要的参数:

执行结果:

参数含义:

  • 基数(Rows):Oracle估计的当前步骤的返回结果集行数
  • 字节(Bytes):执行SQL对应步骤返回的字节数
  • 耗费(COST):CPU耗费:Oracle估计的该步骤的执行耗费和CPU耗费
  • 时间(Time):Oracle估计的执行sql对于步骤需要的时间

1.3 查看真实的执行计划

sqlplus窗口执行:

  • step1:set statistics_level
1
alter session set statistics_level=ALL;
  • step2:执行业务sql
1
select /*+ monitor */ * from ... where ....;
  • step3:为了样式,设置linesize
1
set linesize 200 pagesize 300;
  • step4:查询真实执行计划
1
select * from table(dbms_xplan.display_cursor(null, null, 'iostats last'));

sqlplus一般要数据库管理员才可以使用,如果你不是dba,只能使用plsql developer的话,只能用下面的方法。

使用存储过程,SQL:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
declare
b1 date;
begin
execute immediate 'alter session set statistics_level=ALL';
b1 := sysdate - 1;
for test in (
/*业务SQL(sql后面不需要加";")*/
select * from t) loop
null;
end loop;
for x in (select p.plan_table_output
from table(dbms_xplan.display_cursor(null,
null,
'advanced -bytes -PROJECTION allstats last')) p) loop
dbms_output.put_line(x.plan_table_output);
end loop;
rollback;
end;
/

两种窗口:

  • 1、SQL窗口的,执行SQL后只能去output查看;
  • 2、command window的,需要先设置set serveroutput on size unlimited,然后再执行存储过程

output或者命令窗口查看的真实执行计划和统计信息:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
SQL_ID  abk3ghv9u1tvb, child number 0
-------------------------------------
SELECT /*+ monitor */ * FROM APPR_HANDLE_INFO

Plan hash value: 885170757

------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 210 (100)| | 72059 |00:00:00.06 | 2460 |
| 1 | TABLE ACCESS FULL| APPR_HANDLE_INFO | 1 | 32752 | 210 (1)| 00:00:03 | 72059 |00:00:00.06 | 2460 |
------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$1 / APPR_HANDLE_INFO@SEL$1

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "APPR_HANDLE_INFO"@"SEL$1")
END_OUTLINE_DATA
*/

关键信息解释:

  • Starts:该SQL执行的次数
  • E-Rows:为执行计划预计的行数
  • A-Rows:实际返回的行数,E-Rows和A-Rows作比较,就可以看出具体那一步执行计划出问题了
  • A-Time:每一步实际执行的时间,可以看出耗时的SQL
  • Buffers:每一步实际执行的逻辑读或一致性读

1.4 分析执行计划

1.4.1 查看explain

找一条比较复杂的SQL,执行:

set statistics_level=ALL方式:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
SQL_ID  4qfq3t2ukm0y1, child number 0
-------------------------------------
SELECT /*+ monitor*/ A.USER_CODE, A.FULL_NAME, A.USER_PWD, C.UNIT_CODE,
C.UNIT_NAME FROM BASE_USER A LEFT JOIN (SELECT UR.USER_CODE,
UR.UNIT_CODE FROM APPR_USER_ROLE UR WHERE UR.USER_ROLE < 10) B ON
A.USER_CODE = B.USER_CODE LEFT JOIN LZCITY_APPROVE_UNIT_INFO C ON
B.UNIT_CODE = C.UNIT_CODE WHERE C.UNIT_CODE ='15803'

Plan hash value: 3288287052

------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 3 (100)| | 16 |00:00:00.01 | 38 |
| 1 | NESTED LOOPS | | 1 | 1 | 3 (0)| 00:00:01 | 16 |00:00:00.01 | 38 |
| 2 | NESTED LOOPS | | 1 | 1 | 3 (0)| 00:00:01 | 16 |00:00:00.01 | 22 |
| 3 | NESTED LOOPS | | 1 | 1 | 2 (0)| 00:00:01 | 16 |00:00:00.01 | 5 |
| 4 | TABLE ACCESS BY INDEX ROWID| LZCITY_APPROVE_UNIT_INFO | 1 | 1 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 5 | INDEX UNIQUE SCAN | PK_LZCITY_APPROVE_UNIT_INFO | 1 | 1 | 0 (0)| | 1 |00:00:00.01 | 2 |
|* 6 | INDEX RANGE SCAN | PK_APPR_USER_ROLE | 1 | 1 | 1 (0)| 00:00:01 | 16 |00:00:00.01 | 2 |
|* 7 | INDEX UNIQUE SCAN | PK_BASE_USER | 16 | 1 | 0 (0)| | 16 |00:00:00.01 | 17 |
| 8 | TABLE ACCESS BY INDEX ROWID | BASE_USER | 16 | 1 | 1 (0)| 00:00:01 | 16 |00:00:00.01 | 16 |
------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

1 - SEL$E3445A69
4 - SEL$E3445A69 / C@SEL$4
5 - SEL$E3445A69 / C@SEL$4
6 - SEL$E3445A69 / UR@SEL$2
7 - SEL$E3445A69 / A@SEL$3
8 - SEL$E3445A69 / A@SEL$3

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$E3445A69")
MERGE(@"SEL$2")
OUTLINE(@"SEL$A2E96217")
OUTER_JOIN_TO_INNER(@"SEL$E9F4A6F9" "B"@"SEL$1")
OUTER_JOIN_TO_INNER(@"SEL$E9F4A6F9" "C"@"SEL$4")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$E9F4A6F9")
MERGE(@"SEL$80808B20")
OUTLINE(@"SEL$6")
OUTLINE(@"SEL$80808B20")
MERGE(@"SEL$4")
MERGE(@"SEL$F1D6E378")
OUTLINE(@"SEL$5")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$F1D6E378")
MERGE(@"SEL$1")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$1")
INDEX_RS_ASC(@"SEL$E3445A69" "C"@"SEL$4" ("LZCITY_APPROVE_UNIT_INFO"."UNIT_CODE"))
INDEX(@"SEL$E3445A69" "UR"@"SEL$2" ("APPR_USER_ROLE"."UNIT_CODE" "APPR_USER_ROLE"."USER_CODE" "APPR_USER_ROLE"."AREA_SEQ"
"APPR_USER_ROLE"."USER_ROLE"))
INDEX(@"SEL$E3445A69" "A"@"SEL$3" ("BASE_USER"."USER_CODE"))
LEADING(@"SEL$E3445A69" "C"@"SEL$4" "UR"@"SEL$2" "A"@"SEL$3")
USE_NL(@"SEL$E3445A69" "UR"@"SEL$2")
USE_NL(@"SEL$E3445A69" "A"@"SEL$3")
NLJ_BATCHING(@"SEL$E3445A69" "A"@"SEL$3")
END_OUTLINE_DATA
*/

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("C"."UNIT_CODE"='15803')
6 - access("UR"."UNIT_CODE"='15803' AND "UR"."USER_ROLE"<10)
filter("UR"."USER_ROLE"<10)
7 - access("A"."USER_CODE"="UR"."USER_CODE")

1.4.2 explain执行顺序

不管是用F5方式还是set statistics_level=ALL方式,都有Operation参数,Operation表示sql执行过程,查看怎么执行的,有两个规则:

  • 根据Operation缩进判断,缩进最多的最先执行;
  • Operation缩进相同时,最上面的是最先执行的;

如图执行计划,根据规则,可以得出执行顺序:INDEX UNIQUE SCAN->TABLE ACCESS BY INDEX ROWID->INDEX RANGE SCAN ->NESTED LOOPS ->INDEX UNIQUE SCAN->NESTED LOOPS ->TABLE ACCESS BY INDEX ROWID->NESTED LOOPS-> SELECT STATEMENT 。

1.4.3 访问数据方式

Oracle访问表中数据的方法有两种:

  • 访问表数据:直接表中访问数据。

  • 索引扫描:先访问索引,如果索引数据不符合目标SQL,就回表,符合就不回表,直接访问索引就可以。

    先通过index查找到数据对应的rowid值(对于非唯一索引可能返回多个rowid值),然后根据rowid直接从表中得到具体的数据。该行对应的数据块是通过一次i/o得到的,在此情况下该次i/o只会读取一个数据库块。

Oracle直接访问表中数据的方法又分为两种:

  • 全表扫描(TABLE ACCESS FULL)
  • ROWID扫描(TABLE ACCESS BY ROWID)

访问索引的种类更多(TABLE ACCESS BY INDEX SCAN):

  • 索引唯一扫描(INDEX UNIQUE SCAN)
  • 索引范围扫描(INDEX RANGE SCAN)
  • 索引全扫描(INDEX FULL SCAN)
  • 索引快速全扫描(INDEX FAST FULL SCAN)
  • 索引跳跃式扫描(INDEX SKIP SCAN)

索引扫描可以由2步组成:

  1. 扫描索引得到对应的rowid值。
  2. 通过找到的rowid从表中读出具体的数据。

每步都是单独的一次I/O,但是对于索引,由于经常使用,绝大多数都已经CACHE到内存中,所以第1步的 I/O经常是逻辑I/O,即数据可以从内存中得到。

但是对于第2步来说,如果表比较大,则其数据不可能全在内存中,所以其I/O很有可能是物理I/O,这是一个机械操作,相对逻辑I/O来说,是极其费时间的。

所以如果多大表进行索引扫描,取出的数据如果大于总量的5% —— 10%,使用索引扫描会效率下降很多。如下列所示:

1
2
3
4
5
6
7
explain plan for select empno, ename from emp where empno=10
  Query Plan

------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED]
INDEX UNIQUE SCAN EMP_I1

如果查询的数据能全在索引中找到,就可以避免进行第2步操作,避免了不必要的I/O,此时即使通过索引扫描取出的数据比较多,效率还是很高的:

1
2
3
4
5
6
explain plan for select empno from emp where empno=10;-- 只查询empno列值

Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
INDEX UNIQUE SCAN EMP_I1

如果sql语句中对索引列进行排序,因为索引已经预先排序好了,所以在执行计划中不需要再对索引列进行排序:

1
2
3
4
5
6
7
explain plan for select empno, ename from emp where empno > 7876 order by empno;

Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT[CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED]
INDEX RANGE SCAN EMP_I1 [ANALYZED]
(1)全表扫描(TABLE ACCESS FULL)
  • 全表扫描时从第一个区(EXTENT)的第一个块(BLOCK)开始扫描,一直扫描的到表的高水位线(High Water Mark),这个范围内的数据块都会扫描到。
  • 全表扫描是采用多数据块一起扫的,并不是一个个数据库扫的,也不是只读取一个数据块,减少了I/O总次数,提高了系统的吞吐量。
  • 全表扫描慢是针对数据量很多的情况,数据量少的话,全表扫描并不慢的,不过随着数据量越多,高水位线也就越高,也就是说需要扫描的数据库越多,自然扫描所需要的IO越多,时间也越多。

注意:数据量越多,全表扫描所需要的时间就越多,然后直接删了表数据呢?查询速度会变快?其实并不会的,因为即使我们删了数据,高位水线并不会改变,也就是同样需要扫描那么多数据块。

使用场景:在较大的表上不建议使用全表扫描,除非取出数据的比较多,超过总量的5%到10%,以及想使用并行查询功能时。

1
2
3
4
5
SQL> explain plan for select * from dual;
Query Plan
-----------------------------------------
SELECT STATEMENT[CHOOSE] Cost=
TABLE ACCESS FULL DUAL
(2)ROWID扫描(TABLE ACCESS BY ROWID)

ROWID也就是表数据行所在的物理存储地址,所谓的ROWID扫描是通过ROWID所在的数据行记录去定位。ROWID是一个伪列,数据库里并没有这个列,它是数据库查询过程中获取的一个物理地址,用于表示数据对应的行数。 Oracle存取单行数据的最快方法。

这种存取方法不会用到多块读操作,一次I/O只能读取一个数据块。我们会经常在执行计划中看到该存取方法,如通过索引查询数据。

1
2
3
4
5
6
explain plan for select * from dept where rowid = ''AAAAyGAADAAAAATAAF''

Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
TABLE ACCESS BY ROWID DEPT [ANALYZED]

可以用sql查询rowid:

1
select t.*,rowid from 表格

随意获取一个ROWID序列:AAAWSJAAFAAAWwUAAA

  • 前6位表示对象编号(Data Object number)
  • 其后3位文件编号(Relative file number)
  • 再后6位表示块编号(Block number)
  • 最后3位表示行编号(Row number)

ROWID编码方法是:A ~ Z表示0到25;a ~ z表示26到51;0~9表示52到61;+表示62;/表示63;刚好64个字符。

随意找张表查一下文件编号、区编号、行编号,查询后会返回rowid的一系列物理地址和文件编号(rowid_relative_fno(rowid))、块编号(rowid_block_number(rowid))、行编号(rowid_row_number(rowid)):

1
2
3
4
5
6
select t.seq,
rowid,
dbms_rowid.rowid_relative_fno(rowid),
dbms_rowid.rowid_block_number(rowid),
dbms_rowid.rowid_row_number(rowid)
from t_info t

SQL查询一下表格名称为TABLE的对象编码

1
select owner,object_id,data_object_id,status from dba_objects where 

相对文件id和绝对文件编码:相对文件id是指相对于表空间,在表空间唯一;绝对文件编码是指相当于全局数据库而言的,全局唯一。

查询一下相对文件id和绝对文件编码:

1
select file_name,file_id,relative_fno from dba_data_files;
(3)索引唯一扫描(INDEX UNIQUE SCAN)
  • 索引唯一性扫描(INDEX UNIQUE SCAN)是针对唯一性索引(UNIQUE INDEX)来说的,也就是建立唯一性索引才能索引唯一性扫描。
  • 唯一性扫描,其结果集只会返回一条记录。
1
2
3
4
5
6
7
explain plan for select empno,ename from emp where empno=10

Query Plan
------------------------------------
SELECT STATEMENT [CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED]
INDEX UNIQUE SCAN EMP_I1
(4)索引范围扫描(INDEX RANGE SCAN)
  • 当扫描的对象是非唯一性索引的情况,where谓词条件为Between、=、<、>等等的情况就是索引范围扫描,注意,可以是等值查询,也可以是范围查询。
  • 适用于所有类型的B树索引,一般不包括唯一性索引,因为唯一性索引走索引唯一性扫描。

使用index rang scan的3种情况:

  • 在唯一索引列上使用了range操作符(> < <> >= <= between)
  • 在组合索引上,只使用部分列进行查询,导致查询出多行
  • 对非唯一索引列上进行的任何查询
1
2
3
4
5
6
7
explain plan for select empno,ename from emp where empno > 7876 order by empno;

Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT[CHOOSE] Cost=1
TABLE ACCESS BY ROWID EMP [ANALYZED]
INDEX RANGE SCAN EMP_I1 [ANALYZED]

如果where条件里有一个索引键值列没限定为非空的,那就可以走索引范围扫描,如果改索引列是非空的,那就走索引全扫描。

前面说了,同样的SQL建的索引不同,就可能是走索引唯一性扫描,也有可能走索引范围扫描。在同等的条件下,索引范围扫描所需要的逻辑读和索引唯一性扫描对比,逻辑读如何?索引范围扫描可能返回多条记录,所以优化器为了确认,肯定会多扫描,所以在同等条件,索引范围扫描所需要的逻辑读至少会比相应的唯一性扫描的逻辑读多1。

(5)索引全扫描(INDEX FULL SCAN)
  • 适用于所有类型的B树索引(包括唯一性索引和非唯一性索引)。
  • 查询出的数据都必须从索引中可以直接得到。
  • 索引全扫描是指扫描目标索引所有叶子块的索引行,但不意思着需要扫描所有的分支块,索引全扫描时只需要访问必要的分支块,然后定位到位于改索引最左边的叶子块的第一行索引行,就可以利用改索引叶子块之间的双向指针链表,从左往右依次顺序扫描所有的叶子块的索引行。
1
2
3
4
5
6
7
8
9
10
11
An Index full scan will not perform single block i/o''s and so it may prove to be inefficient. 
e.g.

Index BE_IX is a concatenated index on big_emp(empno, ename)

explain plan for select empno, ename from big_emp order by empno,ename;

Query Plan
--------------------------------------------------------------------------------
SELECT STATEMENT[CHOOSE] Cost=26
INDEX FULL SCAN BE_IX [ANALYZED]
(6)索引快速全扫描(INDEX FAST FULL SCAN)
  • 索引快速全扫描和索引全扫描很类似,也适用于所有类型的B树索引(包括唯一性索引和非唯一性索引)。
  • 和索引全扫描类似,也是扫描所有叶子块的索引行,这些都是索引快速全扫描和索引全扫描的相同点。
  • 它不对查询出的数据进行排序,即数据不是以排序顺序被返回。
  • 可以使用多块读功能,也可以使用并行读入,以便获得最大吞吐量与缩短执行时间。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# BE_IX索引是一个多列索引: big_emp(empno, ename)

explain plan for select empno,ename from big_emp;

Query Plan
------------------------------------------
SELECT STATEMENT[CHOOSE] Cost=1
INDEX FAST FULL SCAN BE_IX [ANALYZED]

# 只选择多列索引的第2列:
explain plan for select ename from big_emp;

Query Plan
------------------------------------------
SELECT STATEMENT[CHOOSE] Cost=1
INDEX FAST FULL SCAN BE_IX [ANALYZED]

索引快速全扫描和索引全扫描区别:

  • 索引快速全扫描只适应于CBO(基于成本的优化器)
  • 索引快速全扫描可以使用多块读,也可以并行执行
  • 索引全扫描会按照叶子块排序返回,而索引快速全扫描则是按照索引段内存储块顺序返回
  • 索引快速全扫描的执行结果不一定是有序的,而索引全扫描的执行结果是有序的,因为索引快速全扫描是根据索引行在磁盘的物理存储顺序来扫描的,不是根据索引行的逻辑顺序来扫描的
(7)索引跳跃式扫描(INDEX SKIP SCAN)

索引跳跃式扫描(INDEX SKIP SCAN)适用于所有类型的复合B树索引(包括唯一性索引和非唯一性索引),索引跳跃式扫描可以使那些在where条件中没有目标索引的前导列指定查询条件但是有索引的非前导列指定查询条件的目标SQL依然可以使用跳跃索引。

如图执行计划就有INDEX RANGE SCAN、 INDEX UNIQUE SCAN 等等:

1.4.4 表连接方式

如图,执行计划中有如下NESTED LOOPS等,是Oracle中表连接的方法:

  • Join是一种试图将两个表结合在一起的谓词,一次只能连接2个表,表连接也可以被称为表关联。
  • 使用row source来代替表的描述更准确,将参与连接的2个row source分别称为row source1和row source 2。
  • Join过程的各个步骤经常是串行操作,即使相关的row source可以被并行访问,即可以并行的读取做join连接的两个row source的数据,但是在将表中符合限制条件的数据读入到内存形成row source后,join的其它步骤一般是串行的。

row source之间的连接顺序对于查询的效率有非常大的影响。通过首先存取特定的表,即将该表作为驱动表,这样可以先应用某些限制条件,从而得到一个较小的row source,使连接的效率较高,这也就是我们常说的要先执行限制条件的原因。一般是在将表读入内存时,应用where子句中对该表的限制条件。

表连接方式:

  • 排序合并连接(sort merge join) :先将关联表的关联列各自做排序,然后从各自的排序表中抽取数据,到另一个排序表中做匹配。
    • 对于非等值连接,这种连接方式的效率是比较高的。
    • 如果在关联的列上都有索引,效果更好。
    • 对于将2个较大的row source做连接,该连接方法比NL连接要好一些。
    • 但是如果sort merge返回的row source过大,则又会导致使用过多的rowid在表中查询数据时,数据库性能下降,因为过多的I/O。
  • 嵌套循环连接(Nested loop join):Nested loops 工作方式是循环从一张表中读取数据(驱动表outer table),然后访问另一张表(被查找表 inner table,通常有索引)。驱动表中的每一行与inner表中的相应记录JOIN。类似一个嵌套的循环。对于被连接的数据子集较小的情况,nested loop连接是个较好的选择
    • 如果driving row source(外部表)比较小,并且在inner row source(内部表)上有唯一索引,或有高选择性非唯一索引时,使用这种方法可以得到较好的效率。
    • NESTED LOOPS有其它连接方法没有的的一个优点是:可以先返回已经连接的行,而不必等待所有的连接操作处理完才返回数据,这可以实现快速的响应时间。
  • 哈希连接(Hash join):散列连接是CBO 做大数据集连接时的常用方式,优化器使用两个表中较小的表(或数据源)利用连接键在内存中建立散列表,然后扫描较大的表并探测散列表,找出与散列表匹配的行。
    • 这种方法是在oracle7后来引入的,使用了比较先进的连接理论,一般来说,其效率应该好于其它2种连接,但是这种连接只能用在CBO优化器中,而且需要设置合适的hash_area_size参数,才能取得较好的性能。
    • 在2个较大的row source之间连接时会取得相对较好的效率,在一个row source较小时则能取得更好的效率。
    • 只能用于等值连接中
  • 笛卡尔连接(Cross join):如果两个表做表连接而没有连接条件,而会产生笛卡尔积,在实际工作中应该尽可能避免笛卡尔积。
(1)排序合并连接(sort merge join)

内部连接过程:

  1. 首先生成row source1需要的数据,然后对这些数据按照连接操作关联列(如A.col3)进行排序。
  2. 随后生成row source2需要的数据,然后对这些数据按照与sort source1对应的连接操作关联列(如B.col4)进行排序。
  3. 最后两边已排序的行被放在一起执行合并操作,即将2个row source按照连接条件连接起来。

如果row source已经在连接关联列上被排序,则该连接操作就不需要再进行sort操作,这样可以大大提高这种连接操作的连接速度,因为排序是个极其费资源的操作,特别是对于较大的表。

预先排序的row source包括已经被索引的列(如a.col3或b.col4上有索引)或row source已经在前面的步骤中被排序了。

尽管合并两个row source的过程是串行的,但是可以并行访问这两个row source(如并行读入数据,并行排序)。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
explain plan for
select /*+ ordered */ e.deptno, d.deptno
from emp e, dept d
where e.deptno = d.deptno
order by e.deptno, d.deptno;

Query Plan
-------------------------------------
SELECT STATEMENT [CHOOSE] Cost=17
MERGE JOIN
SORT JOIN
TABLE ACCESS FULL EMP [ANALYZED]
SORT JOIN
TABLE ACCESS FULL DEPT [ANALYZED]

排序是一个费时、费资源的操作,特别对于大表。基于这个原因,SMJ经常不是一个特别有效的连接方法,但是如果2个row source都已经预先排序,则这种连接方法的效率也是蛮高的。

(2)嵌套循环连接(Nested loop join)

这个连接方法有驱动表(外部表)的概念。其实,该连接过程就是一个2层嵌套循环,所以外层循环的次数越少越好,这也就是我们为什么将小表或返回较小row source的表作为驱动表(用于外层循环)的理论依据。

但是这个理论只是一般指导原则,因为遵循这个理论并不能总保证使语句产生的I/O次数最少。有时不遵守这个理论依据,反而会获得更好的效率。如果使用这种方法,决定使用哪个表作为驱动表很重要。有时如果驱动表选择不正确,将会导致语句的性能很差。

内部连接过程:

  • Row source1的Row 1 —— Probe ->Row source 2
  • Row source1的Row 2 —— Probe ->Row source 2
  • Row source1的Row 3 —— Probe ->Row source 2
  • ……
  • Row source1的Row n —— Probe ->Row source 2

从内部连接过程来看,需要用row source1中的每一行,去匹配row source2中的所有行,所以此时保持row source1尽可能的小与高效的访问row source2(一般通过索引实现)是影响这个连接效率的关键问题。

这只是理论指导原则,目的是使整个连接操作产生最少的物理I/O次数,而且如果遵守这个原则,一般也会使总的物理I/O数最少。但是如果不遵从这个指导原则,反而能用更少的物理I/O实现连接操作,那尽管违反指导原则吧!因为最少的物理 I/O次数才是我们应该遵从的真正的指导原则。

在NESTED LOOPS连接中,Oracle读取row source1中的每一行,然后在row sourc2中检查是否有匹配的行,所有被匹配的行都被放到结果集中,然后处理row source1中的下一行。这个过程一直继续,直到row source1中的所有行都被处理。这是从连接操作中可以得到第一个匹配行的最快的方法之一,这种类型的连接可以用在需要快速响应的语句中,以响应速度为主要目标。

如果driving row source(外部表)比较小,并且在inner row source(内部表)上有唯一索引,或有高选择性非唯一索引时,使用这种方法可以得到较好的效率。

NESTED LOOPS有其它连接方法没有的的一个优点是:可以先返回已经连接的行,而不必等待所有的连接操作处理完才返回数据,这可以实现快速的响应时间。

如果不使用并行操作,最好的驱动表是那些应用了where限制条件后,可以返回较少行数据的的表,所以大表也可能称为驱动表,关键看限制条件。

对于并行查询,我们经常选择大表作为驱动表,因为大表可以充分利用并行功能。当然,有时对查询使用并行操作并不一定会比查询不使用并行操作效率高,因为最后可能每个表只有很少的行符合限制条件,而且还要看你的硬件配置是否 可以支持并行(如是否有多个CPU,多个硬盘控制器),所以要具体问题具体对待。

1
2
3
4
5
6
7
8
9
10
11
explain plan for
select a.dname,b.sql
from dept a,emp b
where a.deptno = b.deptno;

Query Plan
-------------------------
SELECT STATEMENT [CHOOSE] Cost=5
NESTED LOOPS
TABLE ACCESS FULL DEPT [ANALYZED]
TABLE ACCESS FULL EMP [ANALYZED]
(3)哈希连接(Hash join)

这种连接是在oracle 7.3以后引入的,从理论上来说比NL与SMJ更高效,而且只用在CBO优化器中。

较小的row source被用来构建hash table与bitmap,第2个row source被用来被hansed,并与第一个row source生成的hash table进行匹配,以便进行进一步的连接。

内部连接过程:

  • 取出 row source 1(驱动表,在HASH JOIN中又称为Build Table) 的数据集,然后将其构建成内存中的一个 Hash Table(Hash函数的Hash KEY就是连接操作关联列),创建Hash位图(bitmap)
  • 取出 row source 2(匹配表)的数据集,对其中的每一条数据的连接操作关联列使用相同的Hash函数并找到对应的 a) 里的数据在 Hash Table 中的位置,在该位置上检查能否找到匹配的数据

Bitmap被用来作为一种比较快的查找方法,来检查在hash table中是否有匹配的行。特别的,当hash table比较大而不能全部容纳在内存中时,这种查找方法更为有用。

这种连接方法也有NL连接中所谓的驱动表的概念,被构建为hash table与bitmap的表为驱动表,当被构建的hash table与bitmap能被容纳在内存中时,这种连接方式的效率极高。

1
2
3
4
5
6
7
8
9
10
11
explain plan for
select /*+ use_hash(emp) */ empno
from emp, dept
where emp.deptno = dept.deptno;

Query Plan
----------------------------
SELECT STATEMENT[CHOOSE] Cost=3
HASH JOIN
TABLE ACCESS FULL DEPT
TABLE ACCESS FULL EMP

要使哈希连接有效,需要设置HASH_JOIN_ENABLED=TRUE,缺省情况下该参数为TRUE,另外,不要忘了还要设置 hash_area_size参数,以使哈希连接高效运行,因为哈希连接会在该参数指定大小的内存中运行,过小的参数会使哈希连接的性能比其他连接方式还要低。

(4)笛卡尔连接(Cross join)

当两个row source做连接,但是它们之间没有关联条件时,就会在两个row source中做笛卡儿乘积,这通常由编写代码疏漏造成(即程序员忘了写关联条件)。

笛卡尔乘积是一个表的每一行依次与另一个表中的所有行匹配。在特殊情况下我们可以使用笛卡儿乘积,如在星形连接中,除此之外,我们要尽量不使用笛卡儿乘积。

1
2
3
4
5
6
7
8
9
10
11
explain plan for
select emp.deptno,dept,deptno
from emp,dept

Query Plan
------------------------
SELECT STATEMENT [CHOOSE] Cost=5
MERGE JOIN CARTESIAN
TABLE ACCESS FULL DEPT
SORT JOIN
TABLE ACCESS FULL EMP

CARTESIAN关键字指出了在2个表之间做笛卡尔乘积。假如表emp有n行,dept表有m行,笛卡尔乘积的结果就是得到n * m行结果。

1.4.5 explain参数信息

执行计划关键信息介绍:

  • Starts:该SQL执行的次数
  • E-Rows:为执行计划预计的行数
  • Cost (%CPU):CPU cost在整个cost中占的百分比
  • A-Rows:实际返回的行数,E-Rows和A-Rows作比较,就可以看出具体那一步执行计划出问题了
  • A-Time:每一步实际执行的时间,可以看出耗时的SQL
  • Buffers:每一步实际执行的逻辑读或一致性读

二. MySQL

2.1 使用explain

1
2
3
4
5
6
mysql> explain select * from actor;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | actor | ALL | NULL | NULL | NULL | NULL | 2 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+

explain有两个变种:

  • explain extended:额外提供一些查询优化的信息,并且紧随使用 show warnings 命令可以得到优化后的查询语句。filtered列表示估算出将要和explain中前一个表进行连接的行数、
  • explain partitions:当查询基于分区表会显示查询将访问的分区。

2.2 返回列

(1)id

表示select的序列号,有几个select就有几个id,id顺序即select的出现顺序。

  • 简单查询。

  • 复杂查询:

    • 简单子查询:

      1
      2
      3
      4
      5
      6
      7
      mysql> explain select (select 1 from actor limit 1) from film;
      +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
      | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
      +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
      | 1 | PRIMARY | film | index | NULL | idx_name | 32 | NULL | 1 | Using index |
      | 2 | SUBQUERY | actor | index | NULL | PRIMARY | 4 | NULL | 2 | Using index |
      +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
    • 派生表(from语句中的子查询):

      1
      2
      3
      4
      5
      6
      7
      mysql> explain select id from (select id from film) as der;
      +----+-------------+------------+-------+---------------+----------+---------+------+------+-------------+
      | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
      +----+-------------+------------+-------+---------------+----------+---------+------+------+-------------+
      | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | NULL |
      | 2 | DERIVED | film | index | NULL | idx_name | 32 | NULL | 1 | Using index |
      +----+-------------+------------+-------+---------------+----------+---------+------+------+-------------+
    • union查询:

      1
      2
      3
      4
      5
      6
      7
      8
      mysql> explain select 1 union all select 1;
      +----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
      | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
      +----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
      | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
      | 2 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
      | NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
      +----+--------------+------------+------+---------------+------+---------+------+------+-----------------+

      union结果总是放在一个匿名临时表中,临时表不在SQL总出现,因此它的id是NULL。

(2)select_type

表示对应行是是简单还是复杂的查询,如果是复杂的查询,又是上述三种复杂查询中的哪一种。

  • simple:简单查询,查询不包含子查询和union。

    1
    2
    3
    4
    5
    6
    mysql> explain select * from film where id = 2;
    +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
    | 1 | SIMPLE | film | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
    +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
  • primary:复杂查询中最外层的 select。

  • subquery:包含在 select 中的子查询(不在 from 子句中)。

  • derived:包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中,也称为派生表(derived的英文含义)。

    1
    2
    3
    4
    5
    6
    7
    8
    mysql> explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;
    +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
    | 1 | PRIMARY | <derived3> | system | NULL | NULL | NULL | NULL | 1 | NULL |
    | 3 | DERIVED | film | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
    | 2 | SUBQUERY | actor | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
    +----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
  • union:在 union 中的第二个和随后的 select。

  • union result:从 union 临时表检索结果的 select

    1
    2
    3
    4
    5
    6
    7
    8
    mysql> explain select 1 union all select 1;
    +----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+--------------+------------+------+---------------+------+---------+------+------+-----------------+
    | 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
    | 2 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
    | NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
    +----+--------------+------------+------+---------------+------+---------+------+------+-----------------+

(3)table

表示 explain 的一行正在访问哪个表。

  • 当 from 子句中有子查询时,table列是 <derivenN> 格式,表示当前查询依赖 id=N 的查询,于是先执行 id=N 的查询。
  • 当有 union 时,UNION RESULT 的 table 列的值为 <union1,2> ,1和2表示参与 union 的 select 行id。

(4)type

这一列表示关联类型访问类型,即MySQL决定如何查找表中的行。

从最优到最差分别为:system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

  • NULL:mysql能够在优化阶段分解查询语句,在执行阶段用不着再访问表或索引。例如:在索引列中选取最小值,可以单独查找索引来完成,不需要在执行时访问表。

    1
    2
    3
    4
    5
    6
    mysql> explain select min(id) from film;
    +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
    | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
    +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
  • const / system:mysql能对查询的某部分进行优化并将其转化成一个常量(可以看show warnings 的结果)。用于 primary key 或 unique key 的所有列与常数比较时,所以表最多有一个匹配行,读取1次,速度比较快。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    mysql> explain extended select * from (select * from film where id = 1) tmp;
    +----+-------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+
    | 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
    | 2 | DERIVED | film | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
    +----+-------------+------------+--------+---------------+---------+---------+-------+------+----------+-------+

    mysql> show warnings;
    +-------+------+---------------------------------------------------------------+
    | Level | Code | Message |
    +-------+------+---------------------------------------------------------------+
    | Note | 1003 | /* select#1 */ select '1' AS `id`,'film1' AS `name` from dual |
    +-------+------+---------------------------------------------------------------+
  • eq_ref:primary key 或 unique key 索引的所有部分被连接使用 ,最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了,简单的 select 查询不会出现这种 type。

    1
    2
    3
    4
    5
    6
    7
    mysql> explain select * from film_actor left join film on film_actor.film_id = film.id;
    +----+-------------+------------+--------+---------------+-------------------+---------+-------------------------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+------------+--------+---------------+-------------------+---------+-------------------------+------+-------------+
    | 1 | SIMPLE | film_actor | index | NULL | idx_film_actor_id | 8 | NULL | 3 | Using index |
    | 1 | SIMPLE | film | eq_ref | PRIMARY | PRIMARY | 4 | test.film_actor.film_id | 1 | NULL |
    +----+-------------+------------+--------+---------------+-------------------+---------+-------------------------+------+-------------+
  • ref:相比 eq_ref ,不使用唯一索引,而是使用普通索引或者唯一性索引的部分前缀,索引要和某个值相比较,可能会找到多个符合条件的行。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    1. 简单 select 查询,name是普通索引(非唯一索引)
    mysql> explain select * from film where name = "film1";
    +----+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+
    | 1 | SIMPLE | film | ref | idx_name | idx_name | 33 | const | 1 | Using where; Using index |
    +----+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+

    2.关联表查询,idx_film_actor_id是film_id和actor_id的联合索引,这里使用到了film_actor的左边前缀film_id部分。
    mysql> explain select * from film left join film_actor on film.id = film_actor.film_id;
    +----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+-------------+
    | 1 | SIMPLE | film | index | NULL | idx_name | 33 | NULL | 3 | Using index |
    | 1 | SIMPLE | film_actor | ref | idx_film_actor_id | idx_film_actor_id | 4 | test.film.id | 1 | Using index |
    +----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+-------------+
  • ref_or_null:类似ref,但是可以搜索值为NULL的行。

    1
    2
    3
    4
    5
    6
    mysql> explain select * from film where name = "film1" or name is null;
    +----+-------------+-------+-------------+---------------+----------+---------+-------+------+--------------------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-------+-------------+---------------+----------+---------+-------+------+--------------------------+
    | 1 | SIMPLE | film | ref_or_null | idx_name | idx_name | 33 | const | 2 | Using where; Using index |
    +----+-------------+-------+-------------+---------------+----------+---------+-------+------+--------------------------+
  • index_merge:表示使用了索引合并的优化方法。 例如下表:id是主键,tenant_id是普通索引。or 的时候没有用 primary key,而是使用了 primary key(id) 和 tenant_id 索引

    1
    2
    3
    4
    5
    6
    mysql> explain select * from role where id = 11011 or tenant_id = 8888;
    +----+-------------+-------+-------------+-----------------------+-----------------------+---------+------+------+-------------------------------------------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-------+-------------+-----------------------+-----------------------+---------+------+------+-------------------------------------------------+
    | 1 | SIMPLE | role | index_merge | PRIMARY,idx_tenant_id | PRIMARY,idx_tenant_id | 4,4 | NULL | 134 | Using union(PRIMARY,idx_tenant_id); Using where |
    +----+-------------+-------+-------------+-----------------------+-----------------------+---------+------+------+-------------------------------------------------+
  • range:范围扫描通常出现在 in, between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

    1
    2
    3
    4
    5
    6
    mysql> explain select * from actor where id > 1;
    +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
    | 1 | SIMPLE | actor | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where |
    +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
  • index:和ALL一样,不同就是mysql只需扫描索引树,这通常比ALL快一些。

    1
    2
    3
    4
    5
    6
    mysql> explain select count(*) from film;
    +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
    | 1 | SIMPLE | film | index | NULL | idx_name | 33 | NULL | 3 | Using index |
    +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
  • ALL:即全表扫描,意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了。

    1
    2
    3
    4
    5
    6
    mysql> explain select * from actor;
    +----+-------------+-------+------+---------------+------+---------+------+------+-------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-------+------+---------------+------+---------+------+------+-------+
    | 1 | SIMPLE | actor | ALL | NULL | NULL | NULL | NULL | 2 | NULL |
    +----+-------------+-------+------+---------------+------+---------+------+------+-------+

(5)possible_keys

这一列显示查询可能使用哪些索引来查找。

  • explain 时可能出现 possible_keys 有列,而 key 显示 NULL 的情况,这种情况是因为表中数据不多,mysql认为索引对此查询帮助不大,选择了全表查询。
  • 如果该列是NULL,则没有相关的索引。在这种情况下,可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能,然后用 explain 查看效果。

(6)key

这一列显示mysql实际采用哪个索引来优化对该表的访问。

  • 如果没有使用索引,则该列是 NULL。
  • 如果想强制mysql使用或忽视possible_keys列中的索引,在查询中使用 force index、ignore index。

(7)key_len

这一列显示了mysql在索引里使用的字节数,通过这个值可以算出具体使用了索引中的哪些列。

举例来说,film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成,并且每个int是4字节。通过结果中的key_len=4可推断出查询使用了第一个列:film_id列来执行索引查找。

1
2
3
4
5
6
mysql> explain select * from film_actor where film_id = 2;
+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+-------------+
| 1 | SIMPLE | film_actor | ref | idx_film_actor_id | idx_film_actor_id | 4 | const | 1 | Using index |
+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+-------------+

key_len计算规则如下:

  • 字符串
    • char(n):n字节长度
    • varchar(n):2字节存储字符串长度,如果是utf-8,则长度 3n + 2
  • 数值类型
    • tinyint:1字节
    • smallint:2字节
    • int:4字节
    • bigint:8字节  
  • 时间类型 
    • date:3字节
    • timestamp:4字节
    • datetime:8字节
  • 如果字段允许为 NULL,需要1字节记录是否为 NULL

索引最大长度是768字节,当字符串过长时,mysql会做一个类似左前缀索引的处理,将前半部分的字符提取出来做索引。

(8)ref

这一列显示了在key列记录的索引中,表查找值所用到的列或常量,常见的有:const(常量),func,NULL,字段名(例:film.id)

(9)rows

这一列是mysql估计要读取并检测的行数,注意这个不是结果集里的行数。

(10)Extra

这一列展示的是额外信息。常见的重要值如下:

  • distinct:一旦mysql找到了与行相联合匹配的行,就不再搜索了

    1
    2
    3
    4
    5
    6
    7
    mysql> explain select distinct name from film left join film_actor on film.id = film_actor.film_id;
    +----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+------------------------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+------------------------------+
    | 1 | SIMPLE | film | index | idx_name | idx_name | 33 | NULL | 3 | Using index; Using temporary |
    | 1 | SIMPLE | film_actor | ref | idx_film_actor_id | idx_film_actor_id | 4 | test.film.id | 1 | Using index; Distinct |
    +----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+------------------------------+
  • Using index:这发生在对表的请求列都是同一索引的部分的时候,返回的列数据只使用了索引中的信息,而没有再去访问表中的行记录。是性能高的表现。

    1
    2
    3
    4
    5
    6
    mysql> explain select id from film order by id;
    +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
    | 1 | SIMPLE | film | index | NULL | PRIMARY | 4 | NULL | 3 | Using index |
    +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
  • Using where:mysql服务器将在存储引擎检索行后再进行过滤。就是先读取整行数据,再按 where 条件进行检查,符合就留下,不符合就丢弃

    1
    2
    3
    4
    5
    6
    mysql> explain select * from film where id > 1;
    +----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+
    | 1 | SIMPLE | film | index | PRIMARY | idx_name | 33 | NULL | 3 | Using where; Using index |
    +----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+
  • Using temporary:mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的,首先是想到用索引来优化。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    1. actor.name没有索引,此时创建了张临时表来distinct
    mysql> explain select distinct name from actor;
    +----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-------+------+---------------+------+---------+------+------+-----------------+
    | 1 | SIMPLE | actor | ALL | NULL | NULL | NULL | NULL | 2 | Using temporary |
    +----+-------------+-------+------+---------------+------+---------+------+------+-----------------+

    2. film.name建立了idx_name索引,此时查询时extra是using index,没有用临时表
    mysql> explain select distinct name from film;
    +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
    | 1 | SIMPLE | film | index | idx_name | idx_name | 33 | NULL | 3 | Using index |
    +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
  • Using filesort:mysql 会对结果使用一个外部索引排序,而不是按索引次序从表里读取行。此时mysql会根据联接类型浏览所有符合条件的记录,并保存排序关键字和行指针,然后排序关键字并按顺序检索行信息。这种情况下一般也是要考虑使用索引来优化的。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    1. actor.name未创建索引,会浏览actor整个表,保存排序关键字name和对应的id,然后排序name并检索行记录
    mysql> explain select * from actor order by name;
    +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-------+------+---------------+------+---------+------+------+----------------+
    | 1 | SIMPLE | actor | ALL | NULL | NULL | NULL | NULL | 2 | Using filesort |
    +----+-------------+-------+------+---------------+------+---------+------+------+----------------+

    2. film.name建立了idx_name索引,此时查询时extra是using index
    mysql> explain select * from film order by name;
    +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
    | 1 | SIMPLE | film | index | NULL | idx_name | 33 | NULL | 3 | Using index |
    +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
  • Using index condition:表的读取是通过访问索引图元并首先测试它们来确定是否要读取全表的行。通过这种方式,索引信息被用来推迟(“push down”) 读取全表的行,除非有必要。

    • 详细参考: Section 8.2.1.6, “Index Condition Pushdown Optimization”
    • 存储引擎根据索引尽可能的过滤数据,然后在返回给服务器层根据where其他条件进行过滤。
    • 假设有联合索引 XXX_index(a, b, c) ,查询的时候 where a='xxx' and c=1 ,没有按顺序与索引字段一直,就导致后面条件c无法命中索引。
    • type值为range、 ref、 eq_ref或者ref_or_null的时候 , 会使用到索引条件下推技术。

上述SQL使用到的表和数据:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
DROP TABLE IF EXISTS `actor`;
CREATE TABLE `actor` (
`id` int(11) NOT NULL,
`name` varchar(45) DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2017-12-22 15:27:18'), (2,'b','2017-12-22 15:27:18'), (3,'c','2017-12-22 15:27:18');

DROP TABLE IF EXISTS `film`;
CREATE TABLE `film` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2');

DROP TABLE IF EXISTS `film_actor`;
CREATE TABLE `film_actor` (
`id` int(11) NOT NULL,
`film_id` int(11) NOT NULL,
`actor_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_film_actor_id` (`film_id`,`actor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1);

参考:

🔗 《Oracle调优之看懂SQL执行计划explain

🔗 《Oracle执行计划详细解读

🔗 《看懂Oracle执行计划

🔗 《Orace SQL调优系列之执行计划学习笔记

🔗 《MySQL :: MySQL 8.0 Reference Manual :: 8.8.2 EXPLAIN Output Format

🔗 《mysql explain详解》

🔗 《《收获,不止SQL优化》读书笔记