«

MySQL查看表占用空间大小

时间:2024-2-19 17:27     作者:韩俊     分类: 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 |
+-----------+---------------------------+-----------+------------------+------------------+

标签: mysql

热门推荐