园龄：5年2个月粉丝：4 关注：5

查看Mysql数据库数据量大小、表大小、索引大小

通过MySQL的information_schema数据库，可查询数据库中每个表占用的空间、表记录的行数；
该库中有一个TABLES表，这个表主要字段分别是：
TABLE_SCHEMA：数据库名
TABLE_NAME：表名
ENGINE：所使用的存储引擎
TABLES_ROWS：记录数
DATA_LENGTH：数据大小
INDEX_LENGTH：索引大小
其他字段请参考MySQL的手册，查看一个表占用空间的大小，那就相当于是数据大小 + 索引大小。

查看所有库的大小

 mysql> use information_schema;
 
Reading table information for completion of table and column names
 
You can turn off this feature to get a quicker startup with -A
 
 
 
Database changed
 
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES;
 
+------------+
 
| data       |
 
+------------+
 
| 550.82MB   |
 
+------------+
 
1 row in set (0.17 sec)

查看指定库的大小

 mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema='mysql';
 
+---------+
 
| data    |
 
+---------+
 
| 0.70MB  |
 
+---------+
 
1 row in set (0.00 sec)

查看指定库的指定表的大小

 mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema='mysql' and table_name='user';
 
+---------+
 
| data    |
 
+---------+
 
| 0.00MB  |
 
+---------+
 
1 row in set (0.00 sec)

查看指定库的索引大小

 mysql> SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM TABLES WHERE table_schema = 'mysql';
 
+------------------+
 
| Total Index Size |
 
+------------------+
 
| 0.10 MB          |
 
+------------------+
 
1 row in set (0.00 sec)

查看指定库的指定表的索引大小

 mysql> SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM TABLES WHERE table_schema = 'mysql' and table_name='user';
 
+------------------+
 
| Total Index Size |
 
+------------------+
 
| 0.00 MB          |
 
+------------------+
 
1 row in set (0.00 sec)
 
mysql> show create table mysql.user\G
 
*************************** 1. row ***************************
 
Table: user
 
Create Table: CREATE TABLE `user` (
 
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
 
`User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
 
`Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
 
`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
`Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
`Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
`Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
`Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
`File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
`Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
`Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
`Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
`Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
`Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
`Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
`Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
`Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
`Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
`ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
 
`ssl_cipher` blob NOT NULL,
 
`x509_issuer` blob NOT NULL,
 
`x509_subject` blob NOT NULL,
 
`max_questions` int(11) unsigned NOT NULL DEFAULT '0',
 
`max_updates` int(11) unsigned NOT NULL DEFAULT '0',
 
`max_connections` int(11) unsigned NOT NULL DEFAULT '0',
 
`max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
 
`plugin` char(64) COLLATE utf8_bin DEFAULT 'mysql_native_password',
 
`authentication_string` text COLLATE utf8_bin,
 
`password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
 
PRIMARY KEY (`Host`,`User`)
 
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
 
1 row in set (0.00 sec)
 
mysql> select count(*) from mysql.user;
 
+----------+
 
| count(*) |
 
+----------+
 
| 20       |
 
+----------+
 
1 row in set (0.00 sec)

查询指定数据库中每个表的总行数,数据大小,索引大小和总大小

 mysql> SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name', CONCAT(ROUND(table_rows/1000000,4),'M') AS 'Number of Rows', CONCAT(ROUND(data_length/(1024*1024*1024),4),'G') AS 'Data Size', CONCAT(ROUND(index_length/(1024*1024*1024),4),'G') AS 'Index Size', CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),4),'G') AS'Total'FROM information_schema.TABLES WHERE table_schema LIKE 'mysql';
 
+---------------------------------+----------------+-----------+------------+---------+
 
| Table Name                      | Number of Rows | Data Size | Index Size | Total   |
 
+---------------------------------+----------------+-----------+------------+---------+
 
| mysql.columns_priv                          | 0.0000M | 0.0000G | 0.0000G | 0.0000G |
 
| mysql.db                                    | 0.0001M | 0.0000G | 0.0000G | 0.0000G |
 
| mysql.event                                 | 0.0000M | 0.0000G | 0.0000G | 0.0000G |
 
| mysql.func                                  | 0.0000M | 0.0000G | 0.0000G | 0.0000G |
 
| mysql.general_log                           | 0.0000M | 0.0000G | 0.0000G | 0.0000G |
 
| mysql.help_category                         | 0.0000M | 0.0000G | 0.0000G | 0.0000G |
 
| mysql.help_keyword                          | 0.0006M | 0.0001G | 0.0000G | 0.0001G |
 
| mysql.help_relation                         | 0.0012M | 0.0000G | 0.0000G | 0.0000G |
 
| mysql.help_topic                            | 0.0006M | 0.0005G | 0.0000G | 0.0006G |
 
| mysql.ndb_binlog_index                      | 0.0000M | 0.0000G | 0.0000G | 0.0000G |
 
| mysql.plugin                                | 0.0000M | 0.0000G | 0.0000G | 0.0000G |
 
| mysql.proc                                  | 0.0000M | 0.0000G | 0.0000G | 0.0000G |
 
| mysql.procs_priv                            | 0.0000M | 0.0000G | 0.0000G | 0.0000G |
 
| mysql.proxies_priv                          | 0.0000M | 0.0000G | 0.0000G | 0.0000G |
 
| mysql.servers                               | 0.0000M | 0.0000G | 0.0000G | 0.0000G |
 
| mysql.slow_log                              | 0.0000M | 0.0000G | 0.0000G | 0.0000G |
 
| mysql.tables_priv                           | 0.0000M | 0.0000G | 0.0000G | 0.0000G |
 
| mysql.time_zone                             | 0.0000M | 0.0000G | 0.0000G | 0.0000G |
 
| mysql.time_zone_leap_second                 | 0.0000M | 0.0000G | 0.0000G | 0.0000G |
 
| mysql.time_zone_name                        | 0.0000M | 0.0000G | 0.0000G | 0.0000G |
 
| mysql.time_zone_transition                  | 0.0000M | 0.0000G | 0.0000G | 0.0000G |
 
| mysql.time_zone_transition_type             | 0.0000M | 0.0000G | 0.0000G | 0.0000G |
 
| mysql.user                                  | 0.0000M | 0.0000G | 0.0000G | 0.0000G |
 
+---------------------------------+----------------+-----------+------------+---------+
 
23 rows in set (0.00 sec)

查询数据库指定表的数据部分大小,索引部分大小和总占用磁盘大小

 mysql> SELECT
 
a.table_schema ,
 
a.table_name ,
 
concat(round(sum(DATA_LENGTH / 1024 / 1024) + sum(INDEX_LENGTH / 1024 / 1024) ,2) ,'MB') 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 a
 
WHERE
 
a.table_schema = '数据库'
 
AND a.table_name = '表名';
 
+----------------+-------------+-----------+-----------+------------+
 
| table_schema   | table_name  | total_size | data_size | index_size|
 
+---------------+--------------+-----------+-----------+------------+
 
| mysql         | user         | 0.00MB    | 0.00MB    | 0.00MB     |
 
+---------------+---------------+-----------+-----------+-----------+
 
1 row in set (0.00 sec)

上一篇MySQL造数据，批量插入数据脚本

下一篇mysqldump参数详细说明

本文作者：高宏宇

本文链接：https://www.cnblogs.com/gaohongyu/p/14120261.html

posted @ 2020-12-11 14:39 高宏宇阅读(746) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

阅读排行：
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型，支持深度思考和联网搜索！
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· ollama系列01：轻松3步本地部署deepseek，普通电脑可用
· 25岁的心里话
· 按钮权限的设计及实现

高宏宇

业精于勤而荒于嬉，行成于思而毁于随

查看Mysql数据库数据量大小、表大小、索引大小

查看所有库的大小

查看指定库的大小

查看指定库的指定表的大小

查看指定库的索引大小

查看指定库的指定表的索引大小

查询指定数据库中每个表的总行数,数据大小,索引大小和总大小

查询数据库指定表的数据部分大小,索引部分大小和总占用磁盘大小

公告

随笔分类 (95)

文章分类 (2)

阅读排行榜

推荐排行榜

	mysql> use information_schema;

	Reading table information for completion of table and column names

	You can turn off this feature to get a quicker startup with -A



	Database changed

	mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES;

	+------------+

	\| data \|

	+------------+

	\| 550.82MB \|

	+------------+

	1 row in set (0.17 sec)

	mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema='mysql';

	+---------+

	\| data \|

	+---------+

	\| 0.70MB \|

	+---------+

	1 row in set (0.00 sec)

	mysql> SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM TABLES WHERE table_schema = 'mysql';

	+------------------+

	\| Total Index Size \|

	+------------------+

	\| 0.10 MB \|

	+------------------+

	1 row in set (0.00 sec)

	mysql> SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name', CONCAT(ROUND(table_rows/1000000,4),'M') AS 'Number of Rows', CONCAT(ROUND(data_length/(102410241024),4),'G') AS 'Data Size', CONCAT(ROUND(index_length/(102410241024),4),'G') AS 'Index Size', CONCAT(ROUND((data_length+index_length)/(102410241024),4),'G') AS'Total'FROM information_schema.TABLES WHERE table_schema LIKE 'mysql';

	+---------------------------------+----------------+-----------+------------+---------+

	\| Table Name \| Number of Rows \| Data Size \| Index Size \| Total \|

	+---------------------------------+----------------+-----------+------------+---------+

	\| mysql.columns_priv \| 0.0000M \| 0.0000G \| 0.0000G \| 0.0000G \|

	\| mysql.db \| 0.0001M \| 0.0000G \| 0.0000G \| 0.0000G \|

	\| mysql.event \| 0.0000M \| 0.0000G \| 0.0000G \| 0.0000G \|

	\| mysql.func \| 0.0000M \| 0.0000G \| 0.0000G \| 0.0000G \|

	\| mysql.general_log \| 0.0000M \| 0.0000G \| 0.0000G \| 0.0000G \|

	\| mysql.help_category \| 0.0000M \| 0.0000G \| 0.0000G \| 0.0000G \|

	\| mysql.help_keyword \| 0.0006M \| 0.0001G \| 0.0000G \| 0.0001G \|

	\| mysql.help_relation \| 0.0012M \| 0.0000G \| 0.0000G \| 0.0000G \|

	\| mysql.help_topic \| 0.0006M \| 0.0005G \| 0.0000G \| 0.0006G \|

	\| mysql.ndb_binlog_index \| 0.0000M \| 0.0000G \| 0.0000G \| 0.0000G \|

	\| mysql.plugin \| 0.0000M \| 0.0000G \| 0.0000G \| 0.0000G \|

	\| mysql.proc \| 0.0000M \| 0.0000G \| 0.0000G \| 0.0000G \|

	\| mysql.procs_priv \| 0.0000M \| 0.0000G \| 0.0000G \| 0.0000G \|

	\| mysql.proxies_priv \| 0.0000M \| 0.0000G \| 0.0000G \| 0.0000G \|

	\| mysql.servers \| 0.0000M \| 0.0000G \| 0.0000G \| 0.0000G \|

	\| mysql.slow_log \| 0.0000M \| 0.0000G \| 0.0000G \| 0.0000G \|

	\| mysql.tables_priv \| 0.0000M \| 0.0000G \| 0.0000G \| 0.0000G \|

	\| mysql.time_zone \| 0.0000M \| 0.0000G \| 0.0000G \| 0.0000G \|

	\| mysql.time_zone_leap_second \| 0.0000M \| 0.0000G \| 0.0000G \| 0.0000G \|

	\| mysql.time_zone_name \| 0.0000M \| 0.0000G \| 0.0000G \| 0.0000G \|

	\| mysql.time_zone_transition \| 0.0000M \| 0.0000G \| 0.0000G \| 0.0000G \|

	\| mysql.time_zone_transition_type \| 0.0000M \| 0.0000G \| 0.0000G \| 0.0000G \|

	\| mysql.user \| 0.0000M \| 0.0000G \| 0.0000G \| 0.0000G \|

	+---------------------------------+----------------+-----------+------------+---------+

	23 rows in set (0.00 sec)

	mysql> SELECT

	a.table_schema ,

	a.table_name ,

	concat(round(sum(DATA_LENGTH / 1024 / 1024) + sum(INDEX_LENGTH / 1024 / 1024) ,2) ,'MB') 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 a

	WHERE

	a.table_schema = '数据库'

	AND a.table_name = '表名';

	+----------------+-------------+-----------+-----------+------------+

	\| table_schema \| table_name \| total_size \| data_size \| index_size\|

	+---------------+--------------+-----------+-----------+------------+

	\| mysql \| user \| 0.00MB \| 0.00MB \| 0.00MB \|

	+---------------+---------------+-----------+-----------+-----------+

	1 row in set (0.00 sec)