ユニークな番号を採番したいケースがありますよね。SQLでやれば複数のサーバ間でユニークにできる。UUIDは重ならないとかいう話もあるが、実際システムでユニークであることを保証しようと思えば乱数等に依存するわけにはいかない。
というわけで、ここではSQLを使って採番することを考える。
CREATE TABLE tbl (num integer NOT NULL UNIQUE PRIMARY KEY)
で、どんなSQLを発行すれば次に取りたい値を求められるのか？
SELECT (num+1) FROM tbl
WHERE (num+1) NOT IN (SELECT num FROM tbl)
これは十分直感的な書き方。num+1の値であって既存のnumの中にはない、というもの。どっかでググって見つけてきたクエリだ。ただこれはMySQLの場合dependent subqueryになる。
mysql> EXPLAIN SELECT (num+1) FROM tbl WHERE (num+1) NOT IN (SELECT num FROM tbl);
+----+--------------------+-------+-----------------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+-----------------+---------------+---------+---------+------+------+--------------------------+
| 1 | PRIMARY | tbl | index | NULL | PRIMARY | 4 | NULL | 1 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | tbl | unique_subquery | PRIMARY,num | PRIMARY | 4 | func | 1 | Using index; Using where |
+----+--------------------+-------+-----------------+---------------+---------+---------+------+------+--------------------------+
2 rows in set (0.00 sec)
この「dependent subquery」というやつはMySQLにおいては忌み嫌われている有名な「遅くなる」クエリで、よく「外側から評価される」と称される。外側が評価されて、結果それぞれに対して内側のサブクエリが毎回実行されるという意味で直感的な動きとは異なってしまい、効率良さそうに書いたつもりが実は良くない、という問題をはらんでいるらしい。遅くなってしまうクエリにしても、内外を逆転して書ける種類のものであれば、良くなるんだろう。
↑で上げたクエリの場合は外側と内側が同じ分量なのでまあ、そんなに悪化するとは思えない。実際いろいろ試してみたが、そんなに遅くはならない。
JOINを使ってみる場合はこんなクエリになる。
SELECT (t2.num+1) FROM tbl t1
RIGHT OUTER JOIN tbl t2
ON t1.num=t2.num+1
WHERE t1.num IS NULL
これはsimple queryが2発。見るからに問題は起きなさそう。
mysql> EXPLAIN SELECT (t2.num+1) FROM tbl t1
-> RIGHT OUTER JOIN tbl t2
-> ON t1.num=t2.num+1
-> WHERE t1.num IS NULL;
+----+-------------+-------+--------+---------------+---------+---------+------+------+--------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+------+------+--------------------------------------+
| 1 | SIMPLE | t2 | index | NULL | PRIMARY | 4 | NULL | 1 | Using index |
| 1 | SIMPLE | t1 | eq_ref | PRIMARY,num | PRIMARY | 4 | func | 1 | Using where; Not exists; Using index |
+----+-------------+-------+--------+---------------+---------+---------+------+------+--------------------------------------+
2 rows in set (0.00 sec)
たとえばSqliteはRIGHT OUTER JOINが使えないのでLEFT OUTER JOINを使うと、、、
SELECT (t1.num+1) FROM tbl t1
LEFT OUTER JOIN tbl t2
ON t1.num+1=t2.num
WHERE t2.num IS NULL
これもsimpleが2発。
mysql> EXPLAIN SELECT (t1.num+1) FROM tbl t1
-> LEFT OUTER JOIN tbl t2
-> ON t1.num+1=t2.num
-> WHERE t2.num IS NULL;
+----+-------------+-------+--------+---------------+---------+---------+------+------+--------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+------+------+--------------------------------------+
| 1 | SIMPLE | t1 | index | NULL | PRIMARY | 4 | NULL | 1 | Using index |
| 1 | SIMPLE | t2 | eq_ref | PRIMARY,num | PRIMARY | 4 | func | 1 | Using where; Not exists; Using index |
+----+-------------+-------+--------+---------------+---------+---------+------+------+--------------------------------------+
2 rows in set (0.00 sec)
まあでも実際、やってみるとクエリの時間に大差ないんですよね。↑の場合はjoinよりもdependent subqueryのクエリのほうがむしろ(若干)速かったりするしね。
ああ、↑で書いたクエリはテーブルがカラの状態では何も返ってこない。最初に初期値(0とか)を入れて番兵みたいにしておくとか、カラの結果だったら適当に選ぶとかする必要がある。
