DBMS 공부 8 - FULL TEXT SEARCH , BULK INSERT

1. FULL TEXT SEARCH 

• 기존 sql의 like 검색은 여러 개의 검색필터를 동시에 매칭시키는 방식이다 , 이를  and/or과 함께 사용 할 경우 심각한 성능 저하가 일어난다.
• full text search는 위의 방식과 결과는 동일하지만 DB서버에 부담을 주지 않는 검색 방식이다 .
• 기본적으로 컬럼 내용 전체를 단순 문자열(plain text)로 생각하고 검색하는 방식이다. 
• 문자편집기의 편집 찾기 / 바꾸기 메뉴의 동작방식과 유사하다.

 

mysql에서의 full text search 

- 자연어 검색 , 불린 검색 , 쿼리 확장 검색  세가지로 나뉜다.

- full text search 인덱스를 생성해야 한다. 

 

full text search 추가 

alter table 테이블명 add fulltext(컬럼명);  

 

full text search my.cnf 설정 

 

- my.cnf 에서 아래 내용을 추가한다

ft_min_word_len = 2  
(2자 이상의 단어에 대해 적용하도록 제한( default는 4 ))

- 기존 full text index가 있으면 인덱스를  재구성해준다 .

repair table 테이블명 quick; 

 

테이블 생성시 full text index 지정

 

create table articles (
     id int unsigned auto_increment not null primary key, 
     title varchar(200),
     body text ,  
     fulltext(title , body)
);

 

자연어 검색 

where match(컬럼명) against('검색어 / 검색문장')

- 검색의 정확도(score) 확인하여 검색한다 .

- 결과는 검색의 정확도에 대한 내림차순 정렬

예시 )
select * from articles where match(title,body) against('mysql');
* 만약 like 를 사용했다면 아래와 같다.
select * from articles where title like '%mysql%' or body like 'myslq%';

 

boolean 검색

where match(컬럼명) against('"단어*"  -제외단어 in boolean mode')

- 검색의 정확도에 따른 정렬이 안되고 연사자 사용한 구문 검색이 가능한 방식

- 필수단어는 + 예외단어 - 부분단어 * 를 붙혀 사용한다. 

예시)
select * from articles where match(title,body) against("spring*" in boolean mode);

 

 

2. BULK INSERT

• insert의 경우 레코드를 추가한 후 내부적으로 인덱스 재구성 작업(정렬 등)이 필요하다 
• bulk insert 는여러 개의 레코드를 넣어야 하는 경우 하나의 레코드 입력할 떄 마다 이 작업이 연속적으로 발생하는 문제를 해결한다

 

인덱스 작업 정지 명령어 

alter table '테이블명' disable keys ; 

 

인덱스 작업 재설정

alter table '테이블명 ' enable keys ;  

 

인덱스 작업 정지와 재설정 사이에  bulk insert를 한다. 

 

 

Bulk insert 2가지 방식

 

1. 기존의 insert 문에 values 를 여러 번 나열한다. 

insert into 테이블명 values(...) values(....) ; 

2. 파일로 덤프한다(2가지 방식).

 

2-1.  sql을 사용한 방식

source inser.sql;

 

2-2 . csv 파일을 사용한다. 

load data infile '/opt/homebrew/etc/test.csv'
into table articles
fields terminated by '\t' enclosed by '"' escaped by '\\'  lines starting by '' terminated by '\n';

 

* 데이터 내용을 csv 로 출력하는 방법

 

select * from 테이블명 into outfile '파일 경로';

 

* 주의점 

- load data/select into outfile 의 경우 보안 상의 문제로 특정 디렉토리에만 가능하도록 아래 설정을 my.cnf에 추가해야한다. 

secure-file-priv=디렉토리 경로

 

city 테이블의 내용을 csv 파일로 변환하고 load file 명령어를 사용하여 복원하라

// 파일 아웃풋 디렉토리 확인
-> show variables like 'secure%'
// city 테이블을 csv로 출력
-> select * from city into outfile '/opt/homebrew/etc/city.csv'
// city 테이블 데이터 삭제 
-> truncate table city 
// index 기능 정지
-> alter table city disable keys;

// city.csv 파일을 읽어 bulk insert 수행
-> load data infile '/opt/homebrew/etc/city.csv'into table city
     fields terminated by '\t' enclosed by '"' escaped by '\\'  lines starting by '' terminated by '\n';

// index 기능 작동
-> alter table city enable keys;