Database

[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, “불리안 전문 검색을 참조할 것.

관련자료

등록된 댓글이 없습니다.
Today's proverb
어떤 기쁨을 얻기보다 얻기 위해 일하는 것보다 책임을 다하기 위해 일하는 것뿐이다.