在碼農(nóng)的世界里，優(yōu)美的應(yīng)用體驗(yàn)，來源于程序員對細(xì)節(jié)的處理以及自我要求的境界，年輕人也是忙忙碌碌的碼農(nóng)中一員，每天、每周，都會留下一些腳印，就是這些創(chuàng)作的內(nèi)容，有一種執(zhí)著，就是不知為什么，如果你迷茫，不妨來瞅瞅碼農(nóng)的軌跡。

有100萬條數(shù)據(jù)，需要從中篩選查詢10條數(shù)據(jù)，盡然用了3秒才出來，于是乎，你會想到添加個索引，然后查詢速度一下從秒級別變成了毫秒級別，你知道中間發(fā)生了什么嗎？

1 索引

索引的作用就是為了提高數(shù)據(jù)查詢的效率，如同書的目錄一樣，你可以通過書的目錄來快速定位內(nèi)容的大概位置。

在 MySQL 中，索引是在存儲引擎層實(shí)現(xiàn)的，所以并沒有統(tǒng)一的索引標(biāo)準(zhǔn)，不同存儲引 擎的索引的工作方式并不一樣。

1.1 哈希表 索引模型

哈希表數(shù)據(jù)結(jié)構(gòu)適用于只等值查詢的場景。

哈希表是以鍵 - 值(key-value)存儲數(shù)據(jù)的結(jié)構(gòu)，只要輸入待查找 key， 就可以從中獲取到對應(yīng)的值 Value，實(shí)現(xiàn)原理也就是在一個數(shù)組中，將key通過哈希函數(shù)換算成一個確定的位置，然后把 value 放在數(shù)組的這個位置上。

多個 key 值經(jīng)過哈希函數(shù)的換算，會出現(xiàn)同一個值的情況，然后處理沖突的方法是，拉出一個鏈表，如下圖中的152號位置。

1.2 有序數(shù)組 索引模型

有序數(shù)組常應(yīng)用于等值查詢和范圍、適用于靜態(tài)存儲引擎 查詢場景。

如將用戶表的電話在數(shù)組中以遞增的順序保存，在查詢某一電話號碼時，用二分法就可以快速得到，這個時間復(fù)雜度很小。

1.3 二叉搜索樹

二叉搜索樹的特點(diǎn)是如下圖所示：

二叉樹是搜索效率最高的，但是實(shí)際上大多數(shù)的數(shù)據(jù)庫存儲卻并不使用二叉樹。

原因是，索引不止存在內(nèi)存中，還要寫到磁盤上，對于一個 100 多萬行的表，如果使用二叉樹來存儲，一次查詢可能需要訪問 20 個數(shù)據(jù)塊，從機(jī)械硬盤中隨機(jī)讀一個數(shù)據(jù)塊大約需要 10 ms 左右的時間，那么查詢一次就需要訪問20個10ms，效率很低。

N 叉樹在讀寫上的性能優(yōu)點(diǎn)，以及適配磁盤的訪問模式，已經(jīng)被廣泛應(yīng)用在數(shù)據(jù)庫引 擎中。

2 InnoDB 存儲引擎的索引模型

在 InnoDB 使用了 B+ 樹索引模型，所以數(shù)據(jù)都是存儲在 B+ 樹中的，其中表是根據(jù)主鍵順序以索引的形式存放的，這種存儲方式的表稱為索引組織表。

每一個索引在 InnoDB 里面對應(yīng)一棵 B+ 樹。

例如在文章開如時有指令

question_wrong 表中有主鍵 ID，同時又為 create_time 添加了一個索引，這就有了主鍵索引和非主鍵索引區(qū)分。

主鍵索引的葉子節(jié)點(diǎn)存的是整行數(shù)據(jù)，在 InnoDB 里，主鍵索引也被稱為聚簇索引 (clustered index)。

非主鍵索引的葉子節(jié)點(diǎn)內(nèi)容是主鍵的值，在 InnoDB 里，非主鍵索引也被稱為二級索引 (secondary index)。

如執(zhí)行查詢語句

即主鍵查詢方式，則只需要搜索 ID 這棵 B+ 樹

普通索引查詢方式，則需要先搜索 create_time索引樹，得到對應(yīng)的ID 的值，然后再到ID 索引樹搜索一次（這個過程稱為回表）。

基于非主鍵索引的查詢需要多掃描一棵索引樹。因此在實(shí)際應(yīng)用中推薦使用主鍵查詢。

3 索引維護(hù)

在插入新值的時候，B+ 樹為了維護(hù)索引有序性，也需要做對應(yīng)的更新，如當(dāng)前主鍵索引B+ 樹維護(hù)到 ID為 100，當(dāng)插入新的數(shù)據(jù)ID為101時，只需要在 主鍵索引B+ 樹記錄的后面插入一個新記錄就可以，如果插入的新的數(shù)據(jù)ID為50時，就需要邏輯上挪動后面的數(shù)據(jù)，空出位置。

如果當(dāng)前 主鍵索引B+ 樹的數(shù)據(jù)頁滿了，就需要申請一 個新的數(shù)據(jù)頁，然后挪動部分?jǐn)?shù)據(jù)過去，這個過程稱為頁分裂。

4 自增主鍵

一般情況下建表語句里要有自增主鍵，自增主鍵是指自增列上定義的主鍵，在建表語句中一般是這么定義的: NOT NULL PRIMARY KEY AUTO_INCREMENT。

小編的表構(gòu)建規(guī)范就是建表一定要有自增主鍵，一般是ID。

當(dāng)指定自增主鍵ID時，插入新記錄的時候可以不指定 ID 的值，系統(tǒng)會獲取當(dāng)前 ID 最大值加 1 作為下一條記錄的 ID 值，每次插入一條新記錄，都是追加操作，都不涉及到挪動其他記錄，也不會觸發(fā)葉子節(jié)點(diǎn)的分裂。

主鍵長度越小，普通索引的葉子節(jié)點(diǎn)就越小，普通索引占用的空間也就越小。所以，從性能和存儲空間方面考量，自增主鍵往往是一個不錯的的選擇。

MySql 一條更新語句是如何執(zhí)行的? MySql雜談、MySql WAL 技術(shù)

一條SQL查詢語句是如何執(zhí)行的? MySql雜談