《MYSQL教程MySQL索引背后的數據結構及算法原理詳解》要點：
本文介紹了MYSQL教程MySQL索引背后的數據結構及算法原理詳解，希望對您有用。如果有疑問，可以聯系我們。

MYSQL入門摘要

MYSQL入門本文以MySQL數據庫為研究對象,討論與數據庫索引相關的一些話題.特別需要說明的是,MySQL支持諸多存儲引擎,而各種存儲引擎對索引的支持也各不相同,因此MySQL數據庫支持多種索引類型,如BTree索引,哈希索引,全文索引等等.為了避免混亂,本文將只關注于BTree索引,因為這是平常使用MySQL時主要打交道的索引,至于哈希索引和全文索引本文暫不討論.

MYSQL入門文章主要內容分為三個部分.

MYSQL入門第一部分主要從數據結構及算法理論層面討論MySQL數據庫索引的數理基礎.

MYSQL入門第二部分結合MySQL數據庫中MyISAM和InnoDB數據存儲引擎中索引的架構實現討論聚集索引、非聚集索引及覆蓋索引等話題.

MYSQL入門第三部分根據上面的理論基礎,討論MySQL中高性能使用索引的策略.

MYSQL入門數據結構及算法基礎

MYSQL入門索引的本質

MYSQL入門MySQL官方對索引的定義為：索引(Index)是幫助MySQL高效獲取數據的數據結構.提取句子主干,就可以得到索引的本質：索引是數據結構.

MYSQL入門我們知道,數據庫查詢是數據庫的最主要功能之一.我們都希望查詢數據的速度能盡可能的快,因此數據庫系統的設計者會從查詢算法的角度進行優化.最基本的查詢算法當然是順序查找(linear search),這種復雜度為O(n)的算法在數據量很大時顯然是糟糕的,好在計算機科學的發展提供了很多更優秀的查找算法,例如二分查找(binary search)、二叉樹查找(binary tree search)等.如果稍微分析一下會發現,每種查找算法都只能應用于特定的數據結構之上,例如二分查找要求被檢索數據有序,而二叉樹查找只能應用于二叉查找樹上,但是數據本身的組織結構不可能完全滿足各種數據結構(例如,理論上不可能同時將兩列都按順序進行組織),所以,在數據之外,數據庫系統還維護著滿足特定查找算法的數據結構,這些數據結構以某種方式引用(指向)數據,這樣就可以在這些數據結構上實現高級查找算法.這種數據結構,就是索引.

MYSQL入門看一個例子：

MYSQL入門

MYSQL入門圖1

MYSQL入門圖1展示了一種可能的索引方式.左邊是數據表,一共有兩列七條記錄,最左邊的是數據記錄的物理地址(注意邏輯上相鄰的記錄在磁盤上也并不是一定物理相鄰的).為了加快Col2的查找,可以維護一個右邊所示的二叉查找樹,每個節點分別包含索引鍵值和一個指向對應數據記錄物理地址的指針,這樣就可以運用二叉查找在O(log2n)的復雜度內獲取到相應數據.

MYSQL入門雖然這是一個貨真價實的索引,但是實際的數據庫系統幾乎沒有使用二叉查找樹或其進化品種紅黑樹(red-black tree)實現的,原因會在下文介紹.

MYSQL入門B-Tree和B+Tree

MYSQL入門目前大部分數據庫系統及文件系統都采用B-Tree或其變種B+Tree作為索引結構,在本文的下一節會結合存儲器原理及計算機存取原理討論為什么B-Tree和B+Tree在被如此廣泛用于索引,這一節先單純從數據結構角度描述它們.

MYSQL入門B-Tree

MYSQL入門為了描述B-Tree,首先定義一條數據記錄為一個二元組[key, data],key為記錄的鍵值,對于不同數據記錄,key是互不相同的；data為數據記錄除key外的數據.那么B-Tree是滿足下列條件的數據結構：

MYSQL入門1. d為大于1的一個正整數,稱為B-Tree的度.

MYSQL入門2. h為一個正整數,稱為B-Tree的高度.

MYSQL入門3. 每個非葉子節點由n-1個key和n個指針組成,其中d<=n<=2d.

MYSQL入門4. 每個葉子節點最少包含一個key和兩個指針,最多包含2d-1個key和2d個指針,葉節點的指針均為null .

MYSQL入門5. 所有葉節點具有相同的深度,等于樹高h.

MYSQL入門6. key和指針互相間隔,節點兩端是指針.

MYSQL入門7. 一個節點中的key從左到右非遞減排列.

MYSQL入門8. 所有節點組成樹結構.

MYSQL入門9. 每個指針要么為null,要么指向另外一個節點.

MYSQL入門10. 如果某個指針在節點node最左邊且不為null,則其指向節點的所有key小于v(key1),其中v(key1)為node的第一個key的值.

MYSQL入門11. 如果某個指針在節點node最右邊且不為null,則其指向節點的所有key大于v(keym),其中v(keym)為node的最后一個key的值.

MYSQL入門12. 如果某個指針在節點node的左右相鄰key分別是keyi和keyi+1且不為null,則其指向節點的所有key小于v(keyi+1)且大于v(keyi).

MYSQL入門圖2是一個d=2的B-Tree示意圖.

MYSQL入門

MYSQL入門圖2

MYSQL入門由于B-Tree的特性,在B-Tree中按key檢索數據的算法非常直觀：首先從根節點進行二分查找,如果找到則返回對應節點的data,否則對相應區間的指針指向的節點遞歸進行查找,直到找到節點或找到null指針,前者查找成功,后者查找失敗.B-Tree上查找算法的偽代碼如下：

MYSQL入門
BTree_Search(node, key)
{
  if(node == null) return null;
 
  foreach(node.key)
  {
    if(node.key[i] == key) return node.data[i];
    if(node.key[i] > key) return BTree_Search(point[i]->node);
  }
 
  return BTree_Search(point[i+1]->node);
}
 
data = BTree_Search(root, my_key);

MYSQL入門關于B-Tree有一系列有趣的性質,例如一個度為d的B-Tree,設其索引N個key,則其樹高h的上限為logd((N+1)/2),檢索一個key,其查找節點個數的漸進復雜度為O(logdN).從這點可以看出,B-Tree是一個非常有效率的索引數據結構.

MYSQL入門另外,由于插入刪除新的數據記錄會破壞B-Tree的性質,因此在插入刪除時,需要對樹進行一個分裂、合并、轉移等操作以保持B-Tree性質,本文不打算完整討論B-Tree這些內容,因為已經有許多資料詳細說明了B-Tree的數學性質及插入刪除算法,有興趣的朋友可以在本文末的參考文獻一欄找到相應的資料進行閱讀.

MYSQL入門B+Tree

MYSQL入門B-Tree有許多變種,其中最常見的是B+Tree,例如MySQL就普遍使用B+Tree實現其索引結構.

MYSQL入門與B-Tree相比,B+Tree有以下不同點：

MYSQL入門1. 每個節點的指針上限為2d而不是2d+1.

MYSQL入門2. 內節點不存儲data,只存儲key；葉子節點不存儲指針.

MYSQL入門圖3是一個簡單的B+Tree示意.

MYSQL入門

MYSQL入門圖3

MYSQL入門由于并不是所有節點都具有相同的域,因此B+Tree中葉節點和內節點一般大小不同.這點與B-Tree不同,雖然B-Tree中不同節點存放的key和指針可能數量不一致,但是每個節點的域和上限是一致的,所以在實現中B-Tree往往對每個節點申請同等大小的空間.

MYSQL入門一般來說,B+Tree比B-Tree更適合實現外存儲索引結構,具體原因與外存儲器原理及計算機存取原理有關,將在下面討論.

MYSQL入門帶有順序訪問指針的B+Tree

MYSQL入門一般在數據庫系統或文件系統中使用的B+Tree結構都在經典B+Tree的基礎上進行了優化,增加了順序訪問指針.

MYSQL入門

MYSQL入門圖4

MYSQL入門如圖4所示,在B+Tree的每個葉子節點增加一個指向相鄰葉子節點的指針,就形成了帶有順序訪問指針的B+Tree.做這個優化的目的是為了提高區間訪問的性能,例如圖4中如果要查詢key為從18到49的所有數據記錄,當找到18后,只需順著節點和指針順序遍歷就可以一次性訪問到所有數據節點,極大提到了區間查詢效率.

MYSQL入門這一節對B-Tree和B+Tree進行了一個簡單的介紹,下一節結合存儲器存取原理介紹為什么目前B+Tree是數據庫系統實現索引的首選數據結構.

MYSQL入門為什么使用B-Tree(B+Tree)

MYSQL入門上文說過,紅黑樹等數據結構也可以用來實現索引,但是文件系統及數據庫系統普遍采用B-/+Tree作為索引結構,這一節將結合計算機組成原理相關知識討論B-/+Tree作為索引的理論基礎.

MYSQL入門一般來說,索引本身也很大,不可能全部存儲在內存中,因此索引往往以索引文件的形式存儲的磁盤上.這樣的話,索引查找過程中就要產生磁盤I/O消耗,相對于內存存取,I/O存取的消耗要高幾個數量級,所以評價一個數據結構作為索引的優劣最重要的指標就是在查找過程中磁盤I/O操作次數的漸進復雜度.換句話說,索引的結構組織要盡量減少查找過程中磁盤I/O的存取次數.下面先介紹內存和磁盤存取原理,然后再結合這些原理分析B-/+Tree作為索引的效率.

MYSQL入門主存存取原理

MYSQL入門目前計算機使用的主存基本都是隨機讀寫存儲器(RAM),現代RAM的結構和存取原理比較復雜,這里本文拋卻具體差別,抽象出一個十分簡單的存取模型來說明RAM的工作原理.

MYSQL入門

MYSQL入門圖5

MYSQL入門從抽象角度看,主存是一系列的存儲單元組成的矩陣,每個存儲單元存儲固定大小的數據.每個存儲單元有唯一的地址,現代主存的編址規則比較復雜,這里將其簡化成一個二維地址：通過一個行地址和一個列地址可以唯一定位到一個存儲單元.圖5展示了一個4 x 4的主存模型.

MYSQL入門主存的存取過程如下：

MYSQL入門當系統需要讀取主存時,則將地址信號放到地址總線上傳給主存,主存讀到地址信號后,解析信號并定位到指定存儲單元,然后將此存儲單元數據放到數據總線上,供其它部件讀取.

MYSQL入門寫主存的過程類似,系統將要寫入單元地址和數據分別放在地址總線和數據總線上,主存讀取兩個總線的內容,做相應的寫操作.

MYSQL入門這里可以看出,主存存取的時間僅與存取次數呈線性關系,因為不存在機械操作,兩次存取的數據的“距離”不會對時間有任何影響,例如,先取A0再取A1和先取A0再取D3的時間消耗是一樣的.

MYSQL入門磁盤存取原理

MYSQL入門上文說過,索引一般以文件形式存儲在磁盤上,索引檢索需要磁盤I/O操作.與主存不同,磁盤I/O存在機械運動耗費,因此磁盤I/O的時間消耗是巨大的.

MYSQL入門圖6是磁盤的整體結構示意圖.

MYSQL入門

MYSQL入門圖6

MYSQL入門一個磁盤由大小相同且同軸的圓形盤片組成,磁盤可以轉動(各個磁盤必須同步轉動).在磁盤的一側有磁頭支架,磁頭支架固定了一組磁頭,每個磁頭負責存取一個磁盤的內容.磁頭不能轉動,但是可以沿磁盤半徑方向運動(實際是斜切向運動),每個磁頭同一時刻也必須是同軸的,即從正上方向下看,所有磁頭任何時候都是重疊的(不過目前已經有多磁頭獨立技術,可不受此限制).

MYSQL入門圖7是磁盤結構的示意圖.

MYSQL入門

MYSQL入門圖7

MYSQL入門盤片被劃分成一系列同心環,圓心是盤片中心,每個同心環叫做一個磁道,所有半徑相同的磁道組成一個柱面.磁道被沿半徑線劃分成一個個小的段,每個段叫做一個扇區,每個扇區是磁盤的最小存儲單元.為了簡單起見,我們下面假設磁盤只有一個盤片和一個磁頭.

MYSQL入門當需要從磁盤讀取數據時,系統會將數據邏輯地址傳給磁盤,磁盤的控制電路按照尋址邏輯將邏輯地址翻譯成物理地址,即確定要讀的數據在哪個磁道,哪個扇區.為了讀取這個扇區的數據,需要將磁頭放到這個扇區上方,為了實現這一點,磁頭需要移動對準相應磁道,這個過程叫做尋道,所耗費時間叫做尋道時間,然后磁盤旋轉將目標扇區旋轉到磁頭下,這個過程耗費的時間叫做旋轉時間.

MYSQL入門局部性原理與磁盤預讀

MYSQL入門由于存儲介質的特性,磁盤本身存取就比主存慢很多,再加上機械運動耗費,磁盤的存取速度往往是主存的幾百分分之一,因此為了提高效率,要盡量減少磁盤I/O.為了達到這個目的,磁盤往往不是嚴格按需讀取,而是每次都會預讀,即使只需要一個字節,磁盤也會從這個位置開始,順序向后讀取一定長度的數據放入內存.這樣做的理論依據是計算機科學中著名的局部性原理：

MYSQL入門當一個數據被用到時,其附近的數據也通常會馬上被使用.

MYSQL入門程序運行期間所需要的數據通常比較集中.

MYSQL入門由于磁盤順序讀取的效率很高(不需要尋道時間,只需很少的旋轉時間),因此對于具有局部性的程序來說,預讀可以提高I/O效率.

MYSQL入門預讀的長度一般為頁(page)的整倍數.頁是計算機管理存儲器的邏輯塊,硬件及操作系統往往將主存和磁盤存儲區分割為連續的大小相等的塊,每個存儲塊稱為一頁(在許多操作系統中,頁得大小通常為4k),主存和磁盤以頁為單位交換數據.當程序要讀取的數據不在主存中時,會觸發一個缺頁異常,此時系統會向磁盤發出讀盤信號,磁盤會找到數據的起始位置并向后連續讀取一頁或幾頁載入內存中,然后異常返回,程序繼續運行.

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

MYSQL入門到這里終于可以分析B-/+Tree索引的性能了.

MYSQL入門上文說過一般使用磁盤I/O次數評價索引結構的優劣.先從B-Tree分析,根據B-Tree的定義,可知檢索一次最多需要訪問h個節點.數據庫系統的設計者巧妙利用了磁盤預讀原理,將一個節點的大小設為等于一個頁,這樣每個節點只需要一次I/O就可以完全載入.為了達到這個目的,在實際實現B-Tree還需要使用如下技巧：

MYSQL入門每次新建節點時,直接申請一個頁的空間,這樣就保證一個節點物理上也存儲在一個頁里,加之計算機存儲分配都是按頁對齊的,就實現了一個node只需一次I/O.

MYSQL入門B-Tree中一次檢索最多需要h-1次I/O(根節點常駐內存),漸進復雜度為O(h)=O(logdN).一般實際應用中,出度d是非常大的數字,通常超過100,因此h非常小(通常不超過3).

MYSQL入門綜上所述,用B-Tree作為索引結構效率是非常高的.

MYSQL入門而紅黑樹這種結構,h明顯要深的多.由于邏輯上很近的節點(父子)物理上可能很遠,無法利用局部性,所以紅黑樹的I/O漸進復雜度也為O(h),效率明顯比B-Tree差很多.

MYSQL入門上文還說過,B+Tree更適合外存索引,原因和內節點出度d有關.從上面分析可以看到,d越大索引的性能越好,而出度的上限取決于節點內key和data的大小：

MYSQL入門dmax = floor(pagesize / (keysize + datasize + pointsize)) (pagesize C dmax >= pointsize)

MYSQL入門或

MYSQL入門dmax = floor(pagesize / (keysize + datasize + pointsize)) C 1 (pagesize C dmax < pointsize)

MYSQL入門floor表示向下取整.由于B+Tree內節點去掉了data域,因此可以擁有更大的出度,擁有更好的性能.

MYSQL入門這一章從理論角度討論了與索引相關的數據結構與算法問題,下一章將討論B+Tree是如何具體實現為MySQL中索引,同時將結合MyISAM和InnDB存儲引擎介紹非聚集索引和聚集索引兩種不同的索引實現形式.

MYSQL入門MySQL索引實現

MYSQL入門在MySQL中,索引屬于存儲引擎級別的概念,不同存儲引擎對索引的實現方式是不同的,本文主要討論MyISAM和InnoDB兩個存儲引擎的索引實現方式.

MYSQL入門MyISAM索引實現

MYSQL入門MyISAM引擎使用B+Tree作為索引結構,葉節點的data域存放的是數據記錄的地址.下圖是MyISAM索引的原理圖：

MYSQL入門

MYSQL入門圖8

MYSQL入門這里設表一共有三列,假設我們以Col1為主鍵,則圖8是一個MyISAM表的主索引(Primary key)示意.可以看出MyISAM的索引文件僅僅保存數據記錄的地址.在MyISAM中,主索引和輔助索引(Secondary key)在結構上沒有任何區別,只是主索引要求key是唯一的,而輔助索引的key可以重復.如果我們在Col2上建立一個輔助索引,則此索引的結構如下圖所示：

MYSQL入門

MYSQL入門圖9

MYSQL入門同樣也是一顆B+Tree,data域保存數據記錄的地址.因此,MyISAM中索引檢索的算法為首先按照B+Tree搜索算法搜索索引,如果指定的Key存在,則取出其data域的值,然后以data域的值為地址,讀取相應數據記錄.

MYSQL入門MyISAM的索引方式也叫做“非聚集”的,之所以這么稱呼是為了與InnoDB的聚集索引區分.

MYSQL入門InnoDB索引實現

MYSQL入門雖然InnoDB也使用B+Tree作為索引結構,但具體實現方式卻與MyISAM截然不同.

MYSQL入門第一個重大區別是InnoDB的數據文件本身就是索引文件.從上文知道,MyISAM索引文件和數據文件是分離的,索引文件僅保存數據記錄的地址.而在InnoDB中,表數據文件本身就是按B+Tree組織的一個索引結構,這棵樹的葉節點data域保存了完整的數據記錄.這個索引的key是數據表的主鍵,因此InnoDB表數據文件本身就是主索引.

MYSQL入門

MYSQL入門圖10

MYSQL入門圖10是InnoDB主索引(同時也是數據文件)的示意圖,可以看到葉節點包含了完整的數據記錄.這種索引叫做聚集索引.因為InnoDB的數據文件本身要按主鍵聚集,所以InnoDB要求表必須有主鍵(MyISAM可以沒有),如果沒有顯式指定,則MySQL系統會自動選擇一個可以唯一標識數據記錄的列作為主鍵,如果不存在這種列,則MySQL自動為InnoDB表生成一個隱含字段作為主鍵,這個字段長度為6個字節,類型為長整形.

MYSQL入門第二個與MyISAM索引的不同是InnoDB的輔助索引data域存儲相應記錄主鍵的值而不是地址.換句話說,InnoDB的所有輔助索引都引用主鍵作為data域.例如,圖11為定義在Col3上的一個輔助索引：

MYSQL入門

MYSQL入門圖11

MYSQL入門這里以英文字符的ASCII碼作為比較準則.聚集索引這種實現方式使得按主鍵的搜索十分高效,但是輔助索引搜索需要檢索兩遍索引：首先檢索輔助索引獲得主鍵,然后用主鍵到主索引中檢索獲得記錄.

MYSQL入門了解不同存儲引擎的索引實現方式對于正確使用和優化索引都非常有幫助,例如知道了InnoDB的索引實現后,就很容易明白為什么不建議使用過長的字段作為主鍵,因為所有輔助索引都引用主索引,過長的主索引會令輔助索引變得過大.再例如,用非單調的字段作為主鍵在InnoDB中不是個好主意,因為InnoDB數據文件本身是一顆B+Tree,非單調的主鍵會造成在插入新記錄時數據文件為了維持B+Tree的特性而頻繁的分裂調整,十分低效,而使用自增字段作為主鍵則是一個很好的選擇.

MYSQL入門下一章將具體討論這些與索引有關的優化策略.

MYSQL入門索引使用策略及優化

MYSQL入門MySQL的優化主要分為結構優化(Scheme optimization)和查詢優化(Query optimization).本章討論的高性能索引策略主要屬于結構優化范疇.本章的內容完全基于上文的理論基礎,實際上一旦理解了索引背后的機制,那么選擇高性能的策略就變成了純粹的推理,并且可以理解這些策略背后的邏輯.

MYSQL入門示例數據庫

MYSQL入門為了討論索引策略,需要一個數據量不算小的數據庫作為示例.本文選用MySQL官方文檔中提供的示例數據庫之一：employees.這個數據庫關系復雜度適中,且數據量較大.下圖是這個數據庫的E-R關系圖(引用自MySQL官方手冊)：

MYSQL入門圖12

MYSQL入門MySQL官方文檔中關于此數據庫的頁面為http://dev.mysql.com/doc/employee/en/employee.html.里面詳細介紹了此數據庫,并提供了下載地址和導入方法,如果有興趣導入此數據庫到自己的MySQL可以參考文中內容.

MYSQL入門最左前綴原理與相關優化

MYSQL入門高效使用索引的首要條件是知道什么樣的查詢會使用到索引,這個問題和B+Tree中的“最左前綴原理”有關,下面通過例子說明最左前綴原理.

MYSQL入門這里先說一下聯合索引的概念.在上文中,我們都是假設索引只引用了單個的列,實際上,MySQL中的索引可以以一定順序引用多個列,這種索引叫做聯合索引,一般的,一個聯合索引是一個有序元組<a1, a2, …, an>,其中各個元素均為數據表的一列,實際上要嚴格定義索引需要用到關系代數,但是這里我不想討論太多關系代數的話題,因為那樣會顯得很枯燥,所以這里就不再做嚴格定義.另外,單列索引可以看成聯合索引元素數為1的特例.

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

MYSQL入門
SHOW INDEX FROM employees.titles;
+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Null | Index_type |
+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+
| titles |     0 | PRIMARY |      1 | emp_no   | A     |    NULL |   | BTREE   |
| titles |     0 | PRIMARY |      2 | title    | A     |    NULL |   | BTREE   |
| titles |     0 | PRIMARY |      3 | from_date  | A     |   443308 |   | BTREE   |
| titles |     1 | emp_no  |      1 | emp_no   | A     |   443308 |   | BTREE   |
+--------+------------+----------+--------------+-------------+-----------+-------------+------+------------+

MYSQL入門從結果中可以到titles表的主索引為<emp_no, title, from_date>,還有一個輔助索引<emp_no>.為了避免多個索引使事情變復雜(MySQL的SQL優化器在多索引時行為比較復雜),這里我們將輔助索引drop掉：

MYSQL入門
ALTER TABLE employees.titles DROP INDEX emp_no;

MYSQL入門這樣就可以專心分析索引PRIMARY的行為了.

MYSQL入門情況一：全列匹配.

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

MYSQL入門很明顯,當按照索引中所有列進行精確匹配(這里精確匹配指“=”或“IN”匹配)時,索引可以被用到.這里有一點需要注意,理論上索引對順序是敏感的,但是由于MySQL的查詢優化器會自動調整where子句的條件順序以使用適合的索引,例如我們將where中的條件順序顛倒：

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

MYSQL入門效果是一樣的.

MYSQL入門情況二：最左前綴匹配.

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

MYSQL入門當查詢條件精確匹配索引的左邊連續一個或幾個列時,如<emp_no>或<emp_no, title>,所以可以被用到,但是只能用到一部分,即條件所組成的最左前綴.上面的查詢從分析結果看用到了PRIMARY索引,但是key_len為4,說明只用到了索引的第一列前綴.

MYSQL入門情況三：查詢條件用到了索引中列的精確匹配,但是中間某個條件未提供.

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

MYSQL入門此時索引使用情況和情況二相同,因為title未提供,所以查詢只用到了索引的第一列,而后面的from_date雖然也在索引中,但是由于title不存在而無法和左前綴連接,因此需要對結果進行掃描過濾from_date(這里由于emp_no唯一,所以不存在掃描).如果想讓from_date也使用索引而不是where過濾,可以增加一個輔助索引<emp_no, from_date>,此時上面的查詢會使用這個索引.除此之外,還可以使用一種稱之為“隔離列”的優化方法,將emp_no與from_date之間的“坑”填上.

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

MYSQL入門
SELECT DISTINCT(title) FROM employees.titles;
+--------------------+
| title       |
+--------------------+
| Senior Engineer  |
| Staff       |
| Engineer      |
| Senior Staff    |
| Assistant Engineer |
| Technique Leader  |
| Manager      |
+--------------------+

MYSQL入門只有7種.在這種成為“坑”的列值比較少的情況下,可以考慮用“IN”來填補這個“坑”從而形成最左前綴：

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

MYSQL入門這次key_len為59,說明索引被用全了,但是從type和rows看出IN實際上執行了一個range查詢,這里檢查了7個key.看下兩種查詢的性能比較：

MYSQL入門
SHOW PROFILES;
+----------+------------+-------------------------------------------------------------------------------+
| Query_ID | Duration  | Query                                     |
+----------+------------+-------------------------------------------------------------------------------+
|    10 | 0.00058000 | SELECT * FROM employees.titles WHERE emp_no='10001' AND from_date='1986-06-26'|
|    11 | 0.00052500 | SELECT * FROM employees.titles WHERE emp_no='10001' AND title IN ...     |
+----------+------------+-------------------------------------------------------------------------------+

MYSQL入門“填坑”后性能提升了一點.如果經過emp_no篩選后余下很多數據,則后者性能優勢會更加明顯.當然,如果title的值很多,用填坑就不合適了,必須建立輔助索引.

MYSQL入門情況四：查詢條件沒有指定索引第一列.

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

MYSQL入門由于不是最左前綴,索引這樣的查詢顯然用不到索引.

MYSQL入門情況五：匹配某列的前綴字符串.

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

MYSQL入門此時可以用到索引,但是如果通配符不是只出現在末尾,則無法使用索引.(原文表述有誤,如果通配符%不出現在開頭,則可以用到索引,但根據具體情況不同可能只會用其中一個前綴)

MYSQL入門情況六：范圍查詢.

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

MYSQL入門范圍列可以用到索引(必須是最左前綴),但是范圍列后面的列無法用到索引.同時,索引最多用于一個范圍列,因此如果查詢條件中有兩個范圍列則無法全用到索引.

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

MYSQL入門可以看到索引對第二個范圍索引無能為力.這里特別要說明MySQL一個有意思的地方,那就是僅用explain可能無法區分范圍索引和多值匹配,因為在type中這兩者都顯示為range.同時,用了“between”并不意味著就是范圍查詢,例如下面的查詢：

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

MYSQL入門看起來是用了兩個范圍查詢,但作用于emp_no上的“BETWEEN”實際上相當于“IN”,也就是說emp_no實際是多值精確匹配.可以看到這個查詢用到了索引全部三個列.因此在MySQL中要謹慎地區分多值匹配和范圍匹配,否則會對MySQL的行為產生困惑.

MYSQL入門情況七：查詢條件中含有函數或表達式.

MYSQL入門很不幸,如果查詢條件中含有函數或表達式,則MySQL不會為這列使用索引(雖然某些在數學意義上可以使用).例如：

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

MYSQL入門雖然這個查詢和情況五中功能相同,但是由于使用了函數left,則無法為title列應用索引,而情況五中用LIKE則可以.再如：

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

MYSQL入門顯然這個查詢等價于查詢emp_no為10001的函數,但是由于查詢條件是一個表達式,MySQL無法為其使用索引.看來MySQL還沒有智能到自動優化常量表達式的程度,因此在寫查詢語句時盡量避免表達式出現在查詢中,而是先手工私下代數運算,轉換為無表達式的查詢語句.

MYSQL入門索引選擇性與前綴索引

MYSQL入門既然索引可以加快查詢速度,那么是不是只要是查詢語句需要,就建上索引?答案是否定的.因為索引雖然加快了查詢速度,但索引也是有代價的：索引文件本身要消耗存儲空間,同時索引會加重插入、刪除和修改記錄時的負擔,另外,MySQL在運行時也要消耗資源維護索引,因此索引并不是越多越好.一般兩種情況下不建議建索引.

MYSQL入門第一種情況是表記錄比較少,例如一兩千條甚至只有幾百條記錄的表,沒必要建索引,讓查詢做全表掃描就好了.至于多少條記錄才算多,這個個人有個人的看法,我個人的經驗是以2000作為分界線,記錄數不超過 2000可以考慮不建索引,超過2000條可以酌情考慮索引.

MYSQL入門另一種不建議建索引的情況是索引的選擇性較低.所謂索引的選擇性(Selectivity),是指不重復的索引值(也叫基數,Cardinality)與表記錄數(#T)的比值：

MYSQL入門Index Selectivity = Cardinality / #T

MYSQL入門顯然選擇性的取值范圍為(0, 1],選擇性越高的索引價值越大,這是由B+Tree的性質決定的.例如,上文用到的employees.titles表,如果title字段經常被單獨查詢,是否需要建索引,我們看一下它的選擇性：

MYSQL入門
SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;
+-------------+
| Selectivity |
+-------------+
|   0.0000 |
+-------------+

MYSQL入門title的選擇性不足0.0001(精確值為0.00001579),所以實在沒有什么必要為其單獨建索引.

MYSQL入門有一種與索引選擇性有關的索引優化策略叫做前綴索引,就是用列的前綴代替整個列作為索引key,當前綴長度合適時,可以做到既使得前綴索引的選擇性接近全列索引,同時因為索引key變短而減少了索引文件的大小和維護開銷.下面以employees.employees表為例介紹前綴索引的選擇和使用.

MYSQL入門從圖12可以看到employees表只有一個索引<emp_no>,那么如果我們想按名字搜索一個人,就只能全表掃描了：

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

MYSQL入門如果頻繁按名字搜索員工,這樣顯然效率很低,因此我們可以考慮建索引.有兩種選擇,建<first_name>或<first_name, last_name>,看下兩個索引的選擇性：

MYSQL入門
SELECT count(DISTINCT(first_name))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|   0.0042 |
+-------------+
SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|   0.9313 |
+-------------+

MYSQL入門<first_name>顯然選擇性太低,<first_name, last_name>選擇性很好,但是first_name和last_name加起來長度為30,有沒有兼顧長度和選擇性的辦法?可以考慮用first_name和last_name的前幾個字符建立索引,例如<first_name, left(last_name, 3)>,看看其選擇性：

MYSQL入門
SELECT count(DISTINCT(concat(first_name, left(last_name, 3))))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|   0.7879 |
+-------------+

MYSQL入門選擇性還不錯,但離0.9313還是有點距離,那么把last_name前綴加到4：

MYSQL入門
SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|   0.9007 |
+-------------+

MYSQL入門這時選擇性已經很理想了,而這個索引的長度只有18,比<first_name, last_name>短了接近一半,我們把這個前綴索引 建上：

MYSQL入門
ALTER TABLE employees.employees
ADD INDEX `first_name_last_name4` (first_name, last_name(4));

MYSQL入門此時再執行一遍按名字查詢,比較分析一下與建索引前的結果：

MYSQL入門
SHOW PROFILES;
+----------+------------+---------------------------------------------------------------------------------+
| Query_ID | Duration  | Query                                      |
+----------+------------+---------------------------------------------------------------------------------+
|    87 | 0.11941700 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
|    90 | 0.00092400 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
+----------+------------+---------------------------------------------------------------------------------+

MYSQL入門性能的提升是顯著的,查詢速度提高了120多倍.

MYSQL入門前綴索引兼顧索引大小和查詢速度,但是其缺點是不能用于ORDER BY和GROUP BY操作,也不能用于Covering index(即當索引本身包含查詢所需全部數據時,不再訪問數據文件本身).

MYSQL入門InnoDB的主鍵選擇與插入優化

MYSQL入門在使用InnoDB存儲引擎時,如果沒有特別的需要,請永遠使用一個與業務無關的自增字段作為主鍵.

MYSQL入門經常看到有帖子或博客討論主鍵選擇問題,有人建議使用業務無關的自增主鍵,有人覺得沒有必要,完全可以使用如學號或身份證號這種唯一字段作為主鍵.不論支持哪種論點,大多數論據都是業務層面的.如果從數據庫索引優化角度看,使用InnoDB引擎而不使用自增主鍵絕對是一個糟糕的主意.

MYSQL入門上文討論過InnoDB的索引實現,InnoDB使用聚集索引,數據記錄本身被存于主索引(一顆B+Tree)的葉子節點上.這就要求同一個葉子節點內(大小為一個內存頁或磁盤頁)的各條數據記錄按主鍵順序存放,因此每當有一條新的記錄插入時,MySQL會根據其主鍵將其插入適當的節點和位置,如果頁面達到裝載因子(InnoDB默認為15/16),則開辟一個新的頁(節點).

MYSQL入門如果表使用自增主鍵,那么每次插入新的記錄,記錄就會順序添加到當前索引節點的后續位置,當一頁寫滿,就會自動開辟一個新的頁.如下圖所示：

MYSQL入門

MYSQL入門圖13

MYSQL入門這樣就會形成一個緊湊的索引結構,近似順序填滿.由于每次插入時也不需要移動已有數據,因此效率很高,也不會增加很多開銷在維護索引上.

MYSQL入門如果使用非自增主鍵(如果身份證號或學號等),由于每次插入主鍵的值近似于隨機,因此每次新紀錄都要被插到現有索引頁得中間某個位置：

MYSQL入門

MYSQL入門圖14

MYSQL入門此時MySQL不得不為了將新記錄插到合適位置而移動數據,甚至目標頁面可能已經被回寫到磁盤上而從緩存中清掉,此時又要從磁盤上讀回來,這增加了很多開銷,同時頻繁的移動、分頁操作造成了大量的碎片,得到了不夠緊湊的索引結構,后續不得不通過OPTIMIZE TABLE來重建表并優化填充頁面.

MYSQL入門因此,只要可以,請盡量在InnoDB上采用自增字段做主鍵.

MYSQL入門后記

MYSQL入門這篇文章斷斷續續寫了半個月,主要內容就是上面這些了.不可否認,這篇文章在一定程度上有紙上談兵之嫌,因為我本人對MySQL的使用屬于菜鳥級別,更沒有太多數據庫調優的經驗,在這里大談數據庫索引調優有點大言不慚.就當是我個人的一篇學習筆記了.

MYSQL入門其實數據庫索引調優是一項技術活,不能僅僅靠理論,因為實際情況千變萬化,而且MySQL本身存在很復雜的機制,如查詢優化策略和各種引擎的實現差異等都會使情況變得更加復雜.但同時這些理論是索引調優的基礎,只有在明白理論的基礎上,才能對調優策略進行合理推斷并了解其背后的機制,然后結合實踐中不斷的實驗和摸索,從而真正達到高效使用MySQL索引的目的.

MYSQL入門另外,MySQL索引及其優化涵蓋范圍非常廣,本文只是涉及到其中一部分.如與排序(ORDER BY)相關的索引優化及覆蓋索引(Covering index)的話題本文并未涉及,同時除B-Tree索引外MySQL還根據不同引擎支持的哈希索引、全文索引等等本文也并未涉及.如果有機會,希望再對本文未涉及的部分進行補充吧.

MYSQL入門參考文獻

MYSQL入門[1] Baron Scbwartz等 著,王小東等 譯；高性能MySQL(High Performance MySQL)；電子工業出版社,2010

MYSQL入門[2] Michael Kofler 著,楊曉云等 譯；MySQL5權威指南(The Definitive Guide to MySQL5)；人民郵電出版社,2006

MYSQL入門[3] 姜承堯 著；MySQL技術內幕-InnoDB存儲引擎；機械工業出版社,2011

MYSQL入門[4] D Comer, Ubiquitous B-tree; ACM Computing Surveys (CSUR), 1979

MYSQL入門[5] Codd, E. F. (1970). “A relational model of data for large shared data banks”. Communications of the ACM, , Vol. 13, No. 6, pp. 377-387

MYSQL入門[6] MySQL5.1參考手冊 C http://dev.mysql.com/doc/refman/5.1/zh/index.html

歡迎參與《MYSQL教程MySQL索引背后的數據結構及算法原理詳解》討論，分享您的想法，維易PHP學院為您提供專業教程。

轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/13624.html