Index를 이용하여 조회 쿼리 1374% 성능 향상기 (with MySQL)

1. 성능 최적화 계기

현재 프로젝트는 예약과 관련하여 시간을 많이 다루고 있다.
따라서, DATETIME 타입의 컬럼을 범위 비교 또는 동등 비교하여 조회하는 쿼리가 많이 존재하였다.
해당 컬럼에 대해 인덱스를 지정하여 성능 최적화를 할 수 있을것 같아 Real MySQL 8.0의 도움을 받아 시도해보았고 기간별 예약 내역 조회 쿼리에 대해 1374% 향상을 보게 되었다.

2. 문제의 쿼리

문제의 아래 쿼리는 기간별로 예약 내역을 조회하는 쿼리이다.
where절에서 DATETIME 타입의 예약 시작날짜시간에 해당하는 start_at 컬럼에 대해 BETWEEN 연산을 진행한다.
나는 start_at 컬럼에 대해 Index를 적용해보고자 했다.

select
    reservatio0_.project_table_id,
    reservatio0_.start_at,
    reservatio0_.end_at,
    tableretur1_.returned_at,
    reservatio0_.reservation_status
from reservation reservatio0_ 
left outer join table_return tableretur1_ 
        on reservatio0_.reservation_id=tableretur1_.reservation_id 
where (reservatio0_.start_at between '2023-01-01 08:00:00' and '2023-01-02 08:00:00') 
    and (reservatio0_.reservation_status not in  ('CANCELED' , 'UN_USED' , 'RETURNED')) 
    and (reservatio0_.project_table_id in (
            select projecttab2_.table_id 
            from project_table projecttab2_ 
            where projecttab2_.project_room_id=1
    )
);

 

3. 인덱스를 사용하는 이유와 선택한 기준

인덱스를 사용하는 이유는 Table에 데이터를 지속적으로 저장하게 되면 내부적으로 순서 없이 쌓이게 된다.
🥵 이 경우에 특정 조건을 만족하는 데이터를 찾고자 WHERE절을 사용한다면 Table의 레코드를 처음부터 끝까지 접근하여 검색조건과 일치하는지 비교하는 과정이 필요하다. 이를 Full Table Scan이라고 표현하고 효율이 많이 떨어진다.

 하지만, 😄 특정 column에 대한 Index를 생성해 놓은 경우 해당 속성에 대하여 search-key가 정렬되어 (search-key, pointer)를 별도로 저장하기 때문에 조건 검색(SELECT-WHERE) 속도가 굉장히 빨라지기 때문에 인덱스를 사용한다. 

위의 말만 들으면 인덱스는 많을 수록 좋다고 착각할 수 있다. 인덱스는 무조건 많이 생성한다고 해서 검색 속도를 향상시켜주지는 않는다.
이유는 크게 두 가지가 있다.

• (단점1) 추가저장 공간이 필요
  • index를 생성하면 index 자료구조를 위한 저장 공간이 추가적으로 필요하다.
  • 보통 테이블의 크기의 10% 정도의 공간을 차지한다.
• (단점2) 느린 데이터 변경 작업
  • 검색이 아닌 데이터 변경을 할 때, 즉 INSERT, UPDATE, DELETE가 자주 발생하면 성능이 나빠질 수 있다.
  • B+ Tree 구조의 index는 데이터가 추가될 때마다 tree 구조가 변경될 수 있기 때문이다.

나는 아래의 네가지 기준을 통해 start_at 컬럼을 인덱스로 선택하게 되었다.

 

1. 카디널리티 (Cardinality)

카디널리티는 컬럼에 사용되는 값의 다양성 정도를 의미한다. 값의 중복이 적을수록 카디널리티는 높다고 볼 수 있다.
즉, 카디널리티가 높을 수록 인덱스 설정에 좋은 컬럼이다.
✅ start_at 컬럼은 '2023-08-01 08:00:00' 과 같은 DATETIME값으로 저장되고 중복되는 경우가 적으므로 카디널리티가 높다.

2.  선택도 (Selectivity)

데이터에서 특정 값을 얼마나 잘 선택할 수 있는지에 대한 지표이다.
선택도는 낮을수록 인덱스 설정에 좋은 컬럼이다.
선택도 = (컬럼의 특정 값의 레코드 수 / 테이블의 총 레코드 수) * 100

선택도는 5~10%가 적당하다.
✅ start_at 컬럼은 만약 해당 테이블에 전체 레코드가 1000개이고 start_at 컬럼의 값이 중복될 수 있는 최대 경우의 수는 회의공간의 수와 일치하기 때문에  최악의 경우에도 2%를 넘지 않는다.

3.  활용도 

해당 컬럼이 실제 작업에서 얼마나 활용되는지에 대한 지표이다.
활용도가 높을 수록 인덱스 설정에 좋은 컬럼이다.

✅ start_at 컬럼은 WHERE 조건에 자주 사용되는 컬럼으로 활용도가 높은 컬럼이다.

4.  수정빈도

해당 컬럼을 수정하는 빈도가 낮을수록 적합하다. 
✅ start_at 컬럼은 사용자는 예약 시간을 수정할 수 없으므로 수정빈도보다 조회빈도가 훨씬 월등히 많다. 수정가능하더라도 트레이드오프를 생각했을 때 이득이라고 생각한다.

 

4. 인덱스 사용 전 후 실행 계획 비교

reservation 테이블의 전체 레코드 수는 351,360개이다. 그리고 검색 대상 레코드 수는 553개이다.

1. 인덱스 사용 전 실행계획

두번째 줄에, reservation 테이블은
- select_type: WHERE절에서 서브쿼리를 가지므로 PRIMARY이다.

- type : 우선 3개의 행이 모두 id가 같으므로 조인이 되어 실행된다는 것은 알 수 있다. 나는 서브쿼리를 작성했지만 MySQL 8.0에서는 세미조인 최적화를 통해 조인으로 변형된다는 것을 여기서도 확인할 수 있다. ref가 사용되었으며 조인에 유니크하지 않은 FK를 이용하며 반환되는 레코드가 반드시 1건이라는 보장이 없지만, 그래도 동등한 조건으로만 비교되므로 빠른 레코드 조회 방법 중 하나이다.

- ref: 값 비교에 사용된 값은 project_table 테이블의 table_id 컬럼이다.

- rows: MySQL 옵티마이저가 해당 부분을 처리하는데 읽어야 할 레코드의 수로 예측한 것이 14650개이다. 해당 rows 개수는 반환하는 레코드의 예측치가 아니라 쿼리를 처리하기 위해 얼마나 많은 레코드를 읽고 체크해야 하는지를 나타내므로 정확하지 않을 수 있다. 

2. 인덱스 사용 후 실행계획

- select_type: WHERE절에서 서브쿼리를 가지므로 PRIMARY이다.

- type : 인덱스 레인지 스캔 형태의 접근 방법이 사용되었다는 것을 range 값을 통해 알 수 있다. BETWEEN 연산으로 범위 검색을 하였을 때 사용된다. 우선순위가 낮긴하지만, range 접근 방법도 상당히 빠르며, 모든 쿼리가 이 접근 방법만 사용해도 최적의 성능이 보장된다.

- key_len : DATETIME 포맷을 이용하였고, 단일 컬럼 인덱스이므로 8바이트의 키 길이를 사용했음을 알 수 있다.

- rows: 1151개로 WHERE절에서 인덱스로 설정된 start_at 컬럼의 비교가 먼저 실행되어 비교 해야할 레코드가 확 줄었다고 판단된다.

 

5. 성능 비교

쿼리의 실행 시간 측정은 SHOW profiles; 명령을 통해 확인하였다.

✅ 환경: reservation 테이블의 전체 레코드 수는 351,360개이다. 그리고 검색 대상 레코드 수는 553개이다.

 

10회 쿼리 성능을 측정하였을 때 start_at에 대해
인덱스 미적용하였을 때는 평균 174ms
인덱스를 적용하였을 때는  평균 11.8ms

 

해당 검색 쿼리의 성능이 1374% 향상되었음을 볼 수 있었다.

 

이슈

인덱스가 설정되어 있음에도 풀 테이블 스캔이 되는 현상

start_at에 대하여 BETWEEN 연산을 넓은 범위 즉, 많은 데이터에 대해서 진행하였을 때 인덱스가 설정되어 있음에도, 풀 테이블 스캔을 하는 문제가 있었다.

 

우선 전체 데이터 레코드 수는 351,360이고 실행계획을 살펴보았을 때, rows가 1151개일 때까지는 인덱스 레인지 스캔으로 동작하고,
 그 이상으로 늘어날 때는 풀 테이블 스캔을 하는 것이었다.

 

해당 문제에 대해 찾아보다가 Real MySQL 8.0 책에서도 비슷한 사례를 발견했는데, 정확한 해답을 찾지는 못했지만
MySQL 옵티마이저가 최적화를 진행하면서 상황에 따라 인덱스 레인지 스캔이 별로 효율적이지 못하다고 판단하였을 때 풀 테이블 스캔을 진행하는 것 같았다.