MySQLで直感的じゃない動きをするRAND()とSYSDATE()について

端的にいうと

SELECTのWHEREの条件の「右辺」に、RAND()やSYSDATE()のような非決定性関数を使うと、想定外のことが起こる。
- 戻ってくる行数が想定と異なる。
- Indexが効かなくなる。（テーブルフルスキャン走る）

どっちもなかなかのインパクトです。

追記：2019/05/30

PostgreSQLもMySQLと同じ挙動でした。（9.6と10系で確認）
Oracleは、MySQLやPostgreSQLとは異なり、想定通りの件数が返ってきますし、Indexも効いてました。（11g R2で確認）

追記：2019/05/31

「PostgreSQLもMySQLと同じ」なのは、「ランダム関数利用時の挙動」です。日付系は調査しきれてません。
公式ドキュメントを読む限り、clock_timestamp()やtimeofday()を意図的に使わない限り、１つのSQLの中で違う日時を取り直すことはなさそうです。

戻ってくる行数が想定と異なる件

RAND関数を使って、テーブルからサンプル１件取得したい、ってケースありますよね。

テーブルt1に、AUTO INCREMENTでPRIMARY KEYなID列「itemid」に欠番なしの16777216件のデータが入っているとします。
うち、先頭10件からランダムに1件取得したいと思います。

このテーブルに以下のSQLを実行すると、何件の値が返ってくるでしょうか？

SELECT * FROM t1 WHERE itemid = floor(1 + rand() *(11-1));

MySQLのrand関数は、1以下のランダムな値を返す関数です。

0 <= v < 1.0 の範囲内で、ランダムな浮動小数点値 v を返します。
（中略）
i <= R < j の範囲内でランダムな整数 R を取得するには、式 FLOOR(i + RAND() * (j - i)) を使用します。たとえば、7 <= R < 12 の範囲内でランダムな整数を取得するには、次のようなステートメントを使用します。
SELECT FLOOR(7 + (RAND() * 5));
MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.6.2 数学関数

「1 <= R <11」、つまり1～10の値が返ってきます。

なので、こちらの期待としては、「プライマリキー値が1～10の10件の中から1件を取ってくる」です。

ですが、そうなりません。返ってくる件数は「不定」です！

何が起こっているかというと、テーブルt1の10件に対して、1行ずつRANDで生成し直した値を評価しています。

なので、t1.itemid=1の行を評価する時に実行したRAND()の結果を用いた「FLOOR(i + RAND() * (j - i))」がたまたま「1」だったときだけTrueとなります。
よって、Empty Setが返ってきたり、２件以上返ってきたり。10件返ってくる確率は、、、0％では無いです。

実はドキュメントにほんのり書いてありました。

WHERE 句内の RAND() は、WHERE が実行されるたびに再評価されます。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.6.2 数学関数

この日本語だけだと、微妙にわかりにくいのですが、その直後に、遅くなるので有名な「ORDER BY RAND()」の件が書いてあるので、そこで気づける人は気づくはずですね。

Indexが効かない件

ORDER BY RAND()が「全レコードにRANDを行った結果に対してソートをするから、件数増えると遅い」って話は有名ですが、先程のSQL

SELECT * FROM t1 WHERE itemid = floor(1 + rand() *(11-1));

もテーブルに入ってる件数が増えると重くなります。上記の「戻り件数が不定な理由」として説明したとおり、「テーブルt1の全レコードに対して、1行ずつRANDで生成し直した値を評価」しているからなんですが、この際、Indexも使ってません。

実行計画を取ってみます。

EXPLAIN SELECT * FROM t1 WHERE itemid = floor(1 + rand() *(11-1));
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 15160086 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

プライマリキーであるitemidに対してWHERE検索を行っていますが、possible_keysもkeyも「NULL」です。Extraも「Using Where」です。rowsもフルスキャン相当の件数が表示されています。

MySQLの構造に即して説明すると、
「 floor(1 + rand() *(11-1))」の結果をもとにストレージエンジンに投げて絞った結果を取得しているのではなく、
ストレージエンジンからは全件取得して、その結果１行１行に対して「floor(1 + rand() *(11-1))」を実行し、照合しています。

似たような事例に嵌りそうなユースケース

「SYSDATE関数を使って、未来日時で登録されているレコードを取りたい」ってことありますよね。

テーブルt1には、create_atというtimestamp型の列があり、セカンダリインデックスが設定されているものとします。

なんか事情があって、未来日付でもデータが登録されいる環境で、その未来日付レコードを取ってくるのに、以下のようなSQLを書きますよね。

SELECT * FROM t1 WHERE created_at > sysdate();

これも、先程のRAND()と同じ事象にぶつかりますので

テーブル件数が大きいと、評価のタイミングが最初のレコードと最後のレコードでズレて、正確な件数にならない。
Indexが効かず、フルスキャンになる。

となります。

公式ドキュメントのココにも書いてあります。

SYSDATE() に非決定的な特性があるということは、それを参照する式を評価する際にインデックスを使用できないことも意味します。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.7 日付および時間関数

回避方法

JOINを使う
MySQL変数「@」を使う
SQLを組み立てるアプリケーション側の言語でRANDやSYSDATE相当の値を生成する

1.JOINを使う

RAND関数の例ですが、

SELECT * FROM t1 WHERE itemid = floor(1 + rand() *(11-1));

と

SELECT * FROM t1  WHERE itemid = (SELECT floor(1 + rand() *(11-1));

は結果、実行計画ともに同じです。

しかし、JOINを使って

SELECT * FROM t1 JOIN (SELECT floor(1 + rand() *(11-1)) as itemid) t2 ON t1.itemid = t2.itemid;

と書くと、結果は異なります。実行計画は、

mysql> EXPLAIN SELECT * FROM t1 JOIN (SELECT floor(1 + rand() *(11-1)) as itemid) t2 ON t1.itemid = t2.itemid;
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+----------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref   | rows | filtered | Extra          |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+----------------+
|  1 | PRIMARY     | <derived2> | NULL       | system | NULL          | NULL    | NULL    | NULL  |    1 |   100.00 | NULL           |
|  1 | PRIMARY     | t1         | NULL       | const  | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL           |
|  2 | DERIVED     | NULL       | NULL       | NULL   | NULL          | NULL    | NULL    | NULL  | NULL |     NULL | No tables used |
+----+-------------+------------+------------+--------+---------------+---------+---------+-------+------+----------+----------------+
3 rows in set, 1 warning (0.00 sec)

となり、当初期待しているように、最初に「floor(1 + rand() *(11-1))」した結果をderived2が生成され、テーブルt1とJOINしています。結果、プライマリキーのインデックススキャンが聞いていて、スキャンしたROWSが「1」となっています。

2.MySQL変数「@」を使う

MySQLでは「SET @変数名」を使うと、変数として後続の処理の中でその変数の値を利用することができます。
よって、

SET @1 = floor(1 + rand() *(11-1));
SELECT * FROM t1  WHERE itemid = @1;

と書き直します。

実行計画を見ると、

mysql> EXPLAIN SELECT * FROM t1  WHERE itemid = @1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

のように、Indexが効いています。

これの亜種で、「SYSDATE関数の代わりにNOW関数を使う」という方法があります。

MySQLではSYSDATE関数とNOW関数は微妙に違います。そのへんの説明は
nippondanji.blogspot.com
が詳しいですね。

平たく言うと、「NOW関数は内部で@変数に現在時刻を保存して使いまわしているのと同等」って話です。

3.SQLを組み立てるアプリケーション側の言語でRANDやSYSDATE相当の値を生成する

MySQLの世界を離れたところで検索条件の数値を作って、「普通のSQL」として実行してあげれば良いので、これは当然ですね。説明不要でしょう。

普通のSQLとは（哲学

他に同じような影響がある関数はないか

全部調べてないのでわかりません。しかし、ここまでに引用してきた公式ドキュメントの文章の中にヒントがあります。

それは、この一文です。

SYSDATE() に非決定的な特性があるということは、それを参照する式を評価する際にインデックスを使用できないことも意味します。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.7 日付および時間関数

この「非決定性」関数というのは、実行するたびに結果が変わる関数のことで、ステートメントベースのレプリケーションにおける破滅の呪文としての文脈＝マスターとレプリカにおいてSQLの実行結果が変わってしまい、データが一致しなくなる、というで語られることが多いです

しかし、この「非決定性」は、「WHEREによる条件検索の評価の都度、関数が実行される」「インデックスを使えない」という挙動にもなります。
実行の度に結果が変わる関数は、「UUID」くらいしかすぐに思いつかないのですが、こうした関数を使用する場合には、むやみにWHERE条件に組み込まないように気をつけましょう。

じつはこの辺の話、日本語版のMySQL 5.6のドキュメントにはないけど、英語版の5.6、5.7、8.0のドキュメントには存在する「MySQL :: MySQL 5.6 Reference Manual :: 8.2.1.17 Function Call Optimization」というページに解説があります。