Database JUNKY

MySQL,MariaDBを中心としたブログです

トップ > index > 遅いクエリーを検出する①

遅いクエリーを検出する①

index MySQL インデクス スロークエリ チューニング ログ 最適化 索引

mysqlにかかわらず、データベース全般のチューニングの基本は、まず、インデクスを貼るってことだと思います。 mysqlでは特に、レプリケーションとか、クラスタリングとか、とにかくそちらでなんとかしようって動きになってしまいがちがですが、他のDBMSでは、この部分がとにかく最初の最初でしょう。と自分も反省

で、MySQLでどうやってインデクスを貼る必要があるテーブルを特定するかですが、一番簡単な方法としては、ログを確認するって方法かと思います。じゃあ、なんのログってことになりますが。それはslow query log を見るというのが一番ベターな方法でしょう。ibm db2をよく使っておりましたが、知り限りではそういうログってなかったような、気がしますので大体システムがリリースされる前に発行されるクエリを出してもらって、コストを図るってのが基本だった気がします。

  1. 設定はできておりますか？ まず、slowqueryログが有効かどうかについては以下のコマンドで確認できます。mysql> show variables like 'log%'; +---------------------------------+----------------------------------------+ | Variable_name                   | Value                                  | +---------------------------------+----------------------------------------+ | log                             | ON                                     | | log_bin                         | ON                                     | | log_bin_trust_function_creators | OFF                                    | | log_bin_trust_routine_creators  | OFF                                    | | log_error                       | /var/lib/mysql/sqdbm02.squad.local.err | | log_output                      | FILE                                   | | log_queries_not_using_indexes | OFF                                    | | log_slave_updates               | OFF                                    | | log_slow_queries | ON                                     | | log_warnings                    | 1                                      | +---------------------------------+----------------------------------------+ 上記の設定で、インデクスを利用していないクエリーは、OFFになっておりました。この場合は、/etc/my.cnfに以下の追記をするか？ # vi /etc/my.cnf [mysqld] log_queries_not_using_indexes = 1 コマンドで以下のように実行するか mysql> set @@global.log_queries_not_using_indexes = 1; にします。ログの出力が環境によって半端ないかと思いますので、my.cnfに書くより一時的にコマンド対応したほうがよさそうです 一方、どれくらいの秒数でslowqueryと判断するかについては以下のコマンドで確認できます。 mysql> show variables like 'long%'; +-----------------+----------+ | Variable_name   | Value    | +-----------------+----------+ | long_query_time | 1.000000 | +-----------------+----------+ 1 row in set (0.00 sec)
  2. 確認してみよう 上記を実行した上でログを確認してみよう、slow queryのログは、こんな感じで表示されております。 # Time: 110110 17:14:36 # User@Host: root[root] @ sqwww01.squad.local [192.168.0.31] # Query_time: 0.002914  Lock_time: 0.000116 Rows_sent: 188  Rows_examined: 220 SET timestamp=1294647276; SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'; 上記出力の意味は以下の通りになります。 ・Time: ログが出力っされた日時 ・User@Host : 実行したユーザとホスト ・Query_time,Lock_Time,Rows_sent：Query_time, Rows_examined : 実行時間,ロック時間,送信行数,処理対象となった行数 ・クエリ時間 ざっとみた感じでは、インデクスを利用しないでアクセスしているクエリは、Query_timeが、1秒以下で出力されているものすべてかな？というのが僕の解釈です。
  3. どこにインデクスを張るべきか考える たとえば上記のクエリは、どのようなアクセスパスを利用してこのような結果になったのかに関してはEXPLAINをつけることで確認することができる。下記Slow Queryに出力されたクエリを、EXPLAINした結果は以下の通り mysql> EXPLAIN SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: wp_options type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 701 Extra: Using where 1 row in set (0.00 sec) ERROR: No query specified うう。。見方がわからない。。。 今回はインデクスのチューニングという部分だけ考えると、どうやらtypeの部分だけに着目すれば良いのかな？ということで、typeについて説明する
  4. typeを確認する MySQLリファレンスから丸パクです。すみません。 system： 1 レコードのみで構成されるテーブル（= システムテーブル）。これは、const結合型の特殊なケースである。 const： テーブルに、一致するレコードが最大で 1 つあり、クエリの開始時に読み取られる。レコードが 1 つしかないため、このレコードのカラムの値はオプティマイザによって定数と見なされる。constテーブルは、1 回しか読み取られないため、非常に高速である。 eq_ref： 前のテーブルのレコードの組み合わせのそれぞれに対して、このテーブルから 1 レコードずつ読み取られる。これは、systemとconst型以外で最適な結合型である。 結合でインデックスのすべての部分が使用され、このインデックスが UNIQUEまたは PRIMARY KEYである場合に使用される。 ref： 前のテーブルのレコードの組み合わせのそれぞれに対して、インデックス値にマッチするすべてのレコードがこのテーブルから読み取られる。refは、インデックスの左端の先頭部分のみが結合で使用される場合、またはインデックスが UNIQUEや PRIMARY KEYではない場合（すなわち、この結合において、インデックス値から1つのレコードをSELECTできない場合）に使用される。 この結合型は、使用されるインデックスと一致するレコードが数レコードしかない場合に適している。 ref_or_null： refと同様だが、NULLを使用したレコードの補足検索も追加で実行される。 この結合型の最適化は主としてサブクエリを解決する場合に使用される。 下記の例では、MySQLはref_tableで ref_or_nullが使用される。 unique_subquery： この型は、下記のフォームでINサブクエリの代わりに、refを使用します。 index_subquery： この結合型はunique_subqueryに似ています。INサブクエリの代わりに使用されますが、下記のサブクエリのフォームでユニークではないインデックスで使用できます。 range： インデックスを使用して、一定の範囲にあるレコードのみが取り出される。keyカラムに使用されるインデックスが示される。key_len_には使用される最長のインデックス部分が記載される。 この型ではrefカラムがNULLになる。 range は、インデックスを張っているカラムが =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、およびIN を使用して定数と比較される場合に使用される。 index： これは、インデックスツリーのみがスキャンされる点を除いて ALLと同じである。一般にインデックスファイルはデータファイルより小さいため、通常は ALLより高速である。 MySQL は、クエリで 1 インデックスの構成部分であるカラムのみが使用される場合にのみ使用できます。 all： 前のテーブルのレコードの組み合わせのそれぞれに対して、フルテーブルスキャンが実行される。一般に、テーブルが constの指定がない第 1 テーブルの場合には適さず、その他の場合はすべて非常に不適である。 通常は、さらにインデックスを追加することで ALLを回避し、 定数値または以前のテーブルのカラム値を基準にレコードを取り出すようにすることができる。 ここを見る限りでは、allが選択されている部分が一番コストが高いと思われるので、インデクスのチューニングが必要と思われ
  5. インデクスを設定する 上記の設定結果から、インデクスを張ったほうが良いと思われる部分にインデクスを貼る、ちょっと例が悪いけどこんな感じでインデクスを貼る mysql> create index idx01_wp_options on wp_options(autoload); Query OK, 0 rows affected (0.10 sec) Records: 0  Duplicates: 0  Warnings: 0
  6. 再度確認 索引を張ったところ以下のような結果になった mysql> EXPLAIN SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: wp_options type: ref possible_keys: idx01_wp_options key: idx01_wp_options key_len: 62 ref: const rows: 188 Extra: Using where 1 row in set (0.00 sec) ERROR: No query specified ざくっとした説明で申し訳ないですが。、ようは、インデクスを使うようになったということを上記にあらわしております。

こんな感じでイタチごっこではありますが、インデクスのチューニングを随時進めておきます。根気強く行うことがパフォーマンスアップにつながるでしょう（きっと）、ただしものによっては、allのほうが速いパターンもあります。なんでもかんでもインデクスを張ればいいというものでもないのでそのあたりは、データベースエンジニアとアプリケーションスペシャリストとの力量しだいですかね？

そんなところで今日はおしまい。

SlowLog