mysql 查看数据表磁盘碎片空间大小

查看数据库里空间碎片大于1G的表

	SELECT
  table_schema AS '库名',
  table_name AS '表名',
  ENGINE AS '存储引擎',
  table_rows AS '行数',
  trim(
    concat(
    round( DATA_LENGTH / 1024 / 1024, 1 ))) AS '数据大小MB',
  trim(
  round( index_length / 1024 / 1024, 1 )) AS '索引大小MB',
  trim(
  round( DATA_FREE / 1024 / 1024, 1 )) AS '碎片大小MB' 
FROM
  information_schema.TABLES 
WHERE
  table_schema NOT IN ( 'information_schema', 'phpmyadmin', 'scripts', 'test', 'performance_schema', 'mysql' ) 
  AND DATA_FREE / 1024 / 1024 > 1000 
ORDER BY
  DATA_FREE DESC;