이번 글에서는 보조 인덱스와 커버링 인덱스를 실제 사용하면서 속도를 비교해보겠습니다. 이전 글에서 인덱스 트리에 대한 개념 설명 글에 이어지는 글입니다!
진행중인 리멤버미 프로젝트에서는 회원 가입하면서 사용자 가족을 찾아야 하기 때문에 사용자 검색 이 많이 발생합니다. 사용자는 nickname으로 검색하기 때문에, nickname 컬럼에 보조 인덱스를 생성해주기로 했습니다.
그리고 현 프로젝트에서의 사용자 검색할 때 모든 column 데이터가 필요하지는 않습니다. 닉네임과 아이디, 그리고 프로필 이미지 column만 조회하면 됩니다. 따라서 이 column들만을 위한 커버링 인덱스를 생성해주어 검색 속도를 개선해보고자 합니다.
이전 프로젝트에서 MySQL Index를 사용해서 속도를 개선해본 경험이 있습니다(해당 프로젝트는 데이터의 모든 column을 조회하기 때문에 커버링 인덱스를 사용할 필요가 없었습니다). 여기서는 보조 인덱스에 이어 커버링 인덱스까지 생성하여 속도를 비교해보겠습니다.
데이터 1만 개로 테스트 해보았습니다. 더미 데이터는 https://www.mockaroo.com/ 이 사이트와 엑셀을 활용해서 생성해주었습니다. 데이터가 1만개밖에 되지 않아서 적게 나왔지만, 대규모 서비스일때를 가정하고 개선해보겠습니다. (mockaroo 사이트의 무료 버전에서는 한번에 최대 1000개만 생성해줄 수 있습니다.)
- 샘플 member 데이터 1만개
+) 이후 데이터 100만건 추가
랜덤 데이터 1만건은 너무 적은 것 같아, 풀 스캔도 @@ms밖에 안걸렸습니다. 그래서 다음의 MySQL의 프로시저(여러 SQL문 한번에 호출)로 더미 데이터 생성방법을 통해서 약 100만건의 데이터를 넣어주고 속도를 다시 비교해보았습니다!
- MySQL의 프로시저로 더미 데이터 만들어주는 코드 샘플
여기에 원하는 컬럼들에 랜덤값을 넣어서 더미 데이터를 생성해주었습니다.
1. 보조 인덱스 생성할 경우
사용자를 nickname으로 조회하므로, 이 컬럼으로 보조 인덱스를 생성해보겠습니다. 아래 그림이 보조 인덱스 트리의 구조입니다. 보조 인덱스트리에서 데이터의 PK를 찾아 다시 클러스터형 인덱스 트리를 탐색하게 됩니다.
아래처럼 인덱스를 생성해주고 쿼리 계획을 확인해보겠습니다!
ALTER TABLE 테이블명 ADD INDEX 인덱스별명 (컬럼명);ANALYZE TABLE member;SELECT nickname, profile_img, username WHERE nickname LIKE ‘이름’;
2. 커버링 인덱스 생성할 경우
이제 커버링 인덱스를 생성해주겠습니다. 아래는 커버링 인덱스 트리입니다. 해당 트리에 쿼리에서 원하는 column이 모두 있기 때문에 이 트리만 탐색하여 조회합니다. 다시 클러스터형 인덱스 트리를 탐색하지 않으므로 속도가 빨라질 것으로 예상합니다.
커버링 인덱스를 생성해주는 방법은 엄청 간단합니다. 기존에 인덱스를 생성해주던 방식에서 컬럼들을 추가해서 지정해주면 됩니다. 그러면 해당 컬럼들을 포함하여 인덱스 트리를 생성합니다.
ALTER TABLE 테이블명 ADD INDEX 인덱스별명 (컬럼명1, 컬럼명2, 컬럼명3);
커버링 인덱스를 다음처럼 생성해주겠습니다.
ALTER TABLE 테이블명 ADD INDEX 인덱스별명 (nickname, profile_img, username);ANALYZE TABLE member;SELECT nickname, profile_img, username WHERE nickname LIKE ‘이름’;
그리고 마지막 SELECT 이 쿼리에는 커버링 인덱스 트리에 있는 정보만 조회하므로, 해당 트리만 탐색하게 됩니다. (만약 쿼리에 커버링 인덱스에 있는 column외에 다른 column이 있다면 커버링 인덱스를 안 타게 됩니다. 어차피 다른 column 데이터를 찾으러 클러스터형 인덱스 트리를 탐색해야 하기 때문입니다! 그때는 다른 보조 인덱스 트리를 타는 등 쿼리마다 알아서 DBMS에서 최적화해줍니다.)
3. 결과 비교
우선 MySQL Workbench에서 Query Cost를 비교해보겠습니다.
이때 쿼리 cost는 MySQL에서 자체적으로 판단한 쿼리 비용이라고 합니다. (CPU 비용 등을 합산한 수치) 1,000 - 100,000이 평균치이고 그 이상이면 비싼 쿼리라고 합니다. 그리고 각각의 실행시간 또한 PROFILES로 측정해보겠습니다. (측정할 쿼리 앞에 EXPLAIN → duration time으로 확인해주었습니다. 100만건 기준)
++) 참고로 원래는 1만건 기준으로 쿼리 실행 시간(duration time)을 측정해주었으나, 풀 스캔했을 때도 너무 적은 시간이 걸려서 100만건으로 더미 데이터 세팅 후 다시 시간을 측정해주었습니다.
결과적으로 각각의 Query Cost와 Duration time을 비교해보면 다음과 같습니다.
- Full Scan시
- 쿼리 cost : 1037.65
- 쿼리 실행 시간 : 0.2153s
(EXPLAIN → duration time으로 확인)
- 보조 인덱스 사용시
- 쿼리 cost : 0.71
- 쿼리 실행 시간 : 0.0004s
- 커버링 인덱스 사용시
- 쿼리 cost : 0.45
- 쿼리 실행 시간 : 0.0003s
쿼리 실행 시간을 살펴보면, Full Scan은 0.2153s에서 보조 인덱스 사용은 0.0004s로 개선되었습니다. 그리고 커버링 인덱스 생성 후에는 0.0003s로 개선되었습니다.(100만건 기준) Full Scan에서 보조 인덱스는 크게 개선되었지만, 커버링 인덱스로는 생각보다 시간이 크게 단축되지는 않았습니다.
이론적으로는 커버링 인덱스 생성 시, 다시 DB 테이블 탐색(클러스터형 인덱스 탐색)을 하지 않기 때문에 보조 인덱스보다 커버링 인덱스가 시간이 훨씬 단축되어야 합니다. 물론 단축되기는 했지만, 보조 -> 커버링 생성 후에는 0.0004s/0.0003s로 큰 차이가 발생하지 않았습니다. 예상하기로, 현재는 PK를 알고 있으면 DB 테이블 탐색은 금방할 수 있어서 보조 -> 커버링 생성 후에는 속도 차이가 크지 않은 것 같습니다. 반면 원하는 nickname 컬럼값을 찾는 게(보조 인덱스 트리 탐색) 훨씬 오래 걸리기 때문에 풀스캔 -> 보조 인덱스 생성 후에 속도가 크게 단축된 것으로 보입니다.
추가적으로 EXPLAIN으로 쿼리 실행계획을 살펴보면, Extra 탭에서 실제 인덱스를 타는지 확인해볼 수 있습니다.
- Using index : 다시 DB 테이블 서치X, 인덱스 트리만으로 조회 완료! ⇒ 커버링 인덱스 사용했다는 의미
- Using index condition : 보조 인덱스 트리로 탐색후, 다시 클러스터형 인덱스 트리 탐색 (index push down이라고 하기도 한다고 합니다.)
인덱스는 언제 사용하는 게 좋을까
여기서는 1만개의 데이터로 테스트를 해보았는데, 100만, 1000만 그 이상이면 속도 개선이 더욱 클 것 같습니다. 하지만 만약 커버링 인덱스 페이지에 많은 column을 두면 오히려 비효율적일 수 있습니다. 실제 데이터 페이지 외에 중복 데이터들을 저장하고 있게 되니까 DB 리소스 낭비가 될 수 있기 때문입니다. 또한 데이터가 자주 update가 된다면 페이지 분할 등 관리 비용이 커지게 됩니다. 인덱스는 속도를 개선해주지만, 그만큼 관리 비용이 발생하므로 자주 사용하는 조회 쿼리 / DB 상황에 따라 고민하여 선택해야 할 것 같습니다.
아마 해당 서비스에서 속도 vs 메모리 공간 중 어떤 게 더 중요한 지에 따라 달라지지 않을까 싶습니다. 서비스에서 특정 조회 쿼리가 가장 중요한 기능이고, 속도가 빨라야 한다면 DB 리소스를 더 사용하더라도 커버링 인덱스를 사용하는 게 좋지 않을 까 싶습니다. 그 외에 경우라면, DB에 중복 데이터가 너무 많아져서 오히려 비효율적일 수 있을 것 같습니다. 한 컬럼만 저장하는 보조 인덱스도 DB 리소스를 잡아먹는데 커버링 인덱스는 여러 컬럼을 저장하기 때문에 더 부담되지 않을 까 생각합니다. 또한 조회보다 update가 많을 경우에는 커버링 인덱스 트리 값도 다 수정하고 페이지 분할이 일어나고.. 오히려 성능이 떨어질 수도 있을 것 같습니다. 인덱스는 속도 개선이 큰 만큼 관리 비용도 발생하므로 서비스의 특성에 따라 선택해서 사용해야 할 것 같습니다!
현재 조회 기능에서는 인덱스가 필요할까
현재 프로젝트에서 커버링 인덱스를 생성한 결과, 보조 인덱스 생성 시와 커버링 인덱스 생성 시의 속도 차이가 크지 않고, 오히려 DB 리소스를 불필요하게 잡아먹는 다고 판단했습니다 또한 member 테이블은 회원 가입시마다 새로운 정보가 update되고, 사용자 조회시 많은 데이터들(profile_img, username, nickname)이 필요했습니다.
현재 기능에서는 조회 성능 개선은 커버링 인덱스가 아닌 보조 인덱스만으로도 충분했기 때문에, 보조 인덱스를 생성하는 것만으로 성능 개선을 마쳤습니다. 하지만 만약 서비스에서 이 기능이 가장 핵심 기능이고, 조회 속도가 중요한 기능이면 DB 비용을 더 쓰더라도 커버링 인덱스를 사용했을 것 같습니다.