MySQL运维——查看指定数据库 / 数据表的大小

1、查看指定数据库的总大小

SELECT 
  CONCAT(ROUND(SUM(DATA_LENGTH + INDEX_LENGTH)/1024/1024,2),'MB') AS total_size,
  CONCAT(ROUND(SUM(DATA_LENGTH)/1024/1024,2),'MB') AS data_size,
  CONCAT(ROUND(SUM(INDEX_LENGTH)/1024/1024,2),'MB') AS index_size
FROM information_schema.TABLES 
WHERE table_schema='指定库名';

2、查看指定数据库中指定表的大小

SELECT 
  CONCAT(ROUND((DATA_LENGTH + INDEX_LENGTH)/1024/1024,2),'MB') AS total_size,
  CONCAT(ROUND(DATA_LENGTH/1024/1024,2),'MB') AS data_size,
  CONCAT(ROUND(INDEX_LENGTH/1024/1024,2),'MB') AS index_size
FROM information_schema.TABLES 
WHERE table_schema='指定库名' AND table_name='指定表名';

3、查看指定库中所有表的大小（按大小排序）

SELECT 
  table_name AS 表名,
  CONCAT(ROUND((DATA_LENGTH + INDEX_LENGTH)/1024/1024,2),'MB') AS 总大小,
  CONCAT(ROUND(DATA_LENGTH/1024/1024,2),'MB') AS 数据大小,
  CONCAT(ROUND(INDEX_LENGTH/1024/1024,2),'MB') AS 索引大小
FROM information_schema.TABLES 
WHERE table_schema='指定库名'
ORDER BY (DATA_LENGTH + INDEX_LENGTH) DESC;  -- 按总大小降序排列