關於組合索引的使用一直都存在著一些爭議，記得在學OU的Performance Tuning時，講師認為組合索引的使用存在著一定的侷限，只有在謂詞中出現全部索引列時才能使用效率最高的index unique scan, 否則謂詞中必須包含前導列，否則會走Index full scan或者FTS。

kl@k02> create table te_emp as select * from hr.employees;

Table created.

kl@k02> create index emp_id1 on te_emp(employee_id,JOB_ID, department_id);

Index created.

kl@k02> create index emp_id2 on te_emp(salary);

Index created.

kl@k02> analyze table te_emp compute statistics for table for all columns for all indexes;

Table analyzed.

kl@k02> set autotrace traceonly exp;

kl@k02> select employee_id,JOB_ID, department_id from te_emp;
(不加謂詞，走EMP_ID1的FULL SCAN)
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=107 Bytes=1391)
   1    0   INDEX (FULL SCAN) OF 'EMP_ID1' (NON-UNIQUE) (Cost=1 Card=107 Bytes=1391)

kl@k02> select employee_id,JOB_ID, department_id from te_emp where employee_id=9293;
(加謂詞前導列Employee_id時，走EMP_ID1 range scan)
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=13)
   1    0   INDEX (RANGE SCAN) OF 'EMP_ID1' (NON-UNIQUE) (Cost=1 Card=1 Bytes=13)

kl@k02> select employee_id,JOB_ID, department_id from te_emp where employee_id=1334 and JOB_ID=435 and department_id=273;
(如謂詞中包含全部索引列，走EMP_ID1 Range scan)
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=13)
   1    0   INDEX (RANGE SCAN) OF 'EMP_ID1' (UNIQUE) (Cost=1 Card=1 Bytes=13)

kl@k02> set autotrace off;
kl@k02> select count(*) from (select distinct employee_id from te_emp);

COUNT(*)
----------
       107

kl@k02> select count(*) from te_emp;

COUNT(*)
----------
       107

kl@k02> create index emp_id3 on te_emp(employee_id);
(此時建立一個單列索引emp_id3, 目前組合索引emp_id1仍然存在)
Index created.

kl@k02> analyze table te_emp compute statistics for table for all columns for all indexes;

Table analyzed.

kl@k02> select employee_id from te_emp where employee_id=107;
(並沒有走emp_id3, 而是emp_id1的INDEX RANGE SCAN)
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=3)
   1    0   INDEX (RANGE SCAN) OF 'EMP_ID1' (UNIQUE) (Cost=1 Card=1 Bytes=3)

kl@k02> drop index emp_id1;

Index dropped.

kl@k02> select employee_id from te_emp where employee_id=107;
(drop原來的聯合索引，用到了emp_id3,但是還是INDEX RANGE SCAN)
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=3)
   1    0   INDEX (RANGE SCAN) OF 'EMP_ID3' (NON-UNIQUE) (Cost=1 Card=1 Bytes=3)

(現在測試一下，如果組合index是unique index，會如何?)

kl@k02> drop index emp_id3;

Index dropped.

kl@k02> create unique index emp_id1 on te_emp(employee_id,JOB_ID, department_id);

Index created.

kl@k02> analyze table te_emp compute statistics for table for all columns for all indexes;

Table analyzed.

kl@k02> select * from te_emp where employee_id=107 and JOB_ID='ENGINEER' and department_id=10;
(此時走了unique scan,也就是說只有這種情況聯合索引的作用才發揮出來)
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=62)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TE_EMP' (Cost=1 Card=1 Bytes=62)
   2    1     INDEX (UNIQUE SCAN) OF 'EMP_ID1' (UNIQUE)

kl@k02> select * from te_emp where employee_id=107;
(此時謂詞內只有前導列，不會走unique scan)
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=62)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TE_EMP' (Cost=2 Card=1 Bytes=62)
   2    1     INDEX (RANGE SCAN) OF 'EMP_ID1' (NON-UNIQUE) (Cost=1 Card=1)

kl@k02> select * from te_emp where employee_id=107 and JOB_ID=999 and department_id=10;
(JOB_ID應該是Varchar型的，結果導致整個查詢無法走Unique scan)
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=1 Bytes=62)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TE_EMP' (Cost=2 Card=1 Bytes=62)
   2    1     INDEX (RANGE SCAN) OF 'EMP_ID1' (UNIQUE) (Cost=1 Card=1)

kl@k02> select * from te_emp where employee_id='TEST' and JOB_ID='TEST' and department_id=10;
(如果前導列型別出錯了，索引掃描型別還是一樣，UNIQUE SCAN)
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=62)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TE_EMP' (Cost=1 Card=1 Bytes=62)
   2    1     INDEX (UNIQUE SCAN) OF 'EMP_ID1' (UNIQUE)

總結，針對組合索引，比較適宜的情況是組合列唯一，這種情況下對組合列建組合UNIQUE索引受益最大，一次索引掃描就可以了。但如果其中非前導列型別出錯，索引UNIQUE掃描將變成RANGE SCAN；有趣的是如果前導列型別不對，甚至為null，都不影響組合索引的UNIQUE SCAN.

組合索引的前導列與查詢——ORACLE

相關文章