[MySQL] Full-Text Search

12.7. 전문 (Full-Text) 검색 함수

 

 

12.7.1. 불리안 전문 검색

12.7.2. 쿼리 확장을 사용한 전문 검색

12.7.3. 전문 스톱워드 (Stopwords)

12.7.4. 전문 제약 사항

12.7.5. 올바른 MySQL 전문 검색 튜닝

 

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

 

search_modifier: { IN BOOLEAN MODE | WITH QUERY EXPANSION }

 

MySQL은 전문 인덱싱과 검색을 지원한다:

• MySQL에서 전문 인덱스는 타입 FULLTEXT의 인덱스이다.

• 전문 인덱스는 MyISAM 테이블을 사용해서만 사용될 수 있으며, 또한 CHAR,VARCHAR, 또는 TEXT 컬럼용으로만 생성할 수가 있다.

• FULLTEXT 인덱스 정의는 테이블을 생성할 때 CREATE TABLE 명령문에 주어질 수 있거나, 또는 ALTER TABLE 또는 CREATE INDEX를 사용한 후에 추가될 수가 있다.

• 대형 데이터 셋의 경우에는, FULLTEXT 인덱스를 가지고 있지 않는 테이블에 데이터를 읽어 오는 것이 더 빠르다.

전문 검색은 MATCH() ... AGAINST 신텍스를 사용해서 실행된다. MATCH()은 콤마로 구분된 리스트를 가져오는데, 이 리스트는 검색할 컬럼들의 이름이 된다. AGAINST는 검색에 사용할 스트링을 가져오며, 실행할 검색의 타입이 어떤 것인지를 가리키는 모디파이어 (modifier)를 옵션으로 가져온다. 검색 스트링은 반드시 리터럴 스트링이어야 하며, 변수 또는 컬럼 이름은 될 수 없다. 전문 검색에는 세 가지 타입이 있다:

• 불리안 검색은 특별한 쿼리 언어 규칙을 사용해서 검색 스트링을 해석한다. 이 스트링은 검색에 사용할 단어를 가진다. 또한, 이 스트링에는 특정 단어가 매칭 열에 반드시 있어야 하거나 없어야 하는 것, 또는 가중치를 일반적인 것 보다 더 주거나 또는 덜 주는 것과 같은 요구 조건을 지정하는 연산자를 포함할 수 있다. “some” 또는 “then”과 같은 일반적인 단어는 스톱워드 (stopword)이며, 이런 단어가 검색 스트링에 존재할 경우에는 매치가 되지 않는다. IN BOOLEAN MODE 모디파이어는 불리안 검색을 지정한다. 보다 자세한 정보는, Section 12.7.1, “불리안 전문 검색”을 참조할 것.

• 자연어 검색은 검색 스트링을 자연어의 구문 (자연스러운 문장의 구문)처럼 해석한다.여기에는 특별한 연산자가 존재하지 않는다. 스톱워드 리스트는 적용된다. 또한, 열 중에 50% 이상에서 존재하는 단어들은 일반적인 것으로 간주되어서 매치를 하지 않게 된다. 어떠한 모디파이어도 제공되지 않을 경우의 전문 검색은 자연어 검색이다.

• 쿼리 확장 검색은 자연어 검색의 변형된 형태이다. 자연어 검색을 실행하기 위해 검색 스트링을 사용한다. 검색의 결과로 가장 연관성이 높은 열에서 나오는 단어들이 검색 스트링에 추가되며 검색이 재차 실행된다. 쿼리는 두 번째 검색에서 그 열들을 리턴한다.WITH QUERY EXPANSION 모디파이어는 쿼리 확장 검색을 지정한다. 보다 자세한 정보는, Section 12.7.2, “쿼리 확장을 사용한 전문”을 참조하기 바란다.

전문 검색의 제약 사항에 대해서는 Section 12.7.4, “전문검색 제약 사항”을 참조하기 바란다.

 

mysql> CREATE TABLE articles (

    ->   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

    ->   title VARCHAR(200),

    ->   body TEXT,

    ->   FULLTEXT (title,body)

    -> );

Query OK, 0 rows affected (0.00 sec)

 

mysql> INSERT INTO articles (title,body) VALUES

    -> ('MySQL Tutorial','DBMS stands for DataBase ...'),

    -> ('How To Use MySQL Well','After you went through a ...'),

    -> ('Optimizing MySQL','In this tutorial we will show ...'),

    -> ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),

    -> ('MySQL vs. YourSQL','In the following database comparison ...'),

    -> ('MySQL Security','When configured properly, MySQL ...');

Query OK, 6 rows affected (0.00 sec)

Records: 6  Duplicates: 0  Warnings: 0

 

mysql> SELECT * FROM articles

    -> WHERE MATCH (title,body) AGAINST ('database');

+----+-------------------+------------------------------------------+

| id | title             | body                                     |

+----+-------------------+------------------------------------------+

|  5 | MySQL vs. YourSQL | In the following database comparison ... |

|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |

+----+-------------------+------------------------------------------+

2 rows in set (0.00 sec)

 

MATCH() 함수는 텍스트 콜렉션 (text collection)에 대응해서 특정 스트링에 대한 자연어 검색을 실행한다.

콜렉션이란 FULLTEXT 인덱스에 포함되어 있는 하나 이상의 컬럼 셋이다. 검색 스트링은AGAINST()에 대한 인수 형태로 주어진다. 테이블에 있는 각각의 열에 대해서는, MATCH()은 연관도 (relevance value) 리턴한다; 즉, 검색 스트링과 MATCH() 리스트에 명명되어 있는 컬럼내의 열에 있는 텍스트간의 유사성을 측정한다.

 

디폴트로는, 검색은 문자의 크기를 구분하지 않고 실행된다. 하지만, 인덱스된 컬럼에 대한 바이너리 콜레션을 사용하면 문자 크기를 구분하는 전문 검색을 실행할 수가 있다. 예를 들면,latin1 문자 셋을 사용하는 컬럼은 전문 검색을 할 때 문자 크기를 구분하기 위해서 latin1_bin의 콜레션을 할당 받을 수가 있다.

 

위에 나와 있는 예문과 같이, MATCH() 함수가 WHERE 구문에서 사용되게 되면, 리턴되는 열은 연관도가 높은 순서대로 자동 정렬이 되어 나오게 된다. 연관도는 음수가 아닌 부동 소수점 숫자이다. 제로 연관도는 유사성이 없다는 것을 의미한다. 연관도는 열에 있는 단어의 수, 열에 있는 유니크한 단어의 수, 콜렉션에 있는 전체 단어의 수, 그리고 특정 단어를 가지고 있는 열의 숫자등을 기반으로 계산된다.

 

자연어 전문 검색의 경우, MATCH() 함수에 명명된 컬럼은 여러분이 사용하는 테이블에 있는FULLTEXT 인덱스에 포함되어 있는 컬럼과 같아야 하는 제약 조건이 있다. 위의 예문에 나와 있는 쿼리의 경우, MATCH() 함수에 명명된 컬럼 (title 및 body)이 article 테이블의 FULLTEXT인덱스 정의문에서 명명된 컬럼 이름과 같다는 점을 기억하자. 만일 여러분이 title 또는 body를 개별적으로 검색하고자 한다면, 여러분은 각각의 컬럼에 대해서 별도의 FULLTEXT 인덱스를 생성해야 할 것이다.

 

쿼리 확장을 사용한 검색 또는 불리안 검색을 실행하는 것 역시 가능하다. 이러한 검색 타입은Section 12.7.1, “불리안 전문 검색”, 그리고 Section 12.7.2, “쿼리 확장을 사용한 전문 검색”을 참고하기 바란다.

 

위의 예문은 연관도가 낮은 순서대로 열이 리턴되는 곳에서 MATCH() 함수를 어떻게 사용하는지를 보여주는 기본적인 것이다. 다음에 나오는 예문은 명확하게 연관도를 추출하는 방법에 대해서 보여주고 있다. 리턴되는 열은 순서대로 정렬되지 않는데, 그 이유는 SELECT 명령문이WHERE 또는 ORDER BY 구문중의 어느 것도 포함하고 있지 않기 때문이다:

 

mysql> SELECT id, MATCH (title,body) AGAINST ('Tutorial')

    -> FROM articles;

+----+-----------------------------------------+

| id | MATCH (title,body) AGAINST ('Tutorial') |

+----+-----------------------------------------+

|  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') AS score

    -> FROM articles WHERE MATCH (title,body) AGAINST

    -> ('Security implications of running MySQL as root');

+----+-------------------------------------+-----------------+

| 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)

 

MySQL의 FULLTEXT 실행은 모든 실제 문자 (문자, 숫자, 그리고 언더 스코어 문자) 시퀀스를 하나의 단어로 인식한다. 이러한 시퀀스에는 어퍼스크로피 (apostrophe) (‘'’)도 포함되지만, 하나의 열에 하나 이상은 포함되지 않는다. 이것은 aaa'bbb는 하나의 단어로 인식되지만,aaa''bbb는 두 개의 단어로 인식된다는 것을 의미한다. 단어의 처음 또는 마지막에 나오는 어퍼스트로피는 FULLTEXT 파서 (parser)가 잘라낸다; 'aaa'bbb'는 aaa'bbb 형태가 된다.

 

FULLTEXT 파서는 특정 구분 문자 (delimiter)를 찾아서 단어가 시작되는 곳과 끝나는 곳을 결정한다; 예를 들면, ‘ ’ (space), ‘,’ (comma), and ‘.’ (period). 만일 단어들이 구분 문자에 의해 분리되어 있지 않으면, FULLTEXT 파서는 단어의 시작과 끝을 알아낼 수가 없게 된다. 특정 언어 (예를 들면, 중국어)에 있는 단어 또는 별도의 인덱스된 용어를 FULLTEXT 인덱스에 추가하기 위해서는, ‘"’와 같은 임의의 구분 문자를 사용해서 각각의 단어를 미리 분리하는 작업을 해 두어야 한다.

 

전문 검색에서는 몇 가지 단어들이 무시가 된다:

• 너무 짧은 단어는 모두 무시가 된다. 전문 검색이 될 수 있는 디폴트 최소 길이는 4개 문자이다.

• 스톱워드 리스트에 있는 단어들은 무시가 된다. 스톱워드는 A stopword is a word such as “the” 또는 “some”과 같은 단어이며, 이러한 단어들은 너무 일반적인 것이기 때문에 의미론적으로 제로 값을 가지게 된다. 빌트-인 스톱워드 리스트가 있기는 하지만, 사용자 지정 리스토로 대체를 할 수가 있다.

디폴트 스톱워드 리스트는 Section 12.7.3, “전문 스톱워드”에 있다. 디폴트 최소 단어 길이 및 스톱워드 리스트는 Section 12.7.5, “ 올바른 MySQL 전문 검색 튜닝”에서 설명하는 방법으로 수정할 수가 있다.

 

콜렉션 및 쿼리 안에 있는 모든 유효한 단어들은 콜렉션 또는 쿼리에서의 중요도에 따라서 가중치를 가지게 된다. 결론적으로, 하나의 단어가 여러 열에 등장하게 되면 낮은 가중치를 가지게 되는데, 그 이유는 그러한 단어는 특정 콜렉션에서 낮은 의미를 가지기 때문이다. 반대로, 만일 어떤 단어가 희귀하다면, 그 단어는 높은 가중치를 가지게 된다. 어떤 단어에 대한 가중치는 열의 연관도를 계산하기 위해 결합된 것이다.

 

이러한 기법은 대형 콜렉션에서도 올바르게 동작을 한다 (주의 깊게 튜닝이 되어야 함). 매우 작은 테이블의 경우, 단어 분포도는 자신의 의미 값을 적절하게 반영하지 못하며, 이러한 모델은 엉뚱한 결과를 만들기도 한다. 예를 들면, 비록 “MySQL”이라는 단어가 위에 나와 있는 articles테이블의 모든 열에 나오기는 하지만, 이 단어에 대한 검색은 아무런 결과도 리턴하지 않게 된다:

 

mysql> SELECT * FROM articles

    -> WHERE MATCH (title,body) AGAINST ('MySQL');

Empty set (0.00 sec)

 

검색 결과가 위와 같이 나오는 이유는, 단어 “MySQL”이 최소 50%의 열에 나오기 때문이다. 따라서, 이와 같은 경우에는 이 단어를 스톱워드로 다루는 것이 효과적이다. 대형 데이터 셋의 경우, 이렇게 하는 것이 가장 권장할 수 있는 방식이다.

 

테이블에 있는 열의 50%와 매치가 되는 단어는 관련된 서류에 위치해 있을 가능성이 덜하다. 사실, 이것들은 연관성이 없는 서류에서 더 많이 발견이 된다. 인터넷 검색 엔진을 사용해서 인터넷에서 무언가를 찾고자 할 때 이러한 일이 너무 자주 발생한다는 것을 우리는 잘 알고 있다. 이러한 이유로 인해, 이러한 단어를 포함하고 있는 열은 그 단어가 나오는 특정 데이터 셋에 대해서 낮은 가중치를 할당 받게 되는 것이다. 주어진 단어는 어떤 데이터 셋에서는 50% 이상 나올 수도 있지만 다른 곳에서는 그렇지 않을 수도 있다.

 

여러분이 전문 검색이 어떻게 동작을 하는지 보고자 할 때 50%의 쓰레드 홀드 (threshold)는 중요한 의미를 갖는다: 만일 여러분이 테이블을 하나 생성한 다음에 하나 또는 두개의 텍스트 열만 그 테이블에 삽입한다면, 텍스트에 나오는 모든 단어들은 최소 50% 이상의 열에 존재하게 된다. 그 결과, 어떠한 검색 결과도 얻을 수가 없게 된다. 최소 3개의 열을 삽입해야 한다는 점을 알아두기 바라며, 가능하면 많은 열을 삽입하도록 한다. Section 12.7.1, “불리안 전문 검색”을 참조할 것.