前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >计算MySQL表碎片的SQL整理

计算MySQL表碎片的SQL整理

作者头像
jeanron100
发布2019-09-26 16:28:31
2.9K0
发布2019-09-26 16:28:31
举报
文章被收录于专栏:杨建荣的学习笔记

这是学习笔记的第 2111 篇文章

在之前整理过一版MySQL的数据字典,整理了一圈,发现远比想象的复杂。

当然整理的过程不光是知识梳理的过程,也是转化为实践场景的一个过程,通过这样一个体系,对于整个MySQL对象生命周期管理有了较为深入的认识,这里我来抛砖引玉,来作为深入学习MySQL数据字典的一个入口,这个问题就是:如何较为准确的计算MySQL碎片情况?

我想碎片的情况在数据库中是很少有清晰的界定,不过它的的确确会带来副作用,通过修复碎片情况我们可以提高SQL的执行效率,同时能够释放大量的空间。

最近在思考中感悟到:我们所做的很多事情,难点主要都在于查找,比如我告诉你test库的表test_data存在大量碎片,需要修复一下,这个难度是完全可控的,我们可以很麻利的处理好,但是如果我告诉你需要收集下碎片情况,然后做一下改进,而不告诉你具体的情况,其实难度就会高几个层次。

我们这个场景主要会用到两个数据字典表:

information_schema.tables

information_schema.INNODB_SYS_TABLESPACES

我们依次来看一下两个数据字典的输出信息:

查询常规的数据字典tables得到的信息基本可以满足我们的大多数需求。

mysql> select *from information_schema.tables where table_name='tgp_redis_command'\G

*************************** 1. row ***************************

TABLE_CATALOG: def

TABLE_SCHEMA: tgp_db

TABLE_NAME: tgp_redis_command

TABLE_TYPE: BASE TABLE

ENGINE: InnoDB

VERSION: 10

ROW_FORMAT: Dynamic

TABLE_ROWS: 477103

AVG_ROW_LENGTH: 111

DATA_LENGTH: 53035008

MAX_DATA_LENGTH: 0

INDEX_LENGTH: 0

DATA_FREE: 5242880

AUTO_INCREMENT: 478096

CREATE_TIME: 2019-08-16 10:54:02

UPDATE_TIME: 2019-09-23 21:12:05

CHECK_TIME: NULL

TABLE_COLLATION: utf8_general_ci

CHECKSUM: NULL

CREATE_OPTIONS:

TABLE_COMMENT: redis命令执行记录表

1 row in set (0.00 sec)

通过tables字典我们可以得到通过逻辑计算出来的预估表大小,包括数据和索引的空间情况,还有平均行长度来作为校验。

但是在这里我们总是会感觉有些隔靴搔痒,因为我们通过计算得到了逻辑大小,但是我们还是无从得知物理文件的大小,如果逐个去通过du方式计算,这个成本是很高的,而且如果有很多的表,这种模式的效率和代价是不大合理的,所幸MySQL 5.7版本中的innodb_sys_tablespaces这个数据字典做了扩容,有了新的字段FILE_SIZE,可以完美的解决我们的疑虑,使用innodb_sys_tablespaces得到的结果如下:

mysql> select *from INNODB_SYS_TABLESPACES where name like 'tgp_db/tgp_redis_command'\G

*************************** 1. row ***************************

SPACE: 818

NAME: tgp_db/tgp_redis_command

FLAG: 33

FILE_FORMAT: Barracuda

ROW_FORMAT: Dynamic

PAGE_SIZE: 16384

ZIP_PAGE_SIZE: 0

SPACE_TYPE: Single

FS_BLOCK_SIZE: 4096

FILE_SIZE: 62914560

ALLOCATED_SIZE: 62918656

1 row in set (0.00 sec)

比如常规来说我们要得到表tgp_redis_command的物理文件大小(即.ibd文件),可以通过INNODB_SYS_TABLESPACES 来查询得到,这是一个缓存中刷新得到的实时的值,远比我们通过du等方式计算要快捷方便许多。

可以做一个简单的计算,表里的数据量为:

mysql> select count(*) from tgp_redis_command;

+----------+

| count(*) |

+----------+

| 478093 |

+----------+

1 row in set (0.06 sec)

物理文件的大小,和innodb_sys_tablespaces的结果是完全一致的。

# ll *redis*

-rw-r----- 1 mysql mysql 9176 Aug 16 10:54 tgp_redis_command.frm

-rw-r----- 1 mysql mysql 62914560 Sep 23 21:14 tgp_redis_command.ibd

所以表的大小逻辑计算为data_length+index_length=53035008+0,大约是50M左右,而物理文件大小是60M左右,那么碎片率大约是(60-50)/60约等于16.7%

我们做一下数据的truncate操作,发现物理文件的大小很快收缩了。

mysql> select *from INNODB_SYS_TABLESPACES where name like 'tgp_db/tgp_redis_command'\G

*************************** 1. row ***************************

SPACE: 818

NAME: tgp_db/tgp_redis_command

FLAG: 33

FILE_FORMAT: Barracuda

ROW_FORMAT: Dynamic

PAGE_SIZE: 16384

ZIP_PAGE_SIZE: 0

SPACE_TYPE: Single

FS_BLOCK_SIZE: 4096

FILE_SIZE: 98304

ALLOCATED_SIZE: 102400

1 row in set (0.00 sec)

mysql> select *from information_schema.tables where table_name='tgp_redis_command'\G

*************************** 1. row ***************************

TABLE_CATALOG: def

TABLE_SCHEMA: tgp_db

TABLE_NAME: tgp_redis_command

TABLE_TYPE: BASE TABLE

ENGINE: InnoDB

VERSION: 10

ROW_FORMAT: Dynamic

TABLE_ROWS: 0

AVG_ROW_LENGTH: 0

DATA_LENGTH: 16384

MAX_DATA_LENGTH: 0

INDEX_LENGTH: 0

DATA_FREE: 0

AUTO_INCREMENT: 1

CREATE_TIME: 2019-08-16 10:54:02

UPDATE_TIME: 2019-09-24 09:51:22

CHECK_TIME: NULL

TABLE_COLLATION: utf8_general_ci

CHECKSUM: NULL

CREATE_OPTIONS:

TABLE_COMMENT: redis命令执行记录表

1 row in set (0.00 sec)

[root@hb30-dba-mysql-tgp-124-34 tgp_db]# ll *redis*

-rw-r----- 1 mysql mysql 9176 Aug 16 10:54 tgp_redis_command.frm

-rw-r----- 1 mysql mysql 98304 Sep 24 09:55 tgp_redis_command.ibd

当然这种计算方式是不够完整的,而且不够清晰,我们可以写一个简单的SQL来做下统计,就是把那些需要修复的表列出来即可。

SQL如下:

SELECT

t.table_schema,

t.table_name,

t.table_rows,

t.data_length+

t.index_length data_size,

t.index_length index_size,

t.avg_row_length,

t.avg_row_length * t.table_rows logic_size,

s.FILE_SIZE,

truncate(s.FILE_SIZE/ (t.data_length+ t.index_length)*1.1*2 ,0)tab_frag

FROM

information_schema.tables t,

information_schema.INNODB_SYS_TABLESPACES s

WHERE

t.table_type = 'BASE TABLE'

and concat(t.table_schema,'/',t.table_name)=s.name

and t.table_schema not in ('sys','information_schema','mysql','test')

-- and t.table_schema in('tgp_db','test')

and s.FILE_SIZE >102400000

and (t.data_length+ t.index_length)*1.1*2 < s.FILE_SIZE

order by s.FILE_SIZE;

以如下的输出为例,我们可以看到整个碎片率极高,基本就是逻辑大小为100M,实际大小为500M,类似这种情况。

其中对于逻辑大小的计算做了一些取舍,默认在MySQL中变化的数据在10%以外是会重新去统计计算的,所以我们可以把基数调整的稍大一些为1.1,然后以这个为基线,如果碎片率超过了200%则计入统计结果中。

通过这种方式我们可以很快的分析出那些要具体修复的表,而整个性能的分析也可以更加清晰。

稍后,把它包装为一个批量异步任务,通过异步任务来得到尽可能完整的碎片表列表,然后集中去处理就好了。

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2019-09-25,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 杨建荣的学习笔记 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档