一、查看所有數(shù)據(jù)庫容量大小
SELECT
table_schema AS '數(shù)據(jù)庫',
sum( table_rows ) AS '記錄數(shù)',
sum(
TRUNCATE ( data_length / 1024 / 1024, 2 )) AS '數(shù)據(jù)容量(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;
??
二、查看所有數(shù)據(jù)庫各表容量大小
SELECT
table_schema AS '數(shù)據(jù)庫',
table_name AS '表名',
table_rows AS '記錄數(shù)',
TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '數(shù)據(jù)容量(MB)',
TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)'
FROM
information_schema.TABLES
ORDER BY
data_length DESC,
index_length DESC;
三、查看指定數(shù)據(jù)庫容量大小
SELECT
table_schema AS '數(shù)據(jù)庫',
sum( table_rows ) AS '記錄數(shù)',
sum(
TRUNCATE ( data_length / 1024 / 1024, 2 )) AS '數(shù)據(jù)容量(MB)',
sum(
TRUNCATE ( index_length / 1024 / 1024, 2 )) AS '索引容量(MB)'
FROM
information_schema.TABLES
WHERE
table_schema = 'osale_im';
四、查看指定數(shù)據(jù)庫各表容量大小
SELECT
table_schema AS '數(shù)據(jù)庫',
table_name AS '表名',
table_rows AS '記錄數(shù)',
TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '數(shù)據(jù)容量(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;
?
五：查看指定數(shù)據(jù)庫指定表容量大小
六.?查看所有產(chǎn)生碎片的表
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 '表名';
查詢結(jié)果中的'Data_free'字段的值就是碎片大小。文章來源：http://www.zghlxwxcb.cn/news/detail-507264.html
7. 清理表碎片文章來源地址http://www.zghlxwxcb.cn/news/detail-507264.html
/*1. MyISAM表*/
OPTIMIZE TABLE 表名
/*2. InnoDB表*/
ALTER TABLE 表名 engine = InnoDB到了這里，關(guān)于mysql語句查看數(shù)據(jù)庫表所占容量空間大小的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
