[SQL] DB 오브젝트와 쿼리 최적화: Trigger, Index, Window Function

감자·2026년 1월 29일
MicrosoftDataSchoolPostgreSQLsql

microsoft_dataschool

목록 보기
16/28
post-thumbnail

01. 트리거 (Trigger): 데이터 변경 자동 감지 및 처리

트리거는 테이블에 대한 이벤트(INSERT, UPDATE, DELETE)가 발생할 때 자동으로 실행되는 특수한 프로시저임. 데이터 무결성 유지, 로그 기록, 유효성 검사 등에 사용됨.

1) 건강 상태 변경 감지 및 감사 로그 (Audit Log)

닭의 건강 정보가 수정될 때, 변경 전/후 데이터를 별도 테이블에 기록하고 비정상 수치 입력 시 경고를 발생시키는 로직임.

감사 로그 테이블 생성

CREATE TABLE fms.health_cond_audit (
    audit_id SERIAL PRIMARY KEY,
    chick_no VARCHAR(20) NOT NULL,
    old_body_temp NUMERIC(3,1),      -- 변경 전 체온
    new_body_temp NUMERIC(3,1),      -- 변경 후 체온
    check_date DATE,
    modified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 변경 일시
    operation VARCHAR(10)            -- 작업 유형 (UPDATE, DANGER 등)
);

트리거 함수 정의

  • NEW: 수정되려고 하는 새로운 데이터 행.
  • OLD: 수정되기 전의 기존 데이터 행.
  • TG_OP: 트리거를 유발한 작업명(INSERT, UPDATE 등).
  • RAISE NOTICE: 클라이언트에게 알림 메시지를 보냄.
CREATE OR REPLACE FUNCTION fms.log_health_changes()
RETURNS TRIGGER AS $$
begin
    -- 체온이 정상 범위(38~43)를 벗어나는지 체크
    if new.body_temp > 43.0 or new.body_temp < 38.0 then
        RAISE NOTICE '[경고] % 의 체온이 비정상으로 높거나 낮습니다 : % 도',
                     old.chick_no, new.body_temp;
        
        -- 비정상 데이터는 'DANGER' 태그와 함께 로그 저장
        insert into fms.health_cond_audit
        (chick_no, old_body_temp, new_body_temp, check_date , operation)
        values (old.chick_no, old.body_temp, new.body_temp, new.check_date, 'DANGER');
    else
        -- 정상 수정은 작업 유형(TG_OP)과 함께 저장
        insert into fms.health_cond_audit
        (chick_no, old_body_temp, new_body_temp, check_date , operation)
        values (old.chick_no, old.body_temp, new.body_temp, new.check_date, TG_OP);
    end if;
    return new; -- 변경된 데이터 반환 (필수)
end;
$$ LANGUAGE plpgsql;

트리거 등록
UPDATE 작업이 발생한 후(AFTER)에 트리거가 실행되도록 설정함.

CREATE TRIGGER health_audit_trigger
AFTER UPDATE
ON fms.health_cond
FOR EACH ROW
EXECUTE FUNCTION fms.log_health_changes();

실행 테스트 및 결과 확인

-- 비정상 체온(45.6도)으로 업데이트 시도 -> 경고 발생 및 로그 적재
UPDATE fms.health_cond
SET body_temp = 45.6
WHERE chick_no = 'A2500019' AND check_date = '2025-01-30';

-- 로그 확인
SELECT * FROM fms.health_cond_audit;

2) 환경 데이터 이상 감지 (Validation Trigger)

환경 데이터(env_cond)가 입력되기 전에 습도 값을 검사하여, 범위를 벗어나면 이상치 테이블(env_anomaly)에 별도로 기록함.

이상치 테이블 생성

CREATE TABLE fms.env_anomaly( 
    anomaly_id SERIAL PRIMARY KEY,
    farm char(1),
    temp numeric(3,0),
    humid numeric(3,0),
    reason TEXT,
    detected_at TIMESTAMP DEFAULT current_timestamp
);

트리거 함수 정의
BEFORE 트리거로 사용될 예정이지만, 여기서는 데이터를 수정하지 않고 별도 테이블에 기록만 수행함.

CREATE OR REPLACE FUNCTION fms.detect_env_anomaly()
RETURNS TRIGGER AS $$
BEGIN
    -- 습도가 55 미만 혹은 77 초과인 경우
    IF NEW.humid < 55 OR NEW.humid > 77 THEN
        INSERT INTO fms.env_anomaly (farm, temp, humid, reason)
        VALUES (
            NEW.farm, NEW.temp, NEW.humid, 
            case
                when new.humid > 75 then '습도 과다'
                else '습도 부족'
            end
        );
    END IF;
    RETURN new; -- 원본 데이터는 정상적으로 진행
END;
$$ LANGUAGE plpgsql;

트리거 등록
데이터가 입력되거나 수정되기 전(BEFORE)에 검사를 수행함.

CREATE TRIGGER env_anomaly_trigger
BEFORE INSERT OR UPDATE ON fms.env_cond
FOR EACH row
EXECUTE FUNCTION fms.detect_env_anomaly();

테스트

-- 정상 데이터 (로그 안 남음)
INSERT INTO fms.env_cond (farm,date,temp,humid) VALUES ('B','2025-01-25',21,60);

-- 이상 데이터 (습도 85 -> env_anomaly 테이블에 기록됨)
INSERT INTO fms.env_cond (farm,date,temp,humid) VALUES ('B','2025-01-25',21,85);

트리거 조회
시스템 카탈로그를 통해 현재 DB에 등록된 트리거 목록을 확인함.

SELECT event_object_table AS table_name, trigger_name
FROM information_schema.triggers
GROUP BY table_name, trigger_name
ORDER BY table_name, trigger_name;

02. 쿼리 최적화와 인덱스 (Query Optimization & Index)

대용량 데이터 처리 시 성능 향상을 위해 인덱스를 활용하고 실행 계획(EXPLAIN)을 분석하는 방법임.

1) 실행 계획 분석 준비 (대용량 더미 데이터 생성)

인덱스의 효과를 확인하기 위해 generate_series 함수를 사용하여 100만 건의 데이터를 생성함.

-- 테스트용 테이블
CREATE TABLE public.users(
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    signup_date DATE
);

-- 100만 건 데이터 삽입 (랜덤 날짜 생성 포함)
INSERT INTO public.users(name, email, signup_date)
SELECT
    'User ' || ((i % 100) + 1)::text,
    'user' || i || '@example.com',
    NOW() - (random() * (365 * 5) || ' days')::interval
FROM generate_series(1, 1000000) AS s(i);

2) 인덱스 유무에 따른 성능 비교

인덱스 없이 조회 (Full Table Scan)
name 컬럼에 인덱스가 없는 상태에서 조회함. Seq Scan(순차 스캔)이 발생하여 모든 행을 다 읽어야 하므로 느림.

EXPLAIN (ANALYZE, VERBOSE, buffers)
SELECT * FROM users WHERE name = 'User ' || '77';

PK 조회 (Index Scan)
id는 PK로 지정되어 자동으로 인덱스가 생성되어 있음. Index Scan이 발생하여 매우 빠름.

-- 정확한 비교를 위해 병렬 처리 끔
SET max_parallel_workers_per_gather = 0;

EXPLAIN (ANALYZE, VERBOSE, buffers)
SELECT * FROM users WHERE id = 77;

단일 인덱스 생성 및 조회
name 컬럼에 B-Tree 인덱스를 생성 후 다시 조회하면 Bitmap Heap Scan 또는 Index Scan이 발생하여 속도가 획기적으로 개선됨.

-- 인덱스 생성
CREATE INDEX idx_users_name ON users(name);

-- 실행 계획 확인
EXPLAIN (ANALYZE, VERBOSE, buffers)
SELECT * FROM users WHERE name = 'User ' || '77';

-- 인덱스 삭제
DROP INDEX IF EXISTS idx_users_name;

3) 복합 인덱스 (Composite Index)

두 개 이상의 컬럼을 조합하여 인덱스를 생성함. WHERE 절에 두 컬럼이 같이 사용될 때 효과적임.

-- 이름과 가입일자를 묶은 인덱스 생성
CREATE INDEX IF NOT exists idx_users_name_signup_date
ON users (name, signup_date);

-- 두 조건을 모두 만족하는 쿼리 실행 시 성능 최적화
EXPLAIN (ANALYZE, VERBOSE, buffers)
SELECT * FROM users
WHERE name = 'User ' || '77'
AND signup_date BETWEEN '2021-01-30' AND '2021-02-01';

-- 테스트 종료 후 삭제
DROP INDEX IF EXISTS idx_users_name_signup_date;

4) JOIN 성능과 인덱스

조인 키(chick_no)에 인덱스가 있을 때와 없을 때의 성능 차이를 비교함.

-- 1. 인덱스 없이 조인 실행 -> Hash Join 발생 (테이블 전체를 읽어 해시 테이블 생성)
EXPLAIN ANALYZE
SELECT a.chick_no, a.pass_fail, a.raw_weight, b.order_no, b.customer 
FROM fms.prod_result a
JOIN fms.ship_result b ON a.chick_no = b.chick_no;

-- 2. 조인 컬럼에 인덱스 생성
CREATE INDEX ship_result_chick_idx ON fms.ship_result (chick_no);
CREATE INDEX prod_result_chick_idx ON fms.prod_result (chick_no);

-- 3. 인덱스 생성 후 실행 -> Merge Join 또는 Nested Loop Join으로 최적화될 수 있음
EXPLAIN (ANALYZE, FORMAT JSON)
SELECT a.chick_no, a.pass_fail, a.raw_weight, b.order_no, b.customer 
FROM fms.prod_result a
JOIN fms.ship_result b ON a.chick_no = b.chick_no;

03. [Project] 회사를 지켜라: 데이터 분석 실습

실전 시나리오를 바탕으로 문제를 해결하는 쿼리 작성임.

1) 조류독감 의심 닭 찾기

체온이 높은 순서대로 정렬하여 이상 개체를 확인함.

SELECT * FROM fms.health_cond hc ORDER BY body_temp DESC;

2) 환경 악화 원인 분석

특정 온도 범위(33미만, 35초과)이면서 습도가 높은(75초과) 날짜를 필터링함.

SELECT * FROM fms.env_cond
WHERE (temp < 33 OR temp > 35) AND humid > 75;

3) 윈도우 함수를 이용한 품종별 랭킹 (Window Function)

ROW_NUMBER() 함수를 사용하여 품종별로 그룹화(PARTITION BY)하고 무게순으로 정렬(ORDER BY)하여 순위를 매김.

SELECT chick_no, breeds, raw_weight 
FROM (
    SELECT 
        a.chick_no, 
        a.breeds, 
        b.raw_weight,
        -- 품종별로 구획을 나누고, 구획 내에서 무게 내림차순으로 순번 매김
        ROW_NUMBER() OVER (PARTITION BY a.breeds ORDER BY b.raw_weight DESC) as ranking
    FROM fms.chick_info a
    JOIN fms.prod_result b ON a.chick_no = b.chick_no
) a
WHERE ranking <= 3; -- 각 품종별 상위 3마리만 추출

04. 종합 리포트 생성 및 뷰(View) 활용

1) 복합 쿼리 작성 (스칼라 서브쿼리 + 조인)

4개의 테이블을 조인하고, 코드값(예: 'C1')을 명칭(예: 'Cornish')으로 변환하며, 단위(unit)를 붙여서 가독성 높은 리포트를 만듦.

  • 스칼라 서브쿼리: SELECT 절 안에서 코드 테이블을 조회하여 명칭을 가져옴.
  • LEFT OUTER JOIN: 건강/생산/출하 정보가 없는 닭도 조회되도록 함.
  • 문자열 결합(||): 데이터 값과 단위를 합침.
SELECT 
    a.chick_no AS 육계번호,
    -- 품종 코드 -> 명칭 변환
    (SELECT m.code_desc FROM fms.master_code m WHERE m.column_nm = 'breeds' and m.code = a.breeds) AS 품종,
    -- 무게 + 단위 결합
    a.egg_weight || (SELECT u.unit FROM fms.unit u WHERE u.column_nm = 'egg_weight') AS 종란무게,
    b.body_temp || (SELECT u.unit FROM fms.unit u WHERE u.column_nm = 'body_temp') AS 체온,
    b.breath_rate || (SELECT u.unit FROM fms.unit u WHERE u.column_nm = 'breath_rate') AS 호흡수,
    -- 호수 코드 -> 명칭 변환 (숫자 형변환 포함)
    (SELECT m.code_desc FROM fms.master_code m WHERE m.column_nm = 'size_stand' AND to_number(m.code,'99') = c.size_stand) AS 호수,
    -- 부적합 여부 코드 변환
    (SELECT m.code_desc FROM fms.master_code m WHERE m.column_nm = 'pass_fail' AND m.code = c.pass_fail) AS 부적합여부,
    d.order_no AS 주문번호,
    d.customer AS 고객사,
    d.arrival_date AS 도착일,
    d.destination AS 도착지
FROM fms.chick_info a
LEFT OUTER JOIN fms.health_cond b ON a.chick_no = b.chick_no 
LEFT OUTER JOIN fms.prod_result c ON a.chick_no = c.chick_no 
LEFT OUTER JOIN fms.ship_result d ON a.chick_no = d.chick_no 
WHERE b.check_date = '2025-01-30';

2) 뷰(View)로 저장

위의 복잡한 쿼리를 매번 작성하지 않도록 뷰로 생성하여 재사용성을 높임. 타입 캐스팅(::text, ::numeric)을 명시하여 데이터 타입 불일치를 방지함.

CREATE OR REPLACE VIEW fms.total_result AS 
SELECT 
    a.chick_no AS "육계번호",
    (SELECT m.code_desc FROM fms.master_code m WHERE m.column_nm::text = 'breeds' AND m.code = a.breeds) AS "품종",
    a.egg_weight || ((SELECT u.unit FROM fms.unit u WHERE u.column_nm = 'egg_weight')) AS "중란무게",
    b.body_temp || ((SELECT u.unit FROM fms.unit u WHERE u.column_nm = 'body_temp')) AS "체온",
    b.breath_rate || ((SELECT u.unit FROM fms.unit u WHERE u.column_nm = 'breath_rate')) AS "호흡수",
    (SELECT m.code_desc FROM fms.master_code m WHERE m.column_nm = 'size_stand' AND to_number(m.code, '99') = c.size_stand) AS "호수",
    (SELECT m.code_desc FROM fms.master_code m WHERE m.column_nm = 'pass_fail' AND m.code = c.pass_fail) AS "부적합여부",
    d.order_no AS "주문번호",
    d.customer AS "고객사",
    d.arrival_date AS "도착일",
    d.destination AS "도착지"
FROM fms.chick_info a
LEFT JOIN fms.health_cond b ON a.chick_no = b.chick_no
LEFT JOIN fms.prod_result c ON a.chick_no = c.chick_no
LEFT JOIN fms.ship_result d ON a.chick_no = d.chick_no
WHERE b.check_date = '2025-01-30';

-- 뷰 소유권 및 주석 설정
ALTER TABLE fms.total_result OWNER TO postgres;
COMMENT ON VIEW fms.total_result IS '종합실적';
profile
감자
제 별명이 감자입니다
이전 포스트

[SQL] PostgreSQL 함수, 프로시저, Job 스케줄링

다음 포스트

[Python] 가상환경 구축과 DB 연동, Flask 웹 서버 입문

0개의 댓글