[MySQL] DETERMINISTIC vs NOT DETEMINISTIC

🔹 의미

DETERMINISTIC = “같은 입력값에 대해서 항상 같은 결과를 반환한다”

CREATE FUNCTION double_value(x INT)
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN x * 2;
END;

NOT DETERMINISTIC = “같은 입력값이라도 상황(시간, 환경 등)에 따라 결과가 달라질 수 있다”

CREATE FUNCTION random_value()
RETURNS DOUBLE
NOT DETERMINISTIC
BEGIN
    RETURN RAND();
END;

두 함수가 동일한 동작을 해도 함수 호출 시에 호출 횟수 등에 차이가 발생할 수 있다.

→ 이전에 계산된 값을 재사용할 수 있다면 굳이 함수를 다시 호출할 필요가 없기 때문이다.

인덱스 사용 시 DETERMINISTIC를 사용

전제 개념 — “인덱스는 예측 가능한 값에만 사용 가능하다”

MySQL의 옵티마이저는 인덱스를 사용할 수 있는지 여부를 결정할 때,

해당 조건이 예측 가능한(fixed, deterministic) 값인지 판단한다.

즉,

• 쿼리 실행 전에 값이 확정되는 경우 → 인덱스 사용 가능
• 쿼리 실행 중에 값이 매번 달라질 수 있는 경우 → 인덱스 사용 불가

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    created_at DATETIME,
    INDEX idx_name (name),
    INDEX idx_created_at (created_at)
);

위와 같은 코드에서

EXPLAIN SELECT * 
FROM users 
WHERE LEFT(name, 3) = 'Kim';

이름 앞의 세 글자를 추출하는 함수라고 가정한다면, 동일한 입력값에 대해 출력을 보장하기 때문에 인덱스를 사용할 수 있다.

EXPLAIN SELECT * 
FROM users 
WHERE created_at < SYSDATE();

하지만, 호출 시점마다 달라지는 결과에 대해서는 인덱스를 사용할 수가 없기 때문에 쿼리 성능이 저하된다.

NOT DETERMINISTIC 최적화 이슈

• 매번 호출 시점마다 결과가 달라질 수 있다.
  • 비교 기준 값이 상수가 아니고 변수이다.
  • 매번 레코드를 읽은 후, WHERE절을 평가할 때마다 결과가 달라질 수 있다.
  • 인덱스에서 특정 값을 검색할 수 없다.
  • 인덱스 최적화가 불가능하다.

 

NOT DETERMINISTIC 예외

• NOW() vs SYSDATE()
  • 동일하게 현재 일자와 시간을 반환하는 함수이다.
  • 둘 모두 NOT DETERMINISTIC 함수이다.
  • 하지만 NOW() 함수는 DETERMINISTIC 처럼 작동한다.

SELECT NOW(), SYSDATE(), SLEEP(1)

ㄴ +---------------------+---------------------+
| NOW()               | SYSDATE()           |
+---------------------+---------------------+
| 2025-10-11 14:59:12 | 2025-10-11 14:59:12 |   
| 2025-10-11 14:59:12 | 2025-10-11 14:59:13 |  
| 2025-10-11 14:59:12 | 2025-10-11 14:59:14 |   
| 2025-10-11 14:59:12 | 2025-10-11 14:59:15 |     
| 2025-10-11 14:59:12 | 2025-10-11 14:59:16 |     
| 2025-10-11 14:59:12 | 2025-10-11 14:59:17 |  
| 2025-10-11 14:59:12 | 2025-10-11 14:59:18 | 
| 2025-10-11 14:59:12 | 2025-10-11 14:59:19 |     
| 2025-10-11 14:59:12 | 2025-10-11 14:59:20 |     
| 2025-10-11 14:59:12 | 2025-10-11 14:59:21 | 
+---------------------+---------------------+

를 실행하고 결과를 보자.

 

NOW() 함수는 하나의 Statement 내에서는 Statement의 시작 시점을 반환한다.

SYSDATE() 함수는 NOT DETERMINISTIC으로 매번 함수 호출 시점을 반환한다.

 

그렇기 때문에 NOW()를 쓴다면 인덱스를 사용할 수 있다.

 

설정에서 sysdate-is-now를 설정한다면 동일한 효과를 SYSDATE()에서도 볼 수 있다.