[MySQL] like, fulltext index, sphinx engine cost 비교

검색 엔진 모듈화를 위한 3가지 방안의 차이점을 비교합니다.

✍️ 목차

• 1. LIKE Search
  • 1-1. 선형 검색으로 인한 속도 저하
  • 1-2. 인덱스 사용의 제한
  • 1-3. 문자열 패턴 매칭 한계
• 2. Full-Text Index Search
  • 2-1. 전문 검색 기능
  • 2-2. 인덱스 기반 검색
  • 2-3. 정확성 및 유사성 검색
  • 2-4. 확장성 제한
• 3. Sphinx Search Engine
  • 3-1. 가중치 기반 검색
  • 3-2. 문서 유사성 검색
  • 3-3. 유연성 및 통합
• 4. 비교 분석
  • sphinx
    • 검색 속도
    • 용량
    • 소요시간
    • Fulltext indexing 시 리스크
• 5. 선택 기준
  • 5-1. 데이터베이스 내장 Full-Text 검색 기능
  • 5-2. 외부 검색 엔진(Sphinx, Elasticsearch 등)

---

1. LIKE Search

LIKE 검색은 매우 간단하고 직관적이지만, 대량의 데이터베이스에서는 성능과 효율성 면에서 다소 제한적입니다.
주요 단점은 다음과 같습니다.

1-1. 선형 검색으로 인한 속도 저하

• LIKE 절은 데이터베이스의 각 레코드를 순차적으로 비교하여 일치하는지 확인하는 선형 검색 (Linear Search) 방식입니다. 이는 대량의 데이터베이스에서 검색 속도를 저하시킬 수 있습니다.

1-2. 인덱스 사용의 제한

• LIKE 절에 인덱스를 사용하여 검색 성능을 향상시킬 수 있지만, 와일드카드 문자의 사용이 제한됩니다. 와일드카드 문자가 맨 앞에 나오는 경우 인덱스가 사용되지 않을 수 있습니다.
  ex) ‘%검색’, ‘%검색%’

1-3. 문자열 패턴 매칭 한계

• LIKE 절은 완벽한 문자열 매칭만 가능하며, 부분적인 문자열 매칭이나 유사성 검색은 어려울 수 있습니다.

---

2. Full-Text Index Search

Full-Text Index Search는 텍스트 기반 검색에 특화된 검색 방법으로, LIKE 검색보다 효율적이고 성능이 뛰어납니다. 이를 위한 개념과 특징은 다음과 같습니다.

2-1. 전문 검색 기능

• 텍스트의 전문 검색을 위한 데이터 구조를 제공합니다. 이를 통해 부분적인 문자열 매칭, 단어의 유사성 검색 등을 효율적으로 수행할 수 있습니다.

2-2. 인덱스 기반 검색

• 데이터베이스의 레코드를 순차적으로 비교하는 것이 아니라 인덱스를 사용하여 효율적으로 검색할 수 있습니다.

2-3. 정확성 및 유사성 검색

• 문자열의 유사성을 고려하여 검색할 수 있으며, 이를 통해 다양한 검색 요구사항을 충족시킬 수 있습니다.

2-4. 확장성 제한

• 여러 조건자가 포함된 쿼리를 처리할 때 상당히 제한됩니다. 전체 텍스트 검색 인덱스에 포함되지 않은 추가 조건자를 사용하면 MySQL의 성능이 오히려 저하 될 수 있습니다. 또한 급격한 데이터 볼륨 증가를 예상 되는 경우 MySQL의 전체 텍스트 검색에만 의존하면 병목 현상이 발생하여 시스템을 효과적으로 확장하는 능력이 저해될 수 있습니다.

---

3. Sphinx Search Engine

Sphinx Engine은 Full-Text Index보다 더 많은 고급 기능을 제공하여 다양한 검색 요구 사항을 처리할 수 있습니다. 이러한 고급 기능은 다음과 같은 것들을 포함할 수 있습니다.

3-1. 가중치 기반 검색

• Sphinx Engine은 검색 결과에 가중치를 할당하여 특정 조건에 따라 결과를 필터링하거나 정렬할 수 있습니다. 이를 통해 특정 요구에 맞게 검색 결과를 조정할 수 있습니다.

3-2. 문서 유사성 검색

• Sphinx Engine은 문서 간의 유사성을 분석하여 관련된 문서를 찾을 수 있습니다. 이를 통해 사용자가 비슷한 내용을 가진 문서를 쉽게 발견할 수 있습니다.

3-3. 유연성 및 통합

• Sphinx는 SQL 데이터베이스와 통합할 수 있는 능력과 다양한 데이터 형식을 지원하므로 다용도 솔루션이 됩니다. 독립형 검색 서버로 사용하거나 기존 데이터베이스 시스템에 통합하여 검색 기능을 향상시킬 수 있습니다.

---

4. 비교 분석

• 1,000,000 건 더미데이터 대상
• ngram 인덱싱
• full-text search는 boolean mode 로 실행
• Column 별 크기
  • menu: 약 54 bytes
  • dummy: 4839 bytes

 

	LIKE Search	Full-Text Search	sphinx
검색 속도	40s 383ms	14s 424ms	6s180ms
용량		약 24.55 MB 증가 (인덱싱된 컬럼 : menu)	약 4.894 Gb 증가 (인덱싱된 컬럼: menu, dummy)
소요시간	Write : 56 ms	Write : 1s 256ms Indexing : 3m 39s 813ms	Indexing : 5m 35.4s
Fulltext indexing 시 리스크		테이블 잠금 및 작업 중단: 테이블에 인덱스를 추가하는 동안 테이블이 잠길 수 있으며, 작업 중단을 초래할 수 있습니다. 디스크 필요 공간: fulltext 인덱스를 추가하면 디스크 공간이 추가로 필요합니다. 테이블의 크기와 인덱싱 할 열의 데이터 유형에 따라 필요한 공간이 달라집니다. 인덱스 생성 시간: 테이블의 크기와 데이터 양에 따라 fulltext 인덱스를 생성하는 데 시간이 오래 걸릴 수 있습니다. 특히 큰 테이블의 경우 인덱스 생성이 오랜 시간이 걸리고, 이는 서비스의 가용성에 영향을 줄 수 있습니다. 성능 영향: fulltext 인덱스를 추가하면 검색 쿼리의 성능이 향상될 수 있지만, 동시에 쓰기 작업의 성능에도 영향을 미칠 수 있습니다. 특히 많은 양의 데이터를 자주 업데이트하는 경우에 이러한 영향이 더욱 두드러집니다. 기존 쿼리 및 애플리케이션 로직 변경: fulltext 인덱스를 추가하면 기존의 검색 쿼리 및 응용 프로그램 코드를 수정해야 할 수 있습니다.

 

5. 선택 기준

5-1. 데이터베이스 내장 Full-Text 검색 기능

1. 장점:
   • 데이터베이스와의 통합이 용이하며, 데이터베이스의 다른 기능과의 상호작용이 간단합니다.
   • 데이터의 일관성과 안정성을 유지하기 쉽습니다.
2. 단점:
   • 대용량 데이터의 경우 성능이 저하될 수 있습니다.
   • 검색 기능이 다양하고 복잡한 쿼리를 처리하기에는 제한적일 수 있습니다.

 

5-2. 외부 검색 엔진(Sphinx, Elasticsearch 등)

• 장점:
  • 대용량 데이터를 빠르고 효율적으로 처리할 수 있습니다.
  • 다양한 검색 기능과 유연한 쿼리 처리가 가능합니다.
• 단점:
  • 데이터베이스와의 통합이 추가 작업이 필요합니다.
  • 데이터의 일관성을 유지하기 위한 추가 관리가 필요합니다.

 

 

 

즉, 가볍고 비교적 데이터 양이 적은 환경에서는 데이터베이스 내장 Full-Text 검색 기능을 사용하고, 대용량 데이터를 처리해야 하는 환경에서는 외부 검색 엔진을 사용하는 것이 좋을 것 같습니다.