2021-09-11

索引詳細講解
1.1 索引介紹當我們在某本書中查找特定的章節內容時，可以先從書的目錄著手，找到該章節所在的頁碼，然后快速的定位到該頁，這種做法的前提是頁面編號是有序的，如果頁碼無序，就只能從第一頁開始，一頁頁的查找了，資料庫中索引（Index）的概念與目錄的概念非常類似，如果某列出現在查詢的條件中，而該列的資料是無序的，查詢時只能從第一行開始一行一行的匹配，創建索引就是對某些特定列中的資料排序，生成獨立的索引表，在某列上創建索引后，如果該列出現在查詢條件中，Oracle會比較全表掃描與索引掃描的代價，如果索引掃描代價小，那Oracle會自動參考該索引，先從索引表中查詢出符合條件記錄的ROWID，由于ROWID是記錄的物理地址，因此可以根據ROWID快速的定位到具體的記錄，表中的資料非常多時，參考索引帶來的查詢效率非常可觀，
?如果表中的某些欄位經常被查詢并作為查詢的條件出現時，就應該考慮為該列創建索引，?當從很多行的表中查詢少數行時，也要考慮創建索引，有一潭訓本的準則是：當任何單個查詢要檢索的行少于或者等于整個表行數的10%時，索引就非常有用，Oracle資料庫會為表的主鍵和包含唯一約束的列自動創建索引，索引可以提高查詢的效率，但是在資料增刪改時需要更新索引，因此索引對增刪改時會有負面影響，
語法結構：創建索引CREATE [UNIQUE] INDEX index_name ON table_name(column_name[,column_name…])
語法決議：
1.UNIQUE:指定索引列上的值必須是唯一的，稱為唯一索引，
2.index_name：指定索引名，
3.tabl_name：指定要為哪個表創建索引4.column_name：指定要對哪個列創建索引，我們也可以對多列創建索引；這種索引稱為組合索引，
【例】為EMP表的ENAME列創建創建唯一索引，為EMP表的工資列創建普通索引，把JOB列先變為小寫再創建索引，
代碼演示：創建索引–為ENAME創建唯一索引CREATE UNIQUE INDEX UQ_ENAME_IDX ON EMP(ENAME); --將JOB轉成小寫再為函式創建索引CREATE INDEX IDX_JOB_LOWER ON EMP(LOWER(JOB));
代碼決議：①為SCOTT.EMP表的ENAME列創建唯一索引， ②在查詢中可能經常使用job的小寫作為條件的運算式，因此創建索引時，可以先對JOB列中的所有值轉換為小寫后創建索引，而這時需要使用lower函式，這種索引稱為基于函式的索引，在select陳述句查詢時，Oracle系統會自動為查詢條件上的列應用索引，索引就是對某一列進行排序，因此在索引列上，重復值越少，索引的效果越明顯，Oracle可以為一些列值重復非常多且值有限的列（比如性別列）上創建位圖索引，
語法結構：洗掉索引DROP INDEX 索引名1.2 索引種類唯一索引（用的最多）
1、何時創建：當某列任意兩行的值都不相同
2、當建立Primary Key(主鍵)或者Unique constraint(唯一約束)時，唯一索引將被自動建立組合索引
1、何時創建：當兩個或多個列經常一起出現在where條件中時，則在這些列上同時創建2、組合索引中列的順序是任意的，也無需相鄰，但是建議將最頻繁訪問的列放在串列的最前面位圖索引1、何時創建：列中有非常多的重復的值時候，例如某列保存了 “性別”資訊，Where 條件中包含了很多OR運算子，較少的update操作，因為要相應的跟新所有的bitmap基于函式的索引1、何時創建：在WHERE條件陳述句中包含函式或者運算式時2、函式包括：算數運算式、PL索引詳細講解
1.1 索引介紹
當我們在某本書中查找特定的章節內容時，可以先從書的目錄著手，找到該章節所在的頁碼，然后快速的定位到該頁，這種做法的前提是頁面編號是有序的，如果頁碼無序，就只能從第一頁開始，一頁頁的查找了，

資料庫中索引（Index）的概念與目錄的概念非常類似，如果某列出現在查詢的條件中，而該列的資料是無序的，查詢時只能從第一行開始一行一行的匹配，創建索引就是對某些特定列中的資料排序，生成獨立的索引表，在某列上創建索引后，如果該列出現在查詢條件中，Oracle會比較全表掃描與索引掃描的代價，如果索引掃描代價小，那Oracle會自動參考該索引，先從索引表中查詢出符合條件記錄的ROWID，由于ROWID是記錄的物理地址，因此可以根據ROWID快速的定位到具體的記錄，表中的資料非常多時，參考索引帶來的查詢效率非常可觀，

?如果表中的某些欄位經常被查詢并作為查詢的條件出現時，就應該考慮為該列創建索引，

?當從很多行的表中查詢少數行時，也要考慮創建索引，有一潭訓本的準則是：當任何單個查詢要檢索的行少于或者等于整個表行數的10%時，索引就非常有用，

Oracle資料庫會為表的主鍵和包含唯一約束的列自動創建索引，索引可以提高查詢的效率，但是在資料增刪改時需要更新索引，因此索引對增刪改時會有負面影響，

語法結構：創建索引
CREATE [UNIQUE] INDEX index_name ON table_name(column_name[,column_name…])
語法決議：
1.UNIQUE:指定索引列上的值必須是唯一的，稱為唯一索引，
2.index_name：指定索引名，
3.tabl_name：指定要為哪個表創建索引，
4.column_name：指定要對哪個列創建索引，我們也可以對多列創建索引；這種索引稱為組合索引，

【例】為EMP表的ENAME列創建創建唯一索引，為EMP表的工資列創建普通索引，把JOB列先變為小寫再創建索引，

代碼演示：創建索引
–為ENAME創建唯一索引
CREATE UNIQUE INDEX UQ_ENAME_IDX ON EMP(ENAME);
–將JOB轉成小寫再為函式創建索引
CREATE INDEX IDX_JOB_LOWER ON EMP(LOWER(JOB));
代碼決議：
①為SCOTT.EMP表的ENAME列創建唯一索引，
②在查詢中可能經常使用job的小寫作為條件的運算式，因此創建索引時，可以先對JOB列中的所有值轉換為小寫后創建索引，而這時需要使用lower函式，這種索引稱為基于函式的索引，

在select陳述句查詢時，Oracle系統會自動為查詢條件上的列應用索引，索引就是對某一列進行排序，因此在索引列上，重復值越少，索引的效果越明顯，
Oracle可以為一些列值重復非常多且值有限的列（比如性別列）上創建位圖索引，

語法結構：洗掉索引
DROP INDEX 索引名

1.2 索引種類
唯一索引（用的最多）
1、何時創建：當某列任意兩行的值都不相同
2、當建立Primary Key(主鍵)或者Unique constraint(唯一約束)時，唯一索引將被自動建立
組合索引
1、何時創建：當兩個或多個列經常一起出現在where條件中時，則在這些列上同時創建
2、組合索引中列的順序是任意的，也無需相鄰，但是建議將最頻繁訪問的列放在串列的最前面
位圖索引
1、何時創建：列中有非常多的重復的值時候，例如某列保存了 “性別”資訊，
Where 條件中包含了很多OR運算子，較少的update操作，因為要相應的跟新所有的bitmap
基于函式的索引
1、何時創建：在WHERE條件陳述句中包含函式或者運算式時
2、函式包括：算數運算式、PL/SQL函式、程式包函式、SQL函式、用戶自定義函式，
反向鍵索引
鍵壓縮索引
索引組織表(IOT)
磁區索引

1.3 索引優缺點
創建索引可以大大提高系統的性能，
第一，通過創建唯一性索引，可以保證資料庫表中每一行資料的唯一性，
第二，可以大大加快資料的檢索速度，這也是創建索引的最主要的原因，
第三，可以加速表和表之間的連接，特別是在實作資料的參考完整性方面特別有意義，
第四，在使用分組和排序子句進行資料檢索時，同樣可以顯著減少查詢中分組和排序的時間，

也許會有人要問：增加索引有如此多的優點，為什么不對表中的每一個列創建一個索引呢？因為，增加索引也有許多不利的方面，
第一，創建索引和維護索引要耗費時間，這種時間隨著資料量的增加而增加，
第二，索引需要占物理空間，除了資料表占資料空間之外，每一個索引還要占一定的物理空間，如果要建立聚簇索引，那么需要的空間就會更大，
第三，當對表中的資料進行增加、洗掉和修改的時候，索引也要動態的維護，這樣就降低了資料的維護速度，

如果一張表20個索引，往里面寫入100萬條資料，怎么優化？
先把索引全刪了，再把記錄寫進去，再建立索引，

索引是建立在資料庫表中的某些列的上面，
在創建索引的時候，應該考慮在哪些列上可以創建索引，在哪些列上不能創建索引，
一般來說，應該在這些列上創建索引：
在經常需要搜索的列上，可以加快搜索的速度；
在經常用在連接的列上，這些列主要是一些外鍵，可以加快連接的速度；
在經常需要根據范圍進行搜索的列上創建索引，因為索引已經排序，其指定的范圍是連續的；
在經常需要排序的列上創建索引，因為索引已經排序，這樣查詢可以利用索引的排序，加快排序查詢時間；

同樣，對于有些列不應該創建索引，一般來說，不應該創建索引的的這些列具有下列特點：
第一，對于那些在查詢中很少使用或者參考的列不應該創建索引，這是因為，既然這些列很少使用到，因此有索引或者無索引，并不能提高查詢速度，相反，由于增加了索引，反而降低了系統的維護速度和增大了空間需求，
第二，對于那些只有很少資料值的列也不應該增加索引，這是因為，由于這些列的取值很少，例如人事表的性別列，在查詢的結果中，結果集的資料行占了表中資料行的很大比例，即需要在表中搜索的資料行的比例很大，增加索引，并不能明顯加快檢索速度，
第三，對于那些定義為text, image和bit資料型別的列不應該增加索引，這是因為，這些列的資料量要么相當大，要么取值很少，
第四，當修改性能遠遠大于檢索性能時，不應該創建索引，這是因為，修改性能和檢索性能是互相矛盾的，當增加索引時，會提高檢索性能，但是會降低修改性能，當減少索引時，會提高修改性能，降低檢索性能，因此，當修改性能遠遠大于檢索性能時，不應該創建索引，

1.4 索引失效
1．隱式轉換導致索引失效.這一點應當引起重視.也是開發中經常會犯的錯誤.
由于表的欄位tu_mdn定義為varchar2(20),但在查詢時把該欄位作為number型別？以where條件傳給Oracle,這樣會導致索引失效.
錯誤的例子：select * from test where tu_mdn=13333333333;
正確的例子：select * from test where tu_mdn=‘13333333333’;

2. 對索引列進行運算導致索引失效,我所指的對索引列進行運算包括(+，-，*，/，! 等)
   錯誤的例子：select * from test where id-1=9;
   正確的例子：select * from test where id=10;

3. 使用Oracle內部函式導致索引失效.對于這樣情況應當創建基于函式的索引.
   錯誤的例子：select * from test where round(id)=10; 說明，此時id的索引已經不起作用了
   正確的例子：首先建立函式索引，create index test_id_fbi_idx on test(round(id));然后 select * from test where round(id)=10; 這時函式索引起作用了

4. 以下使用會使索引失效，應避免使用；
   a. 使用 <> 、not in 、not exist、!=
   b. like “%" 百分號在前（可采用在建立索引時用reverse(columnName)這種方法處理）
   c. 單獨參考復合索引里非第一位置的索引列，應總是使用索引的第一個列，如果索引是建立在多個列上, 只有在它的第一個列被where子句參考時，優化器才會選擇使用該索引，
   d. 字符型欄位為數字時在where條件里不添加引號.
   e. 當變數采用的是times變數，而表的欄位采用的是date變數時.或相反情況，/SQL函式、程式包函式、SQL函式、用戶自定義函式，反向鍵索引鍵壓縮索引索引組織表(IOT)磁區索引1.3 索引優缺點創建索引可以大大提高系統的性能，第一，通過創建唯一性索引，可以保證資料庫表中每一行資料的唯一性，第二，可以大大加快資料的檢索速度，這也是創建索引的最主要的原因，第三，可以加速表和表之間的連接，特別是在實作資料的參考完整性方面特別有意義，第四，在使用分組和排序子句進行資料檢索時，同樣可以顯著減少查詢中分組和排序的時間，也許會有人要問：增加索引有如此多的優點，為什么不對表中的每一個列創建一個索引呢？因為，增加索引也有許多不利的方面，第一，創建索引和維護索引要耗費時間，這種時間隨著資料量的增加而增加，第二，索引需要占物理空間，除了資料表占資料空間之外，每一個索引還要占一定的物理空間，如果要建立聚簇索引，那么需要的空間就會更大，第三，當對表中的資料進行增加、洗掉和修改的時候，索引也要動態的維護，這樣就降低了資料的維護速度，如果一張表20個索引，往里面寫入100萬條資料，怎么優化？先把索引全刪了，再把記錄寫進去，再建立索引，索引是建立在資料庫表中的某些列的上面，在創建索引的時候，應該考慮在哪些列上可以創建索引，在哪些列上不能創建索引，一般來說，應該在這些列上創建索引：在經常需要搜索的列上，可以加快搜索的速度；在經常用在連接的列上，這些列主要是一些外鍵，可以加快連接的速度；在經常需要根據范圍進行搜索的列上創建索引，因為索引已經排序，其指定的范圍是連續的；在經常需要排序的列上創建索引，因為索引已經排序，這樣查詢可以利用索引的排序，加快排序查詢時間；同樣，對于有些列不應該創建索引，一般來說，不應該創建索引的的這些列具有下列特點：第一，對于那些在查詢中很少使用或者參考的列不應該創建索引，這是因為，既然這些列很少使用到，因此有索引或者無索引，并不能提高查詢速度，相反，由于增加了索引，反而降低了系統的維護速度和增大了空間需求，第二，對于那些只有很少資料值的列也不應該增加索引，這是因為，由于這些列的取值很少，例如人事表的性別列，在查詢的結果中，結果集的資料行占了表中資料行的很大比例，即需要在表中搜索的資料行的比例很大，增加索引，并不能明顯加快檢索速度，第三，對于那些定義為text, image和bit資料型別的列不應該增加索引，這是因為，這些列的資料量要么相當大，要么取值很少，第四，當修改性能遠遠大于檢索性能時，不應該創建索引，這是因為，修改性能和檢索性能是互相矛盾的，當增加索引時，會提高檢索性能，但是會降低修改性能，當減少索引時，會提高修改性能，降低檢索性能，因此，當修改性能遠遠大于檢索性能時，不應該創建索引，1.4 索引失效 1．隱式轉換導致索引失效.這一點應當引起重視.也是開發中經常會犯的錯誤. 由于表的欄位tu_mdn定義為varchar2(20),但在查詢時把該欄位作為number型別？以where條件傳給Oracle,這樣會導致索引失效. 錯誤的例子：select * from test where tu_mdn=13333333333; 正確的例子：select * from test where tu_mdn=‘13333333333’; 2. 對索引列進行運算導致索引失效,我所指的對索引列進行運算包括(+，-，*，/，! 等) 錯誤的例子：select * from test where id-1=9; 正確的例子：select * from test where id=10; 3. 使用Oracle內部函式導致索引失效.對于這樣情況應當創建基于函式的索引. 錯誤的例子：select * from test where round(id)=10; 說明，此時id的索引已經不起作用了 正確的例子：首先建立函式索引，create index test_id_fbi_idx on test(round(id));然后 select * from test where round(id)=10; 這時函式索引起作用了 4. 以下使用會使索引失效，應避免使用； a. 使用 <> 、not in 、not exist、!= b. like "%” 百分號在前（可采用在建立索引時用reverse(columnName)這種方法處理） c. 單獨參考復合索引里非第一位置的索引列，應總是使用索引的第一個列，如果索引是建立在多個列上, 只有在它的第一個列被where子句參考時，優化器才會選擇使用該索引， d. 字符型欄位為數字時在where條件里不添加引號. e. 當變數采用的是times變數，而表的欄位采用的是date變數時.或相反情況，

本文參考學習了不少站內大佬的帖子，再結合自己平時作業遇到的問題所撰寫，內容肯定有不足，希望各位多多提出，交流討論