Ray`s Tech Lab

1. 트리거란?

RDB에서 특정 테이블에 이벤트(CREATE or UPDATE or DELETE)가 발생했을 때 수행할 작업들을 정의할 수 있는데, 이를 트리거라고 부릅니다. 즉 DB 테이블에 어떠한 변경이 일어날 때 이를 방아쇠(Trigger)삼아 미리 정의된 SQL 코드가 자동으로 실행되는 것으로 이해할 수 있습니다.

트리거는 실행 시점을 기준으로 크게 다음과 같이 2종류로 구분할 수 있습니다.

1) BEFORE 트리거

UPDATE, INSERT, DELETE 실행 전에 실행되는 트리거입니다. 애플리케이션으로부터 온 잘못된 요청을 거부하는 방식으로 활용 가능하며, UPDATE와 INSERT의 경우 실제 값이 수정/삭제되기 전 그 값을 가공할 수도 있습니다.

2) AFTER 트리거

UPDATE, INSERT, DELETE 실행 후에 실행되는 트리거입니다. 주로 변경된 데이터를 기반으로 다른 테이블을 업데이트하는 후속 조치를 남기는 식으로 많이 활용합니다.

참고) BEFORE 트리거든 AFTER 트리거든, 트리거에 정의된 작업들은 트리거를 실행시킨 작업과 같은 트랜잭션에서 수행됩니다. 즉 트리거가 실패하면 트리거를 실행시킨 작업도 실패합니다. (https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html)

만약 트리거를 적용 범위 수준으로 구분하면 다음과 같이 2종류로 구분 가능합니다.

1) Row-level 트리거

이벤트가 발생된 행별로 수행되는 트리거입니다. 예를 들어 UPDATE절에 의해 10개의 행이 바뀌면, 이 트리거는 10번 실행됩니다.

2) Statement-level 트리거

변경된 행 수에 관련없이 조작되는 전체 문장(statement)단위로 실행되는 트리거입니다. 전체 배치 작업 전/후 하나의 로직만 실행하면 되는 경우 등에 사용할 수 있습니다. (MySQL의 경우 지원하지 않습니다)

참고) SQL Server나 Oracle의 경우 DDL에도 트리거를 만들 수 있습니다.

2. 트리거 생성 방법 (MySQL 기준)

트리거 생성은 CREATE TRIGGER를 통해 수행할 수 있고, 해당 테이블에 대한 Trigger 권한이 필요합니다. 세부적인 생성 문법은 다음과 같습니다.

-- 대괄호는 Optional을 의미합니다.

CREATE
    [DEFINER = user]
    TRIGGER [IF NOT EXISTS] trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [trigger_order]
    trigger_body

-- 각 필드
-- 1. trigger_time: { BEFORE | AFTER }
-- 2. trigger_event: { INSERT | UPDATE | DELETE }
-- 3. trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

• DEFINER : 트리거 정의자를 어떤 계정으로 할 것인지 지정합니다. 트리거는 실행되면 트리거를 실행시킨 계정의 권한이 아니라 트리거를 정의한 사람의 권한으로 실행되는데, 그때의 트리거 정의자 계정을 지정하는 것으로 이해할 수 있습니다. 이 필드를 따로 작성하지 않으면 트리거를 생성한 사람이 자동으로 DEFINER가 됩니다. DEFINER로 지정되는 계정에는 해당 테이블(트리거가 붙는 테이블)에 대한 TRIGGER 권한이 필요하며, trigger_body에서 참조되는 테이블들에는 TRIGGER 권한은 필요없으나 적절한 DML 권한이 필요합니다.
• trigger_name : 트리거의 이름으로, 같은 스키마 내에서 트리거 이름은 유일해야 합니다.
• trigger_time : 트리거의 실행시점을 지정합니다.
• trigger_event : 트리거를 발생시킬 이벤트 종류를 지정합니다.
• trigger_order : 같은 테이블의 동일한 이벤트에 대한 트리거가 여러 개일때, 각각의 실행 순서를 지정합니다. FOLLOWS 사용시 지정한 트리거 실행 후에 실행되고, PRECEDES 사용시 지정한 트리거 실행 전에 실행됩니다. 참고로 MySQL 5.7버전부터만 동일 이벤트에 대한 복수의 트리거 정의가 가능합니다.
• trigger_body : 실행할 작업을 정의합니다. BEGIN ... END를 작성하면 여러 작업을 정의할 수 있습니다.

trigger_body에서는 OLD와 NEW라는 키워드를 통해 변경 전후의 데이터에 접근할 수 있습니다.

• OLD : UPDATE 또는 DELETE 문에서 변경/삭제 전의 기존 데이터를 가리킵니다. OLD.column_name을 통해 해당 데이터의 컬럼값에 접근할 수 있습니다.
• NEW: INSERT 또는 UPDATE 문에서 새로 추가되거나 변경될 행의 데이터를 가리킵니다. NEW.column_name을 통해 해당 데이터의 컬럼값에 접근할 수 있습니다.

UPDATE 문에서는 OLD & NEW를 둘 다 접근할 수 있는데, 당연히 BEFORE 트리거에서는 NEW만 수정 가능합니다.

위 내용들을 토대로 트리거 생성 예제를 보면 다음과 같습니다. employees 테이블에 있는 직원 연봉(salary)가 변경될 때마다 변경내용을 salary_audit 테이블에 기록하는 AFTER 트리거입니다.

-- 트리거 생성도 세미콜론으로 끝나는데, trigger_body 내에서 사용되는 세미콜론이 트리거 종료로 인식되는걸 방지
DELIMITER $$

CREATE DEFINER = 'admin_user'@'localhost'
TRIGGER after_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    -- 연봉이 변경되었을 때만 로그를 남김
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO salary_audit (emp_no, old_salary, new_salary, changed_by)
        VALUES (OLD.emp_no, OLD.salary, NEW.salary, USER());
    END IF;
END$$

DELIMITER ;

여기서 질문. USER()를 통해 변경을 실행한 DB 계정을 기록하는 건 알겠는데, 트리거를 실행시킨 DB 계정이 기록될까요 아니면 DEFINER로 정의된 DB계정이 기록될까요?

실제 예제를 통해 보겠습니다. 현재 employees 테이블에 다음과 같은 레코드가 있습니다.

그리고 admin_user, app_user 계정을 만든 뒤 다음처럼 권한을 부여했습니다.

• admin_user
  1. employees 테이블에 대한 SELECT, TRIGGER 권한
  2. salary_audit 테이블에 대한 INSERT 권한
• app_user
  1. employees 테이블에 대한 SELECT, INSERT 권한

그리고 app_user 계정으로 접속하여 salary를 20000으로 바꿔주었습니다.

salary_audit 테이블에 적재된 결과는..

다음과 같이 트리거 정의자 계정이 아닌, 트리거를 실행시킨 DB 계정이 기록된 것을 볼 수 있었습니다.

사실 이 내용은 MySQL 공식 문서 내에서도 확인 가능합니다 (https://dev.mysql.com/doc/refman/8.0/en/create-trigger.html)

3. ⭐️ 어디에, 그리고 왜 트리거를 쓸까

우선 트리거는 특정 테이블에 이벤트가 발생됐을 때 수행하고 싶은 기능을 자동화하는 것으로 볼 수 있습니다. 애플리케이션에서 반복 작성해야 할 로직을 트리거를 쓰면 한 번만 구현하면 되고, 중요한 데이터에 대한 감사 로깅이 필요한 경우 트리거를 통해 별도의 로그 테이블에 자동으로 기록하게 하거나, 유저 테이블의 회원 상태가 '탈퇴'로 변경됐을 때 관련된 게시판 테이블의 게시물 상태들을 '비공개'로 변경하는 작업 등에 활용 가능합니다. 외부로의 이벤트/메시지 전파를 위해 같은 트랜잭션 내에서 비즈니스 테이블에 데이터를 연동하고 outbox 테이블에 발행할 이벤트를 적재하는 '트랜잭셔널 아웃박스 패턴'의 구현 방법으로 트리거를 채택할 수도 있습니다.

하지만 단순히 '자동화'를 넘어서 트리거의 핵심 역할을 DB 스스로가 데이터의 정합성과 일관성을 유지하도록 강제하는 것으로 바라볼 수 있습니다. 장애라고 하는 것은 트래픽으로 인해서 뿐만 아니라 여러 원인으로 인해 논리적으로 잘못된 형태의 데이터가 적재되면서도 발생할 수 있는데요. 물론 개발자가 애플리케이션 레벨에서 최대한 조심하며 개발할 수 있겠지만 결국 개발자 스스로도 휴먼 에러를 낼 수 있고(예상치 못하게 로직이 꼬인다든가.. 등) 이로 인해 잘못된 데이터가 들어올 수 있습니다. 물론 테이블에 CHECK 등의 제약 조건을 붙이며 최대한 데이터 무결성을 지킬 순 있겠으나, 'VIP 단계의 고객이 주문한 아이템들의 총액은 10,000원을 넘어야 한다' 등의 복잡한 비즈니스 제약들을 표현하고 지키기는 힘듭니다.

이럴 때 트리거를 사용해서 복잡한 데이터의 유효성 검사를 담당하게 할 수 있고, 단순 참조 무결성을 넘어서 복잡한 비즈니스 제약을 DB 스스로가 지키도록 강제할 수 있습니다. 또한 트리거 정의시 작성하는 DEFINER 계정에만 A 테이블에 대한 권한을 준다면, 해당 트리거 실행을 제외하고는 A 테이블에 데이터가 넣어질 수 없도록 아예 틀어막아 버릴 수도 있습니다. (개발자 실수 등이 생겨도 잘못된 데이터가 들어가는 일 자체가 안 생기게 됨)

그렇다고 트리거가 늘 좋냐!고 하면 당연히 아닙니다. 다음과 같은 단점들이 있습니다.

1) 성능 저하

애플리케이션에서 실행한 DB 작업 외에도 트리거를 통한 부가 작업이 수행되는 만큼, 성능 저하가 발생됩니다. 특히 MySQL은 Row-level 트리거이므로 1,000개의 행이 갱신되면 트리거가 1,000번 실행되므로.. 만약 트리거가 부착된 테이블의 변경이 잦다면 그만큼 성능 오버헤드도 비례해서 올라갑니다.

2) 불투명성

"A 테이블에 데이터를 넣으면 B 테이블에도 데이터를 추가한다" 라는 비즈니스 로직이 있다고 가정해보겠습니다. 이 비즈니스 로직이 애플리케이션 레벨에서 우리에게 친숙한 자바 코드 등으로 작성되어 있다면 처음 서비스를 접하는 개발자도 이런 로직이 있다고 쉽게 파악할 수 있습니다. 하지만 트리거를 통해 DB 레벨에서 이 비즈니스 로직이 구현되어 있다면, 개발자가 이런 비즈니스 로직이 있다고 인지하기도 어려울 뿐더러 문제 발생 시 디버깅도 어렵게 됩니다. 더 나아가 한 테이블에 부착된 트리거들이 FOLLOWS 등을 통해 여러 선후행 관계를 가진다면 골치가 더 아파지게 됩니다.

3) 작동 확인 방법의 번거로움

트리거는 작동을 확인하기 위해 명시적으로 실행해볼 수 있는 방법이 없어서, 잘 동작하는지 확인하려면 직접 부착된 테이블에 INSERT나 UPDATE, DELETE를 해줘야 합니다.

트리거 사용의 장단점을 표로 정리하면 다음과 같습니다.

그러면 트리거는 어떤 상황에 쓰면 좋고 어떤 상황에는 안 쓰는게 좋을까요? 개인적으로 생각해본 건 다음과 같습니다.

• 써도 괜찮을 때
  1. DB에 직접 접근해서 데이터를 수동으로 수정하는 경우가 많아, 휴먼 에러를 방지하고 데이터 무결성을 반드시 지켜주고 싶을 때 (앞서 말한 것처럼 DEFINER로 작성하는 계정에만 특정 테이블에 대한 권한을 주는 식으로 등..)
  2. 여러 애플리케이션이 하나의 DB를 공유하고 있어서 모두에게 동일한 규칙을 강제하고 싶을 때
• 쓰면 안 좋을 때
  1. 트리거를 부착하고 싶은 테이블의 데이터 변경이 잦아서 성능 저하가 예상될 때 (MySQL은 특히나 Row-level 트리거이므로)
  2. 비즈니스 로직이 자주 변경될 때 (대부분의 경우 애플리케이션 코드를 수정하는게 더 쉬울 것 같습니다)
  3. 서비스의 로직을 애플리케이션에서만 관리하는게 좋다고 판단될 때

4. 알아두면 좋은 트리거 주의사항

1. "INSERT INTO ... ON DUPLICATE SET" 는 중복이 없다면 BEFORE INSERT와 AFTER INSERT 이벤트를 발생시키고, 중복이 있다면 BEFORE UPDATE와 AFTER UPDATE 이벤트를 발생시킵니다.
2. 테이블에 외래키 관계에 의해 자동으로 변경된 경우는 트리거가 호출되지 않습니다.
3. 트리거 작성 시 BEGIN .. END 블록 내에서는 ROLLBACK / COMMIT 등 트랜잭션을 명시적으로 시작하거나 종료하는 SQL 문장을 작성할 수 없습니다.
4. mysql과 information_schema, performance_schema 데이터베이스에 존재하는 테이블들에는 트리거를 생성할 수 없습니다.

5. 레퍼런스

https://dev.mysql.com/doc/refman/8.4/en/trigger-syntax.html 

https://dev.mysql.com/doc/refman/8.4/en/create-trigger.html

https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/dml-triggers.html#GUID-E76C8044-6942-4573-B7DB-3502FB96CF6F

https://www.ibm.com/docs/en/ias?topic=triggers-types

들어가며

BE 개발자라면 DB를 공부하면서 트랜잭션 격리 수준(Transaction Isolation Level)을 접한 적이 있을 겁니다. 저 역시 취준 시절 기술 면접을 준비하면서, 그리고 DB를 공부하면서 트랜잭션 격리 수준을 공부한 적이 있는데요. 사실 공부한 적만 있지 사이드 프로젝트 또는 실무에서 트랜잭션 격리 수준으로 인한 문제를 마주쳤던 적은 없었습니다. 하지만 이번에 사내에서 낙관적 락으로 동시성 문제를 방지하는 기능을 개발하는 과정에서 트랜잭션 격리 수준에 대해 어설프게 알고 있던 덕에 뜻밖의 곤욕을 치르게 됐는데요. 아무래도 저처럼 이론으로만 트랜잭션 격리 수준을 공부했던 분들에게 도움이 될 수도 있는 내용인 것 같아 공유하고자 이 글을 쓰게 됐습니다.

※ 하기 내용은 트랜잭션 격리 수준, 그 중에서도 REPEATABLE_READ에 대한 지식이 있음을 바탕으로 작성했습니다.

※ 보안 문제 등으로 제가 담당한 시스템의 정확한 명칭과 세부 업무, 기능은 언급하지 않고자 했습니다. 이 점 양해 부탁드립니다.

개발 과정 & 고민했던 점

제가 이번에 개발한 기능은 "통보서 중복 작성 방지" 기능이었습니다. 기존 통보서 작성 프로세스는 다음과 같았습니다.

1. 통보서를 보낼 업무(BUSINESS_INFO 테이블의 레코드)에 대해 특정 프로세스 진행
2. 고객에게 보낼 통보서 내용을 DB에 INSERT ( = 통보서 작성)
3. 해당 업무의 통보상태를 NOT_REGISTER(통보 내용 미작성)에서 REGISTER(통보 내용 작성)으로 UPDATE

해당 비즈니스 로직은 코드 상에서는 다음처럼 구현되어 있었습니다.

@Transactional
public void processNotice(String bizSeqNbr) {
    // 통보서를 보낼 업무 정보 조회
    BusinessInfo businessInfo = readBusinessInfo(bizSeqNbr);

    // .. 생략 (특정 프로세스 진행)

    // 고객에게 보낼 통보서 내용 INSERT
    insertNotice(notice);

    // 해당 업무의 통보상태를 UPDATE
    updateBusinessNoticeStatus(bizSeqNbr, NoticeStatus.REGISTER);
}

-- updateBusinessNoticeStatus

UPDATE BUSINESS_INFO
SET NOTICE_STATUS    = #{BUSINESS_NOTICE_STATUS}
  , LAST_CHANGE_ID   = #{EMPNO}
  , LAST_CHANGE_DTTM = NOW()
WHERE BUSINESS_SEQUENCE_NUMBER = #{BIZ_SEQ_NBR};

통보서 작성 후 해당 업무의 통보상태(BUSINESS_INFO.NOTICE_STATUS)를 REGISTER로 갱신하므로, 중복 작성을 방지하는 가장 쉬운 방법은 해당 업무의 통보상태 값이 REGISTER라면  통보서를 작성하지 않도록 하는 것이라고 생각했습니다. 하지만 단순히 이 상태 조회 체크 로직만 둔다면 동시성 문제가 발생할 수 있으니 이를 막기 위해서는 업무 정보를 조회하는 시점부터 비관적 락을 적용하는 방법도 있었으나, 이 시스템은 소수의 직원이 사용하는 사내 시스템이었기 때문에 중복 작성이 흔하게 일어나진 않을 거라 판단하여 다음과 같은 낙관적 락 형태를 적용한 쿼리를 활용하기로 했습니다. 

-- updateBusinessNoticeStatusToRegister

UPDATE BUSINESS_INFO
SET NOTICE_STATUS    = #{BUSINESS_NOTICE_STATUS}
  , LAST_CHANGE_ID   = #{EMPNO}
  , LAST_CHANGE_DTTM = NOW()
WHERE BUSINESS_SEQUENCE_NUMBER = #{BIZ_SEQ_NBR}
AND NOTICE_STATUS = 'NOT_REGISTER'; 

-- version 등의 컬럼을 추가할 수 없던 상황이라.. 통보서 작성 전의 통보상태는 NOT_REGISTER임을 활용
-- 갱신된 레코드가 0개라면 예외를 일으켜 롤백

그러나 사용 중인 DB(=MySQL)은 트랜잭션 격리 수준으로 디폴트인 REPEATABLE_READ를 사용중이었습니다. 동일 트랜잭션에서는 여러 번 SELECT를 해도 항상 같은 결과가 나오도록 보장하는 격리 수준으로, 트랜잭션 시작 후 최초로 읽기 시작한 시점의 스냅샷을 참조하는 원리인데요. 저는 이 스냅샷을 UPDATE 시에도 동일하게 활용할 것이라고 생각해서, 다음처럼 낙관적 락을 적용해도 통보서를 중복으로 작성되는 문제가 생길 수 있다는 생각이 들었습니다.

하지만 여러 번의 테스트 결과, 중복 갱신은 발생하지 않았습니다. 벌어지지 않을 일에 대해 "헉 이거 문제 생기는 거 아냐?"라고 생각하고 있던 거죠.. 근데 왜 발생하지 않았을까요? 저는 트랜잭션 안에서 SELECT와 UPDATE가 같은 시점의 데이터(스냅샷)를 바라보고 수행된다고 생각했지만, 실제로는 SELECT는 과거의 스냅샷을 바라보고 UPDATE는 현재를 바라보고 수행되기 때문이었습니다.

어설프게 알면 당하는 MySQL REPEATABLE_READ의 함정

이런 현상은 MySQL 공식 문서에서도 찾아볼 수 있었습니다.

A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by transactions that committed before that point in time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by earlier statements within the same transaction. This exception causes the following anomaly: If you update some rows in a table, a SELECT sees the latest version of the updated rows, but it might also see older versions of any rows. If other sessions simultaneously update the same table, the anomaly means that you might see the table in a state that never existed in the database.

• 일관된 읽기(Consistent Read)란 InnoDB가 MVCC을 사용해, 쿼리에 특정 시점의 DB 상태를 스냅샷으로 제공하는 것.
• 쿼리는 해당 시점 이전에 커밋된 트랜잭션의 변경분은 볼 수 있지만, 그 이후에 커밋되었거나 아직 커밋되지 않은 트랜잭션의 변경분은 볼 수 없다. (= REPEATABLE_READ의 원리)
• 단, 같은 트랜잭션 내에서 수행된 DML(INSERT, UPDATE 등)로 인한 변경분은 볼 수 있다.
• 이 때문에 아래와 같은 비일관성(anomaly) 이 발생 가능하다.
  1. 같은 트랜잭션 내에서 일부 레코드만 UPDATE한 후 SELECT를 실행하면, 업데이트된 레코드만 최신 버전으로 보인다.
  2. 여러 트랜잭션이 동시에 동일 테이블에 대해 업데이트를 하고 있다면, 트랜잭션 내에서 SELECT로 조회되는 결과는 실제 데이터베이스에 존재한 적 없던 상태를 보여줄 수도 있다. (트랜잭션 내에서는 동일 트랜잭션의 DML로 인한 변경분만 보이므로)

The snapshot of the database state applies to SELECT statements within a transaction, not necessarily to DML statements. If you insert or modify some rows and then commit that transaction, a DELETE or UPDATE statement issued from another concurrent REPEATABLE READ transaction could affect those just-committed rows, even though the session could not query them. If a transaction does update or delete rows committed by a different transaction, those changes do become visible to the current transaction.

• 스냅샷은 트랜잭션 내의 SELECT 문에만 적용되며, DML(INSERT, UPDATE 등)에도 적용되어야 하는 건 아니다.
• A 트랜잭션이 일부 레코드를 INSERT 또는 UPDATE한 뒤 커밋했을 때, 동시에 실행 중이던 REPEATABLE_READ 수준의 B 트랜잭션에서 실행되는 DELETE나 UPDATE 문은 A 트랜잭션이 방금 커밋한 행에도 영향을 줄 수 있다(B 트랜잭션에서는 그 레코드들을 SELECT로 조회할 수 없더라도)
  • >> UPDATE / DELETE는 특정 시점의 스냅샷이 아닌 현재를 바라보고 수행되기 때문으로 이해할 수 있습니다.
• 한 트랜잭션이 다른 트랜잭션에서 커밋한 레코드를 UPDATE하거나 DELETE할 경우에는 그로 인한 변경사항이 현재 트랜잭션 내에서 즉시 반영되어 보이게 된다. (하단 예제 이미지 참고)

이를 토대로 정리해보겠습니다. UPDATE / DELETE는 특정 시점의 스냅샷이 아닌 현재를 바라보고 수행됩니다. 따라서 하나의 트랜잭션 안에서 SELECT한 결과를 조건으로 활용해 UPDATE / DELETE 등을 해줄 때, 이런 트랜잭션이 여러 개가 동시에 실행된다면 내가 예상한 레코드가 UPDATE / DELETE되지 않거나 내가 예상하지 않은 레코드가 UPDATE / DELETE되는, 즉 예상치 못한 동작이 발생할 수 있습니다. 

결국 트랜잭션 격리 수준은 읽기와 관련된 문제들(Dirty Read, Non-Repeatable Read 등)들에 대한 해결책은 될 수 있으나, 쓰기에 대한 문제들(Lost Update 등)은 여전히 발생 가능합니다. 이를 위해 낙관적 락, 비관적 락(사실 끝나서 얘기하지만 저는 낙관적 동시성 제어 등으로 부르는게 더 맞다고 생각하는 편)을 적절히 적용해서 쓰기와 관련된 문제들을 해결할 수 있어야 하죠. 다행히 제가 이번 기능 개발에서 적용한 낙관적 락 형태의 쿼리는 시스템 특성상 해당 쿼리를 활용하는 코드가 제한되어 있어 동시성 문제가 나지 않았지만, 이번 삽질을 통해 낙관적 락 적용시에는 version등의 컬럼을 활용해 UPDATE 조건을 정확하고 유일하게 적용해야 쓰기 관련된 동시성 문제들을 깔끔하게 해결할 수 있다는 인사이트를 얻었습니다. 단순한 CRUD를 넘어 격리 수준 등을 토대로 나올 수 있는 복합적인 동시성 문제들까지 고려하는 습관을 잡아야 할 것 같습니다 :)

Reference

https://dev.mysql.com/doc/refman/8.4/en/innodb-consistent-read.html

https://stackoverflow.com/questions/59287861/repeatable-read-isolation-level-select-vs-update-where 

다음과 같은 테이블이 있다고 하자.

SALES_HISTORY 테이블은 특정 상품들과 해당 상품들이 판매된 시간, 판매된 가격 정보를 가진다.

1. GROUP BY를 통한 집계

GROUP BY를 통해 특정 컬럼(들)을 기준으로 그룹핑한 다음 집계함수를 활용한 합이나 평균값의 산출이 가능하다. 본 글에서는 SUM을 활용한 상품들의 일자별 매출액을 조회하는 상황을 가정한다.

SELECT
  PRODUCT_ID,
  DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DAY,
  SUM(PRICE) AS TOTAL_SALES
FROM
  SALES_HISTORY
GROUP BY
  PRODUCT_ID, SALES_DAY;

2. WITH ROLLUP을 가미한 집계

WITH ROLLUP을 가미하면, GROUP BY로 보여지는 결과물 뿐만 아니라 그룹핑한 각각의 소그룹별 집계와 전체 집계를 볼 수 있다.

SELECT
  PRODUCT_ID,
  DATE_FORMAT(SALES_DATE, '%Y-%m-%d') AS SALES_DAY,
  SUM(PRICE) AS TOTAL_SALES
FROM
  SALES_HISTORY
GROUP BY
  PRODUCT_ID, SALES_DAY WITH ROLLUP;

NULL값으로 표기된 부분들이 WITH ROLLUP을 통해 산출된 집계값이다. 1000번 상품 그룹에 대한 집계와 1001번 상품에 대한 집계, 1002번 상품에 대한 집계와 더불어 전체 집계 또한 나온 것을 확인할 수 있다. 

MySQL, 그리고 InnoDB의 Index

인덱스(Index)는 DB에서 테이블의 데이터를 빠르게 검색하기 위해 사용되는 보조적인 자료구조로, 인덱스와 실제 데이터는 따로 보관 및 관리된다. 전통적으로 인덱스를 위해 가장 많이 사용되는 자료구조는 B-tree (Balanced Tree)로, Leaf Node는 실제 데이터 레코드를 찾아갈 수 있는 주솟값을 가지고 있다.

MySQL 역시 B-tree 기반 인덱스를 사용하며, InnoDB 엔진의 경우 클러스터링 인덱스(클러스터링 테이블이라고도 부름)를 사용하는데 이는 테이블에서 PK(Primary Key)값이 비슷한 레코드들끼리 묶어 저장하는 걸 의미한다. 즉 스토리지 엔진으로 InnoDB를 사용한다면 아무 것도 하지 않아도 알아서 PK값들에 의해 B-tree 구조로 테이블의 데이터가 저장된다(각 Leaf Node가 레코드의 모든 컬럼 값을 가짐). 즉 PK값에 따라 레코드의 저장 위치가 결정되는 것이며, 만약 PK값이 변경된다면 해당 레코드가 저장되는 물리적 위치 또한 바뀌게 됨을 의미한다. 따라서 이렇게 클러스터링 인덱스로 저장되는 테이블은 PK 기반의 검색이 매우 빠르나 레코드의 저장이나 PK 변경은 상대적으로 느리다.

이 특성 때문에, 세컨더리 인덱스에 대한 B-tree를 기준으로 볼 때 스토리지 엔진으로 MyISAM을 사용할 경우 Leaf Node가 실제 레코드의 물리적인 주소들을 가지는 반면 InnoDB는 Leaf Node가 PK를 가진다. 즉 InnoDB를 사용할 경우 세컨더리 인덱스를 통해 데이터를 읽을 때 데이터를 바로 찾아가는 게 아니라, Leaf Node에 저장된 PK를 이용해 클러스터링 인덱스에서 찾아가게 된다.

본 포스트에서는 Index를 사용하지 않는 Full Table Scan방법과 Index를 사용하는 4가지 Scan 방법에 대해 다룬다.

Full Table Scan (=Table Full Scan)

말 그대로 테이블 전체 데이터를 순차적으로 scan하는 방식이다(배열에서 모든 데이터를 선형탐색으로 읽는 것에 비유할 수 있다). InnoDB가 데이터(인덱스도 포함)들을 저장하는 기본적인 단위는 "페이지"인데, Full Table Scan은 테이블의 데이터가 담긴 페이지들을 모두 읽어와서 읽게 된다. 레코드가 많은 테이블의 경우, Full Table Scan은 성능 저하의 주범이 되는 경우가 많다. 일반적으로 인덱스가 없거나, 또는 인덱스를 활용할 수 없거나, 아니면 테이블의 레코드가 너무 적어서 인덱스를 사용하지 않고 테이블 전체를 읽는 게 더 빠를 경우 이 방법이 사용된다.

Index Full Scan

인덱스의 Leaf 노드 전체(즉 인덱스의 처음부터 끝까지)를 순차적으로 scan하지만 실제 테이블을 참조하지는 않는 방식을 말한다. 쿼리가 인덱스에 명시된 컬럼들만으로 조건을 처리가능한 경우에 주로 이 방법이 사용된다.(B-tree의 각 노드가 인덱스로 명시된 컬럼값과 child node의 위치를 키-값 형태로 가지고 있음을 상기할 것) 인덱스 뿐만이 아닌 레코드까지 읽어야 한다면 절대 이 방식을 사용하지 않으며, 실제 테이블을 참조하지는 않으니 Full Table Scan보다는 효율적(왜냐면 인덱스의 전체 크기가 테이블 자체의 사이즈보다 작아서 Disk I/O가 줄어들기 때문)이다.

참고로 이렇게 쿼리가 인덱스에 존재하는 컬럼만으로 처리 가능할 때 "커버링 인덱스"라고 부른다.

Index Range Scan

특정 범위만큼의 인덱스를 스캔한 뒤 이에 대응하는 레코드들을 읽는 방식이다. 쿼리에서 BETWEEN이나 부등호 등이 사용되어 검색해야 할 인덱스의 범위(range)가 결정됐을 때 사용된다. 스캔의 시작점이 되는 리프 노드만 찾으면 그때부터는 종료 지점까지의 리프 노드들을 쫙 스캔한 후(B+ tree는 리프 노드끼리도 서로 연결돼있음을 상기), 필요하다면 읽은 리프 노드들이 가리키는 레코드들을 읽어온다(따라서 Random I/O가 수반되게 된다). 쿼리가 인덱스에 명시된 컬럼들만으로 조건을 처리가능하다면(즉 커버링 인덱스) Index Full Scan처럼 테이블 레코드들을 읽어오는 과정은 없다. 

Loose Index Scan 

인덱스에서 필요한 부분만 선택적으로 스캔한 뒤 이에 대응하는 레코드들을 읽는 방식이다. Index Range Scan과 비슷하게 동작하나, 필요한 인덱스 키와 불필요한 인덱스 키를 구분한 뒤 불필요한 인덱스 키는 무시하는 식으로 동작한다. 보통 GROUP BY, MAX, MIN 등이 사용된 쿼리를 최적화할 때 사용된다.

Index Skip Scan

인덱스는 두 개 이상의 컬럼에 대해서도 만들어줄 수 있는데(멀티 컬럼 인덱스 등으로 부름), 이 때 컬럼 순서에 따라 인덱스가 어떤 기준으로 정렬되는지가 결정된다.(예를 들어 A, B 순서로 인덱스를 만들었다면 A에 대해 먼저 정렬된 다음 B에 대해 정렬됨) 이때 WHERE절에 멀티 컬럼 인덱스의 첫 컬럼이 없으면 원래 인덱스를 사용할 수 없으나, 첫 컬럼이 없음에도 불구하고 인덱스를 사용할 수 있는 스캔 방식이 이 Index Skip Scan이다. 

예로, student테이블에 대해 다음과 같이 gender, address에 대해 인덱스가 걸려있다고 해보자.

ALTER TABLE students ADD INDEX ix_gender_address (gender, address);

이때다음 쿼리는 원래는 Full Table Scan 또는 Index Full Scan(커버링 인덱스인 경우)으로 수행될 것이다.

SELECT gender, address
FROM students
WHERE address LIKE "경기도%";

하지만 Index Skip Scan을 사용하면 WHERE절에서 쓰이지 않은 인덱스의 선행 컬럼에 대해 가능한 값들을 구한 다음, 해당 컬럼의 조건을 추가해 쿼리를 다시 실행하는 형태로 처리하게 된다. 즉 여기서는 WHERE절에서 쓰이지 않은 gender라는 컬럼에 대해, 해당 컬럼에서 유니크한 값들을 모두 뽑은 다음 다음과 같은 형태의 쿼리를 다시 실행하게 된다.

SELECT gender, address
FROM students
WHERE gender = "M" AND address LIKE "경기도%";

SELECT gender, address
FROM students
WHERE gender = "F" AND address LIKE "경기도%";

참고로, Index Skip Scan은 WHERE절에서 쓰이지 않는 선행컬럼의 유니크한 값의 개수가 적어야 하고(많으면 오히려 쿼리를 처리하는 속도가 더 느려질 수 있기 때문), 커버링 인덱스를 만족해야 한다는 단점이 있다.

배경

다음과 같은 관계를 갖는 테이블이 있다고 하자.

member는 회원들의 회원번호와 가입일을,

sales는 회원별 물품을 주문한 정보를,

menu는 각 상품 정보를 가지는 테이블이다.

이때, 현재 members, sales, menu에 다음과 같이 데이터들이 적재돼있다고 하자.

1. members

2. sales

3. menu

주어진 문제

각 고객들이 구입한 첫 물건은?

sales 테이블에는 각 회원들이 특정 물품을 구입한 날에 대한 데이터들이 적재돼있으니, 이 테이블에서 적절한 쿼리를 활용해볼 수 있을 것이다. 이 때 처음 물품을 구매한 날짜에 여러 물품을 구매했다면, 해당 물품들의 정보를 모두 출력해야 한다.

풀이1 : GROUP BY를 활용한 접근

sales테이블에 있는 구매한 물품들에 대한 상세정보는 sales와 menu의 product_id들을 INNER JOIN을 걸어 알아낼 수 있다. 그러나 회원별로 가장 처음 구매한 물품들에 대한 정보만 필요하다. sales에서 customer_id로 GROUP BY를 걸고, 그 안에서 MIN함수를 통해 가장 낮은 order_date를 뽑아내면 다음과 같이 회원별로 물품을 처음 구매한 날짜를 추출할 수 있다.

SELECT customer_id, min(order_date)
FROM sales
GROUP BY customer_id;

위 결과를 WHERE절에서 활용, sales테이블에서 다음과 같이 회원별로 해당 날짜에 대한 데이터들을 추출 가능하다.

SELECT *
FROM sales
WHERE (customer_id, order_date) in (
    SELECT customer_id, min(order_date)
    FROM sales
    GROUP BY customer_id
);

그러나 위 결과에서 4, 5번 레코드를 보면 중복되는 레코드들임을 알 수 있다. C는 2021년 1월 1일에 3번 물품을 2번 구매했기 때문에 이런 결과가 생기는 것이다. 따라서 다음과 같이 DISTINCT를 걸어줘서 중복을 제거할 수 있다.

✅ DISTINCT

: SQL에서 중복된 값을 제거하여 결과 집합에서 고유한 값을 반환하는 데 사용되는 키워드. DISTINCT 뒤에 2개 이상의 컬럼을 사용하면, 해당 컬럼들로 구성되는 조합들에 대한 중복을 제거하는 식으로 동작한다.

(즉, 아래 예제에서는 customer_id, order_date, product_id로 이뤄지는 조합에서 중복이 없어진다)

SELECT DISTINCT customer_id, order_date, product_id
FROM sales
WHERE (customer_id, order_date) in (
    SELECT customer_id, min(order_date)
    FROM sales
    GROUP BY customer_id
);

이제 menu와 INNER JOIN을 걸어 다음과 같이 처음 구매한 물품의 상세정보를 뽑아낼 수 있다.

SELECT DISTINCT customer_id, order_date, sales.product_id, product_name, price
FROM sales
INNER JOIN menu ON sales.product_id = menu.product_id 
WHERE (customer_id, order_date) in (
    SELECT customer_id, min(order_date)
    FROM sales
    GROUP BY customer_id
);

쿼리를 좀만 다듬으면(사실 SELECT절에서 필요한 정보만 뽑도록 하면), 다음과 같이 회원 아이디와 상품명만 추출 가능하다.

SELECT DISTINCT customer_id, product_name
FROM sales
INNER JOIN menu ON sales.product_id = menu.product_id 
WHERE (customer_id, order_date) in (
    SELECT customer_id, min(order_date)
    FROM sales
    GROUP BY customer_id
);

풀이 2 : PARTITION BY를 이용한 접근

그러나 이 문제는 PARTITION BY를 이용해서도 풀 수 있다.

✅ PARTITION BY?

: 특정 그룹 내에서의 집계 또는 순위를 매길 때 사용하는 함수.

(집계함수 : MIN, MAX, SUM 등을 말함)

(순위함수 : ROW_NUMBER, RANK 등을 말함)

🧐 GROUP BY와의 차이점?

: GROUP BY, PARTITION BY 모두 특정 컬럼을 기준으로 레코드들을 그룹핑한다는 공통점이 있다. 그러나 GROUP BY는 그룹핑되는 과정에서 레코드들의 상세 정보들이 없어지는 반면(레코드들이 집약되기 때문), PARTITION BY는 그룹핑돼도 레코드들의 상세 정보가 남아있게 된다.

sales테이블에 대해 회원별로 구매 횟수를 알고 싶다고 할 때, GROUP BY와 PARTITION BY에 대한 다음 결과를 비교해보면 더욱 잘 이해할 수 있다.

1) GROUP BY로 그룹핑해서 계산하는 경우

SELECT customer_id, count(*) as purchased_count
FROM sales
GROUP BY customer_id;

sales 테이블이 갖고 있던 레코드들이 나오는게 아니라, 집계 결과만 나오는 모습을 확인 가능하다. 만약 order_date나 product_id를 SELECT에서 찍어서 보려고 하면, 1055 ERROR가 난다 (GROUP BY가 사용된 경우, GROUP BY로 그룹핑할 때 사용된 컬럼만 SELECT에서 명시가 가능하기 때문)

2) PARTITION BY로 그룹핑해서 계산하는 경우

SELECT customer_id, count(*) OVER (PARTITION BY customer_id) as purchased_count
FROM sales;

sales테이블이 갖고 있는 레코드들이 살아있음을 확인할 수 있다. SELECT절에서도 order_date, product_id컬럼값을 뽑아낼 수 있으며 에러가 나지 않는다.

SELECT customer_id, order_date, product_id, count(*) OVER (PARTITION BY customer_id) as purchased_count
FROM sales;

RANK순위 함수와 ORDER BY를 사용하면 그룹핑된 섹션 내에서 order_date를 기준으로 다음과 같이 순위를 매길 수 있다.

SELECT
    customer_id,
    order_date,
    product_name,
    RANK() OVER (PARTITION BY customer_id ORDER BY order_date) as order_date_rank
FROM sales
INNER JOIN menu ON sales.product_id = menu.product_id;

그룹핑된 각 섹션 내에서 order_date값이 같으면 같은 rank가 매겨진다. RANK와는 달리 DENSE_RANK를 사용하면 동일한 등수가 발생해도 다음번 등수는 이전 등수 + 1이 된다.

위 결과를 WHERE절에서 활용, order_date_rank가 1인 레코드만 추출하면 원하는 결과를 가져올 수 있다. 

WITH ranked_date as (
    SELECT
        customer_id,
        order_date,
        product_name,
        RANK() OVER (PARTITION BY customer_id ORDER BY order_date) as order_date_rank
    FROM sales
    INNER JOIN menu ON sales.product_id = menu.product_id
)

SELECT DISTINCT customer_id, product_name
FROM ranked_date
WHERE order_date_rank = 1;

더 적합한 방식은?

위에서 소개한 GROUP BY와 PARTITION BY 모두 같은 결과를 뱉는다. 그러면 둘 중 어느 것이 좀 더 "적합"할까? 어느 것이 더 효율적으로 동작하는가? 지금은 sales테이블에 데이터가 끽해야 15개 남짓이지만, x억개 있는 상황이라면 뭐가 더 나을까?

쿼리 실행 계획을 살펴보며 어떤 것이 더 나을지 살펴보자.

문제 출처 : 8 week SQL Challenge

https://8weeksqlchallenge.com/