mysql5.7表大小取数测试

 

测试过程：

 

测试库版本：MySQL5.7.18

 

一、测试库使用独立表空间，也就是表的数据和索引都会存在自已的表空间中。

 

mysql>show variables like '%innodb_file_per_table%';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| innodb_file_per_table | ON    |

+-----------------------+-------+

 

二，建库建表初始化数据

 

mysql> create  database poc01; 

Query OK, 1 row affected (0.04 sec)

mysql> use poc01;

Database changed

mysql> create table t1(a int not null auto_increment primary key,b varchar(10),c datetime);

Query OK, 0 rows affected (0.15 sec)

mysql> insert into t1 values (1,'yang',current_time);

Query OK, 1 row affected (0.16 sec)

mysql> insert into t1(b,c) select b,c from t1;

Query OK, 524288 rows affected (15.13 sec)

Records: 524288  Duplicates: 0  Warnings: 0

 

mysql> select now();

+---------------------+

| now()               |

+---------------------+

| 2019-11-29 12:41:53 |

+---------------------+

1 row in set (0.00 sec)

 

mysql> select name,file_size/1024/1024 from information_schema.INNODB_SYS_TABLESPACES where name='poc01/t1';

+----------+---------------------+

| name     | file_size/1024/1024 |

+----------+---------------------+

| poc01/t1 |         44.00000000 |

+----------+---------------------+

1 row in set (0.00 sec)

通过information_schema.INNODB_SYS_TABLESPACES 查到约44 M数据；

 

 

mysql>  select table_schema,table_name,data_length/1024/1024, index_length/1024/1024,(data_length+index_length)/1024/1024,data_free/1024/1024 from information_schema.tables where TABLE_SCHEMA='poc01' and  TABLE_NAME = 't1';

+--------------+------------+-----------------------+------------------------+--------------------------------------+---------------------+

| table_schema | table_name | data_length/1024/1024 | index_length/1024/1024 | (data_length+index_length)/1024/1024 | data_free/1024/1024 |

+--------------+------------+-----------------------+------------------------+--------------------------------------+---------------------+

| poc01        | t1         |           34.56250000 |             0.00000000 |                          34.56250000 |          4.00000000 |

+--------------+------------+-----------------------+------------------------+--------------------------------------+---------------------+

1 row in set (0.00 sec)

通过 information_schema.tables  查看到约34 M数据；

 

[root@Demo16 data]# date

Fri Nov 29 12:43:27 CST 2019

[root@Demo16 data]# cd /usr/local/mysql/data/poc01

[root@Demo16 poc01]# ls -lh

total 45M

-rw-r----- 1 mysql mysql   65 Nov 29 12:40 db.opt

-rw-r----- 1 mysql mysql 8.5K Nov 29 12:40 t1.frm

-rw-r----- 1 mysql mysql  44M Nov 29 12:41 t1.ibd

表文件大小约44 M数据；

 

也就是说information_schema.INNODB_SYS_TABLESPACES更接近实际表文件大小；

 

三，建索引

mysql>  create index ind_c on t1(c);

Query OK, 0 rows affected (1 min 11.86 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> select now();

+---------------------+

| now()               |

+---------------------+

| 2019-11-29 12:44:31 |

+---------------------+

1 row in set (0.00 sec)

 

mysql>  select table_schema,table_name,data_length/1024/1024, index_length/1024/1024,(data_length+index_length)/1024/1024,data_free/1024/1024 from information_schema.tables where TABLE_SCHEMA='poc01' and  TABLE_NAME = 't1';

+--------------+------------+-----------------------+------------------------+--------------------------------------+---------------------+

| table_schema | table_name | data_length/1024/1024 | index_length/1024/1024 | (data_length+index_length)/1024/1024 | data_free/1024/1024 |

+--------------+------------+-----------------------+------------------------+--------------------------------------+---------------------+

| poc01        | t1         |           34.56250000 |             0.00000000 |                          34.56250000 |          5.00000000 |

+--------------+------------+-----------------------+------------------------+--------------------------------------+---------------------+

1 row in set (0.00 sec)

说明MySQL并不是实时更新information_schema.tables中 index_length的值

 

mysql> select name,file_size/1024/1024 from information_schema.INNODB_SYS_TABLESPACES where name='poc01/t1';                               

+----------+---------------------+

| name     | file_size/1024/1024 |

+----------+---------------------+

| poc01/t1 |         64.00000000 |

+----------+---------------------+

1 row in set (0.00 sec)

较加索引前44M,说明information_schema.INNODB_SYS_TABLESPACES 是实时更新file_size值

 

[root@Demo16 poc01]# date

Fri Nov 29 12:44:26 CST 2019

[root@Demo16 poc01]# cd /usr/local/mysql/data/poc01

[root@Demo16 poc01]# ls -lh

total 65M

-rw-r----- 1 mysql mysql   65 Nov 29 12:40 db.opt

-rw-r----- 1 mysql mysql 8.5K Nov 29 12:44 t1.frm

-rw-r----- 1 mysql mysql  64M Nov 29 12:44 t1.ibd

 

再次印证information_schema.INNODB_SYS_TABLESPACES更接近实际表文件大小；

 

 

四，删表数据

 

mysql> select now();                                                                                                                       

+---------------------+

| now()               |

+---------------------+

| 2019-11-29 12:45:51 |

+---------------------+

1 row in set (0.00 sec)

 

mysql> select min(a),max(a) from t1;

+--------+---------+

| min(a) | max(a)  |

+--------+---------+

|      1 | 1310693 |

+--------+---------+

1 row in set (0.00 sec)

 

mysql> delete from t1 where a> 600000;

Query OK, 645162 rows affected (13.22 sec)

 

mysql> select name,file_size/1024/1024 from information_schema.INNODB_SYS_TABLESPACES where name='poc01/t1';

+----------+---------------------+

| name     | file_size/1024/1024 |

+----------+---------------------+

| poc01/t1 |         64.00000000 |

+----------+---------------------+

1 row in set (0.04 sec)

 

删除数据产生了碎片，但表大小还是64M

 

mysql>  select table_schema,table_name,data_length/1024/1024, index_length/1024/1024,(data_length+index_length)/1024/1024,data_free/1024/1024 from information_schema.tables where TABLE_SCHEMA='poc01' and  TABLE_NAME = 't1';

+--------------+------------+-----------------------+------------------------+--------------------------------------+---------------------+

| table_schema | table_name | data_length/1024/1024 | index_length/1024/1024 | (data_length+index_length)/1024/1024 | data_free/1024/1024 |

+--------------+------------+-----------------------+------------------------+--------------------------------------+---------------------+

| poc01        | t1         |           36.56250000 |            18.54687500 |                          55.10937500 |         37.00000000 |

+--------------+------------+-----------------------+------------------------+--------------------------------------+---------------------+

1 row in set (0.00 sec)

 

对比删除数据前data_length得值没有变化，都是36.56250000， 说明data_length值也不是实时更新，而data_free值实时更新；

 

[root@Demo16 poc01]# date

Fri Nov 29 12:48:16 CST 2019

[root@Demo16 poc01]# 

[root@Demo16 poc01]# 

[root@Demo16 poc01]# ls -lh

total 65M

-rw-r----- 1 mysql mysql   65 Nov 29 12:40 db.opt

-rw-r----- 1 mysql mysql 8.5K Nov 29 12:44 t1.frm

-rw-r----- 1 mysql mysql  64M Nov 29 12:47 t1.ibd

 

information_schema.INNODB_SYS_TABLESPACES和实际表文件大小任然是一样得；

 

 

四，来一次碎片整理

 

 

 

mysql> select now();

+---------------------+

| now()               |

+---------------------+

| 2019-11-29 12:48:40 |

+---------------------+

1 row in set (0.00 sec)

 

mysql> alter table t1 engine=innodb;

Query OK, 0 rows affected (10.03 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql>  select table_schema,table_name,data_length/1024/1024, index_length/1024/1024,(data_length+index_length)/1024/1024,data_free/1024/1024 from information_schema.tables where TABLE_SCHEMA='poc01' and  TABLE_NAME = 't1';

+--------------+------------+-----------------------+------------------------+--------------------------------------+---------------------+

| table_schema | table_name | data_length/1024/1024 | index_length/1024/1024 | (data_length+index_length)/1024/1024 | data_free/1024/1024 |

+--------------+------------+-----------------------+------------------------+--------------------------------------+---------------------+

| poc01        | t1         |           16.51562500 |             7.51562500 |                          24.03125000 |          2.00000000 |

+--------------+------------+-----------------------+------------------------+--------------------------------------+---------------------+

1 row in set (0.00 sec)

 

mysql> select name,file_size/1024/1024 from information_schema.INNODB_SYS_TABLESPACES where name='poc01/t1';                             

+----------+---------------------+

| name     | file_size/1024/1024 |

+----------+---------------------+

| poc01/t1 |         30.00000000 |

+----------+---------------------+

1 row in set (0.00 sec)

碎片后空闲部分释放，表大小就是表实际数据大小；

[root@Demo16 poc01]# date

Fri Nov 29 12:49:47 CST 2019

 

[root@Demo16 poc01]# ls -lh

total 31M

-rw-r----- 1 mysql mysql   65 Nov 29 12:40 db.opt

-rw-r----- 1 mysql mysql 8.5K Nov 29 12:48 t1.frm

-rw-r----- 1 mysql mysql  30M Nov 29 12:48 t1.ibd

 

information_schema.INNODB_SYS_TABLESPACES和实际表文件大小还是一致，

 

 

 

结论：

•      表大小可以查看information.INNODB_SYS_TABLESPACES，INNODB_SYS_TABLESPACES的值是实时更新的，也不需要额外的配置；
•      碎片大小可以查看information_schema.tables，data_free是实时更新的，而data_length 或 index_length的值MySQL并不是实时更新的，而是周期性地维护；
•      表大小=information.INNODB_SYS_TABLESPACES中file_size大小=表文件大小（tablename.ibd文件）；
•      表碎片大小=information_schema.tables中data_free大小
•      表真实数据大小=表大小-表碎片大小