MySQL查看表数据大小及碎片空间大小语句

SELECT
  TABLE_SCHEMA AS '数据库',
  TABLE_NAME AS '表名',
  ENGINE AS '存储引擎',
  ROUND(DATA_LENGTH / ( 1024 * 1024 * 1024 ), 3) AS '数据大小(GB)',
  ROUND(INDEX_LENGTH / ( 1024 * 1024 * 1024 ), 3) AS '索引大小(GB)',
  ROUND((DATA_LENGTH + INDEX_LENGTH) / ( 1024 * 1024 * 1024 ), 3) AS '总逻辑大小(GB)',
  -- 只有 InnoDB 独立表空间或 MyISAM 的 DATA_FREE 才是真正的碎片
  ROUND(DATA_FREE / ( 1024 * 1024 * 1024 ), 3) AS '可回收碎片(GB)',
  TABLE_TYPE AS '表类型'
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'dbname' AND TABLE_NAME = 'tablename';