56,678
社区成员
发帖
与我相关
我的任务
分享MySQL 占用空间的疑问
1：字符集latin1，字符类型 int，存储引擎MYISAM
结构：
mysql> desc a;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
向表中插入1条记录，MYD文件由0变成7
-rw-rw---- 1 mysql mysql 8.4K 09-27 18:32 a.frm
-rw-rw---- 1 mysql mysql 7 09-27 21:52 a.MYD
-rw-rw---- 1 mysql mysql 1.0K 09-27 21:52 a.MYI
再像表中插入1条记录，MYD文件由7变成14
-rw-rw---- 1 mysql mysql 8.4K 09-27 18:32 a.frm
-rw-rw---- 1 mysql mysql 14 09-27 21:52 a.MYD
-rw-rw---- 1 mysql mysql 1.0K 09-27 21:52 a.MYI
疑问：
理论上int 是占用4个字节的，为什么上面测试下来要占用7个字节，并且以7字节增加？难道还有存一些 行号 等相关信息吗？
2：字符集latin1，字符类型 varchar/char，存储引擎MYISAM
表结构：
mysql> desc ttt;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
mysql> desc test_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(10) | NO | | | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)
向上面的表中插入一条数据“abc”，并查看其MYD大小。
-rw-rw---- 1 mysql mysql 8.4K 09-27 21:43 test_table.frm
-rw-rw---- 1 mysql mysql 20 09-27 22:00 test_table.MYD
-rw-rw---- 1 mysql mysql 1.0K 09-27 22:00 test_table.MYI
-rw-rw---- 1 mysql mysql 8.4K 09-27 21:44 ttt.frm
-rw-rw---- 1 mysql mysql 11 09-27 22:00 ttt.MYD
-rw-rw---- 1 mysql mysql 1.0K 09-27 22:00 ttt.MYI
疑问：
为什么在varchar中占用的空间要20字节，而char 的需要11字节？
理论上来说，上面的varchar上应该占用4个字节，而char中应该是10个字节。
再插入一条数据“csdn”并查看其MYD大小。
-rw-rw---- 1 mysql mysql 8.4K 09-27 21:43 test_table.frm
-rw-rw---- 1 mysql mysql 40 09-27 22:02 test_table.MYD
-rw-rw---- 1 mysql mysql 1.0K 09-27 22:02 test_table.MYI
-rw-rw---- 1 mysql mysql 8.4K 09-27 21:44 ttt.frm
-rw-rw---- 1 mysql mysql 22 09-27 22:02 ttt.MYD
-rw-rw---- 1 mysql mysql 1.0K 09-27 22:02 ttt.MYI
varhcar类型的表以20字节的大小增加，char 类型的表以11字节的大小增加。
想通过这个方法来计算表占用的空间大小，但是和理论差距很大，不知道哪里出问题了。
求指教！
...全文
请发表友善的回复…
发表回复
小小小小周 2011-10-13
- 打赏
- 举报
非常感谢大家的回答，特别的zuoxingyu 大哥，哈哈。测试这么久。最后还得出了自己的看法。
这精神值得学习，哈哈！
我也总结了第2个问题的观点，
http://blog.csdn.net/mr_mablevi/article/details/6870778 ，有兴趣的同学去看看，不知道是对是错。
大家相互学习。
这精神值得学习，哈哈！
我也总结了第2个问题的观点，
http://blog.csdn.net/mr_mablevi/article/details/6870778 ，有兴趣的同学去看看，不知道是对是错。
大家相互学习。
ACMAIN_CHM 2011-10-12
- 打赏
- 举报
。[Quote=MySQL 5.1参考手册]15.1.3. MyISAM表的存储格式
15.1.3.1. 静态（固定长度）表特征
15.1.3.2. 动态表特征
15.1.3.3. 已压缩表特征
MyISAM支持三种不同存储格式。其中两个（固定格式和动态格式)根据正使用的列的类型来自动选择。第三个，即已压缩格式，只能使用myisampack工具来创建。
当你CREATE或ALTER一个没有BLOB或TEXT列的表，你可以用ROW_FORMAT表选项强制表的格式为FIXED或DYNAMIC。这会导致CHAR和VARCHAR列因FIXED格式变成CHAR，或因DYNAMIC格式变成VARCHAR。
通过用ALTER TABLE指定ROW_FORMAT={COMPRESSED | DEFAULT}，你可以压缩或解压缩表，请参阅13.1.5节，“CREATE TABLE语法”。
15.1.3.1. 静态（固定长度）表特征
静态格式是MyISAM表的默认存储格式。当表不包含变量长度列（VARCHAR, BLOB, 或TEXT）时，使用这个格式。每一行用固定字节数存储。
MyISAM的三种存储格式中，静态格式就最简单也是最安全的（至少对于崩溃而言）。静态格式也是最快的on-disk格式。快速来自于数据文件中的行在磁盘上被找到的容易方式：当按照索引中的行号查找一个行时，用行长度乘以行号。同样，当扫描一个表的时候，很容易用每个磁盘读操作读一定数量的记录。
当MySQL服务器正往一个固定格式MyISAM文件写的时候，如果计算机崩溃了，安全是显然的。在这种情况下，myisamchk可以容易地决定每行从哪里开始到哪里结束，所以它通常可以收回所有记录，除了写了一部分的记录。注意，基于数据行，MyISAM表索引可以一直被重新构建。
静态格式表的一般特征：
· CHAR列对列宽度是空间填补的。
· 非常快。
· 容易缓存。
· 崩溃后容易重建，因为记录位于固定位置。
· 重新组织是不必要的，除非你删除巨量的记录并且希望为操作系统腾出磁盘空间。为此，可使用OPTIMIZE TABLE或者myisamchk -r。
· 通常比动态格式表需要更多的磁盘空间。
15.1.3.2. 动态表特征
如果一个MyISAM表包含任何可变长度列（VARCHAR, BLOB或TEXTDynamic），或者如果一个表被用ROW_FORMAT=DYNAMIC选项来创建，动态存储格式被使用。
这个格式更为复杂一点，因为每行有一个表明行有多长的头。当一个记录因为更新的结果被变得更长，该记录也可以在超过一个位置处结束。
你可以使用OPTIMIZE TABLE或myisamchk来对一个表整理碎片。如果在一个表中有你频繁访问或改变的固定长度列，表中也有一些可变长度列，仅为避免碎片而把这些可变长度列移到其它表可能是一个好主意。
动态格式表的一般特征：
· 除了长度少于4的列外，所有的字符串列是动态的。
· 在每个记录前面是一个位图，该位图表明哪一列包含空字符串（对于字符串列）或者0（对于数字列）。注意，这并不包括包含NULL值的列。如果一个字符列在拖曳空间移除后长度为零，或者一个数字列为零值，这都在位图中标注了且列不被保存到磁盘。 非空字符串被存为一个长度字节加字符串的内容。
· 通常比固定长度表需要更少的磁盘空间。
· 每个记录仅使用必需大小的空间。尽管如此，如果一个记录变大，它就按需要被分开成多片，造成记录碎片的后果。比如，你用扩展行长度的信息更新一行，该行就变得有碎片。在这种情况下，你可以时不时运行OPTIMIZE TABLE或myisamchk -r来改善性能。可使用myisamchk -ei来获取表的统计数据。
· 动态格式表在崩溃后要比静态格式表更难重建，因为一个记录可能被分为多个碎片且链接（碎片）可能被丢失。
· 动态尺寸记录期望的行长度用下列表达式来计算：
· 3
· + (number of columns + 7) / 8
· + (number of char columns)
· + (packed size of numeric columns)
· + (length of strings)
· + (number of NULL columns + 7) / 8
对每个链接需要额外的6字节。在一个更新导致一个记录的扩大之时，一个动态记录被链接了。每个新链接至少是20字节，所以下一个扩大可能在同样的链接里进行。如果不是，则另一个链接将被建立。你可以使用myisamchk -ed来找出链接的数目。所有的链接可以用myisamchk -r来移除。
15.1.3.3. 已压缩表特征
已压缩存储格式是由myisampack工具创建的只读格式。
所有MySQL分发版里都默认包括myisampack。已压缩表可以用myisamchk来解压缩。
已压缩表有下列特征：
· 已压缩表占据非常小的磁盘空间。这最小化了磁盘用量，当使用缓慢的磁盘（如CD-ROM)之时，这是很有用的。
· 每个记录是被单独压缩的，所以只有非常小的访问开支。依据表中最大的记录，一个记录的头在每个表中占据1到3个字节。每个列被不同地压缩。通常每个列有一个不同的Huffman树。一些压缩类型如下：
o 后缀空间压缩。
- 前缀空间压缩。
- 零值的数用一个位来存储。
- 如果在一个整型列中的值有一个小的范围，列被用最小可能的类型来存储。比如，一个BIGINT列（8字节），如果所有它的值在-128到127范围内，它可以被存储为TINYINT列（1字节）
- 如果一个列仅有一小组可能的值，列的类型被转化成ENUM。
- 一个列可以使用先前压缩类型的任意合并。
· 可以处理固定长度或动态长度记录。
[/Quote]
15.1.3.1. 静态（固定长度）表特征
15.1.3.2. 动态表特征
15.1.3.3. 已压缩表特征
MyISAM支持三种不同存储格式。其中两个（固定格式和动态格式)根据正使用的列的类型来自动选择。第三个，即已压缩格式，只能使用myisampack工具来创建。
当你CREATE或ALTER一个没有BLOB或TEXT列的表，你可以用ROW_FORMAT表选项强制表的格式为FIXED或DYNAMIC。这会导致CHAR和VARCHAR列因FIXED格式变成CHAR，或因DYNAMIC格式变成VARCHAR。
通过用ALTER TABLE指定ROW_FORMAT={COMPRESSED | DEFAULT}，你可以压缩或解压缩表，请参阅13.1.5节，“CREATE TABLE语法”。
15.1.3.1. 静态（固定长度）表特征
静态格式是MyISAM表的默认存储格式。当表不包含变量长度列（VARCHAR, BLOB, 或TEXT）时，使用这个格式。每一行用固定字节数存储。
MyISAM的三种存储格式中，静态格式就最简单也是最安全的（至少对于崩溃而言）。静态格式也是最快的on-disk格式。快速来自于数据文件中的行在磁盘上被找到的容易方式：当按照索引中的行号查找一个行时，用行长度乘以行号。同样，当扫描一个表的时候，很容易用每个磁盘读操作读一定数量的记录。
当MySQL服务器正往一个固定格式MyISAM文件写的时候，如果计算机崩溃了，安全是显然的。在这种情况下，myisamchk可以容易地决定每行从哪里开始到哪里结束，所以它通常可以收回所有记录，除了写了一部分的记录。注意，基于数据行，MyISAM表索引可以一直被重新构建。
静态格式表的一般特征：
· CHAR列对列宽度是空间填补的。
· 非常快。
· 容易缓存。
· 崩溃后容易重建，因为记录位于固定位置。
· 重新组织是不必要的，除非你删除巨量的记录并且希望为操作系统腾出磁盘空间。为此，可使用OPTIMIZE TABLE或者myisamchk -r。
· 通常比动态格式表需要更多的磁盘空间。
15.1.3.2. 动态表特征
如果一个MyISAM表包含任何可变长度列（VARCHAR, BLOB或TEXTDynamic），或者如果一个表被用ROW_FORMAT=DYNAMIC选项来创建，动态存储格式被使用。
这个格式更为复杂一点，因为每行有一个表明行有多长的头。当一个记录因为更新的结果被变得更长，该记录也可以在超过一个位置处结束。
你可以使用OPTIMIZE TABLE或myisamchk来对一个表整理碎片。如果在一个表中有你频繁访问或改变的固定长度列，表中也有一些可变长度列，仅为避免碎片而把这些可变长度列移到其它表可能是一个好主意。
动态格式表的一般特征：
· 除了长度少于4的列外，所有的字符串列是动态的。
· 在每个记录前面是一个位图，该位图表明哪一列包含空字符串（对于字符串列）或者0（对于数字列）。注意，这并不包括包含NULL值的列。如果一个字符列在拖曳空间移除后长度为零，或者一个数字列为零值，这都在位图中标注了且列不被保存到磁盘。 非空字符串被存为一个长度字节加字符串的内容。
· 通常比固定长度表需要更少的磁盘空间。
· 每个记录仅使用必需大小的空间。尽管如此，如果一个记录变大，它就按需要被分开成多片，造成记录碎片的后果。比如，你用扩展行长度的信息更新一行，该行就变得有碎片。在这种情况下，你可以时不时运行OPTIMIZE TABLE或myisamchk -r来改善性能。可使用myisamchk -ei来获取表的统计数据。
· 动态格式表在崩溃后要比静态格式表更难重建，因为一个记录可能被分为多个碎片且链接（碎片）可能被丢失。
· 动态尺寸记录期望的行长度用下列表达式来计算：
· 3
· + (number of columns + 7) / 8
· + (number of char columns)
· + (packed size of numeric columns)
· + (length of strings)
· + (number of NULL columns + 7) / 8
对每个链接需要额外的6字节。在一个更新导致一个记录的扩大之时，一个动态记录被链接了。每个新链接至少是20字节，所以下一个扩大可能在同样的链接里进行。如果不是，则另一个链接将被建立。你可以使用myisamchk -ed来找出链接的数目。所有的链接可以用myisamchk -r来移除。
15.1.3.3. 已压缩表特征
已压缩存储格式是由myisampack工具创建的只读格式。
所有MySQL分发版里都默认包括myisampack。已压缩表可以用myisamchk来解压缩。
已压缩表有下列特征：
· 已压缩表占据非常小的磁盘空间。这最小化了磁盘用量，当使用缓慢的磁盘（如CD-ROM)之时，这是很有用的。
· 每个记录是被单独压缩的，所以只有非常小的访问开支。依据表中最大的记录，一个记录的头在每个表中占据1到3个字节。每个列被不同地压缩。通常每个列有一个不同的Huffman树。一些压缩类型如下：
o 后缀空间压缩。
- 前缀空间压缩。
- 零值的数用一个位来存储。
- 如果在一个整型列中的值有一个小的范围，列被用最小可能的类型来存储。比如，一个BIGINT列（8字节），如果所有它的值在-128到127范围内，它可以被存储为TINYINT列（1字节）
- 如果一个列仅有一小组可能的值，列的类型被转化成ENUM。
- 一个列可以使用先前压缩类型的任意合并。
· 可以处理固定长度或动态长度记录。
[/Quote]
rucypli 2011-10-12
- 打赏
- 举报
内存占用量无法精确貌似 尤其是innodb
ACMAIN_CHM 2011-10-12
- 打赏
- 举报
Storage Requirements for String Types in MyISAM
In the following table, M represents the declared column length in characters for nonbinary string types and bytes for binary string types. L represents the actual length in bytes of a given string value.
CHAR(M) M × w bytes, 0 <= M <= 255, where w is the number of bytes required for the maximum-length character in the character set
VARCHAR(M), VARBINARY(M) L + 1 bytes if column values require 0 – 255 bytes, L + 2 bytes if values may require more than 255 bytes
In the following table, M represents the declared column length in characters for nonbinary string types and bytes for binary string types. L represents the actual length in bytes of a given string value.
CHAR(M) M × w bytes, 0 <= M <= 255, where w is the number of bytes required for the maximum-length character in the character set
VARCHAR(M), VARBINARY(M) L + 1 bytes if column values require 0 – 255 bytes, L + 2 bytes if values may require more than 255 bytes
zuoxingyu 2011-10-12
- 打赏
- 举报
up up up
zuoxingyu 2011-10-12
- 打赏
- 举报
[Quote=引用 2 楼 wwwwb 的回复:]
使用什么系统，估计与系统的文件分配有关(比如FAT32、NTFS)
[/Quote]
centos 5.5
使用什么系统，估计与系统的文件分配有关(比如FAT32、NTFS)
[/Quote]
centos 5.5
小小小小周 2011-10-12
- 打赏
- 举报
[Quote=引用 2 楼 wwwwb 的回复:]
使用什么系统，估计与系统的文件分配有关(比如FAT32、NTFS)
[/Quote]
上面的结果来自 redhat5 64位。
在windows 下面结果也是一样。
使用什么系统，估计与系统的文件分配有关(比如FAT32、NTFS)
[/Quote]
上面的结果来自 redhat5 64位。
在windows 下面结果也是一样。
wwwwb 2011-10-12
- 打赏
- 举报
使用什么系统，估计与系统的文件分配有关(比如FAT32、NTFS)
zuoxingyu 2011-10-12
- 打赏
- 举报
同求准确的计算磁盘占用量和内存占用量的办法。
zuoxingyu 2011-10-12
- 打赏
- 举报
10楼有错误
超过255的，前面4个字节占位符，一个字节记录开始标志位，3个字节保存字段长度，后面才是数据位。
超过255的，前面4个字节占位符，一个字节记录开始标志位，3个字节保存字段长度，后面才是数据位。
zuoxingyu 2011-10-12
- 打赏
- 举报
测试写满20个字节的情况后：
每条记录默认有7个字节保留，1个长度位。写入20个a，占用28个字节，并不是20个字节。
超过255，会多出来9个字节，2个字节保存长度。
每条记录默认有7个字节保留，1个长度位。写入20个a，占用28个字节，并不是20个字节。
超过255，会多出来9个字节，2个字节保存长度。
zuoxingyu 2011-10-12
- 打赏
- 举报
分析.MYD文件可以得到如下结论：
VARCHAR(4),写入3条记录，(a),(bbbbb),(ccc)
a记录
03 00 03 0D FE 01 61 00 00 00 00 00 00 00 00 00 00 00 00 00 (a)
03 00 06 0A FE 04 62 62 62 62 00 00 00 00 00 00 00 00 00 00 (bbbbb)
03 00 05 0B FE 03 63 63 63 00 00 00 00 00 00 00 00 00 00 00 (cc)
这里是16进制的，把16进制的61转换成10进制=97，
mysql> SELECT CHAR(97);
+----------+
| CHAR(97) |
+----------+
| a |
+----------+
1 row in set (0.00 sec)
得到a这个字符。b和c同理。
FE后面的01,04,03是以字节记录记录长度，低于255的用一个字节保存。
可以认为FE前面的，是记录说明标志位，每条记录最少分配20个字节，不到20字节的以00 补全。
VARCHAR(4),写入3条记录，(a),(bbbbb),(ccc)
a记录
03 00 03 0D FE 01 61 00 00 00 00 00 00 00 00 00 00 00 00 00 (a)
03 00 06 0A FE 04 62 62 62 62 00 00 00 00 00 00 00 00 00 00 (bbbbb)
03 00 05 0B FE 03 63 63 63 00 00 00 00 00 00 00 00 00 00 00 (cc)
这里是16进制的，把16进制的61转换成10进制=97，
mysql> SELECT CHAR(97);
+----------+
| CHAR(97) |
+----------+
| a |
+----------+
1 row in set (0.00 sec)
得到a这个字符。b和c同理。
FE后面的01,04,03是以字节记录记录长度，低于255的用一个字节保存。
可以认为FE前面的，是记录说明标志位，每条记录最少分配20个字节，不到20字节的以00 补全。
