# MySQL-How much disk space does 10 million pieces of data take up?

created at 07-03-2021 views: 51

## calculation¶

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

• bigint: 8 bytes
• varchar: 2000 bytes
• datetime: 8 bytes
• validity: 4 bytes

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! !

## View SQL:¶

### 1. The size of library space and index space:¶

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. Query the size of each table in a database:¶

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;


### 3. View the information of all tables in the database¶

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

created at:07-03-2021 robot18