• Trailing Wildcard(word*)는 가능하고 Leading Wildcard(*word)는 불가능한 이유는?
• B-Tree 인덱스와 Full-Text 인덱스의 제약이 동일한가?
• 정렬된 데이터 구조에서 prefix search와 suffix search의 시간 복잡도 차이는?

• 핵심 포인트 💡:

  • Full-Text 인덱스는 단어를 알파벳 순으로 정렬하여 저장하므로 Leading Wildcard를 지원할 수 없습니다
  • Trailing Wildcard는 Binary Search + 순차 스캔으로 O(log n + m)에 처리 가능합니다
  • Leading Wildcard는 Full Index Scan이 필요하여 O(n)이 소요되므로 인덱스 사용 의미가 없습니다
  • B-Tree 인덱스도 동일한 이유로 Leading Wildcard를 지원하지 않습니다

• 결론

  • MySQL Full-Text 인덱스는 정렬된 prefix 기반 구조로 설계되어 있어 Leading Wildcard를 지원하지 않습니다. MySQL 8.4 Reference Manual - Boolean Full-Text Searches (opens in a new tab)

  • 위 출처에서 "The asterisk serves as the truncation (or wildcard) operator. Unlike the other operators, it is appended to the word to be affected" 라고 명시되어 있습니다.

  • 단어의 왼쪽 문자가 인덱스 구조에서 가장 중요한 부분이므로, *word와 같은 검색은 인덱스를 효과적으로 활용할 수 없습니다.

• Trailing Wildcard(data*)가 작동하는 이유

  • -- ✅ 인덱스 활용 가능
    SELECT * FROM articles
    WHERE MATCH(title, body) AGAINST('data*' IN BOOLEAN MODE);
  • 검색 과정:
    1. Binary Search로 'data'로 시작하는 첫 단어 찾기: O(log n)
    2. 'data'로 시작하는 단어들만 순차 스캔: O(m)
       • 'data' ← 시작점
       • 'database' ← 매칭
       • 'datatype' ← 매칭
       • 'dbms' ← 중단 ('data'로 시작하지 않음)
  • 총 시간 복잡도: O(log n + m), 여기서 n은 전체 단어 수, m은 결과 수입니다.

• Leading Wildcard(*base)가 불가능한 이유

  • -- ❌ 인덱스 활용 불가
    SELECT * FROM articles
    WHERE MATCH(title, body) AGAINST('*base' IN BOOLEAN MODE);
  • 문제점: 'base'로 끝나는 단어들이 인덱스 전체에 흩어져 있습니다.

  • FTS_index_1:
      ├─ abase     ← 여기서 발견
      ├─ database  ← 여기서 발견
      └─ dbms
    
    FTS_index_2:
      └─ firebase  ← 여기서 발견
    
    FTS_index_5:
      └─ supabase  ← 여기서 발견
  • 모든 인덱스 테이블을 처음부터 끝까지 스캔해야 하므로 시간 복잡도는 O(n)입니다. 이는 인덱스를 사용하지 않는 것과 동일한 성능입니다.

• B-Tree 인덱스와의 유사성

  • B-Tree 인덱스도 동일한 제약이 있습니다. MySQL 9.4 Reference Manual - B-Tree Index Characteristics (opens in a new tab)

  • 위 출처에서 "A comparison of the form col_name LIKE 'Patrick%' uses the index on col_name. A comparison of the form col_name LIKE '%Patrick%' does not use the index" 라고 명시되어 있습니다.

  • CREATE INDEX idx_name ON users(name);
     
    -- ✅ Trailing: 인덱스 사용 (range scan)
    SELECT * FROM users WHERE name LIKE 'Patrick%';
     
    -- ❌ Leading: 인덱스 사용 불가 (full table scan)
    SELECT * FROM users WHERE name LIKE '%Patrick%';

• 시간 복잡도 비교

  • 검색 유형	Binary Search	순차 스캔	총 시간 복잡도	결과
    Trailing Wildcard (data*)	O(log n)	O(m)	O(log n + m)	✅ 빠름
    Leading Wildcard (*base)	❌ 불가능	O(n)	O(n)	❌ 느림
    Exact Match (database)	O(log n)	O(1)	O(log n)	✅ 가장 빠름

  • m = 결과 수 (일반적으로 n보다 훨씬 작음), n = 전체 인덱스 엔트리 수

• 출처

  • MySQL 8.4 Reference Manual - Boolean Full-Text Searches (opens in a new tab)
  • MySQL 9.4 Reference Manual - B-Tree Index Characteristics (opens in a new tab)
  • Stack Overflow - Wildcard search in MySQL full-text search (opens in a new tab)

• 역색인(Inverted Index)이란 무엇이고 어떻게 동작하나?
• 왜 6개의 보조 인덱스 테이블로 분할하나?
• FTS_DOC_ID는 어떤 값이 예약되어 있나?
• 캐싱 메커니즘은 어떻게 동작하나?

• 핵심 포인트 💡:

  • 역색인(Inverted Index) 구조: 단어 → 문서 목록 매핑으로 빠른 검색 지원
  • 6개의 보조 인덱스 테이블로 분할하여 병렬 인덱스 생성 가능
  • Full-Text Index Cache로 삽입 최적화 후 배치 플러시
  • FTS_DOC_ID는 0이 예약된 내부 문서 식별자

• 결론

  • InnoDB Full-Text 인덱스는 역색인 구조를 사용하며, 6개의 보조 테이블로 분할되어 병렬 처리를 지원합니다. MySQL 8.4 Reference Manual - InnoDB Full-Text Indexes (opens in a new tab)

  • 위 출처에서 "The inverted index is partitioned into six auxiliary index tables to support parallel index creation. When incoming documents are tokenized, the individual words are inserted into the index tables along with position information and an associated DOC_ID" 라고 명시되어 있습니다.

• 역색인 (Inverted Index) 구조

  • 일반 인덱스와 달리 역색인은 단어를 키로 사용하여 해당 단어가 포함된 문서 목록을 저장합니다.

  • 역색인 예시:
    database → [DOC_ID: 1, POSITION: 31], [DOC_ID: 5, POSITION: 44]
    mysql    → [DOC_ID: 1, POSITION: 0], [DOC_ID: 2, POSITION: 15]
    tutorial → [DOC_ID: 1, POSITION: 6], [DOC_ID: 3, POSITION: 0]
  • 장점: 단어 검색 시 해당 단어가 포함된 모든 문서를 즉시 찾을 수 있습니다.

• 전체 아키텍처

  • ┌─────────────────────────────────────────────────────────────┐
    │                메인 테이블 (articles)                          │
    │  ┌─────┬────────────┬──────────┬───────────────┐            │
    │  │ id  │ title      │ body     │ FTS_DOC_ID    │ (hidden)   │
    │  ├─────┼────────────┼──────────┼───────────────┤            │
    │  │ 1   │ "MySQL...  │ "DBMS..  │ 1             │            │
    │  │ 2   │ "How To..  │ "After.  │ 2             │            │
    │  └─────┴────────────┴──────────┴───────────────┘            │
    └─────────────────────────────────────────────────────────────┘
                                 │
                                 │ FTS_DOC_ID로 매핑
                                 ▼
    ┌─────────────────────────────────────────────────────────────┐
    │              Full-Text Index Cache (메모리)                   │
    │  최근 삽입된 행의 토큰을 임시로 캐싱                              │
    │  캐시가 가득 차면 → Batch Flush → Auxiliary Tables           │
    └─────────────────────────────────────────────────────────────┘
                                 │
                                 ▼
    ┌─────────────────────────────────────────────────────────────┐
    │         6개의 보조 인덱스 테이블 (Auxiliary Index Tables)        │
    │                                                             │
    │  FTS_*_index_1  │  단어 첫 글자 정렬 가중치에 따라 분할         │
    │  FTS_*_index_2  │  ↓                                       │
    │  FTS_*_index_3  │  병렬 인덱스 생성 지원                       │
    │  FTS_*_index_4  │  (기본 2개 스레드)                          │
    │  FTS_*_index_5  │                                           │
    │  FTS_*_index_6  │                                           │
    │                                                             │
    │  + FTS_*_deleted (삭제된 문서 DOC_ID)                         │
    │  + FTS_*_being_deleted (삭제 처리중)                          │
    │  + FTS_*_config (내부 상태)                                  │
    └─────────────────────────────────────────────────────────────┘

• 6개 테이블 분할 메커니즘

  • 병렬 인덱스 생성을 지원하기 위해 단어의 첫 글자 문자 집합 정렬 가중치(character set sort weight)로 6개 테이블에 분할합니다.

  • FTS_index_1: 가중치 구간 1
      ├─ abase     → [DOC_ID: 3, POSITION: 10]
      ├─ database  → [DOC_ID: 1, POSITION: 31]
      └─ dbms      → [DOC_ID: 1, POSITION: 0]
    
    FTS_index_2: 가중치 구간 2
      └─ engine    → [DOC_ID: 4, POSITION: 22]
    
    FTS_index_3: 가중치 구간 3
      └─ mysql     → [DOC_ID: 1, POSITION: 0]
  • 각 테이블 내에서 단어들이 완전히 정렬(fully sorted)되며, 여러 스레드가 동시에 다른 테이블을 처리할 수 있습니다.
  • 병렬 처리 스레드 수 조정:

    SET GLOBAL innodb_ft_sort_pll_degree = 4;  -- 4개 스레드 사용

• 보조 인덱스 테이블 내부 구조

  • CREATE TABLE fts_index_1 (
      word VARCHAR(FTS_MAX_WORD_LEN),           -- 인덱싱된 단어
      first_doc_id INT NOT NULL,                -- 처음 나타나는 문서 ID
      last_doc_id INT NOT NULL,                 -- 마지막 나타나는 문서 ID
      doc_count INT NOT NULL,                   -- 총 문서 개수
      ilist VARBINARY NOT NULL,                 -- 역색인 리스트 (압축 저장)
      UNIQUE CLUSTERED INDEX ON (word, first_doc_id)
    );

• FTS_DOC_ID: 내부 문서 식별자

  • PRIMARY KEY와 독립적인 내부 식별자입니다. MySQL Developer Documentation - fts0fts.h (opens in a new tab)

  • 위 출처에서 "constexpr doc_id_t FTS_NULL_DOC_ID = 0" 이라고 명시되어 있습니다.

  • // InnoDB 소스 코드 (fts0fts.h)
    #define FTS_NULL_DOC_ID 0  // NULL/Invalid 예약값
  • 예약된 값:
    • 0 = FTS_NULL_DOC_ID (NULL/Invalid로 예약)
    • 1 이상 = 실제 문서 ID로 사용 가능

• 캐싱 메커니즘 (Full-Text Index Cache)

  • 목적:
    • 동시 접근 충돌(concurrent access contention) 방지
    • 작은 삽입들을 배치 처리(batch flush)로 최적화
    • 디스크 I/O 최소화
  • 동작 과정:

    1. INSERT 실행
       ↓
    2. 토큰화 (병렬 처리: 기본 2개 스레드)
       ↓
    3. Full-Text Index Cache에 임시 저장 (메모리)
       ↓
    4. 캐시가 가득 차면 Batch Flush
       (innodb_ft_cache_size 도달 또는 OPTIMIZE TABLE 실행)
       ↓
    5. Auxiliary Tables에 저장 (디스크)
  • 캐시 설정:

    -- 캐시 크기 조정 (기본: 8MB)
    SET GLOBAL innodb_ft_cache_size = 80000000;  -- 80MB
     
    -- 캐시 내용 확인
    SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE LIMIT 10;
     
    -- 수동 플러시
    OPTIMIZE TABLE articles;

• 검색 흐름

  • SELECT * FROM articles
    WHERE MATCH(title, body) AGAINST('mysql database');

  • 1. 쿼리 토큰화: ['mysql', 'database']
       ↓
    2. 각 단어를 적절한 보조 테이블에서 병렬 검색
       Thread 1: 'mysql'    → FTS_index_3에서 Binary Search
                 결과: DOC_ID [1, 2, 4, 6]
       Thread 2: 'database' → FTS_index_1에서 Binary Search
                 결과: DOC_ID [1, 3, 5]
       ↓
    3. Full-Text Index Cache도 함께 검색
       ↓
    4. 결과 병합 (Boolean 연산: 기본 AND)
       교집합: DOC_ID [1]
       ↓
    5. 관련도 점수 계산 및 정렬
       ↓
    6. 메인 테이블에서 실제 행 조회
       SELECT * FROM articles WHERE id IN (1)

• 성능 최적화 메커니즘

  • ✅ Binary Search: 정렬된 구조로 O(log n) 검색
  • ✅ 병렬 인덱스 생성: 6개 테이블 × 여러 스레드
  • ✅ 캐시 메커니즘: 배치 플러시로 디스크 I/O 감소
  • ✅ 위치 정보 압축: ilist 필드에 압축 저장

• 출처

  • MySQL 8.4 Reference Manual - InnoDB Full-Text Indexes (opens in a new tab)
  • MySQL Developer Documentation - fts0fts.h (opens in a new tab)
  • MySQL 8.0 Reference Manual - InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables (opens in a new tab)

• FIRST_DOC_ID와 LAST_DOC_ID는 문서 ID의 범위를 나타내나 순서를 나타내나?
• INNODB_FT_INDEX_TABLE과 INNODB_FT_INDEX_CACHE의 차이는?
• DOC_COUNT는 단어가 나타나는 문서 개수인가, 단어의 총 출현 횟수인가?

• 핵심 포인트 💡

  • FIRST_DOC_ID: 해당 단어가 처음 나타나는 문서의 ID (범위의 시작)
  • LAST_DOC_ID: 해당 단어가 마지막으로 나타나는 문서의 ID (범위의 끝)
  • DOC_COUNT: 해당 단어가 나타나는 총 문서 개수
  • Full-Text Index Cache: 최근 삽입된 문서는 캐시에 임시 저장 후 배치로 디스크에 플러시

• INNODB_FT_INDEX_TABLE 컬럼 정의

  • MySQL 8.4 Reference Manual - The INFORMATION_SCHEMA INNODB_FT_INDEX_TABLE Table (opens in a new tab)

  • 위 출처에서 다음과 같이 명시되어 있습니다:

    • WORD: "A word extracted from the text of the columns that are part of a FULLTEXT."
    • FIRST_DOC_ID: "The first document ID in which this word appears in the FULLTEXT index."
    • LAST_DOC_ID: "The last document ID in which this word appears in the FULLTEXT index."
    • DOC_COUNT: "The number of rows in which this word appears in the FULLTEXT index. The same word can occur several times within the cache table, once for each combination of DOC_ID and POSITION values."
    • DOC_ID: "The document ID of the row containing the word."
    • POSITION: "The position of this particular instance of the word within the relevant document identified by the DOC_ID value."

  • 컬럼	의미
    WORD	FULLTEXT 인덱스에 포함된 컬럼 텍스트에서 추출된 단어
    FIRST_DOC_ID	해당 단어가 처음 나타나는 문서 ID
    LAST_DOC_ID	해당 단어가 마지막으로 나타나는 문서 ID
    DOC_COUNT	해당 단어가 나타나는 행(row)의 개수
    DOC_ID	해당 단어를 포함하는 문서의 ID
    POSITION	문서 내에서 단어가 나타나는 위치

  • 핵심 개념
    • 단일 문서만 포함: FIRST_DOC_ID = LAST_DOC_ID
    • 여러 문서 포함: FIRST_DOC_ID < LAST_DOC_ID
    • 문서 ID의 범위를 나타냄 (순서가 아님)

• 실제 예시

  • 단일 문서 (ngram parser):

    INSERT INTO books(title, author)
    VALUES('철학은 어떻게 삶의 무기가 되는가','야마구치 슈');
     
    -- INNODB_FT_INDEX_CACHE 조회 결과
    +--------+--------------+-------------+-----------+--------+----------+
    | WORD   | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
    +--------+--------------+-------------+-----------+--------+----------+
    | 철학    |            1 |           1 |         1 |      1 |        0 |
    | 학은    |            1 |           1 |         1 |      1 |        3 |
    | 어떻    |            1 |           1 |         1 |      1 |       10 |
    +--------+--------------+-------------+-----------+--------+----------+
  • 다중 문서:

    INSERT INTO articles VALUES
      (1, 'MySQL Tutorial', 'DBMS stands for database...'),
      (2, 'How To Use MySQL', 'After you went through...'),
      (3, 'MySQL vs MariaDB', 'In the following database...');
     
    -- "database" 단어 검색 결과
    +----------+--------------+-------------+-----------+--------+----------+
    | WORD     | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
    +----------+--------------+-------------+-----------+--------+----------+
    | database |            1 |           3 |         2 |      1 |       31 |
    | database |            1 |           3 |         2 |      3 |       44 |
    +----------+--------------+-------------+-----------+--------+----------+
  • FIRST_DOC_ID = 1: 처음으로 DOC_ID 1 문서에 나타남
  • LAST_DOC_ID = 3: 마지막으로 DOC_ID 3 문서에 나타남
  • DOC_COUNT = 2: 총 2개 문서에 나타남 (DOC_ID 1, 3)
  • 동일한 단어가 DOC_ID와 POSITION 조합마다 한 행씩 표시됨

• Full-Text Index Cache와 배치 플러시

  • MySQL 8.4 Reference Manual - InnoDB Full-Text Indexes (opens in a new tab)

  • 위 출처에서 다음과 같이 명시되어 있습니다:

    • "InnoDB uses a full-text index cache to temporarily cache index table insertions for recently inserted rows."
    • "When incoming documents are tokenized, the individual words (also referred to as 'tokens') are inserted into the index tables along with position information and an associated DOC_ID."

  • ✅ 최근 삽입된 행은 Full-Text Index Cache에 임시 저장
  • ✅ 캐시가 가득 차면 배치로 보조 인덱스 테이블(auxiliary index tables)에 플러시
  • ✅ 동일한 단어의 삽입은 병합되어 하나의 엔트리로 디스크에 플러시 (효율성 향상)

• 조회 전 필수 설정

  • -- INNODB_FT_INDEX_TABLE 조회 전 필수
    SET GLOBAL innodb_ft_aux_table = 'database_name/table_name';
  • innodb_ft_aux_table 시스템 변수를 조회할 테이블 이름으로 설정 필요

• LIKE 쿼리 대비 실제 성능 차이는 얼마나 되나?
• 관련도 순위(Relevance Ranking)는 어떻게 계산하나?
• Boolean 연산자는 어떤 것들이 지원되나?
• 어떤 경우에 Elasticsearch로 마이그레이션해야 하나?

• 핵심 포인트 💡:

  • 압도적인 성능: LIKE 대비 검색 속도가 빠르며, 특히 대규모 데이터셋에서 효과적입니다
  • 관련도 순위: 단어 희귀도, 출현 빈도 등을 고려한 자동 정렬 제공
  • Boolean 연산자: +, -, *, "" 등으로 복잡한 검색 가능
  • 운영 단순성: 별도 시스템 없이 MySQL 내에서 완결

• 결론

  • Full-Text 인덱스는 Leading Wildcard를 지원하지 않지만, LIKE보다 빠른 검색 성능과 관련도 순위 기능을 제공하여 대부분의 텍스트 검색 요구사항을 충족합니다.

• 성능 향상

  • LIKE 쿼리는 prefix 패턴(LIKE 'abc%')에서만 B-Tree 인덱스를 사용할 수 있고, 와일드카드가 앞에 오거나 중간에 위치하는 패턴(LIKE '%abc' 또는 LIKE '%abc%')에서는 Full Table Scan이 발생합니다. 반면 Full-Text 인덱스는 역색인(Inverted Index) 구조를 활용하여 패턴 위치에 관계없이 빠른 검색이 가능합니다. Medium - Index를 타지않는 쿼리 (opens in a new tab) / Stack Overflow - Full text search vs LIKE (opens in a new tab)

  • -- ❌ LIKE: Full Table Scan (O(n))
    SELECT * FROM articles WHERE body LIKE '%database%';
     
    -- ✅ Full-Text: Index Lookup (O(log n + m))
    SELECT * FROM articles
    WHERE MATCH(title, body) AGAINST('database' IN BOOLEAN MODE);
  • 실제 환경에서 Full-Text 검색이 LIKE보다 빠른 성능을 보이며, 특히 대규모 데이터셋과 짧은 검색어에서 성능 차이가 두드러집니다. MySQL 공식 문서 - Full-Text Indexes (opens in a new tab)

• 관련도 순위 (Relevance Ranking)

  • 자동 관련도 계산 요소:
    • 인덱스 내 전체 단어 수
    • 행(row)에 포함된 고유 단어 수
    • 단어의 희귀도 (희귀한 단어일수록 높은 가중치)
    • 단어의 출현 빈도
  • 실제 사용 예시:

    -- 관련도 점수와 함께 검색
    SELECT
      title,
      MATCH(title, body) AGAINST('MySQL database') as relevance
    FROM articles
    WHERE MATCH(title, body) AGAINST('MySQL database')
    ORDER BY relevance DESC;
     
    -- 결과:
    -- title: "MySQL Database Optimization"  relevance: 2.45
    -- title: "Introduction to MySQL"        relevance: 1.82
    -- title: "Database Design Patterns"     relevance: 1.23
  • LIKE 쿼리와 비교:

    -- ❌ LIKE: 관련도 순위 없음 (단순 매칭만)
    SELECT * FROM articles WHERE body LIKE '%MySQL%' AND body LIKE '%database%';
     
    -- ✅ Full-Text: 자동으로 가장 관련성 높은 결과가 상위에 노출

• Boolean 연산자 지원

  • 연산자	의미	예시
    +	반드시 포함 (AND)	+MySQL
    -	제외 (NOT)	-tutorial
    *****	Wildcard (trailing만)	data* → database, datasheet
    ""	정확한 구문 매칭	\"MySQL database\"
    ()	그룹화	+(MySQL MariaDB) -tutorial

  • 실제 활용 예시:

    -- MySQL은 반드시 포함, tutorial은 제외
    SELECT * FROM articles
    WHERE MATCH(title, body) AGAINST('+MySQL -tutorial +database*' IN BOOLEAN MODE);
     
    -- "MySQL performance"가 정확히 나타나는 문서만
    SELECT * FROM articles
    WHERE MATCH(title, body) AGAINST('"MySQL performance"' IN BOOLEAN MODE);
  • MySQL 8.4 Reference Manual - Boolean Full-Text Searches (opens in a new tab)

  • 위 출처에서 "The asterisk serves as the truncation (or wildcard) operator. Unlike the other operators, it is appended to the word to be affected" 라고 명시되어 있습니다.

• 여러 컬럼 동시 검색

  • -- ✅ 한 번의 쿼리로 title과 body를 동시에 검색
    SELECT * FROM articles
    WHERE MATCH(title, body) AGAINST('MySQL optimization');
     
    -- ❌ LIKE로 구현하면 복잡하고 느림
    SELECT * FROM articles
    WHERE (title LIKE '%MySQL%' AND title LIKE '%optimization%')
       OR (body LIKE '%MySQL%' AND body LIKE '%optimization%');

• 사용 사례별 선택 가이드

  • MySQL Full-Text를 선택해야 하는 경우:
    • ✅ 소규모~중규모 애플리케이션 (< 1000만 행)
    • ✅ 기존 MySQL 중심 아키텍처
    • ✅ 간단한 검색 요구사항 (자연어, trailing wildcard)
    • ✅ 추가 인프라 구축 어려움
    • ✅ 트랜잭션 일관성 중요
    • 예시: 블로그 검색, 소규모 이커머스 상품 검색, FAQ 검색
  • Elasticsearch로 마이그레이션해야 하는 경우:
    • ❌ 대규모 데이터셋 (> 1000만 행)
    • ❌ 복잡한 검색 (Fuzzy, 정규식, 다국어)
    • ❌ Leading wildcard 필요
    • ❌ 실시간 분석 및 집계 필요
    • 예시: 대형 이커머스, 로그 분석, 소셜 미디어 검색

• 성능 트레이드오프

  • 항목	MySQL Full-Text	LIKE	Elasticsearch
    검색 속도	⚡⚡⚡ 빠름	🐌 느림	⚡⚡⚡⚡ 매우 빠름
    Leading wildcard	❌ 불가능	✅ 가능 (느림)	✅ 가능 (빠름)
    관련도 순위	✅ 기본 제공	❌ 없음	✅ 고급 제공
    설정 복잡도	✅ 간단	✅ 없음	❌ 복잡
    운영 비용	✅ 낮음	✅ 없음	❌ 높음
    확장성	⚠️ 제한적	❌ 없음	✅ 무한

• 출처

  • MySQL 8.4 Reference Manual - InnoDB Full-Text Indexes (opens in a new tab)
  • MySQL 8.4 Reference Manual - Boolean Full-Text Searches (opens in a new tab)
  • Stack Overflow - What is Full Text Search vs LIKE (opens in a new tab)

• 왜 Elasticsearch는 분산 시스템이고 MySQL은 단일 서버인가?
• 데이터 일관성 모델의 차이는 무엇인가?
• 검색 기능 차이 (Fuzzy, Aggregation 등)는?
• 하이브리드 접근법은 언제 사용하나?

• 핵심 포인트 💡:

  • 아키텍처: Elasticsearch는 분산 검색 엔진, MySQL은 RDBMS의 부가 기능
  • 확장성: Elasticsearch는 수평 확장(샤드), MySQL은 수직 확장
  • 일관성: MySQL은 ACID (즉시 일관성), Elasticsearch는 최종 일관성 (NRT)
  • 검색 기능: Elasticsearch가 압도적으로 강력 (Fuzzy, Aggregation 등)

• 결론

  • Elasticsearch는 전문 검색 엔진으로 대규모 데이터와 복잡한 검색에 최적화되어 있으며, MySQL Full-Text는 RDBMS 내에서 기본적인 검색 기능을 제공합니다. Airbyte - Elasticsearch Vs MySQL (opens in a new tab)

  • 위 출처에서 "Elasticsearch scales horizontally across nodes with built-in sharding, making it better for very large datasets. MySQL can scale vertically and with replication, but large-scale sharding is more complex" 라고 명시되어 있습니다.

• 핵심 아키텍처 차이

  • 구분	MySQL Full-Text	Elasticsearch
    정체성	관계형 데이터베이스의 부가 기능	Apache Lucene 기반 전문 검색 엔진
    데이터 모델	테이블, 행, 컬럼 (구조화된 스키마)	JSON 문서 (NoSQL)
    처리 방식	OLTP (트랜잭션 처리)	OLAP (검색 및 분석)
    기본 구조	단일 서버	분산 시스템 (클러스터)

  • MySQL 구조:

    Client → MySQL Server → 순차 검색 → 결과 반환
  • Elasticsearch 구조:

    Client → Coordinator Node →
      ├─> Shard 1 (병렬 검색)
      ├─> Shard 2 (병렬 검색)
      ├─> Shard 3 (병렬 검색)
      └─> Shard N (병렬 검색)
    → 결과 병합 및 정렬 → 결과 반환

• 확장성 (Scalability)

  • MySQL Full-Text:
    • 수직 확장 (Vertical Scaling): CPU, 메모리 증설
    • Read Replica로 제한적 수평 확장
    • 효과적인 데이터 규모: ~100만 ~ 1000만 행
  • Elasticsearch:
    • 수평 확장 (Horizontal Scaling): 샤드로 데이터 분산
    • 노드 추가로 선형적 확장
    • 자동 샤드 리밸런싱
    • 효과적인 데이터 규모: 수십억 개 문서

  • PUT /articles
    {
      "settings": {
        "number_of_shards": 5,      // 5개 샤드로 데이터 분산
        "number_of_replicas": 2     // 각 샤드당 2개 복제본
      }
    }

• 데이터 일관성 모델

  • MySQL Full-Text:
    • ✅ ACID 트랜잭션: 완벽한 트랜잭션 지원
    • ✅ 즉시 일관성: 쓰기 후 즉시 읽기 가능
    • ✅ 참조 무결성: Foreign Key 지원
    • Knowi - Elasticsearch vs. MySQL (opens in a new tab)

    • 위 출처에서 "MySQL supports transactions and adheres to the ACID properties, ensuring data integrity and consistency" 라고 명시되어 있습니다.

    • BEGIN;
      INSERT INTO articles (title, body) VALUES ('New Article', 'Content...');
      COMMIT;
      -- 커밋 후 즉시 검색 가능
  • Elasticsearch:
    • ⚠️ 최종 일관성 (Eventual Consistency): Near Real-Time (NRT)
    • ⚠️ 1초 지연: 기본 refresh interval 1초
    • ❌ 트랜잭션 없음: 단일 문서 작업만 원자적

    • POST /articles/_doc
      {
        "title": "New Article",
        "body": "Content..."
      }
      // 기본적으로 1초 후 검색 가능

• 검색 기능 비교

  • 기능	MySQL Full-Text	Elasticsearch
    기본 검색	✅	✅
    Boolean 검색	✅ 제한적 (+, -, *)	✅ 고급 (bool query)
    Fuzzy 검색 (오타 허용)	❌	✅ (편집 거리 기반)
    와일드카드	⚠️ Trailing만	✅ 완전 지원
    정규식 검색	❌	✅
    관련도 순위	✅ 기본	✅ 고급 (BM25)
    Highlighting	❌	✅
    집계 (Aggregation)	❌	✅ 강력
    지리 검색	⚠️ 제한적	✅ 완전 지원
    동의어	❌	✅

  • MySQL 검색 예시:

    SELECT * FROM articles
    WHERE MATCH(title, body) AGAINST('+MySQL -tutorial' IN BOOLEAN MODE);
  • Elasticsearch 검색 예시 (고급):

    GET /articles/_search
    {
      "query": {
        "bool": {
          "must": [
            {
              "match": {
                "title": {
                  "query": "MySQL tutrial",  // 오타 허용
                  "fuzziness": "AUTO"
                }
              }
            }
          ]
        }
      },
      "highlight": {
        "fields": {
          "title": {},
          "body": {}
        }
      }
    }

• 성능 벤치마크

  • 메트릭	MySQL Full-Text	Elasticsearch
    검색 속도 (100만 행)	~100ms	~10ms
    검색 속도 (1억 행)	~5-10초	~50-100ms
    인덱싱 속도	중간	빠름 (벌크)
    메모리 사용	낮음	높음 (Java 힙)
    디스크 사용	낮음	높음 (역색인 + 원본)
    동시 쿼리 처리	제한적	우수 (분산)

• 운영 복잡도

  • MySQL Full-Text:
    • ✅ 기존 MySQL 인프라 활용
    • ✅ 추가 시스템 불필요
    • ✅ 데이터 동기화 문제 없음
    • ❌ 제한적인 튜닝 옵션
    • ❌ 대규모 확장 어려움
  • Elasticsearch:
    • ✅ 강력한 검색 기능
    • ✅ 무한 확장 가능
    • ❌ 별도 클러스터 운영 필요
    • ❌ MySQL과 데이터 동기화 필요
    • ❌ 메모리 사용량 높음
  • 데이터 동기화 방식:

    MySQL → Logstash/Debezium → Elasticsearch
            (CDC - Change Data Capture)

• 사용 사례별 선택 가이드

  • MySQL Full-Text를 선택해야 하는 경우:
    • ✅ 소규모~중규모 (< 1000만 행)
    • ✅ 트랜잭션 일관성 중요
    • ✅ 기존 MySQL 중심 아키텍처
    • ✅ 간단한 검색 요구사항
    • 예시: 블로그 검색, 소규모 이커머스, 사내 문서 관리
  • Elasticsearch를 선택해야 하는 경우:
    • ✅ 대규모 데이터셋 (> 1000만 행)
    • ✅ 복잡한 검색 (Fuzzy, 정규식, 다국어)
    • ✅ 실시간 분석 및 집계
    • ✅ 높은 검색 처리량(TPS)
    • 예시: 대형 이커머스, 로그 분석 (ELK), 소셜 미디어

• 하이브리드 접근법

  • 많은 현대 애플리케이션은 두 시스템을 함께 사용합니다:

  • MySQL (주 데이터베이스)
      ├─> 트랜잭션 데이터
      ├─> 관계형 데이터
      └─> CRUD 작업
    
          ↓ (동기화: Debezium, Logstash)
    
    Elasticsearch (검색 엔진)
      ├─> 복잡한 검색
      ├─> 실시간 분석
      └─> 집계 및 대시보드
  • 장점:
    • MySQL의 트랜잭션 일관성 + Elasticsearch의 강력한 검색
    • 각 시스템을 최적의 용도로 사용
    • 점진적 마이그레이션 가능
  • 단점:
    • 데이터 동기화 복잡도
    • 운영 비용 증가
    • 동기화 지연 처리 필요

• 출처

  • Airbyte - Elasticsearch Vs MySQL (opens in a new tab)
  • Knowi - Elasticsearch vs. MySQL: What to Choose? (opens in a new tab)
  • Medium - Full-text search with MySQL and Elasticsearch (opens in a new tab)

• Lock은 인덱스 스캔시에 발생할 수 있음 락은 XLock이나 SLock을 명시적으로 거는 경우에 발생함(MVCC)
• Repeatable Read에서는 Next Key Lock, Read Commited에서는 Record 락이 걸린다.
  • Shared Lock, Exclusive Lock 동일한듯
  • 
  • 

• SSD 환경에서도 순차 I/O가 랜덤 I/O보다 빠른 이유는 무엇인가요?

✅ 클러스터링 인덱스는 데이터 페이지의 물리적 순서를 결정하는 인덱스이고, 논클러스터링 인덱스는 별도의 구조체로 데이터 위치를 가리키는 인덱스입니다.

• 클러스터링 인덱스 🗂️

  • 데이터 페이지의 물리적 저장 순서를 결정
  • 테이블당 1개만 생성 가능 (Primary Key가 일반적)
  • 책의 페이지 번호와 같은 개념
  • 순차 I/O 패턴으로 높은 성능

• 논클러스터링 인덱스 📋

  • 별도의 인덱스 구조체에서 데이터 페이지를 가리킴
  • 테이블당 여러 개 생성 가능
  • 책의 색인(Index)과 같은 개념
  • Key Lookup 과정에서 랜덤 I/O 발생 가능

• HDD vs SSD 차이점 💾

  • HDD: 기계적 디스크 헤드 이동으로 순차 I/O가 압도적으로 유리
  • SSD: 기계적 움직임 없지만 여전히 순차 I/O가 2-3배 빠름

• 꼬리질문: SSD 환경에서도 순차 I/O가 랜덤 I/O보다 빠른 이유는 무엇인가요?

  • 병렬 처리 최적화 ⚡
    • SSD 내부 구조 이해 🏗️
      • 채널(Channel): SSD 컨트롤러와 NAND 플래시 간의 독립적 데이터 통로 (일반적으로 4-8개)
      • 다이(Die): 각 채널 내의 독립적으로 작동하는 NAND 플래시 칩 (채널당 2-4개)
      • 플레인(Plane): 다이 내에서 병렬 처리가 가능한 단위 (다이당 2-4개)
    • 순차 I/O의 병렬 활용 🔄
      • 연속된 데이터를 여러 채널에 분산 저장 (Striping)
      • 예: 16KB 데이터 → 4개 채널에 각각 4KB씩 동시 처리
      • 모든 채널이 동시에 작업하여 총 처리량 4배 증가
    • 랜덤 I/O의 병렬성 제한 ⚠️
      • 불규칙한 주소로 인해 특정 채널/다이에 집중
      • 일부 채널은 대기 상태로 유휴 자원 발생
      • 채널 간 로드 밸런싱 불균형
  • 컨트롤러 최적화 🧠
    • 연속된 주소 패턴 감지로 Prefetch 버퍼 활용
    • 예측 가능한 패턴에서 캐시 적중률 향상
  • Write Amplification 차이 📈
    • 순차 쓰기: 1:1 비율로 효율적
    • 랜덤 쓰기: 가비지 컬렉션 유발로 30% 오버헤드
  • 대역폭 활용도 📊
    • 순차 I/O: 최대 대역폭 활용 (~3.5GB/s)
    • 랜덤 I/O: 제한적 활용 (~500MB/s, 약 7배 차이)