조회 키가 어느 컬럼인지 정확히 알아야 했다

주문을 찾는 쿼리가 느렸다. 인덱스는 이미 있었다.

Table of contents

비슷한 컬럼이 여럿이었다

테이블을 열어 보니 주문을 가리키는 값이 넷이었다.

`no`              bigint       주문 일련번호
`order_no`        varchar(20)  주문번호 (화면에 보이는 것)
`channel_order_no` varchar(50) 채널이 준 주문번호
`payment_no`      varchar(30)  결제사 거래번호

넷 다 주문 하나를 가리키고 상황마다 다른 것을 쓰는데 이름이 비슷해 어느 것이 조회에 쓰이는지가 코드로는 안 갈렸다.

CREATE INDEX ix_order_no ON orders (order_no);

인덱스는 order_no 에 걸려 있었는데 화면에 보이는 번호이니 그것으로 찾을 것이라는 판단이 자연스럽다.

인덱스를 건 사람도 그렇게 봤을 텐데 그 판단이 지금도 맞는지는 따로 확인할 일이었다.

인덱스는 한 번 걸면 잘 안 들여다보고 걸 때의 판단이 그대로 굳어 몇 해를 간다.

그 사이에 조회하는 쪽은 계속 바뀌고 화면이 늘고 연동이 붙으며 무엇으로 찾는지가 달라진다.

원인 — 인덱스와 다른 조회 컬럼

느린 쿼리를 봤다.

SELECT * FROM orders WHERE channel_order_no = ?;

channel_order_no 로 찾는데 거기에는 인덱스가 없었고 이 조회가 하루 12만 번 돌고 있었다.

EXPLAIN SELECT * FROM orders WHERE channel_order_no = 'ABC123';

type: ALL   rows: 4218441

type 이 ALL 이고 rows 가 420만이라 한 건을 찾으려고 전체를 읽고 있었다.

인덱스가 있는데 느리면 그것을 안 타는 것이다. 조건의 컬럼과 인덱스의 컬럼이 달랐다.

인덱스가 있다는 사실만 확인하고 넘어가면 여기서 막힌다. 어느 컬럼에 걸렸는지까지 봐야 그다음이 나온다.

느리다는 제보와 인덱스가 있다는 사실이 둘 다 맞았고 둘을 이어 붙인 결론만 틀렸다.

어느 것으로 얼마나 찾는지 셌다

인덱스를 걸기 전에 실제 사용을 셌다.

$ grep -o "WHERE [a-z_]*_no = " /var/log/mysql/slow.log | sort | uniq -c | sort -rn
 122841 WHERE channel_order_no = 
  41022 WHERE order_no = 
   8820 WHERE payment_no = 

channel_order_no 가 order_no 보다 세 배 많이 쓰였고 있는 인덱스가 실제 쓰는 컬럼과 달랐다.

코드로 짐작하는 것과 나가는 조회를 세는 것은 결과가 달랐다. 배치와 연동에서 부르는 경로가 있어서다.

무엇에 인덱스를 걸지는 이 숫자가 정해 줬다. 짐작으로 걸었으면 또 order_no 를 손봤을 것이다.

판단 기준 — 값의 성격

걸기 전에 그 컬럼의 값이 어떤 성격인지 봤다.

SELECT COUNT(*), COUNT(DISTINCT channel_order_no) FROM orders;

4,218,441   4,102,882

COUNT(DISTINCT) 가 전체와 비슷해 인덱스로 잘 갈린다. NULL 이 얼마나 되는지도 봤다.

SELECT COUNT(*) FROM orders WHERE channel_order_no IS NULL;

115,559

직접 주문은 채널 주문번호가 없는데 NULL 이 많으면 인덱스의 쓸모가 줄지만 3%라 괜찮았다.

값이 안 갈리면 인덱스를 걸어도 효과가 작다. 거는 비용은 같으므로 걸기 전에 재 보는 것이 순서다.

인덱스를 거는 일은 되돌리기 쉬워 보여서 일단 걸어 보자는 쪽으로 기울기 쉽다. 400만 건에 8분이 걸리는 작업이라 실제로는 그렇지 않았다.

거는 시간과 지우는 시간이 둘 다 들어서 재 보고 거는 쪽이 두 번 일하지 않는 길이었다.

채널과 같이 걸었다

같은 채널 주문번호가 다른 채널에 있을 수 있다는 것이 걸렸다.

SELECT channel_order_no, COUNT(DISTINCT channel) FROM orders
GROUP BY channel_order_no HAVING COUNT(DISTINCT channel) > 1 LIMIT 5;

ABC123   2

실제로 그런 값이 있었고 채널 없이 찾으면 다른 채널의 주문이 나온다.

CREATE INDEX ix_channel_order ON orders (channel, channel_order_no);

channel 을 앞에 두고 함께 걸었고 조회하는 쪽 코드도 고쳤다.

$this->db->where('channel', $channel)->where('channel_order_no', $no);

인덱스를 거는 김에 조회 자체가 틀렸던 것을 찾았는데 느리다는 문제를 보러 갔다가 하나를 더 건졌다.

느린 조회를 들여다보면 그 조회가 무엇을 찾으려는 것인지까지 읽게 된다. 그 과정에서 조건이 모자란 것이 눈에 띈다.

성능 작업이 정확성 문제를 찾아 주는 경우가 있는데 평소 안 읽는 쿼리를 그때 읽기 때문이다.

검증 — 조회와 입력 양쪽

걸고 나서 다시 쟀다.

EXPLAIN SELECT * FROM orders WHERE channel = 'A' AND channel_order_no = 'ABC123';

type: ref   key: ix_channel_order   rows: 1

전   1.8초
후   0.002초

type 이 ref 가 되고 rows 가 1이 됐는데 조회만 보고 끝내면 판단이 반쪽이다.

전   INSERT 평균 1.2ms
후   INSERT 평균 1.4ms

인덱스가 늘면 넣을 때 일이 늘어 0.2밀리초 늘었는데 조회가 줄어든 것에 비하면 작았다.

한쪽만 재면 판단이 틀리고 거는 데 걸린 시간도 400만 건에 8분이라 새벽에 작업했다.

조회가 900배 빨라졌다는 숫자만 보고하면 듣는 쪽은 공짜로 보인다. 넣는 쪽이 얼마나 느려졌는지를 같이 적어야 그 거래가 보인다.

이번에는 그 거래가 명백히 남는 쪽이었지만 재 보고 말하는 것과 안 재고 말하는 것은 다르다.

주의 — 안 쓰는 인덱스

거는 김에 안 쓰는 것도 봤다.

SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'order';

세 개가 나왔고 하나는 이미 없어진 옛 화면에서 쓰던 것이었다.

지우기 전에 며칠 더 봤는데 통계가 재시작으로 초기화될 수 있어 한 번 보고 판단하면 틀린다.

인덱스를 거는 것과 지우는 것은 위험이 다르다. 지우는 쪽은 틀리면 조회가 전부 느려진다.

정리

• 비슷한 컬럼이 여럿이면 어느 것으로 찾는지를 실제 로그에서 센다
• 있는 인덱스가 실제 쓰는 컬럼과 다를 수 있다
• 코드로 짐작하면 배치와 연동에서 부르는 경로가 빠진다
• 걸기 전에 값이 잘 갈리는지와 NULL 비중을 본다
• 값이 안 갈리면 거는 비용은 같은데 얻는 것이 작다
• 조회 조건이 부족한 것이 그 과정에서 드러나기도 한다
• 걸고 나서 EXPLAIN 과 INSERT 시간을 둘 다 잰다
• 한쪽만 재면 판단이 틀린다
• ALTER TABLE 에 걸리는 시간을 미리 재고 서비스 시간을 피한다
• 안 쓰는 인덱스를 지울 때는 한 번 보고 판단하지 않는다