총 게시물 12건, 최근 0 건 안내 RSS 글쓰기
이전글  다음글  목록 글쓰기
[강좌]

[MYSQL] LIKE vs INSTR()

글쓴이 : 카이로 날짜 : 2013-04-29 (월) 07:57 조회 : 10079
글주소 : http://www.androidside.com/B19/3
 
  아마존 AWS 공식사이트
AWS 클라우드 데이터베이스, 저렴한 비용, 쉬운 관리, 지금 1년무료체험 신청!
aws.amazon.com/ko/
  제이에스페이지
PHP 웹프로그램 웹표준 퍼블리싱 PHP MySql HTML CSS jQuery
www.jspage.co.kr
  MYSQL 인터넷교보문고
MYSQL, 최대 14,000원 혜택, 온오프 통합포인트! 바로드림 배송혁명!
www.kyobobook.co.kr
신청하기


출처 : http://www.phpschool.com/bbs2/inc_view.html?id=9464&code=tnt2

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



로그인 창이 있는 "정보 수정"에서
서명 이쁘게 등록해보세요 ^^

카이로 님의 데이터베이스 최신글 [더보기]


추천 버튼을 클릭하면 추천한 자와 추천받은 자 모두에게 포인트가 지급됩니다.

사가정만발 2015-01-09 (금) 08:34
좋은정보 감사합니다.
댓글주소
hi
이전글  다음글  목록 글쓰기

총 게시물 12건, 최근 0 건 안내 RSS
번호 분류 제목 글쓴이 날짜 추천 비추천 조회
12 TIP&TECH
char대신 varchar를 쓰는이유??http://okky.kr/article/217655 정리가 잘되있는거 같아서 링크를 가지고 와봤습니다. char…
이힝힝 08-08 1 0 3027
11 소스코드
아래는 java 파일에 sql 쿼리문을 작성한 부분입니다. 여기서 궁금한게 DATETIME 은 테이블내에 있는 DATE 형식의 컬럼…
아르페우스 11-16 1 0 3807
10 TIP&TECH
기능설명 :   A 서버에 A DB와 B 서버에 B DB가 있을경우   1. A서버의 ADB에 테이블에 데이…
카이로 11-26 4 0 9545
9 TIP&TECH
### 원격서버의 MYSQL데이테베이스 백업하기   1) 원격서버의 MYSQL데이테베이스 백업하기 (기본포트 사…
카이로 07-12 6 0 8620
8 TIP&TECH
MySQL 인덱스(INDEX) 사용 방법  인덱스는 특정 칼럼 값을 가지고 있는 열을 빠르게 찾기 위해서 사용된다.&n…
앱개발재밌어 09-30 2 0 11056
7 TIP&TECH
1. 인덱스를 검사하는 법. explain select * from Table_Name where A='a' and B='b' order by C,D,E ; 해당 쿼리문이 인덱스를 …
앱개발재밌어 09-30 0 1 9804
6 TIP&TECH
INDEX의 의미?   RDBMS에서 검색속도를 높이기 사용하는 하나의 기술이입니다. INDEX는 색인입니다. 해당 TABLE의 …
앱개발재밌어 09-30 4 0 14158
5 TIP&TECH
안녕하세요. 디비구조를 만만하게 생각하고, 엑셀로 끄적이며 설계해보려 했으나, 만만하지가 않더군요. …
앱개발재밌어 09-26 0 0 10560
4 소스코드
샘플긁어 온건데 테스트 함 해보세요
레오니아 08-08 15 0 12454
3 강좌
출처 : http://www.phpschool.com/bbs2/inc_view.html?id=9464&code=tnt2[MYSQL] LIKE vs INSTR()0. 배경1. 영문 검색어 테스트  1-1…
카이로 04-29 2 0 10080
2 강좌
아래 내용은 Ubuntu/GNU Linux 11.04 를 기반으로 한다. - MySQL 서버 중지 후 데이터 디렉토리의 내용을 새 디렉토리로 …
카이로 04-25 0 0 10178
1 강좌
ibdata1 파일은 MySQL의 innodb 로그 파일로 줄일 수 없다. 단, 다음 과정을 통해 삭제 후 재생성할 수 있다. 1. 전체 데…
카이로 04-25 0 0 15390
 


Copyright ⓒ www.androidside.com. All rights reserved.
채팅 권한: 글쓰기 1개
2레벨 이상만 대화 가능
공개 채팅: 평일 !(9시 ~ 17시),토,일
안사2 변경사항 보러가기 챗방이 잘 안보이면 크롬에서 접속해주세요
챗방 숨기기 |  챗방 보이기