MySQL5.7とMySQL8.0のパフォーマンスの違いを計測してみた

はじめに

MySQL8.0 を使ったユニットテストがどうにも遅いので、気になって計測してみた。特に Truncate が遅い気がしたので検証。
MySQL5.7(5.7.44)と MySQL8.0(8.0.28)で比較する。

検証コード

iwahara/mysql_performance: 記事用のパフォーマンス計測コード

検証用テーブル

検証に使うテーブル定義は以下の通り。主キーのみのテーブルと、index を１つ、２つ、３つ設定したテーブルを用意した。
照合順序は揃えてある。

CREATE TABLE `no_index` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL,
  `code1` varchar(8) NOT NULL,
  `code2` varchar(8) NOT NULL,
  `code3` varchar(8) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `index_1` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL,
  `code1` varchar(8) NOT NULL,
  `code2` varchar(8) NOT NULL,
  `code3` varchar(8) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `code1` (`code1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `index_2` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL,
  `code1` varchar(8) NOT NULL,
  `code2` varchar(8) NOT NULL,
  `code3` varchar(8) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `code1` (`code1`),
  KEY `code2` (`code2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `index_3` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL,
  `code1` varchar(8) NOT NULL,
  `code2` varchar(8) NOT NULL,
  `code3` varchar(8) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `code1` (`code1`),
  KEY `code2` (`code2`),
  KEY `code3` (`code3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

検証内容

それぞれのテーブルに対して以下の処理を 100 回繰り返し行った際の平均、最大、最小、中央、合計を算出する。
行う処理は以下の通り。

• Bulk Insert(1000 件)
• Select(1000 件)
• Truncate(1000 件)
• Truncate(データなし)
• Insert(1000 件)
• Delete(1000 件)
• Delete(データなし)

Bulk Insert(1000 件)

1000 件のデータを Bulk Insert を行う。遅いのは Bulk Insert 用の SQL を組み立てるのに時間がかかってるため。

傾向

30%程度の性能劣化が認められる。index が増えてもその傾向は変わらない。

MySQL5.7

タイトル	平均(秒)	最大(秒)	最小(秒)	中央(秒)	合計(秒)
bulk_insert_no_index	0.0231641889	0.0367610455	0.0213940144	0.0225814581	2.3164188862
bulk_insert_index_1	0.0254954004	0.0517539978	0.0232260227	0.0245360136	2.5495400429
bulk_insert_index_2	0.0284771204	0.1518249512	0.0254700184	0.0264970064	2.8477120399
bulk_insert_index_3	0.0292506337	0.0466861725	0.0272989273	0.0283349752	2.9250633717

MySQL8.0

タイトル	平均(秒)	最大(秒)	最小(秒)	中央(秒)	合計(秒)
bulk_insert_no_index	0.0315956211	0.0535829067	0.0270080566	0.0310341120	3.1595621109
bulk_insert_index_1	0.0344718528	0.1701998711	0.0291798115	0.0322824717	3.4471852779
bulk_insert_index_2	0.0356402326	0.0565741062	0.0307290554	0.0345799923	3.5640232563
bulk_insert_index_3	0.0375872278	0.0714590549	0.0324048996	0.0365456343	3.7587227821

Select(1000 件)

1000 件のデータを対象に検索を行う。全件取得(no_index)、主キー、index をそれぞれ使った検索を行い比較する。

傾向

10% - 20%程度の性能向上が認められる。全件検索が遅いのはデータ転送と思われる。

MySQL5.7

タイトル	平均(秒)	最大(秒)	最小(秒)	中央(秒)	合計(秒)
select_no_index	0.0016746187	0.0026111603	0.0015099049	0.0016205311	0.1674618721
select_pk_index	0.0003020883	0.0014519691	0.0002210140	0.0002760887	0.0302088261
select_index_1	0.0003040624	0.0006368160	0.0002198219	0.0002869368	0.0304062366
select_index_2	0.0003006864	0.0007510185	0.0002169609	0.0002901554	0.0300686359
select_index_3	0.0002978945	0.0005428791	0.0001568794	0.0002889633	0.0297894478

MySQL8.0

タイトル	平均(秒)	最大(秒)	最小(秒)	中央(秒)	合計(秒)
select_no_index	0.0015912366	0.0021889210	0.0013730526	0.0015374422	0.1591236591
select_pk_index	0.0002672482	0.0004720688	0.0002019405	0.0002520084	0.0267248154
select_index_1	0.0002675319	0.0004041195	0.0002031326	0.0002584457	0.0267531872
select_index_2	0.0002642131	0.0003681183	0.0001831055	0.0002579689	0.0264213085
select_index_3	0.0002632833	0.0004239082	0.0001838207	0.0002524853	0.0263283253

Truncate(1000 件)

1000 件のデータを対象に行う。MySQL8.0 から Truncate が Drop table -> create table になった影響がある。

傾向

170%程度の性能劣化が見られる。index が多くなればなるほど遅くなる傾向がある。

MySQL5.7

タイトル	平均(秒)	最大(秒)	最小(秒)	中央(秒)	合計(秒)
truncate_no_index	0.0069247794	0.0400118828	0.0053930283	0.0064899921	0.6924779415
truncate_index_1	0.0062697434	0.0305800438	0.0052719116	0.0058289766	0.6269743443
truncate_index_2	0.0063848376	0.0214231014	0.0052778721	0.0059005022	0.6384837627
truncate_index_3	0.0063020420	0.0137798786	0.0055360794	0.0059854984	0.6302042007

MySQL8.0

タイトル	平均(秒)	最大(秒)	最小(秒)	中央(秒)	合計(秒)
truncate_no_index	0.0208982396	0.0430920124	0.0166108608	0.0200840235	2.0898239613
truncate_index_1	0.0252788687	0.1356990337	0.0194070339	0.0233175755	2.5278868675
truncate_index_2	0.0270094562	0.0362949371	0.0223419666	0.0264205933	2.7009456158
truncate_index_3	0.0293176961	0.0441050529	0.0241057873	0.0286749601	2.9317696095

Truncate(データなし)

データがない場合の Truncate の検証を行う。

傾向

データありなしであまり差異はない。やっぱり MySQL8.0 は遅い。

MySQL5.7

タイトル	平均(秒)	最大(秒)	最小(秒)	中央(秒)	合計(秒)
truncate_no_index_no_data	0.0059074974	0.0224390030	0.0046651363	0.0055905581	0.5907497406
truncate_index_1_no_data	0.0059228110	0.0111019611	0.0048861504	0.0057380199	0.5922811031
truncate_index_2_no_data	0.0061133814	0.0122220516	0.0050880909	0.0058416128	0.6113381386
truncate_index_3_no_data	0.0064744711	0.0177650452	0.0050418377	0.0059884787	0.6474471092

MySQL8.0

タイトル	平均(秒)	最大(秒)	最小(秒)	中央(秒)	合計(秒)
truncate_no_index_no_data	0.0209189200	0.0354561806	0.0168840885	0.0205534697	2.0918920040
truncate_index_1_no_data	0.0245975399	0.0451631546	0.0191290379	0.0238614082	2.4597539902
truncate_index_2_no_data	0.0266360569	0.0374019146	0.0218529701	0.0256050825	2.6636056900
truncate_index_3_no_data	0.0294342709	0.0485591888	0.0241098404	0.0283745527	2.9434270859

Insert(1000 件)

1000 件の単純な Insert を行う。

傾向

若干 MySQL8.0 のほうが性能が悪い。

MySQL5.7

タイトル	平均(秒)	最大(秒)	最小(秒)	中央(秒)	合計(秒)
insert_no_index	0.0023774171	0.0033290386	0.0022161007	0.0023190975	0.2377417088
insert_index_1	0.0023774529	0.0032980442	0.0021870136	0.0023189783	0.2377452850
insert_index_2	0.0024431610	0.0035209656	0.0022211075	0.0023540258	0.2443161011
insert_index_3	0.0024940205	0.0036921501	0.0022621155	0.0024454594	0.2494020462

MySQL8.0

タイトル	平均(秒)	最大(秒)	最小(秒)	中央(秒)	合計(秒)
insert_no_index	0.0026536870	0.0041041374	0.0022859573	0.0025539398	0.2653687000
insert_index_1	0.0026141381	0.0041279793	0.0022709370	0.0025414228	0.2614138126
insert_index_2	0.0026466966	0.0045549870	0.0022540092	0.0025506020	0.2646696568
insert_index_3	0.0026832271	0.0045568943	0.0022499561	0.0025730133	0.2683227062

Delete(1000 件)

1000 件のデータを単純に全削除する。

傾向

3 倍から 4 倍程度の性能劣化が見られる。とても遅い。

MySQL5.7

タイトル	平均(秒)	最大(秒)	最小(秒)	中央(秒)	合計(秒)
delete_no_index	0.0002464366	0.0004069805	0.0001850128	0.0002374649	0.0246436596
delete_index_1	0.0002192831	0.0004470348	0.0001640320	0.0002074242	0.0219283104
delete_index_2	0.0002188396	0.0003190041	0.0001740456	0.0002110004	0.0218839645
delete_index_3	0.0002155471	0.0003519058	0.0001688004	0.0002090931	0.0215547085

MySQL8.0

タイトル	平均(秒)	最大(秒)	最小(秒)	中央(秒)	合計(秒)
delete_no_index	0.0009334326	0.0021967888	0.0007290840	0.0009105206	0.0933432579
delete_index_1	0.0008070946	0.0033020973	0.0005948544	0.0007630587	0.0807094574
delete_index_2	0.0007705402	0.0016009808	0.0005648136	0.0007508993	0.0770540237
delete_index_3	0.0007852602	0.0015149117	0.0005800724	0.0007640123	0.0785260201

Delete(データなし)

空のテーブルに対して delete を行う。

傾向

データがない場合はあまり変化はない。MySQL8.0 の方が若干性能劣化が見られる。

MySQL5.7

タイトル	平均(秒)	最大(秒)	最小(秒)	中央(秒)	合計(秒)
delete_no_index_no_data	0.0002146220	0.0004940033	0.0001549721	0.0002069473	0.0214622021
delete_index_1_no_data	0.0002131844	0.0003180504	0.0001609325	0.0002070665	0.0213184357
delete_index_2_no_data	0.0002178550	0.0004401207	0.0001499653	0.0002084970	0.0217854977
delete_index_3_no_data	0.0002088809	0.0003221035	0.0001280308	0.0002049208	0.0208880901

MySQL8.0

タイトル	平均(秒)	最大(秒)	最小(秒)	中央(秒)	合計(秒)
delete_no_index_no_data	0.0002569604	0.0004529953	0.0001869202	0.0002460480	0.0256960392
delete_index_1_no_data	0.0002568078	0.0004301071	0.0001559258	0.0002454519	0.0256807804
delete_index_2_no_data	0.0002508163	0.0004100800	0.0001389980	0.0002443790	0.0250816345
delete_index_3_no_data	0.0002520847	0.0004329681	0.0001609325	0.0002400875	0.0252084732