information_schemaでちょこちょこ使えるTIPS

ちょこちょこ使うi_s関連のSELECTステートメント。
やる前にSET GLOBAL innodb_stats_on_metadata= 0; しておかないと重くなる。

• xxxってカラム、どのテーブルにあるんだっけ？

mysql56> SELECT CONCAT(table_schema, '.', table_name) AS object, column_type FROM columns WHERE column_name= ? ORDER BY 1, 2;

• データベース上のINDEXの一覧がほしい。

mysql56> SELECT CONCAT(table_schema, '.', table_name) AS object, index_name, GROUP_CONCAT(column_name ORDER BY seq_in_index) AS columns FROM statistics WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema') GROUP BY 1, 2;

• フルテキストインデックスどこだっけ？

mysql56> SELECT CONCAT(table_schema, '.', table_name) AS object, index_name, GROUP_CONCAT(column_name ORDER BY seq_in_index) AS columns FROM statistics WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema') AND index_type= 'FULLTEXT' GROUP BY 1, 2;

• パーティションの一覧と、入っている件数がほしい。
• partition_name IS NOT NULLをはずすと、パーティショニングされてないテーブルも出力できる。

mysql56> SELECT CONCAT(table_schema, '.', table_name) AS object, partition_name, table_rows FROM partitions WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema') AND partition_name IS NOT NULL ORDER BY 1, 2;

• PARTITION .. LESS THAN MAXVALUEなパーティションに今入っている件数をチェック。

mysql56> SELECT CONCAT(table_schema, '.', table_name) AS object, partition_name, table_rows FROM partitions WHERE partition_description= 'MAXVALUE' ORDER BY 1, 2;

• InnoDBバッファプールにどのテーブルのデータがどれくらい載ってるか知りたい。
• table_name, index_nameがNULLのは空きページ。

mysql56> SELECT table_name, index_name, SUM(number_records) AS record, SUM(data_size) AS datasize FROM innodb_buffer_page GROUP BY 1, 2;

• テーブルごと、スキーマごと、エンジンごとのデータサイズを一発で。

mysql56> SELECT engine, table_schema, table_name, SUM(data_length+ index_length) AS size FROM tables WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema') GROUP BY 1, 2, 3 WITH ROLLUP;

• InnoDBのもろもろの情報を詳しく(5.6から)
• デフォルトでは面白そうなモニターはdisabledにされているので、適宜enabledしておかないとデータが取れない http://dev.mysql.com/doc/refman/5.6/en/innodb-metrics-table.html

mysql56> SELECT name, count, status FROM innodb_metrics;

InnoDBのロック競合を解析するアレはご本家をどうぞ。
　⇒ MySQL InnoDBにおけるロック競合の解析手順 - SH2の日記


【2014/03/05 18:41】
1箇所ANDが抜けてた…(´･ω･｀)