MySQL で ORDER BY 狙いのインデックスがある列で NULL を下に持ってくる

MySQL で ORDER BY で NULL を下に持ってくるには下記の記事のような SQL で出来ますが、

※ mysql order by null でググって出てきた記事を羅列しただけ

例えば、次のようなそこそこの行数のテーブルで ORDER BY 狙いのインデックスがある場合・・・

テーブル作成

drop table if exists t;
create table t (
  id int not null primary key auto_increment,
  val int
);

コンソールから適当に行を突っ込む

$ seq 10 | mysql test -e "load data local infile '/dev/stdin' into table t (id) set val = rand()*1000000"
$ seq 1000000 | mysql test -e "load data local infile '/dev/stdin' into table t (id) set val = null"

ORDER BY 狙いのインデックス

create index idx on t (val, id);

LIMIT を掛ければ ORDER BY 狙いのインデックスによってたかだか 20 行ちょいしか読まれないので高速なはずです。

select * from t order by val asc, id asc limit 20;

+----+------+
| id | val  |
+----+------+
| 11 | NULL |
| 12 | NULL |
| 13 | NULL |
| 14 | NULL |
| 15 | NULL |
| 16 | NULL |
| 17 | NULL |
| 18 | NULL |
| 19 | NULL |
| 20 | NULL |
| 21 | NULL |
| 22 | NULL |
| 23 | NULL |
| 24 | NULL |
| 25 | NULL |
| 26 | NULL |
| 27 | NULL |
| 28 | NULL |
| 29 | NULL |
| 30 | NULL |
+----+------+
20 rows in set (0.00 sec)

が、NULL を下に持ってこようとすると ORDER BY 狙いのインデックスが有効に使われないので遅くなります。

select * from t order by val is null asc, val asc, id asc limit 20;

+----+--------+
| id | val    |
+----+--------+
|  6 | 167023 |
|  9 | 302768 |
|  5 | 387339 |
|  3 | 409595 |
|  7 | 673096 |
|  2 | 708250 |
|  1 | 710551 |
|  8 | 864411 |
| 10 | 920608 |
|  4 | 923225 |
| 11 |   NULL |
| 12 |   NULL |
| 13 |   NULL |
| 14 |   NULL |
| 15 |   NULL |
| 16 |   NULL |
| 17 |   NULL |
| 18 |   NULL |
| 19 |   NULL |
| 20 |   NULL |
+----+--------+
20 rows in set (0.24 sec)

次のように is null と is not null を無理やり２回に分けた方が速いことがあります。

(select * from t where val is null order by id asc limit 20)
union all
(select * from t where val is not null order by val asc, id asc limit 20)
order by val is null asc, val asc, id asc limit 20;

+----+--------+
| id | val    |
+----+--------+
|  6 | 167023 |
|  9 | 302768 |
|  5 | 387339 |
|  3 | 409595 |
|  7 | 673096 |
|  2 | 708250 |
|  1 | 710551 |
|  8 | 864411 |
| 10 | 920608 |
|  4 | 923225 |
| 11 |   NULL |
| 12 |   NULL |
| 13 |   NULL |
| 14 |   NULL |
| 15 |   NULL |
| 16 |   NULL |
| 17 |   NULL |
| 18 |   NULL |
| 19 |   NULL |
| 20 |   NULL |
+----+--------+
20 rows in set (0.00 sec)

explain で違いを見てみると・・・

次のクエリはインデックスを 全行走査 して filesort でソートして先頭の 20 行を返します。

explain select * from t order by val is null asc, val asc, id asc limit 20;

+----+-------------+-------+-------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | Extra                       |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-----------------------------+
|  1 | SIMPLE      | t     | index | NULL          | idx  | 9       | NULL | 998968 | Using index; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+------+--------+-----------------------------+

次のクエリは（そうでは無いように見えるけど多分）次のようになっています。

idx の val is null である範囲を昇順に走査して最初の 20 件
idx の val is not null である範囲を昇順に走査して最初の 20 件（実際には 10 件しかありませんが）
これらの合計の40件でテンポラリテーブルを作ってソートして先頭の 20 件を返す

explain
(select * from t where val is null order by id asc limit 20)
union all
(select * from t where val is not null order by val asc, id asc limit 20)
order by val is null asc, val asc, id asc limit 20;

+------+--------------+------------+-------+---------------+------+---------+-------+--------+---------------------------------+
| id   | select_type  | table      | type  | possible_keys | key  | key_len | ref   | rows   | Extra                           |
+------+--------------+------------+-------+---------------+------+---------+-------+--------+---------------------------------+
|  1   | PRIMARY      | t          | ref   | idx           | idx  | 5       | const | 499484 | Using where; Using index        |
|  2   | UNION        | t          | range | idx           | idx  | 5       | NULL  |     10 | Using where; Using index        |
| NULL | UNION RESULT | <union1,2> | ALL   | NULL          | NULL | NULL    | NULL  |   NULL | Using temporary; Using filesort |
+------+--------------+------------+-------+---------------+------+---------+-------+--------+---------------------------------+

show status を使えば実際に走査されている行数をざっくり見積もれます。

flush status;

select * from t order by val is null asc, val asc, id asc limit 20;

show status like 'handler%';

+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| Handler_commit             | 1       |
| Handler_delete             | 0       |
| Handler_discover           | 0       |
| Handler_external_lock      | 2       |
| Handler_mrr_init           | 0       |
| Handler_prepare            | 0       |
| Handler_read_first         | 1       |
| Handler_read_key           | 1       |
| Handler_read_last          | 0       |
| Handler_read_next          | 0       |
| Handler_read_prev          | 0       |
| Handler_read_rnd           | 0       |
| Handler_read_rnd_next      | 1000001 |
| Handler_rollback           | 0       |
| Handler_savepoint          | 0       |
| Handler_savepoint_rollback | 0       |
| Handler_update             | 0       |
| Handler_write              | 0       |
+----------------------------+---------+

flush status;

(select * from t where val is null order by id asc limit 20)
union all
(select * from t where val is not null order by val asc, id asc limit 20)
order by val is null asc, val asc, id asc limit 20;

show status like 'handler%';

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 4     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 2     |
| Handler_read_last          | 0     |
| Handler_read_next          | 29    |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 20    |
| Handler_read_rnd_next      | 31    |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 30    |
+----------------------------+-------+

後者の方がはるかに走査行が少なくなります（その代わりテンポラリテーブルを作成するコストがあります）。

無理矢理にでも複数の SELECT に分けたほうが早いのは下記の記事と同じですね。

グループ内の上位 N 件を抽出する SQL はストアドでいいんじゃない？ - ngの日記

PostgreSQL なんかだとインデックスの作成時に NULL をどっちに持ってくるかを指定できるようです(NULLS FIRST/NULLS LAST)。

CREATE INDEX