Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.