Innodb表碎片问题

查看表占用的磁盘空间

SELECT
    TABLE_NAME,
    ( DATA_LENGTH + INDEX_LENGTH )/ 1048576 AS size_Mb,
    TABLE_ROWS 
FROM
    information_schema.TABLES 
WHERE
    TABLE_SCHEMA = 'xx_db' 
    AND TABLE_NAME = 'xx_table';

alter table xxx engine=innodb;

OPTIMIZE table

fragmented defragmentation

InnoDB表 重建表

1、进入information_schema 数据库（存放了其他的数据库的信息）

use information_schema;

mysql> use information_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

2、查询所有数据的大小：
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables;


3、查看指定数据库的大小：
比如查看数据库home的大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='database_name';

4、查看指定数据库的某个表的大小
比如查看数据库home中 members 表的大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='database_name' and table_name='table_name';