SQL 쿼리 최적화 실전: 인덱스와 실행 계획으로 느린 쿼리 고치기

작성 0xmote · 2026년 7월 14일 · 22분 읽기 · 수정 2026년 9월 27일

이 글의 핵심

느린 쿼리는 대개 인덱스를 타지 못해 테이블 전체를 스캔하거나 ORM이 같은 쿼리를 수백 번 반복하면서 생깁니다. 인덱스가 있어도 WHERE 절의 함수 때문에 쓰이지 않는 경우, MySQL과 PostgreSQL의 차이, 느린 쿼리를 찾고 고치고 확인하는 작업 순서를 정리합니다.

이 글에서 다루는 것

느린 쿼리를 고치는 작업은 대부분 같은 순서를 따릅니다. 느린 쿼리를 찾고, 실행 계획을 보고, 인덱스나 쿼리를 고치고, 다시 측정합니다. 이 글은 그 순서에 필요한 배경 지식(B-Tree 인덱스가 어떻게 동작하는지)과, 실무에서 자주 만나는 함정(인덱스가 있는데도 안 쓰이는 조건, N+1, OFFSET 페이지네이션)을 정리합니다.

예제의 실행 시간은 적지 않았습니다. 같은 쿼리도 데이터 크기, 분포, 캐시 상태, 하드웨어에 따라 몇 배씩 달라지기 때문에, 숫자보다는 실행 계획이 어떻게 바뀌는지를 보는 것이 중요합니다. 개선 효과는 반드시 자신의 데이터로 측정하세요.

인덱스가 빠른 이유

인덱스 없이 찾기

인덱스가 없으면 데이터베이스는 조건에 맞는 행을 찾기 위해 테이블의 모든 행을 읽어야 합니다(Full Table Scan). 비용은 행 수에 비례해서 늘어납니다.

SELECT * FROM users WHERE email = '[email protected]';
-- email에 인덱스가 없으면 모든 행의 email을 하나씩 비교

B+Tree 인덱스

MySQL(InnoDB)과 PostgreSQL의 기본 인덱스는 B+Tree입니다. 키가 정렬된 상태로 트리에 저장되어 있어서, 루트에서 시작해 몇 단계만 내려가면 원하는 키가 있는 리프 페이지에 도달합니다.

                 [Root: m | t]
               /       |       \
     [a | d | g]   [m | p | s]   [t | w | z]
         ...           |             ...
                 [mike → PK 5, peter → PK 6, sam → PK 7]  ← 리프 페이지
                       ↔ 이웃 리프와 연결 (범위 검색에 유리)

B+Tree의 특징은 세 가지입니다.

한 페이지(InnoDB 기본 16KB)에 키가 수백 개씩 들어가므로 트리가 매우 낮습니다. 수백만~~수억 행 테이블도 대개 높이 3~~4 정도라, 찾는 데 읽는 페이지 수가 몇 개에 불과합니다.
리프 페이지끼리 연결되어 있어 BETWEEN, >=, ORDER BY 같은 범위 작업을 순서대로 읽을 수 있습니다.
키가 정렬되어 있으므로 왼쪽(앞쪽)부터 일치하는 조건만 탐색 범위를 좁힐 수 있습니다. 뒤에서 다룰 “인덱스를 못 타는 조건”은 대부분 이 성질에서 나옵니다.

클러스터드 인덱스와 보조 인덱스

InnoDB에서 테이블 데이터 자체는 기본 키(PK) 순서로 정렬된 B+Tree(클러스터드 인덱스)에 저장됩니다. email 같은 보조 인덱스의 리프에는 행 전체가 아니라 PK 값이 들어 있어서, 보조 인덱스로 찾은 뒤 PK로 클러스터드 인덱스를 한 번 더 찾아야 행을 읽을 수 있습니다.

보조 인덱스 (email)            클러스터드 인덱스 (PK)
alice@... → PK 1   ──────→    PK 1 → (id, name, email, created_at ...)

그래서 쿼리가 필요로 하는 컬럼이 모두 인덱스 안에 있으면(커버링 인덱스) 두 번째 탐색을 건너뛸 수 있습니다. PostgreSQL은 구조가 다르지만(테이블은 힙, 인덱스는 행 위치를 가리킴) Index Only Scan이라는 같은 최적화가 있습니다.

인덱스 만들기

-- 단일 컬럼 인덱스
CREATE INDEX idx_email ON users(email);
-- 복합 인덱스
CREATE INDEX idx_user_created ON orders(user_id, created_at);
-- 유니크 인덱스
CREATE UNIQUE INDEX idx_email_unique ON users(email);

-- 삭제와 조회 (MySQL)
DROP INDEX idx_email ON users;
SHOW INDEX FROM users;
-- 삭제와 조회 (PostgreSQL)
DROP INDEX idx_email;
-- psql에서: \d users

인덱스는 공짜가 아닙니다. INSERT·UPDATE·DELETE마다 인덱스도 함께 갱신되고 저장 공간과 버퍼 풀 메모리를 차지합니다. 쓰기가 많은 테이블에 “혹시 몰라서” 만든 인덱스가 쌓이면 쓰기 성능이 눈에 띄게 떨어집니다.

EXPLAIN으로 실행 계획 읽기

EXPLAIN은 옵티마이저가 쿼리를 어떤 순서와 방법으로 실행할지 보여 줍니다.

EXPLAIN SELECT * FROM orders WHERE user_id = 123;

MySQL에서 볼 컬럼

+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 498213 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+

type: 접근 방식입니다. 대략 const(PK·유니크로 한 행) → eq_ref → ref(인덱스로 여러 행) → range(인덱스 범위) → index(인덱스 전체 스캔) → ALL(테이블 전체 스캔) 순으로 읽는 양이 많아집니다. 다만 index가 항상 나쁜 것은 아닙니다. 커버링 인덱스를 끝까지 읽는 경우(Extra: Using index)는 테이블 전체를 읽는 것보다 훨씬 가볍습니다. 작은 테이블의 ALL도 문제가 아닙니다.
key: 실제로 선택된 인덱스입니다. possible_keys에는 있는데 key가 NULL이면, 옵티마이저가 인덱스를 쓰는 것보다 전체 스캔이 싸다고 판단한 것입니다(조건에 맞는 행이 테이블의 상당 부분일 때 흔합니다).
rows: 읽을 것으로 추정한 행 수입니다. 통계 기반 추정치라 실제와 다를 수 있습니다.
Extra: Using filesort(인덱스 순서로 정렬하지 못해 별도 정렬), Using temporary(임시 테이블 사용), Using index(커버링 인덱스) 등이 자주 나옵니다.

인덱스를 추가한 뒤에는 이렇게 바뀌는 것을 확인합니다.

CREATE INDEX idx_user_id ON orders(user_id);
EXPLAIN SELECT * FROM orders WHERE user_id = 123;
-- type: ref, key: idx_user_id, rows: (해당 사용자의 주문 수 정도)

추정이 아니라 실제를 보려면

-- PostgreSQL: 실제로 실행하고 단계별 실제 행 수와 시간을 출력
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 123;

-- MySQL 8.0.18+: 트리 형식으로 실제 실행 결과 출력
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;

ANALYZE는 쿼리를 실제로 실행합니다. UPDATE·DELETE에 붙이면 데이터가 바뀌므로 트랜잭션 안에서 실행하고 롤백하세요. 제가 실행 계획을 볼 때 가장 먼저 확인하는 것은 추정 행 수(rows)와 실제 행 수의 차이입니다. 수십 배 이상 어긋나 있으면 인덱스를 추가하기 전에 ANALYZE TABLE(MySQL)이나 ANALYZE(PostgreSQL)로 통계부터 갱신해 보는 것이 순서입니다.

복합 인덱스 설계

컬럼 순서

SELECT * FROM orders
WHERE user_id = 123
  AND status = 'completed'
  AND created_at >= '2026-01-01';

-- 범위 조건 컬럼이 맨 앞이면 뒤의 두 컬럼으로 범위를 좁히기 어렵습니다
CREATE INDEX idx_bad ON orders(created_at, status, user_id);
-- 등치 조건을 앞에, 범위 조건을 뒤에
CREATE INDEX idx_good ON orders(user_id, status, created_at);

원칙은 다음과 같습니다.

등치(=) 조건 컬럼을 앞에 둡니다. 등치 조건끼리의 순서는 탐색 효율보다 “다른 쿼리에서도 이 인덱스를 재사용할 수 있는가”로 정하는 경우가 많습니다. WHERE user_id = ?만 쓰는 쿼리도 있다면 user_id를 맨 앞에 두는 식입니다.
범위 조건 컬럼은 뒤에 둡니다. 범위 조건 이후의 컬럼은 탐색 범위를 좁히는 데 쓰이지 못하고, 기껏해야 인덱스 안에서 필터링(MySQL의 Index Condition Pushdown)에 쓰입니다.
ORDER BY까지 고려합니다. WHERE user_id = ? ORDER BY created_at DESC LIMIT 20이라면 (user_id, created_at) 인덱스 하나로 필터와 정렬을 모두 해결해 Using filesort를 없앨 수 있습니다.

앞쪽 컬럼을 건너뛰면

CREATE INDEX idx_abc ON t(a, b, c);
SELECT * FROM t WHERE a = 1 AND b = 2;  -- 앞에서부터 일치하므로 사용
SELECT * FROM t WHERE b = 2;            -- 선행 컬럼 a가 없어 보통 범위 탐색에 못 씀

전화번호부가 성으로 먼저 정렬되어 있으면 이름만으로는 찾기 어려운 것과 같습니다. MySQL 8.0.13 이상의 Skip Scan처럼 선행 컬럼의 값 종류가 적을 때 예외적으로 쓰는 최적화도 있지만, 설계할 때 기대할 동작은 아닙니다.

인덱스가 있는데도 안 쓰이는 조건

아래 경우는 인덱스를 만들어 놓고도 전체 스캔이 나오는 흔한 원인입니다. 공통점은 인덱스에 저장된 값 그대로 비교하지 않는다는 것입니다.

컬럼에 함수나 연산을 적용

-- 인덱스는 created_at 원래 값으로 정렬되어 있어 YEAR() 결과로는 찾을 수 없습니다
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
-- 범위 조건으로 바꾸면 인덱스를 씁니다
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

-- 컬럼 쪽 연산도 마찬가지입니다
SELECT * FROM users WHERE id + 1 = 2;   -- 안 좋음
SELECT * FROM users WHERE id = 2 - 1;   -- 상수 쪽으로 옮기기

함수를 꼭 써야 한다면 함수 결과로 인덱스를 만듭니다. 문법이 DB마다 다릅니다.

-- PostgreSQL: 표현식 인덱스
CREATE INDEX idx_email_lower ON users (LOWER(email));
-- MySQL 8.0.13+: 함수형 키 부분 (괄호가 두 겹)
CREATE INDEX idx_email_lower ON users ((LOWER(email)));

-- 쿼리도 인덱스와 같은 식을 써야 합니다
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

앞쪽 와일드카드 LIKE

SELECT * FROM users WHERE email LIKE 'alice%';        -- 접두사: 인덱스 범위 탐색 가능
SELECT * FROM users WHERE email LIKE '%@gmail.com';   -- 앞이 비어 있어 정렬 순서를 쓸 수 없음

PostgreSQL에서는 LIKE 'alice%'가 인덱스를 쓰려면 컬럼 콜레이션이 C이거나 text_pattern_ops 연산자 클래스로 인덱스를 만들어야 할 수 있습니다. 중간·끝 매칭이 자주 필요하면 다음을 검토합니다.

PostgreSQL: pg_trgm 확장의 GIN 트라이그램 인덱스는 LIKE '%alice%'와 ILIKE를 그대로 가속합니다.
MySQL: FULLTEXT 인덱스는 단어(토큰) 단위 검색이라 부분 문자열 LIKE와 의미가 다릅니다. 도메인 검색처럼 끝부분 매칭이 핵심이라면 도메인만 따로 저장한 컬럼에 인덱스를 두는 편이 단순합니다.
검색이 서비스의 핵심 기능이면 Elasticsearch·OpenSearch 같은 검색 엔진으로 분리하는 것도 선택지입니다.

OR 조건

SELECT * FROM users WHERE id = 1 OR name = 'Alice';

OR의 한쪽 컬럼(name)에 인덱스가 없으면 그 조건 때문에 결국 전체를 읽어야 하므로 전체 스캔이 됩니다. 양쪽 모두 인덱스가 있으면 MySQL은 index merge, PostgreSQL은 BitmapOr로 두 인덱스를 함께 쓸 수 있습니다. 옵티마이저가 그렇게 하지 못할 때 UNION으로 나누는 방법이 있습니다.

SELECT * FROM users WHERE id = 1
UNION
SELECT * FROM users WHERE name = 'Alice';

여기서 UNION ALL을 쓰면 중복 제거 비용은 없지만, 두 조건을 모두 만족하는 행이 두 번 나옵니다. 결과가 겹칠 수 있으면 UNION, 겹칠 수 없음이 확실할 때만 UNION ALL을 씁니다.

암묵적 타입 변환

-- phone이 VARCHAR인데 숫자로 비교하면 MySQL은 컬럼 값을 숫자로 변환해 비교합니다
SELECT * FROM users WHERE phone = 01012345678;   -- 인덱스 사용 불가
SELECT * FROM users WHERE phone = '01012345678'; -- 타입을 맞추면 사용

JOIN 키의 타입이나 문자셋·콜레이션이 서로 다를 때도 같은 일이 생깁니다. 두 테이블의 user_id가 하나는 INT, 하나는 VARCHAR이거나, 문자셋이 utf8mb3와 utf8mb4로 다르면 인덱스가 있어도 조인에 쓰이지 않을 수 있습니다.

N+1 문제

N+1 문제는 목록 1번 조회 후, 각 행마다 관련 데이터를 1번씩 더 조회하는 패턴입니다. 쿼리 하나하나는 빠르지만 왕복 횟수가 행 수만큼 늘어납니다.

// 사용자 100명 조회 1번 + 사용자마다 게시글 수 조회 100번 = 101번 왕복
const users = await db.query('SELECT id, name FROM users LIMIT 100');
for (const user of users) {
  const rows = await db.query(
    'SELECT COUNT(*) AS count FROM posts WHERE user_id = ?',
    [user.id]
  );
  user.postCount = rows[0].count;
}

해결 1: JOIN과 GROUP BY

SELECT u.id, u.name, COUNT(p.id) AS post_count
FROM (SELECT id, name FROM users ORDER BY id LIMIT 100) u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.id, u.name;

LIMIT을 바깥에 두면 전체 사용자와 게시글을 먼저 조인·집계한 뒤 100명을 자르게 되므로, 사용자 100명을 먼저 고르고 조인하는 형태로 씁니다. posts.user_id에 인덱스가 있어야 합니다.

해결 2: IN 절로 한 번에

const users = await db.query('SELECT id, name FROM users ORDER BY id LIMIT 100');
const ids = users.map(u => u.id);
const counts = await db.query(
  'SELECT user_id, COUNT(*) AS count FROM posts WHERE user_id IN (?) GROUP BY user_id',
  [ids]
);
const countMap = Object.fromEntries(counts.map(r => [r.user_id, r.count]));
users.forEach(u => { u.postCount = countMap[u.id] ?? 0; });
// 101번 왕복 → 2번 왕복

IN 목록이 수천 개 이상으로 커지면 파싱 비용과 DB별 제한이 생기므로 적당한 크기로 나눠 보냅니다.

ORM에서

// Sequelize: include로 즉시 로딩
const users = await User.findAll({ include: [{ model: Post }] });

# Django
User.objects.select_related('profile')   # 1:1, N:1 → JOIN 한 번
User.objects.prefetch_related('posts')   # 1:N, N:N → IN 절 쿼리를 한 번 더

1:N 관계를 JOIN으로 즉시 로딩하면 사용자 한 명당 게시글 수만큼 행이 중복되어 전송됩니다. 자식이 많은 관계라면 prefetch_related처럼 IN 절로 따로 가져오는 방식이 대개 더 가볍습니다. 저는 N+1을 찾을 때 ORM의 쿼리 로그를 켜고 한 요청에서 나가는 쿼리 개수부터 셉니다. 코드를 읽는 것보다 로그에서 같은 모양의 쿼리가 반복되는 것을 보는 쪽이 훨씬 빨리 찾아집니다.

조인과 서브쿼리에 대한 오해

FROM 절의 테이블 순서

“작은 테이블을 FROM에 먼저 쓰라”는 조언을 자주 보지만, MySQL과 PostgreSQL의 옵티마이저는 INNER JOIN의 순서를 통계에 따라 스스로 정합니다. 쿼리에 쓴 순서를 바꿔도 실행 계획은 대개 같습니다. 실제로 중요한 것은 다음 두 가지입니다.

조인 키에 인덱스가 있는가. 보통 안쪽(반복해서 찾히는) 테이블의 조인 컬럼, 예를 들어 orders.user_id에 인덱스가 필요합니다. PK는 이미 인덱스이므로 따로 만들 필요가 없습니다.
필터 조건이 인덱스로 먼저 걸러지는가. WHERE u.country = 'KR'이 많은 행을 걸러 낸다면 users.country의 인덱스가 조인할 행 수를 줄여 줍니다.

(PostgreSQL은 조인 테이블 수가 join_collapse_limit을 넘으면 쓴 순서를 따르기 시작하므로, 아주 많은 테이블을 조인할 때는 순서가 의미를 가질 수 있습니다.)

”서브쿼리로 먼저 필터링하라”

-- 두 쿼리는 현대 옵티마이저에서 보통 같은 계획이 됩니다
SELECT * FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at >= '2026-01-01';

SELECT * FROM users u
JOIN (SELECT * FROM orders WHERE created_at >= '2026-01-01') o ON u.id = o.user_id;

옵티마이저는 WHERE 조건을 조인 전에 적용(predicate pushdown)하므로 파생 테이블로 감쌀 필요가 없습니다. 오래된 MySQL(5.6 이하)에서는 파생 테이블을 임시 테이블로 먼저 만들어 오히려 느려지기도 했습니다. 필요한 것은 orders.created_at(또는 (user_id, created_at)) 인덱스입니다.

상관 서브쿼리

SELECT u.name,
       (SELECT COUNT(*) FROM posts p WHERE p.user_id = u.id) AS post_count
FROM users u;

개념적으로는 바깥 행마다 서브쿼리가 실행됩니다. posts.user_id에 인덱스가 있으면 한 번 실행이 짧아서 사용자 수가 적을 때는 문제가 되지 않고, 사용자 전체를 대상으로 할 때는 JOIN + GROUP BY로 한 번에 집계하는 편이 대개 유리합니다. 어느 쪽이 나은지는 실행 계획으로 확인합니다.

EXISTS와 IN

SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
SELECT * FROM users u WHERE u.id IN (SELECT user_id FROM orders);

예전 MySQL(5.5 이하)에서는 IN (서브쿼리)가 비효율적으로 실행되어 “EXISTS가 빠르다”는 말이 생겼습니다. MySQL 5.6 이후와 PostgreSQL은 둘 다 세미 조인으로 변환하므로 대개 성능 차이가 없습니다. 차이가 나는 쪽은 NOT IN입니다. 서브쿼리 결과에 NULL이 하나라도 있으면 NOT IN은 아무 행도 반환하지 않으므로, 부정 조건은 NOT EXISTS로 쓰는 것이 안전합니다.

사례: 페이지네이션

OFFSET의 문제

SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;

OFFSET은 건너뛸 행을 실제로 읽고 버립니다. 인덱스를 타더라도 뒤쪽 페이지로 갈수록 읽는 양이 OFFSET만큼 늘어납니다.

키셋(커서) 페이지네이션

마지막으로 본 행의 정렬 키를 기억했다가 그 다음부터 읽습니다. created_at은 중복될 수 있으므로 고유한 id를 함께 정렬 키로 써야 행이 빠지거나 중복되지 않습니다.

CREATE INDEX idx_posts_created_id ON posts (created_at DESC, id DESC);

-- 첫 페이지
SELECT * FROM posts ORDER BY created_at DESC, id DESC LIMIT 20;

-- 다음 페이지: 직전 페이지 마지막 행의 (created_at, id)를 전달
SELECT * FROM posts
WHERE (created_at, id) < ('2026-03-31 10:00:00', 98765)
ORDER BY created_at DESC, id DESC
LIMIT 20;

행 값 비교 (a, b) < (x, y)는 PostgreSQL에서 인덱스를 잘 탑니다. MySQL에서 계획이 좋지 않으면 created_at < ? OR (created_at = ? AND id < ?)로 풀어서 써 봅니다. 대신 “37페이지로 바로 이동”은 할 수 없으므로, 무한 스크롤이나 “다음” 버튼 UI에 맞는 방식입니다.

사례: COUNT와 대시보드 집계

정확한 COUNT(*)

InnoDB는 MVCC 때문에 테이블 전체 행 수를 따로 저장하지 않아서, SELECT COUNT(*) FROM posts는 가장 작은 인덱스 하나를 끝까지 읽습니다. PostgreSQL도 마찬가지로 행을 실제로 셉니다. 큰 테이블에서 이 쿼리를 매 요청마다 실행하면 부담이 됩니다.

선택지는 세 가지입니다.

-- 1) 근사치: 통계 정보 사용 (정확하지 않음)
-- MySQL: InnoDB의 TABLE_ROWS는 표본 추정치라 실제와 꽤 차이 날 수 있습니다
SELECT TABLE_ROWS FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'posts';
-- PostgreSQL: 마지막 VACUUM/ANALYZE 시점 기준 추정치
SELECT reltuples::bigint FROM pg_class WHERE relname = 'posts';

// 2) 캐시: 약간 오래된 값이어도 되는 화면이라면
const cached = await redis.get('posts:count');
if (cached !== null) return Number(cached);
const [{ count }] = await db.query('SELECT COUNT(*) AS count FROM posts');
await redis.set('posts:count', count, { EX: 3600 });
return count;

집계 테이블: 정확한 값이 자주 필요하면 게시글 추가·삭제 시 카운터 테이블을 함께 갱신합니다. 쓰기 경합이 생길 수 있으니 트래픽이 많으면 카운터를 여러 행으로 나누거나 비동기로 반영합니다.

대시보드 쿼리를 하나로 합치면 빨라질까

SELECT
  (SELECT COUNT(*) FROM users) AS total_users,
  (SELECT COUNT(*) FROM orders) AS total_orders,
  (SELECT SUM(amount) FROM orders WHERE status = 'completed') AS revenue,
  (SELECT COUNT(*) FROM orders WHERE created_at >= CURDATE()) AS today_orders;

이 쿼리를 users LEFT JOIN orders 하나로 합치는 “최적화”를 종종 보는데, 대개 역효과입니다. 네 개의 스칼라 서브쿼리는 각각 독립적으로 가장 적합한 인덱스를 쓸 수 있습니다(today_orders는 created_at 인덱스, revenue는 (status, amount) 인덱스). 반면 전체 조인은 두 테이블을 모두 읽어 조인한 뒤 집계하므로 읽는 양이 더 많아집니다. 이런 대시보드는 쿼리 모양을 바꾸기보다 다음이 효과적입니다.

각 서브쿼리가 인덱스를 타는지 확인합니다.
초 단위로 정확할 필요가 없다면 결과를 몇 분간 캐시합니다.
기간별 집계는 배치로 요약 테이블에 미리 계산해 둡니다.

쿼리를 쓸 때의 원칙

필요한 컬럼만 조회

SELECT * FROM users;               -- 모든 컬럼
SELECT id, name, email FROM users; -- 필요한 컬럼만

SELECT *가 문제인 이유는 조건에 따라 다릅니다. 큰 TEXT·BLOB·JSON 컬럼이 있으면 전송량과 메모리가 크게 늘고, 커버링 인덱스로 끝날 수 있는 쿼리가 테이블까지 읽게 됩니다. 반대로 작은 테이블에서 PK로 한 행을 읽을 때는 차이가 거의 없습니다. 성능보다 더 흔한 문제는 유지보수입니다. 테이블에 컬럼이 추가되면 애플리케이션이 모르는 데이터가 따라오고, 컬럼 순서에 의존한 코드가 깨질 수 있습니다.

DISTINCT와 GROUP BY

SELECT DISTINCT user_id FROM orders;
SELECT user_id FROM orders GROUP BY user_id;

집계 없이 중복만 제거하는 경우 MySQL과 PostgreSQL은 두 쿼리를 사실상 같은 방식으로 처리합니다. 둘 다 user_id 인덱스가 있으면 그것을 이용할 수 있습니다. 성능을 이유로 바꿀 필요는 없고, 의도가 드러나는 쪽을 쓰면 됩니다. 조심할 것은 JOIN 때문에 생긴 중복을 DISTINCT로 가리는 습관입니다. 이 경우 대개 EXISTS로 바꾸는 것이 의미도 정확하고 읽는 행도 적습니다.

UNION과 UNION ALL

UNION은 중복을 제거하기 위해 정렬이나 해시 작업을 합니다. 두 결과가 겹칠 수 없다는 것이 확실하거나(예: 서로 다른 테이블의 PK 범위가 분리됨) 중복이 있어도 괜찮을 때만 UNION ALL로 바꿉니다. 결과가 달라질 수 있는 변경이므로 성능만 보고 바꾸면 안 됩니다.

인덱스 관리

사용되지 않는 인덱스

-- MySQL: sys 스키마 (서버 재시작 이후 통계 기준)
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'mydb';

-- PostgreSQL: 통계 초기화 이후 한 번도 스캔되지 않은 인덱스
SELECT schemaname, relname AS table_name, indexrelname AS index_name, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

두 결과 모두 “통계를 모으기 시작한 이후”만 반영합니다. 월말 정산처럼 가끔 도는 작업이 쓰는 인덱스가 목록에 나올 수 있으니, 충분히 긴 기간을 지켜본 뒤 삭제하세요. 유니크 인덱스는 조회에 안 쓰여도 제약 조건 역할을 하므로 지우면 안 됩니다.

인덱스 크기

-- MySQL: stat_name = 'size'는 페이지 수
SELECT table_name, index_name,
       ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE database_name = 'mydb' AND stat_name = 'size'
ORDER BY size_mb DESC;

-- PostgreSQL
SELECT relname AS table_name, indexrelname AS index_name,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

작업 순서

1단계: 느린 쿼리 찾기

-- MySQL: slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;   -- 1초 이상 걸린 쿼리 기록
SHOW VARIABLES LIKE 'slow_query_log_file';

PostgreSQL은 log_min_duration_statement로 느린 쿼리를 로그에 남기고, pg_stat_statements 확장으로 쿼리 모양별 누적 시간과 호출 횟수를 볼 수 있습니다. 한 번에 오래 걸리는 쿼리보다 자주 호출되어 누적 시간이 큰 쿼리가 실제 부하의 원인인 경우가 많으므로, 누적 시간 기준으로 정렬해 보는 것이 좋습니다. MySQL에서는 sys.statement_analysis 뷰가 비슷한 정보를 줍니다.

2단계: 실행 계획 분석

EXPLAIN SELECT * FROM orders
WHERE user_id = 123 AND created_at >= '2026-01-01';
-- type, key, rows, Extra 확인

3단계: 인덱스나 쿼리 수정

CREATE INDEX idx_user_created ON orders(user_id, created_at);

4단계: 다시 측정

같은 쿼리로 EXPLAIN ANALYZE를 다시 실행해 계획과 실제 시간이 바뀌었는지 확인합니다. 첫 실행은 데이터가 캐시에 없어 느리고 두 번째부터 빨라지므로, 여러 번 실행해 비교하세요.

5단계: 프로덕션 적용

-- MySQL: 온라인 DDL (InnoDB, 5.6+). 가능하지 않으면 에러가 나므로 락을 걸고 진행되지 않습니다
CREATE INDEX idx_user_created ON orders(user_id, created_at) ALGORITHM=INPLACE, LOCK=NONE;

-- PostgreSQL: 쓰기를 막지 않고 생성 (트랜잭션 블록 안에서는 실행 불가)
CREATE INDEX CONCURRENTLY idx_user_created ON orders(user_id, created_at);

CREATE INDEX CONCURRENTLY는 실패하면 INVALID 상태의 인덱스를 남깁니다. \d orders로 확인해 지우고 다시 만들어야 합니다. MySQL의 온라인 DDL도 작업 마지막에 짧은 메타데이터 락이 필요해서, 그 테이블에 오래 열린 트랜잭션이 있으면 DDL이 대기하고 그 뒤의 쿼리들까지 줄줄이 막힐 수 있습니다. 대형 테이블 작업 전에는 오래 실행 중인 트랜잭션이 없는지 먼저 확인하세요.

MySQL과 PostgreSQL 설정에서 알아 둘 것

MySQL

쿼리 캐시는 MySQL 8.0에서 제거되었습니다. 5.7 이하에서도 쓰기가 잦으면 캐시 무효화 경합 때문에 오히려 느려지는 경우가 많아 꺼 두는 것이 일반적이었습니다.
innodb_buffer_pool_size는 데이터와 인덱스를 메모리에 올려 두는 공간으로, 전용 DB 서버라면 물리 메모리의 상당 부분을 할당하는 것이 보통입니다. 5.7.5부터 재시작 없이 바꿀 수 있습니다.
max_connections는 커넥션 풀이 아니라 동시 접속 상한입니다. 올리기만 하면 스레드별 메모리가 늘어나므로, 애플리케이션 쪽 커넥션 풀 크기와 함께 조정합니다.

PostgreSQL

ANALYZE users;         -- 통계 갱신
VACUUM ANALYZE users;  -- 죽은 튜플 정리 + 통계 갱신

# postgresql.conf (값은 서버 메모리와 워크로드에 맞춰 정합니다)
shared_buffers        # 흔히 물리 메모리의 1/4 안팎에서 시작
effective_cache_size  # OS 캐시까지 포함한 추정치, 옵티마이저 비용 계산에만 쓰임
work_mem              # 정렬·해시 작업 하나당 메모리. 동시 쿼리 × 작업 수만큼 곱해질 수 있어 크게 잡으면 위험

autovacuum이 켜져 있으면 대부분 자동으로 처리되지만, 대량 적재나 삭제 직후에는 수동으로 ANALYZE를 실행해 두면 잘못된 실행 계획을 피할 수 있습니다.

FAQ

Q1. 인덱스는 많을수록 좋은가요? 아닙니다. 인덱스마다 쓰기 비용과 저장 공간이 늘어납니다. 자주 실행되는 쿼리가 실제로 쓰는 인덱스만 두고, 앞쪽 컬럼이 같은 인덱스가 여럿이면 하나로 합칠 수 있는지 봅니다. 예를 들어 (user_id)와 (user_id, created_at)이 둘 다 있으면 앞의 것은 대개 필요 없습니다.

Q2. 값 종류가 적은 컬럼에는 인덱스가 소용없나요? 대체로 효과가 적습니다. 예를 들어 절반씩 나뉘는 값이라면 인덱스로 찾은 뒤 테이블을 다시 읽는 것보다 전체 스캔이 싸다고 옵티마이저가 판단합니다. 다만 분포가 치우쳐 있으면 이야기가 다릅니다. status가 대부분 done이고 pending이 소수라면 WHERE status = 'pending'에는 인덱스가 잘 듣고, PostgreSQL이라면 WHERE status = 'pending' 부분 인덱스로 크기도 줄일 수 있습니다. 또 값 종류가 적은 컬럼도 복합 인덱스의 앞부분으로는 유용할 수 있습니다.

Q3. 복합 인덱스와 단일 인덱스 중 무엇을 만들까요? 쿼리 패턴을 봅니다. user_id와 status가 항상 함께 쓰이면 (user_id, status) 복합 인덱스 하나가 낫고, 이 인덱스는 WHERE user_id = ? 단독 쿼리에도 쓰입니다. status 단독 조회도 많다면 그때 status 인덱스를 따로 추가합니다.

Q4. 인덱스를 추가했는데도 느려요. EXPLAIN으로 인덱스가 실제로 선택되었는지부터 봅니다. 선택되지 않았다면 4장의 원인(함수, 앞쪽 와일드카드, OR, 타입 변환, 선행 컬럼 누락)을 확인하고, 조건에 맞는 행이 너무 많아 옵티마이저가 전체 스캔을 고른 것은 아닌지, 통계가 오래되지 않았는지 확인합니다. 인덱스는 쓰는데도 느리다면 인덱스로 찾은 행이 많아 테이블 접근이 많거나(커버링 인덱스 검토), 정렬(Using filesort)이나 락 대기가 원인일 수 있습니다.