sphinx #3 :: MySQL에서 스핑크스 직접 join하기 (sphinxSE)
오픈소스 검색엔진 뭐 사용할까? 가벼운 스핑크스(Sphinx) 사용해보자 (인트로)
Sphinx #1 :: 스핑크스 설치 및 환경설정
sphinx #2 :: 스핑스크 쿼리 테스트 (sphinxQL + PHP연동)
sphinx #3 :: MySQL에서 스핑크스 직접 join하기 (sphinxSE)
sphinx #4 :: 스핑크스 주기적 인덱스 방법 (delta + crontab)
이전 시간까지 스핑크스를 설치했고, sphinxQL을 이용하여 주로 사용하는 쿼리를 테스트 해봤으며, PHP 연동까지도 해봤습니다.
이번 장은 sphinxSE를 사용하는 방법을 설명하고자 합니다.
이글 부터 보시는 분들은 꼭 #1 부터 읽어주시기 바랍니다.
sphinxSE는 searchd(스핑크스 데몬)와 대화하여 텍스트 검색을 가능하게 하는 스토리 엔진입니다.
스핑크스와 sphinxSE는 mariaDB에 내장된 전체 텍스트 검색(full text) 보다 빠르고 사용자 정의 가능한 대안으로 사용됩니다.
MariaDB에서 sphinxSE 설치하기
# mysql에 스핑크스 엔진 설치
mysql> INSTALL SONAME 'ha_sphinx';
mysql> SHOW ENGINES\G
+--------------------+---------+--------------+------+------------+
| Engine | Support | Transactions | XA | Savepoints |
+--------------------+---------+--------------+------+------------+
| CSV | YES | NO | NO | NO |
| MRG_MyISAM | YES | NO | NO | NO |
| MyISAM | YES | NO | NO | NO |
| SEQUENCE | YES | YES | NO | YES |
| SPHINX | YES | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | NO | NO | NO |
| MEMORY | YES | NO | NO | NO |
| Aria | YES | NO | NO | NO |
| FEDERATED | YES | YES | NO | YES |
| InnoDB | DEFAULT | YES | YES | YES |
+--------------------+---------+--------------+------+------------+
MySQL에 접속하여 ha_sphinx를 install 하면 준비과정은 끝났습니다.
MySQL의 스토리엔진 중 스핑크스는 단독으로 사용할 수 없습니다. 반드시 sphinx가 설치되어 있어야 합니다.
참고자료로 메뉴얼 링크 걸어 놓았습니다. 참고하세요.
MariaDB의 sphinx 엔진 문서 바로가기
sphinx의 sphinxSE 관련 문서 바로기
MariaDB에서 sphinxSE 사용하기
# MySQL 접속]
# mysql -uroot -p
# sphinx DB 생성
mysql> create databse sphinx;
# sphinx DB 사용
mysql> use teset
# sph_zip_code 테이블 생성
mysql> CREATE TABLE `sph_zip_code` (
`id` BIGINT(20) UNSIGNED NOT NULL,
`weight` INT(11) NOT NULL,
`query` VARCHAR(3072) NOT NULL,
`group_id` INT(11) DEFAULT NULL,
`address` VARCHAR(100) DEFAULT NULL,
KEY `query` (`query`(1024))
) ENGINE=SPHINX DEFAULT CHARSET=utf8 CONNECTION='sphinx://스핑크스설치된IP:9312/sph_zip_code';
테이블 생성 시 컬럼 중 id, weight, query, group_id는 반드시 만들어야 합니다. 순서도 똑같이 하세요.
그 아래에 스핑크스 환경설정에서 sql_field_string=컬럼명 설정한 것을 생성하는것은 옵션입니다.
테스트 차원으로 address 컬럼만 만들어 보겠습니다.
우선은 만들기만 해보고 다음 쿼리 실행할때 차이점을 설명 드릴게요.
- engine = sphinx # 스핑크스 엔진 사용한다 명시합니다.
- connection='sphinx://127.0.0.1:9312/sph_zip_code'
- 127.0.0.1 # 스핑크스 설치한 서버의 IP
- 9312 # 포트(sphinx.conf 파일에서 listen=9312 라고 설정했었습니다. 그 포트 번호에요)
- sph_zip_code # sphinxconf파일에서 source명 입니다.
지금까지는 뭐하는건지 모르겠죠.
쿼리 하나하나 같이 질의하다보면 윤곽 보이실 겁니다.
mysql> select * from sph_zip_code;
Empty set (0.01 sec)생성한 테이블을 select하면 비어있습니다.
where 조건을 태워보죠
mysql> SELECT * FROM sphinx.sph_zip_code WHERE `query` = '"운산";mode=ext2';
+-------+--------+--------------------+----------+-----------------------------------------------------+
| id | weight | query | group_id | address |
+-------+--------+--------------------+----------+-----------------------------------------------------+
| 15004 | 4536 | "서산";mode=ext2 | 0 | 충남 서산시 서산대산우체국사서함 |
| 15003 | 4535 | "서산";mode=ext2 | 0 | 충남 서산시 서산우체국사서함 |
| 15007 | 4535 | "서산";mode=ext2 | 0 | 충남 서산시 읍내동 서산경찰서 |
| 14989 | 4533 | "서산";mode=ext2 | 0 | 충남 서산시 동문동 서산우체국 |
| 15005 | 4533 | "서산";mode=ext2 | 0 | 충남 서산시 예천동 대전지방법원서산지원 |
| 15006 | 4533 | "서산";mode=ext2 | 0 | 충남 서산시 동문동 KT서산지사 |
| 15008 | 4533 | "서산";mode=ext2 | 0 | 충남 서산시 읍내동 서산시청 |
| 15011 | 4533 | "서산";mode=ext2 | 0 | 충남 서산시 예천동 대전지방검찰청서산지청 |
| 15016 | 4533 | "서산";mode=ext2 | 0 | 충남 서산시 대산읍 대죽리 (주)엘지화학대산공장 |
| 15029 | 4533 | "서산";mode=ext2 | 0 | 충남 서산시 읍내동 서산읍내현대아파트 |
| 15031 | 4533 | "서산";mode=ext2 | 0 | 충남 서산시 석남동 서산센스빌아파트 (101~117동) |
| 15039 | 4533 | "서산";mode=ext2 | 0 | 충남 서산시 읍내동 서산롯데캐슬아파트 (101~114동) |
| 15040 | 4533 | "서산";mode=ext2 | 0 | 충남 서산시 음암면 부장리 서산수림미소가아파트 (101~115동) |
| 15099 | 4533 | "서산";mode=ext2 | 0 | 충남 서산시 운산면 갈산리 |
| 15105 | 4533 | "서산";mode=ext2 | 0 | 충남 서산시 운산면 고산리 |
| 15159 | 4533 | "서산";mode=ext2 | 0 | 충남 서산시 대산읍 대산리 |
| 15163 | 4533 | "서산";mode=ext2 | 0 | 충남 서산시 대산읍 운산리 |
| 14996 | 4532 | "서산";mode=ext2 | 0 | 충남 서산시 갈산동 |
| 15012 | 4532 | "서산";mode=ext2 | 0 | 충남 서산시 대산읍 삼성토탈(주) |
| 15013 | 4532 | "서산";mode=ext2 | 0 | 충남 서산시 대산읍 대죽리 (주)씨텍 |
+-------+--------+--------------------+----------+-----------------------------------------------------+
드디어 실체가 나왔습니다.
- id : 스핑크스에서 seq(PK) 값입니다.
- weight : 가중치 값입니다.
첫번째 row를 보면 "운산" 을 검색한 결과에 매칭되는 점수가 4536점 이다 라고 생각하시면 됩니다. - query : 질의한 쿼리를 보여줍니다.
- address : 테이블 생성 시 옵션 컬럼이라고 했었죠.
테이블 생성시 add1, add2 등 컬럼을 만들었다면 결과에 해당되는 데이터를 자동으로 매핑되어 확인 할 수 있습니다.
결국 query="어쩌구"를 통해서 스핑크스에 인덱싱된 데이터를 가져올 수 있는 구조입니다.
# mysql에서 아래의 쿼리는
mysql> SELECT * FROM sphinx.sph_zip_code WHERE `query` = '"운산";mode=ext2';
#sphinxQL에서 아래의 쿼리와 동일합니다.
sphinxQL> select * from sph_zip_code where match('"운산"');쿼리 문법은 아래에서 자세히 나올 거니까 우선은 '이런식으로 검색하는구나' 정도로만 이해하고 넘어갑시다.
지금까지 살펴본 shpinxSE 구조도
좀 감이 잡히시나요?
아리송 하죠?
지금까지 우리가 한 행위를 구조화 해보도록 하겠습니다.
- 기존 MySQL 이 설치되어 있는 상태로 (왼쪽 파란 영역)
- ⓐ zip_code 테이블을 스핑크스 태우고 싶었습니다.
- 그래서 스핑크스를 설치했고 (오른쪽 주황색 영역)
- sphinx.conf 파일을 생성하여 indexer 유틸로 ⓑ스핑크스 인덱스 테이블(sph_zip_code)을 생성시켰죠.
- ⓑsph_zip_code에 데이터 잘 들어갔는지 확인 하기 위해서 ⓔ sphinxQL 접속도 해봤고
- ⓔ sphinxQL 에서 match 쿼리를 사용하여 검색 쿼리도 테스트 해봤습니다.
- php에서 ⓔ sphinxQL를 바로 connection 하여 사용할 수 있는 것도 확인했고요.
- 궁극적으로 사용하고 싶었던 것은 sphinxSE 였습니다.
- MySQL 데이터베이스에 ⓒsph_zip_code를 sphinx 엔진 타입으로 생성하여 ⓑsph_zip_code를 링크? 시켰고,
- 데이터 확인을 위해
ⓓmysql 접속을 하여 query='"운산";mode=ext2'" 를 사용하여
ⓒ를통해 ⓑ의 데이터를 가지고도 왔습니다.
이제 좀 감이 잡히시나요?
안잡히시면... 저의 표현력을 탓하면서 계속 반복해서 읽어주세요 ㅠ.ㅠ
inner join 사용하기
sphinxSE를 사용하는 이유는 mysql의 다른 테이블과 join 해서 바로 사용하려고 하는게 아닐까 싶습니다.
저는 그렇습니다. ㅎ
바로 inner join 한번 해봅시다.
mysql> SELECT a.*, b.* FROM sphinx.sph_zip_code a, test.`zip_code` b
WHERE a.id = b.seq
AND `query` = '서산;mode=ext2'
AND chang_date > 20040517 LIMIT 10;
+-------+--------+------------------+----------+----------------------------------------------------------------------------+-------+----------+--------+--------+-----------+-----------+-----------+------+--------+---------------------------------------+------------+----------------------------------------------------------------------------+
| id | weight | query | group_id | address | seq | zip_code | sub_no | add1 | add2 | add3 | add4 | add5 | number | bilding | chang_date | address |
+-------+--------+------------------+----------+----------------------------------------------------------------------------+-------+----------+--------+--------+-----------+-----------+-----------+------+--------+---------------------------------------+------------+----------------------------------------------------------------------------+
| 15016 | 5533 | 서산;mode=ext2 | 0 | 충남 서산시 대산읍 대죽리 (주)엘지화학대산공장 | 15016 | 356715 | 014 | 충남 | 서산시 | 대산읍 | 대죽리 | NULL | NULL | (주)엘지화학대산공장 | 20060401 | 충남 서산시 대산읍 대죽리 (주)엘지화학대산공장 |
| 15012 | 5532 | 서산;mode=ext2 | 0 | 충남 서산시 대산읍 삼성토탈(주) | 15012 | 356711 | 004 | 충남 | 서산시 | 대산읍 | NULL | NULL | NULL | 삼성토탈(주) | 20050212 | 충남 서산시 대산읍 삼성토탈(주) |
+-------+--------+------------------+----------+----------------------------------------------------------------------------+-------+----------+--------+--------+-----------+-----------+-----------+------+--------+---------------------------------------+------------+----------------------------------------------------------------------------+
6 rows in set (0.00 sec)
실 사용되는 테이블인 몇백만 row의 text, varchar등을 스핑크스 태우고 join해서 사용하는 서비스 프로그램에도 속도 짱짱하게 잘 나옵니다.
그런데 query='"운산";mode=ext2'" 에서 mode=ext2 는 무엇일까요?
스핑크스의 검색 모드는 총 6가지를 지원합니다.
- SPH_MATCH_ALL : 모든 쿼리 단어와 일치
- SPH_MATCH_ANY : 모르겠음.
- SPH_MATCH_PHRASE : 완벽하게 일치를 필요로 하는 문구
- SPH_MATCH_BOOLEAN : 부울표현식으로 쿼리
- SPH_MATCH_EXTENDED : 스핑크스 내부 쿼리 언어의 표현대로 쿼리
- SPH_MATCH_EXTENDED2 : extened의 확장검색모드 (@, |, MAYBE, ! 등 연산자를 사용할 수 있다)
위의 6가지를 sphinxSE에서 사용하기 위해서 약식 mode를 사용합니다.
- SPH_MATCH_ALL : mode=all
- SPH_MATCH_ANY : mode=any
- SPH_MATCH_PHRASE : mode=phr
- SPH_MATCH_BOOLEAN : mode=bool
- SPH_MATCH_EXTENDED : mode=ext
- SPH_MATCH_EXTENDED2 : mode=ext2
다양한 예제
[컬럼조회]
# address 컬럼에서 운산을 찾아라
mysql> SELECT * FROM sphinx.sph_zip_code WHERE `query` = '@address "운산";mode=ext2';
[검색 가중치]
# 운산을 찾는다, add1컬럼에서 가중치 10으로 address컬럼에서 가중치 5로 잡아라.
mysql> SELECT * FROM sphinx.sph_zip_code
WHERE `query` = '"운산";mode=ext2;fieldweights=add1,10,address,5';
[정렬]
# address 컬럼으로 desc정렬해라 ("relevance", "attr_desc", "attr_asc", "time_segments", or "extended")
mysql> SELECT * FROM sphinx.sph_zip_code WHERE `query` = '"운산";mode=ext2;sort=attr_desc:address';
mysql> SELECT * FROM sphinx.sph_zip_code WHERE `query` = '"운산";mode=ext2;sort=extended:@weight desc, group_id asc';
[limit]
# 정렬하고 30 row만 출력해라.
mysql> SELECT * FROM sphinx.sph_zip_code
WHERE `query` = '"운산";mode=ext2;sort=attr_desc:address;limit=30';limit 과 sort는 적절히 잘 사용해야 합니다.
기존적으로 정렬은 weight desc이고, limit 20 입니다.
적중률이 엄청 높은 1991년 자료이다 라고 하면 옳은 데이터 일수도 있고, 좀 그런 데이터가 될수도 있기 때문입니다.
[근접성]
# 충남 운산 2개의 단어 모두를 포함하는 3단어 미만 범위의 데이터 가져오기
mysql> SELECT * FROM sphinx.sph_zip_code WHERE `query` = '@condition "충남 운산"~3";mode=ext2;';
+-------+--------+--------------------------------------+
| id | weight | address |
+-------+--------+--------------------------------------+
| 15099 | 3555 | 충남 서산시 운산면 갈산리 |
| 15105 | 3555 | 충남 서산시 운산면 고산리 |
| 15094 | 3554 | 충남 서산시 운산면 |
| 15095 | 3554 | 충남 서산시 운산면 가좌리 |
| 15096 | 3554 | 충남 서산시 운산면 상성리 |
| 15097 | 3554 | 충남 서산시 운산면 소중리 |
| 15098 | 3554 | 충남 서산시 운산면 원벌리 |
| 15100 | 3554 | 충남 서산시 운산면 용장리 |
| 15101 | 3554 | 충남 서산시 운산면 거성리 |
| 15102 | 3554 | 충남 서산시 운산면 신창리 |
| 15103 | 3554 | 충남 서산시 운산면 용현리 |
| 15104 | 3554 | 충남 서산시 운산면 태봉리 |
| 15106 | 3554 | 충남 서산시 운산면 수당리 |
| 15107 | 3554 | 충남 서산시 운산면 수평리 |
| 15108 | 3554 | 충남 서산시 운산면 안호리 |
| 15109 | 3554 | 충남 서산시 운산면 여미리 |
| 15110 | 3554 | 충남 서산시 운산면 팔중리 |
| 15111 | 3554 | 충남 서산시 운산면 고풍리 |
| 15112 | 3554 | 충남 서산시 운산면 와우리 |
| 15113 | 3554 | 충남 서산시 운산면 원평리 |
+-------+--------+--------------------------------------+
[단락검색]
# 같은 단락중 충남 또는 운산 있는 row 검색
mysql> SELECT * FROM sphinx.sph_zip_code
WHERE `query` = '@address "충남"PARAGRAPH"운산";mode=ext2;';
마무리
적고 보니까 다양하지는 않네요. ^^;;
다음장이 마지막이 될듯합니다.
다음장에서 살펴볼 내용은 아래와 같습니다.
- 스핑크스 부분 index 태우는 방법
- 크론 등록하여 배치성으로 index 태우는 방법
- 서버 reboot 했을때 searchd 자동으로 실행하게하기
- 기타...
[참고]
https://sacstory.tistory.com/entry/Debain-%EA%B2%80%EC%83%89-%EC%97%94%EC%A7%84-Sphinx-%ED%95%9C%EA%B8%80-%EC%84%A4%EC%A0%95
http://sphinxsearch.com/
http://sphinxsearch.com/docs/manual-2.2.11.html#extended-syntax
본 글은 나의 다른 블로그(폐쇄예정) 에서 작성한 글을 이관한 포스팅입니다. (2018년 12월 5일 작성)
'개발 > MariaDB' 카테고리의 다른 글
| [MySQL] MySQL 리플리케이션 UUID 관련 에러 (0) | 2022.10.25 |
|---|---|
| sphinx #4 :: 스핑크스 주기적 인덱스 방법 (delta + crontab) (0) | 2022.10.25 |
| sphinx #2 :: 스핑스크 쿼리 테스트 (sphinxQL + PHP연동) (0) | 2022.10.25 |
| Sphinx #1 :: 스핑크스 설치 및 환경설정 (0) | 2022.10.25 |
| 오픈소스 검색엔진 뭐 사용할까? 가벼운 스핑크스(Sphinx) 사용해보자 (인트로) (0) | 2022.10.25 |