MySQL查看表占用空间大小
前言：在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> select data_length,index_length from tables where table_schema='dbname' and table_name = 'tablename';
+-------------+--------------+ | data_length | index_length | +-------------+--------------+ | 166379520 | 235782144 | +-------------+--------------+
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='xiaoneiwai' and table_name = 'course_promotion_agents';
+----------------+-----------------+ | data_length_MB | index_length_MB | +----------------+-----------------+ | 158.67MB | 224.86MB | +----------------+-----------------+
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;
+--------------------+-----------+------------------+------------------+ | 数据库 | 记录数 | 数据容量(MB) | 索引容量(MB) | +--------------------+-----------+------------------+------------------+ | xiaoneiwai | 469948640 | 60724.32 | 61246.20 | | admin | 86341747 | 39631.26 | 7924.72 | | smsgw | 7367058 | 2125.15 | 462.67 | | back | 4447309 | 956.16 | 1078.14 | | gs1 | 2426963 | 71.89 | 74.82 | | mysql | 2034 | 0.50 | 0.03 | | uc | 14 | 0.00 | 0.00 | | test | 1 | 0.00 | 0.00 | | information_schema | NULL | 0.00 | 0.00 | | performance_schema | 23014 | 0.00 | 0.00 | +--------------------+-----------+------------------+------------------+
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;
+--------------------+----------------------------------------------+-----------+------------------+------------------+ | 数据库 | 表名 | 记录数 | 数据容量(MB) | 索引容量(MB) | +--------------------+----------------------------------------------+-----------+------------------+------------------+ | admin | user_active_data | 43441854 | 19513.23 | 3779.16 | | admin | qun_active_data | 41141839 | 18794.00 | 3679.30 | | xiaoneiwai | notification | 26199895 | 6102.00 | 3385.84 | | xiaoneiwai | weibo_footprint | 122371009 | 4856.00 | 9398.00 | | xiaoneiwai | attachment | 9452395 | 3770.00 | 2605.15 | | xiaoneiwai | weibo_pic_video | 2763042 | 3437.96 | 0.00 | +--------------------+----------------------------------------------+-----------+------------------+------------------+
3.查看指定数据库容量大小
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';
+-----------+-----------+------------------+------------------+ | 数据库 | 记录数 | 数据容量(MB) | 索引容量(MB) | +-----------+-----------+------------------+------------------+ | mysql | 2034 | 0.50 | 0.03 | +-----------+-----------+------------------+------------------+
4.查看指定数据库各表容量大小
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;
+-----------+---------------------------+-----------+------------------+------------------+ | 数据库 | 表名 | 记录数 | 数据容量(MB) | 索引容量(MB) | +-----------+---------------------------+-----------+------------------+------------------+ | mysql | help_topic | 505 | 0.40 | 0.01 | | mysql | help_keyword | 453 | 0.08 | 0.01 | | mysql | help_category | 38 | 0.02 | 0.00 | | mysql | help_relation | 992 | 0.00 | 0.01 | | mysql | db | 17 | 0.00 | 0.00 | | mysql | user | 23 | 0.00 | 0.00 | | mysql | proxies_priv | 2 | 0.00 | 0.00 | | mysql | procs_priv | 0 | 0.00 | 0.00 | | mysql | tables_priv | 0 | 0.00 | 0.00 | | mysql | columns_priv | 0 | 0.00 | 0.00 | | mysql | proc | 0 | 0.00 | 0.00 | | mysql | host | 0 | 0.00 | 0.00 | | mysql | event | 0 | 0.00 | 0.00 | | mysql | time_zone | 0 | 0.00 | 0.00 | | mysql | plugin | 0 | 0.00 | 0.00 | | mysql | ndb_binlog_index | 0 | 0.00 | 0.00 | | mysql | servers | 0 | 0.00 | 0.00 | | mysql | func | 0 | 0.00 | 0.00 | | mysql | time_zone_transition_type | 0 | 0.00 | 0.00 | | mysql | time_zone_transition | 0 | 0.00 | 0.00 | | mysql | time_zone_name | 0 | 0.00 | 0.00 | | mysql | time_zone_leap_second | 0 | 0.00 | 0.00 | | mysql | slow_log | 2 | 0.00 | 0.00 | | mysql | general_log | 2 | 0.00 | 0.00 | +-----------+---------------------------+-----------+------------------+------------------+
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。