Mysql table defragmentation
View the size of the table, index, and fragment size in the database:
Select round (sum (data_length/1024/1024), 2) as data_length_MB
Round (sum (index_length/1024/1024), 2) as index_length_MB
Round (sum (data_free/1024/1024), 2) as data_free_MB, table_name
From information_schema.tables where TABLE_SCHEMA= 'db_name' group by table_name order by 3 desc; has more query results to sort out.
View table fragmentation: DATA_FREE
Show TABLE status like'tapphire username'
Or check out:
Select * from information_schema.tables where table_schema= 'tattach appended username'
Generate bulk scripts:
Select CONCAT ('alter table', table_name, 'ENGINE=INNODB;') from information_schema.tables where TABLE_SCHEMA =' db_chunqiu' and table_name like 'tasking appended usernames headlined%'
Organize tables with data_free greater than 100m:
Select round (sum (data_length/1024/1024), 2) as data_length_MB
Round (sum (index_length/1024/1024), 2) as index_length_MB
Round (sum (data_free/1024/1024), 2) as data_free_MB, CONCAT ('alter table', table_name, 'ENGINE=INNODB;') dd
From information_schema.tables where TABLE_SCHEMA= 'db_chunqiu' group by dd having data_free_MB > 100 order by 3 desc
To defragment:
Alter table t_app_user ENGINE=INNODB
Before finishing:
Mysql > show TABLE status like'tattach appended user'\ G
* * 1. Row *
Name: st_order_cal_record
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 3033960
Avg_row_length: 7117
Data_length: 21594390528
Max_data_length: 0
Index_length: 0
Data_free: 201046622208-200G fragments around
Auto_increment: 241541550
Create_time: 2018-05-04 16:17:26
Update_time: 2018-10-12 15:11:18
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
ERROR:
No query specified
After finishing:
Mysql > show TABLE status like'tattach appended user'\ G
* * 1. Row *
Name: st_order_cal_record
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 3292968
Avg_row_length: 2038
Data_length: 6711918592
Max_data_length: 0
Index_length: 0
Data_free: 4194304-4m after finishing
Auto_increment: 241583900
Create_time: 2018-10-12 15:14:30
Update_time: 2018-10-12 15:57:51
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
ERROR:
No query specified
