日々の覚書: 3月 2016

2016/03/30

SHOW TABLE STATUSのData_lengthとかIndex_lengthとかData_freeの値をぼんやり考える

InnoDBの場合。MyISAMは全くアテにならなかった（少なくとも5.7.11では）
InnoDBでも所詮統計情報なので完全にアテになる訳じゃないのはお約束。

テスト。

mysql57> CREATE TABLE t1 (val char(250));
Query OK, 0 rows affected (0.02 sec)

mysql57> INSERT INTO t1 SET val= '';
Query OK, 1 row affected (0.00 sec)

mysql57> INSERT INTO t1 SELECT * FROM t1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

..

mysql57> INSERT INTO t1 SELECT * FROM t1;
Query OK, 131072 rows affected (1.67 sec)
Records: 131072  Duplicates: 0  Warnings: 0

ysql57> ANALYZE TABLE t1;
+-------+---------+----------+----------+
| Table | Op      | Msg_type | Msg_text |
+-------+---------+----------+----------+
| d1.t1 | analyze | status   | OK       |
+-------+---------+----------+----------+
1 row in set (0.00 sec)

mysql57> SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 257368
 Avg_row_length: 312
    Data_length: 80330752
Max_data_length: 0
   Index_length: 0
      Data_free: 4194304
 Auto_increment: NULL
    Create_time: 2016-03-30 19:14:45
    Update_time: 2016-03-30 19:24:46
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options: row_format=Dynamic
        Comment:
1 row in set (0.01 sec)

mysql57> SELECT table_rows, sys.format_bytes(data_length) AS data_length, sys.format_bytes(index_length) AS index_length, sys.format_bytes(data_free) AS data_free FROM information_schema.tables WHERE (table_schema, table_name)= ('d1', 't1');
+------------+-------------+--------------+-----------+
| table_rows | data_length | index_length | data_free |
+------------+-------------+--------------+-----------+
|     257368 | 76.61 MiB   | 0 bytes      | 4.00 MiB  |
+------------+-------------+--------------+-----------+
1 row in set (0.01 sec)

SHOW TABLE STATUSだと見にくいのでi_s使って取ってみる。
innodb_autoextend_incrementはシステムテーブルスペース(ibdata1)の自動拡張単位で、.ibdファイルの場合は最大で4MB（らしい）

https://dev.mysql.com/doc/refman/5.6/ja/innodb-parameters.html#sysvar_innodb_autoextend_increment

100行ずつINSERTしながら100万行くらいまで様子を見てみたけど、Data_freeはだいたい4MB～7MBの間を彷徨っている様子。4MBまでは利用可能領域を使って、それを割るとautoextendするっぽい。

行を半分くらい消してみる。

mysql57> DELETE FROM t1 WHERE rand() < 0.5;
Query OK, 130891 rows affected (1.00 sec)

mysql57> ANALYZE TABLE t1;
+-------+---------+----------+----------+
| Table | Op      | Msg_type | Msg_text |
+-------+---------+----------+----------+
| d1.t1 | analyze | status   | OK       |
+-------+---------+----------+----------+
1 row in set (0.00 sec)

mysql57> SELECT table_rows, sys.format_bytes(data_length) AS data_length, sys.format_bytes(index_length) AS index_length, sys.format_bytes(data_free) AS data_free FROM information_schema.tables WHERE (table_schema, table_name)= ('d1', 't1');
+------------+-------------+--------------+-----------+
| table_rows | data_length | index_length | data_free |
+------------+-------------+--------------+-----------+
|     131354 | 76.61 MiB   | 0 bytes      | 4.00 MiB  |
+------------+-------------+--------------+-----------+
1 row in set (0.00 sec)

Data_lengthは変わらず、Data_freeも変わらず。.ibdファイルのサイズももちろん変わらない。

mysql57> INSERT INTO t1 SELECT * FROM t1 WHERE rand() < 0.5;
Query OK, 65675 rows affected (0.77 sec)
Records: 65675  Duplicates: 0  Warnings: 0

mysql57> ANALYZE TABLE t1;
+-------+---------+----------+----------+
| Table | Op      | Msg_type | Msg_text |
+-------+---------+----------+----------+
| d1.t1 | analyze | status   | OK       |
+-------+---------+----------+----------+
1 row in set (0.00 sec)

mysql57> SELECT table_rows, sys.format_bytes(data_length) AS data_length, sys.format_bytes(index_length) AS index_length, sys.format_bytes(data_free) AS data_free FROM information_schema.tables WHERE (table_schema, table_name)= ('d1', 't1');
+------------+-------------+--------------+-----------+
| table_rows | data_length | index_length | data_free |
+------------+-------------+--------------+-----------+
|     193161 | 76.42 MiB   | 0 bytes      | 4.00 MiB  |
+------------+-------------+--------------+-----------+
1 row in set (0.00 sec)

消したぶんより少ない行をINSERTすると、Data_lengthは伸びない(= 空きページが再利用されている）けどData_freeも変わらない。謎い（deleteした時にdata_freeをインクリメントしてた気がするんだけど見付けられない。気のせいだったのか）
パージスレッドのご機嫌なのかなぁと思ってinnodb_fast_shutdown= 0にして再起動してみたけど変わらない。WHEREなしのDELETEでどかんと行くとData_freeに全部計上されるんだけどなぁ。

mysql57> SET GLOBAL innodb_fast_shutdown= 0;
Query OK, 0 rows affected (0.00 sec)

mysql57> SHUTDOWN;
Query OK, 0 rows affected (0.00 sec)

$ mysqld_multi start 57

Index_lengthも同じ様子。

mysql57> ALTER TABLE t1 ADD KEY (val);
Query OK, 0 rows affected (9.73 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql57> ANALYZE TABLE t1;
+-------+---------+----------+----------+
| Table | Op      | Msg_type | Msg_text |
+-------+---------+----------+----------+
| d1.t1 | analyze | status   | OK       |
+-------+---------+----------+----------+
1 row in set (0.01 sec)

mysql57> SELECT table_rows, sys.format_bytes(data_length) AS data_length, sys.format_bytes(index_length) AS index_length, sys.format_bytes(data_free) AS data_free FROM information_schema.tables WHERE (table_schema, table_name)= ('d1', 't1');
+------------+-------------+--------------+-----------+
| table_rows | data_length | index_length | data_free |
+------------+-------------+--------------+-----------+
|     191498 | 76.44 MiB   | 58.98 MiB    | 4.00 MiB  |
+------------+-------------+--------------+-----------+
1 row in set (0.00 sec)

mysql57> DELETE FROM t1 WHERE rand() < 0.5;
Query OK, 98257 rows affected (1.94 sec)

mysql57> ANALYZE TABLE t1;
+-------+---------+----------+----------+
| Table | Op      | Msg_type | Msg_text |
+-------+---------+----------+----------+
| d1.t1 | analyze | status   | OK       |
+-------+---------+----------+----------+
1 row in set (0.02 sec)

mysql57> SELECT table_rows, sys.format_bytes(data_length) AS data_length, sys.format_bytes(index_length) AS index_length, sys.format_bytes(data_free) AS data_free FROM information_schema.tables WHERE (table_schema, table_name)= ('d1', 't1');
+------------+-------------+--------------+-----------+
| table_rows | data_length | index_length | data_free |
+------------+-------------+--------------+-----------+
|      92680 | 76.44 MiB   | 58.98 MiB    | 4.00 MiB  |
+------------+-------------+--------------+-----------+
1 row in set (0.00 sec)

大体、storage/innobase/handler/i_s.ccから上手く見つけられないんだけどどこから見つければいいんだっけ。。:(;ﾞﾟ'ωﾟ'):

2016/03/08

MySQL <= 5.7.10からMySQL >= 5.7.11に乗り換えるだけでdefault_password_lifetimeの呪縛から逃れられる理由

見直したらほぼ日々の覚書: MySQL 5.7.4で導入されたdefault_password_lifetimeがじわじわくる（MySQL 5.7.11でFIX！！）に書いてあったんだけど、

@yoku0825 @soudai1025 yumでさくっとバージョン上げた時に、=360のままだった気がしたので、そりゃ確かに勝手に設定値を書き換えたりしないよなと思ったのでした。（勝手に既定コードを latin1 に書き換えられたら困るのと同じ）
— 坂井恵(SAKAI Kei) (@sakaik) 2016年3月6日

default_password_lifetime はユーザー作成時には何もせず、ユーザーがログインするたびにpassword_last_changedと比較するので、default_password_lifetime= 360でユーザーを作っちゃっても今の値が0なら特に何もする必要はないです

文字コードの話でいうと、character_set_serverのデフォルトにあたるものが置き換わっただけで、テーブル単位で既に指定されている文字コードは変わらない、というのと同じ感じ。

default_password_lifetimeは「ユーザーを作ってからn日後にEXPIREする」ではなくて（変数の名前からするとそんな動作しそうなんだけど）、「ログイン試行時にpassword_lifetimeが明示的に決められていない場合、password_last_changedと現在時刻を比較して、n日以上経過してたらエラーを返す」ためのパラメーター。

mysql57> SELECT user, host, password_expired, password_last_changed, password_lifetime FROM user;
+-----------+-----------+------------------+-----------------------+-------------------+
| user      | host      | password_expired | password_last_changed | password_lifetime |
+-----------+-----------+------------------+-----------------------+-------------------+
| root      | localhost | N                | 2016-02-07 21:31:49   |              NULL |
| mysql.sys | localhost | N                | 2016-02-07 21:31:49   |              NULL |
+-----------+-----------+------------------+-----------------------+-------------------+
2 rows in set (0.00 sec)

関係してくるカラムはこのへん。

まず、password_lifetimeが NULLならuse_default_password_lifetimeのフラグが立つ。NULL以外の場合（EXPIREする日付が入る）はuse_default_password_lifetimeのフラグは降りる。これはacl_loadの中なので、mysqldが起動した時やFLUSH PRIVILEGESの時にこの処理を通る。

https://github.com/mysql/mysql-server/blob/7ef2156f065d388f2c7ba2e0a69b3610e417f4d6/sql/auth/sql_auth_cache.cc#L1764-L1798

それから認証時のcheck_password_lifetime。

1) password_expiredが'Y'ならreturn false
2) use_default_password_lifetimeフラグが降りてたらpassword_lifetimeと現在時刻を比較。過ぎてたらtrue
3) フラグが降りてなかったらpassword_last_changedと現在時刻とdefault_password_lifetimeで比較。過ぎてたらtrue

https://github.com/mysql/mysql-server/blob/7ef2156f065d388f2c7ba2e0a69b3610e417f4d6/sql/auth/sql_authentication.cc#L1988-L2028

check_password_lifetimeの呼び出し元では、password_expiredが'Y'またはcheck_password_lifetimeの戻り値がtrueならEXPIREされているとしてER_MUST_CHANGE_PASSWORD_LOGIN。

https://github.com/mysql/mysql-server/blob/7ef2156f065d388f2c7ba2e0a69b3610e417f4d6/sql/auth/sql_authentication.cc#L2358-L2381

CREATE USER時に通るのはmysql_create_userだけれど、ここは特にdefault_password_lifetimeはチェックしてない。ALTER USERとSET PASSWORDの時だけ変数を参照してるくらい。

https://github.com/mysql/mysql-server/blob/7ef2156f065d388f2c7ba2e0a69b3610e417f4d6/sql/auth/sql_user.cc#L1232

5.7.11の時点で、という感じなので、5.8以降で再び仕掛けてくる時はどうなるか知らない。

2016/03/02

複数のテーブルのON UPDATE current_timestampなカラムの値を揃える方法を考える

タイトルで何を言ってるのか我ながら良くわからない。。

mysql56> SELECT * FROM t1 JOIN t2 USING(num);
+-----+-------+---------------------+---------------------+--------+---------------------+---------------------+
| num | val   | created             | updated             | val    | created             | updated             |
+-----+-------+---------------------+---------------------+--------+---------------------+---------------------+
|   1 | one   | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | eins   | 2016-03-01 15:38:44 | 2016-03-01 16:40:29 |
|   2 | two   | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | zwei   | 2016-03-01 15:38:44 | 2016-03-01 16:40:33 |
|   3 | three | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | drei   | 2016-03-01 15:38:44 | 2016-03-01 16:40:36 |
|   4 | four  | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | vier   | 2016-03-01 15:38:44 | 2016-03-01 16:40:59 |
|   5 | five  | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | funf   | 2016-03-01 15:38:44 | 2016-03-01 16:41:05 |
|   6 | six   | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | sechs  | 2016-03-01 15:38:44 | 2016-03-01 16:41:11 |
|   7 | seven | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | sieben | 2016-03-01 15:38:44 | 2016-03-01 16:41:16 |
|   8 | eight | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | acht   | 2016-03-01 15:38:44 | 2016-03-01 16:41:27 |
|   9 | nine  | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | neun   | 2016-03-01 15:38:44 | 2016-03-01 16:41:34 |
|  10 | ten   | 2016-03-01 15:38:44 | 2016-03-01 15:38:44 | zehn   | 2016-03-01 15:38:44 | 2016-03-01 16:41:46 |
+-----+-------+---------------------+---------------------+--------+---------------------+---------------------+
10 rows in set (0.00 sec)

t1.updated, t2.updatedはそれぞれDATETIME ON UPDATE CURRENT_TIMESTAMPなカラム。
やりたいことは、

mysql56> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql56> UPDATE t1 SET val= 'updated' WHERE num = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql56> UPDATE t2 SET val= 'updated' WHERE num = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql56> SELECT * FROM t1 JOIN t2 USING(num) WHERE num = 2;
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
| num | val     | created             | updated             | val     | created             | updated             |
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
|   2 | updated | 2016-03-01 15:38:44 | 2016-03-01 16:43:35 | updated | 2016-03-01 15:38:44 | 2016-03-01 16:43:40 |
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
1 row in set (0.01 sec)

この時点でt1.updatedとt2.updatedを同じ時刻にすること。

1. リテラル渡す

mysql56> ROLLBACK AND CHAIN;
Query OK, 0 rows affected (0.00 sec)

mysql56> UPDATE t1 SET val= 'updated', updated= '2016-03-01 16:44:05' WHERE num = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql56> UPDATE t2 SET val= 'updated', updated= '2016-03-01 16:44:05' WHERE num = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql56> SELECT * FROM t1 JOIN t2 USING(num) WHERE num = 2;
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
| num | val     | created             | updated             | val     | created             | updated             |
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
|   2 | updated | 2016-03-01 15:38:44 | 2016-03-01 16:44:05 | updated | 2016-03-01 15:38:44 | 2016-03-01 16:44:05 |
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
1 row in set (0.00 sec)

はじめっからUPDATE ON current_timestampなんてつけないでほしかった。

2. 1ステートメントで更新する

mysql56> ROLLBACK AND CHAIN;
Query OK, 0 rows affected (0.00 sec)

mysql56> UPDATE t1 JOIN t2 USING(num) SET t1.val= 'updated', t2.val= 'updated' WHERE t1.num = 2;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql56> SELECT * FROM t1 JOIN t2 USING(num) WHERE num = 2;
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
| num | val     | created             | updated             | val     | created             | updated             |
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
|   2 | updated | 2016-03-01 15:38:44 | 2016-03-01 16:47:24 | updated | 2016-03-01 15:38:44 | 2016-03-01 16:47:24 |
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
1 row in set (0.00 sec)

CURRENT_TIMESTAMP()はNOW()のシノニムなので、そのまま使うと「ステートメント開始時点の現在時刻」を返す。
という訳で1ステートメントなら開始時刻は1つに定まる。

どう考えても綺麗にJOINできないテーブルとかあるしINSERTやDELETEが混じったトランザクションで詰むので却下。

3. timestamp変数使う

さらに、SET TIMESTAMP ステートメントによって、NOW() で返された値は影響を受けますが、SYSDATE() で返された値は影響を受けません。つまり、バイナリログのタイムスタンプ設定は、SYSDATE() の呼び出しに影響しないことを意味します。タイムスタンプをゼロ以外の値に設定すると、後続の NOW() が起動されるたびに、その値が返されます。タイムスタンプをゼロに設定すると、この効果が取り消され、再度 NOW() が現在の日付と時間を返すようになります。

http://dev.mysql.com/doc/refman/5.6/ja/date-and-time-functions.html#function_now

というわけでこうじゃ。

mysql56> ROLLBACK AND CHAIN;
Query OK, 0 rows affected (0.00 sec)

mysql56> SET timestamp= @@timestamp;
Query OK, 0 rows affected (0.00 sec)

mysql56> UPDATE t1 SET val= 'updated' WHERE num = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql56> UPDATE t2 SET val= 'updated' WHERE num = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql56> SELECT * FROM t1 JOIN t2 USING(num) WHERE num = 2;
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
| num | val     | created             | updated             | val     | created             | updated             |
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
|   2 | updated | 2016-03-01 15:38:44 | 2016-03-01 16:55:46 | updated | 2016-03-01 15:38:44 | 2016-03-01 16:55:46 |
+-----+---------+---------------------+---------------------+---------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql56> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql56> SET timestamp= 0;
Query OK, 0 rows affected (0.00 sec)

コミットした後は（前でもいいけど）timestamp変数を0に戻しておかないと、そのコネクションのNOW()がいつまでもSET timestampした時点の時刻を返すようになる。

…はじめっからUPDATE ON current_timestampなんてつけないでほしかった。

2016/03/01

ORDER BY RAND()はしたくないけどそれなりにランダムな結果セットを返すいくつかの方法を考える

ORDER BY RAND()といえば、「結果セットをランダムにソートし、LIMITと組み合わせることでランダムに指定件数をピックアップしたかのように見える」黒魔術。

( ´-｀).oO(そういえばこれも ORDER BY FIELD と一緒で構文だと思っていた人がいたな。。

これもまあRAND()関数を使ってるだけなので、select_listに放り込めば何やってるかわかりやすい。

mysql56> SELECT num, val, RAND() AS rand_val FROM t1 ORDER BY rand_val LIMIT 3;
+-------+----------------------------------+-------------------------+
| num   | val                              | rand_val                |
+-------+----------------------------------+-------------------------+
| 94164 | e8d2546088e6be7ff164964c7a07bdb3 | 0.000012977980089353379 |
|  4354 | 46d0671dd4117ea366031f87f3aa0093 |  0.00001926440747386255 |
| 11573 | 2d6304a207cd9469f776e651e81ed7f8 | 0.000023321248612665803 |
+-------+----------------------------------+-------------------------+
3 rows in set (0.09 sec)

余談だけど、RAND()関数は引数にシード値を取れる（引数を取らない場合はテキトーにシード値が設定される）ので、ランダムっぽいけど再現可能な並び順を作り出すこともできる。

mysql56> SELECT num, val, RAND(10) AS rand_val FROM t1 ORDER BY rand_val LIMIT 3;
+-------+----------------------------------+-------------------------+
| num   | val                              | rand_val                |
+-------+----------------------------------+-------------------------+
| 78811 | 77cd08791011fb678c97302a06de9999 | 0.000002980232241545089 |
| 48089 | 7a045a3247aa6fafd68634aa6acb941f | 0.000006978400058092922 |
| 34076 | 6dcfff2b73388f6307994658463a9341 |  0.00004350300882337895 |
+-------+----------------------------------+-------------------------+
3 rows in set (0.08 sec)

mysql56> SELECT num, val, RAND(10) AS rand_val FROM t1 ORDER BY rand_val LIMIT 3;
+-------+----------------------------------+-------------------------+
| num   | val                              | rand_val                |
+-------+----------------------------------+-------------------------+
| 78811 | 77cd08791011fb678c97302a06de9999 | 0.000002980232241545089 |
| 48089 | 7a045a3247aa6fafd68634aa6acb941f | 0.000006978400058092922 |
| 34076 | 6dcfff2b73388f6307994658463a9341 |  0.00004350300882337895 |
+-------+----------------------------------+-------------------------+
3 rows in set (0.06 sec)

なおこの場合、「そのシードで何番目に生成された乱数か」がキモになるので、WHERE句評価後 *何番目に* その行がフェッチされるかでrand_valの値は変わる。WHERE句を評価した後に、取り出したレコードの順番にRAND()を計算してソートするからだ。

mysql56> SELECT num, val, RAND(10) AS rand_val FROM t1 WHERE num > 30000 ORDER BY rand_val LIMIT 3;
+-------+----------------------------------+-------------------------+
| num   | val                              | rand_val                |
+-------+----------------------------------+-------------------------+
| 78089 | 67ebeaa4f6391a89d2b629860fff2c9d | 0.000006978400058092922 |
| 64076 | 6a3f8e5443504151a7306f2a13fae303 |  0.00004350300882337895 |
| 30140 | 8befb4efe8ce6cdf0e1a84974d452a9f |  0.00004702340815870409 |
+-------+----------------------------------+-------------------------+
3 rows in set (0.06 sec)

mysql56> SELECT num, val, RAND(10) AS rand_val FROM t1 WHERE num < 80000 ORDER BY rand_val LIMIT 3;
+-------+----------------------------------+-------------------------+
| num   | val                              | rand_val                |
+-------+----------------------------------+-------------------------+
| 78811 | 77cd08791011fb678c97302a06de9999 | 0.000002980232241545089 |
| 48089 | 7a045a3247aa6fafd68634aa6acb941f | 0.000006978400058092922 |
| 34076 | 6dcfff2b73388f6307994658463a9341 |  0.00004350300882337895 |
+-------+----------------------------------+-------------------------+
3 rows in set (0.10 sec)

だから本当に再現可能なランダムっぽい何かをしたい場合、（あるなら）サロゲートキーを渡してゴニョる方がいい気がする。これなら常に「そのシードで1番目の乱数」を使うことになるので、「結果セットの先頭から何番目にあるか」が変わってもRAND()関数の戻す値は変わらない（はず）並び順をズラす場合、サロゲートキーに何かしら足してやったりかけてやったりすればいい。

mysql56> SELECT num, val, RAND(num) AS rand_val FROM t1 ORDER BY rand_val LIMIT 3;
+-------+----------------------------------+-------------------------+
| num   | val                              | rand_val                |
+-------+----------------------------------+-------------------------+
| 15536 | dbe1a0a2c9bd9241b3499318bf96f756 | 0.000004868954424624289 |
| 51034 | 861c3ad6224d443664f925552d2255a1 |  0.00001504737885207625 |
| 86532 | 4169f89a1c9c1e6eaf14b1b1e50967fb | 0.000025210902118320486 |
+-------+----------------------------------+-------------------------+
3 rows in set (0.06 sec)

mysql56> SELECT num, val, RAND(num) AS rand_val FROM t1 WHERE num < 90000 ORDER BY rand_val LIMIT 3;
+-------+----------------------------------+-------------------------+
| num   | val                              | rand_val                |
+-------+----------------------------------+-------------------------+
| 15536 | dbe1a0a2c9bd9241b3499318bf96f756 | 0.000004868954424624289 |
| 51034 | 861c3ad6224d443664f925552d2255a1 |  0.00001504737885207625 |
| 86532 | 4169f89a1c9c1e6eaf14b1b1e50967fb | 0.000025210902118320486 |
+-------+----------------------------------+-------------------------+
3 rows in set (0.06 sec)

mysql56> SELECT num, val, RAND(num) AS rand_val FROM t1 WHERE num > 10000 ORDER BY rand_val LIMIT 3;
+-------+----------------------------------+-------------------------+
| num   | val                              | rand_val                |
+-------+----------------------------------+-------------------------+
| 15536 | dbe1a0a2c9bd9241b3499318bf96f756 | 0.000004868954424624289 |
| 51034 | 861c3ad6224d443664f925552d2255a1 |  0.00001504737885207625 |
| 86532 | 4169f89a1c9c1e6eaf14b1b1e50967fb | 0.000025210902118320486 |
+-------+----------------------------------+-------------------------+
3 rows in set (0.08 sec)

mysql56> SELECT num, val, RAND(num * 2) AS rand_val FROM t1 WHERE num > 10000 ORDER BY rand_val LIMIT 3;
+-------+----------------------------------+-------------------------+
| num   | val                              | rand_val                |
+-------+----------------------------------+-------------------------+
| 25517 | 362f278d9150aaf7894f586b5682de06 |  0.00001504737885207625 |
| 43266 | faeddbfcef4331221e71ed4186e0c65b | 0.000025210902118320486 |
| 61015 | 67f68835939b9fa291a4e417312b4ec1 |  0.00003538932654577245 |
+-------+----------------------------------+-------------------------+
3 rows in set (0.06 sec)

さて本題。
ORDER BY RAND()はWHERE句でフィルターされた後の行全てに対してRAND()関数を適用し、その結果でソートするので、WHERE句でフィルターした後の行が多ければ多いほど重くなるし、WHERE句で十分フィルターが聞いてもUsing temporaryに落ちる。

昔からよく言われることではあるが、アプリケーション側で乱数を作ってWHERE句に指定するのはよくあるやり方だ。その場合、ORDER BY FIELD() はやっぱり役に立つかもしれない。
アプリケーション側と言いながらSQLでやってるのは気にしない。

mysql56> SELECT MAX(num) FROM t1 INTO @max_num;
Query OK, 1 row affected (0.01 sec)

mysql56> SET @r1 := CAST(@max_num * RAND() + 1 AS signed), @r2 := CAST(@max_num * RAND() + 1 AS signed), @r3 := CAST(@max_num * RAND() + 1 AS signed);
Query OK, 0 rows affected (0.00 sec)

mysql56> SELECT num, val FROM t1 WHERE num IN (@r1, @r2, @r3) ORDER BY FIELD(num, @r1, @r2, @r3);
+-------+----------------------------------+
| num   | val                              |
+-------+----------------------------------+
| 68966 | 3ccf50b9d73ea11f758bd030e5ac593f |
| 51533 | 2f6a0826437abc6688b22dfd89d783c0 |
| 50767 | 8deda01d4df57c1d3cb6b1e4a0391fbe |
+-------+----------------------------------+
3 rows in set (0.01 sec)

実際問題、auto_increment（の値とは限らないけれど）の値が一貫して抜けがないことは期待できない（innodb_autoinc_lock_mode= 0だってDELETEすれば抜ける訳で、= 1ならトランザクションをロールバックしたりDuplicate Key Errorを起こすだけでautoincは進む）ので、こんな風に書くしかなかろうか。

mysql56> SELECT MAX(num) FROM t1 INTO @max_num;
Query OK, 1 row affected (0.00 sec)

mysql56> SET @r := @max_num * RAND() + 1;
Query OK, 0 rows affected (0.00 sec)

mysql56> SELECT num, val FROM t1 WHERE num > @r LIMIT 1;
+------+----------------------------------+
| num  | val                              |
+------+----------------------------------+
| 8564 | 621eb0b827c09dd1804e87bd74f79383 |
+------+----------------------------------+
1 row in set (0.00 sec)

mysql56> SET @r := @max_num * RAND() + 1;
Query OK, 0 rows affected (0.00 sec)

mysql56> SELECT num, val FROM t1 WHERE num > @r LIMIT 1;
+-------+----------------------------------+
| num   | val                              |
+-------+----------------------------------+
| 67575 | fd85263468f2e1315a31116cf7b12a00 |
+-------+----------------------------------+
1 row in set (0.00 sec)

mysql56> SET @r := @max_num * RAND() + 1;
Query OK, 0 rows affected (0.00 sec)

mysql56> SELECT num, val FROM t1 WHERE num > @r LIMIT 1;
+-------+----------------------------------+
| num   | val                              |
+-------+----------------------------------+
| 12181 | b75867b590e9a1a38ceaea9f8cb9cf45 |
+-------+----------------------------------+
1 row in set (0.00 sec)

INで仕留めた場合でもLIMIT 1で仕留めた場合でも、SQLのレイヤーでは重複（たとえばnum = 1が2回引っかかる）や空振り（その条件にマッチするレコードが1件もない）を検出しないので、それが嫌ならアプリケーション側で重複判定をする必要がある。それに、必要な件数が揃うまで複数回クエリーを投げる必要があるので、それが嫌な感じはもちろんする（とはいえ、速度的には大概の場合お釣りがくる）。あとはサロゲートキーが極端に偏ってるとこれは死ぬかも知れない。1, 2, 3, .., 1000の次が10万だったりすると、max_num * rand()を超えるレコードはかなりの高確率でnum= 10万だ。

もう一つ、WHERE RAND()という手法もある。`tail -f .. | perl -nle 'if (rand() < 0.01) {print}'` って話を聞いて思い付いただけだけれども。

mysql56> SELECT num, val FROM t1 WHERE rand() < 1/100 ORDER BY num DESC LIMIT 3;
+-------+----------------------------------+
| num   | val                              |
+-------+----------------------------------+
| 99966 | 44d3377fd88bc32cd46acd38d716abd3 |
| 99899 | a9cfebcdb4e20ed975e82b7fd877693f |
| 99790 | 7aab6ba599620439ed28d3cee272c3af |
+-------+----------------------------------+
3 rows in set (0.00 sec)

mysql56> SELECT num, val FROM t1 WHERE rand() < 1/100 ORDER BY num DESC LIMIT 3;
+-------+----------------------------------+
| num   | val                              |
+-------+----------------------------------+
| 99954 | 758f0bc5e561543556e9f4e0d23335cc |
| 99663 | 10ef53cc7b761466d851d05dda6b82e3 |
| 99650 | 02aecc1719dc308a7efac6064861cf93 |
+-------+----------------------------------+
3 rows in set (0.00 sec)

DESCにしてるのは趣味なのでASCでもいいかも知れない（けど、古いものからやるよりは新しいものから引いた方がバッファプール効率がいい）もうちょっと分散させたい場合はRAND()と比較する定数の値を小さくすれば良くて、1/100でLIMIT 3なら、期待値として直近300行をhandler_read_prevして、その中から3件が選ばれる。飽くまで1クエリーで1つのテーブルから取ってくるので、重複はない。が、確率で取ってくることになるのであまりタイトなサンプリングをすると空振り（LIMITで指定した件数が集まらない）はあり得る。あと、この形はサロゲートキーに依存せずインデックスさえあれば好きなインデックスでORDER BYできる。

mysql56> SHOW SESSION STATUS LIKE 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 1     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 312   |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.00 sec)

ほぼ期待値通り。 OORDEER BY RAND()とWHEREで3回引くケースはこんな感じだった。

mysql56> SHOW SESSION STATUS LIKE 'handler_read%';
+-----------------------+--------+
| Variable_name         | Value  |
+-----------------------+--------+
| Handler_read_first    | 1      |
| Handler_read_key      | 1      |
| Handler_read_last     | 0      |
| Handler_read_next     | 0      |
| Handler_read_prev     | 0      |
| Handler_read_rnd      | 3      |
| Handler_read_rnd_next | 200002 |
+-----------------------+--------+
7 rows in set (0.00 sec)

mysql56> SHOW SESSION STATUS LIKE 'handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 4     |
| Handler_read_last     | 1     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+
7 rows in set (0.00 sec)

直近のPRIMARY KEYはバッファプールに載ってる（INSERTされたままバッファプールに残ってる）率が高い気がするので、偏りが許せるなら実用的だと思う（し、本当に完全ランダムでなきゃいけないならRAND()関数とか使っちゃいけない気がする）これだけ件数が絞れてれば、FROM句に閉じ込めてORDER BY RAND()しても許せなくはないくらいのパフォーマンスのはず。パッと見、numでORDER BYされてるとは思えないような感じに仕上がる（かもしれない）

mysql56> SELECT * FROM (SELECT num, val FROM t1 WHERE rand() < 1/3000 ORDER BY num DESC LIMIT 3) AS dummy ORDER BY RAND();
+-------+----------------------------------+
| num   | val                              |
+-------+----------------------------------+
| 96640 | 3076ef0ad4d1e7c6dec15fb4541b6997 |
| 95735 | 3b2016665210c18767dfe611b76ffbea |
| 97248 | 32d32773f19f2f421ebc2d41da4ef5a9 |
+-------+----------------------------------+
3 rows in set (0.00 sec)

mysql56> show profile cpu;
+--------------------------------+----------+----------+------------+
| Status                         | Duration | CPU_user | CPU_system |
+--------------------------------+----------+----------+------------+
| starting                       | 0.000046 | 0.000000 |   0.000000 |
| Waiting for query cache lock   | 0.000005 | 0.000000 |   0.000000 |
| init                           | 0.000008 | 0.000000 |   0.000000 |
| checking query cache for query | 0.000348 | 0.000000 |   0.000000 |
| checking permissions           | 0.000016 | 0.000000 |   0.000000 |
| Opening tables                 | 0.000195 | 0.000000 |   0.000000 |
| init                           | 0.000019 | 0.000000 |   0.000000 |
| System lock                    | 0.000018 | 0.000000 |   0.000000 |
| optimizing                     | 0.000004 | 0.000000 |   0.000000 |
| optimizing                     | 0.000019 | 0.000000 |   0.000000 |
| statistics                     | 0.000035 | 0.000000 |   0.000000 |
| preparing                      | 0.000019 | 0.000000 |   0.000000 |
| Sorting result                 | 0.000003 | 0.000000 |   0.000000 |
| statistics                     | 0.000003 | 0.000000 |   0.000000 |
| preparing                      | 0.000006 | 0.000000 |   0.000000 |
| Creating tmp table             | 0.000022 | 0.000000 |   0.000000 |
| Sorting result                 | 0.000004 | 0.000000 |   0.000000 |
| executing                      | 0.000014 | 0.000000 |   0.000000 |
| Sending data                   | 0.000012 | 0.000000 |   0.000000 |
| executing                      | 0.000002 | 0.000000 |   0.000000 |
| Sending data                   | 0.001889 | 0.001999 |   0.000000 |
| Creating sort index            | 0.000045 | 0.000000 |   0.000000 |
| end                            | 0.000002 | 0.000000 |   0.000000 |
| removing tmp table             | 0.000025 | 0.000000 |   0.000000 |
| end                            | 0.000009 | 0.000000 |   0.000000 |
| query end                      | 0.000007 | 0.000000 |   0.000000 |
| closing tables                 | 0.000002 | 0.000000 |   0.000000 |
| removing tmp table             | 0.000004 | 0.000000 |   0.000000 |
| closing tables                 | 0.000010 | 0.000000 |   0.000000 |
| freeing items                  | 0.000176 | 0.001000 |   0.000000 |
| cleaning up                    | 0.000041 | 0.000000 |   0.000000 |
+--------------------------------+----------+----------+------------+
31 rows in set, 1 warning (0.00 sec)

サロゲートキーへの依存度はmax_num * RAND()より小さいけど、なんかどこかに落とし穴がありそうだよなぁ。。

( ´-｀).oO(query_cache_type= DEMANDなんだけど、これ切れば300usくらい速くなるな。。

最後に、ランダムピックしたものをそもそもキャッシュする方法も考え付いた。そもそも元の行数が少なければ、ORDER BY RAND()でも戦えるんじゃないか、って話。
キャッシュは定期的に更新（というかスワップ）してやればいい。

mysql56> CREATE TABLE rand_cache AS SELECT num, val FROM t1 ORDER BY RAND() LIMIT 10000;
Query OK, 10000 rows affected (0.17 sec)
Records: 10000  Duplicates: 0  Warnings: 0

mysql56> SELECT * FROM rand_cache ORDER BY RAND() LIMIT 3;
+-------+----------------------------------+
| num   | val                              |
+-------+----------------------------------+
|  2533 | 4de754248c196c85ee4fbdcee89179bd |
| 78915 | d3272a819b09ced96c69e22f183cc88e |
| 16351 | dcb8e02b8527b08dbd8acb146bccc612 |
+-------+----------------------------------+
3 rows in set (0.00 sec)

mysql56> CREATE TABLE tmp_rand_cache AS SELECT num, val FROM t1 ORDER BY RAND() LIMIT 10000;
Query OK, 10000 rows affected (0.14 sec)
Records: 10000  Duplicates: 0  Warnings: 0

mysql56> RENAME TABLE rand_cache TO old_rand_cache, tmp_rand_cache TO rand_cache;
Query OK, 0 rows affected (0.01 sec)

mysql56> DROP TABLE old_rand_cache;
Query OK, 0 rows affected (0.01 sec)

mysql56> SELECT * FROM rand_cache ORDER BY RAND() LIMIT 3;
+-------+----------------------------------+
| num   | val                              |
+-------+----------------------------------+
| 98937 | 73a3320fa46a5e4fad268056af61cd42 |
| 54927 | a11d83c11ed8c95a32b3628a762cf41f |
| 38444 | d3e8129138f2c76dc6e4048281160fe0 |
+-------+----------------------------------+
3 rows in set (0.01 sec)

何も考えないORDER BY RAND()はORDER BY RAND()で、「空振りが無い（WHERE句でフィルターした結果がLIMIT未満でなければ）」、「重複がない」、「サロゲートキーに依存しない」というメリットもあるのだなぁと思った。そりゃあ使いたがる人多くてもわかるは。

【2020/05/13 16:01】

奥さんのブコメで(4年前に)教えてもらっていたもうちょっとスマートそうなやり方。

WHERE RAND() LIMIT するくらいなら、SELECT primary_key ORDER BY RAND() LIMIT 5した結果をセルフジョインするほうがいいんじゃ / top-N sortは最近のmysqlでは… https://t.co/VTcw6HQkgs
— Kazuho Oku (@kazuho) March 1, 2016

4年が経って手元の環境が既に5.6ではないけれど、吊るしのORDER BY RAND()に比べて10msくらい節約できるみたい（これは平均行サイズが大きくなれば大きくなるほど差も大きくなるはず）

ただしMySQL 8.0ではテンポラリーテーブルの方式が変わっている(TempTableストレージエンジン)ので5.7とそれ以前だと傾向は変わるかも知れないし変わらないかも知れない。

mysql80 88> SELECT num, val FROM t1 ORDER BY RAND() LIMIT 3;
+-------+----------------------------------+
| num | val |
+-------+----------------------------------+
| 88253 | a885315b8b17d78a1121b875bc0bc198 |
| 67289 | fde520a1553f284433c0140de0bab59a |
| 85943 | f324bc6faedca902976e58abd5d8a9d1 |
+-------+----------------------------------+
3 rows in set (0.06 sec)

mysql80 122> SELECT t1.num, t1.val FROM t1 JOIN (SELECT num FROM t1 ORDER BY RAND() LIMIT 3) AS _dummy ON t1.num = _dummy.num;
+-------+----------------------------------+
| num | val |
+-------+----------------------------------+
| 63347 | 65b553cedfe36b1a168b7600ba146140 |
| 22114 | fb70c5b7a51b1a13deec0dcf7026459d |
| 2169 | bd0cc810b580b35884bd9df37c0e8b0f |
+-------+----------------------------------+
3 rows in set (0.06 sec)

mysql80 123> EXPLAIN SELECT num, val FROM t1 ORDER BY RAND() LIMIT 3;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 99750 | 100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql80 123> EXPLAIN SELECT t1.num, t1.val FROM t1 JOIN (SELECT num FROM t1 ORDER BY RAND() LIMIT 3) AS _dummy ON t1.num = _dummy.num;

+----+---------------------+------------+------------+--------+---------------+------+---------+------------+-------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+---------------------+------------+------------+--------+---------------+------+---------+------------+-------+----------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
| 1 | PRIMARY | t1 | NULL | eq_ref | num | num | 8 | _dummy.num | 1 | 100.00 | NULL |
| 2 | UNCACHEABLE DERIVED | t1 | NULL | index | NULL | num | 8 | NULL | 99750 | 100.00 | Using index; Using temporary; Using filesort |
+----+---------------------+------------+------------+--------+---------------+------+---------+------------+-------+----------+----------------------------------------------+
3 rows in set, 1 warning (0.01 sec)

$ mysqlslap -S /usr/mysql/8.0.20/data/mysql.sock -uroot --query="SELECT num, val FROM d1.t1 ORDER BY RAND() LIMIT 3" --number-of-queries=1000
Benchmark
Average number of seconds to run all queries: 52.038 seconds
Minimum number of seconds to run all queries: 52.038 seconds
Maximum number of seconds to run all queries: 52.038 seconds
Number of clients running queries: 1
Average number of queries per client: 1000

$ mysqlslap -S /usr/mysql/8.0.20/data/mysql.sock -uroot --query="SELECT t1.num, t1.val FROM d1.t1 JOIN (SELECT num FROM d1.t1 ORDER BY RAND() LIMIT 3) AS _dummy ON t1.num = _dummy.num" --number-of-queries=1000
Benchmark
Average number of seconds to run all queries: 42.276 seconds
Minimum number of seconds to run all queries: 42.276 seconds
Maximum number of seconds to run all queries: 42.276 seconds
Number of clients running queries: 1
Average number of queries per client: 1000

4年越しですがありがとうございます！

登録: 投稿 ( Atom )

GA