mysql5.6 表碎片_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