Search improvement with index

Posted Feb 8, 2025 Updated Apr 17, 2025

By So Hee Park 17 min read

✅ Requirements

인덱스가 있으면 검색 속도 향상에 도움이 됨
검색하고 싶은 것은 두 종류
user name index
goal title index

✅ 참고자료

DB조회 최적화 방법, index 설정 방법, explain 사용 방법
https://wanglan.tistory.com/entry/Spring-DB-%EC%A1%B0%ED%9A%8C%EC%9D%98-%EC%B5%9C%EC%A0%81%ED%99%94
인덱스 설정 방법, profiling, ngrinder test
https://ryusunny.tistory.com/118
fulltext search
https://velog.io/@ttomy/%EA%B2%80%EC%83%89-fulltext-search%EC%99%80-%EC%8B%A4%ED%97%98

✅ mock data

데이터 더 만들어 넣기
https://www.mockaroo.com/
데이터 10000개 넣음

✅ Jmeter 설치

✔️ Jmeter란?
Apache에서 만들었으며 순수하게 자바로 만들어진 웹 애플리케이션 성능 테스트 오픈 소스입니다.
GUI를 제공하며 Logic controller라고 불리는 기능은 GUI에서 테스트를 설정할 수 있는 뛰어난 유연성을 제공합니다.
다양한 프로토콜/서버를 테스트할 수 있으며 CLI또한 지원합니다.
다양한 외부 플러그인을 사용하여 기능 확장이 가능합니다.
(나는 그래프 그리는 플러그인 설치함)
여러 튜토리얼을 보면 GUI설정을 통해 테스트할 수 있으며 .jmx 파일을 통해 CLI로도 테스트할 수 있습니다.
✔️ nGrinder란? 네이버에서 성능 측정 목적으로 개발 된 오픈소스 프로젝트입니다. Jython, Groovy 두 가지 중 하나를 선택하여 스크립트를 작성합니다. 또한 Junit 기반으로 되어 있어 IDE에서 먼저 확인해보고 디버깅할 수 있습니다. 스크립트를 수정할 수 있어 세밀한 성능 테스트가 가능합니다. 개발자가 교착 상태와 속도 저하를 찾을 수 있도록 구축되었습니다.
Jmeter 설치 블로그 참고
https://leeggmin.tistory.com/10

☑️ 토큰 추가해주어야 함

✅ SQL profiling on

profiling on 하고
size 100으로 지정

1️⃣ 데이터 1000명, btree index

❌ Without Index

☑️ btree index 생성

인덱스 설정 잘 된 것 확인

⭕️ With Index

👎🏻 Conclusion

검색 데이터 수가 너무 적어 큰 속도 차이가 없음
💊 데이터를 더 만들어 넣자

2️⃣ 데이터 10000명, btree index

그러나 Btree index를 추가했는데 와일드카드 %에는 먹히지 않음
우리는 keyword가 포함되는 검색을 하고 싶은데,
the wildcard (%) at the beginning of the LIKE pattern prevents MySQL from using a BTREE index

3️⃣ 데이터 10000명, FULLTEXT index를 쓰기로 함

이유: 와일드카드가 있으면 btree index안 먹힘
👍🏻 Fastest for text searches, optimized for words, can apply index
👎🏻 Requires FULLTEXT support (only in InnoDB or MyISAM)
luckily mySQL storage engine in Spring boot uses InnoDB
1. btree index를 지우고
이유: 같은 column에 두 개의 인덱스 공존 불가
1. Create a FULLTEXT index

ALTER TABLE user ADD FULLTEXT INDEX idx_user_name (name);

1. SQL쿼리 설정

SELECT * FROM user
WHERE MATCH(name) AGAINST('+a*' IN BOOLEAN MODE)
LIMIT 10000;

1. Springboot JPQL쿼리 설정
MATCH() ... AGAINST() is not directly supported by JPA
need to use a native query
The nativeQuery = true flag tells Spring Data JPA to use raw SQL instead of JPQL

@Query(value = "SELECT * FROM user WHERE MATCH(name) AGAINST(:keyword IN BOOLEAN MODE) LIMIT 10000", nativeQuery = true)
List<User> searchByFullTextName(@Param("keyword") String keyword);

1. Fulltext index 쓰는지 확인 방법

EXPLAIN
-- 하고 SQL 실행

1. BOOLEAN MODE 🆚 Natural language mode
Natural language mode는 사용하지 않았음
이유: simpler but may not match as aggressively as BOOLEAN MODE
하지만 장점: 서비스에서 MySQL to determine relevant matches without +keyword* 이런거 없어도 바로 키워드로 검색 가능함

👎🏻 Conclusion

이렇게 돌렸더니 원래 쿼리랑 검색값이 다름!

SELECT * FROM user
WHERE name LIKE '%a%'
LIMIT 0, 10000;

SELECT * FROM user
WHERE MATCH(name) AGAINST('+a*' IN BOOLEAN MODE)
LIMIT 10000;

👎🏻 FULLTEXT index설정 후 쿼리를 돌렸더니 원래 쿼리와 검색값이 다름
이유 https://velog.io/@ttomy/%EA%B2%80%EC%83%89-fulltext-search%EC%99%80-%EC%8B%A4%ED%97%98

Full-text index는 MySQL에서 에서 FULLTEXT 타입의 인덱스이다.
그런데 원래 built-in된 기본 text parser는 공백 단위로 되어있기에
어절에 완전히 일치하는 것만 조회해온다.

부분 문자열(part-of-word) 검색이 안된다는 것인데, 예를 들면
mysql fulltext index라는 데이터가 있고 'my', 'full'라는 검색어로 조회해도 한 어절과 완전히 일치하지는 않기에 검색되지 않는다는 것이다.

이를 글자 단위로 검색가능하게 하기 위해 n-gram parser를 지원한다.
다만 더 작은 단위로 text의 인덱스들을 많이 생성하기에 더 많은 메모리를 사용한다는 것을 인지해야한다.
아주 많은 text에 대해 n-gram parser의 token사이즈를 작게 잡으면 db에서도 인덱스 사용 시 디스크에 접근할 가능성이 높아져 성능이 저하될 수 있다.

💊 n-gram parser을 적용

4️⃣ 데이터 10000명, FULLTEXT index, n-gram

⭕️ With Index

n-gram token size: 2
👎🏻 문제 1: ngram token 사이즈가 2라서 검색어가 2글자 이상이어야 하는 문제가 생김
한 글자는 검색이 불가
👎🏻 문제 2: 이름 Beniamino를 검색했더니 203개의 결과가 나옴
정작 Beniamino는 DB에 한 명밖에 없는데
❓ 왜 그런지 이유를 파악해보니
- 1. 현재 ngram을 쓰고 있음
- 1. splits words into smaller pieces (tokens) based on ngram_token_size
- 1. 현재 토큰 사이즈 = 2
- 👎🏻 결론: 2글자씩 쪼개서 검색 중
  1 Be, en, ni, ia, am, mi, in, no
MySQL doesn’t search for exactly “Beniamino”. Instead, it searches for any name that contains these tokens, which is why you get extra results.

☑️ ngram token 사이즈 변경

sudo vi /etc/my.cnf 편집함
변경완료
하고 나서 인덱스 지우고 다시 만들어 줘야 함

👎🏻 Conclusion

n-gram도 설정하고
n-gram token사이즈로 1로 변경하여 한 글자 알파벳 검색도 가능하게 했으나
검색 속도가 더 느려짐
❓ 이유: The ngram parser splits words into smaller pieces, which increases index size and slows down searches.
하지만 ngram을 안 쓸수는 없음. 안 쓰면 LIKE로 했을때랑 결과 값 갯수가 다르기 때문이다
한마디로, ngram을 안 쓰면 A 로 시작하는 이름만 검색, 또는 정확히 일치하는 것만 검색
그래서 ngram을 쓰면 token사이즈에 따라 검색 방법이 바뀌는데
한 글자 검색도 가능하도록 token사이즈를 1로 하면 검색 속도가 더 느려진다
그래서 한 글자 검색을 포기하고, token사이즈를 더 키워서 2로 하더라도 글씨를 쪼개서 검색하다보니 검색 결과가 예상과 다르다는 문제가 발생함

✅ 유저는 btree index로 검색하기로 결정

그리고 목표는 fulltext index로 검색하기로 결정

유저 이름 검색,
keyword로 시작하는 유저 검색하기로
btree index

목표는 목표 제목 검색
keyword를 포함하는 목표 검색하기로
fulltext index

유저이름 검색을 keyword를 포함하는 검색을 하고 싶어서
btree index로 구현하였으나, %이 들어가면 btree index불가
그래서 fulltext index로 구현하려고 했으나
fulltext index는 ngram 토큰 사이즈로 검색어를 쪼개서 검색, 검색 결과가 예상과 다름
그리고 유저 이름의 일부를 검색하는 일은 없을 것이라 판단
따라서 유저 검색은 keyword로 시작하는 유저 이름을 btree index를 활용해 검색하기로 결정
반대로 목표는 keyword를 포함해서 찾는 경우가 필요하다고 판단
fulltext index로 구현하기로 결정

BTREE 🆚 FULLTEXT

FULLTEXT

ALTER TABLE user ADD FULLTEXT INDEX idx_user_name (name);

SELECT * FROM user
WHERE MATCH(name) AGAINST('a*' IN BOOLEAN MODE)
LIMIT 10000;

BTREE

CREATE INDEX idx_user_name ON user(name);

SELECT * FROM user
WHERE name LIKE 'a%'
LIMIT 10000;

List<User> findByNameStartingWith(String keyword);

entity
@Table(name= "user", indexes= @Index(name="idx_user_name", columnList = "user"))

✅ User name BTREE Index

keyword로 시작하는 유저 검색

☑️ SQL commands

SELECT * FROM zzikzzik.user LIMIT 1000, 1020
SELECT count(*) FROM user;

set profiling

SET profiling=1;
SET profiling_history_size=100;
show variables like '%profiling%';

CREATE INDEX

USE zzikzzik;
-- btree index
CREATE INDEX idx_user_name ON user(name);
-- index 없애기
DROP INDEX idx_user_name ON user;
-- fulltext index
ALTER TABLE user ADD FULLTEXT INDEX idx_user_name (name);
-- fulltext index with ngram
ALTER TABLE user ADD FULLTEXT INDEX idx_user_name (name) with parser ngram;
show index from user;

BTREE INDEX query

USE zzikzzik;
-- EXPLAIN
SELECT * FROM user
WHERE name LIKE 'Beniamino%'
LIMIT 0, 10000;
-- show profiles;
-- show profile for query 33;
-- show profile cpu for query 33;

FULL TEXT INDEX query

USE zzikzzik;
-- EXPLAIN
SELECT * FROM user
WHERE MATCH(name) AGAINST('e' IN BOOLEAN MODE)
LIMIT 10000;

SELECT * FROM user
WHERE MATCH(name) AGAINST('Beniamino' IN NATURAL LANGUAGE MODE)
LIMIT 10000;
-- show profiles;
-- show profile for query 22;
-- show profile cpu for query 22;

SHOW VARIABLES

SHOW TABLE STATUS WHERE Name = 'user';
show global variables like 'ngram_token_size';
SHOW VARIABLES LIKE 'ft_min_word_len';
SHOW VARIABLES LIKE 'ft_stopword_file';

☑️ Spring

✔️ 인덱스 전

repository
List<User> findByNameContains(@Param("keyword") String keyword);

serviceImpl
if( searchField.equals(SearchField.USER_NAME.getValue()) ){
           List<User> searchedUsers = userRepository.findByNameContains(keyword);

✔️ BTREE 인덱스 후

repository
List<User> findByNameStartingWith(@Param("keyword") String keyword);

serviceImpl
if( searchField.equals(SearchField.USER_NAME.getValue()) ){
     List<User> searchedUsers = userRepository.findByNameStartingWith(keyword);

user entity
@Entity
@Getter
@NoArgsConstructor(access = AccessLevel.PROTECTED)
@Table(name= "user", indexes= @Index(name="idx_user_name", columnList = "user"))
public class User {

}

✔️ FULLTEXT 인덱스 후

repository
@Query(value = "SELECT * FROM user WHERE MATCH(name) AGAINST(:keyword IN BOOLEAN MODE) LIMIT 10000", nativeQuery = true)
List<User> searchByFullTextName(@Param("keyword") String keyword);

serviceImpl
if( searchField.equals(SearchField.USER_NAME.getValue()) ){
     List<User> searchedUsers = userRepository.searchByFullTextName(keyword);

user entity
@Entity
@Getter
@NoArgsConstructor(access = AccessLevel.PROTECTED)
@Table(name= "user", indexes= @Index(name="idx_user_name", columnList = "user"))
public class User {

❌ Without Index

✔️ 인덱스 없음 확인
✔️ mySQL explain
인덱스 안쓰고 있음 확인
✔️ MySQL에서 그냥 쿼리 돌려보기, 시간 확인
- ➡️ a로 시작하는 유저 검색: 0.755/0.820초
- ➡️ 특정 이름 검색: 0.405초
✔️ MySQL show profile
✔️ MySQL show CPU profile
- ➡️ 0.000073초
✔️ Jmeter test 샘플 200개
✔️ Jmeter test 샘플 10000개
✔️ Jmeter test 샘플 10000개 특정 유저 검색
✔️ Postman test
- ➡️ 4.88초
✔️ Postman test 특정 유저 검색
- ➡️ 3.66초

⭕️ With Index

✔️ Btree index 설정 확인
✔️ mySQL explain
인덱스 사용하는 것 확인
✔️ MySQL에서 그냥 쿼리 돌려보기, 시간 확인
- ➡️ a검색: 0.666초
➡️ 특정 이름 검색: 0.371초
✔️ MySQL show profile
✔️ MySQL show CPU profile
✔️ Jmeter test 샘플 200개
✔️ Jmeter test 샘플 10000개
✔️ Jmeter test 샘플 10000개 특정 유저 검색
✔️ Postman test
➡️ 3.69초
✔️ Postman test 특정 유저 검색

👍🏻 Improvements

index설정으로 search rows 9854 → 865
특정 알파벳으로 시작하는 유저 찾기 jmeter 1만 데이터 sample 200건 throughput 3.3/sec → 4.0/sec
jmeter 1만 데이터 sample 1만건에 대한 throughput 4.5 → 4.6/sec
같은 유저를 찾는 쿼리는 처리량에서는 큰 차이가 나지 않았으나, 응답 시간과 표준편차에서 개선
에러 0

❓ 이렇게 개발한 이유

BTREE index사용
- 이유: 유저 이름은 특정 키워드로 검색하는 경우가 많고, 포함해서 검색하지 않는다고 판단
boolean mode를 사용하였다
- 이유: 다양한 검색 조건을 줄 수 있다(특정 단어 포함, 어떤 단어는 포함하지 않을 것)

✅ Goal title FULLTEXT Index

keyword 포함하는 목표 검색

❌ Without Index

✔️ 인덱스 없는 것 확인
Goal Index 설정 전
goal title에 대해서 인덱스 없는 것 확인
✔️ mySQL explain
인덱스 사용하지 않는 것 확인
✔️ MySQL에서 그냥 쿼리 돌려보기, 시간 확인
✔️ MySQL show profile
✔️ MySQL show CPU profile
- ➡️ 204
✔️ Jmeter test 샘플 200
➡️ 4.9/sec
더 높게 나온 경우
✔️ Jmeter test 샘플 10000
✔️ Postman test
- ➡️ 2.97초 8.87KB

⭕️ With Index

✔️ mySQL에 인덱스 만들고 잘 돌아가는지 확인
✔️ ngram_token_size를 2로 했을 때
✔️ mySQL explain
인덱스 사용하고 있는 것 확인
✔️ MySQL에서 그냥 쿼리 돌려보기, 시간 확인
➡️ 301초
✔️ MySQL show profile
✔️ MySQL show CPU profile
➡️ 43
✔️ Jmeter test 샘플 200
➡️ 200개 5.6
✔️ Jmeter test 샘플 10000
✔️ Postman test

👍🏻 Improvements

search rows 9961 → 1
jmeter 1만 데이터 sample 200건 표준편차 감소 및 throughput 4.9/sec → 5.6/sec
하지만 크게 개선한 점은 없는 것 같은게, ngram token이 2라서 그런 것 같기도 함

❓ 이렇게 개발한 이유

FULLTEXT Index
- 이유: 목표는 단어를 포함해서 검색해야 한다(예를 들어 toeic이면 이번 달 toeic 700점 목표등 찾아야 함)
- 따라서 와일드카드 %를 사용해야 함
- 그런데 BTREE index는 와일드카드 불가
ngram size token=2
- 이유: 어절에 완전히 일치하는 것만 조회하는게 아니라 부분 문자열(part of word)검색도 하기 위해
- 👎🏻 다만 더 작은 단위로 text의 인덱스들을 많이 생성하기에 더 많은 메모리를 사용한다
- 너무 작은 단위로 n-gram parser의 token사이즈를 잡으면 성능 저하

Project, Zzikzzik

This post is licensed under CC BY 4.0 by the author.

✅ Requirements

✅ 참고자료

✅ mock data

✅ Jmeter 설치

☑️ 토큰 추가해주어야 함

✅ SQL profiling on

1️⃣ 데이터 1000명, btree index

❌ Without Index

☑️ btree index 생성

⭕️ With Index

👎🏻 Conclusion

2️⃣ 데이터 10000명, btree index

3️⃣ 데이터 10000명, FULLTEXT index를 쓰기로 함

👎🏻 Conclusion

4️⃣ 데이터 10000명, FULLTEXT index, n-gram

⭕️ With Index

☑️ ngram token 사이즈 변경

👎🏻 Conclusion

✅ 유저는 btree index로 검색하기로 결정

BTREE 🆚 FULLTEXT

✅ User name BTREE Index

☑️ SQL commands

☑️ Spring

❌ Without Index

⭕️ With Index

👍🏻 Improvements

❓ 이렇게 개발한 이유

✅ Goal title FULLTEXT Index

❌ Without Index

⭕️ With Index

👍🏻 Improvements

❓ 이렇게 개발한 이유

Trending Tags