검색 기능

쿠팡에는 키워드로 상품을 검색하는 기능이 있다
이 기능을 더 효율적인 쿼리로 구현하기 위해서는 어떻게 할 수 있을지 고민해보았다!

1. LIKE

쿼리

SELECT * 
FROM coupang.PRODUCT
WHERE name LIKE '%키워드%';

성능 테스트

• Execution Time
  • 2.862 sec
• Explain
  
  • type : ALL
    • 인덱스를 타지 못하고, full scan 하는 것을 확인할 수 있다.
  • rows : 49488572
    • 전체 로우를 검사한다.

문제점

• LIKE 에서 물티슈%와 같이, 와일드카드를 뒤쪽에 붙였을 때에는 인덱스를 잘 활용한다.
• 하지만, %물티슈, %물티슈% 와 같이% 와일드카드를 앞에 붙이면 인덱스를 활용하지 못하고, full scan 하여 성능이 매우 떨어지는 것이다.
  • mysql 버전에 따라 %물티슈 처럼 앞에만 와일드카드를 붙이는 경우에는 인덱스를 탈 수 있다고 한다.

2. fulltext

쿼리

1. FULLTEXT 인덱스 추가

ALTER TABLE `coupang`.`PRODUCT` 
ADD FULLTEXT INDEX `name` (`name`) 
WITH PARSER NGRAM VISIBLE;

2. 검색 쿼리 작성

-- QUERY 1
SELECT * 
FROM coupang.PRODUCT 
WHERE MATCH (`name`) AGAINST ('2312341' IN NATURAL LANGUAGE MODE);

• 이 쿼리는 NGRAM 파서로 나누어진 단어들도 찾기 때문에 잘못된 결과가 나오기 쉽다.
• 위의 쿼리를 실행했을 때, 일치하지 않는 칼럼도 나오게 된다.
  

-- QUERY 2
SELECT * 
FROM coupang.PRODUCT 
WHERE MATCH (`name`) AGAINST ('2312341' IN BOOLEAN MODE);

• 첫번째 쿼리에서 모드를 BOOLEAN 으로 바꾸었다.
• 그러나 아래와 같은 캐시 제한에 걸렸고, 최대한으로 늘려보아도 부족하여 해결할 수 없었다.

  [ERROR] FTS query exceeds result cache limit mysql

  • 캐시 한도를 두 배로 올렸지만, 계속 같은 오류가 발생하였음
    SET GLOBAL innodb_ft_result_cache_limit = 4000000000;

성능 테스트

• Explain
  

3. 검색 엔진

• 추후에 검색 엔진을 사용해서 검색 성능을 높여보겠다.

reference

• https://stackoverflow.com/questions/10354248/optimizing-mysql-like-string-queries-in-innodb
• https://gongzza.github.io/database/mysql-fulltext-search/