原 MySQL查询数据库大小
Tags： 原创MySQL脚本小麦苗常用数据库大小表空间大小
👉 本文共约8个字，系统预计阅读时间或需1分钟。
目录
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 | /* 1、表和索引在同一个文件中，例如sbtest6.ibd文件中包括了索引和数据 2、主键索引的大小就是数据大小 3、SQL查询出来的总大小应该减去datafree才是真实的占用空间 4、若磁盘文件大小为空，请执行： ANALYZE TABLE 表名 ; */ select a.SCHEMA_NAME, a.DEFAULT_CHARACTER_SET_NAME,a.DEFAULT_COLLATION_NAME, sum(table_rows) as '记录数', truncate(sum(data_length)/1024/1024, 2) as '数据容量(MB)', truncate(sum(index_length)/1024/1024, 2) as '索引容量(MB)', truncate(sum(data_length+index_length)/1024/1024, 2) as '总大小(MB)', truncate(sum(max_data_length)/1024/1024, 2) as '最大值(MB)', truncate(sum(data_free)/1024/1024, 2) as '空闲空间(MB)', max(f.filesize_M) as '磁盘文件大小(MB)' from INFORMATION_SCHEMA.SCHEMATA a left outer join information_schema.tables b on a.SCHEMA_NAME=b.TABLE_SCHEMA left outer join (select substring(b.file_name,3,locate('/',b.file_name,3)-3) as db_name, truncate(sum(total_extents*extent_size)/1024/1024,2) filesize_M from information_schema.FILES b group by substring(b.file_name,3,locate('/',b.file_name,3)-3)) f on ( a.SCHEMA_NAME= f.db_name) group by a.SCHEMA_NAME, a.DEFAULT_CHARACTER_SET_NAME,a.DEFAULT_COLLATION_NAME UNION ALL select '总计', '','', sum(table_rows) as '记录数', truncate(sum(data_length)/1024/1024, 2) as '数据容量(MB)', truncate(sum(index_length)/1024/1024, 2) as '索引容量(MB)', truncate(sum(data_length+index_length)/1024/1024, 2) as '总大小(MB)', truncate(sum(max_data_length)/1024/1024, 2) as '最大值(MB)', truncate(sum(data_free)/1024/1024, 2) as '空闲空间(MB)', max(f.filesize_M) as '磁盘文件大小(MB)' from INFORMATION_SCHEMA.SCHEMATA a left outer join information_schema.tables b on a.SCHEMA_NAME=b.TABLE_SCHEMA left outer join (select substring(b.file_name,3,locate('/',b.file_name,3)-3) as db_name, truncate(sum(total_extents*extent_size)/1024/1024,2) filesize_M from information_schema.FILES b group by substring(b.file_name,3,locate('/',b.file_name,3)-3)) f on ( a.SCHEMA_NAME= f.db_name) order by `数据容量(MB)` , `索引容量(MB)` ; +--------------------+----------------------------+------------------------+---------+--------------+--------------+------------+------------------+--------------+------------------+ | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | 记录数 | 数据容量(MB) | 索引容量(MB) | 总大小(MB) | 最大值(MB) | 空闲空间(MB) | 磁盘文件大小(MB) | +--------------------+----------------------------+------------------------+---------+--------------+--------------+------------+------------------+--------------+------------------+ | db_monitor | utf8mb4 | utf8mb4_general_ci | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | performance_schema | utf8 | utf8_general_ci | 1335144 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | NULL | | db6 | utf8mb4 | utf8mb4_general_ci | 0 | 0.01 | 0.00 | 0.01 | 0.00 | 0.00 | 0.00 | | db8 | utf8mb4 | utf8mb4_general_ci | 4 | 0.01 | 0.00 | 0.01 | 0.00 | 0.00 | 0.00 | | percona_schema | utf8mb4 | utf8mb4_general_ci | 0 | 0.01 | 0.00 | 0.01 | 0.00 | 0.00 | 0.00 | | testdb | utf8mb4 | utf8mb4_general_ci | 14 | 0.01 | 0.01 | 0.03 | 0.00 | 0.00 | 0.00 | | db7 | utf8mb4 | utf8mb4_general_ci | 0 | 0.03 | 0.00 | 0.03 | 0.00 | 0.00 | 0.00 | | sys | utf8 | utf8_general_ci | 6 | 0.03 | 0.00 | 0.03 | 0.00 | 0.00 | 0.00 | | ceshi2 | utf8mb4 | utf8mb4_general_ci | 4 | 0.03 | 0.00 | 0.03 | 0.00 | 0.00 | 0.00 | | db3 | utf8mb4 | utf8mb4_general_ci | 15 | 0.04 | 0.00 | 0.04 | 0.00 | 0.00 | 0.00 | | db4 | utf8mb4 | utf8mb4_general_ci | 428 | 0.07 | 0.01 | 0.09 | 0.00 | 0.00 | 0.00 | | select_lhrdb | utf8mb4 | utf8mb4_general_ci | 34 | 0.09 | 0.03 | 0.12 | 0.00 | 0.00 | 0.00 | | ocp0126 | utf8mb4 | utf8mb4_general_ci | 1659 | 0.11 | 0.00 | 0.11 | 268435455.99 | 0.00 | 0.00 | | information_schema | utf8 | utf8_general_ci | NULL | 0.15 | 0.00 | 0.15 | 782.22 | 80.00 | NULL | | db2 | utf8mb4 | utf8mb4_general_ci | 60 | 0.15 | 0.04 | 0.20 | 0.00 | 0.00 | 0.00 | | orchestrator | utf8mb4 | utf8mb4_general_ci | 166 | 0.73 | 1.17 | 1.90 | 0.00 | 0.00 | 0.00 | | lhrdb1 | utf8mb4 | utf8mb4_general_ci | 23003 | 1.79 | 0.02 | 1.81 | 268435455.99 | 4.00 | 9.00 | | lhrdb2 | utf8mb4 | utf8mb4_general_ci | 22939 | 2.08 | 0.00 | 2.08 | 268435455.99 | 4.00 | 9.00 | | lhrdb3 | utf8mb4 | utf8mb4_general_ci | 22939 | 2.08 | 0.00 | 2.08 | 268435455.99 | 4.00 | 9.00 | | lhrdb | utf8mb4 | utf8mb4_general_ci | 22941 | 2.14 | 0.00 | 2.14 | 268435455.99 | 4.00 | 9.00 | | sakila | utf8mb4 | utf8mb4_general_ci | 47674 | 4.20 | 2.28 | 6.48 | 0.00 | 8.00 | 20.00 | | DB1 | utf8mb4 | utf8mb4_general_ci | 19568 | 5.56 | 0.75 | 6.31 | 536870911.99 | 8.00 | 10.00 | | mysql | latin1 | latin1_swedish_ci | 142157 | 8.57 | 0.24 | 8.81 | 1250640789503.99 | 12.00 | 30.00 | | sbtest | utf8mb4 | utf8mb4_general_ci | 99104 | 25.15 | 1.98 | 27.14 | 0.00 | 40.00 | 100.00 | | tpcc | latin1 | latin1_swedish_ci | 3268483 | 553.42 | 22.06 | 575.48 | 0.00 | 4.00 | 17.00 | | 总计 | | | 5006342 | 606.56 | 28.63 | 635.19 | 1252519838478.22 | 168.00 | 100.00 | +--------------------+----------------------------+------------------------+---------+--------------+--------------+------------+------------------+--------------+------------------+ 26 rows in set (0.33 sec) MySQL [(none)]> |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
