by Hayato Matsuura.

アクセス述語とフィルタ述語の見分け方


MySQLは、where句(述語)を適用するのに、 3つの方法を使い分けます。

アクセス述語 (「key_len」 列)

アクセス述語は、リーフノードの走査の開始と終了の条件を表しています。

インデックスフィルタ述語 (MySQL 5.6以降の「Using index condition」)

インデックスフィルタ述語は、リーフノード走査の時にのみ適用されます。スキャンされる範囲の開始と終了の条件を表したり、 範囲を狭めるものとしては使われません。

テーブルレベルのフィルタ述語 (「Extra」列の「Using where」)

インデックスの一部でない列に対する述語は、テーブルのレベルで評価されます。その場合データベースは、 まずテーブルから行をロードしなければなりません。

MySQLの実行計画は、各条件に対してどの述語タイプが使われるかは 表示してくれません。使用されている述語タイプを列挙するだけです。

次の例では、where句全体が アクセス述語として使われています。

CREATE TABLE demo (
   id1 NUMERIC
 , id2 NUMERIC
 , id3 NUMERIC
 , val NUMERIC)
INSERT INTO demo VALUES (1,1,1,1)
INSERT INTO demo VALUES (2,2,2,2)
CREATE INDEX demo_idx
          ON demo
             (id1, id2, id3)
EXPLAIN
 SELECT * 
   FROM demo
  WHERE id1=1
    AND id2=1
+------+----------+---------+-------------+------+-------+
| type | key      | key_len | ref         | rows | Extra |
+------+----------+---------+-------------+------+-------+
| ref  | demo_idx | 12      | const,const |    1 |       |
+------+----------+---------+-------------+------+-------+

「Extra」列には、「Using where」も「Using index condition」も 表示されていません。しかしインデックスは使われており(type=ref, key=demo_idx)、where句全体が アクセス述語になっているであろう事が分かります。

協力してください

この記事が気に入ったら、私の書いた本「SQLパフォーマンス詳解」や私によるトレーニングもきっと気にいるはず。

ref列が、インデックスから2つの列を使っていることを 示しているかも確認してください。インデックスのどの部分が使われているかを確認するもうひとつの 方法は、key_lenの値を見ることです。ここでは、このクエリはインデックスの定義の 最初の12バイトを使っていると表示されています。これを列名に対応付けるには、各列が どのくらいのストレージ容量を使用するかを「とにかく」知っておかなければなりません(MySQLのドキュメントのデータ型のストレージ要件 も参照してください)。NOT NULL制約がない場合、MySQLは各列に追加の容量を必要とします。結局、 NUMERICの各列は、例では6バイトが必要です。従って、 キー長の12バイトは、アクセス述語としてインデックスの列の最初の2つを使っているということになります。

(ID2の代わりに)ID3でフィルタすると、MySQL 5.6以降ではインデックスフィルタ述語(「Using index condition」)を使います。

EXPLAIN
 SELECT * 
   FROM demo
  WHERE id1=1
    AND id3=1
+------+----------+---------+-------+------+-----------------------+
| type | key      | key_len | ref   | rows | Extra                 |
+------+----------+---------+-------+------+-----------------------+
| ref  | demo_idx | 6       | const |    1 | Using index condition |
+------+----------+---------+-------+------+-----------------------+

この場合、ken_len=6およびref列に ひとつだけあるconstは、ひとつの列だけがアクセス述語として使われているという事を示しています。

5.5以前のMySQLバージョンでは、このクエリはテーブルレベルのフィルタ述語になります。これは、「Extra」列に「Using where」が出る事から分かります。

+------+----------+---------+-------+------+-------------+
| type | key      | key_len | ref   | rows | Extra       |
+------+----------+---------+-------+------+-------------+
| ref  | demo_idx | 6       | const |    1 | Using where |
+------+----------+---------+-------+------+-------------+

ヒント

著者について

Markus Winandの写真

Markus Winand氏は、開発者がSQLパフォーマンスを改善するお手伝いをしています。彼は、SQL Performance Explainedの 著者でもあり、出張トレーニングhttp://winand.at/での リモート講義も 行っています。

彼の本

カバー『SQLパフォーマンス詳解』

核心をわかりやすく 解説。

Markusから購入します
(送料無料+PDF)

Amazonで購入
(印刷版のみ)

Do not use offset for pagination

Learn why

Visit my sibling!A lot changed since SQL-92!

Use The Index, Luke のカップは

ステッカー、コースター、本、コーヒーマグ。 学習に必要なものすべて。

今すぐ購入

Connect with Markus Winand

Markus Winand on LinkedInMarkus Winand on XINGMarkus Winand on Twitter
“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
法律上の通知 | お問い合わせ | 無保証 | 商標 | プライバシーとGDPR | CC-BY-NC-ND 3.0 license