這篇文章主要講解了“MySQL海量存儲(chǔ)的索引與分表設(shè)計(jì)的方法教程”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“MySQL海量存儲(chǔ)的索引與分表設(shè)計(jì)的方法教程”吧！

成都創(chuàng)新互聯(lián)專業(yè)為企業(yè)提供五臺(tái)網(wǎng)站建設(shè)、五臺(tái)做網(wǎng)站、五臺(tái)網(wǎng)站設(shè)計(jì)、五臺(tái)網(wǎng)站制作等企業(yè)網(wǎng)站建設(shè)、網(wǎng)頁(yè)設(shè)計(jì)與制作、五臺(tái)企業(yè)網(wǎng)站模板建站服務(wù)，10年五臺(tái)做網(wǎng)站經(jīng)驗(yàn)，不只是建網(wǎng)站，更提供有價(jià)值的思路和整體網(wǎng)絡(luò)服務(wù)。

一、什么是InnoDB記錄存儲(chǔ)方式?

大家都知道在InnoDB存儲(chǔ)引擎中記錄是按主鍵順序存儲(chǔ)，并且依靠這個(gè)特性為表創(chuàng)建了主鍵聚簇索引。

InnoDB是如何實(shí)現(xiàn)記錄“順序存儲(chǔ)”的呢?首先要知道“順序”分頁(yè)內(nèi)順序和頁(yè)間順序，頁(yè)為InnoDB內(nèi)外存交換的基本單位。

• 頁(yè)間順序：磁盤文件中頁(yè)與頁(yè)之間使用雙向鏈表連接，頁(yè)間有可能是物理有序。大多數(shù)情況是邏輯上的有序;

• 頁(yè)內(nèi)順序：頁(yè)內(nèi)各記錄使用單項(xiàng)鏈表把記錄連接起來(lái)，所以頁(yè)內(nèi)是邏輯有序，配合slot數(shù)據(jù)結(jié)構(gòu)實(shí)現(xiàn)頁(yè)內(nèi)接近二分查找的查詢效率。

圖為InnoDB頁(yè)內(nèi)空間分布：

根據(jù)以上特點(diǎn)，我們來(lái)分析下使用不同的主鍵對(duì)存儲(chǔ)會(huì)造成哪些影響：

• 自增主鍵：主鍵值遞增，數(shù)據(jù)是順序插入的，所以在頁(yè)內(nèi)數(shù)據(jù)物理連續(xù)，寫滿一頁(yè)后在順序分配下一頁(yè)。在沒(méi)有刪除操作的情況下，整個(gè)表的記錄在磁盤文件中都是按照寫入順序連續(xù)存儲(chǔ)的。這中存儲(chǔ)方式磁盤利用率非常高，且隨機(jī)IO很低。插入效率相當(dāng)高。

• 業(yè)務(wù)主鍵：比如用戶表使用uid做主鍵，商品表使用infoId做主鍵，這種有意義的主鍵，我們稱為業(yè)務(wù)主鍵。很明顯，業(yè)務(wù)主鍵不但無(wú)法做到記錄物理連續(xù)而且在插入數(shù)據(jù)時(shí)還可能造成頁(yè)的分裂，從而導(dǎo)致頁(yè)內(nèi)碎片，例如如果一個(gè)頁(yè)空間已滿，存儲(chǔ)主鍵值0~99，100條數(shù)據(jù)，如果要插入55這條記錄，頁(yè)內(nèi)已經(jīng)放不下，需要分裂成兩個(gè)頁(yè)才能完成插入操作，而分裂后的兩個(gè)頁(yè)很難被寫滿，會(huì)造成頁(yè)內(nèi)碎片，所以業(yè)務(wù)主鍵在寫入性能和磁盤利用率上都不如自增主鍵。

通過(guò)上面的分析，我們是不是可以得出結(jié)論：使用自增主鍵一定好呢?在我們分析完InnoDB的索引以前，現(xiàn)在下結(jié)論還有些早。

二、什么是主鍵索引?

InnoDB會(huì)自動(dòng)在表的主鍵上創(chuàng)建索引，數(shù)據(jù)結(jié)構(gòu)使用B+Tree。根據(jù)存儲(chǔ)上的特點(diǎn)主鍵索引也被稱為聚簇索引。聚簇索引的索引結(jié)構(gòu)和實(shí)際數(shù)據(jù)是存儲(chǔ)在一起的，B+Tree葉子節(jié)點(diǎn)存儲(chǔ)的就是實(shí)際的記錄，如圖所示：

三、什么是非主鍵索引?

既然記錄存儲(chǔ)在主鍵索引結(jié)構(gòu)中，那么在其他列創(chuàng)建的索引是如何找到記錄的呢?我們可以很自然的想到，非主鍵列上的索引可以先通過(guò)自身索引結(jié)構(gòu)查找到主鍵值，然后在用主鍵值在聚簇索引上找到相應(yīng)的記錄。InnoDB就是這么做的，所以我們也稱非主鍵列上的索引為二級(jí)索引(因?yàn)橐淮尾樵冃枰檎覂蓚€(gè)索引樹(shù))

二級(jí)索引有以下特點(diǎn)：

• 除了主鍵索引以外的索引;

• 索引結(jié)構(gòu)葉子節(jié)點(diǎn)中的Data是主鍵值;

• 一次查詢需要查找自身和主鍵兩個(gè)索引。

四、什么是聯(lián)合索引?

聯(lián)合索引也叫多列索引，索引結(jié)構(gòu)的key包含多個(gè)字段，排序時(shí)先第一列比較，如果相同再按第二列比較，以此類推。聯(lián)合索引結(jié)構(gòu)圖如圖所示：

聯(lián)合索引上的查詢要滿足以下特點(diǎn)：

• key按照最左開(kāi)始查找，否則無(wú)法使用索引;

• 跳過(guò)中間列，會(huì)導(dǎo)致后面的列不能使用索引;

• 某列使用范圍查詢是，后面的列不能使用索引。

根據(jù)前綴索引特性，聯(lián)合索引(a,b,c)，可以滿足(a)，(a,b)，(a,b,c)三種查詢。

五、小結(jié)

了解了InnoDB的索引后，我們?cè)賮?lái)分析自增主鍵和業(yè)務(wù)主鍵優(yōu)缺點(diǎn)：

• 自增主鍵：寫入、查詢效率和磁盤利用率都高，但每次查詢都需要兩級(jí)索引，因?yàn)榫€上業(yè)務(wù)不會(huì)有直接使用主鍵列的查詢。

• 業(yè)務(wù)主鍵：寫入、查詢效率和磁盤利用率都低，但可以使用一級(jí)索引，依賴覆蓋索引的特性，某些情況下在非主鍵索引上也可以實(shí)現(xiàn)1次索引完成查詢(后面的案例中會(huì)詳細(xì)介紹)。

自增主鍵相對(duì)業(yè)務(wù)主鍵在IO效率上優(yōu)勢(shì)在SSD硬盤下幾乎可以忽略，而在業(yè)務(wù)查詢性能上業(yè)務(wù)主鍵有明顯優(yōu)勢(shì)，所以在業(yè)務(wù)數(shù)據(jù)庫(kù)中，我們使用的都是業(yè)務(wù)主鍵。

六、電商業(yè)務(wù)分表設(shè)計(jì)與實(shí)踐

針對(duì)MyQL數(shù)據(jù)庫(kù)特性結(jié)合自身業(yè)務(wù)特點(diǎn)制定了一系列數(shù)據(jù)庫(kù)使用規(guī)范，可以有效的指導(dǎo)一線RD在項(xiàng)目開(kāi)發(fā)過(guò)程中數(shù)據(jù)庫(kù)表和索引的設(shè)計(jì)工作。下面介紹電商業(yè)務(wù)中表和索引的重點(diǎn)設(shè)計(jì)原則以及兩個(gè)實(shí)際案例。

1、表設(shè)計(jì)原則

• 主鍵選擇：前面我們已經(jīng)對(duì)比分析過(guò)業(yè)務(wù)主鍵和自增主鍵的優(yōu)缺點(diǎn)，結(jié)論是業(yè)務(wù)主鍵更符合業(yè)務(wù)的查詢需求，而互聯(lián)網(wǎng)業(yè)務(wù)大多都符合讀多寫少的特性，所以所有線上業(yè)務(wù)都使用業(yè)務(wù)主鍵。

• 索引個(gè)數(shù)：由于過(guò)多的索引會(huì)造成索引文件過(guò)大，所以要求索引數(shù)不多于5個(gè)。

• 列類型選擇：通常越小、越簡(jiǎn)單越好，例如：BOOL字段統(tǒng)一使用TINYINT，枚舉字段統(tǒng)一使用TINYINT，交易金額統(tǒng)一使用LONG。因?yàn)锽OOL和枚舉類型使用TINYINT可以很方便的擴(kuò)展，針對(duì)金額數(shù)據(jù)，雖然InnoDB提供了支持精確計(jì)算的DECIMAL類型，但DECIMAL是存儲(chǔ)類型不是數(shù)據(jù)類型，不支持CPU原聲計(jì)算，效率會(huì)低一些，所以我們簡(jiǎn)單處理將小數(shù)轉(zhuǎn)換為整數(shù)用LONG存儲(chǔ)。

• 分表策略：首先要明確數(shù)據(jù)庫(kù)出現(xiàn)性能問(wèn)題一般在數(shù)據(jù)量到達(dá)一定程度后!所以要求我們提前做好預(yù)估，不要等需要拆分時(shí)再拆，一般把表的數(shù)據(jù)量控制在千萬(wàn)級(jí)別;常用分表策略有兩種：按key取模，讀寫均勻;按時(shí)間分，冷熱數(shù)據(jù)明確。

2、實(shí)際案例

案例一：用戶表設(shè)計(jì)

用戶表包含字段：uid，nickname，mobile，addr，image…..，switch;uid為主鍵，業(yè)務(wù)上有按uid和mobile兩種查詢需求，所以要在moblie上創(chuàng)建索引。

switch列比較特殊，類型為BIGINT，用來(lái)保存用戶的BOOL類型的屬性，每一位可以保存用戶的一個(gè)屬性，例如我們用第一位保存是否接收推送，第二位保存是否保存離線消息等等。

這種設(shè)計(jì)有很高的擴(kuò)展性(因?yàn)锽IGINT有64位，可以保存64個(gè)狀態(tài)，一般情況很難用滿)，但是同時(shí)也帶來(lái)一些問(wèn)題，switch有很高的查詢頻率。由于InnoDB是行存儲(chǔ)，要找查詢switch需要把正行數(shù)據(jù)取出來(lái)。

針對(duì)上述場(chǎng)景，我們?cè)诒碓O(shè)計(jì)上可以做哪些優(yōu)化呢?常用的方案是把表垂直查分，這種很常見(jiàn)我們不做過(guò)多討論。

還有一種方案我們可以利用InnoDB覆蓋索引的特性，在uid和switch兩列上創(chuàng)建聯(lián)合索引，這樣在二級(jí)索引上包含uid和switch兩列的值，這樣用uid查詢switch時(shí)，只通過(guò)二級(jí)所以就能找到switch，不需要訪問(wèn)記錄，甚至不需要到二級(jí)索引的葉子節(jié)點(diǎn)就可以找到要查詢的switch值，查詢效率非常高。

另外有一點(diǎn)需要考慮，可以想象switch的變更也是相當(dāng)頻繁的，switch值得改變會(huì)導(dǎo)致聯(lián)合索引的變更嗎(這里的變更指索引節(jié)點(diǎn)分裂或順序調(diào)整)?

答案是不會(huì)!因?yàn)槁?lián)合索引的第一列uid是唯一且不會(huì)變的，所以u(píng)id就已經(jīng)決定了索引的順序，switch列的改變只會(huì)改變索引節(jié)點(diǎn)上第二個(gè)key的值，不會(huì)改變索引結(jié)構(gòu)。

案例二：IM子系統(tǒng)分表方案

IM子系統(tǒng)包含：用戶、聯(lián)系人、云消息、系統(tǒng)消息四個(gè)主要的業(yè)務(wù)表。數(shù)據(jù)庫(kù)按業(yè)務(wù)拆分，每個(gè)業(yè)務(wù)使用單獨(dú)的實(shí)例。除系統(tǒng)消息表外，其他表都是以u(píng)id做key按128取模分了128個(gè)表。由于系統(tǒng)消息的業(yè)務(wù)比較特殊，所以其分表方案與其他業(yè)務(wù)不太一樣。

我們先來(lái)了解下系統(tǒng)消息的業(yè)務(wù)特點(diǎn)：系統(tǒng)消息表保存的是服務(wù)器發(fā)出通知類型的消息，既然是通知，就會(huì)有實(shí)效性，我們規(guī)定系統(tǒng)消息有效期為30天，所以針對(duì)以上特點(diǎn)我們采取如下分表方案：

• 按月對(duì)系統(tǒng)消息表進(jìn)行分表，每個(gè)月的數(shù)據(jù)又分為128個(gè)表。

大家思考一個(gè)問(wèn)題：查詢一個(gè)人的系統(tǒng)消息時(shí)，由于是按月分表，而大多數(shù)查詢都是跨月的(因?yàn)樾枰檎?0天內(nèi)的消息)，所以需要兩次數(shù)據(jù)庫(kù)交互。是否可以優(yōu)化呢?

我們可以冗余存儲(chǔ)，具體優(yōu)化方案如下：

• 插入系統(tǒng)消息時(shí)寫當(dāng)前月和上個(gè)月兩個(gè)表;

• 讀從上一個(gè)月開(kāi)始讀;

這個(gè)方案我們可以保證一次查詢可以找到用戶所有有效期內(nèi)的系統(tǒng)消息，但是通過(guò)犧牲了存儲(chǔ)空間和寫入效率換取的，不一定是最優(yōu)的方案，但在總數(shù)據(jù)量不大，且比較注重查詢性能的業(yè)務(wù)場(chǎng)景下還是可以選用的。

七、總結(jié)

• 自增主鍵性能不一定高，需要結(jié)合實(shí)際業(yè)務(wù)場(chǎng)景做分析;

• 大多數(shù)場(chǎng)景數(shù)據(jù)類型選擇上盡量使用簡(jiǎn)單的類型;

• 索引不是越多越好，太多的索引會(huì)導(dǎo)致過(guò)大的索引文件;

• 如果要查詢的數(shù)據(jù)可以在索引文件中找到，存儲(chǔ)引擎就不會(huì)查找主鍵索引訪問(wèn)實(shí)際記錄。

感謝各位的閱讀，以上就是“MySQL海量存儲(chǔ)的索引與分表設(shè)計(jì)的方法教程”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì)MySQL海量存儲(chǔ)的索引與分表設(shè)計(jì)的方法教程這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是創(chuàng)新互聯(lián)，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！