8.2.1.19 LIMIT クエリーの最適化

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

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

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

• ORDER BY とともに LIMIT row_count を使用した場合、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 が使用されていないかぎり、クエリーを中止します。

• LIMIT 0 は迅速に空のセットを返します。これは、クエリーの妥当性のチェックに役立つことがあります。いずれかの MySQL API を使用している場合、それは結果カラムの型の取得にも使用できます。この技法は mysql クライアントプログラムでは機能せず、そのような場合には、単に Empty set を表示します。代わりに、この目的では SHOW COLUMNS または DESCRIBE を使用します。

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

複数の行の 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 |
+----+----------+--------+

MySQL 5.6.2 時点で、オプティマイザは次の形式のクエリー (およびサブクエリー) をより効率的に処理します。

SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,]N;

この種のクエリーは、大きな結果セットの数行だけを表示する Web アプリケーションで一般的なものです。例:

SELECT col1, ... FROM t1 ... ORDER BY name LIMIT 10;
SELECT col1, ... FROM t1 ... ORDER BY RAND() LIMIT 15;

ソートバッファーには、sort_buffer_size のサイズが入ります。N 行 (M が指定された場合は M+N 行) のソート要素が、ソートバッファーに収まるほど小さい場合、サーバーはマージファイルの使用を回避し、ソートバッファーを優先度キューとして扱うことでメモリー内で完全にソートを実行できます。

• テーブルをスキャンし、キュー内のソート順で選択された各行から選択リストカラムを挿入します。キューがいっぱいになった場合、ソート順で最後の行を押し出します。

• キューから最初の N 行を返します。(M が指定されている場合、最初の M 行をスキップし、次の N 行を返します。)

以前、サーバーはソートにマージファイルを使用して、この操作を実行していました。

• テーブルをスキャンし、テーブルの最後まで次の手順を繰り返します。

  • ソートバッファーがいっぱいになるまで、行を選択します。

  • バッファー内の最初の N 行 (M が指定された場合は M+N 行) をマージファイルに書き込みます。

• マージファイルをソートして、最初の N 行を返します。(M が指定されている場合、最初の M 行をスキップし、次の N 行を返します。)

テーブルスキャンのコストは、キュー方法でもマージファイル方法でも同じであるため、オプティマイザはその他のコストに基づいて、方法を選択します。

• キュー方法では、キューに行を順番に挿入するために多くの CPU を必要とします

• マージファイル方法では、ファイルの書き込みと読み取りの I/O コストとそれをソートするための CPU コストがあります

オプティマイザは、N の特定の値と行サイズのこれらの要素のバランスを考慮します。