[MySQL] サブクエリでLIMITが使えない場合の対策

最近、会社でSQLを使い始めた。もらったサンプルコードにサブクエリがあり、それを書き換えながら実行することがある。これをもらうまで、サブクエリというものを知らなかった。

先月、自力でサブクエリを組み立てようとしたら、書き方がわからなかったことがあり、そのままGWに突入したので、GW中に考えてみようと思った。その為には、家で同じようなSQLが試せる環境が欲しい。
ふと、家のMySQLでもサブクエリが使えるのだろうか？と思って調べてみたらできるようで、ドキュメントに書かれている形の単純なものは動いた。そこで、今回悩んでいるクエリに近づけようと、サブクエリにLIMITを追加すると、エラーが出た。

●コード

SELECT *
FROM testdata
WHERE id IN (
  SELECT DISTINCT(id)
  FROM testdata
  WHERE '2022-04-01' <= date AND date < '2022-04-10'
  LIMIT 3
);

●実行結果

ERROR 1235 (42000): This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

使用したMySQLはMySQL 5.5から派生したMariaDBのバージョン10.1である。この制限はMariaDBのドキュメントにもMySQL 5.6のドキュメントにも書かれている（MariaDBのドキュメントの記載はわかりにくい）。
2発目で制限事項にヒットするとは、何とも引きが強い。

ネットで探し回ると、対策が2種類ほど見つかった。

●対策１：INでなくJOINを使って絞る

SELECT *
FROM testdata RIGHT JOIN (
  SELECT DISTINCT(id)
  FROM testdata
  WHERE '2022-04-01' <= date AND date < '2022-04-10'
  LIMIT 3
) AS t1 USING (id);

ネットではRIGHT JOINでなくJOIN、USINGでなくONとINを使う例が多く、こう書く例は見なかったが、筆者はSQLに慣れていないせいか、こう書く方がわかり易い。

●対策２：制限事項に当たらないサブクエリを挟む

SELECT *
FROM testdata
WHERE id IN (
  SELECT *
  FROM (
    SELECT DISTINCT(id)
    FROM testdata
    WHERE '2022-04-01' <= date AND date < '2022-04-10'
    LIMIT 3
  ) AS t1
);

こちらの対策は極めて簡単である。

サンプルデータの行数やサブクエリが返す行数を増やしたりして簡単に2つの対策の処理時間を比較してみたが、ほぼ差が見られなかった。
上の対策２が通るなら、元のコードは何故動かせないの？と思ってしまう。

以下、実験に使用したコードとメモを残す。

●サンプルデータ作成コード

DROP TABLE IF EXISTS testdata;
CREATE TABLE testdata (
  id CHAR(10),
  date DATE,
  count INT,
  PRIMARY KEY (id, date)
);

DROP PROCEDURE IF EXISTS make_testdata;
DELIMITER //
CREATE PROCEDURE make_testdata(IN id_num INT, date_num INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE j INT DEFAULT 0;
  DECLARE seed1 INT DEFAULT 2;
  DECLARE seed2 INT DEFAULT 2;

  /* stored procedures with a lot of INSERT without this is 20x slower */
  SET autocommit = 0;

  /* get reproducible random sequence as a table */
  DROP TABLE IF EXISTS rand_tmp;
  CREATE TABLE rand_tmp (
    id INT PRIMARY KEY,
    rand_val FLOAT
  );

  SET i = 1;
  WHILE i <= id_num DO
    INSERT INTO rand_tmp VALUES (i, 0);
    SET i = i + 1;
  END WHILE;
  UPDATE rand_tmp SET rand_val = RAND(seed1);

  /* make test data */
  SET i = 1;
  WHILE i <= id_num DO
    /* data starts from random day */
    SET j = (SELECT rand_val FROM rand_tmp WHERE id = i) * date_num;
    WHILE j < date_num DO
      INSERT INTO testdata VALUES(
        CONCAT('ID_', LPAD(i, 3, '0')),
        DATE_ADD('2022-4-1', INTERVAL j DAY),
        0  /* "count" values are set later */
      );
      SET j = j + 1;
    END WHILE;
  SET i = i + 1;
  END WHILE;
  UPDATE testdata SET count = FLOOR(RAND(seed2) * (10 + SUBSTRING(id, 4)));

  DROP TABLE rand_tmp;
  SET autocommit = 1;
END
//
DELIMITER ;

CALL make_testdata(20, 30);

"SET autocommit = 0"をせずにストアドプロシージャーでINSERTやUPDATEを大量に実行すると非常に時間が掛かる。

MySQLのRAND()には乱数のシードを渡せるが、CやPythonのように1回シードを渡してRAND()を繰り返し呼び出せばシードを渡した後の乱数列が同じになる訳ではなかった。シードを与えないRAND()は常に別のシードから生成された乱数を返す。同じになるのは、1回のSELECT, UPDATE等が呼び出すRAND(seed)の乱数列（t1が100行なら"SELECT RAND(seed) FROM t1"が返す100個の乱数）である。
それなら、RAND()を呼び出す度にシードを変えれば良いのではないかと思ったが、RAND(1), RAND(2), RAND(3), ...とシードを変えながら呼び出すと、4種類の乱数が繰り返されるだけだった。
今回は乱数の再現性の為に、使う乱数をひとまとめに取得するように書き換えたら、面倒なことになった。次からは再現性のある乱数を使うテストデータはPythonでSQLのINSERT文を出力するようにして作ろうと思った。

●MySQLの制限でエラーになったコードと対策例

/* ends in "not supported" error */
SELECT *
FROM testdata
WHERE id IN (
  SELECT DISTINCT(id)
  FROM testdata
  WHERE '2022-04-01' <= date AND date < '2022-04-10'
  LIMIT 3
);

/* workaround 1 */
SELECT *
FROM testdata RIGHT JOIN (
  SELECT DISTINCT(id)
  FROM testdata
  WHERE '2022-04-01' <= date AND date < '2022-04-10'
  LIMIT 3
) AS t1 USING (id);

/* workaround 2 */
SELECT *
FROM testdata
WHERE id IN (
  SELECT *
  FROM (
    SELECT DISTINCT(id)
    FROM testdata
    WHERE '2022-04-01' <= date AND date < '2022-04-10'
    LIMIT 3
  ) AS t1
);

●対策例の実行結果

id	date	count
ID_002	2022-04-05	1
ID_002	2022-04-06	0
ID_002	2022-04-07	8
...	...	...
ID_002	2022-04-29	3
ID_002	2022-04-30	8
ID_006	2022-04-07	3
ID_006	2022-04-08	0
ID_006	2022-04-09	6
...	...	...
ID_006	2022-04-29	3
ID_006	2022-04-30	11
ID_008	2022-04-02	12
ID_008	2022-04-03	16
ID_008	2022-04-04	8
...	...	...
ID_008	2022-04-29	7
ID_008	2022-04-30	7

●もう少しやりたいことに近づけたコード

SELECT
  id,
  COUNT(*) as data_num,
  AVG(count) AS mean,
  MAX(count) AS max
FROM (
  SELECT *
  FROM testdata
  WHERE id IN (
    SELECT *
    FROM (
      SELECT DISTINCT(id)
      FROM testdata
      WHERE '2022-04-01' <= date AND date < '2022-04-10'
      LIMIT 3
    ) AS t1
  ) AND  '2022-04-01' <= date AND date < '2022-05-01'
) AS t2
GROUP BY id
ORDER BY id;

●実行結果

id	data_num	mean	max
ID_002	26	5.9231	11
ID_006	24	8.0000	15
ID_008	29	9.2069	16

自宅である程度サブクエリの実験をする環境が整ったが、肝心の、会社で書き方がわからなかったクエリが何だったかを思い出せず、GW中には取り組めなかった。

筆者はMySQLとは20年以上の付き合いなのだが、SQLの知識はtexinfoのチュートリアル程度で、JavaやPerlから簡単なSQLを渡すばっかりで、こんなにSQLを書いたのは初めてである。SQLだけでも結構色々なことができるんだなと思った。

Weblog on mebius.tokaichiba.jp

[MySQL] サブクエリでLIMITが使えない場合の対策

コメント