1.查看数据库的大小
mysql> desc tables;
+-----------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512) | NO | | | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| TABLE_TYPE | varchar(64) | NO | | | |
| ENGINE | varchar(64) | YES | | NULL | |
| VERSION | bigint(21) unsigned | YES | | NULL | |
| ROW_FORMAT | varchar(10) | YES | | NULL | |
| TABLE_ROWS | bigint(21) unsigned | YES | | NULL | |
| AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_FREE | bigint(21) unsigned | YES | | NULL | |
| AUTO_INCREMENT | bigint(21) unsigned | YES | | NULL | |
| CREATE_TIME | datetime | YES | | NULL | |
| UPDATE_TIME | datetime | YES | | NULL | |
| CHECK_TIME | datetime | YES | | NULL | |
| TABLE_COLLATION | varchar(32) | YES | | NULL | |
| CHECKSUM | bigint(21) unsigned | YES | | NULL | |
| CREATE_OPTIONS | varchar(255) | YES | | NULL | |
| TABLE_COMMENT | varchar(2048) | NO | | | |
+-----------------+---------------------+------+-----+---------+-------+
21 rows in set (0.02 sec)
mysql> select sum(DATA_LENGTH)+sum(INDEX_LENGTH) from tables where TABLE_SCHEMA='tpcc';
+------------------------------------+
| sum(DATA_LENGTH)+sum(INDEX_LENGTH) |
+------------------------------------+
| 52817952768 |
+------------------------------------+
1 row in set (0.06 sec)
mysql> select (sum(DATA_LENGTH)+sum(INDEX_LENGTH))/1024/1024 from tables where TABLE_SCHEMA='tpcc';
+------------------------------------------------+
| (sum(DATA_LENGTH)+sum(INDEX_LENGTH))/1024/1024 |
+------------------------------------------------+
| 50605.17187500 |
+------------------------------------------------+
1 row in set (0.00 sec)
得到的单位是MB
mysql> select (sum(DATA_LENGTH)+sum(INDEX_LENGTH))/1024/1024/1024 from tables where TABLE_SCHEMA='tpcc';
+-----------------------------------------------------+
| (sum(DATA_LENGTH)+sum(INDEX_LENGTH))/1024/1024/1024 |
+-----------------------------------------------------+
| 49.419113159180 |
+-----------------------------------------------------+
1 row in set (0.00 sec)
得到的是GB
得到的结果是以字节为单位，除1024为K，除1048576为M。
计算数据库表的大小:
mysql> select table_schema,table_name,(data_length+index_length)/1024/1024/1024,table_rows from tables where table_schema='tpcc' and table_name='customer';
+--------------+------------+-------------------------------------------+------------+
| table_schema | table_name | (data_length+index_length)/1024/1024/1024 | table_rows |
+--------------+------------+-------------------------------------------+------------+
| tpcc | customer | 16.292968750000 | 24633762 |
+--------------+------------+-------------------------------------------+------------+
1 row in set (0.00 sec)
#######################################################################################################################################################
计算表的数据大小和索引的大小:
mysql> select table_schema,table_name,index_length/1024/1024/1024,table_rows from tables where table_schema='tpcc' and table_name='customer';
+--------------+------------+-----------------------------+------------+
| table_schema | table_name | index_length/1024/1024/1024 | table_rows |
+--------------+------------+-----------------------------+------------+
| tpcc | customer | 1.541992187500 | 25132578 |
+--------------+------------+-----------------------------+------------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql> select table_schema,table_name,data_length/1024/1024/1024,table_rows from tables where table_schema='tpcc' and table_name='customer';
+--------------+------------+----------------------------+------------+
| table_schema | table_name | data_length/1024/1024/1024 | table_rows |
+--------------+------------+----------------------------+------------+
| tpcc | customer | 14.750976562500 | 25200169 |
+--------------+------------+----------------------------+------------+
1 row in set (0.00 sec)
mysql>
mysql -e "use information_schema;select table_schema,table_name,data_length/1024/1024/1024,table_rows from tables where table_schema='tpcc' and table_name='customer';"
