來自公眾號：碼大叔
作者碼大叔
在日常工作中我們不可避免地會遇到慢SQL問題，比如筆者在之前的公司時會定期收到DBA彪哥發(fā)來的Oracle AWR報告，并特別提示我某條sql近階段執(zhí)行明顯很慢，可能要優(yōu)化一下等。對于這樣的問題通常大家的第一反應就是看看sql是不是寫的不合理啊諸如：“避免使用in和not in，否則可能會導致全表掃描”“ 避免在where子句中對字段進行函數(shù)操作”等等，還有一種常見的反應就是這個表有沒有加索引？絕大部分情況下，加了個索引基本上就搞定了。
既然題目是《從千萬級數(shù)據(jù)查詢來聊一聊索引結構和數(shù)據(jù)庫原理》，首先就來構造一個千萬級的表直觀感受下。我們創(chuàng)建了一張user表，然后插入了1000萬條數(shù)據(jù)，查詢一下：
用了近30秒的時間，這還是單表查詢，關聯(lián)查詢明顯會更讓人無法忍受。接下來，我們只是對id增加一個索引，再來驗證一把：
從30s到0.02s，提升了足足1500倍。為什么加了索引之后，速度嗖地一下子就上去了呢？我們從【索引數(shù)據(jù)結構】、【Mysql原理】兩個方面入手。
一、索引數(shù)據(jù)結構
我們先來看下 MySQL官方對索引的定義：
索引（Index）是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結構。
這里面有2個關鍵詞：高效查找、數(shù)據(jù)結構。對于數(shù)據(jù)庫來說，查詢是我們最主要的使用功能，查詢速度肯定是越快越好。最基本的查找是順序查找，更高效的查找我們很自然會想到二叉樹、紅黑樹、Hash表、BTree等等。
1.1 二叉樹
這個大家很熟悉了，他有一個很重要的特點：左邊節(jié)點的鍵值小于根的鍵值，右邊節(jié)點的鍵值大于根的鍵值。比如圖1，它確實能明顯提高我們的搜索性能。但如果用來作為數(shù)據(jù)庫的索引，明顯存在很大的缺陷，但對于圖2這種遞增的id，存儲后索引近似于變成了單邊的鏈表，肯定是不合適的。
1.2 紅黑樹
也稱之為平衡二叉樹。在JDK1.8后，HashMap對底層的鏈表也優(yōu)化成了紅黑樹（后續(xù)文章我們可以講講Hashmap1.8之后的調整）。平衡二叉樹的結構使樹的結構較好，明顯提高查找運算的速度。但是缺陷也同樣很明顯，插入和刪除運算變得復雜化，從而降低了他們的運算速度。對大數(shù)據(jù)量的支撐很不好，當數(shù)據(jù)量很大時，樹的高度太高，如果查找的數(shù)據(jù)是葉子節(jié)點，依然會超級慢。
1.3 BTree
B-Tree是為磁盤等外存儲設備設計的一種平衡查找樹。系統(tǒng)從磁盤讀取數(shù)據(jù)到內存時是以磁盤塊（block）為基本單位的，位于同一個磁盤塊中的數(shù)據(jù)會被一次性讀取到內存中。在Mysql存儲引擎中有頁（Page）的概念，頁是其磁盤管理的最小單位。Mysql存儲引擎中默認每個頁的大小為16KB，查看方式：
mysql> show variables like 'innodb_page_size';
我們也可以將它修改為4K、8K、16K。系統(tǒng)一個磁盤塊的存儲空間往往沒有16K，因此Mysql每次申請磁盤空間時都會將若干地址連續(xù)磁盤塊來達到頁的大小16KB。Mysql在把磁盤數(shù)據(jù)讀入到磁盤時會以頁為基本單位，在查詢數(shù)據(jù)時如果一個頁中的每條數(shù)據(jù)都能有助于定位數(shù)據(jù)記錄的位置，這將會減少磁盤I/O次數(shù)，提高查詢效率。
如上圖所示，一棵B樹包含有鍵值、存儲子節(jié)點的指針信息、及除主鍵外的數(shù)據(jù)。相對于普通的樹BTree將橫向節(jié)點的容量變大，從而存儲更多的索引。
1.4 B+Tree
在B-Tree的基礎上大牛們又研究出了許多變種，其中最常見的是B+Tree，MySQL就普遍使用B+Tree實現(xiàn)其索引結構。
與B-Tree相比，B+Tree做了以下一些改進：
1、非葉子節(jié)點，只存儲鍵值信息，這樣極大增加了存放索引的數(shù)據(jù)量。
2、 所有葉子節(jié)點之間都有一個鏈指針。對于區(qū)間查詢時，不需要再從根節(jié)點開始，可直接定位到數(shù)據(jù)。
3、 數(shù)據(jù)記錄都存放在葉子節(jié)點中。根據(jù)二叉樹的特點，這個是順序訪問指針，提升了區(qū)間訪問的性能。
通過這樣的設計，一張千萬級的表最多只需要3次磁盤交互就可以找出數(shù)據(jù)。
二、Mysql部分原理說明
這一部分我們選舉幾個日常面試過程中或者使用過程中比較常見的問題通過問答的形式來進行講解。
2.1、數(shù)據(jù)庫引擎MyISAM和InnoDB有什么區(qū)別
MyISAM：
在Mysql8之前，默認引擎是MyISAM，其目標是快速讀取。
特點：
1、讀取非?？?，如果頻繁插入和更新的話，因為涉及到數(shù)據(jù)全表鎖，效率并不高
2、保存了數(shù)據(jù)庫行數(shù)，執(zhí)行count時，不需要掃描全表；
3、不支持數(shù)據(jù)庫事務；
4、不支持行級鎖和外鍵；
5、不支持故障恢復。
6、支持全文檢索FullText，壓縮索引。
建議使用場景：
1、做很多count計算的，（如果count計算后面有where還是會全表掃描）
2、插入和更新較少，查詢比較頻繁的InnoDB：
在Mysql8里，默認存儲引擎改成了InnoDB。
特點
1、支持事務處理、ACID事務特性
2、實現(xiàn)了SQL標準的四種隔離級別
3、支持行級鎖和外鍵約束
4、可以利用事務日志進行數(shù)據(jù)恢復
5、不支持FullText類型的索引，沒有保存數(shù)據(jù)庫行數(shù)，計算count(*)需要全局掃描
6、支持自動增加列屬性auto_increment
7、最后也是非常重要的一點：InnerDB是為了處理大量數(shù)據(jù)時的最大性能設計，其CPU效率可能是其他基于磁盤的關系型數(shù)據(jù)庫所不能匹敵的。
建議使用場景
1、可靠性高或者必須要求事務處理
2、表更新和查詢相當?shù)念l繁，并且表鎖定的機會比較大的情況下，指定InnerDB存儲引擎。
2.2 表和數(shù)據(jù)等在Mysql中是如何存儲的
我們新建一個數(shù)據(jù)庫mds_demo，里面有兩張表：order_info,user
我們找到mysql存放數(shù)據(jù)的data目錄，存在一個mds_demo的文件夾，同時我們也找到了order_info和user的文件。
為什么兩張表產生了不同的文件呢？原因很簡單，因為創(chuàng)建這兩張表時使用了不同的引擎
MyISAM引擎在創(chuàng)建表的時候，會創(chuàng)建三個文件
.MYD文件：存放表里的數(shù)據(jù)
.MYI文件：存放索引數(shù)據(jù)
.sdi文件： Serialized Dictionary Information的縮寫。在Mysql5里沒有sdi文件，但會有一個FRM文件，用戶存放表結構信息。在MySQL8.0中重新設計了數(shù)據(jù)字典，改為sdi。
MyISAM的索引和數(shù)據(jù)是分開的，并且索引是有壓縮的，所以存儲文件就會小很多，MyISAM應對錯誤碼導致的數(shù)據(jù)恢復的速度很快。InnerDB引擎在創(chuàng)建表的時候，只有1個文件.ibd，即存放了索引又存放了文件，參見B+Tree。所以它也被稱之為聚集索引，即葉子節(jié)點包含完整的索引和數(shù)據(jù)，對應的MyISAM為非聚集索引。
補充說明一下：存儲引擎是針對表的，而不是針對數(shù)據(jù)庫，同一個庫的不同的表可以使用不同的引擎。
2.3 為什么InnoDB必須要有主鍵，并且推薦使用整型的自增主鍵？
通過上面的講解這個問題其實已經很清楚了，為了滿足MySQL的索引數(shù)據(jù)結構B+樹的特性，必須要有索引作為主鍵，可以有效提高查詢效率。有的童鞋可能會說我創(chuàng)建表的時候可以沒有主鍵啊，這個其實和Oracle的rownum一樣，如果不指定主鍵，InnoDB會從插入的數(shù)據(jù)中找出不重復的一列作為主鍵索引，如果沒找到不重復的一列，InnoDB會在后臺增加一列rowId做為主鍵索引。所以不如我們自己創(chuàng)建一個主鍵。
將索引的數(shù)據(jù)類型是設置為整型，一來占有的磁盤空間或內存空間更少，另一方面整型相對于字符串比較更快速，而字符串需要先轉換為ASCII碼然后再一個個進行比較的。
參見B+樹的圖它本質上是多路多叉樹，如果主鍵索引不是自增的，那么后續(xù)插入的索引就會引起B(yǎng)+樹的其他節(jié)點的分裂和重新平衡，影響數(shù)據(jù)插入的效率，如果是自增主鍵，只用在尾節(jié)點做增加就可以。
最后特別強調一點：不管當前是否有性能要求或者數(shù)據(jù)量多大，千萬不要使用UUID作為索引。
2.4 為什么Mysql存儲引擎中默認每個頁的大小為16KB？
假設我們一行數(shù)據(jù)大小為1K，那么一頁就能存16條數(shù)據(jù)，包含指針+數(shù)據(jù)+索引。假設一行數(shù)據(jù)大小為1K，那么一頁（1個葉子節(jié)點）就能存16條數(shù)據(jù)；對于非葉子節(jié)點，假設ID為bigint類型那么長度為8B，指針大小在Innodb源碼中為6B，一共就是14B，那么一頁里就可以存儲16K/14=1170個(主鍵+指針)，這樣一顆高度為3的B+樹能存儲的數(shù)據(jù)為：1170117016=2千萬級別。所以我們前面1000萬的數(shù)據(jù)只有0.02s。
2.5 HASH算法的使用場景
Hash算法是一種散列算法，就是計算出某個字段的hash，然后存放在對應的地址中，查找數(shù)據(jù)時只需要1次定位而不像BTree那樣從根節(jié)點找到葉子節(jié)點經過多次IO操作，所以查詢效率非常地高。但同樣也有很多的弊端，講一下最重要的兩條。
1、很明顯hash只支持=、IN等查詢，而不支持范圍查詢
2、 Hash 索引在任何時候都不能避免表掃描。
所以使用時務必注意。
圖片： 本文中的部分圖片來源于網(wǎng)絡，版本歸原作者所有。
參考： https://www.cnblogs.com/vianzhang/p/7922426.html
https://www.cnblogs.com/yangecnu/p/Introduce-B-Tree-and-B-Plus-Tree.html
http://blog.codinglabs.org/articles/theory-of-mysql-index.html
https://tech.meituan.com/2014/06/30/mysql-index.html
https://www.ucloud.cn/yun/110762.html
https://www.cs.usfca.edu/~galles/visualization/BST.html
向圖片作者及內容參考的作者表示感謝！
