한 테이블에 다 담으니 인덱스를 못 걸었다

MySQL 로그 테이블 하나에 여러 종류를 담고 있었다. 조회가 느려서 EXPLAIN 을 보고 인덱스를 걸려는데 어디에 걸어야 할지 정할 수가 없었다.

Table of contents

한 테이블에 다 있었다

테이블은 이랬다.

CREATE TABLE app_log (
  no        bigint      NOT NULL AUTO_INCREMENT,
  log_type  varchar(20) NOT NULL,   -- LOGIN / ORDER / ERROR / BATCH
  member_no int         DEFAULT NULL,
  order_no  bigint      DEFAULT NULL,
  message   text,
  reg_date  datetime    NOT NULL,
  PRIMARY KEY (no)
);

종류마다 쓰는 컬럼이 달랐다.

LOGIN   member_no 를 쓴다. order_no 는 NULL
ORDER   order_no 를 쓴다. member_no 도 쓴다
ERROR   둘 다 NULL. message 만 있다
BATCH   둘 다 NULL

한 행에서 쓰이지 않는 컬럼이 절반쯤이었다. 그것이 전부 NULL 로 채워져 있었다. 어느 컬럼이 어느 종류에서 쓰이는지는 코드를 봐야 알 수 있었다.

결과 — 넣는 속도를 쟀다

조회도 종류마다 달랐다.

-- 회원별 로그인 이력
SELECT * FROM app_log WHERE log_type='LOGIN' AND member_no=? ORDER BY reg_date DESC;

-- 주문 처리 이력
SELECT * FROM app_log WHERE log_type='ORDER' AND order_no=?;

-- 어제 오류
SELECT * FROM app_log WHERE log_type='ERROR' AND reg_date >= ?;

셋이 다 다른 조건이라 각각에 맞는 인덱스가 다르다.

CREATE INDEX ix1 ON app_log (log_type, member_no, reg_date);
CREATE INDEX ix2 ON app_log (log_type, order_no);
CREATE INDEX ix3 ON app_log (log_type, reg_date);

셋을 다 걸면 넣는 것이 느려진다. InnoDB 는 행 하나를 넣을 때 인덱스마다 항목을 더 넣기 때문이다.

-- 인덱스 없이
INSERT 10000건: 4.2초

-- 인덱스 3개
INSERT 10000건: 11.8초

세 배 가까이 느려졌다. 로그는 넣는 양이 많은 테이블이라 그 비용을 감당할 수 없었다. 로그를 넣는 것 때문에 주문 처리가 느려지는 상황이었다.

나누기로 했다

종류마다 테이블을 나눴다.

CREATE TABLE login_log (
  no        bigint   NOT NULL AUTO_INCREMENT,
  member_no int      NOT NULL,
  ip        varchar(45),
  reg_date  datetime NOT NULL,
  PRIMARY KEY (no),
  KEY ix_member (member_no, reg_date)
);

CREATE TABLE order_log (
  no        bigint   NOT NULL AUTO_INCREMENT,
  order_no  bigint   NOT NULL,
  state     varchar(20) NOT NULL,
  reg_date  datetime NOT NULL,
  PRIMARY KEY (no),
  KEY ix_order (order_no)
);

각 테이블에 필요한 인덱스만 있고 log_type 컬럼도 없어졌다. NULL 이던 컬럼이 사라지니 무엇이 필수인지가 드러난다.

전부 나누려다 멈춘 것이 있었다. 전체 이력을 시간순으로 본다는 요구가 있었는데 나누면 네 테이블을 합쳐서 정렬해야 한다.

물어보니 실제로 그렇게 보는 일이 한 달에 한 번쯤이었다. 그 한 번을 위해 매일 느린 것을 감수할 이유가 없었다. 그때만 각 테이블을 따로 뽑아 UNION ALL 로 합치기로 했다.

옮기는 순서와 옛 자료

한 번에 옮기면 그동안 로그가 빠진다.

1. 새 테이블을 만든다
2. 넣는 코드가 양쪽에 다 넣게 한다
3. 읽는 코드를 하나씩 새 테이블로 옮긴다
4. 옛 테이블에 넣는 것을 뺀다
5. 옛 데이터를 옮기거나 그대로 둔다

2번 동안은 두 배로 넣지만 안전했다. 읽는 쪽을 옮기다 문제가 생기면 옛 테이블로 돌아갈 수 있다.

옛 자료는 얼마나 되는지부터 봤다.

SELECT log_type, COUNT(*) FROM app_log GROUP BY log_type;

LOGIN   4,182,033
ORDER     882,410
ERROR      41,220
BATCH      12,884

500만 건이라 전부 옮기는 데 오래 걸린다. 물어보니 로그인 로그는 3개월치만 보면 된다고 해서 그만큼만 옮기고 나머지는 옛 테이블에 남겼다.

옛 테이블은 조회용으로만 남기고 이름을 바꿨다.

RENAME TABLE app_log TO app_log_archive_20140410;

이름에 날짜를 넣어서 언제까지 것인지 드러나게 했다.

정리

• 여러 용도를 한 MySQL 테이블에 담으면 인덱스 선택이 막힌다
• 조회 조건이 종류마다 다르면 인덱스도 종류마다 필요하다
• InnoDB 는 인덱스마다 항목을 넣으므로 다 걸면 INSERT 가 느려진다. 재 보고 정한다
• 나누면 각 테이블에 필요한 인덱스만 남고 NULL 컬럼도 없어진다
• 나누기 전에 합쳐서 봐야 하는 요구가 있는지 확인하고 빈도를 물어본다
• 옮길 때는 양쪽에 다 넣는 기간을 둔다. 문제가 생기면 돌아갈 수 있다
• 옛 자료를 전부 옮길 필요는 없다. 얼마나 필요한지 물어본다
• 남기는 테이블 이름에 날짜를 넣어 언제까지 것인지 드러나게 한다