【MySQL】テーブルの断片化を確認しよう

はじめに

自分用にテーブルが断片化してるか否かを確認する方法を書いてみました。

もし他にこんなのあるよー、って話あれば教えてください。

SHOW TABLE STATUS

• Data_freeを見ると良い
• innodb_file_per_tableオプションが有効であるべき

mysql> SHOW TABLE STATUS FROM test\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 98304
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 1015
    Create_time: 2020-11-19 12:38:12
    Update_time: 2020-11-19 12:47:15
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.01 sec)

SHOW TABLE STATUS 構文については以下のドキュメントに詳細がありますので、興味のある方は読んでみてください。

MySQL :: MySQL 5.6 Reference Manual :: 13.7.5.37 SHOW TABLE STATUS Statement

information_schema.tablesで確認

とりあえずの概算値ではあるが、100MB以上のdata_freeがあるようなテーブルの一覧を出してみる方法。

SELECT 
    table_schema, table_name, data_free/1024/1024 AS data_free_MB
FROM 
    INFORMATION_SCHEMA.TABLES 
WHERE 
    engine LIKE 'InnoDB'
 AND 
    data_free > 100*1024*1024;

INFORMATION_SCHEMA TABLES テーブルについては以下のドキュメントに詳細がありますので、興味のある方は読んでみてください。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 21.22 INFORMATION_SCHEMA TABLES テーブル

innodb_ruby

Ruby製のMySQL InnoDBテーブルの断片化を見ることができるツール。

インストールについてはここ参照

使い方のイメージ

$ innodb_space -f /home/takiida/git/mysql/bld/data/test/t1.ibd  space-extents-illustrate

【番外編】バッファプール上のデータのフラグメンテーションの見積もり

こちらはファイルではなく、バッファプール上のデータのフラグメンテーションを見るものです。

SELECT 
    table_name, index_name, AVG(data_size), COUNT(*)
FROM
    INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE
    table_name IS NOT NULL
GROUP BY table_name , index_name
HAVING COUNT(*) > 100
ORDER BY AVG(data_size) ASC
LIMIT 10;

INFORMATION_SCHEMA INNODB_BUFFER_PAGE テーブルについては以下のドキュメントに詳細がありますので、興味のある方は読んでみてください。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 21.29.16 INFORMATION_SCHEMA INNODB_BUFFER_PAGE テーブル

最後に

とりあえず5.6のでも問題ないから読みやすい日本語ドキュメントを選んでます。

MySQL8.0の日本語ドキュメント公開されたらリンクは差し替えようかなと思っとります。