SQL-39-FULLTEXT

면접 때, 이 질문을 받았었다.
'LIKE절에서 인덱스를 어떻게 거나요?'
%를 앞 뒤, 또는 앞이나 뒤에만 붙여서 검색하는 방법만 사용해봤기에...
하지만 이게 답은 아닐 것 같았다.
결국 그 때 같이 면접봤던 3명 모두 그 대답을 하지 못했다.
그 날 집에와서 찾아봤었고, 뭔지는 어렴풋이 알았지만
다른 것들 준비하고, 공부한다고 정리는 못해뒀다.
그걸 지금 한다~

1. WHERE 컬럼명 LIKE %ABC
2. WHERE 컬렴명 LIKE %ABC%
3. WHERE 컬럼명 LIKE ABC%

사실 mapper이용해서 쿼리로 긁어올 때를 생각하면 1, 2, 3 모두 잘 적용이 됐었던 걸로 기억한다.
DB가 양이 부족했나?
헉.. 여기저기 찾아보니까 한 1만개 정도는 해야 이게 티가 나나보다...ㅎㅎㅎㅎ
나는 고작 500개 정도였으니 허허...

어쨌든 LIKE에 인덱스가 안먹히는 경우를 FULL SCAN이라고 하는데,
결과 자체가 원하는 결과도 아니거니와, FULL SCAN을 하게되면 느려진다.
즉, 부하가 걸린다.

왜 인덱스가 안먹히고 FULL SCAN이 들어가는고 하니,
DB의 구조가 대부분 B-TREE구조라고 한다.
B-TREE는, 부모노드 기준으로 자식노드 값이 작으면 왼쪽, 크면 오른쪽에 배치되는 구조다
이 구조 때문이라고 하는데.. 이 부분은 좀 더 깊게 공부해야지 싶다ㅠㅠ

일단, 이 해결책으로 FULL-TEXT라는 걸 찾아냈다!

FULLTEXT

검색 속도나 옵션이 LIKE에 비해 월등하다.

하지만 FULLTEXT를 사용하기 위해선 다음과 같은 준비가 필요하다

• Full-text indexes can be used only with MyISAM, Aria, InnoDB and Mroonga tables, and can be created only for CHAR, VARCHAR, or TEXT columns.

일단 엔진이 MylSAM, Aria, InnoDB, Mroonga 네개 중에 하나여야 한다.
나는 MariaDB를 사용하기 때문에 InnoDB로 설정되어있는걸 확인할 수 있었다!

그리고 컬럼명이 CHAR, VARCHAR, TEXT 세개 중 하나여야 한다!

• Partitioned tables cannot contain fulltext indexes, even if the storage engine supports them.

파티셔닝된 테이블은 아무리 엔진이 지원한다고 해도 FULLTEXT를 사용할 수 없다는데, 
파티셔닝은 정처기 공부할 때 슬쩍보고 제대로 공부 안했다...^^
다음에 뭔지 다시 파보자고~

• A FULLTEXT index definition can be given in the CREATE TABLE statement when a table is created, or added later using ALTER TABLE or CREATE INDEX.

FULLTEXT인덱스는 테이블 생성시에도 걸 수 있지만, 이미 생성된 테이블에 추가도 가능하다
(ALTER TABLE or CREATE INDEX)

• For large data sets, it is much faster to load your data into a table that has no FULLTEXT index and then create the index after that, than to load data into a table that has an existing FULLTEXT index.

다량의 데이터가 있는 상황에서는 테이블 생성시에 FULLTEXT 인덱스 설정보다,
그냥 테이블 생성하고 추가적으로 FULLTEXT를 추가하는게 낫대
이건 내가 나중에 해봐야겠어.
찍어먹어봐야 안다

 

Full-text searching is performed using MATCH() ... AGAINST syntax. 
FULLTEXT를 사용해서 문장 구성하려면 MATCH() ~ AGAINST를 사용한대
예시를 보자고,

-- 검색어1 또는 검색어2 (or 검색) 
SELECT * FROM Table_Name WHERE MATCH(Col_Name) AGAINST('검색어1 검색어2'); 



-- 검색어 중간에 공백이 들어가는 경우 
SELECT * FROM Table_Name WHERE MATCH(Col_Name) AGAINST('"검색어 검색어"'); 



-- 검색어1을 검색한 결과에서 검색어2가 들어간것을 제외 할 때 
SELECT * FROM Table_Name WHERE MATCH(Col_Name) AGAINST('"검색어1" -검색어2' in boolean mode); 



-- 검색어1, 검색어2 함께 검색이 되는 경우 (and 검색) 
SELECT * FROM Table_Name WHERE MATCH(Col_Name) AGAINST('+"검색어1" +"검색어2"' in boolean mode);



-- 검색어1과 검색어2 And 검색과 함께 검색어2의 결과도 함께 출력 
SELECT * FROM Table_Name WHERE MATCH(Col_Name) AGAINST('"검색어1" +"검색어2"' in boolean mode);

 MATCH() takes a comma-separated list that names the columns to be searched. 

오옹~ MATCH안에 컬럼명은 쉼표로 구분해서 여러개 설정도 가능한가봐

 AGAINST takes a string to search for, and an optional modifier that indicates what type of search to perform. The search string must be a literal string, not a variable or a column name.

MATCH (col1,col2,...) AGAINST (expr [search_modifier])

 

AGAINST는 찾고자 하는 검색어를 적는데, modifier를 추가할 수도 있대 옵션으로. 그게 뭐냐면, 찾고자하는 대상이 어떤 타입인지! 사실 이 부분 쪼끔 이해가 안간다..? 흠...
컬럼은 VARCHAR, CHAR, TEXT여야하는데, 검색어는 뭐 다른게 있을 수 있는거여? 옵션이라고는 했지만 궁금하네..
더 찾아보자고

 

CREATE TABLE ft_myisam(copy TEXT,FULLTEXT(copy)) ENGINE=MyISAM;

INSERT INTO ft_myisam(copy) VALUES ('Once upon a time'),
  ('There was a wicked witch'), ('Who ate everybody up');

SELECT * FROM ft_myisam WHERE MATCH(copy) AGAINST('wicked');
+--------------------------+
| copy                     |
+--------------------------+
| There was a wicked witch |
+--------------------------+

음 예시를 보니까 좀 와닿는다

SELECT * FROM ft_myisam WHERE MATCH(copy) AGAINST('wicked,witch');
+---------------------------------+
| copy                            |
+---------------------------------+
| There was a wicked witch        |
+---------------------------------+

 

복수개의 검색어 찾을 때!

 

SELECT * FROM ft_myisam WHERE MATCH(copy) AGAINST('Once');

STOPWORD를 사용하면 아무런 결과가 안나올 수 있다는데,
이건 다음 장에서 알아보자!
MylSAM, InnoDB에 있는데, 둘이 또 다르대!

 

 

 

 

REF.
https://stricky.tistory.com/435
https://mariadb.com/kb/en/full-text-index-overview/