MySQL里,假如需要查询某个库里磁盘占用的数据或者索引超过1GB的SQL如下:
show table status where Data_length > 1000000000 or Index_length > 1000000000\G
不过,此sql不能排序,如果要排序,且是整个MySQL里查询(跨数据库),其SQL如下:
select table_schema, TABLE_NAME, dat, idx from
(SELECT table_schema, TABLE_NAME,
( data_length ) / 1024 / 1024 as dat,
( index_length ) / 1024 / 1024 as idx
FROM information_schema.TABLES
order by 3 desc ) a
order by 3 desc
limit 10;