14.2.13.3 FULLTEXT 인덱스

텍스트 기반의 컬럼 ( CHAR , VARCHAR , 또는 TEXT 컬럼)에 생성 된 인덱스의 유형입니다. 이것을 사용하면 중지 단어로 정의되는 어떤 단어가 생략 된 것으로,이 컬럼에 포함 된 데이터에서 InnoDB 의 쿼리 및 DML 작업의 속도를 높일 때 유용합니다.

FULLTEXT 인덱스는 CREATE TABLE 문의 일부로 정의하거나 나중에 ALTER TABLE 또는 CREATE INDEX 를 사용하여 추가 할 수 있습니다.

전문 검색은 MATCH() ... AGAINST 구문을 사용하여 실행됩니다. 사용법은 섹션 12.9 "전체 텍스트 검색 함수" 를 참조하십시오.

전체 인덱스 디자인

InnoDB 의 FULLTEXT 인덱스에서는 "전치 인덱스"디자인이 사용되고 있습니다. 전치 인덱스에는 단어 목록과 단어마다 그 단어가 문서의 목록이 포함됩니다. 근접 검색을 지원하기 위해 각 단어의 위치 정보도 문자 오프셋으로 저장됩니다.

전체 텍스트 인덱싱 테이블

다음의 예와 같이, InnoDB 의 FULLTEXT 인덱스에 대해 인덱스 테이블 세트가 생성됩니다.

CREATE TABLE opening_lines (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
opening_line TEXT(500),
author VARCHAR(200),
title VARCHAR(200),
FULLTEXT idx (opening_line)
) ENGINE=InnoDB;

mysql> SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_SYS_TABLES 
WHERE name LIKE 'test/%';
+----------+----------------------------------------------------+-------+
| table_id | name                                               | space |
+----------+----------------------------------------------------+-------+
|      333 | test/FTS_0000000000000147_00000000000001c9_INDEX_1 |   289 |
|      334 | test/FTS_0000000000000147_00000000000001c9_INDEX_2 |   290 |
|      335 | test/FTS_0000000000000147_00000000000001c9_INDEX_3 |   291 |
|      336 | test/FTS_0000000000000147_00000000000001c9_INDEX_4 |   292 |
|      337 | test/FTS_0000000000000147_00000000000001c9_INDEX_5 |   293 |
|      338 | test/FTS_0000000000000147_00000000000001c9_INDEX_6 |   294 |
|      330 | test/FTS_0000000000000147_BEING_DELETED            |   286 |
|      331 | test/FTS_0000000000000147_BEING_DELETED_CACHE      |   287 |
|      332 | test/FTS_0000000000000147_CONFIG                   |   288 |
|      328 | test/FTS_0000000000000147_DELETED                  |   284 |
|      329 | test/FTS_0000000000000147_DELETED_CACHE            |   285 |
|      327 | test/opening_lines                                 |   283 |
+----------+----------------------------------------------------+-------+

처음 6 개의 테이블은 전치 인덱스를 나타내며 "근접 검색 인덱스 테이블 '라고합니다. 수신 문서가 토큰 화 된 각 단어가 위치 정보 및 문서 ID ( DOC_ID )와 함께 인덱스 테이블에 삽입됩니다. 단어는 완전히 정렬 된 후 단어의 첫 문자 세트 가중치에 따라 6 개의 인덱스 테이블간에 분할됩니다.

전치 인덱스는 인덱스의 병렬 생성을 지원하기 위해 6 개의 보조 인덱스 테이블에 "분할"됩니다. 기본적으로 2 개의 스레드를 사용하여 단어 및 관련 데이터 토큰 화, 정렬 및 인덱스 테이블에 삽입이 수행됩니다. 스레드 수는 innodb_ft_sort_pll_degree 옵션을 사용하여 구성 할 수 있습니다. 큰 테이블에 FULLTEXT 인덱스를 만들 때 스레드의 수를 많이하는 것을 고려하십시오.

보조 인덱스 테이블 이름 앞에 FTS_ , 뒤에 INDEX_* 가 표시됩니다. 각 인덱스 테이블은 인덱스 테이블의 table_id 와 일치하는 인덱스 테이블 이름에 포함 된 16 진수 값에 의해 인덱싱 된 테이블에 연결됩니다. 예를 들어, test/opening_lines 테이블의 table_id 는 327 (16 진수 값은 0x147)입니다. 위의 예에서 나타난 바와 같이 16 진수의 「147」는 test/opening_lines 테이블과 연관된 인덱스 테이블의 이름에 표시됩니다.

보조 인덱스 이름에 표시되는 또 하나의 16 진수 값은 FULLTEXT 인덱스 index_id 입니다. 예를 들어, 보조 테이블 이름 test/FTS_0000000000000147_00000000000001c9_INDEX_1 는 16 진수 1c9 10 진수 값은 457입니다. opening_lines 테이블에 정의 된 인덱스 ( idx )는 INFORMATION_SCHEMA.INNODB_SYS_INDEXES 테이블에서이 값 (457)를 쿼리하여 확인할 수 있습니다.

mysql> SELECT index_id, name, table_id, space from INFORMATION_SCHEMA.INNODB_SYS_INDEXES 
  WHERE index_id=457;
+----------+------+----------+-------+
| index_id | name | table_id | space |
+----------+------+----------+-------+
|      457 | idx  |      327 |   283 |
+----------+------+----------+-------+

innodb_file_per_table 을 사용하면 인덱스 테이블이 독자적인 테이블 스페이스에 저장됩니다. innodb_file_per_table 을 해제하면 인덱스 테이블이 InnoDB 시스템 테이블 스페이스 (공간 0)에 저장됩니다.

위의 예제와 기타 인덱스 테이블은 FULLTEXT 인덱스 삭제 처리 및 내부 상태의 저장에 사용됩니다.

전체 인덱스 캐시

문서가 삽입되면, 토큰 화 된 각 단어 및 연관된 데이터가 FULLTEXT 인덱스에 삽입됩니다. 이 프로세스가 실행되면 작은 문서의 경우에도 보조 인덱스 테이블에 다수의 소규모 삽입이 발생합니다. 이로 인해 충돌 발생시 해당 테이블에 대한 병렬 액세스가 발생할 수 있습니다. 이 문제를 해결하기 위해 InnoDB 는 최근 삽입 된 행의 인덱스 테이블의 삽입을 일시적으로 캐시 위해 FULLTEXT 인덱스 캐시가 사용됩니다. 이 '인 메모리'캐시 구조는 캐시가 가득 찰 때까지 삽입이 유지되어 그 후 디스크 (보조 인덱스 테이블)에 배치 플래시됩니다. INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE 테이블에서 쿼리를 실행하면 최근 삽입 된 행의 토큰 화 된 데이터를 볼 수 있습니다.

캐시 및 배치 플래시 동작은 보조 인덱스 테이블에 빈번한 업데이트가 해결되지만, 부하가 높은 삽입 및 갱신시에 병렬 액세스 문제가 발생할 수 있습니다. 또한 배치 기술을 사용하면 동일한 단어에 삽입이 여러 번 발생하기도 방지하고 중복 항목도 최소화됩니다. 각 단어를 개별적으로 플래시 대신 같은 단어의 삽입이 병합 된 단일 항목으로 디스크로 플러시되기 때문에 보조 인덱스 테이블의 크기를 작게 유지하면서 삽입의 효율성이 개선됩니다 .

전체 인덱스 캐시의 크기를 (테이블 당) 구성하려면 innodb_ft_cache_size 변수가 사용됩니다. 이렇게하면 전체 텍스트 인덱싱 캐시가 플러시되는 빈도가 영향을받습니다. 특정 인스턴스에서 innodb_ft_total_cache_size 옵션을 사용하면 모든 테이블에 대응 한 글로벌 전문 인덱스 캐시의 크기 제한을 정의 할 수 있습니다.

전체 인덱스 캐시는 보조 인덱스 테이블과 같은 정보가 포함됩니다. 그러나 전체 인덱스 캐시는 최근 삽입 된 행의 토큰 화 된 데이터 만 캐시합니다. 이미 디스크 (전문 보조 테이블)에 플래시 된 데이터는 쿼리시에 전체 인덱스 캐시에 반환되지 않습니다. 보조 인덱스 테이블의 데이터는 직접 쿼리가 실행됩니다. 보조 인덱스 테이블에서의 결과는 전체 인덱스 캐시의 결과와 병합 된 후 반환됩니다.

InnoDB의 전체 문서 ID 및 FTS_DOC_ID 컬럼

InnoDB 에서는 전체 텍스트 인덱스의 단어를 그 단어가 문서 기록과지도 할 때 문서 ID ( DOC_ID )라는 고유의 문서 식별자가 사용됩니다. 이 매핑은 인덱싱 된 테이블에서 FTS_DOC_ID 컬럼이 필요합니다. FTS_DOC_ID 컬럼이 정의되어 있지 않은 경우는 전체 인덱스를 만들 때, InnoDB 에 의해 자동으로 숨겨진 FTS_DOC_ID 컬럼이 추가됩니다. 다음 예제에서는이 동작을 시연합니다.

다음 테이블 정의는 FTS_DOC_ID 열이 포함되어 있지 않습니다.

  CREATE TABLE opening_lines (
 id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
 opening_line TEXT (500)
 author VARCHAR (200)
 title VARCHAR (200)
 ) ENGINE = InnoDB; 

CREATE FULLTEXT INDEX 구문을 사용하여 테이블에 전체 텍스트 인덱스를 만들면 FTS_DOC_ID 컬럼이 추가되도록 InnoDB 테이블을 재구성하고있는 것을보고 경고를 반환합니다.

mysql> CREATE FULLTEXT INDEX idx ON opening_lines(opening_line);
Query OK, 0 rows affected, 1 warning (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------------+
| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning |  124 | InnoDB rebuilding table to add column FTS_DOC_ID |
+---------+------+--------------------------------------------------+

ALTER TABLE 을 사용하여 FTS_DOC_ID 컬럼이 존재하지 않는 테이블에 전체 텍스트 인덱싱을 추가 할 경우에도 동일한 경고가 반환됩니다. CREATE TABLE 의 실행시에 전체 텍스트 인덱싱을 만들 때 FTS_DOC_ID 컬럼을 정의하지 않으면 InnoDB 는 경고없이 숨겨진 FTS_DOC_ID 컬럼이 추가됩니다.

CREATE TABLE 의 실행시 FTS_DOC_ID 컬럼을 정의하려면 이미 데이터가로드되는 테이블에 전체 텍스트 인덱싱해야합니다. 데이터를로드하기 전에 테이블에 FTS_DOC_ID 컬럼이 정의되어있는 경우, 새로운 열이 추가되도록 테이블 및 인덱스를 재 구축 할 필요가 없습니다. CREATE FULLTEXT INDEX 의 성능에 관심이없는 경우는 InnoDB 에서 자동으로 생성되도록 FTS_DOC_ID 컬럼을 제외합니다. InnoDB 에 의해 FTS_DOC_ID_INDEX 라는 FTS_DOC_ID 컬럼에 고유 인덱스와 함께 숨겨진 FTS_DOC_ID 가 만들어집니다. 자신의 FTS_DOC_ID 컬럼을 만들려면 다음 예에서와 같이 열을 BIGINT UNSIGNED NOT NULL 로 정의하고 FTS_DOC_ID (모두 대문자)라는 이름을 붙입니다.

  CREATE TABLE opening_lines (
 FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
 opening_line TEXT (500)
 author VARCHAR (200)
 title VARCHAR (200)
 ) ENGINE = InnoDB; 

FTS_DOC_ID 컬럼을 사용자가 직접 정의하도록 결정한 경우 빈 값이나 중복 값을 피할 수 있도록 열을 관리 할 사용자의 책임입니다. FTS_DOC_ID 값은 다시 사용할 수 없습니다. 즉, FTS_DOC_ID 값은 계속 증가하고 있습니다.

옵션에서 FTS_DOC_ID 컬럼에 필요한 고유의 FTS_DOC_ID_INDEX (대문자)를 만들 수 있습니다.

  CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on opening_lines (FTS_DOC_ID); 

FTS_DOC_ID_INDEX 를 작성하지 않는 경우, InnoDB 에 의해 자동으로 생성됩니다.

InnoDB에 의한 전체 인덱스 삭제 처리

전체 인덱스 컬럼이 포함 된 레코드를 삭제하면 보조 인덱스 테이블에 다수의 소규모 삭제가 발생합니다. 이로 인해 충돌 발생시 해당 테이블에 대한 병렬 액세스가 발생할 수 있습니다. 이 문제를 해결하기 위해 인덱싱 된 테이블에서 레코드를 삭제할 때마다 삭제 된 문서의 문서 ID ( DOC_ID )가 특별한 "DELETED"테이블에 기록 된 인덱싱 된 레코드가 전체 텍스트 인덱싱에 남아 있습니다. 쿼리 결과가 반환되기 전에 "DELETED"테이블의 정보를 사용하여 삭제 된 문서 ID가 제거됩니다. 이 설계의 장점은 삭제가 빠르고, 낮은 에너지 인 것입니다. 단점은 레코드 삭제 후 즉시 인덱스의 크기가 감소되지 않을 것입니다. 삭제 된 항목의 전체 인덱스 항목을 삭제하려면 innodb_optimize_fulltext_only=ON 을 사용하여 인덱스 테이블에서 OPTIMIZE TABLE 을 실행하여 전체 인덱스를 다시 작성합니다. 자세한 내용은 InnoDB 전체 텍스트 인덱싱 최적화 를 참조하십시오.

InnoDB에 의한 전체 인덱스의 트랜잭션 처리

InnoDB 의 FULLTEXT 인덱스는 캐시 및 일괄 처리 작업을 위해 특별한 트랜잭션 처리의 특성을 갖추고 있습니다. 특히 FULLTEXT 인덱스에서 업데이트 및 삽입은 트랜잭션 커밋시에 처리됩니다. 즉, FULLTEXT 검색은 커밋 된 데이터 만 볼 수 있습니다. 다음 예제에서는이 동작을 시연합니다. FULLTEXT 검색은 삽입 된 행이 커밋 된 후에 비로소 결과가 반환됩니다.

mysql> CREATE TABLE opening_lines (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
opening_line TEXT(500),
author VARCHAR(200),
title VARCHAR(200),
FULLTEXT idx (opening_line)
) ENGINE=InnoDB;

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO opening_lines(opening_line,author,title) VALUES
('Call me Ishmael.','Herman Melville','Moby-Dick'),
('A screaming comes across the sky.','Thomas Pynchon','Gravity\'s Rainbow'),
('I am an invisible man.','Ralph Ellison','Invisible Man'),
('Where now? Who now? When now?','Samuel Beckett','The Unnamable'),
('It was love at first sight.','Joseph Heller','Catch-22'),
('All this happened, more or less.','Kurt Vonnegut','Slaughterhouse-Five'),
('Mrs. Dalloway said she would buy the flowers herself.','Virginia Woolf','Mrs. Dalloway'),
('It was a pleasure to burn.','Ray Bradbury','Fahrenheit 451');
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT COUNT(*) FROM opening_lines WHERE MATCH(opening_line) AGAINST('Ishmael');
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT(*) FROM opening_lines WHERE MATCH(opening_line) AGAINST('Ishmael');
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+

InnoDB에 의한 전체 텍스트 인덱싱 모니터

다음 INFORMATION_SCHEMA 테이블에서 쿼리를 실행하면 InnoDB 의 FULLTEXT 인덱스의 특별한 텍스트 처리의 측면을 모니터 및 조사 할 수 있습니다.

INNODB_SYS_INDEXES 및 INNODB_SYS_TABLES 에서 쿼리를 실행하면 FULLTEXT 인덱스 및 테이블에 대한 기본 정보를 표시 할 수 있습니다.

이러한 테이블에 대한 자세한 내용은 INFORMATION_SCHEMA 문서를 참조하십시오.