测试过程：
测试库版本：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大小
- 表真实数据大小=表大小-表碎片大小