MySQL - stored procedure 성능

동일한 입력 매개변수에 대해 항상 동일한 결과를 생성하는 경우 "DETERMINISTIC"인 것으로 간주되고 그렇지 않은 경우 "NOT DETERMINISTIC" 것으로 간주됩니다. 기본값은 NOT DETERMINISTIC입니다. 함수가 "DETERMINISTIC"임을 선언하려면 DETERMINISTIC을 명시적으로 지정해야 합니다.

 

MySQL은 DETERMINISTIC으로 선언된 루틴에 비결정적(NOT DETERMINISTIC) 결과를 생성하는 문이 없는지 확인하지 않습니다. 루틴을 잘못 선언하면 결과나 성능에 영향을 미칠 수 있습니다. 비결정적 루틴을 DETERMINISTIC으로 선언하면 옵티마이저가 잘못된 실행 계획을 선택하여 예기치 않은 결과가 발생할 수 있습니다. DETERMINISTIC 루틴을 NON DETERMINISTIC으로 선언하면 성능이 저하될 수 있습니다.

 

동일한 입력 매개 변수에 대해 항상 동일한 결과를 생성하는 경우, DETERMINISTIC로 간주되고 그렇지 않으면 NOT DETERMINISTIC으로 간주됩니다.

 

MySQL의 Stored procedure나 Function이 NOT DETERMINISTIC으로 정의되면 Mysql은 이 Stored routine의 결과값이 시시각각 달라진다고 가정하고, 비교가 실행되는 레코드마다 이 Stored routine을 매번 새로 호출해서 비교를 실행하게 됩니다.

즉, 함수 호출의 결과값이 Cache되지 않고, 비교되는 레코드 건수만큼 함수 호출을 발생하는 것입니다.

 

테스트 수행

- 테이블생성

- 테이블 데이터 생성(1백만건), 코드테이블 생성(함수를 수행해서 코드값을 return 할 수 있도록, 1000건)

- 코드별 균등하게 데이터 생성(case1: 코드 별로 1,000건 정도, case2: 코드종류 1건만)

- 펑션 생성(determistic, non-determistic)

- 수행 결과 비교

- 수행시간, profiling, 자원사용량?

 

코드테이블 생성 및 데이터 생성

-- 코드테이블 생성
create table code_tab(code int, code_name varchar(20));
alter table code_tab add constraint pk_code_tab primary key (code);

-- 코드데이터 생성
insert into code_tab(code, code_name)
WITH RECURSIVE cre_code(code, code_name) AS
(
 select 1 code, cast('code-1' as char(50))
 union all
 select code + 1, concat('code-', code) from cre_code where code < 1000
)
select * From cre_code;

transaction 테이블 생성 및 데이터 생성

-- transaction 테이블 생성
create table transaction_tab(tid int, tid_name varchar(20), code int);
alter table transaction_tab add constraint pk_transaction_tab primary key (tid);

-- transaction 데이터 생성
INSERT INTO TRANSACTION_TAB(TID, TID_NAME, CODE)
WITH RECURSIVE cre_tid(tid, tid_name, code) AS
(
 SELECT 1 tid, cast('transaction-1' AS CHAR(50)), 999
 UNION ALL
 SELECt /*+ SET_VAR(cte_max_recursion_depth = 2M) */ tid + 1, CONCAT('transaction-', tid), 999 FROM cre_tid where tid < 1000000
)
SELECT * FROM cre_tid;

 

stored function 생성 - DETERMISTIC, NON-DETERMISTIC

-- 코드값을 return 하는 stored function
DELIMITER $$
CREATE FUNCTION getcodename(v_code varchar(20)) RETURNS VARCHAR(20)
  DETERMINISTIC
BEGIN
declare v_codename varchar(20);
  select code_name into v_codename from code_tab where code = v_code ;
  return v_codename;
END $$
DELIMITER ;

수행시간 검증 

- Stored Function 호출, Scalar Subquery, Join 비교

-- function 호출
explain analyze
select *, getcodename(code) from transaction_tab 
where tid > 10000 and tid < 21000;
--------------------------------------------------------------------------------------
-> Filter: ((a.tid > 10000) and (a.tid < 21000))  (cost=101289.41 rows=110728) (actual time=4.361..475.477 rows=10999 loops=1)
     -> Table scan on A  (cost=101289.41 rows=996748) (actual time=0.022..422.806 rows=1000000 loops=1)
  ==> 수행시간 : 4.891 sec 
     
-- scalar subquery
explain analyze
select *, (select code_name from code_tab where code = A.code) from transaction_tab A 
where tid > 10000 and tid < 21000;
--------------------------------------------------------------------------------------
3	364	16:43:37	explain analyze
 select A.*, (select B.code_name from code_tab B where B.code = A.code) from transaction_tab A 
 where A.tid > 10000 and A.tid < 21000	1 row(s) returned	5.078 sec / 0.000 sec
 ==> 수행시간 : 5.078 sec 
 
-- join query
explain analyze
select *, B.code_name from transaction_tab A, code_tab B 
where A.tid > 10000 and A.tid < 21000
and   A.code = B.code;
--------------------------------------------------------------------------------------
-> Inner hash join (a.`code` = b.`code`)  (cost=1227191.04 rows=123006) (actual time=5.196..444.114 rows=10999 loops=1)
     -> Filter: ((a.tid > 10000) and (a.tid < 21000))  (cost=120.92 rows=11073) (actual time=4.628..442.106 rows=10999 loops=1)
        ...
==> 수행시간 : 0.469 sec

DETERMISTIC Stored Function 호출과 Scalar Subquery 그리고 JOIN 쿼리 비교 시

DETERMISTIC Stored Function 호출과 Scalar Subquery의 소요시간과 실행계획은 동일하였고 미세하게 Strored Function 수행 시간이 빨랐다. 그러나, JOIN 쿼리가 상대적으로 더 좋은 수행 속도를 보여주었다.(HASH JOIN을 수행하기 때문)

JOIN Query를 사용할 수 있으면 최대한 JOIN 쿼리를 사용하도록 한다.

 

code_table의 code 컬럼에 인덱스 생성 시(또는 Primary key 지정 시) 3개의 수행속도는 거의 비슷했다.

 

데이터와 테스트 방법에 따라 결과가 상이할 수 있으니 실제 적용 시에는 더 많은 Case로 테스트 해보는 것이 좋겠다.

 

NON-DETERMISTIC Function은 대부분의 경우 성능이 떨어지기 때문에 테스트하지 않는다.