mysql，對it打工人，這個幾乎是必備的技能之一。mysql可以解決我們平時工作中的大量的、有關(guān)增刪查改的問題。所以想深入了解mysql，我覺得關(guān)鍵在于他的增刪查改背后的算法，開搞。

面對增刪查改等問題，直接通過場景來看吧

場景1

有很多個數(shù)據(jù)，如上圖右邊的兩個鏈表，順序查找會會遍歷每一個元素，會很慢，尤其是數(shù)據(jù)量很大的時候，遍歷整個表都會會一次都會消耗大量的時間，而右邊是一個二叉樹，很明顯可以看到，二叉樹找到我們要的元素，最多只需要比對3次。常見的各種樹的算法復(fù)雜度操作二叉查找樹平衡二叉樹紅黑樹

查找O(n)O(logn)Olog(n)

插入O(n)O(logn)Olog(n)

刪除O(n)O(logn)Olog(n)

結(jié)論：鏈表不得行哦~

直接上二叉樹吧

于是乎制作成二叉樹，如下圖右邊的樹。左邊的鏈表89號元素需要遍歷查找，而89的元素在在二叉樹中，一次比對后就完成了定位。所以單一個二叉樹就大大的優(yōu)化了效率。

mysql有使用二叉樹嗎

沒有！為什么呢？

想想一下這個一個場景：有一堆連續(xù)的數(shù)組，放到二叉樹里邊，那么他的二叉樹結(jié)構(gòu)就是一個鏈表的樣子！說明了說在個別場景中，二叉樹相對鏈表并沒有優(yōu)勢可言。

ps：安利一個網(wǎng)站：https://www.cs.usfca.edu/~gal...

結(jié)論就是：二叉樹也不見得很好，個別場景下它就是個鏈表

紅黑樹(二叉平衡樹)

于是乎來到了紅黑樹，如下圖。性質(zhì)1：每個節(jié)點要么是黑色，要么是紅色。

性質(zhì)2：根節(jié)點是黑色。

性質(zhì)3：每個葉子節(jié)點(NIL)是黑色。

性質(zhì)4：每個紅色結(jié)點的兩個子結(jié)點一定都是黑色。

性質(zhì)5：任意一結(jié)點到每個葉子結(jié)點的路徑都包含數(shù)量相同的黑結(jié)點。

紅黑樹的應(yīng)用比較廣泛，主要是用它來存儲有序的數(shù)據(jù)，它的時間復(fù)雜度是O(lgn)，效率非常之高。TODO：

補充紅黑樹的偽算法

這樣看起來，紅黑樹效率會高一些，但是為什么mysql用的不是紅黑樹呢

因為實際場景的時候，數(shù)據(jù)量會很大，比如幾百萬的時候，樹的層級可能達到20層，其實這個速度是很快了，但是依然不夠高。在數(shù)據(jù)量大的時候，查找速度慢，它很難避免。

到這里可以得出，數(shù)據(jù)查詢的效率，跟數(shù)據(jù)層級是有關(guān)系的

所以這里是不是可以思考一下：我們就讓這個紅黑樹高度可控，控制在一個指定的深度，比如就三層，就會快很多？

這樣子做得話，我們只能在一個節(jié)點上，掛載更多的節(jié)點，而不是只是兩個。

實際上這就是b樹

看上去b樹效果已經(jīng)很好了，mysql有用到嗎？ NO！

實際上mysql用的是b+樹

b樹和b+樹在底層存儲數(shù)據(jù)的區(qū)別，葉子節(jié)點有一個指針指向左右，這樣可以按照順序地在葉子節(jié)點上遍歷數(shù)據(jù)。在mysql中b+樹的特點

mysql的b+樹的第一層節(jié)點的大小是16kb。可以通過sql語句查詢到：show global status liek "innodb_page_size"。其實第一層大索引大致可以存放(8+6)的數(shù)量是1170個的樣子，第二層的節(jié)點也是1170個樣樣子。第三場就單純存放索引，大概存放16個。

所以mysqlb+樹的索引，大概可以存放的數(shù)據(jù)量在2000萬個左右。

其實實際情況中，根節(jié)點的數(shù)據(jù)是常駐內(nèi)存的，并不是每次查詢都會重新載入一遍到內(nèi)存。所以千萬級別的表，查找依然很快。

實際上b+樹的層級是可以大于3層的，需要的話可以設(shè)置。單實際上到這一步的時候，早就應(yīng)該分庫分表的。也就是為什么mysql不建議存儲數(shù)據(jù)量大于2000萬行左右數(shù)據(jù)的一個原因。

MyISAM和innodb的區(qū)別

tips：

myisam引擎mysql數(shù)據(jù)文件夾里邊的文件簡介：frm存放的是表結(jié)構(gòu)，MYD是存放的數(shù)據(jù)，MYI存放的樹索引。

那么myisam的數(shù)據(jù)內(nèi)容，就是：

當(dāng)查找col1(假如col1是索引)等于30是，就是去myi的的文件里邊找，按照左邊的這個樹形結(jié)構(gòu)，定位到數(shù)據(jù)地址，拉出里邊的索引位置。通過索引記錄的位置，迅速定位到數(shù)據(jù)表的行的地址。這就是MYIsam

但實際中我們用的更多是innodb的引擎

為什么要用innodb呢？

innodb的文件結(jié)構(gòu)。

innodb在mysql數(shù)據(jù)庫文件夾下的數(shù)據(jù)文件格式是：

frm代表表結(jié)構(gòu)文件, idb存放的既包含數(shù)據(jù)，又包含索引。這幾個名稱猴嘴的mysql文件，相信做過數(shù)據(jù)遷移的童鞋，都不會覺得陌生，甚至看到他會有一絲絲氣憤，當(dāng)初就是這些讓自己經(jīng)常加班！

所以可以看出，innodb葉子節(jié)點上存放的不是索引，而直接是數(shù)據(jù)。Innodb的數(shù)據(jù)是直接掛在索引下的！

那么這些差異，導(dǎo)致不同引擎的數(shù)據(jù)庫，都有哪些差異呢？

好幾種索引介紹

聚集索引(聚蔟索引)：innodb葉子節(jié)點包含了完整數(shù)據(jù)的索引。

非聚集索引(離散索引)：myslam 葉子節(jié)點上，沒有存放完整的數(shù)據(jù)。

為什么innodb表建議必須建主鍵，并且推薦使用整形的自增主鍵呢

uuid不合適，開篇死。因為uuid不是遞增的

因為innodb的數(shù)據(jù)，必須有一個b+樹的索引結(jié)構(gòu)，來組織他的數(shù)據(jù)。mysql如果不建立主鍵，他就會進入這張表的第一列，判斷如果這列數(shù)據(jù)每一個數(shù)據(jù)都是唯一的，那么就用這一列當(dāng)做索引。如果遍歷完了每一列，都沒有唯一的列，那么mysql就會悄悄地為你建立一列隱藏列。這樣一下走下來，就花費了太多運算了，所以推薦用戶自建自增索引。

為什么建議自增整形呢？(雪花算法也是自增的。)因為查找的時候需要經(jīng)常比對大小，每一層會逐個比對大小，用asii碼去比對。而整形，在這一塊比對的時候會快一些，所以要求整形。為甚自增呢？

為了快。

比如遇到范圍查詢的時候，定位到了一個元素，是不是就很快能判斷上下位置的元素。

建立索引的時候可以選擇btree，還有hash。hash是什么樣的呢

hash定位速度很快哦。超級快！

首先算hash值，然后根據(jù)hash存放在某個位置，這個數(shù)據(jù)里邊存放了值和節(jié)點的地址。當(dāng)有多個的時候，就在這個后邊直接添加。當(dāng)數(shù)據(jù)量不大的時候，hash的效率非常高。

但實際上工作用絕大多數(shù)的時候都用的是b+而不是hash(99.99%都不是)。為什么呢？hash沖突，只是一個

最主要的是：不支持范圍查詢。當(dāng)有返回查詢的時候，就只能遍歷查詢。所以范圍查詢hash掛掉了，那么b+樹就能hold住嗎？

b+樹的葉子節(jié)點有一個雙向指針，來保持數(shù)據(jù)的連貫性。此外b+數(shù)據(jù)幫我們維護了一個從做到右的一種遞增結(jié)構(gòu)。所以范圍查詢，只要定位到了上限或者下限，直接順藤摸瓜，很快就能完成定位。這也是為什么b+樹葉子節(jié)點要維護一個雙向指針的原因。

即使你沒有按照順序存放數(shù)據(jù)，b+樹，也會幫你整理好順序。比如7,8,6,也會將6放到78前。所以如果不是遞增，調(diào)整順序會消耗計算，尤其是在節(jié)點分裂的時候，要影響整個樹的結(jié)構(gòu)的時候，結(jié)構(gòu)變化就會占用很多資源。但是如果順序存放，反正結(jié)構(gòu)性調(diào)整的概率是比較低的，相當(dāng)于也是加快了存儲的順序。

這也是添加了索引為什么會讓存儲變慢的底層原因。

常見的聯(lián)合索引的底層存儲結(jié)構(gòu)長什么樣子的

從上圖，可以看一下二級索引的特點，二級索引緊貼在一級索引后邊，question：為什么二級索引長成這樣，實際使用時運作機制是什么樣的。

其實：索引是一種排好序的數(shù)據(jù)結(jié)構(gòu)

想要理解多級索引的特點，其實，理解好mysql處理索引時，是如何為多級索引排序的就好了。

其實查找時，方法就是逐個比對。如果第一層，就已經(jīng)可以比對處順序了，那么這些數(shù)據(jù)，就直接就按這個順序來排就行。如果第一個索引大小相同，那就根據(jù)第二個來排序，如果第二個相同，就看第三個。如果第三個也相同，就看主鍵，如果沒有主鍵，那就看隱藏主鍵(所以建立自增索引很重要！)。

總結(jié)

以上是生活随笔為你收集整理的mysql索引使增删变慢_mysql优化之索引篇的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。