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

最新推荐文章于 2024-11-14 15:58:00 发布

林的BLOG

最新推荐文章于 2024-11-14 15:58:00 发布

阅读量202

点赞数

文章标签：数据库 python mysql java 大数据

本文链接：https://blog.csdn.net/jayjwes190/article/details/108709568

版权

前言

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

设计层面的SQL优化-概览

从SQL所应用的DBMS体系结构层面
从SQL所应用的DBMS逻辑结构层面
从SQL所应用的DBMS表设计层面
从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

其他

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

        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在主键代价不是一般的高。

心得体会

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