MySQL道普請便り

第189回information_schemaのInnoDB関連テーブルその2]

以前に筆者が担当した第186回 information_schemaのInnoDB関連テーブル[その1]に続いて、information_schemaのInnoDB関連テーブルについて紹介します。先に186回の記事をご確認ください。同回と同様に、MySQL 8.0.31を使用します。

INNODB_TABLESTATSビュー

INNODB_TABLESTATSビューから、InnoDBテーブルに関する統計情報のステータスを確認できます。まずは、簡単にこのビューのカラムとその意味について紹介します。

  • NAMEINNODB_TABLES.NAMEと同様のテーブル名
  • STATS_INITIALIZED… 統計情報が収集されている場合はInitialized、されていない場合はUninitialized
  • NUM_ROWS… 統計情報で推定されたテーブルの行数
  • CLUST_INDEX_SIZE… 統計情報で推定されたクラスターインデックスのページ数
  • OTHER_INDEX_SIZE… 統計情報で推定されたセカンダリーインデックスのページ数
  • MODIFIED_COUNTER… 前回統計情報が収集されてから更新された行数
  • AUTOINC… 現在のオートインクリメント値
  • REF_COUNT… カウンタがゼロになると、テーブルメタデータをテーブルキャッシュから削除
mysql> SELECT * FROM INNODB_TABLESTATS WHERE NAME='test/t1'\G
*************************** 1. row ***************************
         TABLE_ID: 1065
             NAME: test/t1
STATS_INITIALIZED: Initialized
         NUM_ROWS: 19
 CLUST_INDEX_SIZE: 1
 OTHER_INDEX_SIZE: 0
 MODIFIED_COUNTER: 0
          AUTOINC: 23
        REF_COUNT: 2
1 row in set (0.00 sec)

統計情報は、オプティマイザーがクエリの実行計画を作成するときに利用されます。また、デフォルトでは自動統計情報収集が有効になっています。有効の場合、統計情報を収集するタイミングは以下のとおりです。

  • テーブル全体の10%を超える行の更新
  • ANALYZE TABLEを実行

また、最後に統計情報を収集した時間は、mysql.innodb_table_statsまたはmysql.innodb_index_statsテーブルのlast_updateカラムから確認できます。

SELECT database_name,table_name,last_update FROM mysql.innodb_table_stats WHERE table_name='t1';
+---------------+------------+---------------------+
| database_name | table_name | last_update         |
+---------------+------------+---------------------+
| test          | t1         | 2023-01-20 09:45:22 |
+---------------+------------+---------------------+
1 row in set (0.00 sec)

INNODB_TABLESTATSビューは普段参照することはないでしょう。唯一このビューだけが持つ情報として興味深いのは、MODIFIED_COUNTERカラムの値だと思います。この値は前回統計情報を収集してから変更された行数です。前述で、⁠テーブル全体の10%を超える行の更新⁠で自動で統計情報が収集されると説明しました。このカラムの値がNUM_ROWSの10%を超えると統計情報が収集されます。また、一度統計情報が収集されると、値は0にリセットされます。last_updateが古いテーブルに対して、現在までにどれだけの変更がテーブルに行われていたかの、ざっくりな把握はできると思います。しかし、これといって使いどころはありませんので、参考程度に覚えておくだけでいいでしょう。

INNODB_COLUMNSテーブル

INNODB_COLUMNSテーブルはInnoDBのカラムに関する情報を確認できます。カラムの情報は主にinformation_schema.COLUMNSテーブルを参照する方が多いでしょう。そのほうがわかりやすいので、筆者もそうしています。しかし、このINNODB_COLUMNSテーブルからのみ確認できる情報があり、それはALGORITHM=INSTANTで追加されたカラム(以降、インスタントカラム)かどうかがわかります。

インスタントカラムはMySQL 8.0から導入された機能で、カラム追加をメタデータの変更のみで完了します。テーブルのデータには触れないので、テーブルサイズの大きさにかかわらずカラムが即座に追加されます。そして、今まではテーブルの最後のカラム追加のみサポートされていましたが、MySQL 8.0.30以降からは任意の位置へのカラム追加もサポートされました(例:ALTER TABLE t1 ADD col2 int AFTER col1⁠。

INNODB_COLUMNSテーブルのHAS_DEFAULTから、どのテーブルのカラムがインスタントカラムなのか確認することができます。値が1であれば、インスタントカラムであり、0であれば通常のカラムとなっています。例として、test.t2テーブルにcol1カラムをインスタントカラムとして追加し確認してみましょう。

mysql> ALTER TABLE test.t2 ADD col1 int AFTER id, ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT TABLE_ID,NAME,POS,HAS_DEFAULT FROM INNODB_COLUMNS WHERE TABLE_ID=1069;
+----------+------+-----+-------------+
| TABLE_ID | NAME | POS | HAS_DEFAULT |
+----------+------+-----+-------------+
|     1069 | id   |   0 |           0 |
|     1069 | col1 |   1 |           1 |
+----------+------+-----+-------------+
2 rows in set (0.01 sec)

また、インスタントカラムはテーブル再構築を伴うDDLやOPTIMIZE TABLEなどを実行すると、テーブルの再構築によりテーブルIDが変わり、インスタントカラムから通常カラムへと変更されます。

mysql> ALTER TABLE test.t2 ADD col2 int AFTER id, ALGORITHM=INPLACE;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT TABLE_ID,NAME,POS,HAS_DEFAULT FROM INNODB_COLUMNS WHERE TABLE_ID=1070;
+----------+------+-----+-------------+
| TABLE_ID | NAME | POS | HAS_DEFAULT |
+----------+------+-----+-------------+
|     1070 | id   |   0 |           0 |
|     1070 | col2 |   1 |           0 |
|     1070 | col1 |   2 |           0 |
+----------+------+-----+-------------+

運用中にインスタントカラムかどうか確認することは少ないと思います。ただし、新機能であるために、その他の処理と組み合わさってバグを踏むこともあるでしょう。そのときはここを確認することもあると思います。

INNODB_TABLESPACESテーブル

INNODB_TABLESPACESテーブルはInnoDBテーブルスペースに関するメタデータを参照することができます。

以前の記事 第56回 InnoDBテーブルサイズのいろいろな確認方法 にてこのテーブルを用いた実際のファイルサイズの見方を紹介していますので、ご確認ください。MySQL 5.7ではINNODB_SYS_TABLESPACESテーブルでしたが、MySQL 8.0からはINNODB_TABLESPACESテーブルにテーブル名が変更されています。

MySQL 8.0からのINNODB_TABLESPACESテーブルでは以下のようなカラムが追加され、確認できる項目が増えています。

  • AUTOEXTEND_SIZE… テーブルスペースの自動拡張サイズ
  • ENCRYPTION… テーブルスペースが暗号化されているかどうか

まとめ

今回はあまり実用的な内容ではなかったかもしれませんが、知っておいて損はない情報を紹介しました。 紹介したテーブルに関する公式ドキュメント次のとおりです。こちらも合わせてご参照いただければと思います。

おすすめ記事

記事・ニュース一覧