[MySQL] MATCH AGAINST

들어가며

MATCH AGAINST란?

• MySQL에서 텍스트 검색시 LIKE '%text%' 조건으로 검색할 수 있지만 성능이 좋지 않다.
• MATCH AGAINST 키워드를 사용하면 비교적 빠른 검색이 가능하다.
• MATCH AGAINST 키워드를 사용하려면 테이블에 FULLTEXT INDEX가 추가되어있어야 한다.
• MyISAM 엔진에서는 기본 지원, InnoDB에서는 5.6 버전 이상부터 지원한다.
  • 특정 테이블 engine 확인하기 참고 : https://sg-choi.tistory.com/391#특정-테이블-engine-확인하기

주요 쿼리

테이블 생성시 FULLTEXT 인덱스 생성

CREATE TABLE board (
    ...,
    FULLTEXT KEY idx_contents (contents)
);

이미 생성되어 있는 테이블에 FULLTEXT 인덱스 추가

ALTER TABLE board ADD FULLTEXT INDEX idx_contents (contents);

조회

• "검색어1" OR "검색어2"가 포함된 데이터를 조회

  SELECT * FROM board
  WHERE MATCH(contents) AGAINST('검색어1 검색어2' IN BOOLEAN MODE);
  

• "검색어1" AND "검색어2"가 모두 포함된 데이터를 조회

  SELECT * FROM board
  WHERE MATCH(contents) AGAINST('검색어1 +검색어2' IN BOOLEAN MODE);
  

• "검색어1"이 포함된 데이터 중에 "검색어2"가 들어간 것 제외하여 조회

  SELECT * FROM board
  WHERE MATCH(contents) AGAINST('검색어1 -검색어2' IN BOOLEAN MODE);
  

• "검색어%"가 포함된 데이터를 조회 ("%검색어" 조회는 안되는듯?)

  SELECT * FROM board
  WHERE MATCH(contents) AGAINST('검색어*' IN BOOLEAN MODE);
  

• "검색어1 검색어2"가 포함된 데이터를 조회

  SELECT * FROM board
  WHERE MATCH(contents) AGAINST('"검색어1 검색어2"' IN BOOLEAN MODE);
  

테스트

테스트 테이블 생성

-- LIKE 검색용 테이블
CREATE TABLE UserV1
(
    userSeq INTEGER UNSIGNED AUTO_INCREMENT,
    name    VARCHAR(100),
    PRIMARY KEY (userSeq)
);

-- MATCH AGAINST 검색용 테이블
CREATE TABLE UserV2
(
    userSeq INTEGER UNSIGNED AUTO_INCREMENT,
    name    VARCHAR(100),
    PRIMARY KEY (userSeq),
    FULLTEXT KEY (name)
);

테스트 데이터 추가

• 편의상 자바 코드로 데이터 추가

IntStream
    .range(0, 100000)
    .forEach(value -> {
        String name = UUID.randomUUID().toString();

        jdbcTemplate.update("INSERT INTO UserV1 (name) VALUES (?)", name);
        jdbcTemplate.update("INSERT INTO UserV2 (name) VALUES (?)", name);
    });

조회 - LIKE

mysql> select count(*) from UserV1 where name like '%bcc9%';
+----------+
| count(*) |
+----------+
|       27 |
+----------+
1 row in set (0.04 sec)

조회 - MATCH AGAINST

mysql> select count(*) from UserV2 where match(name) against('bcc9');
+----------+
| count(*) |
+----------+
|       11 |
+----------+
1 row in set (0.00 sec)

결론

• LIKE 검색과 MATCH AGAINST 검색은 조회 결과가 다를 수 있다.
• 조회시 LIKE 검색은 0.04s, MATCH AGAINST는 0.00s로 MATCH AGAINST가 비교적 빠르다.

참고

• https://dogcowking.tistory.com/78
• https://hoing.io/archives/16853