点击关注 异步图书，置顶公众号
每天与你分享 IT好书 技术干货 职场知识
参与文末话题讨论，每日赠送异步图书。
——异步小编
随着系统的数据量逐年增加，并发量也成倍增长，SQL性能越来越成为IT系统设计和开发时首要考虑的问题之一。SQL性能问题已经逐步发展成为数据库性能的首要问题，80%的数据库性能问题都是因SQL而导致。面对日益增多的SQL性能问题，如何下手以及如何提前审核已经成为越来越多的程序员必须要考虑的问题。
今天将带来《SQL优化核心思想》，作者将8年专职SQL优化的经验和心得与大家一起分享，以揭开SQL优化的神秘面纱，让一线工程师在实际开发中不再寝食难安、谈虎色变，最终能够对SQL优化技能驾轻就熟.首先进入主题SQL优化必懂概念。
1.1 基数（CARDINALITY）
某个列唯一键（Distinct_Keys）的数量叫作基数。比如性别列，该列只有男女之分，所以这一列基数是2。主键列的基数等于表的总行数。基数的高低影响列的数据分布。
以测试表test为例，owner列和object_id列的基数分别如下所示。
1SQL> select count(distinct owner),count(distinct object_id),count(*) from test;
2COUNT(DISTINCTOWNER) COUNT(DISTINCTOBJECT_ID) COUNT(*)
3-------------------- ------------------------ ----------
4 29 72462 72462
TEST表的总行数为72 462，owner列的基数为29，说明owner列里面有大量重复值，object_id列的基数等于总行数，说明object_id列没有重复值，相当于主键。owner列的数据分布如下。
1SQL> select owner,count(*) from test group by owner order by 2 desc;
2OWNER COUNT(*)
3-------------------- ----------
4SYS 30808
5PUBLIC 27699
6SYSMAN 3491
7ORDSYS 2532
8APEX_030200 2406
9MDSYS 1509
10XDB 844
11OLAPSYS 719
12SYSTEM 529
13CTXSYS 366
14WMSYS 316
15EXFSYS 310
16SH 306
17ORDDATA 248
18OE 127
19DBSNMP 57
20IX 55
21HR 34
22PM 27
23FLOWS_FILES 12
24OWBSYS_AUDIT 12
25ORDPLUGINS 10
26OUTLN 9
27BI 8
28SI_INFORMTN_SCHEMA 8
29ORACLE_OCM 8
30SCOTT 7
31APPQOSSYS 3
32OWBSYS 2
33
owner列的数据分布极不均衡，我们运行如下SQL。
1select * from test where owner='SYS';
SYS有30 808条数据，从72 462条数据里面查询30 808条数据，也就是说要返回表中42.5%的数据。
1SQL> select 30808/72462*100 "Percent" from dual;
2 Percent
3----------
442.5160774
那么请思考，你认为以上查询应该使用索引吗？现在我们换一种查询语句。
1select * from test where owner='SCOTT';
SCOTT有7条数据，从72 462条数据里面查询7条数据，也就是说要返回表中0.009%的数据。
1SQL> select 7/72462*100 "Percent" from dual;
2 Percent
3----------
4.009660236
请思考，返回表中0.009%的数据应不应该走索引？
如果你还不懂索引，没关系，后面的章节我们会详细介绍。如果你回答不了上面的问题，我们先提醒一下。当查询结果是返回表中5%以内的数据时，应该走索引；当查询结果返回的是超过表中5%的数据时，应该走全表扫描。
当然了，返回表中5%以内的数据走索引，返回超过5%的数据就使用全表扫描，这个结论太绝对了，因为你还没掌握后面章节的知识，这里暂且记住5%这个界限就行。我们之所以在这里讲5%，是怕一些初学者不知道上面问题的答案而纠结。
现在有如下查询语句。
1select * from test where owner=:B1;
语句中，“:B1”是绑定变量，可以传入任意值，该查询可能走索引也可能走全表扫描。
现在得到一个结论：如果某个列基数很低，该列数据分布就会非常不均衡，由于该列数据分布不均衡，会导致SQL查询可能走索引，也可能走全表扫描。在做SQL优化的时候，如果怀疑列数据分布不均衡，我们可以使用select列，count(*) from表group by列order by 2 desc来查看列的数据分布。
如果SQL语句是单表访问，那么可能走索引，可能走全表扫描，也可能走物化视图扫描。在不考虑有物化视图的情况下，单表访问要么走索引，要么走全表扫描。现在，回忆一下走索引的条件：返回表中5%以内的数据走索引，超过5%的时候走全表扫描。相信大家读到这里，已经搞懂了单表访问的优化方法。
我们来看如下查询。
1select * from test where object_id=:B1;
不管object_id传入任何值，都应该走索引。
我们再思考如下查询语句。
1select * from test where object_name=:B1;
不管给object_name传入任何值，请问该查询应该走索引吗？
请你去查看object_name的数据分布。写到这里，其实有点想把本节名称改为“数据分布”。大家在以后的工作中一定要注意列的数据分布！
1.2 选择性（SELECTIVITY）
基数与总行数的比值再乘以100%就是某个列的选择性。
在进行SQL优化的时候，单独看列的基数是没有意义的，基数必须对比总行数才有实际意义，正是因为这个原因，我们才引出了选择性这个概念。
下面我们查看test表各个列的基数与选择性，为了查看选择性，必须先收集统计信息。关于统计信息，我们在第2章会详细介绍。下面的脚本用于收集test表的统计信息。
1SQL> BEGIN
2 2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
3 3 tabname => 'TEST',
4 4 estimate_percent => 100,
5 5 method_opt => 'for all columns size 1',
6 6 no_invalidate => FALSE,
7 7 degree => 1,
8 8 cascade => TRUE);
9 9 END;
10 10 /
11PL/SQL procedure successfully completed.
下面的脚本用于查看test表中每个列的基数与选择性。
1SQL> select a.column_name,
2 2 b.num_rows,
3 3 a.num_distinct Cardinality,
4 4 round(a.num_distinct / b.num_rows * 100, 2) selectivity,
5 5 a.histogram,
6 6 a.num_buckets
7 7 from dba_tab_col_statistics a, dba_tables b
8 8 where a.owner = b.owner
9 9 and a.table_name = b.table_name
10 10 and a.owner = 'SCOTT'
11 11 and a.table_name = 'TEST';
12COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
13--------------- ---------- ----------- ----------- --------- -----------
14OWNER 72462 29 .04 NONE 1
15OBJECT_NAME 72462 44236 61.05 NONE 1
16SUBOBJECT_NAME 72462 106 .15 NONE 1
17OBJECT_ID 72462 72462 100 NONE 1
18DATA_OBJECT_ID 72462 7608 10.5 NONE 1
19OBJECT_TYPE 72462 44 .06 NONE 1
20CREATED 72462 1366 1.89 NONE 1
21LAST_DDL_TIME 72462 1412 1.95 NONE 1
22TIMESTAMP 72462 1480 2.04 NONE 1
23STATUS 72462 1 0 NONE 1
24TEMPORARY 72462 2 0 NONE 1
25GENERATED 72462 2 0 NONE 1
26SECONDARY 72462 2 0 NONE 1
27NAMESPACE 72462 21 .03 NONE 1
28EDITION_NAME 72462 0 0 NONE 0
2915 rows selected.
请思考：什么样的列必须建立索引呢？
有人说基数高的列，有人说在where条件中的列。这些答案并不完美。基数高究竟是多高？没有和总行数对比，始终不知道有多高。比如某个列的基数有几万行，但是总行数有几十亿行，那么这个列的基数还高吗？这就是要引出选择性的根本原因。
当一个列选择性大于20%，说明该列的数据分布就比较均衡了。测试表test中object_name、object_id的选择性均大于20%，其中object_name列的选择性为61.05%。现在我们查看该列数据分布（为了方便展示，只输出前10行数据的分布情况）。
1SQL> select *
2 2 from (select object_name, count(*)
3 3 from test
4 4 group by object_name
5 5 order by 2 desc)
6 6 where rownum <= 10;
7OBJECT_NAME COUNT(*)
8------------------ ----------
9COSTS 30
10SALES 30
11SALES_CHANNEL_BIX 29
12COSTS_TIME_BIX 29
13COSTS_PROD_BIX 29
14SALES_TIME_BIX 29
15SALES_PROMO_BIX 29
16SALES_PROD_BIX 29
17SALES_CUST_BIX 29
18DBMS_REPCAT_AUTH 5
1910 rows selected.
由上面的查询结果我们可知，object_name列的数据分布非常均衡。我们查询以下SQL。
1select * from test where object_name=:B1;
不管object_name传入任何值，最多返回30行数据。
什么样的列必须要创建索引呢？当一个列出现在where条件中，该列没有创建索引并且选择性大于20%，那么该列就必须创建索引，从而提升SQL查询性能。当然了，如果表只有几百条数据，那我们就不用创建索引了。
下面抛出SQL优化核心思想第一个观点：只有大表才会产生性能问题。
也许有人会说：“我有个表很小，只有几百条，但是该表经常进行DML，会产生热点块，也会出性能问题。”对此我们并不想过多地讨论此问题，这属于应用程序设计问题，不属于SQL优化的范畴。
下面我们将通过实验为大家分享本文第一个全自动优化脚本。
抓出必须创建索引的列（请读者对该脚本适当修改，以便用于生产环境）。
首先，该列必须出现在where条件中，怎么抓出表的哪个列出现在where条件中呢？有两种方法，一种是可以通过V$SQL_PLAN抓取，另一种是通过下面的脚本抓取。
先执行下面的存储过程，刷新数据库监控信息。
1begin
2 dbms_stats.flush_database_monitoring_info;
3end;
运行完上面的命令之后，再运行下面的查询语句就可以查询出哪个表的哪个列出现在where条件中。
1select r.name owner,
2 o.name table_name,
3 c.name column_name,
4 equality_preds, ---等值过滤
5 equijoin_preds, ---等值JOIN 比如where a.id=b.id
6 nonequijoin_preds, ----不等JOIN
7 range_preds, ----范围过滤次数 > >= < <= between and
8 like_preds, ----LIKE过滤
9 null_preds, ----NULL 过滤
10 timestamp
11 from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r
12 where o.obj# = u.obj#
13 and c.obj# = u.obj#
14 and c.col# = u.intcol#
15 and r.name = 'SCOTT'
16 and o.name = 'TEST';
下面是实验步骤。
我们首先运行一个查询语句，让owner与object_id列出现在where条件中。
1SQL> select object_id, owner, object_type
2 2 from test
3 3 where owner = 'SYS'
4 4 and object_id < 100
5 5 and rownum <= 10;
6 OBJECT_ID OWNER OBJECT_TYPE
7---------- -------------------- -----------
8 20 SYS TABLE
9 46 SYS INDEX
10 28 SYS TABLE
11 15 SYS TABLE
12 29 SYS CLUSTER
13 3 SYS INDEX
14 25 SYS TABLE
15 41 SYS INDEX
16 54 SYS INDEX
17 40 SYS INDEX
1810 rows selected.
其次刷新数据库监控信息。
1SQL> begin
2 2 dbms_stats.flush_database_monitoring_info;
3 3 end;
4 4 /
5PL/SQL procedure successfully completed.
然后我们查看test表有哪些列出现在where条件中。
1SQL> select r.name owner, o.name table_name, c.name column_name
2 2 from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r
3 3 where o.obj# = u.obj#
4 4 and c.obj# = u.obj#
5 5 and c.col# = u.intcol#
6 6 and r.name = 'SCOTT'
7 7 and o.name = 'TEST';
8OWNER TABLE_NAME COLUMN_NAME
9---------- ---------- ------------------------------
10SCOTT TEST OWNER
11SCOTT TEST OBJECT_ID
接下来我们查询出选择性大于等于20%的列。
1SQL> select a.owner,
2 2 a.table_name,
3 3 a.column_name,
4 4 round(a.num_distinct / b.num_rows * 100, 2) selectivity
5 5 from dba_tab_col_statistics a, dba_tables b
6 6 where a.owner = b.owner
7 7 and a.table_name = b.table_name
8 8 and a.owner = 'SCOTT'
9 9 and a.table_name = 'TEST'
10 10 and a.num_distinct / b.num_rows >= 0.2;
11OWNER TABLE_NAME COLUMN_NAME SELECTIVITY
12---------- ---------- ------------- -----------
13SCOTT TEST OBJECT_NAME 61.05
14SCOTT TEST OBJECT_ID 100
最后，确保这些列没有创建索引。
1SQL> select table_owner, table_name, column_name, index_name
2 2 from dba_ind_columns
3 3 where table_owner = 'SCOTT'
4 4 and table_name = 'TEST';
5未选定行
把上面的脚本组合起来，我们就可以得到全自动的优化脚本了。
1SQL> select owner,
2 2 column_name,
3 3 num_rows,
4 4 Cardinality,
5 5 selectivity,
6 6 'Need index' as notice
7 7 from (select b.owner,
8 8 a.column_name,
9 9 b.num_rows,
10 10 a.num_distinct Cardinality,
11 11 round(a.num_distinct / b.num_rows * 100, 2) selectivity
12 12 from dba_tab_col_statistics a, dba_tables b
13 13 where a.owner = b.owner
14 14 and a.table_name = b.table_name
15 15 and a.owner = 'SCOTT'
16 16 and a.table_name = 'TEST')
17 17 where selectivity >= 20
18 18 and column_name not in (select column_name
19 19 from dba_ind_columns
20 20 where table_owner = 'SCOTT'
21 21 and table_name = 'TEST')
22 22 and column_name in
23 23 (select c.name
24 24 from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r
25 25 where o.obj# = u.obj#
26 26 and c.obj# = u.obj#
27 27 and c.col# = u.intcol#
28 28 and r.name = 'SCOTT'
29 29 and o.name = 'TEST');
30OWNER COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY NOTICE
31---------- ------------- ---------- ----------- ----------- ----------
32SCOTT OBJECT_ID 72462 72462 100 Need index
1.3 直方图（HISTOGRAM）
前面提到，当某个列基数很低，该列数据分布就会不均衡。数据分布不均衡会导致在查询该列的时候，要么走全表扫描，要么走索引扫描，这个时候很容易走错执行计划。
如果没有对基数低的列收集直方图统计信息，基于成本的优化器（CBO）会认为该列数据分布是均衡的。
下面我们还是以测试表test为例，用实验讲解直方图。
首先我们对测试表test收集统计信息，在收集统计信息的时候，不收集列的直方图，语句for all columns size 1表示对所有列都不收集直方图。
1SQL> BEGIN
2 2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
3 3 tabname => 'TEST',
4 4 estimate_percent => 100,
5 5 method_opt => 'for all columns size 1',
6 6 no_invalidate => FALSE,
7 7 degree => 1,
8 8 cascade => TRUE);
9 9 END;
10 10 /
11PL/SQL procedure successfully completed.
Histogram为none表示没有收集直方图。
1SQL> select a.column_name,
2 2 b.num_rows,
3 3 a.num_distinct Cardinality,
4 4 round(a.num_distinct / b.num_rows * 100, 2) selectivity,
5 5 a.histogram,
6 6 a.num_buckets
7 7 from dba_tab_col_statistics a, dba_tables b
8 8 where a.owner = b.owner
9 9 and a.table_name = b.table_name
10 10 and a.owner = 'SCOTT'
11 11 and a.table_name = 'TEST';
12COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
13--------------- ---------- ----------- ----------- --------- -----------
14OWNER 72462 29 .04 NONE 1
15OBJECT_NAME 72462 44236 61.05 NONE 1
16SUBOBJECT_NAME 72462 106 .15 NONE 1
17OBJECT_ID 72462 72462 100 NONE 1
18DATA_OBJECT_ID 72462 7608 10.5 NONE 1
19OBJECT_TYPE 72462 44 .06 NONE 1
20CREATED 72462 1366 1.89 NONE 1
21LAST_DDL_TIME 72462 1412 1.95 NONE 1
22TIMESTAMP 72462 1480 2.04 NONE 1
23STATUS 72462 1 0 NONE 1
24TEMPORARY 72462 2 0 NONE 1
25GENERATED 72462 2 0 NONE 1
26SECONDARY 72462 2 0 NONE 1
27NAMESPACE 72462 21 .03 NONE 1
28EDITION_NAME 72462 0 0 NONE 0
2915 rows selected.
owner列基数很低，现在我们对owner列进行查询。
1SQL> set autot trace
2SQL> select * from test where owner='SCOTT';
37 rows selected.
4Execution Plan
5----------------------------------------------------------
6Plan hash value: 1357081020
7--------------------------------------------------------------------------
8| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
9--------------------------------------------------------------------------
10| 0 | SELECT STATEMENT | | 2499 | 236K| 289 (1)| 00:00:04 |
11|* 1 | TABLE ACCESS FULL| TEST | 2499 | 236K| 289 (1)| 00:00:04 |
12--------------------------------------------------------------------------
13Predicate Information (identified by operation id):
14---------------------------------------------------
15 1 - filter("OWNER"='SCOTT')
请注意看粗体字部分，查询owner＝'SCOTT'返回了7条数据，但是CBO在计算Rows的时候认为owner='SCOTT'返回2 499条数据，Rows估算得不是特别准确。从72 462条数据里面查询出7条数据，应该走索引，所以现在我们对owner列创建索引。
1SQL> create index idx_owner on test(owner);
2Index created.
我们再来查询一下。
1SQL> select * from test where owner='SCOTT';
27 rows selected.
3Execution Plan
4----------------------------------------------------------
5Plan hash value: 3932013684
6-------------------------------------------------------------------------------------
7| Id |Operation |Name | Rows | Bytes | Cost(%CPU)| Time |
8-------------------------------------------------------------------------------------
9| 0 | SELECT STATEMENT | | 2499 | 236K | 73 (0)| 00:00:01 |
10| 1 | TABLE ACCESS BY INDEX ROWID |TEST | 2499 | 236K | 73 (0)| 00:00:01 |
11|* 2 | INDEX RANGE SCAN |IDX_OWNER| 2499 | | 6 (0)| 00:00:01 |
12-------------------------------------------------------------------------------------
13Predicate Information (identified by operation id):
14---------------------------------------------------
15 2 - access("OWNER"='SCOTT')
现在我们查询`owner='SYS'。
1SQL> select * from test where owner='SYS';
230808 rows selected.
3Execution Plan
4----------------------------------------------------------
5Plan hash value: 3932013684
6-------------------------------------------------------------------------------------
7| Id |Operation | Name | Rows | Bytes | Cost(%CPU)| Time |
8-------------------------------------------------------------------------------------
9| 0 | SELECT STATEMENT | | 2499 | 236K| 73 (0)| 00:00:01 |
10| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 2499 | 236K| 73 (0)| 00:00:01 |
11|* 2 | INDEX RANGE SCAN | IDX_OWNER| 2499 | | 6 (0)| 00:00:01 |
12-------------------------------------------------------------------------------------
13Predicate Information (identified by operation id):
14---------------------------------------------------
15 2 - access("OWNER"='SYS')
注意粗字体部分，查询owner='SYS'返回了30 808条数据。从72 462条数据里面返回30 808条数据能走索引吗？很明显应该走全表扫描。也就是说该执行计划是错误的。
为什么查询owner='SYS'的执行计划会用错呢？因为owner这个列基数很低，只有29，而表的总行数是72 462。前文着重强调过，当列没有收集直方图统计信息的时候，CBO会认为该列数据分布是均衡的。正是因为CBO认为owner列数据分布是均衡的，不管owner等于任何值，CBO估算的Rows永远都是2 499。而这2 499是怎么来的呢？答案如下。
1SQL> select round(72462/29) from dual;
2round(72462/29)
3--------------
4 2499
现在大家也知道了，执行计划里面的Rows是假的。执行计划中的Rows是根据统计信息以及一些数学公式计算出来的。很多DBA到现在还不知道执行计划中Rows是假的这个真相，真是令人遗憾。
在做SQL优化的时候，经常需要做的工作就是帮助CBO计算出比较准确的Rows。注意：我们说的是比较准确的Rows。CBO是无法得到精确的Rows的，因为对表收集统计信息的时候，统计信息一般都不会按照100%的标准采样收集，即使按照100%的标准采样收集了表的统计信息，表中的数据也随时在发生变更。另外计算Rows的数学公式目前也是有缺陷的，CBO永远不可能计算得到精确的Rows。
如果CBO每次都能计算得到精确的Rows，那么相信我们这个时候只需要关心业务逻辑、表设计、SQL写法以及如何建立索引了，再也不用担心SQL会走错执行计划了。
Oracle12c的新功能SQL Plan Directives在一定程度上解决了Rows估算不准而引发的SQL性能问题。关于SQL Plan Directives，本文不做过多讨论。
为了让CBO选择正确的执行计划，我们需要对owner列收集直方图信息，从而告知CBO该列数据分布不均衡，让CBO在计算Rows的时候参考直方图统计。现在我们对owner列收集直方图。
1SQL> BEGIN
2 2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
3 3 tabname => 'TEST',
4 4 estimate_percent => 100,
5 5 method_opt => 'for columns owner size skewonly',
6 6 no_invalidate => FALSE,
7 7 degree => 1,
8 8 cascade => TRUE);
9 9 END;
10 10 /
11PL/SQL procedure successfully completed.
查看一下owner列的直方图信息。
1SQL> select a.column_name,
2 2 b.num_rows,
3 3 a.num_distinct Cardinality,
4 4 round(a.num_distinct / b.num_rows * 100, 2) selectivity,
5 5 a.histogram,
6 6 a.num_buckets
7 7 from dba_tab_col_statistics a, dba_tables b
8 8 where a.owner = b.owner
9 9 and a.table_name = b.table_name
10 10 and a.owner = 'SCOTT'
11 11 and a.table_name = 'TEST';
12COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
13--------------- ---------- ----------- ----------- ---------- -----------
14OWNER 72462 29 .04 FREQUENCY 29
15OBJECT_NAME 72462 44236 61.05 NONE 1
16SUBOBJECT_NAME 72462 106 .15 NONE 1
17OBJECT_ID 72462 72462 100 NONE 1
18DATA_OBJECT_ID 72462 7608 10.5 NONE 1
19OBJECT_TYPE 72462 44 .06 NONE 1
20CREATED 72462 1366 1.89 NONE 1
21LAST_DDL_TIME 72462 1412 1.95 NONE 1
22TIMESTAMP 72462 1480 2.04 NONE 1
23STATUS 72462 1 0 NONE 1
24TEMPORARY 72462 2 0 NONE 1
25GENERATED 72462 2 0 NONE 1
26SECONDARY 72462 2 0 NONE 1
27NAMESPACE 72462 21 .03 NONE 1
28EDITION_NAME 72462 0 0 NONE 0
2915 rows selected.
现在我们再来查询上面的SQL，看执行计划是否还会走错并且验证Rows是否还会算错。
1SQL> select * from test where owner='SCOTT';
27 rows selected.
3Execution Plan
4----------------------------------------------------------
5Plan hash value: 3932013684
6-------------------------------------------------------------------------------------
7| Id |Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
8-------------------------------------------------------------------------------------
9| 0 | SELECT STATEMENT | | 7 | 679 | 2 (0)| 00:00:01 |
10| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 7 | 679 | 2 (0)| 00:00:01 |
11|* 2 | INDEX RANGE SCAN | IDX_OWNER| 7 | | 1 (0)| 00:00:01 |
12-------------------------------------------------------------------------------------
13Predicate Information (identified by operation id):
14---------------------------------------------------
15 2 - access("OWNER"='SCOTT')
16SQL> select * from test where owner='SYS';
1730808 rows selected.
18Execution Plan
19----------------------------------------------------------
20Plan hash value: 1357081020
21--------------------------------------------------------------------------
22| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
23--------------------------------------------------------------------------
24| 0 | SELECT STATEMENT | | 30808 | 2918K| 290 (1)| 00:00:04 |
25|* 1 | TABLE ACCESS FULL| TEST | 30808 | 2918K| 290 (1)| 00:00:04 |
26--------------------------------------------------------------------------
27Predicate Information (identified by operation id):
28---------------------------------------------------
29 1 - filter("OWNER"='SYS')
对owner列收集完直方图之后，CBO估算的Rows就基本准确了，一旦Rows估算对了，那么执行计划也就不会出错了。
大家是不是很好奇，为什么收集完直方图之后，Rows计算得那么精确，收集直方图究竟完成了什么操作呢？对owner列收集直方图其实就相当于运行了以下SQL。
1select owner,count(*) from test group by owner;
直方图信息就是以上SQL的查询结果，这些查询结果会保存在数据字典中。这样当我们查询owner为任意值的时候，CBO总会算出正确的Rows，因为直方图已经知道每个值有多少行数据。
如果SQL使用了绑定变量，绑定变量的列收集了直方图，那么该SQL就会引起绑定变量窥探。绑定变量窥探是一个老生常谈的问题，这里不多做讨论。Oracle11g引入了自适应游标共享（Adaptive Cursor Sharing），基本上解决了绑定变量窥探问题，但是自适应游标共享也会引起一些新问题，对此也不做过多讨论。
当我们遇到一个SQL有绑定变量怎么办？其实很简单，我们只需要运行以下语句。
1select 列, count(*) from test group by 列 order by 2 desc;
如果列数据分布均衡，基本上SQL不会出现问题；如果列数据分布不均衡，我们需要对列收集直方图统计。
关于直方图，其实还有非常多的话题，比如直方图的种类、直方图的桶数等，本文在此不做过多讨论。在我们看来，读者只需要知道直方图是用来帮助CBO在对基数很低、数据分布不均衡的列进行Rows估算的时候，可以得到更精确的Rows就够了。
什么样的列需要收集直方图呢？当列出现在where条件中，列的选择性小于1%并且该列没有收集过直方图，这样的列就应该收集直方图。注意：千万不能对没有出现在where条件中的列收集直方图。对没有出现在where条件中的列收集直方图完全是做无用功，浪费数据库资源。
下面我们为大家分享本文第二个全自动化优化脚本。
抓出必须创建直方图的列（大家可以对该脚本进行适当修改，以便用于生产环境）。
1SQL> select a.owner,
2 2 a.table_name,
3 3 a.column_name,
4 4 b.num_rows,
5 5 a.num_distinct,
6 6 trunc(num_distinct / num_rows * 100,2) selectivity,
7 7 'Need Gather Histogram' notice
8 8 from dba_tab_col_statistics a, dba_tables b
9 9 where a.owner = 'SCOTT'
10 10 and a.table_name = 'TEST'
11 11 and a.owner = b.owner
12 12 and a.table_name = b.table_name
13 13 and num_distinct / num_rows<0.01
14 14 and (a.owner, a.table_name, a.column_name) in
15 15 (select r.name owner, o.name table_name, c.name column_name
16 16 from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ r
17 17 where o.obj# = u.obj#
18 18 and c.obj# = u.obj#
19 19 and c.col# = u.intcol#
20 20 and r.name = 'SCOTT'
21 21 and o.name = 'TEST')
22 22 and a.histogram ='NONE';
23OWNER TABLE COLUM NUM_ROWS NUM_DISTINCT SELECTIVITY NOTICE
24----- ----- ----- ---------- ------------ ----------- ----------------------
25SCOTT TEST OWNER 72462 29 .04 Need Gather Histogram
1.4 回表（TABLE ACCESS BY INDEX ROWID）
当对一个列创建索引之后，索引会包含该列的键值以及键值对应行所在的rowid。通过索引中记录的rowid访问表中的数据就叫回表。回表一般是单块读，回表次数太多会严重影响SQL性能，如果回表次数太多，就不应该走索引扫描了，应该直接走全表扫描。
在进行SQL优化的时候，一定要注意回表次数！特别是要注意回表的物理I/O次数！
大家还记得1.3节中错误的执行计划吗？
1SQL> select * from test where owner='SYS';
230808 rows selected.
3Execution Plan
4----------------------------------------------------------
5Plan hash value: 3932013684
6-------------------------------------------------------------------------------------
7| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time |
8-------------------------------------------------------------------------------------
9| 0 | SELECT STATEMENT | | 2499 | 236K| 73 (0)| 00:00:01 |
10| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 2499 | 236K| 73 (0)| 00:00:01 |
11|* 2 | INDEX RANGE SCAN | IDX_OWNER| 2499 | | 6 (0)| 00:00:01 |
12-------------------------------------------------------------------------------------
13Predicate Information (identified by operation id):
14---------------------------------------------------
15 2 - access("OWNER"='SYS')
执行计划中加粗部分（TABLE ACCESS BY INDEX ROWID）就是回表。索引返回多少行数据，回表就要回多少次，每次回表都是单块读（因为一个rowid对应一个数据块）。该SQL返回了30 808行数据，那么回表一共就需要30 808次。
请思考：上面执行计划的性能是耗费在索引扫描中还是耗费在回表中？
为了得到答案，请大家在SQLPLUS中进行实验。为了消除arraysize参数对逻辑读的影响，设置arraysize=5000。arraysize表示Oracle服务器每次传输多少行数据到客户端，默认为15。如果一个块有150行数据，那么这个块就会被读10次，因为每次只传输15行数据到客户端，逻辑读会被放大。设置了arraysize=5000之后，就不会发生一个块被读n次的问题了。
1SQL> set arraysize 5000
2SQL> set autot trace
3SQL> select owner from test where owner='SYS';
430808 rows selected.
5Execution Plan
6----------------------------------------------------------
7Plan hash value: 373050211
8------------------------------------------------------------------------------
9| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
10------------------------------------------------------------------------------
11| 0 | SELECT STATEMENT | | 2499 | 14994 | 6 (0)| 00:00:01 |
12|* 1 | INDEX RANGE SCAN| IDX_OWNER | 2499 | 14994 | 6 (0)| 00:00:01 |
13------------------------------------------------------------------------------
14Predicate Information (identified by operation id):
15---------------------------------------------------
16 1 - access("OWNER"='SYS')
17Statistics
18----------------------------------------------------------
19 0 recursive calls
20 0 db block gets
21 74 consistent gets
22 0 physical reads
23 0 redo size
24 155251 bytes sent via SQL*Net to client
25 486 bytes received via SQL*Net from client
26 8 SQL*Net roundtrips to/from client
27 0 sorts (memory)
28 0 sorts (disk)
29 30808 rows processed
从上面的实验可见，索引扫描只耗费了74个逻辑读。
1SQL> select * from test where owner='SYS';
230808 rows selected.
3Execution Plan
4----------------------------------------------------------
5Plan hash value: 3932013684
6-------------------------------------------------------------------------------------
7| Id |Operation | Name | Rows | Bytes | Cost(%CPU)| Time |
8-------------------------------------------------------------------------------------
9| 0 | SELECT STATEMENT | | 2499 | 236K| 73 (0)| 00:00:01 |
10| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 2499 | 236K| 73 (0)| 00:00:01 |
11|* 2 | INDEX RANGE SCAN | IDX_OWNER| 2499 | | 6 (0)| 00:00:01 |
12-------------------------------------------------------------------------------------
13Predicate Information (identified by operation id):
14---------------------------------------------------
15 2 - access("OWNER"='SYS')
16Statistics
17----------------------------------------------------------
18 0 recursive calls
19 0 db block gets
20 877 consistent gets
21 0 physical reads
22 0 redo size
23 3120934 bytes sent via SQL*Net to client
24 486 bytes received via SQL*Net from client
25 8 SQL*Net roundtrips to/from client
26 0 sorts (memory)
27 0 sorts (disk)
28 30808 rows processed
29SQL> set autot off
30SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) blocks
31 2 from test
32 3 where owner = 'SYS';
33 BLOCKS
34----------
35 796
SQL在有回表的情况下，一共耗费了877个逻辑读，那么这877个逻辑读是怎么来的呢？
SQL返回的30 808条数据一共存储在796个数据块中，访问这796个数据块就需要消耗796个逻辑读，加上索引扫描的74个逻辑读，再加上7个逻辑读[其中7=ROUND（30808/5000）]，这样累计起来刚好就是877个逻辑读。
因此我们可以判断，该SQL的性能确实绝大部分损失在回表中！
更糟糕的是：假设30 808条数据都在不同的数据块中，表也没有被缓存在buffer cache中，那么回表一共需要耗费30 808个物理I/O，这太可怕了。
大家看到这里，是否能回答为什么返回表中5%以内的数据走索引、超过表中5%的数据走全表扫描？根本原因就在于回表。
在无法避免回表的情况下，走索引如果返回数据量太多，必然会导致回表次数太多，从而导致性能严重下降。
Oracle12c的新功能批量回表（TABLE ACCESS BY INDEX ROWID BATCHED）在一定程度上改善了单行回表（TABLE ACCESS BY INDEX ROWID）的性能。关于批量回表本文不做讨论。
什么样的SQL必须要回表？
1Select * from table where ...
这样的SQL就必须回表，所以我们必须严禁使用Select *。那什么样的SQL不需要回表？
1Select count(*) from table
这样的SQL就不需要回表。
当要查询的列也包含在索引中，这个时候就不需要回表了，所以我们往往会建立组合索引来消除回表，从而提升查询性能。
当一个SQL有多个过滤条件但是只在一个列或者部分列建立了索引，这个时候会发生回表再过滤（TABLE ACCESS BY INDEX ROWID前面有“*”），也需要创建组合索引，进而消除回表再过滤，从而提升查询性能。
关于如何创建组合索引，这问题太复杂了，我们在后文反复提及如何创建组合索引。
本文摘自《SQL优化核心思想》
《SQL优化核心思想》
罗炳森 黄超 钟侥 著
点击封面购买纸书
结构化查询语言（Structured Query Language，SQL）是一种功能强大的数据库语言。它基于关系代数运算，功能丰富、语言简洁、使用方便灵活，已成为关系数据库的标准语言。 本书旨在引导读者掌握SQL优化技能，以更好地提升数据库性能。本书基于Oracle进行编写，内容讲解由浅入深，适合各个层次的读者学习。
本书面向一线工程师、运维工程师、数据库管理员以及系统设计与开发人员，无论是初学者还是有一定基础的读者，都将从中获益。
本书目录
第 1章 SQL优化必懂概念 1
1.1 基数（CARDINALITY） 1
1.2 选择性（SELECTIVITY） 3
1.3 直方图（HISTOGRAM） 7
1.4 回表（TABLE ACCESS BY INDEXROWID） 13
1.5 集群因子（CLUSTERING FACTOR） 15
1.6 表与表之间关系 19
第 2章 统计信息 21
2.1 什么是统计信息 21
2.2 统计信息重要参数设置 24
2.3 检查统计信息是否过期 32
2.4 扩展统计信息 37
2.5 动态采样 42
2.6 定制统计信息收集策略 47
第3章 执行计划 49
3.1 获取执行计划常用方法 49
3.1.1 使用AUTOTRACE查看执行计划 49
3.1.2 使用EXPLAIN PLAN FOR查看执行计划 52
3.1.3 查看带有A-TIME的执行计划 54
3.1.4 查看正在执行的SQL的执行计划 56
3.2 定制执行计划 57
3.3 怎么通过查看执行计划建立索引 59
3.4 运用光标移动大法阅读执行计划 63
第4章 访问路径（ACCESS PATH） 67
4.1 常见访问路径 67
4.1.1 TABLE ACCESS FULL 67
4.1.2 TABLE ACCESS BY USER ROWID 71
4.1.3 TABLE ACCESS BY ROWID RANGE 71
4.1.4 TABLE ACCESS BY INDEX ROWID 72
4.1.5 INDEX UNIQUE SCAN 72
4.1.6 INDEX RANGE SCAN 73
4.1.7 INDEX SKIP SCAN 74
4.1.8 INDEX FULL SCAN 75
4.1.9 INDEX FAST FULL SCAN 77
4.1.10 INDEX FULL SCAN （MIN/MAX） 80
4.1.11 MAT_VIEW REWRITE ACCESS FULL 83
4.2 单块读与多块读 83
4.3 为什么有时候索引扫描比全表扫描更慢 84
4.4 DML对于索引维护的影响 84
第5章 表连接方式 86
5.1 嵌套循环（NESTED LOOPS） 86
5.2 HASH连接（HASH JOIN） 90
5.3 排序合并连接（SORT MERGE JOIN） 93
5.4 笛卡儿连接（CARTESIAN JOIN） 95
5.5 标量子查询（SCALAR SUBQUERY） 98
5.6 半连接（SEMI JOIN） 100
5.6.1 半连接等价改写 100
5.6.2 控制半连接执行计划 101
5.6.3 读者思考 103
5.7 反连接（ANTI JOIN） 104
5.7.1 反连接等价改写 104
5.7.2 控制反连接执行计划 105
5.7.3 读者思考 108
5.8 FILTER 108
5.9 IN与EXISTS谁快谁慢 111
5.10 SQL语句的本质 111
第6章 成本计算 112
6.1 优化SQL需要看COST吗 112
6.2 全表扫描成本计算 112
6.3 索引范围扫描成本计算 116
6.4 SQL优化核心思想 119
第7章 必须掌握的查询变换 120
7.1 子查询非嵌套 120
7.2 视图合并 125
7.3 谓词推入 129
第8章 调优技巧 133
8.1 查看真实的基数（Rows） 133
8.2 使用UNION代替OR 134
8.3 分页语句优化思路 135
8.3.1 单表分页优化思路 135
8.3.2 多表关联分页优化思路 150
8.4 使用分析函数优化自连接 153
8.5 超大表与超小表关联优化方法 154
8.6 超大表与超大表关联优化方法 155
8.7 LIKE语句优化方法 159
8.8 DBLINK优化 161
8.9 对表进行ROWID切片 167
8.10 SQL三段分拆法 169
第9章 SQL优化案例赏析 170
9.1 组合索引优化案例 170
9.2 直方图优化案例 173
9.3 NL被驱动表不能走INDEX SKIP SCAN 177
9.4 优化SQL需要注意表与表之间关系 178
9.5 INDEX FAST FULL SCAN优化案例 179
9.6 分页语句优化案例 181
9.7 ORDER BY取别名列优化案例 183
9.8 半连接反向驱动主表案例一 185
9.9 半连接反向驱动主表案例二 187
9.10 连接列数据分布不均衡导致性能问题 192
9.11 Filter优化经典案例 198
9.12 树形查询优化案例 202
9.13 本地索引优化案例 204
9.14 标量子查询优化案例 206
9.14.1 案例一 206
9.14.2 案例二 207
9.15 关联更新优化案例 211
9.16 外连接有OR关联条件只能走NL 213
9.17 把你脑袋当CBO 217
9.18 扩展统计信息优化案例 221
9.19 使用LISGAGG分析函数优化WMSYS.WM_CONCAT 227
9.20 INSTR非等值关联优化案例 230
9.21 REGEXP_LIKE非等值关联优化案例 233
9.22 ROW LEVEL SECURITY优化案例 237
9.23 子查询非嵌套优化案例一 240
9.24 子查询非嵌套优化案例二 247
9.25 烂用外连接导致无法谓词推入 252
9.26 谓词推入优化案例 262
9.27 使用CARDINALITY优化SQL 268
9.28 利用等待事件优化SQL 272
第 10章 全自动SQL审核 281
10.1 抓出外键没创建索引的表 281
10.2 抓出需要收集直方图的列 282
10.3 抓出必须创建索引的列 283
10.4 抓出SELECT * 的SQL 284
10.5 抓出有标量子查询的SQL 285
10.6 抓出带有自定义函数的SQL 286
10.7 抓出表被多次反复调用SQL 287
10.8 抓出走了FILTER的SQL 288
10.9 抓出返回行数较多的嵌套循环SQL 290
10.10 抓出NL被驱动表走了全表扫描的SQL 292
10.11 抓出走了TABLE ACCESS FULL的SQL 293
10.12 抓出走了INDEX FULL SCAN的SQL 294
10.13 抓出走了INDEX SKIP SCAN的SQL 295
10.14 抓出索引被哪些SQL引用 297
10.15 抓出走了笛卡儿积的SQL 298
10.16 抓出走了错误的排序合并连接的SQL 299
10.17 抓出LOOP套LOOP的PSQL 301
10.18 抓出走了低选择性索引的SQL 302
10.19 抓出可以创建组合索引的SQL（回表再过滤选择性高的列） 304
10.20 抓出可以创建组合索引的SQL（回表只访问少数字段） 306
今日互动
你对异步图书的哪类文章感兴趣？为什么？截止时间4月27日17时，留言+转发本活动到朋友圈，小编将抽奖选出5名读者赠送纸书2本和3张e读版100元异步社区代金券，（留言点赞最多的自动获得一张）。
推荐阅读
在“异步图书”后台回复“关注”，即可免费获得2000门在线视频课程；推荐朋友关注根据提示获取赠书链接，免费得异步e读版图书一本。赶紧来参加哦！
扫一扫上方二维码，回复“关注”参与活动！
点击阅读原文，直接购买《SQL优化核心思想》
扫一扫
630
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
