Stored function의 NOT DETERMINISTIC 옵션은 무엇이고 쿼리에 어떤 영향을 미칠까?

Procedure나 Function의 생성시에 사용되는 키워드 중에서 DETERMINISTIC 또는 NOT DETERMINISTIC이라는 키워드를 본 적이 있을 것이다.
여기서 DETERMINISTIC이 의미하는 것이 무엇일까 ?. 그리고 이 옵션으로 인해서 어떤 차이가 생기는 것일까 ?.


이 글에서는 DETERMINITIC하고 그러지 않은 함수의 차이를 알아보고자 한다.
우선, 아래와 같은 예제 함수를 하나 만들었다고 가정해보자.


CREATE FUNCTION 
  getKeyValue() RETURNS BIGINT
  NOT DETERMINISTIC
BEGIN
  return 99999999;
END;;


이 함수는 NOT DETERMINISTIC 으로 정의가 되었다는 것을 기억하고, 아래 쿼리를 한번 보자.


SELECT COUNT(*) FROM tb_test WHERE fdpk > getKeyValue();


tb_test 테이블에는 대략 1억건 정도의 레코드가 저장되어 있고, fdpk는 tb_test 테이블의 Primary key 컬럼이며,
tb_test 의 fdpk 값은 1~1억까지 값을 가지고 있다고 가정해보자.


이 쿼리는 최종적으로 값 1을 리턴하는 쿼리인데, 이 쿼리가 실행되는데, 시간이 얼마나 걸릴까 ?
직접 한번 테스트해보길 바라며, 정답은 테스트를 해보진 않아서 모르겠지만, 아마 기대 했던 1초 미만은 아닐 것이다.


왜 이런 결과가 나온 것일까 ?
이 질문의 정답은 이 게시물의 제목에서 말하듯이 "NOT DETERMINISTIC" 옵션 때문이다.
MySQL의 Stored procedure나 Function이 NOT DETERMINISTIC으로 정의되면, 
MySQL은 이 Stored routine의 결과값이 시시각각 달라진다고 가정하고, 
비교가 실행되는 레코드마다 이 Stored routine을 매번 새로 호출해서 비교를 실행하게 된다.
즉, 함수 호출의 결과값이 Cache되지 않고, 비교되는 레코드 건수만큼 함수 호출이 발생하는 것이다.


그래서 위 예제 쿼리의 경우, 이 쿼리문이 완료되기 위해서는 getKeyValue() 함수가 1억번 호출이 되어야 되며,
그와 동시에 fdpk 컬럼에 생성되어 있는 인덱스까지도 무용지물로 만드는 것이다.


만약, getKeyValue() 함수가 DETERMINISTIC으로 정의되었다면 우리가 기대하는 시간안에 처리를 완료할 것이다.
이 때에는 MySQL이 이 함수가 DETERMINISTIC 옵션으로 입력값이 동일하면 출력값은 항상 동일하다는 것을 인지하고
단 1번만 이 함수를 호출해서 결과값으로 Primary key를 검색하게 될 것이기 때문이다.


별것 아닌것으로 보이는 이 옵션으로 엄청난 성능 차이를 낼 수 있는 것이므로,
함수를 이와 같은 용도로 사용할 경우에는 이 옵션에 주의하자.