前一陣子，又跑出去搞了一場面試，心態算是崩了，關于MySQL索引的原理及使用被面試官懟的體無完膚，立志要總結一番，然后一直沒有時間（其實是懶……），準備好了嗎？

無語，我差點被面試官懟壞了，又給我問到MySQL索引

一、MySQL中索引的語法

創建索引

在創建表的時候添加索引

CREATE TABLE mytable(  
    ID INT NOT NULL,   
    username VARCHAR(16) NOT NULL,  
    INDEX [indexName] (username(length))  
);

在創建表以后添加索引

ALTER TABLE my_table ADD [UNIQUE] INDEX index_name(column_name);
或者CREATE INDEX index_name ON my_table(column_name);

注意：

1、索引需要占用磁盤空間，因此在創建索引時要考慮到磁盤空間是否足夠

2、創建索引時需要對表加鎖，因此實際操作中需要在業務空閑期間進行

根據索引查詢

具體查詢：
SELECT * FROM table_name WHERE column_1=column_2;(為column_1建立了索引)
 或者模糊查詢SELECT * FROM table_name WHERE column_1 LIKE '%三'
SELECT * FROM table_name WHERE column_1 LIKE '三%'
SELECT * FROM table_name WHERE column_1 LIKE '%三%'
 SELECT * FROM table_name WHERE column_1 LIKE '_好_'
 如果要表示在字串中既有A又有B，那么查詢陳述句為：SELECT * FROM table_name WHERE column_1 LIKE '%A%' AND column_1 LIKE '%B%';
 SELECT * FROM table_name WHERE column_1 LIKE '[張李王]三';  //表示column_1中有匹配張三、李三、王三的都可以
SELECT * FROM table_name WHERE column_1 LIKE '[^張李王]三';  //表示column_1中有匹配除了張三、李三、王三的其他三都可以
 //在模糊查詢中，%表示任意0個或多個字符；_表示任意單個字符（有且僅有），通常用來限制字串長度;[]表示其中的某一個字符；[^]表示除了其中的字符的所有字符 或者在全文索引中模糊查詢SELECT * FROM table_name WHERE MATCH(content) AGAINST('word1','word2',...);

洗掉索引

DROP INDEX my_index ON tablename；
或者ALTER TABLE table_name DROP INDEX index_name;

查看表中的索引

SHOW INDEX FROM tablename

查看查詢陳述句使用索引的情況

//explain 加查詢陳述句
explain SELECT * FROM table_name WHERE column_1='123';

二、索引的優缺點

優勢：可以快速檢索，減少I/O次數，加快檢索速度；根據索引分組和排序，可以加快分組和排序；

劣勢：索引本身也是表，因此會占用存盤空間，一般來說，索引表占用的空間的資料表的1.5倍；索引表的維護和創建需要時間成本，這個成本隨著資料量增大而增大；構建索引會降低資料表的修改操作（洗掉，添加，修改）的效率，因為在修改資料表的同時還需要修改索引表；

三、索引的分類

常見的索引型別有：主鍵索引、唯一索引、普通索引、全文索引、組合索引

1、主鍵索引：即主索引，根據主鍵pk_clolum（length）建立索引，不允許重復，不允許空值；

ALTER TABLE 'table_name' ADD PRIMARY KEY pk_index('col')；

2、唯一索引：用來建立索引的列的值必須是唯一的，允許空值

ALTER TABLE 'table_name' ADD UNIQUE index_name('col')；

3、普通索引：用表中的普通列構建的索引，沒有任何限制

ALTER TABLE 'table_name' ADD INDEX index_name('col')；

4、全文索引：用大文本物件的列構建的索引（下一部分會講解）

ALTER TABLE 'table_name' ADD FULLTEXT INDEX ft_index('col')；

5、組合索引：用多個列組合構建的索引，這多個列中的值不允許有空值

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')；

*遵循“最左前綴”原則，把最常用作為檢索或排序的列放在最左，依次遞減，組合索引相當于建立了col1,col1col2,col1col2col3三個索引，而col2或者col3是不能使用索引的，

*在使用組合索引的時候可能因為列名長度過長而導致索引的key太大，導致效率降低，在允許的情況下，可以只取col1和col2的前幾個字符作為索引

ALTER TABLE 'table_name' ADD INDEX index_name(col1(4),col2（3))；

表示使用col1的前4個字符和col2的前3個字符作為索引

四、索引的實作原理

MySQL支持諸多存盤引擎，而各種存盤引擎對索引的支持也各不相同，因此MySQL資料庫支持多種索引型別，如BTree索引，B+Tree索引，哈希索引，全文索引等等，

1、哈希索引：

只有memory（記憶體）存盤引擎支持哈希索引，哈希索參考索引列的值計算該值的hashCode，然后在hashCode相應的位置存執該值所在行資料的物理位置，因為使用散列演算法，因此訪問速度非常快，但是一個值只能對應一個hashCode，而且是散列的分布方式，因此哈希索引不支持范圍查找和排序的功能，

2、全文索引：

FULLTEXT（全文）索引，僅可用于MyISAM和InnoDB，針對較大的資料，生成全文索引非常的消耗時間和空間，對于文本的大物件，或者較大的CHAR型別的資料，如果使用普通索引，那么匹配文本前幾個字符還是可行的，但是想要匹配文本中間的幾個單詞，那么就要使用LIKE %word%來匹配，這樣需要很長的時間來處理，回應時間會大大增加，這種情況，就可使用時FULLTEXT索引了，在生成FULLTEXT索引時，會為文本生成一份單詞的清單，在索引時及根據這個單詞的清單來索引，FULLTEXT可以在創建表的時候創建，也可以在需要的時候用ALTER或者CREATE INDEX來添加：

//創建表的時候添加FULLTEXT索引
CTREATE TABLE my_table(
    id INT(10) PRIMARY KEY,
    name VARCHAR(10) NOT NULL,
    my_text TEXT,
    FULLTEXT(my_text)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

//創建表以后，在需要的時候添加FULLTEXT索引
ALTER TABLE my_table ADD FULLTEXT INDEX ft_index(column_name);

全文索引的查詢也有自己特殊的語法，而不能使用LIKE %查詢字串%的模糊查詢語法

SELECT * FROM table_name MATCH(ft_index) AGAINST('查詢字串');

注意：

*對于較大的資料集，把資料添加到一個沒有FULLTEXT索引的表，然后添加FULLTEXT索引的速度比把資料添加到一個已經有FULLTEXT索引的表快，

*5.6版本前的MySQL自帶的全文索引只能用于MyISAM存盤引擎，如果是其它資料引擎，那么全文索引不會生效，5.6版本之后InnoDB存盤引擎開始支持全文索引

*在MySQL中，全文索引支隊英文有用，目前對中文還不支持，5.7版本之后通過使用ngram插件開始支持中文，

*在MySQL中，如果檢索的字串太短則無法檢索得到預期的結果，檢索的字串長度至少為4位元組，此外，如果檢索的字符包括停止詞，那么停止詞會被忽略，

3、BTree索引和B+Tree索引

BTree索引

BTree是平衡搜索多叉樹，設樹的度為2d（d>1），高度為h，那么BTree要滿足以一下條件：

每個葉子結點的高度一樣，等于h；
每個非葉子結點由n-1個key和n個指標point組成，其中d<=n<=2d,key和point相互間隔，結點兩端一定是key；
葉子結點指標都為null；
非葉子結點的key都是[key,data]二元組，其中key表示作為索引的鍵，data為鍵值所在行的資料；

BTree的結構如下：

無語，我差點被面試官懟壞了，又給我問到MySQL索引

在BTree的機構下，就可以使用二分查找的查找方式，查找復雜度為h*log(n)，一般來說樹的高度是很小的，一般為3左右，因此BTree是一個非常高效的查找結構，

B+Tree索引

B+Tree是BTree的一個變種，設d為樹的度數，h為樹的高度，B+Tree和BTree的不同主要在于：

B+Tree中的非葉子結點不存盤資料，只存盤鍵值；
B+Tree的葉子結點沒有指標，所有鍵值都會出現在葉子結點上，且key存盤的鍵值對應data資料的物理地址；
B+Tree的每個非葉子節點由n個鍵值key和n個指標point組成；

B+Tree的結構如下：

無語，我差點被面試官懟壞了，又給我問到MySQL索引

B+Tree對比BTree的優點：

1、磁盤讀寫代價更低

一般來說B+Tree比BTree更適合實作外存的索引結構，因為存盤引擎的設計專家巧妙的利用了外存（磁盤）的存盤結構，即磁盤的最小存盤單位是扇區（sector），而作業系統的塊（block）通常是整數倍的sector，作業系統以頁（page）為單位管理記憶體，一頁（page）通常默認為4K，資料庫的頁通常設定為作業系統頁的整數倍，因此索引結構的節點被設計為一個頁的大小，然后利用外存的“預讀取”原則，每次讀取的時候，把整個節點的資料讀取到記憶體中，然后在記憶體中查找，已知記憶體的讀取速度是外存讀取I/O速度的幾百倍，那么提升查找速度的關鍵就在于盡可能少的磁盤I/O，那么可以知道，每個節點中的key個數越多，那么樹的高度越小，需要I/O的次數越少，因此一般來說B+Tree比BTree更快，因為B+Tree的非葉節點中不存盤data，就可以存盤更多的key，

2、查詢速度更穩定

由于B+Tree非葉子節點不存盤資料（data），因此所有的資料都要查詢至葉子節點，而葉子節點的高度都是相同的，因此所有資料的查詢速度都是一樣的，

更多作業系統內容參考：

硬碟結構

扇區、塊、簇、頁的區別

作業系統層優化（進階，初學不用看）

帶順序索引的B+TREE

很多存盤引擎在B+Tree的基礎上進行了優化，添加了指向相鄰葉節點的指標，形成了帶有順序訪問指標的B+Tree，這樣做是為了提高區間查找的效率，只要找到第一個值那么就可以順序的查找后面的值，

B+Tree的結構如下：

無語，我差點被面試官懟壞了，又給我問到MySQL索引

聚簇索引和非聚簇索引

分析了MySQL的索引結構的實作原理，然后我們來看看具體的存盤引擎怎么實作索引結構的，MySQL中最常見的兩種存盤引擎分別是MyISAM和InnoDB，分別實作了非聚簇索引和聚簇索引，

聚簇索引的解釋是:聚簇索引的順序就是資料的物理存盤順序

非聚簇索引的解釋是:索引順序與資料物理排列順序無關

（這樣說起來并不好理解，讓人摸不著頭腦，清繼續看下文，并在插圖下方對上述兩句話有解釋）

首先要介紹幾個概念，在索引的分類中，我們可以按照索引的鍵是否為主鍵來分為“主索引”和“輔助索引”，使用主鍵鍵值建立的索引稱為“主索引”，其它的稱為“輔助索引”，因此主索引只能有一個，輔助索引可以有很多個，

MyISAM——非聚簇索引

MyISAM存盤引擎采用的是非聚簇索引，非聚簇索引的主索引和輔助索引幾乎是一樣的，只是主索引不允許重復，不允許空值，他們的葉子結點的key都存盤指向鍵值對應的資料的物理地址，
非聚簇索引的資料表和索引表是分開存盤的，
非聚簇索引中的資料是根據資料的插入順序保存，因此非聚簇索引更適合單個資料的查詢，插入順序不受鍵值影響，
只有在MyISAM中才能使用FULLTEXT索引，(mysql5.6以后innoDB也支持全文索引)

最開始我一直不懂既然非聚簇索引的主索引和輔助索引指向相同的內容，為什么還要輔助索引這個東西呢，后來才明白索引不就是用來查詢的嗎，用在那些地方呢，不就是WHERE和ORDER BY 陳述句后面嗎，那么如果查詢的條件不是主鍵怎么辦呢，這個時候就需要輔助索引了，

InnoDB——聚簇索引

聚簇索引的主索引的葉子結點存盤的是鍵值對應的資料本身，輔助索引的葉子結點存盤的是鍵值對應的資料的主鍵鍵值，因此主鍵的值長度越小越好，型別越簡單越好，
聚簇索引的資料和主鍵索引存盤在一起，
聚簇索引的資料是根據主鍵的順序保存，因此適合按主鍵索引的區間查找，可以有更少的磁盤I/O，加快查詢速度，但是也是因為這個原因，聚簇索引的插入順序最好按照主鍵單調的順序插入，否則會頻繁的引起頁分裂，嚴重影響性能，
在InnoDB中，如果只需要查找索引的列，就盡量不要加入其它的列，這樣會提高查詢效率，

使用主索引的時候，更適合使用聚簇索引，因為聚簇索引只需要查找一次，而非聚簇索引在查到資料的地址后，還要進行一次I/O查找資料，

*因為聚簇輔助索引存盤的是主鍵的鍵值，因此可以在資料行移動或者頁分裂的時候降低成本，因為這時不用維護輔助索引，但是由于主索引存盤的是資料本身，因此聚簇索引會占用更多的空間，

*聚簇索引在插入新資料的時候比非聚簇索引慢很多，因為插入新資料時需要檢測主鍵是否重復，這需要遍歷主索引的所有葉節點，而非聚簇索引的葉節點保存的是資料地址，占用空間少，因此分布集中，查詢的時候I/O更少，但聚簇索引的主索引中存盤的是資料本身，資料占用空間大，分布范圍更大，可能占用好多的扇區，因此需要更多次I/O才能遍歷完畢，

下圖可以形象的說明聚簇索引和非聚簇索引的區別

無語，我差點被面試官懟壞了，又給我問到MySQL索引

從上圖中可以看到聚簇索引的輔助索引的葉子節點的data存盤的是主鍵的值，主索引的葉子節點的data存盤的是資料本身，也就是說資料和索引存盤在一起，并且索引查詢到的地方就是資料（data）本身，那么索引的順序和資料本身的順序就是相同的；

而非聚簇索引的主索引和輔助索引的葉子節點的data都是存盤的資料的物理地址，也就是說索引和資料并不是存盤在一起的，資料的順序和索引的順序并沒有任何關系，也就是索引順序與資料物理排列順序無關，

此外MyISAM和innoDB的區別總結如下：

無語，我差點被面試官懟壞了，又給我問到MySQL索引

總結如下：

InnoDB 支持事務，支持行級別鎖定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事務，支持表級別鎖定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；

此外，Memory 不支持事務，支持表級別鎖定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；

五、索引的使用策略

什么時候要使用索引？

主鍵自動建立唯一索引；
經常作為查詢條件在WHERE或者ORDER BY 陳述句中出現的列要建立索引；
作為排序的列要建立索引；
查詢中與其他表關聯的欄位，外鍵關系建立索引
高并發條件下傾向組合索引；
用于聚合函式的列可以建立索引，例如使用了max(column_1)或者count(column_1)時的column_1就需要建立索引

什么時候不要使用索引？

經常增刪改的列不要建立索引；
有大量重復的列不建立索引；
表記錄太少不要建立索引，只有當資料庫里已經有了足夠多的測驗資料時，它的性能測驗結果才有實際參考價值，如果在測驗資料庫里只有幾百條資料記錄，它們往往在執行完第一條查詢命令之后就被全部加載到記憶體里，這將使后續的查詢命令都執行得非常快--不管有沒有使用索引，只有當資料庫里的記錄超過了1000條、資料總量也超過了MySQL服務器上的記憶體總量時，資料庫的性能測驗結果才有意義，

索引失效的情況：

在組合索引中不能有列的值為NULL，如果有，那么這一列對組合索引就是無效的，
在一個SELECT陳述句中，索引只能使用一次，如果在WHERE中使用了，那么在ORDER BY中就不要用了，
LIKE操作中，'%aaa%'不會使用索引，也就是索引會失效，但是‘aaa%’可以使用索引，
在索引的列上使用運算式或者函式會使索引失效，例如：select * from users where YEAR(adddate)<2007，將在每個行上進行運算，這將導致索引失效而進行全表掃描，因此我們可以改成：select * from users where adddate<’2007-01-01′，其它通配符同樣，也就是說，在查詢條件中使用正則運算式時，只有在搜索模板的第一個字符不是通配符的情況下才能使用索引，
在查詢條件中使用不等于，包括<符號、>符號和！=會導致索引失效，特別的是如果對主鍵索引使用！=則不會使索引失效，如果對主鍵索引或者整數型別的索引使用<符號或者>符號不會使索引失效，（經erwkjrfhjwkdb同學提醒，不等于，包括<符號、>符號和！，如果占總記錄的比例很小的話，也不會失效）
在查詢條件中使用IS NULL或者IS NOT NULL會導致索引失效，
字串不加單引號會導致索引失效，更準確的說是型別不一致會導致失效，比如欄位email是字串型別的，使用WHERE email=99999 則會導致失敗，應該改為WHERE email='99999'，
在查詢條件中使用OR連接多個條件會導致索引失效，除非OR鏈接的每個條件都加上索引，這時應該改為兩次查詢，然后用UNION ALL連接起來，
如果排序的欄位使用了索引，那么select的欄位也要是索引欄位，否則索引失效，特別的是如果排序的是主鍵索引則select * 也不會導致索引失效，
盡量不要包括多列排序，如果一定要，最好為這佇列構建組合索引；

六、索引的優化

1、最左前綴

索引的最左前綴和和B+Tree中的“最左前綴原理”有關，舉例來說就是如果設定了組合索引<col1,col2,col3>那么以下3中情況可以使用索引：col1，<col1,col2>，<col1,col2,col3>，其它的列，比如<col2,col3>，<col1,col3>，col2，col3等等都是不能使用索引的，

根據最左前綴原則，我們一般把排序分組頻率最高的列放在最左邊，以此類推，

2、帶索引的模糊查詢優化

在上面已經提到，使用LIKE進行模糊查詢的時候，'%aaa%'不會使用索引，也就是索引會失效，如果是這種情況，只能使用全文索引來進行優化（上文有講到），

3、為檢索的條件構建全文索引，然后使用

SELECT * FROM tablename MATCH(index_colum) ANGAINST(‘word’);

4、使用短索引

對串列進行索引，如果可能應該指定一個前綴長度，例如，如果有一個CHAR(255)的列，如果在前10 個或20 個字符內，多數值是惟一的，那么就不要對整個列進行索引，短索引不僅可以提高查詢速度而且可以節省磁盤空間和I/O操作，

轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/72774.html

標籤：其他

上一篇：MYSQL-計算兩個時間的時間差和作業日差

下一篇：Oracle快速入門（游標，拋出自定義例外資訊，存盤程序）