MySQL表碎片整理

2022-03-11 20:56 abce 阅读(527) 评论(0) 编辑收藏举报

查看指定数据库所占空间

select concat(round(sum(data_length / 1024 / 1024), 2), 'MB') as data
  from information_schema.tables
 where table_schema = 'dbName';

查看指定表所占空间

select concat(round(sum(data_length / 1024 / 1024), 2), 'MB') as data
  from information_schema.tables
 where table_schema = 'dbName'
   and table_name = 'tableName';

查看指定数据库下各个表分别占用的空间

select concat(round(sum(data_length / 1024 / 1024), 2), 'MB') as data,
       table_name
  from information_schema.tables
 where table_schema = 'dbName'
 GROUP BY table_name
 ORDER BY data desc;

查看所有数据库各自的容量大小

select table_schema as '数据库名称',
       sum(table_rows) as '记录数',
       sum(truncate(data_length / 1024 / 1024, 2)) as '数据容量(MB)',
       sum(truncate(index_length / 1024 / 1024, 2)) as '索引容量(MB)'
  from information_schema.tables
 group by table_schema
 order by sum(data_length) desc, sum(index_length) desc;

查看所有数据库各表容量大小

select table_schema as '数据库名称',
       table_name as '表名',
       table_rows as '记录数',
       truncate(data_length / 1024 / 1024, 2) as '数据容量(MB)',
       truncate(index_length / 1024 / 1024, 2) as '索引容量(MB)'
  from information_schema.tables
 order by data_length desc, index_length desc;

查看表的碎片

select table_schema db_name,
       table_name,
       round(data_length / 1024 / 1024) as data_length_mb,
       round(index_length / 1024 / 1024) as index_length_mb,
       round(data_free / 1024 / 1024) as data_free_mb,
       engine
  from information_schema.tables
 where table_schema not in ('information_schema', 'mysql')
   and data_free > 0
 order by data_free_mb desc;

其中：

·data_length：对于innodb表，表示实际分配给聚簇索引的空间大小

·index_length：对于innodb表，表示实际分配给二级索引的空间大小

·data_free：对于innodb表，表示未使用的空间。对于分区表，这个值可能不准，具体要查看information_schema.partitions

清除表碎片

方法1 alter table 表名 engine=InnoDB

1	`alter` `table` `tableName engine=InnoDB;`

方法2 optimize table 表名

#单个表
optimize table tableName;
#多个表
optimize table tableName1,tableName2,tableName3,tableName4;

方法3：

#针对单个表
mysqlcheck -o dbName1 tableName1 -u root -pxxxx
 
#针对某个库
mysqlcheck -o dbName1 -u root -pxxxx
 
#针对所有的库
mysqlcheck -o --all-databases -u root -pxxxx