[MySQL] 검색 쿼리 성능 비교

개요

- ner을 포함하는 기사들을 출력할 때, 효율적으로 검색하기 위한 쿼리 성능 비교

    1) JSON_CONTAIN

    2) MATCH … AGAINST

    3) MATCH … AGAINST + REGEXP

- 100,000개의 article로 진행

 

 

확인

EXPLAIN ANALYZE 로 분석을 진행했다.

 

1. JSON_CONTAIN

EXPLAIN ANALYZE
  SELECT * FROM articles WHERE JSON_CONTAINS(ner, '"삼성"');

-> Filter: json_contains(articles.ner,<cache>('"삼성"'))
   (cost=10185.55 rows=98728)
   (actual time=0.148..102.554 rows=27555 loops=1)

-> Table scan on articles
   (cost=10185.55 rows=98728)
   (actual time=0.099..43.564 rows=100000 loops=1)

• Table scan 발생: 전체 articles 테이블을 처음부터 끝까지 훑음 (100,000 rows)
• json_contains()는 인덱스를 사용하지 못하므로 모든 행을 직접 검사
• actual time: 전체 소요 시간은 약 103ms 정도 걸림

 

2. MATCH … AGAINST

EXPLAIN ANALYZE
  SELECT * FROM articles
  WHERE MATCH(ner_text) AGAINST('삼성' IN BOOLEAN MODE);

-> Filter: (match articles.ner_text against ('+삼성' in boolean mode))
   (cost=0.35 rows=1)
   (actual time=13.105..77.032 rows=49819 loops=1)

-> Full-text index search on articles using idx_ner_text_ngram (ner_text='+삼성')
   (cost=0.35 rows=1)
   (actual time=13.104..74.931 rows=49819 loops=1)

• FULLTEXT 인덱스 사용: idx_ner_text가 사용됨
• 예상 비용 (cost)이 매우 낮음 (0.35) → 매우 가벼운 작업으로 인식
• actual time: 13.105ms ~ 77.032ms
  • 13.105ms : 첫번재 row 나온 시간
  • 77.032ms : 전체 49819건 나온 시간
  • 이건 인덱스를 통해 50,126개의 매칭 row를 찾음

 

✅ JSON_CONTAIN, MATCH … AGAINST 의 출력 개수가 다르다.

• JSON_CONTAIN: 27555
• MATCH … AGAINST: 49819
• MATCH … AGAINST으로 ‘삼성’이라는 키워드는 검색했을 때, ‘삼성’을 포함하는 ner을 모두 검색한다.
  • 예: 삼성, 삼성전자 등..
• 만약 ‘삼성’을 포함하는 단어가 아닌, 오직 ‘삼성’만 검색하려면 아래의 쿼리를 사용해야한다.

 

3. MATCH … AGAINST + REGEXP

EXPLAIN ANALYZE
  SELECT COUNT(*) FROM articles
  WHERE MATCH(ner_text) AGAINST('+삼성' IN BOOLEAN MODE)
  AND ner_text REGEXP '\\b삼성\\b';

-> Filter: ((match articles.ner_text against ('+삼성' in boolean mode)) and regexp_like(articles.ner_text,'\\b삼성\\b'))
   (cost=0.35 rows=1)
   (actual time=9.632..87.606 rows=27555 loops=1)

-> Full-text index search on articles using idx_ner_text_ngram
   (ner_text='+삼성')  (cost=0.35 rows=1)
   (actual time=9.528..73.840 rows=49819 loops=1)

• JSON_CONTAIN으로 검색했을 때와 27555으로 동일함
• REGEXP는 인덱스를 사용하지 못하므로 후처리 단계에서 시간이 추가됨
• 그래도 Fulltext 인덱스로 먼저 필터를 걸었기 때문에 전체 테이블 스캔보다는 훨씬 빠름

 

 

정리

	JSON_CONTAINS	MATCH ... AGAINST
인덱스 사용	불가 (테이블 전체 스캔)	FULLTEXT 인덱스 사용
속도 (실행 시간)	빠르게 시작하지만 오래 걸림	약간 느리게 시작하지만 더 최적화됨
예측 비용	높음	매우 낮음
필터링	JSON 내부값 포함 여부 검사	텍스트 기반 단어 매칭

 

➡️ 데이터가 많지 않을 때는 JSON_CONTAINS 가 더 빠르지만

크롤링을 진행하며, 데이터는 계속 누적되면서 양이 많아질 것 이고

운영 비용도 줄이기 위해서는 MATCH ... AGAINST가 더 효율적이라고 판단됨

 

➡️ 키워드 검색 시, 그 키워드는 포함하는 것 모두 출력할 것인지 / 그 키워드만 출력할 것인지는 프로젝트 방향에 맞출예정