この記事はモバイルファクトリー Advent Calendar 2020 15日目の記事です。

エンジニアの yokoi0803 です。DB設計をしていて多対一のリレーションを見たり、設計したりする機会が何度かあって、その度にどう設計するかで悩んでます。多対一のリレーションはいくつかの設計で実現できますが、その選定の際の指標を得るため、今回はパフォーマンスの観点から設計の比較をしてみたいと思います。

多対一のリレーションを表現する3つの設計

あるテーブルが複数のテーブルに対して多対一で紐付くケースについて、ここではくじ引きとその景品を表現するためのDB設計を想定します。箱の中のくじそれぞれに景品が設定されており、景品には旅行券や果物など、様々な種類のものがあります。

こういった仕様の設計手法にはいくつか種類がありますが、今回は「ポリモーフィック関連」、「交差テーブルを用いた設計」、「親テーブルを用いた設計」の3種類について取り上げることにします。まずはそれぞれどのような設計なのか、簡単に紹介していきます。

ポリモーフィック関連

どのテーブルのどのレコードに紐付くのか、という情報をテーブルに持たせる設計です。

prizes テーブルの target_type が「どのテーブルに紐付くか」、 target_id が「どのレコードに紐付くか」の情報を示します。

ポリモーフィック関連はSQLアンチパターンでも取り上げられているように、外部キー制約をつけることができないために紐付く対象のテーブルが保証されず、理由がない限り推奨される設計ではありません。

交差テーブルを用いた設計

紐付き先の種類ごとに関連情報だけを持たせたテーブル（交差テーブル）を用意する設計です。

prizes とその紐付き先である tickets、 fruits との間に、関連を示す交差テーブルがそれぞれ存在します。

親テーブルを用いた設計

紐付き元と紐付き先の全てのテーブルに共通の親テーブルを用意する設計です。

今回のくじ引きのケースでは、くじ引きのくじそのものを表す balls というテーブルを親として、全てのテーブルが紐付くように設計してみました。

パフォーマンスの比較

同じ仕様を実現する3種類の設計がありますが、どれを選択すれば良いでしょうか。「良い設計」についてはよく言及されていると思いますが、今回はそれについては考えず、パフォーマンスの観点から3種類の設計を比較してみたいと思います。

準備

今回は上の説明で取り上げた、くじ引きとその景品についてのDB設計をそのまま題材とします。

「ポリモーフィック関連」、「交差テーブルを用いた設計」、「親テーブルを用いた設計」の3種類で設計し、「紐付き先のデータ量の変化」および「紐付き先の種類数の変化」に対して検索パフォーマンスがどのように変化するかを確認します。

具体的には紐付き元である prizes テーブルから1,000件と、その紐付き先のレコードから情報を取得するまでの実行時間を計測しました。計測は Benchmark で10,000回試行し、結果としています。

マシンスペックについて

項目	値
OS	Ubuntu 16.04.7 LTS
CPU	Intel(R) Xeon(R) CPU E3-1220L V2 @ 2.30GHz ×4
メモリ	16GB

DB管理システムについて

MySQL5.6を利用している
計測時に発行されるクエリの全てにINDEXが使用されるように設定

クエリの実行計画を表示する

ポリモーフィック関連

EXPLAIN SELECT * FROM prizes
  LEFT JOIN fruits ON prizes.target_type = 'fruits' AND prizes.target_id = fruits.id
  LEFT JOIN tickets ON prizes.target_type = 'tickets' AND prizes.target_id = tickets.id
WHERE prizes.id IN (:prize_ids);

+----+-------------+---------+--------+---------------+---------+---------+----------------------------------------------+------+-------------+
| id | select_type | table   | type   | possible_keys | key     | key_len | ref                                          | rows | Extra       |
+----+-------------+---------+--------+---------------+---------+---------+----------------------------------------------+------+-------------+
|  1 | SIMPLE      | prizes  | range  | PRIMARY       | PRIMARY | 4       | NULL                                         |    9 | Using where |
|  1 | SIMPLE      | fruits  | eq_ref | PRIMARY       | PRIMARY | 4       | polymorphic_relation.prizes.target_id |    1 | Using where |
|  1 | SIMPLE      | tickets | eq_ref | PRIMARY       | PRIMARY | 4       | polymorphic_relation.prizes.target_id |    1 | Using where |
+----+-------------+---------+--------+---------------+---------+---------+----------------------------------------------+------+-------------+

交差テーブルを用いた設計

EXPLAIN SELECT * FROM prizes
  LEFT JOIN prizes_fruits ON prizes.id = prizes_fruits.prize_id
  LEFT JOIN fruits ON prizes_fruits.fruit_id = fruits.id
  LEFT JOIN prizes_tickets ON prizes.id = prizes_tickets.prize_id
  LEFT JOIN tickets ON prizes_tickets.ticket_id = tickets.id
  WHERE prizes.id IN (:prizes_ids);

+----+-------------+----------------+--------+---------------+---------+---------+-----------------------------------------------+------+-------------+
| id | select_type | table          | type   | possible_keys | key     | key_len | ref                                           | rows | Extra       |
+----+-------------+----------------+--------+---------------+---------+---------+-----------------------------------------------+------+-------------+
|  1 | SIMPLE      | prizes         | range  | PRIMARY       | PRIMARY | 4       | NULL                                          |    9 | Using where |
|  1 | SIMPLE      | prizes_fruits  | eq_ref | PRIMARY       | PRIMARY | 4       | cross_table_relation.prizes.id                |    1 | NULL        |
|  1 | SIMPLE      | fruits         | eq_ref | PRIMARY       | PRIMARY | 4       | cross_table_relation.prizes_fruits.fruit_id   |    1 | NULL        |
|  1 | SIMPLE      | prizes_tickets | eq_ref | PRIMARY       | PRIMARY | 4       | cross_table_relation.prizes.id                |    1 | NULL        |
|  1 | SIMPLE      | tickets        | eq_ref | PRIMARY       | PRIMARY | 4       | cross_table_relation.prizes_tickets.ticket_id |    1 | NULL        |
+----+-------------+----------------+--------+---------------+---------+---------+-----------------------------------------------+------+-------------+

親テーブルを用いた設計

EXPLAIN SELECT * FROM prizes
  LEFT JOIN tickets ON prizes.ball_id = tickets.ball_id
  LEFT JOIN fruits ON prizes.ball_id = fruits.ball_id
  WHERE prizes.id IN (:prize_ids);

+----+-------------+---------+-------+---------------+----------+---------+--------------------------------------+------+-------------+
| id | select_type | table   | type  | possible_keys | key      | key_len | ref                                  | rows | Extra       |
+----+-------------+---------+-------+---------------+----------+---------+--------------------------------------+------+-------------+
|  1 | SIMPLE      | prizes  | range | PRIMARY       | PRIMARY  | 4       | NULL                                 |    9 | Using where |
|  1 | SIMPLE      | tickets | ref   | ball_idx      | ball_idx | 4       | parent_table_relation.prizes.ball_id |    1 | NULL        |
|  1 | SIMPLE      | fruits  | ref   | ball_idx      | ball_idx | 4       | parent_table_relation.prizes.ball_id |    1 | NULL        |
+----+-------------+---------+-------+---------------+----------+---------+--------------------------------------+------+-------------+

設定ファイル (my.cnf) を表示する

[mysqld]
character-set-server = utf8

expire_logs_days = 1
max_binlog_size=300M
skip-name-resolve
wait_timeout = 10

log_error = /var/log/mysql/error.log

slow_query_log
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time=0.1

enforce_gtid_consistency

sql_mode = TRADITIONAL,NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY
binlog_format = ROW
binlog_row_image=minimal

query_cache_size=0
query_cache_type=0


#------------------------------------------------
## InnoDB
#------------------------------------------------
innodb_file_per_table
innodb_log_file_size = 2G
innodb_flush_method = O_DIRECT
innodb_open_files = 2000


#メモリ使用抑制
table_definition_cache=400

[mysqld_safe]
open_files_limit = 65535

スキーマ定義について

それぞれの設計でのスキーマ定義を掲載します。 tickets 関係のテーブルは fruits 関係のテーブルと同様になるので省略しています。

ポリモーフィック関連

[root@localhost] polymorphic_relation> show create table prizes\G
*************************** 1. row ***************************
       Table: prizes
Create Table: CREATE TABLE `prizes` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `rank` int(10) unsigned NOT NULL,
  `target_id` int(10) unsigned NOT NULL,
  `target_type` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `target_type_target_id_idx` (`target_type`,`target_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

[root@localhost] polymorphic_relation> show create table fruits\G
*************************** 1. row ***************************
       Table: fruits
Create Table: CREATE TABLE `fruits` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

交差テーブルを用いた設計

[root@localhost] cross_table_relation> show create table prizes\G
*************************** 1. row ***************************
       Table: prizes
Create Table: CREATE TABLE `prizes` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `rank` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

[root@localhost] cross_table_relation> show create table prizes_fruits\G
*************************** 1. row ***************************
       Table: prizes_fruits
Create Table: CREATE TABLE `prizes_fruits` (
  `prize_id` int(10) unsigned NOT NULL,
  `fruit_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`prize_id`),
  KEY `fruit_idx` (`fruit_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

[root@localhost] cross_table_relation> show create table fruits\G
*************************** 1. row ***************************
       Table: fruits
Create Table: CREATE TABLE `fruits` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

親テーブルを用いた設計

[root@localhost] parent_table_relation> show create table prizes\G
*************************** 1. row ***************************
       Table: prizes
Create Table: CREATE TABLE `prizes` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ball_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ball_idx` (`ball_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

[root@localhost] parent_table_relation> show create table balls\G
*************************** 1. row ***************************
       Table: balls
Create Table: CREATE TABLE `balls` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `color` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

[root@localhost] parent_table_relation> show create table fruits\G
*************************** 1. row ***************************
       Table: fruits
Create Table: CREATE TABLE `fruits` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ball_id` int(10) unsigned NOT NULL,
  `name` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ball_idx` (`ball_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

計測方法について

計測で用いたコードを掲載します。

use strict;
use warnings;
use utf8;

use DBI;
use Benchmark qw/timethese cmpthese/;

use constant +{
    PRIZE_COUNT => 1000,
    ITERATION   => 10000,
};

my $user = "root";
my $pass = "";

my $result = timethese(ITERATION, +{
    polymorphic => sub {
        my $dbh = DBI->connect(
            "dbi:mysql:database=polymorphic_relation;host=Localhost;port=3306",
            $user, $pass, +{ RootClass => 'DBIx::Sunny' },
        ) || die $DBI::errstr;

        my $sql = <<EOT;
SELECT * FROM prizes
  LEFT JOIN fruits ON prizes.target_type = 'fruits' AND prizes.target_id = fruits.id
  LEFT JOIN tickets ON prizes.target_type = 'tickets' AND prizes.target_id = tickets.id
WHERE prizes.id IN (:prize_ids);
EOT

        $dbh->select_all($sql, +{ prize_ids => [1..PRIZE_COUNT] });
    },
    cross_table => sub {
        my $dbh = DBI->connect(
            "dbi:mysql:database=cross_table_relation;host=Localhost;port=3306",
            $user, $pass, +{ RootClass => 'DBIx::Sunny' },
        ) || die $DBI::errstr;

        my $sql = <<EOT;
SELECT * FROM prizes
  LEFT JOIN prizes_fruits ON prizes.id = prizes_fruits.prize_id
  LEFT JOIN fruits ON prizes_fruits.fruit_id = fruits.id
  LEFT JOIN prizes_tickets ON prizes.id = prizes_tickets.prize_id
  LEFT JOIN tickets ON prizes_tickets.ticket_id = tickets.id
  WHERE prizes.id IN (:prize_ids);
EOT

        $dbh->select_all($sql, +{ prize_ids => [1..PRIZE_COUNT] });
    },
    parent_table => sub {
        my $dbh = DBI->connect(
            "dbi:mysql:database=parent_table_relation;host=Localhost;port=3306",
            $user, $pass, +{ RootClass => 'DBIx::Sunny' },
        ) || die $DBI::errstr;

        my $sql = <<EOT;
SELECT * FROM prizes
  LEFT JOIN tickets ON prizes.ball_id = tickets.ball_id
  LEFT JOIN fruits ON prizes.ball_id = fruits.ball_id
  WHERE prizes.id IN (:prize_ids);
EOT

        $dbh->select_all($sql, +{ prize_ids => [1..PRIZE_COUNT] });
    },
});

cmpthese $result;

計測

紐付き先のデータ量によるパフォーマンス変化の比較

紐付き先のテーブルのレコード数が変化することで、パフォーマンスがどのように変化するか計測します。紐付き先の種類は全て2つとし、レコード数はそれぞれ10件、1,000件、100,000件と変化させました。

結果を以下に示します。

レコード数10件

Benchmark: timing 10000 iterations of cross_table, parent_table, polymorphic...
cross_table: 87 wallclock secs (36.01 usr +  2.82 sys = 38.83 CPU) @ 257.53/s (n=10000)
parent_table: 81 wallclock secs (31.21 usr +  1.76 sys = 32.97 CPU) @ 303.31/s (n=10000)
polymorphic: 73 wallclock secs (35.58 usr +  2.55 sys = 38.13 CPU) @ 262.26/s (n=10000)
              Rate  cross_table  polymorphic parent_table
cross_table  258/s           --          -2%         -15%
polymorphic  262/s           2%           --         -14%
parent_table 303/s          18%          16%           --

レコード数1,000件

Benchmark: timing 10000 iterations of cross_table, parent_table, polymorphic...
cross_table: 90 wallclock secs (36.54 usr +  2.41 sys = 38.95 CPU) @ 256.74/s (n=10000)
parent_table: 88 wallclock secs (30.96 usr +  2.88 sys = 33.84 CPU) @ 295.51/s (n=10000)
polymorphic: 79 wallclock secs (36.63 usr +  1.97 sys = 38.60 CPU) @ 259.07/s (n=10000)
              Rate  cross_table  polymorphic parent_table
cross_table  257/s           --          -1%         -13%
polymorphic  259/s           1%           --         -12%
parent_table 296/s          15%          14%           --

レコード数100,000件

Benchmark: timing 10000 iterations of cross_table, parent_table, polymorphic...
cross_table: 91 wallclock secs (36.68 usr +  2.29 sys = 38.97 CPU) @ 256.61/s (n=10000)
parent_table: 101 wallclock secs (31.46 usr +  2.33 sys = 33.79 CPU) @ 295.95/s (n=10000)
polymorphic: 79 wallclock secs (36.45 usr +  1.52 sys = 37.97 CPU) @ 263.37/s (n=10000)
              Rate  cross_table  polymorphic parent_table
cross_table  257/s           --          -3%         -13%
polymorphic  263/s           3%           --         -11%
parent_table 296/s          15%          12%           --

考察

レコード数の変化に関して見ていくと、どの設計でもレコード数が少なくとも100,000件程度までであれば、パフォーマンスの劣化は見られませんでした。
各設計手法をパフォーマンスで比較すると、親テーブル > 交差テーブル ≒ ポリモーフィック関連となっています。
親テーブルに対して交差テーブルのパフォーマンスが低いのは、JOINするテーブルの数が関係していそうです。
親テーブルに対してポリモーフィック関連のパフォーマンスが低いのは、JOINする際の処理の差が関係しているのではないかと考えています。

紐付き先の種類数によるパフォーマンス変化の比較

紐付き先の種類数が変化することで、パフォーマンスがどのように変化するか計測します。紐付き先の種類は2個と10個のケースで比較し、レコード数は全て1,000件として固定しました。

紐付き先の種類数を10個に増やして計測した結果を以下に示します。

Benchmark: timing 10000 iterations of cross_table, parent_table, polymorphic...
cross_table: 188 wallclock secs (57.09 usr +  3.08 sys = 60.17 CPU) @ 166.20/s (n=10000)
parent_table: 163 wallclock secs (35.00 usr +  2.79 sys = 37.79 CPU) @ 264.62/s (n=10000)
polymorphic: 104 wallclock secs (38.80 usr +  2.74 sys = 41.54 CPU) @ 240.73/s (n=10000)
              Rate  cross_table  polymorphic parent_table
cross_table  166/s           --         -31%         -37%
polymorphic  241/s          45%           --          -9%
parent_table 265/s          59%          10%           --

考察

紐付き先が増えることでJOINするテーブルも増えるため、どの設計でもパフォーマンスの劣化を起こすようです。
紐付き先が増えるほどJOINするテーブルの数の差が交差テーブルと他2つの設計との間で開いていくため、パフォーマンスの差も顕著になっているのだと考えられます。

まとめ

多対一のリレーションを表現する設計として「ポリモーフィック関連」、「交差テーブルを用いた設計」、「親テーブルを用いた設計」をパフォーマンスの観点から比較した。
どの設計でも、紐付き先の種類が増えた場合、テーブル結合処理もその分増えていくためにパフォーマンスの劣化を起こす。
交差テーブルを用いた設計は、他2つの設計と比較して、紐付き先の種類が増えることに対するパフォーマンスの劣化度合いが大きい。

明日の記事は id:Dozi0116 さんです。

Mobile Factory Tech Blog

技術好きな方へ！モバイルファクトリーのエンジニアたちが楽しい技術話をお届けします！

多対一リレーションの設計手法によるパフォーマンスの違いを確認した

多対一のリレーションを表現する3つの設計

ポリモーフィック関連

交差テーブルを用いた設計

親テーブルを用いた設計

パフォーマンスの比較

準備

マシンスペックについて

DB管理システムについて

ポリモーフィック関連

交差テーブルを用いた設計

親テーブルを用いた設計

スキーマ定義について

ポリモーフィック関連

交差テーブルを用いた設計

親テーブルを用いた設計

計測方法について

計測

紐付き先のデータ量によるパフォーマンス変化の比較

レコード数10件

レコード数1,000件

レコード数100,000件

考察

紐付き先の種類数によるパフォーマンス変化の比較

考察

まとめ