索引
索引是應(yīng)用程序設(shè)計(jì)和開發(fā)的一個(gè)重要方面滞诺。如果索引太多形导，應(yīng)用的性能可能會(huì)受到影響；如果索引太少习霹，對(duì)查詢性能又會(huì)產(chǎn)生影響朵耕。
1 innodb存儲(chǔ)引擎介紹
innodb存儲(chǔ)引擎支持兩種常見(jiàn)的索引：B+樹索引和哈希索引。
innodb支持哈希索引是自適應(yīng)的淋叶，innodb會(huì)根據(jù)表的使用情況自動(dòng)生成哈希索引阎曹。
B+樹索引就是傳統(tǒng)意義上的索引，是關(guān)系型數(shù)據(jù)庫(kù)中最常用最有效的索引煞檩。B+樹是從最早的平衡二叉樹演變而來(lái)处嫌，但是B+樹不是一個(gè)二叉樹。B+中的B不代表二叉(Binary),而是代表平衡(Balance)斟湃。
注意：
B+樹索引并不能找到一個(gè)鍵值對(duì)應(yīng)的具體行锰霜。b+樹索引只能查到被查找數(shù)據(jù)行所在的頁(yè)，然后數(shù)據(jù)庫(kù)通過(guò)把頁(yè)讀入內(nèi)存桐早，再在內(nèi)存中查找癣缅，最后得到結(jié)果厨剪。
2 B+樹索引介紹
B+樹索引的本質(zhì)是B+樹在數(shù)據(jù)庫(kù)中的實(shí)現(xiàn)。但是B+樹索引有一個(gè)特點(diǎn)是高扇出性友存，因此在數(shù)據(jù)庫(kù)中祷膳，B+樹的高度一般在2到3層。也就是說(shuō)查找某一鍵值的記錄屡立，最多只需要2到3次IO開銷直晨。按磁盤每秒100次IO來(lái)計(jì)算，查詢時(shí)間只需0.0.2到0.03秒膨俐。
數(shù)據(jù)庫(kù)中B+樹索引分為聚集索引（clustered index)和非聚集索引（secondary index)勇皇，這兩種索引的共同點(diǎn)是內(nèi)部都是B+樹，高度都是平衡的焚刺，葉節(jié)點(diǎn)存放著所有數(shù)據(jù)敛摘。不同點(diǎn)是葉節(jié)點(diǎn)是否存放著一整行數(shù)據(jù)。
1. 聚集索引
Innodb存儲(chǔ)引擎表是索引組織表乳愉，即表中數(shù)據(jù)按主鍵順序存放兄淫。而聚集索引就是按每張表的主鍵構(gòu)造一顆B+樹。并且葉節(jié)點(diǎn)存放整張表的行記錄數(shù)據(jù)蔓姚。每張表只能有一個(gè)聚集索引（一個(gè)主鍵）捕虽。
聚集索引的另一個(gè)好處是它對(duì)于主鍵的排序查找和范圍的速度非常快坡脐。葉節(jié)點(diǎn)的數(shù)據(jù)就是我們要找的數(shù)據(jù)泄私。
2. 輔助索引
輔助索引（也稱非聚集索引）。葉級(jí)別不包含行的全部數(shù)據(jù)备闲，葉級(jí)別除了包含行的鍵值以外挖滤，每個(gè)索引行還包含了一個(gè)書簽（bookmark），該書簽告訴innodb存儲(chǔ)引擎浅役，哪里可以找到與索引對(duì)應(yīng)的數(shù)據(jù)斩松。
輔助索引的存在并不影響數(shù)據(jù)再聚集索引中的組織，因此一個(gè)表可以有多個(gè)輔助索引觉既。當(dāng)通過(guò)輔助索引查找數(shù)據(jù)時(shí)惧盹，innodb會(huì)遍歷輔助索引并通過(guò)葉級(jí)別的指針獲得指向主鍵索引的主鍵。然后再通過(guò)主鍵索引找到一行完整的數(shù)據(jù)
3 使用場(chǎng)景
- 快速查找符合where條件的記錄
- 快速確定候選集瞪讼。若where條件使用了多個(gè)索引字段钧椰，則MySQL會(huì)優(yōu)先使用能使候選記錄集規(guī)模最小的那個(gè)索引，以便盡快淘汰不符合條件的記錄符欠。
- 如果表中存在幾個(gè)字段構(gòu)成的聯(lián)合索引嫡霞，則查找記錄時(shí)，這個(gè)聯(lián)合索引的最左前綴匹配字段也會(huì)被自動(dòng)作為索引來(lái)加速查找希柿。例如诊沪，若為某表創(chuàng)建了3個(gè)字段(c1, c2, c3)構(gòu)成的聯(lián)合索引养筒，則(c1), (c1, c2), (c1, c2, c3)均會(huì)作為索引，(c2, c3)就不會(huì)被作為索引端姚，而(c1, c3)其實(shí)只利用到c1索引晕粪。
- 多表做join操作時(shí)會(huì)使用索引（如果參與join的字段在這些表中均建立了索引的話）
- 若某字段已建立索引，求該字段的min()或max()時(shí)渐裸，MySQL會(huì)使用索引
- 對(duì)建立了索引的字段做sort或group操作時(shí)巫湘，MySQL會(huì)使用索引
4 建立、修改索引
1 索引創(chuàng)建：兩種方式
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name| ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option]
CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON tbl_name (index_col_name,...)
2 索引刪除：兩種方式
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name | DROP PRIMARY KEY | DROP {INDEX|KEY} index_names
DROP [ONLINE|OFFLINE] INDEX index_name ON tbl_name
算法（B+樹）
B+樹是為磁盤及其他存儲(chǔ)輔助設(shè)備而設(shè)計(jì)一種平衡查找樹（不是二叉樹）昏鹃。B+樹中尚氛，所有記錄的節(jié)點(diǎn)按大小順序存放在同一層的葉節(jié)點(diǎn)中，各葉節(jié)點(diǎn)用指針進(jìn)行連接洞渤。
下面演示一個(gè)B+數(shù)結(jié)構(gòu)阅嘶，高度為2，每頁(yè)可放4條記錄您宪，扇出(fan out)為5奈懒。從下圖1可以看出奠涌，所有記錄都在頁(yè)節(jié)點(diǎn)中宪巨，并且為順序存放，我們從最左邊的葉節(jié)點(diǎn)開始遍歷溜畅，可以得到所有鍵值的順序排序：5捏卓、10、15慈格、20怠晴、25、30浴捆、50蒜田、55、60选泻、65冲粤、75、80页眯、85梯捕、90.
-
B+樹的插入操作
B+樹的插入必須保證插入后葉節(jié)點(diǎn)的記錄依然排序。同時(shí)要考慮插入B+樹的三種情況窝撵，每種情況都可能導(dǎo)致不同的插入算法傀顾。如下表所示：
我們實(shí)例分析B+樹的插入，在圖1的B+樹中碌奉，我們需要插入28這個(gè)值短曾。因?yàn)長(zhǎng)eaf Page和Index page都沒(méi)有滿寒砖，我們直接將記錄插入葉節(jié)點(diǎn)就可以了。如下圖2所示：
圖2 插入鍵值28
下面我們?cè)俨迦?0這個(gè)值错英，這時(shí)Leaf Page已經(jīng)滿了入撒，但是Index Page還沒(méi)有滿，符合上面的第二種情況椭岩。這時(shí)插入Leaf Page的情況為50茅逮、55、60判哥、65献雅、70.我們根據(jù)中間的值60拆分葉節(jié)點(diǎn)，可得到下圖3所示（雙項(xiàng)鏈表指針依然存在塌计，沒(méi)有畫出)：
最后我們?cè)俨迦?5挺身，這個(gè)Leaf Page和Index Page都滿了，符合上面第三種情況锌仅。需要做2次拆分章钾，如下圖3 插入鍵值70圖4所示：
圖4 插入鍵值95
可以看到，不管怎么變化热芹，B+樹總會(huì)保持平衡贱傀。但是為了保持平衡，對(duì)于新插入的鍵值可能需要做大量的拆分頁(yè)操作伊脓。B+樹主要用于磁盤府寒，拆分意味著磁盤的操作，應(yīng)該在可能的情況下盡量減少頁(yè)的拆分报腔。因此株搔，B+樹提供了旋轉(zhuǎn)功能。旋轉(zhuǎn)發(fā)生在Leaf Page已經(jīng)滿了纯蛾，但是左右兄弟節(jié)點(diǎn)沒(méi)有滿的情況下纤房。這時(shí)B+樹并不是急著做頁(yè)的拆分，而是旋轉(zhuǎn)翻诉。旋轉(zhuǎn)結(jié)果如圖5所示炮姨，可以看到旋轉(zhuǎn)操作使B+樹減少了一次頁(yè)的拆分操作，高度仍然為2.
圖5 B+樹的旋轉(zhuǎn)操作 -
B+樹的刪除操作
B+樹使用填充因子來(lái)控制數(shù)的刪除變化米丘。填充因子可以設(shè)置的最小值為50%剑令。B+樹的刪除操作同樣保證刪除后葉節(jié)點(diǎn)的記錄依然排序。
根據(jù)填充因子的變化拄查，B+樹刪除依然需要考慮三種情況吁津，如下表所示：
根據(jù)圖4的B+樹，我們進(jìn)行刪除操作，首先刪除鍵值為70的這條記錄，該記錄符合上表第一種情況，刪除后如下圖6所示：
接著我們刪除鍵值為25的記錄蒜危，這也是屬于上表第一種情況，不同的是該值還是index page中的值役拴。因此在刪除Leaf Page中的25后，還需要將25的右兄弟節(jié)點(diǎn)28更新到Index Page中钾埂，如下
圖7所示（圖中有兩個(gè)筆誤河闰，紅色為修正值）：
優(yōu)化
MySQL數(shù)據(jù)庫(kù)是常見(jiàn)的兩個(gè)瓶頸是CPU和I/O的瓶頸部念，CPU在飽和的時(shí)候一般發(fā)生在數(shù)據(jù)裝入內(nèi)存或從磁盤上讀取數(shù)據(jù)時(shí)候。磁盤I/O瓶頸發(fā)生在裝入數(shù)據(jù)遠(yuǎn)大于內(nèi)存容量的時(shí)候氨菇，如果應(yīng)用分布在網(wǎng)絡(luò)上儡炼，那么查詢量相當(dāng)大的時(shí)候那么平瓶頸就會(huì)出現(xiàn)在網(wǎng)絡(luò)上，我們可以用mpstat, iostat, sar和vmstat來(lái)查看系統(tǒng)的性能狀態(tài)查蓉。
除了服務(wù)器硬件的性能瓶頸乌询，對(duì)于MySQL系統(tǒng)本身，我們可以使用工具來(lái)優(yōu)化數(shù)據(jù)庫(kù)的性能奶是，通常有三種：使用索引楣责，使用EXPLAIN分析查詢以及調(diào)整MySQL的內(nèi)部配置
1 性能分析工具
show profile;
mysqlsla; // 壓測(cè)
mysqldumpslow; // 慢查詢slow sql
explain;
show slow log;
show processlist;
show query_response_time(percona);
2 Explain
explain select count(*) from XXX;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | **rows** | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | XXX | ALL | NULL | NULL | NULL | NULL | 6897 | NULL |+----+-------------+---------+------+---------------+------+---------+------+------+-------+
EXPLAIN字段:
- id:本次 select 的標(biāo)識(shí)符竣灌。在查詢中每個(gè) select都有一個(gè)順序的數(shù)值聂沙。
- select_type:
- SIMPLE：查詢中不包含子查詢或者UNION
- PRIMARY：查詢中若包含任何復(fù)雜的子部分，最外層查詢則被標(biāo)記為
- SUBQUERY：在SELECT或WHERE列表中包含了子查詢初嘹，該子查詢被標(biāo)記為
- DERIVED（衍生)：在FROM列表中包含的子查詢被標(biāo)記為
- UNION: 若第二個(gè)SELECT出現(xiàn)在UNION之后及汉，則被標(biāo)記為UNION；若UNION包含在 FROM子句的子查詢中屯烦，外層SELECT將被標(biāo)記為：DERIVED
- 從UNION表獲取結(jié)果的SELECT被標(biāo)記為：UNION RESULT
Table：顯示這一行的數(shù)據(jù)是關(guān)于哪張表的
possible_keys：顯示可能應(yīng)用在這張表中的索引坷随。如果為空，沒(méi)有可能的索引驻龟∥旅迹可以為相關(guān)的域從WHERE語(yǔ)句中選擇一個(gè)合適的語(yǔ)句
key：實(shí)際使用的索引。如果為NULL翁狐，則沒(méi)有使用索引类溢。MYSQL很少會(huì)選擇優(yōu)化不足的索引，此時(shí)可以在SELECT語(yǔ)句中使用USE INDEX（index）來(lái)強(qiáng)制使用一個(gè)索引或者用IGNORE INDEX（index）來(lái)強(qiáng)制忽略索引
key_len：使用的索引的長(zhǎng)度露懒。在不損失精確性的情況下闯冷，長(zhǎng)度越短越好
ref：顯示索引的哪一列被使用了砂心，如果可能的話，是一個(gè)常數(shù)
rows：MySQL認(rèn)為必須檢索的用來(lái)返回請(qǐng)求數(shù)據(jù)的行數(shù)
type：這是最重要的字段之一蛇耀，顯示查詢使用了何種類型辩诞。從最好到最差的連接類型為
system、const纺涤、eq_reg译暂、ref、range撩炊、index和ALL
system秧秉、const：可以將查詢的變量轉(zhuǎn)為常量. 如id=1; id為 主鍵或唯一鍵.
eq_ref：訪問(wèn)索引,返回某單一行的數(shù)據(jù).(通常在聯(lián)接時(shí)出現(xiàn)，查詢使用的索引為主鍵或惟一鍵)
ref：訪問(wèn)索引,返回某個(gè)值的數(shù)據(jù).(可以返回多行) 通常使用=時(shí)發(fā)生
range：這個(gè)連接類型使用索引返回一個(gè)范圍中的行衰抑，比如使用>或<查找東西象迎，并且該字段上建有索引時(shí)發(fā)生的情況(注:不一定好于index)
index：以索引的順序進(jìn)行全表掃描，優(yōu)點(diǎn)是不用排序,缺點(diǎn)是還要全表掃描
ALL：全表掃描呛踊，應(yīng)該盡量避免
- Extra：關(guān)于MYSQL如何解析查詢的額外信息砾淌。
主要有以下幾種：
using index：只用到索引,可以避免訪問(wèn)表.
using where：使用到where來(lái)過(guò)慮數(shù)據(jù). 不是所有的where clause都要顯示using where. 如以=方式訪問(wèn)索引.
using tmporary：用到臨時(shí)表
using filesort：用到額外的排序. (當(dāng)使用order by v1,而沒(méi)用到索引時(shí),就會(huì)使用額外的排序)
range checked for eache record(index map:N)：沒(méi)有好的索引.
----
參考
- MySQL技術(shù)內(nèi)幕I弄DB存儲(chǔ)引擎
- http://www.oicto.com/mysql-explain-show/