티스토리 뷰
이전 포스트을 통해 아래와 같은 것을 했습니다.
● 스핑크스 설치
● 환경설정 (sphinx.conf)
● 인덱싱 생성
● 데몬 실행
● sphinxQL 실행
이번에는 sphinxQL을 통해 쿼리를 날려보고 동작원리 및 유용한 쿼리를 연습하는 시간을 가져보려 합니다.
기본
sphinxQL를 접속해주세요.
]$ mysql -h0 -P9306
]$ mysql -h0 -P9306
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 2.2.11-id64-release (95ae9a6)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show tables;
+--------------+-------+
| Index | Type |
+--------------+-------+
| sph_zip_code | local |
+--------------+-------+
1 rows in set (0.00 sec)
기본적인 문법은 일반 sql문과 동일합니다.
show tlabes를 통해 스핑크스의 테이블을 확인 할 수 있습니다.
select도 해볼까요?
select * from sph_zip_code를 한 결과는 sphinx.conf 파일에 설정 데이터에 의거하여 나온 결과물입니다.
]$ vi /etc/sphinx/sphinx.conf
#############################################################################
## source settings
#############################################################################
source sph_zip_code
{
type = mysql
sql_host = mysql ip주소 (localhost)
sql_user = 계정명
sql_pass = 패스워드
sql_db = DB명
sql_port = 3306
sql_query_pre = SET NAMES utf8
sql_query_pre = SET SESSION query_cache_type=OFF
sql_query = \
SELECT seq, zip_code, add1, add2, add3, address \
FROM zip_code
sql_field_string = add1
sql_field_string = add2
sql_field_string = add3
sql_field_string = address
}
1. mysql에 있는 zip_code를 select하여
2. zip_code, add1, add2, add3, address 컬럼을 index 시켰습니다.
3. 결과적으로 sphinxQL에서 select * from sph_zip_code 를 질의하니 id(zip_code), add1, add3, address 컬럼이 조회됐습니다. (sql_field_string 설정한 값)
4. 만약 sql_field_string 을 설정안했다면 select * from sph_zip_code의 결과는 id컬러만 보이게 됩니다.
구문검색
MySQL [(none)]> select add1, add2, add3, address from sph_zip_code where match('운산') limit 10, 20;
+--------+---------------------------+------------+--------------------------------------------------------------------------------------+
| add1 | add2 | add3 | address |
+--------+---------------------------+------------+--------------------------------------------------------------------------------------+
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 용현리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 태봉리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 수당리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 수평리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 안호리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 여미리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 팔중리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 고풍리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 와우리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 원평리 |
| 강원 | 강릉시 | 운산동 | 강원 강릉시 운산동 |
| 충남 | 서산시 | 대산읍 | 충남 서산시 대산읍 운산리 |
| 부산 | 해운대구 | 재송1동 | 부산 해운대구 재송1동 부산해운대경찰서 |
| 경남 | 창원시 마산합포구 | 해운동 | 경남 창원시 마산합포구 해운동 해운동두산1차아파트 (101~104동) |
| 전북 | 군산시 | 나운2동 | 전북 군산시 나운2동 금호타운1차아파트 (101~107동) |
| 전북 | 군산시 | 나운2동 | 전북 군산시 나운2동 금호타운2차아파트 (201~206동) |
| 부산 | 해운대구 | 좌1동 | 부산 해운대구 좌1동 해운대우체국 |
| 부산 | 해운대구 | | 부산 해운대구 해운대우체국사서함 |
| 부산 | 해운대구 | 중1동 | 부산 해운대구 중1동 해운대구청 |
| 부산 | 해운대구 | 우1동 | 부산 해운대구 우1동 대우월드마크해운대 (101~102동) |
+--------+---------------------------+------------+--------------------------------------------------------------------------------------+
결과를 보시면 유추되는 것이 몇가지 있습니다.
1. 검색어("운산")이라는 단어는 add1, add2, add3, address 컬럼을 바라보고 조회된다.
2. 검색어"운산") 을 "운", "산" 을 쪼개서 검색도 한다.
3. "운산" 정확히 일치하는 단어가 점수가 높고, 쪼개진 단어가 점수가 낮다. 이를 합산한 순으로 정렬되는것으로 추정된다.
"운산"만 검색하려면 어떻게 해야 할까요?
match('"운산"') 하시면 됩니다. 무엇이 다른거냐고요?
쌍따음표를 하지 않으면 -> '운산' -> "운산", "산운", "운", "산" 찾음
쌍따음표를 같이 넣으면 -> '"운산"' -> "운산" 찾음
MySQL [(none)]> select add1, add2, add3, address from sph_zip_code where match('충남 운산');
MySQL [(none)]> select add1, add2, add3, address from sph_zip_code where match('충남운산');
MySQL [(none)]> select add1, add2, add3, address from sph_zip_code where match('운산 충남');
MySQL [(none)]> select add1, add2, add3, address from sph_zip_code where match('운산충남');
+--------+---------------------+-----------+--------------------------------------------------------------------------------------+
| add1 | add2 | add3 | address |
+--------+---------------------+-----------+--------------------------------------------------------------------------------------+
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 갈산리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 고산리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 가좌리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 상성리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 소중리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 원벌리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 용장리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 거성리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 신창리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 용현리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 태봉리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 수당리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 수평리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 안호리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 여미리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 팔중리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 고풍리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 와우리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 원평리 |
| 충남 | 논산시 | 채운면 | 충남 논산시 채운면 |
| 충남 | 논산시 | 채운면 | 충남 논산시 채운면 삼거리 |
| 충남 | 논산시 | 채운면 | 충남 논산시 채운면 야화리 |
| 충남 | 논산시 | 채운면 | 충남 논산시 채운면 장화리 |
| 충남 | 논산시 | 채운면 | 충남 논산시 채운면 화산리 |
| 충남 | 논산시 | 채운면 | 충남 논산시 채운면 심암리 |
| 충남 | 논산시 | 채운면 | 충남 논산시 채운면 용화리 |
| 충남 | 논산시 | 채운면 | 충남 논산시 채운면 우기리 |
| 충남 | 논산시 | 채운면 | 충남 논산시 채운면 화정리 |
| 충남 | 서산시 | 대산읍 | 충남 서산시 대산읍 운산리 |
| 충남 | 예산군 | 광시면 | 충남 예산군 광시면 운산리 |
| 충남 | 논산시 | 강경읍 | 충남 논산시 강경읍 채운리 |
| 충남 | 아산시 | 영인면 | 충남 아산시 영인면 신운리 |
| 충남 | 아산시 | 둔포면 | 충남 아산시 둔포면 운교리 |
| 충남 | 아산시 | 둔포면 | 충남 아산시 둔포면 운용리 |
| 충남 | 예산군 | 응봉면 | 충남 예산군 응봉면 운곡리 |
| 충남 | 부여군 | 내산면 | 충남 부여군 내산면 운치리 |
| 충남 | 홍성군 | 갈산면 | 충남 홍성군 갈산면 운곡리 |
| 충남 | 천안시 동남구 | 청당동 | 충남 천안시 동남구 청당동 청당산운마을5단지아파트 (501~508동) |
| 충남 | 당진시 | 정미면 | 충남 당진시 정미면 대운산리 |
| 충남 | 당진시 | 합덕읍 | 충남 당진시 합덕읍 운산리 |
+--------+---------------------+-----------+--------------------------------------------------------------------------------------+
41 rows in set (0.00 sec)
1. '충남 운산', '운산 충남', '충남운산', '운산충남' 다 결과가 같다.
2. 충남운산 or 충남 or 운산 or 충 or 남 or 운 or 산 을 찾습니다.
MySQL [(none)]> select add1, add2, add3, address from sph_zip_code where match('"충남""운산"') limit 100;
MySQL [(none)]> select add1, add2, add3, address from sph_zip_code where match('"충남" "운산"') limit 100;
MySQL [(none)]> select add1, add2, add3, address from sph_zip_code where match('"운산" "충남"') limit 100;
MySQL [(none)]> select add1, add2, add3, address from sph_zip_code where match('"운산""충남"') limit 100;
+--------+-----------+-----------+-----------------------------------------+
| add1 | add2 | add3 | address |
+--------+-----------+-----------+-----------------------------------------+
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 갈산리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 고산리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 가좌리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 상성리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 소중리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 원벌리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 용장리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 거성리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 신창리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 용현리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 태봉리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 수당리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 수평리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 안호리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 여미리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 팔중리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 고풍리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 와우리 |
| 충남 | 서산시 | 운산면 | 충남 서산시 운산면 원평리 |
| 충남 | 서산시 | 대산읍 | 충남 서산시 대산읍 운산리 |
| 충남 | 예산군 | 광시면 | 충남 예산군 광시면 운산리 |
| 충남 | 당진시 | 정미면 | 충남 당진시 정미면 대운산리 |
| 충남 | 당진시 | 합덕읍 | 충남 당진시 합덕읍 운산리 |
+--------+-----------+-----------+-----------------------------------------+
24 rows in set (0.00 sec)
1. 위의 결과 4개는 모두 같은 결과를 출력합니다.
2. 충남 과 운산 포함한 row를 찾습니다.
충남 또는 운산인 row 결과
MySQL [(none)]> select add1, add2, add3, address from sph_zip_code where match('"운산" "충남" -"서산"') limit 100;
MySQL [(none)]> select add1, add2, add3, address from sph_zip_code where match('"운산" "충남" -서산') limit 100;
MySQL [(none)]> select add1, add2, add3, address from sph_zip_code where match('"운산" "충남" !"서산"') limit 100;
MySQL [(none)]> select add1, add2, add3, address from sph_zip_code where match('"운산" "충남" !서산') limit 100;
+--------+-----------+-----------+-----------------------------------------+
| add1 | add2 | add3 | address |
+--------+-----------+-----------+-----------------------------------------+
| 충남 | 예산군 | 광시면 | 충남 예산군 광시면 운산리 |
| 충남 | 당진시 | 정미면 | 충남 당진시 정미면 대운산리 |
| 충남 | 당진시 | 합덕읍 | 충남 당진시 합덕읍 운산리 |
+--------+-----------+-----------+-----------------------------------------+
3 rows in set (0.01 sec)
"충남" 과 "운산"을 포함한 결과 중 "서산"은 제외시켜라
# address 컬럼에서 운산 찾아라
MySQL [(none)]> select add1, add2, add3, address from sph_zip_code where match('@address "운산"');
# address, add1 컬럼에서 운산 찾아라
MySQL [(none)]> select add1, add2, add3, address from sph_zip_code where match('@(address, add1) "운산"');
# address 컬럼 제외한 컬럼에서 운산 찾아라
MySQL [(none)]> select add1, add2, add3, address from sph_zip_code where match('@!address "운산"');
# address, add1 컬럼 제외한 컬럼에서 "운산" 또는 "서산" 찾아라
MySQL [(none)]> select add1, add2, add3, address from sph_zip_code where match('@!(address, add1) "운산""서산"');
PHP에서 사용하기
error_reporting(E_ERROR | E_WARNING | E_PARSE);
ini_set("display_errors","1");
//=============== 스핑크스 테스트 ===========================================================
$sphinx_con = mysqli_connect("스핑크스IP:9306", "", "","");
mysqli_query($sphinx_con, 'set names utf8');
$sql = "select * from sph_zip_code where match('운산') limit 10";
$rs = mysqli_query($sphinx_con, $sql);
$totalrows = mysqli_num_rows($rs);
while($row = mysqli_fetch_assoc($rs)) {
print_r($row);
}
[결과 화면]
Array
(
[seq] => 52522
[zip_code] => 799823
[sub_no] => 001
[add1] => 경북
[add2] => 울릉군
[add3] => 북면
[add4] => 현포리
[add5] =>
[number] =>
[bilding] =>
[chang_date] => 20040517
[address] => 경북 울릉군 북면 현포리
)
Array
(
[seq] => 52521
[zip_code] => 799822
[sub_no] => 021
[add1] => 경북
[add2] => 울릉군
[add3] => 북면
[add4] => 천부4리
[add5] =>
[number] =>
[bilding] =>
[chang_date] => 20040517
[address] => 경북 울릉군 북면 천부4리
)
.......
만약 connection 에러 발생되면 방화벽 내려보고 테스트해보세요. 그때 문제가 없다면 9306포트 방화벽에서 열어주세요.
일반적으로(sphinxQL, sphinxAPI)는 스핑크스 쿼리를 통해 pk를 뽑아 온 후 mysql에 where in(pk, pk, pk) 형식으로 실제 데이터를 가져옵니다.
저는 그게 싫어서 sphinxSE를 이용하여 바로 실제데이터와 join하여 데이터를 가져올 계획입니다.
다음편에서 그 사용방법을 다루도록 하겠습니다.
'DataBase > Sphinx' 카테고리의 다른 글
| [Sphinx] 전체내용 검색하는법 (0) | 2019.06.11 |
|---|---|
| [Sphinx] 스핑크스 설치 및 환경설정 (0) | 2019.05.16 |
| [Sphinx] MySQL과 연동후 limit 1000 이상으로 하는법 (0) | 2019.05.16 |
| [Sphinx] select limit 1000 이상 출력하는법 (0) | 2019.05.16 |