ただメモができるだけのブラウザアプリ「TextArea」はこちらw

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.7MySQL8.0
        登録直後インデックスサイズ23MB23MB
        登録直後selectパフォーマンス0.00 sec0.00 sec
        1回テーブル全件更新した際の
        パフォーマンス
        31.10 sec31.18 sec
        1回テーブル全件更新した後の
        インデックスサイズ
        52MB52MB
        1回テーブル全件更新した後の
        selectパフォーマンス
        0.01 sec0.01 sec
        3回テーブル全件更新した際の
        パフォーマンス
        1 min 54.21 sec

        →1回あたり約38秒

        1 min 55.57 sec

        →1回あたり約38秒

        3回テーブル全件更新した後の
        インデックスサイズ
        50MB50MB
        3回テーブル全件更新した後の
        selectパフォーマンス
        0.01 sec0.02 sec
        10回テーブル全件更新した際の
        パフォーマンス
        6 min 46.46 sec

        →1回あたり約40秒

        6 min 50.47 sec

        →1回あたり約41秒

        10回テーブル全件更新した後の
        インデックスサイズ
        50MB50MB
        10回テーブル全件更新した後の
        selectパフォーマンス
        0.01 sec0.01 sec
        OPTIMIZE TABLEした後の
        インデックスサイズ
        16MB16MB
        OPTIMIZE TABLEした後の
        selectパフォーマンス
        0.00 sec0.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:カウントを2で割った余りを格納(あまり意味がないカラムです。)
        • 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でインデックスを貼ったカラムを更新した場合のインデックスサイズとパフォーマンスへの影響を確認してみました。

        結果は以下の通りです!

        結果まとめ

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

         

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

        MySQL5.7と8.0のパフォーマンスを比較検証してみましたー
        MySQL 5.7と比べると、8.0の方が性能が良いという噂を簡単な検証で確認してみました。 結論としては、 8.0の方がINSERTは早い。 8.0の方がSELECTも早いけど、正しくインデックス貼られていれば同程度。 UPDATE、DELETEは同程度。
        スポンサーリンク
        MySQL
        フォローする
        さゆフィクション