MySQL資料庫入門——索引概念

索引號是什麼意思

一、基本概念：

為什麼要有索引？

在生產環境中讀寫比例大約是10比1，寫操作較少出現效能問題，出問題最多的一般是複雜查詢。因此對查詢語句的最佳化尤為重要，為了提高查詢的效率所以有了索引這項技術。

什麼是索引？

索引在MySQL中也叫做“鍵”（key），是儲存引擎為了提高查詢效率而運用的一種技術。索引在資料庫中的資料越多時作用越大，索引是提高查詢效率最簡單有效的手段。正確地運用索引可以讓查詢效能提升很多倍。

索引的利弊？

索引是資料庫設計和管理的一個重點。在應用程式開發初期就要對資料庫結構及索引做良好的規劃，儘量避免在事後胡亂新增索引。資料庫管理系統維護索引存在一定的系統開銷，每多一條索引系統開銷也會相應地增加。索引過多反而會降低整體效能。

二、索引原理：

索引的作用是提高查詢效率，使用的方法在本質上就是對資料按某種規則進行編號儲存，查詢時按編號查詢即可實現快速定位並獲取相關資料。

為了更好地學習索引原理，要先學習資料結構中的許多概念：

二分查詢法：

兩分法是一種快速查詢資料的演算法。

二叉樹：

平衡樹：

平衡樹（Balance Tree，BT） 指的是，任意節點的子樹的高度差都小於等於1。常見的符合平衡樹的有，B樹（多路平衡搜尋樹）、AVL樹（二叉平衡搜尋樹）等。平衡樹在完成集合的一系列操作中始終保持平衡，為大型資料庫的組織、索引提供了一條新的途徑。

B樹：

B樹是一種自平衡的搜尋樹；每個節點既儲存索引又儲存資料；搜尋時相當於二分查詢。

B+樹：

B+樹與B樹非常相似，只有2點區別：一是隻有葉子節點儲存資料；二是有指向相鄰節點的指標。

三、聚集索引和輔助索引

聚集索引（clustered index）

聚集索引一個表只能有一個；

聚集索引的儲存記錄是物理上連續存在；

聚集索引在插入資料時比輔助索引慢（插入資料時要做平衡樹的排序），查詢速度比輔助索引快。

輔助索引（secondary index）

輔助索引一個表可以存在多個；

輔助索引的儲存記錄是邏輯上的連續而物理儲存並不連續；

輔助索引在插入資料時比聚集索引快，查詢速度比聚集索引慢。

四、MySQL四種常見索引型別

普通索引：

normal index：普通索引，一遍在某欄位經常被作為查詢條件使用，且該欄位中的值重複率較低（10%左右）時可以使用，在重複率大於20%的欄位建索引效果不佳。

唯一索引：

primary key：主鍵索引，加速查詢+約束（不為空、不能重複），資料表中有且僅有一個主鍵欄位，主鍵自帶三重屬性：索引、非空、唯一。

unique：唯一索引，加速查詢+約束（不能重複），資料表中可以有多個unique欄位。

聯合索引：

primary key（id，name）：聯合主鍵索引

unique（id，name）：聯合唯一索引

index（id，name）：聯合普通索引

全文索引：

full text：全文索引，在檢索長文字的時候效果最好，短文字建議使用index。

使用全文索引匯入大量資料時，先導資料再建索引比先建索引再導資料要快得多。

全文索引的特性比較多，系統開銷非常大，非常影響整體效能，使用全文索引一定要慎重。

另外：

spatial：空間索引是對空間資料型別欄位建立的索引。注意：空間索引只能在儲存引擎為myisam的表中建立。mysql5。7以後大多數場合使用的是innodb，所以spatial空間索引不必細究。

五、建立/刪除索引語法

# 方法一：建表時建索引：create table 表名（ 欄位名1 資料型別 ［完整性約束條件…］， 欄位名2 資料型別 ［完整性約束條件…］， ［unique 或 fulltext 或 spatial］ index 或 key ［索引名］ （欄位名［（長度）］ ［asc預設增序|desc降序］） ）；# 方法二：建表後加索引：create ［unique | fulltext | spatial ］ index 索引名 on 表名 （欄位名［（長度）］ ［asc預設增序|desc降序］） ；# 方法三：建表後加索引：alter table 表名 add ［unique | fulltext | spatial ］ index 索引名 （欄位名［（長度）］ ［asc預設增序|desc降序］） ；# 上面的方法二和方法三的效果一致，使用時二選一即可。 # 刪除索引：drop index 索引名 on 表名字；

六、正確使用索引

索引失效的常見情況：

查詢條件沒使用索引欄位。

建立索引時應該對區分度較大（重複率10%及以下）的欄位建立。

範圍越大越慢；範圍越小越快。

範圍搜尋用到like時，對被索引欄位使用like ‘a%‘快，使用’%a’慢。因為搜尋比較時總是從左到右進行的，所以只要第一個值不確定速度就慢。

索引欄位參與計算或使用函式。

and和or的邏輯。多個條件的組合，若用and連線，其中一列有索引都能加快查詢速度；若用or連線，需要所有列都有索引才能加快查詢速度。

聯合索引，最左字首原則。查詢條件使用最左邊的欄位就能生效。但不能帶範圍查詢，用範圍查整條索引失效。

型別不一致。如索引欄位是字元型，查詢條件寫數字型會導致索引失效。

排序條件為索引欄位，那麼select欄位也必須是索引欄位，否則會導致索引失效。