FULLTEXT Index, 파티션

1. FULLTEXT Index

- FULLTEXT Index 는 InnoDB와 MyISAM 테이블만 지원한다.

- FULLTEXT Index 는 char, varchar, etxt의 열에만 생성이 가능하다.

- Index Hint의 사용이 일부 제한된다.

- 여러 개의 열에 FULLTEXT Index를 지정할 수 있다.

-- 방법1
CREATE TABLE 테이블이름(
열이름 데이터형식
FULLTEXT 인덱스이름(열이름)
);
 
-- 방법2
CREATE TABLE 테이블이름(
열이름 데이터형식,
);
ALTER TABLE 테이블이름
ADD FULLTEXT(열이름);
 
CREATE TABLE 테이블이름(
열이름);
CREATE FULLTEXT INDEX 인덱스이름
ON 테이블이름(열이름);
 
-- FULLTEXT Index 삭제
ALTER TABLE 테이블이름
DROP INDEX FULLTEXT(열이름);

중지단어 :  FULLTEXT Index 긴문장에대해서 인덱스를 생성하기 때문에 필요없는 단어는 제외시키도록 하는것.

use information_schema;
show tables;
select * from INNODB_FT_DEFAULT_STOPWORD;

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

SELECT문의 WHERE절에 MATCH() AGAINST()를 사용하면 된다.

1) 자연어 검색

- 특별히 옵션을 지정하지 않거나 IN NATURAL LANGUAGE MODE를 붙이면된다.

2) 불린모드검색

- 단어나 문장이 정확히 일치하지 않는 것도 검색

---

실습 준비작업

create table fulltextTbl select * from emp;
alter table fulltextTbl add fulltext(first_name);
show table status;

데이터가 많을수록 작업이 오래걸리고 데이터 크기도 커졌다.

---

실습 다시 준비작업

중간에 생각해보니 first_name은 문장이 아니라서 

이사이트(https://gongzza.github.io/database/mysql-fulltext-search/) 를 참고해서 다시했다.

CREATE TABLE articles (
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        title VARCHAR(200),
        body TEXT,
        FULLTEXT idx_ft_title_and_body (title,body)
) ENGINE=InnoDB;
 
INSERT INTO articles (title,body) VALUES
    ('MySQL Tutorial','DBMS stands for DataBase ...'),
    ('How To Use MySQL Well','After you went through a ...'),
    ('Optimizing MySQL','In this tutorial we will show ...'),
    ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    ('MySQL vs. YourSQL','In the following database comparison ...'),
    ('MySQL Security','When configured properly, MySQL ...');
 
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE);

SELECT * FROM articles WHERE MATCH (title, body) AGAINST ('data*' in boolean mode); -- 부분검색 *
SELECT * FROM articles WHERE MATCH (title, body) AGAINST ('data' in boolean mode); -- 특정단어 검색(단어일치)
SELECT * FROM articles WHERE MATCH (title, body) AGAINST ('DBMS +data*' in boolean mode); -- DBMS 단어들중에 data단어가 필요한경우
SELECT * FROM articles WHERE MATCH (title, body) AGAINST ('DBMS -data*' in boolean mode); -- DBMS 단어들중에 data단어가 제외인경우
-- SELECT * FROM articles WHERE MATCH (title, body) AGAINST ('+남자* +여자*' in boolean mode); -- 남자 여자 둘다 포함한경우

-- 저장단어 확인하기
SET GLOBAL innodb_ft_aux_table = 'indexdb/fulltexttbl'; -- db이름/테이블이름
SET GLOBAL innodb_optimize_fulltext_only=ON; 
OPTIMIZE TABLE fulltexttbl;
 
SELECT WORD, DOC_COUNT, DOC_ID, POSITION FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;

---

2. 파티션

물리적으로 여러 개의 테이블로 쪼개는 것을 말한다.

- 물론 무조건 파티션으로 나눈다고 효율적이 되는것은 아니다.

- MySQL은 최대 1024개의 파티션을 지원하는데, 파티션을 나누면 물리적으로 파일로 분리된다. 그렇기 때문에 파티션 테이블은 파일이 동시에 여러 개 열린다. MySQL은 동시에 열 수 있는 ㅍ파일의 개수가 시스템 변수 open_file_limt에 지정된다. 그러므로 파티션을 많이 나눌경우 값을 크게 변경시킬 필요가 있다.

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 (1970),
PARTITION part2 VALUES LESS THAN (1980),
PARTITION part3 VALUES LESS THAN MAXVALUE
);
 
select table_schema, table_name, partition_name, partition_ordinal_position, table_rows 
from information_schema.partitions
where table_name = 'parttbl';

insert into parttbl values('abcd', '홍길동', 1975, '부산'),
('abce', '이순신', 1960, '서울'),
('abcf', '세종대왕', 1999, '대전'),
('abcg', '차관', 2002, '대구');
select * from parttbl;
EXPLAIN select * from parttbl where birthYear<1970;
EXPLAIN select * from parttbl where birthYear<1980;
EXPLAIN select * from parttbl where birthYear>=1980;

EXPLAIN 참고자료 : https://dev.mysql.com/doc/refman/5.7/en/partitioning-info.html

https://dev.mysql.com/doc/refman/8.0/en/partitioning-info.html

-- 파티션 재조정 및 분리
alter talbe parttbl
reorganize partition part3 into(
partition part3 values less than (1990),
partition part4 values less than maxvalue
);
 
optimize table parttbl;
 
-- 파티션 합치기
alter talbe parttbl
reorganize partition part1, part2 into(
partition part12 values less then(1980)
);
optimize table parttbl;
 
-- 파티션 삭제
alter table parttbl drop partition part3;
optimize table parttbl;
 
-- 파티션3을 삭제하면 그와 함께 저장된 데이터도 삭제된다.
-- 대용량 데이터 삭제시 delete문보다 파티션 자체를 삭제하는것이 더 효율적이다.

파티션 정리

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

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

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

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

- 파티션 개수는 최대 1024개 까지 지원된다.

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

- 리스트 파티션에는 MAXVALUE를 사용할 수 없다. 즉 모든 경우의 파티션 키 값을 지정해야 한다.