數(shù)據(jù)庫索引

文章目錄

• • 數(shù)據(jù)庫索引
  • • • 定義
      • 優(yōu)缺點
      • 索引類型
      • 建立普通索引或組合索引
      • 適合建立索引的情況
      • 索引失效的sql

定義

索引是對數(shù)據(jù)庫表中一列或多列的值進行排序的一種結(jié)構(gòu)，使用索引可快速訪問數(shù)據(jù)庫表中的特定信息。數(shù)據(jù)庫索引好比是一本書前面的目錄，能加快數(shù)據(jù)庫的查詢速度。索引分為聚簇索引和非聚簇索引兩種，聚簇索引是按照數(shù)據(jù)存放的物理位置為順序的，而非聚簇索引就不一樣了；聚簇索引能提高多行檢索的速度，而非聚簇索引對于單行的檢索很快。

本人對于索引理解較淺，如有錯誤或不足的地方，歡迎指正，共同學(xué)習(xí)。

優(yōu)缺點

優(yōu)點

• 在設(shè)計數(shù)據(jù)庫時，通過創(chuàng)建一個惟一的索引，能夠在索引和信息之間形成一對一的映射式的對應(yīng)關(guān)系，增加數(shù)據(jù)的惟一性特點。

• 能提高數(shù)據(jù)的搜索及檢索速度，符合數(shù)據(jù)庫建立的初衷。

• 能夠加快表與表之間的連接速度，這對于提高數(shù)據(jù)的參考完整性方面具有重要作用。

• 在信息檢索過程中，若使用分組及排序子句進行時，通過建立索引能有效的減少檢索過程中所需的分組及排序時間，提高檢索效率。

• 建立索引之后，在信息查詢過程中可以使用優(yōu)化隱藏器，這對于提高整個信息檢索系統(tǒng)的性能具有重要意義。

  缺點

• 在數(shù)據(jù)庫建立過程中，需花費較多的時間去建立并維護索引，特別是隨著數(shù)據(jù)總量的增加，所花費的時間將不斷遞增。

• 在數(shù)據(jù)庫中創(chuàng)建的索引需要占用一定的物理存儲空間，這其中就包括數(shù)據(jù)表所占的數(shù)據(jù)空間以及所創(chuàng)建的每一個索引所占用的物理空間，如果有必要建立起聚簇索引，所占用的空間還將進一步的增加

• 在對表中的數(shù)據(jù)進行修改時，例如對其進行增加、刪除或者是修改操作時，索引還需要進行動態(tài)的維護，這給數(shù)據(jù)庫的維護速度帶來了一定的麻煩。

索引類型

根據(jù)數(shù)據(jù)庫的功能，可以在數(shù)據(jù)庫設(shè)計器中創(chuàng)建三種索引：唯一索引、主鍵索引和聚集索引。有關(guān)數(shù)據(jù)庫所支持的索引功能的詳細信息，請參見數(shù)據(jù)庫文檔。

提示：盡管唯一索引有助于定位信息，但為獲得最佳性能結(jié)果，建議改用主鍵或唯一約束。

唯一索引

UNIQUE create unique index 索引名 on 表名（表中的列[(length)]） alter table 表名 add UNIQUE 索引名 (表中的列[(length)])

唯一索引是不允許其中任何兩行具有相同索引值的索引。當(dāng)現(xiàn)有數(shù)據(jù)中存在重復(fù)的鍵值時，大多數(shù)數(shù)據(jù)庫不允許將新創(chuàng)建的唯一索引與表一起保存。數(shù)據(jù)庫還可能防止添加將在表中創(chuàng)建重復(fù)鍵值的新數(shù)據(jù)。例如，如果在employee表中職員的姓(lname)上創(chuàng)建了唯一索引，則任何兩個員工都不能同姓。

主鍵索引

PRIMARY KEY -- 建表時自接指定 alert table 表名 add primary key (表中的列[(length)])

數(shù)據(jù)庫表經(jīng)常有一列或多列組合，其值唯一標(biāo)識表中的每一行。該列稱為表的主鍵。在數(shù)據(jù)庫關(guān)系圖中為表定義主鍵將自動創(chuàng)建主鍵索引，主鍵索引是唯一索引的特定類型。該索引要求主鍵中的每個值都唯一。當(dāng)在查詢中使用主鍵索引時，它還允許對數(shù)據(jù)的快速訪問。

聚集索引

在聚集索引中，表中行的物理順序與鍵值的邏輯（索引）順序相同。一個表只能包含一個聚集索引。如果某索引不是聚集索引，則表中行的物理順序與鍵值的邏輯順序不匹配。與非聚集索引相比，聚集索引通常提供更快的數(shù)據(jù)訪問速度。聚集索引和非聚集索引的區(qū)別，如字典默認按字母順序排序（物理順序），讀者如知道某個字的讀音可根據(jù)字母順序快速定位（索引順序與物理順序相同）。因此聚集索引和表的內(nèi)容是在一起的。如讀者需查詢某個生僻字，則需按字典前面的索引，舉例按偏旁進行定位，找到該字對應(yīng)的頁數(shù)，再打開對應(yīng)頁數(shù)找到該字。這種通過兩個地方而查詢到某個字的方式就如非聚集索引。

建立普通索引或組合索引

create index 索引名 on 表名(列名); create index 索引名 on 表名(列名1,列名2，..); -- 組合索引

組合索引 最左前綴 原則

個人理解 例如 ： create index index_abc on TEST_TAB(a,b,c); 相當(dāng)于建立了 a, ab, abc 三組索引。

只要某查詢條件中包含復(fù)合索引中的第一個列，該查詢就會走索引，如果不包含，就不會走索引

有博主的實驗 https://blog.csdn.net/tw7752/article/details/44595281

適合建立索引的情況

• 經(jīng)常作為查詢條件的列 （where）
• 經(jīng)常用于表連接的列，如外鍵 （join）
• 經(jīng)常用于排序的列 （order）

索引失效的sql

• 組合索引使用 or 索引失效 如 a=1 or b=2 or c=3

• 索引條件為 is null / is not null 索引失效(看清況，測試過 null 比較多時， not null 會走索引)

• 索引條件 like ‘%xxx’, 索引失效； like ‘xxxx%’ 索引生效

• 索引列參加計算 如 t.score/10 > 10 失效， 應(yīng)改成 t.score > 10*10

• 索引列不要使用NOT （ != 、 <> ）如 t.score! = 10 失效，改成：t.score > 10 or t.score < 10

• 索引列上發(fā)生類型轉(zhuǎn)換, 例如 VARCHAR2 類型的索引列 ，寫成 where id = 1 ,應(yīng)該 改成 where id = ‘1’ ( oracle實驗)

最好是 看一下sql的執(zhí)行計劃，看看是否走了索引。

Oracle查看sql執(zhí)行計劃

explain plan for select xxx from tablename where xxx ;select * from table(dbms_xplan.display);

索引的原理 請看大神博客 https://blog.csdn.net/sinat_30186009/article/details/52169057

組合索引設(shè)計 https://blog.csdn.net/bless2015/article/details/84035845

總結(jié)

以上是生活随笔為你收集整理的数据库索引的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。