2007-06-26

MySQLではver5.0からinformation_schema対応が行われています。information_schemaですが、これはANSI標準SQL2003かなにかで定義されている機能のひとつで、RDBMSからテーブル等に関するメタ情報を取得したいときに使えるRDBMS間共通のインタフェースとして用意されています(あってるよね?)。

ver5.0以前は

MySQLではver4.1まではそういったメタ情報はSHOW系コマンドによってアクセス手段が提供されていました。

SHOW TABLES;
SHOW DATABSES;
SHOW INDEXES FROM...;
...

ver5.0からは

MySQL 5.0からは従来のSHOW系コマンドに加え、information_schemaデータベースからSELECTで情報を取得できるようになっています。

information_schemaにはこんなテーブルがあります。これはUnix/Linuxでいうところの/procファイルシステムのアイデアに近い、いわゆる仮想テーブルです。実体があるわけではないです。

[information_schema] > show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        | 
| COLLATIONS                            | 
| COLLATION_CHARACTER_SET_APPLICABILITY | 
| COLUMNS                               | 
| COLUMN_PRIVILEGES                     | 
| KEY_COLUMN_USAGE                      | 
| PROFILING                             | 
| ROUTINES                              | 
| SCHEMATA                              | 
| SCHEMA_PRIVILEGES                     | 
| STATISTICS                            | 
| TABLES                                | 
| TABLE_CONSTRAINTS                     | 
| TABLE_PRIVILEGES                      | 
| TRIGGERS                              | 
| USER_PRIVILEGES                       | 
| VIEWS                                 | 
+---------------------------------------+
17 rows in set (0.00 sec)

前置きは終わり

で、このエントリはこんなこと↑↑を書いておくために作成したわけじゃありません。show系コマンドとinformation_schema系テーブルの関連についてメモっておくために書いているのです。

MySQL 5.0と4.1の大きな実装上の違いのひとつですが、このshow系コマンドはver4.1まではメタ情報を返す実装が施されていたのが、ver5.0からのshow系コマンドは内部でinformation_schemaテーブルに対してSELECT文を発行する実装になっているのです。

SHOW TABLE STATUSを処理するパーサ(sql/sql_yacc.yy)のソースコード

         | TABLE_SYM STATUS_SYM opt_db wild_and_where
           {
             LEX *lex= Lex;
             lex->sql_command= SQLCOM_SELECT;
             lex->orig_sql_command= SQLCOM_SHOW_TABLE_STATUS;
             lex->select_lex.db= $3;
             if (prepare_schema_table(YYTHD, lex, 0, SCH_TABLES))
               MYSQL_YYABORT;
           }

オリジナルのSQLコマンドは"SQLCOM_SHOW_TABLE_STATUS"なんですが、この後の処理は"SQLCOM_SELECT"として扱うようにしています。この時、SELECT対象のテーブルを用意する必要がありますので、prepare_schema_table関数に"SCH_TABLES"を渡しているわけです。

ここでこの先を読み解くヒントとなるのがこの"SCH_TABLES"だったりするわけですが。。。

enum enum_schema_tables

sql/table.hに以下の定義があります。

enum enum_schema_tables
{
  SCH_CHARSETS= 0,
  SCH_COLLATIONS,
  SCH_COLLATION_CHARACTER_SET_APPLICABILITY,
  SCH_COLUMNS,
  SCH_COLUMN_PRIVILEGES,
  SCH_KEY_COLUMN_USAGE,
  SCH_OPEN_TABLES,
  SCH_PROFILING,
  SCH_PROCEDURES,
  SCH_SCHEMATA,
  SCH_SCHEMA_PRIVILEGES,
  SCH_STATISTICS,
  SCH_STATUS,
  SCH_TABLES,
  SCH_TABLE_CONSTRAINTS,
  SCH_TABLE_NAMES,
  SCH_TABLE_PRIVILEGES,
  SCH_TRIGGERS,
  SCH_USER_PRIVILEGES,
  SCH_VARIABLES,
  SCH_VIEWS
};

information_schemaデータベースで"SHOW TABLES"を実施した結果と似たようなテーブル名が散見されますね。

ところが、このEnumに存在するテーブルのうち、いくつかのテーブルがinformation_schema上では見えません。

SHOW OPEN TABLESはhiddenなinformation_schemaテーブルにアクセスして情報を返す

先ほどのEnumを使用してアクセスされる配列があります。それがST_SCHEMA_TABLE型の配列、schema_tablesです。sql/sql_show.ccにあります。

ST_SCHEMA_TABLE schema_tables[]=
{
  {"CHARACTER_SETS", charsets_fields_info, create_schema_table,
   fill_schema_charsets, make_character_sets_old_format, 0, -1, -1, 0},
  {"COLLATIONS", collation_fields_info, create_schema_table,
   fill_schema_collation, make_old_format, 0, -1, -1, 0},
  {"COLLATION_CHARACTER_SET_APPLICABILITY", coll_charset_app_fields_info,
   create_schema_table, fill_schema_coll_charset_app, 0, 0, -1, -1, 0},
  {"COLUMNS", columns_fields_info, create_schema_table,
   get_all_tables, make_columns_old_format, get_schema_column_record, 1, 2, 0},
  {"COLUMN_PRIVILEGES", column_privileges_fields_info, create_schema_table,
    fill_schema_column_privileges, 0, 0, -1, -1, 0},
  {"KEY_COLUMN_USAGE", key_column_usage_fields_info, create_schema_table,
    get_all_tables, 0, get_schema_key_column_usage_record, 4, 5, 0},
  {"OPEN_TABLES", open_tables_fields_info, create_schema_table,
   fill_open_tables, make_old_format, 0, -1, -1, 1},
  {"PROFILING", query_profile_statistics_info, create_schema_table,
    fill_query_profile_statistics_info, NULL, NULL, -1, -1, false},
  {"ROUTINES", proc_fields_info, create_schema_table,
    fill_schema_proc, make_proc_old_format, 0, -1, -1, 0},
  {"SCHEMATA", schema_fields_info, create_schema_table,
   fill_schema_shemata, make_schemata_old_format, 0, 1, -1, 0},
  {"SCHEMA_PRIVILEGES", schema_privileges_fields_info, create_schema_table,
    fill_schema_schema_privileges, 0, 0, -1, -1, 0},
  {"STATISTICS", stat_fields_info, create_schema_table,
    get_all_tables, make_old_format, get_schema_stat_record, 1, 2, 0},
  {"STATUS", variables_fields_info, create_schema_table, fill_status,
   make_old_format, 0, -1, -1, 1},
  {"TABLES", tables_fields_info, create_schema_table,
   get_all_tables, make_old_format, get_schema_tables_record, 1, 2, 0},
  {"TABLE_CONSTRAINTS", table_constraints_fields_info, create_schema_table,
    get_all_tables, 0, get_schema_constraints_record, 3, 4, 0},
  {"TABLE_NAMES", table_names_fields_info, create_schema_table,
   get_all_tables, make_table_names_old_format, 0, 1, 2, 1},
  {"TABLE_PRIVILEGES", table_privileges_fields_info, create_schema_table,
    fill_schema_table_privileges, 0, 0, -1, -1, 0},
  {"TRIGGERS", triggers_fields_info, create_schema_table,
   get_all_tables, make_old_format, get_schema_triggers_record, 5, 6, 0},
  {"USER_PRIVILEGES", user_privileges_fields_info, create_schema_table,
    fill_schema_user_privileges, 0, 0, -1, -1, 0},
  {"VARIABLES", variables_fields_info, create_schema_table, fill_variables,
   make_old_format, 0, -1, -1, 1},
  {"VIEWS", view_fields_info, create_schema_table,
    get_all_tables, 0, get_schema_views_record, 1, 2, 0},
  {0, 0, 0, 0, 0, 0, 0, 0, 0}
};

ここで、1つ前のエントリで触れていた"SHOW OPEN TABLES"について見てみましょう。schema_tablesの中に"OPEN TABLES"というinformation_schemaテーブルが定義されていますが、"SHOW TABLES"で得られたinformation_schemaのテーブル名一覧には名前がありませんでした。

もしかしたら隠しテーブルなのかもしれない、ということでST_SCHEMA_TABLE構造体の定義を見てみます。sql/table.hです。

typedef struct st_schema_table
{
  const char* table_name;
  ST_FIELD_INFO *fields_info;
  /* Create information_schema table */
  TABLE *(*create_table)  (THD *thd, struct st_table_list *table_list);
  /* Fill table with data */
  int (*fill_table) (THD *thd, struct st_table_list *tables, COND *cond);
  /* Handle fileds for old SHOW */
  int (*old_format) (THD *thd, struct st_schema_table *schema_table);
  int (*process_table) (THD *thd, struct st_table_list *tables,
                        TABLE *table, bool res, const char *base_name,
                        const char *file_name);
  int idx_field1, idx_field2;
  bool hidden;
} ST_SCHEMA_TABLE;

予想通り、hiddenというメンバがありますね。schema_tablesの"OPEN TABLE"の代入値を見てみると、、、最後の値が1になっていますね。

というわけで、SHOW OPEN TABLESコマンドが内部的にアクセスするinformation_schemaのテーブルは隠しテーブルだということが分かりました。

まとめ

SHOW系コマンドは内部的にinformation_schemaデータベースのテーブルへのSELECTにマッピングされて結果が返される。しかしながら隠しテーブルがいくつかあったりなど、information_schemaへのSELECTで全てのSHOW系コマンドを代替することは今のところはできていない。

個別のコマンド／テーブルで気になるところがあれば、schema_tables(sql/sql_show.cc)を確認すればいい。

テーブルロックの状況確認に付いて。

構文

SHOW OPEN TABLES [FROM db_name] [LIKE 'tbl_name_pattern'];

カラムの意味

[test] > show open tables from test;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| test     | t1    |      0 |           0 | 
+----------+-------+--------+-------------+
1 row in set (0.00 sec)

Database ... データベース名
Table ... テーブル名
In_use ... 現在のテーブルロックの要求数。0だと誰もlockしていない。1以上の数値のときはN人がロックしている状態。
Name_locked ... DROP TABLEとかALTER TABLEとかのDDL文によりテーブルがロックされている場合は1。そうでなければ0。

つまり上記のt1テーブルはロックされていない。

ロックされている例(1人がロック獲得)

[test] > show open tables from test;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| test     | t1    |      1 |           0 | 
+----------+-------+--------+-------------+
1 row in set (0.00 sec)

ロックされている例(1人がロック獲得、1人がブロック待ち)

[test] > show open tables from test;
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| test     | t1    |      2 |           0 | 
+----------+-------+--------+-------------+
1 row in set (0.00 sec)

補足

LOCK TABLES t1 READを複数セッションから発行すると、ロックされずにREADロックを取得でき、In_useはカウントアップされていく。

従って、In_useの値からREAD/READ LOCAL/WRITEのロックの種別を判別したり、ブロック待ちの人数を推定することはできない。

mir the developer

WindowsでDROP INDEX時などにSEN系ファイルが残ってしまう問題の修正

マージ元 -r67:71

マージ履歴@r72

SHOW系コマンドとinformation_schemaデータベースの関係