什么是數據庫索引？MySQL官方對索引的定義及索引優劣勢分析

1、索引

1.1、索引概述：

MySQL官方對索引的定義：索引（index）是幫助MySQL高效獲取數據的數據結構（有效），在數據之外，數據庫系統還維護著滿足特定查找算法的數據結構，這些數據結構以某種方式引用（指向）數據， 這樣就可以在這些數據結構上實現高級查找算法，這種數據結構就是索引。簡而言之：幫助MySQL高效的查詢出數據的數據結構叫做索引。

【資料圖】

一般來說，索引本身也很大，不可能全部都存儲在內存中，因此索引一般都是以索引文件的形式存儲在磁盤上。索引是數據庫用來提高性能最好的方式。

1.2、索引優劣勢

優勢：

索引類似于書籍的目錄，提高數據檢索的效率，減少數據庫IO的成本通過索引列對數據進行排序，降低數據排序的成本，降低CPU的消耗

劣勢：

實際上索引也是一張表，存儲在磁盤上，該表保存了主鍵與索引字段，并指向實體類的記錄。雖然索引大大提高了查詢的速度，但是降低了增刪改的速度，對表進行update、insert、delete時，需要對索引文件進行更新

1.3、索引結構

索引是在MySQL的存儲引擎中實現的，而不是服務層實現的。所以每種存儲引擎的索引都是不一樣的，也不是所有的存儲引擎都支持所有的索引類型。MySQL目前提供了以下4種索引：

BTREE 索引： 最常見的索引類型，大部分索引都支持 B 樹索引。HASH 索引：只有Memory引擎支持 ， 使用場景簡單 。R-tree 索引（空間索引）：空間索引是MyISAM引擎的一個特殊索引類型，主要用于地理空間數據類型，通常使用較少，不做特別介紹。Full-text （全文索引）：全文索引也是MyISAM的一個特殊索引類型，主要用于全文索引，InnoDB從Mysql5.6版本開始支持全文索引。

MyISAM、InnoDB、Memory三種存儲引擎對各種索引類型的支持

索引InnoDB引擎MyISAM引擎Memory引擎

BTREE索引支持支持支持

HASH 索引不支持不支持支持

R-tree 索引不支持支持不支持

Full-text5.6版本之后支持支持不支持

在平常，如果沒有特殊的說明，都是使用B+樹結構組織的索引。其中聚集索引、復合索引、前綴索引、唯一索引默認都是使用 B+tree 索引。

1.3.1、BTREE樹

Btree樹的特點：

BTree又叫多路平衡搜索樹，一顆m叉的BTree特征如下：

樹中每個節點最多包含m個孩子除根節點與葉子節點外，每個節點至少有[ceil(m/2)]個孩子若根節點不是葉子節點，至少含有兩個孩子所有的葉子節點都在同一層每個非葉子節點由n個key與n+1個指針組成，其中[ceil(m/2)-1]<=n<=m-1

舉個例子，以3叉BTree為例，插入1~10數字

（1）、插入1,2的時候

（2）、插入3 （3）、插入4 （4）、插入5 （5）、插入6 （6）、插入7 （7）、插入8 （8）、插入9 （9）、插入10

1.3.2、B+TREE樹

B+Tree為BTree的變體，B+Tree與Tree的區別為：

n叉B+Tree最多含有n個key，而BTree最毒含有n-1個key。B+Tree的葉子節點保存所有的key信息，依key大小順序排列。所有的非葉子節點都可以看作是key的索引部分。

在數據庫的聚集索引（Clustered Index）中，葉子節點直接包含衛星數據。在非聚集索引（NonClustered Index）中，葉子節點帶有指向衛星數據的指針。

1.3.2、MYSQL中的B+Tree

MySql索引數據結構對經典的B+Tree進行了優化。在原B+Tree的基礎上，增加一個指向相鄰葉子節點的鏈表指針，就形成了帶有順序指針的B+Tree，提高區間訪問的性能。

1.4、索引分類

（1）、單值索引：及一個索引值包含單個列，一個表可以有多個單列索引 （2）、唯一索引：索引列的值必須唯一，但允許有控制 （3）、復合索引：即一個索引包含多個列

1.5、索引的用法

創建索引

CREATE [UNIQUE|FULLTEXT|SPATIAL]  INDEX index_name [USING  index_type]ON tbl_name(index_col_name,...)

查看索引

show index  from  table_name;

刪除索引

DROP  INDEX  索引名稱  ON  表名;

一些alter命令

1). alter  table  tb_name  add  primary  key(column_list); 該語句添加一個主鍵，這意味著索引值必須是唯一的，且不能為NULL2). alter  table  tb_name  add  unique index_name(column_list);這條語句創建索引的值必須是唯一的（除了NULL外，NULL可能會出現多次）3). alter  table  tb_name  add  index index_name(column_list);添加普通索引， 索引值可以出現多次。4). alter  table  tb_name  add  fulltext  index_name(column_list);該語句指定了索引為FULLTEXT， 用于全文索引

1.6、索引的設計原則

我們在設計索引的時候，有一些原則，創建索引的以后盡量符合這些原則。便于提升索引的使用效率，更高效的使用索引：

對于查詢頻率比較高的列，可以設置為索引列索引字段的選擇，最佳應從where條件語句中提取，如果where子句中組合比較多，那么應當挑選最常用、效率效果最好的列的組合使用唯一索引，區分度越高，使用索引的效率越高索引可以有效的提升查詢數據的效率，但是并不是索引越多越好，索引越多，維護起來月麻煩。對于DML操作而言，會引起相當高的維護代價，可以使用段索引，索引創建之后也是使用磁盤來進行存儲的，因此提升索引訪問的I/O效率，也可以提升總體的訪問效率。假如構成索引的字段的總長度比較短，那么在給定大小的存儲塊可以存儲更多的索引值，相應的可以有效的提升MySQL訪問索引的I/O效率利用最左前綴，N個列組合而成的組合索引，那么相當于是創建了N個索引，如果查詢時where子句中使用了組成該索引的前幾個字段，那么這條查詢SQL可以利用組合索引來提升查詢效率

創建復合索引:CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);就相當于對name 創建索引 ;對name , email 創建了索引 ;對name , email, status 創建了索引 ;