如何使用mysql 命令行 查看mysql表大小、数据大小、索引大小

select * from information_schema.TABLES 

  where information_schema.TABLES.TABLE_SCHEMA='databasename'  and information_schema.TABLES.TABLE_NAME='tablename'\G

返回结果:

*************************** 1. row ***************************  TABLE_CATALOG: def   TABLE_SCHEMA: databasename     TABLE_NAME: tablename     TABLE_TYPE: BASE TABLE         ENGINE: MyISAM        VERSION: 10     ROW_FORMAT: Dynamic     TABLE_ROWS: 6422930 AVG_ROW_LENGTH: 213    DATA_LENGTH: 1370884700MAX_DATA_LENGTH: 281474976710655   INDEX_LENGTH: 412930048      DATA_FREE: 0 AUTO_INCREMENT: 6422931    CREATE_TIME: 2012-05-11 05:00:02    UPDATE_TIME: 2012-05-22 15:12:06     CHECK_TIME: 2012-05-11 09:58:52TABLE_COLLATION: utf8_general_ci       CHECKSUM: NULL CREATE_OPTIONS:   TABLE_COMMENT: 'table comment'1 row in set (0.00 sec)

其中：
information_schema.TABLES.TABLE_SCHEMA = ‘databasename’ and information_schema.TABLES.TABLE_NAME = ‘tablename’

信息存储在information_schema.TABLES这个表中，TABLE_SCHEMA 对应数据库名，TABLE_NAME 对应表名。
# TABLE_ROWS 代表拥有的数据行数。
# 总大小 = DATA_LENGTH(数据大小) + INDEX_LENGTH(索引大小)

结果以字节为单位，除1024为K，除1048576(=1024*1024)为M。

--本篇文章转自：http://digdeeply.org/archives/05222029.html