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

小辉博客
小辉博客
小辉博客
358
文章
3
评论
2020-07-0715:32:44 评论 1.9K 7170字阅读23分54秒

说明:

通过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)

继续阅读
若文章图片、下载链接等信息出错,请在评论区留言反馈,博主将第一时间更新!如果喜欢本站,请打赏支持本站,谢谢!
  • 我的微信
  • 微信扫一扫
  • weinxin
  • 我的微信公众号
  • 微信扫一扫
  • weinxin
小辉博客
  • 本文由 发表于 2020-07-0715:32:44
  • 除非特殊声明,本站文章均为原创,转载请务必保留本文链接
MySQL Binlog日志三种模式 Mysql

MySQL Binlog日志三种模式

binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二进制...
MySQL 连接 Mysql

MySQL 连接

MySQL 连接 使用mysql二进制方式连接 您可以使用MySQL二进制方式进入到mysql命令提示符下来连接MySQL数据库。 实例 以下是从命令行中连接mysql服务...
MySQL PHP 语法 Mysql

MySQL PHP 语法

MySQL PHP 语法 MySQL 可应用于多种语言,包括 PERL, C, C++, JAVA 和 PHP,在这些语言中,MySQL 在 PHP 的 web 开发中是应用最广泛。 在本...
Mysql数据库备份脚本 Mysql

Mysql数据库备份脚本

摘要 众所周知数据是应用的核心部分,程序坏了换台机器重新发布就可以,但数据一旦丢失,造成的损失将不可挽回,程序发布到生产后,数据的备份便显得尤为重要,由于不一定所有的服务均有资金完成高级的备份如RA...
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: