[MySQL 튜닝] INSTR, LIKE, LOCATE, REGEXP 검색 속도 비교

 

 

ㅁ 개요

  MySQL의 문자열에서 특정문자열의 검색을 위해서 LIKE 함수를 많이 이용합니다. 상황에 따라서 INSTR이 더 빠른 경우도 있는데, 구체적으로 INSTR, LOCATE, LIKE, REGEXP들의 속도차이가 얼마인지,  다시 말해 상황에 따라 어떤 것이 더 효율적인지 분석한 내용을 정리하였습니다.

 

 

ㅁ 테스트 방법

  a-z의 문자열에서 처음, 중간, 끝의 문자열 검색 속도를 비교 하기 위해 테스트를 진행하였습니다.

[MySQL] 실습환경 구성하기에서 생성한 MySQL이며, 버젼은 5.7.37입니다.

### INSTR ###
# 16 s 529 ms
SELECT BENCHMARK(50000000,INSTR('abcdefghijklmnopqrstuvwxyz','abc'))

# 1 m 24 s 146 ms
SELECT BENCHMARK(50000000,INSTR('abcdefghijklmnopqrstuvwxyz','mno'))

# 2 m 26 s 420 ms
SELECT BENCHMARK(50000000,INSTR('abcdefghijklmnopqrstuvwxyz','xyz'))

### LOCATE ###
# 5 s 289 ms
SELECT BENCHMARK(50000000,LOCATE('abcdefghijklmnopqrstuvwxyz','abc'))

# 5 s 200 ms
SELECT BENCHMARK(50000000,LOCATE('abcdefghijklmnopqrstuvwxyz','mno'))

# 5 s 282 ms
SELECT BENCHMARK(50000000,LOCATE('abcdefghijklmnopqrstuvwxyz','xyz'))

### LIKE ###
# 22 s 579 ms
SELECT BENCHMARK(50000000,'abcdefghijklmnopqrstuvwxyz' LIKE 'abc%')

# 51 s 136 ms
SELECT BENCHMARK(50000000,'abcdefghijklmnopqrstuvwxyz' LIKE '%mno%')

# 1 m 8 s 679 ms
SELECT BENCHMARK(50000000,'abcdefghijklmnopqrstuvwxyz' LIKE '%xyz')

### REGEXP ###
# 34 s 370 ms
SELECT BENCHMARK(50000000,'abcdefghijklmnopqrstuvwxyz' REGEXP 'abc')

# 1 m 24 s 497 ms
SELECT BENCHMARK(50000000,'abcdefghijklmnopqrstuvwxyz' REGEXP 'mno')

# 2 m 12 s 733 ms
SELECT BENCHMARK(50000000,'abcdefghijklmnopqrstuvwxyz' REGEXP 'xyz')

 ㅇ 테스트 진행 시 LOCATE의 문법에 오류가 있다는 것을 발견하였습니다.

 

 

The first syntax returns the position of the first occurrence of substring substr 
in string str. The second syntax returns the position of the first occurrence of 
substring substr in string str:str, starting at position pos. 
Returns 0 if substr is not in str.

mysql> SELECT LOCATE('bar', 'foobarbar');
        -> 4
mysql> SELECT LOCATE('xbar', 'foobar');
        -> 0
mysql> SELECT LOCATE('bar', 'foobarbar', 5);
        -> 7
This function is multi-byte safe, 
and is case-sensitive only if at least one argument is a binary string.

 ㅇ LOCATE('검색 문자', '검색 대상')의 순서를 보정하여 다시 테스트를 진행하였습니다.

 

 

### LOCATE ###
# 16 s 718 ms
SELECT BENCHMARK(50000000,LOCATE('abc','abcdefghijklmnopqrstuvwxyz'))

# 1 m 24 s 300 ms
SELECT BENCHMARK(50000000,LOCATE('mno','abcdefghijklmnopqrstuvwxyz'))

# 2 m 26 s 483 ms
SELECT BENCHMARK(50000000,LOCATE('xyz','abcdefghijklmnopqrstuvwxyz'))

 

 

ㅁ 속도 지표 분석

속도 지표 분석	처음	중간	끝
INSTR	16.529	84.146	146.420
LOCATE	16.718	84.3	146.483
LIKE	22.579	51.136	68.679
REGEXP	34.370	84.497	132.733

 ㅇ 모든 함수가 문자열의 위치가 끝으로 갈 수록 속도가 늦어짐을 알 수 있었습니다.

 ㅇ 처음은 INSTR과 LOCATE는 비슷한 성능을 보여주었고, 문자열이 길어지면 속도가 늦어지는 특성이 있었습니다.

 ㅇ 문자열이 길어질 수록 LIKE가 포퍼먼스가 있었습니다.

 ㅇ REGEXP은 정규식을 이용한 이용한 복잡한 검색 조건을 사용할 수 있겠지만, 단순 문자열 색인에는 포퍼먼스가 떨어졌습니다.

 

 

ㅁ 함께 보면 좋은 사이트

ㅇ Which is faster — INSTR or LIKE?

ㅇ [DB] MySQL에서의 LIKE vs INSTR()