인덱스 튜닝

2. 핵심 원리

2.1 인덱스로 찾은 뒤에 드는 비용

인덱스로 조건에 맞는 행을 찾았다고 끝이 아닙니다. 조회할 컬럼이 인덱스에 없으면 행마다 테이블로 가서 나머지 컬럼을 읽어야 합니다. 이 왕복이 행마다 한 번씩 일어나는 랜덤 접근이라 행이 많을수록 큰 비용이 됩니다.

튜닝은 이 비용을 줄이는 세 방향으로 나뉩니다. 테이블 왕복을 없애는 커버링 인덱스, 찾는 범위를 좁히는 컬럼 순서, 그리고 인덱스가 오히려 손해인 경우를 가려내는 판단입니다.

2.2 커버링 인덱스

조회하는 컬럼이 모두 인덱스 안에 있으면 테이블을 읽지 않고 인덱스만으로 답합니다. 이런 인덱스를 커버링 인덱스라고 부릅니다. 인덱스에 컬럼이 늘어 크기가 커지는 대신 테이블 왕복이 사라집니다.

만드는 방법은 DB마다 조금 다릅니다. Oracle 과 MySQL 은 조회 컬럼을 복합 인덱스 뒤쪽에 이어 붙입니다. MSSQL 은 INCLUDE 로 키가 아닌 컬럼을 인덱스 잎(leaf)에만 둘 수 있어서, 정렬·탐색에 안 쓰는 컬럼을 부담 없이 붙입니다.

MySQL InnoDB 의 보조 인덱스는 잎에 PK 값을 함께 저장합니다. 그래서 PK 컬럼은 따로 넣지 않아도 커버링이 됩니다. 예를 들어 인덱스가 (emp_id) 뿐이어도 SELECT id FROM orders WHERE emp_id = 7 은 테이블을 읽지 않습니다.

핵심

커버링 인덱스는 조회 컬럼을 인덱스에 넣어 테이블 접근을 없앱니다. 자주 실행되고 조회 컬럼이 적은 쿼리에만 쓰고, 컬럼을 무턱대고 붙이지 않습니다.

2.3 복합 인덱스의 컬럼 순서

복합 인덱스는 첫 컬럼으로 정렬되고 그 안에서 둘째 컬럼으로 정렬됩니다. 선두 컬럼이 조건에 없으면 인덱스를 잘 못 쓴다는 것은 중급 09 인덱스에서 다뤘습니다. 여기서는 그다음, 조건이 여럿일 때의 순서입니다.

기준은 하나입니다. 등호(=) 조건 컬럼을 앞에, 범위 조건(BETWEEN, 부등호) 컬럼을 뒤에 둡니다. 범위 조건이 나온 컬럼 뒤의 컬럼은 탐색 범위를 좁히지 못하고, 범위 안에서 하나씩 걸러 보는 용도로만 쓰입니다.

등호 조건끼리의 순서는 다른 쿼리와 공유할 수 있는 쪽을 앞에 둡니다. 여러 쿼리가 선두 컬럼으로 쓰면 인덱스 하나로 여러 쿼리를 받을 수 있습니다.

선두 컬럼 조건이 아예 없는 쿼리에도 예외는 있습니다. Oracle 은 INDEX SKIP SCAN 으로 선두 컬럼의 값 종류별로 건너뛰며 찾을 수 있고, MySQL 은 8.0.13 부터 Skip Scan 을 씁니다. 다만 선두 컬럼 값 종류가 적을 때만 쓸 만하므로 이것에 기대어 설계하지 않습니다.

2.4 선택도

선택도는 컬럼의 값이 얼마나 다양한지를 나타냅니다. 값 종류 수를 전체 행 수로 나눈 값으로 가늠하고, 1에 가까울수록(값이 거의 다 다를수록) 선택도가 높다고 합니다.

선택도가 낮은 컬럼 하나만으로 만든 인덱스는 한 값에 걸리는 행이 너무 많아 좁히는 효과가 적습니다. 그런 컬럼은 단독 인덱스보다 다른 컬럼과 묶은 복합 인덱스의 한 자리로 쓰는 편이 낫습니다. 값이 한쪽으로 치우친 경우에는 값 종류 수만으로 판단하지 말고 값별 건수도 함께 봅니다.

2.5 인덱스가 오히려 느린 경우

인덱스를 타면 항상 빠를 것 같지만 그렇지 않습니다. 조건에 걸리는 행이 전체의 상당 부분이면, 인덱스로 찾아 행마다 테이블을 따로 찾아가는 것보다 테이블을 처음부터 순서대로 읽는 편이 빠릅니다. 전체 읽기는 연속된 블록을 한꺼번에 읽는 순차 접근이기 때문입니다.

경험적으로 전체의 절반이 넘는 범위라면 인덱스가 도움이 안 되는 경우가 많습니다. 정해진 기준선이 있는 것은 아니고 테이블 행이 인덱스 순서와 얼마나 비슷하게 놓여 있는지에 따라 달라집니다. Oracle 은 이 정도를 클러스터링 팩터라는 통계로 판단합니다.

이런 경우 옵티마이저는 인덱스가 있어도 전체 읽기를 고르는 것이 정상입니다. 계획이 전체 읽기로 나왔다고 무조건 인덱스를 강제하지 말고, 조건에 걸리는 행이 전체의 몇 %인지부터 확인합니다.

2.6 인덱스의 비용과 정리

인덱스는 INSERT, UPDATE, DELETE 마다 함께 갱신됩니다. 인덱스가 5개인 테이블에 한 행을 넣으면 테이블과 인덱스 5개를 모두 고쳐야 하므로 인덱스 개수만큼 쓰기가 느려집니다. 대량 적재 배치에서는 이 차이가 크게 나옵니다.

그래서 안 쓰는 인덱스는 지우는 것이 좋습니다. 다만 사용 기록이 충분히 쌓이기 전에 지우면 월말 배치처럼 가끔 돌던 쿼리가 느려집니다.

숨기기는 인덱스를 지우지 않고 옵티마이저가 못 쓰게 만드는 것입니다. 숨긴 뒤 한동안 느려지는 쿼리가 없으면 그때 지웁니다. Oracle 과 MySQL 은 다시 VISIBLE 로 돌리면 즉시 복구되고, MSSQL 의 DISABLE 은 다시 쓰려면 REBUILD 해야 합니다.

2.7 H2 EXPLAIN 으로 확인할 수 있는 것

H2 는 다시 쓴 SQL 아래에 /* PUBLIC.인덱스이름: 조건 */ 주석을 답니다. 이 레슨에서는 어떤 인덱스를 골랐는지, 인덱스 조건에 무엇이 들어갔는지, 정렬을 인덱스 순서로 대신하는지(index sorted) 세 가지만 읽습니다.

H2 계획에는 테이블 접근 단계가 따로 나오지 않아 커버링 여부를 볼 수 없습니다. 인덱스 쓰기 비용이나 클러스터링 팩터도 H2 로 재현되지 않으므로 그 부분은 설명만 있습니다.