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