[실전 DB 06] 부분 인덱스와 커버링 인덱스로 더 정교하게

기본 인덱스와 복합 인덱스를 다뤘으니, 이제 한 단계 더 정교한 기법을 볼 차례다. 인덱스 크기를 줄이는 부분 인덱스와, 인덱스만으로 쿼리를 끝내는 커버링 인덱스다. 이 둘을 알면 인덱스를 더 작고 빠르게 만들 수 있다. 이번 편은 이 두 심화 기법을 다룬다.

이 기법들은 인덱스를 그냥 만드는 것과 정교하게 만드는 것의 차이를 만든다. 특히 대규모 데이터에서 효과가 크다.

1. 부분 인덱스란 무엇인가

부분 인덱스는 테이블의 일부 행만 대상으로 하는 인덱스다. 특정 조건에 맞는 행만 인덱스에 담는 것이다. 예를 들어 가상의 글 테이블에서 삭제되지 않은 공개 글만 목록에 나온다고 하자. 그러면 삭제됐거나 비공개인 글은 인덱스에 담을 필요가 없다.

이럴 때 조건을 붙인 부분 인덱스를 만든다. CREATE INDEX idx_articles_live ON articles(created_at) WHERE is_deleted = 0 AND status = published 같은 형태다. 이러면 살아 있는 공개 글만 인덱스에 담긴다. 인덱스가 전체 행이 아니라 조건에 맞는 일부만 담으니 크기가 작아진다.

인덱스가 작아지면 여러 이점이 있다. 저장 공간을 덜 쓰고, 인덱스를 뒤지는 것도 빨라지고, 데이터를 쓸 때 인덱스 갱신 부담도 준다. 특히 목록에 나오는 조건이 정해져 있다면, 그 조건에 맞는 행만 담은 부분 인덱스가 전체를 담은 인덱스보다 효율적이다. 필요한 부분만 색인하는 것이다.

부분 인덱스와 커버링 인덱스는 인덱스를 정교하게 만드는 심화 기법이다. 기본 인덱스로 스캔을 없애는 게 1단계라면, 이 기법들은 그 위에서 인덱스를 더 작고 빠르게 만드는 2단계다. 대규모 데이터일수록 이 정교함이 큰 차이를 만든다. 기본이 갖춰진 다음 이 기법으로 다듬는 것이다.

한 가지 더, 이 심화 기법들은 순서를 지켜 적용한다. 아직 전체 스캔을 하는 쿼리가 있다면, 부분 인덱스나 커버링 인덱스 같은 정교한 기법보다 기본 인덱스부터 만드는 게 순서다. 큰 문제를 먼저 잡고 그다음 이런 기법으로 다듬는 것이다. 순서를 지키면 노력이 효과적인 곳에 먼저 쓰인다.

2. 부분 인덱스가 유용한 경우

부분 인덱스가 특히 유용한 건, 전체 중 일부만 자주 조회되는 경우다. 예를 들어 대부분의 글은 평범한데 소수만 베스트로 뽑힌다고 하자. 베스트 글 목록을 자주 보여준다면, 베스트인 글만 담은 부분 인덱스가 효과적이다. 전체 글을 뒤질 필요 없이 베스트만 담긴 작은 인덱스를 보면 된다.

내 사이트에서도 특정 조건의 글만 자주 조회되는 경우에 부분 인덱스를 썼다. 전체를 담은 인덱스는 크고, 그 조건에 안 맞는 대다수 행까지 담고 있어 비효율적이었다. 조건에 맞는 소수만 담은 부분 인덱스로 바꾸니, 인덱스가 훨씬 작아지고 그 목록 조회가 빨라졌다.

다만 부분 인덱스는 그 조건에 맞는 쿼리에만 쓰인다. 삭제 안 된 공개 글만 담은 인덱스는, 쿼리도 그 조건을 포함해야 쓸 수 있다. 그래서 부분 인덱스의 조건은 실제 쿼리가 항상 쓰는 조건에 맞춰야 한다. 조건이 어긋나면 인덱스가 안 쓰인다. 이것도 실행 계획으로 확인한다.

부분 인덱스는 조건에 맞는 일부 행만 담아 인덱스를 작게 만든다. 목록에 나오는 조건이 정해져 있다면, 그 조건에 맞는 행만 담은 인덱스가 전체를 담은 것보다 효율적이다. 인덱스가 작아지면 공간도 덜 쓰고, 뒤지기도 빠르고, 쓰기 갱신 부담도 준다. 필요한 부분만 색인하는 발상이다.

3. 커버링 인덱스란 무엇인가

커버링 인덱스는 쿼리가 필요로 하는 모든 컬럼을 인덱스에 담아, 인덱스만으로 쿼리를 끝내는 기법이다. 보통 인덱스로 행의 위치를 찾은 다음, 그 행을 다시 읽어 필요한 컬럼을 가져온다. 그런데 필요한 컬럼이 다 인덱스에 있으면, 그 행을 다시 읽을 필요 없이 인덱스만으로 답을 낸다.

예를 들어 목록에서 글의 제목과 시각만 보여준다고 하자. 인덱스에 시각과 제목이 다 담겨 있으면, 데이터베이스는 인덱스만 읽고 원래 행은 안 읽어도 된다. 인덱스가 쿼리를 통째로 커버하는 것이다. 그래서 커버링 인덱스라 부른다.

이게 효율적인 이유는 행을 다시 읽는 단계가 사라지기 때문이다. 인덱스로 위치를 찾고 다시 행을 읽는 두 단계가, 인덱스만 읽는 한 단계로 준다. 읽기가 줄고 속도가 빨라진다. 자주 실행되는 목록 쿼리에 커버링 인덱스를 적용하면 그 효과가 크다.

부분 인덱스는 그 조건을 포함한 쿼리에만 쓰인다는 걸 기억해야 한다. 삭제 안 된 공개 글만 담은 인덱스는 쿼리도 그 조건을 포함해야 쓸 수 있다. 그래서 부분 인덱스의 조건은 실제 쿼리가 항상 쓰는 조건에 맞춰야 한다. 조건이 어긋나면 인덱스가 안 쓰이니, 이것도 실행 계획으로 확인한다.

또 하나, 부분 인덱스는 전체 중 일부만 자주 조회될 때 특히 빛난다. 대부분의 글은 평범한데 소수만 베스트로 뽑히고 그 베스트 목록을 자주 보여준다면, 베스트만 담은 부분 인덱스가 효과적이다. 전체를 뒤질 필요 없이 작은 인덱스만 보면 되니, 자주 조회되는 특수 목록에 이 기법이 잘 맞는다.

4. 커버링 인덱스의 균형

커버링 인덱스에도 균형이 필요하다. 쿼리가 필요로 하는 컬럼을 다 인덱스에 담으면, 인덱스가 그만큼 커진다. 컬럼을 많이 담을수록 커버 범위는 넓지만 인덱스가 무거워진다. 그래서 자주 조회되면서 몇 개 컬럼만 필요한 쿼리에 커버링 인덱스가 잘 맞는다.

본문처럼 큰 컬럼까지 인덱스에 담으면 인덱스가 지나치게 커져 역효과다. 그래서 커버링 인덱스는 작은 컬럼 몇 개로 끝나는 목록 쿼리에 적합하다. 제목, 시각, 작성자 정도의 가벼운 컬럼으로 목록을 보여주는 쿼리라면, 그것들을 담은 커버링 인덱스가 효과적이다. 큰 컬럼은 담지 않는다.

이 균형을 잡으려면 그 쿼리가 정말 어떤 컬럼을 필요로 하는지 정확히 알아야 한다. 필요 없는 컬럼까지 담으면 인덱스만 무거워진다. 딱 필요한 컬럼만 담아 인덱스만으로 끝나게 하는 게 커버링 인덱스의 요령이다. 이것도 쿼리 패턴을 알아야 설계할 수 있다.

커버링 인덱스는 필요한 컬럼을 다 담아 행을 다시 읽는 단계를 없앤다. 보통은 인덱스로 위치를 찾고 그 행을 다시 읽는데, 필요한 컬럼이 다 인덱스에 있으면 인덱스만으로 끝난다. 두 단계가 한 단계로 주니 읽기가 줄고 빨라진다. 가벼운 컬럼 몇 개로 끝나는 목록 쿼리에 이 기법이 잘 맞는다.

또 하나, 부분 인덱스와 커버링 인덱스는 함께 쓸 수도 있다. 조건에 맞는 일부만 담으면서 그 쿼리가 필요로 하는 컬럼도 다 담으면, 작으면서도 인덱스만으로 끝나는 인덱스가 된다. 자주 조회되는 특정 목록에 이렇게 두 기법을 결합하면, 인덱스가 작고 행 읽기도 없어 매우 효율적이다.

5. 정교한 인덱스의 값어치

부분 인덱스와 커버링 인덱스는 인덱스를 그냥 만드는 것과 정교하게 만드는 것의 차이다. 기본 인덱스로도 스캔은 없앨 수 있지만, 부분 인덱스로 크기를 줄이고 커버링 인덱스로 행 읽기를 없애면 한 단계 더 최적화된다. 대규모 데이터일수록 이 정교함이 큰 차이를 만든다.

다만 이 기법들은 기본 인덱스가 잘 갖춰진 다음의 심화다. 아직 전체 스캔을 하는 쿼리가 있다면, 정교한 기법보다 기본 인덱스부터 만드는 게 순서다. 큰 문제를 먼저 잡고, 그다음 이런 정교한 기법으로 다듬는 것이다. 순서를 지키면 노력이 효율적으로 쓰인다.

정리하면 부분 인덱스는 조건에 맞는 일부만 담아 인덱스를 작게 만들고, 커버링 인덱스는 필요한 컬럼을 다 담아 행 읽기를 없애며, 둘 다 균형을 잡아 적용한다. 다음 편에서는 인덱스를 만들었는데도 데이터베이스가 안 쓰는 답답한 상황과 그 원인인 통계 문제를 다룬다.

정교한 인덱스에도 균형이 필요하다. 커버링 인덱스는 컬럼을 많이 담을수록 커지고, 부분 인덱스는 조건이 쿼리와 맞아야 쓰인다. 큰 컬럼까지 담으면 역효과다. 그래서 그 쿼리가 정말 어떤 컬럼을 필요로 하는지 정확히 알고, 딱 필요한 만큼만 담아야 한다. 쿼리 패턴을 알아야 정교하게 설계할 수 있다.

정교한 인덱스의 값어치는 대규모 데이터에서 확실해진다. 데이터가 적을 땐 기본 인덱스로 충분하지만, 데이터가 커지면 인덱스 크기와 행 읽기 단계가 부담이 된다. 부분 인덱스로 크기를 줄이고 커버링 인덱스로 행 읽기를 없애면 그 부담이 준다. 규모가 커질수록 정교한 최적화의 효과가 커지는 것이다.

또 하나 기억할 것은, 이 심화 기법들도 실행 계획으로 검증한다는 점이다. 부분 인덱스가 그 조건 쿼리에 쓰이는지, 커버링 인덱스로 행 읽기가 사라졌는지를 실행 계획으로 확인한다. 만들었다고 믿지 말고 쓰인다고 확인하는 원칙이 여기서도 그대로다.