SELECTconcat(round(sum((data_length+index_length)/1024/1024),2),'MB'asdata FROM information_schema.tables WHERE table_schema='mydb'and table_name='mytable';
新建表空间大小测算
从上面可以看到表空间大小由数据大小+索引大小两部分组成 下面通过一个例子来实际测算一下 DDL
1 2 3 4 5 6 7 8 9 10
mysql> desc City; +-------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | Name | char(35) | NO | | | | | CountryCode | char(3) | NO | MUL | | | | District | char(20) | NO | | | | | Population | int(11) | NO | | 0 | | +-------------+----------+------+-----+---------+----------------+
数据大小测算 根据表结构中字段大小来测算 66 bytes per row of data(4+35+3+20+4)
索引大小测算 4 bytes per row for the primary key 7 bytes per row for country code index
3 bytes for the country
4 bytes for Clustered Key attached to the country code