🛫

DB 성능 개선 일지

유저 검색 개선

기존 방식

MATCH (…) AGAINST (…)

Search Type

Relevance

적용기

검색 최소 글자 수, 토큰 사이즈 변경

FullText Index 적용

INNODB_FT_INDEX_TABLE

INNODB_FT_INDEX_CACHE

유저 검색 개선

기존 방식

•

검색 페이지에 들어올 때 마다 모든 유저의 [ id, name, introduce, profile_image ]를 가져와 클라이언트로 전송한다.

const userProfileList = await this.userRepository
      .createQueryBuilder("user")
      .select("user.user_id", "user_id")
      .addSelect("user.name", "name")
      .addSelect("user.introduce", "introduce")
      .addSelect("user.profile_image", "profile_image")
      .getRawMany();
TypeScript
복사

•

클라이언트측에서는 검색을 할 때 해당 검색어를 리스트의 name에서 찾아 일치하는 유저들을 보여준다.

문제점

•

검색 페이지에 들어올 때 모든 유저의 정보를 가져오는게 너무 오래 걸린다.

◦

심지어 이걸 매번 검색 페이지에 들어올 때 마다 받아온다.

•

검색 페이지에 들어오면 로딩 시간이 필요한 상황이라 사용자 입장에서 사용했을 때 심각한 불편을 느꼈다.

•

개발자 도구에서 네트워크를 확인해보니 모든 유저 정보를 가져오는데 대략 120ms이 걸리는 것을 볼 수 있으며, 다른 API와 비교했을 때 대략 4~6배 느린것을 확인할 수 있다.

◦

현재 대략 5000명 가짜 유저를 mock 데이터 생성기를 통해 DB에 삽입 된 상태이다.

◦

즉, 5000명의 [ id, name, introduce, profile_image ]를 갖고오는데 120ms이 걸린다.

◦

만약 서비스 이용자가 1만 명, 10만 명일 경우 매우 느릴 것으로 보인다.

고민

•

먼저 사용자 입장에서 검색 기능의 이용 빈도를 생각 했을 때 과연 얼마나 사용할까를 생각해봤다.

•

인스타를 생각해봤을 때 다른 사람을 팔로잉할 때 주로 검색 기능을 사용한다.

•

그 때문에 다른 기능에 비해 상대적으로 낮은 이용 빈도가 예상된다.

◦

또한 검색 페이지에 들어온다고 해서 무조건 검색을 하는 것도 아니다.

•

그래서 굳이 모든 유저 정보를 가져올 필요 없이 사용자가 검색어(이름)를 입력할 경우 해당 검색어에 해당되는 유저 정보들만 가져와서 보여주면 되지 않을까라는 생각을 했다.

개선 방법

검색 기능

•

수 천, 수 만 명의 유저들 중에서 사용자가 다른 유저의 정보를 찾고, 원하는 유저를 검색해 팔로잉을 하는 검색 기능은 우리 서비스(Fitory)에서 필수적인 기능이다.

•

제한된 프로젝트 개발 기간 동안 적절한 성능을 내는 검색 엔진을 개발하기 앞서 우리가 고려한 부분은 다음과 같다.

프로젝트 개발 기간이 제한적이다. → 러닝 커브가 있는 새로운 기술 스택을 사용하지 않고, 기존 기술 스택인 MySQL을 사용한다.

대용량 데이터에서 문자열 검색을 처리하는데 준수한 성능을 보여준다.

LIKE

•

데이터베이스에서 문자열을 찾는 쿼리를 날릴 때 가장 먼저 떠오르는 것은 MySQL의 LIKE이다. 초기에는 LIKE를 이용한 쿼리를 개발하고, 추후에는 조회 시에 인덱스를 이용해서 성능을 개선할려 했다.

const userProfileList = await this.userRepository
      .createQueryBuilder("user")
      .select("user.user_id", "user_id")
      .addSelect("user.name", "name")
      .addSelect("user.introduce", "introduce")
      .addSelect("user.profile_image", "profile_image")
			.where("user.name like :name", { name: `%${userName}%`})
      .getRawMany();
TypeScript
복사

•

그러나 알아본 결과 MySQL의 LIKE는 와일드카드(%)를 사용할 때 항상 인덱스를 이용하는 것이 아니다.

SELECT ... FROM ... WHERE keyword LIKE 'A%';
SQL
복사

◦

위와 같이 와일드카드가 키워드의 우측에만 있는 경우에는 인덱스를 이용할 수 있어서 Index Range Scan으로 검색한다.

SELECT ... FROM ... WHERE keyword LIKE '%A';
SELECT ... FROM ... WHERE keyword LIKE '%A%';
SQL
복사

◦

반면에 위와 같이 와일드카드가 키워드의 좌측에 붙은 경우에는 어떤 문자로 시작하는지 알 수 없기 때문에 Full Table Scan으로 검색한다.

•

즉, 제한적으로만 인덱스를 이용할 수 있게 되어서 좋은 선택지가 아니라고 생각했다.

FullText Search

•

MySQL에서는 문자열 검색을 위해 LIKE 외에 FullText Search를 제공한다.

•

FullText Search는 단어나 구문에 대한 검색을 지원하고자 제공되는 방식이다.

•

검색하고자 하는 column에 FullText Index를 설정해주면, 문자열이 정해진 방법으로 분리되어 인덱스를 생성하고, 이를 빠르게 검색할 수 있다.

•

검색 키워드와 관련성이 높은 순으로 정렬할 수 있고, 추가적인 검색 규칙을 적용할 수 있다.

•

우리(Fitory)는 위와 같은 이유들로 LIKE 대신에 FullText Search를 이용해 검색 기능을 제공하기로 결정했다.

FullText Index

•

FullText Search에서 인덱스를 생성하는 방법은 여러가지 있다. 

•

인덱스는 파서가 문자열을 토크나이징한 후에 생성하게 된다. 이런 역할을 수행하는 파서가 여러가지 있지만 여기서는 두 가지만 다뤄본다.

•

Built-In Parser

◦

Built-In Parser는 stopword(구분자)를 기준으로 키워드를 추출하는 방식이다. 공백이나 문장 기호 혹은 사용자가 지정한 특정 단어를 기준으로 토크나이징하게 된다.

◦

예를 들어서, 구분자가 공백이라면 문장이 다음과 같이 쪼개집니다.

왕승재는 집에서 개발을 한다 -> 왕승재는 / 집에서 / 개발을 / 한다

우리가 만든 운동기록서비스 -> 우리가 / 만든 / 운동기록서비스
Plain Text
복사

◦

위와 같은 방식으로 토크나이징 되어 있다면 “승재는” 혹은 “서비스” 와 같은 검색 키워드로는 위 문장을 검색할 수 없다.

◦

FullText Search는 토큰과 검색 키워드가 전부 일치하거나 전방(prefix) 일치한 경우에만 결과를 가져오기 때문이다.

•

N-gram Parser

◦

위와 같은 문제를 해결해주는 파서도 존재한다. N-gram Parser는 MySQL에서 기본적으로 제공하기 때문에 FullText Index를 설정해줄 때 옵션으로 지정해주기만 하면 사용할 수 있다.

ALTER TABLE articles ADD FULLTEXT INDEX ft_index (title,body) WITH PARSER ngram;

# OR

CREATE FULLTEXT INDEX ft_index ON articles (title,body) WITH PARSER ngram;
SQL
복사

◦

N-gram 파서는 지정된 토큰 사이즈를 기준으로 키워드를 추출합니다.

◦

예를 들어서, 토큰 사이즈가 2 라면 문장이 다음과 같이 쪼개집니다.

왕승재는 집에서 개발을 한다
-> 왕승 / 승재 / 재는 / 는집 / 집에 / 에서 / 서개 / 개발 / 발을 / 을한 / 한다

우리가 만든 운동기록서비스
-> 우리 / 리가 / 가만 / 만든 / 든운 / 운동 / 동기 / 기록 / 록서 / 서비 / 비스
Plain Text
복사

◦

위와 같은 방식으로 토크나이징 되어 있다면 “승재는” 는 “승재 / 재는” 로 검색되고, “서비스” 는 “서비 / 비스” 로 검색됩니다. 각각 두 개 씩 일치하기 때문에 Built-In Parser에서는 검색되지 않던 내용들이 검색되게 된다.

Space Handling : N-gram 파서는 공백이 포함된 경우 키워드로 추출하지 않는다.

MATCH (…) AGAINST (…)

•

FullText Index가 걸려있는 column에 한해서 MATCH (…) AGAINST (…)를 사용해서 검색을 이용할 수 있다.

SELECT ... FROM ... 
WHERE MATCH(column) AGAINST('keyword' IN NATURAL LANGUAGE MODE);

SELECT ... FROM ... 
WHERE MATCH(column) AGAINST('keyword' IN BOOLEAN MODE);
SQL
복사

Search Type

•

FullText Search에서는 세 가지 종류의 검색 방식(search type)을 지원한다. 여기서는 위의 두 가지의 검색 방식을 알아본다.

•

IN NATURAL LANGUAGE MODE

◦

해당 모드는 검색 키워드를 토큰 사이즈로 분리한 후, 분리된 단어 중에서 하나라도 포함되는 데이터를 찾는다.

◦

해당 모드는 검색 방식을 생략해서 적었을 때 기본 모드이고, 위와 같이 명시적으로 나타낼 수 있다.

•

IN BOOLEAN MODE

◦

해당 모드는 검색 키워드를 토큰 사이즈로 분리한 후, 추가적인 검색 규칙을 적용해서 단어가 포함되는 데이터를 찾는다.

SELECT ... FROM ... 
WHERE MATCH(column) AGAINST('+A -B' IN BOOLEAN MODE);
SQL
복사

◦

위와 같은 검색 규칙은 “A”는 포함하지만 “B”는 포함하지 않는 데이터를 검색한다.

◦

이외에도 여러 가지 검색 규칙이 있다. 원하는 결과를 얻기 위해서 적절하게 조합하면 된다.

Operator	Description
+	반드시 포함하는 단어
–	반드시 제외하는 단어
>	포함하면서 검색 순위를 높일 단어
<	포함하지만 검색 순위를 낮출 단어
()	하위 표현식으로 그룹화
~	'-' 연산자와 비슷하지만 제외 시키지는 않고 검색 조건을 낮춤
*	와일드카드
“”	구문 정의

Relevance

•

MATCH (…) AGAINST (…)은 검색 키워드가 얼마나 많이 포함되어 있는 지에 따라서 관련성(relevance)이 결정된다. 관련성을 정렬해서 사용자에게 더욱 적절한 검색 결과를 보여줄 수도 있다.

•

WHERE에 MATCH (…) 를 사용하면 반환된 row는 다음 조건이 충족하는 한 가장 관련성이 높은 항목부터 자동으로 정렬된다.

◦

명시적인 ORDER BY가 없어야한다.

◦

Table Scan이 아닌 FullText Index Scan을 사용해 검색을 수행해야 한다.

◦

쿼리가 테이블을 조인하는 경우 FullText Index Scan 은 조인에서 가장 왼쪽에 있는 non-constant table 이어야 한다.

keyword에 매칭되는 결과들의 count하는 쿼리 예시.

관련성 쿼리 예시

출처 : https://dev.mysql.com/doc/refman/8.0/en/fulltext-natural-language.html

적용기

검색 최소 글자 수, 토큰 사이즈 변경

•

단어의 길이가 지정된 길이보다 짧을 경우 무시된다.

•

MySQL5.7의 기본 길이는 아래와 같다.

mysql> show variables like '%ft_min%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| ft_min_word_len          | 4     |
| innodb_ft_min_token_size | 3     |
+--------------------------+-------+
SQL
복사

•

우리 서비스의 검색 기능은 이름을 검색하기 때문에 한 글자 단위로도 검색이 가능하게 검색 최소 글자 수를 1로 설정했고, 토큰 사이즈도 1로 설정했다.

FullText Index 적용

•

유저의 이름을 이용해 검색을 할 것이기 때문에 user 테이블의 name column을 FullText Index로 변경했다. Parser는 N-gram을 사용했다.

ALTER USER user ADD FULLTEXT name_idx (name) WITH PARSER ngram;
SQL
복사

◦

적용 결과, user 테이블의 name column에 index_type이 FullText인 name_idx라는 새로운 Key가 생긴것을 확인할 수 있다.

INNODB_FT_INDEX_TABLE

•

FullText Index 적용 후, 이미 DB에 저장되어 있던 이름들은 INNODB_FT_INDEX_TABLE에 저장되며, 아래의 쿼리문을 통해 인덱싱된 단어들을 확인할 수 있다.

select * from INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
SQL
복사

INNODB_FT_INDEX_CACHE

•

만약 새로운 사용자가 가입하거나, 이름을 변경 했을 때, 즉, 새로 삽입된 데이터는 INNODB_FT_INDEX_CACHE에 저장되며, 아래의 쿼리문을 통해 캐싱된 단어들을 확인할 수 있다.

select * from INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
SQL
복사

•

아래 쿼리문 실행 시 서버가 종료 될 때 또는 캐시 크기가 innodb_ft_cache_size
와 innodb_ft_total_cache_size에서 정의 된 제한을 초과하는 경우에만 기본 검색 인덱스(INNODB_FT_INDEX_TABLE)와 결합된다.

OPTIMIZE TABLE user;
SQL
복사

쿼리 실행 결과

SELECT user_id, name, introduce, profile_image FROM `user` `User`
WHERE MATCH(name) AGAINST('+왕' IN BOOLEAN MODE);
SQL
복사

•

검색이 잘 되는 것을 확인 할 수 있다.

결과 : GET API 성능

•

120ms → 20ms

◦

약 83.3%의 성능 향상

•

Before

•

After

•

이로 인해 사용자 경험 또한 대폭 향상된 것을 느낄 수 있었다.

결과 : k6 (VUs : 250)

개선 전

•

http_req_duration

◦

avg = 2.03s

◦

p(90) = 3.98s

◦

p(95) = 15.8s

개선 후

•

http_req_duration

◦

avg = 872.36ms

◦

p(90) = 2.67s

◦

p(95) = 4.79s

http_req_duration 요약

avg

•

2.03s → 872.36ms

◦

약 56.4%의 성능 향상

p(90)

•

3.98s → 2.67s

◦

약 32.9%의 성능 향상

p(95)

•

15.8s → 4.79s

◦

약 69.6%의 성능 향상

통계 데이터 GET API 개선

•

검색 성능을 개선했지만 여전히 http_req_duration이 약 3초 정도 걸린다.

•

이를 해결하기 위해 API 성능을 다 조사해본 결과 통계 데이터를 가져오는 API가 상당히 오래 걸리는 것을 확인했다.

기존 방식

const statistics = await this.statisticsRepository.find({
	  where: { gender, weight: betweenWeight },
	  order: { SBD_volume: "ASC" },
});

...
TypeScript
복사

•

사용자의 성별이 남성, 체중이 100kg 일 경우, 성별이 남자면서 체중이 93~105kg 인 모든 사용자의 3대 운동 총량을 가져옵니다. 그 후에, 3대 운동 총량을 기준으로 정렬합니다.

•

하지만 이럴 경우 체중이 93~105kg인 사용자들이 테이블에 여기저기 흩어져 있기 때문에 Full Table Scan을 통해 모든 사용자 정보를 가져옵니다.

Query cost: 13815.59

개선 방법

아이디어

•

처음에는 체중을 정렬 시킨 상태로 between으로 가져오는 것이 휠씬 빠를 것이라 생각했다.

◦

between 93~105를 Index Range Scan으로 쭉 가져오는게 빠르고, 후에 정렬을 안해줘도 되기 때문이고 생각했다.

체중 Index

•

하지만 실험 결과 성별로 인해 Index Range Scan을 과도하게 길게 한다는 것을 파악했다.

Query cost: 10190.21

◦

Full Table Scan보단 나아졌지만 여전히 느린 모습.

성별 Index

•

오히려 성별로 정렬 시킨 상태로 가져오는 것이 휠씬 빠른 모습을 확인했다.

Query cost: 6951.00

Multi Column Index

최종적으로 성별과 체중의 Multi Column Index 을 이용해 성능을 개선했다.

ALTER TABLE SBD_statistics ADD INDEX gender_weight_idx (gender, weight);
SQL
복사

const statistics = await this.statisticsRepository.find({
	  where: { gender, weight: betweenWeight },
    // order: { SBD_volume: "ASC" },  index로 인해 정렬이 필요 없어진다.
});

...
TypeScript
복사

Query cost: 3253.21

결과

Query cost

•

13815.59 → 3253.21

◦

약 76.4%의 성능 향상

Query cost: 13815.59

Query cost: 3253.21

k6 (VUs : 150)

개선 전

•

http_req_duration

◦

avg = 541.85ms

◦

p(90) = 1.87s

◦

p(95) = 3.68s

개선 후

•

http_req_duration

◦

avg = 78.13ms

◦

p(90) = 115.39ms

◦

p(95) = 427.82ms

http_req_duration 요약

avg

•

541.85ms → 78.13ms

◦

약 85%의 성능 향상

p(90)

•

1.87s → 115.39ms

◦

약 93.8%의 성능 향상

p(95)

•

3.68s → 427.82ms

◦

약 88.3%의 성능 향상