最近據傳因中美關係緊張，公司買不到 oracle 的伺服器。又因為新專案是基於 sef2.0 微服務開發的，租用阿里雲的伺服器，阿里雲的伺服器是 mysql 為主。學習一下 mysql 的相關知識。
最近據傳因中美關係緊張，公司買不到 oracle 的伺服器。又因為新專案是基於 sef2.0 微服務開發的，租用阿里雲的伺服器，阿里雲的伺服器是 mysql 為主。學習一下 mysql 的相關知識。
MySQL 是目前絕大多數互聯網公司所使用的的關聯型資料庫。對於我們公司的社保醫保業務，查詢是至關重要的。查詢的場景往往比更新或者寫入多得多。所以如何優化查詢是非常重要的。
下面看一條常見的查詢語句：
SELECT COUNT(*)
FROM SI3C.PER_REG a
WHERE a.ryid=’33580202’
AND a.zcsj BETWEEN ‘2018-01-01’ and ‘2019-01-01’
一般優化的方法就是在查詢的 where 的字段上添加索引（或者是主鍵，但是如何添加索引，索引的順序怎麼弄？一般人並不太了解（包括我的領導
MySQL 索引原理
下面我們想像一個場景，當我們想查詢一個單詞（LOVE）的中文意思的時候是如何操作的？（當然是在實體的英文詞典裡，APP 上是如何做到的也是個有趣的問題。如果從頭開始查詢，a,abandon,… 只有到 LOVE 的時候才能查到，那將耗費大量的時間，所有就有了目錄，通過先查詢 L 再查詢 O 再查 V 再查 E 後定位到 LOVE，通過這樣的方式來鎖定這個資料。資料庫也是一樣的，其中的 =，>,< BETWEEN,IN,LIKE,AND,OR and so on, 都是用來模擬現實生活中的場景的。那么怎麼模擬就成為了決定性問題。我們還是用查詞典來舉例，有 100 個單詞，love 在第 66 個，那就可以將資料分成段 1-10,11-20・・・・那就可以直接找第六段就可以了，省去了百分之 90 的 invalid data。100m 的資料怎麼辦呢？資料結構的二叉樹中有一個 BST（Binary Search Tree）（PS：很像 BTS 有木有！）有很快的查詢性能，但是我們計算一種資料結構的複雜的時是基於每次相同的操作成本的。真實的計算機情況是，資料保存在磁碟上，每次操作是將磁碟上的部分資料放到記憶體中處理計算，磁碟的訪問成本大概是記憶體訪問成本的 100 thousand times，所以簡單的 BST 是不行的
磁碟 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 樹，上面的 17，35，8，12 等非葉子節點僅僅是索引）
如上圖，是一棵 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 + 樹的性質：
1，I/O 的次數取決於 B + 樹的高度 H，假設當前資料表的資料為 N，每個磁碟塊的資料項的數量是 M，則有：H=log (M+1) N，當資料量 N 一定的情況下，M 越大，H 越小；而 M = 磁碟塊大小 / 資料項大小，磁碟塊大小也就是一個資料頁的大小，是固定的，如果資料項佔的空間越小，資料項的數量越多，樹的高度也就越低。這也就是為什麼每個資料項，即索引字段要盡量的小，比如 int 佔 4 個字節，要比 bigint 的 8 個字節小一半。這也是為什麼 B + 樹要求把真實資料放在葉子節點內而不是內層節點內，一旦放到內層節點內，磁碟塊的資料項會大幅度的下降，導致樹層級的增高。當資料項為 1 時，B + 樹會退化成線性表。
2，B + 樹的資料項是複合性資料結構，比如（name，age，gender）這樣的資料，B + 樹是按照從左到右的順序來建立搜索樹的，比如當（小張，22，女）這樣的資料來檢索的時候，B + 樹會優先比較 name 來確定下一步的搜索方向，如果 name 相同再依次比較 age 和 gender，最後得到檢索的資料。但是，當（22，女）這樣沒有 name 的資料來的時候，B + 樹就不知道下一步該查哪個節點，因為建立搜索樹的時候，name 就是第一個比較因子，必須根據 name 來搜索才知道下一步去哪裡查詢。比如，當（小張，男）這樣的資料來檢索時，B + 樹就可以根據 name 來指定搜索方向，但下一字段 age 缺失，所以只能把名字是 “小張” 的所有資料都找到，然後再匹配性別是 “男” 的資料了。這個是非常重要的一條性質，即索引的最左匹配特性。
索引的類型
在 MySQL 中，索引分為兩大類：聚簇索引和非聚簇索引。聚簇索引是按照資料存放的物理位置為順序的，而非聚簇索引則不同；聚簇索引能夠提高多行檢索的速度，而非聚簇索引則對單行的檢索速度很快。
在這兩大類的索引類型下，還可以將索引分成四個小類：
1，普通索引：最基本的索引，沒有任何限制，是我們大多數情況下使用到的索引。
2，唯一索引：與普通索引類型，不同的是唯一索引的列值必須唯一，但允許為空值。
3，全文索引：全文索引（FULLTEXT）僅可以適用於 MyISAM 引擎的資料表；作用於 CHAR、VARCHAR、TEXT 資料類型的列。
4，組合索引：將幾個列作為一條索引進行檢索，使用最左匹配原則。
建立索引的原則
我們回頭來看一開始提到的慢查詢，當我們了解完索引原理之後，對慢查詢的優化應該有一些想法，這裡我們先總結一下建立索引的一些原則：
1，最左前綴匹配原則。這是非常重要、非常重要、非常重要（重要的事情說三遍）的原則，MySQL 會一直向右匹配直到遇到範圍查詢（>,<,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 的順序可以任意調整。
2，等於（=）和 in 可以亂序。比如，a = 1 AND b = 2 AND c = 3 建立（a,b,c）索引可以任意順序，MySQL 的查詢優化器會幫你優化成索引可以識別的模式。
3，盡量選擇區分度高的列作為索引，區分度的公式是 COUNT (DISTINCT col) / COUNT (*)。表示字段不重複的比率，比率越大我們掃描的記錄數就越少，唯一鍵的區分度是 1，而一些狀態、性別字段可能在大資料面前區分度是 0。可能有人會問，這個比率有什麼經驗麼？使用場景不同，這個值也很難確定，一般需要 JOIN 的字段我們要求在 0.1 以上，即平均 1 條掃描 10 條記錄。
4，索引列不能參與計算，盡量保持列 “乾淨”。比如，FROM_UNIXTIME (create_time) = ‘2016-06-06’ 就不能使用索引，原因很簡單，B + 樹中存儲的都是資料表中的字段值，但是進行檢索時，需要把所有元素都應用函數才能比較，顯然這樣的代價太大。所以語句要寫成 ： create_time = UNIX_TIMESTAMP (‘2016-06-06’)。
5，盡可能的擴展索引，不要新建立索引。比如表中已經有了 a 的索引，現在要加（a,b）的索引，那麼只需要修改原來的索引即可。
6，單個多列組合索引和多個單列索引的檢索查詢效果不同，因為在執行 SQL 時，MySQL 只能使用一個索引，會從多個單列索引中選擇一個限制最為嚴格的索引。
本文源自於:
https://blog.csdn.net/mysteryhaohao/article/details/51719871