'MySQL' 카테고리의 글 목록 (7 Page)

MySQL

세션변수, 재귀호출, 중첩커서

Tutor.C 2024. 12. 13. 16:56

2024. 12. 13. 16:56

1.세션변수

스토어드 프로그램에서는 DECLARE 명령을 이용해 스토어드 프로그램의 로컬 변수를 정의할 수 있다. 또한 스토어드 프로그램 내에서는 "@"로 시작하는 사용자 변수를 사용할 수도 있다. DECLARE로 스토어드 프로그램의 변수를 정의할 때는 정확한 타입과 길이를 명시해야 하지만 사용자 변수는 이런 제약이 없다.

mysql> DELIMITER ;;
mysql> CREATE FUNCTION sf_getsum(p_arg1 INT, p_arg2 INT)
    -> RETURNS INT
    -> BEGIN
    ->  DECLARE v_sum INT DEFAULT 0;
    ->  SET v_sum=p_arg1 + p_arg2;
    ->  SET @v_sum=v_sum;
    ->  RETURN v_sum;
    -> END;;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT sf_getsum(1,2);;
+----------------+
| sf_getsum(1,2) |
+----------------+
|              3 |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT @v_sum;;
+--------+
| @v_sum |
+--------+
|      3 |
+--------+
1 row in set (0.00 sec)

mysql> DELIMITER ;

사용자 변수는 타입을 지정하지 않기 때문에 데이터 타입에 대해 안전하지 않고, 영향을 미치는 범위가 넓기 때문에 그만큼 느리게 처리된다. 또한 사용자 변수는 적어도 그 커넥션에서는 계속 그 값을 유지한 채 남아 있기 때문에 항상 사용하기 전에 적절한 값으로 초기화하고 사용해야 한다.

스토어드 프로그램에서 프리페어 스테이트먼트를 실행하려면 반드시 세션 변수를 사용할 수 밖에 없다. 하지만 이러한 경우가 아니라면 가능한 세션 변수보다는 스토어드 프로그램의 로컬 변수를 사용하자.

2.재귀호출

재귀 호출은 스토어드 프로시저에서만 사용 가능하면 스토어드 함수와 트리거 그리고 이벤트에서는 사용할 수 없다. 또한 프로그래밍 언어에서처럼 재귀 호출이 무한 반복되거나 너무 많이 반복해서 호출되면 스택의 메모리 공간이 모자라서 오류가 발생할 수도 있다.

MySQL에서는 최대 몇 번까지 재귀 호출을 허용할지를 설정하는 시스템 변수가 있다. max_sp_recursion_depth인데 기본적으로 0으로 설정돼어 있다. 이 설정값을 변경하지 않으면 재귀 호출을 사용할 수 없고 에러 메시지가 출력되고 프로시저의 실행은 종료된다.

mysql> show variables like 'max_sp_recursion_depth';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_sp_recursion_depth | 0     |
+------------------------+-------+
1 row in set (0.00 sec)

다음 예제는 "SET max_sp_recursion_depth=50;"를 이용해 최대 재귀 호출 가능 횟수를 50회로 설정했다. 예제의 스토어드 프로시저는 1씩 감소하면서 재귀 호출을 수행하므로 max_sp_recursion_depth 시스템 변수가 50으로 설정되면 재귀 호출도 50이상은 할 수 없다는 것을 의미한다.

그리고 재귀호출에서 반복 횟수만 문제되는 것이 아니다. MySQL 서버에서 할당한 스택의 메모리가 다 소모돼 버린다면 "스택 메모리가 부족하다(Thread stack overrun)"라는 에러 메시지와 함께 종료된다.

mysql> DELIMITER ;;
mysql> CREATE PROCEDURE sf_getfactorial(IN p_max INT, OUT p_sum INT)
    -> BEGIN
    ->  SET max_sp_recursion_depth=5;
    ->  SET p_sum=1;
    ->  
    ->  IF p_max>1 THEN
    ->   CALL sf_decreaseandmultiply(p_max,p_sum);
    ->  END IF;
    -> END;;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE sf_decreaseandmultiply(IN p_current INT, INOUT p_sum INT)
    -> BEGIN
    ->  SET p_sum=p_sum*p_current;
    ->  SELECT CONCAT('>p_sum:',p_sum);
    ->  SELECT CONCAT('>p_current:',p_current);
    ->  
    ->  IF p_current>1 THEN
    ->    CALL sf_decreaseandmultiply(p_current-1,p_sum);
    ->  END IF;
    -> END;;
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> CALL sf_getfactorial(3,@factorial);
+-------------------------+
| CONCAT('>p_sum:',p_sum) |
+-------------------------+
| >p_sum:3                |
+-------------------------+
1 row in set (0.01 sec)

+---------------------------------+
| CONCAT('>p_current:',p_current) |
+---------------------------------+
| >p_current:3                    |
+---------------------------------+
1 row in set (0.01 sec)

+-------------------------+
| CONCAT('>p_sum:',p_sum) |
+-------------------------+
| >p_sum:6                |
+-------------------------+
1 row in set (0.01 sec)

+---------------------------------+
| CONCAT('>p_current:',p_current) |
+---------------------------------+
| >p_current:2                    |
+---------------------------------+
1 row in set (0.01 sec)

+-------------------------+
| CONCAT('>p_sum:',p_sum) |
+-------------------------+
| >p_sum:6                |
+-------------------------+
1 row in set (0.01 sec)

+---------------------------------+
| CONCAT('>p_current:',p_current) |
+---------------------------------+
| >p_current:1                    |
+---------------------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

3. 중첩 커서 사용

중첩된 루프 안에서 두 개의 커서를 동시에 열어서 사용할 때는 특별히 예외 핸들링 부분에 주의해야 한다.

1) 중첩된 커서 사용

아래 예제는 외부 루프와 내부 루프에서 각각 동시에 커서를 하나씩 사용하고 있다. 하지만 커서를 끝까지 읽었는지 판단하는 핸들러는 하나만 정의해서 공통으로 사용하고 있다. 이러한 이유로 LOOP_INNER의 반복이 끝나고 나면 반드시 v_no_more_rows를 FALSE로 변경해야만 LOOP_OUTER가 계속 반복 실행할 수가 있다. 즉, 하나의 로컬 변수로 두 개의 반복 루프를 제어하다보니 이런 이해하기 힘든 조작이 필요해진 것이다.

mysql> DROP PROCEDURE sp_updateemployeehiredate;
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;;
mysql> 
mysql> CREATE PROCEDURE sp_updateemployeehiredate()
    -> BEGIN
    ->   -- // 첫 번째 커서로부터 읽은 부서 번호를 저장
    ->   DECLARE v_dept_no CHAR(4);
    ->   -- // 두 번째 커서로부터 읽은 사원 번호를 저장
    ->   DECLARE v_emp_no INT;
    ->   -- // 커서를 끝까지 읽었는지 여부를 나타내는 플래그를 저장
    ->   DECLARE v_no_more_rows BOOLEAN DEFAULT FALSE;
    ->   
    ->   -- // 부서 정보를 읽는 첫 번째 커서
    ->   DECLARE v_dept_list CURSOR FOR
    ->      SELECT dept_no FROM departments;
    ->  
    ->   -- // 부서별 사원 1명을 읽는 두 번째 커서
    ->   DECLARE v_emp_list CURSOR FOR
    ->      SELECT emp_no FROM dept_emp WHERE dept_no=v_dept_no LIMIT 1;
    ->  
    ->   -- // 커서의 레코드를 끝까지 다 읽은 경우에 대한 핸들러
    ->   DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_no_more_rows := TRUE;
    ->   
    ->   OPEN v_dept_list;
    ->   
    ->   LOOP_OUTER: LOOP -- // 외부 루프 시작
    ->     FETCH v_dept_list INTO v_dept_no;
    -> IF v_no_more_rows THEN  -- // 레코드를 모두 읽었으면 커서 종료 및 외부 루프 종료  
    ->   CLOSE v_dept_list;
    ->   SELECT CONCAT(' <<<<<<<< LOOP OUTER END >>>>>>>> ') AS debug_message;
    ->   LEAVE loop_outer;  
    -> END IF;
    -> SELECT CONCAT(' > v_dept_no :', v_dept_no) AS debug_message;
    -> 
    -> OPEN v_emp_list;
    -> 
    -> LOOP_INNER: LOOP  -- // 내부 루프 시작 
    ->   FETCH v_emp_list INTO v_emp_no;  
    ->   -- // 레코드를 모두 읽었으면 커서 종료 및 내부 루프를 종료
    ->   IF v_no_more_rows THEN
    ->   -- // 반드시 no_more_rows를 FALSE로 다시 변경해야 한다.
    ->   -- // 그렇지 않으면 내부 루프 때문에 외부 루프까지 종료돼 버린다.
    ->     SET v_no_more_rows := FALSE;
    ->     CLOSE v_emp_list;
    ->     SELECT CONCAT(' <<<<<<<< LOOP INNER END >>>>>>>> ') AS debug_message;    -> 
    ->     LEAVE loop_inner;
    ->   END IF;
    ->   SELECT CONCAT(' > v_emp_no :', v_emp_no) AS debug_message;
    ->   
    ->     END LOOP loop_inner; -- // 내부 루프 종료
    -> 
    ->   END LOOP loop_outer; -- // 외부 루프 종료
    ->  
    -> END;;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> DELIMITER ;

중첩된커서1.sql

0.00MB

위 프로시저를 실행 시 Display all 1003 possibilities? (y or n) 같은 에러가 나면은 Notepad와 같은 곳에서 바꾸기로 \t 을 공백으로 만들어 주면 된다.

mysql> call sp_updateemployeehiredate;
+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d009 |
+--------------------+
1 row in set (0.00 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10011 |
+--------------------+
1 row in set (0.00 sec)

+------------------------------------+
| debug_message                      |
+------------------------------------+
|  <<<<<<<< LOOP INNER END >>>>>>>>  |
+------------------------------------+
1 row in set (0.00 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d005 |
+--------------------+
1 row in set (0.00 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10001 |
+--------------------+
1 row in set (0.00 sec)

+------------------------------------+
| debug_message                      |
+------------------------------------+
|  <<<<<<<< LOOP INNER END >>>>>>>>  |
+------------------------------------+
1 row in set (0.00 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d002 |
+--------------------+
1 row in set (0.00 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10042 |
+--------------------+
1 row in set (0.00 sec)

+------------------------------------+
| debug_message                      |
+------------------------------------+
|  <<<<<<<< LOOP INNER END >>>>>>>>  |
+------------------------------------+
1 row in set (0.00 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d003 |
+--------------------+
1 row in set (0.00 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10005 |
+--------------------+
1 row in set (0.00 sec)

+------------------------------------+
| debug_message                      |
+------------------------------------+
|  <<<<<<<< LOOP INNER END >>>>>>>>  |
+------------------------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d001 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10017 |
+--------------------+
1 row in set (0.01 sec)

+------------------------------------+
| debug_message                      |
+------------------------------------+
|  <<<<<<<< LOOP INNER END >>>>>>>>  |
+------------------------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d004 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10003 |
+--------------------+
1 row in set (0.01 sec)

+------------------------------------+
| debug_message                      |
+------------------------------------+
|  <<<<<<<< LOOP INNER END >>>>>>>>  |
+------------------------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d006 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10009 |
+--------------------+
1 row in set (0.01 sec)

+------------------------------------+
| debug_message                      |
+------------------------------------+
|  <<<<<<<< LOOP INNER END >>>>>>>>  |
+------------------------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d008 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10007 |
+--------------------+
1 row in set (0.01 sec)

+------------------------------------+
| debug_message                      |
+------------------------------------+
|  <<<<<<<< LOOP INNER END >>>>>>>>  |
+------------------------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d007 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10002 |
+--------------------+
1 row in set (0.01 sec)

+------------------------------------+
| debug_message                      |
+------------------------------------+
|  <<<<<<<< LOOP INNER END >>>>>>>>  |
+------------------------------------+
1 row in set (0.01 sec)

+------------------------------------+
| debug_message                      |
+------------------------------------+
|  <<<<<<<< LOOP OUTER END >>>>>>>>  |
+------------------------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

2) 중첩된 커서 사용(핸들러 처리 보완)

반복 루프가 여러 번 중첩되어 커서가 사용될 때는 LOOP_OUTER와 LOOP_INNER를 서로 다른 BEGIN ... END 블록으로 구분해서 작성할 수 있다. 스토어드 프로시저 코드의 처리 중 발생한 에러나 예외는 항상 가장 가까운 블럭에 정의된 핸들러가 사용되므로 각 반복루프를 블록으로 처리할 수 있다.

아래 예제는 사원을 조회하는 커서와 부서를 조회하는 각 커서에 대해 예외 핸들러를 정의 하였다. 중첩된 커서를 각 프로시저 블럭에 작성함으로써 각 커서별로 이벤트 핸들러를 생성할 수 있게 된 것이다.

mysql> DELIMITER ;;
mysql> 
mysql> CREATE PROCEDURE sp_updateemployeehiredate1()
    -> BEGIN
    ->   -- // 첫 번째 커서로부터 읽은 부서 번호를 저장
    ->   DECLARE v_dept_no CHAR(4);
    ->   -- // 커서를 끝까지 읽었는지 여부를 나타내는 플래그를 저장
    ->   DECLARE v_no_more_depts BOOLEAN DEFAULT FALSE;  
    ->   -- // 부서 정보를 읽는 첫 번째 커서
    ->   DECLARE v_dept_list CURSOR FOR
    ->      SELECT dept_no FROM departments; 
    ->   -- // 부서 커서의 레코드를 끝까지 다 읽은 경우에 대한 핸들러
    ->   DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_no_more_depts := TRUE;
    ->   
    ->   OPEN v_dept_list;
    -> 
    ->   LOOP_OUTER: LOOP -- // 외부 루프 시작
    ->     FETCH v_dept_list INTO v_dept_no;
    -> IF v_no_more_depts THEN  -- // 레코드를 모두 읽었으면 커서 종료 및 외부 루프 종료  
    ->   CLOSE v_dept_list;
    ->   LEAVE loop_outer;  
    -> END IF;
    -> SELECT CONCAT(' > v_dept_no :', v_dept_no) AS debug_message;
    -> 
    ->   BLOCK_INNER: BEGIN -- // 내부 프로시저 블럭 시작 
    ->     -- // 두 번째 커서로부터 읽은 사원 번호 저장
    ->     DECLARE v_emp_no INT;
    -> -- // 사원 커서를 끝까지 읽었는지 여부를 위한 플래그 저장 
    -> DECLARE v_no_more_employees BOOLEAN DEFAULT FALSE;
    ->     -- // 부서별 사원 1명을 읽는 두 번째 커서
    ->     DECLARE v_emp_list CURSOR FOR
    ->      SELECT emp_no FROM dept_emp WHERE dept_no=v_dept_no LIMIT 1;
    ->     -- // 사원 커서의 레코드를 끝까지 다 읽은 경우에 대한 핸들러
    ->     DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_no_more_employees := TRUE;
    -> 
    -> OPEN v_emp_list;
    -> 
    -> LOOP_INNER: LOOP  -- // 내부 루프 시작 
    ->   FETCH v_emp_list INTO v_emp_no;  
    ->   -- // 레코드를 모두 읽었으면 커서 종료 및 내부 루프를 종료
    ->   IF v_no_more_employees THEN   
    ->     CLOSE v_emp_list;
    ->     LEAVE loop_inner;
    ->   END IF;
    ->   SELECT CONCAT(' > v_emp_no :', v_emp_no) AS debug_message;  
    -> END LOOP loop_inner; -- // 내부 루프 종료
    -> 
    ->    END block_inner; -- // 내부 프로시저 블럭 종료
    -> 
    ->   END LOOP loop_outer; -- // 외부 루프 종료
    ->  
    -> END;;
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

중첩된커서2.sql

0.00MB

사원 정보에 대한 커서가 끝까지 읽히면 v_no_more_employees라는 변수가 TRUE로 설정되며, 부서 정보에 대한 커서가 끝까지 읽히면 v_no_more_depts라는 변수가 TRUE로 설정된다. 1)번 예제에서와 같이 플래그 변수의 값을 다시 조정하는 작업을 하지 않아도 된다.

mysql> call sp_updateemployeehiredate1;
+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d009 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10011 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d005 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10001 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d002 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10042 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d003 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10005 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d001 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10017 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d004 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10003 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d006 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10009 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d008 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10007 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_dept_no :d007 |
+--------------------+
1 row in set (0.01 sec)

+--------------------+
| debug_message      |
+--------------------+
|  > v_emp_no :10002 |
+--------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

'MySQL > MySQL' 카테고리의 다른 글

Collation (0)	2024.12.20
문자열(CHAR와 VARCHAR) (0)	2024.12.13
스토어드 프로그램 본문(Body) 작성 (0)	2024.12.13
스토어드 함수 (0)	2024.12.13
스토어드 프로시저 (0)	2024.12.13

스토어드 프로그램 본문(Body) 작성

2024. 12. 13. 16:49

스토어드 함수

Tutor.C 2024. 12. 13. 16:44

2024. 12. 13. 16:44

스토어드 함수(Stored Function)

스토어드 함수는 하나의 SQL 문장으로 작성이 불가능한 기능을 하나의 SQL 문장으로 구현해야 할 때 사용한다. 각 부서별로 가장 최근에 배속된 사원을 2명씩 가져오는 기능을 생각해 보자. dept_emp 테이블의 데이터를 부서별로 그룹핑하는 것까지는 가능하지만, 해당 부서 그룹별로 최근 2명씩만 잘라서 가져오는 방법은 없다. 이럴 때 부서 코드를 인자로 입력받아 최근 2명의 사원 번호만 SELECT하고 문자열로 결합해서 반환하는 함수를 만들어서 사용하면 된다. 만약 이런 스토어드 함수가 준비되면 다음과 같이 사용하면 된다.

select dept_no, sf_getRecentEmp(dept_no)
  from dept_emp 
 group by dept_no;

SQL 문장과 관계없이 별도로 실행되는 기능이라면 굳이 스토어드 함수를 개발할 필요가 없다. 독립적으로 실행돼도 된다면 스토어드 프로시저를 사용하는 것이 좋다. 상대적으로 스토어드 함수는 스토어드 프로시저보다 제약 사항이 많기 때문이다. 스토어드 프로시저와 비교했을 때 스토어드 함수의 장점은 SQL 문장의 일부로 사용할 수 있다는 것이다.

1. 스토어드 함수 생성 및 삭제

스토어드 함수는 CREATE FUNCTION 명령으로 생성할 수 있으며, 모든 입력 파라미터는 읽기 전용이라서 IN이나 OUT 또는 INOUT과 같은 형식을 지정할 수 없다. 그리고 스토어드 함수는 반드시 정의부에 RETURNS 키워드를 이용해 반환되는 값의 타입을 명시해야 한다.

mysql> CREATE FUNCTION sf_sum(param1 INTEGER, param2 INTEGER)
    ->  RETURNS INTEGER
    -> BEGIN
    ->  DECLARE param3 INTEGER DEFAULT 0;
    ->  SET param3 = param1+param2;
    ->  RETURN param3;
    -> END;;
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

mysql> show global variables like 'log_bin_trust_function_creators';;
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
1 row in set (0.01 sec)

mysql> SET GLOBAL log_bin_trust_function_creators = 1;
Query OK, 0 rows affected (0.00 sec)

SET GLOBAL log_bin_trust_function_creators = 1; -- ON FUNCTION 생성 가능

SET GLOBAL log_bin_trust_function_creators = 0; -- OFF

(복사하면 공백문자로 에러나면 직접 기입하자.)

mysql> DELIMITER ;;
mysql> create function sf_sum(param1 integer, param2 integer)
    ->  returns integer
    -> begin
    ->  declare param3 integer default 0;
    ->  set param3 = param1 + param2;
    ->  return param3;
    -> end;;
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql> select sf_sum(1,2);
+-------------+
| sf_sum(1,2) |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)

스토어드 함수가 스토어드 프로시저와 크게 다른 부분은 다음 두 가지다.

- 함수 정의부에 RETURNS로 반환되는 값의 타입을 명시해야 한다.

- 함수 본문 마지막에 정의부에 지정된 타입과 동일한 타입의 값을 RETURN 명령으로 반환해야 한다.

스토어드 프로시저와는 달리 스토어드 프로그램의 본문(BEGIN ... END)에서는 다음과 같은 사항을 사용하지 못한다.

- PREFARE와 EXECUTE 명령을 이용한 프리페어 스테이트먼트를 사용할 수 없다.

- 명시적 또는 묵시적인 ROLLBACK/COMMIT을 유발하는 SQL 문장을 사용할 수 없다.

- 재귀 호출(Recursive call)을 사용할 수 없다.

- 스토어드 함수 내에서 프로시저를 호출할 수 없다.

- 결과 셋을 반환하는 SQL 문장을 사용할 수 없다.

결과 셋을 Fetch하지 않아서 결과 셋이 클라이언트로 전송되는 스토어드 함수를 생성하면 어떤 에러가 발생하는지 테스트로 한번 살펴보자. 스토어드 함수의 본문에서 커서나 SELECT 쿼리를 사용해도 특별히 문제되지 않지만 스토어드 프로시저에서와 같이 디버깅 용도로 화면에 메시지를 출력하기 위해서는 사용할 수 없다. 즉 스토어드 함수에서 커서를 정의하면 반드시 오픈해야 하며, "SELECT .. INTO .."가 아닌 단순히 SELECT 쿼리만을 실행해서는 안 된다. 스토어드 함수 내에서 오픈되지 않는 커서나 단순 SELECT 쿼리가 실행되는 것은 결과적으로 클라이언트로 쿼리의 결과 셋을 반환하는 것과 똑같기 때문이다. 동일한 효과를 만들어 내는 SHOW 또는 EXPLAIN 등의 명령도 사용할 수 없다.

mysql> DELIMITER ;;
mysql> CREATE FUNCTION sf_resultset_test()
    ->  RETURNS INTEGER
    -> BEGIN
    ->  DECLARE param3 INTEGER DEFAULT 0;
    ->  SELECT 'Start stored function' AS debug_message;
    ->  RETURN param3;
    -> END;;
ERROR 1415 (0A000): Not allowed to return a result set from a function

2. 스토어드 함수 실행

스토어드 함수는 CALL 명령으로 실행할 수 없다. SELECT 문장을 이용해 실행한다.

mysql> DELIMITER ;
mysql> select sf_sum(1,2) as sum;
+------+
| sum  |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

mysql> call sf_sum(1,2);
ERROR 1305 (42000): PROCEDURE employees.sf_sum does not exist

'MySQL > MySQL' 카테고리의 다른 글

세션변수, 재귀호출, 중첩커서 (0)	2024.12.13
스토어드 프로그램 본문(Body) 작성 (0)	2024.12.13
스토어드 프로시저 (0)	2024.12.13
MySQL PARTITION (0)	2024.12.10
MySQL 계층쿼리 구현(사용자변수,CTE) (0)	2024.12.10

스토어드 프로시저

Tutor.C 2024. 12. 13. 16:41

2024. 12. 13. 16:41

스토어드 프로시저

스토어드 프로시저는 서로 데이터를 주고 받아야 하는 여러 쿼리를 하나의 그룹으로 묶어서 독립적으로 실행하기 위해 사용하는 것이다. 배치 프로그램에서 첫 번째 쿼리의 결과를 이용해 두 번째 쿼리를 실행해야 할 때를 대표적인 예로 볼 수 있다. 이처럼 각 쿼리가 서로 연관되어 데이터를 주고 받으면서 반복적으로 실행돼야 할 때 스토어드 프로시저를 사용하면 MySQL 서버와 클라이언트 간의 네트워크 전송 작업을 최소화하고 수행 시간을 줄일 수 있다. 스토어드 프로시저는 반드시 독립적으로 호출돼야 하며, SELECT나 UPDATE와 같은 SQL 문장에서 스토어드 프로시저를 참조할 수 없다.

1. 스토어드 프로시저 생성 및 삭제

- 스토어드 프로시저는 기본 반환값이 없다. 즉, 스토어드 프로시저 내부에서는 값을 반환하는 RETURN 명령을 사용할 수 없다.

- 스토어드 프로시저의 각 파라미터는 아래의 3가지 특성 중 하나를 지닌다.

- IN 타입으로 정의된 파라미터는 입력 전용 파라미터를 의미한다. IN 파라미터는 외부에서 스토어드 프로그램을 호출할 때 프로시저에 값을 전달하는 용도로 사용하고, 값을 반환하는 용도로 사용하지 않는다. 즉 IN 타입으로 정의된 파라미터는 스토어드 프로시저 내부에서는 일기 전용으로 이해하면 된다.

- OUT 타입으로 정의된 파라미터는 출력 전용 파라미터다. OUT 파라미터는 스토어드 프로시저 외부에서 스토어드 프로시저를 호출할 때 어떤 값을 전달하는 용도로는 사용할 수 없다. 스토어드 프로시저의 실행이 완료되면 외부 호출자로 결과 값을 전달하는 용도로만 사용한다.

- INOUT 타입으로 정의된 파라미터는 입력 및 출력 용도로 모두 사용할 수 있다.

스토어드 프로시저를 포함한 스토어드 프로그램을 사용할 때는 특별히 SQL 문장의 구분자를 변경해야 한다. 일반적으로 MySQL 클라이언트 프로그램에서는 ";" 문자가 쿼리의 끝을 의미한다. 하지만 스토어드 프로그램은 본문 내부에 무수히 많은 ";" 문자를 포함하므로 MySQL 클라이언트가 CREATE PROCEDURE 명령의 끝을 정확히 찾을 수가 없다. 그래서 CREATE 명령으로 스토어드 프로그램을 생성할 때는 MySQL 서버가 CREATE 명령의 끝을 정확히 판별할 수 있게 별도의 문자열을 구분자로 설정해야 한다.

명령의 끝을 알려주는 종료문자를 변경하는 명령어는 DELIMITER다. 일반적으로 CREATE로 스토어드 프로그램을 생성할 때는 ";;" 또는 "//"과 같이 연속된 2개의 문자열을 종료문자로 설정한다. 종료문자는 어떤 것이든 쓸 수 있지만 스토어드 프로그램에서는 사용되지 않은 문자열을 선택해야 한다. 다음 예제는 종료 문자를 ";;"로 변경하고, 스토어드 프로그램을 생성하는 예제다.

mysql> DELIMITER ;;
mysql> create procedure sp_sum (IN param1 INTEGER, IN param2 INTEGER, OUT param3 INTEGER)
    -> begin
    -> set param3 = param1 + param2;
    -> end;;
Query OK, 0 rows affected (0.01 sec)

mysql> DELIMITER ;
mysql> set @result:=0;
Query OK, 0 rows affected (0.00 sec)

mysql> call sp_sum(1,3,@result);
Query OK, 0 rows affected (0.00 sec)

mysql> select @result;
+---------+
| @result |
+---------+
|       4 |
+---------+
1 row in set (0.00 sec)

예제와 같이 종료 문자가 ";;"로 변경되면 스토어드 프로그램의 생성 명령뿐 아니라 일반적인 SELECT나 INSERT와 같은 명령에서도 ";;"를 사용해야 한다. 이 상태에서는 실수가 자주 발생할 수 있으므로 다시 종료 문자를 기본 종료 문자인 ";"로 되돌리는 것이 좋다.

2. 스토어드 프로시저 실행

스토어드 프로시저는 SELECT 쿼리에 사용될 수 없으며, 반드시 CALL 명령어로 실행해야 한다. IN 타입의 파라미터는 상수값을 그대로 전달해도 무방하지만 OUT이나 INOUT 타입의 파라미터는 세션 변수를 이용해 값을 주고 받아야 한다. IN 타입의 파라미터에 MySQL 세션 변수를 사용해도 문제가 되지는 않는다.

mysql> set @param1:=2;
Query OK, 0 rows affected (0.00 sec)

mysql> set @param2:=4;
Query OK, 0 rows affected (0.00 sec)

mysql> set @result:=0;
Query OK, 0 rows affected (0.00 sec)

mysql> call sp_sum(@param1,@param2,@result);
Query OK, 0 rows affected (0.00 sec)

mysql> select @result;
+---------+
| @result |
+---------+
|       6 |
+---------+
1 row in set (0.00 sec)

3. 스토어드 프로시저의 커서 반환

스토어드 프로시저 내에서 커서를 오픈하지 않거나 SELECT 쿼리의 결과 셋을 Fetch 하지 않으면 해당 쿼리의 결과 셋은 클라이언트로 바로 전송된다.

mysql> DELIMITER ;;
mysql> CREATE PROCEDURE sp_selectEmployees (IN in_empno INTEGER)
    -> BEGIN
    ->   SELECT * FROM employees WHERE emp_no=in_empno;
    -> END;;
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> call sp_selectEmployees(10001);
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
+--------+------------+------------+-----------+--------+------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

실행 결과를 보면 스토어드 프로시저에서 SELECT 쿼리의 결과 셋을 별도로 반환하는 OUT 변수에 담거나 화면에 출력하는 처리를 하지도 않았는데 쿼리의 결과가 클라이언트로 전송된 것을 확인할 수 있다. 물론 이 기능은 JDBC를 이용하는 자바 프로그램에서도 그대로 이용할 수 있으며, 하나의 스토어드 프로시저에서 2개 이상의 결과 셋을 반환할 수도 있다. 스토어드 프로시저에서 쿼리의 결과 셋을 클라이언트로 전송하는 기능은 스토어드 프로시저의 디버깅 용도로도 자주 사용한다.

다음 예제는 프로시저의 본문 처리가 시작되기 전에 입력된 값을 화면에 표시하도록 스토어드 프로시저의 내용을 조금 변경하는 것이다.

mysql> DELIMITER ;;
mysql> CREATE PROCEDURE sp_sum1 (IN param1 INTEGER, IN param2 INTEGER, OUT param3 INTEGER)
    -> BEGIN
    -> SELECT '> Stored procedure started.' AS debug_message;
    -> SELECT CONCAT(' > param1 : ', param1) AS debug_message;
    -> SELECT CONCAT(' > param2 : ', param2) AS debug_message;
    -> SET param3 = param1+param2;
    -> SELECT '> Stored procedure completed.' AS debug_message;
    -> END;;
Query OK, 0 rows affected (0.01 sec)

mysql> SET @result:=0;;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp_sum1(4,6,@result);;
+-----------------------------+
| debug_message               |
+-----------------------------+
| > Stored procedure started. |
+-----------------------------+
1 row in set (0.00 sec)

+---------------+
| debug_message |
+---------------+
|  > param1 : 4 |
+---------------+
1 row in set (0.00 sec)

+---------------+
| debug_message |
+---------------+
|  > param2 : 6 |
+---------------+
1 row in set (0.00 sec)

+-------------------------------+
| debug_message                 |
+-------------------------------+
| > Stored procedure completed. |
+-------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select @result;;
+---------+
| @result |
+---------+
|      10 |
+---------+
1 row in set (0.00 sec)

'MySQL > MySQL' 카테고리의 다른 글

스토어드 프로그램 본문(Body) 작성 (0)	2024.12.13
스토어드 함수 (0)	2024.12.13
MySQL PARTITION (0)	2024.12.10
MySQL 계층쿼리 구현(사용자변수,CTE) (0)	2024.12.10
사용자 정의 변수 (0)	2024.12.10

MySQL PARTITION

Tutor.C 2024. 12. 10. 18:02

2024. 12. 10. 18:02

PARTITION

1. 개요

MySQL 5.1부터 제공되는 파티션 기능은 기본적으로 해시와 리스트 그리고 키와 레인지로 4가지 파티션 방법을 제공하고 있으며 서브 파티셔닝 기능까지 사용할 수 있다.

1.1 파티션을 사용하는 이유 (결론 포함)

하나의 테이블이 너무 커서 인덱스의 크기가 물리적인 메모리보다 크거나, 데이터 특성상 주기적인 삭제작업이 필요한 경우 등에 사용한다. 파티션을 생성하더라도 전체 범위(모든 파티션)를 읽는 업무에선 SELECT의 기능이 향상되지 않는다 부분적인 범위만 읽는 SELECT 쿼리의 경우 고려할 만 하다.

레코드의 건수가 너무 많아져서 INSERT나 DELETE와 같은 쓰기 작업이 심각하게 느려지고 있다면 파티션 적용을 고려해 보는 것이 좋다. 만약 날짜 컬럼을 이용해 레인지 파티션을 사용할 수 있고, 읽기나 쓰기 작업을 일부 파티션으로 모을 수 있다면 테이블의 크기에 관계없이 쓰기 및 읽기, 그리고 관리 작업까지 상당히 유용하다.

해시나 키 파티션을 사용해야 하는 상황이라면 무조건 파티션을 적용하기 보다는 쓰기 성능이 현저히 떨어졌을때 개선을 위해 파티션 적용을 고려해 본다.

MySQL에서 병렬 처리가 도입되는 순간이 오면 MySQL의 파티션이 새로운 대안이 될 것이다.

MySQL 5.6 또는 MySQL 5.7과 호환되는 Aurora MySQL 버전은 병렬 쿼리와 해시조인을 지원한다

Extra 컬럼에

Using join buffer (Hash Join Outer table orders);

Using parallel query (4 columns, 1 filters, 1 exprs; 0 extra)

와 같이 표시된다.

https://docs.aws.amazon.com/ko_kr/AmazonRDS/latest/AuroraUserGuide/aurora-mysql-parallel-query.html

1) 단일 INSERT와 단일 또는 범위 SELECT의 빠른 처리

테이블의 레코드를 변경하는 쿼리를 실행하면 인덱스의 변경을 위한 부가적인 작업을 수행한다. 인덱스는 SELECT를 할 때도 쓰이지만 대량의 테이블의 작은 범위의 UPDATE나 DELETE 처리를 위해 대상 레코드를 검색하려면 인덱스가 필요하다. 하지만 인덱스가 커지면 커질수록 SELECT는 물론이고 INSERT나 UPDATE 그리고 DELETE 작업도 느려지는 문제가 생긴다.

특히 한 테이블의 인덱스 크기가 물리적으로 MySQL이 사용 가능한 메모리 공간보다 크다면 그 영향은 심각해진다. 테이블의 데이터는 실질적인 물리 메모리보다 큰것이 일반적이지만 인덱스의 워킹 셋(Working set)이 실질적인 물리 메모리보다 크다면 쿼리가 상당히 느려질 것이다.

큰 테이블을 파티셔닝하지 않고 그냥 사용할 때와 작은 파티션으로 나눠서 워킹 셋의 크기를 줄인다고 가정하자. 파티셔닝하지 않고 하나의 큰테이블로 사용하면 인덱스도 커지고 그만큼 물리적인 메모리 공간도 많이 필요해진다. 결과적으로 파티션은 데이터와 인덱스를 조각화해서 물리적 메모리를 효율적으로 사용할 수 있게 만들어 준다.

[인덱스와 데이터를 분리해서 물리 메모리에 맞게 조각화]

테이블의 데이터가 10GB이고 인덱스가 3GB라고 가정해보자. 하지만 대부분 테이블은 13GB 전체를 항상 사용하는 것이 아니라 그중에서 활발하게 사용하는 부분을 주로 다시 사용한다. 즉 회원이 100만명이라 하더라도 그 중에서 활발하게 상요하는 회원은 2~30% 수준이라는 것이다. 거의 대부분의 테이블 데이터가 이런 형태로 사용된다고 볼 수 있는데, 활발하게 사용되는 데이터를 워킹 셋이라고 표현한다. 테이블의 데이터를 워킹 셋과 그렇지 않은 부류로 파티셔닝 할 수 있다면 상당히 효과적으로 성능을 개선할 수 있을 것이다.

2) 데이터의 물리적인 저장소 분리

데이터 파일이나 인덱스 파일이 파일 시스템에서 차지하는 공간이 크다면 그만큼 백업이나 관리 작업이 어려워진다. 이러한 문제는 파티션을 통해 파일의 크기를 조절하거나 각 파티션별 파일들이 저장될 위치나 디스크를 구분해서 지정해서 해결하는 것도 가능하다. 하지만 MySQL에서는 테이블의 파티션 단위로 인덱스를 순차적으로 생성하는 방법은 아직 허용되지 않는다.

3) 이력 데이터의 효율적인 관리

로그라는 이력데이터는 단기간에 대량으로 누적됨과 동시에 일정 기간이 지나면 쓸모가 없어진다. 특히 다른 데이터에 비해 라이프 사이클이 짧다. 로그 데이터는 시간이 지나면 아카이빙하거나 백업한 후 삭제해 버리는 것이 일반적이다. 로그 테이블에서 불필요해진 데이터를 백업하거나 삭제하는 작업은 일반 테이블에서는 고부하의 작업에 속한다. 하지만 로그 테이블을 파티션 테이블로 관리한다면 불필요한 데이터 삭제 작업은 단순히 파티션을 추가하거나 삭제하는 방식으로 간단하고 빠르게 해결할 수 있다.

[파티션을 이용한 특정 기간의 로그 로테이션]

대량의 데이터가 저장된 로그 테이블을 기간 단위로 삭제한다면 MySQL 서버에 전체적으로 미치는 부하뿐만 아니라 로그 테이블 자체의 동시성에도 영향을 미칠 수가 있다. 하지만 파티션을 이용하면 이러한 문제를 줄일 수 있다.

1.2 MySQL 파티션 내부 처리

파티션이 적용된 테이블에서 레코드의 INSERT와 UPDATE, 그리고 SELECT가 어떻게 처리되는지 확인해보기 위해 다음과 같은 간단한 테이블을 가정해 보자.

select distinct concat(concat(concat(concat('partition p',year(from_date)),' values less than ('),year(from_date)+1),'),') from titles;

create table titles_part1(
emp_no int not null,
title varchar(50) not null,
from_date date not null,
to_date date default null,
primary key(emp_no, from_date)
)
partition by range (year(from_date))(
partition p1985 values less than (1986),
partition p1986 values less than (1987),
partition p1987 values less than (1988),
partition p1988 values less than (1989),
partition p1989 values less than (1990),
partition p1990 values less than (1991),
partition p1991 values less than (1992),
partition p1992 values less than (1993),
partition p1993 values less than (1994),
partition p1994 values less than (1995),
partition p1995 values less than (1996),
partition p1996 values less than (1997),
partition p1997 values less than (1998),
partition p1998 values less than (1999),
partition p1999 values less than (2000),
partition p2000 values less than (2001),
partition p2001 values less than (2002),
partition p2002 values less than (2003),
partition p9999 values less than MAXVALUE
);

insert into titles_part1 select * from titles;

create table titles_part2(
emp_no int not null,
title varchar(50) not null,
from_date date not null,
to_date date default null,
primary key(from_date, emp_no)
)
partition by range (year(from_date))(
partition p1985 values less than (1986),
partition p1986 values less than (1987),
partition p1987 values less than (1988),
partition p1988 values less than (1989),
partition p1989 values less than (1990),
partition p1990 values less than (1991),
partition p1991 values less than (1992),
partition p1992 values less than (1993),
partition p1993 values less than (1994),
partition p1994 values less than (1995),
partition p1995 values less than (1996),
partition p1996 values less than (1997),
partition p1997 values less than (1998),
partition p1998 values less than (1999),
partition p1999 values less than (2000),
partition p2000 values less than (2001),
partition p2001 values less than (2002),
partition p2002 values less than (2003),
partition p9999 values less than MAXVALUE 
);

insert into titles_part2 select * from titles;

여기서 titles 테이블의 from_date의 연도 부분은 파티션 키로서 해당 레코드가 어느 파티션에 저장될지를 결정하는 중요한 역할을 담당한다.

1) 파티션 테이블의 레코드 INSERT

INSERT 쿼리가 실행되면 MySQL 서버는 INSERT되는 컬럼의 값 중에서 파티션 키인 from_date 컬럼의 값을 이용해 파티션 표현식을 평가하고, 그 결과를 이용해 레코드가 저장될 적절한 파티션을 결정한다. 새로 INSERT되는 레코드를 위한 파티션이 결정되면 나머지 과정은 파티션되지 않은 일반 테이블과 마찬가지로 처리된다.

2) 파티션 테이블의 UPDATE

UPDATE 쿼리를 실행하려면 변경 대상 레코드가 어느 파티션에 저장돼 있는지 찾아야 한다. 이때 UPDATE 쿼리의 WHERE 조건에 파티션 키 컬럼이 조건으로 존재한다면 그 값을 이용해 레코드가 저장된 파티션을 찾아서 대상 레코드를 검색할 수 있다.

하지만 WHERE 조건에 파티션 키 컬럼의 조건이 명시되지 않았다면 MySQL 서버는 변경 대상 레코드를 찾기 위해 테이블의 모든 파티션을 검색해야 한다. 그리고 실제 레코드의 컬럼을 변경하는 작업의 절차는 UPDATE 쿼리가 어떤 컬럼의 값을 변경하는지에 따라 큰 차이가 생긴다.

- 파티션 키 이외의 컬럼만 변경될 때는 파티션이 적용되지 않은 일반 테이블과 마찬가지로 컬럼 값만 변경한다.

- 파티션 키 컬럼이 변경될 때는 기존의 레코드가 저장된 파티션에서 해당 레코드를 삭제한다. 그리고 변경되는 파티션 키 컬럼의 표현식을 평가하여 그 결과를 이요해 레코드를 이동시킬 새로운 파티션을 결정해서 레코드를 저장한다.

3) 파티션 테이블의 검색

SQL이 수행되기 위해 파티션 테이블을 검색할 때 성능에 크게 영향을 미치는 조건은 다음과 같다.

- WHERE 절의 조건으로 검색해야 할 파티션을 선택할 수 있는가?

- WHERE 절의 조건이 인덱스를 효율적으로 사용(Index Range Scan)할 수 있는가?

두 번째 내용은 파티션 테이블뿐 아니라 파티션되지 않은 일반 테이블의 검색 성능에도 똑같이 영향을 미친다.

하지만 파티션 테이블에서는 첫 번째 선택사항의 결과에 의해 두 번째 선택사항의 작업내용이 달라 질 수 있다.

[파티션 선택 가능 + 인덱스 효율적 사용 가능]

두 선택사항이 모두 가능할 때 쿼리가 가장 효율적으로 처리된다. 파티션의 개수에 관계없이 검색을 위해 꼭 필요한 파티션의 인덱스만 레인지 스캔한다.

[파티션 선택 불가 + 인덱스 효율적 사용 가능]

모든 파티션의 개수만큼 인덱스 레인지 스캔을 수행해서 검색하게 된다. 이 작업은 파티션의 개수만큼의 테이블에 대해 인덱스 레인지 스캔을 한 다음 결과를 병합해서 가져오는 것과 같다.

파티션의 개수가 많을 때는 MySQL 서버의 부하도 높아지고 처리 시간도 많이 느려진다.

[파티션 선택 가능 + 인덱스 효율적 사용 불가]

검색을 위해 필요한 대상 파티션에 대해 풀 테이블 스캔을 한다. 대상 파티션의 레코드 건수가 많다면 상당히 느리게 처리될 것이다.

[파티션 선택 불가 + 인덱스 효율적 사용 불가]

모든 파티션을 검색하며 각 파티션에 대해서도 풀 테이블 스캔을 수행해야 한다. 이 방식은 파티션을 만든 의미가 없어지므로 피해야 한다.

MySQL에서 PK가 테이블에 존재한다면 파티션키는 PK에 포함되어야 하고 조건절에도 사용되어야 파티션 프루닝이 작동되어 효율적인 검색을 할 수 있다는 사실을 기억하자.

4) 파티션 테이블의 인덱스 스캔과 정렬

파티션 테이블에서 인덱스는 전부 로컬 인덱스에 해당한다. 모든 인덱스는 파티션 단위로 생성되며, 파티션에 관계없이 테이블 전체 단위로 글로벌하게 하나의 통합된 인덱스는 지원하지 않는다.

일반 테이블에서는 인덱스를 순서대로 읽으면 그 컬럼으로 정렬된 결과를 바로 얻을 수 있지만 파티션된 테이블에서는 파티션의 키가 아닌 인덱스의 선두 컬럼으로 정렬되어 있으므로 그것을 고려하여 쿼리를 작성하자.

select * from titles_part1 partition(p1985) limit 10;

titles_part1 PK : emp_no, from_date

titles_part1 테이블의 파티션은 emp_no 순서대로 정렬되어 있다. PK 구성요소의 제일 앞 컬럼의 순서대로 구성되어 있다.

select *
  from titles_part1
 where from_date between '1985-01-01' and '1985-12-31' limit 10;

인덱스 선두 컬럼인 emp_no 조건이 없기 때문에 p1985 PARTITION을 FULL Table Scan 한다.

결과는 인덱스 선두컬럼 순서대로 정렬되어 나온다.

select * from titles_part2 partition(p1985) limit 10;

titles_part2 PK : from_date, emp_no

titles_part2 테이블의 파티션은 from_date 순서대로 정렬되어 있다. PK 구성요소의 제일 앞 컬럼의 순서대로 구성되어 있다.

select *
from titles_part2
where from_date between '1985-01-01' and '1985-12-31' limit 10;

인덱스 선두 컬럼인 from_date 조건이 있기 때문에 p1985 PARTITION을 Index Range Scan 한다.

결과는 인덱스 선두컬럼 순서대로 정렬되어 나온다.

[두개의 파티션을 읽을 때 정렬작업 대신 emp_no, from_date 인덱스를 사용하여 데이터를 읽어 온다.]

explain
select *
from titles_part1
where from_date between '1985-01-01' and '1986-12-31'
order by emp_no
limit 10;

[두개의 파티션을 읽을 때 from_date로 정렬 시 정렬작업을 수행한다]

explain
select *
from titles_part1
where from_date between '1985-01-01' and '1986-12-31'
order by from_date
limit 10;

5) 파티션 프루닝

옵티마이저에 의해 최적화 단계에서 필요한 파티션만 골라내어 불필요한 것들은 실행 계획에서 배제하는 것을 파티션 프루닝(Partition Pruning)이라고 한다. 이러한 파티션 프루닝 정보는 실행계획을 확인해보면 옵티마이저가 어떤 파티션만 접근하는지 알 수 있다.

2. 파티션 주의사항

2.1 파티션의 제한사항

MySQL의 파티션에서 인덱스는 글로벌인덱스가 없으므로 모두 로컬 인덱스이며, 같은 테이블에 소속돼 있는 모든 파티션은 같은 구조의 인덱스만 가질 수 있다. 즉 파티션 단위로 인덱스를 변경하거나 추가할 수 없다. 하나의 테이블에 소속된 파티션은 다른 종류의 스토리지 엔진으로 구성하는 것을 추천하지 않는다.

숫자 값(INTEGER 타입 컬럼 또는 INTEGER 타입을 반환하는 함수 및 표현식)에 의해서 파티션이 가능
5.5 부터는 파티션 키 값에 정수형 값 이외에 문자열이나 날짜 타입(DATE,DATETIME)도 사용 가능
키 파티션은 해시 함수를 MySQL이 직접 선택하기 때문에 컬럼 타입 제한이 없음
최대 1024개의 파티션을 가질 수 있음(서브 파티션까지 포함해서)
스토어드 루틴이나 UDF 그리고 사용자 변수 등을 파티션 함수나 식에 사용할 수 없음
파티션 생성 이후 MySQL 서버의 sql_mode 파라미터 변경은 추천하지 않음
파티션 테이블에서는 외래키 사용 불가
파티션 테이블은 전문 검색 인덱스 생성 불가
공간 확장 기능에서 제공되는 컬럼 타입(POINT, GEOMETRY,...)은 파티션 테이블에서 사용 불가
임시 테이블(Temporary table)은 파티션 기능 사용 불가
MyISAM 파티션 테이블의 경우 키 캐시를 사용할 수 없음(MySQL 5.5부터는 이 버그는 보완됨)
파티션 키의 표현식은 일반적으로 컬럼 그 자체 또는 MySQL 내장 함수를 사용할 수 있는데, 여기서 MySQL 내장 함수를 모두 사용할 수 있는 것이 아니라 일부만 사용할 수 있다. (자세한 함수 목록은 현재 사용중인 MySQL 버전의 메뉴얼을 참고하자). 하지만 이 함수 중에서도 정상적으로 파티션 프루닝(Pruning)을 지원하는 함수는 YEAR()와 TO_DAYRS(), 그리고 TO_SECONDES()(MySQL5.5) 밖에 없으므로 제대로 파티션의 기능을 이용하려고 한다면 INTEGER 타입의 컬럼 그 자체 또는 이 3가지 내장 함수를 사용한 표현식을 파티션 키로 사용할 것을 권장한다.

파티션 제약사항이 버전별로 바뀌기 때문에 사용하는 버전에서 파티션을 생성하고 파티션 프루닝 기능을 확인해 보자.

https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations-functions.html

MySQL :: MySQL 5.7 Reference Manual :: 22.6.3 Partitioning Limitations Relating to Functions

22.6.3 Partitioning Limitations Relating to Functions This section discusses limitations in MySQL Partitioning relating specifically to functions used in partitioning expressions. Only the MySQL functions shown in the following list are allowed in partiti

dev.mysql.com

In MySQL 5.7, partition pruning is supported for the TO_DAYS(), TO_SECONDS(), YEAR(), and UNIX_TIMESTAMP() functions. See Section 22.4, “Partition Pruning”, for more information.

[UNIX_TIMESTAMP() 파티션 예제]

select min(from_date), max(from_date), max(to_date) from titles;

- datetime은 8bytes timestamp는 4bytes

-- timestamp는 1970-01-01 00:00:00 ~ 2038-01-19 03:14:00 까지 가능하다.

select timestamp('2021-01-01') ; -- 반환 값은 datetime

select UNIX_TIMESTAMP(); -- 1970-01-01 00:00:00 부터 정수값 반환, 인수 없으면 현재 일시의 타입스탬프 값

select FROM_UNIXTIME(UNIX_TIMESTAMP()); -- 타임스탬프를 인수로 받아서 datetime 반환

drop table titles_part3;

create table titles_part3(
emp_no int not null,
title varchar(50) not null,
from_date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
to_date date default null,
primary key(emp_no, from_date)
)

PARTITION BY RANGE ( UNIX_TIMESTAMP(from_date) ) (
PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('1987-01-01 00:00:00') ),
PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('1989-01-01 00:00:00') ),
PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('1991-01-01 00:00:00') ),
PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('1993-01-01 00:00:00') ),
PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('1994-01-01 00:00:00') ),
PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('1997-01-01 00:00:00') ),
PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('1999-01-01 00:00:00') ),
PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2001-01-01 00:00:00') ),
PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2003-01-01 00:00:00') ),
PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

insert into titles_part3 select * from titles;

explain
select *
from titles_part3
where from_date between '1985-01-01' and '1986-12-31 00:00:00'
order by from_date
limit 10;

2.2 파티션 사용 시 주의사항

파티션 테이블의 경우 프라이머리 키를 포함한 유니크 키에 대해서는 제약사항이 있다. 파티션의 목적이 작업의 범위를 좁히기 위함인데, 유니크 인덱스는 중복 레코드에 대한 체크 작업 때문에 범위가 좁혀지지 않기 때문이다. 또한 MySQL의 파티션 또한 테이블과 같이 별도의 파일로 관리되기 때문에 MySQL 서버가 조작할 수 있는 파일의 개수와도 연관된 제약이 있다.

1) 파티션과 유니크 키(프라이머리 키 포함)

종류에 관계없이 테이블에 유니크 인덱스(프라이머리 키 포함)가 있으면 파티션 키는 모든 유니크 인덱스의 일부 또는 모든 컬럼을 포함해야 한다.

create table tb_partition(
col1 int not null, col2 int not null, col3 int not null,
unique key(col1, col2)
)partition by hash(col3)
partitions 4;

create table tb_partition(
col1 int not null, col2 int not null, col3 int not null,
unique key(col1),
unique key(col2)
)partition by hash(col1+col2)
partitions 4;

create table tb_partition(
col1 int not null, col2 int not null, col3 int not null,
primary key(col1),
unique key(col2, col3)
)partition by hash(col1+col2)
partitions 4;

create table tb_partition(
col1 int not null, col2 int not null, col3 int not null,
unique key(col1),
unique key(col2),
)partition by range(col1) (
PARTITION p0 VALUES LESS THAN (MAXVALUE)
);

위의 예제는 모두 잘못된 테이블 파티션을 생성하는 방법이다.

- 첫 번째 쿼리는 유니크 키와 파티션 키과 전혀 연관이 없기 때문에 불가능하다.

- 두 번째 쿼리는 첫 번째 유니크 키 컬럼인 col1 만으로 파티션이 결정이 되지 않는다.(col2 컬럼 값도 같이 있어야

파티션의 위치를 판단할 수 있다) 두 번째 유니크 키 또한 첫 번째와 같은 이유로 불가능하다.

- 세 번째 쿼리는 또한 두 번째 쿼리와 같이 프라이머리 키 컬럼인 col1 값만으로 파티션 판단이 되지 않으며, 유니크 키인 col2와 col3로도 파티션 위치를 결정할 수 없다.

이제 파티션 키로 사용할 수 있는 예제를 몇 개 살펴보자.

create table tb_partition1(
col1 int not null, col2 int not null, col3 int not null,
unique key(col1, col2, col3)
)partition by hash(col1)
partitions 4;

create table tb_partition2(
col1 int not null, col2 int not null, col3 int not null,
unique key(col1, col2)
)partition by hash(col1+col2)
partitions 4;

create table tb_partition3(
col1 int not null, col2 int not null, col3 int not null,
unique key(col1, col2, col3),
unique key(col3)
)partition by hash(col3)
partitions 4;

위 예제에서 테이블의 파티션 키는 모든 유니크 인덱스의 일부 또는 모든 컬럼을 포함하여 만들어졌다.

2) 파티션과 open_files_limit 파라미터

MySQL에서는 일반적으로 테이블을 파일 단위로 관리하기 때문에 MySQL 서버에서 동시에 오픈된 파일의 개수가 상당히 많아질 수 있다. 이를 제한하기 위해 open-files-limit 시스템 변수에 동시에 오픈할 수 있는 적절한 파일의 개수를 설정할 수 있다. 파티션되지 않은 일반 테이블은 테이블 1개당 오픈된 파일의 개수가 2~3개 수준이지만 파티션 테이블에서는 (파티션 개수 * 2~3)개가 된다. 예를들어, 파티션이 1,024개 포함된 테이블 이라고 하자. 쿼리가 적절히 파티션 프루닝으로 최적화되어 1,024개의 파티션 가운데 2개의 파티션만 접근해도 된다고 하더라도 일단 동시에 모든 파티션의 데이터 파일이 오픈돼야 한다. 그래서 파티션을 많이 사용하는 경우에는 open-files-limit 을 적절히 높은 값으로 다시 설정해 줄 필요가 있다.

3) 파티션 테이블과 잠금

MySQL에서는 파티션 테이블이 가지는 파티션의 개수가 늘어날수록 성능이 더 떨어질 수도 있다. 예를 들어, 파티션이 350개 정도인 테이블에 10,000건의 레코드를 INSERT 하면 오히려 파티션이 없는 테이블의 INSERT가 30% 정도 더 빠르게 처리된다. 이러한 성능 차이는 테이블의 파티션 개수에 따라 더 커질 수도 있다.

MySQL에서 파티션 테이블에 쿼리가 수행되면 우선 테이블을 열고 잠금을 걸고 쿼리의 최적화를 수행한다. 쿼리의 처리에 필요한 파티션만 파티션 프루닝 작업은 쿼리의 최적화 단계에서 수행되므로 테이블을 열고 잠금을 거는 시점에서는 어떤 파티션만 사용될지 MySQL 서버가 알아내는 방법이 없다. 그래서 파티션된 테이블을 열고 잠금을 거는 작업은 파티션 프루닝이 적용되지 않는다. 즉 파티션 테이블에 쿼리가 실행되면 MySQL 서버는 테이블의 파티션 개수에 관계없이 모든 파티션을 열고 잠금을 걸게 된다. 이는 테이블의 파티션 개수가 많아지면 많아질수록 더 느려지는 현상이 발생하는 것이다.

여기서 언급한 잠금은 테이블 잠금을 이야기하는 것인데, InnoDB 테이블에서 테이블 잠금은 큰 역할을 수행하지는 않는다. 하지만 여전히 모든 파티션에 테이블 잠금을 거는 추가적인 부하는 피할 수 없다. 만약 파티션이 많이 포함된 테이블에 한 번에 많은 레코드를 INSERT하거나 UPDATE한다면 LOCK TABLES 명령으로 테이블을 잠그고 INSERT나 UPDATE를 수행하면 조금은 더 빠르게 처리할 수 있다.

3. MySQL 파티션의 종류

MySQL에서는 4가지 기본 파티셔닝 기법을 제공하고 있으며, 해시와 키 파티션에 대해서는 리니어(Linear) 파티션과 같은 추가적인 기법도 제공한다.

- RANGE PARTITION

- LIST PARTITION

- HASH PARTITION

- KEY PARTITION

3.1 레인지 파티션

파티션 키의 연속된 범위로 파티션을 정의하는 방법으로, 가장 일반적으로 사용되는 파티션 방법 중 하나다. 다른 파티션 방법과는 달리 MAXVLUES라는 키워드를 이용해 명시되지 않은 범위의 키 값이 담긴 레코드를 저장하는 파티션을 정의할 수 있다.

1) RANGE 파티션의 용도

아래와 같은 성격의 테이블에서는 레인지 파티션을 사용하는 것이 좋다.

- 날짜를 기반으로 데이터가 누적되고 년도나 월 또는 일 단위로 분석하고 삭제해야 할 때

- 범위 기반으로 데이터를 여러 파티션에 균등하게 나눌 수 있을 때

- 파티션 키 위주로 검색이 자주 실행될 때

2) RANGE 파티션 테이블 생성

RANGE 파티션을 이용해서 사원의 입사 일자별로 파티션 테이블을 만드는 방법을 살펴보자.

CREATE TABLE `employees_partition` (
  `emp_no` int NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`,`hire_date`),
  KEY `ix_firstname` (`first_name`),
  KEY `ix_hiredate` (`hire_date`),
  KEY `ix_test` (`emp_no` DESC,`first_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
PARTITION BY RANGE(YEAR(hire_date))(
partition p1985 values less than (1986),
partition p1986 values less than (1987),
partition p1987 values less than (1988),
partition p1988 values less than (1989),
partition p1989 values less than (1990),
partition p1990 values less than (1991),
partition p1991 values less than (1992),
partition p1992 values less than (1993),
partition p1993 values less than (1994),
partition p1994 values less than (1995),
partition p1995 values less than (1996),
partition p1996 values less than (1997),
partition p1997 values less than (1998),
partition p1998 values less than (1999),
partition p1999 values less than (2000),
partition p9999 values less than MAXVALUE
);
insert into employees_partition select * from employees;

- PARTITION BY RANGE 키워드로 레인지 파티션을 정의한다.

- PARTITION BY RANGE 뒤에 컬럼 또는 내장 함수를 이용해 파티션 키를 명시한다. 여기서는 사원의 입사일자에서 년도만을 파티션 키로 사용했다.

- VALUES LESS THAN으로 명시된 값보다 작은 값만 해당 파티션에 저장하도록 설정한다. 단, LESS THAN 절에 명시된 값은 그 파티션에 포함되지 않는다.

- VALUES LESS THAN MAXVALUES 파티션은 선택사항이므로 지정하지 않아도 된다.

- VALUES LESS THAN MAXVALUES가 정의되지 않으면 hire_date 컬럼의 값이 '2021-01-01' 이상인 레코드가 삽입될 때 에러가 발생하면서 "Table has no partition for value 2021"이라는 메시지가 표시된다.

- 테이블과 각 파티션은 같은 스토리지 엔진으로 정의한다. 각 파티션에 ENGINE을 명시하지 않으면 테이블 스토리지 엔진이 자동으로 적용된다. 이 예제에서는 테이블의 스토리지 엔진이 InnoDB이므로 자동으로 모든 파티션의 스토리지 엔진은 InnoDB를 사용할 것이다.

select max(hire_date) from employees_partition partition(p1987);

파티션 p1987에는 hire_date가 1988년 미만의 데이터가 저장된다.

3) RANGE 파티션의 분리와 병합

[1] 단순 파티션의 추가

ALTER TABLE employees_partition ADD PARTITION (PARTITION p2000 VALUES less than (2001));
SQL Error [1481] [HY000]: MAXVALUE can only be used in last partition definition

테이블에 MAXVALUES 파티션이 이미 정의돼 있을 때는 테이블에 새로운 파티션을 추가할 수 없다. 이때는 MAXVALUE 파티션을 분리하는 방법으로 새로운 파티션을 끼워 넣어야 한다.

[2] 단순 파티션의 삭제

ALTER TABLE employees DROP PATITION p1986;

[3] 기존 파티션의 분리

하나의 파티션을 두 개 이상의 파티션으로 분리하고자 할 때는 REORGANIZE PATITION 명령을 사용한다.

아래 예제는 MAXVALUE에 있던 데이터는 파티션 키에 의해 재비치되어 저장된다.

ALTER TABLE employees_partition 
REORGANIZE PARTITION p9999 INTO (
PARTITION p2000 VALUES less than (2001),
partition p9999 values less than MAXVALUE
);

select * from employees_partition partition(p2000);

select * from employees_partition partition(p9999);

[4] 기존 파티션의 병합

여러 파티션을 하나의 파티션으로 병합하는 작업도 REORGANIZE PARTITION 명령으로 처리한다.

ALTER TABLE employees_partition 
REORGANIZE PARTITION p2000, p9999 INTO (
partition p9999 values less than MAXVALUE
);

select * from employees_partition partition(p9999);

4) RANGE 파티션 주의사항

레인지 파티션에서 NULL은 어떤 값보다 작은 값으로 간주된다. 만약 employees_partition 파티션 테이블에 hire_date 컬럼이 NULL인 레코드가 INSERT 된다면 이 레코드는 입사 일자가 가장 작은 값을 저장하는 p1985 파티션으로 저장된다. 하지만 명시적으로 VALUES LESS THAN(NULL)은 사용할 수 없다. 날짜 컬럼의 값으로 파티션을 만들 경우, 다음과 같은 파티션 키를 사용하는 파티셔닝은 피하는 것이 좋다.

- UNIX_TIMESTAMP()를 이용한 변환 식을 파티션 키로 사용(5.7부터 파티션 프루닝 작동)

- 날짜를 문자열로 포맷팅한 형태('2020-12-31')의 파티션 키

- YEAR()나 TO_DAYS() 함수 이외의 함수가 사용된 파티션 키

위와 같은 표현식으로 파티션된 테이블에서는 MySQL의 파티션 프로닝이 정상적으로 작동하지 않을 수 도 있다.

NULL 삽입 후 저장되는 위치 테스트 -> PK를 이루는 컬럼은 모두 NOT NULL 이어야 한다.

SQL Error [1171] [42000]: All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead

CREATE TABLE `employees_partition1` (
  `emp_no` int NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) NOT NULL,
  `last_name` varchar(16) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `hire_date` date NULL,
  UNIQUE KEY (`emp_no`,`hire_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
PARTITION BY RANGE(YEAR(hire_date))(
partition p1985 values less than (1986),
partition p1986 values less than (1987),
partition p1987 values less than (1988),
partition p1988 values less than (1989),
partition p1989 values less than (1990),
partition p1990 values less than (1991),
partition p1991 values less than (1992),
partition p1992 values less than (1993),
partition p1993 values less than (1994),
partition p1994 values less than (1995),
partition p1995 values less than (1996),
partition p1996 values less than (1997),
partition p1997 values less than (1998),
partition p1998 values less than (1999),
partition p1999 values less than (2000),
partition p9999 values less than MAXVALUE
);
insert into employees_partition1 values(10000, '1994-06-02', 'Test','NullValue', 'M', NULL);

[파티션키가 NULL일때 데이터 저장 위치 확인 --> 첫 번째 파티션 p1985]

select * from employees_partition1 partition(p1985);

[파티션 프루닝 되는지 실행계획 확인]

explain 
select * from employees_partition1 where hire_date is null ;

3.2 리스트 파티션

레인지 파티션은 연속된 값의 범위로 파티션을 구성 할 수 있지만 리스트 파티션은 파티션 키 값 하나하나를 리스트로 나열해야 한다. 또한 리스트 파티션에서는 레인지 파티션과 같이 MAXVALUES 파티션을 정의할 수 없다.

1) LIST 파티션의 용도

테이블이 아래와 같은 특성을 지닐 때는 리스트 파티션으로 사용하는 것이 좋다. 마지막 항목은 모든 파티션에 공통적인 사항이지만 RANGE or LIST PARITITION 에 더 필요한 사항이다.

- 파티션 키 값이 코드 값이나 카테고리와 같이 고정적일 때

- 키 값이 연속되지 않고 정렬 순서와 관계없이 파티션을 해야 할 때

- 파티션 키 값을 기준으로 레코드 건수가 균일하고, 검색 조건에 파티션 키가 자주 사용될 때

2) LIST 파티션 테이블 생성

CREATE TABLE product(
id int NOT NULL,
name VARCHAR(30),
category_id INT NOT NULL
)
PARTITION BY LIST(category_id)( 
PARTITION LIST1 VALUES IN (1,2), 
PARTITION LIST2 VALUES IN (3,4,5),
PARTITION LIST3 VALUES IN (6,7,8),
PARTITION LIST4 VALUES IN (9,0,NULL)
);

위의 예제는 리스트 파티션 테이블을 생성하는 명령이다.

- PARTITION BY LIST 키워드로 생성할 파티션이 리스트 파티션이라는 것을 명시한다.

- PARTITION BY LIST 키워드 뒤에 파티션 키를 정의한다. 이 에제에서는 INT 타입의 category_id 컬럼 값을 그대로 파티션 키로 사용하고 있다.

- VALUES IN (...) 을 사용해 각 파티션 별로 저장할 파티션 키 값의 목록을 나열한다.

- 위 예제의 마지막 파티션과 같이 파티션별로 저장할 키 값 중에 NULL을 명시할 수도 있다.

- 레인지 파티션과 달리, 마지막 파티션의 저장 범위 값을 초과하는 데이터를 저장하는 MAXVALUE 파티션은 정의 할 수 없다.

LIST columns 파티셔닝

LIST 파티셔닝의 변형으로 파티션키로 정수형이 아닌 데이터 유형을 사용될 수 있도록 한다.

drop table if exists favorite;
CREATE TABLE favorite(
id int NOT NULL,
name VARCHAR(30),
category_id VARCHAR(20) NOT NULL
)
PARTITION BY LIST COLUMNS(category_id)( 
PARTITION watching VALUES IN ('TV', 'Utube'), 
PARTITION computer VALUES IN ('Notebook','Desktop'),
PARTITION exercise VALUES IN ('Tennis', 'Soccer'),
PARTITION animal VALUES IN ('cat','squirrel',null)
);

3) LIST 파티션의 분리와 병합

파티션을 정의하는 부분에서 VALUES LESS THAN이 아닌 VALUES IN을 사용한다는 것 말고는 레인지 파티션의 추가, 삭제, 병합 작업이 모두 간다. 그리고 특정 파티션의 레코드 건수가 많아져서 두 개 이상의 파티션으로 분리하거나 그 반대로 병합하려면 REORGANIZE PARTITION 명령을 사용하면 된다.

drop table if exists product;
CREATE TABLE product(
id int NOT NULL,
name VARCHAR(30),
category_id INT NOT NULL
)
PARTITION BY LIST(category_id)( 
PARTITION LIST1 VALUES IN (1,2), 
PARTITION LIST2 VALUES IN (3,4,5),
PARTITION LIST3 VALUES IN (6,7,8),
PARTITION LIST4 VALUES IN (9,0,NULL)
);
-- add
alter table product add partition (partition LIST5 values in (11));
-- drop
alter table product drop partition LIST4;

-- split
alter table product 
reorganize partition LIST3 into ( 
partition LIST3 values in (6),
partition LIST4 values in (7,8)
);

-- coalition
alter table product 
reorganize partition LIST1, LIST2 INTO(
partition LIST2 values in (1,2,3,4,5)
);

-- result
show create table product;

CREATE TABLE `product` (
  `id` int NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `category_id` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY LIST (`category_id`)
(PARTITION LIST2 VALUES IN (1,2,3,4,5) ENGINE = InnoDB,
 PARTITION LIST3 VALUES IN (6) ENGINE = InnoDB,
 PARTITION LIST4 VALUES IN (7,8) ENGINE = InnoDB,
 PARTITION LIST5 VALUES IN (11) ENGINE = InnoDB) */

/*!50100 */ MySQL5.01.00 버전 이상이면 해당 주석을 실행해라

4) LIST 파티션 주의사항

명시되지 않은 나머지 값을 저장하는 MAXVALUE 파티션을 정의할 수 없다.

RANGE PARTITION 과는 달리 NULL을 저장하는 파티션을 별도로 생성할 수 있다.

MySQL 5.1 버전에서 파티션 키는 정수 타입의 컬럼 또는 정수 타입을 반환하는 표현식만 사용할 수 있지만 MySQL 5.5 이상부터는 문자열 타입의 컬럼도 파티션 키로 사용할 수 있다.

3.3 해시 파티션

해시 파티션은 MySQL에서 정의한 해시 함수에 의해 레코드가 저장될 파티션을 결정하는 방법이다. 해시 함수는 복잡한 알고리즘이 아니라 파티션 표현식의 결과 값을 파티션의 개수로 나눈 나머지로 저장될 파티션을 결정하는 방식이다.

해시 파티션의 파티션 키는 항상 정수 타입의 컬럼이거나 정수를 반환하는 표현식만 사용할 수 있다. 해시 파티션에서 파티션의 개수는 레코드를 각 파티션에 할당하는 알고리즘과 연관되기 때문에 파티션을 추가하거나 삭제하는 작업에는 테이블 전체적으로 레코드를 재분배하는 작업이 따른다.

1) HASH 파티션의 용도

해시 파티션은 다음과 같은 특성을 지닌 테이블에 적합하다.

- 레인지 파티션이나 리스트 파티션으로 데이터를 균등하게 나누는 것이 어려울 때

- 테이블의 모든 레코드가 비슷한 사용 빈도를 보이지만 테이블이 너무 커서 파티션을 적용해야 할 때

해시 파티션과 키 파티션의 대표적인 용도로는 회원 테이블을 들 수 있다. 회원 정보는 가입 일자가 오래돼서 사용되지 않는다거나 최신이어서 더 빈번하게 사용되거나 하지 않느다. 또한 회원의 지역이나 취미와 같은 정보 또한 사용 빈도에 미치는 영향이 거의 없다. 이처럼 테이블의 데이터가 특정 컬럼의 값에 영향을 받지 않고, 전체적으로 비슷한 사용 빈도를 보일 때 적합한 파티션 방법이다.

2) HASH 파티션 테이블 생성

-- // 파티션의 개수만 지정할 때 
create table tb_partition(
id int not null,
first_name varchar(30),
last_name varchar(30),
hired date not null default '1970-01-01'
)
partition by hash(id)
partitions 4;

show create table tb_partition;
CREATE TABLE `tb_partition` (
  `id` int NOT NULL,
  `first_name` varchar(30) DEFAULT NULL,
  `last_name` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`id`)
PARTITIONS 4 */

-- // 파티션의 이름을 별도로 지정하고자 할 때
create table tb_partition_naming(
id int not null,
first_name varchar(30),
last_name varchar(30),
hired date not null default '1970-01-01'
)
partition by hash(id)
partitions 4(
partition p0,
partition p1,
partition p2,
partition p3
);

show create table tb_partition_naming;
CREATE TABLE `tb_partition_naming` (
  `id` int NOT NULL,
  `first_name` varchar(30) DEFAULT NULL,
  `last_name` varchar(30) DEFAULT NULL,
  `hired` date NOT NULL DEFAULT '1970-01-01'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`id`)
(PARTITION p0 ENGINE = InnoDB,
 PARTITION p1 ENGINE = InnoDB,
 PARTITION p2 ENGINE = InnoDB,
 PARTITION p3 ENGINE = InnoDB) */

해시 파티션으로 테이블을 생성하는 위의 예제를 간단히 살펴보자.

[1] PARTITION BY HASH 키워드로 파티션 종류를 해시 파티션으로 지정한다.

[2] PARTITION BY HASH 키워드 뒤에 파티션 키를 명시한다. 해시 파티션의 파티션 키는 MySQL 서버 5.1 그리고 5.5 모두 정수 타입의 컬럼이나 표현식만 사용할 수 있다.

[3] PARTITIONS n 으로 몇 개의 파티션을 생성할 것인지 명시한다. 파티션의 개수는 1024개이다.

MySQL5.7 or MySQL8.8 에서 최대 파티션수는 8192개 이다.

Maximum number of partitions. The maximum possible number of partitions for a given table not using the NDB storage engine is 8192. This number includes subpartitions.

[4] 파티션의 개수 뿐만 아니라 각 파티션의 이름을 명시하려면 "PARTITION BY HASH(id) (" 뒤에 각 파티션을 나열하면 된다. 하지만 해시나 키 파티션에서는 특정 파티션을 삭제하거나 병합하는 작업이 거의 불필요하므로 파티션의 이름을 부여하는 것이 크게 의미가 없다. 만약 파티션의 개수만 지정하면 각 파티션의 이름은 기본적으로 "p0, p1, p2, p3, ..." 과 같은 규칙으로 생성된다.

SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
  FROM INFORMATION_SCHEMA.PARTITIONS
 WHERE TABLE_NAME = 'tb_partition';

3) HASH 파티션의 분리와 병합

해시 파티션의 분리와 병합은 리스트 파티션이나 레인지 파티션과는 달리, 대상 테이블의 모든 파티션에 저장된 레코드를 재분배하는 작업이 필요하다. 파티션의 분리나 병합으로 인해 파티션의 개수가 변경된다는 것은 해시 함수의 알고리즘을 변경하는 것이므로 전체적인 파티션이 영향을 받는 것은 피할 수 없다.

[1] 해시 파티션 추가

해시 파티션은 특정 파티션 키 값을 테이블의 파티션 개수로 MOD 연산한 결과 값에 의해 각 레코드가 저장될 파티션을 결정한다. 즉, 해시 파티션은 테이블에 존재하는 파티션의 개수에 의해 파티션 알고리즘이 변하는 것이다. 따라서 새로이 파티션이 추가된다면 기존의 각 파티션에 저장된 모든 레코드가 재배치돼야 한다. 다음 예제와 같이 해시 파티션을 새로 추가할 때는 별도의 영역이나 범위는 명시하지 않고 몇 개의 파티션을 더 추가할 것인지만 지정하면 된다.

-- 파티션 1개 추가, 파티션 이름을 부여
alter table tb_partition_naming add partition(partition p4);
-- 파티션 3개 추가
alter table tb_partition_naming add partition partitions 3;
-- 확인
SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
  FROM INFORMATION_SCHEMA.PARTITIONS
 WHERE TABLE_NAME = 'tb_partition_naming';

해시 파티션이 사용되는 테이블에 새로운 파티션을 추가하면 기존 모든 파티션에 저장돼 있던 레코드를 새로운 파티션으로 재분배하는 작업이 발생한다. 즉, 해시 파티션에서 파티션을 추가하거나 생성하는 작업은 많은 부하를 발생시킨다.

[2] 해시 파티션 삭제

해시나 키 파티션은 파티션 단위로 레코드를 삭제하는 방법이 없다. 해시나 키 파티션을 사용하는 테이블에서 특정 파티션을 삭제하려고 하면 다음과 같은 에러 메시지가 발생하면서 종료될 것이다.

alter table tb_partition drop partition p0;
SQL Error [1512] [HY000]: DROP PARTITION can only be used on RANGE/LIST partitions

MySQL 서버가 지정한 파티션 키 값을 가공해서 데이터를 각 파티션으로 분산한 것이므로 각 파티션에 저장된 레코드가 어떤 부류의 데이터인지 사용자가 예측할 수가 없다. 결국 해시 파티션이나 키 파티션을 사용한 테이블에서 파티션 단위로 데이터를 삭제하는 작업은 의미도 없으며 해서도 안 될 작업이다.

[3] 해시 파티션 분할

해시 파티션이나 키 파티션에서 특정 파티션을 두 개 이상의 파티션으로 분할하는 기능은 없으며, 테이블 전체적으로 파티션의 개수를 늘리는 것만 가능하다.

[4] 해시 파티션 병합

해시나 키 파티션은 2개 이상의 파티션을 하나의 파티션으로 통합하는 기능을 제공하지 않는다. 단지 파티션의 개수를 줄이는 것만 가능하다. 파티션의 개수를 줄일 때는 COALESCE PARTITION 명령을 사용하면 된다. 명령어 자체로만 보명 파티션을 통합하는 것처럼 보이지만 원래 파티션 8개로 구성된 테이블에 다음 명령이 실행되면 6개의 파티션을 가진 테이블로 다시 재구성하는 작업이 수행된다.

alter table tb_partition_naming coalesce partition 2;

SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
  FROM INFORMATION_SCHEMA.PARTITIONS
 WHERE TABLE_NAME = 'tb_partition_naming';

COALESCE PARTITION 뒤에 명시한 숫자 값은 줄이고자 하는 파티션의 개수를 의미한다. 즉, 원래 employees 테이블이 8개의 파티션으로 구성돼 있었다면 COALESCE PARTITION 명령은 이 테이블이 파티션을 6개만 사용하도록 변경할 것이다. 하지만 삭제되는 파티션에 저장돼 있던 레코드가 남은 6개의 파티션으로 복사되는 것이 아니라 테이블의 모든 레코드가 재배치되는 작업이 수행돼야 한다.

4) HASH 파티션 주의사항

- 특정 파티션만 DROP 하는 것은 불가능하다.

- 새로운 파티션을 추가하는 작업은 단순히 파티션만 추가하는 것이 아니라 기존의 모든 데이터의 재배치 작업이 필요하다.

- 해시 파티션은 레인지 파티션이나 리스트 파티션과는 상당히 다른 방식으로 관리하기 때문에 해시 파티션이 용도에 적합한 해결책인지 확인이 필요하다.

- 일반적으로 사용자들에게 익숙한 파티션의 조작이나 특성은 대부분 리스트 파티션이나 레인지 파티션에 제한적인 것들이 많다. 해시 파티션이나 키 파티션을 사용하거나 조작할 때는 주의가 필요하다.

3.4 키 파티션

키 파티션은 해시 파티션과 사용법과 특성이 거의 같다. 해시 파티션은 해시 값을 계산하는 방법을 파티션 키나 표현식에 사용자가 명시한다. 물론, MySQL 서버가 그 값을 다시 MOD 연산을 수행해서 최종 파티션을 결정한다. 하지만 키 파티션에서는 해시 값의 계산도 MySQL 서버가 수행한다. 키 파티션에서는 정수 타입이나 정수 값을 반환하는 표현식뿐 아니라 거의 대부분의 데이터 타입에 대해 파티션 키를 적용할 수 있다. MySQL 서버는 선정된 파티션 키의 값을 MD5() 함수를 이용해 해시 값을 계산하고, 그 값을 MOD 연산해서 데이터를 각 파티션에 분배한다. 이것이 키 파티션과 해시 파티션의 유일한 차이점이다.

1) KEY 파티션 테이블 생성

create table k1(
id int not null,
name varchar(20),
primary key(id)
)
-- 괄호의 내용을 비워두면 PK의 모든 컬럼이 파티션 키가 된다.
partition by key() 
partitions 2;


create table k2(
id int not null,
name varchar(20),
unique key(id)
)
-- 괄호의 내용을 비워두면 PK가 없는 경우 unique 키의 모든 컬럼이 파티션 키가 된다.
partition by key() 
partitions 2;

create table k3(
id int not null,
name varchar(20) not null,
unique key(id, name)
)
-- PK의 일부로 파티션 키를 지정
partition by key(name) 
partitions 2;

create table k4(
id int not null,
name varchar(20) not null,
unique key(id, name)
)
-- UK의 일부로 파티션 키를 지정
partition by key(name) 
partitions 2;

create table k5(
id int not null,
name varchar(20) not null
)
-- PK, UK 없다면 컬럼을 파티션 키로 지정
partition by key(id) 
partitions 2;

키 파티션을 생성하는 위의 예제를 간단히 살펴보자.

- PARTITION BY KEY 키워드로 키 파티션을 정의한다.

- PARTITION BY KEY 키워드 뒤에 파티션 키 컬럼을 명시한다. 첫 번째나 두 번째 예제와 같이 PARTITION BY KEY()에 아무 컬럼도 명시하지 않으면 MySQL 서버가 자동으로 프라이머리 키나 유니크 키의 모든 컬럼을 파티션 키로 선택한다. 만약 테이블에 프라이머리 키가 있다면 프라이머리 키의 모든 컬럼으로, 프라이머리 키가 없는 경우에는 유니크 인덱스의 모든 컬럼으로 파티션 키를 구성한다.

- MySQL 5.1.6부터는 예제의 세번째 쿼리와 같이 프라이머리 키나 유니크 키를 구성하는 컬럼들 중에서 일부만 파티션 키로 명시하는 것도 가능하다.

- PARTITIONS 키워드로 생성할 파티션 개수를 지정한다.

- 키 파티션은 MySQL 서버가 내부적으로 MD5() 함수를 이용해 파티셔닝하기 때문에 파티션 키가 반드시 정수 타입이 아니어도 된다. 해시 파티션으로 파티셔닝이 어렵다면 키 파티션 적용을 고려해보자.

- MySQL 5.1.6 미만의 버전에서 파티션 키는 항상 프라이머리 키나 유니크 키의 모든 컬럼을 명시해야 한다. 하지만 MySQL 5.1.6 부터는 프라이머리 키나 유니크 키를 구성하는 컬럼 중 일부만으로도 파티션할 수 있다.

- 유니크 키를 파티션 키로 사용할 때 해당 유니크 키는 반드시 NOT NULL 이어야 한다.

- 해시 파티션에 비해 파티션 간의 레코드를 더 균등하게 분할할 수 있기 때문에 키 파티션이 더 자주 사용된다.

2) KEY 파티션의 분리와 병합

키 파티션도 해시 파티션과 마찬가지로 분할과 삭제는 불가능하다. 파티션의 추가와 병합만 가능하며 이 때 테이블의 모든 레코드가 재배치되는 작업이 수행된다.

[1] 키 파티션 추가

create table tb_key_partition(
id int not null,
first_name varchar(30),
last_name varchar(30),
hired date not null default '1970-01-01',
primary key(id, first_name)
)
partition by key(first_name)
partitions 4;

-- 파티션 1개 추가, 파티션 이름을 부여
alter table tb_key_partition add partition(partition p4);
-- 파티션 3개 추가
alter table tb_key_partition add partition partitions 3;
-- 확인
SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_ORDINAL_POSITION, TABLE_ROWS
  FROM INFORMATION_SCHEMA.PARTITIONS
 WHERE TABLE_NAME = 'tb_key_partition';

[2] 키 파티션 삭제

alter table tb_key_partition drop partition p0;
SQL Error [1512] [HY000]: DROP PARTITION can only be used on RANGE/LIST partitions;

[3] 키 파티션 병합

alter table tb_key_partition coalesce partition 2;
SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
  FROM INFORMATION_SCHEMA.PARTITIONS
 WHERE TABLE_NAME = 'tb_key_partition';

3.5 리니어 해시 파티션 /리니어 키 파티션

해시 파티션이나 키 파티션은 새로운 파티션을 추가하거나 파티션을 통합해서 개수를 줄일 때 대상 파티션만이 아니라 테이블의 전체 파티션에 저장된 레코드의 재분배 작업이 발생한다. 이러한 단점을 최소화하기 위해 리너어(Linear) 해시 파티션/리니어 키 파티션 알고리즘이 고안된 것이다. 리니어 해시 파티션/리니어 키 파티션은 각 레코드 분배를 위해 "Power-of-two(2의 승수)" 알고리즘을 이용하며, 이 알고리즘은 파티션의 추가나 통합 시 다른 파티션에 미치는 영향을 최소화될 수 있게 해준다.

1) 리니어 해시 파티션/리니어 키 파티션의 추가 및 통합

리니어 해시 파티션이나 리니어 키 파티션의 경우, 단순히 나머지 연산으로 레코드가 저장될 파티션을 결정하는 것이 아니라 "Power-of-two" 분배 방식을 사용하기 때문에 파티션의 추가나 통합 시 특정 파티션의 데이터에 대해서만 이동 작업을 하면 된다. 그래서 파티션을 추가하거나 통합하는 작업에서 나머지 파티션의 데이터는 재분배 대상이 되지 않는 것이다.

[1] 리니어 해시 파티션/리니어 키 파티션의 추가

리니어 해시 파티션이나 리니어 키 파티션에 새로운 파티션을 추가하는 명령은 일반 해시 파티션이나 키 파티션과 동일하다. 하지만 리니어 해시 파티션이나 리니어 키 파티션은 "Power-of-two" 알고리즘으로 레코드가 분배돼 있기 때문에 새로운 파티션을 추가할 때도 특정 파티션의 레코드만 재분배되면 된다. 다른 파티션 데이터는 레코드 재분배 작업과 관련이 없기 때문에 일반 해시 파티션이나 키 파티션의 파티션 추가보다 매우 빠르게 처리할 수 있다.

[2] 리니어 해시 파티션/리니어 키 파티션의 통합

리니어 해시 파티션이나 리니어 키 파티션에서 여러 파티션을 하나의 파티션으로 통합하는 작업 또한 새로운 파티션을 추가할 때와 같이 일부 파티션에 대해서만 레코드 통합 작업이 필요하다. 통합이 되는 파티션만 레코드 이동이 필요하며 나머지 파티션의 레코드는 레코드 재분배 작업에서 제외된다.

2) 리니어 해시 파티션/리니어 키 파티션과 관련된 주의사항

일반 해시 파티션 또는 키 파티션은 데이터 레코드의 배치를 위해 단순히 해시 값의 결과를 파티션 수로 나눈 나머지 값으로 배치하는 데 비해 리니어(Linear) 파티션은 "Power-of-two" 알고리즘을 사용한다. 그래서 파티션을 추가하거나 통합할 때 작업의 범위를 최소화할 수 있는 대신 각 파티션이 가지는 레코드의 건수는 일반 해시 파티션이나 키 파티션보다는 덜 균등해질 수 있다. 해시 파티션이나 키 파티션으로 파티션된 테이블에 대해 새로운 파티션을 추가하거나 삭제해야 할 요건이 많다면 리니어 해시 파티션 또는 리니어 키 파티션을 적용하는 것이 좋다. 만약 파티션을 조정할 필요가 거의 없다면 일반 해시 파티션이나 키 파티션을 사용하는 것이 좋다.

3.6 서브 파티션

1) 복합 파티셔닝

RANGE PARTITION 3개에 각각의 하위에 HASH PARTITION 2개 씩으로 구성된 서브파티션을 생성한다.

CREATE TABLE tb_subpartition(id INT, purchased DATE)
 PARTITION BY RANGE( YEAR(purchased) )
  SUBPARTITION BY HASH( TO_DAYS(purchased) )
  SUBPARTITIONS 2 (
    PARTITION p0 VALUES LESS THAN (1990),
    PARTITION p1 VALUES LESS THAN (2000),
    PARTITION p2 VALUES LESS THAN maxvalue)
;

SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME,SUBPARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
  FROM INFORMATION_SCHEMA.PARTITIONS
 WHERE TABLE_NAME = 'tb_subpartition';

2) SUBPARTITION 을 명시적으로 정의

각 파티션에는 동일한 수의 하위 파티션이 있어야 하며 SUBPARTITON을 명시적으로 정의하는 경우 모든 파티션절에 대해 작성해야 한다. 아래의 경우 3군데 모두 정의 해야 한다.

CREATE TABLE tb_subpartition1(id INT, purchased DATE)
 PARTITION BY RANGE( YEAR(purchased) )
  SUBPARTITION BY HASH( TO_DAYS(purchased))(  
    PARTITION p0 VALUES LESS THAN (1990)(
        SUBPARTITION sub0,
        SUBPARTITION sub1
    ),
    PARTITION p1 VALUES LESS THAN (2000)(
        SUBPARTITION sub2,
        SUBPARTITION sub3
    ),
    PARTITION p2 VALUES LESS THAN maxvalue (
        SUBPARTITION sub4,
        SUBPARTITION sub5
    )
);

SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME,SUBPARTITION_NAME, PARTITION_ORDINAL_POSITION, TABLE_ROWS
  FROM INFORMATION_SCHEMA.PARTITIONS
 WHERE TABLE_NAME = 'tb_subpartition1';

더 자세한 사항은 메뉴얼을 참고 하도록 하자.

3.7 파티션 테이블의 실행계획

파티션 테이블에서 쿼리의 실행에 필요한 파티션만 걸러내는 과정을 파티션 프루닝이라고 하는데 쿼리의 성능은 테이블에서 얼마나 많은 파티션을 프루닝할 수 있는지가 관건이다. 즉 작은 범위의 파티션을 읽는 것이 효율적이다.

CREATE TABLE `employees_part` (
  `emp_no` int NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) COLLATE euckr_bin NOT NULL,
  `last_name` varchar(16) COLLATE euckr_bin NOT NULL,
  `gender` enum('M','F') COLLATE euckr_bin NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`, `hire_date`)
) ENGINE=InnoDB DEFAULT CHARSET=euckr COLLATE=euckr_bin
partition by range(year(hire_date)) (
PARTITION p1985 VALUES LESS THAN (1986),
PARTITION p1986 VALUES LESS THAN (1987),
PARTITION p1987 VALUES LESS THAN (1988),
PARTITION p1988 VALUES LESS THAN (1989),
PARTITION p1989 VALUES LESS THAN (1990),
PARTITION p1990 VALUES LESS THAN (1991),
PARTITION p1991 VALUES LESS THAN (1992),
PARTITION p1992 VALUES LESS THAN (1993),
PARTITION p1993 VALUES LESS THAN (1994),
PARTITION p1994 VALUES LESS THAN (1995),
PARTITION p1995 VALUES LESS THAN (1996),
PARTITION p1996 VALUES LESS THAN (1997),
PARTITION p1997 VALUES LESS THAN (1998),
PARTITION p1998 VALUES LESS THAN (1999),
PARTITION p1999 VALUES LESS THAN (2000),
PARTITION p2000 VALUES LESS THAN MAXVALUE 
);

insert into employees_part select * from employees;

explain
select *
  from employees_part
 where hire_date between '1985-01-01' and '1985-12-31';

위의 쿼리의 실행계획에 p1985 파티션만 읽는 것을 알수 있다. 하지만 인덱스는 사용하지 못하고 풀 테이블 스캔으로 검색된다.

hire_date에 인덱스를 생성하더라도 파티션 프루닝은 일어나도 index range scan은 하지 않는다.

alter table employees_part add index employees_part_ix01(hire_date);
explain
select *
  from employees_part
 where hire_date between '1985-01-01' and '1985-12-31';

일반 인덱스와 마찬가지로 선행 컬럼이 등위조건(in 포함) 으로 쓰여야 Index range scan으로 실행계획이 생성된다.

explain
select *
  from employees_part
 where hire_date between '1985-01-01' and '1985-12-31'
   and emp_no in ('10002', '10013');

인덱스 컬럼을 가공하는 year(hire_date) 형식의 조건은 쓰면 안된다. 그러면 파티션 프루닝도 안되는 심각한 문제가 발생된다.

explain
select *
  from employees_part
 where year(hire_date) = 1985;

파티션을 이용하는 목적은 많은 데이터를 가진 테이블에서 주기적으로 특정 데이터를 삭제한다고 할때 삭제의 유의점이나 특정 범위만 읽어도 되는 파티션 프루닝의 기능을 사용할 수 있다는데 이점이 있다.

파티션의 개수가 적은 테이블에서는 INSERT의 성능이 일반테이블보다 35%정도 빠르게 나타났다. 하지만 파티션이 많은 테이블에서는 INSERT나 UPDATE, 그리고 DELETE 등의 쿼리가 더 느려질 수 있다는 점에 주의하자.

하지만, 병렬처리가 가능하다면 파티션 테이블을 처리하는데 더 많은 성능상의 이점이 있다. 간단한 예로 병렬 프로세스들이 서로 독립적으로 조인을 수행 할 수 있도록 데이터 분배하는 작업을 생략할 수 있다. INSERT 같은 배치 작업의 경우도 1억건의 100개로 나눈 100만건의 파티션이라면 범위를 나눠서 작업하면은 100만건에 대한 INDEX와 TABLE 데이터를 삽입하는 과정에서 LOCK을 점유하는 시간이 줄어들 수 있다. 또한 INDEX의 크기와 depth에 대한 문제도 줄어 들 것이다. 그리고 데이터가 삽입이 완료되거나 삽입되지 않는 부분은 LOCK이 안걸려서 다른 프로세서가 작업을 할 수 있게 되는 이점이 생긴다.

[참고도서] - RealMySQL

[참고사이트]

https://dev.mysql.com/doc/refman/8.0/en/partitioning.html

https://dev.mysql.com/doc/refman/8.0/en/partitioning-subpartitions.html

'MySQL > MySQL' 카테고리의 다른 글

스토어드 함수 (0)	2024.12.13
스토어드 프로시저 (0)	2024.12.13
MySQL 계층쿼리 구현(사용자변수,CTE) (0)	2024.12.10
사용자 정의 변수 (0)	2024.12.10
UPDATE (0)	2024.12.10

MySQL 계층쿼리 구현(사용자변수,CTE)

Tutor.C 2024. 12. 10. 09:56

2024. 12. 10. 09:56

계층쿼리 구현하기

1. Recursive CTE 사용 (MySQL8.0부터 지원)

with recursive cte(empno, ename, job, mgr, lv, pre_empno, pre_ename, by_path)
as(
select empno, ename, job, mgr
,1 as lv
,cast('' as char(10))as pre_empno
,cast('' as char(10))as pre_ename
,cast(empno as char(30)) as by_path -- by_path
from emp
where mgr is null
union all
select c.empno, c.ename, c.job, c.mgr
,p.lv+1 as lv
,p.empno as pre_empno
,p.ename as pre_ename
,concat_ws('>',p.by_path,c.empno) as by_path
from cte p
inner join emp c
on p.empno = c.mgr
)
select lv, empno, ename, job, mgr,pre_empno, pre_ename, by_path
from cte
order by by_path
;

2. 사용자 변수

use employees;
drop table hire;
CREATE TABLE hire (
id int(10) unsigned NOT NULL AUTO_INCREMENT, 
parent_id int(10) unsigned default '0', 
name varchar(50), 
primary key(`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into hire(parent_id, name) values ( 0, '음식');     -- ID:1
insert into hire(parent_id, name) values ( 1, '중식');     -- ID:2
insert into hire(parent_id, name) values ( 1, '한식');     -- ID:3
insert into hire(parent_id, name) values ( 2, '요리');     -- ID:4
insert into hire(parent_id, name) values ( 2, '식사');     -- ID:5
insert into hire(parent_id, name) values ( 3, '찌게');     -- ID:6
insert into hire(parent_id, name) values ( 3, '반찬');     -- ID:7
insert into hire(parent_id, name) values ( 5, '짜장면');    -- ID:8
insert into hire(parent_id, name) values ( 6, '김치찌게');   -- ID:9
insert into hire(parent_id, name) values ( 7, '젓갈');      -- ID:10
insert into hire(parent_id, name) values ( 9, '참치김치찌게'); -- ID:11
insert into hire(parent_id, name) values ( 10,'오징어젓갈');  -- ID:12

select * from hire;

DROP FUNCTION IF EXISTS fnc_hierarchi;
 
DELIMITER $$
 
CREATE FUNCTION  fnc_hierarchi() RETURNS INT
 
NOT DETERMINISTIC
 
READS SQL DATA
 
BEGIN
 
    DECLARE v_id INT;
    DECLARE v_parent INT;    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;
 
    SET v_parent = @id;
    SET v_id = -1;
 
    IF @id IS NULL THEN
        RETURN NULL;
    END IF;
 
    LOOP
    
    SELECT MIN(id)
      INTO @id 
      FROM hire
     WHERE parent_id = v_parent
       AND id > v_id;
 
    IF (@id IS NOT NULL) OR (v_parent = @start_with) THEN
       SET @level = @level + 1;
    RETURN @id;
    END IF;
    
    SET @level := @level - 1;
 
    SELECT id, parent_id
      INTO v_id , v_parent 
        FROM hire
       WHERE id = v_parent;
   
    END LOOP;
 
END
 
$$
 
DELIMITER ;

ID로 계층정보와 LEVEL 정보를 가져온다.

SELECT fnc_hierarchi() AS id, @level AS level
FROM (SELECT @start_with:=0, @id:=@start_with, @level:=0) x
JOIN hire
WHERE @id IS NOT NULL;

조인하여 나머지 정보를 가져온다.

SELECT CASE WHEN LEVEL-1 > 0 then CONCAT_WS('┗',REPEAT(' ', level - 1), h.name)
ELSE h.name
END AS name
, h.id
, h.parent_id
, fn.level
FROM
(SELECT fnc_hierarchi() AS id, @level AS level, name, parent_id
FROM (SELECT @start_with:=0, @id:=@start_with, @level:=0) x
JOIN hire
WHERE @id IS NOT NULL) fn
JOIN hire h ON fn.id = h.id;

SELECT CASE WHEN LEVEL-1 > 0 then CONCAT(CONCAT(REPEAT(' ', level - 1),'┗'), h.name)
ELSE h.name
END AS name
, h.id
, h.parent_id
, fn.level
FROM
(SELECT fnc_hierarchi() AS id, @level AS level, name, parent_id
FROM (SELECT @start_with:=0, @id:=@start_with, @level:=0) x
JOIN hire
WHERE @id IS NOT NULL) fn
JOIN hire h ON fn.id = h.id;

hire1.sql

0.00MB

3. 사용자 변수(emp 테이블로 계층쿼리 작성 하기)

use employees;
drop table IF EXISTS emp_hire;

create table emp_hire (
empno integer(4) not null,
ename varchar(10) not null,
job varchar(9) not null,
mgr integer(4) ,
hiredate date not null,
sal decimal(7,2) not null,
comm decimal(7,2),
deptno integer(10) not null,
primary key(empno) 
) engine=innodb default charset=utf8mb4 collate=utf8mb4_0900_ai_ci;

insert into emp_hire values (7839,'king', 'president',9999,'19811117',5000,null,10);
insert into emp_hire values (7698,'blake', 'manager' ,7839,'19810501',2850,null,30);
insert into emp_hire values (7782,'clark', 'manager' ,7839,'19810509',2450,null,10);
insert into emp_hire values (7566,'jones', 'manager' ,7839,'19810401',2975,null,20);
insert into emp_hire values (7654,'martin','salesman',7698,'19810910',1250,1400,30);
insert into emp_hire values (7499,'allen', 'salesman',7698,'19810211',1600,300, 30);
insert into emp_hire values (7844,'turner','salesman',7698,'19810821',1500,0, 30);
insert into emp_hire values (7900,'james', 'clerk' ,7698,'19811211',950, null,30);
insert into emp_hire values (7521,'ward', 'salesman',7698,'19810223',1250,500, 30);
insert into emp_hire values (7902,'ford', 'analyst' ,7566,'19811211',3000,null,20);
insert into emp_hire values (7369,'smith', 'clerk' ,7902,'19801209',800, null,20);
insert into emp_hire values (7788,'scott', 'analyst' ,7566,'19821222',3000,null,20);
insert into emp_hire values (7876,'adams', 'clerk' ,7788,'19830115',1100,null,20);
insert into emp_hire values (7934,'miller','clerk' ,7782,'19820111',1300,null,10);

select * from emp_hire;

DROP FUNCTION IF EXISTS fn_hier;
 
DELIMITER $$
 
CREATE FUNCTION  fn_hier() RETURNS INT
 
NOT DETERMINISTIC
 
READS SQL DATA
 
BEGIN
 
    DECLARE v_id INT;
    DECLARE v_parent INT;    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;
 
    SET v_parent = @id;
    SET v_id = -1;
 
    IF @id IS NULL THEN
        RETURN NULL;
    END IF;
 
    LOOP
    
    SELECT MIN(empno)
      INTO @id 
      FROM emp_hire
     WHERE mgr = v_parent
       AND empno > v_id;
 
    IF (@id IS NOT NULL) OR (v_parent = @start_with) THEN
       SET @level = @level + 1;
    RETURN @id;
    END IF;
    
    SET @level := @level - 1;
 
    SELECT empno, mgr
      INTO v_id , v_parent 
        FROM emp_hire
       WHERE empno = v_parent;
   
    END LOOP;
 
END
 
$$
 
DELIMITER ;

select * from emp_hire;

SELECT fn_hier() AS empno, @level AS level -- empno 정보와 LEVEL 정보만 나옴
  FROM (SELECT @start_with:=9999, @id:=@start_with, @level:=0) x
       JOIN emp_hire
 WHERE @id IS NOT NULL;

SELECT CASE WHEN LEVEL-1 > 0 then CONCAT(CONCAT(REPEAT('    ', level  - 1),'┗'), h.empno)
                 ELSE fn.empno
           END AS empno
     , h.empno
     , h.mgr
     , fn.level
  FROM
     (SELECT fn_hier() AS empno, @level AS level
        FROM (SELECT @start_with:=9999, @id:=@start_with, @level:=0) x
          JOIN emp_hire
         WHERE @id IS NOT NULL) fn
  JOIN emp_hire h ON fn.empno = h.empno;

SELECT CASE WHEN LEVEL-1 > 0 then CONCAT(CONCAT(REPEAT('    ', level  - 1),'┗'), h.empno)
                 ELSE fn.empno
           END AS empno
      ,CASE WHEN LEVEL-1 > 0 then CONCAT_WS('>', REPEAT('    ', level  - 1), h.ename)
                 ELSE h.ename
           END AS ename
     , h.empno
     , h.ename
     , h.mgr
     , fn.level
  FROM
     (SELECT fn_hier() AS empno, @level AS level
        FROM (SELECT @start_with:=9999, @id:=@start_with, @level:=0) x
          JOIN emp_hire
         WHERE @id IS NOT NULL) fn
  JOIN emp_hire h ON fn.empno = h.empno;

hirearchy_emp.sql

0.00MB

'MySQL > MySQL' 카테고리의 다른 글

스토어드 프로시저 (0)	2024.12.13
MySQL PARTITION (0)	2024.12.10
사용자 정의 변수 (0)	2024.12.10
UPDATE (0)	2024.12.10
LOAD DATA INFILE (1)	2024.12.10

사용자 정의 변수

Tutor.C 2024. 12. 10. 09:47

2024. 12. 10. 09:47

사용자 정의 변수

1. 사용자 정의 변수

누가생성하느냐? 시스템변수 or 사용자 변수

커넥션에 종속적인가? 글로벌 변수 or 세션 변수

동적(MySQL서버 재기동 않고)으로 변수를 변경할 수 있느냐? 정적 변수 or 동적 변수

시스템 변수 : MySQL 서버의 설정 파일이나 MySQL 서버의 명령행 인자를 통해 설정되는 변수.

사용자 정의 변수 : - 시스템 변수와 충돌되지만 않는다면 임의로 이름을 부여.

- 해당 커넥션에서만 유효하기 때문에 항상 세션 변수.

- 사용자가 언제든지 값을 변경할 수 있기 때문에 동적 변수.

사용자 변수는 스토어드 프로시저나 함수뿐 아니라 SQL 문장에서도 사용할 수 있다. 하나의 커넥션에서 정의된 사용자 변수는 다른 커넥션과 공유하지 못하고 해당 커넥션에서만 사용 가능하다. MySQL의 사용자 변수의 이름은 "@"로 시작한다.

사용자 변수는 일반적인 스크립트 언어와 마찬가지로 별도로 타입을 정의하지 않고 저장하는 값에 의해서 그 타입이 정해진다. 사용자 정의 변수에 저장할 수 있는 값은 Integer, Decimal, Float, Binary와 문자열 타입만 가능하다. 타입이 정해지지 않은 NULL도 저장할 수 있으며, 초기값을 설정하지 않으면 기본값으로 NULL을 가진다.

변수는 SET 문장으로 값이 할당됨과 동시에 생성된다. SET 문장은 "=" 또는 ":=" 연산자를 이용한다.

[SQL문장에서 사용자 변수 사용 예제]

set @var := 'My first user variable';

set @var1 = 'My first', @var2 = 'user variable';

select @var as var1, concat(@var1, ' ', @var2) as var2;

set @rn=0;

select (@rn:=@rn+1) as rn, emp_no, first_name, last_name from employees limit 5;

사용자 변수의 값을 참조하고 변경된 값을 다시 그 사용자 변수에 할당하고 있는 예이다. MySQL 서버의 각 버전별 호환성이 없고 작동방식이 달라질 수 있으므로 유의해야 하며 동일 SQL문장에서 변수에 값을 할당하고 동시에 값을 참조하는 것은 MySQL에서는 결과를 보장하지 않으므로 충분한 테스트 후에 버전별로 결과가 달라질 수 있음에 유의하며 사용하여야 한다.

아래 두개의 쿼리는 WHERE절에 사용된 쿼리가 차이가 있다. @rank:=0과 @rank:=@rank*0이 다르게 작동하여 결과가 달라지는 것을 볼 수 있다. 이러한 부분 때문에 사용자 정의 변수를 사용할 때는 발생할 수 있는 예외 케이스를 여러 번 체크해야 한다. 그럼에도 사용자 정의 변수를 사용하는 이유는 효용 가치가크기 때문이다. 사용자 변수는 일회성의 대량 작업에 적합할 때가 많다.

SET @rank:=0;

SELECT (@rank:=@rank+1) as rn , emp_no, hire_date

FROM employees

WHERE GREATEST(1,(SELECT @rank:=0)); -- 한번만 실행

SELECT @rank; -- 200

SET @rank:=0;

SELECT (@rank:=@rank+1) as rn , emp_no, hire_date

FROM employees

WHERE GREATEST(1,(SELECT @rank:=@rank*0)) -- 매번 실행

limit 10;

SET @rank:=0;

SELECT (@rank:=@rank+1) as rn , emp_no, hire_date

FROM employees

WHERE GREATEST(1,(SELECT @rank:=@rank*2)) -- 매번 실행

limit 10;

SELECT @rank; -- 1023

1번째 : 조건절 @rank:=@rank*2 -> 0=0*2, SELECT @rank:=@rank+1 -> 1=0+1

2번째 : 조건절 @rank:=@rank*2 -> 2=1*2, SELECT @rank:=@rank+1 -> 3=2+1

3번째 : 조건절 @rank:=@rank*2 -> 6=3*2, SELECT @rank:=@rank+1 -> 7=6+1

4번째 : 조건절 @rank:=@rank*2 -> 14=7*2, SELECT @rank:=@rank+1 -> 15=14+1

.....

사용자 정의 변수를 사용할 때는 반드시 다음 주의사항을 고려해야 한다.

- MySQL5.0 미만의 버전에서는 변수명의 대소문자를 구분했지만 그 이상의 버전에서는 대소문자 구분을 하지 않는다.

- 사용자 정의 변수를 사용하는 쿼리는 MySQL의 쿼리 캐시 기능을 사용하지 못한다.

- 초기화되지 않은 변수는 문자열(VARCHAR) 타입의 NULL을 가진다.

- 사용자 정의 변수의 연산 순서는 정해져 있지 않다.(내부적으로 결정돼 있지만 MySQL에서 보장하지 않는다)

- MySQL의 버전에 따른 작동 방식이나 순서에 차이가 있기 때문에 여러 버전에 걸쳐서 사용할 때는 주의해야 한다.

2. 사용자 정의 변수의 기본 활용

사용자 정의 변수는 커넥션 간에는 공유되지 않지만 하나의 커넥션에서는 공유된다. 따라서 커넥션 풀을 사용하는 일반적인 웹 프로그램에서 변수를 사용할 때마다 초기화하지 않는다면 각 웹 프로그램 코드가 상호 영향을 미칠 수 있으며 이 영향으로 의도하지 않은 문제가 발생하게 될 것이다. 사용자 변수를 사용하는 경우에는 매번 사용자 변수 값을 SET 명령으로 초기화 하는 작업을 잊어서는 안된다.

SQL 문장에서 사용자 변수를 초기화 할 수 있다. "(SELECT @rn:=0)"은 SET 명령과 동일한 역할을 수행한다. 또한 하나의 값만 가지는 스칼라 서브 쿼리이므로 조인 조건 없이 사용해도 성능에 영향이 없다. 거의 대부분의 SQL 문장에서 이런 형태의 사용자 변수 초기화를 사용할 수 있지만 ORDER BY가 사용된 JOIN UPDATE나 JOIN DELETE 문장에서는 사용할 수 없다. 이는 JOIN UPDATE나 JOIN DELETE 문장에서는 FROM 절에 사용된 테이블이 2개 이상일 때는 ORDER BY 절을 사용할 수 없기 때문이다.

SELECT (@rn:=@rn+1) AS rn, emp_no, first_name, last_name

FROM employees , (SELECT @rn:=0) der_tab

LIMIT 5;

표현식 @rn:=@rn+1은 할당 연산의 성공 여부를 반환하는 것이 아니라 대입 연산자로 할당된 값을 반환한다. 즉, @rn에 1이 증가된 값을 반환한다. 이처럼 표현식에서 대입 연산자를 기준으로 좌측에 있는 값이 반환되는 형태의 수식을 "L-value 표현식" 이라고 한다.

MySQL 사용자 변수와 더불어 자주 사용되는 몇가지 함수의 사용법을 살펴 보자.

SET @old_salary:=900000;

SELECT @old_salary, salary, @old_salary:=salary FROM salaries LIMIT 1;

select @old_salary;

위 쿼리를 보면 @old_salary 변수에 salary 값으로 '"38263"이 저장 됐음을 알 수 있다.

@old_salary 값을 salary 값으로 초기화 하고 salary 컬럼의 값을 결과 셋을 가져오기 위해서

GREATEST(salary,LEAST(-1, @old_salary:=salary)) as salary 와 같이 사용한다.

SET @old_salary:=900000;

SELECT @old_salary, GREATEST(salary,LEAST(-1, @old_salary:=salary)) as salary

FROM salaries

LIMIT 1;

select @old_salary;

LEAST()나 GREATEST() 함수가 반환할 값은 이미 정해져 있지만 MySQL 서버는 일단 함수에 주어진 모든 인자의 표현식을 수행해 본 후에야 어떤 값을 반환할지 알 수 있다.

변수 초기화를 FROM절로 집어 넣어서 아래와 같이 수행할 수도 있다.

SELECT @old_salary, GREATEST(salary,LEAST(-1, @old_salary:=salary)) as salary

FROM salaries, (SELECT @old_salary:=900000) x

LIMIT 1;

3. 사용자 정의 변수의 적용 예제

3.1 N번째 레코드만 가져오기

일반적인 SQL문장으로는 결과 셋에서 특정 몇 번째의 레코드만 가져오는 작업은 불가능하다. 하지만 사용자 변수를 이용하면 쉽게 해결할 수 있다.

dept_name을 순서대로 정렬해서 결과를 가져오면서 읽는 레코드 순서대로 번호를 붙이고 그 번호가 3인 레코드만 사용자에게 반환한다. 반환되는 레코드는 1건이지만 departments 테이블 전체를 읽는다.

select d.*, x.*

from departments d, (select @rn:=0) x

where (@rn:=@rn+1)=3

order by dept_name;

select d.*, x.*

from departments d, (select @rn:=0) x

where (@rn:=@rn+1)=3

order by dept_no;

select d.*, x.*

from departments d, (select @rn:=0) x

having (@rn:=@rn+1)=3

order by dept_no;

3.2 누적 합계 구하기

사원의 급여 테이블에서 10개의 레코드를 읽고, acc_salary라는 컬럼에는 레코드별로 누적된 salary의 합계 값을 계산해서 가져오는 쿼리이다.

select emp_no, salary, (@acc_salary:=@acc_salary+salary) as acc_salary

from salaries, (select @acc_salary:=0) x -- 파생된 테이블을 만들어 내는 서브쿼리에 별칭 부여해야 한다

limit 10;

3.3 그룹별 랭킹 구하기

각 그룹별로 누적 합계나 랭킹(순위)을 부여하는 방법을 살펴보자.

select emp_no, first_name, last_name

,IF(@prev_firstname=first_name, @rank:=@rank+1, @rank:=1+LEAST(0,@prev_firstname:=first_name)) rn

from employees e, (SELECT @rank:=0) x1, (SELECT @prev_firstname:='DUMMY') x2

where first_name in ('Georgi', 'Bezalel')

and emp_no between 423456 and 433700

order by first_name, last_name;

IF문 사용방법 : IF(조건, 참일 때 수행, 거짓 일때 수행)

select emp_no, first_name, last_name

,@prev_first_name, @rank -- IF문 수행 이전의 값

,IF(@prev_first_name=first_name, @rank:=@rank+1, @rank:=1+LEAST(0,@prev_first_name:=first_name)) rn

,@prev_first_name, @rank -- IF문 수행 이후의 값

from employees e, (SELECT @rank:=0) x1, (SELECT @prev_first_name:=NULL) x2

where first_name in ('Georgi', 'Bezalel')

and emp_no between 423456 and 433700

order by first_name, last_name;

3.4 랭킹 업데이트 하기

member_score로 랭킹을 저장하는 테이블이다. 각 회원의 member_score는 매번 게임에 참여할 때 마다 업데이트 하지만 rank_no는 몇 시간 단위로 집계 한다고 할 때 member_score를 역순으로 읽어 rank_no 컬럼을 업데이트 할수도 있지만 사용자 정의 변수를 이용해 업데이트 할 수 있다.

create table tb_ranking(

member_id int not null

,member_score int not null

,rank_no int not null

,primary key(member_id)

,index tb_ranking_ix01(member_score)

);

insert into tb_ranking values (111, 600, 9999), (112, 500, 9999), (113, 760, 9999), (114, 700, 9999), (115, 900, 9999);

update tb_ranking r, (SELECT @rank:=0) x

set r.rank_no = (@rank:=@rank+1)

order by r.member_score desc;

select * from tb_ranking order by rank_no;

SQL Error [1221] [HY000]: Incorrect usage of UPDATE and ORDER BY

JOIN UPDATE 문장은 ORDER BY 절을 사용할 수 없다.

select @rank :=0;

UPDATE tb_ranking r

SET r.rank_no = (@rank:=@rank+1)

ORDER BY r.member_score DESC;

select * from tb_ranking order by rank_no;

3.5 GROUP BY와 ORDER BY가 인덱스를 사용하지 못하는 쿼리

MySQL의 사용자 정의 변수는 레코드가 디스크로부터 읽힐 때 연산이 수행된다. 대표적으로 사용자 변수가 기대하지 않은 결과를 보이는 경우로는 GROUP BY와 ORDER BY가 함께 사용된 쿼리에서 그룹핑과 정렬이 인덱스를 사용하지 못하고 "Using temporary; Using filesort" 실행 계획이 사용되는 쿼리이다.

CREATE TABLE tb_uservars(rid VARCHAR(10));

INSERT INTO tb_uservars VALUES('z'), ('y'), ('b'), ('c'),('a'),('z'),('y'),('a'),('b'),('m'),('n');

SELECT rid, @rank:=@rank+1 as rn

FROM tb_uservars, (SELECT @rank:=0) x

ORDER BY rid;

ORDER BY를 위해 인덱스를 사용하지 못했지만, 결과는 원하는 대로 1부터 순차적으로 증가되어 반환됐다.

SELECT rid, @rank:=@rank+1 as rn

FROM tb_uservars, (SELECT @rank:=0) x

GROUP BY rid

ORDER BY rid;

GROUP BY와 ORDER BY가 모두 인덱스를 통해 처리되지 못하고 임시테이블과 별도의 정렬 작업을 통해 처리 됐음을 알 수 있다. 결과도 누락된 번호도 있고 순차적으로 증가하지도 않았다. 이는 정렬이 수행되기 전에 임시 테이블에 저장된 순서대로 사용자 변수가 연산되어 버렸기 때문이다. 이럴 때는 쿼리 전체를 임새 테이블(파생 테이블)로 만들어 주면 된다.

SELECT rid, @rank:=@rank+1 as rn, cnt

FROM (

SELECT rid , count(*) cnt

FROM tb_uservars

GROUP BY rid

ORDER BY rid

) a, (SELECT @rank:=0) x;

만약 서브 쿼리의 결과가 많아서 임시 테이블로 결과를 한번 저장하는 과정이 부담스럽다면 다음 쿼리와 같이 GROUP BY와 ORDER BY가 인덱스를 이용할 수 있도록 개선해주면 된다.

ALTER TABLE tb_uservars ADD INDEX ix_rid(rid);

SELECT rid, @rank:=@rank+1 as rn, count(*) cnt

FROM tb_uservars, (SELECT @rank:=0) x

GROUP BY rid

ORDER BY rid;

'MySQL > MySQL' 카테고리의 다른 글

MySQL PARTITION (0)	2024.12.10
MySQL 계층쿼리 구현(사용자변수,CTE) (0)	2024.12.10
UPDATE (0)	2024.12.10
LOAD DATA INFILE (1)	2024.12.10
INSERT INTO .. ON DUPLICATE KEY UPDATE.. (0)	2024.12.10

UPDATE

Tutor.C 2024. 12. 10. 09:46

2024. 12. 10. 09:46

UPDATE

UPDATE 문장은 하나의 테이블에서 레코드 한 건 또는 여러 건의 값을 변경하는 형태가 자주 사용된다. 그외에도 정렬해서 업데이트한다거나 여러 테이블을 조인해서 2개 이상의 테이블을 동시에 변경하는 처리도 가능하다. 여러 테이블을 조인해서 업데이트할 때는 조인 순서가 중요한데 조인 순서를 변경하는 방법을 살펴보자.

1. UPDATE .. ORDER BY .. LIMIT n

UPDATE는 WHERE 조건절에 일치하는 모든 레코드를 업데이트하는 것이 일반적인 처리 방식이다. 하지만 MySQL에서는 UPDATE문장에 ORDER BY절과 LIMIT절을 동시에 사용해 특정 값으로 정렬해서 그 중에서 상위 몇 건만 업데이트하는 것도 가능하다.

create table emp_update
as
select * from emp;

select * from emp_update order by sal desc;

update emp_update
set sal = sal * 2
order by sal desc limit 2;

select * from emp_update order by sal desc;

아래 예제는 SET문장은 사용자 변수값을 0으로 초기화하여 sal값을 역순으로 정렬하여 연봉 순위를 매기는 쿼리이다.

alter table emp_update add ranking int;

set @ranking:=0;

update emp_update
set ranking = (@ranking := @ranking +1)
order by sal desc;

select * from emp_update order by sal desc;

LIMIT절은 있지만 ORDER BY 절이 없는 UPDATE 문장은 복제에서 마스터 역할을 하는 MySQL 서버에서는 사용하지 않는 편이 좋다. WHERE 조건절이 존재하면 일치하는 레코드 가운데 일부를 변경하지만 이런 유형의 쿼리는 레코드의 정렬 규칙이 없기 때문에 업데이트 대상으로 선정된 레코드가 마스터와 슬레이브에서 100%달라질 수 밖에 없다.

ORDER BY절로 정렬이 되더라도 중복된 값은 순서가 마스터와 슬레이브에서 달라 질수 있기 때문에 문제가 될 수 있다. PRIMARY KERY로 정렬하면 문제는 없지만 경고메시지(Code 1592: Statement may not be safe to log in statement format.)가 발생할 수 있다. 복제가 구축된 MySQL 서버에서 ORDER BY가 포함된 UPDATE 문장을 사용할 때는 주의하자.

2. JOIN UPDATE

두 개 이상의 테이블을 조인해 조인된 결과 레코드를 업데이트하는 쿼리를 JOIN UPDATE라고 한다. 일반적으로 JOIN UPDATE는 조인되는 모든 테이블에 대해 읽기 참조만 되는 테이블은 읽기 잠금이 걸리고, 컬럼이 변경되는 테이블은 쓰기 잠금이 걸린다. OLTP 환경에서는 데드락을 유발할 가능성이 높아서 사용하지 않는 것이 좋다. 하지만 배치프로그램이나 통계용 UPDATE 문장에서는 유용하게 사용할 수 있다.

drop table if exists tb_test1;

create table if not exists tb_test1 (emp_no int, first_name varchar(14), primary key(emp_no));

insert into tb_test1 values (10001, NULL), (10002, NULL), (10003, NULL), (10004, NULL);

1) 특정 테이블의 컬럼값을 이용해 다른 테이블의 컬럼에 업데이트

update tb_test1 t1, employees e
set t1.first_name = e.first_name
where e.emp_no = t1.emp_no;

select * from tb_test1;

실행계획 확인 - update 실행계획 지원 안할시 select로 변경 후 확인

explain
update tb_test1 t1, employees e
set t1.first_name = e.first_name
where e.emp_no = t1.emp_no;

explain format=tree
update tb_test1 t1, employees e
set t1.first_name = e.first_name
where e.emp_no = t1.emp_no;

2) GROUP BY가 포함된 JOIN UPDATE

alter table departments add emp_count int;

update departments d, dept_emp de
set d.emp_count=COUNT(*)
where de.dept_no=d.dept_no
group by de.dept_no;

SQL 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 'group by de.dept_no' at line 4

explain format=tree
update departments d,
(select de.dept_no, count(*) as emp_count from dept_emp de group by de.dept_no) dc
set d.emp_count=dc.emp_count
where dc.dept_no=d.dept_no ;

임시 테이블이 드라이빙 테이블이 드라이빙 테이블이 되는 것이 일반적으로 빠른 성능을 보여 준다. JOIN UPDATE 문장에 STRAIGHT_JOIN 키워드를 사용하여 조인의 방향을 조절할 수 있다.

explain format = tree
update (select de.dept_no, count(*) as emp_count from dept_emp de group by de.dept_no) dc
straight_join departments d on dc.dept_no=d.dept_no
set d.emp_count=dc.emp_count ;

'MySQL > MySQL' 카테고리의 다른 글

MySQL 계층쿼리 구현(사용자변수,CTE) (0)	2024.12.10
사용자 정의 변수 (0)	2024.12.10
LOAD DATA INFILE (1)	2024.12.10
INSERT INTO .. ON DUPLICATE KEY UPDATE.. (0)	2024.12.10
AUTO_INCREMENT (0)	2024.12.10

PREV 이전 1 ···4 5 6 7 8 9 10 ···13 NEXT 다음

Let's have a cup of coffee.

MySQL

세션변수, 재귀호출, 중첩커서

'MySQL > MySQL' 카테고리의 다른 글

스토어드 프로그램 본문(Body) 작성

스토어드 함수

'MySQL > MySQL' 카테고리의 다른 글

스토어드 프로시저

'MySQL > MySQL' 카테고리의 다른 글

MySQL PARTITION

PARTITION

1. 개요

2. 파티션 주의사항

3. MySQL 파티션의 종류

'MySQL > MySQL' 카테고리의 다른 글

MySQL 계층쿼리 구현(사용자변수,CTE)

'MySQL > MySQL' 카테고리의 다른 글

사용자 정의 변수

'MySQL > MySQL' 카테고리의 다른 글

UPDATE

'MySQL > MySQL' 카테고리의 다른 글

+ Recent posts

티스토리툴바