DETERMINISTIC , NOT DETERMINISTIC

함수나 프로시저 같은 Object 생성시 사용할 수 있는 옵션입니다. 

default값은 NOT DETERMINISTIC입니다. 아무것도 입력하지 않으면 자동으로 NOT DETERMINISTIC이 설정됩니다.

 

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

 

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

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

 

테스트 테이블 생성(1억건)

--테스트 테이블 생성
mysql> create table test.test_dummy(a int, b varchar(20));

--dummy데이터 삽입 프로시저 생성 (1억건의 데이터 생성가능)
DELIMITER $$
CREATE PROCEDURE test.loopInsert()
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i 100000000 DO
		INSERT INTO test.test_dummy (a, b)
		VALUES (i, concat('test',i));
	SET i = i + 1;
	END WHILE;
END $$
DELIMITER $$


--프로시저 실행
mysql> call test.loopinsert();

1억건까지 너무 오래걸려서 약 270만건의 데이터만 삽입하고 테스트를 진행하겠습니다. 

(중간에 프로시저 실행 끊고  DB 재기동)

 

mysql> select count(*) from test_dummy; 
+----------+ 
| count(*) | 
+----------+ 
|  2702270 | 
+----------+ 
1 row in set (0.24 sec) 

 

 

함수 생성 (NOT DETERMINISTIC)

mysql>
DELIMITER ||
DROP FUNCTION IF EXISTS test.getkeyvalue||
CREATE FUNCTION 
  test.getKeyValue() RETURNS BIGINT
NOT DETERMINISTIC
BEGIN
  return 2700000;
END||
DELIMITER ||
Query OK, 0 rows affected (0.01 sec)

 

17.50초가 소요되었습니다.

--count(*) SQL 수행

mysql> select count(*) from test.test_dummy where a > getkeyvalue();
+----------+
| count(*) |
+----------+
|     2270 |
+----------+
1 row in set (17.50 sec)

함수 생성 (DETERMINISTIC)

mysql>
DELIMITER ||
DROP FUNCTION IF EXISTS test.getkeyvalue||
CREATE FUNCTION 
  test.getKeyValue() RETURNS BIGINT
DETERMINISTIC
BEGIN
  return 2700000;
END||
DELIMITER ||

Query OK, 0 rows affected (0.01 sec)

 1.78초가 소요되었습니다.

mysql> select count(*) from test.test_dummy where a > getkeyvalue();
+----------+
| count(*) |
+----------+
|     2270 |
+----------+
1 row in set (1.78 sec)

 

위의 테스트의 경우 NOT DETERMINISTIC의 경우에는 count쿼리문이 완료되기 위해서는 getkeyvalue() 함수가 270만번 호출이 되어야합니다. 그와 동시에 a 컬럼에 생성되어 있는 인덱스(생성되어 있다면)까지도 무용지물로 만드는 것입니다.

 

하지만 DETERMINISTIC으로 생성시, MySQL에서 이 함수가 입력값이 동일하면 출력값은 항상 동일하다는 것을 인지하고 단 1번만 이 함수를 호출해서 결과값으로 a컬럼을 검색하게 됩니다.

 

 

 

 

 

 

출처 : intomysql.blogspot.com/2010/12/stored-function-not-deterministic.html

출처 : dev.mysql.com/doc/refman/8.0/en/create-procedure.html