Index Dive 비용 최적화

MySQL 서버의 실행계획 수립(Index Dive) 단계에서 많은 CPU와 Disk storage 자원이 소모되는 경우, 어떻게 튜닝할 수 있을까?

MySQL 서버는 여러 통계 정보를 이용해 가장 빠른 쿼리 실행 방식을 결정하는데, 이를 우리는 흔히 최적화라고 해요. Oracle이나 PostgreSQL 서버는 쿼리의 실행 계획을 캐시해서 재활용하는 반면, MySQL 서버에서는 아직 쿼리 실행 계획의 캐시 또는 재활용이 필수적으로 사용되지 않는 편이에요. MySQL 서버의 쿼리 실행 계획이 커넥션내에서만 캐시되고 재활용될 수 있기 때문이에요.

MySQL 서버에서 쿼리가 실행되면, 옵티마이저(Optimizer)는 통계 정보뿐만 아니라 실제 테이블의 데이터를 샘플링해서 확인 후, 최종 사용할 실행 계획을 선택해요. 이때 실제 데이터를 샘플링해서 확인하는 과정을 Index Dive 또는 Random Index Dive라고 해요.

이런 이유로 MySQL 서버의 실행 계획 수립 단계는 조금 무겁게 느껴져요. 아직 개선이 필요한 부분이기도 하고요. 하지만, 일반적으로 우리가 자주 사용하는 가벼운 쿼리들에 대해서는 MySQL 서버의 실행 계획 수립 과정이 거의 문제되지 않아요. 때로는 이렇게 직접 데이터를 샘플링해서 살펴보는 과정으로 인해서 선택되는 실행 계획 자체는 다른 DBMS 서버보다 정확할 때도 있고요.

오늘은 MySQL 서버의 실행 계획 수립의 Index Dive 과정으로 인해서 의도치 않게 많은 CPU와 Disk storage 자원이 소모되는 경우와 튜닝 방법에 대해서 살펴보려고 해요.

Performance Schema 확인

MySQL 서버의 성능에 대해서 관심이 많은 경우, MySQL 서버의 Performance Schema 또는 AWS의 Performance Insights를 살펴본 적이 있을 거예요. 일반적으로 Performance Schema 또는 Performance Insights에는 쿼리의 실행(executing) 자체에 많은 자원이 사용되어야 하는데, 가끔은 아래와 같이 통계 수집(statistics) 단계에서 많은 자원이 소모되는 경우를 확인할 수 있을 거예요. Performance Schema가 어렵다면, AWS의 Performance Insights를 참조해도 동일한 정보를 수집할 수 있어요.

Performance Schema (튜닝 전)

이 차트를 살펴보면, 쿼리의 통계 수집 단계인 statistics 항목이 실제 쿼리의 실행 단계인 executing보다는 훨씬 높은 비중을 차지하고 있다는 것을 확인할 수 있어요. 이 차트는 쿼리의 실행 계획 수집 단계에서 많은 자원을 소모하고 있다는 거죠.

Index Dive는 최소의 데이터 페이지만 검토하기 때문에, 일반적으로는 통계 수집 단계에서 이렇게 높은 CPU나 Disk 리소스를 소모하지 않아요. 통계 수집 단계가 튜닝의 대상이 되지도 않고요. 하지만 아래와 같이 WHERE 조건절이 사용되는 경우에는 상황이 달라질 수 있어요.

• 많은 개수의 엘리멘트가 IN (list)에 사용되는 경우
• IN (list) 조건 자체가 여러 번 사용되는 경우
• IN (list) 조건들이 사용할 수 있는 인덱스가 많은 경우

문제 쿼리 패턴

MySQL 서버를 포함한 모든 RDBMS에서는 쿼리가 실행되면, 해당 쿼리의 최적 실행 경로를 찾기 위해 사용 가능한 모든 처리 방법의 예상 비용을 계산하게 돼요. 여기에서 “사용 가능한 모든 처리 방법”이 중요한데요. 테이블의 인덱스가 많으면 많을수록 사용 가능한 방법이 많아지게 되면서 실행 계획 수립 과정이 복잡해져요. 예를 들어, 아래와 같은 테이블을 생각해볼게요.

CREATE TABLE articles (
  id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  category_id INT NOT NULL,
  ...
  PRIMARY KEY (id),
  INDEX ix_userid (user_id),
  INDEX ix_userid_categoryid (user_id, category_id),
  INDEX ix_userid_categoryid_fd1 (user_id, category_id, fd1),
  INDEX ix_userid_categoryid_fd2 (user_id, category_id, fd2),
  INDEX ix_userid_categoryid_fd3 (user_id, category_id, fd3)
);

articles 테이블에 아래와 같이 2개의 IN (list) 조건을 가진 쿼리를 실행하면,

SELECT *
FROM articles
WHERE user_id IN (1,2,3,4,5, ..., 200)
  AND category_id IN (1,2,3,4,5, ..., 200);

MySQL 서버는 IN (list)에 나열된 모든 값의 (user_id, category_id) 조합에 대해 통계 정보를 수집해요. 즉, (user_id, category_id) 조합 40000개(200 * 200)에 대해서 일치하는 레코드 건수가 몇 건이나 될지 예측하는 작업을 수행하는 거죠.

그런데 articles 테이블에는 이 쿼리를 실행하기 위해서 사용 가능한 인덱스가 5개나 돼요. 그래서 이런 작업을 5번이나 반복하게 되고요. 이 정도의 실행 계획 수립 과정은 실제 쿼리 실행보다는 통계 정보 수집에 더 많은 CPU나 Disk 자원이 소모돼요.

쿼리 튜닝

단순히 생각해보면 인덱스 개수를 줄이면 통계 정보 수집 시간을 단축시킬 수 있을 것 같지만, 그렇지 않아요. 다른 쿼리를 위해서 사용되는 인덱스라면 삭제할 수가 없고, 불필요한 인덱스를 삭제해서 최소화한다 하더라도 여전히 통계 수집이 많은 시간을 소모할 수도 있거든요.

물론 인덱스를 최소화(최적화)하는 전략은 SELECT 뿐만 아니라 INSERT나 UPDATE 그리고 DELETE 문장의 성능을 위해서라도 꼭 필요해요. 그리고 eq_range_index_dive_limit 시스템 변수도 Index Dive 튜닝에 도움이 될 수 있는데, 이는 마지막 부분에서 다시 살펴보도록 할게요.

만약 인덱스를 최적화한 이후에도 성능이 만족스럽지 않다면, MySQL 서버의 옵티마이저 힌트를 이용해서 실행 계획 수립 단계를 최소화할 수도 있어요. MySQL 8.0 버전에서 사용할 수 있는 힌트는 아래 3가지인데요.

1. FORCE INDEX (ix_userid_categoryid)
2. USE INDEX (ix_userid_categoryid)
3. SELECT /*+ INDEX (articles ix_userid_categoryid) */

1번과 2번 힌트는 MySQL 서버의 버전에 관계없이 사용할 수 있지만, 3번 힌트는 MySQL 5.7 이상의 버전에서만 사용할 수 있어요. 3개 힌트 모두 옵티마이저가 articles 테이블의 ix_userid_categoryid 인덱스를 사용하도록 유도한다는 공통점이 있어요.

차이점도 있는데요. 2번과 3번은 옵티마이저가 해당 인덱스의 Index Dive를 거쳐서 효율적인 경우에, 다른 인덱스를 무시하고 쿼리 실행 계획을 수립해요. 반면, 1번은 2번이나 3번과 비슷한 방식으로 작동하지만, 아래 몇 가지 조건만 충족되면 Index Dive 과정을 건너뛰게 돼요.

• 단일 테이블 쿼리인 경우
• 단일 인덱스에만 FORCE INDEX 힌트 사용된 경우
• Subquery가 없는 경우
• Fulltext 인덱스가 사용되지 않는 경우
• GROUP BY와 DISTINCT가 없는 경우
• ORDER BY 절이 없는 경우

적절한 방식으로 쿼리를 튜닝한다면, Performance schema의 그래프는 아래와 같이 statistics 단계의 CPU 및 Disk storage 자원 소모 비용이 executing 보다 훨씬 낮은 수준으로 떨어지는 것을 확인할 수 있어요.

Performance Schema (튜닝 후)

Performance schema의 내용은 단계별로 소모된 비용의 비율을 확인하기에는 좋지만, 실제 성능 개선 효과는 CPU 사용률 차트를 보는 것이 효율적이에요. 아래 그래프는 예시와 동일한 형태의 쿼리를 튜닝한 이후 CPU 변화를 보여주고 있어요.

튜닝 전 후, CPU 사용량 비교

이런 경우가 흔하게 발생하는 건 아니에요. IN (list) 비교 조건에서 list 의 엘리멘트가 많은 경우 그리고 IN (list) 비교 조건이 하나의 WHERE 조건절에 여러 번 사용된 쿼리가 statistics 단계의 시간 소모가 크다면, 우선 FORCE INDEX 힌트를 이용해서 최적의 인덱스를 사용하도록 강제화시키는 방법을 고려해보는 것이 좋아요. 물론 쿼리가 FORCE INDEX 힌트에 명시하는 인덱스를 항상 사용한다는 것을 먼저 확인하고 적용해야 해요.

추가로

이런 패턴(IN (list))의 쿼리 최적화에서, MySQL 서버의 옵티마이저 작동 방식에 영향을 미치는 2개 시스템 변수가 있어요.

• eq_range_index_dive_limit
• range_optimizer_max_mem_size

eq_range_index_dive_limit 는 동등비교 조건의 개수를 기준으로, 실행 계획 수립 단계의 Index Dive를 할지 단순히 통계 정보만 활용할지 여부를 결정해요. 예를 들어서 eq_range_index_dive_limit=200 으로 설정된 경우, MySQL 서버 옵티마이저는 동등 비교 회수가 200을 넘으면 Index Dive를 실행하지 않고 통계 정보만을 이용해서 실행 계획을 수립해요. 그래서 아래 쿼리의 경우, 동등 비교 회수는 40000번(200 * 200)이 필요하기 때문에 eq_range_index_dive_limit 시스템 변수에 설정된 200을 넘어서게 되어서, Index Dive 가 사용되지 않아요.

CREATE TABLE articles (
  id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  category_id INT NOT NULL,
  ...
  PRIMARY KEY (id),
  INDEX ix_userid_categoryid (user_id, category_id)
);
SELECT *
FROM articles
WHERE user_id IN (1,2,3,4,5, ..., 200)
  AND category_id IN (1,2,3,4,5, ..., 200);

그런데 eq_range_index_dive_limit 시스템 변수는 순수하게 동등 비교의 인덱스 활용 케이스에만 적용돼요. 그래서 아래와 같이 id>10000 또는 id<20000과 같이 동등 비교가 아닌 조건이 포함되면 Index Dive 사용을 피할 수 없어요.

SELECT *
FROM articles
WHERE user_id IN (1,2,3,4,5, ..., 200)
  AND category_id IN (1,2,3,4,5, ..., 200)
  AND id>10000;

위의 쿼리는 ix_userid_categoryid 인덱스를 사용하게 되는데, MySQL 서버의 Primary Key는 클러스터링 키이기 때문에 내부적으로 ix_userid_categoryid 인덱스는 (user_id, category_id, id) 컬럼 조합으로 인덱스가 구성돼요. 결국 실제 쿼리의 전체 검색 조건은 동등 조건이 아니고 아래와 같이 “크다 또는 작다”와 같은 범위 조건으로 해석이 되기 때문에, eq_range_index_dive_limit 시스템 변수는 아무런 영향을 못 미치게 돼요.

SELECT *
FROM articles
WHERE (user_id=1 AND category_id=1 AND id>10000)
   OR (user_id=1 AND category_id=2 AND id>10000)
   OR ...
   OR (user_id=2 AND category_id=1 AND id>10000)
   OR (user_id=2 AND category_id=2 AND id>10000)
   OR ...;

Index Dive 에 영향을 미치는 또 다른 시스템 변수로는 range_optimizer_max_mem_size 가 있어요. MySQL 서버는 IN (list) 조건이 사용되면, list 엘리멘트 값들을 메모리에 먼저 로드해서 중복 값을 제거하고 사용 가능한 형태로 확장(fan-out)을 해야 하는데요. 이때 메모리를 무한정 사용하도록 허용하지 않아요. range_optimizer_max_mem_size 시스템 변수는 MySQL 서버가 이런 용도로 사용 가능한 메모리 크기를 설정하는데, 만약 range_optimizer_max_mem_size 시스템 변수에 설정된 메모리 크기를 초과하면 MySQL 서버는 해당 실행 계획을 포기해요. 즉, ix_userid_categoryid 인덱스를 활용하는 실행 계획은 선택할 수 없고, 다른 최적의 실행 계획을 선택해요. 문제는 다른 최적의 실행 계획이 없다면, 풀 테이블 스캔을 하게 될 수도 있어요.

그런데 FORCE INDEX 힌트가 사용되면, range_optimizer_max_mem_size 시스템 변수에 설정된 메모리 공간을 넘어서는 경우에도 ix_userid_categoryid 인덱스를 활용한 실행 계획을 사용할 수 있어요.

결론적으로

쿼리에 IN (list) 패턴이 사용된다 하더라도, 아래와 같은 경우에는 크게 걱정하지 않아도 괜찮아요.

• WHERE 절에 하나의 IN (list)조건만 사용되는 경우
• IN (list) 에 사용된 엘리멘트의 개수가 많지 않은 경우
• IN (list) 조건이 인덱스를 사용하지 못하는 경우

하지만 IN (list)에 많은 엘리멘트가 사용되거나 하나의 쿼리에서 인덱스를 사용하는 IN (list)조건이 여러 번 사용된다면, 이 글의 내용을 참고해서 여러 방식으로 쿼리를 변경해가면서 최적의 성능을 찾는 것이 좋아요.

이번 이야기도 재미있게 읽으셨다면, 그리고 더 많은 고민을 함께 하고 싶으시다면! Real MySQL 오픈 챗 참여 또는 당근 마켓 개발자와 DBA로 지원해주세요.