一、查看所有数据库容量大小
SELECT
table_schema AS '数据库',
sum( table_rows ) AS '记录数',
sum(
TRUNCATE ( data_length / 1024 / 1024, 2 )) AS '数据容量(MB)',
sum(
TRUNCATE ( index_length / 1024 / 1024, 2 )) AS '索引容量(MB)'
FROM
information_schema.TABLES
GROUP BY
table_schema
ORDER BY
sum( data_length ) DESC,
sum( index_length ) DESC;
二、查看所有数据库各表容量大小
SELECT
table_schema AS '数据库',
table_name AS '表名',
table_rows AS '记录数',
TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '数据容量(MB)',
TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)'
FROM
information_schema.TABLES
ORDER BY
data_length DESC,
index_length DESC;
三、查看指定数据库容量大小
SELECT
table_schema AS '数据库',
sum( table_rows ) AS '记录数',
sum(
TRUNCATE ( data_length / 1024 / 1024, 2 )) AS '数据容量(MB)',
sum(
TRUNCATE ( index_length / 1024 / 1024, 2 )) AS '索引容量(MB)'
FROM
information_schema.TABLES
WHERE
table_schema = 'osale_im';
四、查看指定数据库各表容量大小
SELECT
table_schema AS '数据库',
table_name AS '表名',
table_rows AS '记录数',
TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '数据容量(MB)',
TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)'
FROM
information_schema.TABLES
WHERE
table_schema = 'osale_im'
ORDER BY
data_length DESC,
index_length DESC;
五：查看指定数据库指定表容量大小
六. 查看所有产生碎片的表
SELECT table_schema db,
table_name,
data_free,
engine
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'mysql')
AND data_free > 0
ORDER BY DATA_FREE DESC;查看某个表的碎片大小
SHOW TABLE STATUS LIKE '表名';
查询结果中的'Data_free'字段的值就是碎片大小。文章来源：https://www.toymoban.com/news/detail-507264.html
7. 清理表碎片文章来源地址https://www.toymoban.com/news/detail-507264.html
/*1. MyISAM表*/
OPTIMIZE TABLE 表名
/*2. InnoDB表*/
ALTER TABLE 表名 engine = InnoDB到了这里，关于mysql语句查看数据库表所占容量空间大小的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
