字符編碼不同情況下的存儲(chǔ)（重要）

char 、varchar、text 等需要設(shè)置字符編碼的類型，在計(jì)算所占用空間時(shí)，需要考慮不同編碼所占用的空間。

• varchar、text等類型會(huì)有長(zhǎng)度字段列表來(lái)記錄他們所占用的長(zhǎng)度，但char是固定長(zhǎng)度的類型，情況比較特殊，假設(shè)字段 name 的類型為 char(10) ，則有以下情況：
• 對(duì)于長(zhǎng)度固定的字符編碼（比如ASCII碼），字段 name 將以固定長(zhǎng)度格式存儲(chǔ)，ASCII碼每個(gè)字符占一個(gè)字節(jié)，那 name 就是占用 10 個(gè)字節(jié)。
• 對(duì)于長(zhǎng)度不固定的字符編碼（比如utf8mb4），至少將為 name 保留 10 個(gè)字節(jié)。如果可以，InnoDB會(huì)通過(guò)修剪尾部空格空間的方式來(lái)將其存到 10 個(gè)字節(jié)中。
  如果空格剪完了還存不下，則將尾隨空格修剪為 列值字節(jié)長(zhǎng)度的最小值（一般是 1 字節(jié)）。
  列的最大長(zhǎng)度為： 字符編碼的最大字符長(zhǎng)度×N，比如 name 字段的編碼為 utf8mb4，那就是 4×10。
• 大于或等于 768 字節(jié)的 char 列會(huì)被看成是可變長(zhǎng)度字段（就像varchar一樣），可以跨頁(yè)存儲(chǔ)。例如，utf8mb4 字符集的最大字節(jié)長(zhǎng)度為 4，則 char(255) 列將可能會(huì)超過(guò) 768 個(gè)字節(jié)，進(jìn)行跨頁(yè)存儲(chǔ)。

總結(jié)一下：

字符編碼不同，字段所占用的字節(jié)也會(huì)不同，固定字符編碼（比如ASCII碼）就是寫(xiě)多少就是多少，char(10)，就占用10個(gè)字節(jié)，不固定的（比如utf8mb4）超過(guò)10個(gè)字節(jié)就用字符編碼的最大字符長(zhǎng)度（utf8mb4的這個(gè)值是4，結(jié)尾的mb4就代表4個(gè)字節(jié)，這個(gè)可以自己去查）x N，那么char(20)占用字節(jié)=4 x 20 =80，如果結(jié)果大于768，那么就會(huì)跨頁(yè)存儲(chǔ)（知道就行）。

計(jì)算

計(jì)算InnoDB的3層B+樹(shù)最多可以存多少條數(shù)據(jù)
計(jì)算之前，先回顧一下前邊的東西，頁(yè)格式和行格式；
存儲(chǔ)總量 = 可變數(shù)據(jù) + 固定數(shù)據(jù);
可變數(shù)據(jù)就是我們實(shí)際要存儲(chǔ)的數(shù)據(jù)，固定數(shù)據(jù)就是內(nèi)置好的數(shù)據(jù)，從上邊的兩個(gè)表格就能看到了。

通過(guò)頁(yè)格式的介紹，我們知道，刨除固定數(shù)據(jù)量之后，我們得到的剩余存儲(chǔ)空間是15232字節(jié)。

那么，我們開(kāi)始計(jì)算不固定的數(shù)據(jù)量，也就是我們要存儲(chǔ)的實(shí)際信息，這個(gè)就只能舉例說(shuō)明了。

• 簡(jiǎn)單一例：

先計(jì)算單個(gè)節(jié)點(diǎn)的：
假設(shè)我們的主鍵id為 bigint 型，也就是8個(gè)字節(jié)；
那索引頁(yè)中每行數(shù)據(jù)占用的空間就等于 8 + 6（事物ID[上表有寫(xiě)]） + 5（行記錄頭） = 19 字節(jié)。
每頁(yè)可以存 15232 ÷ 19 ≈ 801 條索引數(shù)據(jù)。
那算上頁(yè)目錄的話，按每個(gè)槽平均6條數(shù)據(jù)計(jì)算的話，至少有 801 ÷ 6 ≈ 134 個(gè)槽，需要占用 268 字節(jié)的空間。
所以最終的結(jié)果是（15232-268）÷ 19 ≈ 787條索引數(shù)據(jù)；
三層的數(shù)據(jù)量：
三層的葉子節(jié)點(diǎn)就是7872（反正就是這么算的，不用管，記住公式），也就是787x787= 619369 個(gè)葉子節(jié)點(diǎn)，每個(gè)節(jié)點(diǎn)可以存儲(chǔ)787條數(shù)據(jù)，最終能存儲(chǔ) 787 x 619369 = （自己算）條數(shù)據(jù)。
主鍵為 int 的表可以存放 993 ^ 2 = 986049 個(gè)葉子節(jié)點(diǎn)，下邊會(huì)用。

• 簡(jiǎn)單二例：

– 這是一張非常普通的課程安排表，除id外，僅包含了課程id和老師id兩個(gè)字段
– 且這幾個(gè)字段均為 int (4個(gè)字節(jié))型（當(dāng)然實(shí)際生產(chǎn)中不會(huì)這么設(shè)計(jì)表，這里只是舉例）。
CREATE TABLE course_schedule (
id int NOT NULL,
teacher_id int NOT NULL,
course_id int NOT NULL,
PRIMARY KEY (id) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

每行數(shù)據(jù)所占用的空間就是 4 + 4 + 4 + 6 + 7 + 5 = 30 字節(jié)，每個(gè)葉子節(jié)點(diǎn)可以存放 15232÷30≈507條數(shù)據(jù)。
頁(yè)目錄占用空間：507 ÷ 6 ≈ 85 x 2 = 170字節(jié)
算上頁(yè)目錄的槽位所占空間，每個(gè)葉子節(jié)點(diǎn)可以存放 (15232 - 170) ÷ 30 ≈ 502 條數(shù)據(jù);
那么三層B+樹(shù)可以存放的最大數(shù)據(jù)量就是 502×986049(主鍵是int)=494,996,將近5億條數(shù)據(jù)！

• 常規(guī)一例：

CREATE TABLE blog (
id bigint unsigned NOT NULL AUTO_INCREMENT COMMENT ‘博客id’,
author_id bigint unsigned NOT NULL COMMENT ‘作者id’,
title varchar(50) CHARACTER SET utf8mb4 NOT NULL COMMENT ‘標(biāo)題’,
description varchar(250) CHARACTER SET utf8mb4 NOT NULL COMMENT ‘描述’,
school_code bigint unsigned DEFAULT NULL COMMENT ‘院校代碼’,
cover_image char(32) DEFAULT NULL COMMENT ‘封面圖’,
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘創(chuàng)建時(shí)間’,
release_time datetime DEFAULT NULL COMMENT ‘首次發(fā)表時(shí)間’,
modified_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘修改時(shí)間’,
status tinyint unsigned NOT NULL COMMENT ‘發(fā)表狀態(tài)’,
is_delete tinyint unsigned NOT NULL DEFAULT 0,
PRIMARY KEY (id),
KEY author_id (author_id),
KEY school_code (school_code) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci ROW_FORMAT=DYNAMIC;

1、行記錄頭信息：肯定得有，占用5字節(jié)。
2、可變長(zhǎng)度字段列表：表中 title占用1字節(jié)，description占用2字節(jié)（雖然看著沒(méi)超過(guò)255，但是用的是utf8mb4編碼，所以實(shí)際是255 x 4,所以占用兩個(gè)字節(jié)），共3字節(jié)。
3、null值列表：表中僅school_code、cover_image、release_time3個(gè)字段可為null，故僅占用1字節(jié)。
4、事務(wù)ID和指針字段：兩個(gè)都得有，占用13字節(jié)。
5、字段內(nèi)容信息：

• id、author_id、school_code 均為bigint型，各占用8字節(jié)，共24字節(jié)。
• create_time、release_time、modified_time 均為datetime類型，各占8字節(jié)，共24字節(jié)。
• status、is_delete 為tinyint類型，各占用1字節(jié)，共2字節(jié)。
• cover_image 為char(32)，字符編碼為表默認(rèn)值utf8，由于該字段實(shí)際存的內(nèi)容僅為英文字母（存url的），結(jié)合前面講的字符編碼不同情況下的存儲(chǔ) ，故僅占用32字節(jié)。
• title、description 分別為varchar(50)、varchar(250)，這兩個(gè)應(yīng)該都不會(huì)產(chǎn)生溢出頁(yè)（不太確定），字符編碼均為utf8mb4，實(shí)際生產(chǎn)中70%以上都是存的中文(3字節(jié))，25%為英文(1字節(jié))，還有5%為4字節(jié)的表情，則存滿的情況下將占用：
  (50 + 250) × ( 0.7 × 3 + 0.25 × 1 + 0.05 × 4) = 765 字節(jié)。

統(tǒng)計(jì)上面的所有分析，共占用 869 字節(jié)，則每個(gè)葉子節(jié)點(diǎn)可以存放 15232 ÷869≈17條，算上頁(yè)目錄，仍然能放 17 條。
則三層B+樹(shù)可以存放的最大數(shù)據(jù)量就是 17 × 619369（根據(jù)主鍵是bigint） = 10,529,27317，約一千萬(wàn)條數(shù)據(jù)

文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-428600.html

到了這里，關(guān)于你的mysql到底能存多少數(shù)據(jù)呢？的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！