백엔드 개발을 하다보면 성능에 대해서 고민을 하게된다. 대표적으로 비즈니스 로직의 시간복잡도, 외부 서비스들과의 통신, 그리고 DB 쿼리의 성능 등이 있을 것이다.
오늘 이 글에서는 DB 쿼리의 성능 최적화, SQL 튜닝의 대표적인 방법인 인덱스에 대해서 정리해 보려고 한다. ORM 을 많이 사용하다보니 기반이 되는 SQL 과 그 최적화에 대해서 소홀했던 부분이 있어서 기존에 알고있던 인덱스에 대한 정보들을 정리해서 작성해보고자 한다.
1. 인덱스
인덱스는 이름 그대로 원하는 데이터를 빠르게 찾기 위한 자료구조이다. 인덱스로 설정한 데이터들을 실제 데이터와 따로 보관 및 관리하여 테이블 전체를 스캔하지 않고도 데이터를 찾을 수 있도록 한다.
기본적으로 B-tree 구조로 구성되어 있으며, leaf node 에 해당 인덱스에 대한 실제 데이터 레코드의 주소값을 저장한다. B-tree 구조로 구성되어 있기 때문에 검색에는 빠르지만 insert, delete, update 등 인덱스 컬럼의 변경이 필요한 경우 B-tree 정렬을 위해 수행시간이 오래 걸릴 수 있다. 또한 인덱스 생성을 위해 DB 에 약 10% 추가 공간을 할당해주어야 한다. 이때문에 인덱스를 적용할 때에는 insert, delete, update 가 빈번하게 일어나지 않고 대신 join, where, order by 와 같이 조건문에 자주 사용되고, 데이터의 중복이 없는 컬럼을 선택하는 것이 유리하다.
2. 데이터 스캔방식
그렇다면 인덱스를 적용하는 것이 왜 성능에 유리할까? 이를 알기 위해서는 테이블에서 데이터를 조회하는 방식에대해서 알아야 한다.
데이터를 조회하는 방식을 데이터 스캔이라고 한다. 데이터 스캔방식은 인덱스 사용유무에 따라 크게 테이블 스캔과 인덱스 스캔으로 구분된다.
1) 테이블 스캔
- Table Full Scan
테이블의 모든 데이터를 순차적으로 스캔하는 방식이다. 인덱스가 없거나, 쿼리 조건에 인덱스 컬럼을 포함하지 않아 인덱스를 활용할 수 없거나, 테이블의 레코드가 너무 적어서 인덱스를 사용하지 않는 것이 더 유리한 경우 이 방식으로 스캔한다.
MySQL 의 InnoDB 엔진에서 데이터들을 저장하는 기본 단위는 페이지 (page) 이다. 테이블 풀 스캔은 이 페이지들을 모두 읽어와서 스캔을 수행한다. 이때문에 레코드가 많은 경우 성능이 저하되는 문제가 있다.
2) 인덱스 스캔
- Index Full Scan
인덱스의 leaf node 전체를 순차적으로 스캔하는 방식이다. 인덱스만 전체를 스캔하고 테이블은 스캔하지 않는다. 이때문에 테이블 풀 스캔보다는 효율적이다.
쿼리가 인덱스의 컬럼 값들만 조회하여 실제 테이블 레코드를 읽지 않아도 되는 경우에 사용된다. 이렇게 쿼리가 인덱스의 컬럼만으로 처리 가능할 때를 커버링 인덱스라고 부른다.
- Index Range Scan
특점 범위 조건으로 인덱스를 스캔하고 이에 대응하는 레코드들을 읽는 방식이다. BETWEEN 이나 LIKE, 부등호 등을 사용하여 인덱스의 범위를 조건으로 검색할 때 사용된다.
시작점이 되는 leaf node 부터 종료 지점의 leaf node 까지 스캔한 후, 필요에 따라 leaf node 들이 가리키는 레코드들을 조회한다.
- Index Unique Scan
인덱스의 특정 값으로 스캔하고 해당 leaf node 가 참조하는 테이블 레코드를 읽는 방식이다. PK 나 Unique 인덱스의 모든 컬럼에 대해 WHERE 절에서 '=' 조건으로 특정 값에 접근할 때 사용된다. 특정 값을 조건으로 검색하는 만큼 인덱스 스캔 방식 중 가장 효율적이다.
- Loose Index Scan
인덱스에서 필요한 부분만 선택적으로 스캔하고 이에 대응하는 레코드들을 읽는 방식이다. WHERE 절의 조건을 기준으로 불필요한 인덱스 키는 무시하고 데이터를 조회한다. GROUOP BY, MAX, MIN 등이 사용된 쿼리를 최적화할 때 사용된다.
- Index Merge Scan
테이블 내에 생성된 인덱스들을 통합하여 스캔하는 방식이다. WHERE 절의 조건 컬럼이 서로 다른 인덱스에 존재할 때 사용된다. 각각의 인덱스에 접근하여 두 조건 모두 공통으로 만족하는 데이터에 대해서만 조회한다.
3. 인덱스 사용예제
인덱스를 적용했을때와 적용하지 않았을때 쿼리가 동작하는 차이에 대해서 비교하는 실험을 해보려고 한다. 예제 테이블을 생성하고 특정 컬럼에 인덱스를 적용한 후, 인덱스를 포함한 쿼리와 그렇지 않은 쿼리를 작성하여 EXPLAIN 을 이용한 실행계획을 비교하여 실험을 진행한다.
예제 테이블 users 를 생성하고 더미 데이터 100만건을 INSERT 했다. users 테이블의 컬럼 중에는 username 에만 INDEX 를 적용했다.
먼저 전체 데이터를 조회하는 쿼리를 작성하고, 이에 대한 실행계획을 확인했다.
EXPLAIN SELECT * FROM users;
type ALL 로 모든 데이터를 스캔하는 테이블 풀 스캔이 실행됨을 알 수 있다.
이번에는 인덱스가 적용된 age 컬럼 데이터만 조회하는 쿼리로 실행계획을 확인했다.
EXPLAIN SELECT age FROM users;
type 이 index 로 출력되어 인덱스 전체를 스캔하는 인덱스 풀 스캔이 실행되는 것을 알 수 있다. 또한 Extra 에 Using index 가 출력되었는데, 이 값은 커버링 인덱스를 의미한다.
이번에는 WHERE 절로 조회 조건을 준 쿼리를 테스트 해보았다. age 컬럼에 조건을 준 쿼리를 테스트했다.
EXPLAIN SELECT age FROM users WHERE age = 30;
EXPLAIN SELECT * FROM users WHERE age = 30;
age = 30 조건을 주고 조회 대상을 age 로 했을 때와 전체로 했을 때, 두가지 쿼리를 확인했다.
두 쿼리 모두 idx_age 인덱스를 사용하고 type ref 로 인덱스 조건에 대한 다건 스캔을 수행한다는 것을 알 수 있다.
반면에 차이점은 Extra 의 값인데, age 컬럼만 조회한 쿼리는 커버링 인덱스를 충족하기 때문에 Using index 가 출력된다.
이번에는 age 컬럼을 GROUP BY 로 조회하는 쿼리를 작성하여 확인했다.
EXPLAIN SELECT age FROM users GROUP BY age;
위의 내용에서 정리했듯이 GROUP BY 의 경우 Loose Index Scan 방식으로 동작한다. 실행계획을 보면 type 은 range 로 idx_age 인덱스의 특정 범위만 조회하는 것을 알 수 있다. 그리고 Extra 의 Using index for group-by 값은 인덱스만을 통해 GROUP BY 를 처리할 수 있음을 의미한다. 이를 바탕으로 나머지 테이블의 데이터를 스캔할 필요 없이 인덱스의 leaf node 들만을 스캔하면 되고, idx_age 의 그룹별로 첫번째 값만 조회하면 되기때문에 Loose Index Scan 방식으로 스캔한다는 것을 유추할 수 있다.
이번에는 고유한 값을 가지는 인덱스에 대한 동등조건 쿼리를 확인해보았다. PK 인 id 컬럼을 조건으로 실행했다.
EXPLAIN SELECT id FROM users WHERE id = 1;
const type 으로 여러 데이터를 스캔하는 것이 아니라 특정한 포인트 만을 스캔하는 것을 알 수 있다. 또한 Extra 의 Using index 값을 통해 커버링 인덱스를 만족하는 것을 확인할 수 있다. 이를 통해서 Index Unique Scan 과 같이 인덱스의 특정 값만 조회하는 것을 알 수 있다.
이번에는 서로 다른 인덱스를 가지는 두 컬럼에 조건을 주어 쿼리를 작성했다. 각각 PK 인 id 와 idx_age 인덱스가 적용된 age 컬럼을 사용했다.
EXPLAIN SELECT * FROM users WHERE id < 100 and age = 30;
index_merge type 으로 MySQL 이 Index Merge 방식으로 스캔을 실행하는 것을 확인할 수 있다. Extra 에서는 Using intersect(idx_age,PRIMARY) 라는 값을 출력한다. 이는 Index Merge 스캔이 Index Merge Intersection Access Algorithm 으로 동작한다는 의미이다. 이 방식은 각각 idx_age 인덱스와 PRIMARY 인덱스를 스캔하고 두 인덱스간의 교집합을 찾는 방식이다.
4. 결론
DB 가 실제 데이터를 스캔하는 방식들과 인덱스를 사용하는 방식, 그리고 MySQL 에서 쿼리의 실행계획을 통한 예제들을 정리해보았다. 또한 MySQL 문서를 보면서 이외에도 다양한 다양한 스캔 방식, 최적화 방식들이 있다는 것을 알 수 있었다.
DB 에서 데이터를 조회하는 부분은 서비스에서 빈번하게 일어나는 작업인만큼 성능에 많은 영향을 끼친다. 그렇기에 이번에 정리한 내용들을 머릿속에 새기고 앞으로 쿼리 작성시에 관련 내용들을 잘 확인하고 고려하면서 개발을 진행해야겠다.
[Reference]
- https://jofestudio.tistory.com/127
- https://developers-haven.tistory.com/54
- https://0soo.tistory.com/235
- https://dev.mysql.com/doc/refman/8.4/en/group-by-optimization.html#loose-index-scan
- https://dev.mysql.com/doc/refman/8.4/en/where-optimization.html
- https://dev.mysql.com/doc/refman/8.4/en/index-merge-optimization.html
'Database | ORM' 카테고리의 다른 글
| [MySQL] EXPLAIN (2) | 2025.07.29 |
|---|
