[MYSQL] LIKE vs INSTR()

 http://linuxchannel.net/board/read.php?table=alpha&no=73




안녕하세요?

좀더 다양한 테스트를 해보질 않아서
단정짓기는 어렵지만 우선 테스트한 결과(단순 막노동?)를 올립니다.

column 길이가 극히 짧은 것만 테스트한 경우이므로
다소 틀릴 수 도 있습니다.

- 8 bytes 이하      <--- 본 테스트 포함
- 9 ~ 16 bytes      <--- 본 테스트 포함
- 17 ~ 32 bytes
- 33 ~ 64 bytes      <--- 본 테스트 포함(일부)
- 65 ~ 128 bytes
- 129 ~ 256 bytes
- 257 ~ 512 bytes
- 513 - 1024 bytes  <-- 보통 게시물 평균 크기
- 1025 - 2048 bytes
- 2049 - 4096 bytes
- 4097 bytes 이상

이와 같이 여러가지 경우에 대해서 좀더 테스트해 봐야 합니다.

혹시 아래와 비슷한 방법으로 테스트해 주실분 있나요?

--------------------------------

[MYSQL] LIKE vs INSTR()

0. 배경

1. 영문 검색어 테스트
  1-1. 앞 부분 검색
  1-2. 중간 부분 검색
  1-3. 끝 부분 검색

2. 한글 검색어 테스트
  2-1. 앞 부분 검색
  2-2. 중간 부분 검색
  2-3. 끝 부분 검색

3. 결과 비교(표)
  3-1. 영문 검색어 결과
  3-2. 한글 검색어 결과

4. 결론

5. 후기

---------------------------------------

0. 배경

TRUE 인 경우만 테스트한 경우임.

... cols LIKE '%한글검색어%'
... BINARY cols LIKE '%한글검색어%'

웹 게시판에서, 후자의 경우 속도가 약 3 배, 또는 그 이상 빠름
(평균 레코드가 길이가 1KB,  text 자료형에서 검색)

마찬가지로,

... cols LIKE '%숫자형조합%'
... BINARY cols LIKE '%숫자형조합%'

숫자의 검색도 BINARY 로 검색할 경우 빠름.
(정수형만 테스트해 보았음)



1. 영문 검색어 테스트

1-1. 앞 부분 검색

1) 대소문자 구별시

  DO BENCHMARK(1000000, BINARY 'MSIEdddddd' LIKE 'MSIE%');
  0.47

  DO BENCHMARK(1000000, INSTR('MSIEdddddd','MSIE'));
  0.27


2) 대소문자 구별없이

  DO BENCHMARK(1000000, 'MSIEdddddd' LIKE 'MSIE%');
  0.66

  DO BENCHMARK(1000000, INSTR(LOWER('MSIEdddddd'),LOWER('MSIE')));
  1.86


1-2. 중간 부분 검색

1) 대소문자 구별시

  DO BENCHMARK(1000000, BINARY 'dddMSIEdddddd' LIKE '%MSIE%');
  0.60

  DO BENCHMARK(1000000, INSTR('dddMSIEdddddd','MSIE'));
  0.44

2) 대소문자 구별없이

  DO BENCHMARK(1000000, 'dddMSIEdddddd' LIKE '%MSIE%');
  1.15

  DO BENCHMARK(1000000, INSTR(LOWER('dddMSIEdddddd'),LOWER('MSIE')));
  2.15


1-3. 끝 부분 검색

1) 대소문자 구별시

  DO BENCHMARK(1000000, BINARY 'dddMSIE' LIKE '%MSIE');
  0.56

  DO BENCHMARK(1000000, INSTR('dddMSIE','MSIE'));
  0.43

2) 대소문자 구별없이

  DO BENCHMARK(1000000, 'dddMSIE' LIKE '%MSIE');
  1.22

  DO BENCHMARK(1000000, INSTR(LOWER('dddMSIE'),LOWER('MSIE')));
  1.77


2. 한글 검색어 테스트

2-1. 앞부분 검색

1) 대소문자 구별시

  DO BENCHMARK(10000000, BINARY '한글 테스트' LIKE '한글%');
  Query OK, 0 rows affected (4.70 sec)

  DO BENCHMARK(10000000, INSTR('한글 테스트','한글'));
  Query OK, 0 rows affected (2.73 sec)


2) 대소문자 구별없이

  DO BENCHMARK(10000000, '한글 테스트' LIKE '한글%');
  Query OK, 0 rows affected (6.60 sec)

  DO BENCHMARK(10000000, INSTR('한글 테스트',LOWER('한글')));
  Query OK, 0 rows affected (7.48 sec)

  DO BENCHMARK(10000000, INSTR(LOWER('한글 테스트'),'한글'));
  Query OK, 0 rows affected (12.23 sec)

  DO BENCHMARK(10000000, INSTR(LOWER('한글 테스트'),LOWER('한글')));
  Query OK, 0 rows affected (17.25 sec)


2-2. 중간 부분 검색

1) 대소문자 구별시

  DO BENCHMARK(10000000, BINARY '테스트 한글 테스트' LIKE '%한글%');
  Query OK, 0 rows affected (6.63 sec)

  DO BENCHMARK(10000000, INSTR('테스트 한글 테스트','한글'));
  Query OK, 0 rows affected (5.52 sec)


2) 대소문자 구별없이

  DO BENCHMARK(10000000, '테스트 한글 테스트' LIKE '%한글%');
  Query OK, 0 rows affected (19.50 sec)

  DO BENCHMARK(10000000, INSTR('테스트 한글 테스트',LOWER('한글')));
  Query OK, 0 rows affected (10.60 sec)

  DO BENCHMARK(10000000, INSTR(LOWER('테스트 한글 테스트'),'한글'));
  Query OK, 0 rows affected (18.39 sec)

  DO BENCHMARK(10000000, INSTR(LOWER('테스트 한글 테스트'),LOWER('한글')));
  Query OK, 0 rows affected (23.25 sec)


2-3. 끝 부분 검색

1) 대소문자 구별시

  DO BENCHMARK(10000000, BINARY '테스트 한글' LIKE '%한글');
  Query OK, 0 rows affected (6.40 sec)

  DO BENCHMARK(10000000, INSTR('테스트 한글','한글'));
  Query OK, 0 rows affected (5.51 sec)


2) 대소문자 구별없이

  DO BENCHMARK(10000000, '테스트 한글' LIKE '%한글');
  Query OK, 0 rows affected (19.51 sec)

  DO BENCHMARK(10000000, INSTR('테스트 한글',LOWER('한글')));
  Query OK, 0 rows affected (10.60 sec)

  DO BENCHMARK(10000000, INSTR(LOWER('테스트 한글'),'한글'));
  Query OK, 0 rows affected (15.39 sec)

  DO BENCHMARK(10000000, INSTR(LOWER('테스트 한글'),LOWER('한글')));
  Query OK, 0 rows affected (20.32 sec)


3. 결과 비교(표)

각 5번 테스트 최상,최하 버리고 중간값 선택

3-1. 영문 검색어 결과


+-----------+----------+-------------------+-------------------+--------------------+
|          |          | 대소문자 구별 (O) | 대소문자 구별 (X) |                    |
|  구 분  |  테스트  |---------+---------+---------+---------|      비고        |
|          |          |  LIKE  | INSTR() |  LIKE  | INSTR() |                    |
|-----------+----------+---------+---------+---------+---------+--------------------|
| 앞부분검색| 1,000,000|  0.47  |  0.27  |  0.66  |  1.86  |                    |
|          |10,000,000|  4.69  |  2.72  |  6.55  |  18.58  |                    |
|-----------+----------+---------+---------+---------+---------+--------------------|
|  중간 부분| 1,000,000|  0.60  |  0.44  |  1.15  |  2.15  |                    |
|          |10,000,000|  5.90  |  4.38  |  11.43  |  21.66  |                    |
|          |10,000,000|  8.38  |  18.65  |  37.46  |  53.59  | 51 bytes          |
|-----------+----------+---------+---------+---------+---------+--------------------|
| 뒷부분검색| 1,000,000|  0.56  |  0.43  |  1.22  |  1.77  |                    |
|          |10,000,000|  5.65  |  4.35  |  11.08  |  17.53  |                    |
|-----------+----------+---------+---------+---------+---------+--------------------|
|      결과          |  winner |        |  winner |        |                    |
+----------------------+---------+---------+---------+---------+--------------------+

*주) 단위 초(seconds), 값이 작을수록 우세

영문 검색은 대소문자를 구별하는 경우에 최단 시간이 걸림.

대소문자를 구별하는 검색에서는 검색할 데이터 분포가 중요한데,
앞부분 검색에서는 절대적(길이에 상관없이)으로 INSTR() 함수가 빠르지만,
나머지는 비슷하거나 BINARY ... LIKE 연산이 월등함.
즉,
검색 대상 길이가 길어지고 뒤쪽으로 검색할 수록 확실히 BINARY ... LIKE 연산이 더 빠름.

대소문자를 구별하지 않을 경우에서는,
모든 검색에서 LIKE 연산이 약 1.5 배 이상 우세함.

최악의 경우는 INSTR(LOWER(...),LOWER(...)) 로써 이것은 LIKE 연산보다
절대적으로 느림.

웹 게시판과 같은 검색에서는, 대부분 대소문자를 구별하지 않고 검색하는
경우가 많으므로 영문 검색은 LIKE 연산이 더 유리함.


3-2. 한글 검색어 결과


+-----------+----------+-------------------+----------------------------------------+
|          |          | 대소문자 구별 (O) |          대소문자 구별 (X)            |
|  구 분  |  테스트  |---------+---------+---------+---------+---------+----------|
|          |          |  LIKE  | INSTR() |  LIKE  |INSTR(,L)|INSTR(L,)|INSTR(L,L)|
|-----------+----------+---------+---------+---------+---------+---------+----------|
| 앞부분검색|10,000,000|  4.70  |  2.73  |  6.60  |  7.48  |  12.23  |  17.25  |
|-----------+----------+---------+---------+---------+---------+---------+----------|
|  중간 부분|10,000,000|  6.63  |  5.52  |  19.50  |  10.60  |  18.39  |  23.25  |
|          |10,000,000|  21.40  |  29.62  |  95.39  |  35.19  |  66.47  |  71.71  |
|-----------+----------+---------+---------+---------+---------+---------+----------|
| 뒷부분검색|10,000,000|  6.40  |  5.51  |  19.51  |  10.60  |  15.39  |  20.32  |
|-----------+----------+---------+---------+---------+---------+---------+----------|
|      결과          |  winner |        |        |(winnner)|        |          |
+----------------------+---------+---------+---------+---------+---------+----------+

*주) 단위 초(seconds), 값이 작을수록 우세

한글은 외관적으로 대소문자를 구별하지는 않지만, MySQL 의 내부적 연산에서,
대소문자를 구별하도록 실행할 경우, 모든 면에서 항상 우세함.
(*** 이것은 '한글'뿐만 아니라 '숫자' 자료형의 경우도 그대로 적용됨 ***)

일례로, 앞의 표의 '중간 부분' 검색에서 BINARRY ... LIKE 는 LIKE 보다
약 3 배 이상 빠르다는 것을 알 수 있고 INSTR() 함수 역시 마찬가지임.

영문검색과 마찬가지로 앞부분 검색을 제외하고, 검색 대상 길이가 길어지고
뒤쪽으로 검색할 수록 확실히 BINARY ... LIKE 연산이 더 빠름.

역시 최악의 경우는 최악의 경우는 INSET(LOWER(...),LOWER(...)) 임.


4. 결론


앞의 검색 테스트와 그 결과에서 알 수 있듯이, LIKE 연산이 대부분
유리하지만, LIKE 연산이 더 유리한가 아니면 INSTR() 연산이 더 유리한가에
대한 확답은 없습니다.

이것은, 검색할 타겟(대부분 columns)의 자료형이 어떤 문자열(문자셋)과
어떤 형태로 분포되어 있느냐에 따라서 속도차이가 날 뿐입니다.

그러나,

대부분 웹 게시판 같은 경우는 찾고자하는 단어 배열 형태가 무작위로 분포되어
있고, 또한 사용자 검색어 역시 무작위 임의의 단어있기 때문에 앞에서
테스트한 '중간부분 검색'이 실제 실무에서 적용가능한 방법임을
시사하고 있습니다.

검색할 column 역시, 대부분 32 또는 255 bytes(특이한 경우 제외) 이상이라는
점에서 다름과 같은 방법을 권장합니다.


+----------------+------------------------+------------------------------+
| column 형태    | 권장 (32 bytes 이상)  | 예제                        |
|----------------+------------------------+------------------------------|
| 대문자만      | BINARY str LIKE substr | BINARY cols LIKE '%KWD%'    |
| 소문자만      | BINARY str LIKE substr | BINARY cols LIKE '%kwd%'    |
| 대+소문자      | str LIKE substr        | cols LIKE '%kWd%'            |
|----------------+------------------------+------------------------------|
| 한글만        | BINARY str LIKE substr | BINARY cols LIKE '%한글%'    |
| 한글+대문자    | BINARY str LIKE substr | BINARY cols LIKE '%한글KWD%' |
| 한글+소문자    | BINARY str LIKE substr | BINARY cols LIKE '%한글kwd%' |
| 한글+대+소문자 | str LIKE substr        | cols LIKE '%한글kWd%'        |
+----------------+------------------------+------------------------------+

*주) column length 가 255 bytes 이상, 중간 검색이라는 가정
*주) 'kwd' 는 사용자가 검색하는 임의의 단어

PHP 적용 예)

< ?php
...
$kwd = '사용자 임의 검색어'; // add quoted string

$binary = ''; // 초기값
if(!preg_match('/[a-zA-Z]/',$kwd)) // 영문문자가 들어가 있는 않는 경우
{ $binary = 'BINARY'; }

$sql = "SELECT ... WHERE $binary board.text LIKE '%$kwd%' ...";
...
? >



만약, 검색할 column 이 32 bytes 이하이고, 또한 검색위치가 중간이 아닌
앞이거나 뒤쪽이라면, 앞의 결과표를 보고 적절한 방법을 선택해야 합니다.



5. 후기

없따앙.......

EOF




 
 

 
 이지홍 정말 멋지십니다. 감사합니다. 08/17 14:52:42 
 
 holies 한글로 검색할 경우, BINARY 없이 한 글자만 검색하면 이상한 걸 찾는 경우가 있더군요. 08/17 15:58:38 
 
 눈이오면 자세한 테스트 결과 감사드립니다..

3.x대 버전에서 테스트된 것 같군요.. 4.0.14에 몇가지 해보니..
4.0.14에서는 instr과 like의 검색 결과차가 거의 안나네요..
like와 instr각각에서 binary와의 차이도 거의 없는..

4.x에서는 instr이 대소문자 구분없이 검색되고.. 구분있게
중간 검색시에도 binary like보다 빠른 속도가 나오네요..
그리고 multi-byte safe함수라.. 전 이걸 선호합니다.. ^^ 08/17 17:40:41 
 
 망나니햌어 regexp 도 끼워 주셨음 좋았을 텐데..해보나 마나 겠죠 -_-;; 08/17 22:09:19 
 
 산이 와우 이런........
테스트 과정 및 글 올릴때 중대한 개인 과실(?)이 있었군요.

자고 있어나 봤더니
테스트가 너무 편파적이라는 느낌이 드네요..
(다양한 length 변화에 대한 테스트는 제외하고라도 ...)

- 테스트 버전 표시 누락 <--- 3.23.55

- str LIKE substr (OK)
- BINARY str LIKE substr (OK)
- str LIKE BINARY substr (누락)
- BINARY str LIKE BINARY substr (누락)

- INSTR(str, substr) (OK)
- INSTR(BINARY str, substr) (누락)
- INSTR(str, BINARY substr) (누락)
- INSTR(BINARY str, BINARY substr) (누락)

...
4.0.x 에서 `str LIKE substr' 연산이 상당한 진전이
있군요. 약 2~4배 이상 정도로 향상.

한글 검색 부분에서,
3.23.x 와 4.0.x 를 통틀어 아주 근소한 차이로(도토리 키재기)


1. INSTR(BINARY str, substr) <-- winner
2. INSTR(str, BINARY substr) <-- winner of the semifinals
3. INSTR(BINARY str, BINARY substr)
4. BINARY str LIKE substr
5. str LIKE BINARY substr
6. BINARY str LIKE BINARY substr
(여기까지가 거의 대동소이....)

7. str LIKE substr <--- 4.0.x
8. INSTR(str, substr) <--- 3.23.x, 4.0.x
9. str LIKE substr <--- 3.23.x

...
정리되면 수정된 글을 다시 올리지요.. 08/18 4:43:23 
 
 smilee 좋은팁감사합니다. 참고하도록 할께용... 수고하셨어용.. 08/18 15:52:13 
 
 헤로 instr 함수와 달리 like 의 경우 필드에 인덱스가 걸려 있으면
like "[keyword]" 나 like "[keyword]%" 같은 경우는 인덱스를 사용해서 검색하기 때문에 아주 빠른 검색이 가능하다는 것을 잊지 마시길 바랍니다. (인덱스가 없는 필드 검색일때는 위의 benchmark 함수를 이용해 테스트한 것과 동일형 포퍼먼스를 보여줄 듯 하군요.)