동적쿼리와 인덱싱 적용하여 성능 개선하기 (+ Like 검색은?)

구현할 기능

이번에 구현할 기능은 의사 다건 조회였다.

이때 검색 조건은 총 3가지였다.

1. 아무 조건없이 그냥 페이징만 적용하여 조회하기

2. 의사 전공별로 검색하기

3. 의사 이름으로 검색하기

3. 의사 이름 + 전공으로 검색하기

 

인덱스  고려 사항

• WHERE 에서 OR을 사용할 때는 or 연산자는 비교해야할 ROW가 더 늘어나기 때문에 풀 테이블 스캔이 발생할 확률이 높기에 걸어도 의미가 없을 수 있음
• 복합 인덱스는 카디널리티(중복도)가 높은순에서 낮은순으로 할 것
• 복합 인덱스에 사용되는 컬럼은 가급적 UPDATE가 안되는 값을 선정해야 함 -> 업데이트 되는 순간 인덱스를 무수히 다시 업데이트 하기 때문
• 인덱스는 가급적 테이블 당 3-5개를 넘지 않도록 하는 것이 좋음

 

첫 번째 고민 : 조건이 3가지나 있기 때문에 그럼 그 경우의 수 별로 인덱싱을 만드는 것이 맞는가?

결론은 맞다 이다! 검색 조건별로 각각 만들어야 한다

그래서 인덱스를 처음엔 여러개로 만들게 됐다

@Entity
@Table(
        name = "users",
        indexes = {
                @Index(name = "idx1", columnList = "isDeleted"),
                @Index(name = "idx2", columnList = "major, isDeleted")
                @Index(name = "idx3", columnList = "name, major, isDeleted")
                @Index(name = "idx3", columnList = "name, isDeleted")
        }
)

 

중복도가 낮은 순에서 높은 순서대로 걸러야 하기 때문에 위와 같이 인덱스를 생성했다!

또한 의사 정보도 우리 프로젝트에서는 소프트 딜리트를 사용하기로 약속했기 때문에 삭제 여부도 인덱스에 같이 걸어줬다

 

Like 검색 인덱스 안 걸림

그런데 문제는 name(Like 검색임)이 들어있는 복합 인덱스는 jpa단에서 아예 생성이 안 되는 것이다

-> 하지만 name은 결국 Like 검색이기 때문에 결국 인덱스가 생성이 되어도 (물론 나의 경우 jpa가 자동으로 생성해주지 않았지만) 이걸 탈 수가 없다는 것이다

그렇기에 이러한 문제를 해결하기 위해서는 Elasticsearch나 mysql ngram을 사용해야 한다는 결론에 도달했다

 

그렇다면 아직 Elasticsearch를 도입하지 않았기 때문에 먼저 인덱스는 아래와 같이 구성했다

@Entity
@Table(name = "users",
        indexes = { @Index(name = "idx_is_deleted", columnList = "isDeleted"),
                    @Index(name = "idx_major_is_deleted", columnList = "major, isDeleted") }
)

 

또한 인덱스와 where절의 순서는 중요하기 때문에 (일치해야 걸림)

현재 인덱스가 의미없는 name는 where절에서 마지막으로 지정하고 작성했다

 

그렇다면 이제 인덱스 걸기 전과 후 결과를 측정해보자!

 

의사 다건 조회 성능 인덱스 성능 측정

조건

60초 동안 1000번의 요청을 2번 반복

데이터는 10만개, 의사 전공(카테고리)는 4개

 

 

의사 다건조회시 카테고리 조건가지고 검색하는 경우

 

적용된 인덱스

@Index(name = "idx2", columnList = "major, isDeleted")

 

인덱스 안 건 경우

 

인덱스 건 경우

 

의사 다건조회시 이름으로(Like) 검색하는 경우

 

=> 추후 Elasticsearch를 도입하여 적용할 예정

-> 인덱스를 걸어서 오히려 더 느려진 경우도 있었다 

 

 

의사 다건조회시 카테고리 + 이름 조건가지고 검색하는 경우

 

인덱스 안 건 경우

 

인덱스 건 경우

 

의사 다건조회 하는 경우 (아무런 조건 없이 페이징만 적용)

 

인덱스 안 건 경우

 

인덱스 건 경우

 

 

결론

• 검색 조회 조건이 여러 개라면 그 조건마다 인덱스를 거는 것이 맞음 -> 하지만 테이블 당 인덱스가 5개 초과하면 오히려 성능이 떨어지고 자주 업데이트 되는 값이면 안 됨
• 복합 인덱스를 걸 때는 중복성이 낮은 컬럼 순으로 걸어야 함
• like검색의 경우에는 인덱스가 걸려도 full scan을 해야 하기 때문에 조회 성능 개선을 위해서는 Elasticsearch나 mysql ngram이 필요함