SQL优化核心思想：或许你不知道的5条优化技巧

淩亂°似流年 2022-05-26 06:00 186阅读 0赞

原文链接：[点击打开链接][Link 1]

摘要： 随着系统的数据量逐年增加，并发量也成倍增长，SQL性能越来越成为IT系统设计和开发时首要考虑的问题之一。SQL性能问题已经逐步发展成为数据库性能的首要问题，80%的数据库性能问题都是因SQL而导致。面对日益增多的SQL性能问题，如何下手以及如何提前审核已经成为越来越多的程序员必须要考虑的问题。

点击关注 异步图书，置顶公众号

每天与你分享 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表示没有收集直方图。

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.

我们再来查询一下。

现在我们查询\`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节中错误的执行计划吗？**

执行计划中加粗部分（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优化核心思想》

![20180425200216675][]

**《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 FULL67

4.1.2　TABLE ACCESS BY USER ROWID71

4.1.3　TABLE ACCESS BY ROWID RANGE71

4.1.4　TABLE ACCESS BY INDEX ROWID72

4.1.5　INDEX UNIQUE SCAN72

4.1.6　INDEX RANGE SCAN73

4.1.7　INDEX SKIP SCAN74

4.1.8　INDEX FULL SCAN75

4.1.9　INDEX FAST FULL SCAN77

4.1.10　INDEX FULL SCAN （MIN/MAX）80

4.1.11　MAT\_VIEW REWRITE ACCESS FULL83

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　FILTER108

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代替OR134

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 SCAN177

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关联条件只能走NL213

9.17　把你脑袋当CBO217

9.18　扩展统计信息优化案例221

9.19　使用LISGAGG分析函数优化WMSYS.WM\_CONCAT227

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优化SQL268

9.28　利用等待事件优化SQL272

第 10章　全自动SQL审核281

10.1　抓出外键没创建索引的表281

10.2　抓出需要收集直方图的列282

10.3　抓出必须创建索引的列283

10.4　抓出SELECT \* 的SQL284

10.5　抓出有标量子查询的SQL285

10.6　抓出带有自定义函数的SQL286

10.7　抓出表被多次反复调用SQL287

10.8　抓出走了FILTER的SQL288

10.9　抓出返回行数较多的嵌套循环SQL290

10.10　抓出NL被驱动表走了全表扫描的SQL292

10.11　抓出走了TABLE ACCESS FULL的SQL293

10.12　抓出走了INDEX FULL SCAN的SQL294

10.13　抓出走了INDEX SKIP SCAN的SQL295

10.14　抓出索引被哪些SQL引用297

10.15　抓出走了笛卡儿积的SQL298

10.16　抓出走了错误的排序合并连接的SQL299

10.17　抓出LOOP套LOOP的PSQL301

10.18　抓出走了低选择性索引的SQL302

10.19　抓出可以创建组合索引的SQL（回表再过滤选择性高的列）304

10.20　抓出可以创建组合索引的SQL（回表只访问少数字段）306

[Link 1]: http://click.aliyun.com/m/47589/
[20180425200216675]: /images/20220526/1f23a72179914a81a795d27c9206ccf5.png

SQL优化核心思想：或许你不知道的5条优化技巧

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关【MySQL】七种SQL优化方式你知道几条

相关 SQL优化小技巧

相关 10条SQL优化技巧

相关 SQL优化技巧

相关 SQL优化技巧

相关 SQL技巧与优化

相关 mysql优化（三）或许你不知道的10条SQL技巧

相关 SQL语句优化技巧

相关 SQL优化核心思想：或许你不知道的5条优化技巧

相关 12条MySQL优化技巧