メタデータの末尾にスキップ
メタデータの先頭に移動

返すよりも著しく多いデータをクエリが読み込む場合は、クエリプランにおそらく欠陥があります。おそらく、インデックスが使われていないか、選択されたインデックスがデータ調査をよく制限していません。クエリのパフォーマンスの分析およびSQLステートメントを最適化するために、EXPLAINの出力と以下のロギング機能を使用することができます。

悪いプランのクエリログのオプションは以下の通りです:

  1. session_log_bad_queries: (真偽値) 有効にするには0を指定します。無効にするには1を指定します。
  2. session_log_bad_read_threshold: 指定された数をクエリが超えた時に記録します。
  3. session_log_bad_read_ratio: Specify the ratio of rows read to rows used

 

以下のコマンドは20以上の行が読み込まれる時に、悪いプランを記録します:

SET GLOBAL session_log_bad_read_threshold = 20;

テーブルがメモリ内で走査されるほど十分小さい場合には、テーブルにインデックスを追加することでパフォーマンスは改善されないかも知れません。推奨: 特にプロダクション環境においては、小さなテーブルの走査が記録されることを避けるために、bad readの閾値を4000にします。

The session_log_bad_read_ratio variable controls the ratio of rows examined to rows used and logs any query returning results that exceed the specified limit. 以下の例では比率を4に設定します。

SET GLOBAL session_log_bad_read_ratio = 4;

例えば、25の行を含むインデックスされていないテーブルから一つの値を選択するには、DBMSは25行全てを走査する必要があります。ログのbad readの閾値を25以下に設定(上の例のように)すると、以下のようなメッセージがログファイルに追加されます:

May 1 18:28:15 alpha011 real: INSTR SID:57105409 db=paulm ac=Y sql="select * from x where id = 10" [Ok: 1 rows selected] time 0.5ms; reads: 3; inserts: 1; deletes: 0; updates: 0; counts: 1; rows_read: 25; forwards: 4; rows_output: 3; semaphore_matches: 0


id 列にインデックスを作成することで、クエリを満たすためにテーブル全体を走査する必要がなくなります。テーブルがインデックス化された後は、単一の値のSELECTはbadクエリプランを持っているとしてはもう記録されません。


しかし、単純なインデックスが常に問題を解決しないかも知れません。例えば、以下のように二つのテーブルを作り、配置した場合:

CREATE TABLE `x` (
   `id` int(11),
    KEY `idx_xid` (`id`)
);
 
-- テーブルxに25行を挿入します
 
CREATE TABLE `y` (
    `id` int(11),
    `x` int(11),
    KEY `xix` (`x`)
);
 
-- テーブルxに25行を挿入します


そして、以下のようにjoinを使って問いかけます:

SELECT * FROM x JOIN y ON x.id = y.x AND y.id = 2;

このクエリはbadプランを持っているとして記録されます:

May 1 20:10:34 alpha011 real: INSTR SID:57365505 db=paulm ac=Y sql="select * from x join y on x.id = y.x and y.id = 2" [Ok: 1 rows selected] time 178.6ms; reads: 4;inserts: 1; deletes: 0; updates: 0; counts: 1; rows_read: 26; forwards: 5; e0

26行が読み込まれましたが、一つだけが選択されました。EXPLAINコマンドは以下の統計を表示します:

EXPLAIN SELECT * FROM x JOIN y ON x.id = y.x AND y.id = 2;
 
+---------------------------------------+-----------+-----------+
| Operation                             | Est. Cost | Est. Rows |
+---------------------------------------+-----------+-----------+
| nljoin                                |    253.28 |     46.80 |
|   stream_combine                      |     45.20 |     18.00 |
|     filter (2.id = param(0))          |     14.13 |      6.00 |
|       index_scan 2 := y.__base_y      |     14.00 |      6.67 |
|   index_scan 1 := x.idx_xid, id = 2.x |     11.56 |      2.60 |
+---------------------------------------+-----------+-----------+
5 rows in set (0.00 sec)


統計によると、クエリプランナーはテーブルy上のインデックスxはユニークでは無いと判断し、yの中で id が 2 の行を見つけるために走査します。そしてxからの値に対して比較します。以下のように、y.id 上のインデックスによって、プランナーはyからの行を事前にフィルタするために 定数 y.id = 2 を使うことができます:

CREATE INDEX ixy_id ON y(id);

新しくインデックス化されたテーブルに対して実行すると、以前のSELECTはbadクエリプランを持っているとしてはもう記録されません。EXPLAIN コマンドは今度は以下の統計を表示します:

EXPLAIN SELECT * FROM x JOIN y ON x.id = y.x AND y.id = 2;
 
+-------------------------------------------------+-----------+-----------+
| Operation                                       | Est. Cost | Est. Rows |
+-------------------------------------------------+-----------+-----------+
| nljoin                                          |     32.99 |      2.63 |
|   nljoin                                        |     21.31 |      1.01 |
|     index_scan 2 := y.ixy_id, id = param(0)     |     10.61 |      1.01 |
|     index_scan 2 := y.__base_y, rowid = 2.rowid |     10.60 |      1.00 |
|   index_scan 1 := x.idx_xid, id = 2.x           |     11.56 |      2.60 |
+-------------------------------------------------+-----------+-----------+
5 rows in set (0.01 sec)
  • ラベルなし
TOP
inserted by FC2 system