硬盤這東西平時大家關心的都是我能不能塞下4K電影今天反過來了被人問到一個挺刁鉆的問題1G的硬盤可以存儲多少條MySQL數(shù)據(jù)我第一反應是冷笑一下這問題能答表結(jié)構(gòu)不一樣一行大小能差幾百倍。但細一想這個問題背后其實藏著一連串真正值得搞明白的事MySQL到底怎么在硬盤上組織數(shù)據(jù)一條記錄在物理存儲里占多少字節(jié)字符集、索引、碎片又在中間偷走了多少空間把這些問題拆透了1G能存多少條就不再是一個需要背的答案而是一套你自己能算的賬。這篇文章我就把這套賬從頭捋一遍從InnoDB存儲原理、行格式、頁結(jié)構(gòu)講到實際建表測算和容量規(guī)劃的經(jīng)驗。不管你是剛接觸MySQL的新手還是在為線上庫容量發(fā)愁的運維看完應該都能自己估個八九不離十。1. 先搞清楚MySQL在硬盤上到底存的是什么1.1 一張表背后是B樹不是一行一條記錄的簡單賬很多人想當然地以為MySQL表在硬盤上就是順序一行壓一行存放像Excel表格一樣。實際上InnoDB存儲引擎的默認結(jié)構(gòu)是一棵B樹表數(shù)據(jù)本身是聚簇索引的葉子節(jié)點也就是說每行記錄真實存在B樹最底層的葉子頁里。B樹的特點是數(shù)據(jù)按主鍵有序排列非葉子節(jié)點只存主鍵值和指向下一層頁的指針真正的數(shù)據(jù)行全部排在葉子層。這意味著就算你只存了一行數(shù)據(jù)B樹也要有根節(jié)點頁、分支節(jié)點頁、葉子頁這一整套骨架。每一頁Page默認16KB頁里面除了數(shù)據(jù)行還有頁頭、頁尾、頁目錄這些管理結(jié)構(gòu)永遠不會被填滿到100%。所以1G能存多少行取決于每16KB的頁里平均能塞下多少行而不是簡單拿1G除以單行字節(jié)數(shù)。想準確一點必須把頁頭尾開銷、填充率這些因素都算進去。1.2 頁、區(qū)、段與碎片這些結(jié)構(gòu)偷偷吃掉了空間InnoDB把存儲空間組織成多層最小的單位是頁Page16KB相鄰的64個頁組成一個區(qū)Extent約1MB再往上還有段Segment。這種分層不是閑著沒事干區(qū)是為了保證順序掃描的性能段是為了區(qū)分索引段和葉子數(shù)據(jù)段。這里就出現(xiàn)了一個很多人不關心但很影響容量判斷的事實一張表的最小存儲單位不是一條數(shù)據(jù)而是一個區(qū)。哪怕你只插入一行也可能占用一個完整的區(qū)。不過好在MySQL在數(shù)據(jù)量小的時候會用碎片區(qū)Fragment Extent共享空間不會真的為一行數(shù)據(jù)就獨占1MB。但從幾十萬行往上走表開始以區(qū)為單位申請空間空間的浪費比例就會穩(wěn)定下來。還有一個很隱蔽的開銷每個頁的尾部有校驗值記錄頁頭有偏移鏈表InnoDB為了崩潰恢復和MVCC保留的版本信息都占空間。實踐中大約有6%~10%的頁空間是管理稅。更別說寫滿的頁在DELETE之后不會立刻歸還空間會在頁里留下空洞這些都是后面要仔細算的。2. 單行數(shù)據(jù)的精確體重行格式拆解2.1 定長與變長字段的實際占用要估算行大小先得有張表。我給一個比較典型的用戶表大家順著這個思路去套自己的表就行CREATE TABLE user_info ( id int NOT NULL, name varchar(50) DEFAULT NULL, age tinyint DEFAULT NULL, email varchar(100) DEFAULT NULL, created_at datetime DEFAULT NULL, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;一行數(shù)據(jù)的原始內(nèi)容怎么算idint類型4字節(jié)namevarchar(50)這是變長字段不能按50個字符算。如果用戶名平均10個字符在utf8mb4字符集下最多一個字符4字節(jié)平均一個中文名3~4個字節(jié)通常10個字符約30字節(jié)極端拼音或者數(shù)字可能就10~40字節(jié)先估40字節(jié)保險agetinyint1字節(jié)emailvarchar(100)平均按25個英文字符算utf8mb4下25字節(jié)保守按60字節(jié)算created_atdatetime類型MySQL 5.6后對時間戳壓縮存儲DATETIME通常占用5字節(jié)不帶小數(shù)秒精度時按這個情況用戶數(shù)據(jù)裸重約110字節(jié)。但這還遠遠不是一條記錄真實的硬盤占用因為InnoDB會對每行額外加兩樣東西。2.2 隱藏列與記錄頭每行都有管理費InnoDB的每行記錄在物理存儲時除了業(yè)務字段還會帶上記錄頭信息Record Header約5字節(jié)包含記錄類型、下一條記錄的偏移量、是否已刪除標記等DB_TRX_ID事務ID6字節(jié)InnoDB用它做MVCC并發(fā)控制DB_ROLL_PTR回滾指針7字節(jié)指向undo日志中該行的舊版本DB_ROW_ID行ID6字節(jié)只在表沒有主鍵時才會額外生成這里表有主鍵就不用這三項加起來約18字節(jié)和業(yè)務數(shù)據(jù)110字節(jié)加在一起一行就是128字節(jié)左右。還沒完。varchar變長字段需要用變長字段長度列表記錄每個變長字段的長度至少2字節(jié)NULL值字段需要在NULL標志位里占位每8個可空字段1字節(jié)還有utf8mb4字符集的字符集轉(zhuǎn)換信息。這又得加上5~10字節(jié)。這么一算user_info表中平均一行實際存儲在數(shù)據(jù)頁里的字節(jié)數(shù)大約在135~145字節(jié)。我們?nèi)?40字節(jié)作基準往下算。2.3 字符集陷阱utf8mb4比你想的更吃空間上面那個例子如果用戶名和郵箱都是中文每個漢字在utf8mb4下占4字節(jié)一個varchar(50)的字段如果50個全是中文那就是200字節(jié)單人行的體重大概能翻兩倍。這些年做業(yè)務遇到過太多次這種翻車現(xiàn)場開發(fā)圖省事全庫統(tǒng)一utf8mb4結(jié)果一張只有五六個字段的短文本表單行居然奔著300字節(jié)去了。在MySQL 8.0里utf8mb4還是默認字符集這個坑更隱蔽。反過來如果字段內(nèi)容是純英文和數(shù)字用latin1或ascii一個字符只占1字節(jié)比utf8mb4省3倍。所以估算容量前先確認你用的字符集這個直接決定一行能差幾倍。2.4 varchar超過半個頁存儲方式就變了那如果一行數(shù)據(jù)里有個大字段比如Text類型的文章內(nèi)容或者特別長的varchar呢InnoDB在DYNAMIC行格式下當單行數(shù)據(jù)過大、一頁16KB放不下的時候會把大字段部分內(nèi)容溢出到獨立的溢出頁存儲原數(shù)據(jù)頁里只保留20字節(jié)的指針和前綴信息。這意味著存10KB文章和存1MB文章實際對單行的頁內(nèi)體積影響并沒有想象中大因為大頭都被挪到溢出頁了溢出頁也是16KB一頁結(jié)算的。但千萬不要因此覺得大字段不要錢多一個大字段等于每行額外多出至少一個溢出頁而一個溢出頁被一行獨占空間浪費非?？鋸?。所以估算行大小的時候如果表里有TEXT或超長varchar建議按原始字節(jié)/16KB后向上取整重新計算單行的實際頁成本而不是直接拿原始長度相加。3. 一個真實的估算從建表到跑數(shù)據(jù)3.1 先給出一個可以套用的估算公式核對了InnoDB頁結(jié)構(gòu)和行格式后我自己的估算公式是可存儲行數(shù) ≈ 可用空間字節(jié)數(shù) × 頁填充系數(shù) ÷ 單行實際占用字節(jié)數(shù)可用空間字節(jié)數(shù)1GB 1,073,741,824字節(jié)頁填充系數(shù)InnoDB的頁不會寫滿受頁目錄和填充策略影響留大約0.9比較穩(wěn)單行實際占用字節(jié)數(shù)按上面的方法把業(yè)務字段、記錄頭、隱藏列、變長長度、NULL位圖全部算進去如果用我們那張user_info表一行按140字節(jié)算1,073,741,824 × 0.9 ÷ 140 ≈ 6,898,000 行也就是說1GB大概能放接近700萬行。這個數(shù)值和實際情況其實是吻合的我拿類似結(jié)構(gòu)的表跑過測試單行較小的表1GB確實能存到百萬行量級只是不同表結(jié)構(gòu)偏差很大。3.2 不同類型表在1GB下的參考值給幾類常見業(yè)務表做個估算方便大家心里有個錨點表類型典型單行頁內(nèi)占用1GB可存儲行數(shù)約說明精簡日志表bigint主鍵 短varchar80字節(jié)1200萬行純英文、字段極少用戶表int主鍵 姓名郵箱時間140字節(jié)690萬行中文字符集、常規(guī)字段訂單表多關聯(lián)字段 狀態(tài) 金額400字節(jié)240萬行索引多、字段十來個文章內(nèi)容表含TEXT大字段1個16KB溢出頁6萬行以內(nèi)大字段嚴重拉低容量這里的文章內(nèi)容表最極端假設每篇文章正文平均2KB非要用TEXT存InnoDB通常會為超過頁容量閾值的大字段分配單獨頁幾萬行就能吃掉1GB。所以1GB能存多少條MySQL數(shù)據(jù)從幾萬到上千萬都有核心變量就兩個字行重和索引開銷。3.3 輔助索引容量估算中最容易漏算的一項很多人在估算表大小時只看數(shù)據(jù)行卻忘了主鍵之外建的那些索引二級索引也是要占硬盤的。二級索引的B樹葉子節(jié)點并不存整行數(shù)據(jù)而是存索引列 主鍵值。表面上看起來省空間但索引數(shù)量一多空間占用就會非常可觀。比如一張訂單表你給訂單號、用戶ID、商品ID、支付狀態(tài)各建一個索引四個二級索引加起來可能會讓整張表的物理文件膨脹60%~100%。更麻煩的是輔助索引對varchar字段比較敏感。我給一個案例一張日志表原本只有數(shù)據(jù)16GB按數(shù)據(jù)行算后來給一個冗余的長request_id字段加了索引全部大小直接從16GB漲到27GB。容量看起來是夠的但因為一個索引直接爆了。所以在做1G能存多少行這類估算時別只看SELECT COUNT(*)那樣的邏輯行數(shù)要看data_length index_length。有條件的話直接用SHOW TABLE STATUS或者查information_schema.tables查看實際占用這是最靠譜的。4. 容量是算出來了但還有一堆你沒想到的隱形硬盤消耗4.1 共享表空間、redo log、undo log也要吃蛋糕就算你精確算出了某張表在某個行格式下能存多少行實際部署MySQL時1GB的硬盤還不能全都分給用戶表ibdata1系統(tǒng)表空間存數(shù)據(jù)字典、change buffer等MySQL 8.0里初始化后占用約12MB起步后續(xù)可能自動增長redo logMySQL 8.0默認innodb_redo_log_capacity是100MB一啟動就占了undo log默認會分配獨立的undo表空間初始約16MB事務量大還要擴展binlog如果開啟寫滿的日志不是自動清除而是累積的這是最容易被忽略的空間炸彈doublewrite buffer默認開啟時每個數(shù)據(jù)頁寫盤時要先寫雙寫緩沖區(qū)通常占用2個區(qū)約2MB一圈扣下來1GB的實際可用空間保守要打八到九折真正能用來存業(yè)務數(shù)據(jù)的大概只有800~900MB甚至更少。我自己在低配置測試環(huán)境里踩過一次給一個1GB小盤裝MySQL 8.0還沒建業(yè)務表先是看du -sh /var/lib/mysql已經(jīng)占掉200多MB把redo和undo以及系統(tǒng)表全算進去可用空間比想象中緊張得多。所以嚴格講標題里的1G硬盤指的是MySQL數(shù)據(jù)目錄總體容量時用戶表可用的部分必須扣除這些固定開銷。4.2 頻繁DELETE和UPDATE是空間刺客InnoDB刪除數(shù)據(jù)時并不會立刻把物理空間歸還給操作系統(tǒng)。刪除的行只是被標記為已刪除留下的空間可以被新數(shù)據(jù)復用。如果業(yè)務有大量隨機刪除或者按時間范圍大批量清數(shù)據(jù)表文件可能一直保持一個虛胖的狀態(tài)。統(tǒng)計信息更離譜DELETE后information_schema.tables.data_length不會馬上下降因為頁里的空洞還在。如果刪完馬上繼續(xù)插入新數(shù)據(jù)可能會填充這些空洞那還可以但如果刪完不寫那個空出來的空間就閑置了數(shù)據(jù)庫文件大小沒變實際可用容量卻已經(jīng)被地主圈了地。解決方法是OPTIMIZE TABLE或者ALTER TABLE ... ENGINEInnoDB重建表但重建需要額外的臨時磁盤空間在容量緊張的場景要特別注意重建失敗反而會雪上加霜。還有一個細節(jié)隨機UPDATE變長字段比如varchar改成更長的值如果新值放不進原位置InnoDB會嘗試在頁內(nèi)移動或者把行標記刪除后另起新位置同樣會制造頁碎片。這些碎片累積下來1GB物理空間能容納的行數(shù)比干凈表少10%~20%。4.3 怎么用information_schema算真實的賬不想憑感覺估算直接查數(shù)據(jù)庫給的統(tǒng)計值最穩(wěn)SELECT table_name, engine, ROUND(data_length/1024/1024, 2) AS data_mb, ROUND(index_length/1024/1024, 2) AS index_mb, ROUND((data_length index_length)/1024/1024, 2) AS total_mb, table_rows, ROUND(avg_row_length, 0) AS avg_row_bytes FROM information_schema.tables WHERE table_schema your_database;avg_row_length是MySQL根據(jù)data_length / table_rows算出來的平均行字節(jié)數(shù)。這個值和我在第二節(jié)手工拆解出的140字節(jié)大致能對上。但要注意它是包含數(shù)據(jù)頁內(nèi)管理開銷在內(nèi)的平均值比純業(yè)務字段裸重要大。拿這個表的avg_row_length做容量估算比手工算要簡單得多剩余可用空間 / avg_row_length得出的行數(shù)就是基于當前碎片狀態(tài)下的真實容納量。如果表還沒建好那就只能手工按我們前面的公式估了。5. 我的經(jīng)驗怎么讓1G裝下更多行且不翻車5.1 字段設計上能摳就摳在存儲資源緊張時字段設計是最大的杠桿能用smallint/tinyint的就不用int狀態(tài)碼、枚舉值這類根本不需要4字節(jié)主鍵盡量用bigint自從增或業(yè)務ID不要用varchar類型的UUID做主鍵UDPATE前綴這樣的字符主鍵不僅讓聚簇索引膨脹每個二級索引都會帶上一份吃空間極為兇殘字符串字段看清需求varchar(255)在索引列上會導致部分字符集下索引失效還會讓行格式里的變長長度列表多占用字節(jié)。能用varchar(20)就絕不varchar(255)IP地址用INET_ATON()轉(zhuǎn)成無符號INT存比varchar(15)省一半還多日期時間能用DATE就不用DATETIME精度用不到秒就不要DATETIME(6)我用一個實時統(tǒng)計表做過對比無腦int varchar datetime設計單行裸重約180字節(jié)優(yōu)化后單行裸重壓到不到80字節(jié)容量直接翻倍讀性能也更好。5.2 行格式選擇DYNAMIC還是COMPRESSED當前主流行格式是DYNAMICMySQL 8.0默認適合大部分OLTP場景大字段溢出做得高效行大小控制得好。如果1GB空間實在緊張可以考慮把歸檔類表改成COMPRESSED行格式它會對數(shù)據(jù)頁做壓縮空間節(jié)省可觀。但壓縮有代價寫入時額外消耗CPU讀取時也要解壓。OLTP高頻表用了壓縮反而容易成為性能瓶頸這個折中要權衡清楚。我一般只在冷數(shù)據(jù)表或用ZOOKEEPER這樣的歸檔場景才啟用壓縮。對于1GB這種小空間極限場景還有個土辦法引文.類數(shù)據(jù)量很小的監(jiān)控/流水表直接把ROW_FORMATCOMPRESSED和KEY_BLOCK_SIZE8寫上有時候表體積能縮小40%以上。注意KEY_BLOCK_SIZE不是越小越好太小會導致壓縮后還是超頁InnoDB性能下降。5.3 給容量規(guī)劃留20%余量是不成文的規(guī)矩最后一個經(jīng)驗任何容量規(guī)劃都別按裝滿來設計。數(shù)據(jù)庫的空間是動態(tài)波動的binlog、臨時表、多版本數(shù)據(jù)、后臺刷盤都可能臨時占用空間。一旦磁盤滿了MySQL拒絕寫入會產(chǎn)生大量報警甚至導致復制中斷恢復起來比擴容麻煩得多。我個人習慣的規(guī)劃公式是實際可用容量 硬盤總?cè)萘?× 0.8100GB盤就按80GB可用去設計1GB盤實際可用就當800MB。這個余量不僅給系統(tǒng)開銷兜底也給突發(fā)事件留退路。還有一個小技巧給每個業(yè)務表預估一個月的數(shù)據(jù)增長量把容量規(guī)劃從今天能不能裝下升級成半年后會不會存滿。一張月增500萬行的日志表每行200字節(jié)每個月就要吃掉1GB。如果不提前規(guī)劃三個月后再來看反應時間都沒有。最后再分享一個我自己的測算過程我最近幫人評估一個內(nèi)存極其有限的小機器準備把一部分數(shù)據(jù)從大庫導到一個磁盤只有1GB的從庫上做離線分析。當時表里有大字段有多個索引還開了一個月binlog用前面的方法粗略算了一下單行頁內(nèi)占用超過300字節(jié)六級評估下來1GB最多容納200萬行實際導入了190萬行后表空間已經(jīng)占到88%正好卡在預警線內(nèi)。過程里踩了幾個坑一是沒把redo log提前計入二是低估了二級索引三是忘了binlog也在同一塊盤上增長。把這三項補進去后估算和實際誤差基本在5%以內(nèi)。這個項目的后續(xù)是我建議把幾張大字段表拆分出來文章正文單獨存到對象存儲MySQL里只留ID和摘要這樣單行立刻回到120字節(jié)以內(nèi)同樣1GB就能繼續(xù)扛幾百萬行。如果你也遇到小硬盤裝大表的問題優(yōu)先檢查的應該就是這三個方向冗余的大字段、貪多的索引、開著卻不留余量的binlog。把它們剪干凈了你會發(fā)現(xiàn)1GB能比你想象中裝下更多數(shù)據(jù)。