방대한 자료를 LIKE 검색을 하는데 부하를 줄일수 있는 방법

데이터베이스 부하를 줄이는 몇 가지 효과적인 방법이 있습니다.

특히 % (와일드카드)를 검색어 앞뒤에 사용하는 LIKE '%검색어%' 방식은 인덱스를 거의 사용하지 못하기 때문에 부하가 매우 커질 수 있습니다.

여기 몇 가지 전략과 구체적인 방법을 제시합니다.

1. 검색어 앞의 % 사용 최소화

가장 먼저 고려해야 할 사항입니다. 만약 LIKE '검색어%' 처럼 검색어 뒤에만 와일드카드를 사용한다면, 해당 컬럼에 **인덱스(Index)**가 걸려 있을 경우 인덱스를 활용하여 검색 속도를 크게 향상시킬 수 있습니다.

하지만 LIKE '%검색어' 나 LIKE '%검색어%' 는 대부분의 경우 인덱스를 활용하지 못하고 테이블 전체를 스캔(Full Table Scan)하게 됩니다. 이것이 부하의 주범입니다.

해결 방안:

• 검색 정책 변경 고려: 사용자에게 검색어 앞의 와일드카드(%) 사용을 제한하거나, 검색어의 시작 부분만 입력하도록 유도합니다. (예: "이름으로 검색 시, 시작 글자만 입력하세요.")

• 부분 검색이 필수라면 다른 전략 사용: 아래 방법들을 고려합니다.

2. Full-Text Search (전문 검색) 활용

데이터베이스에서 제공하는 전문 검색 기능을 활용하는 것이 LIKE '%검색어%'의 가장 강력한 대안입니다. 대량의 텍스트 데이터에서 유연하고 효율적인 검색을 가능하게 합니다.

• MySQL (InnoDB) - FULLTEXT 인덱스:

  • 특정 컬럼에 FULLTEXT 인덱스를 생성합니다.

  • MATCH (column_name) AGAINST ('검색어' IN NATURAL LANGUAGE MODE) 또는 IN BOOLEAN MODE 등을 사용하여 검색합니다.

  • 장점: LIKE보다 훨씬 빠르고 관련성 높은 결과를 제공합니다. 한국어의 경우 형태소 분석기가 필요할 수 있습니다 (Mecab 등).

  • 예시: SQL

    ALTER TABLE your_table ADD FULLTEXT(column_name);
    SELECT * FROM your_table WHERE MATCH(column_name) AGAINST('검색어');
    
    

• PostgreSQL - tsvector와 tsquery:

  • GIN 또는 GiST 인덱스를 사용하여 tsvector 타입의 컬럼에 인덱스를 생성합니다.

  • to_tsvector('korean', text_column)과 to_tsquery('korean', '검색어')를 활용합니다.

  • 장점: 매우 강력하고 유연한 전문 검색을 제공합니다. 한국어 형태소 분석 플러그인(KoNLP, pgroonga 등)을 활용할 수 있습니다.

3. 외부 검색 엔진(Search Engine) 도입

데이터베이스만으로는 한계가 있을 때 고려하는 방법입니다. Elasticsearch, Solr, Sphinx와 같은 전용 검색 엔진은 대량의 비정형 데이터를 인덱싱하고 초고속으로 검색하는 데 특화되어 있습니다.

• Elasticsearch (가장 많이 사용됨):

  • 데이터베이스의 데이터를 Elasticsearch로 동기화(또는 실시간으로 반영)합니다.

  • 사용자 검색 요청은 Elasticsearch로 보내고, 검색 결과를 다시 DB에서 조회하여 합칩니다.

  • 장점: 매우 빠르고 확장성이 뛰어나며, 복잡한 쿼리와 랭킹(relevance ranking) 기능을 제공합니다. 한국어 형태소 분석기(Nori, Seunjeon 등)를 쉽게 통합할 수 있습니다.

  • 단점: 별도의 시스템을 구축하고 유지보수해야 하는 복잡성이 추가됩니다.

4. 인덱스 최적화 및 활용

LIKE 검색의 특정 패턴에서는 인덱스를 활용할 수 있습니다.

• LIKE '검색어%'에 B-Tree 인덱스 사용:

  • 이 경우 해당 컬럼에 일반적인 B-Tree 인덱스를 생성하면 인덱스 범위를 스캔(Index Range Scan)하여 효율적으로 검색할 수 있습니다.

  • CREATE INDEX idx_col ON your_table (column_name);

• Trigram 인덱스 (PostgreSQL, 일부 다른 DB):

  • LIKE '%검색어%'와 같은 패턴에 유용한 인덱스입니다. 텍스트를 3글자씩(tri-grams) 쪼개어 인덱싱합니다.

  • PostgreSQL에서는 pg_trgm 확장을 사용합니다. GIN 또는 GiST 인덱스를 생성합니다.

  • 예시 (PostgreSQL): SQL

    CREATE EXTENSION pg_trgm;
    CREATE INDEX trgm_idx ON your_table USING GIN (column_name gin_trgm_ops);
    SELECT * FROM your_table WHERE column_name LIKE '%검색어%';
    
    

  • 장점: LIKE '%검색어%' 패턴에 대해 인덱스를 활용할 수 있게 해줍니다.

  • 단점: 인덱스 크기가 커지고, 쓰기 작업(INSERT/UPDATE/DELETE) 시 부하가 증가할 수 있습니다.

5. 데이터베이스 설정 및 하드웨어 최적화

• 메모리(RAM) 증설: 데이터베이스 버퍼 캐시 크기를 늘려 디스크 I/O를 줄입니다.

• SSD 사용: 디스크 I/O 성능이 병목이라면 SSD로 교체하는 것이 매우 효과적입니다.

• 데이터베이스 튜닝: innodb_buffer_pool_size (MySQL), shared_buffers (PostgreSQL) 등 주요 메모리 관련 파라미터를 적절히 설정합니다.

• 쿼리 캐시 (MySQL 5.7 이하): query_cache_size를 설정하여 동일 쿼리 반복 시 캐시된 결과를 사용합니다. (MySQL 8.0부터는 제거됨)

6. 애플리케이션 레벨 캐싱

• 자주 검색되는 키워드나 검색 결과는 애플리케이션 레벨에서 캐싱하여 데이터베이스 접근 횟수를 줄입니다. Redis, Memcached 등을 활용할 수 있습니다.

• 예를 들어, 인기 검색어에 대한 결과는 일정 시간 동안 캐시해두어 매번 DB를 조회하지 않도록 합니다.

7. 데이터 구조 변경 또는 정규화/비정규화 고려

• 검색용 컬럼 생성: 검색에 사용되는 특정 필드를 조합하여 검색 전용 컬럼을 만들고, 이 컬럼에 인덱스를 걸 수도 있습니다. (예: title || ' ' || content 같은 조합 컬럼)

• 역정규화 (Denormalization): 검색 성능을 위해 일부 데이터를 중복 저장하거나, 조인이 많이 발생하는 경우 미리 조인된 형태의 테이블을 생성하여 검색 복잡성을 줄입니다. (쓰기 부하 증가 가능성 유의)

결론 및 권장 사항:

• 가장 먼저 LIKE '%검색어%' 패턴을 피할 수 있는지 검토하고, 가능하다면 LIKE '검색어%'로 변경하고 컬럼에 B-Tree 인덱스를 거는 것이 가장 간단하고 효과적인 해결책입니다.

• 부분 검색이 필수적이고 대용량 데이터라면, Full-Text Search (MySQL의 FULLTEXT 또는 PostgreSQL의 tsvector/tsquery)를 도입하는 것을 강력히 권장합니다.

• Full-Text Search로도 부족하거나 더 복잡한 검색 요구사항이 있다면 Elasticsearch와 같은 외부 검색 엔진 도입을 고려해야 합니다.