파트 9. 전체 텍스트 검색과 파티션

1. 전체 텍스트 검색 개요


전체 텍스트 검색은 긴 문자로 구성된 구조화되지 않은 텍스트 데이터(신문 기사)등을 빠르게 검색하기 위한 부가적인 mysql 기능이다.
예를 들어, 보조 인덱스가 신문기사내용인 하나의 테이블에서 교통에 관련된 기사를 찾는다고 해보자.

-- 인덱스틀 통해 맨앞이 교통인 기사 찾음
select * from 신문기사_테이블 where 신문기사내용 like '교통%'

-- 하지만 문장 중간에 교통이 있는 경우도 교통 기사일 수도 있음
select * from 신문기사_테이블 where 신문기사내용 like '%교통%'

2 번째와 같이 찾으면 결국 중간에 들어 있는 교통은 인덱스를 사용할 방법이 없으므로 전체 테이블을 읽어서 찾게 된다. 지금처럼 각각의 단어들에 대해서도 검색 시 인덱스를 사용하도록 도와주는 것이 전체 텍스트 검색이다.

2. 전체 텍스트 인덱스 생성, 삭제


전체 텍스트 인덱스는 신문기사와 같이, 텍스트로 이루어진 문자열 데이터의 내용을 가지고 생성한 인덱스를 말한다.

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

전체 텍스트 인덱스를 생성하는 방법은 3가지가 있다.

-- 형식 1
create table 테이블이름(
     이름 데이터 형식,
    fulltext 인덱스이름(열이름)
);

-- 형식 2
create table 테이블이름(
     이름 데이터 형식,
    ...
);
alter table 테이블이름
    add fulltext(열이름);

-- 형식 3
create table 테이블이름(
     이름 데이터형식,
    ...
);
create fulltext index 인덱스이름
on 테이블이름( 이름);


-- 삭제
alter table 테이블이름
    drop index fulltext( 이름);


3. 중지 단어


전체 텍스트 인덱스는 긴 문장에 대해서 인덱스를 생성하기 때문에 그 양이 커질 수밖에 없다. 그러므로, 실제로 검색에서 무시할 만한 단어들은 전체 텍스트 인덱스로 생성하지 않는 편이 좋다. 예를 들면, 아주, 꼭, 모두 같은 단어는 굳이 인덱스를 생성하지 않아도 된다. mysql은 information_schema.innodb_ft_default_stopword 테이블에 36개의 중지 단어를 가지고 있다. 필요하다면 사용자가 별도의 테이블에 중지 단어를 추가한 뒤에 적용시킬 수 있다.

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


전체 텍스트 인덱스를 생성한 후에, 전체 텍스트 인덱스를 이용하기 위한 쿼리는 일반 select문의 where절에 match() against()를 사용하면 된다.

자연어 검색

특별히 옵션을 지정하지 않거나 in natural language mode를 붙이면 자연어 검색을 한다. 자연어 검색은 단어가 정확한 것을 검색해준다. 예로, newspaper이라는 테이블의 article 열에 전체 텍스트 인덱스가 생성되어 있고, 영화라는 단어가 들어간 기사를 찾으려면 다음과 같다.

select * from newspaper
    where match(article) against('영화');

-- 두 단어 중 하나가 포함된 기사
select * from newspaper 
    where math(article) against('영화 배우');

‘영화’라는 정확한 단어만 검색되며 ‘영화가’같이 다른 단어가 들어간 열은 검색되지 않는다.

불린 모드 검색

불린 모드 검색은 단어나 문장이 정확히 일치하지 않는 것도 검색하는 것을 말하는데 in boolean mode 옵션을 붙여줘야 한다. 불린 모드 검색은 필수인 +, 제외하기 위한 -, 부분 검색을 위한 * 연산자 등의 다양한 연산자를 지원한다.

-- '영화' 가 앞에 들어간 모든 결과 검색 
-- ex) 영화가 영화는 영화를
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);


5. 실습


먼저 검색 가능 단어의 숫자를 확인하고 수정해줘야 한다.

-- 몇 글자 이상부터 검색 가능한지 확인
-- 대부분 3글자로 나오므로 2글자로 수정해보자.
SHOW VARIABLES LIKE 'innodb_ft_min_token_size';

windows powershell(관리자)를 통해 다음 작업을 수행하고 notepad로 my.ini를 열어서 맨 아래 innodb_ft_min_token_size=2 값을 넣고 저장하고 net stop mysql -> net start mysql로 mysql을 재시작한다. 자세한 과정을 아래 그림을 참고하자. 그림1

그림2

그림3


이제 2글자부터 검색이 가능하다.

-- 실습을 위한 테이블 만들기
CREATE DATABASE IF NOT EXISTS FulltextDB;
USE FulltextDB;
DROP TABLE IF EXISTS FulltextTbl;
CREATE TABLE FulltextTbl ( 
	id int AUTO_INCREMENT PRIMARY KEY, 	-- 고유 번호
	title VARCHAR(15) NOT NULL, 		-- 영화 제목
	description VARCHAR(1000)  		-- 영화 내용 요약
);

-- 실습을 위한 정보 입력
INSERT INTO FulltextTbl VALUES
(NULL, '광해, 왕이 된 남자','왕위를 둘러싼 권력 다툼과 당쟁으로 혼란이 극에 달한 광해군 8년'),
(NULL, '간첩','남한 내에 고장간첩 5만 명이 암약하고 있으며 특히 권력 핵심부에도 침투해있다.'),
(NULL, '남자가 사랑할 때', '대책 없는 한 남자이야기. 형 집에 얹혀 살며 조카한테 무시당하는 남자'), 
(NULL, '레지던트 이블 5','인류 구원의 마지막 퍼즐, 이 여자가 모든 것을 끝낸다.'),
(NULL, '파괴자들','사랑은 모든 것을 파괴한다! 한 여자를 구하기 위한, 두 남자의 잔인한 액션 본능!'),
(NULL, '킹콩을 들다',' 역도에 목숨을 건 시골소녀들이 만드는 기적 같은 신화.'),
(NULL, '테드','지상최대 황금찾기 프로젝트! 500년 전 사라진 황금도시를 찾아라!'),
(NULL, '타이타닉','비극 속에 침몰한 세기의 사랑, 스크린에 되살아날 영원한 감동'),
(NULL, '8월의 크리스마스','시한부 인생 사진사와 여자 주차 단속원과의 미묘한 사랑'),
(NULL, '늑대와 춤을','늑대와 친해져 모닥불 아래서 함께 춤을 추는 전쟁 영웅 이야기'),
(NULL, '국가대표','동계올림픽 유치를 위해 정식 종목인 스키점프 국가대표팀이 급조된다.'),
(NULL, '쇼생크 탈출','남자는 누명을 쓰고 쇼생크 감옥에 감금된다. 그리고 역사적인 탈출.'),
(NULL, '인생은 아름다워','귀도는 삼촌의 호텔에서 웨이터로 일하면서 또 다시 도라를 만난다.'),
(NULL, '사운드 오브 뮤직','수녀 지망생 마리아는 명문 트랩가의 가정교사로 들어간다'),
(NULL, '매트릭스',' 2199년.인공 두뇌를 가진 컴퓨터가 지배하는 세계.');


-- full scan
SELECT * FROM FulltextTbl WHERE description LIKE '%남자%' ;

-- 전체 텍스트 검색을 위해 fulltext index 생성
CREATE FULLTEXT INDEX idx_description ON FulltextTbl(description);

-- 인덱스 확인
SHOW INDEX FROM FulltextTbl;


-- 전체 텍스트 인덱스를 이용한 불린 모드 검색
SELECT * FROM FulltextTbl WHERE MATCH(description) AGAINST('남자*' IN BOOLEAN MODE);

-- 검색 점수 확인
-- '여자' 또는 '남자' 가 있는 경우 검색
SELECT *, MATCH(description) AGAINST('남자* 여자*' IN BOOLEAN MODE) AS 점수 
	FROM FulltextTbl WHERE MATCH(description) AGAINST('남자* 여자*' IN BOOLEAN MODE);

-- 남자, 여자가 모두 들어있는 검색
SELECT * FROM FulltextTbl 
	WHERE MATCH(description) AGAINST('+남자*+여자*' IN BOOLEAN MODE);

-- 님자가 포함된 행을 찾고 여자가 포함되어 있다면 상위행으로 찾기
SELECT * FROM FulltextTbl 
	WHERE MATCH(description) AGAINST('+남자* 여자*' IN BOOLEAN MODE);

-- 여자가 포함된 행을 찾고 남자가 있으면 상위 행으로 찾기
SELECT * FROM FulltextTbl 
	WHERE MATCH(description) AGAINST('남자* +여자*' IN BOOLEAN MODE);

-- 딱 '남자' 가 포함된 행중에서 여자가 포함된 행만 찾기
SELECT * FROM FulltextTbl 
	WHERE MATCH(description) AGAINST('남자 +여자' IN BOOLEAN MODE);    

-- 딱 '남자' 가 포함된 행중에서 여자가 있으면 상위행으로, 남자만 있는것도 찾기
SELECT * FROM FulltextTbl 
	WHERE MATCH(description) AGAINST('+남자 여자' IN BOOLEAN MODE);

select * from fulltexttbl;

-- 남자로 검색된 영화 중에서 여자가 들어간 영화는 제거하는 검색
SELECT * FROM FulltextTbl 
	WHERE MATCH(description) AGAINST('남자* -여자*' IN BOOLEAN MODE);

-- 전체 텍스트 인덱스로 만들어진 단어들을 확인하기
-- '데이터베이스이름/테이블이름'
SET GLOBAL innodb_ft_aux_table = 'fulltextdb/fulltexttbl'; -- 모두 소문자
-- 전체 텍스트 인덱스 검색
SELECT word, doc_count, doc_id, position 
	FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;

-- 전체 텍스트 인덱스 삭제
DROP INDEX idx_description ON FulltextTbl;

-- 중지단어를 위한 테이블 만들기
-- 테이블의 데이터는 반드시 value , 타입은 varchar -> 약속
CREATE TABLE user_stopword (value VARCHAR(30));

-- 중지 단어 추가
INSERT INTO user_stopword VALUES ('그는'), ('그리고'), ('극에');

-- 중지 단어 테이블에 지금 만들 테이블을 추가하는 작업
SET GLOBAL innodb_ft_server_stopword_table = 'fulltextdb/user_stopword'; -- 모두 소문자
-- 만든 테이블이 들어갔는지 확인
SHOW GLOBAL VARIABLES LIKE 'innodb_ft_server_stopword_table';

-- 전체 텍스트 인덱스 만들기
CREATE FULLTEXT INDEX idx_description ON FulltextTbl(description);

-- 중지 단어 빠졌는지 확인
SELECT word, doc_count, doc_id, position 
	FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;


6. 파티션


파티션은 대량의 테이블을 물리적으로 여러 개의 테이블로 쪼개는 것을 말한다. 파티션 테이블에는 Primary key를 지정하면 안 된다. 파티션을 나누는 기준 열에 따라 지정된 파티션으로 데이터가 저장되는데 primary key를 지정하게 되면 그 열로 정렬이 되기 때문이다. primary key를 지정하려면 파티션에 사용되는 열도 함께 primary key로 지정해야 한다. 이 예시에서 사용하고 싶다면 userid와 birthyear을 함께 primary key로 지정해야 한다.

그림3

CREATE DATABASE IF NOT EXISTS partDB;
USE partDB;
DROP TABLE IF EXISTS partTBL;
-- 테이블 만들기
CREATE TABLE partTBL (
  userID  CHAR(8) NOT NULL, -- Primary Key로 지정하면 안됨
  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 -- maxvalue = 1979 바로 위 것
);

-- insert
INSERT INTO partTBL 
	SELECT userID, name, birthYear, addr FROM sqlDB.userTbl;

-- 검색해보면 파티션별 순서대로 검색하게 된다.
SELECT * FROM partTBL;

-- 파티션 보기
SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
    FROM INFORMATION_SCHEMA.PARTITIONS
    WHERE TABLE_NAME =  'parttbl';

-- 검색
SELECT * FROM partTBL WHERE birthYear <= 1965 ;

-- 어떻게 검색되었나? 파티션 1개만 검색을 통해 효율적 검색했다.를 확인 
EXPLAIN  SELECT * FROM partTBL WHERE birthYear <= 1965;

-- 파이션3을 3과 4로 분할하기
ALTER TABLE partTBL 
	REORGANIZE PARTITION part3 INTO (
		PARTITION part3 VALUES LESS THAN (1986),
		PARTITION part4 VALUES LESS THAN MAXVALUE
	);
OPTIMIZE TABLE partTBL; -- 파티션 적용

-- 파티션 확인
SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
    FROM INFORMATION_SCHEMA.PARTITIONS
    WHERE TABLE_NAME =  'parttbl';

-- 파티션 합치기
ALTER TABLE partTBL 
	REORGANIZE PARTITION part1, part2 INTO (
		PARTITION part12 VALUES LESS THAN (1979)
	);
OPTIMIZE TABLE partTBL; -- 파티션 적용

-- 파티션 확인
SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
    FROM INFORMATION_SCHEMA.PARTITIONS
    WHERE TABLE_NAME =  'parttbl';

-- 파티션 삭제
ALTER TABLE partTBL DROP PARTITION part12;
OPTIMIZE TABLE partTBL; -- 파티션 적용

SELECT * FROM partTBL;


cf) 리스트 파티션

partition by list columns(addr)(
    partition part1 values in ('서울','경기'),
    partition part1 values in ('충북','충남'),
    partition part1 values in ('경남','경북')    
);


정리

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



본 포스팅은 ‘이것이 MySQL이다’를 읽고 공부한 내용을 바탕으로 작성하였습니다.


© 2021. By Backtony