FULLTEXT INDEX WITH PARSER `ngram`

skarltjr commented 2 years ago

1. 인덱스란?

데이터베이스의 인덱스는 지정한 컬럼을 기준으로 메모리 영역에 목차를 생성하는 것
insert, update, delete (Command)의 성능을 희생하고 대신 select (Query)의 성능을 향상
update, delete 행위가 느린것이지, update, delete를 하기 위해 해당 데이터를 조회하는것은 인덱스가 있으면 빠르게 조회

2. b-tree

가장 많이 사용되는 인덱스 알고리즘
인덱스 탐색은 Root -> Branch -> Leaf -> 디스크 저장소 순으로 진행. 예를 들어 Branch (페이지번호 2) 는 dept_no가 d001이면서 emp_no가 10017 ~ 10024까지인 Leaf의 부모로 있다. 즉, dept_no=d001 and emp_no=10018로 조회하면 페이지 번호 4인 Leaf를 찾아 데이터파일의 주소를 불러와 반환하는 과정 진행.
인덱스의 두번째 컬럼은 첫 번째 컬럼에 의존해서 정렬. 즉, 두번째 컬럼의 정렬은 첫번째 컬럼이 똑같은 열에서만 의미가 있다. 만약 3번째, 4번째 인덱스 컬럼도 있다면 두번째 컬럼과 마찬가지로 3번째 컬럼은 2번째 컬럼에 의존하고, 4번째 컬럼은 3번째 컬럼에 의존하는 관계
인덱스 성능을 향상시킨다는 것은 디스크 저장소에 얼마나 덜 접근하게 만드느냐, 인덱스 Root에서 Leaf까지 오고가는 횟수를 얼마나 줄이느냐에 달려있다.

참고 : https://jojoldu.tistory.com/243

skarltjr commented 2 years ago

3. N-GRAM 전문 검색

컨텐츠의 인덱스를 아래와 같이 첫글자/두글자..(1그램,2그램,3그램..) 등등으로 나누어서 파싱하여 전문검색 인덱스를 만드는 것
```
n=1: 'a', 'b', 'c', 'd'
n=2: 'ab', 'bc', 'cd'
n=3: 'abc', 'bcd'
n=4: 'abcd'
```

4. 직접구성해보기

테이블 생성

CREATE TABLE IF NOT EXISTS `T_BOARD` (
`BOARD_SEQ` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`TITLE` VARCHAR(100) NOT NULL,
`CONTENT` MEDIUMTEXT NULL DEFAULT NULL,
`USER_ID` VARCHAR(350) NULL DEFAULT NULL,
`TYPE` VARCHAR(10) NULL DEFAULT NULL,
`CRE_DATE` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`BOARD_SEQ`), 
FULLTEXT INDEX `FT_TITLE` (`TITLE`)  WITH PARSER `ngram`
)
ENGINE = InnoDB
AUTO_INCREMENT = 1
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci ;

기본 ngram 토큰 사이즈 확인

안녕 뭐하고있어
-> 안녕
-> 뭐하
-> 하고
-> 고있
-> 있어
이렇게 쪼개져서 인덱스가 생성된다고 한다.
따라서 만약 안녕 | 뭐하 | ... 등으로 검색하면 해당 단어를 제목에 포함한 게시판이 검색된다.

확인


mysql> set global innodb_ft_aux_table = 'iindex/T_BOARD';
-> 'db/table'
-> 참고 : http://www.gurubee.net/lecture/4203

INSERT INTO T_BOARD(TITLE,CONTENT) VALUES('hello world','test');

SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;

![image](https://user-images.githubusercontent.com/62214428/147844673-7ee14cb1-9fa4-4c73-ab6f-066244c05dda.png)
- 의도와 다른 부분이 몇개보인다. 나중에 더 알아보자

- 4. 테스트

DELIMITER $$ DROP PROCEDURE IF EXISTS loopInsert$$

CREATE PROCEDURE loopInsert() BEGIN DECLARE i INT DEFAULT 1;

WHILE i <= 50000 DO
    INSERT INTO T_BOARD(TITLE , CONTENT)
      VALUES(concat('title',i), concat('content',i));
    SET i = i + 1;
END WHILE;

END$$ DELIMITER $$

call loopInsert;

## 성능 확인
### 1. 실패
- 기본 like `select*from T_BOARD where TITLE like '%title%';` : 0.09s
- `select*from T_BOARD where match(TITLE) against('title' IN BOOLEAN MODE);` : 1.8s

데이터보다 더 많은 인덱스를 모두 뒤져서 오히려 시간이 증가해버렸다

### 2. 성공
- `select*from T_BOARD where match(TITLE) against('+12 +23' IN BOOLEAN MODE);` : 0.01s
- `select*from T_BOARD where TITLE like '%1234%';` : 0.05s

올바른 방법은 우리가 보통 검색을 할 때 서울 동작구 맛집처럼 검색하지 서울동작구맛집으로 검색하지 않는다 즉 AGAINST('+12 +23 +34' IN BOOLEAN MODE) 와 같은 구문을 사용 해야한다.



참고 : https://blog.naver.com/PostView.nhn?blogId=jjdo1994&logNo=222348191751

skarltjr commented 2 years ago

내가 이걸 왜 기록하는가?

다양한 서비스를 사용할 때 (ex) 배달, 온라인 쇼핑 .. ) 내가 가장 많이 하는 행동이 무엇일까?
무언가를 조회하는 것. 즉 검색하는 것
따라서 실질적으로 가장 많이 사용되는 것이 바로 검색이며 여기서 성능을 뽑아내기 위한 방법을 알아보면 좋을것이라고 생각

skarltjr / Memory_Write_Record