概述

今天主要分享下我們怎麼在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方面的內容，感興趣的朋友可以關注下~