mysql 表碎片

SELECT t.TABLE_SCHEMA,        t.TABLE_NAME,        t.TABLE_ROWS,   t.AVG_ROW_LENGTH ,  t.TABLE_ROWS*t.AVG_ROW_LENGTH as rr , t.DATA_LENGTH,        t.INDEX_LENGTH,  t.DATA_LENGTH + t.INDEX_LENGTH as contact_free   ,   concat(round(t.DATA_FREE / 1024 / 1024, 2), 'M') AS datafree FROM information_schema.tables t WHERE t.TABLE_SCHEMA = 'xxxxxxx' and t.TABLE_NAME='xxxxxxx';   

mysql> show table from employees status like 't1'\G
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 1176484
Avg_row_length: 86
Data_length: 101842944
Max_data_length: 0
Index_length: 0
Data_free: 39845888
Auto_increment: NULL
Create_time: 2018-08-28 13:40:19
Update_time: 2018-08-28 13:50:43
Check_time: NULL
Collation: utf8mb4_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

碎片大小 = 数据总大小 - 实际表空间文件大小

数据总大小 = Data_length + Index_length = 101842944

实际表空间文件大小 = rows * Avg_row_length = 1176484 * 86 = 101177624

碎片大小 = (101842944 - 101177624) / 1024 /1024 = 0.63MB