概要

開発中のアプリケーションでSQLの実行計画を検証したい場合に大量のテストデータが必要になることがあります。
この記事の前半は大量テストデータを生成するMySQLプロシージャのサンプルコードの紹介で、後半は生成したテストデータを基に実行計画の検証と適切なインデックスの作成についてのまとめになります。
(記事の後半はまだ未完成です。今後追記します。)

環境

Windows10
MySQL 5.6.25

参考

[13.1.15 CREATE PROCEDURE および CREATE FUNCTION 構文] (https://dev.mysql.com/doc/refman/5.6/ja/create-procedure.html)
[第20章ストアドプログラムおよびビュー] (https://dev.mysql.com/doc/refman/5.6/ja/stored-programs-views.html)

テストデータの生成

使用するテーブル

この記事で使用するテーブルの種類と生成データの内容です。

テーブル	テーブル名	生成データ
category	カテゴリマスター	30件生成、この記事で使用しているカテゴリデータは米Amazonより拝借しました。
item	アイテムマスター	1カテゴリーにつきランダムで3から7件のアイテムがあるという想定です。
item_stock	在庫テーブル	1アイテムにつき1件生成します。在庫数はランダムで設定します。
user	ユーザーテーブル	20万件生成します、うち約10%は退会ユーザーという想定です。
user_order	注文テーブル	注文日が4/1から5/31までの2カ月分を生成します。1日あたり45,000から55,000ユーザーがランダムで1から9件の注文を行うという想定です。
user_review	レビューテーブル	ランダムで2万から3万件のレビューデータを生成します。

DDL文

ddl.sql

DROP TABLE IF EXISTS category;
CREATE TABLE category (
  id INT NOT NULL AUTO_INCREMENT             COMMENT 'カテゴリID',
  name VARCHAR(60) NOT NULL                  COMMENT 'カテゴリ名',
  del_flag TINYINT(1) NOT NULL DEFAULT 0     COMMENT '1:論理削除',
  create_at DATETIME NOT NULL DEFAULT NOW(),
  update_at DATETIME NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id)
)
ENGINE = INNODB
DEFAULT CHARSET = UTF8
COMMENT = 'カテゴリマスター';


DROP TABLE IF EXISTS item;
CREATE TABLE item (
  id INT NOT NULL AUTO_INCREMENT             COMMENT 'アイテムID',
  name VARCHAR(90) NOT NULL                  COMMENT 'アイテム名',
  price INT NOT NULL                         COMMENT '価格',
  sales_from DATE NOT NULL                   COMMENT '販売開始日',
  sales_to DATE NOT NULL                     COMMENT '販売終了日',
  standard_type INT NOT NULL                 COMMENT '規格タイプ',
  category_id INT NOT NULL                   COMMENT 'カテゴリID',
  del_flag TINYINT(1) NOT NULL DEFAULT 0     COMMENT '1:論理削除',
  create_at DATETIME NOT NULL DEFAULT NOW(),
  update_at DATETIME NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id)
)
ENGINE = INNODB
DEFAULT CHARSET = UTF8
COMMENT = 'アイテムマスター';


DROP TABLE IF EXISTS item_stock;
CREATE TABLE item_stock (
  id INT NOT NULL AUTO_INCREMENT             COMMENT 'アイテム在庫ID',
  stock INT NOT NULL DEFAULT 0               COMMENT '在庫数',
  item_id INT NOT NULL                       COMMENT 'アイテムID',
  del_flag TINYINT(1) NOT NULL DEFAULT 0     COMMENT '1:論理削除',
  create_at DATETIME NOT NULL DEFAULT NOW(),
  update_at DATETIME NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id)
)
ENGINE = INNODB
DEFAULT CHARSET = UTF8
COMMENT = 'アイテム在庫テーブル';


DROP TABLE IF EXISTS user;
CREATE TABLE user (
  id INT NOT NULL AUTO_INCREMENT              COMMENT 'ユーザーID',
  nick_name VARCHAR(60) NOT NULL              COMMENT 'ニックネーム',
  gender TINYINT(1) NOT NULL                  COMMENT '性別 1:男性 2:女性',
  prefecture_id TINYINT(2) NOT NULL DEFAULT 0 COMMENT '都道府県 0:不明 1:北海道-47:沖縄',
  email VARCHAR(120)                          COMMENT 'メールアドレス',
  del_flag TINYINT(1) NOT NULL DEFAULT 0      COMMENT '1:論理削除',
  create_at DATETIME NOT NULL DEFAULT NOW(),
  update_at DATETIME NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id)
)
ENGINE = INNODB
DEFAULT CHARSET = UTF8
COMMENT = 'ユーザーテーブル';


DROP TABLE IF EXISTS user_order;
CREATE TABLE user_order (
  id INT NOT NULL AUTO_INCREMENT             COMMENT '注文ID',
  order_num TINYINT(1) NOT NULL              COMMENT '注文数',
  order_at DATETIME NOT NULL                 COMMENT '注文日',
  order_type TINYINT(1) NOT NULL DEFAULT 1   COMMENT '1:店頭 2:オンライン',
  shipped_at DATETIME NULL                   COMMENT '配送日',
  cancel_flag TINYINT(1) NOT NULL DEFAULT 0  COMMENT '1:キャンセル',
  item_id INT NOT NULL                       COMMENT 'アイテムID',
  user_id INT NOT NULL                       COMMENT 'ユーザーID',
  del_flag TINYINT(1) NOT NULL DEFAULT 0     COMMENT '1:論理削除',
  create_at DATETIME NOT NULL DEFAULT NOW(),
  update_at DATETIME NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id)
)
ENGINE = INNODB
DEFAULT CHARSET = UTF8
COMMENT = 'ユーザー注文テーブル';


DROP TABLE IF EXISTS user_review;
CREATE TABLE user_review (
  id INT NOT NULL AUTO_INCREMENT             COMMENT 'レビューID',
  review_at DATETIME NOT NULL                COMMENT 'レビュー日',
  review TEXT NOT NULL                       COMMENT 'レビュー',
  user_order_id INT NOT NULL                 COMMENT '注文ID',
  user_id INT NOT NULL                       COMMENT 'ユーザーID',
  del_flag TINYINT(1) NOT NULL DEFAULT 0     COMMENT '1:論理削除',
  create_at DATETIME NOT NULL DEFAULT NOW(),
  update_at DATETIME NOT NULL DEFAULT NOW(),
  PRIMARY KEY (id)
)
ENGINE = INNODB
DEFAULT CHARSET = UTF8
COMMENT = 'ユーザーレビューテーブル';

テストデータを生成するプロシージャ,ファンクション

テストデータを生成するメイン処理はproc_create_test_dataというプロシージャです。性別や都道府県などランダムで値を決めるデータ項目には、下記のファンクションを呼び出しています。

ファンクション	戻り値	機能
func_get_gender	TINYINT(1)	1(:男性)、2(:女性)をランダムで返す
func_get_email	VARCHAR(120)	ランダムな文字列から構成されるメールアドレスを返す
func_get_prefecture	TINYINT(2)	都道府県コードをランダムで返す
func_get_order_at	DATETIME	引数で渡した注文日(DATE)に、ランダムな時刻を付加して注文日時(DATETIME)を返す
func_get_max_order	INT	引数で渡した都道府県コードによってランダムなアイテムの注文件数を返す
func_get_shipped_at	DATETIME	引数で渡した注文日時(DATETIME)に、ランダムな日数を加算して配送日時(DATETIME)を返す
func_get_review_text	TEXT	ランダムな文字列からなるレビューテキストを返す

proc_create_test_data.sql

DELIMITER //

/*******************************************************************************
 * ランダムで性別を返す関数
 * 割合は男性が60%、女性が40%という想定。
 *
 ******************************************************************************/
DROP FUNCTION IF EXISTS func_get_gender;
SHOW WARNINGS;

CREATE FUNCTION func_get_gender() RETURNS TINYINT(1)
DETERMINISTIC CONTAINS SQL
BEGIN
  DECLARE n INT;
  DECLARE v_gender TINYINT(1);

  /* ランダムで性別を選択 (0-4) */
  SET n = FLOOR(RAND() * 5);

  CASE n
    WHEN 0 THEN
      SET v_gender = 1;
    WHEN 1 THEN
      SET v_gender = 2;
    WHEN 2 THEN
      SET v_gender = 1;
    WHEN 3 THEN
      SET v_gender = 2;
    ELSE
      SET v_gender = 1;
  END CASE;

  RETURN v_gender;

END;
SHOW WARNINGS;


/*******************************************************************************
 * ランダムでメールアドレスを返す関数
 *
 *******************************************************************************/
DROP FUNCTION IF EXISTS func_get_email;
SHOW WARNINGS;

CREATE FUNCTION func_get_email() RETURNS VARCHAR(120)
DETERMINISTIC CONTAINS SQL
BEGIN
  DECLARE n INT;
  DECLARE v_email VARCHAR(120);
  DECLARE v_domain VARCHAR(30);

  /* ランダムでドメインを選択 (0-6) */
  SET n = FLOOR(RAND() * 7);

  CASE n
    WHEN 0 THEN
      SET v_domain = 'example.jp';
    WHEN 1 THEN
      SET v_domain = 'example.co.jp';
    WHEN 2 THEN
      SET v_domain = 'example.ne.jp';
    WHEN 3 THEN
      SET v_domain = 'example.com';
    WHEN 4 THEN
      SET v_domain = 'example.net';
    WHEN 5 THEN
      SET v_domain = 'example.info';
    ELSE
      SET v_domain = 'example.biz';
  END CASE;

  SET v_email = CONCAT(SUBSTRING(MD5(RAND()), 1, 4), '.', SUBSTRING(MD5(RAND()), 1, 5), '@', v_domain);

  RETURN v_email;

END;
SHOW WARNINGS;


/*******************************************************************************
 * ランダムで都道府県コードを返す関数
 * 東京、愛知、大阪、福岡はユーザー数が多いという想定。
 * 
 ******************************************************************************/
DROP FUNCTION IF EXISTS func_get_prefecture;
SHOW WARNINGS;

CREATE FUNCTION func_get_prefecture() RETURNS TINYINT(2)
DETERMINISTIC CONTAINS SQL
BEGIN
  DECLARE n INT;
  DECLARE v_prefecture_id TINYINT(2);

  /* 0-119 */
  SET n = FLOOR(RAND() * 120);

  IF n >= 0 AND n <= 47 THEN
    SET v_prefecture_id = n;

  ELSE

    IF n >= 48 AND n <= 89 THEN
      /* 東京 */
      SET v_prefecture_id = 13;

    ELSEIF n >= 90 AND n <= 99 THEN
      /* 愛知 */
      SET v_prefecture_id = 23;

    ELSEIF n >= 100 AND n <= 109 THEN
      /* 大阪 */
      SET v_prefecture_id = 27;

    ELSE
      /* 福岡 */
      SET v_prefecture_id = 40;
    END IF;

  END IF;

  RETURN v_prefecture_id;

END;
SHOW WARNINGS;


/*******************************************************************************
 * 時刻がランダムな注文日を返す
 * (注文日のカーディナリティーを高めるための細工です。)
 *
 ******************************************************************************/
DROP FUNCTION IF EXISTS func_get_order_at;
SHOW WARNINGS;

CREATE FUNCTION func_get_order_at(v_order_at DATE) RETURNS DATETIME
DETERMINISTIC CONTAINS SQL
BEGIN
  DECLARE v_time_h, v_time_m, v_time_s INT;
  DECLARE v_order_datetime DATETIME;

  /* 注文日の時刻をランダムで決定 */
  SET v_time_h = FLOOR(RAND() * 24);
  SET v_time_m = FLOOR(RAND() * 60);
  SET v_time_s = FLOOR(RAND() * 60);

  SET v_order_datetime = CAST(CONCAT(DATE_FORMAT(v_order_at, '%Y-%m-%d'), ' ',
    LPAD(v_time_h,2,'0'), ':',
    LPAD(v_time_m,2,'0'), ':',
    LPAD(v_time_s,2,'0')) AS DATETIME);

  RETURN v_order_datetime;

END;
SHOW WARNINGS;


/*******************************************************************************
 * 都道府県別に偏りがある注文数を返す
 * 
 ******************************************************************************/
DROP FUNCTION IF EXISTS func_get_max_order;
SHOW WARNINGS;

CREATE FUNCTION func_get_max_order(v_prefecture_id INT) RETURNS INT
DETERMINISTIC CONTAINS SQL
BEGIN
  DECLARE v_max_order INT;

  /* 注文するアイテム数をランダムで決定 */
  CASE v_prefecture_id
  WHEN 13 THEN
    /* 東京 2-9 */
    SET v_max_order = FLOOR(RAND() * 8) + 2;
  WHEN 23 THEN
    /* 愛知 1-7 */
    SET v_max_order = FLOOR(RAND() * 7) + 1;
  WHEN 27 THEN
    /* 大阪 1-8 */
    SET v_max_order = FLOOR(RAND() * 8) + 1;
  WHEN 40 THEN
    /* 福岡 1-7 */
    SET v_max_order = FLOOR(RAND() * 7) + 1;
  ELSE
    /* そのほか 1-5 */
    SET v_max_order = FLOOR(RAND() * 5) + 1;
  END CASE;

  RETURN v_max_order;

END;
SHOW WARNINGS;


/*******************************************************************************
 * 配送日をランダムで返す
 *
 ******************************************************************************/
DROP FUNCTION IF EXISTS func_get_shipped_at;
SHOW WARNINGS;

CREATE FUNCTION func_get_shipped_at(v_order_at DATETIME) RETURNS DATETIME
DETERMINISTIC CONTAINS SQL
BEGIN
  DECLARE v_shipped_datetime DATETIME;

  /* 5/1以前は配送日は確定しているという想定 */
  IF v_order_at < '2016-05-01' THEN

    SET v_shipped_datetime = v_order_at + INTERVAL FLOOR(RAND() * 3) + 1 DAY;

  /* 5/1以降はランダムで配送日を確定  */
  ELSE

    IF FLOOR(RAND() * 10) < 3 THEN

      SET v_shipped_datetime = v_order_at + INTERVAL FLOOR(RAND() * 3) + 1 DAY;

    ELSE

      /* 未確定 */
      SET v_shipped_datetime = NULL;

    END IF;

  END IF;


  RETURN v_shipped_datetime;

END;
SHOW WARNINGS;


/*******************************************************************************
 * レビューテキスト文をランダムで生成する
 *
 ******************************************************************************/
DROP FUNCTION IF EXISTS func_get_review_text;
SHOW WARNINGS;

CREATE FUNCTION func_get_review_text() RETURNS TEXT
DETERMINISTIC CONTAINS SQL
BEGIN
  DECLARE v_rand_num_lines, v_cnt_lines INT;
  DECLARE n INT;

  DECLARE v_review_text TEXT;

  # SELECT MD5(RAND());
  # SELECT SHA(RAND());
  # SELECT SHA2(RAND(), 256);
  # SELECT SHA2(RAND(), 512);
  # SELECT SUBSTRING('あいうえおかきくけこさしすせそ', 10, 1);

  /* レビューの行数 3-5 */
  SET v_rand_num_lines = FLOOR(RAND() * 3) + 3;
  SET v_cnt_lines = 0;

  SET v_review_text = '';

  WHILE v_rand_num_lines > v_cnt_lines DO

    /* ダミー文字列を選択 */
    SET n = FLOOR(RAND() * 10);

    CASE n
      WHEN 0 THEN
        SET v_review_text = CONCAT(v_review_text, 'あいうえおかきくけこさしすせそたちつてとなにぬねの', '\n');
      WHEN 1 THEN
        SET v_review_text = CONCAT(v_review_text, 'ハヒフヘホマミムメモヤユヨワヲン', '\n');
      WHEN 2 THEN
        SET v_review_text = CONCAT(v_review_text, '１２３４５６７８９０一二三四五六七八九零', '\n');
      WHEN 3 THEN
        SET v_review_text = CONCAT(v_review_text, '亜哀挨愛曖悪握圧扱宛嵐安案暗以衣位囲医依委威為畏', '\n');
      WHEN 4 THEN
        SET v_review_text = CONCAT(v_review_text, '1234567890-+*%#$&=()@[]', '\n');
      WHEN 5 THEN
        SET v_review_text = CONCAT(v_review_text, 'ABC DEF GHI JKL MNO PQR STU VWX YZ', '\n');
      WHEN 6 THEN
        SET v_review_text = CONCAT(v_review_text, '弌丐丕个丱丶丼丿乂乖乘亂亅豫亊舒弍于亞亟亠亢亰亳', '\n');
      WHEN 7 THEN
        SET v_review_text = CONCAT(v_review_text, '─┌┐│└┘├┤┬┴〒', '\n');
      WHEN 8 THEN
        SET v_review_text = CONCAT(v_review_text, '丑丞乃之乎也云亘亙些亦亥亨亮仔伊伍伽佃佑伶侃侑俄', '\n');
      ELSE
        SET v_review_text = CONCAT(v_review_text, '俱㐂丨丯丰亍仡份仿伃伋你佈佉佖佟佪佬佾侊侔侗侮俉', '\n');
    END CASE;

    SET v_cnt_lines = v_cnt_lines + 1;
  END WHILE;

  RETURN v_review_text;

END;
SHOW WARNINGS;


/*******************************************************************************
 * テストデータを生成するプロシージャ
 *
 * call proc_create_test_data();
 *
 ******************************************************************************/
DROP PROCEDURE IF EXISTS proc_create_test_data;
SHOW WARNINGS;

CREATE PROCEDURE proc_create_test_data()
BEGIN
  DECLARE done INT DEFAULT 0;

  /* category_cur用の変数 */
  DECLARE category_id_rec INT;
  DECLARE category_name_rec VARCHAR(60);

  /* user_cur用の変数 */
  DECLARE user_id_rec INT;
  DECLARE user_nick_name_rec VARCHAR(60);
  DECLARE user_prefecture_id_rec TINYINT(2);

  /* user_order_cur用の変数 */
  DECLARE user_order_id_rec INT;
  DECLARE user_order_user_id_rec INT;
  DECLARE user_order_shipped_at_rec DATETIME;

  DECLARE rand_num_order_users INT DEFAULT 50000;
  DECLARE rand_num_review_users INT DEFAULT 10000;

  DECLARE category_cur CURSOR FOR
    SELECT c.id
         , c.name
      FROM category c
     ORDER BY
           c.id ASC
         ;

  /* 注文するユーザーをランダムでrand_num_order_users件数、選択するカーソル */
  DECLARE user_cur CURSOR FOR
    SELECT u.id
         , u.nick_name
         , u.prefecture_id
      FROM user u
     WHERE u.del_flag = 0
     ORDER BY
           RAND()
     LIMIT rand_num_order_users
         ;

  /* レビューする注文データをランダムでrand_num_review_users件数、選択するカーソル */
  DECLARE user_order_cur CURSOR FOR
    SELECT uo.id
         , uo.user_id
         , uo.shipped_at
      FROM user_order uo
     WHERE uo.cancel_flag = 0
       AND uo.del_flag = 0
       AND uo.shipped_at IS NOT NULL
     ORDER BY
           RAND()
     LIMIT rand_num_review_users
         ;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  /**
   * テストデータ初期化
   */
  BEGIN
    TRUNCATE TABLE category;
    TRUNCATE TABLE item;
    TRUNCATE TABLE item_stock;
    TRUNCATE TABLE user;
    TRUNCATE TABLE user_order;
    TRUNCATE TABLE user_review;
  END;


  SELECT NOW() AS "START CREATE CATEGORY";

  /**
   * アイテムのカテゴリを作成
   */
  create_category: BEGIN

    START TRANSACTION;

    INSERT INTO category (name) VALUES
      ('Kitchen & Dining'),
      ('FURNITURE'),
      ('BEDDING & BATH'),
      ('Appliances'),
      ('PATIO,LAWN & GARDEN'),
      ('ART'),
      ('PET SUPPLIES'),
      ('Wedding Registry'),
      ('Home Improvement'),
      ('Power and Hand Tools'),
      
      ('Lighting & Ceiling Fans'),
      ('Kitchen & Bath Fixtures'),
      ('Hardware'),
      ('SMART HOME'),
      ('Luxury-Beauty'),
      ('Mens-Grooming'),
      ('Health, Household and Baby Care'),
      ('Vitamins & Dietary Supplements'),
      ('Food & Beverages'),
      ('Specialty Diets'),

      ('Wine'),
      ('Subscribe & Save'),
      ('Prime Pantry'),
      ('Toys & Games'),
      ('DIGITAL MUSIC'),
      ('Musical Instruments'),
      ('HEADPHONES'),
      ('Video Games'),
      ('Digital Video Games'),
      ('Entertainment Collectibles')
      ;

    COMMIT;

  END create_category;

  SELECT NOW() AS "END CREATE CATEGORY";


  SELECT NOW() AS "START CREATE CATEGORY/ITEM DATA";

  /**
   * アイテム、在庫を作成
   * 1カテゴリにつき3から7種のアイテムをランダムで作成
   */
  create_item: BEGIN
    DECLARE v_name VARCHAR(90);
    DECLARE v_price, v_standard_type, v_stock INT;
    DECLARE v_sales_from, v_sales_to DATE;

    DECLARE v_rand_max_item, v_cnt_item INT;


    START TRANSACTION;

    SET done = 0;

    OPEN category_cur;

    category_ite: LOOP

      FETCH category_cur INTO category_id_rec, category_name_rec;
      IF done = 1 THEN
        SELECT "leave category_ite";
        LEAVE category_ite;
      END IF;

      /* 1カテゴリに作成するアイテムの数をランダムで決定 (3-7) */
      SET v_rand_max_item = FLOOR(RAND() * 5) + 3;
      SET v_cnt_item = 0;

      WHILE v_rand_max_item > v_cnt_item DO

        /* アイテム名をランダムで決定 */
        SET v_name = CONCAT(category_name_rec, ' - ',  SUBSTRING(MD5(RAND()), 1, 10));
        /* アイテムの価格をランダムで決定 (100-1000) */
        SET v_price = (FLOOR(RAND() * 10) + 1) * 100;
        /* アイテムの販売期間 */
        SET v_sales_from = CAST('2016-04-01' AS DATE);
        SET v_sales_to = CAST('2016-07-31' AS DATE);
        /* 規格タイプをランダムで決定 (1-10) */
        SET v_standard_type = FLOOR(RAND() * 10) + 1;

        INSERT INTO item (
          name,
          price,
          sales_from,
          sales_to,
          standard_type,
          category_id)
        VALUES (
          v_name,
          v_price,
          v_sales_from,
          v_sales_to,
          v_standard_type,
          category_id_rec
        );

        /* アイテムの在庫数をランダムで決定 */
        SET v_stock = (FLOOR(RAND() * 100) + 1) * 100;
        INSERT INTO item_stock (
          stock,
          item_id)
        VALUES (
          v_stock,
          LAST_INSERT_ID()
        );

        SET v_cnt_item = v_cnt_item + 1;
      END WHILE;

    END LOOP category_ite;

    CLOSE category_cur;

    COMMIT;

  END create_item;

  SELECT NOW() AS "END CREATE CATEGORY/ITEM DATA";


  SELECT NOW() AS "START CREATE USER DATA";

  /**
   * ユーザーを作成
   * 
   * 20万ユーザー (うち10%は退会者)
   * 性別は男性が60%、女性が40%
   */
  create_user: BEGIN
    DECLARE v_nick_name VARCHAR(60);
    DECLARE v_gender TINYINT(1);
    DECLARE v_prefecture_id TINYINT(2);
    DECLARE v_email VARCHAR(120);
    DECLARE v_del_flag, v_del_random TINYINT(1);

    DECLARE v_max_user, v_cnt_user INT;


    START TRANSACTION;

    /* テストユーザーの作成件数 */
    SET v_max_user = 200000;
    SET v_cnt_user = 0;

    WHILE v_max_user > v_cnt_user DO

      /* ユーザー名をランダムで決定 */
      SET v_nick_name = CONCAT('TESTUSER-', SUBSTRING(MD5(RAND()), 1, 20));
      /* 性別をランダムで決定 */
      SET v_gender = func_get_gender();
      /* メールアドレスをランダムで決定 */
      SET v_email = func_get_email();
      /* 住所をランダムで決定 */
      SET v_prefecture_id = func_get_prefecture();

      /* 退会者をランダムで決定 */
      SET v_del_random = FLOOR(RAND() * 10);
      IF v_del_random = 0 THEN
        SET v_del_flag = 1;
      ELSE
        SET v_del_flag = 0;
      END IF;

      INSERT INTO user (
        nick_name,
        gender,
        prefecture_id,
        email,
        del_flag)
      VALUES (
        v_nick_name,
        v_gender,
        v_prefecture_id,
        v_email,
        v_del_flag
      );

      /* 1000件ごとにcommit */
      IF v_cnt_user % 1000 = 0 THEN
        COMMIT;
        START TRANSACTION;
      END IF;

      SET v_cnt_user = v_cnt_user + 1;
    END WHILE;

    COMMIT;

  END create_user;

  SELECT NOW() AS "END CREATE USER DATA";


  SELECT NOW() AS "START CREATE ORDER DATA";

  /**
   * 注文データを作成
   * 
   * 注文データを作成する期間は4/1～5/31までの2カ月間
   * 1日あたり5万ユーザーがアイテムを1-9件購入するという想定
   */
  create_user_order: BEGIN
    DECLARE v_rand_order_num INT;
    DECLARE v_order_at DATETIME;
    DECLARE v_shipped_at DATETIME;
    DECLARE v_category_id, v_item_id INT;

    DECLARE v_order_date, v_order_date_end DATE;

    DECLARE v_rand_max_order, v_cnt_order INT;
    DECLARE v_cnt_user INT;


    START TRANSACTION;

    SET v_cnt_user = 0;

    /* 注文データを作成する期間 */
    SET v_order_date = CAST('2016-04-01' AS DATE);
    SET v_order_date_end = CAST('2016-06-01' AS DATE);

    WHILE v_order_date_end > v_order_date DO
      SELECT v_order_date;

      /* 日毎に注文するユーザー数をランダムで決定 (45000-55000) */
      SET rand_num_order_users = FLOOR(RAND() * 10001) + 45000;
      SELECT rand_num_order_users;

      SET done = 0;

      OPEN user_cur;

      user_ite: LOOP

        FETCH user_cur INTO user_id_rec, user_nick_name_rec, user_prefecture_id_rec;
        IF done = 1 THEN
          SELECT "leave user_ite";
          LEAVE user_ite;
        END IF;

        /* 注文日の時刻をランダムで決定 */
        SET v_order_at = func_get_order_at(v_order_date);

        /* 注文するアイテム数をランダムで決定 */
        SET v_rand_max_order = func_get_max_order(user_prefecture_id_rec);

        SET v_cnt_order = 0;

        /* 注文するカテゴリをランダムで選択 */
        SELECT c.id
          INTO v_category_id
          FROM category c
         WHERE c.del_flag = 0
         ORDER BY
               RAND()
         LIMIT 1
             ;

        /* 1ユーザーの注文を作成 */
        WHILE v_rand_max_order > v_cnt_order DO

          /* 注文するアイテムをランダムで選択 */
          SELECT i.id
            INTO v_item_id
            FROM item i
           WHERE i.category_id = v_category_id
             AND i.sales_from <= v_order_date
             AND i.sales_to   >  v_order_date
             AND i.del_flag    = 0
           ORDER BY
                 RAND()
           LIMIT 1
               ;

          /* 注文数をランダムで決定 (1-3) */
          SET v_rand_order_num = FLOOR(RAND() * 3) + 1;

          /* 配送日をランダムで決定 */
          SET v_shipped_at = func_get_shipped_at(v_order_at);

          INSERT INTO user_order (
            order_num,
            order_at,
            order_type,
            shipped_at,
            item_id,
            user_id)
          VALUES (
            v_rand_order_num,
            v_order_at,
            1,
            v_shipped_at,
            v_item_id,
            user_id_rec
          );

          /* アイテムの在庫を減らす */
          UPDATE item_stock
             SET item_stock.stock = item_stock.stock - v_rand_order_num
               , item_stock.update_at = NOW()
           WHERE item_stock.item_id = v_item_id
               ;

          SET v_cnt_order = v_cnt_order + 1;
        END WHILE;

        /* 100人毎にcommit */
        IF v_cnt_user % 100 = 0 THEN
          COMMIT;
          START TRANSACTION;
        END IF;

        SET v_cnt_user = v_cnt_user + 1;
      END LOOP user_ite;

      CLOSE user_cur;

      COMMIT;

      SET v_order_date = v_order_date + INTERVAL 1 DAY;
    END WHILE;

    COMMIT;

  END create_user_order;

  SELECT NOW() AS "END CREATE ORDER DATA";


  SELECT NOW() AS "START CREATE REVIEW DATA";

  /**
   * レビューデータを作成
   */
  create_user_review: BEGIN
    DECLARE v_review_at DATETIME;
    DECLARE v_review TEXT;

    DECLARE v_cnt_review INT;
    DECLARE v_rand_day_after_shipped INT DEFAULT 1;

    /* レビューデータを作成する件数をランダムで選択 (10000 - 19999) */
    SET rand_num_review_users = FLOOR(RAND() * 10000) + 10000;


    START TRANSACTION;

    SET v_cnt_review = 0;

    SET done = 0;

    OPEN user_order_cur;

    user_order_ite: LOOP

      FETCH user_order_cur INTO user_order_id_rec, user_order_user_id_rec, user_order_shipped_at_rec;
      IF done = 1 THEN
        SELECT "leave user_order_ite";
        LEAVE user_order_ite;
      END IF;

      /* レビュー日は配送日から1-3日後 */
      SET v_rand_day_after_shipped = FLOOR(RAND() * 3) + 1;
      SET v_review_at = user_order_shipped_at_rec + INTERVAL v_rand_day_after_shipped DAY;

      SET v_review = func_get_review_text();

      INSERT INTO user_review (
        review_at,
        review,
        user_order_id,
        user_id)
      VALUES (
        v_review_at,
        v_review,
        user_order_id_rec,
        user_order_user_id_rec
      );

      IF v_cnt_review % 1000 = 0 THEN
        COMMIT;
        START TRANSACTION;
      END IF;

      SET v_cnt_review = v_cnt_review + 1;
    END LOOP user_order_ite;

    CLOSE user_order_cur;

    COMMIT;

  END create_user_review;

  SELECT NOW() AS "END CREATE REVIEW DATA";

END;
//

SHOW WARNINGS//

DELIMITER ;

コンパイルと実行方法

sourceコマンドでプロシージャソースコードを記述したsqlファイルをコンパイルします。

mysql> source proc_create_test_data.sql

callコマンドでプロシージャを実行します。

mysql> call proc_create_test_data();

作成したテストデータの件数を確認

mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
|   200000 |
+----------+
1 row in set (0.24 sec)

mysql> select count(*) from user_order;
+----------+
| count(*) |
+----------+
| 13038016 |
+----------+
1 row in set (7.63 sec)

mysql> select count(*) from user_review;
+----------+
| count(*) |
+----------+
|    34225 |
+----------+
1 row in set (0.01 sec)

mysql> select date_format(order_at,'%Y-%m-%d'),count(*) from user_order group by date_format(order_at, '%Y-%m-%d');
+----------------------------------+----------+
| date_format(order_at,'%Y-%m-%d') | count(*) |
+----------------------------------+----------+
| 2016-04-01                       |   203628 |
| 2016-04-02                       |   225943 |
| 2016-04-03                       |   221178 |
| 2016-04-04                       |   224318 |
| 2016-04-05                       |   215821 |
| 2016-04-06                       |   214844 |
| 2016-04-07                       |   205091 |
| 2016-04-08                       |   193322 |
| 2016-04-09                       |   219763 |
| 2016-04-10                       |   216624 |
| 2016-04-11                       |   211997 |
| 2016-04-12                       |   223492 |
| 2016-04-13                       |   230407 |
| 2016-04-14                       |   206718 |
| 2016-04-15                       |   219937 |
| 2016-04-16                       |   230948 |
| 2016-04-17                       |   226669 |
| 2016-04-18                       |   196278 |
| 2016-04-19                       |   200039 |
| 2016-04-20                       |   227307 |
| 2016-04-21                       |   229773 |
| 2016-04-22                       |   230296 |
| 2016-04-23                       |   197891 |
| 2016-04-24                       |   220273 |
| 2016-04-25                       |   215798 |
| 2016-04-26                       |   199446 |
| 2016-04-27                       |   208080 |
| 2016-04-28                       |   190407 |
| 2016-04-29                       |   214265 |
| 2016-04-30                       |   223841 |
| 2016-05-01                       |   209504 |
| 2016-05-02                       |   193034 |
| 2016-05-03                       |   225004 |
| 2016-05-04                       |   205764 |
| 2016-05-05                       |   219216 |
| 2016-05-06                       |   202977 |
| 2016-05-07                       |   222608 |
| 2016-05-08                       |   211754 |
| 2016-05-09                       |   207673 |
| 2016-05-10                       |   213777 |
| 2016-05-11                       |   192130 |
| 2016-05-12                       |   200673 |
| 2016-05-13                       |   223720 |
| 2016-05-14                       |   214946 |
| 2016-05-15                       |   205062 |
| 2016-05-16                       |   224822 |
| 2016-05-17                       |   225614 |
| 2016-05-18                       |   216231 |
| 2016-05-19                       |   194590 |
| 2016-05-20                       |   194344 |
| 2016-05-21                       |   224965 |
| 2016-05-22                       |   229596 |
| 2016-05-23                       |   219349 |
| 2016-05-24                       |   229466 |
| 2016-05-25                       |   225111 |
| 2016-05-26                       |   221274 |
| 2016-05-27                       |   205995 |
| 2016-05-28                       |   199041 |
| 2016-05-29                       |   221122 |
| 2016-05-30                       |   199228 |
| 2016-05-31                       |   215032 |
+----------------------------------+----------+
61 rows in set (19.38 sec)

[WIP] 実行計画の検証

[WIP] アプリケーションで実行するSQL

1日あたりの注文件数

mysql> select SQL_NO_CACHE
              count(uo.id)
         from user_order uo
        where uo.order_at >= '2016-04-01'
          and uo.order_at <  '2016-04-02'
          and uo.cancel_flag = 0
          and uo.del_flag    = 0
            ;
+--------------+
| count(uo.id) |
+--------------+
|       203628 |
+--------------+
1 row in set (9.84 sec)

+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows     | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | uo    | ALL  | NULL          | NULL | NULL    | NULL | 12684241 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
1 row in set (0.00 sec)

1日あたりのアイテム別の注文件数

mysql> select SQL_NO_CACHE
              i.id         as item_id
            , count(uo.id) as order_num
         from user_order uo
            , item i
        where uo.order_at >= '2016-04-01'
          and uo.order_at <  '2016-04-02'
          and uo.cancel_flag = 0
          and uo.del_flag    = 0
          and i.id = uo.item_id
        group by
              i.id
            ;
+---------+-----------+
| item_id | order_num |
+---------+-----------+
|       1 |      2190 |
|       2 |      2246 |
...省略...
|     148 |      1381 |
|     149 |      1408 |
+---------+-----------+
149 rows in set (10.10 sec)

+----+-------------+-------+--------+---------------+---------+---------+----------------------+----------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                  | rows     | Extra                                        |
+----+-------------+-------+--------+---------------+---------+---------+----------------------+----------+----------------------------------------------+
|  1 | SIMPLE      | uo    | ALL    | NULL          | NULL    | NULL    | NULL                 | 12684241 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | i     | eq_ref | PRIMARY       | PRIMARY | 4       | sample_db.uo.item_id |        1 | Using index                                  |
+----+-------------+-------+--------+---------------+---------+---------+----------------------+----------+----------------------------------------------+
2 rows in set (0.00 sec)

1日あたりのユニークな注文ユーザー数

mysql> select SQL_NO_CACHE
              count(distinct uo.user_id)
         from user_order uo
        where uo.order_at >= '2016-04-01'
          and uo.order_at <  '2016-04-02'
          and uo.cancel_flag = 0
          and uo.del_flag    = 0
            ;
+----------------------------+
| count(distinct uo.user_id) |
+----------------------------+
|                      48509 |
+----------------------------+
1 row in set (9.78 sec)

+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows     | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | uo    | ALL  | NULL          | NULL | NULL    | NULL | 12684241 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
1 row in set (0.00 sec)

ユーザーの注文データ

mysql> select SQL_NO_CACHE
              uo.*
         from user_order uo
        where uo.user_id = 5
          and uo.order_at >= '2016-04-01'
          and uo.order_at <  '2016-04-02'
          and uo.cancel_flag = 0
          and uo.del_flag    = 0
        order by
              uo.id asc
            ;
+--------+-----------+---------------------+------------+---------------------+-------------+---------+---------+----------+---------------------+---------------------+
| id     | order_num | order_at            | order_type | shipped_at          | cancel_flag | item_id | user_id | del_flag | create_at           | update_at           |
+--------+-----------+---------------------+------------+---------------------+-------------+---------+---------+----------+---------------------+---------------------+
| 108896 |         3 | 2016-04-01 05:32:01 |          1 | 2016-04-03 05:32:01 |           0 |      42 |       5 |        0 | 2016-04-11 21:53:34 | 2016-04-11 21:53:34 |
| 108897 |         1 | 2016-04-01 05:32:01 |          1 | 2016-04-04 05:32:01 |           0 |      41 |       5 |        0 | 2016-04-11 21:53:34 | 2016-04-11 21:53:34 |
| 108898 |         1 | 2016-04-01 05:32:01 |          1 | 2016-04-02 05:32:01 |           0 |      40 |       5 |        0 | 2016-04-11 21:53:34 | 2016-04-11 21:53:34 |
+--------+-----------+---------------------+------------+---------------------+-------------+---------+---------+----------+---------------------+---------------------+
3 rows in set (9.57 sec)

+----+-------------+-------+-------+---------------+---------+---------+------+----------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows     | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+----------+-------------+
|  1 | SIMPLE      | uo    | index | NULL          | PRIMARY | 4       | NULL | 12684241 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+----------+-------------+
1 row in set (0.00 sec)

[WIP] インデックスを作成する

create index

CREATE INDEX idx_user_order_01 USING BTREE ON user_order (
  order_at
);
Query OK, 0 rows affected (52.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

CREATE INDEX idx_user_order_02 USING BTREE ON user_order (
  user_id,
  order_at
); 
Query OK, 0 rows affected (1 min 3.47 sec)
Records: 0  Duplicates: 0  Warnings: 0

CREATE INDEX idx_user_order_02_b USING BTREE ON user_order (
  order_at,
  user_id
); 
Query OK, 0 rows affected (1 min 3.88 sec)
Records: 0  Duplicates: 0  Warnings: 0

CREATE INDEX idx_user_order_02_c USING BTREE ON user_order (
  user_id,
  order_at,
  cancel_flag
); 
Query OK, 0 rows affected (1 min 9.50 sec)
Records: 0  Duplicates: 0  Warnings: 0

CREATE INDEX idx_user_order_03 USING BTREE ON user_order (
  user_id,
  shipped_at
); 
Query OK, 0 rows affected (1 min 5.78 sec)
Records: 0  Duplicates: 0  Warnings: 0

CREATE INDEX idx_user_order_03_b USING BTREE ON user_order (
  shipped_at,
  user_id
); 
Query OK, 0 rows affected (1 min 4.78 sec)
Records: 0  Duplicates: 0  Warnings: 0

[WIP] インデックスを確認する

show index

ANALYZE TABLE user_order;
+----------------------+---------+----------+----------+
| Table                | Op      | Msg_type | Msg_text |
+----------------------+---------+----------+----------+
| sample_db.user_order | analyze | status   | OK       |
+----------------------+---------+----------+----------+
1 row in set (1.38 sec)

SHOW INDEX FROM user_order;

[WIP] インデックスを削除する

drop index

DROP INDEX idx_user_order_01 ON user_order;

DROP INDEX idx_user_order_02 ON user_order;

DROP INDEX idx_user_order_03 ON user_order;