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를 클릭하면 이 인덱스에 대한 적절한 설명을 찾을 수 있습니다:

잘못된 인덱스 수: 1. `잘못된` 인덱스란 중복 키가 많은(전체 키의 90%) 인덱스와 동일한 키의 큰 그룹(전체 키의 30%)이 있는 인덱스를 의미합니다. 동일한 키의 큰 그룹은 가비지 컬렉션을 느리게 만듭니다 - 여기서 MaxEquals는 동일한 값을 가진 키 그룹의 최대 비율입니다. 이러한 인덱스에 대한 인덱스 검색은 효율적이지 않습니다. 이러한 인덱스는(FK 제약 조건에 없는 경우) 삭제할 수 있습니다.

인덱스(관계) 중복 MaxEquals TABLEIND1.IDX1 (TABLEIND1) : 100%, 50%

프로덕션 데이터베이스에서는 데이터베이스 성능에 큰 영향을 미칠 수 있는 잘못된 인덱스가 많이 있는 경우가 많습니다. 이 예에서는 1,300만 개의 레코드가 있는 테이블에 7개의 잘못된 인덱스가 있으며, 이는 (대부분) 쓸모가 없고 Firebird 성능을 크게 저하시킵니다:

HQbird Database Analyst가 인덱스를 «Useless»(즉, 값이 1개뿐인 경우)로 강조 표시하면 가능한 경우 즉시 삭제하는 것이 좋습니다.

인덱스가 Bad(값이 몇 개 있는 경우)로 강조 표시된 경우에도 삭제를 고려해야 하지만 주의가 필요합니다: 잘못된 인덱스가 특정 SQL 쿼리에서 사용되어 특정 인덱스 조합(잘못된 인덱스 포함)이 빠르게 실행되는 데 필요할 수 있기 때문입니다.

물론 이러한 SQL 쿼리는 잘못된 인덱스 없이 더 최적의 실행 계획을 사용하도록 다시 작성해야 합니다. 이러한 쿼리를 찾으려면 SQL 계획 감사가 수행되어야 합니다: 가장 쉬운 방법은 HQbird PerfMon으로 추적 세션을 실행하고 SQL 계획 로깅을 활성화한 다음 특정 잘못된 인덱스에 대한 SQL 계획을 검색하는 것입니다.