mysql 碎片率_面试官：如何在mysql数据库查找碎片程度很高的表？

概述

今天主要分享下我们怎么在mysql数据库去统计表的碎片程度？

思路

这里主要会用到两个数据字典表：

information_schema.tables

information_schema.INNODB_SYS_TABLESPACES

结合innodb_sys_tablespaces这个数据字典的字段FILE_SIZE计算表的物理文件大小和逻辑大小。

表的大小逻辑计算为data_length+index_length=xxx，假设是AM左右，而物理文件大小是BM左右，那么碎片率大约是(B-A)/60*100%。

碎片程度比较高的表：

下面大家也可以把基数调整的稍大一些为1.1，然后以这个为基线来做统计。

SELECT

\tt.table_schema,

\tt.table_name,

\tt.table_rows,

\tt.data_length + t.index_length data_size,

\tt.index_length index_size,

\tt.avg_row_length,

\tt.avg_row_length * t.table_rows logic_size,

\ts.FILE_SIZE,

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

FROM

\tinformation_schema.TABLES t,

\tinformation_schema.INNODB_SYS_TABLESPACES s

WHERE

\tt.table_type = 'BASE TABLE'

\tAND concat( t.table_schema, '/', t.table_name ) = s.NAME

\tAND t.table_schema NOT IN ( 'sys', 'information_schema', 'mysql', 'test' )

\t-- and t.table_schema in('fsl_prod','fsl_prod_diaocha')

\tAND s.FILE_SIZE > 102400000

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

ORDER BY

\ts.FILE_SIZE;

觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~