개발공부 기록지

When and How You Should Denormalize a Relational Database 정리한 내용

웹사이트의 속도가 빠를수록 검색 엔진에서 높은 순위를 차지하며 사용자 경험도 개선됩니다.

데이터베이스 최적화는 웹사이트 성능 향상에 필수적인 과정입니다. 일반적으로 개발자들은 데이터 중복을 줄이고 무결성을 향상시키기 위해 관계형 데이터베이스를 정규화합니다. 즉, 데이터베이스를 재구성하는 것입니다.

그러나 가끔은 정규화만으로는 충분하지 않을 때가 있습니다. 이런 경우, 성능 향상을 위해 개발자들은 데이터베이스를 비정규화하기도 합니다.

데이터베이스를 언제 비정규화해야 할까

정규화는 데이터베이스 설계의 첫 단계로, 데이터를 적절한 위치에 배치하여 무결성을 보장하고 업데이트를 용이하게 합니다. 이 과정은 중복을 제거하여 데이터 관리를 효율적으로 만듭니다. 그러나, 데이터가 여러 테이블에 분산되어 있으면, 쿼리가 복잡해지고 데이터 검색 속도가 느려질 수 있습니다.

반면, 비정규화는 데이터 검색 속도를 높이기 위해 동일한 데이터를 의도적으로 여러 위치에 저장하는 방법입니다. 이 방식은 데이터베이스의 응답 시간을 단축시키지만, 업데이트 과정에서 일관성 유지에 더 많은 노력을 요구할 수 있습니다.

따라서, 비정규화는 검색 성능을 중시하는 특정 상황에서 선택적으로 사용되어야 합니다. 데이터의 중복을 허용함으로써 빠른 검색을 제공하지만, 데이터 관리의 복잡성과 오류 가능성을 증가시킬 수 있기 때문입니다.

1. 쿼리 성능 향상

정규화된 데이터베이스는 데이터의 무결성을 유지하기 위해 여러 테이블로 데이터를 분리합니다. 하지만, 이 구조에서는 쿼리가 여러 테이블에 걸쳐 조인을 수행해야 하기 때문에 쿼리 성능이 저하될 수 있습니다. 이 문제를 해결하기 위해, 자주 사용되는 데이터를 부모 테이블에 복사하여 중복을 허용함으로써 조인을 줄일 수 있습니다. 이 방법은 데이터 검색 속도를 향상시키지만, 데이터 동기화에 주의가 필요합니다.

2. 운영 편의성 증가를 위한 데이터 베이스의 비정규화

정규화 과정에서는 계산된 값들을 따로 저장하지 않고, 필요할 때마다 실시간으로 계산합니다. 이는 데이터베이스의 쿼리 실행 속도를 늦출 수 있습니다. 비정규화를 통해 계산된 값을 사전에 테이블에 저장함으로써, 개발자들이 복잡한 계산 로직을 이해하지 않고도 보고서나 쿼리를 쉽게 작성할 수 있도록 도움을 줍니다.

3. 리포팅 가속화와 용이성 향상

애플리케이션은 종종 다양한 분석 및 통계 정보를 제공해야 할 필요가 있습니다. 정규화된 데이터베이스 구조에서는 보고서 작성 시 많은 계산과 데이터 집계가 필요하여, 이 과정이 시스템의 성능을 저하시킬 수 있습니다. 비정규화를 통해 예를 들어, 사용자 테이블에 연간 누적 매출 요약 같은 중요한 정보를 미리 계산하여 저장함으로써, 리포팅을 빠르고 쉽게 만들 수 있습니다. 이러한 접근 방식은 데이터 처리 시간을 단축시켜 성능 저하를 최소화하는 데 도움이 됩니다.

데이터베이스 비정규화 기법 요약

1. 유도 가능한 데이터 저장

자주 계산해야 하는 값을 미리 저장하여 조회 속도를 높인다.

• 장점: 매번 계산할 필요 없음, 조회 성능 향상
• 단점: 원본 데이터 변경 시 재계산 필요, 데이터 불일치 가능성
• 예시: 이메일 메시지 시스템에서 users_received_count를 추가하여 수신자가 모두 삭제되면 메시지를 자동 삭제하도록 함.

2. 사전 조인된 테이블 사용

조인을 최소화하기 위해 비즈니스적으로 중요한 컬럼이 아닌 값을 테이블에 추가한다.

• 장점: 조인 없이 빠른 조회 가능
• 단점: 업데이트 시 추가적인 DML((Data Manipulation Language) 필요, 추가 저장 공간 사용
• 예시: User_messages 테이블에 category_name 컬럼 추가 → 만약 Categories 테이블에서 category_name이 변경되면, User_messages 테이블의 category_name도 일괄적으로 업데이트되어야 한다.

3. 하드코딩된 값 사용

변하지 않는 값을 참조 테이블 대신 애플리케이션에서 하드코딩하여 조회 속도를 향상시킨다.

• 장점: 참조 테이블 조인 불필요
• 단점: 값 변경 시 코드 수정 필요
• 예시: User_kinds 테이블 대신 사용자 유형을 하드코딩하고, 체크 제약 조건을 설정.

4. 마스터 테이블에 상세 정보 포함

상세 테이블의 레코드 개수가 적거나 마스터 테이블과 함께 조회될 경우, 상세 데이터를 마스터 테이블에 포함한다.

• 장점: 조인 불필요, 저장 공간 절약 가능
• 단점: DML 복잡성 증가
• 예시: Users 테이블에 message_space_allocated, message_space_available 등의 저장 공간 제한 정보를 추가.

5.  특정 세부 정보 반복 저장

특정한 단일 레코드를 자주 조회하는 경우, 해당 값을 별도 외래키 컬럼에 저장하여 조인을 최소화한다.

• 장점: 단일 레코드 조회 시 조인 필요 없음
• 단점: 데이터 불일치 가능성 존재
• 예시: Messages 테이블에 first_attachment_name을 추가하여 첫 번째 첨부파일 정보를 빠르게 조회.

6. 단축 키(short-circuit key) 추가

마스터-디테일 관계가 3단계 이상일 때, 최상위 레벨과 최하위 레벨을 직접 연결하는 단축 키를 추가한다.

• 장점: 조인 개수 감소
• 단점: 외래 키 증가, 값 일관성 유지 필요
• 예시: Messages 테이블에 Users 테이블의 기본 키를 직접 추가하여 Categories 테이블을 거치지 않고 조회 가능하게 함.

비정규화의 단점 및 고려사항

비정규화는 성능을 향상시키지만 여러 단점이 존재함.

• 추가 저장 공간 필요: 데이터 중복으로 인해 저장 공간 증가
• 문서화 필요: 비정규화된 데이터 구조는 철저히 문서화해야 유지보수 가능
• 데이터 이상 발생 가능: 중복된 데이터의 동기화 필요
• 추가 코드 필요: 비정규화로 인해 더 많은 업데이트 로직 요구
• 쓰기 성능 저하: 조회 속도는 빨라지지만, 데이터 수정 및 삽입이 느려질 수 있음

비정규화 적용시 주의할 점

• 전체 데이터베이스가 아닌 특정 성능 저하 부분만 비정규화할 것
• 애플리케이션의 논리적 설계를 철저히 분석한 후 적용
• 데이터 변경 빈도를 고려하여 유지보수가 가능한지 검토
• 쿼리 최적화를 통해 성능 문제를 해결할 수 있는지 먼저 확인
• 필요할 경우 가장 적절한 데이터 저장 기법을 선택하여 적용

Mock 이란?

정의

소프트웨어 테스트에서 실제 객체를 대신하여 사용하는 '가짜 객체'를 의미

사용하는 이유

• 테스트 환경을 구현하기 힘든 경우
• 테스트 대상이 외부 시스템과의 의존성이 높아 직접 테스트하기 힘든 경우 
• 테스트하기 힘든 상황이 기대한 대로 상호작용하는지 검증하기 위한 경우 
• 테스트 실행에 많은 시간이 걸리는 경우

사용되는 경우

• 데이터베이스 연동 테스트
• 외부 API 호출 테스트
• 네트워크 통신 테스트
• 파일 시스템 접근 테스트

사용하지 말아야하는 경우

• 순수 함수 : reduce, map 등등 은 입력이 같으면 항상 같은 결과를 반환
• 간단한 상태 관리 로직 
• 단순한 의존성 또는 데이터 구조를 사용하는 경우
• 실제 동작 검증이 필요한 경우
• 과도한 mocking 으로 인해 테스트 유지보수가 어려운 경우
• Mock 객체의 설정이 실제 환경과 크게 다를 경우
• 상태 검증 기반이 더 적합한 경우

종류

• 스텁(stub) : 특정한 입력에 대해 미리 정해진 결과를 반환하도록 하드 코딩된 객체
• 페이크 객체 (fake object) : 실제로 동작하는 것처럼 보이지만, 프로덕션 환경에서 사용하기에는 단순화된 구현을 가진 객체
• 스파이 (spy) : 실제 객체를 부분적으로 목킹하여, 호출된 메서드나 파라미터 등을 기록하고 검증할 수 있는 객체
• Mock 객체 (mock object) : 실제 객체의 행위를 흉내내어 행위 기반 테스트를 위해 사용. 호출 여부, 호출 횟수, 전달된 파라미터 등을 검증가능

이점

• 외부 의존성(API, 데이터베이스 등) 없이 테스트 가능
• 테스트 실행 속도 향상
• 특정 상황(에러, 타임아웃 등)을 쉽게 시뮬레이션
• 함수 호출 여부, 인자, 횟수 등을 검증 가능

Mock 객체 사용 여부 판단 기준

1. 이 코드가 외부 리소스에 의존하는가?
   • API 호출
   • 데이터베이스 접근
   • 파일 시스템 사용 : 파일 읽기/쓰기 가 필요한 경우
2. 테스트 실행이 느리거나 비용이 드는가?
   • 무거운 연산
   • 네트워크 지연
   • API 호출 비용
3. 테스트 결과가 비결정적인가?
   • 랜덤 값 사용 : 무작위 값에 따라 결과가 달라지는 경우
   • 시간에 의존적인 로직 : 현재 시간이나 날짜에 따라 동작이 변하는 경우
   • 동시성 이슈 : 멀티스레딩 등으로 인해 겨로가가 일정하지 않은 경우 
4. 테스트 셋업이 복잡한가?
   • 많은 의존성 필요 : 여러 모듈이나 서비스에 의존하는 경우
   • 복잡한 상태 설정 필요

(단순한 예시) 테스트를 위해서 만들어진 user 객체 ?

▶ user 는 테스트를 위해서 임의로 만들어진 단순한 데이터 객체이지 행위는 없다. 그러므로 Mock 객체라고 할 수 없다. 

분명 인덱스를 걸었음에도 풀 테이블 스캔으로 데이터를 조회하는 경우가 있다.

경우 1 : 넓은 범위의 데이터를 조회하는 경우

옵티마이저가 넓은 범위의 데이터를 조회할 때는 인덱스를 활용하는 것이 비효율적이라고 판단한다. 왜냐면, 굳이 인덱스를 거쳤다가 각 원래 테이블의 데이터를 일일이 하나씩 찾아내는 것보다, 바로 원래 테이블에 접근해서 모든 데이터를 통째로 가져와서 정렬하는게 효율적이라고 판단하기 때문이다.

(예시)

SELECT * FROM users 
ORDER BY name DESC;

  ▶ 이런 경우 인덱스를 활용하지 않고 풀 테이블 스캔으로 데이터를 찾을 때 훨씬 효율적이라고 판단

넓은 범위의 데이터를 조회하는 경우, 인덱스를 사용해서 조회하는 것보다 풀 테이블 스캔을 이용하는 것이 효과적.

* 최적화를 위하여 넓은 범위의 데이터를 조회하는지 잘 파악이 안된다면, 인덱스를 적용한 후에 실행 계획 조회해 보자.

그림에 보이듯이 인덱스를 했음에도 불구하고 trype이 ALL 인 것을 볼 수 있다. 

  → 풀 테이블 스캔으로 데이터를 찾을 때 훨씬 효율적

CREATE INDEX idx_name ON users (name);

EXPLAIN SELECT * FROM users 
ORDER BY name DESC;

경우 2 : 인덱스 컬럼을 가공(함수 적용, 산술 연산, 문자열 조작 등 )을 한 경우

id, name, salary, created_at 콜롬들을 가진 users 테이블에 100만 건의 랜덤 데이터 삽입

인덱스 생성

CREATE INDEX idx_name ON users (name);
CREATE INDEX idx_salary ON users (salary);

실행 계획 조회해보기

# User000000으로 시작하는 이름을 가진 유저 조회
EXPLAIN SELECT * FROM users
WHERE SUBSTRING(name, 1, 10) = 'User000000';

# 2달치 급여(salary)가 1000 이하인 유저 조회
EXPLAIN SELECT * FROM users
WHERE salary * 2 < 1000
ORDER BY salary;

첨부된 결과 사진에 있는 타입에 보이듯이 ALL 이다. 즉, 풀테이블 스캔을 한 것을 볼 수 있다.

인덱스 컬럼을 가공해서 사용하지 않게 SQL문 수정하기

# User000000으로 시작하는 이름을 가진 유저 조회
EXPLAIN SELECT * FROM users
WHERE name LIKE 'User000000%'; # % 와일드 카드: 0개 이상의 어떤 문자든지 매칭될 수 있음

# 2달치 급여(salary)가 1000 이하인 유저 조회
EXPLAIN SELECT * FROM users
WHERE salary < 1000 / 2
ORDER BY salary;

위 두 결과 그림에서 type 을 보면 인덱스가 제대로 활용 된 것을 볼 수 있다.

인덱스를 활용하기 위해서는 인덱스 컬럼 자체를 최대한 가공하지 않아야 한다.

최근 3일 이내에 가입한 유저 조회하기

데이터 조회

SELECT * FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

인덱스를 추가하기전

인덱스 추가 후

CREATE INDEX idx_created_at ON users (created_at);

created_at 에 인덱스를 추가함으로써 검색 타입이 ALL 에서 range 로 바뀌고 소요시간이 약 0.35 초 에서 0.056 초로 감소

Sales 부서이면서 최근 3일 이내에 가입한 유저 조회하기

데이터 생성

DROP TABLE IF EXISTS users; 

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

100만건 데이터 생성 후 데이터 조회해서 성능 측정

SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY)

실행계획 확인

EXPLAIN SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

#세부내용
EXPLAIN ANALYZE SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

-> Filter: ((users.department = 'Sales') and (users.created_at >= <cache>((now() - interval 3 day))))  (cost=93877 rows=33224) (actual time=3.34..243 rows=115 loops=1)
    -> Table scan on users  (cost=93877 rows=996810) (actual time=0.148..191 rows=1e+6 loops=1)

성능개선 할 수 있는 방법들

1. created_at 컬럼을 기준으로 인덱스 추가

2. department 컬럼을 기준으로  인덱스 추가

3. department 와 created_at 두 컬럼에  인덱스 추가

4. 멀티 컬럼 인덱스 = (created_at, department) ? (department, created_at) ?

< created_at 컬럼을 기준으로 인덱스 추가 > 

CREATE INDEX idx_created_at ON users (created_at);

#성능평가
SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

# 실행 계획
EXPLAIN SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

# 실행 계획 세부 내용
EXPLAIN ANALYZE SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

0.24초 에서 0.033 초로 소요시간 단축

type = range , 모든 테이블을 검색하지 않고 idx_created_at 인덱스 레인지 스캔을 함 데이터 접근 개수(rows)도 줄어들었다.

< department 컬럼을 기준으로 인덱스 추가 > 

ALTER TABLE users DROP INDEX idx_created_at; # 기존 created_at 인덱스 삭제
CREATE INDEX idx_department ON users (department);

# 성능 측정
SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

# 실행 계획
EXPLAIN SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

# 실행 계획 세부 내용
EXPLAIN ANALYZE SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

약 0.13초 소요 created_at 컬럼을 기준으로 인덱스 추가 한 경우가 소요시간이 훨씬 짧음

ref 타입, = 비고유 인덱스로 조회를 했다 rows = 191,314 는 인덱스를 하기전 보다 좋아 졌지만 created_at 컬럼을 기준으로 인덱스 추가  한 경우보다 많은 데이터에 접근

<department 와 created_at 두 컬럼에  인덱스 추가>

# CREATE INDEX idx_department ON users (department); # 위에서 이미 추가함
CREATE INDEX idx_created_at ON users (created_at); # created_at 인덱스 추가

# 성능 측정
SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

# 실행 계획
EXPLAIN SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

# 실행 계획 세부 내용
EXPLAIN ANALYZE SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

소요 시간은 idx_created_at 과 비슷

idx_created_at 과 똑같이 range 타입으로 일정 범위만 검색

-> Filter: (users.department = 'Sales')  (cost=498 rows=212) (actual time=0.0929..11.6 rows=114 loops=1)
    -> Index range scan on users using idx_created_at over ('2024-12-27 16:39:14' <= created_at), with index condition: (users.created_at >= <cache>((now() - interval 3 day)))  (cost=498 rows=1106) (actual time=0.062..11.3 rows=1106 loops=1)

디테일을 확인해 보면 idx_created_at 만 사용

결론적으로 idx_department 을 사용하지 않았다는 것은 department 컬럼에 한 인덱스가 필요가 없고 판단하여 idx_department는 의미 성능평가에서도 비슷하므로 created_at 컬럼에만 인덱스를 하는 것이 가장 효율적인 최적화 방법으로 볼수 있다.

[이것만은 꼭 기억해두자!] 

데이터 액세스(rows)를 크게 줄일 수 있는 컬럼은 중복 정도가 낮은 컬럼이다.

따라서 중복 정도가 낮은 컬럼을 골라서 인덱스를 생성해라.

< 멀티 컬럼 인덱스 >

공통 : 성능테스트

SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

순서 (created_at, department) 

ALTER TABLE users DROP INDEX idx_created_at;
ALTER TABLE users DROP INDEX idx_department;
CREATE INDEX idx_created_at_department ON users (created_at, department);

성능 테스트 결과 :  소요시간 약 0.0035 초

순서 (department, created_at)

ALTER TABLE users DROP INDEX idx_created_at_department;
CREATE INDEX idx_department_created_at ON users (department, created_at);

성능 테스트 결과 : 소요시간 약 0.0033초

멀티 컬럼 인덱스를 사용했지만 created_at 만 인덱스를 설정했을 때의 성능과 크게 차이가 나지 않는다.

 ’단일 컬럼에 설정하는 일반 인덱스’를 설정했을 때와 ‘멀티 컬럼 인덱스를 설정했을 때’의 성능 차이가 별로 나지 않는다면 ?

▶ 멀티 컬럼 인덱스를 사용하지 말고 일반 인덱스를 활용하자.

비교한 인덱스 방식의 성능 테스트 비교 표

참고 강의

(인프런) 비전공자도 이해할 수 있는 MySQL 성능 최적화 입문/실전

실제 페이스북, 인스타그램의 서비스를 보더라도 한 번에 모든 게시글의 데이터를 불러오지 않는다. 

→ 페이지네이션을 이용하여 일부 데이터만 조회

예를 들어 100만 건의 데이터가 있을 때 하나의 데이터를 찾기 위해 모든 데이터를 불러오면 성능에 영향을 끼친다.

→ 조회하는 데이터의 개수가 성능에 많은 영향

조회하는데 걸리는 시간 측정 (100만 유저가 있는 데이터베이스)

전부조회

SELECT * FROM users;

제한된 조회 10000 건

SELECT * FROM users LIMIT 10000;

제한된 조회 100 건

조회하는 데이터의 개수가 많을 수록 소요시간이 많아진다.

시스템 성능 저하를 방지하기위해 해야할 것

1. 데이터를 조회할 때 한 번에 너무 많은 데이터를 조회하는 건 아닌 지 체크

2. LIMIT, WHERE 문 등을 활용해서 한 번에 조회하는 데이터의 수를 줄이는 방법을 고려

참고 강의

(인프런) 비전공자도 이해할 수 있는 MySQL 성능 최적화 입문/실전