溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
微信公眾號【Java技術(shù)江湖】一位阿里 Java 工程師的技術(shù)小站。作者黃小斜，專注 Java 相關(guān)技術(shù)：SSM、SpringBoot、MySQL、分布式、中間件、集群、Linux、網(wǎng)絡(luò)、多線程，偶爾講點(diǎn)Docker、ELK，同時(shí)也分享技術(shù)干貨和學(xué)習(xí)經(jīng)驗(yàn)，致力于Java全棧開發(fā)！
MySQL憑借著出色的性能、低廉的成本、豐富的資源，已經(jīng)成為絕大多數(shù)互聯(lián)網(wǎng)公司的首選關(guān)系型數(shù)據(jù)庫。雖然性能出色，但所謂“好馬配好鞍”，如何能夠更好的使用它，已經(jīng)成為開發(fā)工程師的必修課，我們經(jīng)常會(huì)從職位描述上看到諸如“精通MySQL”、“SQL語句優(yōu)化”、“了解數(shù)據(jù)庫原理”等要求。我們知道一般的應(yīng)用系統(tǒng)，讀寫比例在10:1左右，而且插入操作和一般的更新操作很少出現(xiàn)性能問題，遇到最多的，也是最容易出問題的，還是一些復(fù)雜的查詢操作，所以查詢語句的優(yōu)化顯然是重中之重。
本人從13年7月份起，一直在美團(tuán)核心業(yè)務(wù)系統(tǒng)部做慢查詢的優(yōu)化工作，共計(jì)十余個(gè)系統(tǒng)，累計(jì)解決和積累了上百個(gè)慢查詢案例。隨著業(yè)務(wù)的復(fù)雜性提升，遇到的問題千奇百怪，五花八門，匪夷所思。本文旨在以開發(fā)工程師的角度來解釋數(shù)據(jù)庫索引的原理和如何優(yōu)化慢查詢。
selectcount(*)fromtaskwherestatus=2 and operator_id=20839 and operate_time>1371169729 and operate_time<1371174603 and type=2;
系統(tǒng)使用者反應(yīng)有一個(gè)功能越來越慢，于是工程師找到了上面的SQL。
并且興致沖沖的找到了我，“這個(gè)SQL需要優(yōu)化，給我把每個(gè)字段都加上索引”
我很驚訝，問道“為什么需要每個(gè)字段都加上索引？”
“把查詢的字段都加上索引會(huì)更快”工程師信心滿滿
“這種情況完全可以建一個(gè)聯(lián)合索引，因?yàn)槭亲钭笄熬Y匹配，所以operate_time需要放到最后，而且還需要把其他相關(guān)的查詢都拿來，需要做一個(gè)綜合評估?！?
“聯(lián)合索引？最左前綴匹配？綜合評估？”工程師不禁陷入了沉思。
多數(shù)情況下，我們知道索引能夠提高查詢效率，但應(yīng)該如何建立索引？索引的順序如何？許多人卻只知道大概。其實(shí)理解這些概念并不難，而且索引的原理遠(yuǎn)沒有想象的那么復(fù)雜。
索引目的
索引的目的在于提高查詢效率，可以類比字典，如果要查“mysql”這個(gè)單詞，我們肯定需要定位到m字母，然后從下往下找到y(tǒng)字母，再找到剩下的sql。如果沒有索引，那么你可能需要把所有單詞看一遍才能找到你想要的，如果我想找到m開頭的單詞呢？或者ze開頭的單詞呢？是不是覺得如果沒有索引，這個(gè)事情根本無法完成？
索引原理
除了詞典，生活中隨處可見索引的例子，如火車站的車次表、圖書的目錄等。它們的原理都是一樣的，通過不斷的縮小想要獲得數(shù)據(jù)的范圍來篩選出最終想要的結(jié)果，同時(shí)把隨機(jī)的事件變成順序的事件，也就是我們總是通過同一種查找方式來鎖定數(shù)據(jù)。
數(shù)據(jù)庫也是一樣，但顯然要復(fù)雜許多，因?yàn)椴粌H面臨著等值查詢，還有范圍查詢(>、<、between、in)、模糊查詢(like)、并集查詢(or)等等。數(shù)據(jù)庫應(yīng)該選擇怎么樣的方式來應(yīng)對所有的問題呢？我們回想字典的例子，能不能把數(shù)據(jù)分成段，然后分段查詢呢？最簡單的如果1000條數(shù)據(jù)，1到100分成第一段，101到200分成第二段，201到300分成第三段......這樣查第250條數(shù)據(jù)，只要找第三段就可以了，一下子去除了90%的無效數(shù)據(jù)。但如果是1千萬的記錄呢，分成幾段比較好？稍有算法基礎(chǔ)的同學(xué)會(huì)想到搜索樹，其平均復(fù)雜度是lgN，具有不錯(cuò)的查詢性能。但這里我們忽略了一個(gè)關(guān)鍵的問題，復(fù)雜度模型是基于每次相同的操作成本來考慮的，數(shù)據(jù)庫實(shí)現(xiàn)比較復(fù)雜，數(shù)據(jù)保存在磁盤上，而為了提高性能，每次又可以把部分?jǐn)?shù)據(jù)讀入內(nèi)存來計(jì)算，因?yàn)槲覀冎涝L問磁盤的成本大概是訪問內(nèi)存的十萬倍左右，所以簡單的搜索樹難以滿足復(fù)雜的應(yīng)用場景。
磁盤IO與預(yù)讀
前面提到了訪問磁盤，那么這里先簡單介紹一下磁盤IO和預(yù)讀，磁盤讀取數(shù)據(jù)靠的是機(jī)械運(yùn)動(dòng)，每次讀取數(shù)據(jù)花費(fèi)的時(shí)間可以分為尋道時(shí)間、旋轉(zhuǎn)延遲、傳輸時(shí)間三個(gè)部分，尋道時(shí)間指的是磁臂移動(dòng)到指定磁道所需要的時(shí)間，主流磁盤一般在5ms以下；旋轉(zhuǎn)延遲就是我們經(jīng)常聽說的磁盤轉(zhuǎn)速，比如一個(gè)磁盤7200轉(zhuǎn)，表示每分鐘能轉(zhuǎn)7200次，也就是說1秒鐘能轉(zhuǎn)120次，旋轉(zhuǎn)延遲就是1/120/2 = 4.17ms；傳輸時(shí)間指的是從磁盤讀出或?qū)?shù)據(jù)寫入磁盤的時(shí)間，一般在零點(diǎn)幾毫秒，相對于前兩個(gè)時(shí)間可以忽略不計(jì)。那么訪問一次磁盤的時(shí)間，即一次磁盤IO的時(shí)間約等于5+4.17 = 9ms左右，聽起來還挺不錯(cuò)的，但要知道一臺(tái)500 -MIPS的機(jī)器每秒可以執(zhí)行5億條指令，因?yàn)橹噶钜揽康氖请姷男再|(zhì)，換句話說執(zhí)行一次IO的時(shí)間可以執(zhí)行40萬條指令，數(shù)據(jù)庫動(dòng)輒十萬百萬乃至千萬級數(shù)據(jù)，每次9毫秒的時(shí)間，顯然是個(gè)災(zāi)難。下圖是計(jì)算機(jī)硬件延遲的對比圖，供大家參考：
考慮到磁盤IO是非常高昂的操作，計(jì)算機(jī)操作系統(tǒng)做了一些優(yōu)化，當(dāng)一次IO時(shí)，不光把當(dāng)前磁盤地址的數(shù)據(jù)，而是把相鄰的數(shù)據(jù)也都讀取到內(nèi)存緩沖區(qū)內(nèi)，因?yàn)榫植款A(yù)讀性原理告訴我們，當(dāng)計(jì)算機(jī)訪問一個(gè)地址的數(shù)據(jù)的時(shí)候，與其相鄰的數(shù)據(jù)也會(huì)很快被訪問到。每一次IO讀取的數(shù)據(jù)我們稱之為一頁(page)。具體一頁有多大數(shù)據(jù)跟操作系統(tǒng)有關(guān)，一般為4k或8k，也就是我們讀取一頁內(nèi)的數(shù)據(jù)時(shí)候，實(shí)際上才發(fā)生了一次IO，這個(gè)理論對于索引的數(shù)據(jù)結(jié)構(gòu)設(shè)計(jì)非常有幫助。
索引的數(shù)據(jù)結(jié)構(gòu)
前面講了生活中索引的例子，索引的基本原理，數(shù)據(jù)庫的復(fù)雜性，又講了操作系統(tǒng)的相關(guān)知識，目的就是讓大家了解，任何一種數(shù)據(jù)結(jié)構(gòu)都不是憑空產(chǎn)生的，一定會(huì)有它的背景和使用場景，我們現(xiàn)在總結(jié)一下，我們需要這種數(shù)據(jù)結(jié)構(gòu)能夠做些什么，其實(shí)很簡單，那就是：每次查找數(shù)據(jù)時(shí)把磁盤IO次數(shù)控制在一個(gè)很小的數(shù)量級，最好是常數(shù)數(shù)量級。那么我們就想到如果一個(gè)高度可控的多路搜索樹是否能滿足需求呢？就這樣，b+樹應(yīng)運(yùn)而生。
詳解b+樹
如上圖，是一顆b+樹，關(guān)于b+樹的定義可以參見
B+樹，這里只說一些重點(diǎn)，淺藍(lán)色的塊我們稱之為一個(gè)磁盤塊，可以看到每個(gè)磁盤塊包含幾個(gè)數(shù)據(jù)項(xiàng)（深藍(lán)色所示）和指針（黃色所示），如磁盤塊1包含數(shù)據(jù)項(xiàng)17和35，包含指針P1、P2、P3，P1表示小于17的磁盤塊，P2表示在17和35之間的磁盤塊，P3表示大于35的磁盤塊。真實(shí)的數(shù)據(jù)存在于葉子節(jié)點(diǎn)即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非葉子節(jié)點(diǎn)只不存儲(chǔ)真實(shí)的數(shù)據(jù)，只存儲(chǔ)指引搜索方向的數(shù)據(jù)項(xiàng)，如17、35并不真實(shí)存在于數(shù)據(jù)表中。
b+樹的查找過程
如圖所示，如果要查找數(shù)據(jù)項(xiàng)29，那么首先會(huì)把磁盤塊1由磁盤加載到內(nèi)存，此時(shí)發(fā)生一次IO，在內(nèi)存中用二分查找確定29在17和35之間，鎖定磁盤塊1的P2指針，內(nèi)存時(shí)間因?yàn)榉浅６蹋ㄏ啾却疟P的IO）可以忽略不計(jì)，通過磁盤塊1的P2指針的磁盤地址把磁盤塊3由磁盤加載到內(nèi)存，發(fā)生第二次IO，29在26和30之間，鎖定磁盤塊3的P2指針，通過指針加載磁盤塊8到內(nèi)存，發(fā)生第三次IO，同時(shí)內(nèi)存中做二分查找找到29，結(jié)束查詢，總計(jì)三次IO。真實(shí)的情況是，3層的b+樹可以表示上百萬的數(shù)據(jù)，如果上百萬的數(shù)據(jù)查找只需要三次IO，性能提高將是巨大的，如果沒有索引，每個(gè)數(shù)據(jù)項(xiàng)都要發(fā)生一次IO，那么總共需要百萬次的IO，顯然成本非常非常高。
b+樹性質(zhì)
1.通過上面的分析，我們知道IO次數(shù)取決于b+數(shù)的高度h，假設(shè)當(dāng)前數(shù)據(jù)表的數(shù)據(jù)為N，每個(gè)磁盤塊的數(shù)據(jù)項(xiàng)的數(shù)量是m，則有h=㏒(m+1)N，當(dāng)數(shù)據(jù)量N一定的情況下，m越大，h越小；而m = 磁盤塊的大小 / 數(shù)據(jù)項(xiàng)的大小，磁盤塊的大小也就是一個(gè)數(shù)據(jù)頁的大小，是固定的，如果數(shù)據(jù)項(xiàng)占的空間越小，數(shù)據(jù)項(xiàng)的數(shù)量越多，樹的高度越低。這就是為什么每個(gè)數(shù)據(jù)項(xiàng)，即索引字段要盡量的小，比如int占4字節(jié)，要比bigint8字節(jié)少一半。這也是為什么b+樹要求把真實(shí)的數(shù)據(jù)放到葉子節(jié)點(diǎn)而不是內(nèi)層節(jié)點(diǎn)，一旦放到內(nèi)層節(jié)點(diǎn)，磁盤塊的數(shù)據(jù)項(xiàng)會(huì)大幅度下降，導(dǎo)致樹增高。當(dāng)數(shù)據(jù)項(xiàng)等于1時(shí)將會(huì)退化成線性表。
2.當(dāng)b+樹的數(shù)據(jù)項(xiàng)是復(fù)合的數(shù)據(jù)結(jié)構(gòu)，比如(name,age,sex)的時(shí)候，b+數(shù)是按照從左到右的順序來建立搜索樹的，比如當(dāng)(張三,20,F)這樣的數(shù)據(jù)來檢索的時(shí)候，b+樹會(huì)優(yōu)先比較name來確定下一步的所搜方向，如果name相同再依次比較age和sex，最后得到檢索的數(shù)據(jù)；但當(dāng)(20,F)這樣的沒有name的數(shù)據(jù)來的時(shí)候，b+樹就不知道下一步該查哪個(gè)節(jié)點(diǎn)，因?yàn)榻⑺阉鳂涞臅r(shí)候name就是第一個(gè)比較因子，必須要先根據(jù)name來搜索才能知道下一步去哪里查詢。比如當(dāng)(張三,F)這樣的數(shù)據(jù)來檢索時(shí)，b+樹可以用name來指定搜索方向，但下一個(gè)字段age的缺失，所以只能把名字等于張三的數(shù)據(jù)都找到，然后再匹配性別是F的數(shù)據(jù)了， 這個(gè)是非常重要的性質(zhì)，即索引的最左匹配特性。
1. 主鍵索引
primary key() 要求關(guān)鍵字不能重復(fù)，也不能為null,同時(shí)增加主鍵約束
主鍵索引定義時(shí)，不能命名
2. 唯一索引
unique index() 要求關(guān)鍵字不能重復(fù)，同時(shí)增加唯一約束
3. 普通索引
index() 對關(guān)鍵字沒有要求
4. 全文索引
fulltext key() 關(guān)鍵字的來源不是所有字段的數(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 studentadd 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 sutdentdrop primary key,drop index 'stu_code',drop index 'name_phone',drop index 'stu_desc';
在MySQL中，當(dāng)數(shù)據(jù)量增長的特別大的時(shí)候就需要用到索引來優(yōu)化SQL語句，而如何才能判斷我們辛辛苦苦寫出的SQL語句是否優(yōu)良？這時(shí)候 explain就派上了用場。
explain + SQL語句即可 如：explain select * from table;
如下
cdn.xitu.io/2018/5/17/1636ce849c800023?imageView2/0/w/1280/h/960/format/webp/ignore-error/1">
相信第一次使用explain參數(shù)的朋友一定會(huì)疑惑這一大堆參數(shù)究竟有什么用呢？筆者搜集了一些資料，在這兒做一個(gè)總結(jié)希望能夠幫助大家理解。
如果是子查詢，id的序號會(huì)遞增，id的值越大優(yōu)先級越高，越先被執(zhí)行
顯示查詢使用了何種類型 從最好到最差依次是System>const>eq_ref>range>index>All（**全表掃描**） 一般來說**至少達(dá)到range級別，最好達(dá)到ref**System:表只有一行記錄，這是const類型的特例，平時(shí)不會(huì)出現(xiàn)(忽略不計(jì))const:表示通過索引一次就找到了,const用于比較primary key或者unique索引，因?yàn)橹黄ヅ湟恍袛?shù)據(jù)，所以很快。如將主鍵置于where列表中，MySQL就能將該查詢轉(zhuǎn)換為一個(gè)常量。eq_ref:唯一性索引掃描，對于每個(gè)索引鍵，表中只有一條記錄與之匹配。常見于主鍵或唯一索引掃描。ref：非唯一索引掃描，返回匹配某個(gè)單獨(dú)值的行，本質(zhì)上也是一種索引訪問，它返回所有匹配某個(gè)單獨(dú)值的行，然而它可能會(huì)找到多個(gè)符合條件的行，所以它應(yīng)該屬于查找和掃描的混合體range：只檢索給定范圍的行，使用一個(gè)索引來選擇行。key列顯示使用了哪個(gè)索引，一般就是在你的where語句中出現(xiàn)了between、<、>、in等的查詢。這種范圍掃描索引比全表掃描要好，因?yàn)樗恍枰_始于索引的某一點(diǎn)，而結(jié)束于另一點(diǎn)，不用掃描全部索引。index:FULL INDEX SCAN,index與all區(qū)別為index類型只遍歷索引樹。這通常比all快，因?yàn)樗饕募ǔ１葦?shù)據(jù)文件小。
包含不適合在其他列中顯示但十分重要的額外信息 包含的信息： **（危險(xiǎn)!）**Using filesort:說明mysql會(huì)對數(shù)據(jù)使用一個(gè)外部的索引排序，而不是按照表內(nèi)的索引順序進(jìn)行讀取，MYSQL中無法利用索引完成的排序操作稱為“文件排序” **（特別危險(xiǎn)!）**Using temporary:使用了臨時(shí)表保存中間結(jié)果，MYSQL在對查詢結(jié)果排序時(shí)使用臨時(shí)表。常見于排序order by 和分組查詢 group by Using index:表示相應(yīng)的select操作中使用了覆蓋索引，避免訪問了表的數(shù)據(jù)行，效率不錯(cuò)。如果同時(shí)出現(xiàn)using where，表明索引被用來執(zhí)行索引鍵值的查找；如果沒有同時(shí)出現(xiàn)using where，表明索引用來讀取數(shù)據(jù)而非執(zhí)行查找操作。
顯示可能應(yīng)用在這張表中的索引，一個(gè)或多個(gè)。查詢涉及到的字段上若存在索引，則該索引將被列出， 但不一定被查詢實(shí)際使用
實(shí)際使用的索引，如果為NULL，則沒有使用索引。查詢中若使用了覆蓋索引，則該索引僅出現(xiàn)在key列表中，key參數(shù)可以作為使用了索引的判斷標(biāo)準(zhǔn)
:表示索引中使用的字節(jié)數(shù)，可通過該列計(jì)算查詢中索引的長度，在不損失精確性的情況下，長度越短越好，key_len顯示的值為索引字段的最大可能長度，并非實(shí)際使用長度，即key_len是根據(jù)表定義計(jì)算而得，不是通過表內(nèi)檢索出的。
顯示索引的哪一列被使用了，如果可能的話，是一個(gè)常數(shù)。哪些列或常量被用于查找索引上的值。
根據(jù)表統(tǒng)計(jì)信息及索引選用情況，大致估算出找到所需記錄所需要讀取的行數(shù)
關(guān)于MySQL索引原理是比較枯燥的東西，大家只需要有一個(gè)感性的認(rèn)識，并不需要理解得非常透徹和深入。我們回頭來看看一開始我們說的慢查詢，了解完索引原理之后，大家是不是有什么想法呢？先總結(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)化成索引可以識別的形式
3.盡量選擇區(qū)分度高的列作為索引,區(qū)分度的公式是count(distinct col)/count(*)，表示字段不重復(fù)的比例，比例越大我們掃描的記錄數(shù)越少，唯一鍵的區(qū)分度是1，而一些狀態(tài)、性別字段可能在大數(shù)據(jù)面前區(qū)分度就是0，那可能有人會(huì)問，這個(gè)比例有什么經(jīng)驗(yàn)值嗎？使用場景不同，這個(gè)值也很難確定，一般需要join的字段我們都要求是0.1以上，即平均1條掃描10條記錄
4.索引列不能參與計(jì)算，保持列“干凈”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡單，b+樹中存的都是數(shù)據(jù)表中的字段值，但進(jìn)行檢索時(shí)，需要把所有元素都應(yīng)用函數(shù)才能比較，顯然成本太大。所以語句應(yīng)該寫成create_time = unix_timestamp(’2014-05-29’);
5.盡量的擴(kuò)展索引，不要新建索引。比如表中已經(jīng)有a的索引，現(xiàn)在要加(a,b)的索引，那么只需要修改原來的索引即可
根據(jù)最左匹配原則，最開始的sql語句的索引應(yīng)該是status、operator_id、type、operate_time的聯(lián)合索引；其中status、operator_id、type的順序可以顛倒，所以我才會(huì)說，把這個(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è)就是利用了索引的最左匹配的原則
關(guān)于explain命令相信大家并不陌生，具體用法和字段含義可以參考官網(wǎng) explain-output，這里需要強(qiáng)調(diào)rows是核心指標(biāo)，絕大部分rows小的語句執(zhí)行一定很快（有例外，下面會(huì)講到）。所以優(yōu)化語句基本上都是在優(yōu)化rows。
0.先運(yùn)行看看是否真的很慢，注意設(shè)置SQL_NO_CACHE
1.where條件單表查，鎖定最小返回記錄表。這句話的意思是把查詢語句的where都應(yīng)用到表中返回的記錄數(shù)最小的表開始查起，單表每個(gè)字段分別查詢，看哪個(gè)字段的區(qū)分度最高
2.explain查看執(zhí)行計(jì)劃，是否與1預(yù)期一致（從鎖定記錄較少的表開始查詢）
3.order by limit 形式的sql語句讓排序的表優(yōu)先查
4.了解業(yè)務(wù)方使用場景
5.加索引時(shí)參照建索引的幾大原則
6.觀察結(jié)果，不符合預(yù)期繼續(xù)從0分析
高效使用索引的首要條件是知道什么樣的查詢會(huì)使用到索引，這個(gè)問題和B+Tree中的“最左前綴原理”有關(guān)，下面通過例子說明最左前綴原理。
這里先說一下聯(lián)合索引的概念。在上文中，我們都是假設(shè)索引只引用了單個(gè)的列，實(shí)際上，MySQL中的索引可以以一定順序引用多個(gè)列，這種索引叫做聯(lián)合索引，一般的，一個(gè)聯(lián)合索引是一個(gè)有序元組<a1, a2, …, an>，其中各個(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表為例，下面先查看其上都有哪些索引：
從結(jié)果中可以到titles表的主索引為<emp_no, title, from_date>，還有一個(gè)輔助索引<emp_no>。為了避免多個(gè)索引使事情變復(fù)雜（MySQL的SQL優(yōu)化器在多索引時(shí)行為比較復(fù)雜），這里我們將輔助索引drop掉：
這樣就可以專心分析索引PRIMARY的行為了。
很明顯，當(dāng)按照索引中所有列進(jìn)行精確匹配（這里精確匹配指“=”或“IN”匹配）時(shí)，索引可以被用到。這里有一點(diǎn)需要注意，理論上索引對順序是敏感的，但是由于MySQL的查詢優(yōu)化器會(huì)自動(dòng)調(diào)整where子句的條件順序以使用適合的索引，例如我們將where中的條件順序顛倒：
效果是一樣的。
當(dāng)查詢條件精確匹配索引的左邊連續(xù)一個(gè)或幾個(gè)列時(shí)，如<emp_no>或<emp_no, title>，所以可以被用到，但是只能用到一部分，即條件所組成的最左前綴。上面的查詢從分析結(jié)果看用到了PRIMARY索引，但是key_len為4，說明只用到了索引的第一列前綴。
此時(shí)索引使用情況和情況二相同，因?yàn)閠itle未提供，所以查詢只用到了索引的第一列，而后面的from_date雖然也在索引中，但是由于title不存在而無法和左前綴連接，因此需要對結(jié)果進(jìn)行掃描過濾from_date（這里由于emp_no唯一，所以不存在掃描）。如果想讓from_date也使用索引而不是where過濾，可以增加一個(gè)輔助索引<emp_no, from_date>，此時(shí)上面的查詢會(huì)使用這個(gè)索引。除此之外，還可以使用一種稱之為“隔離列”的優(yōu)化方法，將emp_no與from_date之間的“坑”填上。
首先我們看下title一共有幾種不同的值：
只有7種。在這種成為“坑”的列值比較少的情況下，可以考慮用“IN”來填補(bǔ)這個(gè)“坑”從而形成最左前綴：
這次key_len為59，說明索引被用全了，但是從type和rows看出IN實(shí)際上執(zhí)行了一個(gè)range查詢，這里檢查了7個(gè)key?？聪聝煞N查詢的性能比較：
“填坑”后性能提升了一點(diǎn)。如果經(jīng)過emp_no篩選后余下很多數(shù)據(jù)，則后者性能優(yōu)勢會(huì)更加明顯。當(dāng)然，如果title的值很多，用填坑就不合適了，必須建立輔助索引。
由于不是最左前綴，索引這樣的查詢顯然用不到索引。
此時(shí)可以用到索引，但是如果通配符不是只出現(xiàn)在末尾，則無法使用索引。（原文表述有誤，如果通配符%不出現(xiàn)在開頭，則可以用到索引，但根據(jù)具體情況不同可能只會(huì)用其中一個(gè)前綴）
范圍列可以用到索引（必須是最左前綴），但是范圍列后面的列無法用到索引。同時(shí)，索引最多用于一個(gè)范圍列，因此如果查詢條件中有兩個(gè)范圍列則無法全用到索引。
可以看到索引對第二個(gè)范圍索引無能為力。這里特別要說明MySQL一個(gè)有意思的地方，那就是僅用explain可能無法區(qū)分范圍索引和多值匹配，因?yàn)樵趖ype中這兩者都顯示為range。同時(shí)，用了“between”并不意味著就是范圍查詢，例如下面的查詢：
看起來是用了兩個(gè)范圍查詢，但作用于emp_no上的“BETWEEN”實(shí)際上相當(dāng)于“IN”，也就是說emp_no實(shí)際是多值精確匹配?？梢钥吹竭@個(gè)查詢用到了索引全部三個(gè)列。因此在MySQL中要謹(jǐn)慎地區(qū)分多值匹配和范圍匹配，否則會(huì)對MySQL的行為產(chǎn)生困惑。
很不幸，如果查詢條件中含有函數(shù)或表達(dá)式，則MySQL不會(huì)為這列使用索引（雖然某些在數(shù)學(xué)意義上可以使用）。例如：
雖然這個(gè)查詢和情況五中相同，但是由于使用了函數(shù)left，則無法為title列應(yīng)用索引，而情況五中用LIKE則可以。再如：
顯然這個(gè)查詢等價(jià)于查詢emp_no為10001的函數(shù)，但是由于查詢條件是一個(gè)表達(dá)式，MySQL無法為其使用索引?？磥鞰ySQL還沒有智能到自動(dòng)優(yōu)化常量表達(dá)式的程度，因此在寫查詢語句時(shí)盡量避免表達(dá)式出現(xiàn)在查詢中，而是先手工私下代數(shù)運(yùn)算，轉(zhuǎn)換為無表達(dá)式的查詢語句。
既然索引可以加快查詢速度，那么是不是只要是查詢語句需要，就建上索引？答案是否定的。因?yàn)樗饕m然加快了查詢速度，但索引也是有代價(jià)的：索引文件本身要消耗存儲(chǔ)空間，同時(shí)索引會(huì)加重插入、刪除和修改記錄時(shí)的負(fù)擔(dān)，另外，MySQL在運(yùn)行時(shí)也要消耗資源維護(hù)索引，因此索引并不是越多越好。一般兩種情況下不建議建索引。
第一種情況是表記錄比較少，例如一兩千條甚至只有幾百條記錄的表，沒必要建索引，讓查詢做全表掃描就好了。至于多少條記錄才算多，這個(gè)個(gè)人有個(gè)人的看法，我個(gè)人的經(jīng)驗(yàn)是以2000作為分界線，記錄數(shù)不超過 2000可以考慮不建索引，超過2000條可以酌情考慮索引。
另一種不建議建索引的情況是索引的選擇性較低。所謂索引的選擇性（Selectivity），是指不重復(fù)的索引值（也叫基數(shù)，Cardinality）與表記錄數(shù)（#T）的比值：
Index Selectivity = Cardinality / #T
顯然選擇性的取值范圍為(0, 1]，選擇性越高的索引價(jià)值越大，這是由B+Tree的性質(zhì)決定的。例如，上文用到的employees.titles表，如果title字段經(jīng)常被單獨(dú)查詢，是否需要建索引，我們看一下它的選擇性：
title的選擇性不足0.0001（精確值為0.00001579），所以實(shí)在沒有什么必要為其單獨(dú)建索引。
有一種與索引選擇性有關(guān)的索引優(yōu)化策略叫做前綴索引，就是用列的前綴代替整個(gè)列作為索引key，當(dāng)前綴長度合適時(shí)，可以做到既使得前綴索引的選擇性接近全列索引，同時(shí)因?yàn)樗饕齥ey變短而減少了索引文件的大小和維護(hù)開銷。下面以employees.employees表為例介紹前綴索引的選擇和使用。
從圖12可以看到employees表只有一個(gè)索引<emp_no>，那么如果我們想按名字搜索一個(gè)人，就只能全表掃描了：
如果頻繁按名字搜索員工，這樣顯然效率很低，因此我們可以考慮建索引。有兩種選擇，建<first_name>或<first_name, last_name>，看下兩個(gè)索引的選擇性：
<first_name>顯然選擇性太低，<first_name, last_name>選擇性很好，但是first_name和last_name加起來長度為30，有沒有兼顧長度和選擇性的辦法？可以考慮用first_name和last_name的前幾個(gè)字符建立索引，例如<first_name, left(last_name, 3)>，看看其選擇性：
選擇性還不錯(cuò)，但離0.9313還是有點(diǎn)距離，那么把last_name前綴加到4：
這時(shí)選擇性已經(jīng)很理想了，而這個(gè)索引的長度只有18，比<first_name, last_name>短了接近一半，我們把這個(gè)前綴索引 建上：
此時(shí)再執(zhí)行一遍按名字查詢，比較分析一下與建索引前的結(jié)果：
性能的提升是顯著的，查詢速度提高了120多倍。
前綴索引兼顧索引大小和查詢速度，但是其缺點(diǎn)是不能用于ORDER BY和GROUP BY操作，也不能用于Covering index（即當(dāng)索引本身包含查詢所需全部數(shù)據(jù)時(shí)，不再訪問數(shù)據(jù)文件本身
在使用InnoDB存儲(chǔ)引擎時(shí)，如果沒有特別的需要，請永遠(yuǎn)使用一個(gè)與業(yè)務(wù)無關(guān)的自增字段作為主鍵。
經(jīng)?？吹接刑踊虿┛陀懻撝麈I選擇問題，有人建議使用業(yè)務(wù)無關(guān)的自增主鍵，有人覺得沒有必要，完全可以使用如學(xué)號或身份證號這種唯一字段作為主鍵。不論支持哪種論點(diǎn)，大多數(shù)論據(jù)都是業(yè)務(wù)層面的。如果從數(shù)據(jù)庫索引優(yōu)化角度看，使用InnoDB引擎而不使用自增主鍵絕對是一個(gè)糟糕的主意。
上文討論過InnoDB的索引實(shí)現(xiàn)，InnoDB使用聚集索引，數(shù)據(jù)記錄本身被存于主索引（一顆B+Tree）的葉子節(jié)點(diǎn)上。這就要求同一個(gè)葉子節(jié)點(diǎn)內(nèi)（大小為一個(gè)內(nèi)存頁或磁盤頁）的各條數(shù)據(jù)記錄按主鍵順序存放，因此每當(dāng)有一條新的記錄插入時(shí)，MySQL會(huì)根據(jù)其主鍵將其插入適當(dāng)?shù)墓?jié)點(diǎn)和位置，如果頁面達(dá)到裝載因子（InnoDB默認(rèn)為15/16），則開辟一個(gè)新的頁（節(jié)點(diǎn)）。
如果表使用自增主鍵，那么每次插入新的記錄，記錄就會(huì)順序添加到當(dāng)前索引節(jié)點(diǎn)的后續(xù)位置，當(dāng)一頁寫滿，就會(huì)自動(dòng)開辟一個(gè)新的頁。如下圖所示：
圖13
這樣就會(huì)形成一個(gè)緊湊的索引結(jié)構(gòu)，近似順序填滿。由于每次插入時(shí)也不需要移動(dòng)已有數(shù)據(jù)，因此效率很高，也不會(huì)增加很多開銷在維護(hù)索引上。
如果使用非自增主鍵（如果身份證號或?qū)W號等），由于每次插入主鍵的值近似于隨機(jī)，因此每次新紀(jì)錄都要被插到現(xiàn)有索引頁得中間某個(gè)位置：
圖14
此時(shí)MySQL不得不為了將新記錄插到合適位置而移動(dòng)數(shù)據(jù)，甚至目標(biāo)頁面可能已經(jīng)被回寫到磁盤上而從緩存中清掉，此時(shí)又要從磁盤上讀回來，這增加了很多開銷，同時(shí)頻繁的移動(dòng)、分頁操作造成了大量的碎片，得到了不夠緊湊的索引結(jié)構(gòu)，后續(xù)不得不通過OPTIMIZE TABLE來重建表并優(yōu)化填充頁面。
因此，只要可以，請盡量在InnoDB上采用自增字段做主鍵。
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機(jī)網(wǎng)站二維碼