MySQL 인덱스 컨디션 푸시다운(Index condition pushdown)에 대해서

silver 테이블의 인덱스가 ix_seq_createdat_updatedat(seq, created_at, updated_at)으로 생성되어 있을 때,

쿼리에서 인덱스를 사용할 수 있는 컬럼은 seq와 created_at까지이다.

 

SELECT *
FROM silver
WHERE seq = ?
AND created_at BETWEEN ? AND ?
AND updated_at < ?

​​

그런데 MySQL에서는 인덱스 컨디션 푸시다운(Index condition pushdown)이라는 기능으로 updated_at까지 인덱스를 사용할 수 있게끔 만들어준다.

위 쿼리에서 인덱스를 사용할 수 있는 컬럼은 seq, created_at이지만 updated_at 컬럼이 인덱스 구성 컬럼에 포함되어 있기 때문에 인덱스 컨디션 푸시다운 기능으로 updated_at까지 인덱스를 사용할 수 있다.

​

여기서 질문

그럼 위와 같은 쿼리 형태는 updated_at까지 인덱스를 만들어주어야 할까?

아니면 원칙대로 seq, created_at으로 만들어주어야 할까?

​

이를 판단하기에 앞서 크게 3가지 기준점으로 나누어 생각해봐야할 필요가 있다.

​

1) 커버링 인덱스로 충분한 이득을 취할 수 있는가

2) 인덱스 컨디션 푸시다운으로 updated_at은 얼마 만큼의 데이터 검색 범위를 줄여줄 것인가

3) updated_at 컬럼에 대한 데이터 변경 작업은 얼마나 발생하는가

​

첫 번째, 커버링 인덱스로 충분한 이득을 취할 수 있는가

커버링 인덱스는 인덱스 스캔만으로 원하는 쿼리 결과 값을 얻을 수 있을 때 가장 효율적인 방식이다.

만일 아래와 같은 쿼리 요건이었다면, 웬만한 경우가 아니고서는 커버링 인덱스로 처리되도록 인덱스를 구성했을 것이다.

updated_at 데이터를 찾기 위해 데이터 파일에 랜덤 액세스로 접근할 필요를 없애주니까 말이다. 이런 경우에는 사실 인덱스 컨디션 푸시다운을 고려할 필요없이 상황에 맞게 updated_at을 인덱스로 구성 컬럼에 포함시켜주면 된다.

​

SELECT updated_at
FROM silver
WHERE seq = ?
AND created_at BETWEEN ? AND ?
AND updated_at < ?

​

두 번째, 인덱스 컨디션 푸시다운으로 updated_at은 얼마 만큼의 데이터 검색 범위를 줄여줄 것인가

이건 아주 중요한 이야기이다.

데이터의 검색과 읽기를 제외한 필터링(여과) 작업은 스토리지 엔진이 아닌 MySQL 엔진에서 처리한다. 때문에 인덱스를 최대한 활용해서 MySQL 엔진에서 필터링으로 버려지는 데이터가 발생하지 않도록 해야 한다. 인덱싱으로 10,000건의 데이터를 검색해서 MySQL 엔진으로 보냈는데 필터링으로 9,999건의 데이터가 버려지면 이보다 쓸모없는 인덱스는 없을 것이다.

그럼 이런 문제를 위 쿼리에 적용해보면 어떨까. updated_at을 인덱스 구성 컬럼에 포함시켜서 인덱스 컨디션 푸시다운을 활성화시켰지만 10,000건중 9,999건이 버려지는 문제를 전혀 해결할 수 없다면? updated_at은 인덱스 컨디션 푸시다운의 효용성을 전혀 활용하지 못하는 것이다.

이 경우에는 인덱스 구성을 seq, created_at 까지만 설정하는 것이 좋다.

​

세 번째, updated_at 컬럼에 대한 데이터 변경 작업은 얼마나 발생하는가

updated_at에 대한 데이터 변경 작업이 많이 발생하면 인덱스 페이지를 변경하기 위한 랜덤 액세스 부하, 인덱스 페이지 분할(split)에 따른 조각화(fragmentation) 등의 문제가 발생할 수 있다. 컬럼명에서도 알 수 있지만 자주 변경되는 데이터라면 인덱스 구성 컬럼에 포함시키기 보다 제외시키는 편이 DB 전체 관점에서 봤을 때 더 나은 선택이 될 수 있다. DB는 항상 소탐대실보다는 대탐소실해야 하지 않을까.