DB 설계서에 테이블이 하나 더 있었다.
search_user (검색용 유저 이메일, 닉네임 정보 테이블)컬럼이 넷이다.
seq
user_email
user_nick
reg_date전부 유저 테이블에도 있는 값이다.
Table of contents
Open Table of contents
설명이 붙어 있었다
seq 컬럼 설명이 이랬다.
user_info 테이블의 seq 값과 같은 값이다
(search_user 테이블의 seq 값은 user_info 테이블의 seq 값과 동기화된다)그리고 이런 문장도 있었다.
user_info 테이블에 신규 유저가 등록된 후
user_info 테이블에 생성된 seq 값을
search_user 테이블의 seq 컬럼에 저장시킨다본체에 넣고 나서 여기에도 넣는다는 것이다.
왜 나눴는지 궁금했다
같은 값을 두 곳에 두면 어긋날 위험이 생기므로 보통은 피한다. 굳이 나눈 이유가 있을 것 같아 두 테이블을 나란히 놓아 봤다.
user_info
seq, user_id, user_email, user_nick, user_level, user_exp,
user_coin, user_total_payment, user_state, bigwin_coin,
jackpot_coin, bigwin_multiple, jackpot_multiple, max_coin,
is_like, daily_date, last_visit, reg_date, ...search_user
seq, user_email, user_nick, reg_dateuser_info 는 스무 개 가까이 되고 search_user 는 넷이다. 유저 테이블 한 행이 훨씬 크고 검색 테이블은 가볍다.
그러면 같은 100만 행이어도 읽어야 할 양이 다르다. InnoDB 는 16KB 페이지 단위로 읽으니 행이 작을수록 한 페이지에 더 많이 들어가고 같은 행 수를 훑어도 읽는 페이지가 몇 분의 일이 된다.
검색이 어떤 모양인가
관리자 화면에서 유저를 찾는다면 이런 식일 것이다.
WHERE user_email LIKE '%검색어%' OR user_nick LIKE '%검색어%'앞에 와일드카드가 붙으면 인덱스를 못 탄다. 인덱스는 앞에서부터 맞춰 가는 구조라 % 가 앞에 있으면 소용이 없고 EXPLAIN 의 type 이 ALL 로 나온다. 그러면 행이 작을수록 빠르다.
유저 테이블에도 이메일과 닉네임에 인덱스가 걸려 있었다.
user_email index
user_nick index그런데 부분 일치 검색이면 그 인덱스가 안 쓰인다. 인덱스가 있어도 소용없는 검색이 있다는 것을 이때 알았다.
다른 길이 없지는 않았다. 검색에 쓰는 컬럼만으로 복합 인덱스를 걸면 Using index 로 표를 안 읽고 인덱스만 훑으므로 표를 나누지 않고도 읽는 양이 준다. InnoDB 의 FULLTEXT 에 ngram 파서를 붙이는 길도 그 무렵에는 열려 있었는데 설계서에는 둘 다 없었다.
갱신 성격도 달랐다
또 하나 떠오른 것이 있다. 유저 테이블은 코인이 바뀌고 경험치가 오르고 마지막 접속이 갱신되면서 게임 한 판마다 바뀌는 컬럼이 있다.
검색 테이블은 이메일과 닉네임이라 거의 안 바뀐다. 쓰기가 잦은 쪽과 거의 읽기만 하는 쪽으로 갈리니 성격이 다른 컬럼을 나눈 셈이 된다.
검증 — 대가와 양방향 대조
대신 잃는 것이 있다.
등록할 때 두 번 써야 한다
닉네임이 바뀌면 두 곳을 바꿔야 한다
한쪽만 되면 어긋난다설명에 동기화된다고 적혀 있는 것이 이 뜻이고 동기화를 애플리케이션이 책임진다. TRIGGER 로 묶는 길도 있지만 그것은 그것대로 숨은 동작이 된다.
어긋나면 어떤 일이 생길지도 생각해 봤다. 검색에만 있고 본체에 없으면 검색은 되는데 상세가 없고 본체에만 있고 검색에 없으면 존재하는데 안 찾아진다.
두 번째가 더 안 보인다. 유저는 있는데 관리자가 못 찾으니 버그로 인식되기까지 오래 걸릴 것 같다.
SELECT seq FROM user_info
WHERE seq NOT IN (SELECT seq FROM search_user);SELECT seq FROM search_user
WHERE seq NOT IN (SELECT seq FROM user_info);양쪽 다 봐야 한다. 한 방향만 돌리고 0건이 나오면 다른 방향의 누락은 그대로 남는다.
이 문제를 다르게 풀 수도 있었을 것 같다. 전문 검색 기능을 쓰거나 검색 결과를 캐시에 담아 두거나 뷰로 두는 방법이 떠올랐는데 뷰는 실제로 안 나뉘니까 성능 이득이 없을 것이고 전문 검색은 이 시점에 어떻게 쓰는지 잘 몰랐다. 나중에 다른 방법을 알게 되면 다시 볼 부분이다.
정리
- 검색만을 위해 컬럼 넷짜리
search_user를 따로 두었다 - 본체 행이 크면 훑는 비용이 커진다
- InnoDB 는 16KB 페이지로 읽으니 행이 작을수록 한 페이지에 더 들어간다
LIKE '%값%'는 인덱스를 못 쓰고EXPLAIN의type이ALL이 된다- 인덱스가 있어도 소용없는 검색이 있다
- 표를 나누지 않고
Using index로 가는 길도 있었다 - 자주 갱신되는 컬럼과 거의 안 바뀌는 컬럼을 나눈 셈이다
- 대신 두 곳에 쓰고 동기화를 애플리케이션이 책임진다
- 본체에만 있고 검색용에 없는 쪽이 더 안 보인다
- 양방향으로 대조해야 한 방향 누락을 안 놓친다