如何查看MySQL的表空间

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: SELECT CONCAT( table_schema, '.', table_name ) table_name, CONCAT( ROUND( data_length / ( 1024 *1024 ) , 2 ) , 'M' ) data_length, CONCAT( R.

常规方法

通常情况下,我们一般的思路是通过下面的方式获取表空间:

SELECT CONCAT( table_schema, '.', table_name ) table_name, 
    CONCAT( ROUND( data_length / ( 1024 *1024 ) , 2 ) , 'M' ) data_length, 
    CONCAT( ROUND( index_length / ( 1024 *1024 ) , 2 ) , 'M' ) index_length, 
    CONCAT( ROUND( data_free / ( 1024 *1024 ) , 2 ) , 'M' ) free_length, 
    CONCAT( ROUND( ROUND( data_length + index_length + data_free ) / ( 1024 *1024 ) , 2 ) , 'M' ) total_size, 
    CONCAT( ROUND( ROUND( data_free ) / ( 1024 *1024 ) , 2 ) , 'M' ) total_wasted,
             (100*(data_free/(data_length + index_length + data_free))) percent_wasted
FROM information_schema.TABLES
WHERE table_schema = '[NAME_OF_DPA_DATABASE]'
    AND (100*(data_free/(data_length + index_length+data_free))) > [MINIMUM_PERCENT_WASTED]
    AND (data_length + index_length + data_free) > [MINIMUM_FILESIZE_IN_BYTES]
ORDER BY (data_length + index_length + data_free) DESC;

例如:

SELECT CONCAT( table_schema, '.', table_name ) table_name, 
    CONCAT( ROUND( data_length / ( 1024 *1024 ) , 2 ) , 'M' ) data_length, 
    CONCAT( ROUND( index_length / ( 1024 *1024 ) , 2 ) , 'M' ) index_length, 
    CONCAT( ROUND( data_free / ( 1024 *1024 ) , 2 ) , 'M' ) free_length, 
    CONCAT( ROUND( ROUND( data_length + index_length + data_free ) / ( 1024 *1024 ) , 2 ) , 'M' ) total_size, 
    CONCAT( ROUND( ROUND( data_free ) / ( 1024 *1024 ) , 2 ) , 'M' ) total_wasted,
             (100*(data_free/(data_length + index_length + data_free))) percent_wasted
FROM information_schema.TABLES
WHERE table_schema = 'dpa_repo'
    AND (100*(data_free/(data_length + index_length+data_free))) > 75
    AND (data_length + index_length + data_free) > 52428800
ORDER BY (data_length + index_length + data_free) DESC;

可能没有注意到的是

对于上述方法中的data_length 或 index_length的值MySQL并不是实时更新的,而是周期性地维护,通过测试发现当10%的行被改变时,data_length 或 index_length与正在更新的统计数据一致。
而table_rows, data_free 或 update_time却是实时更新的。
那么有没有方法在我们查询information_schema时获取到data_length和 index_length的值呢?
在MySQL 5.7里面如果想获取information_schema精确值,就要disable innodb_stats_persistent 和 enable innodb_stats_on_metadata,这两种方法都有明显的副作用。
disable innodb_stats_persistent意味着每次在MySQL启动时才会刷新统计信息,这中代价非常昂贵并且会在重启之间生成易失性查询计划。
enabling innodb_stats_on_metadata会使每次访问information_schema的时候非常非常慢。

有没有更好的办法

可以查看information.INNODB_SYS_TABLESPACES查看真实的文件大小,不像index_length and data_length,INNODB_SYS_TABLESPACES的值是实时更新的,也不需要额外的配置:

mysql> select * from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES where name='sbinnodb/sbtest1' G
*************************** 1. row ***************************
        SPACE: 42
         NAME: sbinnodb/sbtest1
         FLAG: 33
  FILE_FORMAT: Barracuda
   ROW_FORMAT: Dynamic
    PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0
   SPACE_TYPE: Single
FS_BLOCK_SIZE: 4096
    FILE_SIZE: 245937209344
ALLOCATED_SIZE: 245937266688
1 row in set (0.00 sec)

用这个表还有一个更好的事情是:它可以处理MySQL 5.7 新的“Innodb Page Compression”,这不同于file_size(它是在磁盘上的逻辑文件的大小),也不同于allocated_size(它是文件已经分配的空间,可以小很多)

mysql> select * from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES where name='sbinnodb/testcomp' G
*************************** 1. row ***************************
        SPACE: 48
         NAME: sbinnodb/testcomp
         FLAG: 33
  FILE_FORMAT: Barracuda
   ROW_FORMAT: Dynamic
    PAGE_SIZE: 16384
ZIP_PAGE_SIZE: 0
   SPACE_TYPE: Single
FS_BLOCK_SIZE: 4096
    FILE_SIZE: 285212672
ALLOCATED_SIZE: 113004544
1 row in set (0.00 sec)

结论

查INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES获取INNODB表的真实文件大小。

参考

https://www.percona.com/blog/2016/01/26/finding_mysql_table_size_on_disk/
https://support.solarwinds.com/Success_Center/Database_Performance_Analyzer_(DPA)/Knowledgebase_Articles/MySQL_repository_table_optimization_for_DPA
https://dev.mysql.com/doc/refman/5.7/en/innodb-persistent-stats.html

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
存储 关系型数据库 MySQL
第9章 【MySQL】InnoDB的表空间
第9章 【MySQL】InnoDB的表空间
114 0
第9章 【MySQL】InnoDB的表空间
|
22天前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的表空间
InnoDB是MySQL默认的存储引擎,主要由存储结构、内存结构和线程结构组成。其存储结构分为逻辑和物理两部分,逻辑存储结构包括表空间、段、区和页。表空间是InnoDB逻辑结构的最高层,所有数据都存放在其中。默认情况下,InnoDB有一个共享表空间ibdata1,用于存放撤销信息、系统事务信息等。启用参数`innodb_file_per_table`后,每张表的数据可以单独存放在一个表空间内,但撤销信息等仍存放在共享表空间中。
|
2月前
|
存储 监控 关系型数据库
MySQL造数据占用临时表空间
MySQL造数据占用临时表空间
40 0
|
6月前
|
存储 关系型数据库 MySQL
MySQL数据库——InnoDB引擎-逻辑存储结构(表空间、段、区、页、行)
MySQL数据库——InnoDB引擎-逻辑存储结构(表空间、段、区、页、行)
125 7
|
7月前
|
自然语言处理 监控 关系型数据库
mysql造数据占用临时表空间
【5月更文挑战第20天】MySQL在处理复杂查询时可能使用临时表,可能导致性能下降。临时表用于排序、分组和连接操作。常见问题包括内存限制、未优化的查询、数据类型不当和临时表清理。避免过度占用的策略包括优化查询、调整系统参数、优化数据类型和事务管理。使用并行查询、分区表和监控工具也能帮助管理临时表空间。通过智能问答工具如通义灵码,可实时续写SQL和获取优化建议。注意监控`Created_tmp_tables`和`Created_tmp_disk_tables`以了解临时表使用状况。
437 5
|
7月前
|
存储 关系型数据库 MySQL
MySQL表空间结构与页、区、段的定义
一、概念引入 1、页 InnoDB是以页为单位管理存储空间的,在InnoDB中针对不同的目的设计了各种不同类型的页面。如下(省略了FIL_PAGE或FiL_PAGE_TYPE的前缀):
|
7月前
|
存储 关系型数据库 MySQL
9.3 【MySQL】系统表空间
9.3 【MySQL】系统表空间
84 0
|
7月前
|
存储 缓存 关系型数据库
Mysql专栏 - 缓冲池补充、数据页、表空间简述
Mysql专栏 - 缓冲池补充、数据页、表空间简述
68 0
|
存储 SQL 关系型数据库
面试官:mysql 表删除一半数据,表空间会变小吗?
这期面试官提的问题是: MySQL 表删除一半数据,表空间是否会变小?为什么?
|
关系型数据库 MySQL
MySQL delete后怎么释放表空间
MySQL delete后怎么释放表空间
259 0