[MySQL]並び順（ソート）でNULLを後ろして昇順で並び替える方法

[MySQL]並び順（ソート）でNULLを後ろして昇順で並び替える方法
2022年06月17日2023年10月10日 PR
データベース SQL

MySQLでレコードを昇順に並び替えようとするとnullのレコードが存在すると先頭に来てしまいます。

実装的にこれで問題ないこともありますが、昇順で且つnullが後ろに来てほしい場合もあります。

mysql> SELECT
    ->     *
    -> FROM
    ->     products
    -> ORDER BY expiration_at ASC;
+----+---------------------+
| id | expiration_at       |
+----+---------------------+
|  6 | NULL                |
|  8 | 2021-02-03 06:53:28 |
|  3 | 2021-02-03 11:31:17 |
|  2 | 2021-02-05 11:57:46 |
|  9 | 2021-02-06 07:27:59 |
|  1 | 2021-02-09 00:39:14 |
|  7 | 2021-02-09 16:59:48 |
|  4 | 2021-02-09 21:43:08 |
| 10 | 2021-02-10 16:39:31 |
+----+---------------------+
9 rows in set (0.00 sec)

例えば期限付きポイントを管理したい場合、よくある仕様は下記が多いと思います。

  • ポイントの種類は有効期限がある有効期限ポイント、有効期限が存在しない永久ポイントの2種類を設ける。
  • 有効期限が切れたポイントは失効扱うとする。
  • ポイント有効期限が近いものから消費し、永久ポイントは最後に消費する。

このような条件を達成するには、nullのレコードは後に来てほしいものです。

ではどのようにするのか、下記のテーブル構成で紹介したいと思います。

CREATE TABLE `products` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `expiration_at` datetime DEFAULT NULL COMMENT '有効期限',
  PRIMARY KEY (`id`)
) ;

昇順でnullを後ろに持ってくる

やり方は2種類ありますのでそれぞれ紹介します。

IS NULL ASCを指定する

Selectする際のOrder By句に「IS NULL ASC」を付けてやる方法になります。

mysql> SELECT
    ->     *
    -> FROM
    ->     products
    -> ORDER BY expiration_at IS NULL ASC , expiration_at ASC;
+----+---------------------+
| id | expiration_at       |
+----+---------------------+
|  8 | 2021-02-03 06:53:28 |
|  3 | 2021-02-03 11:31:17 |
|  2 | 2021-02-05 11:57:46 |
|  9 | 2021-02-06 07:27:59 |
|  1 | 2021-02-09 00:39:14 |
|  7 | 2021-02-09 16:59:48 |
|  4 | 2021-02-09 21:43:08 |
| 10 | 2021-02-10 16:39:31 |
|  6 | NULL                |
+----+---------------------+

結果を見ると日付が昇順で最後にnullが来ているのが分かると思います。

ASCの部分をDESCにすると降順にできますので自由に操作することができます。

ただ一つ問題があります。それはINDEXを貼っても遅いです。INDEXの詳細は後述に書いています。

レコード数が限られている場合は採用しても問題と思いますが、レコード数が数十万以上ある場合は、INDEXを貼っても遅いので注意してください。

MAX値「9999-12-31 23:59:59」を使用する

もう一個のやり方が、Datetime型のMAX値である「9999-12-31 23:59:59」を使用してnull扱いとする方法です。

こちらのやり方は通常のORDER BY するだけで簡単に並び替えができます。

mysql> SELECT
    ->     *
    -> FROM
    ->     products
    -> ORDER BY expiration_at ASC;
+----+---------------------+
| id | expiration_at       |
+----+---------------------+
|  8 | 2021-02-03 06:53:28 |
|  3 | 2021-02-03 11:31:17 |
|  2 | 2021-02-05 11:57:46 |
|  9 | 2021-02-06 07:27:59 |
|  1 | 2021-02-09 00:39:14 |
|  7 | 2021-02-09 16:59:48 |
|  4 | 2021-02-09 21:43:08 |
| 10 | 2021-02-10 16:39:31 |
|  6 | 9999-12-31 23:59:59 |
+----+---------------------+

MAX値を入れるのが気にいらないという方もいるかもしれませんが、シンプルでINDEXをしっかり効いてくれるのでおすすめです。

どちらのSQLが早いか試してみる

先ほど紹介した二つのやり方の取得時間を調べてみました。

INDEXが効いているかどうかチェックするにはレコードを増やす必要があります。

120万件弱のデータを投入しました。

mysql> SELECT
    ->     COUNT(*)
    -> FROM
    ->     products;
+----------+
| COUNT(*) |
+----------+
|  1179648 |
+----------+

次ににINDEXを作成します。

ALTER TABLE `products` 
ADD INDEX `idx_expiration_at` (`expiration_at`) ;

IS NULL ASCを使用した場合の取得時間

IS NULL ASCを使用した取得時間を調べてみます。

mysql>  SELECT
    ->     *
    -> FROM
    ->     products
    -> ORDER BY expiration_at IS NULL ASC , expiration_at ASC
    -> LIMIT 10;
+---------+---------------------+
| id      | expiration_at       |
+---------+---------------------+
|  942385 | 2021-02-01 00:00:00 |
| 1327627 | 2021-02-01 00:00:00 |
| 1349719 | 2021-02-01 00:00:00 |
|  465886 | 2021-02-01 00:00:01 |
|  468138 | 2021-02-01 00:00:01 |
|  558431 | 2021-02-01 00:00:01 |
|  701103 | 2021-02-01 00:00:01 |
| 1177319 | 2021-02-01 00:00:01 |
|  163888 | 2021-02-01 00:00:02 |
|  882634 | 2021-02-01 00:00:04 |
+---------+---------------------+
10 rows in set (1.51 sec)

～2回目～
10 rows in set (1.53 sec)

～3回目～
10 rows in set (1.53 sec)

結果平均1.5秒ほどかかりました。

実行計画を確認するとINDEXは効いていますが「Using filesort」が発生していますね。遅い原因は「Using filesort」になります。

mysql> EXPLAIN SELECT
    ->     *
    -> FROM
    ->     products
    -> ORDER BY expiration_at IS NULL ASC , expiration_at ASC
    -> LIMIT 10;
+----+-------------+----------+------------+-------+---------------+-------------------+---------+------+---------+----------+-----------------------------+
| id | select_type | table    | partitions | type  | possible_keys | key               | key_len | ref  | rows    | filtered | Extra                       |
+----+-------------+----------+------------+-------+---------------+-------------------+---------+------+---------+----------+-----------------------------+
|  1 | SIMPLE      | products | NULL       | index | NULL          | idx_expiration_at | 6       | NULL | 1177930 |   100.00 | Using index; Using filesort |
+----+-------------+----------+------------+-------+---------------+-------------------+---------+------+---------+----------+-----------------------------+
Using filesortはソート対象のレコード数が少なければ問題にならないですが、行数が多い場合はパフォーマンスに影響を与えます。

IS NULL ASCを使ったやり方は、レコード数が少なければ問題にならないが、件数が多くなるとパフォーマンスに影響がでてきます。

MAX値「9999-12-31 23:59:59」を使用した取得時間

つぎに2つ目に紹介したMAX値「9999-12-31 23:59:59」を使用した方法で試します。

mysql> SELECT
    ->     *
    -> FROM
    ->     products
    -> ORDER BY expiration_at ASC
    -> LIMIT 10;
+---------+---------------------+
| id      | expiration_at       |
+---------+---------------------+
|  942385 | 2021-02-01 00:00:00 |
| 1327627 | 2021-02-01 00:00:00 |
| 1349719 | 2021-02-01 00:00:00 |
|  465886 | 2021-02-01 00:00:01 |
|  468138 | 2021-02-01 00:00:01 |
|  558431 | 2021-02-01 00:00:01 |
|  701103 | 2021-02-01 00:00:01 |
| 1177319 | 2021-02-01 00:00:01 |
|  163888 | 2021-02-01 00:00:02 |
|  882634 | 2021-02-01 00:00:04 |
+---------+---------------------+
10 rows in set (0.00 sec)

～2回目～
10 rows in set (0.00 sec)

～3回目～
10 rows in set (0.00 sec)

 結果、0秒で返ってきました。

実行計画を確認するとしっかりINDEXが効いて、1つ目で発生していた「Using filesort」も発生していませんね。

mysql> EXPLAIN SELECT
    ->     *
    -> FROM
    ->     products
    -> ORDER BY expiration_at ASC
    -> LIMIT 10;
+----+-------------+----------+------------+-------+---------------+-------------------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key               | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+-------------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | products | NULL       | index | NULL          | idx_expiration_at | 6       | NULL |   10 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+-------------------+---------+------+------+----------+-------------+

100万件のデータでも0秒で返ってくるので、データ量が多いと想定される場合はMAX値を使ったやり方がおすすめです。

最後に

今回NULLを後ろして昇順で並び替える方法を紹介しました。

私が設計する場合は、MAX値「9999-12-31 23:59:59」を使ったやり方をいつも採用しています。 理由はとにかく早いからです。

ぜひ参考にしてみてください。

[改訂第4版]SQLポケットリファレンス
SQL分法が全て網羅されています
MySQL徹底入門 第4版 MySQL 8.0対応
Ver8.0での新機能を把握におすすめ
SHARE

関連記事

コメント

コメントを残す

お名前（任意）
コメント：新規