MySQL Full-Text Search 1

Full-Text Search Functions

https://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html

MATCH (col1,col2,...) AGAINST (expr [search_modifier])

search_modifier:

  {

       IN NATURAL LANGUAGE MODE

     | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION

     | IN BOOLEAN MODE

     | WITH QUERY EXPANSION

  }

MySQL 은 풀텍스트 인덱싱과 서칭을 지원한다.

- MySQL의 풀텍스트 인덱스는 FULLTEXT 형식의 인덱스이다.

- 풀텍스트 인덱스는 MyISAM 테이블에서만 사용할 수 있다. (5.6 이후엔 InnoDB에서도 사용가능). 풀텍스트 인덱트는 CHAR, VARCHAR, TEXT 열에만 생성할 수 있음

- FULLTEXT 인덱스 정의는 테이블 생성시 CREATE TABLE 또는 ALTER TABLE이나 CREATE INDEX로 이후에 추가할 수 있음

- 큰 데이터 셋에 대해, FULLTEXT 인덱스가 없는 테이블의 데이터를 읽는게 빠르며 인덱스를 생성하고 데이터를 FULLTEXT인덱스가 존재하는 테이블에 로드

풀텍스트 검색은 MATCH() ... AGAINST 문법을 사용해 수행한다. MATCH() 는 콤마로 구별된 리스트를 받으며 검색할 컬럼의 이름이다. AGAINST 는 검색할 문자열이며 선택적 수정자를 통해 수해할 검색의 형식을 지정한다. 검색 문자열은 문자열 값이어야 하며 질의 수행중에 상수여야 한다. 이 규칙이 어겨지면 결과가 달라질 수 있다.

풀텍스트 검색의 세 가지 형식이 있다.

- 자연어 검색은 검색 문자열을 자연적언어 내의 문장으로 해석한다. 더블 쿼테이션(") 외 특별한 연산자는 없다. 스탑워드 리스트가 적용된다. 추가적으로 단어는 50% 이상이 존재해야 매치된다.

풀텍스트 검색은 자연어 검색으로서 IN NATURAL LANGUAGE MODE가 지정되거나 아무런 지정자가 없으면 쓰여진다. 더 자세한 사항은 다음 섹션을 확인

- 불린 검색은 특수한 쿼리 언어의 규칙을 사용해 문자열을 검색한다. 문자열은 검색할 단어를 포함하고, 단어가 반드시 보여진 것이든지 없는지, 보통보다 높게 또는 낮게 가중치 설정을 할 것인지 지정한다. "some" "then" 은 스탑워드로서 검색 문자열내에 있으면 매치되지 않는다. IN BOOLEAN MODE 는 불린 서치를 나타낸다. 더 자세한 사항은 다음 불린풀텍스트 검색을 살펴본다.

- 질의 확장 검색은 자연어 검색의 수정본이다. 검색어는 자연어 검색을 수행한다. 연관된 행으로 부터 얻은 단어로 검색 문자열에 추가되어 검색이 다시 이뤄진다. 두번째 검색에서 행들이 결과로 반환된다. IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION OR WITH QUERY EXPANSION 연산자는 쿼리 확장검색을 지정한다. 더 자세한 사항은 쿼리 확장으로 풀텍스트 검색 을 살펴본다.

풀텍스트 검색 제한은 풀텍스트 한계 부분에서 다룬다.

myisam_ftdump 유틸리티로 풀텍스트 인덱스의 컨텐트를 덤프하는데 사용할 수 있다. 이는 풀텍스트 질의를 디버깅하는데 유용하다. myisam_ftdump - 풀텍스트 인덱스 정보 표시하기 를 살펴본다.

1. 자연어 풀텍스트 검색

기본 또는 IN NATURAL LANGUAGE MODE 수정자를 통해 MATCH()함수는 텍스트 컬렉션에 대해 문자열을 찾는데 자연어 검색을 수행한다. 콜렉션은 FULLTEXT 인덱스 내에 포함된 하나 이상의 컬럼 집합이다. 검색 문자열은 AGAINST() 에 매개변수로 건내어진다. 테이블의 각 로우에 대해 MATCH() 는 연관 값을 반환하고 유사도를 나타낼 수 있다.

기본적으로, 검색은 대소문자 구별을하지 않는다. 그러나 인덱스된 컬럽에 대해 이진 콜레이션을 사용하면 대소문자 구별을 할 수 있다. 예를 들어 컬럼이 라틴1 문자 셋을 사용하면 latin1_bin 을 할당해 풀텍스트 검색에 대소문자 구별을 하게 할 수 있다.

MATCH() 가 WHERE 문에 있으면 로우는 높은 유사도에 따라 자동적으로 정렬된다. 유사도 값은 양수의 부동 소수점 값이다. 0은 유사하지 않음을 나타낸다. 유사도는 행의 단어의 숫자, 행의 특이 단어 숫자, 콜렉션의 전체 단어 갯수 그리고 특정 단어를 포함하는 행의 숫자로 처리된다.

단순 매치 갯수는 다음과 같이 쿼리를 쓸 수 있다.

mysql> SELECT COUNT(*) FROM articles    -> WHERE MATCH (title,body)    -> AGAINST ('database' IN NATURAL LANGUAGE MODE);+----------+| COUNT(*) |+----------+|        2 |+----------+1 row in set (0.00 sec)

그러나 다음으로 쿼리를 다시 작성해 다른 방법을 찾을 수 있다.

mysql> SELECT    -> COUNT(IF(MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE), 1, NULL))    -> AS count    -> FROM articles;+-------+| count |+-------+|     2 |+-------+1 row in set (0.03 sec)

첫 번째 쿼리는 연관성으로 정렬하지만 두번째는 그렇지 않다. 그러나, 두 번째 쿼리는 풀테이블 스캔을 하고 첫번째는 그렇지 않다. 로우가 몇개 되지 않으면 첫 번째가 그렇지 않으면 더 빠르지만 많은 로우를 읽어들이니 두 번째가 더 빠를 수도 있다.

자연어 풀텍스트 검색에서, 컬럼이 MATCH() 함수에 있어야 함이 요구사항이며 해당 컬럼은 테이블내의 FULLTEXT 인덱스로 포함된다. 쿼리를 진행하려면 MATCH() 함수의 컬럼이름을 기억하며 해당 이름은 테이블의 FULLTEXT 인덱스와 일치한다. 만약 title과 body를 구별해서 검색하려면 각 컬럼에 대해 FULLTEXT 인덱스를 생성한다.

불린 검색이나 쿼리 확장으로 검색도 같다. 

풀텍스트 검색은 인덱스를 사용해 MATCH() 내의 단일 테이블에서만 수행가능한데, 인덱스는 다중 테이블로 확장되지 못하기 때문이다. 불린 검색은 인덱스가 없어도 수행될 수 있으며 (느리다) 이 경우 다중 테이블의 이름들을 사용할 수 있다.

다음 예시는 기본 MATCH()함수 사용법을 보여준다. 다음 예시는 어떻게 명시적으로 유사도를 얻는지 보여준다. 반환된 행은 정렬되어 있지 않은데 SELECT문에서 WHERE 나 ORDER BY 를 포함하지 않기 때문이다.

mysql> SELECT id, MATCH (title,body)    -> AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) AS score    -> FROM articles;+----+------------------+| id | score            |+----+------------------+|  1 | 0.65545833110809 ||  2 |                0 ||  3 | 0.66266459226608 ||  4 |                0 ||  5 |                0 ||  6 |                0 |+----+------------------+6 rows in set (0.00 sec)

다음 예시는 더 복잡하다. 쿼리는 유사도 값을 반환하고 정렬한다. 이 결과를 얻으려면 MATCH() 를 두 번써야 하는데 한번은 SELECT리스트에 그리고 WHERE 문에서 쓰인다. 이 것이 추가적인 오버헤드를 발생시키지는 않는데, MySQL 옵티마이져가 두 MATCH() 콜이 동일함을 알고 한번만 풀텍스트 검색 코드가 호출되기 때문이다. 

mysql> SELECT id, body, MATCH (title,body) AGAINST    -> ('Security implications of running MySQL as root'    -> IN NATURAL LANGUAGE MODE) AS score    -> FROM articles WHERE MATCH (title,body) AGAINST    -> ('Security implications of running MySQL as root'    -> IN NATURAL LANGUAGE MODE);+----+-------------------------------------+-----------------+| id | body                                | score           |+----+-------------------------------------+-----------------+|  4 | 1. Never run mysqld as root. 2. ... | 1.5219271183014 ||  6 | When configured properly, MySQL ... | 1.3114095926285 |+----+-------------------------------------+-----------------+2 rows in set (0.00 sec)

더블 쿼테이션 " 를 사용하여 지정된 문장은 문장 그대로 포함된 것이 보여진다. 풀텍스트 엔진은 문장을 단어로 구별하고 단어에 대한 풀텍스트 검색을 수행한다. 단어가 아닌 문자는 정확히 매치될 필요가 없다: 문장 검색은 같은 순서로 된 단어들이 매칭되어야 한다. 예를 들어 test phrase 는 "test, phrase"와 매치된다. 만약 phrase 가 인덱스에 있는 단어와 매치되지 않으면 빈결과이다. 예를 들어, 만약 모든 단어가 스탑워드이거나 인덱스 단어의 최소 길이보다 짧으면 빈결과다

MySQL 풀텍스트 구현은 어떤 단어의 연속 (문자, 숫자, 언더스코어) 는 단어로 간주한다. 시퀀스는 어포스트로피를 포함할 수 있지만, 열내에서 하나이상은 아니다. 이 의미는 aaa'bbb 는 한 단어로 간주하지만 aaa''bbb는 두 단어로 간주한다. 시작과 끝의 어포스트로피는 풀 텍스트 파서로 스트립되는데 'aaa'bbb' 는 aaa'bbb 로 해석된다.

풀텍스트 파서는 단어의 시작과 끝은 델리미터 문자를 찾음으로서 끝맺는다. 예를 들어, 스페이스, 콤마, 그리고 . 이다. 만약 워드가 델리미터로 구별되지 않는다면 (중국어와 같이) 풀텍스트 파서는 단어의 시작과 끝을 구별할 수 없다. 단어를 추가하고 다른 인덱스 텀을 처리하려면 반드시 임의 델리미터에 대한 전처리를 수행해야 한다.

빌트인 풀텍스트 파서를 대체하는 플러그인을 작성할 수 도 있다. 자세히는, MySQL 플러그인 API를 살펴본다. 파서 플러그인 소스코드에 대해서는 MySQL 소스 배포에서 plugin/fulltext 디렉토리를 살펴본다.

몇몇 단어는 풀텍스트 검색에서 무시된다.

- 너무 짧은 단어는 무시된다. 단어의 기본 길이는 4이다.

- 스탑워드 내의 단어는 무시된다. 스탑워드는 the, some 과 같은 의미가 없는 것이다. 빌트인 스탑워드 리스트가 있으며 사용자 정의 리스트로 바꿀 수 있다.

콜렉션내의 알맞은 단어는 콜렉션과 쿼리내에서 충분성에 기초한 가중치이다. 결과적으로, 단어가 많은 곳에 있으면 낮은 가중치가 되는데, 특정 콜렉션 내의 낮은 시맨틱 값을 가지기 때문이다. 반대로 만약 단어가 흔하지 않다면 높은 가중치가 된다. 단어의 가중치는 행의 유사도를 계산하도록 결합된다.

이런 기법은 큰 콜렉션에서 최상으로 작동한다. (실제로, 이런 식으로 튜닝되었다.) 작은 테이블에 대해서는 단어 분보가 적합한 시맨틱 값을 반영하지 못하고 이 모델은 간혹 잘못된 결과를 보여주기도 한다. 예를 들어 "MySQL" 단어가 있지만 검색 결과에는 없다.

mysql> SELECT * FROM articles    -> WHERE MATCH (title,body)    -> AGAINST ('MySQL' IN NATURAL LANGUAGE MODE);Empty set (0.00 sec)

결과가 빈것은 "MySQL" 이 적어도 행의 50%에 존재하기 때문이다. 그래서 스탑워드로 간주되 버린 것이다. 대용량 데이터 세트의 경우 이것은 가장 바람직한 동작입니다. 자연어 쿼리는 1GB 테이블에서 두 번째 행을 반환하지 않아야합니다. 소규모 데이터 세트의 경우 바람직하지 않을 수 있습니다.

표의 행의 절반과 일치하는 단어는 관련 문서를 찾을 가능성이 적습니다. 사실, 관련성이없는 많은 문서를 찾을 가능성이 큽니다. 우리는 검색 엔진으로 인터넷에서 무언가를 찾으려 할 때이 일이 너무 자주 일어난다는 것을 알고 있습니다. 단어를 포함하는 행에 발생하는 특정 데이터 세트에 대한 낮은 의미 론적 값이 할당되는 것은 이러한 이유 때문입니다. 주어진 단어는 한 데이터 집합에서 50 % 임계 값에 도달 할 수 있지만 다른 데이터 집합에서는 도달 할 수 없습니다.

50 % 임계 값은 전체 텍스트 검색을 처음 시도 할 때 중요한 의미를 갖습니다. 예를 들어 표를 만들고 하나 또는 두 개의 텍스트 행을 삽입하면 텍스트의 모든 단어가 50 % 행의. 결과적으로 어떤 검색도 결과를 반환하지 않습니다. 적어도 세 행을 삽입해야하며, 더 많은 행을 삽입하는 것이 좋습니다. 50 % 제한을 우회해야하는 사용자는 부울 검색 모드를 사용할 수 있습니다. Section 12.9.2, "Boolean Full-Text Searches"를 보라.