23.读书笔记收获不止Oracle之 组合索引
索引在适当的场合能避免回表。考虑多列删上的索引,就是联合索引。
回表的动作叫做:TABLEACCESS BY INDEX ROWID。非常直白。
1. 组合列返回越少越高效
如果a字段上查询返回的记录比较多,b字段上查询返回的字段也比较多,如果a和b同时查询,返回的记录比较少,那就适合建联合索引了。
查询select * fromt where a=1 and b=2,在a和b字段建联合索引是不可能消除回表的,因为返回的是所有字段。但是如果 a=某值返回较多, b=某值,返回也较多,组合起来返回比较少,适合建联合索引。
过多字段建联合索引往往是不可取的,因为这样的索引必然过大,影响定位数据,影响更新性能,一般不宜超过3个字段组合。
2. 组合列谁在前
索引两列,谁适合放钱?举个例子如下:
SQL>drop tablet purge;
Table dropped.
SQL> createtable t as select * from dba_objects;
建立两个组合索引
Create index idx1_object_idon t(object_id,object_type);
Create index idx2_object_idon t(object_type,object_id);
进行查询对比:
2.1 等值查询
SQL> set autotracetraceonly
SQL> select/*+index(t,idx1_object_id)*/ * from t where object_id=20 andobject_type='TABLE';
ExecutionPlan
----------------------------------------------------------
Planhash value: 2919362295
--------------------------------------------------------------------------------
----------------------
|Id | Operation | Name | Rows | Bytes | C
ost (%CPU)|Time |
--------------------------------------------------------------------------------
----------------------
| 0 | SELECT STATEMENT | | 1 | 115 |
2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 115 |
2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX1_OBJECT_ID | 1 | |
1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
----------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=20 AND"OBJECT_TYPE"='TABLE')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
5 physical reads
0 redo size
1863 bytes sent via SQL*Net to client
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select/*+index(t,idx2_object_id)*/ * from t where object_id=20 andobject_type='TABLE';
ExecutionPlan
----------------------------------------------------------
Planhash value: 957115215
--------------------------------------------------------------------------------
----------------------
|Id | Operation | Name | Rows | Bytes | C
ost(%CPU)| Time |
--------------------------------------------------------------------------------
----------------------
| 0 | SELECT STATEMENT | | 1 | 115 |
2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 115 |
2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX2_OBJECT_ID | 1 | |
1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
----------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='TABLE'AND "OBJECT_ID"=20)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
1 physical reads
0 redo size
1863 bytes sent via SQL*Net to client
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
1 rows processed
在等值查询情况下,组合索引的列无论哪一列在前,性能都一样。
2.2 范围查询
SQL> select/*+index(t,idx1_object_id)*/ * from t where object_id>20 andobject_id<2000 and object_type='TABLE';
589 rowsselected.
ExecutionPlan
----------------------------------------------------------
Planhash value: 2919362295
--------------------------------------------------------------------------------
----------------------
|Id | Operation | Name | Rows | Bytes | C
ost(%CPU)| Time |
--------------------------------------------------------------------------------
----------------------
| 0 | SELECT STATEMENT | | 42 | 4830|
10 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 42 | 4830|
10 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX1_OBJECT_ID | 42 | |
8 (0)| 00:00:01 |
--------------------------------------------------------------------------------
----------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">20 AND"OBJECT_TYPE"='TABLE' AND "OBJECT_ID"<2000)
filter("OBJECT_TYPE"='TABLE')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
108 consistentgets
23 physical reads
0 redo size
72752 bytes sent via SQL*Net to client
980 bytes received via SQL*Net from client
41 SQL*Netroundtrips to/from client
0 sorts (memory)
0 sorts (disk)
589 rows processed
SQL> select/*+index(t,idx2_object_id)*/ * from t where object_id>20 andobject_id<2000 and object_type='TABLE';
589 rowsselected.
ExecutionPlan
----------------------------------------------------------
Planhash value: 957115215
--------------------------------------------------------------------------------
----------------------
|Id | Operation | Name | Rows | Bytes | C
ost(%CPU)| Time |
--------------------------------------------------------------------------------
----------------------
| 0 | SELECT STATEMENT | | 42 | 4830|
4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 42 | 4830|
4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX2_OBJECT_ID | 42 | |
2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
----------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='TABLE'AND "OBJECT_ID">20 AND "OBJECT_ID"<2000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
105 consistentgets
4 physical reads
0 redo size
72752 bytes sent via SQL*Net to client
980 bytes received via SQL*Net from client
41 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
589 rows processed
组合索引的两列,当一列是范围查询,一列是等值查询的情况下,等值查询列在前,范围查询列在后,这样索引才高效。
2.3 原理解释
某t表,AREA_CODE表示地区号,大量重复。另一个字段ID为序列号,重复度很少。
2.3.1 等值查询性能一样
针对SELECT * FROM TWHERE AREA_CODE=591 AND ID=101查询,
索引是AREA_CODE+ID高效还是ID+AREA_CODE高效。
先看下AREA_CODE+ID组合如下图1
AREA_CODE+ID这个索引,按AREA_CODE进行排序,在AREA_CODE相同的情况下,按照ID进行二次排序。
在步骤一中根据索引定位的原理,定位到AREA_CODE=591,ID=101,找到ROWID,然后是ID=102,因为ID列也是有序的,因此查询完毕。
关于ID+AREA_CODE组合,如下图2:
ID+AREA_CODE这个索引同样具有明显的特征。
先找到ID=101和AREA_CODE=591这一行。然后AREA_CODE=592了,因为AREA_CODE也是有序的,说明不会再出现了,查询完毕。
2.3.2 范围查询性能不一样
比如:SELECT * FROM TWHERE AREA_CODE=591 AND ID>=98 AND COL2<=105
先来看下AREA_CODE+ID的组合情况:
快速定位到AREA_CODE=591和ID=99这一行记录,然后在满足AREA_CODE=591的前提下,定位到满足条件的AREA_CODE=591和ID=100,AREA_CODE=591和ID=101等记录。当查询到AREA+591,ID=106后,发现没必要走下去了,因为ID=105不可能再出现了。
如下图3:
再讨论ID+AREA_CODE这个组合
先定位到ID=99和AREA_CODE=591,由于ID列不是等值而是范围,在ID>=98和ANDCOL2<=105的条件下,AREA_CODE定位到AREA_CODE=592后,前进的步伐无法停止。
因为在一下个区域内,比如ID=101后,完全可能再出现591,如下图4:
2.4 组合查询改写
Droptable t purge;
SQL>Create table t as select * from dba_objects where rownum<1000;
Tablecreated.
SQL>update t set object_id=rownum;
999 rowsupdated.
SQL>update t set object_id=20 where rownum<=600;
600 rowsupdated.
SQL>Update t set object_id=21 where object_id<>20;
399 rowsupdated.
SQL>Commit;
SQL>Createindex idx1_object_id on t(object_id,object_type);
SQL>setautotrace traceonly
SQL>Setlinesize 1000
SQL> Select/*+index(t,idx1_object_id)*/ * from t where object_type='TABLE' andOBJECT_ID>=20 and OBJECT_ID<=21;
389 rowsselected.
ExecutionPlan
----------------------------------------------------------
Planhash value: 2919362295
------------------------------------------------------------------------------------------------------
|Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 97 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 97 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX1_OBJECT_ID | 1 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">=20 AND"OBJECT_TYPE"='TABLE' AND "OBJECT_ID"<=21)
filter("OBJECT_TYPE"='TABLE')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
70 consistent gets
4 physical reads
0 redo size
47736 bytes sent via SQL*Net to client
826 bytes received via SQL*Net from client
27 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
389 rows processed
SQL> Select/*+index(t,idx1_object_id)*/ * from t where object_type='TABLE' and object_idin (20,21);
389 rowsselected.
ExecutionPlan
----------------------------------------------------------
Planhash value: 547203187
-------------------------------------------------------------------------------------------------------
|Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 167 | 16199 | 13 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 167 | 16199 | 13 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX1_OBJECT_ID | 167 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
3 - access(("OBJECT_ID"=20 OR"OBJECT_ID"=21) AND "OBJECT_TYPE"='TABLE')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
71 consistentgets
0 physical reads
0 redo size
47736 bytes sent via SQL*Net to client
826 bytes received via SQL*Net from client
27 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
389 rows processed
这边行数太少,看不到明显效果,大家可以把行数设置的足够大进行尝试。
使用IN改写的性能。
2.5 设计考虑单列的查询
当两个索引列都是等值查询时,无论哪一列在前都无所谓,性能都一样。如果涉及两索引列中的一列是等值查询而另一列是范围查询时,等值查询列在前的组合索引更高效。
单列查询,例如select * from t where object_id=2 and object_type=’TABLE’是两列查询,而select* from t where object_id=2就是单列的查询。
2.5.1 试验1
Drop tablet purge;
Createtable t as select * from dba_objects;
Createindex idx_object_id on t (object_id,object_type);
Setautotrace traceonly;
Setlinesize 1000
SQL> Select * from t whereobject_id=19;
ExecutionPlan
----------------------------------------------------------
Planhash value: 1296629646
-----------------------------------------------------------------------------------------------------
|Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 115| 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 115| 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OBJECT_ID | 1 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=19)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
4 physical reads
0 redo size
1862 bytes sent via SQL*Net to client
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
1 rows processed
2.5.2 试验2
Dropindex idx_object_id;
Createindex idx_object_id on t(object_type,object_id);
SQL> Select * from t whereobject_id=19;
ExecutionPlan
----------------------------------------------------------
Planhash value: 974686798
-----------------------------------------------------------------------------------------------------
|Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 115| 46 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 115| 46 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | IDX_OBJECT_ID | 1 | | 45 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
PredicateInformation (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=19)
filter("OBJECT_ID"=19)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
26 consistent gets
49 physical reads
0 redo size
1862 bytes sent via SQL*Net to client
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
1 rows processed
如果单列的查询列和联合索引的前置列一样,那单列就可以不建索引,直接利用联合索引来进行检索数据。试验2使用了跳跃索引INDEX SKIP SCAN
如果是偶尔执行又不是非常重要的查询,不会考虑为这些SQL语句的查询性能提升而建特定的索引的。