mysql 数据库空间统计sql

mysql 数据库空间统计

文章目录

说明

INFORMATION_SCHEMA Table Reference 表参考

information_schema是‌MySQL中的一个特殊数据库，它存储了关于所有其他数据库的元数据信息。 这些元数据包括数据库名、表名、列的数据类型、访问权限等。通过查询information_schema，用户可以获取到关于数据库结构的详细信息，这对于数据库管理和优化非常有帮助。例如，可以通过查询information_schema来查看表的索引信息、视图定义、存储过程和函数的信息等。此外，由于information_schema中的表都是只读的，它们实际上可以被视为视图，因此用户无法直接修改这些数据，保证了元数据的完整性。‌

一、数据库存储代码

请注意
如果启用了innodb_read_only系统变量，ANALYZE TABLE可能会失败，因为它无法更新使用InnoDB的数据字典中的统计表。对于更新键分布的ANALYZE TABLE操作，即使操作更新表本身(例如，如果它是一个MyISAM表)，也可能发生失败。要获取更新的分布统计信息，可以设置information_schema_stats_expiry=0。

代码如下（GB）（示例）：以下 是GB的统计
查询一个实例的所有库的数据的大小总和

selectcoalesce(table_schema,'合计')as table_schema ,
concat(round(sum(data_length/1024/1024/1024),2),'GB')as data_length_GB, 
concat(round(sum(index_length/1024/1024/1024),2),'GB')as index_length_GB  ,
concat(round(sum(index_length/1024/1024/1024),2)+round(sum(data_length/1024/1024/1024),2),'GB')as tal_GB 
from information_schema.tables t where table_Type='BASE TABLE'and table_schema notin('document','mysql','performance_schema','sys')groupby table_schema

代码如下（MB）（示例）： MB
查询一个实例的所有库的数据的大小总和

selectcoalesce(table_schema,'合计')as table_schema,
concat(round(sum(data_length/1024/1024),2),'MB')as data_length_MB, 
concat(round(sum(index_length/1024/1024),2),'MB')as index_length_MB  ,
concat(round(sum(index_length/1024/1024),2)+round(sum(data_length/1024/1024),2),'MB')as tal_MB
from information_schema.tables t where table_Type='BASE TABLE'and table_schema notin('document','mysql','performance_schema','sys')groupby table_schema WITH ROLLUPorderbyround(sum(data_length/1024/1024),2)desc

二、查询某个数据库的所有表的 代码

SELECT
    TABLE_NAME,ENGINE, VERSION, ROW_FORMAT, TABLE_ROWS, AVG_ROW_LENGTH,
    DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE,AUTO_INCREMENT,
    CREATE_TIME, UPDATE_TIME, CHECK_TIME, TABLE_COLLATION, CHECKSUM,
    CREATE_OPTIONS, TABLE_COMMENT
  FROM INFORMATION_SCHEMA.TABLESWHERE table_schema ='db_name'[AND table_name LIKE'wild']SHOWTABLESTATUSFROM db_name
  [LIKE'wild']

The following statements are equivalent:

SELECT
  TABLE_NAME, TABLE_TYPE
  FROM INFORMATION_SCHEMA.TABLESWHERE table_schema ='db_name'[AND table_name LIKE'wild']SHOWFULLTABLESFROM db_name
  [LIKE'wild']

三、列出所有已经产生碎片的表

-- 列出所有已经产生碎片的表 ('information_schema', 'mysql'这两个库是mysql自带的库)select 
table_schema db, 
table_name,
 data_free,engine,
 table_rows,
 data_length+index_length length 
from
information_schema.tableswhere 
table_schema notin('information_schema','mysql')and data_free >0ORDERBY data_free desc

处理表碎片

altertable gd_channel_app_retention engine=innodb;

note：这个语句处理碎片空间其实是先复制现有数据表 然后删除旧的数据表 。如果这个表占用空间巨大，还是直接迁移数据吧。

具体可以查看 mysql 帮助

https://dev.mysql.com/doc/refman/8.0/en/information-schema-tables-table.html

总结

information_schema用于存储数据库元数据，本文sql 主要是 MySQL系统库之information_schema的实现，

查询数据库结构：information_schema 可用于查询数据库、表、列、索引、外键、触发器等对象的结构信息。
权限管理：可以使用 information_schema 查询用户和权限信息，以确保正确的访问控制和权限设置。
性能优化：information_schema 提供有关索引、表大小、表引擎等性能相关信息，这对于性能优化很有帮助。
查询执行计划：可以查询 information_schema 获取查询执行计划，以了解查询如何被执行。