인프런 커뮤니티 질문&답변

안녕하세요 영한님!!
항상 좋은 강의 만들어주셔서 감사합니다!

함수 기반 인덱스 생성에서 질문이 있어서 글을 남깁니다!

강의를 들으면서 함수 기반 인덱스를 생성하면 가상 컬럼이 없고, 인덱스만 만들어진다고 이해했습니다.
그래서 product_json 테이블에 만들어진 가상 컬럼과 idx_v_storage 인덱스를 drop한 다음 각각 두가지 방식으로 테스트를 해보았는데요

CREATE INDEX idx_func_storage
    ON product_json (( CAST(attributes->'$.storage' AS UNSIGNED)));

EXPLAIN SELECT * FROM product_json WHERE attributes->'$.storage' = 256; 

이렇게 할 경우엔idx_func_storage 인덱스를 잘 타는 걸로 나왔지만,

CREATE INDEX idx_func_storage ON product_json ((JSON_VALUE(attributes, '$.storage' RETURNING UNSIGNED)));

EXPLAIN SELECT * FROM product_json WHERE attributes->'$.storage' = 256; 

이 경우에는 FULL TABLE SCAN 이 되고, 인덱스도 NULL로 나왔습니다.

두 방식 모두 각각 idx_func_storage 인덱스는 잘 생성이 되었는데도 JSON_VALUE() 방식에서는 인덱스를 사용하지않았습니다

그래서 AI에게 물어보니
MySQL functional index는 WHERE절의 표현식이 인덱스 정의와 문자 수준으로 동일해야 한다.

이유는 가상 컬럼 방식에서는 MySQL이 내부적으로 expression rewrite 과 virtual column substitution를 더 적극 수행하지만 functional index는 표현식 exact match 요구가 훨씬 엄격하기 때문이다.

CREATE INDEX idx_func_storage ON product_json ((CAST(attributes->'$.storage' AS UNSIGNED))); 

이렇게 인덱스를 생성했다면

EXPLAIN SELECT *FROM product_json WHERE CAST(attributes->'$.storage' AS UNSIGNED) = 256;  

이렇게 WHERE 절을 작성해야 하고

CREATE INDEX idx_func_storage ON product_json ((JSON_VALUE(attributes, '$.storage' RETURNING UNSIGNED)));

이렇게 인덱스를 생성했다면

EXPLAIN SELECT * FROM product_json WHERE JSON_VALUE(attributes, '$.storage' RETURNING UNSIGNED) = 256

이렇게 WHERE 절을 작성해야 한다고 답변해주었는데요!
이렇게 각각 테스트 해보면, 인덱스를 잘 타는 것으로 나옵니다..!

또한
CAST()로 인덱스를 생성한 경우 WHERE절에 JSON_VALUE()를 사용한 쿼리는 Index를 사용하지않고, FULL TABLE SCAN을 했으며,

JSON_VALUE()로 인덱스를 생성한 경우 WHERE절에 JSON_VALUE()를 사용했을 경우에만 Index를 사용했습니다.

제 테스트에서는

• CAST() 기반 인덱스는

  • WHERE절 축약 표현식에서도 인덱스를 사용하였고,

     

  • WHERE절 JSON_VALUE()는 인덱스를 사용하지 않았습니다.

• JSON_VALUE() 기반 인덱스에서는 WHERE절에 동일한 JSON_VALUE() 표현식을 사용했을 경우에만 인덱스를 사용하는 것으로 보였습니다
  

이게 맞는 걸까요? 아니면 제가 잘못 확인한 걸까요?, 또한 functional index 매칭 규칙 실제 범위가 어디까지 인지? AI가 답변해준 동작 방식이 맞는 건지도 여쭤보고 싶습니다!

(제 MySQL버전이 8.0.41이네요,,)

영상을 다시 잘 보니, 2:18 분에 idx_func_storage 인덱스를 CAST() 구문으로 생성하시고, 2:25분에 확인하실 때, EXPLAIN의 결과에서 idx_v_storage인덱스가 나오긴 합니다!