Re: 続・歯抜けを埋めるSQL（10万件編）

この記事は

続・歯抜けを埋めるSQL（10万件編） - sakaikの日々雑感～(T)編

への「俺ならこうひねるかな」版です。

やってることは坂井さんと大して変わっていませんが、方向性が結構違うのが面白いのでメモしておきます。

というかSQLだけでやっていないのでタイトルに反しています（）

---

坂井さんの手法でやった時の所要時間はこんなものでした。290ms。

mysql80 237> SET @@cte_max_recursion_depth=100005;

Query OK, 0 rows affected (0.00 sec)

mysql80 237> INSERT INTO t22

    -> WITH RECURSIVE num(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM num WHERE n<(SELECT MAX(id) FROM t22))

    -> SELECT n id, null, null FROM num LEFT OUTER JOIN t22 ON (num.n=t22.id) WHERE t22.id IS NULL;

Query OK, 1005 rows affected (0.29 sec)

Records: 1005  Duplicates: 0  Warnings: 0

俺ならこうやるかなはこんな感じになります。

$ for n in $(seq 1 10000 100000) ; do 
>  mysql80 -vve "SET SESSION cte_max_recursion_depth = 100000; INSERT IGNORE INTO d1.t22 WITH RECURSIVE v AS (SELECT $n AS id UNION
 ALL SELECT id + 1 FROM v WHERE id <= $n + 10000) SELECT id, NULL, NULL FROM v"
> done

bashのforとseqコマンドにより、idのレンジを10000区切りにして10回実行します。

--------------
SET SESSION cte_max_recursion_depth = 100000
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
INSERT IGNORE INTO d1.t22 WITH RECURSIVE v AS (SELECT 1 AS id UNION ALL SELECT id + 1 FROM v WHERE id <= 1 + 10000) SELECT id, NULL, NULL FROM v
--------------

Query OK, 0 rows affected, 10002 warnings (0.04 sec)
Records: 10002  Duplicates: 10002  Warnings: 10002

Bye
--------------
SET SESSION cte_max_recursion_depth = 100000
--------------

Query OK, 0 rows affected (0.00 sec)

--------------
INSERT IGNORE INTO d1.t22 WITH RECURSIVE v AS (SELECT 10001 AS id UNION ALL SELECT id + 1 FROM v WHERE id <= 10001 + 10000) SELECT id, NULL, NULL FROM v
--------------

Query OK, 110 rows affected, 9892 warnings (0.06 sec)
Records: 10002  Duplicates: 9892  Warnings: 9892

Bye

..

こんな感じね。
この場合 INSERT IGNORE なので「本当は空きじゃないレコード」にも触わる(= バッファプールを多少荒らす) ので、1万刻みにした部分は調整するかも知れません。あとは実際にやるなら多少sleepを入れるでしょう。

雑に一発60msecくらいなので、10回やると600msでだいぶ赤字ですが、この方法のメリットとデメリット。

メリット

1. たとえ詰める行の数が数百万になっても、1回あたりで取るロックの行は高々1万行で、ループの回数が増えるだけで単発のクエリの速度は一定する
   • 坂井さんバージョンだと、INSERT INTO .. SELECT ..のSELECT側は共有ロックを取るので、行数が増えてクエリ時間が長くなれば長くなるほど「既存のレコードを更新できない時間が増え」ます。
   • 外側分割パターンだとsleepの時間とseqで飛ぶ数さえ調整すればこの辺は調整できます。

mysql80 261> SELECT object_name, index_name, lock_type, lock_mode, COUNT(*) FROM performance_schema.data_locks GROUP BY 1, 2, 3, 4;
+-------------+------------+-----------+---------------+----------+
| object_name | index_name | lock_type | lock_mode     | COUNT(*) |
+-------------+------------+-----------+---------------+----------+
| t22         | NULL       | TABLE     | IX            |        1 |
| t22         | NULL       | TABLE     | IS            |        1 |
| t22         | PRIMARY    | RECORD    | S             |      136 |
| t22         | PRIMARY    | RECORD    | S,REC_NOT_GAP |    98046 |
| t22         | PRIMARY    | RECORD    | S,GAP         |     3870 |
+-------------+------------+-----------+---------------+----------+
5 rows in set (0.70 sec)

1. たとえ詰める行の数が（略）レプリケーション遅延を起こしにくいです。

   • MySQL 8.0のbinlog_formatはROWなので、レプリケーションソースで行った計算を再度レプリカで行うわけではないですが(binlog_format=STATEMENTだとレプリカで再計算する)、それでも行の数が増えれば増えるほどレプリケーションは遅延します。
   • こちらもsleepとseqで調整できます。

2. 中断が簡単

   • 1ステートメントだと「All or nothing」なので既存のトラフィックに影響が出た…Ctrl + C!! からのロールバックが始まります
   • こっちは中断しても最後のnの値を持っていれば、最後のn - 1から再開できます(最後のnのところはロールバックされるであろうので、マイナス1)。たとえnを控えずに先頭から再開しても処理が終わったところは空振りするだけなので、中断したところまでは全て空振りのままサクサク進むでしょう（sleepぶんが無駄になりますが）

デメリット

1. アトミックでない

   • これが最大の問題。「歯抜けを埋めている最中の様子がAppに観測されても良い」場合しか使えないので、一発で洗い替えをしたい場合はこの手法は選べない。
     • でもそれなら俺、pt-oscっぽくトリガー貼って埋め直したテーブル作ってRENAME TABLEで入れ替えようかな…

2. 美しくない、というかタイトルに反している

   • 結局アプリケーションコードを書いているのと一緒なので（この程度のループなら好きなプログラミング言語であっさり書けるはず）、わざわざドヤ顔するほどのものではない

3. トータルの所要時間が伸びる

   • ロックやレプリカが許容できるうちは特に分割する必要もない

---

ああ、俺って「それを実機に投入することを前提に考え」がちなんだなあと気づきました（ロックとかレプリカとか）

見方が違って楽しいなと思ったメモです。