spring, db에 의존적인 sql 및 함수 다루기 (mysql ngram과 함께)

2023-04-28 • infoqoch •

spring

•

ngram을 사용할 일이 있었으며, 마침 mysql이 지원하였다

ngram?

ngram은 인덱스 중 하나로 자동완성에 자주 사용하는 기능이다.
단어를 정해진 갯수로 자른 모든 조합을 인덱스로 배치한 후 그 단어를 맵핍하는 방법이다.
- 쿼리 : eating
- 사이즈 : 3
- 결과 : eat, ati, tin, ing
ngram에 대하여 쿼리를 할 경우 DB는 다음과 같으 방식으로 응답한다.
- 쿼리 : eat
- 결과 : ‘eat’, ‘eat’ting, w’eat’her, sw’eat’
ngram은 보통 자동완성에 사용한다.
- 쿼리 : 노트북
- 응답 : 삼성’노트북’, ‘노트북’케이스 등

mysql의 지원

한편, mysql은 ngram을 지원하며 매우 간단하게 사용 가능하다. 성능이나 복잡한 기능이 필요하지 않을 경우 mysql로 테스트 해보기를 추천한다!

-- my_table의 word를 ngram으로 만든다
ALTER TABLE my_table ADD FULLTEXT INDEX my_table_ngram_word (word) WITH PARSER ngram;

쿼리는 일반 match라는 mysql의 특화 함수를 사용한다.
where 문으로 해당 인덱스에 대한 조회를 수행하며
order by를 통해 일치 수준에 따라 정렬한다.

select *
from
    my_table tb
where
    match(tb.word) against (?1 in natural language mode)>0
order by
    match(tb.word) against (?1 in natural language mode) desc,
    match(tb.word) against (?1 in boolean mode) desc
-- ?1 = ord

db에 존재했던 값과, 쿼리의 응답 값 및 정렬 순서는 다음과 같다.
db : [run, swordsman, sword, wording, word]
response : [word, wording, sword, swordsman]

JDBCTemplate을 사용하여 ngram index 사용하기

현재 spring-data-jpa와 함께 ddl-auto=create을 사용 중이다.
DB에 의존적인 인덱스를 jpa가 생성하지 못한다. 별도로 ngram 인덱스를 생성할 ddl을 작성 및 요청해야 한다.
JDBCTemplate을 사용하여 작성하였다. 스프링 컨텍스트가 완료 된 후 동작하도록 CommandLineRunner를 사용하였다.

import com.zaxxer.hikari.HikariConfig;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateProperties;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.sql.DataSource;

@Component
@RequiredArgsConstructor
public class Run implements CommandLineRunner {
    private final HibernateProperties hibernateProperties;
    private final HikariConfig hikariConfig;
    private final DataSource dataSource;

    @Override
    public void run(String... args) {
        log.info("hibernate ddl-auto : {}", hibernateProperties.getDdlAuto());
        log.info("db driver : {}", hikariConfig.getDriverClassName());

        if(hikariConfig.getDriverClassName().toLowerCase().contains("mysql")
                && hibernateProperties.getDdlAuto().toLowerCase().contains("create")){
            JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
            log.info("query : ALTER TABLE my_table ADD FULLTEXT INDEX my_table_ngram_word (word) WITH PARSER ngram;");
            jdbcTemplate.execute("ALTER TABLE my_table ADD FULLTEXT INDEX my_table_ngram_word (word) WITH PARSER ngram;");

        }
    }
}

MySQL8Dialect을 사용하여 mysql 방언 사용히기

jpa에서 MySQL에 특화된 쿼리를 사용하기 위해서는 MySQL8Dialect을 사용한다.
작성 및 사용은 다음과 같다. MySQL8Dialect 구현체와 함께 spring.jpa.properties.hibernate.dialect 에 해당 클래스의 경로를 작성한다.
?1과 ?2는 String.format처럼 인자의 순서에 따라 동적으로 입력된다.

import org.hibernate.dialect.MySQL8Dialect;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.type.StandardBasicTypes;

public class MySQL8DialectCustom extends MySQL8Dialect {
    public MySQL8DialectCustom(){
        super();
        registerFunction("match_bool", new SQLFunctionTemplate(StandardBasicTypes.DOUBLE, "match(?1) against (?2 in boolean mode)"));
        registerFunction("match_nat", new SQLFunctionTemplate(StandardBasicTypes.DOUBLE, "match(?1) against (?2 in natural language mode)"));
    }
}

spring:
  jpa:
    hibernate:
      ddl-auto: create
    properties:
      hibernate:
        dialect: com.example.MySQL8DialectCustom

queryDSL을 사용 중이었으며 다음과 같이 코드를 작성하였다.

import com.querydsl.core.types.dsl.Expressions;
import com.querydsl.core.types.dsl.NumberTemplate;

NumberTemplate<Double> matchBool = Expressions.numberTemplate(Double.class,
        "function('match_bool',{0},{1})", qMyTable.word, query);

NumberTemplate<Double> matchNat = Expressions.numberTemplate(Double.class,
        "function('match_nat',{0},{1})", qMyTable.word, query);

List<MyTable> fetch = queryFactory
        .select(qMyTable)
        .from(qMyTable)
        .where(matchNat.gt(0))
        .orderBy(matchNat.desc(), matchBool.desc())
        .fetch();

mysql에 크게 의존적인 코드

DB를 변경할 일이 없고 자동완성 기능을 간편하게 구현할 목적이라면, 위와 같이 코드를 작성할 수 있다.
mariadb의 경우, 해당 쿼리 문법은 존재하지만 라이브러리는 내장되어 있지 않다. 별도의 라이브러리를 설치해야 하는 것 같다.