22.读书笔记收获不止Oracle之 索引特性活用
|
22.读书笔记收获不止Oracle之 索引特性活用 以实际操作为例： 看看索引的特性灵活使用： SQL> set autotrace traceonly SQL> set linesize 1000 SQL> drop table t purge; SQL> create table t as select * fromdba_objects; SQL> set autotrace traceonly; SQL> select * from t where object_id>2; 91720 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0| SELECT STATEMENT | | 91720 | 10M|430 (1)| 00:00:01 | |* 1| TABLE ACCESS FULL| T | 91720 | 10M|430 (1)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified byoperation id): --------------------------------------------------- 1- filter("OBJECT_ID">2) Statistics ---------------------------------------------------------- 2recursive calls 0 dbblock gets 7545 consistent gets 1539 physical reads 0 redosize 5005356 bytes sent via SQL*Net toclient 67805 bytes received via SQL*Netfrom client 6116 SQL*Net roundtrips to/fromclient 0sorts (memory) 0sorts (disk) 91720 rows processed 在执行一个类似的语句，增加ORDER BY关键字 SQL> select * from t where object_id>2 order by object_id; 91720 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 961378228 ----------------------------------------------------------------------------------- | Id| Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0| SELECT STATEMENT | |91720 | 10M| | 2808(1)| 00:00:01 | | 1| SORT ORDER BY | | 91720 |10M| 13M| 2808(1)| 00:00:01 | |* 2| TABLE ACCESS FULL| T |91720 | 10M| | 430(1)| 00:00:01 | ----------------------------------------------------------------------------------- Predicate Information (identified byoperation id): --------------------------------------------------- 2- filter("OBJECT_ID">2) Statistics ---------------------------------------------------------- 1recursive calls 0 dbblock gets 1542 consistent gets 1539 physical reads 0 redosize 4852173 bytes sent via SQL*Net toclient 67805 bytes received via SQL*Netfrom client 6116 SQL*Net roundtrips to/fromclient 1sorts (memory) 0sorts (disk) 91720 rows processed 发现逻辑读从7545下降到1542.有排序的逻辑读更低。但是排序的COST成本更高。真正决定性能是COST的高低和真实完成的时间。 逻辑读是作为参考，在大部分情况下，逻辑读越少性能越快。 1. 通过索引消除排序如果排序列是索引列，那么可以消除索引，给刚才表增加索引，并重新执行如下： Create index idx_t_object_id on t(object_id); Set autotrace traceonly; SQL>select * from t where object_id>2 order byobject_id; 91720 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 4285561625 ----------------------------------------------------------------------------------------------- | Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 91720 | 10M|2732 (1)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 91720 | 10M|2732 (1)| 00:00:01 | |* 2 | INDEX RANGE SCAN |IDX_T_OBJECT_ID | 91720 | | 204 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------- Predicate Information (identified byoperation id): --------------------------------------------------- 2- access("OBJECT_ID">2) Statistics ---------------------------------------------------------- 1recursive calls 0 dbblock gets 14795 consistent gets 1745 physical reads 0 redosize 4852173 bytes sent via SQL*Net toclient 67805 bytes received via SQL*Netfrom client 6116 SQL*Net roundtrips to/fromclient 0sorts (memory) 0sorts (disk) 91720 rows processed 发下你没有排序了在执行计划中，因为索引已经进行排序过了。不过有没有发现逻辑读却多出了很多？ 这是因为索引范围扫描一次只能读取一个块，要从索引中通过回表获取其他列的信息，需要读取的块就更多了。 虽然走索引消除了排序，但是增加了大量的逻辑读14795。这个需要优化器来综合判断COST大小. 2. 缩小返回列不返回所有字段,只返回做过索引的字段. 执行如下: SQL>select object_id from t where object_id>2 order byobject_id; 91720 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2498590897 ------------------------------------------------------------------------------------ | Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0| SELECT STATEMENT | | 91720 |447K| 204 (0)| 00:00:01 | |* 1| INDEX RANGE SCAN| IDX_T_OBJECT_ID |91720 | 447K| 204(0)| 00:00:01 | ------------------------------------------------------------------------------------ Predicate Information (identified byoperation id): --------------------------------------------------- 1- access("OBJECT_ID">2) Statistics ---------------------------------------------------------- 1recursive calls 0 dbblock gets 6305 consistent gets 0physical reads 0 redosize 1726112 bytes sent via SQL*Net toclient 67805 bytes received via SQL*Netfrom client 6116 SQL*Net roundtrips to/fromclient 0sorts (memory) 0sorts (disk) 91720 rows processed 这样就避免了回表，在实际中要尽可能的避免回表机会，不要犯了业务允许只取一列而你却取了全部列遮掩的错误。 3. DISTINCT排重优化DISTINCT是排除重复记录的大写。 示例如下： SQL> drop table t purge; SQL> create table t as select * from dba_objects; SQL> alter table t modify object_id not null; SQL> update t set object_id=2; 91721 rows updated. SQL> update t set object_id=3 where rownum<=25000; 25000 rows updated. SQL> commit; 进行查看 Set autotrace traceonly Select distinct object_id from t; Execution Plan ---------------------------------------------------------- Plan hash value: 1793979440 -------------------------------------------------------------------------------- --- | Id| Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | -------------------------------------------------------------------------------- --- | 0| SELECT STATEMENT | |91721 | 447K| | 712(1)| 00:00:0 1 | | 1| HASH UNIQUE | | 91721 | 447K|1088K| 712 (1)| 00:00:0 1 | | 2| TABLE ACCESS FULL| T |91721 | 447K| | 429(1)| 00:00:0 1 | -------------------------------------------------------------------------------- --- Statistics ---------------------------------------------------------- 42recursive calls 0 dbblock gets 1589 consistent gets 1543 physical reads 0 redosize 597 bytes sent via SQL*Net to client 551 bytes received via SQL*Net from client 2SQL*Net roundtrips to/from client 5sorts (memory) 0sorts (disk) 2rows processed 3.1 不使用distinct 查看SQL> select object_id from t; Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0| SELECT STATEMENT | | 91721 | 447K|429 (1)| 00:00:01 | | 1| TABLE ACCESS FULL| T | 91721 | 447K|429 (1)| 00:00:01 | -------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1recursive calls 0 dbblock gets 7560 consistent gets 1539 physical reads 0 redosize 1626969 bytes sent via SQL*Net toclient 67805 bytes received via SQL*Netfrom client 6116 SQL*Net roundtrips to/fromclient 0sorts (memory) 0sorts (disk) 91721 rows processed 去掉distinct后，COST降低了。因为DISTINCT是需要排序的。加上DISTINCT是会影响性能的。 3.2 创建索引SQL>create indexidx_t_object_id on t (object_id); Select distinct object_id from t; Execution Plan ---------------------------------------------------------- Plan hash value: 2729247865 -------------------------------------------------------------------------------- ----------------- | Id| Operation | Name |Rows | Bytes |TempSpc| Cost ( %CPU)| Time | -------------------------------------------------------------------------------- ----------------- | 0| SELECT STATEMENT | | 91721 | 447K| | 334 (1)| 00:00:01 | | 1| HASH UNIQUE | | 91721 | 447K|1088K| 334 (1)| 00:00:01 | | 2| INDEX FAST FULL SCAN| IDX_T_OBJECT_ID | 91721| 447K| | 51 (0)| 00:00:01 | -------------------------------------------------------------------------------- ----------------- Statistics ---------------------------------------------------------- 1recursive calls 0 dbblock gets 188 consistent gets 180 physical reads 0 redosize 597 bytes sent via SQL*Net to client 551 bytes received via SQL*Net from client 2SQL*Net roundtrips to/from client 0sorts (memory) 0sorts (disk) 2 rowsprocessed 创建索引，可以消除DISTINCT带来的排序。现实中，靠索引来优化往往收效不明显，因为大多数情况用到DISTINCT都是因为表记录有重复。 4. 索引全扫描与快速全扫INDEX FULL SCAN 索引全扫描 INDEX FAST FULL SCAN索引快速全扫描 索引快速全扫描一次读取多个索引块，而索引全扫描一次只读一个块。 一次读取多个无法保证有序，排序动作就无法消除。无需排序的操作，一般都走INDEX FAST FULL SCAN，涉及排序语句时，就要开始权衡利弊。有时候宁愿排序无法避免，但是减少逻辑读。 刚才创建索引后执行select distinct object_id from t;走的是索引快速全扫描。 在执行如下 SQL>select object_id from t order by object_id; Execution Plan ---------------------------------------------------------- Plan hash value: 439494919 -------------------------------------------------------------------------------- ---- | Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- ---- | 0| SELECT STATEMENT | | 91721 |447K| 181 (0)| 00:00: 01 | | 1| INDEX FULL SCAN | IDX_T_OBJECT_ID |91721 | 447K| 181(0)| 00:00: 01 | -------------------------------------------------------------------------------- ---- Statistics ---------------------------------------------------------- 1recursive calls 0 dbblock gets 6284 consistent gets 0physical reads 0 redosize 1626969 bytes sent via SQL*Net toclient 67805 bytes received via SQL*Netfrom client 6116 SQL*Net roundtrips to/fromclient 0sorts (memory) 0sorts (disk) 91721 rows processed 走的是索引全扫描了。 Index full scan可以消除排序，但是逻辑读比索引快速全扫描要多。 5. UNION合并的优化UNION合并后没有重复记录，UNION ALL合并后可能有重复记录。 UNION会进行排序。UNION ALL不会进行排序。 SQL> drop table t1 purge; SQL> create table t1 as select * from dba_objects; SQL> alter table t1 modify object_id not null; SQL> drop table t2 purge; SQL> create table t2 as select * fromdba_objects; SQL> alter table t2 modify object_id notnull; SQL> set linesize 1000 SQL> set autotrace traceonly SQL> select object_id from t1 union select object_id fromt2; 91786 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3008085330 ------------------------------------------------------------------------------------ | Id| Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0| SELECT STATEMENT | | 183K| 896K| |1423 (1)| 00:00:01 | | 1| SORT UNIQUE | | 183K|896K| 2177K| 1423(1)| 00:00:01 | | 2| UNION-ALL | | | | | | | | 3| TABLE ACCESS FULL| T1 | 91785 |448K| |429 (1)| 00:00:01 | | 4| TABLE ACCESS FULL| T2 | 91784 |448K| |429 (1)| 00:00:01 | ------------------------------------------------------------------------------------ Statistics ---------------------------------------------------------- 35recursive calls 0 dbblock gets 3121 consistent gets 0physical reads 0 redosize 1727425 bytes sent via SQL*Net toclient 67860 bytes received via SQL*Netfrom client 6121 SQL*Net roundtrips to/fromclient 5 sorts (memory) 0sorts (disk) 91786 rows processed 进行了排序操作。 5.1使用索引创建索引 SQL> create index idx_t1_object_id on t1(object_id); SQL> create index idx_t2_object_id on t2(object_id); SQL> select object_id from t1 union select object_id from t2; Execution Plan ---------------------------------------------------------- Plan hash value: 669167125 --------------------------------------------------------------------------------------------------- | Id| Operation | Name | Rows| Bytes |TempSpc| Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0| SELECT STATEMENT | |183K| 896K| | 680 (1)| 00:00:01 | | 1| SORT UNIQUE | | 183K| 896K|2177K| 680 (1)| 00:00:01 | | 2| UNION-ALL | | | | | | | | 3| INDEX FAST FULL SCAN|IDX_T1_OBJECT_ID | 91785 | 448K| | 57(0)| 00:00:01 | | 4| INDEX FAST FULL SCAN|IDX_T2_OBJECT_ID | 91784 | 448K| | 57(0)| 00:00:01 | --------------------------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1recursive calls 0 dbblock gets 424 consistent gets 408 physical reads 0 redosize 1727425 bytes sent via SQL*Net toclient 67860 bytes received via SQL*Netfrom client 6121 SQL*Net roundtrips to/fromclient 1sorts (memory) 0sorts (disk) 91786 rows processed 建索引后ORACEL会走索引快速全扫描，但是排序不可避免。 5.2强制都索引全扫描索引全扫描可以避免排序。 SQL> select /*+index(t1)*/ object_id from t1 union select /*+index(t2)*/object_id from t2; 91786 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 243121257 -------------------------------------------------------------------------------- -------------- | Id| Operation | Name | Rows| Bytes |TempSpc| Cost (%CP U)| Time| -------------------------------------------------------------------------------- -------------- | 0| SELECT STATEMENT | | 183K| 896K| | 977( 1)| 00:00:01 | | 1| SORT UNIQUE | | 183K| 896K| 2177K| 977 ( 1)| 00:00:01 | | 2| UNION-ALL | | | | | | | | 3| INDEX FULL SCAN| IDX_T1_OBJECT_ID |91785 | 448K| | 205( 0)| 00:00:01 | | 4| INDEX FULL SCAN| IDX_T2_OBJECT_ID |91784 | 448K| | 205( 0)| 00:00:01 | -------------------------------------------------------------------------------- -------------- Statistics ---------------------------------------------------------- 104 recursive calls 0 dbblock gets 540 consistent gets 414 physical reads 0 redosize 1727425 bytes sent via SQL*Net toclient 67860 bytes received via SQL*Netfrom client 6121 SQL*Net roundtrips to/fromclient 13sorts (memory) 0sorts (disk) 91786 rows processed 使用了IDNEX FULL SCAN，但是并没有消除排序。INDEX FULL SCAN不能消除排序。所以ORACLE当然弃用索引全扫描方式。因为是两个不同的结果集的筛选，各自索引无法奏效。 这个场景中索引是无法消除排序的，在一些两个表根本就不可能有重复的场景中个，使用UNION修改位UNION ALL。 6. 主外键设计主外键有三大特点：主键本身是一种索引；可以保证表中主键所在列的唯一性；可以有效地限制外键依赖的表的记录的完整性。前两个特点和 CREATE UNIQUE INDEX建立的唯一性索引相同。 6.1外键上的索引与性能SQL> create table t_p (id number,namevarchar2(30)); SQL> alter table t_p add constraintt_p_id_pk primary key (id); SQL> create table t_c (id number,fidnumber,name varchar2(30)); SQL> alter table t_c add constraintfk_t_c foreign key (fid) references t_p (id); SQL> insert into t_p selectrownum,table_name from all_tables; SQL> insert into t_c selectrownum,mod(rownum,1000)+1,object_name from all_objects; Commit; T_p表有主键，T_C表有外键。 执行如下： Set autotrace traceonly Set linesize 1000 SQL> select a.id,a.name,b.name from t_p a,t_c b where a.id=b.fidand a.id=880; 89 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 727955870 ------------------------------------------------------------------------------------------ | Id| Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0| SELECT STATEMENT | | 79 |4740 | 136 (0)| 00:00:01 | | 1| NESTED LOOPS | | 79 |4740 | 136 (0)| 00:00:01 | | 2| TABLE ACCESS BY INDEX ROWID| T_P |1 | 30 | 0(0)| 00:00:01 | |* 3| INDEX UNIQUE SCAN | T_P_ID_PK | 1 | |0 (0)| 00:00:01 | |* 4| TABLE ACCESS FULL | T_C | 79 |2370 | 136 (0)| 00:00:01 | ------------------------------------------------------------------------------------------ Predicate Information (identified byoperation id): --------------------------------------------------- 3- access("A"."ID"=880) 4- filter("B"."FID"=880) Note ----- -dynamic statistics used: dynamic sampling (level=2) Statistics ---------------------------------------------------------- 6recursive calls 1 dbblock gets 576 consistent gets 0physical reads 184 redo size 4822 bytes sent via SQL*Net toclient 606 bytes received via SQL*Net from client 7SQL*Net roundtrips to/from client 0sorts (memory) 0sorts (disk) 89 rowsprocessed 6.2建立外键索引观察SQL> create index ind_t_c_fid ont_c(fid); SQL> select a.id,t_c b where a.id=b.fidand a.id=880; 89 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3784248896 ---------------------------------------------------------------------------------------------------- | Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------- | 0| SELECT STATEMENT | | 89| 5340 | 115(0)| 00:00:01 | | 1 | NESTED LOOPS | | 89| 5340 | 115(0)| 00:00:01 | | 2| TABLE ACCESS BY INDEX ROWID | T_P | 1 | 30 | 0 (0)| 00:00:01 | |* 3| INDEX UNIQUE SCAN | T_P_ID_PK | 1 | | 0 (0)| 00:00:01 | | 4| TABLE ACCESS BY INDEX ROWID BATCHED|T_C | 89| 2670 | 115(0)| 00:00:01 | |* 5| INDEX RANGE SCAN | IND_T_C_FID | 89 | | 1 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------- Predicate Information (identified byoperation id): --------------------------------------------------- 3- access("A"."ID"=880) 5- access("B"."FID"=880) Note ----- -dynamic statistics used: dynamic sampling (level=2) Statistics ---------------------------------------------------------- 9recursive calls 0 dbblock gets 159 consistent gets 1physical reads 0 redosize 4822 bytes sent via SQL*Net toclient 606 bytes received via SQL*Net from client 7SQL*Net roundtrips to/from client 0sorts (memory) 0sorts (disk) 89 rowsprocessed 发现键索引后，逻辑读从 576 降低到了 159. 这个 和NESTED LOOPS链接方式有关。 外键索引是很有用的。 6.3外键索引外键索引还能有效避免锁的竞争。 SQL> select sid from v$mystat where rownum=1; SID ---------- 83 SQL>delete t_c where id=2; 1 row deleted. 开启第二个回话，就是一个新的链接 SQL> select sid from v$mystat where rownum=1; SID ---------- 84 SQL> delete t_p where id=2000; 1 row deleted. 本来很可能锁住，但是因为外键所在的列建了索引，所以避免了。 都执行rollback,然后删除外键索引。 6.4删除外键索引SQL> drop index ind_t_c_fid; 在第一个会话中执行： SQL> delete t_c where id=2; 在第二个会话中执行： SQL> delete t_p where id=2000; 发现卡主了。 外键所在的表，因为删除一条记录而导致T_P所在的表完全锁住，无法做任何DML更新操作。 外键所在表的外键列取值必须在主表中的主键列有记录。 FID是依赖于T_P的主键的。 SQL> select count(*) from t_c where fid=2; COUNT(*) ---------- 90 有90条记录依赖于主表ID=2的记录，先要删除外键中的FID=2记录，然后主表T_P就可轻易删除。 SQL> delete from t_c where fid=2; 90 rows deleted. SQL> commit; Commit complete. SQL> delete t_p where id=2; 1 row deleted. SQL> commit; 这些保证了夺标记录之间记录的制约性。 6.4.1级联删除级联删除，在原先增加外键的基础上增加ON DELETE CASCADE关键字即可。 示例如下： SQL> alter table t_c drop constraintfk_t_c; Table altered. SQL> alter table t_c add constraintfk_t_c foreign key (fid) references t_p (id) on delete cascade; Table altered. SQL> select count(*) from t_c where fid=3; COUNT(*) ---------- 90 SQL> delete from t_p where id=3; 1 row deleted. SQL>commit; 设置级联删除设置后，自动删除了t_c表中的55条记录。 SQL>select count(*) from t_c where fid=3; COUNT(*) ---------- 0 这个技术比较危险，一定要慎用。 6.4.2改造列为主键如果生产系统有一张大表的某字段符合主键的条件。可以删除该列索引，然后建上主键。 这个方法需要在没人使用系统的时候进行操作。 其实可以很方便的完成如下： SQL> droptable t cascade constraints purge; SQL>create table t (id number,name varchar2(30)); SQL> insert into t selectrownum,table_name from all_tables; 2426 rows created. SQL> commit; Commit complete. SQL> create index idx_t_id on t(id); 然后为ID列增加主键索引，如下： Alter table t add constraint t_id_pkprimary key (id); （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |