MySQLのORDER BYとLIMIT、OFFSETで知っておくべき注意点

概要

MySQLでORDER BYとLIMIT、OFFSETを使うと、クエリ結果が期待通りに動作しないことがあります。本記事では、MySQLが提供するサンプルデータベースのemployeesテーブルを使って、こうした問題を回避する方法を解説します。

データの表示

社員の雇用日で検索し、性別でソートするSQLを実行すると以下のような結果が表示されます。

mysql> select * from employees where hire_date = '1985-01-01' order by gender;
+--------+------------+-------------+--------------+--------+------------+
| emp_no | birth_date | first_name  | last_name    | gender | hire_date  |
+--------+------------+-------------+--------------+--------+------------+
| 110022 | 1956-09-12 | Margareta   | Markovitch   | M      | 1985-01-01 |
| 110085 | 1959-10-28 | Ebru        | Alpin        | M      | 1985-01-01 |
| 110511 | 1957-07-08 | DeForest    | Hagimont     | M      | 1985-01-01 |
| 111035 | 1962-02-24 | Przemyslawa | Kaelbling    | M      | 1985-01-01 |
| 111400 | 1959-11-09 | Arie        | Staelin      | M      | 1985-01-01 |
| 110183 | 1953-06-24 | Shirish     | Ossenbruggen | F      | 1985-01-01 |
| 110303 | 1956-06-08 | Krassimir   | Wegerle      | F      | 1985-01-01 |
| 110725 | 1961-03-14 | Peternela   | Onuegbe      | F      | 1985-01-01 |
| 111692 | 1954-10-05 | Tonny       | Butterworth  | F      | 1985-01-01 |
+--------+------------+-------------+--------------+--------+------------+
9 rows in set (0.12 sec)

画面でページネーションをする想定で上記のSQLにLIMITを3レコード単位付けてみましょう。

1ページ目

mysql> select * from employees where hire_date = '1985-01-01' order by gender limit 0, 3;
+--------+------------+------------+------------+--------+------------+
| emp_no | birth_date | first_name | last_name  | gender | hire_date  |
+--------+------------+------------+------------+--------+------------+
| 110022 | 1956-09-12 | Margareta  | Markovitch | M      | 1985-01-01 |
| 110085 | 1959-10-28 | Ebru       | Alpin      | M      | 1985-01-01 |
| 110511 | 1957-07-08 | DeForest   | Hagimont   | M      | 1985-01-01 |
+--------+------------+------------+------------+--------+------------+
3 rows in set (0.13 sec)

2ページ目

mysql> select * from employees where hire_date = '1985-01-01' order by gender limit 3, 3;
+--------+------------+-------------+-------------+--------+------------+
| emp_no | birth_date | first_name  | last_name   | gender | hire_date  |
+--------+------------+-------------+-------------+--------+------------+
| 111400 | 1959-11-09 | Arie        | Staelin     | M      | 1985-01-01 |
| 111035 | 1962-02-24 | Przemyslawa | Kaelbling   | M      | 1985-01-01 |
| 111692 | 1954-10-05 | Tonny       | Butterworth | F      | 1985-01-01 |
+--------+------------+-------------+-------------+--------+------------+
3 rows in set (0.11 sec)

3ページ目

mysql> select * from employees where hire_date = '1985-01-01' order by gender limit 6, 3;
+--------+------------+------------+-------------+--------+------------+
| emp_no | birth_date | first_name | last_name   | gender | hire_date  |
+--------+------------+------------+-------------+--------+------------+
| 110303 | 1956-06-08 | Krassimir  | Wegerle     | F      | 1985-01-01 |
| 110725 | 1961-03-14 | Peternela  | Onuegbe     | F      | 1985-01-01 |
| 111692 | 1954-10-05 | Tonny      | Butterworth | F      | 1985-01-01 |
+--------+------------+------------+-------------+--------+------------+
3 rows in set (0.10 sec)

4ページ目

mysql> select * from employees where hire_date = '1985-01-01' order by gender limit 9, 3;
Empty set (0.13 sec)

問題の発生

上記の結果を見るとページネーションするとTonny Butterworthが2回出て、Arie Staelinが出力されないことが分かります。

なぜ期待通りの結果が得られなかったのでしょうか。

https://bugs.mysql.com/bug.php?id=69732

上記のリンクの内容を見るとORDER BY句で使用しているgender列には重複する値が含まれており、このためOFFSETを使用した際、MySQLが正確な順序を保証できない場合があります。この結果、同じデータが複数回出現したり、あるデータが出現しないという予期しない動作が発生します。

上記のリンクページにも書いているように、以下のようにユニークでない文字列xとRAND()でソートするのと同様とのことです。

SELECT * FROM employees ORDER BY 'x';

SELECT * FROM employees ORDER BY RAND();

他のパターン

例のgenderはnullが許可できないENUMタイプですが、実際サイト等の設計する際はnullを許可している場合も同じ現象がおきます。重複した値か、nullのレコード数が多いほど同じ現象が起きやすいと思います。

解決方法

仕様的に仕方なくユニークでないカラムでソートする場合、解決策はユニークなカラムを追加してソート順序を確定させることです。例えば、genderでソートした後、emp_no（一意のカラム）を追加すると、結果の順序が保証されます。

mysql> select * from employees where hire_date = '1985-01-01' order by gender, emp_no limit 0, 3;
+--------+------------+------------+------------+--------+------------+
| emp_no | birth_date | first_name | last_name  | gender | hire_date  |
+--------+------------+------------+------------+--------+------------+
| 110022 | 1956-09-12 | Margareta  | Markovitch | M      | 1985-01-01 |
| 110085 | 1959-10-28 | Ebru       | Alpin      | M      | 1985-01-01 |
| 110511 | 1957-07-08 | DeForest   | Hagimont   | M      | 1985-01-01 |
+--------+------------+------------+------------+--------+------------+
3 rows in set (0.12 sec)

mysql> select * from employees where hire_date = '1985-01-01' order by gender, emp_no limit 3, 3;
+--------+------------+-------------+--------------+--------+------------+
| emp_no | birth_date | first_name  | last_name    | gender | hire_date  |
+--------+------------+-------------+--------------+--------+------------+
| 111035 | 1962-02-24 | Przemyslawa | Kaelbling    | M      | 1985-01-01 |
| 111400 | 1959-11-09 | Arie        | Staelin      | M      | 1985-01-01 |
| 110183 | 1953-06-24 | Shirish     | Ossenbruggen | F      | 1985-01-01 |
+--------+------------+-------------+--------------+--------+------------+
3 rows in set (0.13 sec)

mysql> select * from employees where hire_date = '1985-01-01' order by gender, emp_no limit 6, 3;
+--------+------------+------------+-------------+--------+------------+
| emp_no | birth_date | first_name | last_name   | gender | hire_date  |
+--------+------------+------------+-------------+--------+------------+
| 110303 | 1956-06-08 | Krassimir  | Wegerle     | F      | 1985-01-01 |
| 110725 | 1961-03-14 | Peternela  | Onuegbe     | F      | 1985-01-01 |
| 111692 | 1954-10-05 | Tonny      | Butterworth | F      | 1985-01-01 |
+--------+------------+------------+-------------+--------+------------+
3 rows in set (0.12 sec)

まとめ

仕様を実装に移すとき、上記のように使用する製品の特殊な動きや制限を考慮する必要があると思います。