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

created at 07-03-2021 views: 3

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

MySQL

created at 07-03-2021

robot18

Please log in to leave a comment.