전체 텍스트 검색과 파티션

전체 텍스트 검색

전체 텍스트 검색은 긴 문자로 구성된 구조화되지 않은 텍스트 데이터 등을 빠르게 검색하기 위한 부갖거인 MYSQL의 기능이다. 저장된 텍스트의 키워드 기반의 쿼리를 위해서 빠른 인덱싱이 가능하다. 이 기능은 FULLTEXT 인덱스를 사용하여 작동한다.

 

전체 텍스트 인덱스(FULLTEXT 인덱스)

MySQL과 같은 관계형 데이터베이스 관리 시스템에서 제공하는 특별한 종류의 인덱스로 이 인덱스는 텍스트 기반의 데이터를 효율적으로 검색할 수 있게 해준다. FULLTEXT 인덱스는 일반적으로 자연어 처리 알고리즘을 사용하여 주어진 키워드가 텍스트 필드 내에서 어떻게 나타나는지를 분석한다.

• 전체 텍스트 인덱스는 InnoDB와 MYISAM 테이블만 지우너한다
• 전체 텍스트 인덱스는 char, varchar, text의 열에만 생성이 가능하다.
• 인덱스 힌트의 사용이 일부 제한된다.
• 여러 개 열에 FULLTEXT 인덱스를 지정할 수 있다.

FULLTEXT index 생성

형식1 
CREATE TABLE 테이블이름 (
 ...
 열 이름 데이터 형식,
 ...,
 FULLTEXT 인덱스 이름 (열 이름)
 );
 
 형식2
 CREATE TABLE 테이블이름 (
  ...
  열 이름 데이터 형식
  ...
 );
 ALTER TABLE 테이블이름
 	ADD FULLTEXT(열 이름);
    
 형식3:
 CRATE TABLE 테이블이름 (
  ...
  열 이름 데이터형식,
  ...
  );
  CREATE FULLTEXT INDEX 인덱스이름
  ON 테이블이름 (열 이름);

전체 텍스트 인덱스 삭제

ALTER TABLE 테이블이름
	DROP INDEX FULTEXT(열 이름);

중지 단어 (Stop Words)

중지 단어(Stop Words)는 텍스트 검색에서 일반적으로 빈번하게 등장하지만 검색 쿼리의 유용성을 떨어뜨리는 단어들을 의미한다. the, and, is, of 등의 단어가 이에 해당한다. 이러한 중지 단어는 FULLTEXT 인덱스 생성 시 자동으로 무시된다. 이는 검색 결과의 정확도를 높이고, 인덱스 크기와 검색 시간을 줄이기 위한 목적으로 사용된다.

 

중지 단어 목록 커스터마이징

MYSQL 설정 파일에서 중지 단어 목록을 커스터 마이징할수 있다.

ft_stopword_file 시스템 변수를 상하여 중지 단어 파일의 경로를 지정할 수 있다.

ft_stopword_file = '/path/to/your/stopword.txt'

전체 텍스트 검색을 위한 쿼리

전체 텍스트 인덱스를 이용하기 위한 쿼리는 일반 SELECT문의 WHERE 절에 MATCH() AGAINST()를 사용하면 된다.

SELECT column1, column2 FROM table_name
WHERE MATCH(column1,column2,...) AGAINST('keyword');

검색모드 

FULLTEXT 인덱스에서는 두가지 검색 모드를 사용한다. 자연어 검색과 불리언 검색이 있다.

 

자연어 검색(Natural Language Mode)

특별히 옵션을 지정하지 않거나 IN NATURAL LANGUAGE MODE를 붙이면 자연어 검색을 한다. 자연어 검색은 정확하 것을 검색해 준다.

중지 단어는 무시됩니다. 단일 단어 검색은 덜 효과적일 수 있습니다.

 

불린 모드 검색(Boolean Mode)

불린 모드 검색은 단어나 문장이 정확히 일치하지 않는 것도 검색한는 것을 말하는데 IN BOOLEAN MODE 옵션을 붙여줘야 한다. 

불리언 연산자
+: 단어가 반드시 포함되어야 함
-: 단어가 포함되어서는 안 됨
> <: 단어의 랭킹을 높이거나 낮춤
*: 와일드카드로 사용
": 정확한 구문 매칭

파티션

파티션은 대량의 테이블을 물리적으로 쪼개는 것을 말한다. 큰 테이블을 더작고, 더 빠르게 관리 가능한 부분으로 나눠어 특정 쿼리를 더 빠르게 수행할 수 있가한다. 일반적으로 파티션은 특정 컬럼의 값에 따라 데이터를 분할한다.

 

파티션 종류

RANGE 파티션

RANGE 파티션은 특정 컬럼의 값 범위에 따라 데이터를 나눈다.

범위 검색이나 지속적으로 증가하는 시퀀스,시간 데이터에 효율적이다.

각 파티션에 범위를 명확하게 정하지 않으면 불균형이 발생할 수 있다.

CREATE TABLE employees (
    id INT,
    name VARCHAR(30),
    hired_date DATE
)
PARTITION BY RANGE (YEAR(hired_date)) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (2001),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

 

LIST 파티션

LIST 파티션은 지정된 값의 목록에 따라 데이터를 분할한다.

특정 값에 의존하는 쿼리에서 유용하고 LIST 값들은 중복되면 안된다.

CREATE TABLE employees (
    id INT,
    name VARCHAR(30),
    department INT
)
PARTITION BY LIST (department) (
    PARTITION pEngineering VALUES IN (1, 2),
    PARTITION pHR VALUES IN (3, 4),
    PARTITION pFinance VALUES IN (5, 6)
);

HASH 파티션

HASH 파티션은 해시 함수의 결과에 따라 데이터를 분할한다. 보통 파티션의 키의 해시 값을 계산한다.

적잘한 해시 함수 선택시 데이터를 균등하게 분살시킬 수 있다

CREATE TABLE employees (
    id INT,
    name VARCHAR(30)
)
PARTITION BY HASH(id)
PARTITIONS 4;

KEY 파티션 

KEY파티션은 MYSQL이 내부적으로 제공하는 해시 함수를 사용하여 데이터를 분할한다.

해시 함수를 선택할 필요가 없다는 장점이 있지만 내부 해시 함수가 모든 상황에 적합하지 않을 수 있다는 단점이 있다,

CREATE TABLE employees (
    id INT,
    name VARCHAR(30)
)
PARTITION BY KEY(id)
PARTITIONS 4;