索引：

索引是一種提高查詢效率的數(shù)據(jù)結(jié)構(gòu)（B樹或者是哈希結(jié)構(gòu)）；索引是創(chuàng)建在數(shù)據(jù)庫(kù)表中，是對(duì)數(shù)據(jù)庫(kù)表中的一列或者多列的值進(jìn)行排序的一個(gè)結(jié)果，好處就是提高查詢效率；

一般情況下，一次查詢只用一個(gè)索引；

索引的分類：

• 普通索引：沒有任何限制，可以給任意字段創(chuàng)建普通索引；

• 唯一性索引：使用unique修飾的字段，值不能重復(fù)的，主鍵索引就屬于唯一性索引；

• 主鍵索引：使用primary key修飾的字段自動(dòng)創(chuàng)建主鍵索引；

• 單列索引：在一個(gè)字段上創(chuàng)建索引 ；

• 多列索引：在多個(gè)字段上創(chuàng)建索引；

• 全文索引：使用fulltext參數(shù)可以設(shè)置全文索引，只支持char\varchar\text類型的字段上，常用于數(shù)據(jù)量較大的字符串類型上；

索引的創(chuàng)建和刪除的SQL語句：

（一）索引的創(chuàng)建

在創(chuàng)建表的時(shí)候指定索引

在已經(jīng)創(chuàng)建好的表上添加索引

create [unique|fulltext|spatial] index idx_id(索引名) on 表名(id屬性名);alter table 表名 add [unique|fulltext|spatial] index index_name; //第二種方法

（二）索引的刪除

索引執(zhí)行過程分析：

使用explain關(guān)鍵分析查詢SQL

 explain select * from Student where Sname like 'zhaolei'\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: Student type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4 Extra: Using where possible_keys:表示SQL執(zhí)行可能會(huì)命中的索引有哪些; key:表示執(zhí)行過程真正使用的索引名稱 rows:表示查詢影響的數(shù)據(jù)行數(shù) 當(dāng)前查詢possible_keys\key都為null，則未命中索引 rows=4表示當(dāng)前查詢操作對(duì)每一行數(shù)據(jù)都進(jìn)行比較  添加索引后，分析執(zhí)行過程 explain select * from Student where Sname like 'zhaolei'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: Student type: range possible_keys: idx_name key: idx_name key_len: 27 ref: NULL rows: 1 Extra: Using index condition 1 row in set (0.01 sec)通過explain關(guān)鍵分析查詢SQL，可以看出當(dāng)前查詢命中索引idx_name， rows: 1表示通過一條記錄就查詢到了結(jié)果

索引的底層原理

mysql支持兩種索引，一種是B樹索引，一種是哈希表索引；

問：數(shù)據(jù)庫(kù)中常見的慢查詢的優(yōu)化方式是什么？

答：加索引；

問：?jiǎn)柺裁醇铀饕梢詢?yōu)化查詢？

答：因?yàn)槟軠p少磁盤IO;

問：怎么減少磁盤IO的？

答：索引是一種優(yōu)化查詢的數(shù)據(jù)結(jié)構(gòu)，比如在MySQL中用到的B+樹，這種數(shù)據(jù)結(jié)構(gòu)是可以優(yōu)化查詢的，所以我們可以利用索引來快速的查找數(shù)據(jù)；

問：那你知道哪些數(shù)據(jù)結(jié)構(gòu)可以提高查詢速度嗎？

答：紅黑樹，二叉樹，哈希表，B樹（B-樹），B+樹等；

問：那為什么MySQL使用B+樹呢？

答：如下：

MYSQL InnoDB存儲(chǔ)引擎，基于B-樹(實(shí)際MYSQL采用的是B+樹)的索引結(jié)構(gòu)。B-樹是一種m階平衡樹，葉子節(jié)點(diǎn)都在同一層，由于每一個(gè)節(jié)點(diǎn)存儲(chǔ)的數(shù)據(jù)量比較大，所以整個(gè)B-樹的層數(shù)是非常低的，基本上不超過三層；

由于磁盤的讀取也是按block塊操作的（內(nèi)存是按page頁(yè)面操作的），因此B-樹的節(jié)點(diǎn)大小一般設(shè)置為和磁盤塊大小一致，這樣一個(gè)B-樹節(jié)點(diǎn)，就可以通過一次磁盤I/O把一個(gè)磁盤塊的數(shù)據(jù)全部存儲(chǔ)下來，所以當(dāng)使用B-樹存儲(chǔ)索引的時(shí)候，磁盤I/O的操作次數(shù)是最少的（MySQL的讀寫效率，主要集中在磁盤I/O上）。

那么MySQL最終為什么要采用B+樹存儲(chǔ)索引結(jié)構(gòu)呢，那么看看B-樹和B+樹在存儲(chǔ)結(jié)構(gòu)上有什么不同？

• B-樹的每一個(gè)節(jié)點(diǎn)，存了關(guān)鍵字和對(duì)應(yīng)的數(shù)據(jù)地址，而B+樹的非葉子節(jié)點(diǎn)只存關(guān)鍵字，不存數(shù)據(jù)地址。因此B+樹的每一個(gè)非葉子節(jié)點(diǎn)存儲(chǔ)的關(guān)鍵字是遠(yuǎn)遠(yuǎn)多于B-樹的，B+樹的葉子節(jié)點(diǎn)存放關(guān)鍵字和數(shù)據(jù)，因此，從樹的高度上來說，B+樹的高度要小于B-樹，使用的磁盤I/O次數(shù)少，因此查詢會(huì)更快一些。

• B-樹由于每個(gè)節(jié)點(diǎn)都存儲(chǔ)關(guān)鍵字和數(shù)據(jù)，因此離根節(jié)點(diǎn)近的數(shù)據(jù)，查詢的就快，離根節(jié)點(diǎn)遠(yuǎn)的數(shù)據(jù)，查詢的就慢；B+樹所有的數(shù)據(jù)都存在葉子節(jié)點(diǎn)上，因此在B+樹上搜索關(guān)鍵字，找到對(duì)應(yīng)數(shù)據(jù)的時(shí)間是比較平均的，沒有快慢之分。

• 在B-樹上如果做區(qū)間查找，遍歷的節(jié)點(diǎn)是非常多的；B+樹所有葉子節(jié)點(diǎn)被連接成了有序鏈表結(jié)構(gòu)，因此做整表遍歷和區(qū)間查找是非常容易的。

哈希索引當(dāng)然是由哈希表實(shí)現(xiàn)的，哈希表對(duì)數(shù)據(jù)并不排序，因此不適合做區(qū)間查找，效率非常低，需要搜索整個(gè)哈希表結(jié)構(gòu)。

MySQL數(shù)據(jù)庫(kù)的存儲(chǔ)引擎MyISAM和InNoDB的索引結(jié)構(gòu)

聚集索引和非聚集索引：聚集就是索引和數(shù)據(jù)存放在一個(gè)文件里面，非聚集索引就是索引和數(shù)據(jù)分別存放在兩個(gè)文件里面；

MyISAM存儲(chǔ)引擎（非聚集索引）

MyISAM引擎使用B+樹作為索引結(jié)構(gòu)、葉節(jié)點(diǎn)的數(shù)據(jù)域存放的是數(shù)據(jù)地址，在MyISAM引擎中，主索引和輔助索引在結(jié)構(gòu)上沒有任何區(qū)別，只是主索引要求key是唯一的，而輔助索引的key可以重復(fù)，如果給其他字段創(chuàng)建輔助索引，結(jié)構(gòu)圖如下：

根據(jù)上面兩張圖，首先按照B+樹搜索算法搜索索引，如果指定的key存在，則取出其數(shù)據(jù)域的值，然后以數(shù)據(jù)域的值為地址，讀取相應(yīng)的數(shù)據(jù)記錄；

InNoDB存儲(chǔ)引擎（聚集索引）

InNoDB存儲(chǔ)引擎的主鍵索引，葉子節(jié)點(diǎn)中，索引關(guān)鍵字和數(shù)據(jù)是在一起存放的，如圖：

可以看到，索引關(guān)鍵字和數(shù)據(jù)存儲(chǔ)在葉子節(jié)點(diǎn)中；

InNoDB輔助索引，葉子節(jié)點(diǎn)存放的是索引關(guān)鍵字和對(duì)應(yīng)的主鍵（為了一致性和節(jié)省存儲(chǔ)空間）：

輔助索引的B+樹，先根據(jù)關(guān)鍵字找到對(duì)應(yīng)的主鍵，再去主鍵索引樹上找到對(duì)應(yīng)的行記錄數(shù)據(jù)，從索引樹上可以看到，InNoDB的索引關(guān)鍵字和數(shù)據(jù)都是在一起存放的，體現(xiàn)在磁盤存儲(chǔ)上，例如創(chuàng)建一個(gè)user表，在磁盤上只存儲(chǔ)兩種結(jié)構(gòu)，user.frm（存儲(chǔ)表的結(jié)構(gòu)），user.idb（存儲(chǔ)索引和數(shù)據(jù)）；

聯(lián)合索引

就是先根據(jù)第一個(gè)鍵排序，第一個(gè)鍵相同的話，按第二個(gè)鍵排序……

索引的優(yōu)化

1. 索引的優(yōu)化，主要就是分析索引在哪些情況下會(huì)失效的問題：

2. 在where后使用or，導(dǎo)致索引失效（盡量少用or）；

3. 使用like ，like查詢是以%開頭；

4. 復(fù)合索引遵守“最左前綴”原則，即在查詢條件中使用了復(fù)合索引的第一個(gè)字段，索引才會(huì)被使用；

5. 如果列類型是字符串，那一定要在條件中將數(shù)據(jù)使用引號(hào)引用起來,否則不使用索引；

6. 使用in導(dǎo)致索引失效；

7. DATE_FORMAT()格式化時(shí)間，格式化后的時(shí)間再去比較，可能會(huì)導(dǎo)致索引失效；

8. 對(duì)于order by、group by 、 union、 distinc 中的字段出現(xiàn)在where條件中時(shí)，才會(huì)利用索引！

總結(jié)下索引的優(yōu)化：

• 最左前綴匹配原則；

• 主鍵外鍵一定要建索引；

• 對(duì) where,on,group by,order by 中出現(xiàn)的列使用索引；

• 盡量選擇區(qū)分度高的列作為索引,區(qū)分度的公式是count(distinct col)/count(*)，表示字段不重復(fù)的比例，比例越大我們掃描的記錄數(shù)越少，唯一鍵的區(qū)分度是1，而一些狀態(tài)、性別字段可能在大數(shù)據(jù)面前區(qū)分度就是0；

• 對(duì)較小的數(shù)據(jù)列使用索引,這樣會(huì)使索引文件更小,同時(shí)內(nèi)存中也可以裝載更多的索引鍵；

• 索引列不能參與計(jì)算，保持列“干凈”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡(jiǎn)單，b+樹中存的都是數(shù)據(jù)表中的字段值，但進(jìn)行檢索時(shí)，需要把所有元素都應(yīng)用函數(shù)才能比較，顯然成本太大。所以語句應(yīng)該寫成create_time = unix_timestamp(’2014-05-29’);

• 為較長(zhǎng)的字符串使用前綴索引；

• 盡量的擴(kuò)展索引，不要新建索引。比如表中已經(jīng)有a的索引，現(xiàn)在要加(a,b)的索引，那么只需要修改原來的索引即可；

• 不要過多創(chuàng)建索引, 權(quán)衡索引個(gè)數(shù)與DML之間關(guān)系，DML也就是插入、刪除數(shù)據(jù)操作。這里需要權(quán)衡一個(gè)問題，建立索引的目的是為了提高查詢效率的，但建立的索引過多，會(huì)影響插入、刪除數(shù)據(jù)的速度，因?yàn)槲覀冃薷牡谋頂?shù)據(jù)，索引也需要進(jìn)行調(diào)整重建；

• 對(duì)于like查詢，”%”不要放在前面。

• 查詢where條件數(shù)據(jù)類型不匹配也無法使用索引

• 字符串與數(shù)字比較不使用索引;

CREATE TABLEa(achar(10)); EXPLAIN SELECT * FROMaWHEREa='1' – 走索引 EXPLAIN SELECT * FROM a WHERE a=1 – 不走索引 

• 正則表達(dá)式不使用索引,這應(yīng)該很好理解,所以為什么在SQL中很難看到regexp關(guān)鍵字的原因；