在前段時(shí)間，由于項(xiàng)目的需要，在進(jìn)行大量數(shù)據(jù)的批處理時(shí)，須考慮如何提高效率，在經(jīng)過(guò)不斷的測(cè)試與研究后，發(fā)現(xiàn)在查詢數(shù)據(jù)庫(kù)時(shí)浪費(fèi)了太多時(shí)間，之前針對(duì)各種需要已經(jīng)創(chuàng)建了幾個(gè)索引，但所起效果不大，故便對(duì)索引進(jìn)行研究及探索，如何合理的創(chuàng)建索引將是本文探索的主要問(wèn)題。
測(cè)試環(huán)境：
操作系統(tǒng)：AIX 數(shù)據(jù)庫(kù):IBM DB2
為了便于測(cè)試，先創(chuàng)建一個(gè)表，并創(chuàng)建相關(guān)的索引以及插入一些數(shù)據(jù)。
表結(jié)構(gòu)如下所示：
describe table sygjj_test_index
Column Type Type
name schema name Length Scale Nulls
ID SYSIBM VARCHAR 4 0 No
USERNAME SYSIBM VARCHAR 20 0 Yes
SEX SYSIBM VARCHAR 50 0 Yes
AGE SYSIBM VARCHAR 4 0 Yes
CLASS SYSIBM VARCHAR 50 0 Yes
表索引結(jié)構(gòu)如下：
describe indexes for table sygjj_test_index show detail
Index Index Unique Number of Column
schema name rule columns names
AFA SY_TEST_KEY P 1+ID
AFA SY_TEST_IDX1 D 2 +USERNAME+CLASS
AFA SY_TEST_IDX2 D 1 +AGE
select * from sygjj_test_index
ID USERNAME SEX AGE CLASS
1 aa m 20 computer
2 aa m 20 computer
3 bb m 20 computer
4 aca f 23 computer
5 asa m 26 english
6 aaa m 20 computer
7 dd m 22 computer
探索查詢時(shí)何時(shí)會(huì)利用username+class這個(gè)索引
1. 當(dāng)查詢條件中包含這兩個(gè)字段時(shí)，且select 后面緊跟的字段也只有username和class或它們之中的一個(gè),這時(shí)索引都會(huì)起作用。
例如：select username,class from sygjj_test_index where username='aa' and class='computer'
2. 當(dāng)查詢條件中包含這兩個(gè)字段，但select后面緊跟的字段不只是username或class時(shí)，這時(shí)索引不起作用。
select * from sygjj_test_index where username='aa' and class='computer'
3. 當(dāng)查詢條件中只包含username時(shí)，且select 后面緊跟的字段也只有username和class或它們之中的一個(gè),這時(shí)索引都會(huì)起作用。
select class from sygjj_test_index where username='aa'
4. 當(dāng)查詢條件中只包含username時(shí)，但select后面緊跟的字段不只是username或class時(shí)，這時(shí)索引不起作用。
select username,class,age from sygjj_test_index where username='aa'
5. 當(dāng)查詢條伯中只包含class時(shí)，這時(shí)索引是不起作用的
select username,class from sygjj_test_index where class='computer'
6. 假如在這個(gè)表建一個(gè)由username+class+age三個(gè)列組成的索引。索引中的行是按username/class/age 的次序存放的，因此，索引中的行也會(huì)自動(dòng)按username/class 的順序和username 的順序存放。這表示，即使在查詢中只指定username的 值或只指定username 和class 的值，也可以利用此索引。因此，此索引可用來(lái)搜索下列的列組合：
username,class,age
username,class
username
不能使用不涉及左前綴的搜索。例如，如果class和age 進(jìn)行搜索，則不能使用該索引。如果要搜索某個(gè)用戶名以及某個(gè)年齡（索引中的列1和列3），則此索引不能用于相應(yīng)值的組合。但是，可利用索引來(lái)尋找與該用戶名相符的行，以減少搜索范圍。
7. 如果username+class這個(gè)索引是被定義為主鍵的話，這此時(shí)將不會(huì)出現(xiàn)上面那種情況。
只要查詢條件中緊跟username這個(gè)查詢條件，則無(wú)論何種組合這個(gè)索引都能起作用
例如：db2expln -d afa -o testsql1.txt -q "select * from sygjj_test_index where username=’aa’"
探索查詢時(shí)會(huì)不會(huì)聯(lián)合使用由單個(gè)列組成的索引
利用下面這個(gè)語(yǔ)句，可探索是否在查詢時(shí)使用了sy_test_key和sy_test_index2,由測(cè)試報(bào)告中可知該查詢使用這兩個(gè)索引
可利用這句代碼進(jìn)行測(cè)試，并觀看測(cè)試結(jié)果db2expln -d afa -o testsql1.txt -q "select * from sygjj_test_index where id='1' and age='20'"
而且使用這種索引不會(huì)被select后面緊跟的字段所限制，只要查詢條件中包含id或age，或包含它們兩個(gè)，索引都會(huì)起效果，由于建了這兩個(gè)索引之后，對(duì)查詢條件中包含它們兩個(gè)時(shí)索引會(huì)聯(lián)合使用，故可不必再建多一個(gè)由這兩個(gè)列組成的索引，因?yàn)楫?dāng)索引建得越多時(shí)，其所占的空間會(huì)越大，而且在插入或更新索引時(shí)的操作時(shí)間也會(huì)相應(yīng)增加。
測(cè)試報(bào)告如下：
db2expln -d afa -o testsql1.txt -q "select * from sygjj_test_index where id='1' and age='20'"
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool
******************** DYNAMIC ***************************************
==================== STATEMENT ==========================================
Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5
Partition Parallel = No
Intra-Partition Parallel = No
SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "AFA"
SQL Statement:
select *
from sygjj_test_index
where id='1'and age='20'
Section Code Page = 1386
Estimated Cost = 0.556049
Estimated Cardinality = 0.040000
Index ANDing
| Optimizer Estimate of Set Size: 1
| Index ANDing Bitmap Build Using Row IDs
| | Optimizer Estimate of Set Size: 1
| | Access Table Name = AFA.SYGJJ_TEST_INDEX ID = 2,31
| | | Index Scan: Name = AFA.SY_TEST_KEY ID = 1
| | | | Regular Index (Not Clustered)
| | | | Index Columns:
| | | | | 1: ID (Ascending)
| | | #Columns = 0
| | | Single Record
| | | Fully Qualified Unique Key
| | | #Key Columns = 1
| | | | Start Key: Inclusive Value
| | | | | | 1: '1'
| | | | Stop Key: Inclusive Value
| | | | | | 1: '1'
| | | Index-Only Access
| | | Index Prefetch: None
| | | Isolation Level: Uncommitted Read
| | | Lock Intents
| | | | Table: Intent None
| | | | Row : None
| Index ANDing Bitmap Probe Using Row IDs
| | Optimizer Estimate of Set Size: 1
| | Access Table Name = AFA.SYGJJ_TEST_INDEX ID = 2,31
| | | Index Scan: Name = AFA.SY_TEST_IDX2 ID = 3
| | | | Regular Index (Not Clustered)
| | | | Index Columns:
| | | | | 1: AGE (Ascending)
| | | #Columns = 0
| | | #Key Columns = 1
| | | | Start Key: Inclusive Value
| | | | | | 1: '20'
| | | | Stop Key: Inclusive Value
| | | | | | 1: '20'
| | | Index-Only Access
| | | Index Prefetch: None
| | | Isolation Level: Uncommitted Read
| | | Lock Intents
| | | | Table: Intent None
| | | | Row : None
Insert Into Sorted Temp Table ID = t1
| #Columns = 1
| #Sort Key Columns = 1
| | Key 1: (Ascending)
| Sortheap Allocation Parameters:
| | #Rows = 1
| | Row Width = 12
| Piped
| Duplicate Elimination
List Prefetch Preparation
| Access Table Name = AFA.SYGJJ_TEST_INDEX ID = 2,31
| | #Columns = 5
| | Single Record
| | Fetch Using Prefetched List
| | | Prefetch: 1 Pages
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | #Predicates = 2
| | | Return Data to Application
| | | | #Columns = 5
Return Data Completion
由上面的測(cè)試報(bào)告中可看出，在該查詢語(yǔ)句中聯(lián)合使用了索引，你可用相應(yīng)的測(cè)試方法，測(cè)試下其他相應(yīng)的結(jié)論，在這我就不一一列舉了。