概要
DBチューニングする際に必要なデータを作れるようになりたいためダミーデータの作成に挑戦した。
PythonのFakerというライブラリを使うと適当な文字列を生成できるので、そちらを利用してSQLのdumpファイルを作成する。
作成したdumpファイルをmysqlコマンドで読み込ませる。
ER図
ユーザーがポストしてそのポストに対してコメントできるシステムのデータをイメージした。
ユーザーに都道府県コードで紐づく都道府県マスターがある。
IDの参照関係があるため、ダミーデータを作る際には制約を違反しないように作成しなければならない。
erDiagram
PREFECTURE {
int prefecture_no
string prefecture_name
}
USER {
binary user_uuid
string user_name
datetime created
datetime updated
int prefecture_no
}
POST {
int post_id
string title
string content
binary post_user_uuid
datetime created
datetime updated
}
COMMENT {
int comment_id
int post_id
string content
binary comment_user_uuid
datetime created
datetime updated
}
USER ||--|| PREFECTURE: prefecture_no
USER ||--o{ POST : user_uuid
USER ||--o{ COMMENT : user_uuid
POST ||--o{ COMMENT : post_id
スキーマ作成とともに都道府県マスターは挿入してしまう。
CREATE TABLE prefecture (
prefecture_name VARCHAR(10) NOT NULL,
prefecture_no INT NOT NULL
);
CREATE TABLE user (
user_uuid VARBINARY(16) NOT NULL,
user_name VARCHAR(100),
prefecture_no INT NOT NULL,
created DATETIME DEFAULT CURRENT_TIMESTAMP,
updated DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
);
CREATE TABLE post (
post_id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(100),
content VARCHAR(2000),
post_user_uuid VARBINARY(16) NOT NULL,
created DATETIME DEFAULT CURRENT_TIMESTAMP,
updated DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
);
CREATE TABLE comment (
comment_id INT PRIMARY KEY AUTO_INCREMENT,
post_id INT,
content VARCHAR(2000),
comment_user_uuid VARBINARY(16) NOT NULL,
created DATETIME DEFAULT CURRENT_TIMESTAMP,
updated DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
);
INSERT INTO prefecture (prefecture_name, prefecture_no) VALUES
('北海道', 1),('青森', 2),('岩手', 3),('宮城', 4),('秋田', 5),('山形', 6),('福島', 7),('茨城', 8),('栃木', 9),('群馬', 10),('埼玉', 11),('千葉', 12),('東京', 13),('神奈川', 14),('新潟', 15),('富山', 16),('石川', 17),('福井', 18),('山梨', 19),('長野', 20),('岐阜', 21),('静岡', 22),('愛知', 23),('三重', 24),('滋賀', 25),('京都', 26),('大阪', 27),('兵庫', 28),('奈良', 29),('和歌山', 30),('鳥取', 31),('島根', 32),('岡山', 33),('広島', 34),('山口', 35),('徳島', 36),('香川', 37),('愛媛', 38),('高知', 39),('福岡', 40),('佐賀', 41),('長崎', 42),('熊本', 43),('大分', 44),('宮崎', 45),('鹿児島', 46),('沖縄', 47);
データ作成
PythonでBULK INSERT文を標準出力してリダイレクションでdumpファイルを作成する。
ダミーデータ生成にはFakerを利用する。初期化時にja_JPを指定すると日本に合わせたデータを生成できる。
下記ではfake.name()、fake.text()でそれぞれ日本人名と日本語文章を作成してくれる(文章とはいえ単語を繋げた文字列だった)。
Fakerの使い方の詳細はドキュメントを参照する。
faker.readthedocs.io
from faker import Faker
fake = Faker('ja_JP')
fake.seed_instance(1234)
USER_NUM = 1000
POST_NUM = 10000
COMMENT_NUM = 10000
user_uuid_list = []
sql = f"INSERT INTO `user` (`user_uuid`, `user_name`, `prefecture_no`) VALUES"
for _ in range(USER_NUM):
user_uuid = fake.uuid4()
user_uuid_list.append(user_uuid)
user_name = fake.name()
prefecture_no = fake.random_int(min=1, max=47)
sql += f"(UNHEX(REPLACE('{user_uuid}', '-', '')), '{user_name}', {prefecture_no}),\n"
sql = sql.rstrip(',\n') + ';'
print(sql)
sql = f"INSERT INTO `post` (`title`, `content`, `post_user_uuid`) VALUES"
for _ in range(POST_NUM):
title = fake.sentence(nb_words=2, variable_nb_words=True)
content = fake.text(max_nb_chars=500)
post_user_uuid = user_uuid_list[fake.random_int(min=0, max=USER_NUM-1)]
sql += f"('{title}', '{content}', UNHEX(REPLACE('{post_user_uuid}', '-', '')) ),\n"
sql = sql.rstrip(',\n') + ';'
print(sql)
sql = f"INSERT INTO `comment` (`post_id`, `content`, `comment_user_uuid`) VALUES"
for _ in range(COMMENT_NUM):
post_id = fake.random_int(min=1, max=POST_NUM)
content = fake.text(max_nb_chars=500)
comment_user_uuid = user_uuid_list[fake.random_int(min=0, max=USER_NUM-1)]
sql += f"('{post_id}', '{content}', UNHEX(REPLACE('{comment_user_uuid}', '-', '')) ),\n"
sql = sql.rstrip(',\n') + ';'
print(sql)
以下のように実行する。
pip install faker
python create_data.py > 01_dump.sql
テーブル作成後MySQLへインポートする。
mysql -uroot -ppassword dummy < 01_dump.sql
作成されたデータ
以下のようなデータが作成された。鈴木陽一さんは実在しそうと思い調べたらDuo3.0を書いている方だった。文章のほうは意味不明すぎて面白い。
mysql> select count(*) from user;
+
| count(*) |
+
| 1000 |
+
1 row in set (0.00 sec)
mysql> select * from user limit 1 \G
*************************** 1. row ***************************
user_uuid: 0x1DE9EA6670D34A1F8735DF5EF7697FB9
user_name: 鈴木 陽一
prefecture_no: 38
created: 2024-02-16 00:05:15
updated: 2024-02-16 00:05:15
1 row in set (0.00 sec)
mysql> select count(*) from post;
+
| count(*) |
+
| 10000 |
+
1 row in set (0.04 sec)
mysql> select * from post limit 1 \G
*************************** 1. row ***************************
post_id: 1
title: デフォルト。
content: 省略緩む状況ノート持つ狐意図。索引ヘア衝突リンク見落とす索引。
彼私柔らかい通行料金。品質状況追放する建築虐待衝突持つ。
クルー賞賛するじぶんの。文言追放する脊椎動物コーラス。
ヘア通行料金あなた自身ジャーナル私拡張。
叔父再現するコーラスリハビリ。ジャム暖かい副柔らかい立派なシュガー数字。腐ったハンマーハードウェア式連続編組文言。風景建築ジャーナル大統領敵マリン叔父差別する。
動物連続必要擁する屋根裏文言。
証言する叔父電話サワー。再現する私省略職人見出しリハビリテント。
カレッジ月トーンキャビネット副職人障害楽しんで。仕上げコーナージャム必要式催眠術ヒール。メニュー必要画面再現する。
コピー職人スマッシュトーンタワートリビュート本質的な普通の。持っていましたブレーキ感謝するチーズ目的。革新目的人形供給。出演者リフト衝突バーゲン建築バケツ舗装。
文言ボトル分割トーン擁する。雪ダイヤモンド省略持ってるログ。式器官コーラス雪。
バーゲンバスコピーデッドバケツ電池リフト。
午前クール評議会癌ノート主婦コンペ。革新雪障害君は。持っていました日曜日ヘア革新シュガーインチ符号バス。
post_user_uuid: 0xC640F5E86F8D4A9289E300CE9453F3B4
created: 2024-02-16 00:05:15
updated: 2024-02-16 00:05:15
1 row in set (0.00 sec)
mysql> select count(*) from comment;
+
| count(*) |
+
| 10000 |
+
1 row in set (0.04 sec)
mysql> select * from comment limit 1 \G
*************************** 1. row ***************************
comment_id: 1
post_id: 3101
content: 拡張スペル織る細かい。腐った行進戦略的葉キャビン。尊敬する倫理キャビンバスケット舗装。
学生コーラス供給あったリフトマリン。緩むトスアクセルペダルヘア。
カレッジブレーキ大統領発生するトレーナー。
参加するダイヤモンド協力雪スキーム電話。トレーナー緩む鉱山コーナー狭い職人供給。
戦略的学生風景フレーム舗装中央サワー必要。改善狐ブラケットブランチ偏差見落とす必要。
分割意図特徴サラダ欠乏サワーニュース見落とす。テント持っていました移動見落とすバケツスペル協力。
じぶんの器官尿副雪。主人不自然な創傷人形仕上げ。
ブレーキクールサワー。職人戦略的彼女動物チーズソース。腐った癌敵ストレージ倫理カレッジ。
協力私シェービング。溝仕上げ怒りヘア装置パーセントホイールパン。
建築本質的なハンマー高い柔らかい目的障害。職人それ今日符号ベルベットインチあなた自身。通行料金連続隠す。
サンプル教会タワー職人本質的な。創傷教授パイオニア持ってるピックバーゲン。
人形ログスペルバーゲンサワー分割今ログ。建築通行料金花嫁叔父。
目的持ってる目的倫理ジャムデフォルト。ハードウェアテント隠す呼ぶ。
comment_user_uuid: 0xF57DFD8A64B74E97BF78AC766F81F3EC
created: 2024-02-16 00:05:16
updated: 2024-02-16 00:05:16
1 row in set (0.00 sec)
データもうまく関連づけられているためJOINできた。
mysql> select * from user u join prefecture p on u.prefecture_no = p.prefecture_no left join post po
on po.post_user_uuid = u.user_uuid left join comment c on po.post_id = c.post_id limit 1 \G
*************************** 1. row ***************************
user_uuid: 0xEE18DFC62E964EF1BD46AD7E51AEFED8
user_name: 小川 直子
prefecture_no: 31
created: 2024-02-16 00:05:15
updated: 2024-02-16 00:05:15
prefecture_name: 鳥取
prefecture_no: 31
post_id: 85
title: 合計。
content: じぶんのログリフトダイヤモンドボトル出演者見出し柔らかい。創傷ソース障害大統領敵日曜日式。
自体バス陶器管理する不自然な出演者ヒット協力。今ハードウェアフレームヒット状況。電話近代化するパイオニア再現するサワー。
叔父分割保持するそれメニュー分割。意図楽しんで持つ指名バナートス。運彼野球学生。
パーセント差別する品質ピック欠乏。バケツトーン花嫁主婦発生する。
リハビリ癌見出しトレーナーフェミニストサラダヘア。評議会合計バケツバナー。ノート血まみれのベルベット発生する怒りフレームピックシュガー。
品質ヒール分割人形出演者。癌倫理ささやき叔父。戦略的保持する擁する器官。
細かいチーズ部隊ヘアスマッシュメニュー。インチ主婦雪倫理リニア教授。緩む楽しんで電話私虐待戦略的リハビリ。通行料金ブラケット陶器オークション差別する人形戦略的。
ストレージ符号状況主婦。ストレージ織る欠乏リフトデッド試してみるコピー。ヘアバナーバーゲン改善革新移動。
ヘア教会狐学生サンプル中世。午前尿それリンクブランチ移動自体。
君は知覚ダイヤモンドテント。奨励します管理する午前中央部隊。
post_user_uuid: 0xEE18DFC62E964EF1BD46AD7E51AEFED8
created: 2024-02-16 00:05:15
updated: 2024-02-16 00:05:15
comment_id: 47
post_id: 85
content: ない立派な省略今日器官屋根裏移動。副自体クロスバーゲン追放するアクセルペダル。バケツコーラス軸敵追放する状況。
運管理するマリン。立派な溝学生隠す敵対的な。指名柔らかいダイヤモンド敵対的な厳しい。
追放する月知覚助けてバナーホイール溝。はノートパイオニアコーラス主人中央。ブランチ革新副発生するバスケット。
埋め込む呼ぶ探査スペル。奨励します不自然な助けて連続タワー奨励します。アクセルペダルリニアカラム教授。通行料金不自然な省略埋め込む状況見出しリハビリ。
錯覚緩む緩む品質タワー創傷。通行料金不自然なメニューストレージ。普通の腐った欠乏戦略的尊敬するベルベットブラケット。目的コーナー呼ぶスペル運装置ホイール憲法。
狐教授編組ダッシュ教授ハードウェア。リハビリダッシュインチヘア隠すトースト器官怒り。
脊椎数字敵敵対的な。インチスキームホイール探査。創傷評議会憲法再現する。職人呼ぶ叔父教授高いシュガー。
溝アクセルペダル追放する。は厳しいインチ見出し式。サンプル教会ジャーナル行進厳しいログ呼ぶ。
comment_user_uuid: 0x04FA1276DC66488E918EEF8CB6E002A3
created: 2024-02-16 00:05:16
updated: 2024-02-16 00:05:16
1 row in set (0.00 sec)
Docker実行環境
MySQLはDocker上で動作させる。Dockerイメージをそのまま使うとbash上で日本語を使えないので日本語設定をする。
以下リポジトリにMySQL5.7と8.0で動作するようDocker設定含めてまとめた。
github.com