-----------------------------------------------------------------------------------
前言: 在 MySQL 中有一个默认的数据表 information_schema,information_schema 这张数据表保存了 MySQL 服务器所有数据库的信息. 如数据库名, 数据库的表, 表栏的数据类型与访问权限等. 再简单点, 这台 MySQL 服务器上, 到底有哪些数据库, 各个数据库有哪些表, 每张表的字段类型是什么, 各个数据库要什么权限才能访问, 等等信息都保存在 information_schema 表里面, 所以请勿删改此表.
代码:
1, 切换数据库
use information_schema;
2, 查看数据库使用大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='DB_Name' ;
3, 查看表使用大小
- select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='DB_Name' and table_name='Table_Name';
- -----------------------------------------------------------------------------------
网上找的一个, 亲测可用:
- // 先进去 MySQL 自带管理库: information_schema
- // 然后查询 data_length,index_length
- // 你自己的数据库名: dbname
- // 你自己的表名: tablename
- MySQL> use information_schema;
- Database changed
- MySQL> select data_length,index_length
- -> from tables where
- -> table_schema='dbname'
- -> and table_name = 'tablename';
- +-------------+--------------+
- | data_length | index_length |
- +-------------+--------------+
- | 166379520 | 235782144 |
- +-------------+--------------+
- row in set (0.02 sec)
- MySQL> select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB,
- -> concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB
- -> from tables where
- -> table_schema='dbname'
- -> and table_name = 'tablename';
- +----------------+-----------------+
- | data_length_MB | index_length_MB |
- +----------------+-----------------+
- | 158.67MB | 224.86MB |
- +----------------+-----------------+
- row in set (0.03 sec)
- -----------------------------------------------------------------------------------
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. 查看指定数据库容量大小
例: 查看 MySQL 库容量大小
- 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
- where table_schema='mysql';
4. 查看指定数据库各表容量大小
例: 查看 MySQL 库各表容量大小
- 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
- where table_schema='mysql'
- order by data_length desc, index_length desc;
- -----------------------------------------------------------------------------------
- select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB, concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB
- from tables
- where table_schema='passport' and table_name='tb_user_info';
- -- 569.98MB 141.98MB
- select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB, concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB
- from tables
- where table_schema='passport_v2' and table_name='tb_user_info';
- -- 2128.94MB 285.00MB
来源: http://www.bubuko.com/infodetail-3113803.html