如何建设内部网站,网站单选框的实现,ps软件免费,政务服务网站建设文档原标题#xff1a;计算MySQL表碎片的SQL整理这是学习笔记的第 2111 篇文章在之前整理过一版MySQL的数据字典#xff0c;整理了一圈#xff0c;发现远比想象的复杂。当然整理的过程不光是知识梳理的过程#xff0c;也是转化为实践场景的一个过程#xff0c;通过这样一个体系…原标题计算MySQL表碎片的SQL整理这是学习笔记的第 2111 篇文章在之前整理过一版MySQL的数据字典整理了一圈发现远比想象的复杂。当然整理的过程不光是知识梳理的过程也是转化为实践场景的一个过程通过这样一个体系对于整个MySQL对象生命周期管理有了较为深入的认识这里我来抛砖引玉来作为深入学习MySQL数据字典的一个入口这个问题就是如何较为准确的计算MySQL碎片情况我想碎片的情况在数据库中是很少有清晰的界定不过它的的确确会带来副作用通过修复碎片情况我们可以提高SQL的执行效率同时能够释放大量的空间。最近在思考中感悟到我们所做的很多事情难点主要都在于查找比如我告诉你test库的表test_data存在大量碎片需要修复一下这个难度是完全可控的我们可以很麻利的处理好但是如果我告诉你需要收集下碎片情况然后做一下改进而不告诉你具体的情况其实难度就会高几个层次。我们这个场景主要会用到两个数据字典表information_schema.tablesinformation_schema.INNODB_SYS_TABLESPACES我们依次来看一下两个数据字典的输出信息查询常规的数据字典tables得到的信息基本可以满足我们的大多数需求。mysql select *from information_schema.tables where table_nametgp_redis_command\G*************************** 1. row ***************************TABLE_CATALOG: defTABLE_SCHEMA: tgp_dbTABLE_NAME: tgp_redis_commandTABLE_TYPE: BASE TABLEENGINE: InnoDBVERSION: 10ROW_FORMAT: DynamicTABLE_ROWS: 477103AVG_ROW_LENGTH: 111DATA_LENGTH: 53035008MAX_DATA_LENGTH: 0INDEX_LENGTH: 0DATA_FREE: 5242880AUTO_INCREMENT: 478096CREATE_TIME: 2019-08-16 10:54:02UPDATE_TIME: 2019-09-23 21:12:05CHECK_TIME: NULLTABLE_COLLATION: utf8_general_ciCHECKSUM: NULLCREATE_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: 818NAME: tgp_db/tgp_redis_commandFLAG: 33FILE_FORMAT: BarracudaROW_FORMAT: DynamicPAGE_SIZE: 16384ZIP_PAGE_SIZE: 0SPACE_TYPE: SingleFS_BLOCK_SIZE: 4096FILE_SIZE: 62914560ALLOCATED_SIZE: 629186561 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_lengthindex_length530350080大约是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: 818NAME: tgp_db/tgp_redis_commandFLAG: 33FILE_FORMAT: BarracudaROW_FORMAT: DynamicPAGE_SIZE: 16384ZIP_PAGE_SIZE: 0SPACE_TYPE: SingleFS_BLOCK_SIZE: 4096FILE_SIZE: 98304ALLOCATED_SIZE: 1024001 row in set (0.00 sec)mysql select *from information_schema.tables where table_nametgp_redis_command\G*************************** 1. row ***************************TABLE_CATALOG: defTABLE_SCHEMA: tgp_dbTABLE_NAME: tgp_redis_commandTABLE_TYPE: BASE TABLEENGINE: InnoDBVERSION: 10ROW_FORMAT: DynamicTABLE_ROWS: 0AVG_ROW_LENGTH: 0DATA_LENGTH: 16384MAX_DATA_LENGTH: 0INDEX_LENGTH: 0DATA_FREE: 0AUTO_INCREMENT: 1CREATE_TIME: 2019-08-16 10:54:02UPDATE_TIME: 2019-09-24 09:51:22CHECK_TIME: NULLTABLE_COLLATION: utf8_general_ciCHECKSUM: NULLCREATE_OPTIONS:TABLE_COMMENT: redis命令执行记录表1 row in set (0.00 sec)[roothb30-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如下SELECTt.table_schema,t.table_name,t.table_rows,t.data_lengtht.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_fragFROMinformation_schema.tables t,information_schema.INNODB_SYS_TABLESPACES sWHEREt.table_type BASE TABLEand concat(t.table_schema,/,t.table_name)s.nameand t.table_schema not in (sys,information_schema,mysql,test)-- and t.table_schema in(tgp_db,test)and s.FILE_SIZE 102400000and (t.data_length t.index_length)*1.1*2 s.FILE_SIZEorder by s.FILE_SIZE;以如下的输出为例我们可以看到整个碎片率极高基本就是逻辑大小为100M实际大小为500M类似这种情况。其中对于逻辑大小的计算做了一些取舍默认在MySQL中变化的数据在10%以外是会重新去统计计算的所以我们可以把基数调整的稍大一些为1.1然后以这个为基线如果碎片率超过了200%则计入统计结果中。通过这种方式我们可以很快的分析出那些要具体修复的表而整个性能的分析也可以更加清晰。稍后把它包装为一个批量异步任务通过异步任务来得到尽可能完整的碎片表列表然后集中去处理就好了。返回搜狐查看更多责任编辑
