【MySQL】良く使う調査コマンド・クエリ

SHOWコマンド

プロセス一覧

show processlist;

MySQLパラメータ確認

SHOW VARIABLES [like 'パラメータ名'];

パラメータ名に hostname と入れればホスト名が取得できるし、
buffer と入れるとメモリ関連、 dir と入れればディレクトリ情報などが確認できる。

SHOW VARIABLES で表示しきれない長い値については、回避策として SELECT @@パラメータ名; で表示可能

データベーススキーマ一覧

show databases [like 'スキーマ名' ] ;

ユーザ権限確認

SHOW GRANTS [FOR ユーザ名];

直前のクエリのエラーやワーニング確認

SHOW ERRORS;
SHOW COUNT(*) ERRORS;

SHOW WARNINGS;
SHOW COUNT(*) WARNINGS;

テーブル定義確認

show create table スキーマ名.テーブル名;

インデックス確認

show index from スキーマ名.テーブル名;

参考：https://dev.mysql.com/doc/refman/5.5/en/show.html
参考：https://dev.mysql.com/doc/refman/5.7/en/show.html

DB、テーブルサイズチェック

DBスキーマサイズ

SELECT 
    table_schema,
    SUM(data_length + index_length) / 1024 / 1024 AS all_tab_mb,
    SUM(data_length) / 1024 / 1024 AS data_mb,
    SUM(index_length) / 1024 / 1024 AS index_mb
FROM
    information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;

参考：https://qiita.com/ikenji/items/b868877492fee60d85ce

テーブルサイズ

SELECT 
    table_name,
    engine,
    table_rows AS tbl_rows,
    avg_row_length AS rlen,
    FLOOR((data_length + index_length) / 1024 / 1024) AS all_tab_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;

参考：https://qiita.com/ikenji/items/b868877492fee60d85ce

InnoDBテーブル断片化チェック

SELECT 
    table_schema, table_name, data_free, table_rows
FROM
    information_schema.tables
WHERE
    table_schema = 'スキーマ名'
AND
    table_name = 'テーブル名';

断片化解消するには？(INNODB前提)

ALTER TABLE テーブル名 ENGINE INNODB;

参考：http://d.hatena.ne.jp/yohei-a/20180610/1528650004

ロック競合

MySQL5.6以前

SELECT 
    t_b.trx_mysql_thread_id blocking_id,
    t_w.trx_mysql_thread_id requesting_id,
    p_b.HOST blocking_host,
    p_w.HOST requesting_host,
    l.lock_table lock_table,
    l.lock_index lock_index,
    l.lock_mode lock_mode,
    p_w.TIME seconds,
    p_b.INFO blocking_info,
    p_w.INFO requesting_info
FROM
    information_schema.INNODB_LOCK_WAITS w,
    information_schema.INNODB_LOCKS l,
    information_schema.INNODB_TRX t_b,
    information_schema.INNODB_TRX t_w,
    information_schema.PROCESSLIST p_b,
    information_schema.PROCESSLIST p_w
WHERE
    w.blocking_lock_id = l.lock_id
        AND w.blocking_trx_id = t_b.trx_id
        AND w.requesting_trx_id = t_w.trx_id
        AND t_b.trx_mysql_thread_id = p_b.ID
        AND t_w.trx_mysql_thread_id = p_w.ID
ORDER BY requesting_id , blocking_id;

MySQL5.7以降

SELECT 
    *
FROM
    sys.innodb_lock_waits;

参考：http://d.hatena.ne.jp/sh2/20090618
参考：https://qiita.com/hmatsu47/items/607d176e885f098262e8