0. 배경 및 요구사항

1) 배경

• 현재 ASKu서비스를 운영중에 있고 이 서비스의 검색기능이 기존 mysql의 단순 쿼리에 의존하고 있어 이를 개선하고자 하고 있다.

2) 요구사항

• 본격적인 elastic search사용 전에 기본적인 개념을 알고자 한다.

1. Full-Text Search 기본

1) Full-Text Search란

• MySQL의 고급 검색 기능으로, 자연어 기반의 텍스트 검색을 수행
• 대규모 텍스트 데이터에 대한 효율적인 검색을 위해 설계
• 텍스트의 관련성을 기반으로 결과를 반환할 수 있음

2) 장단점

장점:

• 대규모 텍스트 데이터에 대해 매우 효율적입니다.
• 자연어 처리 기반 검색을 수행할 수 있어 더 정교한 검색이 가능합니다.
• 검색어의 관련성을 기반으로 결과를 정렬하여 반환할 수 있습니다.

단점:

• 설정과 인덱스 생성이 필요합니다.
• FULLTEXT 인덱스를 지원하지 않는 MyISAM 및 InnoDB 외의 스토리지 엔진에서는 사용할 수 없습니다.
• 매우 빈번한 업데이트가 이루어지는 경우 인덱스 유지 관리 비용이 발생할 수 있습니다.

3) Like를 활용한 검색과의 비교

2. 구현 방법

1) Full-Text 인덱스 추가

나는 wiki_docs의 title과 recent_filtered_content에 대해 검색을 진행하려고한다. 이때 이 두 column에 Full-Text 인덱스를 추가하였다.

(새롭게 테이블을 생성하는 경우)

CREATE TABLE `wiki_docs` (
    `id` int NOT NULL AUTO_INCREMENT,
    `title` varchar(100) NOT NULL,
    `text_pointer` text NOT NULL,
    `recent_filtered_content` text NOT NULL,
    `latest_ver` int NOT NULL,
    `type` enum('doc', 'list') NOT NULL,
    `is_deleted` bool NOT NULL DEFAULT 0,
    `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `is_managed` bool NOT NULL DEFAULT 0,
    PRIMARY KEY(`id`),
    FULLTEXT(`title`, `recent_filtered_content`) // 추가
);

기존에 테이블이 존재하는 경우

ALTER TABLE wiki_docs ADD FULLTEXT(wikidocs_title_content_fulltext (title, recent_filtered_content));

2) 최소 단어 길이 설정(선택)

우리 서비스에서 는 2글자 정도의 검색어가 많을 것 같다고 판단되어 2까지 줄이려고한다.

(1) MySQL설정 파일 열고 수정

우분투 서버를 활용하는 경우

sudo vim /etc/mysql/my.cnf

혹은

sudo vim /etc/my.cnf

[mysqld]
ft_min_word_len=2

(2) MySQL서버 재시작

나는 혹시 몰라 재생성 전에 데이터를 백업하였다.

mysqldump --default-character-set=utf8mb4 --single-transaction --triggers -h [호스트 주소] -u [유저] -p[비밀번호] [데이터베이스 이름] > my_database_dump.sql

서버 재시작

sudo service mysql restart

(3) 변수 설정 확인

SHOW VARIABLES LIKE 'ft_min_word_len';

(4) 인덱스 재생성

ft_min_word_len을 변경할때 기존 인덱스를 삭제하고 다시 생성해야한다.

ALTER TABLE wiki_docs DROP INDEX wikidocs_title_content_fulltext;
ALTER TABLE wiki_docs ADD FULLTEXT wikidocs_title_content_fulltext (title, recent_filtered_content);

나는 위와 같이 warnings가 떠서 이 경고메세지를 확인해보았다.

SHOW WARNINGS;

FTS_DOC_ID라는 새로운 칼럼을 테이블에 추가하고 있다고 하는데. 이는 일반적인 동작이고, 경고 메시지로 표시되지만 문제가 되지 않는다고 한다.

3) stopword 설정

Stopwords는 검색이나 자연어 처리 작업에서 자주 사용되는 불필요한 단어를 제외하기 위해 사용. 이러한 단어는 텍스트 분석 시 의미 있는 정보를 제공하지 않는 경우가 많아, 검색 효율성을 높이기 위해 인덱스에서 제외함.

(1) MySQL설정 파일 열고 수정

sudo vim /etc/mysql/stopwords.txt

korean_stopwords.txt

이
가
은
는
을
를
에
의
도
와
과
로
으로
에서
그리고
하지만
그래서
또는
이것
그것
저것
여기
저기
이제
어떤
이런
그런

환경 변수 수정

sudo vim /etc/mysql/my.cnf

[mysqld]
ft_stopword_file=/etc/mysql/stopwords.txt

(2) MySQL서버 재시작

나는 혹시 몰라 재생성 전에 데이터를 백업하였다.

mysqldump --default-character-set=utf8mb4 --single-transaction --triggers -h [호스트 주소] -u [유저] -p[비밀번호] [데이터베이스 이름] > my_database_dump.sql

서버 재시작

sudo service mysql restart

(3) 변수 설정 확인

SHOW VARIABLES LIKE 'ft_stopword_file';

(4) 인덱스 재생성

ft_min_word_len을 변경할때 기존 인덱스를 삭제하고 다시 생성해야한다.

ALTER TABLE questions DROP INDEX question_content_fulltext;
ALTER TABLE questions ADD FULLTEXT question_content_fulltext (content) WITH PARSER ngram;

ALTER TABLE wiki_docs DROP INDEX wikidocs_title_content_fulltext;
ALTER TABLE wiki_docs ADD FULLTEXT wikidocs_title_content_fulltext (title, recent_filtered_content) WITH PARSER ngram;

3. 검색 쿼리 코드

검색 쿼리 코드 예시

SELECT * FROM wiki_docs WHERE MATCH(title, recent_filtered_content) AGAINST('Elasticsearch');

1) wiki 실제 적용

befor

const [rows] = await pool.query(
      `
      SELECT wiki_docs.*, IF(wiki_favorites.user_id IS NOT NULL, 1, 0) AS is_favorite
      FROM wiki_docs
      LEFT JOIN (
          SELECT user_id, doc_id
          FROM wiki_favorites
          WHERE user_id = ?  -- 여기에 현재 사용자의 ID를 삽입
      ) AS wiki_favorites
      ON wiki_docs.id = wiki_favorites.doc_id
      WHERE wiki_docs.title LIKE ?;
      `,
      [user_id, `%${title}%`]
    );

after

const [rows] = await pool.query(
      `
      SELECT wiki_docs.*, IF(wiki_favorites.user_id IS NOT NULL, 1, 0) AS is_favorite
  FROM wiki_docs
  LEFT JOIN (
      SELECT user_id, doc_id
      FROM wiki_favorites
      WHERE user_id = ?  -- 여기에 현재 사용자의 ID를 삽입
  ) AS wiki_favorites
  ON wiki_docs.id = wiki_favorites.doc_id
  WHERE MATCH(wiki_docs.title, wiki_docs.recent_filtered_content) AGAINST (? IN BOOLEAN MODE);
      `,
      [user_id, title]
    );

2) question실제 적용

before

const result = await pool.query(
    `SELECT q.*, users.nickname, COALESCE(ql.like_count, 0) AS like_count, COALESCE(a.answer_count, 0) AS answer_count, wiki_docs.title
    FROM questions q
    INNER JOIN users ON q.user_id = users.id
    INNER JOIN wiki_docs ON q.doc_id = wiki_docs.id
    LEFT JOIN (
        SELECT id, COUNT(*) as like_count 
        FROM question_like 
        GROUP BY id
    ) ql ON q.id = ql.id
    LEFT JOIN (
        SELECT question_id, COUNT(*) as answer_count 
        FROM answers 
        GROUP BY question_id
    ) a ON q.id = a.question_id
    WHERE q.content LIKE ?
    ORDER BY q.created_at DESC`,
    [`%${query}%`]
  );

after

const result = await pool.query(
    `SELECT q.*, users.nickname, COALESCE(ql.like_count, 0) AS like_count, COALESCE(a.answer_count, 0) AS answer_count, wiki_docs.title
  FROM questions q
  INNER JOIN users ON q.user_id = users.id
  INNER JOIN wiki_docs ON q.doc_id = wiki_docs.id
  LEFT JOIN (
      SELECT id, COUNT(*) as like_count 
      FROM question_like 
      GROUP BY id
  ) ql ON q.id = ql.id
  LEFT JOIN (
      SELECT question_id, COUNT(*) as answer_count 
      FROM answers 
      GROUP BY question_id
  ) a ON q.id = a.question_id
  WHERE MATCH(q.content) AGAINST (? IN BOOLEAN MODE)
  ORDER BY q.created_at DESC`,
    [query]
  );