深入DB2索引

 深入DB2索引

 

ü        DB2索引簡介

ü        DB2索引結構

ü        DB2索引訪問機制

ü        DB2索引設計

ü        DB2索引建立原則

ü        DB2索引維護

ü        DB2索引最佳化

1、DB2索引簡介

 

   索引優點：

 

（1） 建立索引可提高查詢速度。

（2） 建立索引保證資料唯一性。

 

   索引類型：

 

在介紹索引類型前介紹一下關於稠密度的概念.

稠密度定義：在資料分布均勻的情況下，稠密度=資料分布的可能數/資料總條數。例如：表1中有索引1在列1上，其中列1的資料分布有10中，分別是1-10，資料接近均勻分布，總資料量為1000，則該索引的稠密度=100/1000=10%，稠密度最高為1。稠密度越小，索引的選擇性越大，查詢效能越好。

 

（1）    非唯一索引

 

可以說大部分的索引的非唯一索引，這和資料的分布有關係，一般的資料都具有可重複性特性，所以他們不能被定義為唯一索引。非唯一索引可以使用命令：

CREATE INDEX <IDX_NAME> ON <TAB_NAME> (<COLNAME>)來定義。

（2）    唯一索引

 

唯一索引用來保證資料的唯一性，唯一索引一般效能要高於非唯一索引，這與索引的稠密度有關。唯一索引的稠密度永遠等於資料總條數的倒數。

（3）    純索引

 

純索引的概念是相對與一般索引。如下方式表中有倆個欄位，其中欄位1是唯一主鍵，欄位2為資料，實際的查詢中經常是select * from 表 where col1=?

這樣的查詢條件可以使用純索引來避免表查詢，具體建立命令為

CREATE UNIQUE INDEX <IDX_NAME> ON <TAB_NAME> (COL1_NAME) INCLUDE(COL2_NAME)。上述的語句的意思就是在col1上建立唯一索引，選擇包含col2的資料，這些附加的資料將與鍵儲存到一起，但是不作為索引的一部分，所以不被排序。純索引訪問是用來減少對資料頁的訪問，因為所需要的資料已經顯示在索引中了。

（4）    群集索引

 

群集索引允許對資料頁採用更線性訪問模式，允許更有效預取，並且避免排序。群集索引是要求資料在插入時，做更多的操作，將相臨的資料條目放入相同的頁，使得查詢速度更快，因為每次訪問索引頁要將所有的索引條目都訪問完畢才移到下一頁，保證了緩衝池中任何一個時刻都只有一個索引頁存在。

群集索引的特點：

    提高查詢速度，資料頁以鍵的順序排列；

    以鍵的順序掃描整張表；

    插入和更新需要做更多的事情，不建議經常插入和更新的表上做群集索引；

  

2、DB2索引結構

 

在DB2中，索引的資料結構是一顆B+樹。B樹把它的儲存塊組織成一棵樹。這棵樹是平衡的，即從樹根到樹葉的所有路徑都一樣長。通常B樹有三層：根、中介層和葉，但也可以是任意多層。

 

典型的B+樹結構：

根結點中至少有兩個指標被使用。所有指標指向位於B樹下一層的儲存塊；

 

葉結點中，最後一個指標指向它右邊的下一個葉結點儲存塊，即指向下一個索引值大於它的塊。在葉塊的其他n個指標當中，至少有個指標被使用且指向資料記錄；未使用的指標可看作null 指標且不指向任何地方。如果第i個指數被使用，則指向具有第i個索引值的記錄；

 

 在內層結點中，所有的n+ 1個指標都可以用來指向B樹中下一層的塊。其中至少

個指標被實際使用（但如果是根結點，則不管n多大都只要求至少兩個指標被使用）。如果j個指標被使用，那該塊中將有j－1個鍵，設為K1，K2⋯⋯，Kj - 1。第一個指標指向B樹的一部分，一些索引值小於K1的記錄可在這一部分找到。第二個指標指向B樹的另一部分，所有索引值大小等於K1且小於K2的記錄可在這一部分中。依此類推。最後，第j個指標指向B樹的又一部分，一些索引值大於等於Kj - 1的記錄可以在這一部分中找到。注意：某些索引值遠小於K1或遠大於Kj - 1的記錄可能根本無法通過該塊到達，但可通過同一層的其他塊到達。

 

假若我們以常規的畫樹方式來畫B樹，任一給定結點的子結點按從左（第一個子結點）到右（最後一個子結點）的順序排列。那麼，我們在任何一個層次上從左至右來看B樹的結點，結點的索引值將按非減的順序出現。

DB2中索引結構

 

標準表的表和索引管理

記錄表示和資料頁

 

 

在DB2中可使用命令db2dart <dbname> /di /tsi <tabspacesid> /oi <tableid> /ps <N>p /np 1 /v y來查看索引的物理結構以增加對索引的理解。

例如：主庫中

3、 DB2索引訪問機制

 

快速索引式訪問

 

一般來將DB2最快的資料訪問方式就是使用索引。索引是為了快速找著資料區塊的資料結構。

 

在DB2使用索引來查詢資料前，必須滿足以下要求：

 

至少有一個SQL謂詞必須是可索引的。

其中一列必須作為可用索引中的列而存在。

 

 

4、 DB2索引建立原則

 

DB2索引實現是一個B+樹，通過索引可以實現快速查詢，避免全表掃描以此來減少IO操作。

 

    索引是對錶資料的一種抽象，通過抽取有限資料，對資料的分布進行計算，以此來完成對資料的快速檢索。

索引建立語句”CREATE INDEX <INDEX_NAME> ON <TABLE_NAME> (<COLNAME1,COLNAME2…>)”。

X`

    建立索引需要注意的地方：

l        索引應該用來提高查詢速度，但是會對更新和刪除操作帶來負面影響，因為要同步更新索引。所以索引應該建立到更新、刪除相對比讀取少的表上。

 

l            索引需要獨立的空間進行儲存和管理。索引是需要磁碟空間來儲存。所以避免重複建立冗餘索引。如下:“CREATE TABLE TEST_IDX (COL1 INT NOT NULL, COL2 INT NOT NULL, COL3 IN NOT NULL)”“CREATE INDEX TEST_IDX_IDX1 ON TEST_IDX (COL1, COL2, COL3)”已經有索引TEST_IDX_IDX1在三個列上，在建立”CREATE INDEX TEST_IDX_IDX2 ON
TEST_IDX (COL1, COL2)”，這樣的索引一般沒有什麼作用。

   

l            索引用來避免表掃描。通過索引對大量資料幫浦有限部分，形成一個相對少量的有序資料結構，通過對有序資料結構的尋找可以快速想要尋找的資料。所以索引適合建立在資料量比較大的表上，而且該表上的查詢經常是根據條件查詢部分資料。比如一些系統基礎資料表，如SYSTEM表，這些表資料量小，而且經常是查詢全部資料，所以這些表上建立索引對效能的影響不是很大，完全可以避免，以免對管理造成影響。

 

l            建立索引的目的還有一個就是保證資料唯一性，可以利用”CREATE UNIQUE INDEX <INDEX_NAME> ON <TABLENAME> (<COLNAME>)”，來完成。

 

l            主鍵會隱式建立索引，所以請不要在主鍵上建立索引浪費空間。

 

l            盡量減少索引的建立。DB2路徑訪問最佳化器會根據表中所提供的索引來完成儘可能多的訪問路徑的成本估計。建立過多的索引意味著DB2最佳化器產生更多的訪問路徑，完成更多的訪問規劃成本估算，這會增加SQL語句編譯時間。

 

l            建立唯一索引可以避免排序。因為索引是有序資料結構，在進行掃描時，DB2會預設按照順序輸出結果，而不是按照插入先後。通過建立唯一索引可以避免排序，提高查詢效能。

 

l            具有大量重複資料的列上不要建立索引。在大量重複的列上建立索引沒有任何意義。如下資料結構：表中欄位col1有大量重複資料，其中的資料分布是按照90%的Y，和10%的N來分布。這樣的列上建立索引沒有任何意義。在查詢條件為col1=‘Y’時，該表的索引掃描和表掃描沒有特大差異。根據實踐經驗，列上的資料分布應該均勻，並且抽密度不能大於5 ‰。

 

    索引掃描原理圖：

建立如下表：

   

“CREATE TABLE TEST1 (NO INT NOT NULL, NAME CHAR(5))”

“CREATE INDEX TEST1_IDX_1 ON TEST1 (NO)”

“INSERT INTO TEST1 SELECT ROW_NUMBER() OVER(), CHR(INT(RAND()*75+48))||CHR(INT(RAND()*75+48))||CHR(INT(RAND()*75+48)) FROM SYSCAT.COLUMNS T1 JOIN SYSCAT.COLUMNS T2 ON T1.COLNAME!=T2.COLNAME FETCH FIRST 100 ROWS ONLY“

 “SELECT * FROM TEST1 WHERE NO = 50”

 

5、DB2索引維護

6、DB2索引最佳化

 

謂詞類型	可索引	注        釋
Col∝con	Y	∝代表>,>=,=,<=,<,但是<>是可能不可索引的。
Col between con1 and con2	Y	在匹配系列中必須是最後的。
Col in list	Y	僅對一個匹配列
Col is null	Y
Col like ‘xyz%’	Y	模糊比對%在後面。
Col like ‘%xyz’	N	模糊比對%在前面。
Col1∝Col2	N	Col1和col2來自同一個表
Col∝Expression	N	例如：c1（c1+1）/2
Pred1 and Pred2	Y	Pred1和Pred2都是可索引的，指相同索引的列
Pred1 or Pred2	N	除了（c1=a or c1=b）外，他可以被認為是c1 in（a，b）
Not Pred1	N	或者任何的等價形式：Not between,Not in,Not like等等。

 

 深入DB2索引

 

ü        DB2索引簡介

ü        DB2索引結構

ü        DB2索引訪問機制

ü        DB2索引設計

ü        DB2索引建立原則

ü        DB2索引維護

ü        DB2索引最佳化

1、DB2索引簡介

 

   索引優點：

 

（1） 建立索引可提高查詢速度。

（2） 建立索引保證資料唯一性。

 

   索引類型：

 

在介紹索引類型前介紹一下關於稠密度的概念.

稠密度定義：在資料分布均勻的情況下，稠密度=資料分布的可能數/資料總條數。例如：表1中有索引1在列1上，其中列1的資料分布有10中，分別是1-10，資料接近均勻分布，總資料量為1000，則該索引的稠密度=100/1000=10%，稠密度最高為1。稠密度越小，索引的選擇性越大，查詢效能越好。

 

（1）    非唯一索引

 

可以說大部分的索引的非唯一索引，這和資料的分布有關係，一般的資料都具有可重複性特性，所以他們不能被定義為唯一索引。非唯一索引可以使用命令：

CREATE INDEX <IDX_NAME> ON <TAB_NAME> (<COLNAME>)來定義。

（2）    唯一索引

 

唯一索引用來保證資料的唯一性，唯一索引一般效能要高於非唯一索引，這與索引的稠密度有關。唯一索引的稠密度永遠等於資料總條數的倒數。

（3）    純索引

 

純索引的概念是相對與一般索引。如下方式表中有倆個欄位，其中欄位1是唯一主鍵，欄位2為資料，實際的查詢中經常是select * from 表 where col1=?

這樣的查詢條件可以使用純索引來避免表查詢，具體建立命令為

CREATE UNIQUE INDEX <IDX_NAME> ON <TAB_NAME> (COL1_NAME) INCLUDE(COL2_NAME)。上述的語句的意思就是在col1上建立唯一索引，選擇包含col2的資料，這些附加的資料將與鍵儲存到一起，但是不作為索引的一部分，所以不被排序。純索引訪問是用來減少對資料頁的訪問，因為所需要的資料已經顯示在索引中了。

（4）    群集索引

 

群集索引允許對資料頁採用更線性訪問模式，允許更有效預取，並且避免排序。群集索引是要求資料在插入時，做更多的操作，將相臨的資料條目放入相同的頁，使得查詢速度更快，因為每次訪問索引頁要將所有的索引條目都訪問完畢才移到下一頁，保證了緩衝池中任何一個時刻都只有一個索引頁存在。

群集索引的特點：

    提高查詢速度，資料頁以鍵的順序排列；

    以鍵的順序掃描整張表；

    插入和更新需要做更多的事情，不建議經常插入和更新的表上做群集索引；

  

2、DB2索引結構

 

在DB2中，索引的資料結構是一顆B+樹。B樹把它的儲存塊組織成一棵樹。這棵樹是平衡的，即從樹根到樹葉的所有路徑都一樣長。通常B樹有三層：根、中介層和葉，但也可以是任意多層。

 

典型的B+樹結構：

根結點中至少有兩個指標被使用。所有指標指向位於B樹下一層的儲存塊；

 

葉結點中，最後一個指標指向它右邊的下一個葉結點儲存塊，即指向下一個索引值大於它的塊。在葉塊的其他n個指標當中，至少有個指標被使用且指向資料記錄；未使用的指標可看作null 指標且不指向任何地方。如果第i個指數被使用，則指向具有第i個索引值的記錄；

 

 在內層結點中，所有的n+ 1個指標都可以用來指向B樹中下一層的塊。其中至少

個指標被實際使用（但如果是根結點，則不管n多大都只要求至少兩個指標被使用）。如果j個指標被使用，那該塊中將有j－1個鍵，設為K1，K2⋯⋯，Kj - 1。第一個指標指向B樹的一部分，一些索引值小於K1的記錄可在這一部分找到。第二個指標指向B樹的另一部分，所有索引值大小等於K1且小於K2的記錄可在這一部分中。依此類推。最後，第j個指標指向B樹的又一部分，一些索引值大於等於Kj - 1的記錄可以在這一部分中找到。注意：某些索引值遠小於K1或遠大於Kj - 1的記錄可能根本無法通過該塊到達，但可通過同一層的其他塊到達。

 

假若我們以常規的畫樹方式來畫B樹，任一給定結點的子結點按從左（第一個子結點）到右（最後一個子結點）的順序排列。那麼，我們在任何一個層次上從左至右來看B樹的結點，結點的索引值將按非減的順序出現。

DB2中索引結構

 

標準表的表和索引管理

記錄表示和資料頁

 

 

在DB2中可使用命令db2dart <dbname> /di /tsi <tabspacesid> /oi <tableid> /ps <N>p /np 1 /v y來查看索引的物理結構以增加對索引的理解。

例如：主庫中

3、 DB2索引訪問機制

 

快速索引式訪問

 

一般來將DB2最快的資料訪問方式就是使用索引。索引是為了快速找著資料區塊的資料結構。

 

在DB2使用索引來查詢資料前，必須滿足以下要求：

 

至少有一個SQL謂詞必須是可索引的。

其中一列必須作為可用索引中的列而存在。

 

 

4、 DB2索引建立原則

 

DB2索引實現是一個B+樹，通過索引可以實現快速查詢，避免全表掃描以此來減少IO操作。

 

    索引是對錶資料的一種抽象，通過抽取有限資料，對資料的分布進行計算，以此來完成對資料的快速檢索。

索引建立語句”CREATE INDEX <INDEX_NAME> ON <TABLE_NAME> (<COLNAME1,COLNAME2…>)”。

X`

    建立索引需要注意的地方：

l        索引應該用來提高查詢速度，但是會對更新和刪除操作帶來負面影響，因為要同步更新索引。所以索引應該建立到更新、刪除相對比讀取少的表上。

 

l            索引需要獨立的空間進行儲存和管理。索引是需要磁碟空間來儲存。所以避免重複建立冗餘索引。如下:“CREATE TABLE TEST_IDX (COL1 INT NOT NULL, COL2 INT NOT NULL, COL3 IN NOT NULL)”“CREATE INDEX TEST_IDX_IDX1 ON TEST_IDX (COL1, COL2, COL3)”已經有索引TEST_IDX_IDX1在三個列上，在建立”CREATE INDEX TEST_IDX_IDX2 ON
TEST_IDX (COL1, COL2)”，這樣的索引一般沒有什麼作用。

   

l            索引用來避免表掃描。通過索引對大量資料幫浦有限部分，形成一個相對少量的有序資料結構，通過對有序資料結構的尋找可以快速想要尋找的資料。所以索引適合建立在資料量比較大的表上，而且該表上的查詢經常是根據條件查詢部分資料。比如一些系統基礎資料表，如SYSTEM表，這些表資料量小，而且經常是查詢全部資料，所以這些表上建立索引對效能的影響不是很大，完全可以避免，以免對管理造成影響。

 

l            建立索引的目的還有一個就是保證資料唯一性，可以利用”CREATE UNIQUE INDEX <INDEX_NAME> ON <TABLENAME> (<COLNAME>)”，來完成。

 

l            主鍵會隱式建立索引，所以請不要在主鍵上建立索引浪費空間。

 

l            盡量減少索引的建立。DB2路徑訪問最佳化器會根據表中所提供的索引來完成儘可能多的訪問路徑的成本估計。建立過多的索引意味著DB2最佳化器產生更多的訪問路徑，完成更多的訪問規劃成本估算，這會增加SQL語句編譯時間。

 

l            建立唯一索引可以避免排序。因為索引是有序資料結構，在進行掃描時，DB2會預設按照順序輸出結果，而不是按照插入先後。通過建立唯一索引可以避免排序，提高查詢效能。

 

l            具有大量重複資料的列上不要建立索引。在大量重複的列上建立索引沒有任何意義。如下資料結構：表中欄位col1有大量重複資料，其中的資料分布是按照90%的Y，和10%的N來分布。這樣的列上建立索引沒有任何意義。在查詢條件為col1=‘Y’時，該表的索引掃描和表掃描沒有特大差異。根據實踐經驗，列上的資料分布應該均勻，並且抽密度不能大於5 ‰。

 

    索引掃描原理圖：

建立如下表：

   

“CREATE TABLE TEST1 (NO INT NOT NULL, NAME CHAR(5))”

“CREATE INDEX TEST1_IDX_1 ON TEST1 (NO)”

“INSERT INTO TEST1 SELECT ROW_NUMBER() OVER(), CHR(INT(RAND()*75+48))||CHR(INT(RAND()*75+48))||CHR(INT(RAND()*75+48)) FROM SYSCAT.COLUMNS T1 JOIN SYSCAT.COLUMNS T2 ON T1.COLNAME!=T2.COLNAME FETCH FIRST 100 ROWS ONLY“

 “SELECT * FROM TEST1 WHERE NO = 50”

 

5、DB2索引維護

6、DB2索引最佳化

 

謂詞類型	可索引	注        釋
Col∝con	Y	∝代表>,>=,=,<=,<,但是<>是可能不可索引的。
Col between con1 and con2	Y	在匹配系列中必須是最後的。
Col in list	Y	僅對一個匹配列
Col is null	Y
Col like ‘xyz%’	Y	模糊比對%在後面。
Col like ‘%xyz’	N	模糊比對%在前面。
Col1∝Col2	N	Col1和col2來自同一個表
Col∝Expression	N	例如：c1（c1+1）/2
Pred1 and Pred2	Y	Pred1和Pred2都是可索引的，指相同索引的列
Pred1 or Pred2	N	除了（c1=a or c1=b）外，他可以被認為是c1 in（a，b）
Not Pred1	N	或者任何的等價形式：Not between,Not in,Not like等等。