初めに
この記事は、MySQL Advent Calendar 2021のカレンダー | Advent Calendar 2021 - Qiitaのエントリーです。
↓2021年12月12日は、@tmtms さんの記事です。
目次
とある日
「良いクエリは正しい知識と設計に宿る」という言葉を皆さんは御存知でしょうか。
↓知らない方は下記の資料を御覧ください。
実際に使うSQLの書き方 徹底解説 / pgcon21j-tutorial - Speaker Deck
リレーショナルデータベースでは設計が重要だと思ってます。
悪い設計のテーブルでSQL操作するとかなり苦労する話を聞きます。
ということで、今回は悪い設計のテーブルだと良い設計と比べてどんな感じなのということを検証してみます。
良い設計とは？
この記事ではそこまでの範囲は説明しないで引用している資料や下記の書籍をおすすめします。
実際に使うSQLの書き方 徹底解説 / pgcon21j-tutorial - Speaker Deck
他にも紹介してたりします。
悪い設計の定義
はじめに悪い設計の定義について説明します。
ただ、あまり深く切り込むとそれ一冊で本が書けるのでここでは一点だけ軽く説明します。
この記事では下記を悪い設計の定義だとしておきます。
「非正規化のテーブル」
もちろん、非正規化で設計するほうが良い場合もありますが、それは正しい知識によって定義されているとして今回の場合は例外といたします。
今回使用するテーブル
MySQL公式が提供しているサンプルのworld_xデータベースのテーブルを使って検証します。
MySQL :: Other MySQL Documentation
- city
- country
- countryinfo
- countrylanguage
各テーブルのデータ
mysql> select * from city limit 5; +----+----------------+-------------+---------------+-------------------------+ | ID | Name | CountryCode | District | Info | +----+----------------+-------------+---------------+-------------------------+ | 1 | Kabul | AFG | Kabol | {"Population": 1780000} | | 2 | Qandahar | AFG | Qandahar | {"Population": 237500} | | 3 | Herat | AFG | Herat | {"Population": 186800} | | 4 | Mazar-e-Sharif | AFG | Balkh | {"Population": 127800} | | 5 | Amsterdam | NLD | Noord-Holland | {"Population": 731200} | +----+----------------+-------------+---------------+-------------------------+ 5 rows in set (0.00 sec) mysql> select * from country limit 5; +------+-------------+---------+-------+ | Code | Name | Capital | Code2 | +------+-------------+---------+-------+ | ABW | Aruba | 129 | AW | | AFG | Afghanistan | 1 | AF | | AGO | Angola | 56 | AO | | AIA | Anguilla | 62 | AI | | ALB | Albania | 34 | AL | +------+-------------+---------+-------+ 5 rows in set (0.00 sec) mysql> select * from countryinfo limit 5\G; *************************** 1. row *************************** doc: {"GNP": 828, "_id": "00005de917d80000000000000000", "Code": "ABW", "Name": "Aruba", "IndepYear": null, "geography": {"Region": "Caribbean", "Continent": "North America", "SurfaceArea": 193}, "government": {"HeadOfState": "Beatrix", "GovernmentForm": "Nonmetropolitan Territory of The Netherlands"}, "demographics": {"Population": 103000, "LifeExpectancy": 78.4000015258789}} _id: 0x30303030356465393137643830303030303030303030303030303030 _json_schema: {"type": "object"} *************************** 2. row *************************** doc: {"GNP": 5976, "_id": "00005de917d80000000000000001", "Code": "AFG", "Name": "Afghanistan", "IndepYear": 1919, "geography": {"Region": "Southern and Central Asia", "Continent": "Asia", "SurfaceArea": 652090}, "government": {"HeadOfState": "Mohammad Omar", "GovernmentForm": "Islamic Emirate"}, "demographics": {"Population": 22720000, "LifeExpectancy": 45.900001525878906}} _id: 0x30303030356465393137643830303030303030303030303030303031 _json_schema: {"type": "object"} *************************** 3. row *************************** doc: {"GNP": 6648, "_id": "00005de917d80000000000000002", "Code": "AGO", "Name": "Angola", "IndepYear": 1975, "geography": {"Region": "Central Africa", "Continent": "Africa", "SurfaceArea": 1246700}, "government": {"HeadOfState": "Jos� Eduardo dos Santos", "GovernmentForm": "Republic"}, "demographics": {"Population": 12878000, "LifeExpectancy": 38.29999923706055}} _id: 0x30303030356465393137643830303030303030303030303030303032 _json_schema: {"type": "object"} *************************** 4. row *************************** doc: {"GNP": 63.20000076293945, "_id": "00005de917d80000000000000003", "Code": "AIA", "Name": "Anguilla", "IndepYear": null, "geography": {"Region": "Caribbean", "Continent": "North America", "SurfaceArea": 96}, "government": {"HeadOfState": "Elizabeth II", "GovernmentForm": "Dependent Territory of the UK"}, "demographics": {"Population": 8000, "LifeExpectancy": 76.0999984741211}} _id: 0x30303030356465393137643830303030303030303030303030303033 _json_schema: {"type": "object"} *************************** 5. row *************************** doc: {"GNP": 3205, "_id": "00005de917d80000000000000004", "Code": "ALB", "Name": "Albania", "IndepYear": 1912, "geography": {"Region": "Southern Europe", "Continent": "Europe", "SurfaceArea": 28748}, "government": {"HeadOfState": "Rexhep Mejdani", "GovernmentForm": "Republic"}, "demographics": {"Population": 3401200, "LifeExpectancy": 71.5999984741211}} _id: 0x30303030356465393137643830303030303030303030303030303034 _json_schema: {"type": "object"} 5 rows in set (0.00 sec) mysql> select * from countrylanguage limit 5; +-------------+------------+------------+------------+ | CountryCode | Language | IsOfficial | Percentage | +-------------+------------+------------+------------+ | ABW | Dutch | T | 5.3 | | ABW | English | F | 9.5 | | ABW | Papiamento | F | 76.7 | | ABW | Spanish | F | 7.4 | | AFG | Balochi | F | 0.9 | +-------------+------------+------------+------------+ 5 rows in set (0.00 sec)
各テーブルデータ件数
mysql> select count(*) from city\G; *************************** 1. row *************************** count(*): 4079 1 row in set (0.00 sec) ERROR: No query specified mysql> select count(*) from countrylanguage\G; *************************** 1. row *************************** count(*): 984 1 row in set (0.00 sec) ERROR: No query specified mysql> select count(*) from countryinfo\G; *************************** 1. row *************************** count(*): 239 1 row in set (0.00 sec) ERROR: No query specified mysql> select count(*) from country\G; *************************** 1. row *************************** count(*): 239 1 row in set (0.00 sec)
テーブル合体編
上記で悪い設計を「非正規化のテーブル」と定義しました、非正規化を一言で言うならばテーブルが一つということです。
（ここでは正規化について触れると大変なので色々と割愛してます）
ということで、World_xのテーブルを一つのテーブルに集約します。
（ほんとに非正規化にすると色々と大変なので第1正規化までに留めてます）
設計
基本的に重複している列は排除して少しだけ名前変えて集めてます。
CREATE TABLE hello_worlds ( `ID` int NOT NULL AUTO_INCREMENT, `City_Name` char(35) NOT NULL DEFAULT '', `District` char(20) NOT NULL DEFAULT '', `Info` json DEFAULT NULL, `Country_Code` char(3) NOT NULL DEFAULT '', `Country_Name` char(52) NOT NULL DEFAULT '', `Capital` int DEFAULT NULL, `Code2` char(2) NOT NULL DEFAULT '', `Language` char(30) NOT NULL DEFAULT '', `IsOfficial` enum('T', 'F') NOT NULL DEFAULT 'F', `Percentage` decimal(4, 1) NOT NULL DEFAULT '0.0', `doc` json DEFAULT NULL, `_id` varbinary(32) GENERATED ALWAYS AS ( json_unquote(json_extract(`doc`, _utf8mb4 '$._id')) ) STORED NOT NULL, `_json_schema` json GENERATED ALWAYS AS (_utf8mb4 '{"type":"object"}') VIRTUAL, PRIMARY KEY (`ID`) );
各テーブルでNameの列があるので頭に識別子を追加してます。
City_NameとCountry_Nameとか。
データ挿入
いちから生成すると大変なので、すでにあるやつから取ってきます。
ID列にはAUTO_INCREMENTがあるので挿入対象列から排除しています。
あと、_idと_json_schemaはサンプルのSQL見てる感じデフォルトを使ってたので排除してます。
基本的にCountryCodeで結合してます。
INSERT INTO hello_worlds ( `City_Name`, `District`, `Info`, `Country_Code`, `Country_Name`, `Capital`, `Code2`, `Language`, `IsOfficial`, `Percentage`, `doc` ) SELECT city.name, District, Info, city.CountryCode, c.name AS country_code, capital, code2, Language, IsOfficial, percentage, doc FROM city INNER JOIN country c ON city.CountryCode = c.Code INNER JOIN countrylanguage c2 ON city.CountryCode = c2.CountryCode INNER JOIN countryinfo c3 ON JSON_UNQUOTE(JSON_EXTRACT(c3.doc, '$.Code')) = city.CountryCode;
集約結果
一つに集約するので、列が多くなりますね。
hello_worldsテーブル設計
mysql> show create table hello_worlds\G; *************************** 1. row *************************** Table: hello_worlds Create Table: CREATE TABLE `hello_worlds` ( `ID` int NOT NULL AUTO_INCREMENT, `City_Name` char(35) NOT NULL DEFAULT '', `District` char(20) NOT NULL DEFAULT '', `Info` json DEFAULT NULL, `Country_Code` char(3) NOT NULL DEFAULT '', `Country_Name` char(52) NOT NULL DEFAULT '', `Capital` int DEFAULT NULL, `Code2` char(2) NOT NULL DEFAULT '', `Language` char(30) NOT NULL DEFAULT '', `IsOfficial` enum('T','F') NOT NULL DEFAULT 'F', `Percentage` decimal(4,1) NOT NULL DEFAULT '0.0', `doc` json DEFAULT NULL, `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL, `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=32769 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)
hello_worldsテーブルデータ
mysql> select * from hello_worlds limit 2\G; *************************** 1. row *************************** ID: 1 City_Name: Kabul District: Kabol Info: {"Population": 1780000} Country_Code: AFG Country_Name: Afghanistan Capital: 1 Code2: AF Language: Balochi IsOfficial: F Percentage: 0.9 doc: {"GNP": 5976, "_id": "00005de917d80000000000000001", "Code": "AFG", "Name": "Afghanistan", "IndepYear": 1919, "geography": {"Region": "Southern and Central Asia", "Continent": "Asia", "SurfaceArea": 652090}, "government": {"HeadOfState": "Mohammad Omar", "GovernmentForm": "Islamic Emirate"}, "demographics": {"Population": 22720000, "LifeExpectancy": 45.900001525878906}} _id: 0x30303030356465393137643830303030303030303030303030303031 _json_schema: {"type": "object"} *************************** 2. row *************************** ID: 2 City_Name: Kabul District: Kabol Info: {"Population": 1780000} Country_Code: AFG Country_Name: Afghanistan Capital: 1 Code2: AF Language: Dari IsOfficial: T Percentage: 32.1 doc: {"GNP": 5976, "_id": "00005de917d80000000000000001", "Code": "AFG", "Name": "Afghanistan", "IndepYear": 1919, "geography": {"Region": "Southern and Central Asia", "Continent": "Asia", "SurfaceArea": 652090}, "government": {"HeadOfState": "Mohammad Omar", "GovernmentForm": "Islamic Emirate"}, "demographics": {"Population": 22720000, "LifeExpectancy": 45.900001525878906}} _id: 0x30303030356465393137643830303030303030303030303030303031 _json_schema: {"type": "object"} 2 rows in set (0.00 sec) ERROR: No query specified
hello_worldsテーブル件数
ここはかなり違いが現れてます。
集約する前の各テーブルの件数の合計を合わせても5,541行にしかなりません。
mysql> select count(*) from hello_worlds\G; *************************** 1. row *************************** count(*): 30671 1 row in set (0.00 sec)
データ挿入時は、適当にinner joinで結合しましたがかなりデータが増えました。
データ検索編
テーブル設計ではかなり行数が増えたり列数が増えたりと変化がしましたが、実際にSQLを実行する際にはどう変化するのか。
↓クエリの参考にさせていただきました。
MySQL のサンプルデータセット "world" データベース と "world_x" データベースの差とは？ - kakakakakku blog
東京都の Population（人口） を取得する
一行だけの結果でいいのでdistinctしてます。
mysql> SELECT DISTINCT Info->'$.Population' FROM hello_worlds WHERE Country_Code = 'JPN' AND City_Name = 'Tokyo'; +----------------------+ | Info->'$.Population' | +----------------------+ | 7980230 | +----------------------+ 1 row in set (0.02 sec) mysql> SELECT Info->'$.Population' FROM world_x.city WHERE CountryCode = 'JPN' AND Name = 'Tokyo'; +----------------------+ | Info->'$.Population' | +----------------------+ | 7980230 | +----------------------+ 1 row in set (0.00 sec) mysql> explain SELECT DISTINCT Info->'$.Population' FROM hello_worlds WHERE Country_Code = 'JPN' AND City_Name = 'Tokyo'; +----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+------------------------------+ | 1 | SIMPLE | hello_worlds | NULL | ALL | NULL | NULL | NULL | NULL | 29445 | 1.00 | Using where; Using temporary | +----+-------------+--------------+------------+------+---------------+------+---------+------+-------+----------+------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain SELECT Info->'$.Population' FROM world_x.city WHERE CountryCode = 'JPN' AND Name = 'Tokyo'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | city | NULL | ALL | NULL | NULL | NULL | NULL | 4079 | 1.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
日本の Region（地域） と Population（人口） を取得する
hello_worldsテーブルの方は、ただJapanをだけに絞って検索していますが。
もとのテーブルは結合を行っています。
なので、実行プランではeq_refが追加されてます。
mysql> SELECT DISTINCT doc->>'$.Code', doc->'$.demographics.Population', Capital FROM hello_worlds hw WHERE Country_Name = 'Japan'; +----------------+----------------------------------+---------+ | doc->>'$.Code' | doc->'$.demographics.Population' | Capital | +----------------+----------------------------------+---------+ | JPN | 126714000 | 1532 | +----------------+----------------------------------+---------+ 1 row in set (0.02 sec) mysql> SELECT ci.doc->'$.Code', ci.doc->'$.demographics.Population', c.Capital FROM world_x.country c INNER JOIN world_x.countryinfo ci ON c.Code = ci.doc->>'$.Code' WHERE c.Name = 'Japan'; +------------------+-------------------------------------+---------+ | ci.doc->'$.Code' | ci.doc->'$.demographics.Population' | Capital | +------------------+-------------------------------------+---------+ | "JPN" | 126714000 | 1532 | +------------------+-------------------------------------+---------+ 1 row in set (0.00 sec) mysql> explain SELECT DISTINCT doc->>'$.Code', doc->'$.demographics.Population', Capital FROM hello_worlds hw WHERE Country_Name = 'Japan'; +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+------------------------------+ | 1 | SIMPLE | hw | NULL | ALL | NULL | NULL | NULL | NULL | 29445 | 10.00 | Using where; Using temporary | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain SELECT ci.doc->'$.Code', ci.doc->'$.demographics.Population', c.Capital FROM world_x.country c INNER JOIN world_x.countryinfo ci ON c.Code = ci.doc->>'$.Code' WHERE c.Name = 'Japan'; +----+-------------+-------+------------+--------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | ci | NULL | ALL | NULL | NULL | NULL | NULL | 239 | 100.00 | NULL | | 1 | SIMPLE | c | NULL | eq_ref | PRIMARY | PRIMARY | 12 | func | 1 | 10.00 | Using where | +----+-------------+-------+------------+--------+---------------+---------+---------+------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)
街の数が多い上位10件
そもそも結果が違いますが。
hello_worldsテーブルはもっとgroup by の対象列を増やす必要がありますね。
mysql> select CountryCode ,count(*) from city group by CountryCode order by count(*) desc limit 10; +-------------+----------+ | CountryCode | count(*) | +-------------+----------+ | CHN | 363 | | IND | 341 | | USA | 274 | | BRA | 250 | | JPN | 248 | | RUS | 189 | | MEX | 173 | | PHL | 136 | | DEU | 93 | | IDN | 85 | +-------------+----------+ 10 rows in set (0.01 sec) mysql> select Country_Code ,COUNT(*) from hello_worlds hw group by Country_Code ORDER by COUNT(*) desc limit 10; +--------------+----------+ | Country_Code | COUNT(*) | +--------------+----------+ | CHN | 4356 | | IND | 4092 | | USA | 3288 | | RUS | 2268 | | JPN | 1488 | | PHL | 1360 | | BRA | 1250 | | MEX | 1038 | | IDN | 765 | | IRN | 670 | +--------------+----------+ 10 rows in set (0.02 sec) mysql> explain select CountryCode ,count(*) from city group by CountryCode order by count(*) desc limit 10; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ | 1 | SIMPLE | city | NULL | ALL | NULL | NULL | NULL | NULL | 4079 | 100.00 | Using temporary; Using filesort | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select Country_Code ,COUNT(*) from hello_worlds hw group by Country_Code ORDER by COUNT(*) desc limit 10; +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+ | 1 | SIMPLE | hw | NULL | ALL | NULL | NULL | NULL | NULL | 29445 | 100.00 | Using temporary; Using filesort | +----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+---------------------------------+ 1 row in set, 1 warning (0.00 sec)
まとめ
良くない設計にするとどうなるのか。
- 不要な行も検索するため全体的にクエリが遅くなる
- ほとんどインデックスが効かなくなる（今回は設定していないが....もとのテーブル構成だと外部キーでインデックスが使用される）
- データを検索する際に条件が複雑になるまたは行数の調整が必要となる。
- 各カラムの用途を把握するのが大変
以上、ある程度デメリットを上げましたが、本当に何も考えないでテーブルを作成するともっとひどくなるとは思います。
データベースの寿命は長いので新規に作成する場合はよく設計して構築しましょう。
〆
正規化を前提として設計をすると育ったので実際にしないとどうなるのかについて気になったので検証しました。
ただ、いい例となる効率の悪いテーブル構成やSQLが思いうかがなかったです。
次は2021年12月14日は@keny_lala さんの記事です。
