MySQLでインデックスを貼ったカラムを更新するとインデックスサイズが肥大化する

MySQLでインデックスを貼ったカラムを更新した場合のインデックスサイズとパフォーマンスへの影響を確認してみました。

確認した挙動

インデックスサイズ
パフォーマンス

Aurora MySQLを使い、MySQL5.7と8.0で、上記について確認しました！

確認した環境・条件

Aurora MySQLで確認
確認したMySQLのバージョンは5.7と8.0
テーブル内の全レコードについて、インデックスが貼られているカラムを更新

まずは、結果から記載していきます！

確認結果

テーブル内の全レコードについて、インデックスを貼ったカラムを更新した場合、以下の挙動となりました。

確認した挙動

MySQLバージョンでの違いはほとんど無し
インデックスサイズ
→インデックスサイズは2倍以上増加
SELECT・UPDATEのパフォーマンス
→多少劣化する
→UPDATEのパフォーマンスはアップデートする度に劣化していく
「OPTIMIZE TABLE」を実行すると、インデックスサイズは登録直後より小さくなり、パフォーマンスは登録直後と同等になる

結果詳細

結果の詳細です。

MySQLバージョンでの違いはほとんど無し

インデックスサイズ
- 1回目の更新時に、2倍以上に増加
  - その後の更新処理では、ほとんど変化なし
    - OPTIMIZE TABLEすると登録直後より小さくなる

selectパフォーマンス
- 1回目の更新後に、多少劣化
  - その後の更新処理では、ほとんど変化なし
    - OPTIMIZE TABLEすると登録直後と同程度になる
updateパフォーマンス
- updateする度に、多少劣化していく

以下、結果をまとめた表です！

	MySQL5.7	MySQL8.0
登録直後インデックスサイズ	23MB	23MB
登録直後selectパフォーマンス	0.00 sec	0.00 sec
1回テーブル全件更新した際のパフォーマンス	31.10 sec	31.18 sec
1回テーブル全件更新した後のインデックスサイズ	52MB	52MB
1回テーブル全件更新した後の selectパフォーマンス	0.01 sec	0.01 sec
3回テーブル全件更新した際のパフォーマンス	1 min 54.21 sec →1回あたり約38秒	1 min 55.57 sec →1回あたり約38秒
3回テーブル全件更新した後のインデックスサイズ	50MB	50MB
3回テーブル全件更新した後の selectパフォーマンス	0.01 sec	0.02 sec
10回テーブル全件更新した際のパフォーマンス	6 min 46.46 sec →1回あたり約40秒	6 min 50.47 sec →1回あたり約41秒
10回テーブル全件更新した後のインデックスサイズ	50MB	50MB
10回テーブル全件更新した後の selectパフォーマンス	0.01 sec	0.01 sec
OPTIMIZE TABLEした後のインデックスサイズ	16MB	16MB
OPTIMIZE TABLEした後の selectパフォーマンス	0.00 sec	0.00 sec

準備

挙動を確認するための準備をしていきます。

準備すること

テーブル作成
データ作成用プロシージャーの作成
データ更新用プロシージャーの作成

まず、テーブルを作成してきます！

テーブル作成

以下のテーブルを用意します。

create table user(
id INT AUTO_INCREMENT,
number INT,
type INT,
name VARCHAR(255),
PRIMARY KEY (id),
INDEX number_index (number)
);

データ作成用プロシージャー作成

テストデータはプロシージャーで作成します。

以下の通りデータ作成してくれるようになっています。

user.number：ランダムな数値を格納
user.type：カウントを２で割った余りを格納（あまり意味がないカラムです。）
user.name：「user_name_{カウント}」の値を格納

-- 区切り文字を「//」に変更する
DELIMITER //

-- make_sample_dataというテストデータ作成用プロシージャーを作成する

create procedure make_sample_data(in i int)
begin
  declare count int default 0;
  -- 繰り返し
  while count < i do
    set count = count + 1;
    INSERT INTO user (number, type, name) VALUES(CEIL(RAND() * 1000), MOD(count,2),CONCAT('usr_name_',count));
  end while;
end
//
-- 区切り文字を「;」に戻す
DELIMITER ;

データ更新用プロシージャー作成

テストデータはプロシージャーで更新します。

user.numberをランダムな数値で更新するようになっています。

-- 区切り文字を「//」に変更する
DELIMITER //

-- update_sample_dataというテストデータ更新用プロシージャーを作成する

create procedure update_sample_data(in i int)
begin
  declare count int default 0;
  -- 繰り返し
  while count < i do
    set count = count + 1;
    -- number + 1で全テーブルデータを書き換える 
    UPDATE user
    SET number = CEIL(RAND() * 1000);
  end while;
end
//
-- 区切り文字を「;」に戻す
DELIMITER ;

MySQL5.7で検証していきます！

MySQL5.7での検証

まずはバージョン確認から進めていきます。

バージョン確認


mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.12    |
+-----------+
1 row in set (0.00 sec)

100万件のテストデータ登録

◾️100万件インサート

mysql> call make_sample_data(1000000);
Query OK, 1 row affected (1 min 6.98 sec)

◾️インデックスサイズ確認

 mysql> SELECT stat_value, index_name, (stat_value * @@innodb_page_size) / 1024 / 1024 AS size_mb  FROM mysql.innodb_index_stats WHERE index_name = 'number_index' and stat_name = 'size';
+------------+--------------+-------------+
| stat_value | index_name   | size_mb     |
+------------+--------------+-------------+
|       1507 | number_index | 23.54687500 |
+------------+--------------+-------------+
1 row in set (0.00 sec)

◾️登録直後のSELECTパフォーマンス(インデックスが貼られたカラムで絞り込み)

mysql> select * from user where number = 100;
1033 rows in set (0.00 sec)

1回アップデート

◾️テーブル内の全レコードを1回更新

mysql> call update_sample_data(1);
Query OK, 998993 rows affected (31.10 sec)

◾️インデックスサイズ確認

mysql> SELECT stat_value, index_name, (stat_value * @@innodb_page_size) / 1024 / 1024 AS size_mb FROM mysql.innodb_index_stats WHERE index_name = 'number_index' and stat_name = 'size';
+------------+--------------+-------------+
| stat_value | index_name   | size_mb     |
+------------+--------------+-------------+
|       3349 | number_index | 52.32812500 |
+------------+--------------+-------------+
1 row in set (0.00 sec)

◾️登録直後のSELECTパフォーマンス(インデックスが貼られたカラムで絞り込み)

mysql> select * from user where number = 100;
985 rows in set (0.01 sec)

3回アップデート

◾️テーブル内の全レコードを3回更新

mysql> call update_sample_data(3);
Query OK, 998991 rows affected (1 min 54.21 sec)

◾️インデックスサイズ確認

mysql> SELECT stat_value, index_name, (stat_value * @@innodb_page_size) / 1024 / 1024 AS size_mb FROM mysql.innodb_index_stats WHERE index_name = 'number_index' and stat_name = 'size';
+------------+--------------+-------------+
| stat_value | index_name   | size_mb     |
+------------+--------------+-------------+
|       3216 | number_index | 50.25000000 |
+------------+--------------+-------------+
1 row in set (0.00 sec)

◾️登録直後のSELECTパフォーマンス(インデックスが貼られたカラムで絞り込み)

mysql> select * from user where number = 100;
1000 rows in set (0.01 sec)

10回アップデート

◾️テーブル内の全レコードを10回更新

mysql> call update_sample_data(10);
Query OK, 999013 rows affected (6 min 46.46 sec)

◾️インデックスサイズ確認

mysql> SELECT stat_value, index_name, (stat_value * @@innodb_page_size) / 1024 / 1024 AS size_mb FROM mysql.innodb_index_stats WHERE index_name = 'number_index' and stat_name = 'size';
+------------+--------------+-------------+
| stat_value | index_name   | size_mb     |
+------------+--------------+-------------+
|       3207 | number_index | 50.10937500 |
+------------+--------------+-------------+
1 row in set (0.00 sec)

◾️登録直後のSELECTパフォーマンス(インデックスが貼られたカラムで絞り込み)

mysql> select * from user where number = 100;
952 rows in set (0.01 sec)

OPTIMIZE TABLE する

◾️OPTIMIZE TABLEを実行する

OPTIMIZE TABLE user;

◾️インデックスサイズ確認

SELECT stat_value, index_name, (stat_value * @@innodb_page_size) / 1024 / 1024 AS size_mb FROM mysql.innodb_index_stats WHERE index_name = 'number_index' and stat_name = 'size';
+------------+--------------+-------------+
| stat_value | index_name   | size_mb     |
+------------+--------------+-------------+
|       1059 | number_index | 16.54687500 |
+------------+--------------+-------------+
1 row in set (0.00 sec)

◾️登録直後のSELECTパフォーマンス(インデックスが貼られたカラムで絞り込み)

mysql> select * from user where number = 100;
952 rows in set (0.00 sec)

MySQL8.0で検証していきます！

MySQL8.0での検証

まずはバージョン確認から進めていきます。

バージョン確認

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.26    |
+-----------+
1 row in set (0.02 sec)

100万件のテストデータ登録

◾️100万件インサート

mysql> call make_sample_data(1000000);
Query OK, 1 row affected (1 min 2.48 sec)

◾️インデックスサイズ確認

 mysql> SELECT stat_value, index_name, (stat_value * @@innodb_page_size) / 1024 / 1024 AS size_mb  FROM mysql.innodb_index_stats WHERE index_name = 'number_index' and stat_name = 'size';
+------------+--------------+-------------+
| stat_value | index_name   | size_mb     |
+------------+--------------+-------------+
|       1507 | number_index | 23.54687500 |
+------------+--------------+-------------+
1 row in set (0.00 sec)

◾️登録直後のSELECTパフォーマンス(インデックスが貼られたカラムで絞り込み)

mysql> select * from user where number = 100;
1031 rows in set (0.00 sec)

1回アップデート

◾️テーブル内の全レコードを1回更新

mysql> call update_sample_data(1);
Query OK, 999056 rows affected (31.18 sec)

◾️インデックスサイズ確認

mysql> SELECT stat_value, index_name, (stat_value * @@innodb_page_size) / 1024 / 1024 AS size_mb FROM mysql.innodb_index_stats WHERE index_name = 'number_index' and stat_name = 'size';
+------------+--------------+-------------+
| stat_value | index_name   | size_mb     |
+------------+--------------+-------------+
|       3365 | number_index | 52.57812500 |
+------------+--------------+-------------+
1 row in set (0.01 sec)

◾️登録直後のSELECTパフォーマンス(インデックスが貼られたカラムで絞り込み)

mysql> select * from user where number = 100;
937 rows in set (0.01 sec)

3回アップデート

◾️テーブル内の全レコードを3回更新

mysql> call update_sample_data(3);
Query OK, 999038 rows affected (1 min 55.57 sec)

◾️インデックスサイズ確認

mysql> SELECT stat_value, index_name, (stat_value * @@innodb_page_size) / 1024 / 1024 AS size_mb FROM mysql.innodb_index_stats WHERE index_name = 'number_index' and stat_name = 'size';
+------------+--------------+-------------+
| stat_value | index_name   | size_mb     |
+------------+--------------+-------------+
|       3211 | number_index | 50.17187500 |
+------------+--------------+-------------+
1 row in set (0.00 sec)

◾️登録直後のSELECTパフォーマンス(インデックスが貼られたカラムで絞り込み)

mysql> select * from user where number = 100;
1075 rows in set (0.02 sec)

10回アップデート

◾️テーブル内の全レコードを10回更新

mysql> call update_sample_data(10);
Query OK, 999025 rows affected (6 min 50.47 sec)

◾️インデックスサイズ確認

mysql> SELECT stat_value, index_name, (stat_value * @@innodb_page_size) / 1024 / 1024 AS size_mb FROM mysql.innodb_index_stats WHERE index_name = 'number_index' and stat_name = 'size';
+------------+--------------+-------------+
| stat_value | index_name   | size_mb     |
+------------+--------------+-------------+
|       3207 | number_index | 50.10937500 |
+------------+--------------+-------------+
1 row in set (0.00 sec)

◾️登録直後のSELECTパフォーマンス(インデックスが貼られたカラムで絞り込み)

mysql> select * from user where number = 100;
1026 rows in set (0.01 sec)

OPTIMIZE TABLE する

◾️OPTIMIZE TABLEを実行する

OPTIMIZE TABLE user;

◾️インデックスサイズ確認

SELECT stat_value, index_name, (stat_value * @@innodb_page_size) / 1024 / 1024 AS size_mb FROM mysql.innodb_index_stats WHERE index_name = 'number_index' and stat_name = 'size';
+------------+--------------+-------------+
| stat_value | index_name   | size_mb     |
+------------+--------------+-------------+
|       1059 | number_index | 16.54687500 |
+------------+--------------+-------------+
1 row in set (0.01 sec)

◾️登録直後のSELECTパフォーマンス(インデックスが貼られたカラムで絞り込み)

mysql> select * from user where number = 100;
1005 rows in set (0.00 sec)

まとめ

MySQLでインデックスを貼ったカラムを更新した場合のインデックスサイズとパフォーマンスへの影響を確認してみました。

結果は以下の通りです！

結果まとめ

MySQL5.7と8.0のパフォーマンス記事もありますので、よろしければ！

MySQL5.7と8.0のパフォーマンスを比較検証してみましたー

MySQL 5.7と比べると、8.0の方が性能が良いという噂を簡単な検証で確認してみました。結論としては、 8.0の方がINSERTは早い。 8.0の方がSELECTも早いけど、正しくインデックス貼られていれば同程度。 UPDATE、DELETEは同程度。