Mysql数据库空间统计sql代码示例!

Mysql数据库空间统计sql代码示例!

在mysql中有一个information_schema数据库,这个数据库中装的是mysql的元数据,包括数据库信息、数据库中表的信息等,这篇文章主要给大家介绍了关于mysql数据库空间统计sql的相关资料,需要的朋友可以参考下。

说明

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的统计
查询一个实例的所有库的数据的大小总和

1
2
3
4
5
6
7
select coalesce(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 not in ('document','mysql','performance_schema','sys')
group by table_schema

202408090905401

代码如下(MB)(示例): MB
查询一个实例的所有库的数据的大小总和

1
2
3
4
5
6
7
select coalesce(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 not in ('document','mysql','performance_schema','sys')
group by table_schema WITH ROLLUP order by round(sum(data_length/1024/1024),2) desc

202408090905412

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

1
2
3
4
5
6
7
8
9
10
11
12
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.TABLES
WHERE table_schema = 'db_name'
[AND table_name LIKE 'wild']
SHOW TABLE STATUS
FROM db_name
[LIKE 'wild']

The following statements are equivalent:

1
2
3
4
5
6
7
8
9
SELECT
TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'db_name'
[AND table_name LIKE 'wild']
SHOW FULL TABLES
FROM db_name
[LIKE 'wild']

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

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 列出所有已经产生碎片的表 ('information_schema', 'mysql'这两个库是mysql自带的库)
select
table_schema db,
table_name,
data_free,
engine,
table_rows,
data_length+index_length length
from
information_schema.tables  
where
table_schema not in ('information_schema', 'mysql') and data_free > 0
ORDER BY data_free desc

处理表碎片

1
alter table gd_channel_app_retention engine=innodb;

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

参考资料:
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 获取查询执行计划,以了解查询如何被执行。

 

学习资料见知识星球。

以上就是今天要分享的技巧,你学会了吗?若有什么问题,欢迎在下方留言。

快来试试吧,小琥 my21ke007。获取 1000个免费 Excel模板福利​​​​!

更多技巧, www.excelbook.cn

欢迎 加入 零售创新 知识星球,知识星球主要以数据分析、报告分享、数据工具讨论为主;

电商数据分析360°实战攻略!

你将获得:

1、价值上万元的专业的PPT报告模板。

2、专业案例分析和解读笔记。

3、实用的Excel、Word、PPT技巧。

4、VIP讨论群,共享资源。

5、优惠的会员商品。

6、一次付费只需99元,即可下载本站文章涉及的文件和软件。

文章版权声明 1、本网站名称:Excelbook
2、本站永久网址:http://www.excelbook.cn
3、本网站的文章部分内容可能来源于网络,仅供大家学习与参考,如有侵权,请联系站长王小琥进行删除处理。
4、本站一切资源不代表本站立场,并不代表本站赞同其观点和对其真实性负责。
5、本站一律禁止以任何方式发布或转载任何违法的相关信息,访客发现请向站长举报。
6、本站资源大多存储在云盘,如发现链接失效,请联系我们我们会第一时间更新。

THE END
分享
二维码
< <上一篇
下一篇>>