Mysql의 LIKE 연산자 와 full-scan
Mysql의 LIKE 연산자는 문자열을 비교하는 데 사용된다.
패턴 매칭을 통해 특정 문자열이 포함된 레코드를 검색하며, % 와 _를 사용한다.
- % : 0개 이상의 문자와 일치
- _ : 정확히 1개의 문자와 일치
1
2
3
SELECT * FROM users WHERE username LIKE 'john%'; -- 'john'으로 시작하는 모든 사용자 이름 검색
SELECT * FROM users WHERE username LIKE '%doe'; -- 'doe'로 끝나는 모든 사용자 이름 검색
SELECT * FROM users WHERE username LIKE '_o_'; -- 두 번째 문자가 'o'인 모든 사용자 이름 검색
문제
LIKE 연산자는 와일드 카드를 사용하여 패턴 매칭을 한다. 와일드 카드가 패턴의 시작 부분에 위치하면 Mysql의 인덱스를 효과적으로 사용할 수 없습니다.
LIKE 연산자가 와일드카드 패턴을 맨 앞에 포함하게 되면 인덱스를 사용할 수 없다. 예를 들어 LIKE ‘%123’ 과 같은 패턴으로 사용하는 경우 인덱스를 사용할 수 없어, 모든 레코드를 스캔하는 Full Table Scan이 발생한다.
Full Scan
데이터베이스가 테이블의 모든 행을 읽으면서 검색조건에 맞는 데이터를 찾는 방법으로, 조건이 인덱스와 매칭되지 않거나 인덱스가 없는 컬럼에 대해 쿼리가 수행될 때 발생한다.
테이블의 모든 레코드를 읽기 때문에, 데이터베이스의 크기가 클수록 성능이 떨어진다. 평균적으로 O(n)의 시간 복잡도를 갖는다.
테이블의 모든 행을 메모리에서 읽어야 하므로, 메모리와 I/O 비용이 높다.
왜 ‘%abc’ 패턴은 인덱스를 사용할 수 없을까?
인덱스는 일반적으로 B-트리와 같은 데이터 구조를 사용하여, 정렬된 상태로 데이터를 저장한다. 이를 통해서 데이터베이스는 특정 값을 빠르게 찾을 수 있다. 인덱스는 일반적으로 왼쪽에서 오른쪽으로 순차적인 비교를 수행하여 일치하는 값을 찾기 때문에, 값을 찾는 시작점을 명확하게 알고 있어야 한다.
‘%abc’ 패턴은 문자열의 첫 부분에 어떤 문자가 올 지 모르기 때문에, 데이터베이스는 인덱스를 사용하여 값을 찾기가 어렵다. 따라서 테이블의 모든 레코드를 스캔해야 하기 때문에 전체 테이블 스캔이 발생한다.
반면 ‘abc%’와 같은 패턴은 문자열이 ‘abc’로 시작하는 값을 찾기 때문에 인덱스를 활용할 수 있어 훨씬 빠르게 검색할 수 있다.
결론적올, %abc와 같은 패턴은 인덱스의 특성과 데이터 검색 방식 때문에 성능이 떨어지며, 인덱스를 효과적으로 활용하기 위해서는 가능한 와일드카드를 오른쪽에 두는 것이 좋다.
확인
= 으로 완전일치 검색하는 경우
인덱스를 사용하여 검색하는 것을 확인할 수 있다.
LIKE 연산자를 이용해서 검색하는 경우
% 으로 시작하는 문자열을 검색하는 경우
인덱스를 사용하지 않는 것을 확인할 수 있다.
% 으로 끝나는 문자열을 검색하는 경우
인덱스를 사용하는 것을 확인할 수 있다.
이제 속도 측면에서 확인해보자
= 을 사용한 경우
LIKE 연산자를 사용한 경우
% 으로 시작하는 문자열을 검색하는 경우
% 으로 끝나는 문자열을 검색하는 경우
% 가 앞에 나오는 경우, 속도가 떨어지는 것을 볼 수 있다.