Firebird SQLにおけるインデックスがINSERT、UPDATE、DELETEのパフォーマンスに与える悪影響

一般的に、インデックスは本格的なデータベースには不可欠です。なぜなら、WHERE句（SELECT、UPDATE、MERGE、DELETEなど）を伴うクエリを高速化するために重要だからです。しかし、すべてのインデックスにはコストがかかり、そのコストは、インデックス付きフィールドとインデックスなしフィールドでのINSERT/UPDATE/DELETE操作の速度を比較すると明らかになります。

この記事では、一意のキーがいくつかあるインデックスが操作速度に与える影響を実証します。

テストデータベース

シンプルなFirebirdデータベースを作成しましょう：

Code

CREATE DATABASE “E:\TESTFIREBIRDINDEX.FDB” USER “SYSDBA” PASSWORD “masterkey”;
CREATE TABLE TABLEIND1 (
    I1            INTEGER NOT NULL PRIMARY KEY,
    NAME          VARCHAR(250),
    MANORWOMAN  SMALLINT
);
CREATE GENERATOR G1;
SET TERM ^ ;

create or alter procedure INS1MLN
returns (
    INSERTED_CNT integer)
as
BEGIN
inserted_cnt = 0;
WHILE (inserted_cnt <1000000) DO
BEGIN
 Insert into tableind1(i1, name, manorwoman) values(gen_id(g1,1), 'TEST name', (:inserted_cnt - (:inserted_cnt/2)*2));
 inserted_cnt=inserted_cnt+1;
END
suspend;
END^
SET TERM ; ^
GRANT INSERT ON TABLEIND1 TO PROCEDURE INS1MLN;
GRANT EXECUTE ON PROCEDURE INS1MLN TO SYSDBA;
COMMIT;

悪いインデックスの問題を実証するために、以下の操作を実行しましょう：

まず、テーブルに100万件のレコードを挿入します
次に、これらのレコードを更新します
その後、すべてのレコードを削除します
最後に、テーブルからSELECT count(*)を実行します

以下のコマンドが、説明した操作を実行します：

Code

set stat on;  /*enable display of statistics in isql*/
select * from ins1mln;
update tableind1 SET MANORWOMAN  = 3;
delete from tableind1;
select count(*) from tableind1;

実行して、結果を今後の分析のために保存しましょう。

その後、同じ構造の別のテーブルを作成し、MANORWOMAN列にインデックスを追加しましょう：

Code

CREATE INDEX TABLEIND1_IDX1 ON TABLEIND1 (MANORWOMAN);

上記のスクリプトでわかるように、この列には0または1の整数値のみを挿入します。このようなインデックスはSELECTクエリには役に立たず、理論上はすべてのデータベース開発者がこのような悪いインデックスを避けるべきですが（テーブル内の値の分布が不均衡な非常に特殊なケースを除く）、実際には一意の値が2つだけ、あるいは1つだけのインデックスが多数存在します。

次に、このインデックスを使用してスクリプトを繰り返し、結果を比較しましょう - 以下の表を参照してください：


MANORWOMANのインデックスなし	MANORWOMANのインデックスあり
SQL> set stat on; /show statistics/ SQL *> select from ins1mln; INSERTED_CNT ============ 1000000 Current memory = 10487216 Delta memory = 80560 Max memory = 12569996 Elapsed time= 13.33 sec Buffers = 2048 Reads = 0 Writes 18756 Fetches = 7833503 SQL > update tableind1 SET MANORWOMAN = 3; Current memory = 76551788 Delta memory = 66064572 Max memory = 111442520 Elapsed time= 15.04 sec Buffers = 2048 Reads = 16166 Writes 15852 Fetches = 6032307 SQL> delete from tableind1; Current memory = 76550240 Delta memory = -1548 Max memory = 111442520 Elapsed time= 3.27 sec Buffers = 2048 Reads = 16147 Writes 16006 Fetches = 5032277 SQL> select count() from tableind1; COUNT ============ 0 Current memory = 76552064 Delta memory = 1824 Max memory = 111442520 Elapsed time= 1.35* sec Buffers = 2048 Reads = 16021 Writes 1791 Fetches = 2032278	SQL> set stat on; /show statistics/ SQL> *select from ins1mln; INSERTED_CNT ============ 1000000 Current memory = 10484140 Delta memory = 75524 Max memory = 12569996 Elapsed time= 23.94 sec Buffers = 2048 Reads = 1 Writes 23942 Fetches = 11459599 SQL> update tableind1 SET MANORWOMAN = 3; Current memory = 76548712 Delta memory = 66064572 Max memory = 111439444 Elapsed time= 29.30 sec Buffers = 2048 Reads = 16167 Writes 19492 Fetches = 10035948 SQL> delete from tableind1; Current memory = 76547164 Delta memory = -1548 Max memory = 111439444 Elapsed time= 3.41 sec Buffers = 2048 Reads = 16147 Writes 15967 Fetches = 5032277 SQL> select count() from tableind1;* COUNT ============ 0 Current memory = 76548988 Delta memory = 1824 Max memory = 111439444 Elapsed time= 0.69 sec Buffers = 2048 Reads = 16021 Writes 1901 Fetches = 2032278

MANORWOMANのインデックスなし

MANORWOMANのインデックスあり

SQL> set stat on; /*show statistics*/
SQL > select * from ins1mln;
INSERTED_CNT
============
1000000
Current memory = 10487216
Delta memory = 80560
Max memory = 12569996
Elapsed time= 13.33 sec
Buffers = 2048
Reads = 0
Writes 18756
Fetches = 7833503
SQL > update tableind1 SET MANORWOMAN = 3;
Current memory = 76551788
Delta memory = 66064572
Max memory = 111442520
Elapsed time= 15.04 sec
Buffers = 2048
Reads = 16166
Writes 15852
Fetches = 6032307
SQL> delete from tableind1;
Current memory = 76550240
Delta memory = -1548
Max memory = 111442520
Elapsed time= 3.27 sec
Buffers = 2048
Reads = 16147
Writes 16006
Fetches = 5032277
SQL> select count(*) from tableind1;
COUNT
============
0
Current memory = 76552064
Delta memory = 1824
Max memory = 111442520
Elapsed time= 1.35 sec
Buffers = 2048
Reads = 16021
Writes 1791
Fetches = 2032278

SQL> set stat on; /*show statistics*/
SQL> select * from ins1mln;
INSERTED_CNT
============
1000000
Current memory = 10484140
Delta memory = 75524
Max memory = 12569996
Elapsed time= 23.94 sec
Buffers = 2048
Reads = 1
Writes 23942
Fetches = 11459599
SQL> update tableind1 SET MANORWOMAN = 3;
Current memory = 76548712
Delta memory = 66064572
Max memory = 111439444
Elapsed time= 29.30 sec
Buffers = 2048
Reads = 16167
Writes 19492
Fetches = 10035948
SQL> delete from tableind1;
Current memory = 76547164
Delta memory = -1548
Max memory = 111439444
Elapsed time= 3.41 sec
Buffers = 2048
Reads = 16147
Writes 15967
Fetches = 5032277
SQL> select count(*) from tableind1;
COUNT
============
0
Current memory = 76548988
Delta memory = 1824
Max memory = 111439444
Elapsed time= 0.69 sec
Buffers = 2048
Reads = 16021
Writes 1901
Fetches = 2032278

つまり、悪いインデックスは、挿入または更新時にパフォーマンスを約2倍低下させます。また、最適でないインデックスは、書き込み数とレコードフェッチ数を大幅に増加させることもわかります。

このサンプルデータベース（MANORWOMANの悪いインデックス付き）の統計を取得して、詳細を確認してみましょう。統計を収集するには、次のコマンドを実行します：

Code

gstat -r e:\testfirebirdindex.fdb > e:\teststat.txt

TABLEIND1テーブルとインデックスの統計セクションは興味深いものですが、そこからどのような有用な情報が得られるでしょうか？

Code

TABLEIND1 (128)
    Primary pointer page: 166, Index root page: 167
    Average record length: 0.00, total records: 1000000
    Average version length: 27.00, total versions: 1000000, max versions: 1
    Data pages: 16130, data page slots: 16130, average fill: 93%
    Fill distribution:
       0 - 19% = 1
      20 - 39% = 0
      40 - 59% = 0
      60 - 79% = 0
      80 - 99% = 16129

    Index RDB$PRIMARY1 (0)
      Depth: 3, leaf buckets: 1463, nodes: 1000000
      Average data length: 1.00, total dup: 0, max dup: 0
      Fill distribution:
           0 - 19% = 0
          20 - 39% = 0
          40 - 59% = 0
          60 - 79% = 1
          80 - 99% = 1462

    Index TABLEIND1_IDX1 (1)
      Depth: 3, leaf buckets: 2873, nodes: 2000000
      Average data length: 0.00, total dup: 1999997, max dup: 999999
      Fill distribution:
           0 - 19% = 0
          20 - 39% = 1
          40 - 59% = 1056
          60 - 79% = 0
          80 - 99% = 1816

表示された数値とパーセンテージ値の意味を理解するには、データベース統計の視覚的な解釈を提供するHQbird Database Analystを使用できます：

Reports/View recommendationsをクリックすると、このインデックスに関する適切な説明を見つけることができます：

Bad indices count: 1. By `bad` we name indices with many duplicate keys (90% of all keys) and big groups of equal keys (30% of all keys). Big groups of equal keys slowdown garbage collection - MaxEquals here is % of max groups of keys having equal values. Index search for such an index is not efficient. You can drop such indices (if they are not on FK constraints).

Index ( Relation) Duplicates MaxEquals TABLEIND1.IDX1 (TABLEIND1) : 100%, 50%

本番データベースでは、データベースのパフォーマンスに大きな影響を与える可能性のある悪いインデックスが多数見られることがよくあります。この例では、1300万件のレコードを持つテーブルに7つの悪いインデックスがあり、それらは（おそらく）役に立たず、Firebirdのパフォーマンスを大幅に低下させています：

HQbird Database Analystがインデックスを「Useless」（つまり、値が1つしかない）として強調表示した場合、そのようなインデックスは可能であればすぐに削除することをお勧めします。

インデックスがBad（値がいくつかある）として強調表示されている場合も、削除を検討すべきですが、注意が必要です：悪いインデックスが、特定のSQLクエリで使用されている可能性があり、そのクエリが高速に実行されるために特定のインデックスの組み合わせ（悪いインデックスを含む）を必要としている場合があります。

確かに、これらのSQLクエリは、悪いインデックスなしでより最適な実行プランを使用するように書き換えるべきです。そのようなクエリを見つけるには、SQLプランの監査を実行する必要があります：最も簡単な方法は、HQbird PerfMonでトレースセッションを実行し、SQLプランのロギングを有効にして、特定の悪いインデックスについてSQLプランを検索することです。