SQL 인덱스와 실행계획 - B-Tree, 인덱스 못 타는 조건, EXPLAIN 읽는 법 (SQL 중급 9단원)

이 단원에서 배우는 것

7·8단원에서 조인과 서브쿼리로 원하는 결과를 만드는 법을 배웠다. 여기까지는 "맞는 답"이 목표였다. 운영 환경에 올리는 순간 기준이 하나 더 붙는다. 같은 결과를 내는 두 쿼리가 응답 시간에서 1,000배 차이가 나기 때문이다. 이번 단원은 그 차이를 만드는 인덱스의 동작 원리와, 내가 쓴 쿼리가 인덱스를 타는지 확인하는 EXPLAIN 읽는 법을 다룬다. 테이블은 1단원에서 만든 dept · emp 와 7단원에서 추가한 proj 를 그대로 쓴다.

B-Tree 인덱스가 어떤 조건에서 쓸모 있고 어떤 조건에서 무용지물인지 설명한다
복합 인덱스의 컬럼 순서를 요구사항에 맞게 정한다
EXPLAIN 출력에서 풀 스캔 · 정렬 · 임시 테이블을 알아본다

개념

인덱스가 없으면 DB 는 조건에 맞는 행을 찾기 위해 테이블 전체를 처음부터 끝까지 읽는다. 100만 행짜리 테이블에서 한 건을 찾자고 100만 건을 읽는 셈이다. 인덱스는 책 뒤의 색인과 같다. 찾을 값을 정렬해서 따로 보관하고, 그 값이 있는 행의 위치를 함께 적어 둔다.

관계형 DB 의 기본 인덱스는 B-Tree(정확히는 B+Tree)다. 값이 정렬된 상태로 트리에 담겨 있고, 루트에서 시작해 몇 번의 비교로 리프에 도달한다. 100만 행이라도 보통 3~4번만 읽으면 원하는 위치가 나온다. 여기서 인덱스가 잘하는 일과 못하는 일이 갈린다.

잘하는 일 — 등호 검색, 범위 검색(BETWEEN, >=), 접두 일치 LIKE '김%', 정렬(이미 정렬돼 있으므로), 최솟값·최댓값
못하는 일 — 값을 가공한 뒤의 비교, 중간 일치 LIKE '%김%', 값의 종류가 몇 개뿐인 컬럼(성별, 처리여부)

마지막 항목이 카디널리티 이야기다. 전체의 절반이 걸리는 조건이라면 인덱스로 위치를 찾은 뒤 다시 테이블을 50만 번 뒤지느니, 그냥 테이블을 순서대로 읽는 편이 빠르다. 옵티마이저는 이걸 알고 있어서 인덱스가 있어도 일부러 안 쓴다. "인덱스를 만들었는데 안 탄다"는 대부분 이 경우다.

그리고 인덱스는 공짜가 아니다. INSERT · UPDATE · DELETE 마다 인덱스도 함께 갱신된다. 인덱스 열 개를 단 테이블은 쓰기가 눈에 띄게 느려진다.

표준 SQL 문법과 예제

인덱스 만들기

CREATE INDEX idx_emp_dept_salary ON emp (dept_id, salary);
CREATE INDEX idx_emp_hire        ON emp (hire_date);
CREATE UNIQUE INDEX ux_dept_name ON dept (dept_name);

기본키에는 인덱스가 자동으로 생긴다. 외래키 컬럼(emp.dept_id)에는 MySQL/MariaDB 가 자동 생성하지만 PostgreSQL 은 만들지 않으므로, 조인에 쓰는 외래키에는 직접 만들어 준다.

복합 인덱스는 왼쪽부터 읽는다

(dept_id, salary) 인덱스는 dept_id 로 먼저 정렬하고, 같은 dept_id 안에서 salary 로 정렬한 목록이다. 전화번호부가 성으로 정렬되고 같은 성 안에서 이름으로 정렬된 것과 같다.

WHERE 조건	인덱스 사용	이유
`dept_id = 20 AND salary >= 4000`	두 컬럼 모두 사용	선두 컬럼 등호 + 두 번째 범위
`dept_id = 20`	사용	선두 컬럼만 써도 된다
`salary >= 4000`	사실상 못 씀	성을 모르고 이름만 아는 상태
`dept_id >= 20 AND salary = 5000`	dept_id 까지만	선두가 범위면 뒤 컬럼은 정렬돼 있지 않다

여기서 실무 규칙이 나온다. 복합 인덱스는 등호로 비교하는 컬럼을 앞에, 범위로 비교하는 컬럼을 뒤에 둔다. 그리고 (a, b) 인덱스가 있으면 (a) 단독 인덱스는 대개 필요 없다.

커버링 인덱스

SELECT dept_id, salary FROM emp WHERE dept_id = 20;

필요한 컬럼이 전부 인덱스 안에 있으면 DB 는 테이블 본체를 읽지 않는다. MySQL EXPLAIN 의 Extra: Using index, PostgreSQL 의 Index Only Scan 이 이 상태다. 목록 화면처럼 몇 개 컬럼만 뽑는 쿼리에서 체감 효과가 가장 크다.

EXPLAIN 읽기 — MySQL / MariaDB

EXPLAIN SELECT emp_id, salary FROM emp WHERE dept_id = 20 AND salary >= 4000\G

수십만 행 규모의 테이블에서라면 이런 형태가 나온다. (행이 열 개뿐인 실습 테이블에서는 옵티마이저가 그냥 풀 스캔을 고르는 것이 정상이다.)

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: emp
   partitions: NULL
         type: range
possible_keys: idx_emp_dept_salary
          key: idx_emp_dept_salary
      key_len: 10
          ref: NULL
         rows: 4213
     filtered: 100.00
        Extra: Using where; Using index

항목	의미	봐야 할 값
`type`	접근 방식	const > eq_ref > ref > range > index > ALL. ALL 이면 풀 스캔
`key`	실제로 고른 인덱스	NULL 이면 인덱스를 안 썼다
`rows`	읽을 것으로 추정한 행 수	최종 결과 건수와 크게 벌어지면 인덱스 설계를 의심
`Extra`	부가 동작	`Using index`=커버링(좋음), `Using filesort`·`Using temporary`=정렬/임시테이블(느림)

추정이 아니라 실제 측정치를 보려면 MySQL 8.0.18+ 는 EXPLAIN ANALYZE SELECT ..., MariaDB 10.1+ 는 ANALYZE SELECT ... 를 쓴다. 실제로 쿼리를 돌리므로 운영 DB 의 UPDATE/DELETE 에는 절대 쓰지 않는다.

EXPLAIN 읽기 — PostgreSQL

EXPLAIN (ANALYZE, BUFFERS)
SELECT dept_id, salary FROM emp WHERE dept_id = 20 AND salary >= 4000;

Index Only Scan using idx_emp_dept_salary on emp
  (cost=0.29..8.31 rows=4 width=10) (actual time=0.018..0.021 rows=3 loops=1)
  Index Cond: ((dept_id = 20) AND (salary >= 4000))
  Heap Fetches: 0
Planning Time: 0.104 ms
Execution Time: 0.041 ms

PostgreSQL 은 노드 이름으로 바로 읽힌다. Seq Scan 은 풀 스캔, Index Scan 은 인덱스로 위치를 찾아 테이블을 읽는 것, Index Only Scan 은 커버링이다. 위 예제에서 emp_id 대신 dept_id 를 뽑은 이유가 있다. PostgreSQL 의 보조 인덱스에는 기본키가 딸려 들어가지 않으므로, emp_id 를 뽑는 순간 인덱스만으로는 못 채워 Index Scan 으로 떨어진다. 뒤에 나오는 InnoDB 이야기와 정반대다. 핵심은 rows=4(추정)와 actual ... rows=3(실제)의 차이다. 이 둘이 수십 배 벌어지면 통계가 낡았다는 뜻이므로 ANALYZE emp; 를 먼저 돌린다.

DB별 차이

항목	MySQL · MariaDB	PostgreSQL	Oracle	SQL Server
실행계획 확인	`EXPLAIN`, `EXPLAIN ANALYZE`	`EXPLAIN (ANALYZE, BUFFERS)`	`EXPLAIN PLAN FOR` 후 `DBMS_XPLAN.DISPLAY`	`SET STATISTICS IO, TIME ON` / 그래픽 실행계획
통계 갱신	`ANALYZE TABLE emp;`	`ANALYZE emp;` (autovacuum 이 대개 처리)	`DBMS_STATS.GATHER_TABLE_STATS`	`UPDATE STATISTICS emp;`
함수 기반 인덱스	MySQL 8.0.13+ 함수 인덱스, MariaDB 는 가상컬럼	지원 (표현식 인덱스)	지원	계산 컬럼 + 인덱스
부분 인덱스	미지원	지원 (`WHERE` 절 포함)	미지원(파티션으로 대체)	지원 (필터 인덱스)
NULL 저장	인덱스에 저장	저장	단일 컬럼 인덱스에 NULL 미저장	저장

Oracle 의 마지막 항목은 실무에서 자주 걸린다. WHERE col IS NULL 조건이 단일 컬럼 인덱스를 타지 못하기 때문에, 필요하면 (col, 1) 같은 복합 인덱스를 만들어 우회한다.

실무에서 자주 틀리는 것

1. 인덱스 컬럼을 가공하면 인덱스가 죽는다

-- 2021년 입사자 (인덱스 못 탐)
SELECT * FROM emp WHERE YEAR(hire_date) = 2021;

-- 같은 결과, 인덱스 사용
SELECT * FROM emp
WHERE hire_date >= '2021-01-01' AND hire_date < '2022-01-01';

인덱스에는 hire_date 값이 정렬돼 있지 YEAR(hire_date) 값이 정렬돼 있지 않다. SUBSTR(phone,1,3) = '010', salary * 12 > 60000 도 전부 같은 이유로 풀 스캔이 된다. 컬럼은 좌변에 그대로 두고, 가공은 우변에서 한다. 두 번째 쿼리에서 BETWEEN '2021-01-01' AND '2021-12-31' 대신 부등호를 쓴 이유는, 컬럼이 DATETIME 으로 바뀌었을 때 12월 31일 오후 데이터가 빠지는 사고를 막기 위해서다.

2. 암묵적 형변환

-- 회원 테이블의 phone 이 VARCHAR 인데 숫자로 비교한 경우
SELECT * FROM member WHERE phone = 01012345678;

MySQL 은 이 경우 문자열 컬럼 쪽을 숫자로 바꿔 비교한다. 인덱스가 무력화되는 것은 물론이고, '010-1234-5678' 같은 값은 숫자로 읽히는 앞부분만 남아 10 이 되므로 결과까지 틀린다(문자 'abc' 라면 0 이 된다). 반대로 숫자 컬럼에 WHERE id = '123' 처럼 문자열을 넣는 것은 상수 쪽이 변환되므로 인덱스를 탄다. 애매하면 타입을 맞춰서 넘긴다. ORM 이나 JDBC 파라미터 바인딩에서 자주 새는 지점이다.

3. OR 와 앞쪽 와일드카드

SELECT * FROM emp WHERE dept_id = 20 OR mgr_id = 1005;   -- 각각 인덱스가 있어도 풀 스캔이 되기 쉽다
SELECT * FROM emp WHERE emp_name LIKE '%중%';            -- 접두를 모르면 인덱스 무용

OR 는 UNION 으로 나누면 각 갈래가 인덱스를 탄다. 여기서 UNION ALL 을 쓰면 두 조건을 모두 만족하는 행(개발팀이면서 관리자가 1005 인 김유신 · 이순신)이 두 번 나오므로 결과가 달라진다. 참고로 MySQL 은 index_merge 로 OR 양쪽 인덱스를 합쳐 쓰기도 하니, 풀 스캔인지 아닌지는 EXPLAIN 으로 확인하고 판단한다. 중간 일치 검색은 B-Tree 로는 답이 없어서 전문 검색 인덱스(MySQL FULLTEXT, PostgreSQL GIN + pg_trgm)로 풀어야 한다. 이걸 모르고 상품명 검색을 LIKE '%키워드%' 로 만들어 두면 데이터가 늘어난 뒤 서비스가 통째로 느려진다.

4. 정렬과 페이징을 잊는다

SELECT * FROM emp ORDER BY hire_date DESC LIMIT 20 OFFSET 100000;

DB 는 100,020 행을 읽어서 앞의 100,000 행을 버린다. 뒤로 갈수록 느려지는 목록이 여기서 나온다. 정렬 컬럼에 인덱스가 있으면 ORDER BY 자체는 Using filesort 없이 처리되지만 OFFSET 문제는 남는다. 마지막으로 본 값을 조건으로 넘기는 방식(키셋 페이징)으로 바꾸면 페이지 번호와 무관하게 일정한 속도가 된다. 12단원에서 다시 다룬다.

스스로 확인하기

다음 쿼리가 idx_emp_hire 를 타도록 고쳐라. SELECT * FROM emp WHERE DATE_FORMAT(hire_date, '%Y-%m') = '2021-05';
(dept_id, salary) 인덱스만 있을 때 WHERE salary = 5200 이 인덱스를 제대로 못 쓰는 이유를 한 문장으로 설명하라.
SELECT emp_id, emp_name FROM emp WHERE dept_id = 20 ORDER BY hire_date; 를 커버링 인덱스로 처리하려면 어떤 인덱스를 만들어야 하는가.

-- 1
SELECT * FROM emp
WHERE hire_date >= '2021-05-01' AND hire_date < '2021-06-01';

-- 2
-- 인덱스는 dept_id 로 먼저 정렬돼 있어 salary 만으로는 찾아갈 시작점이 없다.
-- (전체를 훑는 인덱스 풀 스캔은 가능하지만 이득이 없다)

-- 3
CREATE INDEX idx_emp_dept_hire_name ON emp (dept_id, hire_date, emp_name);
-- dept_id 로 좁히고, hire_date 순서가 인덱스에 이미 있어 정렬이 생략되며,
-- emp_id 는 기본키라 보조 인덱스에 포함되므로 테이블을 읽지 않는다(InnoDB 기준).

다음 10단원에서는 "부서별 급여 순위"처럼 8단원의 상관 서브쿼리로 힘겹게 풀던 문제를, 한 번의 스캔으로 끝내는 윈도우 함수로 다시 쓴다.

참고: MySQL EXPLAIN 출력 형식 문서, PostgreSQL EXPLAIN 사용법 문서