전체 텍스트 검색과 파티션

Preface

 

이번 장에선 전체 텍스트 검색과 파티션의 사용 방법을 공부했다.

 

특별히 어려운 내용은 없어서 금방 끝낼 수 있었다.

 

이번 장을 끝으로 MySQL의 기본적인 사용 방법은 대부분 마친 것 같다.

 

다음 장 부턴 php, python 등 응용 프로그램들과의 연동 방법을 소개하는 것 같은데, 내가 배웠던 python을 MySQL과 어떻게 연동하여 사용할 수 있을지 궁금하고 기대된다.

---

 

1. 전체 텍스트 검색

 

 

- 전체 텍스트 검색: 긴 문자로 구성된 구조화되지 않은 텍스트 데이터를 빠르게 검색하기 위한 기능

→ 첫 글자뿐 아니라, 중간의 단어나 문장으로도 인덱스를 생성해준다.

 

 

- 전체 텍스트 인덱스(FULL TEXT Index)의 특징

1) InnoDB와 MyISAM 테이블만 지원한다.

2) char, varchar, text의 열에만 생성이 가능하다.

3) 인덱스 힌트의 사용이 일부 제한된다. (인덱스를 강제로 사용하거나 사용 못하게 하는 것)

4) 여러 개 열에 FULLTEXT 인덱스를 지정할 수 있다.

 

 

- 전체 텍스트 인덱스 생성 방법

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


-- 2번
create table 테이블이름(
	열이름 데이터형식,
):
alter table 테이블이름
	add fulltext (열이름);
    
    
-- 3번
create table 테이블이름(
	열이름 데이터형식,
):
create fulltext index 인덱스이름
on 테이블이름 (열이름);

 

 

- 전체 텍스트 인덱스 삭제 방법

alter table 테이블이름
	drop index fulltext(열이름);

 

 

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

→ match( ) 함수는 where절에서 사용한다!

 

 

- 자연어 검색: 정확한 단어 그 자체만 검색해주는 방법

1) 특별히 옵션을 지정하지 않거나, in natural language mode를 붙이면 된다.

2) 예를 들어 '영화'라는 단어만 검색되며, '영화가', '영화는' 등의 단어는 검색하지 않는다.

select * from newspaper
	where match(article) against('영화');
    
    
-- 두 단어 중 하나가 포함된 텍스트를 찾는 방법
select * from newspaper
	where match(article) against('영화 배우');

 

 

- 불린 모드 검색: 단어나 문장이 정확히 일치하지 않는 것도 검색하는 방법

1) in boolean mode를 붙이면 된다.

2) 필수인 '+', 제외하기 위한 '-', 부분 검새을 위한 '*' 연산자 등을 지원한다.

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

 

 

 

- MySQL은 기본적으로 3글자 이상만 전체 텍스트 인덱스로 생성한다.

→ my.ini 파일을 재설정 하면 바꿀 수 있다고 하는데, 맥북에선 어떻게 하는지 모르겠다.

 

 

- 전체 텍스트 인덱스로 만들어진 단어를 확인하는 방법

set global innodb_ft_aux_table = 'DB이름/테이블이름'; -- 모두 소문자로 작성해야 함
select word, doc_count, doc_id, position
	from information_schema.innodb_ft_index_table;

 

 

- 중지 단어(stopwords): 전체 텍스트 인덱스에서 검색할 이유가 없는 단어 (생략해도 될만한 단어)

→ 중지 단어를 적용시키면 이들을 제외하고 전체 텍스트 인덱스를 만들어주므로, 인덱스의 크기가 줄어든다.

 

 

- 사용자 지정 중지 단어를 설정하는 방법

1) 중지 단어를 저장할 테이블을 만든다.

→ 테이블 이름은 상관 없지만, 열 이름은 value, 데이터 형식은 varchar로 지정해야 한다.

2) 중지 단어를 입력한다.

insert into 테이블이름 values ('그는'), ('그리고'), ('극에');

3) 중지 단어용 테이블을 시스템 변수 innodb_ft_server_stopword_table에 설정한다.

→ DB 이름과 테이블 이름은 모두 소문자로 써야 한다.

set global innodb_ft_server_stopword_table = 'DB이름/테이블이름';
show global variables like 'innodb_ft_server_stopword_table';

4) 전체 텍스트 인덱스를 생성한다.

5) 전체 텍스트 인덱스에 생성된 단어를 확인한다.

 

---

 

2. 파티션

 

 

- 파티션(partition): 대량의 테이블을 물리적으로 여러 개의 테이블로 쪼개는 것

→ 테이블의 범위에 따라서 서로 다른 파티션에 저장하는 것이 가장 보편적이다.

 

 

- 테이블을 생성할 때 파티션 키를 함께 지정하면, 데이터를 입력할 때 지정된 파티션 키에 의해서 데이터가 각각의 파티션에 입력된다.

 

 

- 출생 년도에 따라 분리되는 파티션 테이블

create database if not exists partDB;
use partdb;
drop table if exists partTBL;
create table partTBL(
	userID char(8) not null,	-- primary key로 지정하면 안 됨
    username 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
);

 

 

- sqlDB의 데이터를 입력한 후 확인

insert into partTBL
	select userID, username, 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문을 사용할 때 어떤 파티션을 사용했는지 확인하려면 쿼리문 앞에 explain문을 붙이면 된다.

explain select * from parttbl where birthyear <= 1965;

 

 

- 파티션을 추가하는 방법

alter table parttbl
	reorganize partition part3 into(
		partition part3 values less than (1986),
        partition part4 values less than maxvalue
	);
optimize table parttbl;

→ 원래 파티션을 추가할 땐 alter table~add partition문을 사용하면 되지만, maxvalue로 설정된 파티션 테이블에는 파티션을 추가할 수 없다. 이런 경우 위의 방법처럼 파티션을 분리하는 방식으로 추가해야 한다.

 

 

- 두 개의 파티션을 합치는 방법

alter table parttbl
	reorganize partition part1, part2 into(
		partition part12 values less than (1979)
	);
optimize table parttbl;

 

 

- 파티션 사용 시 주의할 점

1) 파티션을 재구성한 후에는 optimize table문을 수행해줘야 한다.

2) 파티션을 삭제하면 그 파티션의 데이터도 함께 삭제된다.

3) 파티션 테이블에는 기본키를 지정하면 안되고, 만약 지정하려면 파티선에서 사용되는 열도 함께 기본키로 지정해야 한다.

4) 파티션 테이블에는 외래키를 설정할 수 없으므로, 단독으로 사용되는 테이블에만 파티션을 설정할 수 있다.

5) 스토어드 프로시저, 스토어드 함수, 사용자 변수 등을 파티션 함수나 식에 사용할 수 없다.

6) 임시 테이블은 파티션 기능을 사용할 수 없다.

7) 파티션 키에는 일부 함수만 사용할 수 있다.

8) 파티션 개수는 최대 8192개까지 지원된다.

9) 레인지 파티션은 숫자형의 연속된 범위를, 리스트 파티션은 숫자형 또는 문자형의 연속되지 않은 하나하나씩 파티션 키 값을 지정한다.

10) 리스트 파티션에는 maxvalue를 사용할 수 없으므로, 모든 경우의 파티션 키 값을 지정해야 한다.