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を追加してみた②
☆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系への移行の際は影響範囲等の兼ね合いもあるため、移行の「即決」はできないが価値はありそうかなと。
今回は触れないが、移行する際にどのような苦難が待ち受けているかなどは、機会があるようであれば今後紹介したいなと思います。
