出典について
この記事はMySQL Step-by-Step BlogのUseful queries on MySQL information_schema(2015/07/15)を翻訳したものです。
MySQLのinformation_schemaはデータベースのインスタンス、ステータス...などなどの日々のDBAの仕事に必要とされる有用な情報を備えている。 私が日々使う基本的なinformation_schemaへのいくつかのシンプルなクエリがあり、私自身が参照するためにこの投稿を書いており、または誰か他の人にも良いリファレンスになるだろう...
主キーまたはユニークキーがないテーブルをみつける
主キーは非常に重要であり、MySQLのInnoDBのテーブルでは主キーをクラスタインデックスとして利用するため、主キーがないと深刻な性能問題につながりかねない。
主キーがないと、スレーブのロギングの問題の主な要因の1つとなり、これは主にRBR(行ベースのレプリケーション)に発生する。例えば、マスタで発行されたdelete文が100万レコードを主キーなしに削除したとすると、フルテーブルスキャンが発生するだろう。これはマスタ上では問題にならない"かもしれない"が、スレーブでは100万行のフルテーブルスキャンが発生する。なぜならば、各行への変更はクエリそのものではなく、ROW形式でバイナリログに記録されるからだ。これはもちろんスレーブに遅延を引き起こす。レプリケーションの形式についての詳細は、マニュアルのドキュメントを参照して欲しい。
Gelera Clusterの場合は、主キーがないテーブルはスレーブへのレプリケーション遅延を引き起こすだけでなく、他の問題にもつながる。
もしテーブルに主キーがない場合、クラスタの異なるノードで行が異なる順番となりうる。そうなると、SELECT...LIMIT...の様なクエリが異なる結果を返すことになる。その上、そのようなテーブルではDELETE文はサポートされていない。
Galera Clusterの制限に関する詳細は、ここで確認できる。
従って主キーがないテーブルがないか確認するのは問題をできるだけ早く修正する為に非常に重要である。
SELECT t.TABLE_SCHEMA,t.TABLE_NAME,ENGINE
FROM information_schema.TABLES t
INNER JOIN information_schema.COLUMNS c
ON t.TABLE_SCHEMA=c.TABLE_SCHEMA
AND t.TABLE_NAME=c.TABLE_NAME
AND t.TABLE_SCHEMA NOT IN ('performance_schema','information_schema','mysql')
GROUP BY t.TABLE_SCHEMA,t.TABLE_NAME
HAVING sum(if(column_key in ('PRI','UNI'), 1,0))=0;
外部キー制約を確認する
Percona社のpt-online-schema-changeをスキーマを変更するのに利用している場合は、テーブル内に外部キーがあるとツールの操作がより複雑となり、追加の操作が必要となる。
外部キーはツールの操作を複雑なものとし、追加のリスクを発生させる。外部キーがテーブルを参照している場合は、アトミックに元のテーブルと新しいテーブルの面賞を変更するテクニックが使えない。ツールはスキーマの変更が完了してから、外部キーが新しいテーブルを参照するように更新する必要がある。ツールはこれを達成する為に2つの方法を提供している。これに関しての詳細な情報については -alter-foreign-keys-methodのドキュメントを参照してほしい。
外部キーはいくつかの副作用をも引き起こす。最後のテーブルは元のテーブルと同じ外部キーとインデックスをもつ(ALTER文の中で違うものを指定しない限り)が、MySQLおよびInnoDBでのオブジェクト名が衝突しないようにオブジェクトの名称が少し変化する。
pt-online-schema-changeの使い方に関する詳細は私のブログを確認して欲しい!
また、外部キーはInnoDBでのみサポートされているため、テーブルをMyISAMに変換する必要が発生した場合は、外部キー制約を始めに確認し、MyISAMに変換する前に削除する必要がある。さもなくばALTER文は失敗するだろう。
SELECT referenced_table_name parent, table_name child, constraint_name
FROM information_schema.KEY_COLUMN_USAGE
WHERE referenced_table_name IS NOT NULL
ORDER BY referenced_table_name;
断片化を確認する
テーブルは幾度もの書込み(insert, update, delete)により断片化するため、テーブルとインデックスを再構成し、性能向上および、OS上で利用されるディスクスペース回収をおこなう。 (InnoDBのテーブルを作成する前に、innodb_file_per_tableオプションが有効化されていたものと仮定する)。これは"OPTIMIZE TABLE"文を実行することにより実現できるが、一方でこのクエリは負荷がかかる。しかしながら、我々はテーブルの断片化をまず始めに確認し、それから"OPTIMIZE TABLE"を実行することで断片化が進んでいるテーブルだけに対して適用可能となる(次に示すクエリは100MB以上の未使用領域があるテーブルのdb_nameのみを返す)。
SELECT TABLE_NAME, (DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 AS sizeMb,DATA_FREE / 1024 / 1024 AS data_free_MB
FROM information_schema.tables
WHERE engine LIKE 'InnoDB'
and TABLE_SCHEMA = 'db_name'
AND DATA_FREE > 100 * 1024 * 1024;
MyISAMのテーブルがあるかどうか確認する
MyISAMはトランザクションをサポートしていないため、MyISAMのテーブルがあるときに一貫性のあるバックアップをとる為には、全てのテーブルをロックする必要がある。 従って、システムのバックアップ計画を考える前に、MyISAMのテーブルがあるかどうかを確認することが推奨される。
SELECT TABLE_SCHEMA, TABLE_NAME
FROM `information_schema`.`TABLES`
WHERE TABLE_SCHEMA NOT IN ('information_schema','performance_schema','mysql')
AND ENGINE='MyISAM';
information_schemaに関するご利用の便利なクエリはあるだろうか？気軽に下のコメントに書込んでいただきたい :)
(訳注: 元記事はこちら)
