実行計画キャッシュ の再利用 多段階JOINを含むSQL 前編

はじめに

本記事では、MySQLにおけるSQLパフォーマンスの違い.実行計画キャッシュ を比較するための準備として、大規模なデータセットを用意する方法を解説します。最終的には、user_idを用いた複数のクエリパターンに対し、PREPARE + EXECUTEやリテラル埋め込み方式などを比較し、それぞれの実行時間にどのような違いが現れるかを検証します。

この記事は3部構成の前編にあたります。ここでは主にテストデータの構造を設計し、MySQL上にデータを投入するところまでを扱います。MySQLは8.4.5を利用しています。

テストデータの準備

今回の検証に使用するテーブルは以下のとおりです。

テーブル作成スクリプト

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100)
);

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    created_at DATETIME,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    price INT,
    category_id INT,
    supplier_id INT
);

CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    product_id INT,
    quantity INT
);

CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100)
);

CREATE TABLE suppliers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100)
);

データ投入スクリプト

-- users（10,000件）
INSERT INTO users (name)
SELECT CONCAT('user_', LPAD(@i:=@i+1, 5, '0'))
FROM information_schema.columns, (SELECT @i:=0) vars
LIMIT 10000;

-- categories（10件）
INSERT INTO categories (name)
SELECT CONCAT('category_', LPAD(@i:=@i+1, 2, '0'))
FROM information_schema.columns, (SELECT @i:=0) vars
LIMIT 10;

-- suppliers（100件）
INSERT INTO suppliers (name)
SELECT CONCAT('supplier_', LPAD(@i:=@i+1, 3, '0'))
FROM information_schema.columns, (SELECT @i:=0) vars
LIMIT 100;

-- products（10,000件）
INSERT INTO products (name, price, category_id, supplier_id)
SELECT 
  CONCAT('product_', LPAD(@i:=@i+1, 5, '0')),
  FLOOR(100 + RAND() * 900),
  FLOOR(1 + RAND() * 10),
  FLOOR(1 + RAND() * 100)
FROM information_schema.columns, (SELECT @i:=0) vars
LIMIT 10000;

-- orders（100,000件）
INSERT INTO orders (user_id, created_at)
SELECT 
  FLOOR(1 + RAND() * 10000),
  NOW() - INTERVAL FLOOR(RAND() * 365) DAY
FROM information_schema.columns, (SELECT @i:=0) vars
LIMIT 100000;

-- order_items（300,000件）
INSERT INTO order_items (order_id, product_id, quantity)
SELECT 
  FLOOR(1 + RAND() * 100000),
  FLOOR(1 + RAND() * 10000),
  FLOOR(1 + RAND() * 5 + 1)
FROM information_schema.columns, (SELECT @i:=0) vars
LIMIT 300000;

次回予告

次回の中編では、上記データを用いたクエリの実行計画の比較、および各方式のEXPLAIN ANALYZE結果を読み解く準備について取り上げます。