백만 건이 넘는 trades 를 다루는 회원별 체결 조회가 느려서 EXPLAIN 을 봤다. rows 가 수백만이고 Using temporary 와 Using filesort 까지 붙어 있었다.
Table of contents
Open Table of contents
증상 — 실행계획이 고른 다른 인덱스
EXPLAIN 의 key 는 idx_trades_side 였다. 우리 조건 member_id·market 과 무관한 인덱스다. 선택도가 낮아서 대량으로 훑은 뒤에 조건을 검사하는 형태였다.
그러면 왜 조건에 맞는 인덱스를 안 쓰는지가 다음 물음이다. 실행계획만 보면 다른 것을 쓴다는 사실까지이고 이유는 SHOW INDEX 로 인덱스 정의를 봐야 나온다.
원인 — 선행 컬럼이 조건에 없었다
인덱스 목록을 보니 우리가 쓰는 두 컬럼이 복합 인덱스 idx_trades_active 의 세 번째와 네 번째에 있었다. 앞의 두 컬럼은 created_at 과 settled_at 기간 조건이었고 우리 쿼리에는 그것이 없었다.
복합 인덱스는 왼쪽 접두(leftmost prefix)부터 연속으로 써야 타므로 앞이 비면 뒤의 두 컬럼만으로는 탈 수 없다. MySQL 5.7 매뉴얼의 다중 컬럼 인덱스 절이 그렇게 정하고 있고 MariaDB 도 같은 InnoDB 라 다르지 않다. 그래서 옵티마이저가 관계없는 인덱스로 물러난 것이었다.
임시 테이블과 파일 정렬
인덱스를 못 타면 정렬 순서도 인덱스에서 얻을 수 없다. 전부 읽어서 따로 정렬하므로 Using temporary 와 Using filesort 가 함께 붙는다.
두 가지 방법이 있었다. 하나는 쿼리에 created_at 기간 조건을 억지로 붙여 기존 인덱스를 타게 하는 것이다. 다른 하나는 (member_id, market, created_at) 순서로 새 인덱스를 만드는 것이다. 뒤쪽을 골랐다. 이유는 회원별 전체 이력 화면에는 기간이 없어서다. 앞쪽은 화면 요건을 바꾸는 일이 된다. 대신 trades 에 인덱스 하나만큼 쓰기 비용이 느는 것을 감수했다. 다시 실행계획을 보니 rows 가 줄고 임시 테이블과 파일 정렬이 사라졌다.
조치 — 무중단 추가와 옵션 명시
운영 테이블이라 인덱스를 추가할 때 ALGORITHM=INPLACE, LOCK=NONE 을 명시했다. 명시하지 않으면 서버가 상황에 따라 테이블 복사와 잠금을 고를 수 있다.
명시해 두면 그 방식이 안 될 때 오류로 끝나고 실행되지 않는다. 운영 중에 몇 분 잠기는 것보다 실행이 안 되는 편이 낫기 때문이다.
이름이 말하지 않는 용도
rows 만 보면 개선이 작아 보였지만 임시 테이블과 파일 정렬이 사라진 것이 실제로 컸다. 실행계획의 rows 는 추정치이므로 그 값만으로 판단하지 않고 Extra 를 함께 봤다.
기존 인덱스의 이름 idx_trades_active 는 유효한 체결을 뜻하는 것으로 읽힌다. 그래서 이 조건에 맞는 것처럼 보였다. 실제로는 기간 조건이 있어야 타므로 이름이 용도를 말해 주지 않았고 그 사실을 규칙으로 남겼다.
정리
- 복합 인덱스는 왼쪽 접두부터 연속으로 써야 탄다
- 선행 컬럼이 조건에 없으면 다른 인덱스로 물러난다
- 그러면 대량 스캔에
Using temporary와Using filesort가 붙는다 - 실행계획만 보면 이유가 안 나오고
SHOW INDEX를 함께 본다 rows만 보면 개선이 작아 보이므로Extra를 같이 본다- 실행계획의
rows는 추정치다 - 대형 테이블은
ALGORITHM=INPLACE, LOCK=NONE을 명시해 무중단으로 넣는다 - 인덱스 이름이 용도를 말해 주지 않는다