매초마다 다양한 기기에서 센서데이터를 수집하는 시스템을 만들고있는데
아무래도 데이터가 많다보니 모니터링이 어렵다.
특히 데이터가 수집될 때마다 갱신돼야하는 값들, 예를 들어 누계나 사용량 등을 다루는게 어려운데
이를 해결하기 위해 트리거를 사용하였다.
트리거
트리거란 CRUD 연산의 전후에 실행되는 내장된 작업(Stored Procedure)이다.
이를 통해 다음과 같은 짓들을 할 수 있다.
- 삽입되는 row로부터 특정 컬럼의 값을 바꿔 삽입한다.
- row가 삽입될 때 특정 테이블에 함께 삽입한다.
- row가 변경될 때 특정 테이블의 컬럼 값을 변경한다.
- row가 삭제될 때 특정 테이블의 특정 row를 함께 삭제한다.
유용한 기능이다.
구현도 어렵지 않다.
다음은 MY_TABLE에 After Trigger를 추가하는 스크립트이다.
DELIMITER $$ USE `TABLE`$$ CREATE DEFINER=`hooni`@`%` TRIGGER `MY_TABLE_AFTER_INSERT` AFTER INSERT ON /* Routine */ END$$ DELIMITER ;
DELIMITER는 스크립트 내부에서 종료 기호를 정의할 때 쓰인다.
위의 구문은 $$를 종료기호로 하여, 스크립트에서 세미콜론을 자유롭게 사용할 수 있도록 해준다.
그 이후 END$$를 통해 구문을 종료한 후, 다시 세미콜론으로 종료기호를 변경한다.
예제
자주 쓰일법한 예제를 몇 가지 들어보자.
예를 들어 센서 데이터로부터 요약 테이블에 데이터를 저장한다고 하자.
그러면 대략적인 절차는 이럴 것이다.
- 요약 테이블에 행 삽입
- 데이터 갱신
중요한건 이 과정이 트리거를 통해 매번 반복된다는 점이다.
이러한 까닭에 센서 데이터가 삽입될 때마다, 요약 테이블에 특정 컬럼값을 가진 행이 존재하지 않을 경우에만 데이터를 삽입한다.
조건부 삽입
insert into DB.MY_SUMMARY_TABLE (value, key)
select NEW.value, NEW.key
from dual
where not exists (select *
from DB.MY_SUMMARY_TABLE as summary
where summary.key = NEW.key
and summary.value = NEW.value;
위의 SQL문에서 주목할 점은 NEW와 dual, exist문이다.
NEW는 트리거를 발생시킨 행을 의미한다.
예를 들어 A라는 행이 삽입되어 트리거가 발생하고, 이 트리거를 통해 B, C라는 행이 삽입된다고 하자.
이 때는 NEW가 A를 지칭하게 된다.
또한 이는 스크립트를 기준으로 하기 때문에, 트리거를 통해 B를 삽입하더라도 여전히 NEW는 A를 지칭한다.
물론 이는 또다른 문제를 야기한다.
트리거 내부에서 발생한 CRUD를 트리거 내부에서 파악할 수 있는 방법은 없다.
예를 들어 위의 조건부 삽입이 동일한 키값으로 두 번 일어난다면, 보통은 첫 번째 쿼리만 실행되고 두번째 쿼리는 실행되지 않고 넘어갈 것 같이 보인다.
하지만 실제로는 두 쿼리 모두 실행되고, 그 결과 두 번째 쿼리는 실패한다.
이에 대한 주의가 필요하다.
dual은 select문에서 사용된 컬럼을 바탕으로 만든 더미 테이블이다.
이는 select 문이 완전하여 from 절이 필요하지 않은데 where 절을 사용하고 싶을 경우 사용한다.
결과적으로는 위의 쿼리에서 from dual이 없으면 실행은 안되지만, 의미상으로는 동일하다.
exists 문은 다음에 오는 statement가 실제로 존재하는지를 확인한다.
실제로 존재한다면 true, 아니라면 false를 반환함으로서
위의 쿼리에서는 MY_SUMMARY_TABLE에 방금 삽입된 행의 key와 value값을 가진 행이 없을 경우에 참을 반환하게 하기 위해 사용되었다.
데이터 갱신
위에서 삽입한 행을 바탕으로 센서 데이터의 요약 값을 갱신한다.
본문에서는 평균값을 이용하여 값을 갱신하였다.
SQL문은 다음과 같다.
update DB.MY_SUMMARY_TABLE as summary set data1 = (select if(data1 is NULL, 0, avg(data1)) from DB.MY_TABLE as table where table.value = NEW.value and table.key = NEW.key);
if문을 통해 만약 데이터가 존재하지 않는다면 0을, 존재한다면 평균값을 반환하여 data1을 갱신하도록 하였다.
문제는 이러한 갱신 구문이 여러개 존재할 때이다.
서브쿼리가 중복수행되기때문에 동일 테이블에서의 갱신 대상이 많아질수록 성능이 급격히 낮아진다.
이 경우 별도의 테이블을 두어 일종의 변수처럼 사용할 수 있다.
다음 SQL문은 TEMPORARY_TABLE을 통해 최적화를 진행하는 구문이다.
/* Insert the Subquery Result */ insert into DB.TEMPORARY_TABLE select * from DB.TABLE as table where table.value = NEW.value and table.key = NEW.key; /* Update */ update DB.MY_SUMMARY_TABLE as summary set data1 = (select if(data1 is NULL, 0, avg(data1)) from DB.TEMPORARY_TABLE), data2 = (select if(data2 is NULL, 0, avg(data2)) from DB.TEMPORARY_TABLE) where summary.value = NEW.value and summary.key = NEW.key; /* Delete All the Subquery Result */ SET SQL_SAFE_UPDATES = 0; delete from DB.TEMPORARY_TABLE; SET SQL_SAFE_UPDATES = 1;
Subquery를 별도의 테이블에 저장하고 재활용한 후 제거하는 코드이다.
Delete시 SQL_SAFE_UPDATES를 설정해주는데, 이는 delete 구문에 key를 포함한 where문이 없을 경우 에러를 출력하는 MYSQL 설정을 우회하기 위한 것이다.
이러한 과정들을 통해 주기적으로 수집되는 데이터로부터 요약 테이블을 자동으로 운용할 수 있다.