[點晴永久免費OA]鍵、索引、約束的理解及其區別
當前位置:點晴教程→點晴OA辦公管理信息系統
→『 經驗分享&問題答疑 』
今天下午剛好沒事,把一些基礎性的概念理順一下,存檔,省的麻煩,嘿嘿 一.索引 1. 什么是索引? 索引是對數據庫表中一列或多列的值進行排序的一種結構。 在關系型數據庫中,索引是一種與表有關的數據庫結構,是事實存在的。它可以使對于表的select等等操作更加快速,相當于一本書的目錄。 對于一張表,如果我們想要找到某一列符合特定值的記錄,第一種方法是全表搜索,匹配,然后把所有符合的記錄列出,但是這樣做會消耗大量數據庫系統時間,并造成大量磁盤I/O操作;第二種就是在表中建立索引,然后在索引中找到符合查詢條件的索引值,最后通過保存在索引中的ROWID(相當于頁碼)快速找到表中對應的記錄。 索引是一個單獨的、物理的數據庫結構,它是某個表中一列或若干列值的集合和相應的指向表中物理標識這些值的數據頁的邏輯指針清單。由此可知,索引是要消耗數據庫空間的。 并非所有的數據庫都以相同的方式使用索引。作為通用規則,只有當經常查詢索引列中的數據時,才需要在表上創建索引。索引占用磁盤空間,并且降低添加、刪除和更新行的速度。在多數情況下,索引用于數據檢索的速度優勢大大超過它的不足之處。但是,如果應用程序非常頻繁地更新數據或磁盤空間有限,則可能需要限制索引的數量。 可以使用單列作為索引,也可以使用多列聯合作為索引。 2. 索引的優缺點 優點: (1)大大加快數據的檢索速度; (2)創建唯一性索引,保證數據庫表中每一行數據的唯一性; (3)加速表和表之間的連接; (4)在使用分組和排序子句進行數據檢索時,可以顯著減少查詢中分組和排序的時間。 缺點: (1)索引需要占物理空間。 (2)當對表中的數據進行增加、刪除和修改的時候,索引也要動態的維護,降低了數據的維護速度。 建立索引時的注意事項: (1) 考慮已在表上創建的索引數量。最好避免在單個表上有很多索引 (2) 檢查已在表上創建的索引的定義。最好避免包含共享列的重疊索引 (3) 檢查某列中唯一數據值的數量,并將該數量與表中的行數進行比較。比如如果有1000w記錄,某字段為性別,只有男,女。也就是說一半的記錄都是重復的,這樣就要考慮是否還有必要建立索引了。 3. 一些索引類別 (1) 普通索引 也即不加任何限制的索引??赏ㄟ^以下語句理解。 create table zjj_temp_1 (id number(10),first_name char(10),last_name char(10),age number(3),val number(10,2)); insert into zjj_temp_1 values(1,''junjie'',''zhang'',25,4000); select * from zjj_temp_1; 此時一條記錄已經插入進入了。 create index zjj_temp_index_1 on zjj_temp_1(first_name); --------建立索引 insert into zjj_temp_1 values(1,''junjie'',''zhang'',25,4000) --------再次插入一條一模一樣的記錄 select * from zjj_temp_1; Ok!兩條記錄出現了,也即此索引的作用是讓你再查找first_name為某一個特定值的記錄時速度更快而已,僅此而已。 (2) 唯一索引 一種索引,不允許具有索引值相同的行,從而禁止重復的索引或鍵值。系統在創建該索引時檢查是否有重復的鍵值,并在每次使用 INSERT 或 UPDATE 語句添加數據時進行檢查。 繼續分析例子: drop index zjj_temp_index_1; ----刪除上文創建的普通索引。 create unique index zjj_temp_1 on zjj_temp_1(id); ----建立唯一索引 數據庫報錯了? 是的,說找到重復的關鍵字。 從上文我們可以看到,zjj_temp_1表中有兩條記錄,id都是1. 這樣是唯一索引是不允許的,所以自然就創建不起來了。 delete from zjj_temp_1 where rownum<2; --刪除一行 create unique index zjj_temp_1 on zjj_temp_1(id); ----繼續創建,發現這次成功了。 insert into zjj_temp_1 values(2,''junjie'',''zhang'',25,4000); ----成功 insert into zjj_temp_1 values(1,''kesi'',''ma'',25,4000); 失?。。?! 耶!就是這樣! (3) 主鍵索引 數據庫表經常有一列或列組合,其值唯一標識表中的每一行。該列稱為表的主鍵。它和唯一索引的共性在于都不允許有重復記錄,區別在于,唯一索引是不限制null的,也就是說或可以有一條以上的null值插入,但是主鍵卻限定不能為空。 繼續執行語句: insert into zjj_temp_1 values(null,''kesi'',''ma'',25,4000); ---成功 select * from zjj_temp_1; 這就表明唯一索引是允許有空值的。 Drop index zjj_temp_1; ---刪除唯一索引 alter table zjj_temp_1 add constraint zjsy_1 primary key(id); ---建立主鍵 我們可以發現id有一條記錄為空,所以是無法建立主鍵的。 刪除那條空記錄就可以了。 (4) 聚簇索引和非聚簇索引 聚簇索引也叫簇類索引,是一種對磁盤上實際數據重新組織以按指定的一個或多個列的值排序。由于聚簇索引的索引頁面指針指向數據頁面,所以使用聚簇索引查找數據幾乎總是比使用非聚簇索引快。每張表只能建一個聚簇索引,并且建聚簇索引需要至少相當該表120%的附加空間,以存放該表的副本和索引中間頁。
聚簇是根據碼值找到數據的物理存儲位置,從而達到快速檢索數據的目的。Oracle聚簇索引的順序就是數據的物理存儲順序,葉節點就是數據節點。非聚簇索引的順序與數據物理排列順序無關,葉節點仍然是索引節點,只不過有一個指針指向對應的數據塊。由于聚簇索引要按照索引排序,所以一個表最多只能有一個聚簇索引,但可以使用多列。 ORACLE中的聚簇表是指兩個表有一個字段完全相同,并且在業務中經常會按這個字段為目標連接這兩個表,這時建立聚簇表, 這兩篇都有實際的例子,這里就不再深入討論了。 建立聚簇索引的思想 1、大多數表都應該有聚簇索引或使用分區來降低對表尾頁的競爭,在一個高事務的環境中,對最后一頁的封鎖嚴重影響系統的吞吐量。 2、在聚簇索引下,數據在物理上按順序排在數據頁上,重復值也排在一起,因而在那些包含范圍檢查(between、<、<=、<>、>=)或使用group by或orderby的查詢時,一旦找到具有范圍中第一個鍵值的行,具有后續索引值的行保證物理上毗連在一起而不必進一步搜索,避免了大范圍掃描,可以大大提高查詢速度。 3、在一個頻繁發生插入操作的表上建立聚簇索引時,不要建在具有單調上升值的列(如IDENTITY)上,否則會經常引起封鎖沖突。 4、在聚簇索引中不要包含經常修改的列,因為碼值修改后,數據行必須移動到新的位置。 5、選擇聚簇索引應基于where子句和連接操作的類型。 本文編輯:插秧機,http://www.cgjfg.com/ 該文章在 2020/3/3 1:59:26 編輯過 |
關鍵字查詢
相關文章
正在查詢... |