티스토리 뷰
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를 사용할 수 없다. 즉 모든 경우의 파티션 키 값을 지정해야 한다.
from http://kururu.tistory.com/186 by ccl(A)