SQL优化-第一章-从设计层面让SQL飞 - codec style - 开源中国
前言
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数据写入到磁盘。从上面的整个过程中，我可以知道一个更新语句大致经过的组件如下图： 
所以了解体系结构中的这部分的东西对与构建快速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 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 /*+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实战优化。
- 忘掉一切的规则，让优化内化成一种本能的思考。> 这里输入引用文本
Original url: Access
Created at: 2018-10-18 11:38:17
Category: default
Tags: none
未标明原创文章均为采集，版权归作者所有，转载无需和我联系，请注明原出处，南摩阿彌陀佛，知识，不只知道，要得到
最新评论