[MySQL] 11. 전체 텍스트 검색과 파티션

전체 텍스트 검색

전체 텍스트 검색 개요

• 신문기사 같은 구조화되지 않은 텍스트를 빠르게 검색하기 위한 기능
• 첫 글자뿐 아니라 중간의 단어나 문장으로도 인덱스를 생성해준다

 

전체 텍스트 인덱스

• 전체 텍스트 인덱스 생성
  • 텍스트로 이루어진 문자열 데이터의 내용으로 생성한 인덱스
  • 일반 인덱스와 차이점
    • InnoDB와 MyISAM 테이블만 지원한다.
    • char, varchar, text의 열에만 생성이 가능하다.
    • 인덱스 힌트의 사용이 일부 제한된다.
    • 여러 개 열에 FULLTEXT 인덱스를 지정할 수 있다.
  • 생성방식

-- 형식1 :
CREATE TABLE 테이블 이름{
    ...
    열 이름 데이터 형식
    ...,
    FULLTEXT 인덱스 이름 (열 이름)
};

-- 형식2 :
CREATE TABLE 테이블 이름{
    ...
    열 이름 데이터 형식
    ...
}
ALTER TABLE 테이블 이름
    ADD FULLTEXT (열 이름);

-- 형식3 :
CREATE TABLE 테이블 이름{
    ...
    열 이름 데이터 형식
    ...,
}
CREATE FULLTEXT INDEX 인덱스이름
ON 테이블이름 (열 이름);

• 전체 텍스트 인덱스 삭제

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

• 중지 단어
  • 검색 시 무시할만한 단어들 (아주, 모두, 꼭.. 등과 같은)
  • 검색 할 이유가 없으므로 제외시키는 것이 좋다.
  • MySQL은 36개의 중지단어를 가지고있고, 사용자가 추가한 후 적용할 수도 있다.
• 전체 텍스트 검색을 위한 쿼리 
  • 전체 텍스트 인덱스를 이용하기 위한 쿼리는 일반 SELECT문의 WHERE절에 MATCH() AGAINST()를 사용하면 된다.
  • MATCH()함수는 WHERE절에서 사용한다.
  • 자연어 검색  
    • 특별히 옵션을 지정하지 않거나 IN NATURAL LANGUAGE MODE를 붙이면 자연어 검색
    • SELECT * FROM newspaper
        WHERE MATCH(article) AGAINST('영화');
  • 불린 모드 검색
    • 단어나 문장이 정확히 일치하지 않는 것 까지 검색하는 것
    • IN BOOLEAN MODE 옵션을 붙여줘야 한다.
    • 연산자
      • '+' : 필수
      • '-' : 제외
      • '*' : 부분검색

-- 앞에 영화가 들어간 모든 결과 검색
SELECT * FROM newspaper
    WHERE MATCH(article) AGAINST('영화*' IN BOOLEAN MODE);
    
-- 영화 배우 단어가 정확히 들어있는 기사 검색
SELECT * FROM newspaper
    WHERE MATCH(article) AGAINST('영화 배우' IN BOOLEAN MODE);
    
-- 영화 배우 단어가 정확히 들어있는 기사 중 '공포'내용이 꼭 들어간 기사 검색
SELECT * FROM newspaper
    WHERE MATCH(article) AGAINST('영화 배우 +공포' IN BOOLEAN MODE);

-- 영화 배우 단어가 정확히 들어있는 기사 중 '남자'내용을 검색에서 제외
SELECT * FROM newspaper
    WHERE MATCH(article) AGAINST('영화 배우 -남자' IN BOOLEAN MODE);

 

파티션

파티션 개요와 실습

파티션

• 대량의 테이블을 물리적으로 여러 개의 테이블로 쪼개는 것
• 테이블이 너무 크면 인덱스를 사용해도 부담이 되기 때문에 쪼갠다.
• 쪼갠다고 항상 효율적인 것은 아니고, 데이터 분포 특성, 사용되는 쿼리문 등에 따라 효율에 차이가 있다.
• 테이블의 범위에 따라 파티션을 지정하는 것이 보편적이다 (과거의 데이터와 현재의 데이터)
• 테이블을 생성할 때 파티션 키를 지정하고, 지정된 파티션 키에 의해 데이터가 각각의 파티션에 입력된다.
• 파티션 테이블에는 primary Key를 지정해선 안된다.

CREATE DATABASE IF NOT EXISTS partDB;
USE partDB
DROP TABLE IF EXISTS partTBL
CREATE TABLE partTBL(
    userID CHAR(8) NOT NULL,
    name VARCHAR(10) NOT NULL,
    birthyear INT NOT NULL,
    addr CHAR(2) NOT NULL)
PARTITION BY RANGE(birthYear)(
    PARTITION part1 VALUES LESS THAN (1971),
    PARTITION part2 VALUES LESS THAN (1979),
    PARTITION part3 VALUES LESS THAN MAXVALUE
);

 

파티션의 부가적으로 기억해야 할 내용

• 파티션 테이블에 외래 키를 설정할 수 없다 (단독으로 사용되는 테이블에만 파티션 설정 가능)
• 스토어드 (프로시저, 함수), 사용자 변수 등 사용할 수 없다.
• 입시 테이블은 파티션을 사용할 수 없다.
• 파티션 키에는 일부 함수만 사용할 수 있다.
• 최대 개수는 8192개까지 지원된다
• 레인지 파티션은 숫자형의 연속된 범위를 사용한다.
• 리스트 파티션은 연속되지 않은  숫자형 또는 문자형 하나하나씩 파티션 키 값을 지정한다.
• 리스트 파티션에는 MAXVALUE를 사용할 수 없다.(모든 경우에 대해 키 값을 지정해야 한다.)

 

힌트

 

옵티마이저의 실행 계획을 원하는대로 바꿀 수 있게 해준다

 

힌트의 종류

• 옵티마이저 힌트
  
  • 옵티마이저 힌트는 제어를 원하는 부분을 지정할 수 있기에, 옵티마이저를 더 세밀하게 제어해야 할 때 사용한다.
  • 적용 범위
    • 전역: 힌트가 전체 문에 영향을 줌
    • 쿼리 블록: 힌트가 명령문 내의 특정 쿼리 블록에만 영향을 줌
    • 테이블: 힌트가 쿼리 블록 내의 특정 테이블에민 영향을 줌
    • 인덱스: 힌트가 테이블 내의 특정 인덱스에만 영향을 줌
  • 옵티마이저 힌트는 주석 내에 지정해야 한다.
• 인덱스 힌트
  • Mysql에서 제공하는 인덱스 힌트를 쓰면 강제적으로 할당한 Index를 이용하여 쿼리가 실행이 된다.
  • ex) table_name 테이블에 index_name이라는 인덱스가 존재하고, INDEX SCAN하려고 할때

 SELECT * FROM table_name USE INDEX (index_name) WHERE col = 1;

https://velog.io/@bae_mung/TIL-MySQL-Hint
http://www.stechstar.com/user/zbxe/?mid=study_SQL&document_srl=41405