20260522 [TIL] - 채팅 문의 인덱싱 정리

개요

문의 채팅의 핵심 테이블은 support_rooms와 support_messages다. 두 테이블은 같은 문의 채팅 도메인 안에 있지만, 조회 패턴과 쓰기 부담이 다르게 나타난다.

• support_rooms는 고객 문의방 목록, 상담사 배정 목록, 운영 큐, 응답 지연 방, 종료 이력처럼 운영 화면에서 반복적으로 조회된다.
• support_messages는 최신 메시지 조회, 커서 기반 이전 메시지 조회, unread count, AI fallback용 최신 USER 메시지 탐색처럼 room 단위의 메시지 접근이 반복된다.
• 인덱스 비교는 데이터 적재 시간과 분리해 보는 편이 더 명확하다고 봤다. 먼저 seed 테이블을 만들고, 그 테이블을 후보군별로 복제한 뒤 no-index와 각 인덱스 구성을 같은 데이터셋에서 비교했다.

이번 정리의 목적은 인덱스를 많이 붙이는 것이 아니라, 실제 조회 패턴에서 읽기 이득과 쓰기 부담이 어디에서 균형을 이루는지 확인하는 데 있다.

---

🧭 조회 패턴

support_rooms

시나리오 where / order by 핵심

고객 문의방 목록	customer_user_id, last_message_at desc, id desc
상담사 배정 방 목록	counselor_user_id, status, last_message_at desc, id desc
운영 큐 목록	status, counselor_user_id is null, customer_requested_counselor_at, last_message_at
응답 지연 방 목록	status, counselor_last_active_at <= cutoff
종료 이력 목록	last_counselor_user_id, status, updated_at desc, id desc
solved 자동 종료 배치	status, solved_at <= cutoff

support_messages

시나리오 where / order by 핵심

최신 메시지 50개	room_id, id desc
커서 기반 이전 메시지 50개	room_id, id < beforeMessageId, id desc
AI fallback 최신 USER 메시지	room_id, sender_type, id desc
고객 unread count	room_id, id > lastRead, sender 제외 조건
상담사 unread count	room_id, id > lastRead, sender 제외 조건

---

1. 후보군 정의

support_rooms 후보군

후보군 기준

no-index	PK 외 추가 인덱스 없이 baseline으로 비교
list-optimized	고객 목록, 상담사 배정 목록처럼 목록성 조회를 먼저 받치기 위한 구성
operation-optimized	목록 조회에 더해 운영 큐, 응답 지연, 종료 이력까지 함께 고려한 구성
heavy	운영형 구성 위에 상태 중심 보조 인덱스를 더 올린 확장 후보

no-index

• PK만 사용하고 추가 인덱스는 두지 않았다.

list-optimized

create index idx_customer_last_message_id
on support_rooms (customer_user_id, last_message_at desc, id desc);

create index idx_counselor_status_last_message_id
on support_rooms (counselor_user_id, status, last_message_at desc, id desc);

operation-optimized

create index idx_customer_last_message_id
on support_rooms (customer_user_id, last_message_at desc, id desc);

create index idx_counselor_status_last_message_id
on support_rooms (counselor_user_id, status, last_message_at desc, id desc);

create index idx_queue_request_id
on support_rooms (status, counselor_user_id, customer_requested_counselor_at, id);

create index idx_status_counselor_active_id
on support_rooms (status, counselor_last_active_at, id);

create index idx_last_counselor_status_id
on support_rooms (last_counselor_user_id, status, id);

create index idx_status_solved_at_id
on support_rooms (status, solved_at, id);

heavy

create index idx_customer_last_message_id
on support_rooms (customer_user_id, last_message_at desc, id desc);

create index idx_counselor_status_last_message_id
on support_rooms (counselor_user_id, status, last_message_at desc, id desc);

create index idx_queue_request_id
on support_rooms (status, counselor_user_id, customer_requested_counselor_at, id);

create index idx_status_counselor_active_id
on support_rooms (status, counselor_last_active_at, id);

create index idx_last_counselor_status_id
on support_rooms (last_counselor_user_id, status, id);

create index idx_status_solved_at_id
on support_rooms (status, solved_at, id);

create index idx_customer_status_id
on support_rooms (customer_user_id, status, id);

create index idx_counselor_status_id
on support_rooms (counselor_user_id, status, id);

create index idx_status_last_message_id
on support_rooms (status, last_message_at desc, id desc);

support_messages 후보군

후보군 기준

no-index	PK 외 추가 인덱스 없이 baseline으로 비교
minimal	room 단위 최신/이전 메시지 조회를 가장 낮은 비용으로 받치기 위한 최소 구성
fallback	minimal에 sender_type 조건을 더해 AI fallback 조회까지 보강한 구성
heavy	fallback 위에 message_type, created_at, sender_user_id 축까지 넓힌 확장 후보

minimal

create index idx_room_id_id
on support_messages (room_id, id);

fallback

create index idx_room_id_id
on support_messages (room_id, id);

create index idx_room_id_sender_type_id
on support_messages (room_id, sender_type, id);

heavy

create index idx_room_id_id
on support_messages (room_id, id);

create index idx_room_id_sender_type_id
on support_messages (room_id, sender_type, id);

create index idx_room_id_message_type_id
on support_messages (room_id, message_type, id);

create index idx_room_id_created_at_id
on support_messages (room_id, created_at, id);

create index idx_sender_user_id_created_at_id
on support_messages (sender_user_id, created_at, id);

---

🧪 측정 환경과 지표

항목 값

도구	JUnit 5, JdbcTemplate, MySQL EXPLAIN
Spring Boot	4.0.5
Gradle	8.14.4
JDK	Temurin 21.0.10
MySQL	8.0
기준 데이터	bench_support_rooms_seed, bench_support_messages_seed
support_rooms row 수	50,000
support_messages row 수	50,000
비교 방식	seed 테이블 복제 후 후보군별 인덱스 적용
read 측정	warm-up 1회, measured 5회
write 측정	warm-up 1회, measured 5회, 라운드마다 seed 복제

write 시나리오별 작업 수

테이블 시나리오 operations per round

support_rooms	last message update	5,000
support_rooms	counselor active update	1,500
support_rooms	counselor assign update	300
support_messages	message insert	5,000

지표

지표 의미

avg	평균 처리 시간
min	가장 빠른 샘플
max	가장 느린 샘플
stddev	샘플 간 흔들림
diff vs no-index	no-index 대비 변화율
EXPLAIN	실제로 어떤 인덱스를 탔는지 확인하는 근거

read는 avg / min / max / stddev를 ms 단위로 비교했고, write는 1건당 평균 ms 기준으로 비교했다.

---

2. 실행 결과

📌 support_rooms 읽기 성능

시나리오 no-index list-optimized operation-optimized heavy

customer room list	17.302ms	10.119ms (-41.52%)	9.760ms (-43.59%)	9.831ms (-43.18%)
assigned counselor room list	17.136ms	10.834ms (-36.78%)	10.973ms (-35.97%)	10.287ms (-39.97%)
queue list	19.661ms	16.668ms (-15.22%)	16.682ms (-15.15%)	18.539ms (-5.70%)
stale response room list	20.142ms	17.910ms (-11.08%)	9.120ms (-54.72%)	8.867ms (-55.98%)
closed history list	17.185ms	18.264ms (+6.28%)	10.349ms (-39.78%)	9.480ms (-44.84%)

support_rooms 읽기 상세 지표

시나리오 no-index list-optimized operation-optimized heavy

customer room list	17.302 / 16.311 / 17.912 / 0.620	10.119 / 9.730 / 10.605 / 0.397	9.760 / 9.247 / 9.979 / 0.311	9.831 / 9.090 / 10.650 / 0.680
assigned counselor room list	17.136 / 16.791 / 17.423 / 0.227	10.834 / 10.278 / 11.491 / 0.458	10.973 / 10.024 / 12.462 / 1.095	10.287 / 9.548 / 11.057 / 0.579
queue list	19.661 / 18.702 / 20.571 / 0.806	16.668 / 15.906 / 17.064 / 0.456	16.682 / 16.238 / 17.418 / 0.505	18.539 / 17.089 / 20.891 / 1.418
stale response room list	20.142 / 19.143 / 21.531 / 0.993	17.910 / 17.248 / 19.739 / 1.048	9.120 / 8.605 / 10.087 / 0.621	8.867 / 8.561 / 9.326 / 0.288
closed history list	17.185 / 15.683 / 18.941 / 1.237	18.264 / 17.384 / 19.544 / 0.873	10.349 / 9.883 / 10.496 / 0.261	9.480 / 8.849 / 9.871 / 0.412

📌 support_rooms 쓰기 성능

시나리오 no-index list-optimized operation-optimized heavy

last message update	11.023ms	11.724ms (+6.36%)	11.508ms (+4.41%)	11.463ms (+3.99%)
counselor active update	11.101ms	11.109ms (+0.07%)	10.921ms (-1.62%)	11.237ms (+1.22%)
counselor assign update	6.546ms	6.565ms (+0.29%)	6.648ms (+1.56%)	6.661ms (+1.76%)

support_rooms 쓰기 상세 지표

시나리오 no-index list-optimized operation-optimized heavy

last message update	11.023 / 10.569 / 11.348 / 0.338	11.724 / 11.001 / 12.408 / 0.511	11.508 / 11.206 / 12.102 / 0.349	11.463 / 11.036 / 11.920 / 0.369
counselor active update	11.101 / 10.467 / 11.611 / 0.507	11.109 / 10.522 / 11.740 / 0.495	10.921 / 10.609 / 11.444 / 0.321	11.237 / 10.696 / 11.946 / 0.510
counselor assign update	6.546 / 6.495 / 6.633 / 0.053	6.565 / 6.354 / 6.711 / 0.159	6.648 / 6.476 / 6.743 / 0.105	6.661 / 6.579 / 6.880 / 0.127

📌 support_messages 읽기 성능

시나리오 no-index minimal fallback heavy

latest 50 messages	12.247ms	8.541ms (-30.26%)	8.459ms (-30.93%)	8.073ms (-34.08%)
previous 50 messages by cursor	12.647ms	9.570ms (-24.33%)	9.657ms (-23.64%)	7.726ms (-38.91%)
ai fallback latest user message	8.110ms	8.875ms (+9.43%)	8.986ms (+10.80%)	8.186ms (+0.93%)
customer unread count	8.785ms	8.200ms (-6.66%)	7.738ms (-11.92%)	8.935ms (+1.71%)
counselor unread count	8.178ms	8.068ms (-1.35%)	11.733ms (+43.46%)	9.592ms (+17.29%)

support_messages 읽기 상세 지표

시나리오 no-index minimal fallback heavy

latest 50 messages	12.247 / 11.428 / 13.210 / 0.659	8.541 / 7.796 / 9.811 / 0.824	8.459 / 7.914 / 8.952 / 0.457	8.073 / 7.756 / 8.427 / 0.302
previous 50 messages by cursor	12.647 / 11.702 / 14.064 / 0.988	9.570 / 9.166 / 10.215 / 0.469	9.657 / 9.315 / 10.002 / 0.262	7.726 / 7.301 / 8.418 / 0.489
ai fallback latest user message	8.110 / 7.792 / 8.284 / 0.188	8.875 / 8.413 / 9.303 / 0.336	8.986 / 8.251 / 9.530 / 0.536	8.186 / 7.915 / 8.402 / 0.199
customer unread count	8.785 / 8.433 / 9.038 / 0.239	8.200 / 7.722 / 8.621 / 0.432	7.738 / 7.216 / 8.435 / 0.450	8.935 / 8.729 / 9.322 / 0.248
counselor unread count	8.178 / 7.586 / 8.912 / 0.507	8.068 / 7.346 / 8.449 / 0.445	11.733 / 9.460 / 19.498 / 4.347	9.592 / 9.079 / 9.971 / 0.343

📌 support_messages 쓰기 성능

시나리오 no-index minimal fallback heavy

message insert

10.852ms

10.848ms (-0.04%)

10.532ms (-2.95%)

10.924ms (+0.66%)

support_messages 쓰기 상세 지표

시나리오 no-index minimal fallback heavy

message insert

10.852 / 10.658 / 11.049 / 0.183

10.848 / 10.606 / 11.404 / 0.322

10.532 / 10.370 / 10.713 / 0.123

10.924 / 10.792 / 10.985 / 0.076

---

3. EXPLAIN에서 먼저 본 변화

support_rooms

시나리오 no-index 인덱스 적용 후 해석

customer room list	type=ALL, rows=50000, Using where; Using filesort	idx_customer_last_message_id, type=ref, rows=100, Using index	고객 목록은 customer_user_id + last_message_at 축이 직접 먹혔다.
assigned counselor room list	type=ALL, rows=50000	list-optimized는 idx_counselor_status_last_message_id, operation/heavy는 idx_queue_request_id	상담사 + 상태 조합이 들어가면 인덱스 후보가 달라질 수 있다는 점이 보였다.
queue list	type=ALL, rows=50000, Using filesort	list/operation은 idx_counselor_status_last_message_id, heavy는 idx_counselor_status_id	운영 큐는 목록 인덱스만으로는 완전히 정리되지 않았고, 여전히 filesort가 남았다.
stale response room list	type=ALL, rows=50000, Using filesort	operation/heavy는 idx_status_counselor_active_id, type=range, rows=11886	응답 지연 조회는 운영형 인덱스의 필요성이 가장 분명하게 드러난 구간이었다.
closed history list	type=ALL, rows=50000, Using filesort	operation/heavy는 idx_last_counselor_status_id, type=ref, rows=1	종료 이력은 last_counselor_user_id + status 축이 직접 효과를 냈다.

support_messages

시나리오 no-index 인덱스 적용 후 해석

latest 50 messages	PRIMARY, backward scan	idx_room_id_id, type=ref, rows=100, Using index	최신 메시지 조회는 (room_id, id) 하나만으로도 충분히 줄었다.
previous 50 messages by cursor	PRIMARY, rows=24958, backward scan	minimal은 rows=99, fallback은 skip scan, heavy는 created_at index 기반 filesort	커서 페이지네이션은 (room_id, id)가 가장 직관적으로 맞았고, 확장 인덱스는 오히려 우회 경로를 타기도 했다.
ai fallback latest user message	no-index도 backward scan으로 빠름	fallback/heavy는 idx_room_id_sender_type_id 사용	sender_type 보조 인덱스는 실행 계획에는 반영됐지만, 평균 시간 이득은 크지 않았다.
unread count	PRIMARY, rows=3962	모든 인덱스 후보에서 idx_room_id_id, rows=4	unread 계산은 room 축을 먼저 좁히는 구성이 중요했다.

---

4. 최종 선택

support_rooms는 operation-optimized

operation-optimized는 읽기와 쓰기 사이의 균형이 가장 좋았다.

• customer room list: 43.59%
• stale response room list: 54.72%
• closed history list: 39.78%
• queue list도 list-optimized와 거의 같은 수준이었다.

쓰기 저하는 크지 않았다.

• last message update: +4.41%
• counselor active update: 1.62%
• counselor assign update: +1.56%

heavy는 일부 조회에서 더 빠르긴 했지만, 운영 큐에서 오히려 밀렸고 쓰기에서도 분명한 추가 이점을 만들지는 못했다. list-optimized는 목록성 조회에는 맞았지만 운영성 조회까지 같이 받치기에는 부족했다.

최종 구성은 아래 6개다.

create index idx_customer_last_message_id
on support_rooms (customer_user_id, last_message_at desc, id desc);

create index idx_counselor_status_last_message_id
on support_rooms (counselor_user_id, status, last_message_at desc, id desc);

create index idx_queue_request_id
on support_rooms (status, counselor_user_id, customer_requested_counselor_at, id);

create index idx_status_counselor_active_id
on support_rooms (status, counselor_last_active_at, id);

create index idx_last_counselor_status_id
on support_rooms (last_counselor_user_id, status, id);

create index idx_status_solved_at_id
on support_rooms (status, solved_at, id);

support_messages는 minimal

support_messages는 읽기 이득만 보는 것보다, insert 비용을 거의 건드리지 않으면서 핵심 조회를 얼마나 안정적으로 받쳐 주는지가 더 중요했다.

minimal은 다음 구간에서 고르게 안정적이었다.

• latest 50 messages: 30.26%
• previous 50 messages by cursor: 24.33%
• customer unread count: 6.66%
• counselor unread count: 1.35%
• message insert: 0.04%

fallback은 message insert만 보면 -2.95%로 가장 좋았지만, 읽기에서는 counselor unread count가 +43.46%까지 흔들렸고 stddev도 4.347로 가장 컸다. heavy는 일부 페이지 조회가 더 빨랐지만, 전체 기본안으로 두기에는 과한 구성에 가까웠다.

최종 구성은 아래 1개다.

create index idx_room_id_id
on support_messages (room_id, id);

---

🔍 더 고려해 볼 수 있는 점

메시지 row 수를 더 키운 보강 비교

이번 정리는 support_rooms 50,000, support_messages 50,000 기준이다. 실제 문의 채팅에서는 room보다 message가 더 빠르게 누적되므로, 다음 단계에서는 support_messages만 더 큰 row 수로 다시 비교해 보는 것도 자연스럽다.

count 쿼리 분리

페이지 기반 API가 늘어나면 COUNT 쿼리 비용이 다시 병목으로 드러날 수 있다. 그 시점에는 count 전용 쿼리를 분리해서 JOIN과 DISTINCT를 줄이는 방향을 함께 검토할 수 있다.

unread 계산 구조 분리

지금 unread는 last_read_message_id 기준으로 다시 계산하는 구조다. 규모가 더 커지면 room summary materialization이나 별도 unread 집계 구조로 나누는 방향도 고려할 수 있다.