MySQL 8.0.14で全件 COUNT(*)でも加速しない！？- 「innodb_parallel_read_threads」検証その３

MySQL 8.0.14でSELECT COUNT(*)が加速する！- 「innodb_parallel_read_threads」検証その１ - なからなLife
MySQL 8.0.14でSELECT COUNT(*)が加速しない！- 「innodb_parallel_read_threads」検証その２ - なからなLife
の続きです。

まさかの、ネガティブ情報が続いてしまった。。。

パーティショニングの話をするつもりだったのですが

いろいろなパーティションを試しながら計測しつつ、そもそもパーティション前の数字ももう一度計測しておこう、と思ったら、今まで加速してたパターンで加速しないケースにぶつかってしまいました。

パラレル化の恩恵が出た（昨日までの）状態；PKのINT AUTO_INCREMENT欠番がそこそこある＋ALTER TABLE ENGINE INNODBしてない。
パラレル化の恩恵が出ない（今日の）状態：PKのINT AUTO_INCREMENT欠番がまったくない＋ALTER TABLE ENGINE INNODBした
この観点でパターン増やさないとまずそう。
— atsuizo (@atsuizo) January 25, 2019

こんな感じです。

環境

「その１」「その２」と基本的には同じですが、データを準備する手順に、「条件」で言及するパターンをもたせています。
バージョンはMySQL8.0.14のみです。

条件

PK連番の欠番の有無

大量テストデータ生成のテクニックとして、PK列がINT AUTO_INCREMENTなテーブルに

INSERT INTO table_name () VALUES ();
INSERT INTO table_name (itemid) SELECT 0 FROM table_name; -- 繰り返すたびに倍のレコードが生まれる

でPKだけのレコードを倍々で増やしていく、というのがあります。

ただしこれ、AUTO_INCREMENTなのに、大量に欠番を生み出すんです。
最初の10件だけでも、5、10、11、12、と欠番が発生します。

欠番をなくすためには、

一度上記の方で必要件数膨らませたテーブルを使って、同じ構造（AUTO INCREMENT列）をもつもう１つのテーブルにINSERTを SELECTする。
倍々生成のINSERT文を select PK列+1、select PK列+2、select PK列+4、と自分で計算しながら実行する。

という方法がとれるので、それを使います。

オプティマイズの有無

「ALTER TABLE table_name ENGINE INNODB」です。
InnoDB以外のテーブルに対して発行するとInnoDBに変換するコマンドですが、もともとInnoDBなテーブルに発行すると、物理ファイルの再構成が走り、実質デフラグが行われます。

ただしこれ、ちょっとくせがあるというか、意味不明な挙動をします。
PK昇順でキレイにINSERTした＝デフラグ不要な状態のときよりも、このコマンドを発行した後のほうが、ibdファイルが大きいのです。。。
未だその理由はわかってません。知っている限りMySQL5.6からMySQL8.0まで同じ挙動です。

パターン

上記の組み合わせで、こんなパターンとなりました。
これに、「innodb_parallel_read_threads」を1、4（デフォルト）、8、16で試しています。

PK連番に欠番有り＋オプティマイズなし
PK連番に欠番なし＋オプティマイズなし
PK連番に欠番有り＋オプティマイズあり
PK連番に欠番なし＋オプティマイズあり

検証結果

条件	ibd file size	innodb_parallel_read_threads	1回目（秒）	2回目（秒）	3回目（秒）
1	1971322880	16	7.06	6.74	6.65
		8	8.13	7.68	8.08
		4	12.43	11.51	12.31
		1	29.35	28.44	28.59
2	1971322880	16	7.01	6.69	7.12
		8	8.07	7.54	7.62
		4	11.77	12.43	12.27
		1	29.78	28.48	29.26
3	1639972864	16	25.48	23.93	24.66
		8	26.58	25.12	25.61
		4	26.94	25.31	27.08
		1	28.88	27.31	27.21
4	1635778560	16	23.53	22.14	22.97
		8	26.13	24.56	25.07
		4	26.36	24.95	25.14
		1	28.24	26.87	28.74