Mysql查看数据库在磁盘上的大小

Administrator

2021-12-22

数据库 Mysql

935 4~5 min

1.查看所有数据库容量大小

SELECT table_schema                                        AS '数据库',
       SUM(table_rows)                                     AS '记录数',
       SUM(TRUNCATE(data_length / 1024 / 1024 / 1024, 2))  AS '数据容量(GB)',
       SUM(TRUNCATE(index_length / 1024 / 1024 / 1024, 2)) AS '索引容量(GB)'
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length) DESC, SUM(index_length) DESC;

2.查看所有数据库各表容量大小

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;

3.查看指定数据库各表容量大小

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 = 'mysql';

4.查看指定数据库各表容量大小

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 = 'mysql'
ORDER BY data_length DESC, index_length DESC;