インデックスショットガンで書き込み性能はどれくらい劣化するのか

この記事はミライトデザイン Advent Calendar 2021 の10日目の記事です。

本日も懲りずに乱入させていただきました。

昨日は@tkek321 さんのDockerについての記事でした。
１つ１つ丁寧に解説してくれている記事なので、ぜひご一読をおすすめします。
読んでね！

docker-laravel の Docker 構成をちゃんと理解する【docker-compose.yml 編】

本日はSQLアンチパターンのインデックスショットガンについて記事にさせていただきます。
名前がカッコイイからこの記事書きました。

インデックスショットガンって何？

インデックスショットガンとは、書籍：「SQLアンチパターン」で紹介されているアンチパターンの1つで、闇雲にインデックスを作成することを指しています。

「インデックスって性能改善にいいんでしょ？よくわからないけど、とにかくインデックスをいっぱい作っちゃえ。」というような状態です。

インデックスショットガンのデメリットって何？

インデックスを作成すると、INSERT, UPDATEなどの書き込み処理が遅くなります。
これはデータをINSERT, UPDATEする際にインデックスの更新処理が発生するためです。

闇雲にインデックスを作成することで、書き込み処理で無用なオーバーヘッドが発生し、処理性能が劣化する恐れがあります。¹

インデックスを作ることでどれくらい書き込み性能が落ちるか測定してみよう

では具体的にインデックスを作ることでどれくらい書き込み性能が落ちるか測定してみようと思います。
今回はMySQLとPostgreSQLをDocker環境で用意して測定を行っていきます。
Oracle？そんな金はない
SQL Server？MariaDB？知らんなあ

環境

Docker for Windows上でMySQL, PostgreSQLを起動して計測を行います。

MySQL: 8.0.26
PostgreSQL: 14.1

テーブル

計測に使用するテーブルを準備します。
（下記SQLはMySQL用で、PostgreSQLはDATETIME→TIMESTAMPにしています。）

CREATE TABLE test_tables (
  id INT NOT NULL PRIMARY KEY,
  column_01 VARCHAR(20) NOT NULL,
  column_02 INT NOT NULL,
  column_03 DATETIME NOT NULL,
  column_04 VARCHAR(20) NOT NULL,
  column_05 INT NOT NULL,
  column_06 DATETIME NOT NULL,
  column_07 VARCHAR(20) NOT NULL,
  column_08 INT NOT NULL,
  column_09 DATETIME NOT NULL,
  column_10 VARCHAR(20) NOT NULL,
  column_11 INT NOT NULL
);

上記のテーブルに対してインデックスを追加していき、インデックスを追加した個数と書き込み処理にかかる時間を見ていきます。

なお、MySQL Innodbの場合、1テーブルに対するセカンダリインデックス数の上限は64個となっています。

一方、PostgreSQLの場合、1テーブルに対するインデックス数の上限はありません。

今回はMySQLに合わせて、60個まで作成してインデックスを作成して計測をしていこうと思います。

計測するSQL

書き込み処理として、今回はINSERT, UPDATEの実行速度を計測していきます。
100万レコードのINSERT, UPDATEにかかる時間をそれぞれ見ていこうと思います。

MySQLとPostgreSQLで実行するSQLが多少異なりますが、内容はほぼ同じだと思っていただいて構いません。

MySQL

-- INSERT用のテーブル作成
CREATE TABLE numbers (`no` int DEFAULT NULL);
INSERT INTO numbers VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);

-- INSERT
INSERT INTO test_tables
SELECT
    CONCAT('C-', LPAD(@rownum := @rownum + 1, 8, '0')),
    @rownum,
    now(),
    CONCAT('C-', LPAD(@rownum, 8, '0')),
    @rownum,
    now(),
    CONCAT('C-', LPAD(@rownum, 8, '0')),
    @rownum,
    now(),
    CONCAT('C-', LPAD(@rownum, 8, '0')),
    @rownum
FROM
    numbers AS s1,
    numbers AS s2,
    numbers AS s3,
    numbers AS s4,
    numbers AS s5,
    numbers AS s6,
    (SELECT @rownum := 0) AS v
;

-- UPDATE
UPDATE test_tables SET
    column_01 = CONCAT('U-', id),
    column_02 = id,
    column_03 = now(),
    column_04 = CONCAT('U-', id),
    column_05 = id,
    column_06 = now(),
    column_07 = CONCAT('U-', id),
    column_08 = id,
    column_09 = now(),
    column_10 = CONCAT('U-', id),
    column_11 = id
;

PostgreSQL

-- INSERT用のテーブル作成
CREATE TABLE numbers (no int DEFAULT NULL);
INSERT INTO numbers VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);

-- INSERT
INSERT INTO test_tables
SELECT
    ROW_NUMBER() OVER (),
    'C-' || LPAD(CAST(ROW_NUMBER() OVER () AS character varying), 8, '0'),
    ROW_NUMBER() OVER (),
    now(),
    'C-' || LPAD(CAST(ROW_NUMBER() OVER () AS character varying), 8, '0'),
    ROW_NUMBER() OVER (),
    now(),
    'C-' || LPAD(CAST(ROW_NUMBER() OVER () AS character varying), 8, '0'),
    ROW_NUMBER() OVER (),
    now(),
    'C-' || LPAD(CAST(ROW_NUMBER() OVER () AS character varying), 8, '0'),
    ROW_NUMBER() OVER ()
FROM
    numbers AS s1,
    numbers AS s2,
    numbers AS s3,
    numbers AS s4,
    numbers AS s5,
    numbers AS s6
;

-- UPDATE
UPDATE test_tables SET
    column_01 = 'U-' || id,
    column_02 = id,
    column_03 = now(),
    column_04 = 'U-' || id,
    column_05 = id,
    column_06 = now(),
    column_07 = 'U-' || id,
    column_08 = id,
    column_09 = now(),
    column_10 = 'U-' || id,
    column_11 = id
;

計測結果

それでは計測結果の発表です🎺²
計測時間の単位は秒（s）になります。

	INDEX	0	1	2	3	4	5	10	20	30	40	50	60
MySQL	INSERT	14.32	16.51	19.6	21.28	26.36	33.99	42.59	85.54	100	146.64	182.13	253.49
MySQL	UPDATE	28.63	39.96	42.98	52.79	57.35	74.4	128.94	220.62	273.34	780.03	1046.79	1253.86
PosgreSQL	INSERT	4.28	5.36	5.93	6.61	8.06	8.54	13.17	26.03	35.59	45.27	56.63	68.75
PosgreSQL	UPDATE	5.71	8.68	9.63	10.11	12.81	14	22.58	52.32	70.13	90.45	116.32	140.81

グラフ化してみると下図のようになりました。

考察

上記の結果を受けて、自分なりの考察をしたいと思います。

• インデックス数が増えればINSERT, UPDATEどちらも実行時間がかかるようになった。
  • この結果は予想通りといえる。
• INSERT, UPDATEで比較すると、UPDATEのほうがインデックスの影響をより多く受けていた。
  • これはUPDATEで全カラムを更新していることの影響も大きいと思われる。更新するカラムを絞ればもう少し影響を小さくできそう。
• MySQL, PostgreSQLともにインデックスを4~5個追加するとINSERTが2倍, 10個追加すると3倍時間がかかるようになった。
  • 思った以上に影響が大きい印象なので、大量の書き込みをするテーブルに対してはインデックスの追加は慎重になる必要がありそう。³
• PostgreSQL速くね？
  • 私はPostgreSQLの回し者ではありません。

まとめ

今回はインデックスショットガンと書き込み性能の関係を、SQLの実行時間を計測することで見てきました。
いかがだったでしょうか。

自分としては思った以上にINSERT, UPDATEへの影響が大きいと感じました。
いままで野生のカンでインデックス追加してきたテーブルたちよ、スマヌ。。。

とはいえ、登録されたデータの参照を行う際に、インデックスを適切に使用することが非常に重要であることもまた確かです。

インデックスを追加する場合、闇雲に追加するのではなく「MENTOR」の原則⁴に従って必要最小限のインデックスを追加することが重要です（自分への戒め）。

明日はほげさんが記事を書いてくれるそうです。

いつもほげさんの記事で勉強させてもらっているので、どんな記事を書いてくれるのか自分も楽しみです。

参考

• SQLアンチパターン（インデックスショットガン）
• SQLで大量のテストデータ作成
• インデックスを作れば作るほど、INSERTが遅くなる
• パフォーマンスチューニング9つの技 ～「書き」について～｜PostgreSQLインサイド : 富士通

1. 他にも不要なインデックスを格納するためのディスク領域の浪費などもデメリットと言えるでしょう。 ↩

2. インデックス数はPrimary Keyを含めていません ↩

3. 大量の書き込みを行う場合、インデックスを外した状態でインポートを行い、全件取り込んだ後にインデックスを追加するという技もあります ↩

4. Measure（測定）, Explain（解析）, Nominate（指名）, Test（テスト）, Optimize（最適化）, Rebuild（再構築）の頭文字をとったもの。詳しくはSQLアンチパターンを読みましょう。 ↩