作為免費又高效的數(shù)據(jù)庫，mysql基本是首選。良好的安全連接，自帶查詢解析、sql語句優(yōu)化，使用讀寫鎖（細化到行）、事物隔離和多版本并發(fā)控制提高并發(fā)，完備的事務(wù)日志記錄，強大的存儲引擎提供高效查詢（表記錄可達百萬級），如果是InnoDB，還可在崩潰后進行完整的恢復(fù)，優(yōu)點非常多。即使有這么多優(yōu)點，仍依賴人去做點優(yōu)化，看書后寫個總結(jié)鞏固下，有錯請指正。

　　完整的mysql優(yōu)化需要很深的功底，大公司甚至有專門寫mysql內(nèi)核的，sql優(yōu)化攻城獅，mysql服務(wù)器的優(yōu)化，各種參數(shù)常量設(shè)定，查詢語句優(yōu)化，主從復(fù)制，軟硬件升級，容災(zāi)備份，sql編程，需要的不是一星半點的知識與時間來掌握，作為一名像俺這樣的菜鳥開發(fā)，強吃這么多消化不了也沒意義：沒地兒用啊，況且還有運維和dba，還不如把手頭的業(yè)務(wù)寫好，也就是寫好點的sql，而且很多sql語句優(yōu)化跟索引還是有很大關(guān)系的。

　　首先，mysql的查詢流程大致是：mysql客戶端通過協(xié)議與mysql服務(wù)器建立連接，發(fā)送查詢語句，先檢查查詢緩存，如果命中，直接返回結(jié)果，否則進行語句解析，有一系列預(yù)處理，比如檢查語句是否寫正確了，然后是查詢優(yōu)化（比如是否使用索引掃描，如果是一個不可能的條件，則提前終止），生成查詢計劃，然后查詢引擎啟動，開始執(zhí)行查詢，從底層存儲引擎調(diào)用API獲取數(shù)據(jù)，最后返回給客戶端。怎么存數(shù)據(jù)、怎么取數(shù)據(jù)，都與存儲引擎有關(guān)。然后，mysql默認(rèn)使用的BTREE索引，并且一個大方向是，無論怎么折騰sql，至少在目前來說，mysql最多只用到表中的一個索引。

　　mysql通過存儲引擎取數(shù)據(jù)，自然跟存儲引擎有很大關(guān)系，不同的存儲引擎索引也不一樣，如MyISAM的全文索引，即便索引叫一個名字內(nèi)部組織方式也不盡相同，最常用的當(dāng)然就是InnoDB了（還有完全兼容mysql的MariaDB，它的默引擎是XtraDB，跟InnoDB很像），這里寫的是InnoDB引擎。而索引的實現(xiàn)也跟存儲引擎，按照實現(xiàn)方式分，InnoDB的索引目前只有兩種：BTREE索引和HASH索引。通常我們說的索引不出意外指的就是B樹索引，InnoDB的BTREE索引，實際是用B+樹實現(xiàn)的，因為在查看表索引時，mysql一律打印BTREE，所以簡稱為B樹索引。至于B樹與B+樹的區(qū)別，原諒的俺數(shù)據(jù)結(jié)構(gòu)沒好好學(xué)，也是需要補的地方。

　　使用了BTREE索引，意味著所有的索引是按順序排列存儲的（升序），mysql就是這么干的，mysl中的BTREE索引抽象結(jié)構(gòu)如下圖（參考高性能mysql）。

　　結(jié)構(gòu)中，每一層節(jié)點均從左往右從小到大排列，key1 < key2 < ... < keyN，對于小于key1或在[key1，key2)或其他的值的節(jié)點，在進入葉子節(jié)點查找，是一個范圍分布，同時，同一層節(jié)點之間可直接訪問，因為他們之間有指針指向聯(lián)系（MyISAM的BTREE索引沒有）。每次搜索是一個區(qū)間搜索，有的話就找到了，沒有的話就是空。索引能加快訪問速度，因為有了它無需全表掃描數(shù)據(jù)（不總是這樣），根據(jù)查找的值，跟節(jié)點中的值比較，通常使用二分查找，對于排好序的數(shù)值來說，平均速度幾乎是最快的。

　　val指向了哪里，對于InnoDB，它指向的就是表數(shù)據(jù)，因為InnoDB的表數(shù)據(jù)本身就是索引文件，這是與MyISAM索引的顯著區(qū)別，MyISAM的索引指向的是表數(shù)據(jù)的地址（val指向的是類似于0x7DFF..之類）。比如對于InnoDB一個主鍵索引來說，可能是這樣

　　InnoDB的索引節(jié)點val值直接指向表數(shù)據(jù)，即它的葉子節(jié)點就是表數(shù)據(jù)，它們連在一起，表記錄行沒有再單獨放在其他地方，葉子節(jié)點（數(shù)據(jù)）之間可訪問。

　　前面在BTREE的抽象結(jié)構(gòu)中，索引值的節(jié)點是放在頁中的，這里有兩個需注意的問題：

　　1. 葉子頁、頁中的值（上上圖），即所謂的頁是啥，俺加了個節(jié)點注釋，即這里的頁最小可近似當(dāng)做是單個節(jié)點。我們知道計算機的存儲空間是一塊一塊的，通常一塊用完了再用另一塊，如果上一塊只剩余5kb，但這里剛好要申請8kb的空間，就得在一個新的塊上申請這個空間，然后以后的申請又接在這個8kb后面，只要這個塊的空間足夠，那么上一塊的5kb通常就成了所謂的“碎片”，電腦用多了會有很多這樣零散的碎片空間，因此有碎片整理。在mysql中，這里的頁可理解為塊存儲空間，即索引的樹節(jié)點是存放在頁中的，每一頁（稱為邏輯頁）有固定大小，InnoDB目前是16kb，一頁用完了，當(dāng)繼續(xù)插入表生成新的索引節(jié)點時，就去新的頁中存儲這個節(jié)點，再有新的節(jié)點就繼續(xù)放在這個新的頁的節(jié)點后面。

　　2. 頁分裂問題，一頁總要被存滿，然后新開一頁繼續(xù)，這種行為被稱作頁分裂。何時開辟新的頁，mysql規(guī)定了一個分裂因子，達到頁存儲空間的15/16則存到下一頁。頁分裂的存在可能極大影響性能維護索引的性能。通常提倡的是，設(shè)定一個無意義的整數(shù)自增索引，有利于索引存儲

　　如果非自增或不是整數(shù)索引，如非自增整數(shù)、類似MD5的字符串，以他們作為索引值時，因為待插入的下一條數(shù)據(jù)的值不一定比上一條大，甚至比當(dāng)前頁所有值都小，需要跑到前幾頁去比較而找到合適位置，InnoDB無法簡單的把新行插入到上一行后面，而找到并插入索引后，可能導(dǎo)致該頁達到分裂因子閥值，需要頁分裂，進一步導(dǎo)致后面所有的索引頁的分裂和排序，數(shù)據(jù)量小也許沒什么問題，數(shù)據(jù)量大的話可能會浪費大量時間，產(chǎn)生許多碎片。

　　主鍵總是唯一且非空，InnoDB自動對它建立了索引（primary key），對于非主鍵字段上建立的索引，又稱輔助索引，索引排列也是順序排列，只是它還附帶一個本條記錄的主鍵值的數(shù)據(jù)域，不是指向本數(shù)據(jù)行的指針，在使用輔助索引查找時，先找到對應(yīng)這一列的索引值，再根據(jù)索引節(jié)點上的另一個數(shù)據(jù)域---主鍵值，來查找該行記錄，即每次查找實際經(jīng)過查找了兩次。額外的數(shù)據(jù)域存儲主鍵值的好處是，當(dāng)頁分裂發(fā)生時，無需修改數(shù)據(jù)域的值，因為即使頁分裂，該行的主鍵值是不變的，而地址就變了。比如name字段的索引簡示如下　

　　 包含一列的索引稱為單列索引，多列的稱為復(fù)合索引，因為BTREE索引是順序排列的，所以比較適合范圍查詢，但是在復(fù)合索引中，還應(yīng)注意列數(shù)目、列的順序以及前面范圍查詢的列對后邊列的影響。

　　比如有這樣一張表

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 '員工記錄表';

　　添加三列的復(fù)合索引

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. 匹配最左列，對于復(fù)合索引來說，不總是匹配所有字段列，但是可以匹配索引中靠左的列

　　如select * from staffs where name = 'July' and age = '23'，key字段顯示用到了索引，注意，key_len字段（表示本次語句使用的索引長度）數(shù)值比上一條小了，意思是它并未使用全部索引列（通常這個長度可估摸著用了哪些索引列，埋個坑），事實上只用到了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%'，據(jù)說是由于底層存儲引擎的API限制

　　4. 匹配范圍，如select * from staffs where name > 'Mary'，但俺在測試時發(fā)現(xiàn)>可以，>=卻不行，至少在字符串列上不行（測試mysql版本5.5.12），然而在時間類型（timestamp）上卻可以，不測試下還真不能確定說就用到了索引==

　　出于好奇測了下整型字段的索引（idx_cn(count, name)，count為整型），發(fā)現(xiàn)整型受限制少很多，下面的都能用到索引，連前模糊匹配的都行

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. 精確匹配一列并范圍匹配右側(cè)相鄰列，即前一列是固定值，后一列是范圍值，它用了name與age兩個列的索引（key_len推測）

　　如select * from staffs where name = 'July' and age > 25

　　6. 只訪問索引的查詢，比如staffs表的情況，索引建立在(name,age,pos)上面，前面一直是讀取的全部列，如果我們用到了哪些列的索引，查詢時也只查這些列的數(shù)據(jù)，就是只訪問索引的查詢，如

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. 前綴索引

　　區(qū)別于列前綴（類似like 'J%'形式的模糊匹配）和最左列索引（順序取索引中靠左的列的查詢），它只取某列的一部分作為索引。通常在說InnoDB跟MyISAM的區(qū)別時，一個明顯的區(qū)別是：MyISAM支持全文索引，而InnoDB不行，甚至對于text、blob這種超長的字符串或二進制數(shù)據(jù)時，MyISAM會取前多少個字符作為索引，InnoDb的前綴索引跟這個類似，某些列，一般是字符串類型，很長，全部作為索引大大增加存儲空間，索引也需要維護，對于長字符串，又想作為索引列，一個可取的辦法就是取前一部分（前綴），代表一整列作為索引串，問題是：如何確保這個前綴能代表或大致代表這一列？所以mysql中有個概念是索引的選擇性，是指索引中不重復(fù)的值的數(shù)目（也稱基數(shù)）與整個表該列記錄總數(shù)（#T）的比值，比如一個列表（1,2,2,3），總數(shù)是4，不重復(fù)值數(shù)目為3，選擇性為3/4，因此選擇性范圍是[1/#T, 1]，這個值越大，表示列中不重復(fù)值越多，越適合作為前綴索引，唯一索引（UNIQUE KEY）的選擇性是1。

　　比如有一列a varchar(255)，以它作前綴索引，比如以7個測試，逐個增加看看選擇性值增長到那個數(shù)基本不變，就表示可以代表整列了，再結(jié)合這個長度的索引列是否存儲數(shù)據(jù)太多，做個權(quán)衡，基本就行了。但如果這個選擇性本來就小的可憐還是算了

select count(distinct left(a, 7))/count(*) as non_repeat from tab;

　　定好一個前綴數(shù)目，如9，添加索引時可以這樣

alter table tab add index idx_pn(name(9)) --單獨前綴索引
  alter table tab add index idx_cpn(count, name(9)) --復(fù)合前綴索引

　　以上為常見的使用索引的方式，有這么些情況不能用或不能全用，有的就是上面情況的反例，以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也不認(rèn)。有時數(shù)據(jù)量不是大到嚴(yán)重影響速度時，一般可以先查出來，比如先查所有有訂單記錄的數(shù)據(jù)，再在程序中去篩選以'cp1001'開頭的訂單，而不是寫sql過濾它；

　　5. 模糊匹配時，盡量寫 where a like 'J%'，字符串放在左邊，這樣才可能用得到a列索引，甚至可能還用不到，當(dāng)然這得看數(shù)據(jù)類型，最好測試一下。

　　排序?qū)λ饕挠绊?/strong>

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

--測試表
  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);

 --改為char型，1個字符
  alter table keyLenTest1 modify typeKey char(1);

--改為char型，2個字符
  alter table keyLenTest1 modify typeKey char(2);

--改為varchar型，1個字符
  alter table keyLenTest1 modify typeKey varchar(1);

--改為varchar型，2個字符
  alter table keyLenTest1 modify typeKey varchar(2);