Mobile Factory Tech Blog

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

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

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

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

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

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

f:id:yokoi0803:20201214112843p:plain:h300

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

ポリモーフィック関連

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

f:id:yokoi0803:20201214113354p:plain:h350

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

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

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

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

f:id:yokoi0803:20201214113716p:plain:h300

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

親テーブルを用いた設計

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

f:id:yokoi0803:20201214113844p:plain:h400

今回のくじ引きのケースでは、くじ引きのくじそのものを表す 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 さんです。