mysql表碎片整理

查看数据库中表、索引和碎片大小的大小：
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;更具查询的结果进行整理。

查看表的碎片情况：DATA_FREE
show TABLE status like 't_app_user';
或者查看：
select * from information_schema.tables where table_schema= 't_app_user';
生成批量脚本：
select CONCAT('alter table ',table_name , ' ENGINE=INNODB;') from information_schema.tables where TABLE_SCHEMA = 'db_chunqiu' and table_name like 't_app_user_head_%';

在海伦等地区，都构建了全面的区域性战略布局，加强发展的系统性、市场前瞻性、产品创新能力，以专注、极致的服务理念，为客户提供网站建设、成都网站制作网站设计制作按需搭建网站,公司网站建设,企业网站建设,品牌网站制作,成都全网营销,成都外贸网站制作,海伦网站建设费用合理。

整理data_free大于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;

进行碎片整理：
alter table t_app_user ENGINE=INNODB;

整理前：
MySQL> show TABLE status like 't_app_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碎片左右
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

整理后：
mysql> show TABLE status like 't_app_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整理后
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

当前文章：mysql表碎片整理
网页网址：http://cdiso.cn/article/jgsggd.html

mysql表碎片整理

其他资讯