[이렇게 사용하세요!] MySQL 8.0, 개발자를 위한 신규 기능 살펴보기! #3 Indexes
안녕하세요, 네이버 클라우드 플랫폼입니다.
지난 포스팅에서는 MySQL 신규 버전의 새로운 기능, InnoDB와 SQL DML을 다루었습니다.
MySQL 8.0, 개발자를 위한 신규 기능 살펴보기! #1 InnoDB
MySQL 8.0, 개발자를 위한 신규 기능 살펴보기! #2 SQL DML
이번 장에서는 MySQL 8.0부터 새롭게 지원되는 ‘3가지 형태의 인덱스’에 대해 살펴보겠습니다.
Indexes
1. Descending Indexes
2. Invisible Indexes
3. Functional Indexes
용어 정의를 위해 그림을 먼저 간단히 보시면,
인덱스의 leaf node(리프 노드)에 데이터가 정렬되는 순서를 기준으로 Ascending / Descending Index로 구분하고,
이를 스캔하는 방향을 기준으로 Forward / Backward Index Scan으로 구분합니다.
1. Descending Indexes
먼저 살펴볼 Descending 인덱스는 단어 그대로 역순으로 정렬된 인덱스를 의미합니다.
MySQL 5.7 이하에서는 문법만 지원하는 형태였기 때문에 Descending 인덱스를 생성하고자 하는 경우,
오류 없이 생성은 가능하나 역순으로 정렬되지 않은, Ascending 인덱스가 생성되었습니다.
이 경우 내부적으로는 Backward Index Scan을 하여 Descending 인덱스의 요건을 만족시킬 수도 있지만 약간의 성능 저하를 감수해야 했는데요
다음에서 간단한 예제를 통해 확인해보도록 하겠습니다.
Rentals 테이블의 where 조건에서 language=’Italian’으로 필터링 후, rental_datetime을 역순으로 정렬한 데이터를 출력하는 쿼리입니다.
위 쿼리를 수행하기 위해 2가지 인덱스를 생성한 뒤 각각의 인덱스가 사용되었을 때의 실행 계획과 수행 시간을 확인해 보겠습니다.
먼저 첫 번째 인덱스를 사용하는 경우, Ascending 인덱스를 Backward 스캔해 역순으로 정렬된 결과를 출력하는 계획을 생성했고 수행 시간은 175ms 걸렸습니다.
두 번째 인덱스의 경우 retal_datetime이 이미 역순으로 정렬되어 있기 때문에 7ms 만에 동일한 결과를 출력할 수 있게 되었습니다.
만약 MySQL 5.7에서 동일한 테스트를 수행했다면, Ix2도 ix1과 동일한 정렬 순서로 생성되기 때문에 실행 계획과 수행 시간은 ix1을 사용하는 경우와 차이가 없었을 것입니다.
2. Invisible Indexes
다음으로 살펴볼 새로운 형태의 인덱스는 Invisible 인덱스입니다.
MySQL 8.0 버전부터 인덱스 정의의 일부로 VISIBLE 혹은 INVISIBLE 키워드를 사용해 가시성을 제어할 수 있게 되었습니다.
(옵티마이저에게 인덱스의 존재 유무를 알려줄지 말지 결정하기 위한 기능이라고 이해해도 좋을 것 같습니다. ^^)
기본적으로 생성되는 모든 인덱스는 VISIBLE 한 속성을 가지고 있는데요, INVISIBLE 한 상태라고 하더라도 삭제된 것은 아니기 때문에 VISIBLE 인덱스와 완전히 동일하게 데이터 변경에 따른 유지 관리가 이루어집니다.
INVISIBLE로 속성을 변경했는데, 필요한 인덱스였다면 어떤 일이 생길까요?
만약 힌트에 사용된 인덱스가 INVISIBLE 하게 속성 변경이 변경된 경우, 옵티마이저 입장에서는 (사용할 수) 없는 인덱스를 사용해야 하는 상황이 되기 때문에 오류를 발생시키게 됩니다.
다음에서 간단한 예제를 보겠습니다.
위 쿼리에서 원하는 정렬 순서에 맞게 ix3을 생성하면 21ms 만에 원하는 결과를 출력할 수 있습니다.
이 인덱스를 옵티마이저가 참조하지 못하도록 INVISIBLE 한 속성으로 변경하면, 옵티마이저는 사용할 수 있는 인덱스가 없다고 인지하기 때문에 Full Table Scan 후 Filesort로 정렬을 위한 추가적인 동작을 해야 합니다.
그래서 인덱스를 사용할 때 보다 수행 시간이 약 25배 이상 더 걸렸습니다.
이렇게 인덱스 속성을 변경함으로써 쿼리의 성능 하락이 곧바로 인지되었다면, 다시 VISIBLE로 속성을 변경해 빠른 복구가 가능합니다.
위 예제와 같이 INVISIBLE 속성 변경은 성능 혹은 가용량 이슈 등으로 불필요한 인덱스를 삭제해야 하는 경우 서비스에 미치는 영향을 확인하고자 할 때 유용하게 사용할 수 있습니다.
인덱스를 삭제(DROP) 한 뒤 필요한 인덱스였다는 것이 인지되고 나면 재생성(CREATE)을 해야만 복구가 가능하기 때문에 테이블 사이즈가 클수록 오랜 시간이 걸려 서비스에 영향도가 높아지는 결과로 이어질 수 있기 때문입니다.
3. Functional Indexes
마지막으로 열 또는 열의 prefix 값이 아닌 “표현식 값을 인덱싱”하는 기능이 새롭게 지원되었습니다.
Functional key를 사용해 테이블에 직접 저장되지 않은 값을 인덱싱 하는 기능인데요, 이해를 돕기 위해 바로 예제를 통해 살펴보겠습니다.
위 쿼리는 where 조건 좌변에 함수가 사용되었습니다.
미리 생성된 ix4를 충분히 잘 활용할 것이라고 기대할 수 있지만 실행 계획을 보면 ix4 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔 방식으로 스토리지 엔진 레벨에서 먼저 범위 제한 처리를 한 뒤,
이를 전달받아 MySQL 엔진에서 필터링을 거친다는 것을 알 수 있습니다.
MySQL 5.7 이하 버전에서는 where 절 좌변 컬럼이 인덱스를 적절히 잘 사용할 수 있도록 가공하지 않은 형태로 수정이 필요할 수 있는데요,
( WHERE rental_datetime >= ‘2006–01–01’ AND rental_datetime < ‘2007–01–01’ )
MySQL 8.0부터 함수 자체를 적용한 functional 인덱스 생성이 가능하기 때문에 ix5와 같은 형태의 인덱스를 생성해 사용할 수 있게 되었습니다.
글을 마무리하며…
지금까지 MySQL 신규 버전에서 지원 가능한 새로운 형태의 3가지 인덱스를 소개해 드렸습니다.
1. 성능 저하 없이 역순으로 정렬된 결과를 인덱싱하는 ‘Descending Indexes’
2. VISIBLE, INVISIBLE 키워드를 사용해 가시성을 제어하는 ‘Invisible Indexes’
3. 표현식 값을 인덱싱하는 ‘Functional Indexes’
위의 인덱스 모두 쿼리 특성에 맞게 적절히 사용될 수 있도록 사전에 충분한 검토는 반드시 필요하다는 점 꼭 기억하시면 좋겠습니다.
다음 포스팅에서는 MySQL 신규 기능 4번째, ‘SQL DDL’ 콘텐츠로 찾아뵙겠습니다.
감사합니다.
