mysql 数据库空间统计sql

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: mysql 数据库空间统计sql

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的统计

查询一个实例的所有库的数据的大小总和

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


代码如下(MB)(示例): MB

查询一个实例的所有库的数据的大小总和

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 

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

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:


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']

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

-- 列出所有已经产生碎片的表 ('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 

处理表碎片

alter table 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 获取查询执行计划,以了解查询如何被执行。


相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
13
分享
相关文章
MySQL进阶突击系列(07) 她气鼓鼓递来一条SQL | 怎么看执行计划、SQL怎么优化?
在日常研发工作当中,系统性能优化,从大的方面来看主要涉及基础平台优化、业务系统性能优化、数据库优化。面对数据库优化,除了DBA在集群性能、服务器调优需要投入精力,我们研发需要负责业务SQL执行优化。当业务数据量达到一定规模后,SQL执行效率可能就会出现瓶颈,影响系统业务响应。掌握如何判断SQL执行慢、以及如何分析SQL执行计划、优化SQL的技能,在工作中解决SQL性能问题显得非常关键。
MySQL原理简介—1.SQL的执行流程
本文介绍了MySQL驱动、数据库连接池及SQL执行流程的关键组件和作用。主要内容包括:MySQL驱动用于建立Java系统与数据库的网络连接;数据库连接池提高多线程并发访问效率;MySQL中的连接池维护多个数据库连接并进行权限验证;网络连接由线程处理,监听请求并读取数据;SQL接口负责执行SQL语句;查询解析器将SQL语句解析为可执行逻辑;查询优化器选择最优查询路径;存储引擎接口负责实际的数据操作;执行器根据优化后的执行计划调用存储引擎接口完成SQL语句的执行。整个流程确保了高效、安全地处理SQL请求。
139 75
Docker Compose V2 安装常用数据库MySQL+Mongo
以上内容涵盖了使用 Docker Compose 安装和管理 MySQL 和 MongoDB 的详细步骤,希望对您有所帮助。
95 42
如何排查和解决PHP连接数据库MYSQL失败写锁的问题
通过本文的介绍,您可以系统地了解如何排查和解决PHP连接MySQL数据库失败及写锁问题。通过检查配置、确保服务启动、调整防火墙设置和用户权限,以及识别和解决长时间运行的事务和死锁问题,可以有效地保障应用的稳定运行。
49 25
MySQL原理简介—10.SQL语句和执行计划
本文介绍了MySQL执行计划的相关概念及其优化方法。首先解释了什么是执行计划,它是SQL语句在查询时如何检索、筛选和排序数据的过程。接着详细描述了执行计划中常见的访问类型,如const、ref、range、index和all等,并分析了它们的性能特点。文中还探讨了多表关联查询的原理及优化策略,包括驱动表和被驱动表的选择。此外,文章讨论了全表扫描和索引的成本计算方法,以及MySQL如何通过成本估算选择最优执行计划。最后,介绍了explain命令的各个参数含义,帮助理解查询优化器的工作机制。通过这些内容,读者可以更好地理解和优化SQL查询性能。
【MySQL基础篇】全面学习总结SQL语法、DataGrip安装教程
本文详细介绍了MySQL中的SQL语法,包括数据定义(DDL)、数据操作(DML)、数据查询(DQL)和数据控制(DCL)四个主要部分。内容涵盖了创建、修改和删除数据库、表以及表字段的操作,以及通过图形化工具DataGrip进行数据库管理和查询。此外,还讲解了数据的增、删、改、查操作,以及查询语句的条件、聚合函数、分组、排序和分页等知识点。
【MySQL基础篇】全面学习总结SQL语法、DataGrip安装教程
【深入了解MySQL】优化查询性能与数据库设计的深度总结
本文详细介绍了MySQL查询优化和数据库设计技巧,涵盖基础优化、高级技巧及性能监控。
275 0
数据库传奇:MySQL创世之父的两千金My、Maria
《数据库传奇:MySQL创世之父的两千金My、Maria》介绍了MySQL的发展历程及其分支MariaDB。MySQL由Michael Widenius等人于1994年创建,现归Oracle所有,广泛应用于阿里巴巴、腾讯等企业。2009年,Widenius因担心Oracle收购影响MySQL的开源性,创建了MariaDB,提供额外功能和改进。维基百科、Google等已逐步替换为MariaDB,以确保更好的性能和社区支持。掌握MariaDB作为备用方案,对未来发展至关重要。
74 3
MySQL崩溃保险箱:探秘Redo/Undo日志确保数据库安全无忧!
《MySQL崩溃保险箱:探秘Redo/Undo日志确保数据库安全无忧!》介绍了MySQL中的三种关键日志:二进制日志(Binary Log)、重做日志(Redo Log)和撤销日志(Undo Log)。这些日志确保了数据库的ACID特性,即原子性、一致性、隔离性和持久性。Redo Log记录数据页的物理修改,保证事务持久性;Undo Log记录事务的逆操作,支持回滚和多版本并发控制(MVCC)。文章还详细对比了InnoDB和MyISAM存储引擎在事务支持、锁定机制、并发性等方面的差异,强调了InnoDB在高并发和事务处理中的优势。通过这些机制,MySQL能够在事务执行、崩溃和恢复过程中保持
127 3

推荐镜像

更多