MySQLのLIMITクエリの最適化

概要

MySQLのLIMITを使ったクエリでは、ORDER BYの結果が変わるというものがある。
https://dev.mysql.com/doc/refman/8.0/ja/limit-optimization.html

それについて手元で検証した。

使用する環境

MySQL8.0のDocker。ただし、MySQL5.7でも同様の事象が確認できると思われる。

FROM --platform=linux/x86_64 mysql:8-debian

ENV LANG ja_JP.UTF-8
ENV LANGUAGE ja_JP:ja
ENV LC_ALL ja_JP.UTF-8
ENV MYSQL_ALLOW_EMPTY_PASSWORD=yes

RUN useradd mysqld
COPY mysqld_charset.cnf /etc/mysql/conf.d/mysqld_charset.cnf
ADD world-db.tar.gz /docker-entrypoint-initdb.d/

CMD ["mysqld"]

テストデータ

下記のようなテストデータを作成した。

mysql> select * from item order by created_at limit 20;
+----+------+---------------------+
| id | name | created_at          |
+----+------+---------------------+
| 68 | 0    | 2023-12-20 18:00:00 |
| 64 | 0    | 2023-12-20 18:00:00 |
| 65 | 0    | 2023-12-20 18:00:00 |
| 66 | 0    | 2023-12-20 18:00:00 |
| 67 | 0    | 2023-12-20 18:00:00 |
| 60 | 0    | 2023-12-20 19:00:00 |
| 63 | 0    | 2023-12-20 19:00:00 |
| 62 | 0    | 2023-12-20 19:00:00 |
| 61 | 0    | 2023-12-20 19:00:00 |
| 59 | 0    | 2023-12-20 19:00:00 |
| 52 | 0    | 2023-12-20 20:00:00 |
| 46 | 0    | 2023-12-20 20:00:00 |
| 44 | 0    | 2023-12-20 20:00:00 |
| 47 | 0    | 2023-12-20 20:00:00 |
| 53 | 0    | 2023-12-20 20:00:00 |
| 43 | 0    | 2023-12-20 20:00:00 |
| 45 | 0    | 2023-12-20 20:00:00 |
| 51 | 0    | 2023-12-20 20:00:00 |
| 49 | 0    | 2023-12-20 20:00:00 |
| 33 | 0    | 2023-12-20 20:00:00 |
+----+------+---------------------+
20 rows in set (0.01 sec)

検証

まず、LIMIT句を付与せずにcreated_atでORDER BYをする

mysql> select * from item order by created_at;
+----+------+---------------------+
| id | name | created_at          |
+----+------+---------------------+
| 68 | 0    | 2023-12-20 18:00:00 |
| 67 | 0    | 2023-12-20 18:00:00 |
| 66 | 0    | 2023-12-20 18:00:00 |
| 65 | 0    | 2023-12-20 18:00:00 |
| 64 | 0    | 2023-12-20 18:00:00 |

そのあとにLIMIT句を付与して実行すると結果が変わるのがわかる。

mysql> select * from item order by created_at limit 5;
+----+------+---------------------+
| id | name | created_at          |
+----+------+---------------------+
| 67 | 0    | 2023-12-20 18:00:00 |
| 66 | 0    | 2023-12-20 18:00:00 |
| 64 | 0    | 2023-12-20 18:00:00 |
| 68 | 0    | 2023-12-20 18:00:00 |
| 65 | 0    | 2023-12-20 18:00:00 |
+----+------+---------------------+
5 rows in set (0.01 sec)

created_atがユニークでない場合、結果が不定となってしまうことがわかる。
なので、純粋にidでORDER BYをかければOK。

mysql> select * from item order by created_at, id limit 5;
+----+------+---------------------+
| id | name | created_at          |
+----+------+---------------------+
| 64 | 0    | 2023-12-20 18:00:00 |
| 65 | 0    | 2023-12-20 18:00:00 |
| 66 | 0    | 2023-12-20 18:00:00 |
| 67 | 0    | 2023-12-20 18:00:00 |
| 68 | 0    | 2023-12-20 18:00:00 |
+----+------+---------------------+
5 rows in set (0.01 sec)