昨天看到一個MySQL資料庫設計原則：強烈建議表的主鍵使用整型自增主鍵，為啥呢？
要弄明白這個問題首先需要了解MySQL是如何維護資料的，你需要知道以下幾點：
- MySQL的InnoDB存盤引擎是在B+樹上維護表資料的
- B+樹是一種平衡樹
- 在這棵樹上，每個節點在計算機中叫做資料頁，默認16k
- 樹的葉子節點是完整的行資料，非葉子節點是主鍵
- 葉子節點中的行資料按id從小到大的順序排列
PS：MySQL索引底層資料結構詳細分析程序參考這篇深入分析MySQL索引底層原理
查詢程序
明白了MySQL維護資料的方式，下面我們再來看一下如何在這棵樹上查詢資料，
假設我們有個T表，表結構和資料如下：
CREATE TABLE `T` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` varchar(50) COMMENT '姓名',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into T(id,name)
values(1,'張三'),(2,'李四'),(3,'王五'),(5,'趙六');
了解了MySQL維護資料的方式，我們可以把T表的資料存盤邏輯結構畫出來：
如上圖所示，根節點上維護了主鍵2和5，兩個葉子節點分別存兩條記錄，
當我們查詢id=2的記錄時，首先會從樹的根節點開始遍歷，通過與根節點的id值比較，定位到記錄在第一個葉子節點，然后把第一個葉子節點從磁盤加載到記憶體，在記憶體中依次讀取記錄進行比較：
- 讀取第一個記錄，發現id不等于2跳過
- 繼續取下一個記錄，判斷id等于2
- 于是就查到了id等于2的這條記錄
可以發現，查找程序是從根節點開始的，通過與非葉子節點的id比較，定位到資料所在節點，然后依次遍歷節點上的記錄逐一對比，從而找到匹配條件的記錄，
插入程序
再來看一下插入資料，如果此時插入的資料為(6,’孫七’)，按照葉子節點的行資料排序特點（按id從小到大排），那么(6,’孫七’)這條記錄一定在(5,’趙六’)后面，如果此時資料頁P3還能存下，則直接順序寫入，如果資料頁P3存不下這條記錄，InnoDB會申請一個新的資料頁P4寫入(6,’孫七’)，這個程序叫做頁分裂，
如果此時插入的資料為(4,’孫七’)，按照葉子節點的行資料排序特點（按id從小到大排），那么(4,’孫七’)這條記錄一定在(3,’王五’)后面(5,’趙六’)前面，如果此時資料頁P3還能存下，那么為了給(4,’孫七’)騰位置，則需要將資料頁P3上(3,’王五’)這條記錄之后的資料全部往后移動，而如果資料頁P3存不下這條記錄，InnoDB會申請一個新的資料頁P4，并將P3上部分資料轉移到P4上，在(3,’王五’)后寫入(6,’孫七’)，
為什么主鍵建議整型、自增？
了解了資料的存盤方式以及查詢程序和插入程序，我們接下來進入正題，回答為什么主鍵建議是整型、自增這個問題，
首先為什么是整型呢？
我們從查找程序可以看到，整個程序關鍵點就是在這棵樹上不停的比較id值是否等于、大于還是小于2，即資料的大小比較，資料比較是消耗CPU的，而不同的資料型別耗時不同，常見的整型要小于字符型，
整型的1<2比較的效率高還是字串的“abc”和“abe”比較的效率高呢？顯然是前者，因為字串的比較是轉換成ASCII碼一位一位的比，如果最后一位不一樣，比到最后才比較出大小，就比整型比較慢多了，存盤空間來說，整型更小，索引越節約資源越好，
那為什么自增呢？
原因也可想而知，其實上面已經提到了，就是因為InnoDB的索引是按大小排好序的，插入的新資料如果主鍵是自增的，那么只需要按順序往后寫入即可，性能會比較高，而如果每次插入的主鍵是跳躍式的，那么就會涉及到上面說的頁分裂，需要挪動資料，性能就會受到影響，
引申一下，是不是一定需要自增呢？
答案是不，我們都知道當某一個業務量增長非常快，資料量非常大，資料庫性能無法滿足業務需求的時候通常會實施分庫分表，這個時候自增主鍵就不適用了，比如訂單表，分成16個表，如果都使用自增的話，肯定會造成訂單id重復，所以此時的解決方案就是分布式id，保證趨勢遞增即可，
小結
今天我通過講解MySQL資料的存盤方式以及資料查找與插入程序，從MySQL的底層機制了解了MySQL主鍵為什么建議使用整型并且自增，最后我們引申了一個分布式id的問題，此時并不強制嚴格自增，保證趨勢遞增即可，
好了，今天的文章就到這里了，如果你對于今天的文章有疑問，請留言探討，
感謝你的觀看，也歡迎你把這篇文章分享給更多的朋友一起閱讀，
說的再好，不如行動，不怕慢，就怕站，轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/548870.html
標籤：其他
上一篇：Mysql之SQL陳述句基礎1