このページは機械翻訳したものです。

8.2.1.19 LIMIT クエリーの最適化

結果セットから指定した数の行のみが必要な場合、結果セット全体をフェッチして、余分なデータを破棄するのではなく、クエリーで LIMIT 句を使用します。

MySQL は LIMIT row_count 句があり HAVING 句のないクエリーを最適化することがあります。

• LIMIT で少数の行のみを選択すると、MySQL では、通常フルテーブルスキャンを実行するより望ましい特定の場合に、インデックスが使用されます。

• LIMIT row_count を ORDER BY と組み合せると、MySQL はソート結果の最初の row_count 行を見つけた直後に、結果全体をソートするのではなくソートを停止します。 インデックスを使用して順序付けが行われている場合、これはきわめて高速になります。 filesort を実行する必要がある場合、最初の row_count を見つける前に、LIMIT 句を使用しないクエリーに一致するすべての行が選択され、それらのほとんどまたはすべてがソートされます。 初期の行が見つかったら、MySQL は結果セットの残りをすべてソートしません。

  この動作をはっきり示している現象の 1 つは、このセクションで後述するように、LIMIT を付けるか付けないかで ORDER BY クエリーは異なる順序で行を返す場合があることです。

• LIMIT row_count を DISTINCT と組み合せると、MySQL は row_count の一意の行を検出するとすぐに停止します。

• 場合によっては、インデックスを順番に読み取る (またはインデックスでソートする) ことで GROUP BY を解決し、インデックス値が変更されるまでサマリーを計算できます。 この場合、LIMIT row_count は不要な GROUP BY 値を計算しません。

• MySQL は必要な数の行をクライアントに送信するとただちに、SQL_CALC_FOUND_ROWS が使用されていないかぎり、クエリーを中止します。 その場合、SELECT FOUND_ROWS() を使用して行数を取得できます。 セクション12.16「情報関数」を参照してください。

• LIMIT 0 は迅速に空のセットを返します。 これは、クエリーの妥当性のチェックに役立つことがあります。 また、結果セットメタデータを使用可能にする MySQL API を使用するアプリケーション内の結果カラムのタイプを取得するためにも使用できます。 mysql クライアントプログラムでは、--column-type-info オプションを使用して結果カラムタイプを表示できます。

• サーバーは、一時テーブルを使用してクエリーを解決する場合、LIMIT row_count 句を使用して必要な容量を計算します。

• ORDER BY にインデックスが使用されていないが、LIMIT 句も存在する場合、オプティマイザはインメモリー filesort 操作を使用して、マージファイルの使用を回避し、メモリー内の行をソートできます。

複数の行の ORDER BY カラムに同一の値がある場合、サーバーは自由にそれらの行を任意の順序で返しますが、その実行は実行プラン全体によって異なることがあります。 言い換えると、それらの行のソート順序は、順序付けされていないカラムに関して決定的ではありません。

実行プランに影響する 1 つの要素は LIMIT であるため、LIMIT を付けるか付けないかで ORDER BY クエリーは異なる順序で行を返すことがあります。 category カラムによってソートされるが、id および rating カラムに関して非決定的である次のクエリーを考慮します。

mysql> SELECT * FROM ratings ORDER BY category;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

LIMIT を含めると、各 category 値内の行の順序に影響することがあります。 たとえば、これは有効なクエリー結果です。

mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  4 |        2 |    3.5 |
|  3 |        2 |    3.7 |
|  6 |        2 |    3.5 |
+----+----------+--------+

各ケースで、行は ORDER BY カラムによってソートされますが、SQL 標準で必要とされるのはこれだけです。

LIMIT を使用してもしなくても同じ行順序を確保することが重要な場合は、ORDER BY 句に順序を決定的にする追加カラムを含めます。 たとえば、id 値が一意の場合、次のようにソートすることで、特定の category 値の行を id の順序で表示できます:

mysql> SELECT * FROM ratings ORDER BY category, id;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

mysql> SELECT * FROM ratings ORDER BY category, id LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
+----+----------+--------+

ORDER BY または GROUP BY と LIMIT 句を含むクエリーの場合、オプティマイザは、クエリーの実行速度を上げるために、順序付けられたインデックスをデフォルトで選択しようとします。 MySQL 8.0.21 より前は、他の最適化を使用する方が高速であっても、この動作をオーバーライドする方法はありませんでした。 MySQL 8.0.21 以降では、optimizer_switch システム変数 prefer_ordering_index フラグを off に設定することで、この最適化をオフにできます。

例: まず、次に示すように、テーブル t を作成して移入します:

# Create and populate a table t:

mysql> CREATE TABLE t (
    ->     id1 BIGINT NOT NULL,
    ->     id2 BIGINT NOT NULL,
    ->     c1 VARCHAR(50) NOT NULL,
    ->     c2 VARCHAR(50) NOT NULL,
    ->  PRIMARY KEY (id1),
    ->  INDEX i (id2, c1)
    -> );

# [Insert some rows into table t - not shown]

prefer_ordering_index フラグが有効になっていることを確認します:

mysql> SELECT @@optimizer_switch LIKE '%prefer_ordering_index=on%';
+------------------------------------------------------+
| @@optimizer_switch LIKE '%prefer_ordering_index=on%' |
+------------------------------------------------------+
|                                                    1 |
+------------------------------------------------------+

次のクエリーには LIMIT 句があるため、可能な場合は順序付けされたインデックスを使用する必要があります。 この場合、EXPLAIN 出力からわかるように、テーブルの主キーが使用されます。

mysql> EXPLAIN SELECT c2 FROM t
    ->     WHERE id2 > 3
    ->     ORDER BY id1 ASC LIMIT 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: index
possible_keys: i
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 2
     filtered: 70.00
        Extra: Using where

次に、prefer_ordering_index フラグを無効にし、同じクエリーを再実行します。今回は、インデックス i (WHERE 句で使用される id2 カラムを含む) と filesort を使用します:

mysql> SET optimizer_switch = "prefer_ordering_index=off";

mysql> EXPLAIN SELECT c2 FROM t
    ->     WHERE id2 > 3
    ->     ORDER BY id1 ASC LIMIT 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: range
possible_keys: i
          key: i
      key_len: 8
          ref: NULL
         rows: 14
     filtered: 100.00
        Extra: Using index condition; Using filesort

セクション8.9.2「切り替え可能な最適化」も参照してください。