서론
이번에 엑셀 기능을 구현하면서, Full-Scan을 해야하는 쿼리들이 상당히 많았다.
이러한 Full-Scan을 해야하는 쿼리들의 특징은 주로 긴 문자열이나 날짜 데이터들이었고 이로 인해서 질의 시간이 길어지는 것을 찾았다.
그래서 조회시의 성능을 극대화하기 위해서 인덱스를 도입하여보려고 한다.
해당 글은 인덱스에 대해서 이미 알고 있다는 전제 하에서 작성되었다.
인덱스에 대해서 잘 모른다면 해당 글을 참고하는 걸 추천한다.
DB 인덱스를 사용함으써 데이터를 조회할 때 소모되는 메모리를 효율적으로 사용할 수 있게 된다.
단, 인덱스가 잘 설정되어 있는 경우에 한한다.
인덱스는 언제, 어디에 걸어야 할까?
- WHERE 절에 쓰이는 컬럼
인덱스는 WHERE 절에 사용할 컬럼에 대한 효율화라고 볼 수 있다.
예를 들어서 인덱스가 “메뉴”라는 필드에 걸려있다고 해보자
- SELECT
‘메뉴’FROM ‘식당’; - SELECT * FROM ‘식당’ WHERE
‘메뉴’= ‘김치찌개’;
위 둘의 쿼리중 1번은 인덱스가 영향을 아예 줄 수 없다.
그러나 2번 쿼리의 경우에는 조회시 인덱스가 영향을 줄 수 있다.
즉 인덱스는 WHERE 절에 사용되는 컬럼에 사용할 때 성능을 향상 시킬 수 있다고 할 수 있다.
- 카디널리티가 높은 컬럼
카디널리티가 높은 컬럼이란 해당 컬럼이 갖고 있는 값의 중복 정도가 낮은 컬럼을 의미한다.
아래 사진을 1번 컬럼이라고 하자.
1번 컬럼은 중복되는 데이터의 수가 굉장히 많음을 볼 수 있다.이는 카디널리티가 굉장히 낮다고 할 수 있다.
이러한 컬럼은 인덱스를 걸기 올바른 컬럼은 아니다.
아래 사진을 2번 컬럼이라고 하자.
2번 컬럼은 볼 수 있듯이 중복도가 굉장히 낮으며, 카디널리티가 높다고 할 수 있다.
2번 같이 카디널리티가 높은 컬럼이 인덱스를 걸기 좋은 컬럼이다.
- 선택도가 낮은 컬럼
선택도라 함은 데이터에서 특정 값을 얼마나 잘 선택할 수 있는지에 대한 지표이다.
선택도를 계산하는 수식은 아래와 같다.
= 컬럼의 특정 값의 row 수 / 테이블의 총 row 수 * 100
= 컬럼의 값들의 평균 row 수 / 테이블의 총 row 수 * 100
이를 예시를 들어보면서 다시 이야기 해보자.
10개의 row가 있을 때 ‘식당’ 테이블에 ‘메뉴’, ‘가격’, ‘위치’ 3개의 컬럼이 있다고 해보자.
이 때 아래의 쿼리들이 있다.
- SELECT COUNT(1) FROM ‘식당’ WHERE ‘가격’ = 3000;
→ Count가 3이 출력되었다.
이는 선택도가 3/10*100 = 30%이다.
- SELECT COUNT(1) FROM ‘식당’ WHERE ‘메뉴’ = “김치찌개”;
→ Count가 1이 출력되었다.
이는 선택도가 1/10*100 = 10%이다.
보통 5~ 10% 정도의 선택도를 가진 컬럼이 인덱스를 걸기 좋은 컬럼이라 할 수 있다.
이는 중복도가 낮은 컬럼이 인덱스를 걸기 좋은 컬럼이라는 것과 어느정도 일맥상통한다고 할 수 있을 것 같다.
- update가 자주 일어나지 않는 컬럼
인덱스 또한 별도의 자료구조로 관리되어야 하기 때문에, INSERT 및 UPDATE시마다 인덱스 테이블 또한 갱신을 해주어야 한다.
이러한 UPDATE가 잦다면 이덱스 테이블 또한 자주 갱신을 해주어야 할 것이고, 이는 DB에 부하를 줄 수 있다.
그러면 나는 인덱스를 어디 걸었는가?
아래 쿼리는 내가 실제로 작성했던 쿼리를 조금 각색시켰다.
SELECT *
FROM EXAMPLE
WHERE model_name LIKE "%modelName%"
AND opening_date >= 2024-01-29 00:00:00
AND delivery_date < 2024-02-01 00:00:00;위 쿼리에서는 3개의 컬럼이 존재한다.
model_name, opening_date, delivery_date
글 위에서부터 이야기했던 조건을 토대로 인덱스를 걸만한 컬럼인지를 판단해보자.
model_name 컬럼 같은 경우에는 해당 쿼리 외에도 다양하게 WHERE 절에 등장을 하였으며, DB 내에서 중복도가 낮아서 카디널리티가 높다.
이에 따라서 선택도도 굉장히 낮으며, UPDATE 또한 자주 일어나지 않는다.
이러한 특성을 통해서 model_name 컬럼은 인덱스를 걸기 적합하다 판단하였다.
그러나 opening_date, delivery_date 컬럼 같은 경우에는 WHERE 절에 자주 등장하기는 하지만 DB 내에서 중복도가 낮은 편은 아니라 카디널리티가 높지 않았다.
이에 따라 선택도도 상대적으로 높은 편이지만, UPDATE는 일어나지 않는다.
여기서 하나 더 중요한 것은 model_name 컬럼 같은 경우에는 쿼리에서 WHERE절에 독단적으로 쓰이는 경우가 있다. 그러나 opening_date, delivery_date 컬럼은 반드시 같이 WHERE 절에서 쓰인다.
이러한 특성들을 통해서 opening_date, delivery_date 컬럼은 다중 컬럼 인덱스로 걸기로 하였다.
다중 컬럼 인덱스란 2개 이상의 필드를 조합해서 생성한 INDEX이며, 1번째 조건과 2번째 조건을 함께 INDEX 해서 사용한다.
이 때 MYSQL은 최대 15개 컬럼으로 구성 가능하다 한다.
다중 컬럼 인덱스는 단일 컬럼 인덱스보다 더 비효율적으로 INDEX/UPDATE/DELETE를 수행하기에 보다 신중해야한다.
그래서 model_name 컬럼은 단일 인덱스로 opening_date, delivery_date 는 다중 컬럼 인덱스로 걸기로 하였다.
CREATE INDEX idx_model_name ON EXAMPLE(model_name);CREATE INDEX idx_opening_delivery_date ON EXAMPLE(opening_date, delivery_date);아래는 인덱스를 걸기전 쿼리 질의에 걸린 시간이다.
아래는 인덱스를 건 후 쿼리 질의에 걸린 시간이다.
약 4~5배 정도의 성능 향상이 있었음을 알 수 있다.
