好程式設計師 Java分享SQL語言之索引，前言：本章我們將學習 MySQL中的索引，本文將從索引的作用、索引的分類、建立索引的語法、索引的使用策略以及索引的實現原理等方面帶大家瞭解索引。

索引的作用

索引的作用就是加快查詢速度，如果把使用了索引的查詢看做是法拉利跑車的話，那麼沒有用索引的查詢就相當於是腳踏車。目前實際專案中表的資料量越來越大，動輒上百萬上千萬級別，沒有索引的查詢會變得非常緩慢，使用索引成為了查詢最佳化的必選專案。

索引的概念

索引其實是一種特殊的資料，也儲存在資料庫檔案中，索引資料儲存著資料表中實際資料的位置。類似書籍前面的目錄，這個目錄就儲存了書中各個章節的頁數，透過檢視目錄我們可以快速定位章節的頁數，從而加快查詢速度。

我們來看一段查詢語句：

select * from book where id = 1000000;

假設書籍表中有幾百萬行資料，沒索引的查詢會遍歷前面的 100萬行資料找到結果，如果我們在id上建立主鍵索引，則直接在索引上定位結果，速度要快得多。

索引的優缺點

優點：提高查詢速度

缺點：本身也是資料，會佔用磁碟空間；索引的建立和維護也需要時間成本；進行刪除、更新和插入操作時，因為要維護索引，所以速度會降低。

使用索引的語法

建立索引

建表的同時建立索引

create table 表名

(

欄位名型別 ,

...

欄位名型別 ,

index 索引名稱 (欄位名)

);

建表後新增索引

alter table 表名 add index 索引名(欄位名);

或

create index 索引名 on 表名(欄位名);

刪除索引

alter table 表名 drop index 索引名;

或

drop index 索引名 on 表名;

檢視錶中的索引

show index from 表名;

檢視查詢語句使用的索引

explain 查詢語句;

索引的分類

索引按功能分為：

普通索引，在普通欄位上建立的索引，沒有任何限制

主鍵索引，建立主鍵時，自動建立的索引，不能為空，不能重複

唯一索引，建立索引的欄位資料必須是唯一的，允許空值

全文索引，在大文字型別（ Text）欄位上建立的索引

組合索引，組合多個列建立的索引，多個列不能有空值

程式碼示例：

-- 建立書籍表

create table tb_book

(

-- 建立主鍵索引

id int primary key,

-- 建立唯一索引

title varchar(100) unique,

author varchar(20),

content Text,

time datetime,

-- 普通索引

index ix_title (title),

-- 全文索引

fulltext index ix_content(content),

-- 組合索引

index ix_title_author(title,author)

);

-- 建表後新增主鍵索引

ALTER TABLE tb_book ADD PRIMARY KEY pk_id(id)；

-- 建表後新增唯一索引

ALTER TABLE tb_book ADD UNIQUE index ix_title(title)；

-- 建表後新增全文索引

ALTER TABLE tb_book ADD FULLTEXT index ix_content(content)；

-- 查詢時使用全文索引

SELECT * FROM tb_book MATCH(content) ANGAINST(‘勝利’);

-- 建表後新增組合索引

ALTER TABLE tb_book ADD INDEX ix_book(title,author)；

注意：建立組合索引時，要遵循”最左字首”原則，把最常查詢、排序的欄位放左邊，按重要性依次遞減。

索引的使用策略

什麼情況下要建立索引？

1）在經常需要查詢和排序的欄位上建立索引

2）資料特別多

什麼情況下不要建立索引？

1）欄位資料存在大量的重複，如：性別

2）資料很少

3）經常需要增刪改的欄位

什麼情況下索引會失效？

1）模糊查詢時，使用 like ‘%張%’ 會失效，而 like ‘ 張 %’不會

2）使用 is null或is not null查詢時

3）使用組合索引時，某個欄位為 null

4）使用 or查詢多個條件時

5）在函式中使用欄位時，如 where year(time) = 2019

索引的結構

不同的儲存引擎使用不同結構的索引：

聚簇索引， InnoDB支援，索引的順序和資料的物理順序一致，類似新華字典中的拼音目錄排列和漢字排列順序一致，聚簇索引一個表中只能有一個。

非聚簇索引， MyISAM支援，索引順序和資料的物理順序不一致，類似新華字典中的偏旁部首目錄和漢字排列順序不一致，非聚簇索引表可以有多個。

索引的資料結構主要是： BTree和B+Tree

BTree的資料結構如下，是一種平衡搜尋多叉樹，每個節點由key和data組成，key是索引的鍵，data是鍵對應的資料，在節點的兩邊是兩個指標，指向另外的索引位置，而所有的鍵都是排序過的，這樣在搜尋索引時，可以使用二分查詢，速度比較快，時間複雜度是h*log(n)，h是樹的高度，BTree是一種比較高效的搜尋結構。

B+Tree的資料結構如下，是BTree的升級版，區別是非葉子節點不在儲存具體的資料，只儲存索引的鍵，資料儲存到葉子節點中，並且葉子節點中沒有指標只有鍵和資料。B+Tree的優點是：搜尋效率更高，因為非葉子節點中沒有儲存資料，就可以儲存更多的鍵，每一層的鍵越多，樹的高度就會減少，這樣查詢速度就會提升。

總結

索引是提高查詢速度的重要手段，本章我們學習了索引的分類和建立語法，以及使用索引的策略，不是所有的表都適合建立索引，最後我們還學習了索引的內部結構，這樣大家對索引會有一個基本的認識。

好程式設計師Java分享SQL語言之索引