information_schemaでMySQLの情報取得

この記事はアピリッツの技術ブログ「DoRuby」から移行した記事です。情報が古い可能性がありますのでご注意ください。

現状のmysqlで、どれくらいのデータが保存されているか等を調べる際に、information_schemaが便利でした。

Information_schema リファレンス

information_schemaとは

データベースやテーブル等に関するメタデータを格納している物になります。

上記のリファレンスや、データベースの中身を見れば、どのようなものが格納されているかはわかりますが、一部を紹介したいと思います。

Information_schema SCHEMATA

データベース自体のメタデータを格納しているテーブルです。
テーブルの名前や、文字コードを確認することが出来ます。

mysql> select * from information_schema.SCHEMATA\G
*************************** 1. row ***************************
              CATALOG_NAME: def
               SCHEMA_NAME: information_schema
DEFAULT_CHARACTER_SET_NAME: utf8
    DEFAULT_COLLATION_NAME: utf8_general_ci
                  SQL_PATH: NULL
*************************** 2. row ***************************
              CATALOG_NAME: def
               SCHEMA_NAME: test_database
DEFAULT_CHARACTER_SET_NAME: latin1
    DEFAULT_COLLATION_NAME: latin1_swedish_ci
                  SQL_PATH: NULL

Information_schema TABLES

テーブルのメタデータを格納しているテーブルです。

TABLE_SCHEMAにデータベース名、TABLE_NAMEにテーブル名が格納されているので、それを利用して絞り込みが可能です。

（今回は、テーブル名を指定しました。）

mysql> select * from information_schema.TABLES where table_name = "test_table"\G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: test_database
     TABLE_NAME: test_table
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Compact
     TABLE_ROWS: 1
 AVG_ROW_LENGTH: 16384
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2017-10-19 12:14:34
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:

TABLE_ROWSが行数になるので、これを取得してあげれば手軽にデータ量が確認できます。ただ、InnoDBを利用していると概数でしか出てこないようなので、注意が必要です。（後述）

また、 DATA_LENGTH・INDEX_LENGTHを足してあげれば、どれくらい容量を使っているかも確認が出来ます。

Information_schema COLUMNS

カラムのメタデータを格納しているテーブルです。

TABLESと同様に、データベース名やテーブル名で絞り込みができます。

mysql> select * from information_schema.COLUMNS where table_name = "test_table"\G
*************************** 1. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: test_database
              TABLE_NAME: test_table
             COLUMN_NAME: id
        ORDINAL_POSITION: 1
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: NO
               DATA_TYPE: int
CHARACTER_MAXIMUM_LENGTH: NULL
  CHARACTER_OCTET_LENGTH: NULL
       NUMERIC_PRECISION: 10
           NUMERIC_SCALE: 0
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: NULL
          COLLATION_NAME: NULL
             COLUMN_TYPE: int(20)
              COLUMN_KEY: PRI
                   EXTRA: auto_increment
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT:

Information_schema KEY_COLUMN_USAGE

キーの制約情報を格納しているテーブルです。

今回のテーブルではidをプライマリーキーにしているので、その情報が出てきます。

mysql> select * from information_schema.KEY_COLUMN_USAGE where table_name = "test_table"\G
*************************** 1. row ***************************
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: test_database
              CONSTRAINT_NAME: PRIMARY
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test_database
                   TABLE_NAME: test_table
                  COLUMN_NAME: id
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: NULL
      REFERENCED_TABLE_SCHEMA: NULL
        REFERENCED_TABLE_NAME: NULL
       REFERENCED_COLUMN_NAME: NULL

テーブルの行数を見る時の注意点

上記のinformation_schema TABLESを利用した時に得られる行数は、InnoDBだと概算値になってます。

自分はこれを知らなくて、どういうことなんだと悩みました。
行数確認の際、select文を走らせなくて良い分手軽ですが、InnoDBを利用している場合はこれに留意しておかないといけないようです。

実際にダミーデータを使って確認してみたのが以下になります。

#information_schemaでtable_rowsを見た時
mysql> select table_name, engine, table_rows from information_schema.tables where table_schema = "test_database";
+-------------+--------+------------+
| table_name  | engine | table_rows |
+-------------+--------+------------+
| user_innodb | InnoDB |      16400 |
| user_myisam | MyISAM |      16384 |
+-------------+--------+------------+

#select文でカウントした時
mysql> select (select count(*) from user_myisam) user_myisam, (select count(*) from user_innodb) user_innodb;
+-------------+-------------+
| user_myisam | user_innodb |
+-------------+-------------+
|       16384 |       16384 |
+-------------+-------------+

実際は16384個データがあるのですが、InnoDBのInformation_schemaでは16400個ということになっていました。
より大きなデータで利用する際は、誤差も無視できないレベルになりそうです。

まとめ

MySQLのメタデータを調べる際に便利な、information_schemaについて記述しました。

上記にピックアップした物以外にも、多くのメタデータが格納されているので、様々な状況で活用出来るかと思います。

自分でもまだ完全に把握できていないので、少しづつ調べていきたいと思います。