TIL MySQL 11/26/2021

커버링 인덱스

사연

약 178만 건 자료가 들어 있는 이력 로그 테이블이 하나 있다. 보다시피 auto increment PK 하나 말고는 인덱스가 없다시피하다.

CREATE TABLE `foo_history` (
  `foo_history_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Foo History ID',
  `foo_id` int(10) unsigned NOT NULL COMMENT 'Foo ID',
  /* 다른 컬럼도 많지만 여기서는 생략 */
  `register_date` datetime NOT NULL COMMENT '등록 일자',
  `modify_date` datetime DEFAULT NULL COMMENT '수정 일자',
  PRIMARY KEY (`foo_history_id`),
  KEY `nix-foo_history-foo_id` (`foo_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1791092 DEFAULT CHARSET=utf8mb4 AVG_ROW_LENGTH=963 COMMENT='Foo History'

여기서 10분 전 ~ 지금 생성된 foo_id를 골라줘야 하는 일이 생겼다. 아무 생각 없이 쿼리를 잡았다. 써 있는 그대로 실행하면 되는 쿼리이고 그래서 실행 계획도 별로 나빠 보이지 않았다.

SELECT /* 중략 */
FROM foo_history fh
WHERE fh.foo_id IN (179)
AND fh.register_date >= now() - INTERVAL 10 MINUTE

그런데 실행해 보니 어? 50초가 넘게 걸린다.

결과가 나오긴 하지만 이건 acceptable하지 않고 곤란한 수준이다. 어떡하지? 50초를 감내해야 하나? 내가 뭐 많은 거 바라는 게 아닌데? 그러던 중 문득, 가급적 인덱스를 활용하는 편이 좋다는 걸 어디서 주워들은 게 생각이 나서, 쿼리를 마개조를 했다.

SELECT /* 중략 */
FROM foo_history fh
WHERE fh.foo_id IN (179)
AND fh.foo_history_id >= (
    SELECT MIN(foo_history_id) FROM foo_history
    WHERE register_date >= now() - INTERVAL 10 MINUTE
);

딱 보기에도 너무 억지스럽고 EXPLAIN도 아주 안 좋아 보였다. 랄까 실행 계획 출력에 시간이 걸리는 건 처음 봤다. '그럼 그렇지 이렇게 마개조를 한 쿼리가 잘 될리가 없지 그럼 이제 얘는 몇 배나 더 오래 걸리나 그거만 빨리 한번 확인하고 넘어가자' 하고 실행을 했는데...

어??? 안 나올 줄 알았던 결과가 나온다. 심지어 더 빠르다. 11.281s +3ms라고 한다. 이건... 이건 벨로그에 가야 해!!!

원리

일단 느린 쿼리가 왜 느린지부터 풀자면, 간단한 얘기다. foo_history.register_date 컬럼에는 인덱스가 없다. 얘를 검사하려면 풀스캔을 돌아야 한다. 이 부분의 이 이상 자세한 설명은 생략한다.

하지만 foo_history 테이블에는 다행히 자동 증감 기본키인 foo_history_id가 있다. 그렇다면 그 기본키가 크면 클수록 register_date의 값도 더 커질 것이라고 기대할 수 있다. 그렇다면:

1. 일단 한 번 foo_history를 쭉 돌면서 register_date 조건을 만족하는 가장 작은 PK 값 F를 하나 찾아놓고
2. 그 다음 foo_history를 질의할 때는 PK >= F인 것만 찾으라고 시키면
3. MySQL은 아무 생각 없이 PK 인덱스를 훑으면서 PK < F인 자료는 아예 쳐다도 보지 않게 되고

그래서 속도가 오르는 것이다.

전문 용어를 쓰자면, 이런 기법을 커버링 인덱스라고 부르는 모양이다. https://gywn.net/2012/04/mysql-covering-index/

1단계 자체가 오래 걸리는 작업이기 때문에 쿼리 성능이 오르지 않을 것이라고 예상했었다. 그런데 결과적으로는, 그 어려운 일을 한번 해놓고 나면, 그 다음부터는 인덱스를 사용해서 전체 자료의 90% 이상을 무시하고 갈 수 있기 때문에, 최종적으로는 성능이 개선되었다.

교훈

• 인덱스를 창의적으로 활용하자.
• 하여간 뭐든 시도해보고 볼 일이다.
• 어려운 일을 한번 해낸 경험으로부터 뭔가를 배우면, 이후의 일이 쉬워지는 수가 있다. 쿼리도 인생도...