文档章节

怎样在磁盘上查找MySQL表的大小?这里有答案

Linux就该这么学
 Linux就该这么学
发布于 09/22 17:10
字数 1043
阅读 6
收藏 0
我想知道 MySQL 表在磁盘上占用多少空间,但看起来很琐碎。不应该在 INFORMATION_SCHEMA.TABLES 中提供这些信息吗?没那么简单!

我想知道 MySQL 表在磁盘上占用多少空间,但看起来很琐碎。不应该在 INFORMATION_SCHEMA.TABLES 中提供这些信息吗?没那么简单!

怎样在磁盘上查找MySQL表的大小?这里有答案怎样在磁盘上查找MySQL表的大小?这里有答案

这个看似简单的问题实际上在 MySQL 中非常复杂。MySQL 支持许多存储引擎(其中一些根本不在磁盘上存储数据), 不同的存储数据格式。例如,InnoDB 存储引擎为 MySQL 5.7 提供了三种“基本”格式,其中包含 row_formats 和两种可压缩的种类。

简化一下:我们如何在磁盘上查找存储在其自己的表空间中的 InnoDB 表的表大小(前提是 innodb_file_per_table=1 )。

在我们得到答案之前,先展示通过 sysbench 运行预先获得的图表(批量数据插入表):

在磁盘上查找 MySQL 表的大小

怎样在磁盘上查找MySQL表的大小?这里有答案怎样在磁盘上查找MySQL表的大小?这里有答案
此图显示了从 INFORMATION_SCHEMA.TABLES 获取的 data_length 和 index_length 所定义的表大小。可以预期,随着数据的增多,表格会跳跃增长(有时会增加 10GB 或更多)。

该图表与磁盘上数据的变化方式不匹配,它逐渐增长(如预期):

-rw-r----- 1 mysql mysql 220293234688 Jan 25 17:03 sbtest1.ibd
-rw-r----- 1 mysql mysql 220310011904 Jan 25 17:03 sbtest1.ibd
-rw-r----- 1 mysql mysql 222499438592 Jan 25 17:07 sbtest1.ibd

正如我们从这个实验中看到的那样,MySQL 并没有真正的实时维护 data_length 和 index_length 的值,而是定期刷新它们 - 而且不规则地刷新它们。图表的后半部分一些数据刷新变得更加规律。这与图表的第一部分不同,后者似乎每次有 10% 的行更改时,就更新一次统计信息。table_rows, data_free 或 update_time ,它们也是实时更新的。

要在 MySQL 5.7获取 information_schema 获取到更准确的实时信息,需要做两件事:

禁用 innodb_stats_persistent
启用 innodb_stats_on_metadata
这两者都会带来严重的代价。

禁用持久性统计信息意味着每次服务器启动时 InnoDB 都必须刷新统计信息,这代价很大,并且可能会在重新启动之间产生不稳定的查询计划。那有没有更好的办法呢?事实证明有。

可以通过 INNODB_SYS_TABLESPACES 查看表空间信息表以查看实际文件大小。与 index_length 和 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)

使用这个表的好处是,它还处理新功能 “InnoDB 页压缩”,正确显示了 file_size (磁盘上的逻辑文件大小)和 allocated_size(为此文件分配的空间,并且可以显着缩小)之间的区别。

最后,让我们看一下不同的 InnoDB 压缩方式如何影响 information_schema 中提供的信息。

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)

如果您使用旧的 InnoDB 压缩(InnoDB 表压缩),您将看到 data_length 和 index_length 中显示的压缩数据大小作为结果。例如, avg_row_length 将远低于您的预期。

如果在 MySQL 5.7 中使用新的 InnoDB 压缩(InnoDB 页压缩),您将看到与文件大小相对应的值,而不是如 information_schema 中所示的分配大小。

结论

回答一个微不足道的问题“这个表在磁盘上占用了多少空间?” 在 MySQL 中真的不是一个简单的问题 - 显而易见的数据,可能会得到错误的答案。

查看 INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES 以获取 InnoDB 表的实际文件大小值。

本文地址:https://www.linuxprobe.com/find-mysql-tables.html

本文转载自:https://www.linuxprobe.com/find-mysql-tables.html

Linux就该这么学
粉丝 24
博文 562
码字总数 462372
作品 0
东城
程序员
私信 提问
技术分享 | 在磁盘上查找 MySQL 表的大小

作者:Peter Zaitsev 翻译:管长龙 我想知道 MySQL 表在磁盘上占用多少空间,但看起来很琐碎。不应该在 中提供这些信息吗?没那么简单! 这个看似简单的问题实际上在 MySQL 中非常复杂。MyS...

爱可生
09/02
29
0
insert into...select需要注意的问题

平常习惯生产数据的时候,直接用insert into ...select这种语法,结果今天需要的数据量稍微一大就出错了,错误信息如下: mysql> insert into t1 select null, b, c from t1; ERROR 126 (HY...

长平狐
2012/11/01
373
0
MySQL索引背后的数据结构及算法原理

摘要 本文以MySQL数据库为研究对象,讨论与数据库索引相关的一些话题。特别需要说明的是,MySQL支持诸多存储引擎,而各种存储引擎对索引的支持也各不相同,因此MySQL数据库支持多种索引类型,...

asdf08442a
2017/12/26
39
0
从程序员的角度深入理解MySQL

前言 作为一名工作了4年的程序猿,今天我将站在程序员的角度以MySQL为例探索数据库的奥秘! 数据库基本原理 第一,数据库的组成:存储 + 实例 不必多说,数据当然需要存储;存储了还不够,显...

zfz_linux_boy
2017/06/11
0
0
InnoDB:B+Tree、聚簇、二级索引原理、架构及应用

1、二叉树 二叉搜索树是最为大家所熟知的一种数据结构,就不展开介绍了,它为什么不适合用作数据库索引? (1)当数据量大的时候,树的高度会比较高,数据量大的时候,查询会比较慢; (2)每个节...

大数据之路
2012/08/30
2.2K
0

没有更多内容

加载失败,请刷新页面

加载更多

视频如何加水印?

很多视频制作者的视频都被他人盗用过,为了防止自己的劳动成果被他人窃取,给视频加水印对于视频制作者来说,是一件非常重要的事情。那么下面分享一个手机给视频加水印的方法,一起来看看吧!...

白米稀饭2019
32分钟前
5
0
004-Envelop-基于Blockstack的文件传输dapp

本篇文章主要介绍基于Blockstack的文件传输工具; ####A-链接地址 官网地址:https://envelop.app/ Github地址:https://github.com/envelop-app ####B-特性: 1: Share private files easil...

Riverzhou
35分钟前
7
0
SpringCloud——声明式调用Feign

Feign声明式调用 一、Feign简介 使用Ribbon和RestTemplate消费服务的时候,有一个最麻烦的点在于,每次都要拼接URL,组织参数,所以有了Feign声明式调用,Feign的首要目标是将Java HTTP客户端...

devils_os
40分钟前
7
0
《JAVA核心知识》学习笔记 (22. 数据结构)

22.1.1. 栈(stack) 栈( stack)是限制插入和删除只能在一个位置上进行的表,该位置是表的末端,叫做栈顶 (top)。它是后进先出(LIFO)的。对栈的基本操作只有 push(进栈)和 pop(出栈...

Shingfi
46分钟前
6
0
你对AJAX认知有多少(1)?

AJAX(一) AJAX技术对于前段或者后端工程师来说,都是必不可缺的 那我们这几期都来细细品味一下AJAX的相关知识,直接上干货喽~ 1、什么是AJAX,为什么要使用Ajax(请谈一下你对Ajax的认识) 什么...

理性思考
54分钟前
15
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部