SQL 성능 튜닝 사고법: latency, throughput, cardinality, selectivity

SQL 튜닝은 “빠르게 만드는 기술”보다 먼저 “일을 줄이는 사고법”이다

SQL 성능 문제를 처음 만났을 때 가장 흔한 실수는 바로 인덱스를 추가하거나, 서버 스펙을 올리거나, 특정 쿼리 문법을 바꾸는 것이다. 이런 조치가 맞을 때도 있지만, 운영 환경에서는 순서가 중요하다. 느린 쿼리는 단순히 “쿼리 하나가 오래 걸린다”가 아니라 애플리케이션 지연, 커넥션 풀 고갈, DB CPU 포화, I/O 증가, 잠금 대기, 복제 지연으로 번질 수 있다.

그래서 SQL 성능 튜닝의 첫 질문은 “어떤 인덱스를 만들까?”가 아니다.

1. 사용자가 느끼는 지연 시간은 어디서 늘었는가?
2. 시스템 전체 처리량은 어떤 자원에서 막히는가?
3. 옵티마이저가 예상한 row 수와 실제 row 수는 얼마나 다른가?
4. 조건절은 충분히 선택적인가, 아니면 대부분의 row를 읽게 만드는가?

이 네 가지가 이번 장의 핵심이다. 용어로 쓰면 latency, throughput, cardinality, selectivity다. 이 개념을 잡아야 다음 장에서 실행계획, 인덱스, 조인, slow log를 읽을 때 숫자가 의미를 갖는다.

네 개의 축: latency, throughput, cardinality, selectivity

Latency: 한 번의 요청이 얼마나 오래 걸리는가

latency는 하나의 요청이나 쿼리가 시작해서 끝날 때까지 걸리는 시간이다. 평균값만 보면 위험하다. 사용자는 평균이 아니라 느린 꼬리 구간을 경험한다. 운영에서는 보통 p95, p99, timeout 비율을 함께 본다.

예를 들어 평균 쿼리 시간이 30ms여도 p99가 4초라면, 일부 요청은 커넥션을 오래 붙잡고 애플리케이션 스레드와 connection pool을 압박한다. 반대로 배치 쿼리는 개별 latency가 길어도 사용자 요청 경로와 분리되어 있으면 우선순위가 다를 수 있다.

SQL 튜닝에서 latency를 볼 때는 아래를 나눠서 봐야 한다.

PostgreSQL의 EXPLAIN (ANALYZE, BUFFERS)나 MySQL의 EXPLAIN ANALYZE는 DB 내부 실행 시간을 보는 데 유용하다. 다만 애플리케이션에서 느린데 DB 실행계획은 빠르다면, connection pool 대기, 네트워크, ORM의 N+1 query, 결과 처리 비용도 같이 봐야 한다.

Throughput: 시스템이 얼마나 많이 처리할 수 있는가

throughput은 단위 시간당 처리량이다. SQL에서는 QPS, TPS, rows/sec, batch 처리량, write throughput, IOPS 같은 형태로 나타난다. latency와 throughput은 연결되어 있지만 같은 말은 아니다.

한 쿼리의 latency를 500ms에서 100ms로 줄이면 보통 처리량도 좋아진다. 하지만 항상 그렇지는 않다. 예를 들어 더 빠른 계획을 만들기 위해 CPU를 훨씬 많이 쓰는 hash aggregate를 선택하면 개별 쿼리는 빨라져도 동시 실행 수가 늘 때 CPU 포화가 먼저 올 수 있다. 반대로 인덱스를 많이 추가하면 읽기 latency는 줄지만 쓰기 throughput은 떨어질 수 있다.

운영자는 다음처럼 질문해야 한다.

• 지금 문제는 사용자 요청 몇 개가 느린 것인가, 아니면 전체 시스템 처리량이 포화된 것인가?
• 병목은 CPU, memory, disk I/O, network, lock, connection 중 어디인가?
• 튜닝 후 읽기는 빨라졌지만 write amplification이나 replication lag가 늘지는 않았는가?
• batch size를 키우면 처리량은 좋아지지만 lock 보유 시간과 rollback 비용이 커지지는 않는가?

Throughput 관점이 없으면 “쿼리 하나는 빨라졌는데 서비스 전체는 더 불안정해지는” 튜닝을 할 수 있다.

Cardinality: 옵티마이저가 실제보다 row 수를 잘못 보았는가

cardinality는 어떤 단계에서 다루는 row 수다. 테이블 전체 row 수일 수도 있고, 조건절을 통과한 row 수일 수도 있고, 조인 후 중간 결과 row 수일 수도 있다. 실행계획에서 cardinality가 중요한 이유는 DB 옵티마이저가 row 수 예측을 바탕으로 scan 방식, join 순서, join 알고리즘, sort 방식, aggregation 방식을 고르기 때문이다.

PostgreSQL 문서는 query planner가 좋은 계획을 고르려면 query가 반환할 row 수를 추정해야 하며, 이를 위해 pg_class의 reltuples, relpages와 pg_statistic/pg_stats의 통계를 사용한다고 설명한다. 특히 WHERE 조건의 selectivity, 즉 조건에 맞을 것으로 예상되는 row 비율을 추정한다. 이 값은 ANALYZE와 VACUUM ANALYZE로 갱신되지만 항상 근사치다.

실행계획에서 가장 먼저 볼 것은 “예상 row 수와 실제 row 수의 차이”다.

-- PostgreSQL: 예측과 실제를 함께 본다.
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.created_at, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'PAID'
  AND o.created_at >= now() - interval '7 days';

PostgreSQL에서는 rows=... 추정치와 actual rows=...를 비교한다. MySQL 8.0.18 이후에는 EXPLAIN ANALYZE가 계획을 실행하면서 실제 row 수와 timing을 출력한다. MySQL 공식 블로그는 이 기능이 query를 plan하고 instrument한 뒤 실행하여 각 iterator의 rows와 time을 측정한다고 설명한다.

Cardinality 예측이 크게 틀어지는 흔한 이유는 다음과 같다.

중요한 점은 “실행계획이 이상하다”가 아니라 옵티마이저가 왜 그런 계획을 싸다고 믿었는지를 추적하는 것이다. 많은 SQL 성능 문제는 실행 엔진이 멍청해서가 아니라, 통계와 조건 표현 때문에 비용 모델에 잘못된 입력이 들어가서 생긴다.

Selectivity: 조건은 얼마나 잘 걸러내는가

selectivity는 조건을 만족하는 row의 비율이다. 단순히 말하면 다음과 같다.

selectivity = 조건을 만족하는 row 수 / 전체 row 수

전체 1억 row 중 100 row만 찾는 조건은 매우 선택적이다. 반대로 전체의 80%를 읽어야 하는 조건은 선택도가 낮다. 인덱스가 항상 빠르지 않은 이유도 여기에 있다. 인덱스로 80%의 row를 찾아 다시 테이블을 랜덤하게 읽는 것보다, 순차적으로 넓게 읽는 편이 더 나을 수 있다.

예를 들어 아래 두 조건은 같은 status 컬럼을 쓰더라도 의미가 다르다.

-- 대부분의 주문이 PAID라면 선택도가 낮다.
WHERE status = 'PAID'

-- 실패 주문이 매우 적다면 선택도가 높다.
WHERE status = 'FAILED'

Use The Index, Luke는 SQL 성능을 개발자가 알아야 할 주제로 다루면서, WHERE 조건, 다중 컬럼 인덱스, 함수가 들어간 조건, range search, LIKE, partial index, join, sorting, pagination을 주요 축으로 설명한다. 이 목록을 관통하는 기준도 결국 “어떤 조건이 어떤 순서로 row를 줄이는가”다.

선택도를 판단할 때는 단일 컬럼만 보지 말고 조건 조합을 봐야 한다.

WHERE tenant_id = 42
  AND status = 'FAILED'
  AND created_at >= '2026-06-01'

tenant_id만으로는 한 tenant의 데이터가 너무 많을 수 있다. status만으로는 전체 DB에서 별로 줄지 않을 수 있다. created_at range는 최근 데이터가 많으면 생각보다 넓을 수 있다. 그러나 세 조건을 함께 보면 매우 좁아질 수 있다. 복합 인덱스 설계와 조인 순서가 어려운 이유가 여기에 있다.

튜닝의 순서: 증상에서 원인으로 좁혀가기

SQL 성능 튜닝을 운영 절차로 만들면 아래 순서가 안전하다.

1. 문제 범위를 정한다. 특정 API, 특정 tenant, 특정 시간대, 특정 batch인지 확인한다.
2. latency와 throughput을 분리한다. 꼬리 지연 문제인지, 전체 포화 문제인지 구분한다.
3. 대표 쿼리를 고른다. slow query log, pg_stat_statements, Performance Schema, APM trace에서 빈도와 총 비용을 함께 본다.
4. 실행계획을 측정한다. 예측 plan만 보지 말고 가능한 환경에서 actual rows와 timing을 본다.
5. cardinality 오차를 찾는다. 예상 row와 실제 row가 크게 갈라지는 첫 지점을 찾는다.
6. selectivity를 개선한다. 조건 rewrite, 인덱스, 통계 갱신, 조인 순서, pre-aggregation 등을 검토한다.
7. 부작용을 확인한다. 쓰기 성능, lock, disk, replication lag, plan regression을 함께 본다.

MySQL의 slow query log는 long_query_time보다 오래 걸리고 min_examined_row_limit 조건을 만족하는 SQL을 기록한다. 공식 문서에 따르면 slow query log는 long-running query를 찾아 최적화 후보로 삼는 데 쓰며, 긴 로그는 mysqldumpslow로 요약할 수 있다. 단, 초기 lock 획득 시간은 실행 시간에 포함되지 않고, 쿼리는 실행과 lock release가 끝난 뒤 기록되므로 로그 순서가 실제 실행 순서와 다를 수 있다. 이런 세부 동작을 모르고 slow log만 보면 잠금 문제를 단순 실행 시간 문제로 오해할 수 있다.

PostgreSQL에서는 pg_stat_statements가 설치되어 있다면 평균 시간만 보지 말고 total time, calls, rows를 함께 보는 편이 좋다. “가끔 10초 걸리는 쿼리”와 “5ms지만 초당 수천 번 실행되는 쿼리”는 다른 방식으로 비용을 만든다.

예시: 같은 2초라도 원인은 다르다

아래 세 쿼리는 모두 2초 걸린다고 해도 튜닝 방향이 다르다.

이 구분을 하지 않고 “2초짜리 쿼리”라고만 보면 잘못된 처방이 나온다. scan 문제에 connection pool을 늘리면 DB를 더 빨리 포화시킬 수 있고, lock 문제에 인덱스를 추가해도 blocker가 그대로면 효과가 없다. sort spill 문제에 CPU를 증설해도 임시 파일 I/O가 병목이면 개선이 제한적이다.

안전한 실험: EXPLAIN ANALYZE는 실행한다

실행계획을 볼 때 특히 조심해야 할 점이 있다. PostgreSQL의 EXPLAIN ANALYZE는 실제로 statement를 실행한다. PostgreSQL 공식 문서는 ANALYZE 옵션을 쓰면 실제 runtime statistics를 얻기 위해 명령을 실행하며, 데이터 변경 statement의 부작용도 발생한다고 경고한다. 변경 쿼리를 분석해야 한다면 트랜잭션 안에서 실행하고 ROLLBACK해야 한다.

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders
SET status = 'EXPIRED'
WHERE status = 'PENDING'
  AND expires_at < now();
ROLLBACK;

MySQL의 EXPLAIN ANALYZE도 query를 실행해 runtime measurement를 얻는다. SELECT가 아닌 변경문이나 운영 데이터에 큰 부하를 줄 수 있는 SELECT에는 같은 주의가 필요하다. 운영 DB에서 바로 실행하기보다 replica, staging with production-like data, 제한된 tenant, LIMIT, 읽기 전용 트랜잭션 등 안전장치를 먼저 잡아야 한다.

운영자가 기억할 튜닝 질문

SQL 튜닝을 할 때 아래 질문을 체크리스트처럼 사용하면 좋다.

• 이 문제는 평균 latency 문제인가, p99 latency 문제인가?
• 사용자는 느린데 DB 내부 실행 시간은 짧지 않은가?
• 처리량 병목은 CPU, I/O, lock, connection, network 중 어디인가?
• 실행계획의 예상 row와 실제 row가 처음으로 크게 어긋나는 지점은 어디인가?
• 조건절의 selectivity는 실제 데이터 분포에서 충분한가?
• 인덱스를 추가하면 읽기 외에 쓰기, 저장공간, replication, cache 효율에 어떤 비용이 생기는가?
• 튜닝 전후를 같은 기준으로 측정했는가?
• plan이 좋아진 것처럼 보여도 실제 p95/p99와 total resource 사용량도 좋아졌는가?

이번 장의 결론은 단순하다. SQL 튜닝은 감으로 문법을 바꾸는 일이 아니라, DB가 실제로 얼마나 읽고, 얼마나 기다리고, 얼마나 잘못 예측했는지 확인하는 작업이다. 다음 장부터 실행계획의 scan, seek, join, sort, aggregate를 읽을 때도 이 네 단어를 계속 들고 가면 된다: latency, throughput, cardinality, selectivity.

References

• PostgreSQL Documentation, “14.2. Statistics Used by the Planner” — https://www.postgresql.org/docs/current/planner-stats.html
• PostgreSQL Documentation, “EXPLAIN” — https://www.postgresql.org/docs/current/sql-explain.html
• MySQL Reference Manual, “The Slow Query Log” — https://dev.mysql.com/doc/en/slow-query-log.html
• MySQL Blog Archive, Norvald H. Ryeng, “MySQL EXPLAIN ANALYZE” — https://dev.mysql.com/blog-archive/mysql-explain-analyze
• Markus Winand, “Use The Index, Luke — What every developer should know about SQL performance” — https://use-the-index-luke.com/sql/table-of-contents