結果セットから指定した数の行のみが必要な場合、結果セット全体をフェッチして、余分なデータを破棄するのではなく、クエリーで LIMIT
句を使用します。
MySQL は LIMIT
句があり row_count
HAVING
句のないクエリーを最適化することがあります。
LIMIT
で少数の行のみを選択すると、MySQL では、通常フルテーブルスキャンを実行するより望ましい特定の場合に、インデックスが使用されます。-
ORDER BY
とともにLIMIT
を使用した場合、MySQL では、結果全体をソートするのではなく、ソートされた結果の最初のrow_count
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
の特定の値と行サイズのこれらの要素のバランスを考慮します。