数据库——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???|?????|?????????|???????|
+-----------------+---------------------+------+-----+---------+-------+

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!