MySQLで取得する行数を制限するのに、LIMIT句を利用することがあると思います。たとえば、ページャーを再現するときに、LIMITとOFFSETを利用して作成したりするのではないでしょうか。

今回は、そんなLIMIT句の利用について見ていきましょう。なお、今回利用するOSはCentOS 7、MySQLはバージョン8.0.23になります。

LIMIT句の使い方

まずはおさらいとして、LIMIT句の使い方を確認しましょう。通常LIMIT句はSELECT文で返される結果の行の制限を利用するのに用いられます。LIMIT句を利用したときは、数値の引数を1つ渡すことで、取得する行数を制限することができます。

mysql> SELECT * FROM t1 LIMIT 1;

+-------+
| id    |
+-------+
|     1 |
+-------+

OFFSET句を組み合わせることで、制限を開始する行を制御することができます。

mysql> SELECT id FROM t1 ORDER BY id LIMIT 3 OFFSET 10000;

+-------+
| id    |
+-------+
| 10001 |
| 10002 |
| 10003 |
+-------+

LIMIT句に2つの引数を渡すことで、OFFSETを省略して書くこともできます。この場合はLIMIT [OFFSET値], [制限する行数]の形式で書くことになります。

※）

OFFSETを明示的に書く場合はLIMIT n OFFSET mと書きますが、省略して書く場合は逆にLIMIT m, nと書くので注意してください。

mysql> SELECT id FROM dummy ORDER BY id LIMIT 10000, 3;
+-------+
| id    |
+-------+
| 10001 |
| 10002 |
| 10003 |
+-------+

ORDER BYとLIMITとINDEX

ORDER BYとLIMITをもつSQLに関して、オプティマイザはINDEXを使って高速化できると判断した場合、WHERE句で絞り込むためのINDEXではなく、ORDER BYのためのINDEXを選択するかもしれません。これは、ソート済みのINDEXを使えば並び替えの必要がなく、LIMITで必要な件数を取得すればその時点ではや抜けできるためです。

この挙動は、バージョン8.0.21で追加されたoptimizer_switchのprefer_ordering_indexで制御することができます。詳細は第135回 MySQL 8.0で追加されたoptimizer_switchのフラグについてのprefer_ordering_indexの項目で説明していますので、こちらを参照ください。。

LIMIT句のよくある勘違い

たまに、クエリチューニングとして重いクエリに対してLIMIT OFFSETを使って、SQLを数回に分けて実施するようなバッチ処理を見かけることがあります。LIMIT n OFFSET mを使ったクエリは実際 n + m 分の件数を取得した後にｍ件を破棄しているため、OFFSETが深くなるに連れて遅くなります。そのため、かえって1回で取得してあげたほうが早く終ることがあります。

もし複数回に分けて実施する場合はLIMIT OFFSETを使ってクエリを分けるより、PRIMARY KEYやINDEXを使って範囲検索を複数回実施してあげるアプローチをとるようにしたほうが効果が出るかと思います。

※）

ただし、このアプローチはトランザクションで保護していなかったり、トランザクション分離レベルがREAD-COMMITTED、またはREAD-UNCOMMITTEDの場合では、複数回実行している間にレコードの追加、削除によって漏れてしまうデータがある事についても十分に考慮が必要です。

件数はアプリケーションで制限するか、SQLで制限するか

指定の件数を表示する際に、MySQLからSQLで取得して、アプリケーションを使って表示する場面はいくつかあると思います。

筆者としては、この場合はSQLでLIMIT句が使える状況で速度も問題ないのであれば、SQLで件数を制限することをおすすめします。というのも、前項にも書いた通り、INDEXでORDER BYとLIMITを使ったクエリをはや抜けできたり、アプリケーションとデータベース間の通信量の削減や、オブジェクトのマッピング処理の回数を減らすことができるからです。

どうしてもアプリケーションで条件を使って件数を制御しなければいけないこともあるので、常にSQLで制御することは難しいかもしれませんが、利用できる場合は積極的に採用したいところです。

MySQLはDELETE構文にLIMITが利用できる

MySQLはDELETE構文に対してLIMIT句が利用できます。これにより、削除する件数を制限することができます。

実際に動作を確認してみましょう。

mysql> SELECT * FROM t1;
+------+-------+
| id   | point |
+------+-------+
|    1 |     5 |
|    2 |     5 |
|    3 |     5 |
|    4 |     5 |
|    5 |     5 |
+------+-------+
5 rows in set (0.01 sec)


mysql> DELETE FROM t1 WHERE point = 5 ORDER BY id LIMIT 3;
Query OK, 3 rows affected (0.01 sec)

mysql> SELECT * FROM t1;
+------+-------+
| id   | point |
+------+-------+
|    4 |     5 |
|    5 |     5 |
+------+-------+
2 rows in set (0.00 sec)

point = 5のデータに対して、idが小さいものから3件だけデータが削除されていることが確認できました。このようにDELETE文でLIMIT句を使うことで、削除する件数を制限することができます。

上記のクエリはORDER BY idなしでもシンタックスエラーにはならないのですが、削除されるデータが不定になるため、binlog_formatがSTATEMENTの環境だとレプリケーションアンセーフになるので、実行には十分な注意が必要です。

この機能を用いることで、たとえば既存データに対して削除バッチを追加したいが、過去分の削除をする際に対象データが多く、広い範囲でロックを取ってしまうといったときなどに、LIMIT句で細かくトランザクションを区切って、ロックの取得時間や範囲を小さくすることが可能になるかもしれません。

WINDOW関数を利用してLIMIT句を再現してみる

さて、話は変わりまして、みなさんはOracleデータベースを利用したことはありますでしょうか？ Oracleデータベースでは、今はFETCH句で取得する件数を制限できるそうですが、バージョン11gまではWINDOW関数のrow_number関数を利用してLIMIT OFFSETを再現していました。

MySQLは8.0からWINDOW関数が利用できるようになり、同じようにWINDOW関数でもLIMIT OFFSETを再現することができます。

 mysql> SELECT * FROM (
    ->      SELECT row_number() over (order by id asc) as row_num,
    ->             name
    ->      FROM dummy
    ->   ) tmp
    -> WHERE tmp.row_num > 500 AND row_num <= 600; 
〈省略〉
|     597 | UyWaJGm1h9    |
|     598 | JK7m8XZtSk    |
|     599 | JZeuNUJKMf    |
|     600 | K3Ab5Z5I2Z    |
+---------+---------------+
100 rows in set (0.08 sec)

mysql> SELECT id, name  FROM dummy ORDER BY id LIMIT 100 OFFSET 500;
〈省略〉
| 597 | UyWaJGm1h9    |
| 598 | JK7m8XZtSk    |
| 599 | JZeuNUJKMf    |
| 600 | K3Ab5Z5I2Z    |
+-----+---------------+
100 rows in set (0.01 sec)

同じように件数を制限して取得することができました。ただし、WINDOW関数を利用した場合、派生テーブルを一度作成してから件数を絞りに行くため、LIMIT OFFSETを使用したときよりも遅くなってしまいます。実際にはこのようなクエリは作らずに、素直にLIMIT OFFSETを利用しましょう。

まとめ

今回はLIMIT句による結果セットの件数制御に関して説明しました。

LIMIT OFFSETの挙動であったり、LIMITのはや抜け等、知っておかないとアプリケーションのパフォーマンスを左右する部分もあるかと思います。このあたりの挙動について詳細については公式ドキュメントの8.2.1.19 LIMIT Query Optimizationにも説明があるので、こちらも参照してください。