Heim  >  Artikel  >  Datenbank  >  组合索引如何选择前导列的几点考虑

组合索引如何选择前导列的几点考虑

WBOY
WBOYOriginal
2016-06-07 16:37:501556Durchsuche

? 选择组合索引的前导列,必须根据具体的业务(SQL)写法和列的数据分布不同而不同,很多书或网上都说,前导列要选择高选择性的,但是,脱离具体的业务,这些是没有意义的, 本文就举一些常见的例子来分析下如何正确选择前导列,以抛砖引玉,实际应用中,有更

?选择组合索引的前导列,必须根据具体的业务(SQL)写法和列的数据分布不同而不同,很多书或网上都说,前导列要选择高选择性的,但是,脱离具体的业务,这些是没有意义的,本文就举一些常见的例子来分析下如何正确选择前导列,以抛砖引玉,实际应用中,有更多复杂的情况需要具体分析。

1.都是等值条件的列,谁做前导列都一样

DROP TABLE t;
CREATE TABLE t?
AS
SELECT * FROM dba_objects;
CREATE INDEX idx1_t ON t(owner,object_id);
CREATE INDEX idx2_t ON t(object_id,owner);
BEGIN
? dbms_stats.gather_table_stats(ownname => USER,tabname => ‘T’,estimate_percent => 100,cascade => TRUE);
END;
?/
dingjun123@ORADB> ?SELECT COUNT(DISTINCT owner),COUNT(DISTINCT object_id),COUNT(*) FROM t;
COUNT(DISTINCTOWNER) COUNT(DISTINCTOBJECT_ID) ? COUNT(*)
——————– ———————— ———-
? ? ? ? ? ? ? ? ? 33 ? ? ? ? ? ? ? ? ? ?75250 ? ? ?75251
1 row selected.

owner有33个不同的值,object_id有75250,显然object_id的选择性更好。但是下面的查询,应用idx1_t与idx2_t的性能一样(COST与CONSISTENT GETS一样)。

dingjun123@ORADB> ?SELECT/*+index(t idx1_t)*/ * FROM t
? 2 ? WHERE owner=’DINGJUN123′ AND object_id=75677;
1 row selected.
Elapsed: 00:00:00.00
Execution Plan
———————————————————-
Plan hash value: 2071967826
————————————————————————————–
| Id ?| Operation ? ? ? ? ? ? ? ? ? | Name ? | Rows ?| Bytes | Cost (%CPU)| Time ? ? |
————————————————————————————–
| ? 0 | SELECT STATEMENT ? ? ? ? ? ?| ? ? ? ?| ? ? 1 | ? ?97 | ? ??2?? (0)| 00:00:01 |
| ? 1 | ?TABLE ACCESS BY INDEX ROWID| T ? ? ?| ? ? 1 | ? ?97 | ? ? 2 ? (0)| 00:00:01 |
|* ?2 | ? INDEX RANGE SCAN ? ? ? ? ?| IDX1_T | ? ? 1 | ? ? ? | ? ? 1 ? (0)| 00:00:01 |
————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
? ?2 – access(“OWNER”=’DINGJUN123′ AND “OBJECT_ID”=75677)
Statistics
———————————————————-
? ? ? ? ? 1 ?recursive calls
? ? ? ? ? 0 ?db block gets
? ? ? ? ??4 ?consistent gets
? ? ? ? ? 0 ?physical reads
? ? ? ? ? 0 ?redo size
? ? ? ?1403 ?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 processeddingjun123@ORADB> ? SELECT/*+index(t idx2_t)*/ * FROM t
? 2 ? WHERE owner=’DINGJUN123′ AND object_id=75677;
1 row selected.
Elapsed: 00:00:00.01
Execution Plan
———————————————————-
Plan hash value: 3787301248
————————————————————————————–
| Id ?| Operation ? ? ? ? ? ? ? ? ? | Name ? | Rows ?| Bytes | Cost (%CPU)| Time ? ? |
————————————————————————————–
| ? 0 | SELECT STATEMENT ? ? ? ? ? ?| ? ? ? ?| ? ? 1 | ? ?97 | ??? 2?? (0)| 00:00:01 |
| ? 1 | ?TABLE ACCESS BY INDEX ROWID| T ? ? ?| ? ? 1 | ? ?97 | ? ? 2 ? (0)| 00:00:01 |
|* ?2 | ? INDEX RANGE SCAN ? ? ? ? ?| IDX2_T | ? ? 1 | ? ? ? | ? ? 1 ? (0)| 00:00:01 |
————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
? ?2 – access(“OBJECT_ID”=75677 AND “OWNER”=’DINGJUN123′)
Statistics
———————————————————-
? ? ? ? ? 1 ?recursive calls
? ? ? ? ? 0 ?db block gets
? ? ? ??? 4 ?consistent gets
? ? ? ? ? 0 ?physical reads
? ? ? ? ? 0 ?redo size
? ? ? ?1403 ?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

? ? ??虽然如此,但是要记住,这个索引不是仅仅给这2条SQL使用的,事实上可能我们有的查询谓词只有owner或object_id,这时候得考虑使用owner作为前导列还是使用object_id作为前导列。
? ??还有其他引用owner,object_id的情况,比如GROUP BY ,ORDER BY,甚至SELECT…都需要进行整体的分析,这样才能建立最佳的索引。
?
2.有的列是大于(等于)或小于(等于)或者是like 模糊匹配等不等条件,有的列是等值的条件,等值的一般作为前导列更好
–做5次,增加几十万行SYS的进去
INSERT INTO t SELECT * FROM t WHERE owner=’SYS’;
COMMIT;
–重新收集统计信息(省略)

dingjun123@ORADB> SELECT * FROM t
? 2 ?WHERE owner=’DINGJUN123′
? 3 ?AND object_id>=107889;
1 row selected.
Elapsed: 00:00:00.01
Execution Plan
———————————————————-
Plan hash value: 2071967826
————————————————————————————–
| Id ?| Operation ? ? ? ? ? ? ? ? ? | Name ? | Rows ?| Bytes | Cost (%CPU)| Time ? ? |
————————————————————————————–
| ? 0 | SELECT STATEMENT ? ? ? ? ? ?| ? ? ? ?| ? ? 1 | ? ?96 | ? ? 4 ? (0)| 00:00:01 |
| ? 1 | ?TABLE ACCESS BY INDEX ROWID| T ? ? ?| ? ? 1 | ? ?96 | ? ? 4 ? (0)| 00:00:01 |
|*??2 | ? INDEX RANGE SCAN ? ? ? ? ?| IDX1_T | ? ? 1 | ? ? ? | ? ? 3 ? (0)| 00:00:01?|
————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
???2 – access(“OWNER”=’DINGJUN123′ AND “OBJECT_ID”>=107889 AND “OBJECT_ID” IS
? ? ? ? ? ? ? NOT NULL)
Statistics
———————————————————-
? ? ? ? ? 1 ?recursive calls
? ? ? ? ? 0 ?db block gets
? ? ? ??? 5 ?consistent gets
? ? ? ? ? 0 ?physical reads
? ? ? ? ? 0 ?redo size
? ? ? ?1399 ?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

? ? ??上面的SQL走idx1_t,注意观察谓词,只有access,说明索引完全被利用上,很显然因为owner是前导列,而且是等值查询,按照前导列查询,然后只要分析索引的第2列object_id,当发现不满足条件object_id>=107889之后就停止了,索引扫描没有浪费。

dingjun123@ORADB> SELECT/*+index(t idx2_t)*/ * FROM t
? 2 ?WHERE owner=’DINGJUN123′
? 3 ?AND object_id>=107889;
1 row selected.
Elapsed: 00:00:00.01
Execution Plan
———————————————————-
Plan hash value: 3787301248
————————————————————————————–
| Id ?| Operation ? ? ? ? ? ? ? ? ? | Name ? | Rows ?| Bytes | Cost (%CPU)| Time ? ? |
————————————————————————————–
| ? 0 | SELECT STATEMENT ? ? ? ? ? ?| ? ? ? ?| ? ? 1 | ? ?96 | ? ? 4 ? (0)| 00:00:01 |
| ? 1 | ?TABLE ACCESS BY INDEX ROWID| T ? ? ?| ? ? 1 | ? ?96 | ? ? 4 ? (0)| 00:00:01 |
|* ?2 | ? INDEX RANGE SCAN ? ? ? ? ?| IDX2_T | ? ? 1 | ? ? ? | ? ? 3 ? (0)| 00:00:01 |
————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
? ?2 – access(“OBJECT_ID”>=107889 AND “OWNER”=’DINGJUN123′ AND “OBJECT_ID” IS
? ? ? ? ? ? ? NOT NULL)
? ??? ?filter(“OWNER”=’DINGJUN123′)
Statistics
———————————————————-
? ? ? ? ? 1 ?recursive calls
? ? ? ? ? 0 ?db block gets
? ? ? ? ??5 ?consistent gets
? ? ? ? ? 0 ?physical reads
? ? ? ? ? 0 ?redo size
? ? ? ?1399 ?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

? ? ??强制使用idx2_t,object_id是前导列,谓词有access,还有filter,说明索引没有被完全利用上,这是因为object_id的不是等值查询,满足object_id>=107889的,按照顺序搜索
所以,中间可能有一些不满足owner=’DINGJUN123′的,还要filter掉。
这种查询和不等值条件作为前导列的查询,一旦object_id>=107889不满足owner=’DINGJUN123′的很多,那么必然造成过多不必要的索引搜索,COST与逻辑读会上升很快,
从而性能急剧下降,因为本例子基本都满足owner条件,所以没有啥浪费。但是下面的例子

dingjun123@ORADB> SELECT * FROM t
? 2 ?WHERE owner=’DINGJUN123′
? 3 ?AND object_id>=100;
2540 rows selected.
Elapsed: 00:00:00.15
Execution Plan
———————————————————-
Plan hash value: 2071967826
————————————————————————————–
| Id ?| Operation ? ? ? ? ? ? ? ? ? | Name ? | Rows ?| Bytes | Cost (%CPU)| Time ? ? |
————————————————————————————–
| ? 0 | SELECT STATEMENT ? ? ? ? ? ?| ? ? ? ?| ?2539 | ? 238K| ? 499 ? (0)| 00:00:06 |
| ? 1 | ?TABLE ACCESS BY INDEX ROWID| T ? ? ?| ?2539 | ? 238K| ? 499 ? (0)| 00:00:06 |
|* ?2 | ? INDEX RANGE SCAN ? ? ? ? ?| IDX1_T | ?2539 | ? ? ? | ? ?12 ? (0)| 00:00:01 |
————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
? ?2 – access(“OWNER”=’DINGJUN123′ AND “OBJECT_ID”>=100 AND “OBJECT_ID” IS
? ? ? ? ? ? ? NOT NULL)
Statistics
———————————————————-
? ? ? ? ? 1 ?recursive calls
? ? ? ? ? 0 ?db block gets
? ? ? ??527 ?consistent gets
? ? ? ? ?21 ?physical reads
? ? ? ? ? 0 ?redo size
? ? ?268134 ?bytes sent via SQL*Net to client
? ? ? ?2275 ?bytes received via SQL*Net from client
? ? ? ? 171 ?SQL*Net roundtrips to/from client
? ? ? ? ? 0 ?sorts (memory)
? ? ? ? ? 0 ?sorts (disk)
? ? ???2540 ?rows processed

还是使用idx1_t,没有问题。看下面的强制使用idx2_t的。

dingjun123@ORADB> SELECT/*+index(t idx2_t)*/ * FROM t
? 2 ?WHERE owner=’DINGJUN123′
? 3 ?AND object_id>=100;2540 rows selected.
Elapsed: 00:00:00.33
Execution Plan
———————————————————-
Plan hash value: 3787301248
————————————————————————————–
| Id ?| Operation ? ? ? ? ? ? ? ? ? | Name ? | Rows ?| Bytes | Cost (%CPU)| Time ? ? |
————————————————————————————–
| ? 0 | SELECT STATEMENT ? ? ? ? ? ?| ? ? ? ?| ?2539 | ? 238K| ?3762 ? (1)| 00:00:46 |
| ? 1 | ?TABLE ACCESS BY INDEX ROWID| T ? ? ?| ?2539 | ? 238K| ?3762 ? (1)| 00:00:46 |
|* ?2 | ? INDEX RANGE SCAN ? ? ? ? ?| IDX2_T | ?2539 | ? ? ? | ?3274 ? (1)| 00:00:40 |
————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
? ?2 – access(“OBJECT_ID”>=100 AND “OWNER”=’DINGJUN123′ AND “OBJECT_ID” IS
? ? ? ? ? ? ? NOT NULL)
? ? ???filter(“OWNER”=’DINGJUN123′)
Statistics
———————————————————-
? ? ? ? ? 1 ?recursive calls
? ? ? ? ? 0 ?db block gets
? ? ???3763 ?consistent gets
? ? ? ? ? 0 ?physical reads
? ? ? ? ? 0 ?redo size
? ? ?268134 ?bytes sent via SQL*Net to client
? ? ? ?2275 ?bytes received via SQL*Net from client
? ? ? ? 171 ?SQL*Net roundtrips to/from client
? ? ? ? ? 0 ?sorts (memory)
? ? ? ? ? 0 ?sorts (disk)
? ? ? ?2540 ?rows processed

dingjun123@ORADB> SELECT COUNT(*) FROM t WHERE object_id >= 100;
? COUNT(*)
———-
? ?1032649

?SELECT COUNT(*) FROM t WHERE object_id >= 100;
返回1032649行,但是WHERE owner=’DINGJUN123′ AND object_id>=100 只返回2540行,要filter掉百万行,轮询索引,造成了极大的浪费。

3.如果都是比较,都是之类的表达式
??这种情况,前导列,根据谓词,选择条件能够定位最接近处理结果的基数,并能够减少索引后filter的工作,因为必然有一列是要走access之后的filter,最好是filter能够过滤较少数据,不要做过多过滤。
??
例如:

dingjun123@ORADB> SELECT * FROM t
? 2 ?WHERE owner>=’DINGJUN123′
? 3 ?AND object_id>=107872;37 rows selected.
Elapsed: 00:00:00.00
Execution Plan
———————————————————-
Plan hash value: 3787301248
————————————————————————————–
| Id ?| Operation ? ? ? ? ? ? ? ? ? | Name ? | Rows ?| Bytes | Cost (%CPU)| Time ? ? |
————————————————————————————–
| ? 0 | SELECT STATEMENT ? ? ? ? ? ?| ? ? ? ?| ? 205 | 19680 | ???43?? (0)| 00:00:01 |
| ? 1 | ?TABLE ACCESS BY INDEX ROWID| T ? ? ?| ? 205 | 19680 | ? ?43 ? (0)| 00:00:01 |
|* ?2 | ??INDEX RANGE SCAN ? ? ? ? ?| IDX2_T?| ? 205 | ? ? ? | ? ? 3 ? (0)| 00:00:01 |
————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
? ?2 – access(“OBJECT_ID”>=107872 AND “OWNER”>=’DINGJUN123′ AND “OBJECT_ID”
? ? ? ? ? ? ? IS NOT NULL)
? ? ? ?filter(“OWNER”>=’DINGJUN123′)

Statistics
———————————————————-
? ? ? ? ? 1 ?recursive calls
? ? ? ? ? 0 ?db block gets
? ? ? ? ?14 ?consistent gets
? ? ? ? ? 0 ?physical reads
? ? ? ? ? 0 ?redo size
? ? ? ?6468 ?bytes sent via SQL*Net to client
? ? ? ? 438 ?bytes received via SQL*Net from client
? ? ? ? ? 4 ?SQL*Net roundtrips to/from client
? ? ? ? ? 0 ?sorts (memory)
? ? ? ? ? 0 ?sorts (disk)
? ? ? ? ?37 ?rows processed

dingjun123@ORADB> SELECT COUNT(*) FROM t WHERE object_id>=107872;
? COUNT(*)
———-
? ? ? ? 37
1 row selected.

??关闭index SKIP SCAN,因为owner种类很少,oracle选择skip SCAN
?alter session set “_optimizer_skip_scan_enabled” = false;
?

dingjun123@ORADB> SELECT/*+index(t idx1_t)*/ * FROM t
? 2 ?WHERE owner>=’DINGJUN123′
? 3 ?AND object_id>=107872;
37 rows selected.
Elapsed: 00:00:00.23
Execution Plan
———————————————————-
Plan hash value: 2071967826
————————————————————————————–
| Id ?| Operation ? ? ? ? ? ? ? ? ? | Name ? | Rows ?| Bytes | Cost (%CPU)| Time ? ? |
————————————————————————————–
| ? 0 | SELECT STATEMENT ? ? ? ? ? ?| ? ? ? ?| ? 205 | 19680 | ?3740?? (1)| 00:00:45 |
| ? 1 | ?TABLE ACCESS BY INDEX ROWID| T ? ? ?| ? 205 | 19680 | ?3740 ? (1)| 00:00:45 |
|* ?2 | ? INDEX RANGE SCAN ? ? ? ? ?| IDX1_T | ? 205 | ? ? ? | ?3700 ? (1)| 00:00:45 |
————————————————————————————–Predicate Information (identified by operation id):
—————————————————
? ?2 – access(“OWNER”>=’DINGJUN123′ AND “OBJECT_ID”>=107872 AND “OWNER” IS
? ? ? ? ? ? ? NOT NULL)
? ? ? ?filter(“OBJECT_ID”>=107872)
Statistics
———————————————————-
? ? ? ? ? 1 ?recursive calls
? ? ? ? ? 0 ?db block gets
? ? ???3688 ?consistent gets
? ? ? ? ? 0 ?physical reads
? ? ? ? ? 0 ?redo size
? ? ? ?6468 ?bytes sent via SQL*Net to client
? ? ? ? 438 ?bytes received via SQL*Net from client
? ? ? ? ? 4 ?SQL*Net roundtrips to/from client
? ? ? ? ? 0 ?sorts (memory)
? ? ? ? ? 0 ?sorts (disk)
? ? ? ? ?37 ?rows processed

因为owner>=’DINGJUN123′返回大量行,但是事实结果很少,只有几十行,过滤object_id>=107872,需要做大量工作,逻辑读和COST增大千倍+,性能低下。

后记:
? ? ?当然如何选择前导列的顺序很复杂,得全盘考虑对应的谓词,SELECT的列等要素,还要考虑ORDER BY ,GROUP BY等列,比如3列组合索引,如何考虑顺序。
? ? ?后续会补充更多的组合索引如何创建的要点。

Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn