인덱스를 고려한 쿼리 개선

문제

모니터링 시스템에 2.2초대의 지연 시간이 발생하는 api 콜이 빈번하게 발생했다. 내가 배포했던 서비스라서 지연시간을 정상 범위로 바꾸고자 하였다.

원인

실무에서 4개의 테이블을 조인하여 데이터를 가지고 오는 쿼리문을 작성하였다. MyBatis와 Oracle DB를 사용하고 있었다.

의심이 가는 부분이 세 가지 있었다.
1. 자바 단에서 중첩 for문으로 인한 지연
2. MyBatis 쿼리에서 merge into 사용으로 인한 지연
3. 인덱스를 잘 타지 않아 발생하는 지연

해결

원인은 3번이었다.
처음에는 1번이나 2번이 문제인줄 알았다. merge into 를 select 하여 체크하고 insert 혹은 update 하도록 바꾸어서 성능 테스트를 해보았다. 시간이 단축 되었지만 크게 차이가 없었다.

테이블을 확인해보니 메타에 맞게 필요한 인덱스가 생성되어 있지 않은 것을 발견했다.

인덱스를 생성하고 반영하니 2.2초대의 지연시간이 0.2~0.3초대로 줄었다.

이미 운영에 반영된 테이블이다보니 새벽 시간대에 인프라 팀에서 작업을 진행했다.

테이블 생성 시 인덱스 생성의 중요성을 알게되었다.

인덱스 생성 기준은 다음과 같다.

1. 자주 사용되는 컬럼
2. join, group by, order by 에 사용되는 컬럼
3. 유니크한 값, 외래 키
   값이 다양할수록 인덱스의 효과가 좋다.

하지만 CRUD 중 CUD 작업을 할 때에는 느려지기 때문에 인덱스를 남발하면 안된다.

대부분의 데이터베이스는 B-Tree 인덱스를 사용한다.
B-Tree 인덱스는 데이터를 키-값 쌍으로 저장하는 데이터 구조이다.

인덱스를 타지 않는 경우는 다음과 같다.

1. not 또는 in 연산자

   1. not 연산자의 경우: not 조건에 맞지 않는 데이터가 보편적으로 훨씬 많을 것이다. 이렇게 판단되면 인덱스를 타지 않는다.

      select * from user where not name='hyy';

   2. in 연산자의 경우: in 절 안에 많은 데이터가 들어가 다른 데이터는 거의 없을 경우 인덱스를 타지 않고 풀스캔을 한다.

      select * from user where name in ('aaa', 'bbb', 'ccc', 'ddd');

   3. not in 연산자의 경우: a와 마찬가지로 조건에 맞지 않는 데이터가 보편적으로 훨씬 많을 것이다. 이런 경우에도 인덱스를 타지 않는다.

      select * from user where name not in ('aaa','bbb');

2. 인덱스 컬럼의 변형

   select * from user where substr(name,2) = 'hy';

3. 와일드카드 % 사용

   1. 다음처럼 문자열 앞쪽에 와일드카드를 사용할 경우 정렬해 둔 것을 사용할 수 없으므로 인덱스를 타지 않는다.

4. 데이터의 변경이 빈번한 경우
   인덱스는 데이터 변경시 마다 업데이트 되어야 한다. 변경이 빈번한 컬럼에 인덱스를 생성하면 성능이 떨어질 수 있다.

5. 대량의 범위 검색이 필요한 경우
   인덱스는 B-Tree 구조로 데이터를 정렬하여 저장하기 때문에 대량의 범위 검색이 필요한 경우 인덱스를 타는 것보다 전체 데이터를 풀스캔 하는 것이 더 효율적일 수 있다.