MySQL 이벤트는 일정에 따라 실행되는 작업입니다. 예약된 이벤트라고 합니다. 이벤트를 만들면 주기적인 간격으로 실행할 SQL 문이 포함된 데이터베이스 객체를 만들고, 특정 시간에 시작합니다. Unix crontab (일명 " cron job " ) 또는 Windows 작업 스케줄러와 유사합니다.
Event 관리 - 생성/수정/삭제
1) Event Scheduler 활성화
이벤트를 실행하려면 Event Scheduler가 활성화되어 있어야 합니다. Event Scheduler가 활성화되어 있지 않다면 아래 명령어로 활성화시킵니다.
SET GLOBAL event_scheduler = ON;
2) Event 생성 권한
이벤트가 생성될 스키마에 대한 권한이 필요합니다. Event 생성 권한이 없다면 아래와 같이 EVENT 권한을 부여합니다. 이벤트는 schema 레벨의 권한이므로 테이블 레벨로 권한을 부여할 수 없습니다.
GRANT EVENT ON schemaname.* TO username@'%";
3) Event 생성
아래는 Event 생성 예시입니다.
CREATE EVENT myevent
ON SCHEDULE AT TIMESTAMP(CURRENT_DATE) + INTERVAL 1 HOUR
DO
UPDATE myschema.mytable SET mycol = mycol + 1;
myevent라는 이름의 이벤트를 생성합니다 .
이 이벤트는1시간에 한 번씩 mytable 테이블의 mycol 컬럼값을1씩 증가시키는 SQL 명령문을 실행합니다.
4) 이벤트 수정
이벤트를 수정하려면 'ALTER EVENT' 명령을 사용합니다.
이벤트의 여러 특성을 변경할 수 있습니다.
이 예제에서는 에서 실행되는 SQL 명령문을 myevent에서 모든 레코드를 삭제하고 하루에 한 번(01 시) 실행되도록 변경합니다.
ALTER EVENT myevent
ON SCHEDULE
AT TIMESTAMP(CURRENT_DATE) + INTERVAL 1 DAY + INTERVAL 1 HOUR
DO
TRUNCATE TABLE myschema.mytable;
auto_commit = 0 일 때, select 일 경우에도 shared_read lock이 발생하여 DDL 수행 시 대기상태로 빠진다.
- session A) SELECT 수행
SELECT * FROM select_lock;
lock 정보 조회 : 다른 session에서 session C) 의 SQL 수행으로 LOCK 정보를 확인해 보면, LOCK_TYPE이 SHARED_READ로 보여지며 이 테이블의 row에 대한 LOCK을 잡는다. 같은 session에서 조회할 경우 LOCK 정보가 보이지 않는다. 이와같이 autocommit 이 off 인 session에서 select 만 할 경우에도 LOCK이 발생하며 다른 session에서 DML은 가능하지만 DDL 수행 시 waiting LOCK이 발생한다.
아래의 명령어와 같이 DDL을 수행하면 수행되지 않고 대기 상태로 된다. 즉 다른 session의 LOCK이 해소되기를 기다린다.
ALTER TABLE select_lock CHANGE col1 colA varchar(16);
-- 대기상태
- session C) lock 조회
위의 두 SQL을 실행한 상태에서 다른 session에서 아래의 SQL을 수행한다.
SELECT l.object_type, l.object_schema, l.object_name, l.lock_type, l.lock_status,
t.processlist_id, t.processlist_user, t.processlist_host, t.processlist_time, t.processlist_info,
t.processlist_state, t.thread_id
FROM performance_schema.metadata_locks l
inner join performance_schema.threads t on t.thread_id = l.owner_thread_id
WHERE processlist_id <> connection_id();
아래의 결과와 같이 최초 autocommit이 OFF 인 session에서 LOCK을 발생시키고, 다른 session에서 수행한 DDL이 대기상태인 것을 알 수 있다. thread_id 또는 processlist_id 별로 LOCK_STATUS를 보면 동일한 id 중에 GRANTED 만 있는 processlist 또는 thread가 있는 것을 확인할 수 있다. 아래에서는 thread_id가 49 또는 processlist_id가 8 인 것을 볼 수 있다. 다른 process는 PENDING 이 있는 것을 볼 수 있다.
processlist를 조회해 보면 STATE이 "Waiting for table metadata lock" 인 것을 볼 수 있으며 이는 다른 프로세스의 LOCK이 해소되기를 기다리는 상태인 것이다. 또는 threads 에서 processlist_state 에서도 확인할 수 있다.
mysql> select * from information_schema.processlist;
+----+-----------------+-----------------+-------+---------+------+---------------------------------+------------------------------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+----+-----------------+-----------------+-------+---------+------+---------------------------------+------------------------------------------------------+
| 8 | root | localhost:63207 | deldb | Sleep | 272 | | NULL |
| 10 | root | localhost:63240 | deldb | Query | 242 | Waiting for table metadata lock | ALTER TABLE select_lock CHANGE cola col1 varchar(16) |
| 12 | root | localhost:63450 | deldb | Query | 0 | executing | select * from information_schema.processlist |
| 5 | event_scheduler | localhost | NULL | Daemon | 1943 | Waiting on empty queue | NULL |
+----+-----------------+-----------------+-------+---------+------+---------------------------------+------------------------------------------------------+
lock를 유발한 session이 아닌 다른 session에서 metalock이 발생한 상태에서 테이블에 대한 어떤 작업을 수행할 경우 작업이 수행되지 않고 PENDING 상태로 대기한다.
즉, 다른 session에서 select를 수행할 경우에도 DDL이 수행되지 않고 PENDING 상태로 대기한다.
단, lock을 유발한 session에서는 DML, DDL 작업이 가능하다.
- LOCK 해소
1) 첫번째 session에서 commit 또는 rollback을 수행한다.
2) 첫번째 session을 찾아 kill 시킨다.
3) DDL session을 kill 시킨다.
💎 explain 을 수행할 때
session A) autocommit = OFF일 때, EXPLAIN 수행 시
EXPLAIN FORMAT=tree
select * from select_lock;
session B)
SELECT l.object_type, l.object_schema, l.object_name, l.lock_type, l.lock_status,
t.processlist_id, t.processlist_user, t.processlist_host, t.processlist_time, t.processlist_info,
t.processlist_state, t.thread_id
FROM performance_schema.metadata_locks l
inner join performance_schema.threads t on t.thread_id = l.owner_thread_id
WHERE processlist_id <> connection_id();
수행결과 : 아래와 같이 SHARED_READ LOCK이 잡히는 것을 볼 수 있다. 즉, EXPLAIN을 수행하더라도 테이블에 SHARED_READ LOCK이 잡히는 것을 알 수 있다.
Blocking 트랜잭션을 식별할 때 쿼리 세션이 유휴 상태가 되면 processlist를 조회해 보면 Blocking 쿼리가 NULL로 보여집니다. 이 경우 다음 단계를 사용하여 Blocking 쿼리를 확인합니다.
Blocking 트랜잭션의 프로세스 ID를 식별합니다. sys.innodb_lock_waits 테이블에서 Blocking_pid 컬럼을 조회해보면 확인할 수 있습니다.
위에서 확인한 Blocking_pid 값으로 performance_schema.threads 테이블을 쿼리하여 Blocking 트랜잭션의 THREAD_ID를 확인합니다. 예를 들어, 1에서 확인한 blocking_pid가 6일 경우 다음과 같은 쿼리를 실행합니다.
SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID = 6;
위의 쿼리에서 확인한 THREAD_ID를 사용하여 performance_schema.events_statements_current 테이블을 쿼리하여 스레드가 실행한 마지막 쿼리를 확인합니다. 예를 들어 THREAD_ID가 28인 경우 다음과 같이 쿼리를 실행합니다.
SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_current
WHERE THREAD_ID = 28;
스레드에 의해 실행된 마지막 쿼리가 LOCK을 확인하는 데 충분하지 않은 경우 performance_schema.events_statements_history 테이블을 쿼리하여 스레드에 의해 실행된 마지막 10개의 SQL문을 확인항여 Lock을 발생시킨 SQL을 확인합니다.
SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_history
WHERE THREAD_ID = 28 ORDER BY EVENT_ID;
💎 Lock 유발 SQL 찾기
1. DML
- session#1 DML 수행
- session#2 session#1과 동일한 데이터에 대한 DML 수행
- session#1의 SQL 찾기
SELECT l.OBJECT_TYPE, l.OBJECT_SCHEMA, l.OBJECT_NAME, l.LOCK_TYPE, l.LOCK_STATUS,
t.THREAD_ID, PROCESSLIST_ID, PROCESSLIST_HOST, PROCESSLIST_USER, PROCESSLIST_INFO,
s.sql_text, s.statement_id
FROM performance_schema.metadata_locks l
INNER JOIN performance_schema.threads t ON t.THREAD_ID = l.OWNER_THREAD_ID
INNER JOIN performance_schema.events_statements_current s ON t.THREAD_ID = s.THREAD_ID
WHERE PROCESSLIST_ID <> CONNECTION_ID();
2. DDL
1. row lock
- insert, update, delete : transaction 을 종료하지 않았을 경우
동일한 입력 매개변수에 대해 항상 동일한 결과를 생성하는 경우 "DETERMINISTIC"인 것으로 간주되고 그렇지 않은 경우 "NOT DETERMINISTIC" 것으로 간주됩니다. 기본값은 NOT DETERMINISTIC입니다. 함수가 "DETERMINISTIC"임을 선언하려면 DETERMINISTIC을 명시적으로 지정해야 합니다.
MySQL은 DETERMINISTIC으로 선언된 루틴에 비결정적(NOT DETERMINISTIC) 결과를 생성하는 문이 없는지 확인하지 않습니다. 루틴을 잘못 선언하면 결과나 성능에 영향을 미칠 수 있습니다. 비결정적 루틴을 DETERMINISTIC으로 선언하면 옵티마이저가 잘못된 실행 계획을 선택하여 예기치 않은 결과가 발생할 수 있습니다. DETERMINISTIC 루틴을 NON DETERMINISTIC으로 선언하면 성능이 저하될 수 있습니다.
동일한 입력 매개 변수에 대해 항상 동일한 결과를 생성하는 경우, DETERMINISTIC로 간주되고 그렇지 않으면 NOT DETERMINISTIC으로 간주됩니다.
MySQL의 Stored procedure나 Function이 NOT DETERMINISTIC으로 정의되면 Mysql은 이 Stored routine의 결과값이 시시각각 달라진다고 가정하고, 비교가 실행되는 레코드마다 이 Stored routine을 매번 새로 호출해서 비교를 실행하게 됩니다.
즉, 함수 호출의 결과값이 Cache되지 않고, 비교되는 레코드 건수만큼 함수 호출을 발생하는 것입니다.
테스트 수행
- 테이블생성
- 테이블 데이터 생성(1백만건), 코드테이블 생성(함수를 수행해서 코드값을 return 할 수 있도록, 1000건)
- 코드별 균등하게 데이터 생성(case1: 코드 별로 1,000건 정도, case2: 코드종류 1건만)
- 펑션 생성(determistic, non-determistic)
- 수행 결과 비교
- 수행시간, profiling, 자원사용량?
코드테이블 생성 및 데이터 생성
-- 코드테이블 생성
create table code_tab(code int, code_name varchar(20));
alter table code_tab add constraint pk_code_tab primary key (code);
-- 코드데이터 생성
insert into code_tab(code, code_name)
WITH RECURSIVE cre_code(code, code_name) AS
(
select 1 code, cast('code-1' as char(50))
union all
select code + 1, concat('code-', code) from cre_code where code < 1000
)
select * From cre_code;
transaction 테이블 생성 및 데이터 생성
-- transaction 테이블 생성
create table transaction_tab(tid int, tid_name varchar(20), code int);
alter table transaction_tab add constraint pk_transaction_tab primary key (tid);
-- transaction 데이터 생성
INSERT INTO TRANSACTION_TAB(TID, TID_NAME, CODE)
WITH RECURSIVE cre_tid(tid, tid_name, code) AS
(
SELECT 1 tid, cast('transaction-1' AS CHAR(50)), 999
UNION ALL
SELECt /*+ SET_VAR(cte_max_recursion_depth = 2M) */ tid + 1, CONCAT('transaction-', tid), 999 FROM cre_tid where tid < 1000000
)
SELECT * FROM cre_tid;
stored function 생성 - DETERMISTIC, NON-DETERMISTIC
-- 코드값을 return 하는 stored function
DELIMITER $$
CREATE FUNCTION getcodename(v_code varchar(20)) RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
declare v_codename varchar(20);
select code_name into v_codename from code_tab where code = v_code ;
return v_codename;
END $$
DELIMITER ;
수행시간 검증
- Stored Function 호출, Scalar Subquery, Join 비교
-- function 호출
explain analyze
select *, getcodename(code) from transaction_tab
where tid > 10000 and tid < 21000;
--------------------------------------------------------------------------------------
-> Filter: ((a.tid > 10000) and (a.tid < 21000)) (cost=101289.41 rows=110728) (actual time=4.361..475.477 rows=10999 loops=1)
-> Table scan on A (cost=101289.41 rows=996748) (actual time=0.022..422.806 rows=1000000 loops=1)
==> 수행시간 : 4.891 sec
-- scalar subquery
explain analyze
select *, (select code_name from code_tab where code = A.code) from transaction_tab A
where tid > 10000 and tid < 21000;
--------------------------------------------------------------------------------------
3 364 16:43:37 explain analyze
select A.*, (select B.code_name from code_tab B where B.code = A.code) from transaction_tab A
where A.tid > 10000 and A.tid < 21000 1 row(s) returned 5.078 sec / 0.000 sec
==> 수행시간 : 5.078 sec
-- join query
explain analyze
select *, B.code_name from transaction_tab A, code_tab B
where A.tid > 10000 and A.tid < 21000
and A.code = B.code;
--------------------------------------------------------------------------------------
-> Inner hash join (a.`code` = b.`code`) (cost=1227191.04 rows=123006) (actual time=5.196..444.114 rows=10999 loops=1)
-> Filter: ((a.tid > 10000) and (a.tid < 21000)) (cost=120.92 rows=11073) (actual time=4.628..442.106 rows=10999 loops=1)
...
==> 수행시간 : 0.469 sec
DETERMISTIC Stored Function 호출과 Scalar Subquery 그리고 JOIN 쿼리 비교 시
DETERMISTIC Stored Function 호출과 Scalar Subquery의 소요시간과 실행계획은 동일하였고 미세하게 Strored Function 수행 시간이 빨랐다. 그러나, JOIN 쿼리가 상대적으로 더 좋은 수행 속도를 보여주었다.(HASH JOIN을 수행하기 때문)
JOIN Query를 사용할 수 있으면 최대한 JOIN 쿼리를 사용하도록 한다.
code_table의 code 컬럼에 인덱스 생성 시(또는 Primary key 지정 시) 3개의 수행속도는 거의 비슷했다.
데이터와 테스트 방법에 따라 결과가 상이할 수 있으니 실제 적용 시에는 더 많은 Case로 테스트 해보는 것이 좋겠다.
NON-DETERMISTIC Function은 대부분의 경우 성능이 떨어지기 때문에 테스트하지 않는다.
각 저장 프로그램에는 SQL 문으로 구성된 문장이 포함되어 있습니다. 이 명령문은 세미콜론(;) 문자로 구분된 여러 명령문으로 구성된 복합 명령문입니다.
예를 들어 다음 저장 프로시저에는 BEGIN ... END 블록에 select 문장이 있습니다.
CREATE PROCEDURE user_count()
BEGIN
SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
END;
mysql 클라이언트 프로그램을 사용하여 위의 세미콜론 문자가 포함된 저장 프로그램을 작성하면 아래와 같이 에러가 발생합니다. 기본적으로 mysql은 세미콜론을 명령문 구분 기호로 인식하기 때문입니다.
그러므로 mysql이 stored procedure 에서 세미콜론(;)을 인식하도록 임시로 구분 기호를 정의해야 합니다.
mysql> CREATE PROCEDURE user_count()
-> BEGIN
-> SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
ERROR 1064 (42000): You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version
for the right syntax to use near '' at line 3
mysql 구분 기호를 재정의하려면 delimiter 명령을 사용합니다. 다음 예에서는 user_count() 프로시저를 정의하기 위해 delimiter를 (//) 로 사용했습니다. 이를 통해 프로시저 본문에 사용된 구분 기호*;)를 자체에서 해석하지 않고 서버로 전달하여 stored procedure를 생성할 수 있습니다.
마지막 줄의 delimiter ; 는 다시 임시로 변경한 delimiter를 세미콜론(;)으로 원복하는 명령어입니다.
mysql> delimiter //
mysql> CREATE PROCEDURE user_count()
-> BEGIN
-> SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
PROCEDURE, FUNCTION, TRIGGER에서 세미콜론을 구분하기 위해 delimeter를 사용합니다.
WITH 절의 CTE가 자신을 참조하는 경우 WITH 절은 WITH RECURSIVE로 시작해야 합니다. (CTE가 자신을 참조하지 않으면 RECURSIVE가 허용되지만 필수는 아닙니다.) 재귀 CTE에 대한 RECURSIVE를 잊은 경우 다음 오류가 발생할 수 있습니다.
재귀 CTE 하위 쿼리에는 UNION ALL 또는 UNION [DISTINCT]로 구분되는 두 부분이 있습니다.
SELECT ... -- return initial row set
UNION ALL
SELECT ... -- return additional row sets
첫 번째 SELECT는 CTE에 대한 초기 행을 생성하며 CTE 이름을 참조하지 않습니다. 두 번째 SELECT는 추가 행을 생성하고 FROM 절에서 CTE 이름을 참조하여 재귀합니다. 이 부분이 새 행을 생성하지 않으면 재귀가 종료됩니다. 따라서 재귀적 CTE는 비재귀적 SELECT 부분과 재귀적 SELECT 부분으로 구성됩니다.각 SELECT 부분은 그 자체로 여러 SELECT 문의 합집합이 될 수 있습니다.
CTE 결과 열의 유형은 비재귀적 SELECT 부분의 열 유형에서만 유추되며 열은 모두 null을 허용합니다. 유형 결정의 경우 재귀 SELECT 부분은 무시됩니다.
비재귀 부분과 재귀 부분이 UNION DISTINCT로 구분되면 중복 행이 제거됩니다. 이는 무한 루프를 피하기 위해 전이적 폐쇄(transitive closures)를 수행하는 쿼리에 유용합니다.
재귀 부분의 각 반복은 이전 반복에서 생성된 행에서만 작동합니다. 재귀 부분에 여러 쿼리 블록이 있는 경우 각 쿼리 블록의 반복은 지정되지 않은 순서로 예약되며 각 쿼리 블록은 이전 반복이 종료된 이후 또는 다른 쿼리 블록에서 생성된 행에서 작동합니다.
이전에 표시된 재귀적 CTE 하위 쿼리에는 단일 행을 검색하여 초기 행 집합을 생성하는 비재귀적 부분이 있습니다.
SELECT 1
CTE 하위 쿼리에는 다음 재귀 부분도 있습니다.
SELECT n + 1 FROM cte WHERE n < 5
각 반복에서 해당 SELECT는 이전 행 집합의 n 값보다 하나 더 큰 새 값을 가진 행을 생성합니다. 첫 번째 반복은 초기 행 세트(1)에서 작동하고 1+1=2를 생성합니다. 두 번째 반복은 첫 번째 반복의 행 집합(2)에서 작동하고 2+1=3을 생성합니다. 이것은 n이 더 이상 5보다 작지 않을 때 발생하는 재귀가 끝날 때까지 계속됩니다.
CTE의 재귀 부분이 비재귀 부분보다 열에 대해 더 넓은 값을 생성하는 경우 데이터 잘림을 방지하기 위해 비재귀 부분의 열을 확장해야 할 수 있습니다.
WITH RECURSIVE cte AS
(
SELECT 1 AS n, 'abc' AS str
UNION ALL
SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;
비재귀적 SELECT가 열 너비를 결정하기 때문에 str 열 값은 모두 'abc'입니다. 결과적으로 재귀 SELECT에 의해 생성된 더 넓은 str 값이 잘립니다. 엄격한 SQL 모드에서 명령문은 오류를 생성합니다.
ERROR 1406 (22001): Data too long for column 'str' at row 1
이 문제를 해결하려면 명령문이 잘림이나 오류를 생성하지 않도록 비재귀 SELECT에서 CAST()를 사용하여 str 열을 더 넓게 만듭니다.
WITH RECURSIVE cte AS
(
SELECT 1 AS n, CAST('abc' AS CHAR(20)) AS str
UNION ALL
SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;
Now the statement produces this result, without truncation:
재귀 SELECT 부분은 다음 구문을 포함해서는 안 됩니다. MySQL 8.0.19 이전에는 재귀 CTE의 재귀 SELECT 부분도 LIMIT 절을 사용할 수 없었습니다. 이 제한은 MySQL 8.0.19에서 해제되었으며 이제 LIMIT가 선택적 OFFSET 절과 함께 이러한 경우에 지원됩니다. 결과 집합에 미치는 영향은 가장 바깥쪽 SELECT에서 LIMIT를 사용할 때와 동일하지만 재귀 SELECT와 함께 사용하면 요청된 행 수가 생성되는 즉시 행 생성을 중지하므로 더 효율적입니다.
이러한 제약 조건은 재귀 CTE의 비재귀 SELECT 부분에는 적용되지 않습니다. DISTINCT에 대한 금지는 UNION 회원에게만 적용됩니다. UNION DISTINCT는 허용됩니다.
Aggregate functions such as SUM()
Window functions
GROUP BY
ORDER BY
DISTINCT
재귀 SELECT 부분은 하위 쿼리가 아닌 FROM 절에서만 한 번만 CTE를 참조해야 합니다. CTE 이외의 테이블을 참조하고 CTE와 조인할 수 있습니다. 이와 같은 조인에서 사용되는 경우 CTE는 LEFT JOIN의 오른쪽에 있어서는 안 됩니다.
이러한 제약 조건은 ORDER BY, LIMIT(MySQL 8.0.18 및 이전 버전) 및 DISTINCT의 MySQL 관련 제외를 제외한 SQL 표준에서 비롯됩니다. 재귀 CTE의 경우 재귀 SELECT 부분에 대한 EXPLAIN 출력 행은 Extra 열에 Recursive를 표시합니다. EXPLAIN이 표시하는 예상 비용은 반복당 비용을 나타내며 총 비용과 상당히 다를 수 있습니다. 옵티마이저는 WHERE 절이 false가 되는 시점을 예측할 수 없기 때문에 반복 횟수를 예측할 수 없습니다.
CTE 실제 비용은 결과 집합 크기의 영향을 받을 수도 있습니다. 많은 행을 생성하는 CTE는 메모리 내 형식에서 디스크 형식으로 변환하기에 충분히 큰 내부 임시 테이블이 필요할 수 있으며 성능이 저하될 수 있습니다. 그럴경우 허용되는 메모리 내 임시 테이블 크기를 늘리면 성능이 향상될 수 있습니다.
공통 테이블 표현식 재귀 제한
재귀 CTE의 경우 재귀 SELECT 부분에 재귀를 종료하는 조건이 포함되어 있다는 것이 중요합니다. 런어웨이 재귀 CTE를 방지하기 위한 개발 기법으로 실행 시간을 제한하여 강제 종료할 수 있습니다.
cte_max_recursion_depth 시스템 변수는 CTE의 재귀 수준 수에 대한 제한을 적용합니다. 서버는 이 변수 값보다 더 많은 수준을 반복하는 모든 CTE의 실행을 종료합니다.
max_execution_time 시스템 변수는 현재 세션 내에서 실행되는 SELECT 문에 대한 실행 제한 시간을 적용합니다.
MAX_EXECUTION_TIME 옵티마이저 힌트는 나타나는 SELECT 문에 대해 쿼리당 실행 제한 시간을 적용합니다.
재귀 CTE가 재귀 실행 종료 조건 없이 실수로 작성되었다고 가정합니다.
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte
)
SELECT * FROM cte;
기본적으로 cte_max_recursion_depth 값은 1000이므로 CTE가 1000 수준을 초과하여 재귀하면 종료됩니다. 애플리케이션은 요구 사항에 맞게 세션 값을 변경할 수 있습니다.
SET SESSION cte_max_recursion_depth = 10; -- permit only shallow recursion
SET SESSION cte_max_recursion_depth = 1000000; -- permit deeper recursion
이후에 시작되는 모든 세션에 영향을 미치도록 전역 cte_max_recursion_depth 값을 설정할 수도 있습니다.
쿼리가 느리게 실행되어 반복되는 경우 또는 cte_max_recursion_depth 값을 매우 높게 설정해야 하는 상황에서 깊은 반복을 방지하는 또 다른 방법은 세션당 시간 제한을 설정하는 것입니다. 이렇게 하려면 CTE 문을 실행하기 전에 다음과 같은 문을 실행하십시오.
SET max_execution_time = 1000; -- impose one second timeout
또는 CTE 문 자체에 옵티마이저 힌트를 포함합니다.
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte
)
SELECT /*+ SET_VAR(cte_max_recursion_depth = 1M) */ * FROM cte;
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte
)
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM cte;
MySQL 8.0.19부터 재귀 쿼리 내에서 LIMIT를 사용하여 가장 바깥쪽 SELECT에 반환할 최대 행 수를 지정할 수도 있습니다. 예를 들면 다음과 같습니다.
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte LIMIT 10000
)
SELECT * FROM cte;
시간 제한을 설정하는 대신 또는 추가로 이 작업을 수행할 수 있습니다. 따라서 다음 CTE는 10,000개 행을 반환하거나 1초(1000밀리초) 동안 실행한 후(둘 중 먼저 발생하는 경우) 종료됩니다.
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte LIMIT 10000
)
SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM cte;
실행 시간 제한이 없는 재귀 쿼리가 무한 루프에 빠지면 KILL QUERY를 사용하여 다른 세션에서 종료할 수 있습니다. 세션 자체 내에서 쿼리를 실행하는 데 사용되는 클라이언트 프로그램은 쿼리를 종료하는 방법을 제공할 수 있습니다. 예를 들어, mysql에서 Ctrl+C를 입력하면 현재 명령문이 중단됩니다.
재귀 공통 테이블 표현식 예제
앞서 언급한 바와 같이 재귀 공통 테이블 표현식(CTE)은 계열 생성 및 계층적 또는 트리 구조 데이터 순회에 자주 사용됩니다. 이 섹션에서는 이러한 기술의 몇 가지 간단한 예를 보여줍니다.
피보나치 수열은 두 개의 숫자 0과 1(또는 1과 1)로 시작하며 그 이후의 각 숫자는 이전 두 숫자의 합입니다. 재귀 공통 테이블 표현식은 재귀 SELECT에 의해 생성된 각 행이 계열의 이전 두 숫자에 액세스할 수 있는 경우 피보나치 수열을 생성할 수 있습니다. 다음 CTE는 0과 1을 처음 두 숫자로 사용하여 10개의 숫자 계열을 생성합니다.
WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
(
SELECT 1, 0, 1
UNION ALL
SELECT n + 1, next_fib_n, fib_n + next_fib_n
FROM fibonacci WHERE n < 10
)
SELECT * FROM fibonacci;
mysql> SELECT date, SUM(price) AS sum_price
FROM sales
GROUP BY date
ORDER BY date;
+------------+-----------+
| date | sum_price |
+------------+-----------+
| 2017-01-03 | 300.00 |
| 2017-01-06 | 50.00 |
| 2017-01-08 | 180.00 |
| 2017-01-10 | 5.00 |
+------------+-----------+
그러나 이 결과에는 테이블에 포함된 날짜 범위에 표시되지 않은 날짜에 대한 "구멍"이 포함되어 있습니다. 범위의 모든 날짜를 나타내는 결과는 판매 데이터에 대한 LEFT JOIN과 조인된 해당 날짜 집합을 생성하는 재귀 CTE를 사용하여 생성할 수 있습니다.
날짜 범위 시리즈를 생성하는 CTE는 다음과 같습니다.
WITH RECURSIVE dates (date) AS
(
SELECT MIN(date) FROM sales
UNION ALL
SELECT date + INTERVAL 1 DAY FROM dates
WHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales)
)
SELECT * FROM dates;
판매 테이블에 대해 LEFT JOIN을 사용하여 CTE를 조인하면 범위의 각 날짜에 대한 행이 있는 판매 요약이 생성됩니다.
WITH RECURSIVE dates (date) AS
(
SELECT MIN(date) FROM sales
UNION ALL
SELECT date + INTERVAL 1 DAY FROM dates
WHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM sales)
)
SELECT dates.date, COALESCE(SUM(price), 0) AS sum_price
FROM dates LEFT JOIN sales ON dates.date = sales.date
GROUP BY dates.date
ORDER BY dates.date;
쿼리, 특히 재귀 SELECT의 각 행에 대해 MAX() 하위 쿼리가 실행되는 쿼리가 비효율적입니까? EXPLAIN은 MAX()를 포함하는 하위 쿼리가 한 번만 평가되고 결과가 캐시됨을 보여줍니다.
COALESCE()를 사용하면 판매 테이블에서 판매 데이터가 발생하지 않는 날에 sum_price 열에 NULL이 표시되는 것을 방지할 수 있습니다.
Hierarchical Data Traversal
재귀 공통 테이블 표현식은 계층 구조를 형성하는 데이터를 순회하는 데 유용합니다. 회사의 각 직원에 대해 직원 이름과 ID 번호, 직원 관리자의 ID를 표시하는 작은 데이터 세트를 생성하는 다음 명령문을 고려하십시오. 최상위 직원(CEO)의 관리자 ID는 NULL(관리자 없음)입니다.
CREATE TABLE employees (
id INT PRIMARY KEY NOT NULL,
name VARCHAR(100) NOT NULL,
manager_id INT NULL,
INDEX (manager_id),
FOREIGN KEY (manager_id) REFERENCES employees (id)
);
INSERT INTO employees VALUES
(333, "Yasmina", NULL), # Yasmina is the CEO (manager_id is NULL)
(198, "John", 333), # John has ID 198 and reports to 333 (Yasmina)
(692, "Tarek", 333),
(29, "Pedro", 198),
(4610, "Sarah", 29),
(72, "Pierre", 29),
(123, "Adil", 692);
결과 데이터 세트는 다음과 같습니다.
mysql> SELECT * FROM employees ORDER BY id;
+------+---------+------------+
| id | name | manager_id |
+------+---------+------------+
| 29 | Pedro | 198 |
| 72 | Pierre | 29 |
| 123 | Adil | 692 |
| 198 | John | 333 |
| 333 | Yasmina | NULL |
| 692 | Tarek | 333 |
| 4610 | Sarah | 29 |
+------+---------+------------+
각 직원의 관리 체인(즉, CEO에서 직원으로의 경로)이 포함된 조직도를 생성하려면 재귀 CTE를 사용합니다.
WITH RECURSIVE employee_paths (id, name, path) AS
(
SELECT id, name, CAST(id AS CHAR(200))
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, CONCAT(ep.path, ',', e.id)
FROM employee_paths AS ep JOIN employees AS e
ON ep.id = e.manager_id
)
SELECT * FROM employee_paths ORDER BY path;
CTE는 다음 출력을 생성합니다.
+------+---------+-----------------+
| id | name | path |
+------+---------+-----------------+
| 333 | Yasmina | 333 |
| 198 | John | 333,198 |
| 29 | Pedro | 333,198,29 |
| 4610 | Sarah | 333,198,29,4610 |
| 72 | Pierre | 333,198,29,72 |
| 692 | Tarek | 333,692 |
| 123 | Adil | 333,692,123 |
+------+---------+-----------------+
CTE 작동 방식:
비재귀적 SELECT는 CEO에 대한 행(NULL 관리자 ID가 있는 행)을 생성합니다.
재귀 SELECT에 의해 생성된 더 긴 경로 값을 위한 공간이 있는지 확인하기 위해 경로 열이 CHAR(200)로 확장됩니다.
재귀 SELECT에 의해 생성된 각 행은 이전 행에서 생성된 직원에게 직접 보고하는 모든 직원을 찾습니다. 이러한 각 직원에 대해 행에는 직원 ID와 이름, 직원 관리 체인이 포함됩니다. 체인은 관리자의 체인이며 끝에 직원 ID가 추가됩니다.
재귀는 직원에게 보고하는 다른 직원이 없을 때 종료됩니다.
특정 직원의 경로를 찾으려면 최상위 SELECT에 WHERE 절을 추가합니다. 예를 들어 Tarek과 Sarah에 대한 결과를 표시하려면 해당 SELECT를 다음과 같이 수정합니다.
mysql> WITH RECURSIVE ...
...
SELECT * FROM employees_extended
WHERE id IN (692, 4610)
ORDER BY path;
+------+-------+-----------------+
| id | name | path |
+------+-------+-----------------+
| 4610 | Sarah | 333,198,29,4610 |
| 692 | Tarek | 333,692 |
+------+-------+-----------------+
유사한 구조와 비교한 공통 테이블 표현식
공통 테이블 표현식(CTE)은 몇 가지 면에서 파생 테이블과 유사합니다.
두 구문 모두 이름이 지정됩니다.
두 구문 모두 단일 문의 범위에 대해 존재합니다.
이러한 유사성으로 인해 CTE와 파생 테이블은 종종 서로 바꿔서 사용할 수 있습니다. 간단한 예로, 다음 문장은 동일합니다.
WITH cte AS (SELECT 1) SELECT * FROM cte;
SELECT * FROM (SELECT 1) AS dt;
그러나 CTE는 파생 테이블에 비해 몇 가지 장점이 있습니다.
파생 테이블은 쿼리 내에서 한 번만 참조할 수 있습니다. CTE는 여러 번 참조될 수 있습니다. 파생 테이블 결과의 여러 인스턴스를 사용하려면 결과를 여러 번 파생시켜야 합니다.
CTE는 자체 참조(재귀적)일 수 있습니다.
하나의 CTE는 다른 CTE를 참조할 수 있습니다.
CTE는 해당 정의가 문장 안에 포함되지 않고 문장의 시작 부분에 나타날 때 읽기가 더 쉬울 수 있습니다.
CTE는 CREATE [TEMPORARY] TABLE로 생성된 테이블과 유사하지만 명시적으로 정의하거나 삭제할 필요는 없습니다. CTE의 경우 테이블을 생성하는 데 권한이 필요하지 않습니다.
"자동확장" 항목의 "자동확장모드"를 사용:그룹에 인스턴스 추가 및 삭제를 선택한다. 자동확장할 인스턴스의 최소/최대 개수를 입력한다.
[만들기]를 클릭한다.
load balancing
GCP 콘솔의 좌측 3단메뉴를 클릭한 다음 네트워크 서비스를 클릭한 후 [부하 분산] 메뉴를 클릭한다.
아래 화면이 나타나면 [부하 분산기 만들기]를 클릭한다.
아래 화면에서 구성하려는 부하분산기의 용도에 따라 HTTP(S), TCP, UDP 부하 분산기의 [구성 시작]을 클릭한다. 본 문서에서는 HTTP(S) 부하 분산기와 TCP부하 분산기를 생성한다.
HTTP(S) 부하 분산 :
HTTP 부하분산을 위해 인터넷 연결 또는 내부 전용 선택 화면에서 "인터넷에서 VM 또는 서버리스 서비스로" 항목을 선택한다.
TCP 부하 분산 :
"부하 분산기 만들기" 화면에서 "백엔드 유형" 항목에서 "대상 풀 및 대상 인스턴스"를 선택한 후 [계속] 버튼을 클릭한다. 백엔드 유형을 인스턴스 그룹으로 지정하려면 "백엔드 서비스"를 선택한다.
부하 분산기 이름을 lbl-aicallbot-int-prd-01 로 입력하고 리전을 선택한다. 우측 화면에서 "기존 인스턴스 선택" 탭을 선택하고 기존에 생성한 VM 인스턴스를 선택한다. 상태 확인에서는 상태확인 생성을 클릭하여 이름과 port를 입력하여 생성한다. 체크할 port는 80으로 설정했다. 본 문서에서는 check-stat-prd-01 이름과 80포트로 생성했다.
화면에서 "프런트엔트 구성"을 클릭한 후, 우측에 나타난 화면에서 포트 부분에 로드 밸런서에 접속할 port 번호를 입력한다. 로드 밸런서는 port forwarding 이 없으므로 백엔드 서버에서 서비스하는 port와 동일하게 입력한다. 백엔드 서버에서 80 포트를 체크하도록 설정하였으므로 80 포트를 입력한다.
최종 "검토 및 완료(선택사항)"을 클릭하여 생성하고자 하는 데로 잘 구성이 되었는지 확인한 다음 [만들기] 버튼을 클릭하여 로드 밸런서를 생성한다.
로드 밸런서의 IP를 확인하여 브라우저에 http://load_balancer_IP/ 를 입력한다.
웹서버의 첫번째 화면을 각 서버별로 상이하게 작성하고 브라우저를 계속 reflesh 하면 웹 화면이 변경되는 것을 확인할 수 있다.
Filestore 생성
첨부 파일 등 파일 공유를 위해 Filestore를 생성한다. 기존 on-premise 에서의 NFS 파일시스템과 동일하다.
좌측 메뉴에서 [Filestore] - [인스턴스]를 클릭한 후 Cloud Filestore API 화면이 나타나면 [사용]을 클릭한다.
그 다음 화면에서 [인스턴스 만들기]를 클릭한다.
"인스턴스 만들기" 화면이 나타나면 인스턴스ID, 스토리지 유형, 용량할당, 저장위치, VPC 네트워크, 파일공유 이름 등을 설정한다.
본 문서에서는 인스턴스ID를 fsr-aicallbot-admin-prd-01 로 입력했고, 저장위치와 영역은 각각 asia-northeast3과 asia-northeast3-a를 선택했으며 VPC 네트워크는 위에서 생성한 VPC를 선택했다.
파일 공유이름으로는 nasdata_prd_01 로 입력했다. 모든 항목을 입력했으면 [만들기] 버튼을 클릭한다.
생성이 완료되면 VM이 이 파일 공유 항목을 마운할 수 있도록 NFS 마운트 point가 생성된다.
본 문서에서는 10.7.208.186:/nasdata_prd_01 로 생성되었다.
Filestore를 사용하는 VM 인스턴스에 접속하여 NFS 파일시스템을 마운트한다. filestore를 공유할 VM 인스턴스 모두에서 작업해준다.
sudo mkdir -p mount-point-directory 에서 mount-point-directory는 로컬에 생성할 디렉토리이다. 본 문서에서는 아래와 같이 /filedir로 생성한다.
sudo mkdir -p /filedir
mount명령어로 Filestore 인스턴스에서 파일 공유를 마운트한다.
위에서 생성된 NFS 마운트포인트명으로 파일시스템을 마운트한다.
sudo mount ip-address:/file-share mount-point-directory 에서 ip-address:/file-share 는 위에서 생성한 filestore의 NFS 마운트포인트이고 mount-point-directory는 VM에서 생성한 마운트포인트 디렉토리이다.
--(이중대시) : MySQL에서 이중 대시(--) 주석은 두 번째 대시 뒤에 최소한 하나의 공백이나 제어 문자(공백, 탭, 개행 문자 등)가 와야 합니다.
/* */ : 이 구문을 사용하면 주석을 여러 줄에 걸쳐 확장할 수 있습니다.
다음 예에서는 세 가지 주석 스타일을 모두 보여줍니다.
mysql> SELECT 1+1; # This comment continues to the end of line
mysql> SELECT 1+1; -- This comment continues to the end of line
mysql> SELECT 1 /* this is an in-line comment */ + 1;
mysql> SELECT 1+
/*
this is a
multiple-line comment
*/
1;
중첩 주석은 지원되지 않습니다.
MySQL Server는 C 스타일 주석의 특정 변형을 지원합니다. 다음 형식의 주석을 사용하여 MySQL 확장을 포함하지만 이식 가능한 코드를 작성할 수 있습니다.
/*! MySQL-specific code */
이 경우 MySQL 서버는 다른 SQL 문과 마찬가지로 주석 내의 코드를 구문 분석하고 실행하지만 다른 SQL 서버는 무시합니다.
예를 들어, MySQL Server는 STRAIGHT_JOIN 키워드를 인식하지만 다른 서버는 인식하지 않습니다.
SELECT /*! STRAIGHT_JOIN */ col1 FROM table1,table2 WHERE ...
문자 뒤에 버전 번호를 추가하면 ! MySQL 버전이 지정된 버전 번호보다 크거나 같은 경우에만 주석 내의 구문이 실행됩니다. 다음 주석 의 KEY_BLOCK_SIZE키워드는 MySQL 5.1.10 이상의 서버에서만 실행됩니다.
위에서 설명한 주석 구문은 mysqld 서버가 SQL 문을 구문 분석하는 데 적용됩니다. 또한 mysql 클라이언트 프로그램은 명령문을 서버로 보내기 전에 구문 분석을 수행합니다. 서버와 mysql 클라이언트 파서의 차이점에 대한 정보는 "mysql 클라이언트" 에서 참조합니다.
/*!12345 ... */ 형식의 주석은 서버에 저장되지 않습니다. 이 형식을 사용하여 stored procedure에 주석을 달면 주석이 procedure body 문에 유지되지 않습니다.
Administration 항목의 일부 메뉴 클릭 시 아래와 같이 chcp 관련 RuntimeError 가 발생합니다.
에러 조치 방안으로 PATH에 C:\Windows\System32 경로를 추가하라는 메시지가 뜨는데 PATH에 경로를 추가해도 동일한 오류가 발생합니다.
유니코드를 지원하지 않는 프로그램용 언어
이 설정은 유니코드를 지원하지 않는 프로그램에서 텍스트를 표시할 때 사용되는 언어를 제어한다고 설명되어 있습니다. 이 언어를 기존 한국어(대한민국)에서 영어(미국)으로 변경한 후, MySQL Workbench가 설치된 컴퓨터를 재기동하면 위에서 발생했던 에러가 발생하지 않습니다.
변경 방법은 [윈도우키] - [설정(톱니바퀴 모양)] 을 클릭하면 아래와 같이 윈도우 설정 화면이 나타납니다. [시간 및 언어] 메뉴를 클릭합니다.
아래 화면이 나타나면 좌측의 [언어] 메뉴를 클릭한 후 우측 상단의 [관리 언어 설정] 메뉴를 클릭합니다.
[관리자 옵션] 탭에서 [시스템 로캘 변경] 버튼을 클릭한 후 한국어(대한민국)를 영어(미국)으로 변경합니다. 변경 후 컴퓨터를 재부팅하면 오류없이 MySQL Workbench 의 기능을 사용할 수 있습니다.
