作為免費又高效的數據庫，mysql基本是首選。良好的安全連接，自帶查詢解析、sql語句優化，使用讀寫鎖（細化到行）、事物隔離和多版本並發控制提高並發，完備的事務日志記錄，強大的存儲引擎提供高效查詢（表記錄可達百萬級），如果是InnoDB，還可在崩潰後進行完整的恢復，優點非常多。即使有這麼多優點，仍依賴人去做點優化，看書後寫個總結鞏固下，有錯請指正。
完整的mysql優化需要很深的功底，大公司甚至有專門寫mysql內核的，sql優化攻城獅，mysql服務器的優化，各種參數常量設定，查詢語句優化，主從復制，軟硬件升級，容災備份，sql編程，需要的不是一星半點的知識與時間來掌握，作為一名像俺這樣的菜鳥開發，強吃這麼多消化不了也沒意義：沒地兒用啊，況且還有運維和dba，還不如把手頭的業務寫好，也就是寫好點的sql，而且很多sql語句優化跟索引還是有很大關系的。
首先，mysql的查詢流程大致是：mysql客戶端通過協議與mysql服務器建立連接，發送查詢語句，先檢查查詢緩存，如果命中，直接返回結果，否則進行語句解析，有一系列預處理，比如檢查語句是否寫正確了，然後是查詢優化（比如是否使用索引掃描，如果是一個不可能的條件，則提前終止），生成查詢計劃，然後查詢引擎啟動，開始執行查詢，從底層存儲引擎調用API獲取數據，最後返回給客戶端。怎麼存數據、怎麼取數據，都與存儲引擎有關。然後，mysql默認使用的BTREE索引，並且一個大方向是，無論怎麼折騰sql，至少在目前來說，mysql最多只用到表中的一個索引。
mysql通過存儲引擎取數據，自然跟存儲引擎有很大關系，不同的存儲引擎索引也不一樣，如MyISAM的全文索引，即便索引叫一個名字內部組織方式也不盡相同，最常用的當然就是InnoDB了（還有完全兼容mysql的MariaDB，它的默引擎是XtraDB，跟InnoDB很像），這裡寫的是InnoDB引擎。而索引的實現也跟存儲引擎，按照實現方式分，InnoDB的索引目前只有兩種：BTREE索引和HASH索引。通常我們說的索引不出意外指的就是B樹索引，InnoDB的BTREE索引，實際是用B+樹實現的，因為在查看表索引時，mysql一律打印BTREE，所以簡稱為B樹索引。至於B樹與B+樹的區別，原諒的俺數據結構沒好好學，也是需要補的地方。
使用了BTREE索引，意味著所有的索引是按順序排列存儲的（升序），mysql就是這麼干的，mysl中的BTREE索引抽象結構如下圖（參考高性能mysql）。
結構中，每一層節點均從左往右從小到大排列，key1 < key2 < ... < keyN，對於小於key1或在[key1，key2)或其他的值的節點，在進入葉子節點查找，是一個范圍分布，同時，同一層節點之間可直接訪問，因為他們之間有指針指向聯系（MyISAM的BTREE索引沒有）。每次搜索是一個區間搜索，有的話就找到了，沒有的話就是空。索引能加快訪問速度，因為有了它無需全表掃描數據（不總是這樣），根據查找的值，跟節點中的值比較，通常使用二分查找，對於排好序的數值來說，平均速度幾乎是最快的。
val指向了哪裡，對於InnoDB，它指向的就是表數據，因為InnoDB的表數據本身就是索引文件，這是與MyISAM索引的顯著區別，MyISAM的索引指向的是表數據的地址（val指向的是類似於0x7DFF..之類）。比如對於InnoDB一個主鍵索引來說，可能是這樣
InnoDB的索引節點val值直接指向表數據，即它的葉子節點就是表數據，它們連在一起，表記錄行沒有再單獨放在其他地方，葉子節點（數據）之間可訪問。
前面在BTREE的抽象結構中，索引值的節點是放在頁中的，這裡有兩個需注意的問題：
1. 葉子頁、頁中的值（上上圖），即所謂的頁是啥，俺加了個節點注釋，即這裡的頁最小可近似當做是單個節點。我們知道計算機的存儲空間是一塊一塊的，通常一塊用完了再用另一塊，如果上一塊只剩余5kb，但這裡剛好要申請8kb的空間，就得在一個新的塊上申請這個空間，然後以後的申請又接在這個8kb後面，只要這個塊的空間足夠，那麼上一塊的5kb通常就成了所謂的“碎片”，電腦用多了會有很多這樣零散的碎片空間，因此有碎片整理。在mysql中，這裡的頁可理解為塊存儲空間，即索引的樹節點是存放在頁中的，每一頁（稱為邏輯頁）有固定大小，InnoDB目前是16kb，一頁用完了，當繼續插入表生成新的索引節點時，就去新的頁中存儲這個節點，再有新的節點就繼續放在這個新的頁的節點後面。
2. 頁分裂問題，一頁總要被存滿，然後新開一頁繼續，這種行為被稱作頁分裂。何時開辟新的頁，mysql規定了一個分裂因子，達到頁存儲空間的15/16則存到下一頁。頁分裂的存在可能極大影響性能維護索引的性能。通常提倡的是，設定一個無意義的整數自增索引，有利於索引存儲
如果非自增或不是整數索引，如非自增整數、類似MD5的字符串，以他們作為索引值時，因為待插入的下一條數據的值不一定比上一條大，甚至比當前頁所有值都小，需要跑到前幾頁去比較而找到合適位置，InnoDB無法簡單的把新行插入到上一行後面，而找到並插入索引後，可能導致該頁達到分裂因子閥值，需要頁分裂，進一步導致後面所有的索引頁的分裂和排序，數據量小也許沒什麼問題，數據量大的話可能會浪費大量時間，產生許多碎片。
主鍵總是唯一且非空，InnoDB自動對它建立了索引（primary key），對於非主鍵字段上建立的索引，又稱輔助索引，索引排列也是順序排列，只是它還附帶一個本條記錄的主鍵值的數據域，不是指向本數據行的指針，在使用輔助索引查找時，先找到對應這一列的索引值，再根據索引節點上的另一個數據域---主鍵值，來查找該行記錄，即每次查找實際經過查找了兩次。額外的數據域存儲主鍵值的好處是，當頁分裂發生時，無需修改數據域的值，因為即使頁分裂，該行的主鍵值是不變的，而地址就變了。比如name字段的索引簡示如下
包含一列的索引稱為單列索引，多列的稱為復合索引，因為BTREE索引是順序排列的，所以比較適合范圍查詢，但是在復合索引中，還應注意列數目、列的順序以及前面范圍查詢的列對後邊列的影響。
比如有這樣一張表
create table staffs(
id int primary key auto_increment,
name varchar(24) not null default '' comment '姓名',
age int not null default 0 comment '年齡',
pos varchar(20) not null default '' comment '職位',
add_time timestamp not null default current_timestamp comment '入職時間'
) charset utf8 comment '員工記錄表';
添加三列的復合索引
alter table staffs add index idx_nap(name, age, pos);
在BTREE索引的使用上，以下幾種情況可以用到該索引或索引的一部分（使用explain簡單查看使用情況）：
1. 全值匹配
如select * from staffs where name = 'July' and age = '23' and pos = 'dev' ，key字段顯示使用了idx_nap索引
2. 匹配最左列，對於復合索引來說，不總是匹配所有字段列，但是可以匹配索引中靠左的列
如select * from staffs where name = 'July' and age = '23'，key字段顯示用到了索引，注意，key_len字段（表示本次語句使用的索引長度）數值比上一條小了，意思是它並未使用全部索引列（通常這個長度可估摸著用了哪些索引列，埋個坑），事實上只用到了name和age列
再試試select * from staffs where name = 'July'，它也用了索引，key_len值更小，實際只用到了索引中的name列
3. 匹配列前綴，即一個索引中列的前一部分，主要用在模糊匹配，如select * fromstaffs where name like 'J%'，explain信息的key字段表示使用了索引，但是mysql的B樹索引不能非列前綴的模糊匹配，如select * from staffs where name like '%y' 或者 like '%u%'，據說是由於底層存儲引擎的API限制
4. 匹配范圍，如select * from staffs where name > 'Mary'，但俺在測試時發現>可以，>=卻不行，至少在字符串列上不行（測試mysql版本5.5.12），然而在時間類型（timestamp）上卻可以，不測試下還真不能確定說就用到了索引==
出於好奇測了下整型字段的索引（idx_cn(count, name)，count為整型），發現整型受限制少很多，下面的都能用到索引，連前模糊匹配的都行
select * from indexTest1 where count > '10' select * from indexTest1 where count >= '10' select * from indexTest1 where count > '10%' select * from indexTest1 where count >= '10%' select * from indexTest1 where count > '%10%' select * from indexTest1 where count >= '%10%'
5. 精確匹配一列並范圍匹配右側相鄰列，即前一列是固定值，後一列是范圍值，它用了name與age兩個列的索引（key_len推測）
如select * from staffs where name = 'July' and age > 25
6. 只訪問索引的查詢，比如staffs表的情況，索引建立在(name,age,pos)上面，前面一直是讀取的全部列，如果我們用到了哪些列的索引，查詢時也只查這些列的數據，就是只訪問索引的查詢，如
select name,age,pos from staffs where name = 'July' and age = 25 and pos = 'dev' select name,age from staffs where name = July and age > 25
第一句用到了全部索引列，第二句只用了索引前兩列，select的字段就最多只能是這兩列，這種查詢情況的索引，mysql稱為覆蓋索引，就是索引包含（覆蓋）了查詢的全部字段。是不是用到了索引查詢，在explain中需要看最後一個Extra列的信息，Using index表明使用了覆蓋索引，同時Using where表明也使用了where過濾
7. 前綴索引
區別於列前綴（類似like 'J%'形式的模糊匹配）和最左列索引（順序取索引中靠左的列的查詢），它只取某列的一部分作為索引。通常在說InnoDB跟MyISAM的區別時，一個明顯的區別是：MyISAM支持全文索引，而InnoDB不行，甚至對於text、blob這種超長的字符串或二進制數據時，MyISAM會取前多少個字符作為索引，InnoDb的前綴索引跟這個類似，某些列，一般是字符串類型，很長，全部作為索引大大增加存儲空間，索引也需要維護，對於長字符串，又想作為索引列，一個可取的辦法就是取前一部分（前綴），代表一整列作為索引串，問題是：如何確保這個前綴能代表或大致代表這一列？所以mysql中有個概念是索引的選擇性，是指索引中不重復的值的數目（也稱基數）與整個表該列記錄總數（#T）的比值，比如一個列表（1,2,2,3），總數是4，不重復值數目為3，選擇性為3/4，因此選擇性范圍是[1/#T, 1]，這個值越大，表示列中不重復值越多，越適合作為前綴索引，唯一索引（UNIQUE KEY）的選擇性是1。
比如有一列a varchar(255)，以它作前綴索引，比如以7個測試，逐個增加看看選擇性值增長到那個數基本不變，就表示可以代表整列了，再結合這個長度的索引列是否存儲數據太多，做個權衡，基本就行了。但如果這個選擇性本來就小的可憐還是算了
select count(distinct left(a, 7))/count(*) as non_repeat from tab;
定好一個前綴數目，如9，添加索引時可以這樣
alter table tab add index idx_pn(name(9)) --單獨前綴索引 alter table tab add index idx_cpn(count, name(9)) --復合前綴索引
以上為常見的使用索引的方式，有這麼些情況不能用或不能全用，有的就是上面情況的反例，以key(a, b, c)為例
1. 跳過列，where a = 1 and c = 3，最多用到索引列a；where b = 2 and c = 3，一個也用不到，必須從最左列開始
2. 前面是范圍查詢，where a = 1 and b > 2 and c = 3，最多用到 a, b兩個索引列；
3. 順序顛倒，where c = 3 and b = 2 and a = 1，一個也用不到；
4. 索引列上使用了表達式，如where substr(a, 1, 3) = 'hhh'，where a = a + 1，表達式是一大忌諱，再簡單mysql也不認。有時數據量不是大到嚴重影響速度時，一般可以先查出來，比如先查所有有訂單記錄的數據，再在程序中去篩選以'cp1001'開頭的訂單，而不是寫sql過濾它；
5. 模糊匹配時，盡量寫 where a like 'J%'，字符串放在左邊，這樣才可能用得到a列索引，甚至可能還用不到，當然這得看數據類型，最好測試一下。
排序對索引的影響
order by是經常用的語句，排序也遵循最左前綴列的原則，比如key(a, b)，下面語句可以用到（測試為妙）
select * from tab where a > 1 order by b select * from tab where a > 1 and b > '2015-12-01 00:00：00' order by b select * from tab order by a, b
以下情況用不到
1. 非最左列，select * from tab order by b;
2. 不按索引列順序來的，select * from tab where b > '2015-12-01 00:00:00' order by a;
3. 多列排序，但列的順序方向不一致，select * from tab a asc, b desc。
聚簇索引與覆蓋索引
前面說到，mysql索引從結構上只有兩類，BTREE與HASH，覆蓋索引只是在查詢時，要查詢的列剛好與使用的索引列完全一致，mysql直接掃描索引，然後就可返回數據，大大提高效率，因為不需再去原表查詢、過濾，這種形式下的索引稱作覆蓋索引，比如key(a,b)，查詢時select a,b from tab where a = 1 and b > 2，本質原因：BTREE索引存儲了原表數據。
聚簇索引也不是單獨的索引，前面簡要寫到，BTREE索引會把數據放在索引中，即索引的葉子頁中，包括主鍵，主鍵是跟表數據緊挨著放在一起的，因為表數據只有一份，一列鍵值要跟每一行數據都緊挨在一起，所以一張表只有一個聚簇索引，對於mysql來說，就是主鍵列，它是默認的。
聚簇索引將表數據組織到了一起（參考前面主鍵索引簡略圖），插入時嚴重依賴主鍵順序，最好是連續自增，否則面臨頻繁頁分裂問題，移動許多數據。
哈希索引
簡要說下，類似於數據結構中簡單實現的HASH表（散列表）一樣，當我們在mysql中用哈希索引時，也是對索引列計算一個散列值（類似md5、sha1、crc32），然後對這個散列值以順序（默認升序）排列，同時記錄該散列值對應數據表中某行的指針，當然這只是簡略模擬圖
比如對姓名列建立hash索引，生成hash值按順序排列，但是順序排列的hash值並不對應表中記錄，從地址指針可反應出來，而且，hash索引可能建立在兩列或者更多列上，取得是多列數據後的hash值，它不存儲表中數據。它先計算列數據的hash值，與索引中的hash值比較，找到了然後比對列數據是否相等，可能涉及其他列條件，然後返回數據。hash當然會有沖突，即碰撞，除非有很多沖突，一般hash索引效率很高，否則hash維護成本較高，因此哈希索引通常用在選擇性較高的列上面。哈希索引的結構決定了它的特點：
1. hash索引只是hash值順序排列，跟表數據沒有關系，無法應用於order by；
2. hash索引是對它的所有列計算哈希值，因此在查詢時，必須帶上所有列，比如有(a, b)哈希索引，查詢時必須 where a = 1 and b = 2，少任何一個不行；
3. hash索引只能用於比較查詢 = 或 IN，其他范圍查詢無效，本質還是因不存儲表數據；
4. 一旦出現碰撞，hash索引必須遍歷所有的hash值，將地址所指向數據一一比較，直到找到所有符合條件的行。
填坑
前面提到通過explain的key_len字段，可大致估計出用了哪些列，索引列的長度跟索引列的數據類型直接相關，一般，我們說int是4字節，bigint8字節，char是1字節，考慮到建表時要指定字符集，比如utf8，還跟選的字符集有關（==!），在utf8下邊，一個char是3字節，但是知道這些仍不能說key_len就是將用到的索引列的數據類型代表字節數一加不就完啦？事實總有點區別，測試方法比較機械（以下基於mysql 5.5.2）
建表，加索引，int型
--測試表
create table keyLenTest1(
id int primary key auto_increment,
typeKey int default 0 ,
add_time timestamp not null default current_timestamp
) charset utf8
--添加索引
alter table keyLenTest1 add index idx_k(typeKey);
可知int型索引默認長度為5，在4字節基礎上+1
char型
--改為char型，1個字符 alter table keyLenTest1 modify typeKey char(1);
--改為char型，2個字符 alter table keyLenTest1 modify typeKey char(2);
可知，char型初始是4字節（3+1 bytes），後續按照3字節遞增
varchar型
--改為varchar型，1個字符 alter table keyLenTest1 modify typeKey varchar(1);
--改為varchar型，2個字符 alter table keyLenTest1 modify typeKey varchar(2);
可知，varchar型，1個字符時，key_len為6，以後以3字節遞增
所以，如果一個語句用到了int、char、varchar，key_len如何計算以及用了哪些索引列應該很清楚了。
如果想了解的更詳細點，explain各字段意義，索引的更多細節，除了explain，還有show profiles、慢查詢日志等（沒細看），推薦看高性能mysql，畢竟俺寫的太膚淺。
