LIKE vs INSTR() vs BINARY LIKE 속도 비교 > Mysql 정보

본문 바로가기

검색취소

LIKE vs INSTR() vs BINARY LIKE 속도 비교

검색 이전 다음 목록

본문

[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. 영문 검색어 결과
*주) 단위 초(seconds), 값이 작을수록 우세

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

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

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

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

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


3-2. 한글 검색어 결과
*주) 단위 초(seconds), 값이 작을수록 우세

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

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

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

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


4. 결론


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

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

그러나,

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

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

*주) 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 이하이고, 또한 검색위치가 중간이 아닌
앞이거나 뒤쪽이라면, 앞의 결과표를 보고 적절한 방법을 선택해야 합니다.

등록된 댓글이 없습니다.

검색취소

Mysql 정보

검색
  • no image
  • [MySQL] ARCHIVE 엔진 및 INNODB 테이블 압축

    MySQL (혹은 MariaDB도 동일)에는 기본적으로 다양한 테이블 엔진이 있습니다.최근에 필요에 따라 ARCHIVE 엔진을 사용해 볼 기회가 있었습니다.이 엔진은 로그 저장과 같이 계속해서 Append 되기만 하…

  • no image
  • [펌] MySQL 운영하면서 겪은 문제점과 해법들

     MySQL 운영하면서 겪은 문제점과 해법들지난 2년간 번개장터 서비스를 운영하면서 MySQL 을 사용하였는데 여러가지 한계에 부딛히면서 전문 업체에게 컨설팅을 받아서 시행착오를 줄이는게 낫겠다는 생각을 했습니다. …

  • LIKE vs INSTR() vs BINARY LIKE  속도 비교
  • LIKE vs INSTR() vs BINARY LIKE 속도 비교

    [MYSQL] LIKE vs INSTR()0. 배경1. 영문 검색어 테스트  1-1. 앞 부분 검색  1-2. 중간 부분 검색  1-3. 끝 부분 검색2. 한글 검색어 테스트  2-1. 앞 부분 검색  2-2. 중간…

  • no image
  • [DBMS] MySQL 의 InnoDB 엔진 의 특성

    다들 한번씩은 들어봄직한 InnoDB 엔진에는 개발시 매우 중요한 부분을 차지할 수도 있을 만한 특성이 있습니다. 이 특성 3가지를 언급해 보고자 합니다. 행여 InnoDB 를 사용하여 개발하시는 분들은 참고하시길 …

  • no image
  • 로그활용4 - slow query log

    로그활용4 - slow query logslow-query-log(--slow-query-log)처리에 시간이 걸린 쿼리를 기록하기 위한 옵션이다.쿼리 실행에 long_query_time에 세팅된 초수(표준 10초)…

  • no image
  • Mysql 기록하기 sql log record log 기록

    5.11.3. 일반 쿼리 로그일반 쿼리 로그는 mysqld의 일반적인 실행 결과를 기록한 것이다. 서버는 클라이언트가 접속을하거나 또는 접속을 끊을 때 정보를 이 로그에 기록하고, 클라이언트에서 받는 각 SQL 명령…

?>