Suppose there is a table called table_name
; there are four fields:
CREATE TABLE `table_name` (
id bigint(20) not null auto_increment,
detail varchar(2000),
createtime datetime,
validity int default '0',
primary key (id)
);
The number of bytes occupied by each field after the design is completed
Calculate and multiply the occupied disk:
for a single row of data:
8+2000+8+4 = 2020
10 million pieces of data means 10 million rows:
(8+2000+8+4) * 10000000 = 20200000000 bytes == 18G
At this point, we probably understand why the principle of minimal type should be adopted when designing the database.
In fact, the calculation formula is only a simple calculation; MySQL index and table separation itself are separate, and sometimes the disk space occupied by the index may be larger than the disk space occupied by the table! !
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;
SELECT TABLE_NAME,CONCAT(TRUNCATE(SUM(data_length)/1024/1024,2),' MB') AS data_size,
CONCAT(TRUNCATE(index_length/1024/1024,2),' MB') AS index_size
FROM information_schema.tables WHERE TABLE_SCHEMA = 'usmschis'
GROUP BY TABLE_NAME;
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 'usmschis';