假設有張表叫table_name；其中有四個字段

CREATE TABLE `table_name` (

id bigint(20) not null auto_increment,

detail varchar(2000),

createtime datetime,

validity int default '0',

primary key (id)

);

每個字段設計完成后占用的字節數

bigint 8字節

varchar 2000 字節

datetime 8字節

validity 4字節

算乘法計算占用磁盤：

(8+2000+8+4) * 10000000 = 20200000000 字節 == 18G

此時大概明白，為什么要在設計數據庫的時候，采用最小類型的原則了吧

其實該計算公式僅為簡單計算；mysql索引和表分離本身是分離，有時索引占用的磁盤空間可能比表占用的磁盤空間都要大！！

參看SQL:

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';