这是我的桌子:
+-------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| tckno | bigint(20) | YES | | NULL | |
| adid | mediumint(9) | NO | | NULL | |
| soyadid | mediumint(9) | NO | | NULL | |
| anaadid | mediumint(9) | NO | | NULL | |
| babaadid | mediumint(9) | NO | | NULL | |
| cinsiyet | enum('E','K') | NO | | NULL | |
| dogumyeri | mediumint(9) | NO | | NULL | |
| dogumtarihi | date | NO | | NULL | |
| nufusil | tinyint(4) | NO | | NULL | |
| nufusilce | smallint(9) | NO | | NULL | |
+-------------+---------------+------+-----+---------+----------------+现在,根据https://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html,我的行大小是
id(4) + tckno(8) + next 4 rows (4*3) + cinsiyet(1) + dogumyeri(3) + dogumtarihi(3) + nufusil(1) + nufusilce(2) = 34 bytes
我应该有48166478 (row count) * 34 = 1637660252 bytes数据库。但是,当我导航到mysql/data/<database name>文件夹时,我看到<table name>.idb文件是磁盘上的2826960896字节。2826960896 bytes - 1637660252 bytes ≈ 1.1GB存在差异。这些额外的数据是什么?
这是我从导出所述表中得到的
CREATE TABLE `optimumtablo` (
`id` int(11) NOT NULL,
`tckno` bigint(20) DEFAULT NULL,
`adid` mediumint(9) NOT NULL,
`soyadid` mediumint(9) NOT NULL,
`anaadid` mediumint(9) NOT NULL,
`babaadid` mediumint(9) NOT NULL,
`cinsiyet` enum('E','K') NOT NULL,
`dogumyeri` mediumint(9) NOT NULL,
`dogumtarihi` date NOT NULL,
`nufusil` tinyint(4) NOT NULL,
`nufusilce` smallint(9) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `optimumtablo` ADD PRIMARY KEY (`id`);
ALTER TABLE `optimumtablo` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;这是所述表的显示表状态输出;
+--------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| optimumtablo | InnoDB | 10 | Compact | 47621825 | 57 | 2759852032 | 0 | 0 | 4194304 | 48166479 | 2015-09-11 20:53:45 | NULL | NULL | utf8_general_ci | NULL | | |
+--------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+发布于 2015-09-12 08:32:25
造成这种差异的部分原因是:
InnoDB将表作为B树存储在其主键中(主键是表的聚类索引)。B树有一些存储开销(每个节点和内部节点上的指针-对于您的行计数,树可能有3-5个级别)。+每个二级索引也会添加一些空间,但您没有。不确定它是否占了整个1.1GB的差异,但它将是其中的很大一部分。
另一件事是,文件在删除时不会自动收缩。仅在重新构建整个表的优化/更改上。但是它将在表状态的data free列中报告。
发布于 2015-09-12 19:16:20
在InnoDB中,每一行至少都有以下开销:
CEIL(nullable_column_count/8)的可变长度位标志向量,它指示该行的每个可空列是否为空(在本表中,这将要求每一行1字节)。https://dev.mysql.com/doc/refman/5.6/en/innodb-physical-record.html
这些存储空间将占存储空间的很大一部分,因为行本身非常短。在这个特定的表上,开销是每行19字节,这是实际存储数据大小的50%以上。如果您有更多的列或更长的列,则开销将不会增长很大,而且与实际数据相比,开销要小得多(按比例计算)。
InnoDB也倾向于在每个数据页的末尾留下空白,从而减少(理想情况下)对分页的需求。差距的大小取决于是否按主键me顺序插入行。使用AUTO_INCREMENT主键,页面应该填充到大约15/16的位置。
https://dev.mysql.com/doc/refman/5.6/en/innodb-physical-structure.html
https://dba.stackexchange.com/questions/114841
复制相似问题