![variable.jp [データベース,パフォーマンス,運用]](https://variable.jp/wp-content/uploads/2013/09/cropped-variable.jp_.jpg)
先日、MYSQLを5.5.27に更新したのでinformation_schemaを確認すると同時に
information_schema.tablesを改めて確認してみる。
MYSQL Version 5.5.27のinformation_schema
mysql> select @@version; +-----------+ | @@version | +-----------+ | 5.5.27 | +-----------+ 1 row in set (0.00 sec) mysql> use information_schema; Database changed mysql> show tables; +---------------------------------------+ | Tables_in_information_schema | +---------------------------------------+ | CHARACTER_SETS | | COLLATIONS | | COLLATION_CHARACTER_SET_APPLICABILITY | | COLUMNS | | COLUMN_PRIVILEGES | | ENGINES | | EVENTS | | FILES | | GLOBAL_STATUS | | GLOBAL_VARIABLES | | KEY_COLUMN_USAGE | | PARAMETERS | | PARTITIONS | | PLUGINS | | PROCESSLIST | | PROFILING | | REFERENTIAL_CONSTRAINTS | | ROUTINES | | SCHEMATA | | SCHEMA_PRIVILEGES | | SESSION_STATUS | | SESSION_VARIABLES | | STATISTICS | | TABLES | | TABLESPACES | | TABLE_CONSTRAINTS | | TABLE_PRIVILEGES | | TRIGGERS | | USER_PRIVILEGES | | VIEWS | | INNODB_CMP_RESET | | INNODB_TRX | | INNODB_CMPMEM_RESET | | INNODB_LOCK_WAITS | | INNODB_CMPMEM | | INNODB_CMP | | INNODB_LOCKS | +---------------------------------------+ 37 rows in set (0.00 sec) mysql>
information_schema.tablesの基本構造
mysql> desc information_schema.tables; +-----------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+---------------------+------+-----+---------+-------+ | TABLE_CATALOG | varchar(512) | NO | | | | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | TABLE_TYPE | varchar(64) | NO | | | | | ENGINE | varchar(64) | YES | | NULL | | | VERSION | bigint(21) unsigned | YES | | NULL | | | ROW_FORMAT | varchar(10) | YES | | NULL | | | TABLE_ROWS | bigint(21) unsigned | YES | | NULL | | | AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | | | DATA_LENGTH | bigint(21) unsigned | YES | | NULL | | | MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | | | INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | | | DATA_FREE | bigint(21) unsigned | YES | | NULL | | | AUTO_INCREMENT | bigint(21) unsigned | YES | | NULL | | | CREATE_TIME | datetime | YES | | NULL | | | UPDATE_TIME | datetime | YES | | NULL | | | CHECK_TIME | datetime | YES | | NULL | | | TABLE_COLLATION | varchar(32) | YES | | NULL | | | CHECKSUM | bigint(21) unsigned | YES | | NULL | | | CREATE_OPTIONS | varchar(255) | YES | | NULL | | | TABLE_COMMENT | varchar(2048) | NO | | | | +-----------------+---------------------+------+-----+---------+-------+ 21 rows in set (0.00 sec)
DBインスタンスで利用されているENGINEの種類
mysql> select distinct engine from information_schema.tables; +--------------------+ | engine | +--------------------+ | MEMORY | | MyISAM | | InnoDB | | CSV | | PERFORMANCE_SCHEMA | +--------------------+ 5 rows in set (0.00 sec) mysql>
InnoDBは自分で作成したﾃｰﾌﾞﾙがﾒｲﾝなので今回はあえて、
MYSQL、MEMORYｴﾝｼﾞﾝなどを確認してみる。
MyISAMを利用しているTABLE
mysql> select table_schema, table_name
-> from information_schema.tables
-> where engine='MyISAM';
+--------------------+---------------------------+
| table_schema | table_name |
+--------------------+---------------------------+
| information_schema | COLUMNS |
| information_schema | EVENTS |
| information_schema | PARAMETERS |
| information_schema | PARTITIONS |
| information_schema | PLUGINS |
| information_schema | PROCESSLIST |
| information_schema | ROUTINES |
| information_schema | TRIGGERS |
| information_schema | VIEWS |
| mysql | columns_priv |
| mysql | db |
| mysql | event |
| mysql | func |
| mysql | help_category |
| mysql | help_keyword |
| mysql | help_relation |
| mysql | help_topic |
| mysql | host |
| mysql | ndb_binlog_index |
| mysql | plugin |
| mysql | proc |
| mysql | procs_priv |
| mysql | proxies_priv |
| mysql | servers |
| mysql | tables_priv |
| mysql | time_zone |
| mysql | time_zone_leap_second |
| mysql | time_zone_name |
| mysql | time_zone_transition |
| mysql | time_zone_transition_type |
| mysql | user |
+--------------------+---------------------------+
31 rows in set (0.01 sec)
CSVを利用しているﾃｰﾌﾞﾙ
mysql> select table_schema, table_name
-> from information_schema.tables
-> where engine='CSV';
+--------------+-------------+
| table_schema | table_name |
+--------------+-------------+
| mysql | general_log |
| mysql | slow_log |
+--------------+-------------+
2 rows in set (0.01 sec)
mysql>
MEMORYを利用しているテーブル
mysql> select table_schema, table_name
-> from information_schema.tables
-> where engine='MEMORY';
+--------------------+---------------------------------------+
| table_schema | table_name |
+--------------------+---------------------------------------+
| information_schema | CHARACTER_SETS |
| information_schema | COLLATIONS |
| information_schema | COLLATION_CHARACTER_SET_APPLICABILITY |
| information_schema | COLUMN_PRIVILEGES |
| information_schema | ENGINES |
| information_schema | FILES |
| information_schema | GLOBAL_STATUS |
| information_schema | GLOBAL_VARIABLES |
| information_schema | KEY_COLUMN_USAGE |
| information_schema | PROFILING |
| information_schema | REFERENTIAL_CONSTRAINTS |
| information_schema | SCHEMATA |
| information_schema | SCHEMA_PRIVILEGES |
| information_schema | SESSION_STATUS |
| information_schema | SESSION_VARIABLES |
| information_schema | STATISTICS |
| information_schema | TABLES |
| information_schema | TABLESPACES |
| information_schema | TABLE_CONSTRAINTS |
| information_schema | TABLE_PRIVILEGES |
| information_schema | USER_PRIVILEGES |
| information_schema | INNODB_CMP_RESET |
| information_schema | INNODB_TRX |
| information_schema | INNODB_CMPMEM_RESET |
| information_schema | INNODB_LOCK_WAITS |
| information_schema | INNODB_CMPMEM |
| information_schema | INNODB_CMP |
| information_schema | INNODB_LOCKS |
+--------------------+---------------------------------------+
28 rows in set (0.01 sec)
mysql>
詳細はまた次回再確認確認