InnoDB字段类型大小vs文件大小是否降低? [英] InnoDB field type size vs file size descrepency?
问题描述
我创建了这些表:
create table aca (money TINYINT);
create table acb (money SMALLINT);
create table acc (money INT);
create table acd (money BIGINT);
我反复运行以下插入以添加9420行:
I ran the following inserts over and over to add 9420 rows:
INSERT INTO aca (money) VALUES(7), (8), (9), (10), (12);
INSERT INTO acb (money) VALUES(7), (8), (9), (10), (12);
INSERT INTO acc (money) VALUES(7), (8), (9), (10), (12);
INSERT INTO acd (money) VALUES(7), (8), (9), (10), (12);
当我检查服务器上的文件时，它显示每个数据库都比另一个大:
When I check the files on the server it shows each database being slightly larger than the other:
-rw-r----- 1 mysql mysql 360448 Jul 5 14:21 aca.ibd
-rw-r----- 1 mysql mysql 376832 Jul 5 14:21 acb.ibd
-rw-r----- 1 mysql mysql 393216 Jul 5 14:21 acc.ibd
-rw-r----- 1 mysql mysql 442368 Jul 5 14:21 acd.ibd
由于每个字段的大小是下一个字段的两倍，所以我希望每个数据库文件的大小大约是它的两倍(由于标题而造成的细微差别)，但这不是这种情况，而是大小是两倍(增大100％)，增大了4-13％.
Since each field is twice as large as the next, I would expect each database file to be roughly twice the size (given a small difference because of headers), but this is not the case and instead of being double the size (100% larger) they are 4-13% larger.
为什么?
推荐答案
InnoDB表中包含什么?这是一个粗略的列表:
What goes into an InnoDB table? Here's a rough list:
- 由于您还没有
PRIMARY KEY，所以会添加一个隐藏的6字节数字. - 每列前面都有一个长度和空信息.在您的情况下，这可能会每行增加2个字节.
- 每一行都有各种各样的开销，包括事务之类的东西.一个估计是每行29个字节.
- BTree的填充量不超过15/16. (15KB/16KB).
- Since you have not
PRIMARY KEY, a hidden 6-byte number is added. - Each column is preceded by a length and null info. This may add 2 bytes per row in your case.
- There is all sorts of overhead for each row, including transaction stuff, etc. One estimate is 29 bytes per row.
- A BTree is filled to no more than 15/16th full. (15KB / 16KB).
同时，典型表定义的便捷经验法则是将明显的总列大小乘以2至3，以获得.ibd文件的大小.这不适用于您的情况，因为您的列数(1)少得不切实际.
Meanwhile, a handy Rule of Thumb for typical table definitions is to multiply the obvious total column size by between 2 and 3 to get the size of the .ibd file. This does not work for your case because you have an unrealistically small number (1) of columns.
仅查看基本的，恒定的开销:
Looking at just the basic, constant, overhead:
9420 * 16/15 * (29+6) = 351KB
该数字非常适合作为您获得的表大小的下限.
That number fits nicely as a lower bound on the table sizes you got.
另一个问题:在某个时候，InnoDB会抢占4/8/16MB的空间(范围")(我不确定它的大小).我想你还没有击中那个.发生这种情况时，计算将变得更加混乱并且难以遵循.
Another issue: At some point, InnoDB grabs chunks of space ("extents") of 4/8/16MB (I'm not sure of which size it is). I guess you have not hit that yet. When that happens, the computations get even more confusing and hard to follow.
可以说，InnoDB进行了一些折衷.他们浪费"了一些空间(有时很多空间)，以使处理过程更简单，更快捷.
Suffice it to say, InnoDB has made some tradeoffs. They 'waste' some space (sometimes a lot of space) to make the processing simpler and faster.
如果您有PRIMARY KEY，则15/16会出现问题.如果按PK顺序插入，则15/16条.但是，如果您随机插入，则下降到大约69％.这是因为块分裂.
If you had had a PRIMARY KEY then the 15/16 comes into question. If you insert in PK order, then the 15/16 sticks. But if you insert randomly, it drops to about 69%. This is because of block splits.
哦，大"文本/blob列变为未记录".而ROW_FORMAT会增加皱纹.
Oh, and "large" text/blob columns go "off-record". And the ROW_FORMAT adds wrinkles.
而且，如果您使用MyISAM进行此操作，则会得到令人困惑的尺寸，例如75360、75360、75360和94200.这些解释起来非常简单，而且，如果我理解正确的话，也很容易预测.
And, if you did this with MyISAM, you would be given puzzling sizes like 75360, 75360, 75360, and 94200. Those are much simpler to explain, and, if I got them right, very predictable.
困惑了吗?
这篇关于InnoDB字段类型大小vs文件大小是否降低?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
