目次
この記事はアピリッツの技術ブログ「DoRuby」から移行した記事です。情報が古い可能性がありますのでご注意ください。
現状のmysqlで、どれくらいのデータが保存されているか等を調べる際に、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について記述しました。
上記にピックアップした物以外にも、多くのメタデータが格納されているので、様々な状況で活用出来るかと思います。
自分でもまだ完全に把握できていないので、少しづつ調べていきたいと思います。
