Zenn
😏

[MySQL]CTE を使って簡単なダミーデータをサクッと手軽に大量投入

2024/05/11に公開
MySQL
with
CTE
mysql80
tech

TL;DR

  • MySQL8.0 から追加された新機能である 共通テーブル式 (CTE) を使うと単純なダミーデータが手軽に大量投入できる
  • 例えば hash 値を格納する hashes というテーブルに100万件のダミーデータを投入したい場合の例は下記のように実現できる
    • テーブル定義
      CREATE TABLE hashes (
id INT PRIMARY KEY AUTO_INCREMENT,
hash CHAR(64)
);
    • クエリ実行
      SET SESSION cte_max_recursion_depth = 1000000;

      INSERT INTO hashes(hash)
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 1000000
)
SELECT SHA2(n, 256) FROM cte;
  • このような CTE の使い方を再帰的な共通テーブル式という

解説

CTE について

CTE は複数回参照できる一時的な結果セットらしい。

共通テーブル式 (CTE) は、単一ステートメントのスコープ内に存在し、あとでそのステートメント内で複数回参照できる名前付き一時結果セットです。

13.2.15 WITH (共通テーブル式)

構文について

冒頭に例示した構文を解説していく。

まず下記のシステム変数の取り扱いについて。

SET SESSION cte_max_recursion_depth = 1000000;

cte_max_recursion_depth は再帰する上限を設定するシステム変数。
デフォルトでは 1000 となっているため、それ以上の再帰を行いたい場合は再設定が必要。
ここでは 100 万件を上限に設定している。

続いてメインのデータ投入部分である下記のクエリについて。

INSERT INTO hashes(hash)
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 1000000
)
SELECT SHA2(n, 256) FROM cte;

WITH 句の周辺部分を中心に取り上げる。

WITH RECURSIVE cte (n)

原則 WITH RECURSIVE の構文から始まる。（※WITH 句内の CTE が自身を参照しない場合は RECURSIVE は不要)

cte は結果セットで (n) は結果セットのカラム。どちらも任意の名前で指定可能。
この (n) のカラム型は後述する非再帰的 SELECT で指定した値から推測され、NULL が許容される。

続いてサブクエリの部分。

AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 1000000
)

SELECT 1 は非再帰的 SELECT と呼ばれ、再起処理を行う起点となる。この場合整数型の 1 を指定している（イメージ的にはインクリメントの初期化に近い感じ？）。
前述したように、この非再帰的 SELECT で指定した値を推測することにより CTE のカラムの型が決定される。

非再帰的 SELECT と後述する再帰的 SELECT 部分をUNION ALL で結果セットをまとめている（UNION DISTINCT も使用可能でその場合は重複が排除される）。

サブクエリ内の最後のクエリは再帰的 SELECT と呼ばれ、この場合 cte の n のカラムの値をインクリメントしており、それが1000000に達すると再帰処理が終了するという定義になっている。

処理速度について

検証環境

  • MacBook Pro（チップ：Apple M1 Pro, メモリ：16 GB, OSバージョン：13.6.4）
  • Docker（バージョン：25.0.2）
  • MySQL 公式イメージ（mysql:8.0）

結果

100万件だと、4.46 sec くらい。

mysql> INSERT INTO hashes(hash)
    -> WITH RECURSIVE cte (n) AS
    -> (
    ->   SELECT 1
    ->   UNION ALL
    ->   SELECT n + 1 FROM cte WHERE n < 1000000
    -> )
    -> SELECT SHA2(n, 256) FROM cte;
Query OK, 1000000 rows affected (4.46 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

その他処理結果は下記を参照。

件数 速度
1,000 0.03 sec
10,000 0.07 sec
100,000 0.45 sec
1,000,000 4.46 sec
10,000,000 48.57 sec

おわりに

レコード数を加味した負荷検証等でサクッとダミーデータ入れたい際に使えそうな印象。

Discussion