SQL优化-第一章-从设计层面让SQL飞

前言

SQL优化,老生常谈,确也容易陷入一种思维误区。现谈及SQL化,众口必言,查询走索引,统计行数用count(列),不用count(*).必须用exists 代替in,表关联小表在前(驱动表),大表在后,表链接链接条件等等让人眼晕难记的规则。曾几何时,明明牢记了那些被传成真理规则,确让自己程序跑得越来越慢,各种解释不通。SQL只是数据库的一门语言,诚然记住一些普遍适用的规则,能让自己必过一些坑,确也阻碍我们在繁忙的工作中,遇到问题的一脸茫然,上下求索,历9牛2虎力,恍然原来那个原则是有适用条件的。程序猿是懒的,如果让自己觉得难受的,需要脱离这种懒性。那就意味这事,必然还有更优解,为什么意识不到呢,要么就是从认识问题出发点就产生了问题,要么就是思维模式有误区,就这俩种情况。赘言颇多,该谈谈我理解的sql优化,本文从设计层面来聊聊

设计层面的SQL优化-概览

  1. 从SQL所应用的DBMS体系结构层面
  2. 从SQL所应用的DBMS逻辑结构层面
  3. 从SQL所应用的DBMS表设计层面
  4. 从SQL所应用的DBMS索引设计层面

体系结构-看SQL优化

我知道在一条SQL在做update的时候,会对这条SQL进行三个层面的解析,首先在PGA的session中查找是否存在这条语句(fast parse),如果不存在就去SGA的share pool中查找(soft parse),如果还不存在启动hard parse,大致经过语法,语意,权限,执行计划的解析。在提取数据的时候数据首先会从data buffer中进行查找,如果找不到就从磁盘拉取相关的数据。找到数据后开始进行执行更新,更新时候,会执行write ahead log,这些日志会通过LGWR进程写入到日志组文件中,然后在将数据写入到buffer pool,当系统刚好触发的checkpoint,会将commit数据写入到磁盘。从上面的整个过程中,我可以知道一个更新语句大致经过的组件如下图:
oracle体系结构图
所以了解体系结构中的这部分的东西对与构建快速SQL也有大的帮助。

了解数据缓存池带来的优化举例

--普通插入
SQL> insert into test select * from t;
已创建1166096行。
已用时间:  00: 00: 06.78

--跳过databuffer 采用直接路径读方式
SQL> set timing on
SQL> insert  /*+ append */ into test select * from t;
已创建1166096行。
已用时间:  00: 00: 01.24

了解共享池锁带来的优化举例

--共享池缓存SQL减少sql的硬解析,数据缓冲池缓存数据带来性能提升
-- 仔细比对解析计划中recursive calls,physical reads
SQL> --第1次执行
SQL> select count(*) from t;
  COUNT(*)
----------
     72884
已用时间:  00: 00: 00.11
执行计划
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   291   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 69684 |   291   (1)| 00:00:04 |
-------------------------------------------------------------------
统计信息
----------------------------------------------------------
         28  recursive calls
          0  db block gets
       1110  consistent gets
       1038  physical reads
          0  redo size
        425  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL> --第2次执行
SQL> --这里不做 shared_pool和buffer_cache的flush
SQL> select count(*) from t;
  COUNT(*)
----------
     72884
已用时间:  00: 00: 00.02
执行计划
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   291   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 69684 |   291   (1)| 00:00:04 |
-------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1043  consistent gets
          0  physical reads
          0  redo size
        425  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

了解日志带来的优化举例

--关闭日志带来的提升
SQL> set timing on
SQL> insert  /*+ append */ into test select * from t;
已创建4664384行。
已用时间:  00: 00: 05.01

SQL> alter table test nologging;
表已更改。
SQL> set timing on
SQL> insert  /*+ append */ into test select * from t;
已创建4664384行。
已用时间:  00: 00: 04.39 

--减少日志提交的次数带来的体验
SQL> create table t(x int);
表已创建。
SQL> set timing on
SQL> begin
  2      for i in 1 .. 100000 loop
  3         insert into t1 values (i);
  4        commit;
  5      end loop;
  6  end;
  7  /
PL/SQL 过程已成功完成。
已用时间:  00: 00: 11.21

SQL> drop table t purge;
表已删除。
SQL> create table t(x int);
表已创建。
SQL> begin
  2      for i in 1 .. 100000  loop
  3         insert into t values (i);
  4      end loop;
  5    commit;
  6  end;
  7  /
PL/SQL 过程已成功完成。
已用时间:  00: 00: 04.26

从上面这些例子中,我们可以感受到了解体系结构对SQL优化影响

其他

当然还有一些其他的例子,比如可以增大共享池,降低硬解析,keep 数据,数据预读可以让SQL的效率更高等等,列举这些例子只是让大家切实感受了解基本结构对SQL优化是有极大帮助的。

逻辑结构-看SQL优化

逻辑结构是物理结构的上的一层封装,数据存储在数据库内操作以逻辑结构为准,所以我们有必要去了解下简单的数据库的逻辑结构。逻辑结构和块的物理结构如下图:
逻辑结构图
从上面我们可以看到。在数据库的逻辑结构中,一个数据库中有多个表空间,一个表空间中可以包含多个数据文件,一个数据库文件可以包含多个segment,一个segment可以存在于多个数据文件,一个区只能在一个数据文件上,block是oracle操作数据的最小单位,一个extend有多个连续的block组成。
block组成,由数据块头,表,行目录取,可用空间,和行数据区等几个部分组成。数据块头记录block address,以及所属segment类型(表,行)。表目录,是记录该行所在的表信息。行目录,记录行所在的地址。可用空间就是一个块的未被占用的地方,行数据区,则是数据实际存储的信息。
了解上的基本信息后,我们从最基本的数据单元block说起,然后在逐层往上上说。

block相关的优化

block有俩个主要优化的点:俩个行迁移和行链接。前者表示当更新(特指update)的数据大于一个块的可用空间预设的阈值,会申请第二块。行链接表示,当新增一行的数据大于一个block存储空间时候,需要分配俩个或者多个block。俩个产生的问题都是会让查询访问更多的数据库,也就是跟多的IO导致性能下降。在解析计划中的表现就是consistent gets,逻辑读会增加。如何解决呢?
行迁移解决
将有行迁移的数据便利出来放入临时表,在删除原来的数据,重新插入。

--遍历产生行迁移的数据行
--chained_rows,是oracle提供的一个工具可以直接生成chained_rows表
analyze table 含有大量行行的表名 list chained rows into chained_rows;

行链接解决
行链接的解决办法,只有增大block块。在OLAP场景下block越大越好。

segment相关的优化

segment在跟sql查询效率相关的主要有俩个。1,大segment导致物理读和逻辑读多。2,频繁删除数据产生高水平位的表导致逻辑读多。针对第一种情况,我们一般采用的是进行表分区。见下面例子:

--预先创建分区表RANGE_PART_TAB,和普通表NORM_TAB
SQL> select segment_name,
  2         partition_name,
  3         segment_type,
  4         bytes / 1024 / 1024 "字节数(M)",
  5         tablespace_name
  6    from user_segments
  7   where segment_name IN('RANGE_PART_TAB','NORM_TAB');

SEGMENT_NAME         PARTITION_NAME       SEGMENT_TYPE          字节数(M) TABLESPACE_NAME
-------------------- -------------------- -------------------- ---------- ---------------
NORM_TAB                                  TABLE                        47 TBS_LJB
RANGE_PART_TAB       P1                   TABLE PARTITION           .0625 TBS_LJB
RANGE_PART_TAB       P10                  TABLE PARTITION           .0625 TBS_LJB
RANGE_PART_TAB       P11                  TABLE PARTITION           .9375 TBS_LJB
RANGE_PART_TAB       P12                  TABLE PARTITION               5 TBS_LJB
RANGE_PART_TAB       P2                   TABLE PARTITION           .0625 TBS_LJB
RANGE_PART_TAB       P3                   TABLE PARTITION           .0625 TBS_LJB
RANGE_PART_TAB       P4                   TABLE PARTITION           .0625 TBS_LJB
RANGE_PART_TAB       P5                   TABLE PARTITION           .0625 TBS_LJB
RANGE_PART_TAB       P6                   TABLE PARTITION           .0625 TBS_LJB
RANGE_PART_TAB       P7                   TABLE PARTITION           .0625 TBS_LJB
RANGE_PART_TAB       P8                   TABLE PARTITION           .0625 TBS_LJB
RANGE_PART_TAB       P9                   TABLE PARTITION           .0625 TBS_LJB
RANGE_PART_TAB       P_MAX                TABLE PARTITION              42 TBS_LJB

SQL> select *
  2        from range_part_tab
  3       where deal_date >= TO_DATE('2017-09-04', 'YYYY-MM-DD')
  4         and deal_date <= TO_DATE('2017-09-07', 'YYYY-MM-DD');
---------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                |     1 |  2037 |     2   (0)| 00:00:01 |       |    |
|   1 |  PARTITION RANGE SINGLE|                |     1 |  2037 |     2   (0)| 00:00:01 |     9 |     9 |
|*  2 |   TABLE ACCESS FULL    | RANGE_PART_TAB |     1 |  2037 |     2   (0)| 00:00:01 |     9 |     9 |
---------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
               

SQL> select *
  2        from norm_tab
  3       where deal_date >= TO_DATE('2017-09-04', 'YYYY-MM-DD')
  4         and deal_date <= TO_DATE('2017-09-07', 'YYYY-MM-DD');
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |   223 |   443K|  1606   (1)| 00:00:20 |
|*  1 |  TABLE ACCESS FULL| NORM_TAB |   223 |   443K|  1606   (1)| 00:00:20 |
------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       5923  consistent gets

从上面的例子中我们可以明显看到,分区表的逻辑读,比普通表的逻辑读远远低得多,相应的执行时间也从普通表的20秒,降低到分区表中的1秒。
针对第二种情况,一般是采用move操作(会产生索引失效),或者将原数据备到另外一张表,清空原有的表,在insert。

表空间的相关优化

表空间对SQL的影响,对我们比较常见的,就是一个频繁扩展的表空间,对插入会产生比较大的影响,见下面的例子:

drop tablespace tbs_ljb_a including contents and datafiles;
drop tablespace tbs_ljb_b including contents and datafiles;
--自动扩展
create tablespace TBS_LJB_A datafile  'D:\ORACLE\ORADATA\DATA11G\TBS_LJB_A.DBF' size 1M autoextend on uniform size 64k;
--固定表空间
create tablespace TBS_LJB_B datafile  'D:\ORACLE\ORADATA\DATA11G\TBS_LJB_B.DBF' size 2G ;
--分辨在俩个表空间建立俩个表,t_a,t_b
SQL> CREATE TABLE t_a (id int,contents varchar2(1000)) tablespace TBS_LJB_A;
SQL> CREATE TABLE t_b (id int,contents varchar2(1000)) tablespace TBS_LJB_B;
--插入数据
SQL> ---往自动扩展表空间的表中插入数据
SQL> insert into t_a select rownum,LPAD('1', 1000, '*') from dual connect by level<=200000;
已创建200000行。
已用时间:  00: 00: 52.41

SQL>---往固定大小的表空间的表中插入数据
SQL> insert into t_b select rownum,LPAD('1', 1000, '*') from dual connect by level<=200000;
已创建200000行。
已用时间:  00: 00: 15.17

从上面时间,可以明显看到时间差别。

从表设计-看SQL优化

良好的设计可以极大提升,SQL性能。我们从几个方面来看

分区表的使用

从segment优化一节我们可以看到,分区可以遍历的数据,分区索引可以有效降低索引的二元高度,减少逻辑读,提升访问效率

临时表的使用

中间操作产生的临时数据用临时表存。优点是,减少产生的redo日志,可以降低系统的负担。在体系结构一节我们看到,数据在用户表空间下有数据更新的是会产生WAL日志。频繁的更新删除,会产生大量的redo日志。采用临时表可以有效避免这个问题的产生。请看下面的例子

create table t_tmp (id int,col2 int ,col3 int,contents varchar2(4000));
begin 
   insert into t_tmp select rownum ,rownum+1,rownum+2,rpad('*',400,'*') from dual connect by rownum <=10000; 
   --临时插入t_tmp表后,接下来删除该临时表记录,中间略去了大部分逻辑
   delete from t_tmp ;
   commit;
end;

create global temporary table t_global (id int,col2 int ,col3 int,contents varchar2(4000)) on commit delete rows;

begin 
   insert into t_global select rownum ,rownum+1,rownum+2,rpad('*',400,'*') from dual connect by rownum <=10000; 
   --临时插入t_global表后,如下删除临时表记录的delete动作可以不做,commit后数据自动清理
   --delete from t_global ;
  commit;
end; 

--查询日志的方法 
    select a.name,b.value
    from v$statname a,v$mystat b
    where a.statistic#=b.statistic#
    and a.name='redo size';

首先先用创建普通的表插入一定的数据,在用临时表插入相同的数据,在查询每次的日志量,会发现后者产生的redo 日志远远比前者来得少。

IOT的使用

IOT表和堆表的差别在于IOT的索引和数据是放在一起,可以减少回表(从索引文件定位到数据文件过程),也就是减少了逻辑读,具体表现在于consistent gets,iot的表会比对表来得少,如果返回的数据量越多,这个差别会越大,这也OLAP场景下建议用IOT表的原因。当然占用的空间也会更大。

set autotrace traceonly
SQL> select * from heap_addresses where empno=22;

执行计划
----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    50 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| HEAP_ADDRESSES |     1 |    50 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | SYS_C0013751   |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        659  bytes sent via SQL*Net to client
        405  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
          
          
select * from iot_addresses where empno=22;

执行计划
----------------------------------------------------------------------------------------
| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                    |     1 |    50 |     1   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| SYS_IOT_TOP_104441 |     1 |    50 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        751  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

其他

  1. 主外键的设计,索引的使用。可以极大查询时候提升表连接的速度,减少表关联删除出现锁的等待问题。
  2. 表字段的设计。避免使用过时的数据类型,查询的时候依据字段类型进行查询。避免因为自动转换函数导致查询的时候使用不上索引
        select * from t_col_type where id=6;

        执行计划
        --------------------------------------------------------------------------------
        | Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
        --------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT  |            |     1 |    36 |     9   (0)| 00:00:01 |
        |*  1 |  TABLE ACCESS FULL| T_COL_TYPE |     1 |    36 |     9   (0)| 00:00:01 |
        --------------------------------------------------------------------------------
          1 - filter(TO_NUMBER("ID")=6)    --此处数字做了转换,所以索引无法使用
        统计信息
        ----------------------------------------------------------
                  0  recursive calls
                  0  db block gets
                32  consistent gets
                  0  physical reads
                  0  redo size
                540  bytes sent via SQL*Net to client
                415  bytes received via SQL*Net from client
                  2  SQL*Net roundtrips to/from client
                  0  sorts (memory)
                  0  sorts (disk)
                  1  rows processed

        --实际上只有如下写法才可以用到索引,这个很不应该,是什么类型的取值就设置什么样的字段。
                  
        select * from t_col_type where id='6';
        执行计划
        ------------------------------------------------------------------------------------------
        | Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
        ------------------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT            |            |     1 |    36 |     2   (0)| 00:00:01 |
        |   1 |  TABLE ACCESS BY INDEX ROWID| T_COL_TYPE |     1 |    36 |     2   (0)| 00:00:01 |
        |*  2 |   INDEX RANGE SCAN          | IDX_ID     |     1 |       |     1   (0)| 00:00:01 |
        ------------------------------------------------------------------------------------------
          2 - access("ID"='6')
        统计信息
        ----------------------------------------------------------
                  0  recursive calls
                  0  db block gets
                  4  consistent gets
                  0  physical reads
                  0  redo size
                544  bytes sent via SQL*Net to client
                415  bytes received via SQL*Net from client
                  2  SQL*Net roundtrips to/from client
                  0  sorts (memory)
                  0  sorts (disk)
                  1  rows processed          
                    

从索引设计-看SQL优化

索引(特指B树)从结构上来可以概括为:按照一定结构B+或者B-的形式有序排列的数据表。所以说索引的特性可以归结为,本身存储数据,本身有序,且遵循B+数形式。认识到这三点,就可以来聊聊索引使用对SQL优化。

索引存储数据

在使用SQL语句的时候,如果查询的数据刚好是索引字段,在罗列查询条件的时候不要在加额外的列。简单说即:select *,select 索引列。前者会产生回表的过程,带来更多的逻辑读开销和查询的数据资源开销。看下面的例子: ``` select * from t where object_id<=5;

    执行计划
    ----------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |                |     4 |   828 |     3   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| T              |     4 |   828 |     3   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | IDX1_OBJECT_ID |     4 |       |     2   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------------
    统计信息
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              5  consistent gets
              0  physical reads
              0  redo size
          1666  bytes sent via SQL*Net to client
            415  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              4  rows processed

    --比较消除TABLE ACCESS BY INDEX ROWID回表后的性能,将select * from改为select object_id from 

    select object_id from t where object_id<=5;
    执行计划
    -----------------------------------------------------------------------------------
    | Id  | Operation        | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT |                |     4 |    52 |     2   (0)| 00:00:01 |
    |*  1 |  INDEX RANGE SCAN| IDX1_OBJECT_ID |     4 |    52 |     2   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------
    统计信息
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              3  consistent gets
              0  physical reads
              0  redo size
            478  bytes sent via SQL*Net to client
            415  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              4  rows processed
```

索引本身有序

利用这个特性,在做聚合运算,distinct,order by等需要对表的数据顺序有需求的可以考虑索引的引入。下面看一个求max例子: ``` --利用索引object_id select max(object_id) from t; 执行计划 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 13 | | | | 2 | INDEX FULL SCAN (MIN/MAX)| PK_OBJECT_ID | 1 | 13 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 431 bytes sent via SQLNet to client 415 bytes received via SQLNet from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed

    --如果没用到索引的情况是如下,请看看执行计划有何不同,请看看代价和逻辑读的差异!
    select /*+full(t)*/ max(object_id) from t;
    执行计划
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      |     1 |    13 |   292   (1)| 00:00:04 |
    |   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
    |   2 |   TABLE ACCESS FULL| T    | 92407 |  1173K|   292   (1)| 00:00:04 |
    ---------------------------------------------------------------------------
    统计信息
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
          1047  consistent gets
              0  physical reads
              0  redo size
            431  bytes sent via SQL*Net to client
            415  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed
```
从上面我们已经看到利用索引,求max,会利用 INDEX FULL SCAN (MIN/MAX)这样的一个操作,逻辑读大幅度减少,cost就是明显下降。

B+数的排列,二元高度较低

这样的排列形式,意味在查询的时候会遵循B+数据结构进行查找,会产生更少的逻辑读,前面的俩个例子也侧面说明这个问题。

总结

上面三点是对索引特性的一个概括,但是所有的数据不可能都在索引中找到,也就说会有回表的过程。如果利用索引来降低这个过程的开销就是我们需要注意的。尝试从上面三个特性出发,如果索引在建立的要进行排序操作,那么最好情况下是本身数据有一定的顺序,可以促进索引的创建速度,在查找数据文件,如果数据文件的记录也是有一定顺序,相邻数据会放在同一block,或者临近的block中,也会降低回表的开销。在数据库中衡量 索引和数据文件的有序性叫做聚合因子,聚合因子越小,说明数据排列的有序性越高。看下面这个例子:

--colocated,disorganized俩表是相同的结构,区别是前者x的值是顺序插入,后者是按随机的顺序插入
---两者性能差异显著
select /*+ index( colocated colocated_pk ) */ * from colocated where x between 20000 and 40000;
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'runstats_last'));

------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |      1 |        |  20001 |00:00:00.05 |    2900 |
|   1 |  TABLE ACCESS BY INDEX ROWID| COLOCATED    |      1 |  20002 |  20001 |00:00:00.05 |    2900 |
|*  2 |   INDEX RANGE SCAN          | COLOCATED_PK |      1 |  20002 |  20001 |00:00:00.03 |    1375 |
------------------------------------------------------------------------------------------------------

select /*+ index( disorganized disorganized_pk ) */* from disorganized  where x between 20000 and 40000;
SELECT * FROM table(dbms_xplan.display_cursor(NULL,NULL,'runstats_last'));

---------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |      1 |        |  20001 |00:00:00.09 |   21360 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DISORGANIZED    |      1 |  20002 |  20001 |00:00:00.09 |   21360 |
|*  2 |   INDEX RANGE SCAN          | DISORGANIZED_PK |      1 |  20002 |  20001 |00:00:00.03 |    1375 |
---------------------------------------------------------------------------------------------------------


---下面是聚合因子的统计

select a.index_name,
       b.num_rows,
       b.blocks,
       a.clustering_factor
  from user_indexes a, user_tables b
where index_name in ('COLOCATED_PK', 'DISORGANIZED_PK' )
  and a.table_name = b.table_name;

INDEX_NAME                       NUM_ROWS     BLOCKS CLUSTERING_FACTOR
------------------------------ ---------- ---------- -----------------
COLOCATED_PK                       100000       1252              1190
DISORGANIZED_PK                    100000       1219             99899
 

从上面例子,举一反三,用uuid在主键代价不是一般的高。

心得体会

  1. SQL优化,不单单是语言层面的优化。SQL语言eg,T-SQL,PL-SQL,HIVEQL,MongDBSQL等等都是数据库厂商开放给使用者怎么去拿到到这些数据的工具。工具很简单,就是DDL命令。但是对于如何更快拿到数据,显然停留在知道怎么去拿这些数据的认知是不够的。掌握数据如何放,放哪里,才能够帮助我们更好的提升SQL效率。eg,放在眼前的东西,比放在10米前的东西更快也更容易拿到不是吗?
  2. 尝试去了解设计层面的东西,对我们的提升我们开发,和认知也是有启发的。我一向觉得开发是具有艺术性,灵感就广泛来源各种涉猎。eg:从体系结构,启发我们,在开发的使用引入二级缓存(本地,分布式)有主查询效率提升。从解释计划那边,我们知道数据库的查询,其实是遵循内在的各种元数据,才能够让用户减少规则的记忆去进行自动的调优,提升用户体验,启发我们,能够管理好元数据,那对于系统的管理会有更多有意思的发挥空间,去提升用户体验。去了解这些东西,也能够帮助我们更好理解其他的产品,eg,WAL。现在数据库保证数据的可靠性也都是借鉴这样的实现方式。去了解这些东西也能够帮助我们更好去做其他数据库优化,eg,hbase,mongodb,hive,mysql,不论是nosql,还是rdbms,只要是基于现有计算机结构做数据存储的,都有莫大的裨益。笔者也接触过hbase,mongodb,也做了一定的总结,但是优化的思维,还是基于几年前对oracle,sql优化学习得来的启发,百试不爽。记忆力比较差遗忘了很多东西,有时间在整理下mongodb,hbase实战优化。
  3. 忘掉一切的规则,让优化内化成一种本能的思考。> 这里输入引用文本
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值