发表更新性能优化 / Sql29 分钟读完 (大约4306个字)

SQL 优化之基本概念

必知

基数

某个列唯一键的数量被称为基数。基数的高低通常影响数据的分布。可以使用如下语句查询列的基数与列上数据分布情况：

查询某一列的基数及数据分布
1
2
3
4
5
-- 查询基数
select count(distinct col_name) from table_name;

-- 查询某列数据分布情况
select col_name, count(*) from table_name group by col_name order by 2 desc;

依据基数，计算某一列数据占总量的百分比，可以通过此值大致判断当前查询应该走索引还是全表扫描：当查询结果返回表中 5% 以内的数据时，应该走索引，否则应该走全表扫描。其原因与后面介绍的回表概念有关。

选择性

基数与总行数的比值乘以100%被称为选择性。若要获取选择性，需要先收集统计信息（后文介绍），可以使用如下代码收集表的统计信息：

收集表的统计信息
1
2
3
4
5
6
7
8
9
10
11
BEGIN   
	DBMS_STATS.GATHER_TABLE_STATS(  
        ownname => 'username',  
        tabname => 'table_name',  
        estimate_percent => '100',  
        method_opt => 'for all columns size 1',  
        no_invalidate => FALSE,  
        degree => 1,  
        cascade => TRUE);  
END;
/

收集完统计信息后，可以通过如下语句查看表每个列的基数与选择性：

查询表中各列基数及选择性
1
2
3
4
5
6
7
8
9
10
11
select a.COLUMN_NAME,  
       b.NUM_ROWS,  
       a.NUM_DISTINCT as Cardinality,  
       round(a.NUM_DISTINCT / b.NUM_ROWS * 100, 2) as selectivity,  
       a.HISTOGRAM,  
       a.NUM_BUCKETS  
from "PUBLIC".DBA_TAB_COL_STATISTICS a, "PUBLIC".DBA_TABLES b  
where a.OWNER = b.OWNER  
  and a.TABLE_NAME = b.TABLE_NAME  
  and a.OWNER = 'user_name'  
  and a.TABLE_NAME = 'table_name';

通常，当一个列的选择性 > 20% 时，对应列的数据分布就比较均衡。如果一个列出现在 where 条件中，并且数据分布比较均衡，就应该给对应的列创建索引，以便提高大表的查询性能。可以按如下步骤，利用sql脚本自动抓取出需要创建索引的列：

  1. 运行一条 SQL 语句；
  2. 刷新数据库监控信息：
刷新数据库监控信息
1
2
3
BEGIN   
	DBMS_STATS.flush_database_monitoring_info;  
end;
  1. 运行自动化脚本获取需要创建索引的列
获取需要创建索引的列
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
select owner,  
       COLUMN_NAME,  
       NUM_ROWS,  
       Cardinality,  
       selectivity,  
       'Need index' as notice  
from (select a.COLUMN_NAME,  
           b.NUM_ROWS,  
           a.NUM_DISTINCT as Cardinality,  
           round(a.NUM_DISTINCT / b.NUM_ROWS * 100, 2) as selectivity,  
           a.HISTOGRAM,  
           a.NUM_BUCKETS  
    from "PUBLIC".DBA_TAB_COL_STATISTICS a, "PUBLIC".DBA_TABLES b  
    where a.OWNER = b.OWNER  
      and a.TABLE_NAME = b.TABLE_NAME  
      and a.OWNER = 'user_name'  
      and a.TABLE_NAME = 'table_name')  
where selectivity >= 20  
    and COLUMN_NAME not in (select COLUMN_NAME  
                            from "PUBLIC".DBA_IND_COLUMNS  
                            where TABLE_OWNER = 'user_name' and TABLE_NAME = 'table_name')  
    and COLUMN_NAME in (select c.name  
                        from sys.COL_USAGE$ u, sys.OBJ$ o, sys.COL$ c, sys.USER$ r  
                        where o.OBJ# = u.OBJ# and c.OBJ# = u.OBJ# and c.COL# = u.INTCOL#  
                          and r.name = 'username' and o.NAME = 'table_name');

直方图

直方图为一种统计信息，表示某列数据的分布情况。
如果没有及时收集直方图统计信息，会导致基于成本的优化器（CBO）认为对应列数据分布是均衡的，从而可能导致因统计信息不准而走错执行计划，出现性能问题。选择性中收集统计信息后，查询的结果中，若 HISTOGRAM 为 None ，则表示没有收集直方图统计信息。这是因为收集统计信息时，method_opt 被配置为 for all columns size 1 。此时如果对基数较低的列进行查询，返回的结果数目可能远小于执行计划估算的结果。
如果想要 CBO 估算的行数更加准确，可以在收集统计信息时，将method_opt 被配置为 for columns owner size skewonly 。 具体什么时候应该收集直方图信息，一个指导性原则是：当列出现在 where 条件中，列的选择性小于 1% 并且该列没有收集过直方图信息。下列脚本可以自动化的抓取出需要收集直方图信息的列：

获取需要收集直方图信息的列
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select a.COLUMN_NAME,  
    b.NUM_ROWS,  
    a.NUM_DISTINCT as Cardinality,  
    round(a.NUM_DISTINCT / b.NUM_ROWS * 100, 2) as selectivity,  
    a.HISTOGRAM,  
    'Need gather histogram.' as Notice  
from "PUBLIC".DBA_TAB_COL_STATISTICS a, "PUBLIC".DBA_TABLES b  
where a.OWNER = b.OWNER  
    and a.TABLE_NAME = b.TABLE_NAME  
    and a.OWNER = 'user_name'  
    and a.TABLE_NAME = 'table_name'  
    and NUM_DISTINCT / NUM_ROWS < 0.01  
    and (a.OWNER, a.TABLE_NAME, a.COLUMN_NAME) in (select r.name as owner, o.NAME as table_name, c.name as column_name  
                    from sys.COL_USAGE$ u, sys.OBJ$ o, sys.COL$ c, sys.USER$ r  
                    where o.OBJ# = u.OBJ# and c.OBJ# = u.OBJ# and c.COL# = u.INTCOL#  
                      and r.name = 'username' and o.NAME = 'table_name')  
    and a.HISTOGRAM = 'NONE';

回表

通过索引命中记录的 rowid 访问表中的数据被称为回表。
回表一般为单块读，回表的次数太多会严重影响 SQL 的性能，如果回表次数过多，就应该走全表扫表了。这也就是为什么返回表中 5% 以内的数据应该走索引，而超过 5% 以后，就应该走全表扫描的原因。
一般来说，当要查询的列也包含在索引中时，就不需要回表，因此可以通过建立组合索引的方式开消除回表，提升查询性能；如果一个 SQL 语句有多个过滤条件，但是只有一个列或者部分列创建了索引，会发生回表再过滤，此时也需要创建组合索引以消除回表再过滤。

集群因子

集群因子用于判断索引回表需要消耗的物理IO次数。

  • 如果集群因子与块数接近，说明表的数据基本上是有序的，而却其顺序基本与索引的顺序一致。此时进行索引范围或者索引全扫描时，回表只需要读取少量的数据块就能完成；
  • 如果集群因子与表的记录数接近，说明表的数据和索引的顺序差异很大，在进行索引范围或者索引全扫描时，回表会读取更多的数据块。

集群因子只会影响索引范围扫描以及索引全扫描，因为这两种方式会有大量数据回表，而集群因子主要影响索引回表时的物理IO次数，从而影响 SQL 的查询性能。如果一条SQL查询时不回表，或者返回的数据量较小，此时集群因子的大小对性能几乎没有影响。
以下脚本用于计算某一列数据的集群因子：

计算集群因子
1
2
3
4
5
6
7
select sum(case when block#1 = block#2 and file#1 = file#2 then 0 else 1 end) as clustering_factor  
(select DBMS_ROWID.rowid_relative_fno(ROWID) file#1,  
       load(DBMS_ROWID.rowid_relative_fno(ROWID), 1, null) over(order by OBJECT_ID) file#2,  
       DBMS_ROWID.rowid_block_number(ROWID) block#1,  
       load(DBMS_ROWID.rowid_block_number(ROWID), 1, null) over(order by OBJECT_ID) block#2  
from table_name  
where OBJECT_ID is not null);

表间关系

表间关系有：1:1、1:N、N:N 。
如果两表关系为 1:1 ，关联以后数据不会有重复；如果两表关系为 1:N，关联以后的数据为 N 的关系；如果两表关系为 N:N，关联后的数据为笛卡尔积的关系。

统计信息

统计信息分类

主要有以下几类统计信息：

  • 表的统计信息。主要包含表的总行数、表的块数以及平均长度。可以通过查询数据字典 DBA_TABLES 获取表的统计信息。
  • 列的统计信息。主要包含列的基数、列中空值的数量、以及列的数据分布情况（直方图）。可以通过数据字典 DBA_TAB_COL_STATISTICS 查看列的统计信息。
  • 索引的统计信息。主要包含索引的 blevel （索引高度-1）、叶子块的个数、集群因子。可以通过数据字典 DBA_INDEXES 查看索引的统计信息
  • 系统的统计信息
  • 数据字典的统计信息
  • 动态视图基表的统计信息

通常通过如下语句直接查询表和列的统计信息（查看统计信息前需要先收集统计信息，方法见下一节）：

查询表和列的统计信息
1
2
3
4
5
6
7
8
9
10
11
select a.COLUMN_NAME,  
       b.NUM_ROWS,  
       a.NUM_DISTINCT as Cardinality,  
       round(a.NUM_DISTINCT / b.NUM_ROWS * 100, 2) as selectivity,  
       a.HISTOGRAM,  
       a.NUM_BUCKETS  
from "PUBLIC".DBA_TAB_COL_STATISTICS a, "PUBLIC".DBA_TABLES b  
where a.OWNER = b.OWNER  
  and a.TABLE_NAME = b.TABLE_NAME  
  and a.OWNER = 'user_name'  
  and a.TABLE_NAME = 'table_name';

可以单独收集索引的统计信息：

收集索引的统计信息
1
2
3
4
5
6
begin  
    DBMS_STATS.GATHER_INDEX_STATS(  
        ownname => 'table_name',  
        indname => 'index_name');
end;  
/

统计信息参数设置

通常用下列语句收集统计：

收集统计信息
1
2
3
4
5
6
7
8
9
10
11
BEGIN   
	DBMS_STATS.GATHER_TABLE_STATS(  
        ownname => 'username',  
        tabname => 'table_name',  
        estimate_percent => '100',  
        method_opt => 'for all columns size 1',  
        no_invalidate => FALSE,  
        degree => 1,  
        cascade => TRUE);  
END;
/

其中，各个参数的说明如下：

  • ownname。表的拥有者，不区分大小写；
  • tabname。表名，不区分大小写；
  • granlarity。表示收集统计信息的粒度。选项支队分区表有效，默认为 AUTO 。通常使用默认值即可，表示让数据库自己根据分区表的类型判断如何收集分区表的统计信息；
  • estimate_percent。表示采样率，范围为 0.000001~100 。通常对小于 1G 的表 100% 采样，1G~5G 的表 50% 采样，大于 5G 的表 30% 采样，更大的表建议先分区，再采样；
查看采样率
1
2
3
4
5
6
7
select owner,  
       table_name,  
       num_rows,  
       sample_size,  
       round(SAMPLE_SIZE / NUM_ROWS * 100) estimate_percent  
from "PUBLIC".DBA_TAB_STATISTICS 
where owner = 'username' and table_name = 'table_name';
  • method_opt。用于控制收集直方图的策略。
    • for all columns size 1，表示所有列不收集直方图；
    • for all columns size skewonly，表示所有列自动判断是否收集直方图，实际工作中通常不应该使用此项，因为不是所有的列都出现在 where 条件中；
    • for all columns size auto，默认值，表示出现在 where 条件中的列自动判断是否收集直方图，如果对应列的选择性比较高，一般就不会收集直方图信息；
    • for all columns size repeat，表示仅对当前已经收集过直方图信息的列收集直方图；
    • for columns object_type size skewonly，表示单独对 object_type 列收集直方图，其余列如果之前收集过直方图，则也会收集；
  • no_invalidate。表示共享池中涉及到该表的游标是否立即失效，默认值为 DBMS_STATS.AUTO_INVALIDE，表示让数据库自行决定是否立即失效，建议将此参数设置为 false，防止重新刷新统计信息以后，执行计划没有按预期发生变更。
  • degree。表示收集统计信息的并行度，默认为 null。如果没有设置此项参数，收集统计信息时就不会并行。
  • cascade。表示收及统计信息的时候，是否级联收集索引的统计信息，默认值为 DBMS_STATS.AUTO_CASCADE，一般情况下将其设置为 TRUE。

检查统计信息是否过期

收集完统计信息以后，如果表中有大量的数据发生变更，就会导致表的统计信息过期。此时建议重新收集统计信息。可以通过如下方法检查数据库的统计信息是否过期：

  • 先刷新数据库的监控信息。
刷新数据库监控信息
1
2
3
4
begin  
    DBMS_STATS.flush_database_monitoring_info;
end;  
/
  • 查询统计信息是否过期。如果 STALE_STATS 为 yes 则表示统计信息过期了。
查询统计信息是否过期
1
2
3
4
5
6
7
select OWNER,  
       TABLE_NAME,  
       OBJECT_TYPE,  
       STALE_STATS,  
       LAST_ANALYZED  
from "PUBLIC".DBA_TAB_STATISTICS  
where OWNER='username' and TABLE_NAME='table_name';
  • 进一步，还可以通过如下语句查询统计信息过期的原因。
查询统计信息过期原因
1
2
3
4
5
6
7
8
select TABLE_OWNER,  
       TABLE_NAME,  
       INSERTS,  
       UPDATES,  
       DELETES,  
       TIMESTAMP  
from "PUBLIC".ALL_TAB_MODIFICATIONS  
where TABLE_OWNER='username' and TABLE_NAME='tab_name';

Oracle 数据库通常在数据库有 10% 的数据发生变更时（insert、update、delete），判断统计信息过期。数据库字典 ALL_TAB_MODIFICATIONS 还可以用来判断哪些表需要定期降低高水位：如果一个表经常进行 insert、delete ，那么这个表就应该定期降低高水位，同时表的索引也应该定期重建。
如果一条查询语句涉及的表比较多，可以先用 explain plan for 生成执行计划，再用如下查询语句检查对应SQL中的表的统计信息是否过期：

检查对应SQL中的表的统计信息是否过期
1
2
3
4
5
6
7
8
9
10
select OWNER, TABLE_NAME, OBJECT_TYPE, STALE_STATS, LAST_ANALYZED  
from "PUBLIC".DBA_TAB_STATISTICS  
where (OWNER, TABLE_NAME) in  
      (select OBJECT_OWNER, OBJECT_NAME  
        from "PUBLIC".PLAN_TABLE where OBJECT_TYPE like '%TABLE%'  
        union  
        select TABLE_OWNER, TABLE_NAME  
        from "PUBLIC".DBA_INDEXES where (OWNER, INDEX_NAME) in  
            (select OWNER, OBJECT_NAME  
             from "PUBLIC".PLAN_TABLE where OBJECT_TYPE like '%INDEX%'));

进一步，通过如下语句检查各表统计信息过期原因：

检查各表统计信息过期原因
1
2
3
4
5
6
7
8
9
10
select *  
from "PUBLIC".ALL_TAB_MODIFICATIONS  
where (TABLE_OWNER, TABLE_NAME) in  
      (select OBJECT_OWNER, OBJECT_NAME  
        from "PUBLIC".PLAN_TABLE where OBJECT_TYPE like '%TABLE%'  
        union  
        select TABLE_OWNER, TABLE_NAME  
        from "PUBLIC".DBA_INDEXES where (OWNER, INDEX_NAME) in  
            (select OWNER, OBJECT_NAME  
             from "PUBLIC".PLAN_TABLE where OBJECT_TYPE like '%INDEX%'));

扩展统计信息

当 where 条件中有多个谓词过滤条件，但是这些过滤条件彼此之间有关系，而非相互独立的情况下，这时可能需要收集扩展统计信息以便优化器能够估算出比较准确的行数。这时如果想让优化器能够估算更加准确的行数，可以使用扩展统计信息将相关联的列组合成一个列，再对组合后的列收集统计信息即可。
需要注意：扩展统计信息只能用于等值查询，而不能用于非等值查询。

动态采样

如果一张表从未收集过统计信息，数据库会对表进行动态采样（level=2），动态采样的目的是为了让优化器能够评估处较为准确的行数。动态采样级别共12级：

  • level 0：不进行动态；
  • level 1：当表（非分区表）没有收集过统计信息，并且这个表与另外的表进行关联（不能是单表访问），同时该表没有索引，表的块数大于 32 个时，数据库会随机扫描 32 个数据块，然后评估返回行数；
  • level 2：默认采样级别。对没有收集过统计信息的表启用动态采样，采样块数为 64，如果表的块数小于 64 个，则会全部进行采样；
  • level 3：对没有收集过统计信息的表启用动态采样，采样块数为 64 个。如果表已经收集过统计信息，但是行数还是估算的不准，这时会随机采样 64 个块进行采样；
  • level 4：对没有收集过统计信息的表启用动态采样，采样块数为 64 个。如果表已经收集过统计信息，但是表有两个或两个以上过滤条件（and/or），这个时后会随机扫描 64 个块进行采样。相关列导致的问题至少需要开启 level 4 进行动态采样。此级别包含了 level 3 的采样数据；
  • level 5~10：收集满足 level 4 采样条件的数据，但是采样块数分别为：128、256、512、1024、4086、所有数据块；
  • level 11：数据库自行判断如何采样，且自行决定采样块数。

可以通过如下方式设置采样级别：

  • 修改当前会话的采样级别
设置采样级别
1
alter session set optimizer_dynamic_sampling = 4;
  • 修改 SQL 语句的采样级别，当表已经收集过统计信息以后，可能此 hint 实际不会生效。
添加 hint 设置采样级别
1
select /*+ dynamic_sampling(4) */ * from table_name where conds;

何时需要启用动态采样？
ans：当 1、系统中有全局临时表时（因为全局临时表无法收集统计信息）； 2、当执行计划中的表的行数估算有严重偏差；3、 where 条件中对列使用了 substr、instr、like；4、 where 条件中有非等值过滤；5、 group by 以后，导致函数估算错误时，均建议开启动态采样，并且将采样界别设置为 level 4。

不要改变系统的动态采样率！！！

SQL 优化之基本概念

https://jblogs.tech/2024/11/sqlperfopt/sql-perf-opt-chapt1/

作者

Jeill

发布于

2024-11-23

更新于

2024-12-08

许可协议

#

评论

关注我

订阅更新

×