log_queries_not_using_indexesオプションの値の確認・設定方法
global variablesの値が「on」になっていれば、indexが使われていないSQLが抽出できます。
mysql> show global variables like '%indexes'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | log_queries_not_using_indexes | ON | +-------------------------------+-------+ 1 row in set (0.00 sec)
もし、offになっている場合には以下のコマンドで有効に変更できます。
mysql> set global log_queries_not_using_indexes = on;
抽出されたSQLの出力先
log_queries_not_using_indexesのログは、slow queryログに出力されるのでslow queryの設定で確認できます。slow_query_logが、slow queryログを出力するかどうかの設定。onになっていれば、ログが出力されます。
slow_query_log_fileが、ログの出力先となります。
mysql> show global variables like '%slow%'; +---------------------+------------------+ | Variable_name | Value | +---------------------+------------------+ | log_slow_queries | ON | | slow_launch_time | 2 | | slow_query_log | ON | | slow_query_log_file | c:\work\slow.log | +---------------------+------------------+ 4 rows in set (0.00 sec)
slow queryの設定は、以下のように「set global」コマンドで変更できます。
mysql> set global slow_query_log = on; Query OK, 0 rows affected (0.00 sec) mysql> set global slow_query_log_file = 'c:\\slow.log'; Query OK, 0 rows affected (0.00 sec)
動作を確認してみよう
テスト用に主キーのみのテーブルを作成
mysql> create table test_table (col1 char(1), primary key (col1)); Query OK, 0 rows affected (0.02 sec)
以下の２種類のSQLを実行。前者のSQLは、主キー検索のためindexが使用される。
後者のSQLは、関数を使用しているのでindexは使用されずに検索が行われる。
mysql> select * from test_table where col1 = '1'; Empty set (0.00 sec) mysql> select * from test_table where substr(col1, 1, 1) = '1'; Empty set (0.00 sec)
slow queryログには、以下のようにindexが使用されなかったSQLの情報が取得できる。
# Time: 111030 16:00:38 # User@Host: root[root] @ localhost [::1] # Query_time: 0.000500 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0 use test_db; SET timestamp=1319958038; select * from test_table where substr(col1, 1, 1) = '1';
これを使えばindexの張り忘れや、SQLのつくりが原因でindex使われていないSQLを機械的に抽出できるかなと。