연락처
IBSurgeon
이 페이지는 기계 번역되었습니다. 영어 원본을 읽어보세요. English

IBSurgeon 라이브러리

15가지 Firebird 안티패턴

Alexey Kovyazin 작성, 2025년 1월 14일

소개

이 문서는 Firebird 데이터베이스 작업 시 흔히 발생하는 15가지 안티패턴과 각각에 대한 해결책을 설명합니다.

1. MON$에 대한 다중 병렬 쿼리

안티패턴: 매우 흔한 실수 - OnConnect 트리거에서 감사 목적으로 사용자 세부 정보를 조회하기 위해 MON$ATTACHMENTS에 쿼리를 실행하거나, 라이선스 목적으로 연결 수를 계산하는 경우입니다.

왜 나쁜가?

  • MON$ 테이블은 성능 통계 등이 저장된 fbNN_mon_xx 시스템 파일에 저장되는 가상 테이블입니다

  • 파일이 1Gb를 초과한다면 너무 많이 사용하고 있는 것입니다

  • 이들은 시스템 관리자 전용으로 설계되었습니다 - 즉, 관리자만을 위한 1-2개의 병렬 쿼리

  • MON$에 대한 병렬 쿼리가 있는 200개 이상의 연결은 Firebird를 매우 심각하게 느려지게 하며, 500개 이상의 동시 쿼리는 높은 확률로 Firebird를 “멈추게” 합니다

해결책:

  • 비관리 작업, 즉 계산이나 감사에 MON$를 사용하지 말고, OnConnect에서 사용을 피하십시오

  • 감사 목적:

  • CURRENT_USER, CURRENT_TIMESTAMP 등의 컨텍스트 변수 사용

  • 트리거보다 훨씬 강력한 Firebird 네이티브 기능인 Audit 사용

  • 라이선스 목적 - 사용자의 컨텍스트 변수 사용

2. 느린 대시보드 로딩

안티패턴: 애플리케이션 시작 시 지난 달 또는 연도의 모든 주문과 인보이스를 합산하는 종합 대시보드나 스코어보드를 로딩하거나, 매분 또는 그보다 더 자주 일부 지표를 업데이트하는 경우입니다.

sql 
SELECT
 SUM(total_sales) as yearly_sales,
 COUNT(DISTINCT customers) as customer_count,
 AVG(order_value) as avg_order_value
FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-01-01';

왜 나쁜가?

  • 사용자는 실제 업무를 시작하기 전에 회사 전체 통계를 보기 위해 몇 초를 기다려야 합니다

  • Firebird 관점에서 - 많은 병렬 쿼리를 지속적으로 실행하고, 대량의 데이터를 검색하고, 정렬/그룹화하기 위해 Firebird는 여러 CPU 코어를 집중적으로 사용하며, 디스크, 캐시, 정렬 전용 메모리에서 읽습니다 (때로는 정렬이 디스크로 넘어가기도 함)

  • 분당 여러 번 보고서를 작성하는 것과 같습니다!

해결책:

  1. 대시보드를 볼 사용자 수 줄이기:

  • 일반적으로 대시보드는 분석가와 경영진에게만 필요하므로 일반 애플리케이션 로드에서 제외

  • 시작 시/일부 폼에 대한 대시보드 로딩을 선택 사항으로 만들고 기본적으로 비활성화

  • 시작 시가 아닌 명시적 버튼 클릭으로 대시보드 데이터 로드 (즉, 보고서로 만들기)

  1. 일정에 따라 1개의 프로세스(즉, 로봇)로 대시보드 데이터를 계산하고 간단한 쿼리로 검색할 수 있는 간단한 테이블에 저장

  2. 트리거를 사용하여 데이터를 집계하고 사용 준비 상태로 저장

  3. 복제 데이터베이스를 사용하여 대시보드 데이터(및 모든 무거운 보고서) 계산

3. 불필요한 레코드 로딩

안티패턴: 수십만 개의 레코드가 포함되어 있는지 여부에 관계없이 애플리케이션이나 폼을 열 때 필터링 없이 모든 데이터를 그리드에 로딩하는 경우입니다.

delphi 
procedure TDataForm.LoadAllRecords;
begin
 FDQuery1.SQL.Text := 'SELECT * FROM large_table';
 FDQuery1.Open;
 // Loads entire table into memory
 DBGrid1.DataSource.DataSet := FDQuery1;
end;

왜 나쁜가?

  • 그리드에 50개의 레코드만 표시되더라도 사용자는 검색 기능을 사용하는 대신 수천 개의 레코드를 스크롤해야 합니다

  • 99%의 경우 사용자는 매우 좁은 데이터 하위 집합(예: 가장 최근의 판매 레코드)을 필요로 합니다

  • Firebird 관점에서:

  • 열 때마다 수천 개의 레코드를 읽고, 캐시에 저장하고, 네트워크를 통해 전송해야 합니다

  • (Delphi에서) 데이터셋을 열어두면 Firebird는 데이터셋이 닫힐 때까지 버퍼, 정렬된 레코드를 임시 공간에 유지합니다 (ORDER BY, GROUP BY 등이 있는 경우)

해결책:

  1. FIRST/SKIP/ROWS로 레코드 수 제한

  2. 일부 기준으로 레코드 수 제한, 예를 들어 최근 3일 동안 생성/변경된 레코드만 표시

  3. 일반적으로 쿼리를 가능한 한 빨리 닫기

4. 스크롤 시 과도한 쿼리

안티패턴: 스크롤 이벤트에서 쿼리를 실행하는 경우입니다. 예를 들어 그리드나 테이블에 데이터를 표시할 때 각 레코드에 대해 별도의 쿼리를 수행하거나, 지연 없이 2개의 그리드에서 마스터-디테일 스크롤의 고전적인 예를 사용하는 경우입니다.

delphi 
procedure TForm1.GridScrolled(Sender: TObject);
begin
 // query for each row
 FDQuery2.SQL.Text :=
 'SELECT additional_info FROM details ' +
 'WHERE id = ' + IntToStr(CurrentRowId);
 FDQuery2.Open;
end;

왜 나쁜가?

  • 동적 그리드에서 각 레코드에 대해 별도의 쿼리를 수행하면 Firebird가 수천 개의 작은 쿼리를 처리해야 하므로 CPU 리소스가 불필요하게 소모됩니다

  • Firebird 관점에서:

  • 많은(초당 수천 개) 작은 쿼리는 상당한 CPU 부하를 생성합니다. 쿼리가 통계에서 0ms를 표시하더라도 준비, 실행, 결과 전송 등이 필요하기 때문입니다

해결책:

  1. 배치 작업을 사용하여 여러 행을 한 번에 로드

  2. 그리드의 기본 쿼리를 개선하여 상세 쿼리를 그 일부로 실행

  3. 그리드의 보이는 부분에 대한 세부 정보를 로드하는 명시적 버튼 추가

  4. 세부 정보를 받기 위한 쿼리 실행에 지연을 추가하여 스크롤 중 즉각적인 쿼리 방지

  5. 모든 사용자에 대해 기본적으로 스크롤 시 세부 정보 로딩을 활성화하지 않기

5. 불필요한 자동 새로 고침

안티패턴: 모든 클라이언트 애플리케이션에서 최소 간격으로 그리드 데이터를 자동으로 새로 고치고, 이 기능이 기본적으로 활성화되어 있는 경우입니다.

왜 나쁜가?

  • 이로 인해 수백 개의 클라이언트 연결이 동일한 레코드를 검색하기 위해 거의 동일한 쿼리를 실행하게 됩니다

  • 발생하는 곳: 일정에 대한 자동 새로 고침, 대기열 위치 조회, “가장 가까운 슬롯” 검색 등

  • Firebird 관점에서:

  • 대시보드 로딩과 스크롤 이벤트의 조합: 많은 중간 규모 쿼리가 시스템에 부하를 생성합니다

해결책:

  1. 간격을 늘리십시오!

  2. 명시적(사용자 트리거) 새로 고침 구현

  3. 실제 데이터 변경에 기반한 데이터 세트의 선택적 새로 고침 사용 (스트리밍 또는 트리거 또는 이벤트+스트리밍)

6. 빈번한 레코드 업데이트

안티패턴: 서로 다른 트랜잭션에서 동일한 레코드를 자주 업데이트하여 수많은 레코드 버전을 생성하는 경우입니다.

왜 나쁜가?

  • 수십 개의 버전이 있는 레코드는 성능을 크게 저하시킬 수 있으며, 수천 개의 버전이 있는 레코드는 블로커가 될 수 있습니다

  • Firebird 관점에서: 특정 트랜잭션의 적절한 버전을 식별하기 위해 레코드 버전 체인을 재구성해야 하며, 이는 수많은 읽기 작업을 필요로 하므로 결과적으로 가비지 컬렉션이 상당히 느려집니다.

해결책:

  1. Firebird 4+로 마이그레이션, 중간 가비지 컬렉션이 있습니다

  2. 장기 실행 쓰기 가능 트랜잭션을 유지하지 말고 적절한 가비지 컬렉션 수행

  3. Firebird <4의 경우 UPDATE 대신 DELETE+INSERT 사용 고려

7. 읽기 전용 SELECT에 쓰기 트랜잭션 사용

안티패턴: 읽기 전용 SELECT에 쓰기 트랜잭션을 사용하면 과도한 작업이 발생합니다.

왜 나쁜가?

  • 읽기 전용 SELECT에 쓰기 트랜잭션을 사용하면 헤더 페이지에 불필요한 쓰기가 많이 발생합니다

  • 읽기 전용 작업에 쓰기 트랜잭션을 사용하는 것은 비효율적입니다 (커밋 시 큰 TIP가 서버에 추가 부하를 생성)

해결책:

  • 데이터를 변경하지 않는 작업에는 별도의 읽기 전용 트랜잭션 사용

  • Firebird는 단일 연결 내에서 여러 트랜잭션을 열 수 있는 몇 안 되는 데이터베이스 중 하나입니다

  • Global Temporary 테이블은 읽기 전용 트랜잭션에서 사용할 수 있습니다

8. LIKE :param 사용

매개변수가 있는 다음 쿼리는 fieldName에 대한 인덱스를 사용하지 않습니다 (인덱스가 존재하더라도):

sql 
SELECT * FROM Table1 WHERE fieldName LIKE :param1

왜 나쁜가?

LIKE는 임의의 수의 기호를 대체할 수 있는 와일드카드 검색(%)을 허용하므로, Firebird는 매개변수 값이 사전에 인덱스 검색에 적합한지 판단할 수 없습니다.

일반적으로 개발자들은 쿼리 텍스트에 매개변수 값을 포함시켜 이 문제를 우회하려고 합니다:

  • fieldName LIKE «Alex%» - 인덱스 사용 가능

  • fieldName LIKE «%Alex» - 표준 인덱스를 사용할 수 없음

  • fieldName LIKE «%Alex%» - 인덱스를 전혀 사용할 수 없음

이는 다른 문제들로 이어집니다(아래 #10 참조).

해결책:

1. 알려진 문자열 접두사에는 STARTING WITH 사용

검색 값이 와일드카드 %로 시작하지 않는 경우, LIKE 대신 STARTING WITH를 사용하세요:

sql 
WHERE fieldName STARTING WITH ?param1

2. 양방향 문자열 검색 최적화

접두사 또는 접미사 패턴이 알려진 문자열의 경우, 역방향 인덱스를 사용하세요:

sql 
-- 역방향 인덱스 생성
CREATE INDEX ixreverse1 ON TABLE1 COMPUTED BY (REVERSE(fieldName));
-- 양방향을 사용한 쿼리
WHERE fieldName STARTING WITH :param1
   OR reverse(fieldName) STARTING WITH reverse(:param2)

3. 점진적 검색 전략 구현

문자열이 시작/끝/중간에 나타나는 경우(동시에는 아님):

  • 먼저 STARTING WITH를 사용한 빠른 인덱스 검색을 시도

  • 결과가 없으면 느린 LIKE 검색으로 대체

4. 단어 기반 검색 최적화

완전한 단어(공백, 쉼표 등으로 구분)를 검색하는 경우:

  • 별도의 단어-ID 매핑 테이블 생성

  • 원본 텍스트 대신 매핑 테이블을 통해 검색

5. 포괄적인 전체 텍스트 검색 기능을 위해서는:

  • IBSurgeon Full Text Search UDR 사용 고려

  • 이 오픈소스 솔루션은 고급 텍스트 검색 기능을 제공합니다

9. 읽기 전용 작업에 대한 트랜잭션 미종료

왜 나쁜가?

  • 트랜잭션을 장시간 열어두면 Firebird가 잠재적인 스냅샷 트랜잭션을 위해 수많은 백 버전을 유지해야 할 수 있음

해결책:

  • 가능한 경우 읽기 전용 트랜잭션을 사용하고, 쓰기 가능 트랜잭션은 가능한 한 빨리 종료

  • 레코드 버전 체인의 영향을 줄이기 위해 최신 Firebird 버전(4+) 사용

  • 적절한 sweep 구현

10. 쿼리 매개변수화 문제

안티패턴: 준비된 쿼리와 매개변수화를 피하고, 대신 매개변수 값을 쿼리 텍스트에 직접 포함시키는 것.

delphi 
FDQuery1.SQL.Text :=
 'SELECT * FROM users WHERE name = ''' +
 EditUsername.Text + '''';
FDQuery1.Open;

왜 나쁜가?

  • 이 관행은 반복 쿼리의 성능을 저하시킴

  • 매개변수 값이 포함된 모든 쿼리는 새로운 쿼리로 준비되어야 함

  • 준비 과정은 대형 테이블의 경우 길고 시간이 많이 소요될 수 있음

  • 문제 분석을 복잡하게 만듦

  • 텍스트로 쿼리를 그룹화하기 어려움

  • SQL 인젝션 취약점을 생성함

해결책:

delphi 
FDQuery1.SQL.Text :=
 'SELECT * FROM users WHERE name = :username';
FDQuery1.ParamByName('username').AsString :=
 EditUsername.Text;
FDQuery1.Open;

11. 잘못된 무결성 검사: Primary Key 대신 트리거/CHECK 사용

안티패턴: 데이터베이스 무결성 검사를 위해 Primary Key 대신 트리거나 CHECK를 사용하는 것.

왜 나쁜가?

  • 이는 Primary Key 검증이 사용자의 트랜잭션 격리 수준과 관계없이 레코드의 현재 버전을 읽기 위해 특수 모드를 사용한다는 점을 무시함

  • 사용자 트랜잭션에서 트리거로 PK 검사를 수행하면 중복 가능성이 높아지고 로직이 불필요하게 복잡해짐

해결책:

  • Primary Key 사용

  • 중복된 무결성 검사 회피

  • 데이터베이스 로직을 단순하게 유지

12. MAX()를 사용한 ID 생성

안티패턴: 새로운 식별자에 MAX(id)+1을 사용하는 것은 신뢰할 수 없고 비효율적임.

sql 
INSERT INTO users (id, name)
VALUES ((SELECT MAX(id) + 1 FROM users),
 'John Doe');

왜 나쁜가?

  • 새로운 식별자에 시퀀스(제너레이터) 대신 MAX(id)+1 사용

  • MAX(id)+1은 일반적인 트랜잭션 매개변수로는 고유성을 보장하지 않음 - 두 개의 병렬 트랜잭션이 동일한 MAX() 값을 받을 수 있음

  • Max()+1과 CHECK(select if unique)의 조합도 작동하지 않음!

해결책:

sql 
-- 제너레이터/시퀀스 사용!
CREATE GENERATOR gen_user_id;
-- ID 생성에 제너레이터 사용
INSERT INTO users (id, name)
VALUES (
 GEN_ID(gen_user_id, 1),
 'John Doe' );

13. 비효율적인 GUID 사용

왜 나쁜가?

  • gen_uuid() 대신 시스템 생성 GUID를 사용하면 인덱스 성능에 영향을 줄 수 있음

  • 시스템 생성 GUID는 매우 무작위적임

해결책:

  • gen_uuid() 함수 사용

  • 대신 BIGINT 사용 고려

  • 버전 6에서는 UUID v7이 도입될 예정

14. 비효율적인 계산 필드

안티패턴: 다른 테이블에 대한 SELECT가 포함된 계산 필드를 사용하면 단순 SELECT 작업의 성능이 크게 저하됨.

sql 
CREATE TABLE orders (
 id INTEGER,
 total_amount COMPUTED BY (
 (SELECT SUM(item_price) FROM order_items
 WHERE order_items.order_id = orders.id)));

왜 나쁜가?

  • 계산 필드는 즉석에서 계산되며, 복잡한 로직을 구현하도록 설계되지 않았고 최적화 노력을 크게 복잡하게 만들 수 있음

  • 테이블 간의 관계를 강화함

  • 계산 필드는 연결과 같이 테이블의 필드를 사용한 가벼운 계산에만 사용하는 것이 합리적임

해결책:

sql 
CREATE TABLE orders (
 id INTEGER PRIMARY KEY,
 cached_total_amount DECIMAL(10,2));

CREATE TRIGGER update_order_total
BEFORE INSERT OR UPDATE ON orders
AS
BEGIN
 NEW.cached_total_amount = (
 SELECT SUM(item_price)
 FROM order_items
 WHERE order_items.order_id = NEW.id
 );
END;

15. 로깅 없는 오류 억제

안티패턴: 로깅 없이 Firebird 오류와 경고를 억제하지 마세요!

delphi 
try
 FDQuery1.Open;
except
 // 조용한 실패
end;

왜 나쁜가?

  • 오류를 숨기면 적절한 진단과 디버깅이 불가능해짐. 적절한 오류 로깅은 문제를 신속하게 이해하고 해결하는 데 필수적임.

해결책:

delphi 
try
 FDQuery1.Open;
except
 on E: Exception do
 begin
 // 포괄적인 로깅
 Logger.Error('Database connection failed: ' + E.Message);
 ShowMessage('Unable to connect to database. Please contact support.');
 // 추가 컨텍스트 로깅
 Logger.LogStackTrace(E);
 end;
end;

연락처 정보