Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 27.1Mb
PDF (A4) - 27.1Mb
EPUB - 7.5Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb


8.2.1.2 MySQL の WHERE 句の最適化の方法

このセクションでは、WHERE 句の処理で実行可能な最適化について説明します。例では SELECT ステートメントを使用していますが、DELETE および UPDATE ステートメント内の WHERE 句にも同じ最適化を適用します。

注記

MySQL オプティマイザへの取り組みは継続中であるため、MySQL が実行する最適化のすべてをここで説明しているわけではありません。

読みやすさを犠牲にしても、算術演算を高速化するように、クエリーを書き換えたいと考えがちです。MySQL では同様の最適化を自動的に実行するため、多くの場合にこの作業を回避でき、クエリーを理解しやすく、保守しやすい形式のままにしておくことができます。MySQL によって実行される最適化の一部を次に示します。

  • 不要なかっこの削除:

       ((a AND b) AND c OR (((a AND b) AND (c AND d))))
    -> (a AND b AND c) OR (a AND b AND c AND d)
    
  • 定数畳み込み:

       (a<b AND b=c) AND a=5
    -> b>5 AND b=c AND a=5
    
  • 定数条件の削除 (定数畳み込みのために必要です):

       (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
    -> B=5 OR B=6
    
  • インデックスによって使用される定数式は 1 回だけ評価されます。

  • WHERE を使用しない単一テーブルの COUNT(*) は、MyISAM テーブルと MEMORY テーブルのテーブル情報から直接取得されます。これは、1 つだけのテーブルで使用された場合に、NOT NULL 式にも実行されます。

  • 無効な定数式の早期の検出。MySQL は一部の SELECT ステートメントが実行不可能であることをすみやかに検出し、行を返しません。

  • GROUP BY または集約関数 (COUNT()MIN() など) を使用しない場合、HAVINGWHERE とマージされます。

  • 結合内の各テーブルについて、テーブルの高速の WHERE 評価を取得し、可能なかぎり早く行をスキップするために、より単純な WHERE が構築されます。

  • クエリー内のほかのすべてのテーブルの前に、まず、すべての定数テーブルが読み取られます。定数テーブルは次のいずれかです。

    • 空白のテーブルまたは 1 行のテーブル。

    • PRIMARY KEY または UNIQUE インデックスでの WHERE 句で使用されるテーブル。ここではすべてのインデックス部分が定数式と比較され、NOT NULL として定義されます。

    次のテーブルはすべて定数テーブルとして使用されます。

    SELECT * FROM t WHERE primary_key=1;
    SELECT * FROM t1,t2
      WHERE t1.primary_key=1 AND t2.primary_key=t1.id;
    
  • テーブルを結合するための最適な結合の組み合わせは、すべての可能性を試してみることで見つかります。ORDER BY および GROUP BY 句内のすべてのカラムが同じテーブルにある場合、結合する際に最初にそのテーブルが選ばれます。

  • ORDER BY 句と別の GROUP BY 句がある場合、または、ORDER BY または GROUP BY に結合キュー内の最初のテーブルと異なるテーブルのカラムが含まれている場合は、一時テーブルが作成されます。

  • SQL_SMALL_RESULT オプションを使用すると、MySQL ではインメモリー一時テーブルが使用されます。

  • オプティマイザがテーブルスキャンを使用する方が効率的であると判断しないかぎり、各テーブルインデックスがクエリーされ、最適なインデックスが使用されます。かつて、スキャンは、最適なインデックスがテーブルの 30% 超にまたがっているかどうかに基づいて使用されていましたが、固定のパーセンテージによって、インデックスを使用するか、スキャンを使用するかの選択が決定されなくなりました。現在のオプティマイザは複雑になり、テーブルサイズ、行数、I/O ブロックサイズなどの追加の要因に基づいて推定します。

  • 場合によって、MySQL はデータファイルを参照しなくてもインデックスから行を読み取ることができます。インデックスから使用されるすべてのカラムが数値の場合、クエリーの解決にインデックスツリーのみが使用されます。

  • 各行が出力される前に、HAVING 句に一致しないものはスキップされます。

きわめて高速なクエリーのいくつかの例:

SELECT COUNT(*) FROM tbl_name;

SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;

SELECT MAX(key_part2) FROM tbl_name
  WHERE key_part1=constant;

SELECT ... FROM tbl_name
  ORDER BY key_part1,key_part2,... LIMIT 10;

SELECT ... FROM tbl_name
  ORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10;

MySQL は、インデックス設定されたカラムが数値であるとして、インデックスツリーのみを使用して、次のクエリーを解決します。

SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;

SELECT COUNT(*) FROM tbl_name
  WHERE key_part1=val1 AND key_part2=val2;

SELECT key_part2 FROM tbl_name GROUP BY key_part1;

次のクエリーは、個別のソーティングパスを使用せずに、インデックスを使用して、ソート順で行を取得します。

SELECT ... FROM tbl_name
  ORDER BY key_part1,key_part2,... ;

SELECT ... FROM tbl_name
  ORDER BY key_part1 DESC, key_part2 DESC, ... ;

User Comments
  Posted by André Somplatzki on April 15, 2005
Indices lose their speed advantage when using them in OR-situations (4.1.10):

SELECT * FROM a WHERE index1 = 'foo'
UNION
SELECT * FROM a WHERE index2 = 'baar';

is much faster than

SELECT * FROM a WHERE index1 = 'foo' OR index2 = 'bar';
  Posted by Duncan Simpson on April 19, 2005
The query optimiser also does badly on examples like

SELECT id FROM foo WHERE bar IN (SELECT bar FROM baz WHERE qux='foo')

where foo is a large table and baz a small one. Doing the subselect first would allow the use on an index to eliminate most of foo, which is what happens if you say

SELECT foo.id FROM foo, baz WHERE foo.bar=baz.bar and baz.qux='foo'

However the latter move is not possible if the first operation is DELETE and you want to avoid MySQL specific syntax (DELETE FROM .. USING .. WHERE ... also does the sensible thing but is not SQL-99).
  Posted by Henrik Grubbström on March 24, 2006
Indexes are ignored for the <> operator:

SELECT * FROM tab WHERE score <> 0;

This can be a problem if the table is very slanted (eg >99% of of the rows have the value that is filtered). The obvious workaround is to use a UNION:

(SELECT * FROM tab WHERE score > 0) UNION
(SELECT * FROM tab WHERE score < 0);

  Posted by Gints Plivna on September 19, 2008
SELECT * FROM tab WHERE score <> 0;
functionally IS NOT THE SAME AS
(SELECT * FROM tab WHERE score > 0) UNION
(SELECT * FROM tab WHERE score < 0);
because UNION filters out duplicates.
If one needs this one has to use UNION ALL.
  Posted by Pól Ua Laoínecháin on June 9, 2014
I think that Henrik implicitly assumed that the table (tab) has a primary key (and that score is not part of it), in which case he's correct.
  Posted by Tim Yan on May 10, 2015
Sometime this statement is not true:

SELECT * FROM a WHERE index1 = 'foo'
UNION
SELECT * FROM a WHERE index2 = 'baar';

is much faster than

SELECT * FROM a WHERE index1 = 'foo' OR index2 = 'bar';

If you have many records with 'foo' or 'baar', to satisfy your union, mysql has to pull all 'foo' and 'baar' records and do the 'union' merge. If you have a 5 million records with 3 million 'foo' and 2 'baar', your query will be very slow, much worse than the OR statement.

When your table is very flat (many columns), the problem can be worse since the temp table used for the "union" can be huge.

It can also be an issue when you try to pull "first 100" records using "limit". MySQL has to pull all records before it can do the UNION then pick the first 100 records for you. A table scan can actually stop earlier when MySQL finds enough result.

Most people will consider it a rare case. Think about a search on Ip addresses in your web log table, you may find surprisingly how many records matching one Ip address (or range).


Also, the Union query may generate different results. You need UNION ALL and this:

SELECT * FROM a WHERE index1 = 'foo'
UNION ALL
SELECT * FROM a WHERE index2 = 'baar' and index1 !='foo'

Sign Up Login You must be logged in to post a comment.