第3章 SQL構文

SHOW構文:テーブル情報を表示する

2009年8月17日

SHOW構文を知ろう!

SHOW構文

SHOWはデータベース、テーブル、フィールド、サーバについての情報を与える。LIKEが使用された場合、文字列は通常のSQLワイルドカード (% と _) です。上記の使用例のように、"テーブル名 FROM データベース名"の代わりに、"データベース名.テーブル名"が使用できます。

SHOW 構文

SHOW DATABASES [LIKE 文字列]
or SHOW [OPEN] TABLES [FROM データベース名] [LIKE 文字列]
or SHOW [FULL] COLUMNS FROM テーブル名 [FROM データベース名] [LIKE 文字列]
or SHOW INDEX FROM テーブル名 [FROM データベース名]
or SHOW TABLE STATUS [FROM db_name] [LIKE 文字列]
or SHOW STATUS [LIKE 文字列]
or SHOW VARIABLES [LIKE 文字列]
or SHOW LOGS
or SHOW [FULL] PROCESSLIST
or SHOW GRANTS FOR ユーザ名
or SHOW CREATE TABLE テーブル名
or SHOW MASTER STATUS
or SHOW MASTER LOGS
or SHOW SLAVE STATUS

SHOW構文の使用例
SHOW INDEX FROM mydb.mytable;

構文のキーワード

SHOW構文のキーワード

SHOW DATABASES

データベース名の一覧を表示します。

SHOW DATABASES;
+----------+
| Database |
+----------+
| mydb |
| mysql |
| test |
+----------+

SHOW TABLES

カレントデータベースのテーブル名を一覧表示します。

SHOW TABLES;
+----------------+
| Tables_in_MYDB |
+----------------+
| customer |
| goods |
| purchase |
| purchase_goods |
+----------------+

SHOW FIELDS / SHOW COLUMNS

SHOW COLUMNSは指定されたテーブルのフィールドを表示します。FULLオプションを指定した場合、各フィールドへの権限も表示されます。

SHOW COLUMNS FROM customer;
+-------+-----------------------+------+-----+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+
| id_c | mediumint(8) unsigned | | PRI | NULL |
auto_increment |
| sei | varchar(20) | YES | | NULL | |
| mei | varchar(20) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
| sex | tinyint(1) unsigned | YES | | NULL | |
+-------+-----------------------+------+-----+

SHOW KEYS / SHOW INDEX

SHOW INDEXはインデックス情報を表示します。

SHOW INDEX FROM customer;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index |
Column_name | Collation | Cardinality | Sub_part |
Packed | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+ | CUSTOMER | 0 | PRIMARY | 1 | id_c | A | 5 | NULL | NULL | | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+

以下の項目が返ります。

フィールド名 意味
Table テーブル名です。
Non_unique インデックスが重複を含まないなら 0
Key_name インデックス名です。
Seq_in_index インデックスのフィールド番号。1 から始まります。
Column_name フィールド名です。
Collation インデックスでの ソート方法です。A (Ascending) か NULL (Not sorted) があいrます。
Cardinality インデックスのユニークな値の数。
Sub_part もしこのフィールドがインデックスに一部分だけ使用している場合、そのインデックスに使用しているキャラクター数をしめす。 もしキー全体がインデックスされているなら NULL 。
Comment Various remarks. For now, it tells whether index is FULLTEXT or not.

SHOW VARIABLES

MySQLシステム変数のいくつかの値を示す。もし標準値が適さないなら、ほとんどの変数をmysqld起動時に命令文ラインのオプションとして与えることにより、変更できる。

SHOW VARIABLES [LIKE wild]

SHOW VARIABLESはMySQLシステム変数のいくつかの値を示します。 mysqlshow variables 命令文でも同じ情報が得られます。もし標準値が適さないなら、ほとんどの変数をmysqld起動時に命令文ラインのオプションとして与えることにより、変更できます。

buffer size, buffer length, stack size は byte単位で与えます。これらの値の後ろにKやMを追加すると、キロバイト、メガバイトになります。例えば、16Mは16メガバイトを示します。大文字小文字の区別はなく、16Mと16mは同じ意味になります。

SHOW PROCESSLIST

そのスレッドが走っているかを表示する。もし process 特権があるなら、全てのスレッドがみれる。しかし特権がないなら、自分のスレッドしか見れない。

SHOW PROCESSLIST はどのスレッドが走っているかを表示します。 mysqlshow processlist 命令文でも同じ情報が得られます。もし process 権限があるなら、全てのスレッドがみれます。しかし権限がないなら、自分のスレッドしか見れません。 「4.5.5 KILL 構文」節参照. FULL オプションを使用しない場合、各クエリの最初の100文字だけが表示されます。

This command is very useful if you get the 'too many connections' error message and want to find out what's going on. MySQL reserves one extra connection for a client with the Process_priv privilege to ensure that you should always be able to login and check the system (assuming you are not giving this privilege to all your users).

Some frequently states in mysqladmin processlist

  • Checking table The thread doing an [automatic ?] checking of the table.
  • Closing tables Means that the thread is flushing the changed table data to disk and closing the used tables. This should be a fast operation. If not, then you should check that you don't have a full disk or that the disk is not in very heavy use.
  • Copying to tmp table on disk The temporary result set was larger than tmp_table_size and the thread is now changing the in memory based temporary table to a disk based one to save memory.
  • Creating tmp table The thread is creating a temporary table to hold a part of the result for the query.
  • deleting from main table When executing the first part of a multi-table delete and we are only deleting from the first table.
  • deleting from reference tables When executing the second part of a multi-table delete and we are deleting the matched rows from the other tables.
  • Flushing tables The thread is executing FLUSH TABLES and is waiting for all threads to close their tables.
  • Killed Someone has sent a kill to the thread and it should abort next time it checks the kill flag. The flag is checked in each major loop in MySQL, but in some cases it may still take a short time for the thread to die. If the thread is locked by some other thread, the kill will take affect as soon as the other thread releases it's lock.
  • Sending data The thread is processing rows for a SELECT statement and is also sending data to the client.
  • Sorting for group The thread is doing a sort to satsify a GROUP BY.
  • Sorting for order The thread is doing a sort to satsify a ORDER BY.
  • Opening tables This simply means that the thread is trying to open a table. This is should be very fast procedure, unless something prevents opening. For example an ALTER TABLE or a LOCK TABLE can prevent opening a table until the command is finished.
  • Removing duplicates The query was using SELECT DISTINCT in such a way that MySQL couldn't optimize that distinct away at an early stage. Because of this MySQL has to do an extra stage to remove all duplicated rows before sending the result to the client.
  • Reopen table The thread got a lock for the table, but noticed after getting the lock that the underlying table structure changed. It has freed the lock, closed the table and is now trying to reopen it.
  • Repair by sorting The repair code is using sorting to create indexes.
  • Repair with keycache The repair code is using creating keys one by one through the key cache. This is much slower than Repair by sorting.
  • Searching rows for update The thread is doing a first phase to find all matching rows before updating them. This has to be done if the UPDATE is changing the index that is used to find the involved rows.
  • Sleeping The thread is wating for the client to send a new command to it.
  • System lock The thread is waiting for getting to get a external system lock for the table. If you are not using multiple mysqld servers that are accessing the same tables, you can disable system locks with the --skip-locking option.
  • Upgrading lock The INSERT DELAYED handler is trying to get a lock for the table to insert rows.
  • Updating The thread is searching for rows to update and updating them.
  • User Lock The thread is waiting on a GET_LOCK().
  • Waiting for tables The thread got a notification that the underlying structure for a table has changed and it needs to reopen the table to get the new structure. To be able to reopen the table it must however wait until all other threads have closed the table in question. This notification happens if another thread has used FLUSH TABLES or one of the following commands on the table in question: FLUSH TABLES table_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE or OPTIMIZE TABLE.
  • waiting for handler insert The INSERT DELAYED handler has processed all inserts and are waiting to get new ones.

Most states are very quick operations. If threads last in any of these states for many seconds, there may be a problem around that needs to be investigated.

There are some other states that are not mentioned above, but most of these are only useful to find bugs in mysqld.

SHOW TABLE STATUS

SHOW TABLE STATUS [FROM db_name] [LIKE wild]

SHOW TABLE STATUS (バージョン 3.23 の新機能) は SHOWSTATUSのようですが、それぞれのテーブルについてより多くの情報を提供します。 mysqlshow --status db_name 命令文を実行しても同じものが得られます。以下の項目が返ってきます:

項目 意味
Name テーブル名
Type テーブルの種類 「7 MySQL テーブル型」節参照.
Row_format レコードの保存形式 (Fixed, Dynamic, or Compressed)
Rows レコード数
Avg_row_length レコードの平均長
Data_length データファイルの大きさ
Max_data_length データファイルの最大値
Index_length インデックスファイルの大きさ
Data_free 割り当てられたが使用されていないバイト数
Auto_increment 次の autoincrement 値
Create_time テーブル作成時刻
Update_time 一番最後に更新された時刻
Check_time 一番最後にチェックされた時刻
Create_options CREATE TABLE で使用された拡張オプション
Comment テーブル作成時につけられたコメント (あるいは、なぜこのテーブルにMySQL がアクセスできないかのいくつかの情報).

SHOW STATUS

サーバからのステータス情報を表示します。

ステータス変数

   
Aborted_clients クライアントが接続を閉じる前に死んでしまったために中断されたコネクション数。 
Aborted_connects クライアントが接続を閉じる前に死んでしまったために中断されたコネクション数。 
Created_tmp_tables MySQL サーバに接続を試みて失敗した数
Created_tmp_tables ステートメントを実行している間に創り出された一時的なテーブルの数. 
Delayed_insert_threads 使用中の delayed insert handler threads 数 
Delayed_writes INSERT DELAYED で書かれた行数
Delayed_errors INSERT DELAYED で書かれた行でなんらかのエラーのあった行数
Flush_commands FLUSH 命令文の実行回数
Handler_delete テーブルから行を削除するためのリクエスト数
Handler_read_first あるテーブルから特定行を最初に読み込むことを要求した数
Handler_read_key キーを元にした行の読み込みを要求した数
Handler_read_next キーの順番にそって、次の行を読み込むことを要求した数
Handler_read_rnd ある固定された位置に基づいた行を読み込んだリクエスト数
Handler_update テーブルの行を更新するための要求数
Handler_write テーブルに行を挿入するためのリクエスト数
Key_blocks_used キーキャッシュ中で使用されたブロック数
Key_read_requests キャッシュからキーブロックを読み込ませたリクエスト数
Key_reads ディスクから物理的の読み込んだキーブロックの数
Key_write_requests キャッシュにキーブロックを書かせたリクエスト数
Key_writes ディスクに物理的に書かれたキーブロック数
Max_used_connections 同時に使用された接続の最大数
Not_flushed_key_blocks 変更されたものの、まだディスクに書き出されていない、キーキャッシュ中のキーブロックの数
Not_flushed_delayed_rows INSERT DELAY キューに書きだされることを待っている行の数
Open_tables オープンされているテーブル数
Open_files オープンされているファイル数
Open_streams オープンされているストリーム数
Opened_tables オープンされたテーブル数
Questions サーバにたいする問い合わせ数
Running_threads 現在開かれているコネクション数
Slow_queries long_query_time以上にかかったクエリの数
Uptime サーバが走っている秒数

コメント

  • もし Opened_tables が大きければ、 table_cache 変数が小さすぎるのでしょう。
  • もし key_reads が大きければ、 key_cache が少なすぎるでしょう。キャッシュヒットレートは key_reads/key_read_requests で計算できます。
  • もし Handler_read_rnd が大きければ、 MySQL にテーブルをスキャンさせるような多くのクエリや、 キーを使用しない JOIN がもてる。

SHOW LOGS

SHOW LOGS shows you status information about existing log files. It currently only displays information about Berkeley DB log files.

  • File shows the full path to the log file
  • Type shows the type of the log file (BDB for Berkeley DB log files)
  • Status shows the status of the log file (FREE if the file can be removed, or IN USE if the file is needed by the transaction subsystem)

SHOW GRANTS

SHOW GRANTS FOR user はユーザの許可を複製するために発行する必要がある grant 命令文をリストします。

mysql> SHOW GRANTS FOR root@localhost;
+---------------------------------------------------------------------+
| Grants for root@localhost                                          
|
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH
GRANT OPTION |
+---------------------------------------------------------------------+

SHOW CREATE TABLE

Shows a CREATE TABLE statement that will create the given table:

mysql> show create table t\G
*************************** 1. row
***************************
       Table: t
Create Table: CREATE TABLE t (
  id int(11) default NULL auto_increment,
  s char(60) default NULL,
  PRIMARY KEY (id)
) TYPE=MyISAM

SHOW CREATE TABLE will quote table and column names according to SQL_QUOTE_SHOW_CREATE option.


DESCRIBE構文 Oracle互換

構文

{DESCRIBE | DESC} テーブル名 {フィールド名| 文字列}

DESCRIBE はフィールドについての情報を与えます。

Comment

コメントを残す

メールアドレスが公開されることはありません。

関連記事

リズムファクトリーはホームページの制作会社です。
ホームページ制作に関するご要望・ご相談はこちらからどうぞ。

株式会社リズムファクトリーでは現在、下記の職種について人材募集を行っております。
求人をクリックすると「求人情報サイトFind Job!」の求人詳細画面が開きますので、こちらからご応募下さい。

提供 : Webな人の求人情報サイト Find Job!