MySQL LIKE % 위치에 따른 인덱스 사용 여부

서론

Table 내 어떠한 칼럼에 index를 설정하여 Like 문을 통해 검색하는 경우

INDEX 설정을 하였어도 % 위치에 따라 INDEX 가 정상적으로 작동하는 경우가 있지만 반대로 잘못 사용한 경우 Full Scan 이 발생할 수 있다.

이번글에서는 LIKE 검색에서 % 위치에 따른 INDEX 사용 여부를 하나씩 살펴볼 것이다.

 

Table 구조

간단한 Member Table이며 더미 데이터수는 1만 개로 설정하였다.

CREATE TABLE `Member` (
  `id` int(9) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `address` varchar(255) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8;;

INDEX 칼럼

CREATE INDEX MEMBER_NAME ON Member (name);

---

1. 'ABC%' 데이터 시작 형식을 검색하는 경우

name like 'AB%' 검색인 경우 AB로 시작하는 데이터들을 조회하고자 하는 것이다.

해당 조회 같은 경우에는 인덱스가 잘 적용되는 것을 확인할 수 있다.

 

2. '% ABC' 끝나는 데이터 형식을 검색하는 경우

name like '%AB' 검색인 경우 AB로 끝나는 데이터들을 조회하고자 하는 것이다.

해당 조회 같은 경우에는 인덱스가 적용되지 않는다.

 

3. '%ABC%' 데이터에 포함하는지 검색하는 경우

name like '%AB%' 검색인 경우 AB가 포함된 데이터들을 조회하고자 하는 것이다.

해당 조회 같은 경우에는 인덱스가 적용되지 않는다.

---

왜? AB% 를 제외한 나머지 방식은 인덱스를 타지 않을까?

데이터베이스의 인덱스의 자료구조는 대부분 B-TREE 구조로 이루어져 있다.

(B-TREE 은 노드의 자식 노드의 데이터들은 노드 데이터 기준으로 데이터보다 작은 값이 왼쪽부터 오른쪽으로 정렬되어 있다.)

이러한 구조를 가지고 있다고 생각하고 왜? % 위치에 따라 인덱스 조건이 타지 않는가? 에 대해 다시 생각해보면 당연히 타지 않을 거라고 생각했어야 했다. 😭

하지만 이제라도 공부하여 잊지않으면 괜찮지 않을까요? 🙏

 

 

---

그럼 어떻게 검색해야 하는가?

MySQL 5.7 버전 이상부터 FullText Search(전문 검색) 방식을 사용하여 앞서 살펴본

% ABC%, % ABC 검색을 사용했을 때 인덱스를 사용하지 못한 부분을

FullText Search 을 사용한다면 빠른 검색이 가능하다고 합니다.

해당 본문에서는 FullText Search 대해서 설명하지는 않겠습니다.

추 후 FullText Search 사용방법과 어떻게 동작하는지에 대해 공부해서 포스팅하도록 하겠습니다.

 

참고자료

https://dev.mysql.com/doc/refman/8.0/en/index-btree-hash.html

https://hoing.io/archives/16853

https://stackoverflow.com/questions/2042269/how-to-speed-up-select-like-queries-in-mysql-on-multiple-columns