☕ 개요
이번 글에서는 2개 이상의 컬럼을 묶어서 사용하는 다중 컬럼 Index의 동작 방식에 대해서 알아보도록 하겠다.
다중 컬럼 Index를 사용하는 이유
Index를 사용하는 이유는 당연하게도 데이터 조회의 성능 향상을 위함이다. 다중 컬럼 Index를 사용하는 이유 또한 데이터를 조회할 때, 빈번하게 같이 조회되는 컬럼들에 대해서 조회 성능을 향상시키기 위해서이다.
굳이! 다중 컬럼 Index를 사용하는 이유
성능 향상을 가져오는 것은 알겠지만, 각 컬럼에 Index를 거는 것과는 어떠한 차이가 있을까?
각각의 컬럼에 따로 인덱스가 걸려있다면, 어떤 인덱스를 먼저 사용하는 것이 더 빠른 것인지 DB에서 판단 후, 순서대로 인덱스를 적용하게 된다.
반면에 다중 컬럼 인덱스는 상위 인덱스 값에 대한 하위 인덱스 값을 같이 저장하고 있기 때문에 어떤 인덱스를 먼저 사용할지를 판단하는 과정이 없고, 상위 인덱스로 한번 필터링된 하위 인덱스에서 조건에 맞는 인덱스 값을 찾아낸다.
즉, 인덱스 성능을 비교하는 과정이 생략되고, 스캔해야하는 인덱스의 개수가 줄어든다. 이러한 이유 때문에 개별 인덱스보다 좀 더 나은 조회 성능을 제공할 수 있게 된다. (단, 올바른 순서대로 다중 컬럼 인덱스를 생성했을 때)
준비 작업 : 테이블 생성
CREATE TABLE `book` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`category` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
`name` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
`page` INT(10) UNSIGNED NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `category_name_INDEX` (`category`, `name`) USING BTREE
)본격적으로 다중 컬림 인덱스에 대해서 알아보기 전에 위와 같은 테이블을 갖고 설명하도록 하겠다.
책에 대한 정보를 관리하는 테이블이고, category와 name 순서로 인덱스가 걸려있다.
INSERT INTO `book` (`id`, `category`, `name`, `page`) VALUES (1, '만화', '책 이름1', 10);
INSERT INTO `book` (`id`, `category`, `name`, `page`) VALUES (2, '만화', '책 이름2', 11);
INSERT INTO `book` (`id`, `category`, `name`, `page`) VALUES (3, '소설', '책 이름3', 100);
INSERT INTO `book` (`id`, `category`, `name`, `page`) VALUES (4, '만화', '책 이름4', 200);
INSERT INTO `book` (`id`, `category`, `name`, `page`) VALUES (5, '소설', '소설책1', 1231);
INSERT INTO `book` (`id`, `category`, `name`, `page`) VALUES (6, '소설', '소설책2', 160);
INSERT INTO `book` (`id`, `category`, `name`, `page`) VALUES (7, '역사', '역사책1', 500);그리고 위 쿼리를 활용해서 샘플 데이터를 생성한 후, 본격적으로 다중 컬럼 인덱스의 동작에 대해서 알아보자.
🧐 다중 컬럼 Index 순서의 중요성
1. category, name 순서의 인덱스
SELECT *
FROM book
WHERE category='소설' AND name LIKE '소설%';book을 조회하는 이런 쿼리가 있다고 가정해보자.
SELECT문의 조건에 다중 컬럼 인덱스에 포함된 category와 name이 포함되어 있다. 그래서 위 쿼리를 실행하면 데이터를 스캔할 때, 다중 컬럼 인덱스가 사용된다.
쿼리의 실행계획을 조회해보면, 스캔할 행의 수는 2개, 필터된 행의 수는 100%로 찍혀있는 것을 볼 수 있다.
즉, category_name_INDEX 인덱스를 통해서 조건에 해당하는 데이터 2개를 찾았고, 이 2개의 데이터가 모두 검색 조건에 해당하는 데이터였다는 의미이다.
2. name, category 순서의 인덱스
이번에는 다중 컬럼 인덱스에서 컬럼의 순서를 category, name에서 name, category로 변경하고, 실행 계획을 조회해보자.
실행계획을 살펴보면, 스캔할 행의 수는 2개, 필터된 행의 수는 14.29%로 찍혀있는 것을 확인할 수 있다.
즉, category_name_INDEX 인덱스를 통해서 조건에 해당하는 데이터 2개를 스캔했고, 여기서 14.29%의 데이터가 필터링되고 남았다고 이해할 수 있다.
💡 실행 계획은 실제 수치와는 다를 수 있는데, 위에서 조회해본 실행 계획의 결과 값 또한 실제 값과는 좀 다르니,, 참고만 해보도록 하자. 🥲
3. 컬럼 순서가 주는 영향
다중 컬럼 인덱스가 걸려있을 때의 동작 방식에 대해서 결론부터 이야기하자면, 다중 컬럼 인덱스 내에 포함된 컬럼의 순서대로 인덱스 스캔을 하게 된다.
예를 들어 category, name의 순서대로 다중 컬럼 인덱스가 설정되어 있고, SELECT문의 조건에 두 컬럼 모두 포함되어 있다면, 조건에 해당하는 category 인덱스를 찾고 그 중에서, 조건에 해당하는 name 인덱스를 찾는다.
즉, 인덱스를 두 차례에 걸쳐서 필터링한다는 것이고, 그렇게 찾아진 인덱스를 활용해서 테이블을 스캔하게 된다. 이렇게 다중 컬럼 인덱스는 검색 성능에 큰 영향을 줄 수 있다.
그렇다면, 어떤 종류의 컬럼이 다중 컬럼 인덱스의 앞쪽에 오는 게 좋을까?
4. 순서를 정하는 방법
우리가 도서관에서 책을 찾는 과정에 비유를 하면 정답을 쉽게 유추 해볼 수 있다.
도서관에서 어떤 책을 찾을 때, 모든 섹션을 하나 하나를 뒤지면서 내가 찾고자 하는 책을 제목을 찾진 않는다.
보통은 책이 속한 카테고리가 어떤 섹션에 있는지 파악하고, 그 섹션의 몇 번 트레이에 내 책이 있을지 파악한다. 그러고나서 그 트레이를 쫙 훑어보면서 책을 찾곤 한다.
즉, 다중 컬럼 인덱스를 사용할 때는 상대적으로 대분류에 해당하는 컬럼이 앞쪽으로 오는 것이 바람직하다. 다시 말하면, 데이터의 중복도가 높은 컬럼이 앞쪽으로 오는 것이 바람직하다고 할 수 있다.
🧐 선행 컬럼의 동작
다중 컬럼 인덱스는 기본적으로 인덱스에 포함된 컬럼들이 검색 조건에 들어가야 사용이 된다.
하지만, 선행 컬럼 인덱스는 그 컬럼만 검색 조건에 들어가도 다중 컬럼 인덱스의 정보가 활용이 된다.
왜냐하면, 선행 컬럼의 경우 다른 컬럼에 의존적이지 않고, 선행 컬럼 인덱스만 놓고 보면 일반 인덱스와 동일한 정보를 갖고 동일한 역할을 하기 때문이다.
단, 선행 컬럼을 제외한 그 하위 컬럼들은 단일 인덱스와 같은 역할을 할 수 없다.
☕ 정리
다중 컬럼 인덱스에 대해서 최종적으로 정리해보자면 이렇게 정리할 수 있을 것 같다.
- 인덱스 컬럼의 순서는 매우 중요하다.
- 인덱스 컬럼의 순서는 데이터의 중복도가 높은 게 앞쪽으로 오는 것이 좋다.
- 선행 컬럼은 일반 인덱스처럼 활용할 수 있다.
