資料庫哪些列適合索引

㈠ MySql索引之哪些情況適合創建索引

說明：不要以為唯一索引影響了 insert 速度，這個速度損耗可以忽略，但提高查找速度是明顯的。

某個欄位在SELECT語句的 WHERE 條件中經常被使用到，那麼就需要給這個欄位創建索引了。尤其是在

數據量大的情況下，創建普通索引就可以大幅提升數據查詢的效率。

比如student_info數據表（含100萬條數據），假設我們想要查詢 student_id=123110 的用戶信息。

索引就是讓數據按照某種順序進行存儲或檢索，因此當我們使用 GROUP BY 對數據進行分組查詢，或者

使用 ORDER BY 對數據進行排序的時候，就需要 對分組或者排序的欄位進行索引 。如果待排序的列有多

個，那麼可以在這些列上建立 組合索引 。

對數據按照某個條件進行查詢後再進行 UPDATE 或 DELETE 的操作，如果對 WHERE 欄位創建了索引，就

能大幅提升效率。原理是因為我們需要先根據 WHERE 條件列檢索出來這條記錄，然後再對它進行更新或

刪除。如果進行更新的時候，更新的欄位是非索引欄位，提升的效率會更明顯，這是因為非索引欄位更

新不需要對索引進行維護。

有時候我們需要對某個欄位進行去重，使用 DISTINCT，那麼對這個欄位創建索引，也會提升查詢效率。

比如，我們想要查詢課程表中不同的 student_id 都有哪些，如果我們沒有對 student_id 創建索引，執行

SQL 語句：

運行結果（600637 條記錄，運行時間 0.683s ）：

如果我們對 student_id 創建索引，再執行 SQL 語句：

運行結果（600637 條記錄，運行時間 0.010s ）：

你能看到 SQL 查詢效率有了提升，同時顯示出來的 student_id 還是按照 遞增的順序 進行展示的。這是因

為索引會對數據按照某種順序進行排序，所以在去重的時候也會快很多。

首先， 連接表的數量盡量不要超過 3 張 ，因為每增加一張表就相當於增加了一次嵌套的循環，數量級增

長會非常快，嚴重影響查詢的效率。

其次， 對 WHERE 條件創建索引 ，因為 WHERE 才是對數據條件的過濾。如果在數據量非常大的情況下，

沒有 WHERE 條件過濾是非常可怕的。

最後， 對用於連接的欄位創建索引 ，並且該欄位在多張表中的 類型必須一致 。比如 course_id 在

student_info 表和 course 表中都為 int(11) 類型，而不能一個為 int 另一個為 varchar 類型。

舉個例子，如果我們只對 student_id 創建索引，執行 SQL 語句：

運行結果（1 條數據，運行時間 0.189s ）：

這里我們對 name 創建索引，再執行上面的 SQL 語句，運行時間為 0.002s 。

創建一張商戶表，因為地址欄位比較長，在地址欄位上建立前綴索引

問題是，截取多少呢？截取得多了，達不到節省索引存儲空間的目的；截取得少了，重復內容太多，字

段的散列度(選擇性)會降低。 怎麼計算不同的長度的選擇性呢？

先看一下欄位在全部數據中的選擇度：

通過不同長度去計算，與全表的選擇性對比：

公式：

例如：

引申另一個問題：索引列前綴對排序的影響

拓展：Alibaba《Java開發手冊》

【 強制 】在 varchar 欄位上建立索引時，必須指定索引長度，沒必要對全欄位建立索引，根據實際文本

區分度決定索引長度。

說明：索引的長度與區分度是一對矛盾體，一般對字元串類型數據，長度為 20 的索引，區分度會 高達

90% 以上 ，可以使用 count(distinct left(列名, 索引長度))/count(*)的區分度來確定。

這樣也可以較少的建立一些索引。同時，由於"最左前綴原則"，可以增加聯合索引的使用率。

結論：在數據表中的數據行數比較少的情況下，比如不到 1000 行，是不需要創建索引的。

舉例1：要在 100 萬行數據中查找其中的 50 萬行（比如性別為男的數據），一旦創建了索引，你需要先

訪問 50 萬次索引，然後再訪問 50 萬次數據表，這樣加起來的開銷比不使用索引可能還要大。

舉例2：假設有一個學生表，學生總數為 100 萬人，男性只有 10 個人，也就是占總人口的 10 萬分之 1。

學生表 student_gender 結構如下。其中數據表中的 student_gender 欄位取值為 0 或 1，0 代表女性，1 代

表男性。

如果我們要篩選出這個學生表中的男性，可以使用：

運行結果（10 條數據，運行時間 0.696s ）：

結論：當數據重復度大，比如 高於 10% 的時候，也不需要對這個欄位使用索引。

例如身份證、UUID(在索引比較時需要轉為ASCII，並且插入時可能造成頁分裂)、MD5、HASH、無序長字

符串等。

① 冗餘索引

舉例：建表語句如下

我們知道，通過 idx_name_birthday_phone_number 索引就可以對 name 列進行快速搜索，再創建一

個專門針對 name 列的索引就算是一個 冗餘索引 ，維護這個索引只會增加維護的成本，並不會對搜索有

什麼好處。

② 重復索引

另一種情況，我們可能會對某個列 重復建立索引 ，比方說這樣：

我們看到，col1 既是主鍵、又給它定義為一個唯一索引，還給它定義了一個普通索引，可是主鍵本身就

會生成聚簇索引，所以定義的唯一索引和普通索引是重復的，這種情況要避免。

歡迎共同進步：
QQ群：1007576722
https://huchao.blog.csdn.net/article/details/124220802?spm=1001.2014.3001.5502

㈡ 資料庫索引有哪幾種怎樣建立索引

• 種類：

1、按照索引列值的唯一性，索引可分為唯一索引和非唯一索引；

• 非唯一索引：

create index 索引名 on 表名（列名） tablespace表空間名；

• 唯一索引：

建立主鍵或者唯一約束時會自動在對應的列上建立唯一索引；

2、索引列的個數：單列索引和復合索引；

3、按照索引列的物理組織方式。

• 索引的創建格式：

CREATEUNIUQE|BITMAPINDEX<schema>.<index_name>ON<schema>.<table_name>(<column_name>|<expression>ASC|DESC,<column_name>|<expression>ASC|DESC,...)TABLESPACE<tablespace_name>STORAGE<storage_settings>LOGGING||COMPRESS<nn>NOSORT|REVERSEPARTITION|GLOBALPARTITION<partition_setting>

使用USER_IND_COLUMNS查詢某個TABLE中的相應欄位索引建立情況

使用DBA_INDEXES/USER_INDEXES查詢所有索引的具體設置情況。

在Oracle中的索引可以分為：B樹索引、點陣圖索引、反向鍵索引、基於函數的索引、簇索引、全局索引、局部索引等，下面逐一講解：

一、B樹索引：

最常用的索引，各葉子節點中包括的數據有索引列的值和數據表中對應行的ROWID，簡單的說，在B樹索引中，是通過在索引中保存排過續的索引列值與相對應記錄的ROWID來實現快速查詢的目的。其邏輯結構如圖：

可以保證無論用戶要搜索哪個分支的葉子結點，都需要經過相同的索引層次，即都需要相同的I/O次數。

B樹索引的創建示例：

create index ind_t on t1(id);

注1：索引的針對欄位創建的，相同欄位不能創建一個以上的索引；

注2：默認的索引是不唯一的，但是也可以加上unique，表示該索引的欄位上沒有重復值(定義unique約束時會自動創建)；

注3：創建主鍵時，默認在主鍵上創建了B樹索引，因此不能再在主鍵上創建索引。

二、點陣圖索引：

有些欄位中使用B樹索引的效率仍然不高，例如性別的欄位中，只有「男、女」兩個值，則即便使用了B樹索引，在進行檢索時也將返回接近一半的記錄。

所以當欄位的基數很低時，需要使用點陣圖索引。(「低」的標準是取值數量 < 行數*1%)

反向鍵索引是一種特殊的B樹索引，在存儲構造中與B樹索引完全相同，但是針對數值時，反向鍵索引會先反向每個鍵值的位元組，然後對反向後的新數據進行索引。例如輸入2008則轉換為8002，這樣當數值一次增加時，其反向鍵在大小中的分布仍然是比較平均的。

反向鍵索引的創建示例：

createindex ind_t on t1(id) reverse;

註：鍵的反轉由系統自行完成。對於用戶是透明的。

四、基於函數的索引：

有的時候，需要進行如下查詢：select * from t1 where to_char(date,'yyyy')>'2007';

但是即便在date欄位上建立了索引，還是不得不進行全表掃描。在這種情況下，可以使用基於函數的索引。其創建語法如下：

create index ind_t on t1(to_char(date,'yyyy'));

註：簡單來說，基於函數的索引，就是將查詢要用到的表達式作為索引項。

五、全局索引和局部索引：

這個索引貌似很復雜，其實很簡單。總得來說一句話，就是無論怎麼分區，都是為了方便管理。

具體索引和表的關系有三種：

1、局部分區索引：分區索引和分區表1對1

2、全局分區索引：分區索引和分區表N對N

3、全局非分區索引：非分區索引和分區表1對N

創建示例：

首先創建一個分區表

createtable student

(

stuno number(5),

sname vrvhar2(10),

deptno number(5)

)

partition by hash (deptno)

(

partition part_01 tablespace A1,

partition part_02 tablespace A2

);

創建局部分區索引(1v1)：

create index ind_t on student(stuno)

local(

partition part_01 tablespace A2,

partition part_02 tablespace A1

);--local後面可以不加

創建全局分區索引(NvN)：

create index ind_t on student(stuno)

globalpartition by range(stuno)

(

partition p1 values less than(1000) tablespace A1,

partition p2 values less than(maxvalue) tablespace A2

);--只可以進行range分區

創建全局非分區索引(1vN)

createindex ind_t on student(stuno) GLOBAL;