이것은MySQL이다 - 전체 텍스트 검색과 파티션

전체 텍스트 검색

 

전체 텍스트 검색 개요

전체 텍스트 검색

• 긴 문자로 구성된 구조화 되지 않은 텍스트 데이터(예로, 신문 기사) 등을 빠르게 검색하기 위한 부가적인 MySQL의 기능
• 저장된 텍스트의 키워드 기반의 쿼리를 위해서 빠른 인덱싱 가능

 

 

전체 텍스트 검색

• 신문 기사 검색

• 교통’을 키워드로 가진 기사 검색

 

 

• 키워드가 중간에 들어간 경우 인덱스 사용 불가, 서버에 엄청난 부하 발생

 

 

전체 텍스트 인덱스

전체 텍스트 인덱스(FULLTEXT Index) 생성

• 텍스트로 이루어진 문자열 데이터의 내용으로 생성한 인덱스
• 텍스트 인덱스 생성 형식

 

• 텍스트 인덱스 생성 형식

 

• 전체 텍스트 인덱스의 특징
  • InnoDB와 MyISAM 테이블만 지원
  • char, varchar, text의 열에만 생성 가능
  • 인덱스 힌트 사용 일부 제한
  • 여러 개 열에 FULLTEXT 인덱스를 지정 가능

 

전체 텍스트 인덱스 삭제

 

 

 

전체 텍스트 인덱스

중지 단어

• 전체 텍스트 인덱스는 긴 문장에 대해서 인덱스 생성하기 때문에 양이 방대함
  • 검색시 무시할 만한 단어들은 전체 텍스트 인덱스로 생성하지 않음
• MySQL 8.0은 INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD 테이블 존재
  • 36개의 중지 단어 가지고 있음

 

 

 

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

• 전체 텍스트 인덱스를 생성한 후 전체 텍스트 인덱스 이용하기 위한 쿼리
  • 일반 SELECT문의 WHERE절에 MATCH( ) AGAINST( ) 사용

 

 

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

 

자연어 검색

• 정확한 단어를 검색
• 특별히 옵션 지정하지 않거나 IN NATURAL LANGUAGE MODE 사용
• ‘영화’라는 단어가 들어간 기사를 찾을 때 사용

• 영화’ 또는 ‘배우’ en 단어 중 하나가 포함된 기사를 찾을 때 사용

 

불린 모드 검색

• 단어나 문장이 정확히 일치하지 않는 것도 검색하는 것
• IN BOOLEAN MODE 옵션 필요
• 필수 ‘+’
• 제외 ‘-’
• 부분 검색 ‘*’ 등 연산자 등의 다양한 연산자 지원
• ‘영화를’, ‘영화가’, ‘영화는’ 등의 ‘영화’가 앞에 들어간 결과를 검색하고 싶을 때 사용

 

 

'영화 배우’ 단어가 정확히 들어 있는 기사의 내용을 검색하고 싶을 때 사용

‘영화 배우’ 단어가 들어 있는 기사 중 ‘공포’의 내용이 꼭 들어간 결과만 검색하고 싶을 때 사용

영화 배우’ 단어가 들어 있는 기사 중에서 ‘남자’의 내용은 검색에서 제외하고 싶을 때 사용

 

 

 

 

전체 텍스트 인덱스 실습

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

• MySQL 환경 설정 파일 my.ini 파일의 [mysqld]에 innodb_ft_min_token_size = 2 추가
  • ‘남성’등의 2글자 단어를 검색위해 2로 변경(3으로 설정되어 있음)
• MySQL 서비스 재시작 또는 컴퓨터 재부팅
• 데이터베이스 및 테이블 생성

-- FULLTEXT 검색을 위한 데이터베이스 생성
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)				-- 영화 내용 요약
);

• 데이터 입력 및 ‘남자’ 단어 검색
• https://cafe.naver.com/thisisMySQL/ 에서 쿼리문 다운로드 가능

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년.인공 두뇌를 가진 컴퓨터가 지배하는 세계.');

select * from FulltextTbl;


select * from FulltextTbl where description like '%남자%';

 

 

 

• 전체 테이블 검색 실행 계획 확인

 

 

• 전체 텍스트 인덱스 생성

-- fulltext 인덱스 생성
create fulltext index idx_description on FulltextTbl(description);

 

• 정보 확인

 

• 전체 텍스트 인덱스 활용한 검색

select * from FulltextTbl where match(description) against('남자*' in boolean mode); -- cost 0.35 fulltext index search 1 row

 

• 전체 텍스트 인덱스 검색 실행 계획

 

• ‘남자’ 또는 ‘여자’가 들어간 행 검색, 매치되는 점수 출력

-- '남자' 또는 '여자'가 들어간 행을 검색, 매치되는 점수도 출력
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);

 

• 전체 텍스트 인덱스로 만들어진 단어 확인

-- FULLTEXT INDEX 단어 확인
 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;

• 중지 단어 저장할 테이블 생성

-- 사용자가 추가할 중지 단어 저장할 테이블 생성
create table user_stopword (value varchar(30));

• 중지 단어 입력 : ‘그는’, ‘그리고’, ‘극에’

insert into user_stopword values ('그는'),('그리고'),('극에');

• 중지 단어용 테이블 시스템 변수 설정 : DB 이름과 테이블 이름은 모두 소문자

-- FULLTEXT INDEX 단어 생성시 제외할 단어 테이블 설정
set global innodb_ft_server_stopword_table = 'fulltextdb/user_stopword';-- 중지 단어 테이블 지정
show global variables like 'innodb_ft_server_stopword_table';

 

 

• 전체 텍스트 인덱스 생성

-- FULLTEXT INDEX 단어 재 생성
create fulltext index idx_description on FulltextTbl(description);

• 전체 텍스트 인덱스에 생성된 단어 확인

-- 인덱스가 생성된 단어 확인
 select word, doc_count, doc_id, position
	from information_schema.innodb_ft_index_table;

• 중지 단어 설정한 단어가 보이지 않음
  • ‘그는’, ‘그리고’, ‘극에’

 

 

 

파티션

 

파티션 개요와 실습

파티션

• 대량의 테이블을 물리적으로 여러 개의 테이블로 쪼개기
• 데이터의 분포 특성, 자주 사용되는 쿼리문이 무엇인지에 따라서 효율에 차이 있음

 

 

 

파티션 구성

-- 파티션으로 테이블 구현 : 대용량의 테이블을 물리적으로 분할하여 사용
-- sqldb.sql을 이용하여 sqldb 초기화

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
);

 

 

• 파티션 테이블에는 Primary Key 지정 하면 안됨
• 데이터 입력 : 입력됨과 동시에 파티션 키에 의해서 데이터가 각 파티션으로 나뉘어짐

 

insert into partTbl
	select userId, name, birthYear, addr from sqldb.usertbl;

-- 파티션 순서로 해서 출력    
select * from partTbl;

 

 

• 파티션 확인
  • INFORMATION_SCHEMA 데이터베이스의 PARTITIONS 테이블에 관련 정보 있음 

select table_schema, table_name, partition_name, partition_ordinal_position, table_rows
	from information_schema.partitions
    where table_name = 'parttbl';

 

 

 

• 1965년 이전 출생한 회원 조회

-- 범위를 이용한 조회 1965년 이전 출생자
select * from partTbl where birthYear <= 1965;

• 결과는 3명 출력

• 어느 파티션을 사용했는지 확인하려면 쿼리문 앞에 EXPLAIN문을 붙임

-- explain : 어느 파티션에 조회를 했는지 확인 : 쿼리문 앞에 추가
explain select * from partTbl where birthYear <= 1965;

 

 

• 파티션3을 1979 ~ 1986미만(파티션3)과 1986 이상(파티션4)로 분리
  • 파티션 분리 : ALTER TABLE… REORGANIZE PARTITION문 사용
  • 파티션을 재구성 : OPTIMIZE TABLE문 사용

 

-- 파티션 관리 : 분할
-- 파티션 3 : 1979 ~ 1986 미만 (파티션3), 1986 이상(파티션 4)
alter table partTbl
	reorganize partition part3 into (
		partition part3 values less than (1986),
        partition part4 values less than maxvalue
);

optimize table partTbl;

 

 

 

파티션 합치기

• 파티션1과 파티션2를 합쳐서 파티션12로 합치기

-- 파티션 합치기
alter table partTbl
	reorganize partition part1, part2 into (
		partition part12 values less than (1979)
        
);

optimize table partTbl;

 

 

파티션 삭제

• 파티션12 삭제

-- 파티션 삭제
alter table partTbl drop partition part12;

optimize table partTbl;

• 데이터 조회

select * from partTbl;

 

 

 

 

파티션의 특징

• 파티션 테이블에 외래 키 설정 불가
  • 단독으로 사용되는 테이블에만 파티션 설정
• 스토어드 프로시저, 스토어드 함수, 사용자 변수 등은 파티션 함수나 식에 사용 불가
• 임시 테이블은 파티션 기능을 사용 불가
• 파티션 키에는 일부 함수만 사용 가능
• 파티션 개수는 최대 8,192개까지 지원
• 레인지 파티션은 숫자형의 연속된 범위 사용
• 리스트 파티션
  • 숫자형 또는 문자형의 연속되지 않은 파티션 키 값 지정
  • MAXVALUE 사용 불가, 모든 경우의 파티션 키 값 지정