網(wǎng)上流傳的MySQL單表數(shù)據(jù)量不要超過(guò)兩千萬(wàn)到底有何理論依據(jù)？
當(dāng)前位置：點(diǎn)晴教程→知識(shí)管理交流
→『 技術(shù)文檔交流 』
作為一個(gè)合格的 DBA，在遇到線上單表數(shù)據(jù)量超過(guò)千萬(wàn)級(jí)別的時(shí)候，往往會(huì)建議用戶通過(guò)分表來(lái)縮減單表數(shù)據(jù)量，當(dāng)用戶問(wèn)為什么單表數(shù)據(jù)量不能超過(guò)千萬(wàn)時(shí)，DBA 往往會(huì)說(shuō)：?jiǎn)伪頂?shù)據(jù)量超過(guò)千萬(wàn)，會(huì)影響查詢性能。 知其然而不知所以然，學(xué)習(xí)技術(shù)不能停留在表面，而是要進(jìn)一步去深入挖掘其中的原理，這樣才能不斷進(jìn)步和成長(zhǎng)?；氐竭@個(gè)問(wèn)題：為什么單表數(shù)據(jù)量不能超過(guò)兩千萬(wàn)，其中的依據(jù)是什么？ 事情是這樣的： 小王最近參加了騰訊的技術(shù)面試，面試官向他提了一個(gè)經(jīng)典的面試問(wèn)題：聊聊你日常項(xiàng)目里的分庫(kù)分表實(shí)踐？ 于是小王以過(guò)往項(xiàng)目里的某個(gè) case 為例做了回答： 我負(fù)責(zé)的項(xiàng)目里涉及到存儲(chǔ)用戶操作記錄的功能，因?yàn)槊刻斓臄?shù)據(jù)量比較大，差不多超過(guò) 5000 萬(wàn)條，所以我另外又做了分庫(kù)分表的操作。系統(tǒng)會(huì)自動(dòng)定時(shí)生成 3 張表，數(shù)據(jù)分別存儲(chǔ)其中，防止都放在一個(gè)表里面導(dǎo)致查詢性能降低。 面試官又問(wèn)：這里為什么要做一個(gè)分庫(kù)分表的操作呢？如果放在同一張表里面，為什么會(huì)導(dǎo)致查詢性能降低？ 小王內(nèi)心 OS：為什么1+1=2？但他還是語(yǔ)氣平常地回答說(shuō)： MySQL 單表不要超過(guò) 2000 萬(wàn)行基本上是一個(gè)行業(yè)共識(shí)，只有當(dāng)單表行數(shù)超過(guò) 500 萬(wàn)行或者單表容量超過(guò) 2GB，我們一般才推薦進(jìn)行分庫(kù)分表。 面試官點(diǎn)了點(diǎn)頭表示認(rèn)可，卻也沒(méi)有在這個(gè)問(wèn)題上繼續(xù)深究，繼而問(wèn)起了別的問(wèn)題，不久后就結(jié)束了面試。小王回過(guò)神來(lái)以后復(fù)盤這次面試過(guò)程，覺(jué)得自己在 MySQL 分庫(kù)分表問(wèn)題上沒(méi)有回答得特別到位，于是他開(kāi)始進(jìn)一步地深究起來(lái)這個(gè)“1+1=2”的問(wèn)題。 一、自增主鍵角度 我們先來(lái)看看單表數(shù)據(jù)量理論上最大值是多少？ 假設(shè)我們建表，ID 是自增主鍵，也就是說(shuō)主鍵的大小可以限制表的上限。如果主鍵聲明為 int 類型，那么 int 類型最大為2的32次方 – 1 ，也就是21億左右； 如果主鍵聲明為 bigint 類型，那么 bigint 類型最大為2的64次方 – 1，這個(gè)數(shù)字實(shí)在太大了，一般還沒(méi)到這個(gè)限制，磁盤就撐不住了； 如果主鍵聲明為tinyint類型，那么 tinyint 類型最大為2的8次方 – 1，也就是255，所以如果我插入一條 ID=256 的數(shù)據(jù)，就會(huì)報(bào)錯(cuò)； 上面是從自增主鍵的角度來(lái)講述單表最大數(shù)據(jù)量理論上能達(dá)到多少，那么接下來(lái)從另一個(gè)角度“數(shù)據(jù)頁(yè)”來(lái)闡述一下，單表數(shù)據(jù)量最大能達(dá)到多少，依據(jù)是什么？ 二、數(shù)據(jù)頁(yè)角度 假設(shè)我們有一張 user 表，其中 ID 是自增主鍵，那么該表在硬盤文件上是 user.ibd（innodb 數(shù)據(jù)文件，又叫表空間文件）。這個(gè)數(shù)據(jù)文件被劃分成很多的數(shù)據(jù)頁(yè)，每個(gè)數(shù)據(jù)頁(yè)大小是16K。
即數(shù)據(jù)頁(yè)的結(jié)構(gòu)如下：
數(shù)據(jù)是以數(shù)據(jù)頁(yè)的形式進(jìn)行存儲(chǔ)，數(shù)據(jù)頁(yè)和數(shù)據(jù)頁(yè)之間是以B+樹(shù)的形式進(jìn)行關(guān)聯(lián)，例如：
其中，葉子節(jié)點(diǎn)的數(shù)據(jù)頁(yè)存放的是實(shí)際存儲(chǔ)的數(shù)據(jù)，非葉子節(jié)點(diǎn)存放的是索引內(nèi)容。B+樹(shù)的每一層代表一次磁盤 IO。 舉個(gè)例子，如果我要尋找 ID=5 的記錄，從頂部非葉子節(jié)點(diǎn)開(kāi)始查找，由于 ID=5 大于1并且小于7，故應(yīng)該往左邊尋找，來(lái)到頁(yè)號(hào)為6的數(shù)據(jù)頁(yè)，由于5大于4，故應(yīng)該往右邊尋找，來(lái)到頁(yè)號(hào)為105的數(shù)據(jù)頁(yè)，找到 ID=5 的記錄，完成查詢。 這個(gè)過(guò)程中查詢了三個(gè)數(shù)據(jù)頁(yè)，如果這三個(gè)數(shù)據(jù)頁(yè)都沒(méi)有加載到內(nèi)存，那么就需要經(jīng)歷三次磁盤 IO 查詢。 了解完 B+樹(shù)是如何存儲(chǔ)數(shù)據(jù)的，我們就可以開(kāi)始進(jìn)行數(shù)據(jù)的估算。
假設(shè)：非葉子節(jié)點(diǎn)內(nèi)指向其他數(shù)據(jù)頁(yè)的指針數(shù)量為 X（即非葉子節(jié)點(diǎn)的最大子節(jié)點(diǎn)數(shù)為 X）；每個(gè)葉子節(jié)點(diǎn)可以存儲(chǔ)的行記錄數(shù)為 Y；B+樹(shù)的高度為 N（即 B+樹(shù)的層數(shù)）；
代入計(jì)算：
綜上所述，我們建議單表數(shù)據(jù)量大小在兩千萬(wàn)。當(dāng)然這個(gè)數(shù)據(jù)是根據(jù)每條行記錄的大小為 1K 的時(shí)候估算而來(lái)的，而實(shí)際情況中可能并不是這個(gè)值，所以這個(gè)建議值兩千萬(wàn)只是一個(gè)建議，而非一個(gè)標(biāo)準(zhǔn)。 三、思考 最后考一個(gè)問(wèn)題：一個(gè)4層的 B+樹(shù)，主鍵是 bigint 型，一條記錄平均長(zhǎng)度是1K，不考慮碎片，能存放多少條記錄？ 答案： 根據(jù) B+樹(shù)存儲(chǔ)數(shù)據(jù)的計(jì)算公式：M = X 的 N-1 次方 * Y： 一個(gè)數(shù)據(jù)頁(yè)大小16K，扣除頁(yè)號(hào)、前后指針、頁(yè)目錄，校驗(yàn)碼等信息，實(shí)際可以存儲(chǔ)數(shù)據(jù)的大約為15K，假設(shè)主鍵 ID 為 bigint 型，那么主鍵 ID 占用8個(gè) byte，頁(yè)號(hào)占用4個(gè)byte，則X=15*1024/(8 + 4) 等于1280； 每條記錄1K大小，一個(gè)數(shù)據(jù)頁(yè)有15K是用來(lái)存儲(chǔ)數(shù)據(jù)的，那么一個(gè)數(shù)據(jù)頁(yè)就能存儲(chǔ)15條記錄； 所有葉子節(jié)點(diǎn)數(shù)量為 X 的 N-1 次方，即1280*1280*1280；存儲(chǔ)的記錄數(shù)總數(shù)為：葉子節(jié)點(diǎn)數(shù)量 * 每個(gè)葉子節(jié)點(diǎn)存儲(chǔ)的記錄數(shù)，所以 M = 1280*1280*1280*15。 你，學(xué)會(huì)了嗎？ 該文章在 2024/3/15 15:19:46 編輯過(guò) |
關(guān)鍵字查詢
相關(guān)文章
正在查詢... 
|
400 186 1886
