mysql如何查看table_数据库——MySQL如何查看table(表)占用空间的大小

查询所有数据库占用磁盘空间大小

select

TABLE_SCHEMA,

concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,concat(truncate(sum(index_length)/1024/1024,'MB') as index_size

from information_schema.tables

group by TABLE_SCHEMA

ORDER BY data_size desc;

#order by data_length desc;

查询单个库中所有表磁盘占用大小

select

TABLE_NAME,

concat(truncate(data_length/1024/1024,concat(truncate(index_length/1024/1024,' MB') as index_size

from information_schema.tables

where TABLE_SCHEMA = 'mysql'

group by TABLE_NAME

order by data_length desc;

参数解释

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

| 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   |     |         |       |

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