관리 화면의 느린 주문 검색을 개선하려고 orders 에 인덱스를 셋 설계해서 추가하고 배포까지 했다. 넣기 전에 EXPLAIN 으로 확인까지 했는데 체감 개선이 없다는 말을 들었다.
Table of contents
Open Table of contents
증상 — 인덱스를 넣었는데 체감이 없었다
추가하기 전에 확인한 실행계획에서는 분명히 새 인덱스 idx_orders_member_status 를 타고 있었다. 그러면 확인 자체가 잘못됐거나 확인한 것과 실제로 도는 것이 다르다는 뜻이다.
돌이켜 보니 내가 EXPLAIN 에 넣은 조건은 손으로 직접 쓴 것이었다. 그것이 앱의 OrderSpecification 이 실제로 만드는 조건과 같은지는 한 번도 확인한 적이 없었다.
원인 — 손으로 쓴 조건과 앱이 만드는 조건
두 가지 방법이 있었다. 쿼리 빌더 코드를 읽어 조건을 재구성하는 것과, spring.jpa.show-sql 을 켜고 실제로 나간 SQL 을 로그에서 그대로 복사하는 것이다. 뒤쪽을 골랐다. 이유는 코드를 읽으면 또 한 번 내 손을 거쳐 단순화될 수 있어서다. 대신 운영 로그가 잠시 커지는 것을 감수했다.
로그를 보니 조건 하나가 (member_id = ? OR parent_order_id = ?) 로 두 항목을 묶은 형태로 조립되고 있었다. 내가 실행계획에 넣었던 조건에는 그 OR 부분이 아예 없었다. 손으로 쓰면 핵심 조건만 남기고 무의식적으로 단순화하므로 인덱스가 잘 도는 것처럼 보인다. 실제 조건을 그대로 넣어 다시 돌리니 새 인덱스를 아예 안 타고 있었다.
OR 가 막은 인덱스
member_id 와 parent_order_id 각각은 인덱스로 찾을 수 있는데 OR 로 묶이면 단일 인덱스로 커버가 안 된다. 옵티마이저는 정렬 순서를 얻을 수 있는 PRIMARY 로 물러났다.
ORDER BY id DESC LIMIT 50 이 붙어 있으니 기본 키 역순으로 훑다가 50건을 찾으면 멈추는 편이 낫다고 본 것이다. 인덱스가 있어도 조건의 형태가 그것을 못 쓰게 만든 경우였다.
검증 — 거짓이던 설계 전제
인덱스를 설계할 때 대량 주문의 부모 행은 parent_order_id 가 NULL 이라는 전제를 깔고 있었다. 그 전제가 맞는지 실제로 세어 보니 해당 조합의 행이 하나도 없었다.
부모 행은 parent_order_id 에 자기 id 가 들어 있었으므로 전제가 처음부터 거짓이었다. 틀린 전제 위에서 만든 인덱스라 조건을 못 좁히는 것이 당연한 결과였다.
절반을 넘는 조건과 세 방안
이 조건이 실제로 몇 건을 잡는지 세어 보니 전체의 절반을 넘었다. 절반을 넘게 잡는 조건은 인덱스로 좁혀지지 않으므로 인덱스를 더 넣어도 해결되지 않는다.
남은 것은 셋이었다. 조건을 나눠 각각 인덱스를 타게 한 뒤 UNION 으로 합치거나, MySQL 5.7 매뉴얼의 Index Merge 절이 말하는 Using union 이 걸리도록 두 컬럼에 별도 인덱스를 두거나, 조건 자체를 계산 컬럼으로 만들어 거는 것이다. 인덱스 추가가 아니라 쿼리 구조나 스키마를 바꾸는 쪽으로 가야 하는 자리였다.
정리
- 인덱스를 넣고 체감이 없으면
show-sql로 실제 생성 쿼리를 역추적한다 EXPLAIN에 넣는 조건은 앱이 만드는 것이어야 한다- 손으로 쓰면 단순화되어 인덱스가 잘 도는 것처럼 보인다
OR로 묶은 조건은 단일 인덱스로 커버가 안 된다ORDER BY … LIMIT이 있으면PRIMARY역순으로 물러난다- 설계의 전제 자체가 거짓일 수 있으므로 세어서 확인한다
- 조건이 전체의 절반을 넘으면 인덱스로 안 줄어든다
- 그때는
UNION·Index Merge·계산 컬럼처럼 쿼리 구조나 스키마를 바꾼다