책너두 (Real MySQL 8.0 1권) 24일차 (~267p)

요약

• 전문 검색 인덱스를 이해하게 됨.
  • 전문 검색에는 일반 용도의 B-Tree 인덱스를 사용할 수 없음.
• 전문 검색에서 사용하는 인덱스 알고리즘을 이해하게 됨.
  • 어근 분석 알고리즘
    • 불용어 처리 작업
    • 어근 분석 작업
      • MeCab 플러그인을 지원
    • 상당한 시간과 노력이 필요
  • N-gram 분석 알고리즘
    • 단순한 키워드 검색 인덱싱 알고리즘
    • 인덱스 크기가 상당히 큰 편
    • n은 인덱싱할 키워드의 최소 글자 수임
• 내장된 불용어뿐만 아니라 사용자가 직접 불용어를 등록할 수 있음.
  • 사용자가 등록하는 방법을 더 권장함.
• 불용어 처리 무시하는 방법을 이해하게 됨.
  • 모든 전문 검색 인덱스에 대한 불용어를 제거한다.
  • InnoDB 스토리지 엔진의 경우, 해당 테이블의 전문 검색 인덱스에 대해서만 불용어 처리를 무시한다.
• 사용자 정의 불용어를 사용하는 방법을 이해하게 됨.
  • 불용어 목록을 파일로 저장하여 ft_stopword_file 시스템 변수에 파일 경로를 등록한다.
  • InnoDB 스토리지 엔진을 사용하는 테이블의 전문 검색엔진에서만 사용가능하며, 불용어의 목록을 테이블로 저장한다.
• 전문 검색 인덱스의 가용성을 이해하게 됨.
  • 전문 검색을 위한 문법인 MATCH … AGAINST … 을 사용해야 함.
  • 테이블이 전문 검색 대상 칼럼에 대한 전문 인덱스를 보유해야 함.
• 함수 기반 인덱스에 대해 이해하게 됨.
  • 변형된 값에 대한 인덱스를 구축할 때 사용
  • 가상 칼럼을 이용한 인덱스를 통해 해결할 수 있음.
    • 테이블에 새로운 칼럼을 추가하는 것과 같은 효과임 -> 실제 테이블 구조가 바뀌는 단점이 있음.
  • 함수를 이용한 인덱스를 통해서도 해결할 수 있음.
    • 테이블 구조를 변경하지 않고 인덱스를 생성할 수 있음.

메모

전문 검색 인덱스

• 지금까지의 인덱스 알고리즘은 일반적으로 크지 않은 데이터, 또는 이미 키워드화한 작은 값에 대한 인덱싱 알고리즘이었음.
  • 대표적으로 MySQL의 B-Tree 인덱스는 실제 칼럼의 값이 1MB 이더라도 1MB 전체의 값을 인덱스 키로 사용하는 것이 아님
  • 1,000바이트(MyISAM) 또는 3072바이트(InnoDB) 까지만 잘라서 인덱스 키로 사용함.

InnoDB 로우 포맷(innodb_default_row_Format 시스템 변수)이 DYNAMIC 또는 COMPRESSED 인 경우, 3072 바이트까지임.
로우 포맷이 REDUNDANT 또는 COMPACT인 경우, 767바이트까지만 가능함.
MySQL 8.0 버전의 innodb_default_row_format 시스템 변수의 기본 값은 DYNAMIC 이므로 MySQL 8.0 버전부터는 일반적으로 3072 바이트까지 인덱스 키로 사용됨.

• 문서 내용 전체를 인덱스화해서 특정 키워드가 포함된 문서를 검색하는 전문(Full Text) 검색에는 InnoDB나 MyISAM 스토리지 엔진에서 제공하는 일반적인 용도의 B-Tree 인덱스를 사용할 수 없음.
  • 문서 전체에 대한 분석과 검색을 위한 인덱싱 알고리즘을 전문 검색(Full Text search) 인덱스라고 함.
  • 전문 검색 인덱스는 일반화된 기능의 명칭이지 전문 검색알 고리즘의 이름을 지칭하는 것은 아님.
• MySQL 8.0 부터 InnoDB가 기본 스토리지 엔진이 됐기에 모든 기능이 InnoDB 스토리지 엔진 중심으로 개선되고 있어서 MyISAM 보다는 InnoDB 스토리지 엔진 중심으로 소개한다.

인덱스 알고리즘

• 전문 검색은, 문서 본문 내용에서 사용자가 검색하게 될 키워드를 분석함.
  • 빠른 검색용으로 사용할 수 있도록 키워드로 인덱스를 구축함.
  • 키워드 분석 & 인덱스 구축에는 여러가지 방법이 있음.
    • 단어의 어근 분석 알고리즘
    • n-gram 분석 알고리즘
  • 예전에는 구분자(공백 or 일부 문장 기호를 기준으로 토큰 분리)도 하나의 인덱싱 알고리즘 처럼 생각했음.
  • MySQL 8.0 부터 구분자 방식은 이미 어근 분석과 n-gram 알고리즘에 함께 포함됨.

어근 분석 알고리즘

• MySQL 서버의 전문 검색 인덱스는 다음 두 가지 중요한 과정을 거쳐서 색인 작업이 수행됨.
  • 불용어(Stop Word) 처리
    • 검색에서 별 가치가 없는 단어를 모두 필터링해서 제거하는 작업
    • 불용어 개수는 많지 않기에 알고리즘을 구현한 코드에 모두 상수로 정의해서 사용하는 경우가 많음.
      • 유연성을 위해 불용어 자체를 데이터베이스화해서 사용자가 추가허간 삭제할 수 있게 구현하는 경우도 있음.
    • 현재 MySQL 서버는 불용어가 소스코드로 정의되어 있지만, 이를 무시하고 사용자가 별도로 불용어를 정의할 수 있는 기능을 제공함.
  • 어근 분석(Stemming)
    • 검색어로 선정된 단어의 뿌리인 원형을 찾는 작업임.
    • MySQL 서버에서, 오픈소스 형태소 분석 라이브러리인 MeCab을 플러그인 형태로 사용할 수 있게 지원함.
    • 한글이나 일본어의 경우, 영어와 같이 단어의 변형 자체는 거의 없기 때문에 어근 분석보다는 문장의 형태소를 분석해서 명사와 조사를 구분하는 기능이 더 중요함.
      • 사실 MeCab은 일본어를 위한 형태소 분석 프로그램이며, 서구권 언어를 위한 형태소 분석기는 MongoDB에서 사용되는 Snowball 이라는 오픈소스가 있음.
        • 중요한 것은, 각 국가의 언어가 서로 문법이 다르고 다른 방식으로 발전해왔기에 형태소 분석이나 어근 분석 또한 언어별로 방식이 모두 다름.
        • 그나마 한국어는 일본어와 많이 비슷하기에 MeCab을 이용한 한글 분석이 가능함
        • 하지만 MeCab 프로그램만 가져다 설치한다고 MeCab가 제대로 작동하는건 아님.
          • 우선 단어 사전이 필요하며, 문장을 해체해서 각 단어의 품사를 식별할 수 있는 문장의 구조 인식이 필요함.
          • 문장 구조 인식을 위해서는 실제 언어의 샘플을 이용해 언어를 학습하는 과정이 필요함.
            • 이 과정은 상당한 시간이 필요한 작업임.
            • MeCab을 MySQL 서버에 적용하는 방법은 어렵지 않지만 한글에 맞게 완성도를 갖추는 작업은 많은 시관과 노력이 필요함.

n-gram 알고리즘

• MeCab을 위한 형태소 분석은 매우 전문적인 알고리즘이어서 만족할 만한 결과를 내기 위해서는 많은 노력과 시간이 필요함.
  • 전문적인 검색 엔진을 고려하는 것이 아니라면 범용적으로 적용하기 쉽지 않음.
  • 이 단점을 보완하기 위한 방법으로 n-gram 알고리즘이 도입됨.
  • 형태소 분석이 문장을 이해하는 알고리즘이라면, n-gram은 단순히 키워드를 검색해내기 위한 인덱싱 알고리즘이다.
• n-gram이란, 본문을 무조건 몇 글자씩 잘라서 인덱싱하는 방법임.
  • 형태소 분석보다는 알고리즘이 단순하고 국가별 언어에 대한 이해와 준비 작업이 필요없다.
    • 단순하지만, 만들어진 인덱스의 크기는 상당히 큰 편임.
• n-gram에서 n은 인덱싱할 키워드의 최소 글자 수를 의미함.
  • 일반적으로는 2글자 단위로 키워드를 쪼개서 인덱싱하는 2-gram(또는 Bi-gram) 방식이 많이 사용된다.
• MySQL 서버의 n-gram 알고리즘을 이해하기 위해 2-gram 알고리즘으로 다음 문장의 토큰을 분리하는 방법을 살펴보자.

“To be or not to be. That is the question”

• 각 단어는 띄어쓰기, 마침료를 기준으로 10개의 단어로 구분됨.
• 2글자씩 중첩해서 토큰으로 분리된다.
• n글자 단어라면, 그 단어는 2-gram 알고리즘에서는 n-1개의 토큰으로 구분됨.
  • 구분된 각 토큰을 인덱스에 저장하기만 하면 됨.
  • 이때, 중복된 토큰은 하나의 인덱스 엔트리로 병합되어 저장됨.

• MySQL 서버는 이렇게 생성된 토큰들에 대해 불용어를 걸러내는 작업을 수행함.
  • 이때, 불용어와 동일하거나 불용어를 포함하는 경우, 걸러서 버림.
• MySQL 서버에 내장된 불용어는 다음과 같이 inforamtion_schema.innodb_ft_default_stopword 테이블을 통해 확인할 수 있음.

mysql> SELECT * FROM information_schema.INNODB_FT_DEFAULT_STOPWORD;

+-------+
| value |
+-------+
| a     |
| about |
| an    |
...
+-------+
35 rows in set (0.00 sec)

• information_schema.innodb_ft_default_stopword 테이블에 등록된 불용어의 전체 목록은 다음과 같음.
  • a, la, de, what, at, the, i, with
  • about, of, en, when, be, this, in, und
  • an, on, for, where, by, to, is, the
  • are, or from, who, com, was, it, www
  • as, that, how, will
• 최종적으로 MySQL 서버에 실제 저장되는 인덱스 엔트리는 다음과 같다.

• 위 표의 “출력(최종 인덱스 등록)” 칼럼에 표시된 것들만 전문 검색 인덱스에 등록된다.
• 전문 검색을 더 빠르게 하기 위해 2단계 인덱싱(프론트엔드와 백엔드 인덱스)과 같은 방법도 있음.
  • 그렇지만 MySQL 서번느 이렇게 구분된 토큰을 단순히 B-Tree 인덱스에 저장한다.
• 성능 향상을 위한 Merge-Tree 같은 기능을 가지고 있긴 하지만, MySQL에서 구현하는 n-gram 알고리즘의 핵심 내용은 이 정도로 이해하면 됨.

불용어 변경 및 삭제

• n-gram의 토큰 파싱 & 불용어 처리에 대한 위 예시를 보면 “ti”, “at”, “ha” 같은 토큰들은 “a”와 “i” 철자가 불용어로 등록되어 있기에 모두 걸러져 버림.
  • 실제로 이러한 불용어 처리는 사용자에게 도움이 되기보다 더 혼란스럽게 하는 기능일 수 있음.
    • 그래서 불용어 처리를 완전히 무시하거나 MySQL 서버에 내장된 불용어 대신, 사용자가 직접 불용어를 등록하는 방법을 권장함.

전문 검색 인덱스의 불용어 처리 무시

• 불용어 처리 무시하는 방법은 두 가지가 있음.
  1. 스토리지 엔진에 관계없이 MySQL 서버의 모든 전문 검색 인덱스에 대해 불용어를 완전히 제거하는 것임.
     • 이를 위해 MySQL 서버의 설정 파일(my.cnf)의 ft_stopword_file 시스템 변수에 빈 문자열을 설정하면 됨. → ft_stopword_file=''
     • 이 시스템 변수는 MySQL 서버가 시작될 때만 인지하므로 설정을 변경하면 MySQL 서버를 재시작해야 변경사항이 반영됨.
     • ft_stopword_file 시스템 변수는 MySQL 서버의 내장 불용어 비활성화 할 때와, 사용자 정의 불용어를 적용할 때도 사용할 수 있음.
       • 사용자가 직접 정의한 불용어 목록을 저장한 파일의 경로를 ft_stopword_file 시스템 변수에 설정하면 해당 경로의 파일에서 불용어 목록을 가져와 적용함.
  2. InnoDB 스토리지 엔진을 사용하는 테이블의 전문 검색 인덱스에 대해서만 불용어 처리를 무시할 수도 있음.
     • InnoDB 테이블의 전문 검색 인덱스의 불용어 처리를 무시하려면 다음과 같이 innodb_ft_enable_stopword 시스템 변수를 OFF 로 설정하면 됨.
     • 이 경우, MySQL 서버의 다른 스토리지 엔진(MyISAM 스토리지 엔진)을 사용하는 테이블은 여전히 내장 불용어 처리를 사용함.
     • innodb_ft_enable_stopword 은 동적인 시스템 변수이므로 MySQL 서버가 실행 중인 상태에서도 변경할 수 있음.

mysql> SET GLOBAL innodb_ft_enable_stopword=OFF;

사용자 정의 불용어 사용

• 사용자 정의 불용어를 사용하는 방법은 두 가지임.
  1. 불용어 목록을 파일로 저장하고 MySQL 서버 설정 파일에서 파일의 경로를 ft_stopword_file 설정에 등록하면 됨. ft_stopword_file='/data/my_custom_stopword.txt'
  2. InnoDB 스토리지 엔진을 사용하는 테이블의 전문 검색 엔진에서만 사용할 수 있음. → 불용어의 목록을 테이블로 저장하는 방식임.

mysql> CREATE TABLE my_stopword(value VARCHAR(30)) ENGINE = INNODB;
mysql> INSERT INTO my_stopword(value) VALUES ('MySQL');

mysql> SET GLOBAL innodb_ft_server_stopword_table='mydb/my_stopword';
mysql> ALTER TABLE tb_bi_gram
               ADD FULLTEXT INDEX fx_title_body(title, body) WITH PARSER ngram;

• 위와 같이 테이블을 생성하고 innodb_ft_server_stopword_table 시스템 변수에 불용어 테이블을 설정하면 됨.
  • 이때, 불용어 목록을 변경한 이후, 전문 검색 인덱스가 생성돼야만 변경된 불용어가 적용되는 점을 주의해야 함.
• innodb_ft_user_stopword_table 시스템 변수를 이용하는 방법도 있음.
  • innodb_ft_server_stopword_table 시스템 변수와 사용법이 동일함.
  • 단, 여러 전문 검색 인덱스가 서로 다른 불용어를 사용해야 하는 경우, innodb_ft_user_stopword_table 시스템 변수를 이용하면 됨.

전문 검색 인덱스의 가용성

• 전문 검새 인덱스를 사용하려면 반드시 두 가지 조건을 갖춰야 함.
  • 쿼리 문장이 전문 검색을 위한 문법(MATCH … AGAINST …) 을 사용해야 함.
  • 테이블이 전문 검색 대상 칼럼에 대해서 전문 인덱스를 보유해야 함.

mysql> CREATE TABLE tb_test (
                 doc_id INT,
                 doc_body TEXT,
                 PRIMARY KEY (doc_id),
                 FULLTEXT KEY fx_docbody (doc_body) WITH PARSER ngram
             ) ENGINE=InnoDB;

• 위 테이블에서 doc_body 칼럼에 전문 검색 인덱스를 생성한 상황임.

mysql> SELECT * FROM tb_test WHERE doc_body LIKE '%애플%';

• 위와 같은 검색 쿼리로 원하는 검색 결과를 얻을 수 있음.
• 하지만, 전문 검색 인덱스를 이용해 효율적으로 쿼리가 실행된 것이 아니라 테이블을 처음부터 끝까지 읽는 풀 테이블 스캔으로 쿼리를 처리하게 됨.

mysql> SELECt * FROM tb_test
             WHERE MATCH(doc_body) AGAINST('애플' IN BOOLEAN MODE);

• 전문 검색 인덱스를 사용하려면 반드시 다음 예제와 같이 MATCH … AGAINST … 구문으로 검색 쿼리를 작성해야 함.
• 전문 검색 인덱스를 구성하는 칼럼들은 MATCH 절의 괄호 안에 모두 명시되어야 함.

함수 기반 인덱스

• 일반적인 인덱스는 칼럼의 값 일부(칼럼 값 앞부분) 또는 전체에 대해서만 인덱스 생성이 허용됨
• 하지만, 때로는 칼럼의 값을 변형해서 만들어진 값에 대해 인덱스를 구축해야 할 수 있음.
  • 이 때, 함수 기반의 인덱스를 활용하면 됨.
  • MySQL 서버는 8.0 버전부터 함수 기반 인덱스를 지원하기 시작함.
  • MySQL 서버에서 함수 기반 인덱스를 구현하는 방법은 다음과 같이 두 가지로 구분할 수 있음.
    • 가상 칼럼을 이용한 인덱스
    • 함수를 이용한 인덱스
  • MySQL 서버의 함수 기반 인덱스는 인덱싱할 값을 계산하는 과정의 차이만 있음.
    • 실제 인덱스의 내부적인 구조 & 유지관리 방법은 B-Tree 인덱스와 동일함.
      • 그래서 함수 기반 인덱스의 내부 구조는 앞에서 살펴본 B-Tree 인덱스를 참고하자.

가상 칼럼을 이용한 인덱스

mysql> CREATE TABLE user (
                 user_id BIGINT,
                 first_name VARCHAR(10),
                 last_name VARCHAR(10),
                 PRIMARY KEY (user_id)
             );

• 위 쿼리는 사용자의 정보를 저장하는 테이블을 생성한다.
• first_name 과 last_name을 합쳐서 검색하는 요건이 생겼다면, 이전 버전의 MySQL 서버에서는 full_name이라는 칼럼을 추가하고, 모든 레코드에 대해 full_name 을 업데이트 하는 작업을 거쳐햐 해씅ㅁ.
• MySQL 8.0 버전부터는 아래와 같이 가상 칼럼을 추가하고 그 가상 칼럼에 인덱스를 생성할 수 있게 됨.

mysql> ALTER TABLE user
                 ADD full_name VARCHAR(30) AS (CONCAT(first_name,' ',last_name)) VIRTUAL,
                 ADD INDEX ix_fullname (full_name);

• 이제, full_name 칼럼에 대한 검색도 새로 만들어진 ix_fullname 인덱스를 이용해 실행 계획이 만들어 지는 것을 아래에서 확인할 수 있다.

mysql> EXPLAIN SELECT * FROM user WHERE full_name='Matt Lee';
+---+-------------+-------+------+-------------+---------+-------+
|id | select_type | table | type | key         | key_len | Extra |
+---+-------------+-------+------+-------------+---------+-------+
| 1 | SIMPLE      | user  | ref  | ix_fullname | 1023    | NULL  |
+---+-------------+-------+------+-------------+---------+-------+

• 가상 칼럼이 VIRTUAL 이나 STORED 옵션 중, 어떤 옵션으로 생성됐든 관계없이 해당 가상 칼럼에 인덱스를 생성할 수 있음.
• 가상 칼럼은 테이블에 새로운 칼럼을 추가하는 것과 같은 효과를 내기 때문에 실제 테이블의 구조가 변경된다는 단점이 있음.
  • VIRTUAL, STORED 옵션 차이는 15.8절 가상 칼럼(파생 칼럼) 에서 자세히 살펴봄

함수를 이용한 인덱스

• 가상 칼럼은 5.7 버전에서도 사용할 수 있었음.
• 5.7에서는 함수를 직접 인덱스 생성 구문에 사용할 수 없었음.
• 8.0 부터는 다음과 같이 테이블의 구조를 변경하지 않고 함수를 직접 사용하는 인덱스를 생성할 수 있음.

mysql> CREATE TABLE user (
                 user_id BIGINT,
                 first_name VARCHAR(10),
                 last_name VARCHAR(10),
                 PRIMARY KEY (user_id),
                 INDEX ix_fullname ((CONCAT(first_name,' ',last_name)))
             );

• 함수를 직접 사용하는 인덱스는 테이블 구조는 변경하지 않고, 계산된 결괏값의 검색을 빠르게 만들어 줌.
• 함수 기반 인덱스를 제대로 활용하려면 반드시 조건절에 함수 기반 인덱스에 명시된 표현식이 그대로 사용되야 함.
  • 함수 생성 시, 명시된 표현식과 쿼리의 WHERE 조건절에 사용된 표현식이 다르면 MySQL 옵티마이저는 다른 표현식으로 간주해서 함수 기반 인덱스를 사용하지 못함.

mysql> EXPLAIN SELECT * FROM user WHERE CONCAT(first_name,' ',last_name)='Matt Lee';
+---+-------------+-------+------+-------------+---------+-------+
|id | select_type | table | type | key         | key_len | Extra |
+---+-------------+-------+------+-------------+---------+-------+
| 1 | SIMPLE      | user  | ref  | ix_fullname | 87      | NULL  |
+---+-------------+-------+------+-------------+---------+-------+

• 만약 위 예제 쿼리를 실행했을 때, 옵티마이저가 표시하는 실행 계획이 “ix_fullname” 인덱스를 사용하지 않는 것으로 표시된다면, CONCAT 함수에 사용된 공백 문자 리터럴 때문일 가능성이 높음.
• 이 경우, 3개 시스템 변수의 값을 동일 콜레이션(이 책에서는 “utf8mb4_0900_ai_ci”로 통일해서 테스트 함)으로 일치 시킨 후, 다시 테스트를 수행해 보자.
  • collation_connection
  • collation_database
  • collation_server

가상 칼럼과 함수를 이용한 인덱스, 이 두 가지 방법은 사용법과 SQL 문장에서 문법의 차이가 조금 있음.
하지만 실제로 이 두 방식은 내부적으로 동일한 구현 방법을 사용함. 결국, 내부적인 구현이 동일하므로, 이는 어떤 방식을 사용하더라도 둘의 성능 차이는 발생하지 않는다.