SQL at Scale: 최적화를 위한 최고의 실습

서문

대규모 테이블 최적화는 오랜 기간 논의되어 온 주제이지만, 비즈니스 규모가 커짐에 따라 누군가는 항상 이 문제로 인해 "화상"을 입게 됩니다.

많은 데이터베이스가 처음에는 성능이 좋아서 쿼리가 원활하게 실행되지만, 테이블의 데이터 양이 수천만 건에 도달하면 성능 문제가 나타나기 시작합니다. 쿼리 속도 저하, 쓰기 지연, 페이징 속도 저하, 심지어 시스템 충돌까지 발생할 수 있습니다.

그때쯤 되면 다음과 같은 의문이 들기 시작할 수 있습니다. 데이터베이스가 충분히 좋지 않은가? 더 나은 하드웨어로 업그레이드해야 할까?

실제로 근본적인 원인은 종종 최적화 부족입니다.

오늘 우리는 문제의 핵심부터 시작하여 대규모 테이블의 일반적인 성능 병목 현상을 단계별로 분석하고, 한 번에 하나씩 최적화하는 방법을 살펴볼 것입니다. 바라건대 이것이 여러분에게 도움이 될 것입니다.

1. 대규모 테이블이 느린 이유

최적화에 뛰어들기 전에 먼저 대규모 테이블의 성능 문제의 근본 원인을 이해해 봅시다. 데이터 양이 증가하면 데이터베이스 속도가 느려지는 이유는 무엇일까요?

1.1 디스크 I/O 병목 현상

대규모 테이블 데이터는 디스크에 저장되며, 데이터베이스 쿼리는 일반적으로 디스크에서 데이터 블록을 읽는 것을 포함합니다.

데이터 세트가 매우 클 때 단일 쿼리는 여러 디스크 블록에서 많은 양의 데이터를 읽어야 할 수 있습니다. 디스크의 읽기/쓰기 속도는 쿼리 성능을 직접적으로 제한합니다.

예:

5천만 건의 레코드가 있는 orders라는 주문 테이블이 있고, 사용자의 최신 주문 10건을 쿼리하고 싶다고 가정해 보겠습니다.

SELECT * FROM orders WHERE user_id = 123 ORDER BY order_time DESC LIMIT 10;

인덱스가 없으면 데이터베이스는 전체 테이블을 스캔한 다음 결과를 정렬하므로 성능이 저하될 수밖에 없습니다.

1.2 인덱스 누락 또는 비효율적인 인덱스

쿼리가 인덱스를 적중하지 않으면 데이터베이스는 전체 테이블 스캔을 수행하여 모든 행을 하나씩 읽습니다.

이 작업은 수천만 건의 레코드가 있는 경우 리소스를 매우 많이 사용하며 성능이 급격히 저하됩니다.

예:

다음과 같은 쿼리를 실행한다고 가정해 보겠습니다.

SELECT * FROM orders WHERE DATE(order_time) = '2025-01-01';

여기서 DATE() 함수가 적용되므로 데이터베이스는 모든 레코드에 대해 order_time 값을 계산해야 하므로 인덱스가 비효율적이 됩니다.

1.3 페이징 성능 저하

페이징은 대규모 테이블에서 흔히 사용되지만, 깊은 페이징(예: 100페이지 이후)은 성능 문제를 일으킵니다.

10개의 레코드만 필요하더라도 데이터베이스는 먼저 이전의 모든 레코드를 스캔해야 합니다.

예:

1000페이지에서 10개의 레코드를 쿼리합니다.

SELECT * FROM orders ORDER BY order_time DESC LIMIT 9990, 10;

이 SQL은 데이터베이스에 처음 9990개의 레코드를 가져와서 버리고 다음 10개를 반환하도록 지시합니다.

페이지 번호가 증가함에 따라 쿼리 성능이 계속 저하됩니다.

1.4 잠금 경합

높은 동시성 시나리오에서 여러 스레드가 동시에 동일한 테이블에 대해 삽입, 업데이트, 삭제 또는 선택 작업을 수행하면 행 잠금 또는 테이블 잠금 경합이 발생하여 성능에 영향을 미칠 수 있습니다.

2. 성능 최적화에 대한 전반적인 접근 방식

성능 최적화의 본질은 불필요한 I/O, 계산 및 잠금 경합을 줄이는 것입니다. 목표는 데이터베이스의 "낭비되는 노력"을 최소화하는 것입니다.

일반적인 최적화 접근 방식은 다음과 같이 요약할 수 있습니다.

• 합리적인 테이블 구조 설계: 불필요한 필드를 피하고 가능한 한 데이터를 분할합니다.
• 효율적인 인덱싱 사용: 적절한 인덱스 구조를 설계하고 인덱스 무효화를 피합니다.
• SQL 쿼리 최적화: 조건을 정확하게 만들고 가능한 한 전체 테이블 스캔을 피합니다.
• 테이블 분할 적용: 수평 또는 수직 분할을 사용하여 테이블당 데이터 양을 줄입니다.
• 캐싱 및 비동기화 활용: 데이터베이스에 대한 직접적인 압력을 줄입니다.

다음으로 각 부분을 자세히 살펴보겠습니다.

3. 테이블 구조 최적화

테이블 구조는 데이터베이스 성능 최적화의 기초를 형성합니다. 테이블 구조가 잘못 설계되면 쿼리 및 스토리지 성능 모두에서 심각한 문제가 발생할 수 있습니다.

3.1 필드 유형 간소화

필드 유형은 스토리지 크기 및 쿼리 성능을 결정합니다.

• 가능한 경우 BIGINT 대신 INT를 사용합니다.
• 적절한 경우 TEXT 대신 VARCHAR(100)을 사용합니다.
• 시간 필드의 경우 CHAR 또는 VARCHAR 대신 TIMESTAMP 또는 DATETIME을 사용하는 것이 좋습니다.

예:

-- 권장하지 않음
CREATE TABLE orders (
    id BIGINT,
    user_id BIGINT,
    order_status VARCHAR(255),
    remarks TEXT
);

-- 최적화됨
CREATE TABLE orders (
    id BIGINT,
    user_id INT UNSIGNED,
    order_status TINYINT,  -- 상태에 enum 사용
    remarks VARCHAR(500)   -- 최대 길이 설정
);

이렇게 하면 스토리지 공간을 절약하고 쿼리 성능을 향상시키는 데 도움이 됩니다.

3.2 테이블 분할: 수직 및 수평 분할

수직 분할

테이블에 필드가 너무 많고 일부 필드가 자주 쿼리되지 않는 경우 비즈니스 로직에 따라 여러 개의 더 작은 테이블로 분할할 수 있습니다.

예: orders 테이블을 orders_basic 및 orders_details의 두 테이블로 분할합니다.

-- 기본 정보 테이블
CREATE TABLE orders_basic (
    id BIGINT PRIMARY KEY,
    user_id INT UNSIGNED,
    order_time TIMESTAMP
);

-- 세부 정보 테이블
CREATE TABLE orders_details (
    id BIGINT PRIMARY KEY,
    remarks VARCHAR(500),
    shipping_address VARCHAR(255)
);

수평 분할

단일 테이블에 레코드가 너무 많은 경우 특정 규칙에 따라 여러 테이블로 분할할 수 있습니다.

예: 사용자 ID별로 주문 테이블을 분할합니다.

orders_0 -- user_id % 2 = 0인 주문 저장
orders_1 -- user_id % 2 = 1인 주문 저장

분할 후 테이블당 레코드 수가 크게 줄어 쿼리 성능이 크게 향상됩니다.

4. 인덱스 최적화

인덱스는 데이터베이스 성능 최적화를 위한 "주요 무기"입니다. 그러나 많은 개발자가 인덱스를 효과적으로 사용하는 방법을 잘 알지 못하여 성능이 향상되기보다는 저하될 수 있습니다.

4.1 적절한 인덱스 생성

기본 키, 외래 키 및 쿼리 조건에 사용되는 필드와 같은 고주파 쿼리 필드에 대한 인덱스를 만듭니다.

예:

CREATE INDEX idx_user_id_order_time ON orders (user_id, order_time DESC);

위의 복합 인덱스는 user_id로 필터링하고 order_time으로 동시에 정렬하는 쿼리 속도를 높일 수 있습니다.

4.2 인덱스 무효화 방지

인덱싱된 필드에서 함수 또는 연산을 사용하지 마십시오.

잘못됨:

SELECT * FROM orders WHERE DATE(order_time) = '2023-01-01';

최적화됨:

SELECT * FROM orders WHERE order_time >= '2023-01-01 00:00:00'
  AND order_time < '2023-01-02 00:00:00';

암시적 유형 변환에 주의하십시오.

잘못됨:

SELECT * FROM orders WHERE user_id = '123';

최적화됨:

SELECT * FROM orders WHERE user_id = 123;

매개변수의 데이터 유형이 필드 유형과 일치하지 않으면 데이터베이스가 암시적 변환을 수행하여 인덱스가 사용되지 않을 수 있습니다.

5. SQL 최적화

5.1 쿼리된 필드 줄이기

실제로 필요한 필드만 쿼리하십시오. SELECT *를 사용하지 마십시오.

-- 잘못됨
SELECT * FROM orders WHERE user_id = 123;

-- 최적화됨
SELECT id, order_time FROM orders WHERE user_id = 123;

불필요한 필드를 쿼리하면 특히 테이블에 TEXT 또는 BLOB과 같은 큰 필드가 포함된 경우 I/O 및 메모리 사용량이 증가합니다.

5.2 페이징 최적화

깊은 페이징의 경우 "seek method"(키셋 페이징 또는 커서 기반 페이징이라고도 함)를 사용하여 과도한 데이터 스캔을 피하십시오.

-- 깊은 페이징(성능 저하)
SELECT * FROM orders ORDER BY order_time DESC LIMIT 9990, 10;

-- 최적화됨: 커서 사용
SELECT * FROM orders
WHERE order_time < '2023-01-01 12:00:00'
ORDER BY order_time DESC
LIMIT 10;

이 경우 기준점(order_time)을 사용하면 데이터베이스가 불필요한 행을 건너뛰고 다음 배치를 직접 찾을 수 있으므로 대규모 데이터 세트의 성능이 크게 향상됩니다.

6. 데이터베이스 및 테이블 파티셔닝

6.1 수평 데이터베이스 및 테이블 파티셔닝

단일 테이블 분할만으로는 성능 요구 사항을 충족하기에 충분하지 않은 경우 데이터를 여러 데이터베이스 및 테이블에 분산할 수 있습니다.

일반적인 파티셔닝 전략은 다음과 같습니다.

• 사용자 ID별 모듈로
• 시간별 파티션

데이터를 여러 스토리지 위치에 분산함으로써 읽기 및 쓰기 로드를 분산하여 경합을 줄이고 확장성을 향상시킬 수 있습니다.

7. 캐싱 및 비동기화

7.1 Redis를 사용하여 자주 사용하는 데이터 캐싱

고주파 쿼리의 경우 Redis에 데이터를 저장하여 데이터베이스에 대한 직접적인 액세스를 줄입니다.

예:

// 캐시에서 데이터 읽기
String result = redis.get("orders:user:123");
if (result == null) {
    result = database.query("SELECT * FROM orders WHERE user_id = 123");
    redis.set("orders:user:123", result, 3600); // 1시간 동안 캐시
}

이 접근 방식은 지난 30일 이내의 사용자 주문 내역과 같이 자주 변경되지 않는 데이터에 특히 효과적입니다.

7.2 메시지 큐를 사용하여 쓰기를 비동기적으로 처리

높은 동시성 쓰기 시나리오에서 쓰기 작업을 메시지 큐(예: Kafka)에 푸시한 다음 비동기적으로 일괄 처리하여 데이터베이스에 쓸 수 있습니다. 이렇게 하면 데이터베이스에 대한 압력이 크게 줄어듭니다.

예를 들어 주문 생성 요청을 큐에 넣고 백그라운드 작업자가 데이터베이스에 유지하도록 하면 시스템 처리량과 응답성이 크게 향상될 수 있습니다.

8. 실제 사례 연구

문제:

전자 상거래 시스템에 5천만 건의 레코드가 있는 orders 테이블이 있습니다. 사용자가 주문 세부 정보를 쿼리하면 페이지를 로드하는 데 10초 이상 걸립니다.

해결 방법:

• 주문 테이블의 수직 분할: 자세한 필드를 별도의 테이블로 이동합니다.
• 복합 인덱스 생성: user_id 및 order_time에 인덱스를 추가합니다.
• Redis 캐싱 사용: 사용자의 지난 30일 동안의 주문 데이터를 Redis에 캐시합니다.
• 페이징 최적화: 깊은 페이징의 경우 LIMIT 대신 search_after(또는 유사한 커서 기반 기술)를 사용합니다.

이러한 최적화를 통해 쿼리 응답 시간이 10초 이상에서 500밀리초 미만으로 단축되었습니다.

요약

대규모 테이블 성능을 최적화하는 것은 테이블 구조 및 인덱스에서 SQL 쿼리 및 아키텍처 설계에 이르기까지 전체적인 고려가 필요한 체계적인 작업입니다.

수천만 건의 레코드가 있는 데이터 세트는 방대해 보일 수 있지만 적절한 분할, 인덱스 설계 및 캐싱 전략을 통해 데이터베이스는 이러한 워크로드를 쉽게 처리할 수 있습니다.

가장 중요한 것은 특정 비즈니스 시나리오에 가장 적합한 최적화 전략을 선택하는 것입니다. "멋진" 또는 과도하게 엔지니어링된 솔루션을 맹목적으로 추구하지 마십시오.

이러한 교훈과 기술이 여러분에게 도움이 되기를 바랍니다!

---

백엔드 프로젝트 호스팅을 위한 최고의 선택인 Leapcell입니다.

Leapcell은 웹 호스팅, 비동기 작업 및 Redis를 위한 차세대 서버리스 플랫폼입니다.

다국어 지원

• Node.js, Python, Go 또는 Rust로 개발합니다.

무제한 프로젝트를 무료로 배포

• 사용량에 대해서만 지불합니다. 요청 또는 요금이 없습니다.

탁월한 비용 효율성

• 유휴 요금 없이 사용한 만큼만 지불합니다.
• 예: $25는 평균 응답 시간이 60ms인 694만 건의 요청을 지원합니다.

간소화된 개발자 경험

• 간편한 설정을 위한 직관적인 UI.
• 완전 자동화된 CI/CD 파이프라인 및 GitOps 통합.
• 실행 가능한 통찰력을 위한 실시간 메트릭 및 로깅.

손쉬운 확장성 및 고성능

• 높은 동시성을 쉽게 처리할 수 있도록 자동 확장.
• 운영 오버헤드가 전혀 없습니다. 빌드에만 집중하십시오.

설명서에서 자세히 알아보세요!

X에서 저희를 팔로우하세요: @LeapcellHQ