轉自：CSDN

MySQL是目前絕大多數互聯網公司使用的關系型數據庫，它性能出色、資源豐富、成本低廉，是快速搭建互聯網應用的首選關系型數據庫。但是，俗話說，“好馬配好鞍”，僅僅會使用MySQL是不夠的，對MySQL在不同場景下使用性能的最小化使用代價，是一個重要的課題。一般，在互聯網公司的大部分業務中，讀寫的比例大約是10:1，也就是說，查詢的場景往往比更新或寫入的場景多得多，那么問題來了，如何優化查詢呢？

前幾天公司同事發了一條SQL語句，問這個SQL有沒有走索引？對于一般的開發人員而言，優化SQL的方式是，在SQL中查詢條件里的字段上，添加索引。但是如何添加索引？索引的順序如何？索引是如何匹配命中的？一般的開發人員可能只知道大概，并沒有很深入的了解。

MySQL索引原理

索引的目的與原理

在日常生活中，經常有這樣的場景：有一個沒見到過的英文單詞，我們查字典找到這個單詞的意思；我們要出去旅行，查詢到具體地點的航班號；諸如此類。在這樣的場景中，我們都是通過不斷的縮小范圍來篩選出最終預期的結果，同時把隨機的事件變成順序事件：查詢字典，比如查單詞mysql，我們是按照一個字母一個字母的順序來查詢的；查詢航班號，我們也是通過地點機場航空公司一個一個來篩選縮小范圍的。我們總是通過同一種查找方式來鎖定數據。

數據庫也是一樣，但顯然要比現實生活中的場景要復雜得多，因為不僅會有等值查詢(=)，還有范圍查詢(>，

磁盤I/O與預讀

剛剛提到了磁盤訪問，那么這里先簡單介紹一下磁盤的I/O與預讀。磁盤讀取數據，靠的是機械運動，每次讀取數據花費的時間可以分成：尋道時間、旋轉延遲、傳輸時間三個部分。尋道時間指的是磁臂移動到指定磁盤所需要的時間，主流的磁盤一般在5ms以下；旋轉延遲指的是我們經常說的磁盤轉速，比如一個磁盤7200轉，表示的就是每分鐘磁盤能轉7200次，轉換成秒也就是120次每秒，旋轉延遲就是1/120/2=4.17ms；傳輸時間指的是從磁盤讀取出數據或將數據寫入磁盤的時間，一般都在零點幾毫秒，相對于前兩個，可以忽略不計。那么訪問一次磁盤的時間，即一次磁盤I/O的時間約等于5+4.17=9.17ms，9ms左右，聽起來還是不錯的哈，但要知道一臺500-MIPS的機器每秒可以執行5億條指令，因為指令依靠的是電的性質，換句話說，執行一次I/O的時間可以執行40萬條指令，數據庫動輒百萬級甚至千萬級的數據，每次9ms的時間，顯然是一個災難。

上圖是計算機硬件延遲時間的對比圖。

考慮到磁盤I/O是非常高昂代價的操作，計算機系統做了一些優化，當一次I/O時，不光會把當前磁盤地址的數據讀取到內存中，而且會把相鄰的數據也讀取到內存緩沖區中，因為局部預讀性原理告訴我們，當計算機訪問一個地址的數據的時候，與其相鄰的數據也會很快訪問到。每一次I/O讀取的數據我們稱之為一頁(Page)。具體一頁的數據有多大，這個跟操作系統有關，一般為4K或8K，也就是我們讀取一頁數據的時候，實際上才發生了一次I/O，這個理論對于索引的數據結構設計很有幫助。

索引的數據結構

上面講了索引的基本原理，數據庫的復雜性，以及操作系統的一些內容，目的就是讓大家了解到，任何一種數據結構都不是憑空產生的，一定有它的背景和使用場景。那么，我們需要這些數據結構能夠做什么呢？其實很簡單，就是：每次查找數據的時候，把磁盤I/O次數限制在一個很小的數量級，最好是一個常量數量級。那么我們就想到，如果一個高度可控的多路搜索樹，是否能夠滿足需求呢？在這樣的背景下，B+樹應運而生。

詳解B+樹

如上圖，是一棵B+樹。B+樹的定義，童鞋可以自行百度，我們只說一些重點。圖中淺藍色的塊，我們稱之為一個磁盤，可以看到，每個磁盤塊包含幾個數據項(深藍色)和指針(黃色)。如：磁盤塊1包含數據17和數據35，包含指針P1,P2,P3，P1指向數據小于17的磁盤塊，P2指向數據在17到35之間的數據所在磁盤塊，P3指向數據大于35的數據所在的磁盤塊。真實數據存在于葉子節點，即3，5，9，10，13，15，28，29，36，60，75，79，90，99 。非葉子節點不存儲真實數據，只存儲指引搜索方向的數據項，如17、35并不真實存在于數據表中。

B+樹的查找過程

還是使用上面的B+樹。假設，我們要查找數據項29，那么我們首先會把磁盤塊1由磁盤加載到內存中，此時進行了一次I/O，在內存中用二分查找確定29在17和35之間，鎖定磁盤塊1的P2指針，內存計算時間由于非常短(對比于I/O)可以忽略不計，通過磁盤塊1的P2指針的磁盤地址指向磁盤塊3，由磁盤加載到內存，此時進行了第二次I/O，29在26和30之間，鎖定磁盤塊3的P2指針，通過指針加載磁盤塊8到內存，此時進行了第三次I/O，同時內存中計算二分查找找到29，查詢結束。這一過程，一共進行了3次I/O。在真實使用場景中，三層的B+樹可以表示上百萬的數據，如果上百萬的數據查詢只需要三次I/O，性能提高將會是巨大的。B+樹就是一種索引數據結構，如果沒有這樣的索引，每個數據項發生一次I/O，那么成本將會大大提升。

B+樹的性質

在上面的查找例子中，我們可以分析出一些B+樹的性質：

I/O的次數取決于B+樹的高度H，假設當前數據表的數據為N，每個磁盤塊的數據項的數量是M，則有：H=log(M+1)N，當數據量N一定的情況下，M越大，H越小；而M=磁盤塊大小/數據項大小，磁盤塊大小也就是一個數據頁的大小，是固定的，如果數據項占的空間越小，數據項的數量越多，樹的高度也就越低。這也就是為什么每個數據項，即索引字段要盡量的小，比如int占4個字節，要比bigint的8個字節小一半。這也是為什么B+樹要求把真實數據放在葉子節點內而不是內層節點內，一旦放到內層節點內，磁盤塊的數據項會大幅度的下降，導致樹層級的增高。當數據項為1時，B+樹會退化成線性表。

B+樹的數據項是復合性數據結構，比如(name，age，gender)的時候，B+樹是按照從左到右的順序來建立搜索樹的，比如當(小張，22，女)這樣的數據來檢索的時候，B+樹會優先比較name來確定下一步的搜索方向，如果name相同再依次比較age和gender，最后得到檢索的數據。但是，當(22，女)這樣沒有name的數據來的時候，B+樹就不知道下一步該查哪個節點，因為建立搜索樹的時候，name就是第一個比較因子，必須根據name來搜索才知道下一步去哪里查詢。比如，當(小張，男)這樣的數據來檢索時，B+樹就可以根據name來指定搜索方向，但下一字段age缺失，所以只能把名字是“小張”的所有數據都找到，然后再匹配性別是“男”的數據了。這個是非常重要的一條性質，即索引的最左匹配特性。

索引的類型

在MySQL中，索引分為兩大類：聚簇索引和非聚簇索引。聚簇索引是按照數據存放的物理位置為順序的，而非聚簇索引則不同；聚簇索引能夠提高多行檢索的速度，而非聚簇索引則對單行的檢索速度很快。

在這兩大類的索引類型下，還可以將索引分成四個小類：

普通索引：最基本的索引，沒有任何限制，是我們大多數情況下使用到的索引。

唯一索引：與普通索引類型，不同的是唯一索引的列值必須唯一，但允許為空值。

全文索引：全文索引(FULLTEXT)僅可以適用于MyISAM引擎的數據表；作用于CHAR、VARCHAR、TEXT數據類型的列。

組合索引：將幾個列作為一條索引進行檢索，使用最左匹配原則。

建立索引的原則

當我們了解完索引原理之后，對慢查詢的優化應該有一些想法，這里我們先總結一下建立索引的一些原則：

最左前綴匹配原則。這是非常重要、非常重要、非常重要(重要的事情說三遍)的原則，MySQL會一直向右匹配直到遇到范圍查詢(>, 3 AND d = 4，如果建立 (a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引，則都可以用到，a,b,d的順序可以任意調整。

等于(=)和in 可以亂序。比如，a = 1 AND b = 2 AND c = 3 建立(a,b,c)索引可以任意順序，MySQL的查詢優化器會幫你優化成索引可以識別的模式。

盡量選擇區分度高的列作為索引，區分度的公式是 COUNT(DISTINCT col) / COUNT(*)。表示字段不重復的比率，比率越大我們掃描的記錄數就越少，唯一鍵的區分度是1，而一些狀態、性別字段可能在大數據面前區分度是0。可能有人會問，這個比率有什么經驗么？使用場景不同，這個值也很難確定，一般需要JOIN的字段我們要求在0.1以上，即平均1條掃描10條記錄。

索引列不能參與計算，盡量保持列“干凈”。比如，FROM_UNIXTIME(create_time) = '2016-06-06' 就不能使用索引，原因很簡單，B+樹中存儲的都是數據表中的字段值，但是進行檢索時，需要把所有元素都應用函數才能比較，顯然這樣的代價太大。所以語句要寫成 ：create_time = UNIX_TIMESTAMP('2016-06-06')。

盡可能的擴展索引，不要新建立索引。比如表中已經有了a的索引，現在要加(a,b)的索引，那么只需要修改原來的索引即可。

單個多列組合索引和多個單列索引的檢索查詢效果不同，因為在執行SQL時，MySQL只能使用一個索引，會從多個單列索引中選擇一個限制最為嚴格的索引。

慢查詢的優化步驟

查看運行效果，是否真的很慢，主要設置SQL_NO_CACHE。

WHERE條件單表查詢，鎖定最小返回記錄表。這句話的意思是，把查詢語句的WHERE都應用到表中返回的記錄數最小的表開始查起，單表每個字段分別查詢，看哪個字段的區分度最高

EXPLAIN查看執行計劃，是否與1預期一致(從鎖定記錄較少的表開始查詢)

ORDER BY LIMIT 形式的SQL語句，讓排序的表優先查

多去了解業務的使用場景

加索引時，要參照建立索引的幾大原則

觀察結果，不符合預期，則重新從1開始分析

索引的優化方法

索引不會包含有NULL值的列：只要列中包含有NULL值，都將不會被包含在索引中，組合索引中只要有一列有NULL值，那么這一列對于此條組合索引就是無效的。所以我們在數據庫設計時，不要讓索引字段的默認值為NULL。

使用短索引：假設，如果有一個數據類型為CHAR(255)的列，在前10個或20個字符內，絕大部分數據的值是唯一的，那么就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省I/O操作。

索引列排序：MySQL查詢只使用一個索引，因此如果WHERE子句中已經使用了索引的話，那么ORDER BY中的列是不會使用索引的。因此數據庫默認排序可以符合要求的情況下，不要使用排序操作；盡量不要包含多個列的排序，如果需要，最好給這些列也創建組合索引。

LIKE語句操作：一般情況下，不建議使用LIKE操作；如果非使用不可，如何使用也是一個研究的課題。LIKE "%aaaaa%"不會使用索引，但是LIKE "aaa%"卻可以使用索引。

不要在索引列上進行運算：在建立索引的原則中，提到了索引列不能進行運算，這里就不再贅述了。

END

文字 / echo

文中貼紙 / 網絡(侵刪)

配圖 / 網絡(侵刪)

排版?/ PHP學習手記

PHP學習手記?

科技 | 前沿 | ?技術 | 學習

微信號：phpStudy

總結

以上是生活随笔為你收集整理的mysql 聚簇索引和非聚簇索引_MySQL学习之——索引的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。