Database ‐ ICP, Covering, and Skip Scans

ICP(Index Condition Pushdown)

• MySQL은 크게 2가지 레이어로 나뉜다.
  • MySQL 엔진(서버 레벨) : 쿼리를 파싱하고 실행 계획을 세우며, 최종적으로 데이터를 필터링한다.
  • 스토리지 엔진(InnoDB 등) : 디스크에서 실제 데이터를 읽어온다.
• 과거에는 두 레이어의 역할이 분리되어 있었다. 스토리지 엔진은 인덱스 조건에 맞는 행을 찾으라는 명령만 수행했고 나머지 조건 필터링은 전부 MySQL 엔진의 몫이었는데 이것이 엄청난 비효율을 낳았다.

ICP가 없는 경우

• MySQL 엔진에서 스토리지 엔진에 category_id = 11인 데이터를 요청한다.
• [스토리지 엔진] : 인덱스에서 category_id = 11인 첫 번째 인덱스 레코드를 찾는다.
• [스토리지 엔진] : 해당 레코드 PK를 이용해 클러스터드 인덱스를 재탐색한다. 그 결과를 MySQL 엔진에 반환한다.
• [MySQL 엔진] : 테이블 데이터를 넘겨받아 product_name LIKE '%상품_1000%'조건을 검사한다.
• [MySQL 엔진] : 조건에 맞지 않으면 버린다.

ICP가 없는 경우 문제점 1 - 레이어 간 대량 데이터 전달

• 레이어 간 대량 데이터 전달 : 스토리지 엔진에서 MySQL 엔진으로 대량 데이터를 통쨰로 넘긴다. 실제로 필요한 데이터보다 한참 많은 데이터가 레이어를 건너 이동한다.
• 버퍼 풀 오염 : 곧 버려질 테이블 페이지가 InnoDB 버퍼 풀에 적재되면서 정작 자주 쓰이는 다른 데이터가 버퍼 풀에서 밀려난다.

ICP가 없는 경우 문제점 2 - 불필요한 랜덤 I/O 증폭

• 불필요한 랜덤 I/O 증폭 : 최종 결과는 일부인데 스토리지 엔진은 조건을 만족하는 전체 데이터에 대해 재탐색을 수행한다. 어차피 버려질 데이터임에도 불구하고 무의미한 랜덤 I/O가 발생한다.

ICP 도입 후 아키텍처

• ICP는 MySQL 엔진이 처리하던 인덱스 조건을 스토리지 엔진으로 밀어넣는다는 뜻이다.
• 스토리지 엔진은 인덱스 페이지 안에 존재하는 조건을 활용해 테이블로 가기 전 필터링을 직접 수행할 수 있게 된다.

• MySQL 엔진에서 스토리지 엔진에 category_id = 11만 보내는 것이 아니라 인덱스로 평가할 수 있는 조건까지 함께 밀어넣는다.
• [스토리지 엔진] : 인덱스에서 category_id = 11인 인덱스 레코드를 찾는다.
• [스토리지 엔진] : 재탐색 전에 전달받은 조건을 먼저 평가한다.
• 조건에 맞는 경우에만 테이블 데이터를 읽는다.(이 때는 재탐색이 발생한다)
• 조건에 맞지 않는 경우라면 테이블 접근 없이 바로 다음 인덱스 레코드로 넘어간다.

ICP 발동 조건

• ICP는 range, ref, eq_ref 등의 인덱스 스캔 방식에서 동작한다.
• 세컨더리 인덱스에만 적용된다. 클러스터드 인덱스에는 이미 전체 데이터가 리프 노드에 있기 때문에 재탐색을 피한다는 개념이 성립하지 않는다.
• ICP는 현재 사용 중인 인덱스에 들어있는 컬럼 조건만 평가 가능하다.

커버링 인덱스 - 재탐색을 제거한다

• 커버링 인덱스란, 쿼리가 필요로 하는 모든 데이터를 인덱스만으로 제공할 수 있는 인덱스를 말한다.
• SELECT, WHERE, ORDER BY, GROUP BY에 사용되는 모든 컬럼이 인덱스에 포함되어 있으면 클러스터드 인덱스(테이블 데이터)에 접근할 필요가 없다.
• 따라서 인덱스만 읽고 결과를 반환하면 된다.

• Before/After 차이 정리 : SELECT * 혹은 필요한 컬럼만
• 커버링 인덱스가 적용되려면 쿼리에 사용되는 모든 컬럼이 인덱스에 포함되어야 한다.

확인 대상	쿼리 예제	인덱스 포함 여부
SELECT절의 모든 컬럼	product_id, category_id, product_status, created_at, price	O
WHERE절의 모든 컬럼	category_id , price , product_status	O
ORDER BY 컬럼	created_at	O
GROUP BY 컬럼	-	-

• idx_product_cate_status_created_price 인덱스 리프에는 키 4개(category_id , product_status , created_at , price )에 더해 PK가 자동 포함된다.(InnoDB 세컨더리 인덱스의 특성)
• Covering index lookup과 Index lookup은 다르다. Covering이 붙었다는 것은 이 노드가 인덱스만 읽고 끝낸다는 뜻이다. 즉, 테이블(클러스터드 인덱스)로의 점프가 단 한 번도 발생하지 않는다.
• ICP는 테이블 점프로 인한 재탐색을 줄이는 기술이고 커버링은 아낄 재탐색조차 없다.

필요한 컬럼만 조회하자

• SELECT * 를 습관적으로 쓰지 말고, 필요한 컬럼만 골라서 조회하자. 이것이 성능에 직결되는 이유는 두 가지다.
  • 전송 비용 감소 : 쓰지도 않을 컬럼까지 통째로 읽어서 실어 나르는 낭비가 사라진다.
  • 커버링 인덱스 가능성 : SELECT하는 컬럼이 적을수록 그 컬럼들이 인덱스 안에 모두 들어 있을 확률이 높아진다. 커버링 인덱스가 적용되어 테이블 점프 자체가 사라질 여지가 생기는 것이다.
• 다만 이 습관을 모든 쿼리에 강박적으로 적용할 필요는 없다. PK로 단 한 건을 조회하는 것처럼 이미 충분히 빠른 쿼리까지 굳이 커버링 인덱스에 맞춰 비틀 이유는 없다. 균형이 핵심인거다.
• 커버링 인덱스를 모든 쿼리에 적용할 수는 없다. SELECT 컬럼이 많거나 TEXT, BLOB 같은 대형 컬럼이 필요한 경우에는 현실적으로 인덱스에 모든 컬럼을 담을 수 없다. 커버링 인덱스가 만능이 아니다.
• 게다가 ICP 때와 같이 커버링 인덱스를 노리고 인덱스에 컬럼을 덧붙일수록 오히려 쓰기 비용도 함께 늘어난다.

인덱스 스킵 스캔

• 복합 인덱스 (A, B)는 왼쪽 컬럼 A부터 조건에 써야 제대로 탄다. A없이 B만 조건으로 쓰면 인덱스의 시작점을 잡을 수 없어서, 원칙대로라면 인덱스를 버리고 풀 스캔으로 떨어진다.
• 그런데 MySQL 8.0부터 상황에 따라 이걸 구제해주는 스킵 스캔이 동작한다.
• 핵심은 선행 컬럼 A의 카디널리티(값의 종류 수)이다. 만약 A가 성별처럼 값이 2~3개 종류뿐이라면 A의 묶음에서 B를 범위 스캔하고 A의 또 다른 묶음에서 B를 범위 스캔한다.
• 이렇게 선행 컬럼의 값을 하나씩 건너뛰며(skip) 각 묶음 안에서 후행 컬럼 범위 스캔을 돌리는 것을 스킵 스캔이라고 한다. A가 2종류라면 범위 스캔을 2번만 반복하면 되니, 인덱스를 못 타고 풀 테이블 스캔을 하는 것보다 훨씬 효율적이다.

• 복합 인덱스는 선행 컬럼 기준으로 먼저 정렬된 뒤 후행 컬럼으로 정렬된다.
• 여기서 핵심은 grade = 'A' 그룹만 떼어놓고 보면 score가 이미 정렬되어 있다는 점이다. 마찬가지로 grade = 'B' 그룹만 떼어놓고 봐도 score는 예쁘게 정렬되어 있다.
• 옵티마이저는 선행 컬럼 grade에 어떤 유니크한 값들이 존재하는지 파악한 뒤, 이 값들을 활용해 조건절을 분할하는 묘수를 부린다.
• 따라서 옵티마이저는 선행 컬럼의 고유한 값을 하나씩 대입해가며 후행 컬럼의 범위 스캔을 알아서 반복 실행한다.
  • WHERE grade = 'A' AND score BETWEEN 100 AND 110
  • WHERE grade = 'B' AND score BETWEEN 100 AND 110
• 이처럼 선행 컬럼에 대한 동등 조건이 내부적으로 보완되면서 각각의 쿼리는 인덱스 범위 스캔을 완벽하게 탈 수 있게 된다. 두 경로에서 찾아낸 결과를 하나로 합치면, 원했던 쿼리의 결과를 인덱스 탐색만으로 조기에 솎아낼 수 있다.

스킵 스캔은 커버링 인덱스에서만 사용된다

• MySQL의 스킵 스캔은 쿼리가 사용하는 컬럼이 모두 인덱스 안에 들어있는 경우, 즉 커버링 인덱스가 성립하는 경우에만 사용할 수 있다.

스킵 스캔 발동 조건

• 하나의 테이블만 참조하는 쿼리여야 한다.
• 조건식은 스킵 스캔이 분석할 수 있는 결합 조건 형태여야 한다.
• 쿼리는 인덱스 내부 컬럼만 참조해야 한다.
• GROUP BY 와 DISTINCT를 사용하지 않아야 한다
• optimizer_switch의 skip_scan 옵션이 켜져 있어야 한다. 기본값은 on이다.

스킵 스캔은 구제책이지 정답이 아니라는 점을 명심한다

• 스킵 스캔은 선행 컬럼이 비어 인덱스를 못 탈 뻔한 쿼리를 살려주는 기능으로 만능이 아니다.
• 건너뛰는 선행 컬럼의 카디널리티가 낮을수록(값 종류가 적을수록) 유리하다. 값이 수천, 수만 종류면 그만큼 범위 스캔을 반복해야 하니 이점이 사라진다.
• 쿼리가 참조하는 컬럼이 모두 인덱스 안에 들어있는 커버링 인덱스여야 한다.
• 그 쿼리가 자주 쓰인다면, 실제로 조건을 거는 컬럼이 앞에 오도록 인덱스를 따로 설계하는 것이 스킵 스캔에 기대는 것보다 훨씬 확실한 방법이다.
• 그래서 옵티마이저는 비용을 따져 상황에 따라서만 스킵 스캔을 고른다. 항상 나오는 것이 아니다.