mysql

參考 參考

系統(tǒng)從磁盤內(nèi)讀取數(shù)據(jù)都是一個(gè)磁盤塊（block）讀取的，innodb引擎讀取是按照頁(yè)（page）讀取的茴丰，show variables like '%innodb_page_size%'; 可以查看柏肪，默認(rèn)為16k姐刁，linux 默認(rèn)頁(yè)大小為4K
而一個(gè)磁盤塊肯定沒這么大，innodb讀取的時(shí)候以page頁(yè)為單位烦味，會(huì)讀取連續(xù)若干磁盤塊的數(shù)據(jù)達(dá)到page的大小龙填。而在查詢數(shù)據(jù)時(shí)如果一個(gè)頁(yè)中的每條數(shù)據(jù)都能有助于定位數(shù)據(jù)記錄的位置，
這就減少了磁盤I/O次數(shù)拐叉，提高查詢效率岩遗。而B+Tree結(jié)構(gòu)的數(shù)據(jù)就可以讓系統(tǒng)高效的找到數(shù)據(jù)所在的磁盤塊。
(磁盤是按 block 分的凤瘦，一般為 512 Byte宿礁。磁盤 IO 一次讀取若干個(gè) block，我們稱為一頁(yè)蔬芥，具體大小和操作系統(tǒng)有關(guān)梆靖，一般為 4 k控汉，8 k或 16 k)
?
為什么不用二叉樹呢？
平衡二叉樹是通過旋轉(zhuǎn)來保持平衡的返吻，而旋轉(zhuǎn)是對(duì)整棵樹的操作姑子，若部分加載到內(nèi)存中則無法完成旋轉(zhuǎn)操作。其次平衡二叉樹的高度相對(duì)較大為 log n（底數(shù)為2）测僵，這樣邏輯上很近的節(jié)點(diǎn)實(shí)際可能非常遠(yuǎn)街佑，無法很好的利用磁盤預(yù)讀（局部性原理）

b樹（b-tree）

B-Tree結(jié)構(gòu)的數(shù)據(jù)可以讓系統(tǒng)高效的找到數(shù)據(jù)所在的磁盤塊。為了描述B-Tree捍靠，首先定義一條記錄為一個(gè)二元組[key, data] 沐旨，key為記錄的鍵值榨婆，對(duì)應(yīng)表中的主鍵值，data為一行記錄中除主鍵外的數(shù)據(jù)谊迄。對(duì)于不同的記錄，key值互不相同

一棵m階的B-Tree有如下特性：

• 每個(gè)節(jié)點(diǎn)最多有m個(gè)孩子烟央。

• 除了根節(jié)點(diǎn)和葉子節(jié)點(diǎn)外鳞上，其它每個(gè)節(jié)點(diǎn)至少有Ceil(m/2)個(gè)孩子篙议。

  比如一個(gè)3階的樹至少有2個(gè)孩子

• 若根節(jié)點(diǎn)不是葉子節(jié)點(diǎn)，則至少有2個(gè)孩子

• 所有葉子節(jié)點(diǎn)都在同一層怠硼，且不包含其它關(guān)鍵字信息

• 每個(gè)非葉子節(jié)點(diǎn)包含n個(gè)關(guān)鍵字信息和n+1個(gè)指針組成（P0,P1,…Pn, k1,…kn）

• 關(guān)鍵字的個(gè)數(shù)n滿足：ceil(m/2)-1 <= n <= m-1

 比如一個(gè)3階的樹至少有2個(gè)孩子鬼贱，n等于2</pre>

• ki(i=1,…n)為關(guān)鍵字香璃，且關(guān)鍵字升序排序。

說的是k的保存順序姻乓，從跟節(jié)點(diǎn)開始查找眯牧，從左邊找，每個(gè)節(jié)點(diǎn)的關(guān)鍵字從左到由剪个，由小到大排列參考下圖</pre>

• Pi(i=1,…n)為指向子樹根節(jié)點(diǎn)的指針扣囊。P(i-1)指向的子樹的所有節(jié)點(diǎn)關(guān)鍵字均小于ki，但都大于k(i-1)

這個(gè)說的可能繞點(diǎn)骂澄，我分析下惕虑，p0對(duì)應(yīng)k1,P1對(duì)應(yīng)K2 ,參考每個(gè)非終端節(jié)點(diǎn)包含n個(gè)關(guān)鍵字信息（P0,P1,…Pn, k1,…kn）這個(gè)
?那個(gè)以關(guān)鍵字為8和12來說枷遂，有3個(gè)指針棋嘲，P1,P2,P3，那么對(duì)應(yīng)的關(guān)鍵字2個(gè)分別為K2->8,K3->9,那么當(dāng)i=2的時(shí)候根據(jù)公式套用
p(i-1)指向的是3和5痪伦，k2為8雹锣，那么P(i-1)指向的子樹的所有節(jié)點(diǎn)關(guān)鍵字均小于ki結(jié)論成立蕊爵，當(dāng)i=3的時(shí)候根據(jù)公式繼續(xù)套用
p(i-1)指向的是9和10，k3為12醋旦，那么P(i-1)指向的子樹的所有節(jié)點(diǎn)關(guān)鍵字均小于ki結(jié)論成立会放，k(i-1)為8咧最，
P(i-1)指向的子樹的所有節(jié)點(diǎn)關(guān)鍵字都大于k(i-1)成立。
其實(shí)這段話說明了一件事就是非葉子節(jié)點(diǎn)里的關(guān)鍵字指向的節(jié)點(diǎn)左邊的關(guān)鍵字始終小于右邊的滥搭，中間的大于左邊的捣鲸，小于右邊的摄狱，舉個(gè)栗子：
?
每個(gè)節(jié)點(diǎn)占用一個(gè)盤塊的磁盤空間无午，一個(gè)節(jié)點(diǎn)上有兩個(gè)升序排序的關(guān)鍵字和三個(gè)指向子樹根節(jié)點(diǎn)的指針宪迟，指針存儲(chǔ)的是子節(jié)點(diǎn)所在磁盤塊的地址交惯。
兩個(gè)關(guān)鍵詞劃分成的三個(gè)范圍域?qū)?yīng)三個(gè)指針指向的子樹的數(shù)據(jù)的范圍域席爽。以根節(jié)點(diǎn)為例，關(guān)鍵字為17和35玖像，P1指針指向的子樹的數(shù)據(jù)范圍為小于17捐寥，
P2指針指向的子樹的數(shù)據(jù)范圍為17~35祖驱，P3指針指向的子樹的數(shù)據(jù)范圍為大于35捺僻。

imgae

查找關(guān)鍵字29的過程

1. 根據(jù)根節(jié)點(diǎn)找到磁盤塊1匕坯，讀入內(nèi)存∑拊酰【磁盤I/O操作第1次】

2. 比較關(guān)鍵字29在區(qū)間（17,35）泞歉，找到磁盤塊1的指針P2腰耙。

3. 根據(jù)P2指針找到磁盤塊3，讀入內(nèi)存晰赞∫从悖【磁盤I/O操作第2次】

4. 比較關(guān)鍵字29在區(qū)間（26,30），找到磁盤塊3的指針P2芍瑞。

5. 根據(jù)P2指針找到磁盤塊8褐墅，讀入內(nèi)存妥凳。【磁盤I/O操作第3次】

6. 在磁盤塊8中的關(guān)鍵字列表中找到關(guān)鍵字29

   分析上面過程屑那，發(fā)現(xiàn)需要3次磁盤I/O操作齐莲，和3次內(nèi)存查找操作磷箕。由于內(nèi)存中的關(guān)鍵字是一個(gè)有序表結(jié)構(gòu)岳枷，可以利用二分法查找提高效率呜叫。而3次磁盤I/O操作是影響整個(gè)B-Tree查找效率的決定因素朱庆。 B-Tree相對(duì)于AVLTree（平衡二叉樹）縮減了節(jié)點(diǎn)個(gè)數(shù)，使每次磁盤I/O取到內(nèi)存的數(shù)據(jù)都發(fā)揮了作用傲诵，從而提高了查詢效率拴竹。

B+Tree

B+Tree是在B-Tree基礎(chǔ)上的一種優(yōu)化剧罩，使其更適合實(shí)現(xiàn)外存儲(chǔ)索引結(jié)構(gòu)，InnoDB存儲(chǔ)引擎就是用B+Tree實(shí)現(xiàn)其索引結(jié)構(gòu)幕与。
?
從上一節(jié)中的B-Tree結(jié)構(gòu)圖中可以看到每個(gè)節(jié)點(diǎn)中不僅包含數(shù)據(jù)的key值纽门，還有data值赏陵。而每一個(gè)頁(yè)的存儲(chǔ)空間是有限的，如果data數(shù)據(jù)較大時(shí)將會(huì)導(dǎo)致每個(gè)節(jié)點(diǎn)（即一個(gè)頁(yè)）能存儲(chǔ)的key的數(shù)量很小缕溉，
當(dāng)存儲(chǔ)的數(shù)據(jù)量很大時(shí)同樣會(huì)導(dǎo)致B-Tree的深度較大证鸥，增大查詢時(shí)的磁盤I/O次數(shù)勤晚，進(jìn)而影響查詢效率赐写。在B+Tree中，所有數(shù)據(jù)記錄節(jié)點(diǎn)都是按照鍵值大小順序存放在同一層的葉子節(jié)點(diǎn)上揉忘，而非葉子節(jié)點(diǎn)上
只存儲(chǔ)key值信息泣矛，這樣可以大大加大每個(gè)節(jié)點(diǎn)存儲(chǔ)的key值數(shù)量禾蚕，（這也可以解釋為什么mysql innodb 引擎3階的可以存儲(chǔ)很多數(shù)據(jù)）降低B+Tree的高度换淆。

B+Tree相對(duì)于B-Tree有幾點(diǎn)不同

1. 非葉子節(jié)點(diǎn)只存儲(chǔ)鍵值信息哗总。

2. 所有葉子節(jié)點(diǎn)之間都有一個(gè)鏈指針(每個(gè)葉子節(jié)點(diǎn)組成一個(gè)鏈表)产舞。

3. 數(shù)據(jù)記錄都存放在葉子節(jié)點(diǎn)中易猫。

4. 如果查找的key就在根節(jié)點(diǎn)，那么B樹只需要O(1)就可以找到哈蝇，最大也不過是O(N),而B+TREE則至少需要O(N)才可以找到炮赦，因?yàn)閐ata域存儲(chǔ)在葉子節(jié)點(diǎn)吠勘，一個(gè)3階的樹至少也需要3次才能找到 B+樹內(nèi)節(jié)點(diǎn)不存儲(chǔ)數(shù)據(jù)，所有 data 存儲(chǔ)在葉節(jié)點(diǎn)導(dǎo)致查詢時(shí)間復(fù)雜度固定為 log n植锉。而B-樹查詢時(shí)間復(fù)雜度不固定俊庇，與 key 在樹中的位置有關(guān)鸡挠，最好為O(1)

5. B+TREE支持范圍查找拣展，是因?yàn)槿~子節(jié)點(diǎn)之間形成一個(gè)鏈表瞎惫，而B-TREE不支持译株，每個(gè)節(jié)點(diǎn)的KEY和data域保存在一起歉糜，沒法進(jìn)行范圍查找。 根據(jù)空間局部性原理：如果一個(gè)存儲(chǔ)器的某個(gè)位置被訪問伞辛，那么將它附近的位置也會(huì)被訪問蚤氏，B+樹范圍查找的時(shí)候可以很好的利用局部性原理踊兜。相鄰的葉子節(jié)點(diǎn)利用磁盤預(yù)讀原理提前將這些數(shù)據(jù)讀入內(nèi)存，減少了磁盤 IO 的次數(shù)

6. B+TREE更適合外部存儲(chǔ)毁葱。由于內(nèi)節(jié)點(diǎn)無 data 域贰剥，每個(gè)節(jié)點(diǎn)能索引的范圍更大更精蚌成，由于B-tree節(jié)點(diǎn)內(nèi)部每個(gè)key都帶著data域担忧，而B+樹節(jié)點(diǎn)只存儲(chǔ)key的副本，真實(shí)的key和data域都在葉子節(jié)點(diǎn)存儲(chǔ)乖菱。 前面說過磁盤是分 block 的窒所，一次磁盤IO會(huì)讀取若干個(gè)block帆锋，具體和操作系統(tǒng)有關(guān)锯厢，那么由于磁盤IO數(shù)據(jù)大小是固定的实辑，在一次IO中，單個(gè)元素越小摄乒，量就越大馍佑。這就意味著B+樹單次磁盤IO的信息量大于B-樹梨水，從這點(diǎn)來看B+樹相對(duì)B-樹磁盤IO次數(shù)少疫诽。 ?

B+Tree的非葉子節(jié)點(diǎn)只存儲(chǔ)鍵值信息，假設(shè)每個(gè)磁盤塊能存儲(chǔ)4個(gè)鍵值及指針信息社证，則變成B+Tree后其結(jié)構(gòu)

image

通常在B+Tree上有兩個(gè)頭指針追葡，一個(gè)指向根節(jié)點(diǎn)奕短，另一個(gè)指向關(guān)鍵字最小的葉子節(jié)點(diǎn)，而且所有葉子節(jié)點(diǎn)（即數(shù)據(jù)節(jié)點(diǎn)）之間是一種鏈?zhǔn)江h(huán)結(jié)構(gòu)低矮。因此可以對(duì)B+Tree進(jìn)行兩種查找運(yùn)算：
一種是對(duì)于主鍵的范圍查找和分頁(yè)查找，另一種是從根節(jié)點(diǎn)開始，進(jìn)行隨機(jī)查找

可能上面例子中只有22條數(shù)據(jù)記錄，看不出B+Tree的優(yōu)點(diǎn)填硕，下面做一個(gè)推算：

InnoDB存儲(chǔ)引擎中頁(yè)的大小為16KB鹿鳖，一般表的主鍵類型為INT（占用4個(gè)字節(jié)）或BIGINT（占用8個(gè)字節(jié)）翅帜，指針類型也一般為4或8個(gè)字節(jié)藕甩，也就是說一個(gè)頁(yè)（B+Tree中的一個(gè)節(jié)點(diǎn)）中大概
存儲(chǔ)16KB/(8B+8B)=1K個(gè)鍵值（因?yàn)槭枪乐迪晾常瑸榉奖阌?jì)算概作，這里的K取值為〖10〗^3）讯榕。也就是說一個(gè)深度為3的B+Tree索引可以維護(hù)10^3 * 10^3 * 10^3 = 10億 條記錄

mysql的InnoDB存儲(chǔ)引擎在設(shè)計(jì)時(shí)是將根節(jié)點(diǎn)常駐內(nèi)存的，也就是說查找某一鍵值的行記錄時(shí)最多只需要1~3次磁盤I/O操作痕檬。

數(shù)據(jù)庫(kù)中的B+Tree索引可以分為聚集索引（clustered index）和輔助索引（secondary index）送浊。上面的B+Tree示例圖在數(shù)據(jù)庫(kù)中的實(shí)現(xiàn)即為聚集索引袭景，聚集索引的B+Tree中的葉子節(jié)點(diǎn)
存放的是整張表的行記錄數(shù)據(jù)耸棒。輔助索引與聚集索引的區(qū)別在于輔助索引的葉子節(jié)點(diǎn)并不包含行記錄的全部數(shù)據(jù)，而是存儲(chǔ)相應(yīng)行數(shù)據(jù)的聚集索引鍵单山，即主鍵饥侵。當(dāng)通過輔助索引來查詢數(shù)據(jù)時(shí)躏升，InnoDB
存儲(chǔ)引擎會(huì)遍歷輔助索引找到主鍵狼忱，然后再通過主鍵在聚集索引中找到完整的行記錄數(shù)據(jù)钻弄。

為什么使用B-/B+ Tree

一般來說窘俺，索引本身也很大瘤泪，不可能全部存儲(chǔ)在內(nèi)存中，因此索引往往以索引文件的形式存儲(chǔ)的磁盤上赦邻。這樣的話惶洲，索引查找過程中就要產(chǎn)生磁盤I/O消耗恬吕，相對(duì)于內(nèi)存存取，I/O存取的消耗要高幾個(gè)
數(shù)量級(jí)怀愧，所以評(píng)價(jià)一個(gè)數(shù)據(jù)結(jié)構(gòu)作為索引的優(yōu)劣最重要的指標(biāo)就是在查找過程中磁盤I/O操作次數(shù)的漸進(jìn)復(fù)雜度芯义。換句話說扛拨，索引的結(jié)構(gòu)組織要盡量減少查找過程中磁盤I/O的存取次數(shù)绑警。

紅黑樹等數(shù)據(jù)結(jié)構(gòu)也可以用來實(shí)現(xiàn)索引计盒，但是文件系統(tǒng)及數(shù)據(jù)庫(kù)系統(tǒng)普遍采用B-/+Tree作為索引結(jié)構(gòu)芽丹。MySQL 是基于磁盤的數(shù)據(jù)庫(kù)系統(tǒng),索引往往以索引文件的形式存儲(chǔ)的磁盤上,索引查找過程中
就要產(chǎn)生磁盤I/O消耗,相對(duì)于內(nèi)存存取拔第，I/O存取的消耗要高幾個(gè)數(shù)量級(jí),索引的結(jié)構(gòu)組織要盡量減少查找過程中磁盤I/O的存取次數(shù)蚊俺。為什么使用B-/+Tree泳猬，還跟磁盤存取原理有關(guān)得封。

主存存取原理

局部性原理與磁盤預(yù)讀

由于磁盤的存取速度與內(nèi)存之間鴻溝,為了提高效率,要盡量減少磁盤I/O.磁盤往往不是嚴(yán)格按需讀取呛每，而是每次都會(huì)預(yù)讀,磁盤讀取完需要的數(shù)據(jù),會(huì)順序向后讀一定長(zhǎng)度的數(shù)據(jù)放入內(nèi)存晨横。而這樣做的
理論依據(jù)是計(jì)算機(jī)科學(xué)中著名的局部性原理：

1. 當(dāng)一個(gè)數(shù)據(jù)被用到時(shí)，其附近的數(shù)據(jù)也通常會(huì)馬上被使用
2. 程序運(yùn)行期間所需要的數(shù)據(jù)通常比較集中  

由于磁盤順序讀取的效率很高(不需要尋道時(shí)間啥供，只需很少的旋轉(zhuǎn)時(shí)間)伙狐，因此對(duì)于具有局部性的程序來說贷屎，預(yù)讀可以提高I/O效率.預(yù)讀的長(zhǎng)度一般為頁(yè)(page)的整倍數(shù)唉侄。
MySQL(默認(rèn)使用InnoDB引擎),將記錄按照頁(yè)的方式進(jìn)行管理,每頁(yè)大小默認(rèn)為16K(這個(gè)值可以修改).linux 默認(rèn)頁(yè)大小為4K属划。

B-/+Tree索引的性能分析

每次新建節(jié)點(diǎn)時(shí)候生，直接申請(qǐng)一個(gè)頁(yè)的空間唯鸭，這樣就保證一個(gè)節(jié)點(diǎn)物理上也存儲(chǔ)在一個(gè)頁(yè)里须蜗，加之計(jì)算機(jī)存儲(chǔ)分配都是按頁(yè)對(duì)齊的，就實(shí)現(xiàn)了一個(gè)結(jié)點(diǎn)只需一次I/O目溉。
假設(shè) B-Tree 的高度為 h,B-Tree中一次檢索最多需要h-1次I/O（根節(jié)點(diǎn)常駐內(nèi)存）唠粥，漸進(jìn)復(fù)雜度為O(h)=O(logdN)O(h)=O(logdN)。一般實(shí)際應(yīng)用中停做，出度d是非常大的數(shù)字晤愧，通常超過100，
因此h非常序入纭（通常不超過3）官份。
而紅黑樹這種結(jié)構(gòu)烙丛，h明顯要深的多舅巷。由于邏輯上很近的節(jié)點(diǎn)（父子）物理上可能很遠(yuǎn)，無法利用局部性河咽，所以紅黑樹的I/O漸進(jìn)復(fù)雜度也為O(h)钠右，效率明顯比B-Tree差很多。

為什么使用 B+樹

1. B+樹更適合外部存儲(chǔ),由于內(nèi)節(jié)點(diǎn)無 data 域,一個(gè)結(jié)點(diǎn)可以存儲(chǔ)更多的內(nèi)結(jié)點(diǎn),每個(gè)節(jié)點(diǎn)能索引的范圍更大更精確,也意味著 B+樹單次磁盤IO的信息量大于B-樹,I/O效率更高忘蟹。

2. Mysql是一種關(guān)系型數(shù)據(jù)庫(kù)飒房，區(qū)間訪問是常見的一種情況搁凸，B+樹葉節(jié)點(diǎn)增加的鏈指針,加強(qiáng)了區(qū)間訪問性，可使用在范圍區(qū)間查詢等狠毯，而B-樹每個(gè)節(jié)點(diǎn) key 和 data 在一起护糖，則無法區(qū)間查找。

MyISAM

1. MyISAM引擎使用B+Tree作為索引結(jié)構(gòu)嚼松，葉節(jié)點(diǎn)的data域存放的是數(shù)據(jù)記錄的地址.

2. MyISAM的索引文件僅僅保存數(shù)據(jù)記錄的地址嫡良。在MyISAM中，主索引和輔助索引（Secondary key）在結(jié)構(gòu)上沒有任何區(qū)別献酗，只是主索引要求key是唯一的寝受，而輔助索引的key可以重復(fù).

   MyISAM中索引檢索的算法為首先按照B+Tree搜索算法搜索索引，如果指定的Key存在罕偎，則取出其data域的值很澄，然后以data域的值為地址，讀取相應(yīng)數(shù)據(jù)記錄锨亏。 MyISAM的索引方式也叫做“非聚集”的痴怨，之所以這么稱呼是為了與InnoDB的聚集索引區(qū)分。

InnoDB

1. InnoDB的數(shù)據(jù)文件本身就是索引文件器予，MyISAM索引文件和數(shù)據(jù)文件是分離的浪藻，索引文件僅保存數(shù)據(jù)記錄的地址。而在InnoDB中乾翔，表數(shù)據(jù)文件本身就是按B+Tree組織的一個(gè)索引結(jié)構(gòu)爱葵， 這棵樹的葉節(jié)點(diǎn)data域保存了完整的數(shù)據(jù)記錄。這個(gè)索引的key是數(shù)據(jù)表的主鍵反浓，因此InnoDB表數(shù)據(jù)文件本身就是主索引萌丈。這種索引叫做聚集索引，因?yàn)镮nnoDB的數(shù)據(jù)文件本身要按主鍵聚集雷则， 所以InnoDB要求表必須有主鍵（MyISAM可以沒有）辆雾，如果沒有顯式指定，則MySQL系統(tǒng)會(huì)自動(dòng)選擇一個(gè)可以唯一標(biāo)識(shí)數(shù)據(jù)記錄的列作為主鍵月劈，如果不存在這種列度迂，則MySQL自動(dòng)為InnoDB表 生成一個(gè)隱含字段作為主鍵，這個(gè)字段長(zhǎng)度為6個(gè)字節(jié)猜揪，類型為長(zhǎng)整形惭墓。

2. InnoDB的輔助索引data域存儲(chǔ)相應(yīng)記錄主鍵的值而不是地址。也就是說而姐，InnoDB的所有輔助索引都引用主鍵作為data域腊凶。 輔助索引搜索需要檢索兩遍索引：首先檢索輔助索引獲得主鍵，然后用主鍵到主索引中檢索獲得記錄。 這也是為什么不建議使用過長(zhǎng)的字段作為主鍵钧萍，因?yàn)樗休o助索引都引用主索引褐缠，過長(zhǎng)的主索引會(huì)令輔助索引變得過大。還有為什么主鍵不能重復(fù)划煮，也是因?yàn)楣?jié)點(diǎn)分裂調(diào)整效率低下送丰。

索引使用策略及優(yōu)化

MySQL的優(yōu)化主要分為結(jié)構(gòu)優(yōu)化（Scheme optimization）和查詢優(yōu)化（Query optimization）缔俄。

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

  1. 索引最多用于一個(gè)范圍列弛秋，因此如果查詢條件中有兩個(gè)范圍列則無法全用到索引。

  2. 函數(shù)俐载，參與計(jì)算等蟹略，以及隱式轉(zhuǎn)換都用不到索引，因?yàn)樵诓檎业倪^程當(dāng)中需要查找一次KEY就得計(jì)算一次遏佣，所以優(yōu)化器認(rèn)為不合理挖炬，直接走全表掃描。

  3. like "%aaa" 這個(gè)用不到索引状婶，因?yàn)椴恢繩EY的前綴是什么意敛，沒法在節(jié)點(diǎn)里面查找，like 'aaa%'這個(gè)可以用到索引膛虫。

  4. 聯(lián)合索引比如a,b,c的索引 a,ab,abc都可以用到草姻。參考數(shù)據(jù)結(jié)構(gòu)，會(huì)根據(jù)聯(lián)合索引的a去查找到主鍵稍刀，然后查找相應(yīng)的數(shù)據(jù)撩独，所以a為前綴的都可以用到，索引類型為req否則就沒法找到账月，但explain分析工具分析即使順序不對(duì)综膀，比如c,b,a也是可以用到索引的，只不過索引的類型是index局齿，index類型的索引會(huì)掃描整個(gè)索引樹剧劝，而req可以直接根據(jù)索引樹的有序性直接查找到數(shù)據(jù)。

  5. 前綴索引抓歼，如果有一個(gè)表的字段的前幾個(gè)值都不會(huì)重復(fù)或者重復(fù)的幾率小讥此，這個(gè)時(shí)候建議建立前綴索引，這樣節(jié)點(diǎn)存儲(chǔ)的KEY就小锭部，查找起來會(huì)更快暂论。公式參考高性能MYSQL這本書。 前綴索引兼顧索引大小和查詢速度拌禾，但是其缺點(diǎn)是不能用于ORDER BY和GROUP BY操作取胎，也不能用于Covering index（即當(dāng)索引本身包含查詢所需全部數(shù)據(jù)時(shí)，不再訪問數(shù)據(jù)文件本身）

  6. 覆蓋索引，對(duì)于二級(jí)索引來說闻蛀，如果索引樹上的列剛好滿足查詢的列匪傍，就可以走覆蓋索引，不用回表再查一次真正的數(shù)據(jù)觉痛。

  7. order by 參考mysql order by 排序

• 索引選擇性與前綴索引

  索引可以加快查找的速度役衡，但會(huì)增加索引文件的體積，數(shù)據(jù)插入的時(shí)候可能導(dǎo)致節(jié)點(diǎn)分裂薪棒，索引重排等手蝎，導(dǎo)致刪除，插入速度減慢俐芯，另外棵介，MySQL在運(yùn)行時(shí)也要消耗資源維護(hù)索引。 數(shù)據(jù)少的表不建議建立索引吧史，還有就是根據(jù)索引的選擇性（Selectivity）邮辽，是指不重復(fù)的索引值（也叫基數(shù)，Cardinality）與表記錄數(shù)（#T）的比值贸营。Index Selectivity = Cardinality / #T 顯然選擇性的取值范圍為[0, 1]吨述，選擇性越高的索引價(jià)值越大，這是由B+Tree的性質(zhì)決定的,比如：SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM title;

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

1. 盡量用innodb引擎所帶的自增的建作為主鍵钞脂，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è)肃拜。 這樣就會(huì)形成一個(gè)緊湊的索引結(jié)構(gòu)痴腌，近似順序填滿。由于每次插入時(shí)也不需要移動(dòng)已有數(shù)據(jù)燃领，因此效率很高士聪，也不會(huì)增加很多開銷在維護(hù)索引上。

2. 如果使用非自增主鍵（如果身份證號(hào)或?qū)W號(hào)等）猛蔽，由于每次插入主鍵的值近似于隨機(jī)剥悟，因此每次新紀(jì)錄都要被插到現(xiàn)有索引頁(yè)得中間某個(gè)位置灵寺， 此時(shí)MySQL不得不為了將新記錄插到合適位置而移動(dòng)數(shù)據(jù)，甚至目標(biāo)頁(yè)面可能已經(jīng)被回寫到磁盤上而從緩存中清掉区岗，此時(shí)又要從磁盤上讀回來略板，這增加了很多開銷，同時(shí)頻繁的移動(dòng)慈缔、 分頁(yè)操作造成了大量的碎片叮称，得到了不夠緊湊的索引結(jié)構(gòu)，后續(xù)不得不通過OPTIMIZE TABLE來重建表并優(yōu)化填充頁(yè)面藐鹤。因此瓤檐，只要可以，請(qǐng)盡量在InnoDB上采用自增字段做主鍵教藻。