📔설명
전문 검색, 공간 검색을 알아보자
🌰전문 검색
전문 검색(Full-text Search) : MySQL 서버가 용량이 큰 문서를 단어 수준으로 잘게 쪼개어 문서 검색을 하게 해주는 기능
n-gram 파서: 특정 길이의조각(Token)으로 인덱싱
1. 전문 검색 인덱스의 생성과 검색
토큰 분리 방법
형태소 분석(서구권은어근 분석) : 문장의공백같은 띄어쓰기 단위로 단어 분리 후, 각 단어의조사를 제거해명사또는어근을 찾아 인덱싱
=> MySQL에서는 구현Xn-gram 파서:공백과 같은 띄어쓰기 단위로 단어를 분리 후, 해당 단어를n길이만큼 쪼개서 인덱싱
인덱스 생성할 때 WITH PARSER ngram 옵션을 반드시 추가해야 함
create table tb_bi_gram(
id bigint not null auto_increment,
title varchar(100),
body TEXT,
primary key(id),
FULLTEXT index fx_msg(title,body) with parser ngram
);insert into tb_bi_gram values(null, 'Real MySQL',
'이 책은 지금까지의 매뉴얼 번역이나 단편적인 지식 수준을 벗어나 저자와 다른 많은 MySQL 전문가의...');select count(*) from tb_bi_gram
where match(title,body) against('단편' in boolean mode);
# 결과
# 1n-gram 전문 인덱스 규칙
- 검색어 길이가
ngram_token_size보다작은경우 :검색 불가능 - 검색어 길이가
ngram_token_size보다크거나 같은경우 :검색 가능
사이즈보다 큰 4글자 검색시, 해당 글자를 인덱싱할 때와 동일하게 토큰을 잘라냄
2. 전문 검색 쿼리 모드
자연어 검색 모드와 불리언 검색 모드 지원
=> 지정 X시 자연어 검색 모드 사용
자연어 검색(NATURAL LANGUAGE MODE)
truncate table tb_bi_gram;
insert into tb_bi_gram values
(null, 'Oracle','Oracle is database'),
(null, 'MySQL','MySQL is database'),
(null, 'MySQL article','MySQL is best open source dbms'),
(null, 'Oracle article','Oracle is best commercial dbms'),
(null, 'MySQL Manual','MySQL manual is true guide for MySQL');select id, title, body
from tb_bi_gram
where match(title, body) against ('MySQL' in natural language mode);
자연어 문장 검색 가능
불리언 검색(BOOLEAN MODE)
쿼리에 사용되는 검색어의 존재 여부에 대한 논리적 연산 가능
select id, title, body
from tb_bi_gram
where match(title, body) against ('+MySQL -manual' in boolean mode);
""로 감싸면 하나의 단어인 것처럼 취급
=> ex. +"MySQL man" 으로 검색시 MySQL 단어 뒤에 man 이라는 단어가 나오면 일치
+나 - 를 사용하지 않으면, 검색어에 포함된 단어 중 아무거나 하나라도 있으면 일치
=> ex. MySQL doc 인 경우 MySQL과 doc 단어 중 하나라도 일치하면 결과 반환
검색어 확장(QUERY EXPANSION)
사용자가 쿼리에 사용한 검색어로 검색된 결과에서 공통으로 발견되는 단어들을 모아서 다시 한번 더 검색 수행하는 방식
select *
from tb_bi_gram
where match(title, body) against ('database' with query expansion);
database와 연관 있는 단어를 뽑아서 다시 전문 검색 쿼리 실행
3. 전문 검색 인덱스 디버깅
전문 검색 쿼리 오류의 원인을 쉽게 찾기 위해 전문 검색 인덱스 디버깅 기능 제공
set global innodb_ft_aux_table='employees/tb_bi_gram';
#information_schema DB의 다음 테이블을 통해 전문 검색 인덱스 저장 및 관리 볼 수 있음#전문 검색 인덱스 설정 내용 보여줌
select * from information_schema.innodb_ft_config;
#전문 검색 인덱스가 가지고 있는 인덱스 엔트리 목록
#전문 검색 인덱스의 각 엔트리는 토큰들이 어떤 레코드에 몇 번이나 사용됐는지, 레코드별로 문자 위치가 어디인지 등 관리
select * from information_schema.innodb_ft_index_table;#테이블에 레코드가 새롭게 insert되면 메모리에 임시 저장 후 디스크 파일로 저장
select * from information_schema.innodb_ft_index_cache;#ft_being_deleted : 현재 전문 검색 인덱스에서 삭제되고 있는지
# 어떤 레코드가 삭제됐는지 알려줌
select * from information_schema.innodb_ft_deleted;🥜공간 검색
1. 용어 설명
OGC(Open Geospatial Consortium): 위치 기반 데이터 표준 수립 단체OpenGIS: 지리 정보 시스템(GIS)의 표준SRS,GCS,PCS:SRS는좌표계로, GCS와 PCS로 나뉠 수 있는데GCS는위도와경도로 표시되는 것이고PCS는위치 데이터를2차원 평면인 종이에 어떻게 표현할지를 정의SRID: 특정 SRS를 지칭하는 고유 번호WKT와WKB: WKT는 사람 눈으로 쉽게 확인할 수 있는 텍스트 포맷, WKB는 이진 포맷 저장 표준MBR과R-Tree:MBR은 어떤 도형을 감싸는 최소의 사각 상자,R-Tree는 도형의 포함관계를 이용해 만들어진 인덱스
2. SRS(Spatial Reference System)
SRS(좌표계)는 GCS(지리 좌표계)와 PCS(투영 좌표계)로 구분
#MySQL 서버에서 지원하는 SRS 정보
desc information_schema.ST_SPATIAL_REFERENCE_SYSTEMS;
select * from information_schema.ST_SPATIAL_REFERENCE_SYSTEMS where srs_id=4326 \G
GEOGS는 지리 좌표계(GCS), PROJCS는 투영 좌표계(PCS) 의미
GCS는 point(위도 경도)로 표시
select * from information_schema.ST_SPATIAL_REFERENCE_SYSTEMS where srs_id=3857 \G
단위는 meter이며, point(경도 위도) 순서로 명시
#st_pointfromtext() 함수 : 공간 데이터 생성 함수
#점의 위치, 점이 사용하는 SRID 값
select st_distance(st_pointfromtext('point(0 0)', 0),
st_pointfromtext('point(1 1)', 0)) as distance;
SRID를 0으로 하면, 단순히 피타고라스 정리에 의한 수식으로 계산된 값
3. 투영 좌표계와 평면 좌표계
평면 좌표계 : 투영 좌표계나 지리 좌표계에 속하지 않음
=> SRID=0
=> X축과 Y축의 값이 제한을 가지지 않음
=> 단위를 가지지 않음
#평면 좌표계
st_distance(st_pointfromtext~~)
#투영 좌표계
st_distance(st_geomfromtext(~~))4. 지리 좌표계
지리 좌표계 데이터 관리
#공간 인덱스 칼럼은 반드시 not null
create table sphere_coord(
id int not null auto_increment,
name varchar(20),
location point not null srid 4326, --//wgs84 좌표계
primary key(id),
spatial index sx_location(location)
);insert into sphere_coord values
(null, '서울숲', st_pointfromtext('POINT(37.544738 127.039074)', 4326)),
(null, '한양대학교', st_pointfromtext('POINT(37.555363 127.044521)', 4326)),
(null, '덕수궁', st_pointfromtext('POINT(37.565922 126.975164)', 4326)),
(null, '남산', st_pointfromtext('POINT(37.548930 126.993945)', 4326));ST_Distance_Sphere() 함수를 이용해 두 점의 거리를 구함
=> 단위 : 미터
select id, name, st_astext(location) as location,
round(st_distance_sphere(location, st_pointfromtext('POINT(37.547027 127.047337)', 4326))) as distnace_meters
from sphere_coord
where st_distance_sphere(location, st_pointfromtext('POINT(37.547027 127.047337)', 4326))<1000;
# 위 좌표는 뚝섬역 좌표
위 함수는 인덱스 사용 불가 이므로, 차선책으로 st_within() 함수 사용
st_within() 함수 : 2개의 공간 데이터를 파라미터로 입력
=> 첫 번째 파라미터로 주어진 공간 데이터가 두 번째 파라미터의 공간 데이터에 포함되는지 체크
지리 좌표계 주의 사항
정확성 주의 사항
st_contains() 함수가 정확하지 않은 결과를 반환하기도 함
=> 해당 함수로 인덱스를 활용해 검색 후, 그 결과를 다시 st_distance_sphere() 함수로 걸러내어 정확도 높임
성능 주의 사항
지리 좌표계 데이터는 투영 좌표계보다 느린 성능을 보임
좌표계 반환
ST_Transform() 함수를 이용해 SRID 간에 좌푯값 변환 가능
