real my sql - Stored Function

존스노우·2024년 7월 28일

REAL MY SQL

목록 보기

5/5

MYSQL Function

function vs index optimizaion

DETERMINISTIC VS NOT DETERMINISTIC

MySQL Server의 stroed Function은 반드시 둘 중 하나의 속성을 가지게 됨
DETERMINISTIC
한글로 확정적이라 해석 됨
입력이 동일하면 언제 실행하든지 관계없이 동일 한 출력
함수의 호출 시점마다 참조하는 테이블 데이터가 달라지는 경우도 생각해 볼 수 있음
여기서 입력이라하면 함수의 인자뿐 아니라 함수가 참조하는 데이터도 같이 포함 한다고 이해하자
DETERMINISTIC 펑션이라 하더라도 참조하는 테이블이 데이터가 달라지면
입력이 달라지므로 출력이 달라짐
ex ) 오늘 가입한 사용자의 수를 가져오는 함수를 만들었을 때
stored function이 실행되는 도중에도 사용자의 가입은 꼐속 진행되기때문에
이 함수는 호출할때마다 결과가 달라질 수 도 있다
하지만 이 때 사용자 테이블의 레코드가 달라지는 것도 입력이 달라지는 것으로 해석하기 때문에
MYsql 서버에서 셀럭터를 포함해서 하나의 statement는 반드시 시작 시점의 스냅샷을 보도록 구현 됨
그래서 mysql 서버에서 실행되는 query 문장 하나는 동일한 데이터 상태를 보게 됨
그래서 하나의 문장내에서 stored function이 여러번 호출된다고 하더라도 테이블의 데이터는
해당 시점의 스냅샷을 보기 때문에 함수의 인자만 동일하다면 입력이 달라지지 않는 것으로 볼 수 있다.
지금까지 설명한 것과 반대로 입력이 동일하더라도 호출되는 시점에 따라서 결과가 달라질 수 있는
비확정적 이라하고 NOT DETERMINISTIC 키워드 로 정의

정리

확정적 함수

같은 입력을 넣으면 항상 같은 결과가 나옴

예를 들어, 숫자를 두 배로 만드는 함수는 항상 같은 결과

주의할 점은, '입력'이라고 할 때 함수에 직접 넣는 값뿐만 아니라 함수가 사용하는 테이블의 데이터도 포함된다

MySQL은 하나의 쿼리 문장이 실행될 때, 그 순간의 데이터 상태를 '사진 찍듯이' 저장해둡니다. 이걸 '스냅샷'이라고 해요

같은 쿼리 안에서 함수를 여러 번 호출해도, 항상 같은 데이터를 보게 됨

function 1 , function 2 함수 정의

SELECT * FROM tab WHERE id=func1();
SELECT * FROM tab WHERE id=func2();

SELECT @func1_called , @func2_called;

@func1_called = 3
@func2_called = 12


CREATE
DEFINER=root@ 'localhost'
FUNCTION func1() RETURNS INTEGER
DETERMINISTIC SQL SECURITY INVOKER
BEGIN
   SET @func1_called = INNULL(@func1_called,0) +1;
   RETURN 1;
 END ;;
 
 
 CREATE
DEFINER=root@ 'localhost'
FUNCTION func2() RETURNS INTEGER
NOT DETERMINISTIC SQL SECURITY INVOKER
BEGIN
   SET @func2_called = INNULL(@func2_called,0) +1;
   RETURN 1;
 END ;;

확정 함수와 비확정 함수 정의
변수의 값은 단순히 1씩 증가하는 함수
tab이라는 테이블에서 id 컬럼의 값이 fun1 과 fun2 함수의 결과 값이랑 동일한 레코더 찾는 쿼리 실행해보자
두 쿼리 실행 결과 값 중요 X 쿼리 실행 결과
func1_called 와 fun2_called 가 어떻게 바뀌었는지 중요
각 3 / 12번 호출된걸 알수 있다
호출횟수가 많다? 그만큼 많이 실행 됬다 -> 함수가 사용된 쿼리 처리시 실행ㅅ히간보다 cpu 또는 메모리 사용량이한 4배정도 더 많이 소요됬다.

정리

func1: 확정적 함수 (Deterministic) / func2: 비확정적 함수 (Not Deterministic)
두 함수는 거의 똑같아요. 호출될 때마다 각각의 카운터(@func1_called, @func2_called)를 1씩 증가시키고, 항상 1을 반환

SELECT FROM tab WHERE id=func1();
SELECT FROM tab WHERE id=func2();

각 몇번 호출 되었나? 3 / 12
func2(비확정적 함수)가 func1(확정적 함수)보다 4배나 더 많이 호출되었다는 거
func2를 사용한 쿼리가 더 많은 시간과 자원(CPU, 메모리 등)을 사용했다는 걸 의미
비확정적 함수(Not Deterministic)는 확정적 함수(Deterministic)보다 더 자주 호출될 수 있으며, 이는 쿼리 성능에 큰 영향을 미칠 수 있다