先日、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

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)

information_schema_tables

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>

information_schema_tables_csv

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>

詳細はまた次回再確認確認


MYSQLのテーブルサイズを確認する為にinformation_schema.tablesを利用してみる。
運用する上で、負荷の高い原因となってるテーブルを確認する。

mysql> desc information_schema.tables;
+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512)        | YES  |     | NULL    |       |
| 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(80)         | NO   |     |         |       |
+-----------------+---------------------+------+-----+---------+-------+
21 rows in set (0.01 sec)

mysql>
mysql> select database();
+------------+
| database() |
+------------+
| STUDY      |
+------------+
1 row in set (0.00 sec)
select
table_name, engine, table_rows as tbl_rows, avg_row_length as rlen,
floor((data_length+index_length)/1024/1024) as 'ToTal(MB)',
floor((data_length)/1024/1024) as 'DATA(MB)',
floor((index_length)/1024/1024) as 'INDEX(MB)'
from information_schema.tables
where table_schema=database()
order by (data_length+index_length) desc;

MySQL5.7にて実行した内容は以下@20141218

select TABLE_SCHEMA,table_name, engine, table_rows as tbl_rows, 
avg_row_length as rlen,floor((data_length+index_length)/1024/1024) as 'ToTal(MB)',
floor((data_length)/1024/1024) as 'DATA(MB)',floor((index_length)/1024/1024) as 'INDEX(MB)' 
from information_schema.tables WHERE TABLE_SCHEMA NOT IN ('mysql','INFORMATION_SCHEMA','performance_schema')
AND ENGINE IS NOT NULL
order by (data_length+index_length) desc;
root@localhost [information_schema]>select TABLE_SCHEMA,table_name, engine, table_rows as tbl_rows, 
    -> avg_row_length as rlen,floor((data_length+index_length)/1024/1024) as 'ToTal(MB)',
    -> floor((data_length)/1024/1024) as 'DATA(MB)',floor((index_length)/1024/1024) as 'INDEX(MB)' 
    -> from information_schema.tables WHERE TABLE_SCHEMA NOT IN ('mysql','INFORMATION_SCHEMA','performance_schema')
    -> AND ENGINE IS NOT NULL
    -> order by (data_length+index_length) desc;
+--------------+----------------------+--------+----------+-------+-----------+----------+-----------+
| TABLE_SCHEMA | table_name           | engine | tbl_rows | rlen  | ToTal(MB) | DATA(MB) | INDEX(MB) |
+--------------+----------------------+--------+----------+-------+-----------+----------+-----------+
| nyosm        | nodes                | InnoDB |  5226742 |   132 |       865 |      659 |       206 |
| nyosm        | waytags              | InnoDB |  4564009 |    57 |       602 |      250 |       351 |
| nyosm        | waynodes             | InnoDB |  6244646 |    50 |       429 |      301 |       127 |
| nyosm        | nodetags             | InnoDB |   648414 |    60 |        93 |       37 |        55 |
| nyosm        | ways                 | InnoDB |   868452 |    88 |        91 |       73 |        17 |
| nyosm        | relationtags         | InnoDB |    28733 |    91 |         7 |        2 |         4 |
| nyosm        | relationmembers      | InnoDB |    73485 |    64 |         6 |        4 |         1 |
| sakila       | rental               | InnoDB |    16005 |    99 |         2 |        1 |         1 |
| sakila       | payment              | InnoDB |    16086 |    98 |         2 |        1 |         0 |
| nyosm        | relations            | InnoDB |     4748 |    89 |         0 |        0 |         0 |
| sakila       | inventory            | InnoDB |     4581 |    39 |         0 |        0 |         0 |
| sakila       | film_actor           | InnoDB |     5462 |    35 |         0 |        0 |         0 |
| sakila       | film                 | InnoDB |     1000 |   196 |         0 |        0 |         0 |
| sakila       | film_text            | InnoDB |     1000 |   180 |         0 |        0 |         0 |
| sakila       | customer             | InnoDB |      599 |   136 |         0 |        0 |         0 |
| sakila       | address              | InnoDB |      603 |   163 |         0 |        0 |         0 |
| sakila       | staff                | InnoDB |        2 | 32768 |         0 |        0 |         0 |
| sakila       | film_category        | InnoDB |     1000 |    65 |         0 |        0 |         0 |
| sakila       | city                 | InnoDB |      600 |    81 |         0 |        0 |         0 |
| sakila       | store                | InnoDB |        2 |  8192 |         0 |        0 |         0 |
| sakila       | actor                | InnoDB |      200 |    81 |         0 |        0 |         0 |
| myhttp       | json_types           | InnoDB |        0 |     0 |         0 |        0 |         0 |
| myhttp       | hello                | InnoDB |        6 |  2730 |         0 |        0 |         0 |
| sakila       | country              | InnoDB |      109 |   150 |         0 |        0 |         0 |
| myhttp       | dojo_jsonp_fields    | InnoDB |        2 |  8192 |         0 |        0 |         0 |
| myhttp       | dojo_jsonp           | InnoDB |        2 |  8192 |         0 |        0 |         0 |
| sakila       | category             | InnoDB |       16 |  1024 |         0 |        0 |         0 |
| myhttp       | crud_messages        | InnoDB |        0 |     0 |         0 |        0 |         0 |
| myhttp       | compound_primary_key | InnoDB |        4 |  4096 |         0 |        0 |         0 |
| myhttp       | blog_posts           | InnoDB |        0 |     0 |         0 |        0 |         0 |
| myhttp       | sql_types            | InnoDB |        2 |  8192 |         0 |        0 |         0 |
| myhttp       | simple               | InnoDB |       20 |   819 |         0 |        0 |         0 |
| sakila       | language             | InnoDB |        6 |  2730 |         0 |        0 |         0 |
| myhttp       | no_primary_key       | InnoDB |        3 |  5461 |         0 |        0 |         0 |
+--------------+----------------------+--------+----------+-------+-----------+----------+-----------+
34 rows in set (0.02 sec)

root@localhost [information_schema]>select @@version;
+---------------------+
| @@version           |
+---------------------+
| 5.7.5-labs-http-log |
+---------------------+
1 row in set (0.00 sec)

root@localhost [information_schema]>

特定DBのテーブルサイズ確認
db_size2

MYSQLデータベースのテーブル確認
mysql_db


特定のデータベース上にて特定のテーブル名を含むテーブルを一覧で表示する方法。


mysql> SHOW TABLES FROM test LIKE '%TBL%';
+------------------------+
| Tables_in_test (%TBL%) |
+------------------------+
| TBL1 |
| TBL2 |
+------------------------+
2 rows in set (0.00 sec)

mysql> SHOW TABLE STATUS FROM test LIKE '%TBL%'\G
*************************** 1. row ***************************
Name: TBL1
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 1970324836974591
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2009-07-21 14:18:44
Update_time: 2009-07-21 14:18:44
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
*************************** 2. row ***************************
Name: TBL2
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 2533274790395903
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2009-07-21 14:20:29
Update_time: 2009-07-21 14:20:29
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
2 rows in set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.TABLES
-> WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME LIKE '%TBL%'\G

*************************** 1. row ***************************
TABLE_CATALOG: NULL
TABLE_SCHEMA: test
TABLE_NAME: TBL1
TABLE_TYPE: BASE TABLE
ENGINE: MyISAM
VERSION: 10
ROW_FORMAT: Fixed
TABLE_ROWS: 0
AVG_ROW_LENGTH: 0
DATA_LENGTH: 0
MAX_DATA_LENGTH: 1970324836974591
INDEX_LENGTH: 1024
DATA_FREE: 0
AUTO_INCREMENT: NULL
CREATE_TIME: 2009-07-21 14:18:44
UPDATE_TIME: 2009-07-21 14:18:44
CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT:
*************************** 2. row ***************************
TABLE_CATALOG: NULL
TABLE_SCHEMA: test
TABLE_NAME: TBL2
TABLE_TYPE: BASE TABLE
ENGINE: MyISAM
VERSION: 10
ROW_FORMAT: Fixed
TABLE_ROWS: 0
AVG_ROW_LENGTH: 0
DATA_LENGTH: 0
MAX_DATA_LENGTH: 2533274790395903
INDEX_LENGTH: 1024
DATA_FREE: 0
AUTO_INCREMENT: NULL
CREATE_TIME: 2009-07-21 14:20:29
UPDATE_TIME: 2009-07-21 14:20:29
CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT:
2 rows in set (0.00 sec)

mysql>

show_table