MySQL like 문 % 위치에 따라 index의 적용 여부

들어가기전..

MySQL 데이터베이스의 index의 구조는 Default가 B-tree 구조로 되어있다.

 

• 인덱스 타입 확인

show index from movies;

 

 

인덱스 정보를 조회해보면 pk와 index를 설정한 컬럼의 index type이 B-tree 구조로 되어있는 것을 확인할 수 있다.

 

B-tree 구조는 페이지나 블록으로 나누어 오름차순 정렬을 진행하고 각각의 페이지 첫번째 값을 루트 노드 페이지에 저장하여 루트 노드 부터 리프 노드까지 탐색하는 구조다.

값을 찾으면 탐색을 멈추는 Range Scan 의 특성을 가지고 수직적으로 먼저 탐색하고 수평적으로 탐색한다.

 

조회의 성능을 향상시킬 목적으로 index를 설정하는데 카디널리티가 낮은 컬럼을 사용하면 오히려 성능이 떨어지고 조회 명령이 아닌 insert나 delete, update 같은 쿼리를 날리면 페이지 분할로 인해 오버헤드가 발생 할 문제가 생긴다.

 

# 문제

조회 기능에서 와일드 카드 (%)의 위치에 따라 index 적용 여부가 바뀐다는 문제 발생

1. %A 를 했을 때 index를 타지 않는다.
2. %A% 를 했을 때 index를 타지 않는다.
3. A% 를 했을 때 index를 탄다.

 

---

 

# 적용

title index 설정

 

---

• %A index 적용 조회

explain select * from movies where movies.title like '%아바';

index를 타지 않는 것을 확인

---

• %A% index 적용 조회

explain select * from movies where movies.title like '%아바%';

index를 타지 않는 것을 확인

---

• A% index 적용 조회

explain select * from movies where movies.title like '아바%';

index를 타는 것을 확인

---

 

# 2만건의 데이터를 조회하여 성능 비교

@RequiredArgsConstructor
@Component
public class AppRunner implements ApplicationRunner {

    private final MovieRepository movieRepository;

    @Override
    public void run(ApplicationArguments args) throws Exception {
        for (int count = 0; count < 20000; count++) {
            Movie movie = Movie.builder()
                    .title(UUID.randomUUID().toString())
                    .releaseDate(LocalDate.of(2023, 12, 12))
                    .runningTime(Duration.ofMinutes(360))
                    .build();

            movieRepository.save(movie);
        }
    }
}

• 적용 쿼리

select * from movies where movies.title like '%3000';

성능

 

• 적용 쿼리

select * from movies where movies.title like '%3000%';

성능

 

 

• 적용 쿼리

select * from movies where movies.title like '3000%';

성능

 

 

%가 뒤에 오는 like 연산자가 index를 타면서 조회 성능이 우수하게 측정되었다.

 

왜? % 위치에 따라 index 적용이 달라질까?

위에서 index의 구조인 B-tree 구조를 생각해보자.

데이터를 오름차순 정렬 한 다음 페이지를 분할 하여 각각의 첫번 째 값을 루트 노드 페이지로 저장하게 된다.

그리고 탐색을 루트 노드 부터 시작하게 된다.

 

그러므로 %A 이면 A문자열로 끝나는 데이터를 찾아야 하기 때문에 index를 타지고 않고 full scan 방식으로 조회하게 되고 %A% 이여도 A가 포함 되는 데이터를 찾아야 하기 때문에 full scan 방식으로 조회를 하게 된다.

 

마지막으로 A%는 A로 시작하는 문자열을 찾기 때문에 루트 노드부터 탐색하여 index가 적용이 되어 성능에 도움이 된다.

 

 

 

db B-tree 인덱스 특성 reference

MySQL :: MySQL 8.0 Reference Manual :: 8.3.9 Comparison of B-Tree and Hash Indexes