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

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

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

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

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

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

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

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

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

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

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

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

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

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

　　比如有這樣一張表

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

　　如select * from staffs where name = 'July' and age = '23'，key字段顯示用到了索引，注意，key_len字段（表示本次語句使用的索引長度）數(shù)值比上一條小了，意思是它并未使用全部索引列（通常這個(gè)長度可估摸著用了哪些索引列，埋個(gè)坑），事實(shí)上只用到了name和age列

　　再試試select * from staffs where name = 'July'，它也用了索引，key_len值更小，實(shí)際只用到了索引中的name列

　　3. 匹配列前綴，即一個(gè)索引中列的前一部分，主要用在模糊匹配，如select * fromstaffs where name like 'J%'，explain信息的key字段表示使用了索引，但是mysql的B樹索引不能非列前綴的模糊匹配，如select * from staffs where name like '%y' 或者 like '%u%'，據(jù)說是由于底層存儲(chǔ)引擎的API限制

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

　　出于好奇測(cè)了下整型字段的索引（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兩個(gè)列的索引（key_len推測(cè)）

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

　　6. 只訪問索引的查詢，比如staffs表的情況，索引建立在(name,age,pos)上面，前面一直是讀取的全部列，如果我們用到了哪些列的索引，查詢時(shí)也只查這些列的數(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中需要看最后一個(gè)Extra列的信息，Using index表明使用了覆蓋索引，同時(shí)Using where表明也使用了where過濾

　　7. 前綴索引

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

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

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

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

alter table tab add index idx_pn(name(9)) --單獨(dú)前綴索引  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，一個(gè)也用不到，必須從最左列開始

　　2. 前面是范圍查詢，where a = 1 and b > 2 and c = 3，最多用到 a, b兩個(gè)索引列；

　　3. 順序顛倒，where c = 3 and b = 2 and a = 1，一個(gè)也用不到；

　　4. 索引列上使用了表達(dá)式，如where substr(a, 1, 3) = 'hhh'，where a = a + 1，表達(dá)式是一大忌諱，再簡單mysql也不認(rèn)。有時(shí)數(shù)據(jù)量不是大到嚴(yán)重影響速度時(shí)，一般可以先查出來，比如先查所有有訂單記錄的數(shù)據(jù)，再在程序中去篩選以'cp1001'開頭的訂單，而不是寫sql過濾它；

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

　　排序?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

--測(cè)試表  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個(gè)字符  alter table keyLenTest1 modify typeKey char(1);

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

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

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