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';"