2019年9月

高速(?)なMySQL 8.0を検証してみた

2019年9月時点のMySQL最新バージョンは8.0.17(リリース日:2019/07/22)
業務ではMySQL 5.xを使うことが多いが、「mysql8」で検索をすると
『MySQL 8.0: MySQL 5.7よりも最大2倍高速』と大々的に出てくる。

実際にどれほど高速になっているのかは気にはなっていた(実際にアップデートをかけるのはいろいろと難しいorz)ので、
現行の最新であるMySQL8.0.17と5.7でどこまで差が出てくるのかをざっくり見てみたいと思う。

【検証環境】
Vagrant Ver 2.2.5
VirtualBox Ver 6.0.10
MySQL Ver 8.0.17
MySQL Ver 5.7.27

※デフォルト設定にて検証

■適応なDBとテーブル、200万程度のレコードを用意してみる


まずは適当に下ごしらえ

CREATE DATABASE log_db;
DROP TABLE IF EXISTS `log_db`.`user_l_action`;
CREATE TABLE `log_db`.user_l_action (
    `log_id`           bigint(20) unsigned not null auto_increment comment '履歴ID',
    `user_id`         int(11) unsigned not null comment 'ユーザID',
    `sex`               tinyint(1) unsigned not null comment '性別(0:未指定 1:男性 2:女性 3:それ以外)'
    `action_id`      int(11) unsigned not null comment '行動id',
    `create_date` date not null comment '作成日',
    `create_time` datetime not null comment '作成日時',
    PRIMARY KEY (log_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT 'ユーザ行動履歴';

insert into `log_db`.user_l_action (user_id,sex,action_id,create_date,create_time) values(228841,0,252,'2019-08-01','2019-08-01 00:00:00');
insert into `log_db`.user_l_action (user_id,sex,action_id,create_date,create_time) values(329246,2,699,'2019-08-01','2019-08-01 00:00:00');
insert into `log_db`.user_l_action (user_id,sex,action_id,create_date,create_time) values(456994,2,982,'2019-08-01','2019-08-01 00:00:00');
… and more…                                                    

こんな感じの適当なレコード用意

レッツ検証!白星(☆)黒星(★)で優劣をつけてみる

1. countしてみたら約3倍以上ものパフォーマンスの差が

★mysql Ver 5.7.27

mysql> select count(0) as record_count from log_db.user_l_action;
+--------------+
| record_count |
+--------------+
|        2077420 |
+--------------+
1 row in set (2.74 sec)

☆mysql Ver 8.0.17

mysql> select count(0) as record_count from log_db.user_l_action;
+--------------+
| record_count |
+--------------+
|        2077420 |
+--------------+
1 row in set (1.06 sec)

2回目以降の実行結果は以下のような結果に

2回目(sec) 3回目(sec) 4回目(sec) 5回目(sec) 平均(sec)
mysql 5.7 1.98 2.31 2.09 2.29 2.16
mysql 8.0 0.62 0.59 0.56 0.57 0.58

2. 特定のレコードをフルスキャンしてみた

8.0の方が優勢。

mysql> explain select * from log_db.user_l_action where action_id = 152;
+----+-------------+---------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | user_l_action | NULL | ALL | NULL | NULL | NULL | NULL | 2071994 | 10.00 | Using where |
+----+-------------+---------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

★mysql Ver 5.7.27

2172 rows in set (3.44 sec)

☆mysql Ver 8.0.17

2172 rows in set (3.11 sec)

2回目以降の実行結果は以下のような結果に

2回目(sec) 3回目(sec) 4回目(sec) 5回目(sec) 平均(sec)
mysql 5.7 3.27 3.30 3.38 3.33 3.32
mysql 8.0 3.05 3.07 3.06 3.07 3.06

3. indexを追加してみた①

alterは、5.7の方が優勢

☆mysql Ver 5.7.27

mysql> alter table log_db.user_l_action add index idx_act(action_id);
Query OK, 0 rows affected (16.38 sec)
Records: 0 Duplicates: 0 Warnings: 0

★mysql Ver 8.0.17

mysql> alter table log_db.user_l_action add index idx_act(action_id);
Query OK, 0 rows affected (19.17 sec)
Records: 0 Duplicates: 0 Warnings: 0

あまり差はなさそう

mysql> explain select * from log_db.user_l_action where action_id = 152;
+----+-------------+---------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | user_l_action | NULL | ref | idx_act | idx_act | 4 | const | 2172 | 100.00 | NULL |
+----+-------------+---------------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

★mysql Ver 5.7.27

2172 rows in set (0.17 sec)

☆mysql Ver 8.0.17

2172 rows in set (0.16 sec)

2回目以降の実行結果は以下のような結果に

2回目(sec) 3回目(sec) 4回目(sec) 5回目(sec) 平均(sec)
mysql 5.7 0.03 0.03 0.05 0.04 0.037
mysql 8.0 0.03 0.03 0.03 0.04 0.032

4. indexを追加してみた②

こちらも、alterは5.7の方が優勢

☆mysql Ver 5.7.27

mysql> alter table log_db.user_l_action add index idx_scd(sex,create_date);
Query OK, 0 rows affected (18.80 sec)
Records: 0 Duplicates: 0 Warnings: 0

★mysql Ver 8.0.17

mysql> alter table log_db.user_l_action add index idx_scd(sex,create_date);
Query OK, 0 rows affected (22.07 sec)
Records: 0 Duplicates: 0 Warnings: 0

group byでselectしてみた。8.0の方が優勢。

mysql> explain select create_date, sex, count(0) as create_cnt from log_db.user_l_action group by sex,create_date order by create_date asc, sex asc;
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+---------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+---------+----------+----------------------------------------------+
| 1 | SIMPLE | user_l_action | NULL | index | idx_scd | idx_scd | 4 | NULL | 2071994 | 100.00 | Using index; Using temporary; Using filesort |
+----+-------------+---------------+------------+-------+---------------+---------+---------+------+---------+----------+----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

★mysql Ver 5.7.27

68 rows in set (2.94 sec)

☆mysql Ver 8.0.17

68 rows in set (2.50 sec)

2回目以降の実行結果は以下のような結果に

2回目(sec) 3回目(sec) 4回目(sec) 5回目(sec) 平均(sec)
mysql 5.7 2.84 2.80 2.83 2.87 2.83
mysql 8.0 2.37 2.29 2.41 2.35 2.35

5. 範囲検索用にindexを追加。

8.0の方が優勢。

mysql> alter table log_db.user_l_action add index idx_cdatetime(create_time);
mysql> explain select user_id,sex,action_id, create_time from log_db.user_l_action where create_time between "2019-09-01 00:00:00" and "2019-09-01 23:59:59";
+----+-------------+---------------+------------+-------+---------------+---------------+---------+------+--------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+-------+---------------+---------------+---------+------+--------+----------+-----------------------+
| 1 | SIMPLE | user_l_action | NULL | range | idx_cdatetime | idx_cdatetime | 5 | NULL | 192764 | 100.00 | Using index condition |
+----+-------------+---------------+------------+-------+---------------+---------------+---------+------+--------+----------+-----------------------+
1 row in set, 1 warning (0.02 sec)

★mysql Ver 5.7.27

103871 rows in set (0.80 sec)

☆mysql Ver 8.0.17

103871 rows in set (0.71 sec)

2回目以降の実行結果は以下のような結果に

2回目(sec) 3回目(sec) 4回目(sec) 5回目(sec) 平均(sec)
mysql 5.7 0.67 0.74 0.74 0.69 0.71
mysql 8.0 0.65 0.68 0.66 0.63 0.66

6. ついでにdistinctしてみた

8.0の方が優勢。

mysql> select distinct(user_id),sex,action_id, create_time from log_db.user_l_action where create_time between "
2019-09-01 00:00:00" and "2019-09-01 23:59:59";

★mysql Ver 5.7.27

103871 rows in set (1.90 sec)

☆mysql Ver 8.0.17

103871 rows in set (1.13 sec)

2回目以降の実行結果は以下のような結果に

2回目(sec) 3回目(sec) 4回目(sec) 5回目(sec) 平均(sec)
mysql 5.7 1.33 1.33 1.34 1.29 1.32
mysql 8.0 1.00 0.98 0.95 0.98 0.97

7. 一応logデータなのでupdateするケースはほぼないが、試しにやってみた

26,101レコードの更新で5.7優勢の結果に

☆mysql Ver 5.7.27

mysql> update log_db.user_l_action set sex = 0 where sex = 3 and create_date = '2019-08:30';
Query OK, 26101 rows affected (3.18 sec)
Rows matched: 26101 Changed: 26101 Warnings: 0

★mysql Ver 8.0.17

mysql> update log_db.user_l_action set sex = 0 where sex = 3 and create_date = '2019-08:30';
Query OK, 26101 rows affected (4.82 sec)
Rows matched: 26101 Changed: 26101 Warnings: 0

8. 試しにsumしてみた。

8.0の方が優勢。

★mysql Ver 5.7.27

mysql> select sum(sex) as sum_test_full from log_db.user_l_action;
+---------------+
| sum_test_full |
+---------------+
|         3198555 |
+---------------+
1 row in set (2.55 sec)

mysql> select sum(sex) as sum_test_idx from log_db.user_l_action where create_time between "2019-09-01 00:00:00" and "2019-09-01 23:59:59";
+--------------+
| sum_test_idx |
+--------------+
|          156200 |
+--------------+
1 row in set (0.63 sec)

☆mysql Ver 8.0.17

mysql> select sum(sex) as sum_test_full from log_db.user_l_action;
+---------------+
| sum_test_full |
+---------------+
|         3198555 |
+---------------+
1 row in set (2.13 sec)

mysql> select sum(sex) as sum_test_idx from log_db.user_l_action where create_time between "2019-09-01 00:00:00" and "2019-09-01 23:59:59";
+--------------+
| sum_test_idx |
+--------------+
|          156200 |
+--------------+
1 row in set (0.54 sec)

2回目以降の実行結果は以下のような結果に

2回目(sec) 3回目(sec) 4回目(sec) 5回目(sec) 平均(sec)
mysql 5.7(full) 1.74 1.74 1.71 1.72 1.72
mysql 8.0(full) 1.72 1.69 1.70 1.68 1.69
mysql 5.7(idx) 0.50 0.51 0.50 0.50 0.50
mysql 8.0(idx) 0.44 0.46 0.44 0.44 0.44

9. 無理やりjoinもしてみた。

とりあえずのテスト用のテーブルを用意

DROP TABLE IF EXISTS mst_db.user_m_sex;
CREATE TABLE mst_db.user_m_sex (
  sex tinyint(1) unsigned not null comment '性別(0:未指定 1:男性 2:女性 3:それ以外)',
  sex_name varchar(50) not null default '' comment '性別名',
PRIMARY KEY (sex)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT 'ユーザ性別';

テスト用のテーブルとjoinしてみた。5.7の方が優勢。

☆mysql Ver 5.7.27

mysql> select log_id,sex, sex_name from log_db.user_l_action join mst_db.user_m_sex using(sex);
2077420 rows in set (5.07 sec)

mysql> select log_id,sex, sex_name from log_db.user_l_action join mst_db.user_m_sex using(sex) where create_time between "2019-09-01 00:00:00" and "2019-09-01 23:59:59";
103871 rows in set (2.04 sec)

★mysql Ver 8.0.17

mysql> select log_id,sex, sex_name from log_db.user_l_action join mst_db.user_m_sex using(sex);
2077420 rows in set (5.60 sec)

select log_id,sex, sex_name from log_db.user_l_action join mst_db.user_m_sex using(sex) where create_time between "2019-09-01 00:00:00" and "2019-09-01 23:59:59";
103871 rows in set (2.56 sec)

2回目以降の実行結果は以下のような結果に

2回目(sec) 3回目(sec) 4回目(sec) 5回目(sec) 平均(sec)
mysql 5.7(full) 4.93 4.71 4.74 4.81 4.79
mysql 8.0(full) 5.21 5.14 5.22 5.12 5.17
mysql 5.7(idx) 1.69 1.68 1.75 1.78 1.72
mysql 8.0(idx) 2.42 2.34 2.39 2.39 2.38

10. mysqlslapを使ってみた。

read・key・write・updateを測定。結果5.7が優勢であった

特にwrite・updateの差は顕著。

【read】

☆mysql Ver 5.7.27

$ mysqlslap --no-defaults --concurrency=10 iterations=5 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=read --auto-generate-sql-write-number=1000 --number-of-queries=1000 --user=root --password
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 1.649 seconds
Minimum number of seconds to run all queries: 1.649 seconds
Maximum number of seconds to run all queries: 1.649 seconds
Number of clients running queries: 10
Average number of queries per client: 100

★mysql Ver 8.0.17

$ mysqlslap --no-defaults --concurrency=10 iterations=5 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=read --auto-generate-sql-write-number=1000 --number-of-queries=1000 --user=root --password
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 2.037 seconds
Minimum number of seconds to run all queries: 2.037 seconds
Maximum number of seconds to run all queries: 2.037 seconds
Number of clients running queries: 10
Average number of queries per client: 100


【key】

☆mysql Ver 5.7.27

$ mysqlslap --no-defaults --concurrency=10 iterations=5 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key --auto-generate-sql-write-number=1000 --number-of-queries=1000 --user=root --password
Enter password:
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.113 seconds
Minimum number of seconds to run all queries: 0.113 seconds
Maximum number of seconds to run all queries: 0.113 seconds
Number of clients running queries: 10
Average number of queries per client: 100

★mysql Ver 8.0.17

$ mysqlslap --no-defaults --concurrency=10 iterations=5 --$ mysqlslap --no-defaults --concurrency=10 iterations=5 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=key --auto-generate-sql-write-number=1000 --number-of-queries=1000 --user=root --password
Enter password:
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.173 seconds
Minimum number of seconds to run all queries: 0.173 seconds
Maximum number of seconds to run all queries: 0.173 seconds
Number of clients running queries: 10
Average number of queries per client: 100


【write】

☆mysql Ver 5.7.27

$ mysqlslap --no-defaults --concurrency=10 iterations=5 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=write --auto-generate-sql-write-number=1000 --number-of-queries=1000 --user=root --password
Enter password:
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.534 seconds
Minimum number of seconds to run all queries: 0.534 seconds
Maximum number of seconds to run all queries: 0.534 seconds
Number of clients running queries: 10
Average number of queries per client: 100

★mysql Ver 8.0.17

$ mysqlslap --no-defaults --concurrency=10 iterations=5 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=write --auto-generate-sql-write-number=1000 --number-of-queries=1000 --user=root --password
Enter password:
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 1.238 seconds
Minimum number of seconds to run all queries: 1.238 seconds
Maximum number of seconds to run all queries: 1.238 seconds
Number of clients running queries: 10
Average number of queries per client: 100


【update】

☆mysql Ver 5.7.27

$ mysqlslap --no-defaults --concurrency=10 iterations=5 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=update --auto-generate-sql-write-number=1000 --number-of-queries=1000 --user=root --password
Enter password:
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.538 seconds
Minimum number of seconds to run all queries: 0.538 seconds
Maximum number of seconds to run all queries: 0.538 seconds
Number of clients running queries: 10
Average number of queries per client: 100

★mysql Ver 8.0.17

$ mysqlslap --no-defaults --concurrency=10 iterations=5 --engine=innodb --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=update --auto-generate-sql-write-number=1000 --number-of-queries=1000 --user=root --password
Enter password:
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 1.280 seconds
Minimum number of seconds to run all queries: 1.280 seconds
Maximum number of seconds to run all queries: 1.280 seconds
Number of clients running queries: 10
Average number of queries per client: 100

書き込み系のパフォーマンス差が目立っていたので、パラメータ「auto-generate-sql-write-number」が足を引っ張ている可能性が?(read/keyもパフォーマンスが低下しているのはこれが原因なのでは??)

ということで、「auto-generate-sql-write-number」を使用せず、検証で作成したsqlで並列検索結果を見てみる。

★mysql Ver 5.7.27

$ mysqlslap --concurrency=10 --iterations=5 --create-schema=log_db --query='select * from log_db.user_l_action where action_id = 152' --number-of-queries=1000 --user=root --passwordBenchmark
Average number of seconds to run all queries: 10.284 seconds
Minimum number of seconds to run all queries: 9.263 seconds
Maximum number of seconds to run all queries: 12.670 seconds
Number of clients running queries: 10
Average number of queries per client: 100

☆mysql Ver 8.0.17

$ mysqlslap --concurrency=10 --iterations=5 --create-schema=log_db --query='select * from log_db.user_l_action where action_id = 152' --number-of-queries=1000 --user=root --password
Enter password:
Benchmark
Average number of seconds to run all queries: 9.483 seconds
Minimum number of seconds to run all queries: 9.378 seconds
Maximum number of seconds to run all queries: 9.634 seconds
Number of clients running queries: 10
Average number of queries per client: 100

Averageは8.0優勢の結果に。

少なくとも8.0のインサートは5.7より遅いのではと

※デフォルト設定の場合

■総評

ざっくりの検証だけだが、
1. countのパフォーマンス改善はかなり好印象。他についてはそこまで大きな差はなさそうであった。
ただし、updateなど書き込みは遅くなった印象を受ける(検証ケースが少ないので断言はできないが、結構気になる結果に)
新規開発等でこれから環境構築するのであれば、とりあえずは「mysql8」を選択しておけば良いのかな…といった印象を受けました。
mysql 5系からmysql 8系への移行の際は影響範囲等の兼ね合いもあるため、移行の「即決」はできないが価値はありそうかなと。
今回は触れないが、移行する際にどのような苦難が待ち受けているかなどは、機会があるようであれば今後紹介したいなと思います。

2016/09/19Database
1