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를 검색하게 될 것이기 때문이다.


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

GIS 위치 기반 비교를 위한 유틸리티 함수

MySQL의 GIS Extension을 사용하거나,
아니면, 기본 숫자형의 타입을 이용하여 위치 정보를 관리하는 경우,
특정 GPS 좌표로부터 반경 몇Km 이내의 좌료 정보를 검색하는 경우가 많이 발생한다.
그러한 조작들을 위해서 몇 가지 유용한 MySQL Stored function을 만들어 보았다.
(각 함수의 DETERMINISTIC 옵션은 절대 빼면 안됨)






-- // ------------------------------------------------------------------------------
-- // 두 GPS 좌표간의 실제 거리를 Km 단위로 리턴해주는 함수 ------------------------
DELIMITER ;;
CREATE 
  DEFINER='svc_account'@'%'
FUNCTION GeoDistance(p_lat1 float, p_lon1 float, p_lat2 float, p_lon2 float) RETURNS float
  DETERMINISTIC NO SQL
  SQL SECURITY INVOKER
BEGIN
  /*!99999 Param 1 : position1(from)'s latitude(위도) */
  /*!99999 Param 2 : position1(from)'s longitude (경도)*/
  /*!99999 Param 3 : position2(to)'s latitude(위도) */
  /*!99999 Param 4 : position2(to)'s longitude(경도) */
  
  DECLARE v_theta float;
  DECLARE v_dist float;
           
  SET v_theta = p_lon1 - p_lon2;
  SET v_dist = SIN(p_lat1 * PI() / 180.0) * SIN(p_lat2 * PI() / 180.0) + 
          COS(p_lat1 * PI() / 180.0) * COS(p_lat2 * PI() / 180.0) * COS(v_theta * PI() / 180.0);

  SET v_dist = ACOS(v_dist);
  SET v_dist = v_dist / PI() * 180.0;
  SET v_dist = v_dist * 60 * 1.1515;
  SET v_dist = v_dist * 1.609344; /*!99999 Convert miles to Kilometers */
      
  RETURN v_dist;
END
;;
DELIMITER ;




-- // ------------------------------------------------------------------------------
-- // 사용 예제 --------------------------------------------------------------------
mysql>select GeoDistance(32.96970, -96.80322, 29.46786, -98.53506) as distance_km;
+-----------------+
| distance_km     |
+-----------------+
| 422.73672485352 | 
+-----------------+

mysql>select GeoDistance(32.00000, -96.00000, 32.10000, -96.00000) as distance_km;
+-----------------+
| distance_km     |
+-----------------+
| 11.215754508972 | 
+-----------------+






-- // ------------------------------------------------------------------------------
-- // 특정 원점으로부터 반경 ? Km 사각 영역의 위도 경도 좌표를 리턴하는 함수  ------
DELIMITER ;;


CREATE 
  DEFINER='svc_account'@'%'
FUNCTION GetMinLongitude(p_lat double, p_lon double, p_radiuskilo int) RETURNS double
  DETERMINISTIC NO SQL
  SQL SECURITY INVOKER
BEGIN
  /*!99999 Param 1 : origin position's latitude(위도) */
  /*!99999 Param 2 : origin position's longitude(경도) */
  /*!99999 Param 3 : search radius kilo meter from origin position */
  
  RETURN p_lon - (p_radiuskilo / abs(cos(radians(p_lat))*111.2));
END
;;




CREATE
  DEFINER='svc_account'@'%'
FUNCTION GetMaxLongitude(p_lon double, p_lat double, p_radiuskilo int) RETURNS double
  DETERMINISTIC NO SQL
  SQL SECURITY INVOKER
BEGIN
  /*!99999 Param 1 : origin position's latitude(위도) */
  /*!99999 Param 2 : origin position's longitude(경도) */
  /*!99999 Param 3 : search radius kilo meter from origin position */
  
  RETURN p_lon + (p_radiuskilo / abs(cos(radians(p_lat))*111.2));
END
;;




CREATE
  DEFINER='svc_account'@'%'
FUNCTION GetMinLatitude(p_lon double, p_lat double, p_radiuskilo int) RETURNS double
  DETERMINISTIC NO SQL
  SQL SECURITY INVOKER
BEGIN
  /*!99999 Param 1 : origin position's latitude(위도) */
  /*!99999 Param 2 : origin position's longitude(경도) */
  /*!99999 Param 3 : search radius kilo meter from origin position */
  
  RETURN p_lat - (p_radiuskilo / 111.2);
END
;;


CREATE
  DEFINER='svc_account'@'%'
FUNCTION GetMaxLatitude(p_lon double, p_lat double, p_radiuskilo int) RETURNS double
  DETERMINISTIC NO SQL
  SQL SECURITY INVOKER
BEGIN
  /*!99999 Param 1 : origin position's latitude(위도) */
  /*!99999 Param 2 : origin position's longitude(경도) */
  /*!99999 Param 3 : search radius kilo meter from origin position */
  
  RETURN p_lat + (p_radiuskilo / 111.2);
END
;;


DELIMITER ;




-- // ------------------------------------------------------------------------------
-- // 사용 예제 --------------------------------------------------------------------
-- // shop_position.latitude와 shop_position.longitude 컬럼은 DOUBLE, FLOAT 타입임
SELECT *
FROM shop_position sp
WHERE
  sp.longitude between GetMinLongitude(137.2164, 34.9981, 3) and GetMaxLongitude(137.2164, 34.9981, 3)
  and sp.latitude between GetMinLatitude(137.2164, 34.9981, 3) and GetMaxLatitude(137.2164, 34.9981, 3)
  /* 하단의 조건은 사각 영역이 아니라, 원형으로 반경 검색일 경우 필요한 조건임 */
  and SQRT(POW(sp.longitude - 132.4625946, 2) + POW(sp.latitude - 34.3914636, 2)) < (3 /*Km*/ / 111.2 /*Km*/);






-- // ------------------------------------------------------------------------------
-- // 특정 원점으로부터 반경 ? Km 사각 영역의 Polygon 객체를 생성 리턴해주는 함수  -
DELIMITER ;;


CREATE 
  DEFINER='svc_account'@'%' 
FUNCTION GetRectBoundary(p_lat double, p_lon double, p_radiuskilo int) RETURNS Polygon
    DETERMINISTIC NO SQL
    SQL SECURITY INVOKER
BEGIN
  /*!99999 Param 1 : origin position's latitude(위도) */
  /*!99999 Param 2 : origin position's longitude(경도) */
  /*!99999 Param 3 : search radius kilo meter from origin position */
  
  DECLARE v_minLongitude double;
  DECLARE v_maxLongitude double;
  DECLARE v_minLatitude double;
  DECLARE v_maxLatitude double;
  DECLARE v_RectBoundary Polygon;
  
  SET v_minLongitude = p_lon - (p_radiuskilo / abs(cos(radians(p_lat))*111.2));
  SET v_maxLongitude = p_lon + (p_radiuskilo / abs(cos(radians(p_lat))*111.2));
  SET v_minLatitude = p_lat - (p_radiuskilo / 111.2);
  SET v_maxLatitude = p_lat + (p_radiuskilo / 111.2);
  
  SET v_RectBoundary = GeomFromText(concat('POLYGON(('
    ,v_minLongitude,' ',v_minLatitude,', '
    ,v_maxLongitude,' ',v_minLatitude,', '
    ,v_maxLongitude,' ',v_maxLatitude,', '
    ,v_minLongitude,' ',v_maxLatitude,', '
    ,v_minLongitude,' ',v_minLatitude,'))')) ;
    
  RETURN v_RectBoundary;  
END
;;


DELIMITER ;




-- // ------------------------------------------------------------------------------

-- // 사용 예제 --------------------------------------------------------------------

-- // 아래 예제는 원점(위도,경도) = (34.3914636, 132.4625946) 이며, 반경은 3Km 조회

-- // shop_position.location 컬럼은 Point 타입의 컬럼이어야 함
SELECT *
FROM shop_position sp
WHERE
  Contains( GetRectBoundary(34.3914636, 132.4625946, 3), sp.location )
  /* 하단의 조건은 사각 영역이 아니라, 원형으로 반경 검색일 경우 필요한 조건임 */
  and SQRT(POW(X(sp.location) - 132.4625946, 2) + POW(Y(sp.location) - 34.3914636, 2)) < (3 /*Km*/ / 111.2 /*Km*/);

MySQL의 Procedure나 Function의 한글 깨짐 방지

MySQL 에서 Stored procedure나 function 을 작성하는 경우,

가끔 Procedure나 Function body 부분에 한글이나 일본어와 같은 문자를 사용해야 하는 경우가 있다.

하지만, 가끔 이러한 Procedure나 Function을 실행 해보면 글자가 깨어지는 경우를 자주 볼 수 있다.

이러한 부분의 원인은 Procedure나 Function이 만들어 질 때부터 글자가 깨어져서 만들어 지는 경우가 상당히 많다.

가끔 그냥 놓치는 경우가 많지만,

MySQL client에서 Procedure나 Function 을 만들기 전에 여러가지 character set을 설정한 후

생성하면 이러한 문제점을 해결할 수 있다.

일반적으로 MySQL client를 실행하고, character set들을 확인해보면 아래와 같이 Latin1으로 설정된 경우가 상당히 많다.

root@localhost:test>show variables like '%char%';

+--------------------------+---------+

| Variable_name            | Value   |

+--------------------------+---------+

| character_set_client     | latin1  |

| character_set_connection | latin1  |

| character_set_database   | utf8    |

| character_set_filesystem | binary  |

| character_set_results    | latin1  |

| character_set_server     | utf8    |

| character_set_system     | utf8    |

+--------------------------+---------+

지금과 같은 상태에서 Procedure나 Function을 생성하면 Body내의 한글이나 일본어가 깨어져서 생성되게 된다.

이런 경우에는 SET NAMES utf8; 명령으로 character_set_connection, _client, _results 을 변경하고 Procedure나 Function 을 생성해 주면 된다.

그리고, 가끔 이렇게 정상적으로 생성되어도 글자가 깨어지는 경우에는 아래와 같이 리턴값의 Character set을 지정해주는 것도 방법이다.

create function getString() returns varchar(20) CHARACTER SET UTF8