“索引” 實驗小例，索引實驗

“索引” 實驗小例，索引實驗
由索引引出簡單實驗幾例

***********************************************聲明************************************************ 

原創作品，出自 “深藍的blog” 部落格，歡迎轉載，轉載時請務必註明出處（http://blog.csdn.net/huangyanlong）。

表述有錯誤之處，請您留言，不勝感激。

提醒：點擊目錄，更有助於您的查看。

*****************************************************************************************************

對之前的小例子重新歸納了一下，希望可以協助對索引有進一步的理解。

【例1】資料量小不需建索引

//如果表的資料量很少，全表掃描和走索引成本相差很小，使用索引是不是就沒有必要了。實驗操作：SQL> SELECT ENAME,JOB,SAL FROM SCOTT.EMP;//先找到一張小表以作實驗，查看錶中資訊，只有14行ENAME      JOB          SAL---------- --------- ------SMITH      CLERK        800ALLEN      SALESMAN    1600WARD       SALESMAN    1250JONES      MANAGER     2975MARTIN     SALESMAN    1250BLAKE      MANAGER     2850CLARK      MANAGER     2450SCOTT      ANALYST     3000KING       PRESIDENT   5000TURNER     SALESMAN    1500ADAMS      CLERK       1100JAMES      CLERK        950FORD       ANALYST     3000MILLER     CLERK       1300已選擇14行。SQL> SET AUTOTRACE ONSQL> SET AUTOTRACE TRACEONLYSQL> SELECT * FROM SCOTT.EMP WHERE ENAME='JAMES';//全表掃描尋找JAMES的資訊--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |     1 |    38 |     3   (0)| 00:00:01 ||*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |--------------------------------------------------------------------------SQL> CREATE INDEX IND_EMP_ENAME ON SCOTT.EMP(ENAME);//為ENAME列建索引SQL> SELECT * FROM SCOTT.EMP WHERE ENAME='JAMES';//走列索引尋找JAMES的資訊--------------------------------------------------------------------------------| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |               |     1 |    38 |     2   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| EMP           |     1 |    38 |     2   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | IND_EMP_ENAME |     1 |       |     1   (0)| 00:00:01 |--------------------------------------------------------------------------------//全表掃描成本是3%，走索引成本是2%//從以上實驗發現，在表的資料量很小的情況下，全表掃描和走索引成本上相差不大。

【例2】全表掃描IO成本低於使用索引情況

**************************************************************************舉一個例子，不恰當的使用索引，比用全表掃描的的IO成本更加高。**************************************************************************解答：    思路：建立一組rowid是散落在多個表資料區塊中的索引，這樣由於索引列資料的分布情況和索引中的順序差異很大，致使通過全表掃表比走索引更能降低IO的使用成本。操作如下：SQL> CREATE TABLE TAB_HYL AS SELECT * FROM DBA_OBJECTS;//建立了一個TAB_HYL表以作實驗SQL> ANALYZE TABLE TAB_HYL COMPUTE STATISTICS;//分析這張TAB_HYL實驗表SQL> SELECT NUM_ROWS,BLOCKS FROM USER_TABLES WHERE TABLE_NAME ='TAB_HYL';//尋找出實驗表上的行數、塊數  NUM_ROWS     BLOCKS---------- ----------     72606       1033SQL> SELECT 72606/1033 FROM DUAL;//計算平均每個塊中的行數為70行72606/1033---------- 70.286544SQL> DROP TABLE TAB_HYL PURGE;//刪除這張表，這張表就是為了計算出每塊所佔的行數，從而對其進行構建完成實驗SQL> CREATE TABLE TAB_HYL AS SELECT * FROM DBA_OBJECTS WHERE ROWNUM<=70;//重新建立實驗表讓它裝入70行形成第一個塊SQL> INSERT INTO TAB_HYL SELECT * FROM TAB_HYL;//複製相同的70行插到實驗表中,即實驗表中共有140行資料，兩個塊SQL> /    //再次執行相同操作，但此時基準的實驗表為140行，因此第三次插入了140行資料，即現在實驗表有280行資料SQL> /    //按照上面的方法以下連續建立，形成多個塊，讓每個塊中都有相同的索引值而形成一組實驗用的ROWIDSQL> /SQL> /SQL> /SQL> /SQL> COMMIT;SQL> CREATE INDEX IND_H1 ON TAB_HYL(OBJECT_ID);//建立實驗表中OBJECT_ID列的索引，之後通過該列值進行查詢，來說明查詢的成本SQL> ANALYZE TABLE TAB_HYL COMPUTE STATISTICS; //分析一下實驗表SQL> SELECT NUM_ROWS,BLOCKS FROM USER_TABLES WHERE TABLE_NAME ='TAB_HYL';//查看一下此時實驗表的行數、塊數已經達到實驗準備條件，可以開始實驗了  NUM_ROWS     BLOCKS---------- ----------      8960        103SQL> SET AUTOTRACE ONSQL> SET AUTOTRACE TRACEONLY  //設定跟蹤SQL> SELECT * FROM TAB_HYL WHERE OBJECT_ID=70;//通過上面建立了索引的列來尋找，得到下面的分析結果，記住cpu的成本為30，並且資料庫自動完成的是走全表掃描，說明資料庫已經判斷出什麼方式查詢，成本更低了。----------------------------------------------------------------------| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------|   0 | SELECT STATEMENT  |         |   128 | 10112 |    30   (0)| 00:00:01 ||*  1 |  TABLE ACCESS FULL| TAB_HYL |   128 | 10112 |    30   (0)| 00:00:01 |----------------------------------------------------------------------//之後我們人為讓查詢走索引再看一下分析結果。SQL> SELECT /*+INDEX(TAB_HYL IND_H1)*/ * FROM TAB_HYL WHERE OBJECT_ID=70;//強制查詢走索引，輸出一下結果，看到成本是102，要遠高於全表掃描的成本（全表掃描是30，見上表）。----------------------------------------------------------------------| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time   ----------------------------------------------------------------------|   0 | SELECT STATEMENT            |         |   128 | 10112 |   102(0)| 00:00:02 ||   1 |  TABLE ACCESS BY INDEX ROWID| TAB_HYL |   128 | 10112 |   102(0)| 00:00:02 ||*  2 |   INDEX RANGE SCAN          | IND_H1  |   128 |       |     1(0)| 00:00:01 |----------------------------------------------------------------------通過以上實驗說明，當索引列資料的分布情況和索引中的順序差異很大這種情況出現時，做索引範圍掃描效率偏低。

【例3】構造表時集簇因子數分別為接近塊數、接近行數

**************************************************************************建兩張表，各建一個索引。要求A表的索引集簇因子接近表塊數，B表的索引集簇因子接近表行數。**************************************************************************（一）、建立A表：索引集簇因子接近表塊數操作：SQL> CREATE TABLE TAB_HYL AS SELECT * FROM DBA_OBJECTS;//先建立了一個TAB_HYL表以作實驗源表，為了通過這個表分析出表中一個塊所佔的行數SQL> ANALYZE TABLE TAB_HYL COMPUTE STATISTICS;//分析這張TAB_HYL實驗表SQL> SELECT NUM_ROWS,BLOCKS FROM USER_TABLES WHERE TABLE_NAME ='TAB_HYL';//尋找出實驗表上的行數、塊數  NUM_ROWS     BLOCKS---------- ----------     72606       1033SQL> SELECT 72606/1033 FROM DUAL;//計算平均每個塊中的行數為70行72606/1033---------- 70.286544SQL> DROP TABLE TAB_HYL PURGE;//刪除這張表SQL> CREATE TABLE TAB_HYL AS SELECT * FROM DBA_OBJECTS WHERE ROWNUM<=70;//重新建立實驗表讓它裝入70行形成第一個塊SQL> INSERT INTO TAB_HYL SELECT * FROM TAB_HYL;//複製相同的70行插到實驗表中,即實驗表中共有140行資料，兩個塊SQL> /    //再次執行相同操作，但此時基準的實驗表為140行，因此第三次插入了140行資料，即現在實驗表有280行資料SQL> /    //按照上面的方法以下連續建立，這是為了構造實驗表的集簇因子SQL> /SQL> /SQL> /SQL> /SQL> COMMIT;SQL> CREATE TABLE TAB_A AS SELECT * FROM TAB_HYL ORDER BY OBJECT_ID;//根據實驗表建立出表A，表A是通過OBJECT_ID排序的，因此就得到了索引值相同的分布較集中的塊SQL> CREATE INDEX IND_H1 ON TAB_A(OBJECT_ID);//建立A表中OBJECT_ID列的索引SQL> ANALYZE TABLE TAB_A COMPUTE STATISTICS;//分析一下A表SQL> SELECT NUM_ROWS,BLOCKS FROM USER_TABLES WHERE TABLE_NAME = 'TAB_A';  NUM_ROWS     BLOCKS---------- ----------      8960        102SQL> SELECT BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,CLUSTERING_FACTOR  2  FROM USER_INDEXES  3  WHERE INDEX_NAME = 'IND_H1';//查看A表索引列的b-tree層級、葉的塊數、不同的key值、平均每個key所佔的葉塊的數量、聚集的因子 BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY CLUSTERING_FACTOR------- ----------- ------------- -----------------------  -----------------      1          18            70                       1                102//得到了A表索引列的集簇因子數（102）與上面的A表的塊數（102）是相同的。（二）、建立B表：索引集簇因子接近表行數操作：SQL> CREATE TABLE TAB_HYL AS SELECT * FROM DBA_OBJECTS;//建立了一個實驗表以作實驗SQL> ANALYZE TABLE TAB_HYL COMPUTE STATISTICS;//分析這張TAB_HYL實驗表SQL> SELECT NUM_ROWS,BLOCKS FROM USER_TABLES WHERE TABLE_NAME ='TAB_HYL';//尋找出實驗表上的行數、塊數  NUM_ROWS     BLOCKS---------- ----------     72606       1033SQL> SELECT 72606/1033 FROM DUAL;//計算平均每個塊中的行數為70行72606/1033---------- 70.286544SQL> DROP TABLE TAB_HYL PURGE;//刪除這張表SQL> CREATE TABLE TAB_B AS SELECT * FROM DBA_OBJECTS WHERE ROWNUM<=70;//建立B表讓它裝入70行形成第一個塊SQL> INSERT INTO TAB_B SELECT * FROM TAB_B;//複製相同的70行插到B表中,即B表中共有140行資料，兩個塊SQL> /    //再次執行相同操作，但此時基準的B表為140行，因此第三次插入了140行資料，即現在B表有280行資料SQL> /    //按照上面的方法以下連續建立，這是為了構造B表的集簇因子SQL> /SQL> /SQL> /SQL> /SQL> COMMIT;SQL> CREATE INDEX IND_H2 ON TAB_B(OBJECT_ID);//建立B表中OBJECT_ID列的索引SQL> ANALYZE TABLE TAB_B COMPUTE STATISTICS; //分析一下B表SQL> SELECT NUM_ROWS,BLOCKS FROM USER_TABLES WHERE TABLE_NAME ='TAB_B';//查看一下此時B表的行數、塊數  NUM_ROWS     BLOCKS---------- ----------      8960        103SQL> SELECT BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,CLUSTERING_FACTOR  2  FROM USER_INDEXES  3  WHERE INDEX_NAME = 'IND_H2';//查看B表索引列的b-tree層級、葉的塊數、不同的key值、平均每個key所佔的葉塊的數量、集簇因子 BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY  CLUSTERING_FACTOR------- ----------- ------------- -----------------------  -----------------      1          18            70                       1               7070//B表索引列的集簇因子(7070)和B表中的行數(8960)相對接近.           

【例4】有關索引監控

**************************************************************************對一張表的索引開監控，看是否有使用到。**************************************************************************會話A：SQL> ALTER INDEX IND_H1 MONITORING USAGE;//對上面練習中用到的IND_H1索引開監控SQL> SELECT * FROM V$OBJECT_USAGE;//通過查看V$OBJECT_USAGE視圖查看對IND_H1索引的監控資訊，MON為YES代表已經開監控了，當前沒有人用到INDEX_NAME          TABLE_NAME             MON USE  START_MONITORING     END_MONITORING------------------- ---------------------- --- ---  -------------------  -------------------IND_H1              TAB_A           YES NO   03/18/2014 16:54:29會話B：SQL> SET AUTOTRACE ON;//開監控，確認下面的操作是走索引的SQL> SELECT * FROM TAB_A WHERE OBJECT_ID=70;//使用帶索引列查詢，分析結果如下--------------------------------------------------------------------------------|   0 | SELECT STATEMENT            |        |   128 | 10112 |     3   (0)| 00:00:01 ||   1 |  TABLE ACCESS BY INDEX ROWID| TAB_A  |   128 | 10112 |     3   (0)| 00:00:01 ||*  2 |   INDEX RANGE SCAN          | IND_H1 |   128 |       |     1   (0)| 00:00:01 |--------------------------------------------------------------------------------會話A：SQL> SELECT * FROM V$OBJECT_USAGE;//再次通過V$OBJECT_USAGE視圖查看對IND_H1索引的監控資訊，MON為YES代表已經開監控了，USE為YES代表當前有人在使用INDEX_NAME          TABLE_NAME             MON USE  START_MONITORING     END_MONITORING------------------- ---------------------- --- ---  -------------------  -------------------IND_H1              TAB_A           YES YES  03/18/2014 16:54:29

***********************************************聲明************************************************ 

原創作品，出自 “深藍的blog” 部落格，歡迎轉載，轉載時請務必註明出處（http://blog.csdn.net/huangyanlong）。

表述有錯誤之處，請您留言，不勝感激。

提醒：點擊目錄，更有助於您的查看。

*****************************************************************************************************

什是索引?為何要建立索引?並舉例說明(以某一具體的DBMS為例)

索引是表示資料的另一種方式，它提供的資料順序不同於資料在磁碟上的實體儲存體順序。索引的特殊作用是在表內重新排列記錄的物理位置。索引可建立在資料表的一列上，或建立在表的幾列的組合上。

回想一書館中，存放著一架一架的圖書。假設要找一本書，圖書管理員視必要一架一架的尋找，直到找到想要的那本書為止。這樣的尋找肯定費時費力，那麼管理員可以將所有的圖書標題按字母分類建立索引卡片，按字母順序放在不同的辦公抽屜中，這樣一架一架尋找圖書的過程就變成了在辦公桌前抽屜中查詢索引卡片了，很容易得到有關這本書的資訊。

再進一步設想，圖書管理員很勤快，他不僅按照圖書標題字母分類，而且還按作者姓名和出版社分別建立了另外的索引卡片，這個過程將給借閱者在檢索圖書資訊時以更大的靈活性。因此在一個資料表中是可以建立多個索引的。

在資料庫中，對無索引的表進行查詢一般稱為全表掃描。全表掃描是資料庫伺服器用來搜尋表的每一條記錄的過程，直到所有符合給定條件的記錄返回為止。這個操作可以比作在圖書館中查書，從第一個書架的第一本書開始，瀏覽每一本書，直到發現所要的書為止。為了進行高效查詢，可以在資料表上針對某一欄位建立索引，由於該索引包括了一個指向資料的指標，資料庫伺服器則只沿著索引排列的順序對僅有一列資料的索引進行讀取（只建立一個索引）直至索引指標指向相應的記錄上為止。由於索引只是按照一個欄位進行尋找，而沒有對整表進行遍曆，因此一般說來索引尋找比全表掃描的速度快。

那麼，是不是使用索引查詢一定比全表掃描的速度快呢？答案是否定的。如果查詢小型資料表（記錄很少）或是查詢大型資料表（記錄很多）的絕大部分資料，全表掃描更為實用。例如，查詢“性別”欄位，其值只能是“男或女”，在其上建立索引的意義就不大，甚至不允許在布爾型、大二進位型（備忘型、映像型等）上建立索引。

樣本：www.diylsoft.com/...ze.htm
聚簇索引：
baike.baidu.com/view/1028053.html?wtp=tt
 
舉例告訴我科學小實驗

你也不說說是關於那方面的實驗，只要實驗設計合理都是科學小實驗。