本文轉(zhuǎn)自互聯(lián)網(wǎng)

本系列文章將整理到我在GitHub上的《Java面試指南》倉(cāng)庫(kù)沟堡，更多精彩內(nèi)容請(qǐng)到我的倉(cāng)庫(kù)里查看

https://github.com/h2pl/Java-Tutorial

喜歡的話麻煩點(diǎn)下Star哈

文章首發(fā)于我的個(gè)人博客：

www.how2playlife.com

本文是微信公眾號(hào)【Java技術(shù)江湖】的《重新學(xué)習(xí)MySQL數(shù)據(jù)庫(kù)》其中一篇誓篱，本文部分內(nèi)容來(lái)源于網(wǎng)絡(luò)撩荣，為了把本文主題講得清晰透徹，也整合了很多我認(rèn)為不錯(cuò)的技術(shù)博客內(nèi)容哪审，引用其中了一些比較好的博客文章蛾魄，如有侵權(quán)，請(qǐng)聯(lián)系作者。

該系列博文會(huì)告訴你如何從入門到進(jìn)階滴须，從sql基本的使用方法缴川，從MySQL執(zhí)行引擎再到索引、事務(wù)等知識(shí)描馅，一步步地學(xué)習(xí)MySQL相關(guān)技術(shù)的實(shí)現(xiàn)原理，更好地了解如何基于這些知識(shí)來(lái)優(yōu)化sql而线，減少SQL執(zhí)行時(shí)間铭污，通過(guò)執(zhí)行計(jì)劃對(duì)SQL性能進(jìn)行分析，再到MySQL的主從復(fù)制膀篮、主備部署等內(nèi)容嘹狞，以便讓你更完整地了解整個(gè)MySQL方面的技術(shù)體系，形成自己的知識(shí)框架誓竿。

如果對(duì)本系列文章有什么建議磅网，或者是有什么疑問(wèn)的話，也可以關(guān)注公眾號(hào)【Java技術(shù)江湖】聯(lián)系作者筷屡，歡迎你參與本系列博文的創(chuàng)作和修訂涧偷。

一：Mysql原理與慢查詢

MySQL憑借著出色的性能、低廉的成本毙死、豐富的資源燎潮，已經(jīng)成為絕大多數(shù)互聯(lián)網(wǎng)公司的首選關(guān)系型數(shù)據(jù)庫(kù)。雖然性能出色扼倘，但所謂“好馬配好鞍”确封，如何能夠更好的使用它，已經(jīng)成為開發(fā)工程師的必修課再菊，我們經(jīng)常會(huì)從職位描述上看到諸如“精通MySQL”爪喘、“SQL語(yǔ)句優(yōu)化”、“了解數(shù)據(jù)庫(kù)原理”等要求纠拔。我們知道一般的應(yīng)用系統(tǒng)秉剑，讀寫比例在10:1左右，而且插入操作和一般的更新操作很少出現(xiàn)性能問(wèn)題绿语，遇到最多的秃症，也是最容易出問(wèn)題的，還是一些復(fù)雜的查詢操作吕粹，所以查詢語(yǔ)句的優(yōu)化顯然是重中之重种柑。

本人從13年7月份起，一直在美團(tuán)核心業(yè)務(wù)系統(tǒng)部做慢查詢的優(yōu)化工作匹耕，共計(jì)十余個(gè)系統(tǒng)聚请，累計(jì)解決和積累了上百個(gè)慢查詢案例。隨著業(yè)務(wù)的復(fù)雜性提升，遇到的問(wèn)題千奇百怪驶赏，五花八門炸卑，匪夷所思。本文旨在以開發(fā)工程師的角度來(lái)解釋數(shù)據(jù)庫(kù)索引的原理和如何優(yōu)化慢查詢煤傍。

一個(gè)慢查詢引發(fā)的思考

select   count(*) from   task where   status=2    and operator_id=20839    and operate_time>1371169729    and operate_time<1371174603    and type=2;

系統(tǒng)使用者反應(yīng)有一個(gè)功能越來(lái)越慢盖文，于是工程師找到了上面的SQL。
并且興致沖沖的找到了我蚯姆，“這個(gè)SQL需要優(yōu)化五续，給我把每個(gè)字段都加上索引”
我很驚訝，問(wèn)道“為什么需要每個(gè)字段都加上索引龄恋？”
“把查詢的字段都加上索引會(huì)更快”工程師信心滿滿
“這種情況完全可以建一個(gè)聯(lián)合索引疙驾，因?yàn)槭亲钭笄熬Y匹配，所以operate_time需要放到最后郭毕，而且還需要把其他相關(guān)的查詢都拿來(lái)它碎，需要做一個(gè)綜合評(píng)估∠匝海”
“聯(lián)合索引扳肛？最左前綴匹配乘碑？綜合評(píng)估敞峭？”工程師不禁陷入了沉思。
多數(shù)情況下蝉仇，我們知道索引能夠提高查詢效率旋讹，但應(yīng)該如何建立索引？索引的順序如何轿衔？許多人卻只知道大概沉迹。其實(shí)理解這些概念并不難，而且索引的原理遠(yuǎn)沒(méi)有想象的那么復(fù)雜害驹。

二：索引建立

1. 主鍵索引

primary key() 要求關(guān)鍵字不能重復(fù)鞭呕，也不能為null,同時(shí)增加主鍵約束
主鍵索引定義時(shí)，不能命名

2. 唯一索引

unique index() 要求關(guān)鍵字不能重復(fù)宛官，同時(shí)增加唯一約束

3. 普通索引

index() 對(duì)關(guān)鍵字沒(méi)有要求

4. 全文索引

fulltext key() 關(guān)鍵字的來(lái)源不是所有字段的數(shù)據(jù)葫松，而是字段中提取的特別關(guān)鍵字

關(guān)鍵字：可以是某個(gè)字段或多個(gè)字段，多個(gè)字段稱為復(fù)合索引

建表：creat table student(    stu_id int unsigned not null auto_increment,    name varchar(32) not null default '',    phone char(11) not null default '',    stu_code varchar(32) not null default '',    stu_desc text,    primary key ('stu_id'),     //主鍵索引    unique index 'stu_code' ('stu_code'), //唯一索引    index 'name_phone' ('name','phone'),  //普通索引底洗，復(fù)合索引    fulltext index 'stu_desc' ('stu_desc'), //全文索引) engine=myisam charset=utf8; 更新：alert table student    add primary key ('stu_id'),     //主鍵索引    add unique index 'stu_code' ('stu_code'), //唯一索引    add index 'name_phone' ('name','phone'),  //普通索引腋么，復(fù)合索引    add fulltext index 'stu_desc' ('stu_desc'); //全文索引 刪除：alert table sutdent    drop primary key,    drop index 'stu_code',    drop index 'name_phone',    drop index 'stu_desc';

三：淺析explain用法

有什么用？

在MySQL中亥揖，當(dāng)數(shù)據(jù)量增長(zhǎng)的特別大的時(shí)候就需要用到索引來(lái)優(yōu)化SQL語(yǔ)句唉擂，而如何才能判斷我們辛辛苦苦寫出的SQL語(yǔ)句是否優(yōu)良？這時(shí)候explain就派上了用場(chǎng)盆色。

怎么使用？

explain + SQL語(yǔ)句即可 如：explain select * from table;

如下

相信第一次使用explain參數(shù)的朋友一定會(huì)疑惑這一大堆參數(shù)究竟有什么用呢圣贸？筆者搜集了一些資料，在這兒做一個(gè)總結(jié)希望能夠幫助大家理解扛稽。

參數(shù)介紹

id

如果是子查詢吁峻，id的序號(hào)會(huì)遞增，id的值越大優(yōu)先級(jí)越高在张，越先被執(zhí)行

select_type

查詢的類型锡搜，主要用于區(qū)別普通查詢、聯(lián)合查詢瞧掺、子查詢等的復(fù)雜查詢 SIMPLE:簡(jiǎn)單的select查詢，查詢中不包含子查詢或者UNION PRIMARY:查詢中若包含任何復(fù)雜的子部分凡傅，最外層查詢則被標(biāo)記為PRIMARY（最后加載的那一個(gè) ） SUBQUERY:在SELECT或WHERE列表中包含了子查詢 DERIVED:在FROM列表中包含的子查詢被標(biāo)記為DERIVED（衍生）Mysql會(huì)遞歸執(zhí)行這些子查詢辟狈，把結(jié)果放在臨時(shí)表里。 UNION:若第二個(gè)SELECT出現(xiàn)在UNION之后夏跷，則被標(biāo)記為UNION哼转；若UNION包含在FROM字句的查詢中，外層SELECT將被標(biāo)記為:DERIVED UNION RESULT:從UNION表獲取結(jié)果的SELECT type

顯示查詢使用了何種類型

從最好到最差依次是System>const>eq_ref>range>index>All（全表掃描） 一般來(lái)說(shuō)至少達(dá)到range級(jí)別槽华，最好達(dá)到ref

System:表只有一行記錄壹蔓，這是const類型的特例，平時(shí)不會(huì)出現(xiàn)(忽略不計(jì))const:表示通過(guò)索引一次就找到了,const用于比較primary key或者unique索引猫态，因?yàn)橹黄ヅ湟恍袛?shù)據(jù)佣蓉，所以很快。如將主鍵置于where列表中亲雪，MySQL就能將該查詢轉(zhuǎn)換為一個(gè)常量勇凭。

eq_ref:唯一性索引掃描，對(duì)于每個(gè)索引鍵义辕，表中只有一條記錄與之匹配虾标。常見于主鍵或唯一索引掃描。

ref：非唯一索引掃描灌砖，返回匹配某個(gè)單獨(dú)值的行璧函，本質(zhì)上也是一種索引訪問(wèn)，它返回所有匹配某個(gè)單獨(dú)值的行基显，然而它可能會(huì)找到多個(gè)符合條件的行蘸吓，所以它應(yīng)該屬于查找和掃描的混合體range：只檢索給定范圍的行，使用一個(gè)索引來(lái)選擇行撩幽。

key列顯示使用了哪個(gè)索引美澳，一般就是在你的where語(yǔ)句中出現(xiàn)了between、<、>制跟、in等的查詢舅桩。這種范圍掃描索引比全表掃描要好，因?yàn)樗恍枰_始于索引的某一點(diǎn)雨膨，而結(jié)束于另一點(diǎn)擂涛，不用掃描全部索引。index:FULL INDEX SCAN,index與all區(qū)別為index類型只遍歷索引樹聊记。這通常比all快撒妈，因?yàn)樗饕募ǔ１葦?shù)據(jù)文件小。

extra

包含不適合在其他列中顯示但十分重要的額外信息 包含的信息： （危險(xiǎn)!）Using

filesort:說(shuō)明mysql會(huì)對(duì)數(shù)據(jù)使用一個(gè)外部的索引排序排监，而不是按照表內(nèi)的索引順序進(jìn)行讀取狰右，MYSQL中無(wú)法利用索引完成的排序操作稱為“文件排序” （特別危險(xiǎn)!）Using

temporary:使用了臨時(shí)表保存中間結(jié)果，MYSQL在對(duì)查詢結(jié)果排序時(shí)使用臨時(shí)表舆床。常見于排序order by 和分組查詢 group by Using

index:表示相應(yīng)的select操作中使用了覆蓋索引棋蚌，避免訪問(wèn)了表的數(shù)據(jù)行，效率不錯(cuò)挨队。如果同時(shí)出現(xiàn)using

where谷暮，表明索引被用來(lái)執(zhí)行索引鍵值的查找；如果沒(méi)有同時(shí)出現(xiàn)using where盛垦，表明索引用來(lái)讀取數(shù)據(jù)而非執(zhí)行查找操作湿弦。

possible_keys

顯示可能應(yīng)用在這張表中的索引，一個(gè)或多個(gè)腾夯。查詢涉及到的字段上若存在索引颊埃，則該索引將被列出， 但不一定被查詢實(shí)際使用

key

實(shí)際使用的索引蝶俱，如果為NULL竟秫，則沒(méi)有使用索引。查詢中若使用了覆蓋索引跷乐，則該索引僅出現(xiàn)在key列表中肥败，key參數(shù)可以作為使用了索引的判斷標(biāo)準(zhǔn)

key_len

:表示索引中使用的字節(jié)數(shù)，可通過(guò)該列計(jì)算查詢中索引的長(zhǎng)度愕提，在不損失精確性的情況下馒稍，長(zhǎng)度越短越好，key_len顯示的值為索引字段的最大可能長(zhǎng)度浅侨，并非實(shí)際使用長(zhǎng)度纽谒，即key_len是根據(jù)表定義計(jì)算而得，不是通過(guò)表內(nèi)檢索出的如输。

ref

顯示索引的哪一列被使用了鼓黔，如果可能的話央勒，是一個(gè)常數(shù)。哪些列或常量被用于查找索引上的值澳化。

rows

根據(jù)表統(tǒng)計(jì)信息及索引選用情況崔步，大致估算出找到所需記錄所需要讀取的行數(shù)

四：慢查詢優(yōu)化

關(guān)于MySQL索引原理是比較枯燥的東西，大家只需要有一個(gè)感性的認(rèn)識(shí)缎谷，并不需要理解得非常透徹和深入井濒。我們回頭來(lái)看看一開始我們說(shuō)的慢查詢，了解完索引原理之后列林，大家是不是有什么想法呢瑞你？先總結(jié)一下索引的幾大基本原則

建索引的幾大原則

1.最左前綴匹配原則，非常重要的原則希痴，mysql會(huì)一直向右匹配直到遇到范圍查詢(>者甲、<、between砌创、like)就停止匹配虏缸，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的纺铭，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調(diào)整刀疙。

2.=和in可以亂序舶赔，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優(yōu)化器會(huì)幫你優(yōu)化成索引可以識(shí)別的形式

3.盡量選擇區(qū)分度高的列作為索引,區(qū)分度的公式是count(distinct col)/count(*)谦秧，表示字段不重復(fù)的比例竟纳，比例越大我們掃描的記錄數(shù)越少，唯一鍵的區(qū)分度是1疚鲤，而一些狀態(tài)锥累、性別字段可能在大數(shù)據(jù)面前區(qū)分度就是0，那可能有人會(huì)問(wèn)集歇，這個(gè)比例有什么經(jīng)驗(yàn)值嗎桶略？使用場(chǎng)景不同，這個(gè)值也很難確定诲宇，一般需要join的字段我們都要求是0.1以上际歼，即平均1條掃描10條記錄

4.索引列不能參與計(jì)算，保持列“干凈”姑蓝，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引鹅心，原因很簡(jiǎn)單，b+樹中存的都是數(shù)據(jù)表中的字段值纺荧，但進(jìn)行檢索時(shí)旭愧，需要把所有元素都應(yīng)用函數(shù)才能比較颅筋，顯然成本太大。所以語(yǔ)句應(yīng)該寫成create_time = unix_timestamp(’2014-05-29’);
5.盡量的擴(kuò)展索引输枯，不要新建索引议泵。比如表中已經(jīng)有a的索引，現(xiàn)在要加(a,b)的索引用押，那么只需要修改原來(lái)的索引即可

回到開始的慢查詢

根據(jù)最左匹配原則肢簿，最開始的sql語(yǔ)句的索引應(yīng)該是status、operator_id蜻拨、type池充、operate_time的聯(lián)合索引；其中status缎讼、operator_id收夸、type的順序可以顛倒，所以我才會(huì)說(shuō)血崭，把這個(gè)表的所有相關(guān)查詢都找到卧惜，會(huì)綜合分析；

比如還有如下查詢

select * from task where status = 0 and type = 12 limit 10;

select count(*) from task where status = 0 ;

那么索引建立成(status,type,operator_id,operate_time)就是非常正確的夹纫，因?yàn)榭梢愿采w到所有情況咽瓷。這個(gè)就是利用了索引的最左匹配的原則

查詢優(yōu)化神器 - explain命令

關(guān)于explain命令相信大家并不陌生，具體用法和字段含義可以參考官網(wǎng)explain-output舰讹，這里需要強(qiáng)調(diào)rows是核心指標(biāo)茅姜，絕大部分rows小的語(yǔ)句執(zhí)行一定很快（有例外，下面會(huì)講到）月匣。所以優(yōu)化語(yǔ)句基本上都是在優(yōu)化rows钻洒。

慢查詢優(yōu)化基本步驟

0.先運(yùn)行看看是否真的很慢，注意設(shè)置SQL_NO_CACHE
1.where條件單表查锄开，鎖定最小返回記錄表素标。這句話的意思是把查詢語(yǔ)句的where都應(yīng)用到表中返回的記錄數(shù)最小的表開始查起，單表每個(gè)字段分別查詢萍悴，看哪個(gè)字段的區(qū)分度最高
2.explain查看執(zhí)行計(jì)劃头遭，是否與1預(yù)期一致（從鎖定記錄較少的表開始查詢）
3.order by limit 形式的sql語(yǔ)句讓排序的表優(yōu)先查
4.了解業(yè)務(wù)方使用場(chǎng)景
5.加索引時(shí)參照建索引的幾大原則

6.觀察結(jié)果，不符合預(yù)期繼續(xù)從0分析

五：最左前綴原理與相關(guān)優(yōu)化

高效使用索引的首要條件是知道什么樣的查詢會(huì)使用到索引癣诱，這個(gè)問(wèn)題和B+Tree中的“最左前綴原理”有關(guān)任岸，下面通過(guò)例子說(shuō)明最左前綴原理。

這里先說(shuō)一下聯(lián)合索引的概念狡刘。在上文中享潜，我們都是假設(shè)索引只引用了單個(gè)的列，實(shí)際上嗅蔬，MySQL中的索引可以以一定順序引用多個(gè)列剑按，這種索引叫做聯(lián)合索引疾就，一般的，一個(gè)聯(lián)合索引是一個(gè)有序元組艺蝴，其中各個(gè)元素均為數(shù)據(jù)表的一列猬腰，實(shí)際上要嚴(yán)格定義索引需要用到關(guān)系代數(shù)，但是這里我不想討論太多關(guān)系代數(shù)的話題猜敢，因?yàn)槟菢訒?huì)顯得很枯燥姑荷，所以這里就不再做嚴(yán)格定義。另外缩擂，單列索引可以看成聯(lián)合索引元素?cái)?shù)為1的特例鼠冕。

以employees.titles表為例，下面先查看其上都有哪些索引：

1. SHOW INDEX FROM employees.titles;
2. +--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+
3. | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Null | Index_type |
4. +--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+
5. | titles | 0 | PRIMARY | 1 | emp_no | A | NULL | | BTREE |
6. | titles | 0 | PRIMARY | 2 | title | A | NULL | | BTREE |
7. | titles | 0 | PRIMARY | 3 | from_date | A | 443308 | | BTREE |
8. | titles | 1 | emp_no | 1 | emp_no | A | 443308 | | BTREE |
9. +--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+

從結(jié)果中可以到titles表的主索引為<emp_no, title, from_date>胯盯，還有一個(gè)輔助索引<emp_no>懈费。為了避免多個(gè)索引使事情變復(fù)雜（MySQL的SQL優(yōu)化器在多索引時(shí)行為比較復(fù)雜），這里我們將輔助索引drop掉：

1. ALTER TABLE employees.titles DROP INDEX emp_no;

這樣就可以專心分析索引PRIMARY的行為了博脑。

情況一：全列匹配憎乙。

1. EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title='Senior Engineer' AND from_date='1986-06-26';
2. +----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+
3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4. +----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+
5. | 1 | SIMPLE | titles | const | PRIMARY | PRIMARY | 59 | const,const,const | 1 | |
6. +----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+

很明顯，當(dāng)按照索引中所有列進(jìn)行精確匹配（這里精確匹配指“=”或“IN”匹配）時(shí)叉趣，索引可以被用到泞边。這里有一點(diǎn)需要注意，理論上索引對(duì)順序是敏感的疗杉，但是由于MySQL的查詢優(yōu)化器會(huì)自動(dòng)調(diào)整where子句的條件順序以使用適合的索引阵谚，例如我們將where中的條件順序顛倒：

1. EXPLAIN SELECT * FROM employees.titles WHERE from_date='1986-06-26' AND emp_no='10001' AND title='Senior Engineer';
2. +----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+
3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4. +----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+
5. | 1 | SIMPLE | titles | const | PRIMARY | PRIMARY | 59 | const,const,const | 1 | |
6. +----+-------------+--------+-------+---------------+---------+---------+-------------------+------+-------+

效果是一樣的。

情況二：最左前綴匹配乡数。

1. EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001';
2. +----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4. +----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
5. | 1 | SIMPLE | titles | ref | PRIMARY | PRIMARY | 4 | const | 1 | |
6. +----+-------------+--------+------+---------------+---------+---------+-------+------+-------+

當(dāng)查詢條件精確匹配索引的左邊連續(xù)一個(gè)或幾個(gè)列時(shí)椭蹄，如<emp_no>或<emp_no, title>闻牡，所以可以被用到净赴，但是只能用到一部分，即條件所組成的最左前綴罩润。上面的查詢從分析結(jié)果看用到了PRIMARY索引玖翅，但是key_len為4，說(shuō)明只用到了索引的第一列前綴割以。

情況三：查詢條件用到了索引中列的精確匹配金度，但是中間某個(gè)條件未提供。

1. EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND from_date='1986-06-26';
2. +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4. +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
5. | 1 | SIMPLE | titles | ref | PRIMARY | PRIMARY | 4 | const | 1 | Using where |
6. +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+

此時(shí)索引使用情況和情況二相同严沥，因?yàn)閠itle未提供猜极，所以查詢只用到了索引的第一列，而后面的from_date雖然也在索引中消玄，但是由于title不存在而無(wú)法和左前綴連接跟伏，因此需要對(duì)結(jié)果進(jìn)行掃描過(guò)濾from_date（這里由于emp_no唯一丢胚，所以不存在掃描）。

如果想讓from_date也使用索引而不是where過(guò)濾受扳，可以增加一個(gè)輔助索引<emp_no, from_date>携龟，此時(shí)上面的查詢會(huì)使用這個(gè)索引。除此之外勘高，還可以使用一種稱之為“隔離列”的優(yōu)化方法峡蟋，將emp_no與from_date之間的“坑”填上。

首先我們看下title一共有幾種不同的值：

1. SELECT DISTINCT(title) FROM employees.titles;
2. +--------------------+
3. | title |
4. +--------------------+
5. | Senior Engineer |
6. | Staff |
7. | Engineer |
8. | Senior Staff |
9. | Assistant Engineer |
10. | Technique Leader |
11. | Manager |
12. +--------------------+

只有7種华望。在這種成為“坑”的列值比較少的情況下蕊蝗，可以考慮用“IN”來(lái)填補(bǔ)這個(gè)“坑”從而形成最左前綴：

1. EXPLAIN SELECT * FROM employees.titles
2. WHERE emp_no='10001'
3. AND title IN ('Senior Engineer', 'Staff', 'Engineer', 'Senior Staff', 'Assistant Engineer', 'Technique Leader', 'Manager')
4. AND from_date='1986-06-26';
5. +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
6. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
7. +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
8. | 1 | SIMPLE | titles | range | PRIMARY | PRIMARY | 59 | NULL | 7 | Using where |
9. +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

這次key_len為59，說(shuō)明索引被用全了立美，但是從type和rows看出IN實(shí)際上執(zhí)行了一個(gè)range查詢匿又，這里檢查了7個(gè)key〗ㄌ悖看下兩種查詢的性能比較：

1. SHOW PROFILES;
2. +----------+------------+-------------------------------------------------------------------------------+
3. | Query_ID | Duration | Query |
4. +----------+------------+-------------------------------------------------------------------------------+
5. | 10 | 0.00058000 | SELECT * FROM employees.titles WHERE emp_no='10001' AND from_date='1986-06-26'|
6. | 11 | 0.00052500 | SELECT * FROM employees.titles WHERE emp_no='10001' AND title IN ... |
7. +----------+------------+-------------------------------------------------------------------------------+

“填坑”后性能提升了一點(diǎn)碌更。如果經(jīng)過(guò)emp_no篩選后余下很多數(shù)據(jù)，則后者性能優(yōu)勢(shì)會(huì)更加明顯洞慎。當(dāng)然痛单，如果title的值很多，用填坑就不合適了劲腿，必須建立輔助索引旭绒。

情況四：查詢條件沒(méi)有指定索引第一列。

1. EXPLAIN SELECT * FROM employees.titles WHERE from_date='1986-06-26';
2. +----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4. +----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
5. | 1 | SIMPLE | titles | ALL | NULL | NULL | NULL | NULL | 443308 | Using where |
6. +----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

由于不是最左前綴焦人，索引這樣的查詢顯然用不到索引挥吵。

情況五：匹配某列的前綴字符串。

1. EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title LIKE 'Senior%';
2. +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4. +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
5. | 1 | SIMPLE | titles | range | PRIMARY | PRIMARY | 56 | NULL | 1 | Using where |
6. +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

此時(shí)可以用到索引花椭，但是如果通配符不是只出現(xiàn)在末尾忽匈，則無(wú)法使用索引。（原文表述有誤矿辽，如果通配符%不出現(xiàn)在開頭丹允，則可以用到索引，但根據(jù)具體情況不同可能只會(huì)用其中一個(gè)前綴）

情況六：范圍查詢袋倔。

1. EXPLAIN SELECT * FROM employees.titles WHERE emp_no < '10010' and title='Senior Engineer';
2. +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4. +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
5. | 1 | SIMPLE | titles | range | PRIMARY | PRIMARY | 4 | NULL | 16 | Using where |
6. +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

范圍列可以用到索引（必須是最左前綴）雕蔽，但是范圍列后面的列無(wú)法用到索引。同時(shí)宾娜，索引最多用于一個(gè)范圍列批狐，因此如果查詢條件中有兩個(gè)范圍列則無(wú)法全用到索引。

1. EXPLAIN SELECT * FROM employees.titles
2. WHERE emp_no < '10010'
3. AND title='Senior Engineer'
4. AND from_date BETWEEN '1986-01-01' AND '1986-12-31';
5. +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
6. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
7. +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
8. | 1 | SIMPLE | titles | range | PRIMARY | PRIMARY | 4 | NULL | 16 | Using where |
9. +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

可以看到索引對(duì)第二個(gè)范圍索引無(wú)能為力前塔。這里特別要說(shuō)明MySQL一個(gè)有意思的地方嚣艇，那就是僅用explain可能無(wú)法區(qū)分范圍索引和多值匹配缘眶，因?yàn)樵趖ype中這兩者都顯示為range。同時(shí)髓废，用了“between”并不意味著就是范圍查詢巷懈，例如下面的查詢：

1. EXPLAIN SELECT * FROM employees.titles
2. WHERE emp_no BETWEEN '10001' AND '10010'
3. AND title='Senior Engineer'
4. AND from_date BETWEEN '1986-01-01' AND '1986-12-31';
5. +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
6. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
7. +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
8. | 1 | SIMPLE | titles | range | PRIMARY | PRIMARY | 59 | NULL | 16 | Using where |
9. +----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

看起來(lái)是用了兩個(gè)范圍查詢，但作用于emp_no上的“BETWEEN”實(shí)際上相當(dāng)于“IN”慌洪，也就是說(shuō)emp_no實(shí)際是多值精確匹配顶燕。可以看到這個(gè)查詢用到了索引全部三個(gè)列冈爹。因此在MySQL中要謹(jǐn)慎地區(qū)分多值匹配和范圍匹配涌攻，否則會(huì)對(duì)MySQL的行為產(chǎn)生困惑。

情況七：查詢條件中含有函數(shù)或表達(dá)式频伤。

很不幸恳谎，如果查詢條件中含有函數(shù)或表達(dá)式，則MySQL不會(huì)為這列使用索引（雖然某些在數(shù)學(xué)意義上可以使用）憋肖。例如：

1. EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND left(title, 6)='Senior';
2. +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4. +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
5. | 1 | SIMPLE | titles | ref | PRIMARY | PRIMARY | 4 | const | 1 | Using where |
6. +----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+

雖然這個(gè)查詢和情況五中功能相同因痛，但是由于使用了函數(shù)left，則無(wú)法為title列應(yīng)用索引岸更，而情況五中用LIKE則可以鸵膏。再如：

1. EXPLAIN SELECT * FROM employees.titles WHERE emp_no - 1='10000';
2. +----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4. +----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
5. | 1 | SIMPLE | titles | ALL | NULL | NULL | NULL | NULL | 443308 | Using where |
6. +----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

顯然這個(gè)查詢等價(jià)于查詢emp_no為10001的函數(shù)，但是由于查詢條件是一個(gè)表達(dá)式怎炊，MySQL無(wú)法為其使用索引谭企。看來(lái)MySQL還沒(méi)有智能到自動(dòng)優(yōu)化常量表達(dá)式的程度评肆，因此在寫查詢語(yǔ)句時(shí)盡量避免表達(dá)式出現(xiàn)在查詢中债查，而是先手工私下代數(shù)運(yùn)算，轉(zhuǎn)換為無(wú)表達(dá)式的查詢語(yǔ)句瓜挽。

索引選擇性與前綴索引

既然索引可以加快查詢速度盹廷，那么是不是只要是查詢語(yǔ)句需要，就建上索引秸抚？答案是否定的速和。因?yàn)樗饕m然加快了查詢速度歹垫，但索引也是有代價(jià)的：索引文件本身要消耗存儲(chǔ)空間剥汤，同時(shí)索引會(huì)加重插入、刪除和修改記錄時(shí)的負(fù)擔(dān)排惨，另外吭敢，MySQL在運(yùn)行時(shí)也要消耗資源維護(hù)索引，因此索引并不是越多越好暮芭。一般兩種情況下不建議建索引鹿驼。

第一種情況是表記錄比較少欲低，例如一兩千條甚至只有幾百條記錄的表，沒(méi)必要建索引畜晰，讓查詢做全表掃描就好了砾莱。至于多少條記錄才算多，這個(gè)個(gè)人有個(gè)人的看法凄鼻，我個(gè)人的經(jīng)驗(yàn)是以2000作為分界線腊瑟，記錄數(shù)不超過(guò) 2000可以考慮不建索引，超過(guò)2000條可以酌情考慮索引块蚌。

另一種不建議建索引的情況是索引的選擇性較低闰非。所謂索引的選擇性（Selectivity），是指不重復(fù)的索引值（也叫基數(shù)峭范，Cardinality）與表記錄數(shù)（#T）的比值：

Index Selectivity = Cardinality / #T

顯然選擇性的取值范圍為(0, 1]财松，選擇性越高的索引價(jià)值越大，這是由B+Tree的性質(zhì)決定的纱控。例如辆毡，上文用到的employees.titles表，如果title字段經(jīng)常被單獨(dú)查詢甜害，是否需要建索引胚迫，我們看一下它的選擇性：

1. SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;
2. +-------------+
3. | Selectivity |
4. +-------------+
5. | 0.0000 |
6. +-------------+

title的選擇性不足0.0001（精確值為0.00001579），所以實(shí)在沒(méi)有什么必要為其單獨(dú)建索引唾那。

有一種與索引選擇性有關(guān)的索引優(yōu)化策略叫做前綴索引访锻，就是用列的前綴代替整個(gè)列作為索引key，當(dāng)前綴長(zhǎng)度合適時(shí)闹获，可以做到既使得前綴索引的選擇性接近全列索引期犬，同時(shí)因?yàn)樗饕齥ey變短而減少了索引文件的大小和維護(hù)開銷。下面以employees.employees表為例介紹前綴索引的選擇和使用避诽。

從圖12可以看到employees表只有一個(gè)索引<emp_no>龟虎，那么如果我們想按名字搜索一個(gè)人，就只能全表掃描了：

1. EXPLAIN SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido';
2. +----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
4. +----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
5. | 1 | SIMPLE | employees | ALL | NULL | NULL | NULL | NULL | 300024 | Using where |
6. +----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

如果頻繁按名字搜索員工沙庐，這樣顯然效率很低鲤妥，因此我們可以考慮建索引。有兩種選擇拱雏，建<first_name>或<first_name, last_name>棉安，看下兩個(gè)索引的選擇性：

1. SELECT count(DISTINCT(first_name))/count(*) AS Selectivity FROM employees.employees;
2. +-------------+
3. | Selectivity |
4. +-------------+
5. | 0.0042 |
6. +-------------+
7. SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM employees.employees;
8. +-------------+
9. | Selectivity |
10. +-------------+
11. | 0.9313 |
12. +-------------+

<first_name>顯然選擇性太低，<first_name, last_name>選擇性很好铸抑，但是first_name和last_name加起來(lái)長(zhǎng)度為30贡耽，有沒(méi)有兼顧長(zhǎng)度和選擇性的辦法？可以考慮用first_name和last_name的前幾個(gè)字符建立索引，例如<first_name, left(last_name, 3)>蒲赂，看看其選擇性：

1. SELECT count(DISTINCT(concat(first_name, left(last_name, 3))))/count(*) AS Selectivity FROM employees.employees;
2. +-------------+
3. | Selectivity |
4. +-------------+
5. | 0.7879 |
6. +-------------+

選擇性還不錯(cuò)阱冶，但離0.9313還是有點(diǎn)距離，那么把last_name前綴加到4：

1. SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees.employees;
2. +-------------+
3. | Selectivity |
4. +-------------+
5. | 0.9007 |
6. +-------------+

這時(shí)選擇性已經(jīng)很理想了滥嘴，而這個(gè)索引的長(zhǎng)度只有18木蹬，比<first_name, last_name>短了接近一半，我們把這個(gè)前綴索引 建上：

1. ALTER TABLE employees.employees
2. ADD INDEX first_name_last_name4 (first_name, last_name(4));

此時(shí)再執(zhí)行一遍按名字查詢若皱，比較分析一下與建索引前的結(jié)果：

1. SHOW PROFILES;
2. +----------+------------+---------------------------------------------------------------------------------+
3. | Query_ID | Duration | Query |
4. +----------+------------+---------------------------------------------------------------------------------+
5. | 87 | 0.11941700 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
6. | 90 | 0.00092400 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
7. +----------+------------+---------------------------------------------------------------------------------+

性能的提升是顯著的届囚，查詢速度提高了120多倍。

前綴索引兼顧索引大小和查詢速度是尖，但是其缺點(diǎn)是不能用于ORDER BY和GROUP BY操作意系，也不能用于Covering index（即當(dāng)索引本身包含查詢所需全部數(shù)據(jù)時(shí)，不再訪問(wèn)數(shù)據(jù)文件本身

六：InnoDB的主鍵選擇與插入優(yōu)化

在使用InnoDB存儲(chǔ)引擎時(shí)饺汹，如果沒(méi)有特別的需要蛔添，請(qǐng)永遠(yuǎn)使用一個(gè)與業(yè)務(wù)無(wú)關(guān)的自增字段作為主鍵。

經(jīng)扯荡牵看到有帖子或博客討論主鍵選擇問(wèn)題迎瞧，有人建議使用業(yè)務(wù)無(wú)關(guān)的自增主鍵，有人覺(jué)得沒(méi)有必要逸吵，完全可以使用如學(xué)號(hào)或身份證號(hào)這種唯一字段作為主鍵凶硅。不論支持哪種論點(diǎn)，大多數(shù)論據(jù)都是業(yè)務(wù)層面的扫皱。如果從數(shù)據(jù)庫(kù)索引優(yōu)化角度看足绅，使用InnoDB引擎而不使用自增主鍵絕對(duì)是一個(gè)糟糕的主意。

上文討論過(guò)InnoDB的索引實(shí)現(xiàn)韩脑，InnoDB使用聚集索引氢妈，數(shù)據(jù)記錄本身被存于主索引（一顆B+Tree）的葉子節(jié)點(diǎn)上。這就要求同一個(gè)葉子節(jié)點(diǎn)內(nèi)（大小為一個(gè)內(nèi)存頁(yè)或磁盤頁(yè)）的各條數(shù)據(jù)記錄按主鍵順序存放段多，因此每當(dāng)有一條新的記錄插入時(shí)首量，MySQL會(huì)根據(jù)其主鍵將其插入適當(dāng)?shù)墓?jié)點(diǎn)和位置，如果頁(yè)面達(dá)到裝載因子（InnoDB默認(rèn)為15/16）进苍，則開辟一個(gè)新的頁(yè)（節(jié)點(diǎn)）加缘。

如果表使用自增主鍵，那么每次插入新的記錄觉啊，記錄就會(huì)順序添加到當(dāng)前索引節(jié)點(diǎn)的后續(xù)位置拣宏，當(dāng)一頁(yè)寫滿，就會(huì)自動(dòng)開辟一個(gè)新的頁(yè)柄延。如下圖所示：

圖13

這樣就會(huì)形成一個(gè)緊湊的索引結(jié)構(gòu)蚀浆，近似順序填滿。由于每次插入時(shí)也不需要移動(dòng)已有數(shù)據(jù)搜吧，因此效率很高市俊，也不會(huì)增加很多開銷在維護(hù)索引上。

如果使用非自增主鍵（如果身份證號(hào)或?qū)W號(hào)等）滤奈，由于每次插入主鍵的值近似于隨機(jī)摆昧，因此每次新紀(jì)錄都要被插到現(xiàn)有索引頁(yè)得中間某個(gè)位置：

圖14

此時(shí)MySQL不得不為了將新記錄插到合適位置而移動(dòng)數(shù)據(jù)，甚至目標(biāo)頁(yè)面可能已經(jīng)被回寫到磁盤上而從緩存中清掉蜒程，此時(shí)又要從磁盤上讀回來(lái)绅你，這增加了很多開銷，同時(shí)頻繁的移動(dòng)昭躺、分頁(yè)操作造成了大量的碎片忌锯，得到了不夠緊湊的索引結(jié)構(gòu)，后續(xù)不得不通過(guò)OPTIMIZE TABLE來(lái)重建表并優(yōu)化填充頁(yè)面领炫。

因此偶垮，只要可以，請(qǐng)盡量在InnoDB上采用自增字段做主鍵帝洪。