mariadb CONNECT BY, 시간계산, 프로시저 만들기 요약.

개요 

오픈소스 기반 프로젝트를 수행하면서 MARIADB라는 녀석을 접하게 되었습니다. 기존의 ORACLE환경과는 다르기에 프로시저를 짜는데 애를 먹는 바람에 정리를 하려 합니다. 

그리고 프로그램 설정의 오류 때문인지 LOOP 문을 돌릴 경우 발생되는 데이터가 초당 20건밖에 되지 않는 기이한 현상이 있었는데 이런 건 PGA 메모리가 부족하든 CPU를 프로그램에 할당한 것이 없든 여러 환경적 문제가 있는 것 같습니다. 하지만 이런 문제를 모두 정리하기엔 시간이 부족하기에 루프문을 최소화해서 처리하는 해법을 찾아보았습니다. 

1. CONNECT BY LEVEL 문을 통한 여러 행 생성하기 

마땅히 CONNECT BY 문이 없어 애를 먹는 과정에서 RECURSIVE 관계를 WITH문을 통해 만들 수 있는 것을 발견하였습니다. 

저는 임의의 행 데이터 집합을 가상의 형태로 만들어야 하는 프로시저를 만들어야 합니다. 따라서 기존 작성했던 쿼리와 비교하며 검색을 해보았습니다. 

ORACLE : 

SELECT * FROM 

   임의테이블 A

   , (SELECT LEVEL AS LV FROM DUAL CONNECT BY LEVEL <=10)  B

위와 같은 쿼리를 통해 단일 데이터를 10배 뻥튀기 하는 과정이 필요했습니다. 해당 쿼리는 아래와 같이 변경됩니다. 

MARIADB : 

WITH RECURSIVE a(n) AS (

  SELECT 1 

    UNION ALL 

   SELECT  n+1 

   FROM a

   WHERE n < 10  /*최대 레벨에 해당하는 수치 입력*/

)

SELECT  n, b.* 

FROM  

   a

  ,  임의테이블 b

재귀 호출에 대한 자세한 스펙은 아래 문서를 참조하시면 됩니다. 

MARIADB 재귀호출 <-- 클릭

간단하게 말하자면 WITH RECURSIVE 뒤편에 들어있는 a(n)

a <--테이블이름 (n) 컬럼이름의 형태로 활용할 수 있으며 해당 리커시브 내에서도 재귀를 사용할 수 있습니다. a(n)은 재귀명(컬럼명1,컬럼명2) 처럼 다중 행과 컬럼을 포함할 수 있는 구조가 됩니다. 

2. 시간에 대한 계산하기 

가. 시간 차 계산 

시간의 차이에 대한 계산은 TIMESTAMPDIFF 함수를 사용합니다. 

ORACLE : 

SELECT  TO_DATE('날자','YYYY-MM-DD') - SYSDATE FROM  DUAL; 

같은 함수를 이용하여 시간은 *24 분은 *24*60 초는 *24*60*60을 해서 구했던 것 과 다릅니다. MARIADB 쪽이 조금 더 명시성이 확보되어 좋다는 생각이 듭니다. 

MARIADB : 

SELECT  TIMESTAMPDIFF(SECOND, '시작일자', '종료일자') ; 

위와 같은 구문을 이용하면 두 날짜 차이의 초단위 차를 계산을 수행해줍니다. 

참고로 SECOND는 YEAR, MONTH, DAY, HOUR, MINUTE 등으로 변경하여 해당 단위에 대한 차를 구할 수 있습니다. 

나. 시간의 증감 계산 

TIMESTAMPADD 함수를 통해 시간에 대한 증가와 감소를 계산할 수 있습니다. 

사용문법은 이렇습니다. 

SELECT  TIMESTAMPADD (단위, 증감수치, '날짜') 

ex) SELECT  TIMESTAMPADD (SECOND, 60, '2020-01-01 20:11:25') 

단위에 들어갈 수 있는 수치는 위와 동일하게 

YEAR, MONTH, DAY, HOUR, MINUTE, SECOND 입니다. 

3. 프로시저 

저장 프로시저, 실행 프로시저를 만드는 방법은 오라클과 약간 다릅니다. 

가. 저장 프로시저 

ORACLE :

CREATE OR REPLACE PROCEDURE 프로시저명(변수1 IN 타입, ...)

IS 

커서, 변수 선언 

변수명 데이터타입; 

/*예:) DECLARE TMPVAL VARCHAR(10);  */

BEGIN 

수행내용 .. 

       /*변수에 값을 넣을 경우 */

       변수명 := 값; 

       /*루프유형1*/

       FOR 커서명 IN  1..9 

       LOOP  

           처리내용  ; 

       END LOOP; 

       /*루프유형2*/             

       FOR  커서2 IN (SELECT COL1,COL2 FROM 테이블  ...)

       LOOP  

           INSERT INTO 테이블3

           (커서2.COL1, 커서2.COL2); 

       END LOOP; 

END ; 

 

MARIADB : 

CREATE OR REPLACE PROCEDURE 프로시저명(변수1 IN 타입, ...)

BEGIN 

   DECLARE  변수명 타입; 

   /*예:) DECLARE TMPVAL VARCHAR(10);  */

   /*  위의 변수 선언이 기본 형식이나 임의 값을 사용할 수 있다. */

   /*예 ) SET @tmp = '';  등의 형태로 활용할 경우 변수의 선언이 필요 없음*/ 

   수행내용 .. 

     /*변수에 값을 넣는 경우 */

      SET 변수명 = 값; 

   /*루프유형1*/

       FOR 커서명 IN  1..9 

       DO

           처리내용  ; 

       END FOR; 

       /*루프유형2*/             

       FOR  커서2 IN (SELECT COL1,COL2 FROM 테이블  ...)

       DO

           INSERT INTO 테이블3

           (커서2.COL1, 커서2.COL2); 

       END FOR;     

END ; 

ORACLE과 큰 차이 없이 사용할 수 있다. 

LOOP와 DO의 차이 정도, 그리고 변수를 임의로 지정할 수 있는 부분, 그리고 변수에 값을 넣는 것이 ORACLE 의 변수 := 값  -> MYSQL SET 변수 = 값; 정도의 차이만 있을 뿐 기본적인 부분이 같다. 

LOOP문 : 

LOOP문에서는 간혹 ORACLE 에서 LOOP와 EXIT 를 활용하는 부분이 다르다. 

ORACLE : 

LOOP

   수행로직 ;

   EXIT WHEN 탈출조건

END LOOP; 

MARIADB  : 

REPEAT 

   수행로직 ;

    UNTIL 탈출조건 

END  REPEAT ; 

다. 임의 실행 프로시저 

보편적으로 저장 프로시저와 임의 수행을 위해 (개발, 테스트시) 사용하는 프로시저는 다음과 같은 차이가 있다. 

ORACLE : 

DECLARE  

    변수선언 

BEGIN 

    로직 

END; 

MARIADB : 

DELIMITER|

   BEGIN NOT ATOMIC

     변수선언

     로직수행 

   END| 

DELIMITER; 

이 부분 역시 문법 이외에 별 차이가 없다. 

ORACLE에서는 DECLARE  나 BEGIN 만으로 수행 프로시저를 즉시 실행 시킬 수 있는 것과 MARIADB에서는 DELIMITER라는 명령어로 수행하는 것 만 다르다. 다만 ORACLE 프로시저와 MARIADB 프로시저에서는 눈에 띄게 다른 점이 LOOP 문을 활용할 경우 초당 처리 건수의 치이가 난다. ORACLE에서는 프로시저에서 LOOP를 돌려 수천 건의 임의데이터를 생성할 수 있었던 반면 MARIADB는 초당 20건밖에 데이터가 생성되지 않았으며, 아직 원인 규명이 되지 않은 상태다. 

다. 프로시저 만들기 

임의의 기간에 해당하는 데이터를 자동 생성하는 프로시저를 만들 경우 샘플을 만들어보자면 아래와 같다. 

/*테이블 생성 */

CREATE TABLE TMPTBL(

  STIME timestamp

  , ETIME timestamp

  , VALUE int

); 

/*임의 값 생성  현재시간 + 60초 1 +60초 2*/

INSERT INTO TMPTBL

VALUES(NOW(), TIMESTAMPADD (SECOND, 60, NOW(), 1)

, (TIMESTAMPADD (SECOND, 60, NOW()), TIMESTAMPADD (SECOND, 60, TIMESTAMPADD (SECOND, 60, NOW())), 2); 

/*기간만큼 데이터를 늘리는 저장 프로시저 생성 * /

CREATE OR REPLACE PROCEDURE testProcedure(in prTime timestamp)  

BEGIN

  for rec IN  (SELECT  STIME, ETIME, VALUE FROM TMPTBL)

  do 

SET @itv = TIMESTAMPDIFF(SECOND, rec.STIME, rec.ETIME); /*기간차를 초로*/

INSERT INTO TMPTBL(STIME, ETIME, VALUE) 

WITH 

RECURSIVE  list(n) 

AS (

SELECT  1

UNION ALL

SELECT  n+1 FROM  list

WHERE n < @itv /*초만큼 LEVEL생성*/

)

SELECT  

 TIMESTAMPADD(SECOND, list.n, rec.STIME) 

, TIMESTAMPADD(SECOND, list.n, rec.ETIME)

, rec.VALUE*n 

FROM  list ; 

  END for; 

END;

; 

/*기간만큼 데이터를 늘리는 즉시 수행 프로시저 */

DELIMITER |

BEGIN NOT ATOMIC

for rec IN  (SELECT  STIME, ETIME, VALUE FROM TMPTBL)

  do 

SET @itv = TIMESTAMPDIFF(SECOND, rec.STIME, rec.ETIME); /*기간차를 초로*/

INSERT INTO TMPTBL(STIME, ETIME, VALUE) 

WITH 

RECURSIVE  list(n) 

AS (

SELECT  1

UNION ALL

SELECT  n+1 FROM  list

WHERE n < @itv /*초만큼 LEVEL생성*/

)

SELECT  

 TIMESTAMPADD(SECOND, list.n, rec.STIME) 

, TIMESTAMPADD(SECOND, list.n, rec.ETIME)

, rec.VALUE*n 

FROM  list  ; 

  END for; 

END|

DELIMITER ;  

수행결과 

이번은 MYSQL이나 MARIADB에서 CONNECTION BY 구문, 시간계산과 프로시저를 생성하는 방법 , 그리고 PLSQL을 이용한 임의 데이터 생성하는 방법등 여러가지를 한꺼번에 정리해보았습니다. 개인적으로는 연계되어 사용까지가 한쿨이라고 생각하기에 이런 정리 방식이 좋은 것 같네요. 

실무에 조금이라도 도움이 되었으면 좋겠네요. 이상입니다. 

기타 

mariadb 의 실행 프로시저 begin not atomic 프로시저 내에서 여러 테이블을 호출하고 사용할 경우 다수의 트랜잭션이 동시 처리되는 환경에서 dead lock 오류가 발생하는 것을 확인 하였습니다. 또한 동일 프로그램을 spring 환경에서 개별 SQL MAPPER로 분리할 경우 해당 문제가 해결되는 것도 확인하였습니다.