[MySQL] Stored Function 에서 많이 하는 실수 방지하기

 

  

MySQL Function

MySQL 함수는 MySQL 서버에 내장되어 있는 Built-in Function과 사용자가 직접 C/C++ 언어로 개발해서 플러그인 할 수 있는 User Defined Function 이 존재합니다.

그리고 Stroed Function 이 존재하는데요.

 

MySQL Function 종류

• Built-in Function
• User Defined Function(UDF)
• Stored Function

 

Stored Function

Stored Function는 기본적으로 DETERMINISTIC, NOT DETERMINISTIC 로 구분할 수 있습니다.

반드시 둘 중 하나의 속성만 가지게 됩니다.

 

DETERMINISTIC(확정적)

• 동일 상태와 동일 입력으로 호출
  • 동일한 결과를 반환
  • 여기서 입력은 함수의 인자 뿐만 아니라, 함수가 참조하는 데이터도 포함
• 이런 결과를 의도하는 함수를 정의할 때 DETERMINISTIC 키워드를 속성으로 입력해주면 된다.

 

NOT DETERMINISTIC(비확정적)

• 입력이 동일하더라도 호출 되는 시점에 따라서 결과가 달라짐

 

DETERMINISTIC vs NOT DETERMINISTIC

무슨 말 인지 이해가 잘 안가죠?!

DETERMINISTIC 와 NOT DETERMINISTIC의 예시를 보여드리겠습니다.

-- func1 정의
create
    definer=root@'localhost'
    function func1() return integer
    deterministic sql security invoker
begin
    set @func1_called = ifnull(@func1_called, 0)+1;
    return 1;
end;;

-- func2 정의
create
    definer=root@'localhost'
    function func2() return integer
    not deterministic sql security invoker
begin
    set @func2_called = ifnull(@func2_called, 0)+1;
    return 1;
end;;

 

func1과 func2 함수 모두 똑같지만, fun1은 deterministic fun2는 not deterministic 로 정의한 것을 확인 할 수 있습니다.

 

select * from tab where id=func1();
select * from tab where id=func2();

-- func1_called와 func2_called의 값이 어떻게 바꼈는지가 중요함
select @func1_called, @func2_called;

 

결과

func1_called	@func2_called
3	12

 

결과를 보면 func1과 func2 의 호출 횟수가 많이 차이 나는 것을 확인 할 수 있습니다.

func1_called 보다 func2_called가 4배정도 높은 것을 보니, 대략 4배정도의 리소스가 더 사용되었다는 것을 유추할 수 있습니다.

 

더 자세히 알아보기 위해서 실행 계획을 살펴 볼까요? 👀

 

func1 실행 계획(deterministic)

explain select * from tab where id=func1();

 

Note (Code 105): /* select#1 */ 
select '1' AS `id`, 'dd' AS `col` from `test`.`t` where true

 

위 쿼리는 primary key 인덱스를 const 타입으로 읽었다는 것을 확인 할 수 있습니다.

primary key 를 const 타입으로 접근하는 것은 테이블의 레코드를 1건만 읽었다는 것을 의미합니다.

이는 엄청 빠르게 처리되는 실행 계획이라는 것을 확인 할 수 있습니다!!

그리고 실행 계획 하단에 Note 에 명시된 query 에서도 where 절의 function1 함수 호출이 없어진 것을 알 수 있습니다!!

이는 최적화 되어, 함수 호출 부분 자체가 사라졌다는 것을 의미 합니다~! 🤗

 

func2 실행 계획(not deterministic)

explain select * from tab where id=func2();

 

Note (Code 105): /* select#1 */ 
select `test`.`t`.`id` AS `id`, `test`.`t`.`col` AS `col` from `test`.`t` where (`test`.`t`.`id` = `func2`())

 

위 쿼리의 실행 계획의 type 값이 ALL 이라는 것을 확인 할 수 있습니다.

이것은 쿼리가 full table scan으로 처리 되었다는 것을 의미합니다!

Note 를 보면 where 절의 func2 함수 호출 코드가 그대로 남아 있는 것도 확인 할 수 있습니다.

즉, not deterministic 함수의 호출 횟수가 많은 이유는 쿼리의 실행 계획에서 확인 한 것 처럼 인덱스를 사용하지 못하고 full table scan 으로 변경되었기 때문입니다.

 

NOT DETERMINISTIC 최적화 이슈

왜 이런 문제가 발생했을까요?! 🤔

not deterministic 함수는 입력이 동일해도 실행하는 시점에 따라서 다른 값을 반환할 수 있기 때문에 이 문제가 발생하는 것 입니다.

 

NOT DETERMINISTIC 함수의 결과는 비확정적임
(매번 호출 시점마다 결과값이 달라질 수 있다. → 변수!)

• 비교 기준 값이 상수가 아니고 변수
• 매번 레코드를 읽은 후, where 절을 평가 할 때마다 결과가 달라질 수 있음(변수이기 때문에 당연하게 바뀌겠지?!)
• 인덱스에서 특정 값을 검색 할 수 없음
• 인덱스 최적화 불가능

 

 

NOT DETERMINISTIC 효과

• NOT DETERMINISTIC Built-in Function
  • rand()
  • uuid()
  • sysdate()
  • now()
  • ...

 

따라서 NOT DETERMINISTIC Built-in Function 를 사용하게 되면, NOT DETERMINISTIC 최적화 이슈를 만나게 됩니다.

e.g) where col = (RAND()*1000)

 

NOT DETERMINISTIC 예외

now() 와 sysdate()

공통점

• 둘 다 현재 인자와 시간을 반환하는 함수
• 둘 다 NOT DETERMINISTIC 함수

 

차이점

• now() 함수는, DETERMINISTIC 처럼 동작
• now() 함수는, 하나의 Statement 내에서는 Statement의 시작 시점을 반환
• sysdate() 함수는, 매번 함수 호출 시점 반환

 

now()와 sysdate() 함수의 결과값을 한 번 살펴볼까요?! 👀

select now(), sysdate(), sleep(1)
from sample_table st limit 10;

 

결과

 

위 쿼리를 실행하면, now() 함수의 결과 값은 모두 동일한 것을 알 수 있습니다.

반면에 sysdate() 함수의 결과 값은 매번 다른 것을 확인 할 수 있습니다.

now() 함수와 다르게 sysdate()는 매번 함수 호출 시점의 결과 값을 반환하기 때문입니다.

따라서 아래와 같은 인사이트를 얻을 수 있습니다~!

 

-- 인덱스 사용하여 최적으로 쿼리 실행 가능
select * 
from sample_table st 
where st.created_at = now();

-- 인덱스 사용 불가해서 최적화 이슈발생 가능
select * 
from sample_table st 
where st.created_at = sysdate();

 

Stored Fucntion 주의 사항

Stored Fucntion을 생성할 때 기본적으로 DETERMINISTIC 옵션이 명시되어 있지 않으면, NOT DETERMINISTIC 으로 인식하게 됩니다.

따라서, Stored Fucntion을 생성할 때 기본 옵션을 꼭 명시하는 것을 권장 드립니다~!! 🤗

create
    definer=... -- 보안
    function function_name(...)
    returns ...
    deterministic  -- 옵션 명시(성능)
    sql security invoker -- 보안
begin
    ...

 

 

참고

• https://www.inflearn.com/course/real-mysql-part-1/dashboard

Real MySQL 시즌 1 - Part 1 강의 | 이성욱 - 인프런