MYSQL中如何估算一张表中占用磁盘空间大小呢?

建表

表:table_name

拥有四个字段:

    CREATE TABLE `table_name` (
    id bigint(20) not null auto_increment,
    notes varchar(2000),
    createtime  datetime,
    flag int default '0',
    primary key (id)
    );

每个字段设计完成后占用的字节数
    bigint 8字节 
    varchar 2000 字节 
    datetime  8字节      
    int  4字节

数据表占用磁盘空间大小：
  (8+2000+8+4)*10000000 = 20200000000 字节 == 18G

1.库空间以及索引空间大小

    select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,
        concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
         from information_schema.tables
         group by TABLE_SCHEMA
         order by data_length desc;

2.查询某个数据库内每张表大小

         concat(truncate(index_length/1024/1024,2),' MB') as index_size
         from information_schema.tables where TABLE_SCHEMA = 'metadata'
         group by TABLE_NAME
         order by data_length desc;

3.查看数据库中所有表信息

    SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name', 
    CONCAT(truncate(table_rows/1000000,2),'M') AS 'Number of Rows', 
    CONCAT(truncate(data_length/(1024*1024*1024),2),'G') AS 'Data Size', 
    CONCAT(truncate(index_length/(1024*1024*1024),2),'G') AS 'Index Size' , 
    CONCAT(truncate((data_length+index_length)/(1024*1024*1024),2),'G') AS  'Total'
	FROM information_schema.TABLES WHERE table_schema LIKE 'metadata';