MySQL 查看数据库和表空间大小及碎片处理

1. 查看所有数据库空间大小

  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;

2. 查看所有数据库中各个表的空间大小

  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;

3. 查看所有产生碎片的表

  SELECT table_schema db, 
         table_name, 
         data_free, 
         engine 
    FROM information_schema.tables 
   WHERE table_schema NOT IN ('information_schema', 'mysql') 
     AND data_free > 0 
ORDER BY DATA_FREE DESC;

4. 查看某个表的碎片大小

SHOW TABLE STATUS LIKE '表名';

  查询结果中的'Data_free'字段的值就是碎片大小。

5. 清理表碎片

/*1. MyISAM表*/

OPTIMIZE TABLE 表名

/*2. InnoDB表*/

ALTER TABLE 表名 engine = InnoDB

清除碎片操作会暂时锁表，数据量越大，耗费的时间越长，可以做个脚本，定期检查MySQL中 information_schema.tables字段，查看 data_free 字段，大于0话，就表示有碎片，当大于自己认为的警戒值的话，就清理一次。