Stored Function

 

1. MySQL Function

 

MySQL Function이란 부르면 특정 동작을 해주거나 결과값을 주는 것입니다. 보편적인 프로그래밍 언어면은 대부분 존재하는데 MySQL도 함수를 선언할 수 있습니다.

종류는 크게 세가지로 나뉩니다.

• Built-in Function
• User-Defined Function
• Stored Function

오늘은 이 함수들이 가질 수 있는 특성인 DETERMINSTIC (확정적) vs NOT DETERMINISTIC과 그에 따른 인덱싱에 대해 알아보고자 합니다.

2. DETERMINSTIC vs NOT DETERMINISTIC

MySQL의 함수는 위 두 특성 중 한 가지 특성을 가지게 됩니다. 두 특성의 차이는 무엇일까요?

• 동일 상태와 동일 입력으로 호출 → 동일한 결과 반환한다. -> DETERMINSTIC (확정적)
• 그렇지 않은 경우. 즉 입력이 동일하더라도 결과가 다를 수 있는 경우 → NOT DETERMINISTIC (비확정적)
• 함수 호출 때마다 테이블 데이터가 달라진다면? 그럼 출력이 달라질 수 있겠지만. 그렇다면 NOT DETERMINISTIC으로 봐야 할까요?

→ 입력이라 함은 함수의 인자뿐만 아니라 함수가 참조하는 데이터도 포함한다고 생각하면 됩니다. 따라서 DETERMINISTIC이라고 하더라도 참조하는 데이터가 다르면 다른 결과가 나올 수 있습니다.

 

위 두 개의 차이를 알아야 하는 이유는, 함수가 DETERMINISTIC이냐, NOT DETERMINISTIC이냐에 따라 호출 시간이 크게 차이나기 때문입니다.

 

호출 시간 차이

 

• 첫번째 쿼리: type const와 key PRIMARY는 매우 빠를 수 있습니다. 그래서 함수 호출 자체가 없었습니다.
• 두번째 쿼리: 테이블 풀 스캔을 해버렸습니다.

즉 NON-DETERMINISTIC 함수가 풀 스캔을 할 가능성이 있다는 것입니다. 쿼리의 실행 계획이 인덱스를 사용하지 못하고 full-table scan을 사용하는 것이 핵심 문제입니다.

NON-DETERMINISTIC의 경우 다음과 같은 최적화 이슈를 가집니다.

• 비확정이라는 점.
  • 매번 호출 시점마다 결과가 달라질 수 있습니다
  • 비교 기준 값이 상수가 아니고 변수입니다. 테이블 한건 읽을 때마다 결과값을 매번 새롭게 읽어서 비교해야 합니다.
  • 결론적으로 인덱스 최적화 불가능합니다.

이러한 함수가 뭐가 있을까요?

• RAND()
• UUID()
• SYSDATE()
• NOW()
• ...
• 위 함수를 사용한 표현식들.

즉 위 함수를 쓸 땐 full-scan할 가능성이 높아지므로 주의해야 합니다.

 

단 NOW()와 SYSDATE()는 주의해야 합니다.

둘 모두 NOT DETERMINISTIC이지만 NOW()는 하나의 문장에서는 DETERMINISTIC처럼 작동되지만 SYSDATE는 매 건마다 새롭게 계산되어 NOT DETERMINISTIC으로 동작합니다.

• 하지만 sysdate-is-now로 설정하면 동일하게 동작하도록 할 수 있습니다. 이렇게하면 sysdate도 now처럼 DETERMINISTIC으로 동작할 수 있습니다. 사실 sysdate 동작이 필요한 경우가 거의 없어 표준으로 적용하는 게 좋아보입니다.

주의사항

Stored Function을 만들 때 주의해야할 점은, 기본값이 NOT DETERMINISTIC이라는 점입니다. 따라서

꼭 기본 option을 명시적으로 설정해줘야 합니다.

뿐만 아니라 보안을 위해 DEFINER, SQL SECURITY INVOKER를 함께 정의해줘야 합니다.