MySQL 서버에서 UUID 활용

이 글은 

http://www.percona.com/blog/2014/12/19/store-uuid-optimized-way/ 블로그의 내용에 다른 내용들을 더 보완해서 구성된 내용입니다. UUID 재정렬에 의한 성능 향상은 원문 블로그에서 더 자세히 확인할 수 있습니다.

UUID는 16 옥텟(128비트)의 숫자이며, 때로는 32 글자의 소문자 16진수 문자열로 표현되기도 하는데 이때에는 "-"로 5개의 영역으로 분리되어서 표시된다. 즉 8-4-4-4-12 형태로 전체 32 알파뉴메릭으로 표시된다.

예제 : 123e4567-e89b-12d3-a456-426655440000

Variant와 Version

Variant는 UUID 정렬의 변형 형태를 의미하는데, 현재 UUID 스펙상에서는 대표적인 하나의 Variant만 지원하고 있으며, 나머지는 모두 이전 버전과의 호환성을 위해서 사용된다.

xxxxxxxx-xxxx-Mxxx-Nxxx-xxxxxxxxxxxx 형태에서 최상위 비트(MSb)는 Variant를 의미(물론 Variant에 따라서 1~3비트가 사용되기도 함)한다. UUID 스펙에 지정된 Variant는 "N"의 최상위 2비트가 "10"으로 지정된다. 즉 Variant를 의미하는 "N"에는 8이나 9 또는 'A'나 'B'만 가능한 것이다. UUID 스펙에서 지원하는 Variant는 다섯 개의 버전을 지원하는데, 이 Variant에 대해서는 "M"의 4비트는 UUID의 버전을 의미한다. UUID의 버전은 1~5까지 가능하다.

버전-1 (MAC address & date-time)

  네트워크 랜 카드와 시간을 기반으로 유니크한 ID를 생성한다. 이 아이디는 형태를 기반으로 예측이 가능하며, 이 값을 이용해서 네트워크 카드를 트레이스할 수도 있다. 

  Version 1 UUID는 48 비트의 네트워크 카드의 MAC 주소와 60비트의 시간 정보로 구성된다. 시간 정보는 100 Nano second 단위(Resolution)로 관리되는데, 대략 3655년 정도의 데이터가 저장된다.

  하지만 시간 정보는 1582년 10월 15일부터 시작된 값이므로, 다시 초기화되기까지는 상당히 많은 시간이 남아있다. 또한 UUID Version 1은 100 Nanosecond 단위이므로, 한 서버에서 최대 초당 1000000(1000000000/100)개의 유니크한 UUID를 만들어낼 수 있다.

버전-2 (DCE Security)

  Version1과 비슷하지만, 시간을 위한 비트수가 더 적어서 Version 1보다는 더 짧은 기간내에 시간 부분이 반복(Wrap)될 가능성이 높다.

  이는 DCE (Distributed Computing Evn)를 위해서 고안된 버전이므로, 그다지...

버전-3 (MD5 hash & namespace)

  Namespace와 Name의 MD5 해시 값을 이용해서 유니크한 ID를 생성한다. 만약 특정 Name을 이용해서 다른 시스템에서 생성된 UUID와의 호환성을 유지하고자 한다면 Version 3 UUID를 사용하는 것이 좋다.

  이 방식은 MD5로 해시 값을 생성해서, UUID 포맷으로 변환하는 것과 거의 흡사하게 작동한다고 생각해도 무방하다.

  Name과 Namespace에 대한 자세한 내용은 "http://stackoverflow.com/questions/10867405/generating-v5-uuid-what-is-name-and-namespace" 참조.

버전-4 (random)

  랜덤한 숫자 값을 이용해서 UUID를 생성한다. 그냥 단순히 UUID를 생성하고자 한다면 Version 4 UUID를 사용하는 것이 좋다.

버전-5 (SHA-1 hash & namespace)

  Namespace와 Name의 SHA-1 해시 값을 이용해서 유니크한 ID를 생성한다. 

  이 방식은 SHA-1 알고리즘으로 해시 값을 생성해서, UUID 포맷으로 변환하는 것과 거의 흡사하게 작동한다고 생각해도 무방하다.

  MD5는 이미 충돌이 많이 발생하고 보안성이 떨어지는(Broken) 암호화 해시 알고리즘이므로 MD5보다는 SHA-1을 사용하는 Version 5 UUID를 사용할 것을 권장한다.

만약 유니크한 UUID를 생성하고자 한다면, Version 1과 4를 사용하는 것이 좋고,

주어진 Name을 이용하면 타 시스템에서도 동일한 UUID를 생성해야 한다면 Version 3와 5를 이용하는 것이 좋다.

하지만 UUID Version 1은 시간을 기반으로 하고 있기 때문에, 매우 빠른 처리를 수행하는 시스템(Multi-process && Multi-thread)에서는 중복된 값이 생성될 수도 있다. 

이런 단점을 보완하기 위해서 Time-based UUID(http://johannburkard.de/software/uuid/)가 도입되었는데, 이는 실제 100 Nano-second 단위의 타임 스탬프를 가져오는 것이 아니라 Milli-second 수준의 타임스탬프만 가져오고

나머지는 UUID Class에서 AutoIncrement와 Seudo-Random number를 이용해서 중복 가능성을 낮춘 형태이다. Time-based UUID는 Cassandra에서 식별자 용도로 자주 사용된다.

MySQL 서버에서도 UUID() 함수를 제공하는데, 이는 UUID Version1을 지원하는 함수이다. 때로는 이런 UUID 함수나 Application에서 제공하는 UUID 기능을 이용해서 MySQL 서버의 PK 또는 Unique Key로 사용하는 경우가 많이 있는데,

UUID 값의 특징은 생성되는 값이 전혀 단순 증가나 단순 감소 형태가 아니라 매우 랜덤하게 생성된다는 것이다. 이는 UUID Version 1이 시간을 기반으로 만들어지기는 하지만, 실제 Timestamp 값이 3개 파트로 나뉘어져서 재구성되기 때문이다.

예를 들어서 60bits 타임 스탬프 값이 "1d8eebc58e0a7d7"라고 가정해보자. 그러면 이 값이 part1(1d8)과 part2(eebc) 그리고 part3(58e0a7d7)으로 나뉘어지고, 이 값들이 순서 관계없이 아래와 같이 Version 1의 UUID의 각 블록에 설정된다.

[Timestamp(part3)]-[Timestamp(part2)]-1[Timestamp(part1)]-[NetworkCardMAC]

그래서 최종 생성된 UUID 값은 "58e0a7d7-eebc-11d8-9669-0800200c9a66"가 되는 것이다. 이렇게 랜덤하게 생성되는 값은 MySQL 서버의 InnoDB 테이블에서 PK나 Unique Key로 사용되기에는 (성능상) 아주 부적절한 값이다. 

PK나 Unique Key로 사용되기에 아주 좋은 값은 단조 증가나 감소하는 패턴인 것이 가장 좋은데, (이미 눈치챘겠지만) UUID 값을 그대로 사용하는 것이 아니라 재 조합을 해서 원래 Timestamp를 제일 앞 부분으로 꺼내어서 만들어주면 단순 증가하는 형태의 값을 만들어낼 수 있다.

단순히 이렇게 정렬된 UUID를 생성하는 MySQL 함수를 아래와 같이 생성할 수 있다. 또한 저장 공간을 줄이기 위해서 VARCHAR(32)보다는 BINARY(16)이나 VARBINARY(16)으로 컬럼을 생성해주는 것이 좋다.

DELIMITER ;;

CREATE 

  DEFINER=`user`@`host`

  SECURITY=INVOKER

FUNCTION ordered_uuid(uuid BINARY(36)) RETURNS BINARY(16) DETERMINISTIC

  RETURN UNHEX(CONCAT(SUBSTR(uuid, 15, 4),SUBSTR(uuid, 10, 4),SUBSTR(uuid, 1, 8),SUBSTR(uuid, 20, 4),SUBSTR(uuid, 25)));

;;

DELIMITER ;

물론 Statement 기반의 복제(SBR)을 사용하는 경우라면 MySQL 서버의 UUID() 함수 사용은 복제를 불가능하게 만들수도 있으므로 주의하도록 하자. 복제가 수행되는 MySQL 서버에서 UUID를 사용해야 한다면, 응용 프로그램에서 생성된 값을 MySQL 서버로 INSERT 또는 UPDATE 하는 형태로 사용하도록 해야 한다.

그리고 때로는 Unique ID를 생성하기 위해서 UUID를 사용하지 않고 개발자가 직접 커스텀하게 개발하는 경우도 있는데, 이런 경우에도 Timestamp를 꼭 ID의 앞쪽에 위치하도록 만들어준다면 똑같이 InnoDB 테이블에서 PK나 Unique Key로 사용하기에 적절한 값을 만들어낼 수 있다.

프로시져를 이용한 재귀 쿼리 (2)

스토어드 프로시져를 이용해서, WITH 절을 에뮬레이션한 재귀 처리는 이미 살펴보았는데, 이 프로시져 예제는 성능보다는 범용성에 집중해서 만들어진 것이다. 사실 이 프로시져(with_emulator)로 성능 테스트를 해보진 않았지만, 임시 테이블 생성과 생각보다 많은 테이블 레벨의 조작들이 필요하므로 성능적으로 장점은 별로 없어 보였다.

그래서 특정 테이블의 구조에 제한적인 재귀 처리를 스토어드 프로시져로 만들어서 성능 테스트를 해보았다. 우선 아래와 같은 employees라는 사원 테이블을 가정해보자.

CREATE TABLE `employees` (

  `emp_no` int(11) NOT NULL,

  `last_name` varchar(50) NOT NULL,

  `first_name` varchar(50) NOT NULL,

  `extension` varchar(10) NOT NULL,

  `email` varchar(100) NOT NULL,

  `office_code` varchar(10) NOT NULL,

  `boss_emp_no` int(11) DEFAULT NULL,

  `job_title` varchar(50) NOT NULL,

  PRIMARY KEY (`emp_no`),

  KEY `ix_bossempno` (`boss_emp_no`),

  KEY `ix_officecode` (`office_code`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Oracle이라면 아래와 같이 재귀 쿼리를 이용해서 한번에 트리를 조회할 수 있을 것이다.

SELECT *, LEVEL as LV

FROM employees

CONNECT BY PRIOR emp_no = boss_emp_no

START WITH emp_no='1002';

+--------+-----------+------------+...+-------------+----------------------+------+

| emp_no | last_name | first_name |...| boss_emp_no | job_title            | lv   |

+--------+-----------+------------+...+-------------+----------------------+------+

|   1002 | Murphy    | Diane      |...|        NULL | President            |    1 |

|   1056 | Patterson | Mary       |...|        1002 | VP Sales             |    2 |

|   1088 | Patterson | William    |...|        1056 | Sales Manager (APAC) |    3 |

|   1611 | Fixter    | Andy       |...|        1088 | Sales Rep            |    4 |

|   1612 | Marsh     | Peter      |...|        1088 | Sales Rep            |    4 |

|   1619 | King      | Tom        |...|        1088 | Sales Rep            |    4 |

|   1102 | Bondur    | Gerard     |...|        1056 | Sale Manager (EMEA)  |    3 |

|   1702 | Gerard    | Martin     |...|        1102 | Sales Rep            |    4 |

|   1337 | Bondur    | Loui       |...|        1102 | Sales Rep            |    4 |

|   1370 | Hernandez | Gerard     |...|        1102 | Sales Rep            |    4 |

|   1401 | Castillo  | Pamela     |...|        1102 | Sales Rep            |    4 |

|   1501 | Bott      | Larry      |...|        1102 | Sales Rep            |    4 |

|   1504 | Jones     | Barry      |...|        1102 | Sales Rep            |    4 |

|   1143 | Bow       | Anthony    |...|        1056 | Sales Manager (NA)   |    3 |

|   1165 | Jennings  | Leslie     |...|        1143 | Sales Rep            |    4 |

|   1166 | Thompson  | Leslie     |...|        1143 | Sales Rep            |    4 |

|   1188 | Firrelli  | Julie      |...|        1143 | Sales Rep            |    4 |

|   1216 | Patterson | Steve      |...|        1143 | Sales Rep            |    4 |

|   1286 | Tseng     | Foon Yue   |...|        1143 | Sales Rep            |    4 |

|   1323 | Vanauf    | George     |...|        1143 | Sales Rep            |    4 |

|   1621 | Nishi     | Mami       |...|        1056 | Sales Rep            |    3 |

|   1625 | Kato      | Yoshimi    |...|        1621 | Sales Rep            |    4 |

|   1076 | Firrelli  | Jeff       |...|        1002 | VP Marketing         |    2 |

+--------+-----------+------------+...+-------------+----------------------+------+

하지만 MySQL에서는 아직 WITH절이나 CONNECT BY 절을 사용할 수 없으므로, 이와 유사한 결과를 만들어 내기 위해서 아래와 같은 스토어드 프로시져를 생성해 보았다. 물론 이 프로시져의 결과는 Oracle의 CONNECT BY와 동일한 정렬 순서를 보이지는 않지만, 이런 (Tree 형태의) 정렬 작업들은 응용 프로그램에서 얼마든지 쉽게 구현할 수 있으며, 이전 블로그에서 소개했던 with_emulator 프로시져를 참조하면 MySQL 쿼리로도 쉽게 구현할 수 있으므로 생략하도록 하겠다.

CREATE PROCEDURE recursive_static(p_start_value VARCHAR(100))

BEGIN

  DECLARE recursive_count INT UNSIGNED;

  DECLARE current_lv INT UNSIGNED;

  DECLARE parent_ids VARCHAR(1000);

  SET SESSION sql_log_bin=OFF;

  SET SESSION tx_isolation='READ-COMMITTED';

  SET recursive_count = 1;

  SET @_current_lv := 1;

  CREATE TEMPORARY TABLE IF NOT EXISTS _temp_buffer(

      emp_no int(11), 

      last_name varchar(50), 

      first_name varchar(50), 

      extension varchar(10), 

      email varchar(100), 

      office_code varchar(10), 

      boss_emp_no int(11), 

      job_title varchar(50), 

      lv int, 

      INDEX ix_lv(lv)

  ) ENGINE=MEMORY;

  INSERT INTO _temp_buffer 

    SELECT emp_no, last_name, first_name, extension, email, 

               office_code, boss_emp_no, job_title, @_current_lv AS lv 

      FROM employees WHERE emp_no=p_start_value;

  recursion: REPEAT

    SELECT GROUP_CONCAT(emp_no) INTO parent_ids 

      FROM _temp_buffer WHERE lv=@_current_lv;

    IF parent_ids IS NULL OR parent_ids="" THEN

      LEAVE recursion;

    END IF;

    SET @query = CONCAT("INSERT INTO _temp_buffer 

            SELECT emp_no, last_name, first_name, extension, email,

                        office_code, boss_emp_no, job_title, (? + 1) AS lv 

            FROM employees WHERE boss_emp_no IN (", parent_ids, ")");

    PREPARE stmt3 FROM @query;

    EXECUTE stmt3 USING @_current_lv;

    SET @_current_lv = @_current_lv + 1;

    SET recursive_count = recursive_count + 1;

    IF recursive_count > 10 THEN

      LEAVE recursion;

    END IF;

  UNTIL 0 END REPEAT;

  SELECT * FROM _temp_buffer;

  TRUNCATE TABLE _temp_buffer;

  SET SESSION sql_log_bin=ON;

  SET SESSION tx_isolation='REPEATABLE-READ';

END ;;

이 프로시져는 범용성은 포기하고 성능 및 테스트를 위해서 준비된 것이므로, 위에서 보인 employees 테이블의 구조에서만 사용될 수 있는 프로시져이고, 만약 employees 테이블의 구조가 변경된다면 프로시져의 내용도 변경되어야 할 것이다. 또한 이 프로시져에서는 바이너리 로그 기록과 격리 수준등 불필요하고 성능 저해 요소는 모두 비활성화하고 실행하도록 했다. 이 프로시져도 내부적으로는 임시 테이블을 사용하는데, 여기에서는 특정 컨넥션에서 이 프로시져가 처음 호출될 때에 임시 테이블을 만들고 계속 재활용될 수 있도록 프로시져를 만들어 보았다.

이렇게 준비된 recursive_static 프로시져를 실행하면, 아래와 같은 결과를 얻을 수 있다. 보시다시피 이 결과는 Oracle의 재귀 쿼리 결과와는 조금 다른 모양을 보이고 있다. 하지만 이 부분은 위에서 언급했듯이 이번 테스트의 관심 사항이 아니므로 넘어가도록 하겠다. 어찌되었거나 "Diane"을 포함해서 하위 조직의 사원들 23명의 정보를 가져왔다는 것에 집중하자.

mysql:test> CALL recursive_static('1002');

+--------+-----------+------------+..+-------------+----------------------+------+

| emp_no | last_name | first_name |..| boss_emp_no | job_title            | lv   |

+--------+-----------+------------+..+-------------+----------------------+------+

|   1002 | Murphy    | Diane      |..|        NULL | President            |    1 |

|   1056 | Patterson | Mary       |..|        1002 | VP Sales             |    2 |

|   1088 | Patterson | William    |..|        1056 | Sales Manager (APAC) |    3 |

|   1611 | Fixter    | Andy       |..|        1088 | Sales Rep            |    4 |

|   1612 | Marsh     | Peter      |..|        1088 | Sales Rep            |    4 |

|   1619 | King      | Tom        |..|        1088 | Sales Rep            |    4 |

|   1102 | Bondur    | Gerard     |..|        1056 | Sale Manager (EMEA)  |    3 |

|   1702 | Gerard    | Martin     |..|        1102 | Sales Rep            |    4 |

|   1337 | Bondur    | Loui       |..|        1102 | Sales Rep            |    4 |

|   1370 | Hernandez | Gerard     |..|        1102 | Sales Rep            |    4 |

|   1401 | Castillo  | Pamela     |..|        1102 | Sales Rep            |    4 |

|   1501 | Bott      | Larry      |..|        1102 | Sales Rep            |    4 |

|   1504 | Jones     | Barry      |..|        1102 | Sales Rep            |    4 |

|   1143 | Bow       | Anthony    |..|        1056 | Sales Manager (NA)   |    3 |

|   1165 | Jennings  | Leslie     |..|        1143 | Sales Rep            |    4 |

|   1166 | Thompson  | Leslie     |..|        1143 | Sales Rep            |    4 |

|   1188 | Firrelli  | Julie      |..|        1143 | Sales Rep            |    4 |

|   1216 | Patterson | Steve      |..|        1143 | Sales Rep            |    4 |

|   1286 | Tseng     | Foon Yue   |..|        1143 | Sales Rep            |    4 |

|   1323 | Vanauf    | George     |..|        1143 | Sales Rep            |    4 |

|   1621 | Nishi     | Mami       |..|        1056 | Sales Rep            |    3 |

|   1625 | Kato      | Yoshimi    |..|        1621 | Sales Rep            |    4 |

|   1076 | Firrelli  | Jeff       |..|        1002 | VP Marketing         |    2 |

+--------+-----------+------------+..+-------------+----------------------+------+

이제 recursive_static 프로시져가 얼마나 빨리 처리될 수 있는지 간단히 로드 제너레이터를 만들어서 한번 실행해보도록 하자. 로드 제너레이터의 내용에는 별다를 건 없다. 그냥 멀티 쓰레드로 기동된 프로그램이 JDBC를 이용해서 프로시져를 CALL하고 결과를 가져오는 작업을 반복하면서 초 단위로 Throughput을 출력하도록 했다.

Server spec : Intel Xeon 2.3GHz * 2 socket * 6 core (with HT)

MySQL 5.6.20에서는 초당 12000번 정도의 "CALL recursive_static()"을 처리했으며, MariaDB 10.0.12에서는 9300번 그리고 10.0.14에서는 대략 10200번 정도의 처리 성능을 보였다. MySQL 5.6.20이 MariaDB 10.0.14 보다 대략 20%정도 높은 성능을 보이고 있는데, 이는 아마도 MariaDB 10.0이 MySQL 5.5 코드를 베이스로 하고 있으며 아직 MariaDB가 MySQL 5.6의 최적화 내용들을 모두 포팅하지 못해서일 것으로 보인다. (일단 이 내용은 MariaDB 개발팀에 문의를 해두었으니, 조만간 개선될 것으로 보인다.)

이 테스트가 실행되는 동안 GROUP_CONCAT 함수로 인해서(레코드 건수가 적어서 인덱스를 이용하지 못함) 내부 임시 테이블이 사용되는데, 대략 테스트가 실행되는 동안 초당 45000 ~ 58000번 정도의 내부 임시 테이블이 생성되었다가 삭제되는 현상이 반복되었다. 실제 이렇게 임시 테이블이 아주 빈번하게 사용되는 환경에서는 Linux에서 기본으로 사용되는 PTMalloc보다는 JEMalloc과 같은 캐시 기반의 메모리 할당 라이브러리를 사용해주는 것이 그나마 조금 성능 향상에 도움이 될 것으로 보인다. (이 테스트는 나중에 다시 ..)

재귀 쿼리가 지원되지 않아서 MySQL을 회피하는 경우가 많은데, 서비스의 대 부분 쿼리가 재귀 쿼리로 개발된 서비스가 아니라면 그리고 이 정도의 스토어드 프로그램에 시간을 투자할 수 있다면 이런 방식도 충분히 괜찮은 Work-around가 되지 않을까 생각된다. 물론 이 테스트를 수행하는 동안 MySQL 서버의 CPU 사용량은 거의 90% 수준이었으니, 실제 프로덕션 환경의 MySQL 서버에서 이 정도 성능을 기대하기는 어려울 수도 있을 것이다. 또한 프로그램에서 필요로 하는 데이터의 레코드 수가 더 많거나 레코드의 크기가 크다면 성능은 더 떨어질 수도 있을 것이다. 

이 테스트 케이스에서 보였던 것처럼 초당 10000번의 재귀 쿼리가 실행되어야 하는 서비스는 아직까지 본 적이 없어서, 재귀 쿼리 기능이 RDBMS를 선정하는 기준이 될 수 있는지는 조금 더 고려가 필요해 보인다.

MySQL의 스토어드 프로그램

개요

MySQL의 스토어드 프로그램(이 글에서 스토어드 프로그램은 Stored procedure와 Stored Function에 한함)은 MySQL 5.0버전부터 지원되기 시작했다. MySQL 5.0의 첫번째 릴리즈 버전이 2005년도 10월에 출시되었으니, MySQL에 프로시져가 도입된지 벌써 10년 정도의 시간이 지나가고 있지만 실제로 MySQL에서 프로시져의 인기는 그다지 높지 않다.
요즘은 MSSQL이나 Oracle에 익숙한 사용자(개발자와 DBA 모두)들이 MySQL을 배우거나 사용하고자 하는 경우가 많이 늘어나고 있는데, 많은 사용자들이 MySQL 만의 특징에 익숙치 않아서 혼란스러워하는 경우를 많이 보았다.
특히나 MySQL은 주로 Web 기반의 서비스에서 사용되다 보니, MSSQL이나 Oracle과 같은 RDBMS에서 효율적으로 제공하는 기능들이 MySQL에서는 그렇지 못한 것들이 자주 있다. 물론 때로는 그 반대인 경우도 흔히 볼 수 있다.
그중에서 가장 많은 이슈가 되고 있는 스토어드 프로그램의 특징을 간단히 살펴보고, 왜 MySQL에서는 Oracle이나 MSSQL에서와 같이 스토어드 프로그램을 활용할 수 없는지를 소개해보고자 한다.

스토어드 프로그램의 컴파일

다른 상용의 RDBMS에서와 같이 MySQL 서버에서도 스토어드 프로그램은 컴파일 과정을 거치게 된다. 물론 C/C++과 같이 물리적인 CPU가 직접 해석할 수 있는 이진 코드가 만들어지는 것은 아니지만, Java와 같이 어떤 형태의 목적 코드(Java의 바이트 코드와 같은)가 만들어지고
이 목적 코드는 메모리상에 저장되어서 나중에 재실행 요청시에는 준비된 바이트 코드가 실행된다. 즉 스토어드 프로그램의 소스 코드가 매번 실행될 때마다 파싱되고 분석되어서 실행되는 것이 아니란 것을 의미한다.

간단히 아래와 같은 프로시져를 생각해보자.

CREATE PROCEDURE sp_test(p CHAR(16))
BEGIN
    DECLARE x INT;
    SET x = 3;
    WHILE x > 0 DO
        SET x = x-1;
        INSERT INTO tab_test VALUES (x, p);
    END WHILE;
END

위의 프로시져가 컴파일되면, 아래와 같은 목적 코드가 만들어지게 된다.
목적 코드에서는 단순히 스토어드 프로그램의 코드에서 SET 이나 WHILE과 같은 문장들을 sp_instr_set이나 sp_instr_jump 등과 같은 인스트럭션으로 변환된 형태로 관리하게 된다.
여기에서 한 가지 기억해야 할 것은 컴파일된 스토어드 프로그램의 목적 코드에서 SQL 문장은 그대로 문자열로 남아있게 된다는 것이다. 즉 MySQL의 스토어드 프로그램은 컴파일이 되어도 내부에 사용된 SQL 문장들을 바로 실행할 수 있는 실행 계획이나 Parsed-Tree 형태로 관리하는 것이 아니란 것을 의미한다.

---------+-----------------------------------------------------
Position | Instruction
---------+-----------------------------------------------------
 0       | sp_instr_set(1, '3')
 1       | sp_instr_jump_if_not(5, 'x>0')
 2       | sp_instr_set(1, 'x-1')
 3       | sp_instr_stmt('INSERT INTO tab_test VALUES (x, p)')
 4       | sp_instr_jump(1)
 5       | <end>
---------+-----------------------------------------------------

스토어드 프로그램 캐시

Oracle이나 MSSQL의 스토어드 프로그램은 전역의 스토어드 프로그램 캐시 공간(Memory)에 관리된다. 물론 MySQL 서버의 스토어드 프로그램도 컴파일되면 스토어드 프로그램 캐시(소스 코드에서는 이를 sp_cache라고 함)에 관리한다.
하지만 MySQL의 스토어드 프로그램 캐시는 전역이 아니라 Thread 단위로 관리된다. 여기서 Thread라 함은 사실은 Connection 기반으로 관리됨을 의미한다. 만약 Thread pool을 사용한다 하더라도, 실제 Linux의 Thread 단위가 아니라 Connection 단위의 메모리 공간(THD)에 관리되는 것이다.

큰 차이가 아닌 것 같지만, 사실 스토어드 프로그램 캐시가 전역이나 세션(로컬) 단위냐에 따라서 장단점은 크게 달라진다.

• 전역 스토어드 프로그램 캐시
•  장점 : 메모리 절약, 스토어드 프로그램의 컴파일과 최적화 회수가 적음
•  단점 : 여러 클라이언트가 동시에 컴파일된 스토어드 프로그램을 참조하므로 동기화 비용이 필요하며, Re-Enterant와 Thread-safe한 데이터 구조체 및 구현 필요(뒷 부분은 사실 운영이 아니라 구현상의 이슈이므로, 사용자인 우리에게는 별로 중요하지 않음)
• 로컬 스토어드 프로그램 캐시
• 장점 : 클라이언트간의 공유 이슈가 없으므로 잠금이 없고 빠른 처리 가능, 구현이 쉬움
• 단점 : 많은 메모리 공간이 필요하고, 클라이언트 컨넥션 단위로 스토어드 프로그램의 컴파일 필요

MySQL의 스토어드 프로그램 캐시 공간은 Connection 단위로 관리된다는 것은 컨넥션이 새로 생성되면 필요한 모든 프로시져의 컴파일이 필요하다는 것을 의미한다.
만약 Connection pool이나 PHP의 Persistent-connection을 사용하지 못하고 매번 Connection을 생성해야 하는 경우라면, 매번 스토어드 프로그램이 실행될 때마다 스토어드 프로그램을 (mysql.proc 테이블에서) 읽어서 컴파일을 해야 하므로 최악의 성능을 내게 될 것이다.
그렇다고 Connection pool이나 Persistent-Connection 환경이라고 안전한 것은 아니다. 많은 스토어드 프로그램이 사용되는 서비스에서 MySQL 서버에 연결된 컨넥션이 10000개라고 가정하면 엄청난 메모리 공간이 필요하게 될 것이다.
하지만 성능 향상을 고려한다면, 스토어드 프로그램 캐시 메모리 공간을 적게 설정할 수도 없는 진퇴양난의 상황에 빠지게 될 수도 있다.

스토어드 프로그램의 무효화

MySQL 서버의 스토어드 프로그램 캐시 공간은 컨넥션간 서로 공유되는 전역 공간이 아니라, 컨넥션 단위로 관리된다는 것을 앞에서 살펴보았다.
사실 스토어드 프로그램 캐시가 컨넥션 단위로 관리되기 때문에 발생하는 문제점이 또 있는데, ALTER나 CRETE 등과 같은 DDL을 이용해서 스토어드 프로그램의 코드를 변경하는 경우이다.
만약 컨넥션이 10000개가 만들어져서 각각의 컨넥션에서 sp_test라는 프로시져를 사용하고 있다고 가정해보자. 이때 DBA가 ALTER PROCEDURE나 DROP PROCEDURE + CREATE PROCEDURE를 실행했다고 가정해보자.
그럼 어떤 현상이 발생하게 될까 ?

프로시져를 변경하는 컨넥션에서는 단순히 해당 프로시져의 정보를 mysql DB에 있는 proc 테이블에 변경 저장하고, 해당 프로시져의 버전을 1 증가시키고 완료된다. 이때 해당 프로시져의 버전은 글로벌하게 전역 메모리 공간에 관리된다.
그리고 모든 서비스 컨넥션에서는 프로시져를 실행하기 전에 항상 로컬 스토어드 프로그램 캐시에 괸리되는 프로시져의 버전과 전역 공간의 프로시져 버전을 확인해서, 로컬 스토어드 프로그램 캐시의 버전이 낮으면 로컬 스토어드 프로그램 캐시에 저장되어 있던 컴파일된 목적 코드를 버리고 다시 컴파일을 수행한다.
이렇게 컴파일이 완료되면, 비로소 해당 프로시져를 실행할 수 있게 되는 것이다.

그나마 다행인 것은, 변경된 프로시져가 자주 실행되지 않는다면 모든 컨넥션이 한번에 동일 스토어드 프로그램을 컴파일하기 위해서 상당한 시간을 소모하지 않을 것이다. 하지만 스토어드 프로그램이 아주 빈번하게 모든 컨넥션에서 활용된다면 어떤 상황이 발생하게 될까 ?
이런 경우라면 일부러 사용량이 별로 없는 새벽 시간에 스토어드 프로그램을 배포해야 할 지도 모르겠다.

(참고로, Oracle의 MySQL 개발팀에서는 Production MySQL 서버에서 스토어드 프로그램을 갱신하는 것은 상당히 드문 케이스이며, 별로 심각하게 고려되지 않는 상황이라고 소개하고 있다. ㅠㅠ)있다

메모리 부족 예방

MySQL 서버의 스토어드 프로그램은 컨넥션 단위로 로컬 캐시 영역에 관리되기 때문에, 컨넥션이 많고 사용되는 스토어드 프로그램이 많다면 많은 메모리 공간이 필요할 것이다. 때로는 메모리 부족 현상으로 운영 체제가 MySQL 서버를 강제 종료시킬 수도 있다.
여기에서 스토어드 프로그램의 개수가 많고 적음은 상대적이며, Production MySQL 서버에 장착된 메모리 크기와 여러가지 상황에 따라서 의존적이므로 각 DBA가 적절하게 판단해야 할 것으로 보인다.

MySQL 서버에서는 이런 메모리 과다 사용을 막기 위해서 MySQL 5.5부터 stored_program_cache라는 시스템 변수를 제공하고 있다. 이 변수는 기본 값이 256이며, 설정하는 값의 의미는 스토어드 프로그램 캐시에 저장할 스토어드 프로그램의 개수이다.
스토어드 프로그램 하나 하나의 크기에 의해서도 메모리 사용량이 많이 좌우될 것으로 보이므로, 사실 256이라는 수치가 적절한지 큰 값인지는 판단하기 쉽지 않아 보인다.

만약 스토어드 프로그램 캐시에 저장된 스토어드 프로그램의 개수가 256을 넘게 되면, MySQL 서버는 현재 컨넥션의 스토어드 프로그램 캐시 내용을 모두 무효화시키고 다시 스토어드 프로그램을 하나씩 컴파일해서 저장하게 된다.
물론 스토어드 프로그램이 256개 이상이고 순서대로 하나씩 사용된다면, 위의 무효화 -> 컴파일 과정을 계속 반복하게 될 것이다.

결론

MySQL 스토어드 프로그램의 내부적인 처리 방식을 간단히 살펴보았는데, MySQL의 스토어드 프로그램을 Oracle이나 MSSQL의 그것과 동일하게 생각해서는 안되는 이유를 간략히 정리해보면...
1) 스토어드 프로그램 자체는 컴파일되어서 목적 코드로 관리되지만, 내부의 SQL문장을 파스된 형태(실행계획이나 Parsed-Tree 형태)로 관리하지 않는다.
2) 컴파일된 스토어드 프로그램 목적 코드는 각 컨넥션 단위로 관리되기 때문에 Oracle이나 MSSQL보다 많은 메모리 공간이 필요하다.
3) 스토어드 프로그램이 변경될 때마다, 모든 컨넥션에서 기존 목적 코드의 무효화 및 신규 프로시져의 컴파일 과정일 필요하다.

또한 MySQL은 Web 기반의 단순 쿼리를 고속으로 처리해주는 용도로 많이 활용된다. 그래서 Facebook이나 Twitter 등의 SNS 회사들은 WebScaleSQL이라는 목표로 MySQL 코드 패치를 수행하고 있기도 하다.
이런 방향성으로 본다면, 스토어드 프로그램과 같은 복잡한 절차적 코드(Compound-statement block)를 확장이 어려운 MySQL 서버에 둔다는 것은 적절치 않을 수 있다.
Oracle이나 MSSQL에서는 모든 처리를 DBMS 서버로 집중화하고 서버를 통합(Consolidation) 것이 목표였다면, MySQL의 목표는 그 반대로 볼 수 있다. MySQL은 라이센스 비용이 없으니깐 말이다.
물론 라이센스 비용 이야기는 어떤 형태의 기술 지원을 받는냐에 따라 이야기가 달라지겠지만, 그래도 Oracle이나 MSSQL의 라이센스 비용에 비할바는 아닐 것이다.

<<그렇다고 MySQL의 스토어드 프로그램은 사용해서는 안될 물건이라고 생각하지는 말자. 어디까지나 목적에 맞게 기능들을 잘 활용하자는 수준으로 해석할 것을 당부드린다.>>

WITH절을 이용한 재귀 쿼리 에뮬레이션

Oracle이나 MSSQL을 사용해 본 사람이라면 아마도 “WITH 절” 

을 사용해 보았을 것이다. 어떤 사람들은 이를 “서브 쿼리 팩토링(Subquery Factoring)”이라 하고, 또 어떤 사람들은  “COMMON TABLE EXPRESSION”이라고 한다

3개의 컬럼을 가지는 T1 테이블을 가정해보자.

CREATE TABLE T1(
YEAR INT, # 2000, 2001, 2002 ...
MONTH INT, # January, February, ...
SALES INT # how much we sold on that month of that year
);

이제 연도별로 판매 실적의 트렌드(증감)를 조회하고자 한다고 해보자.

SELECT D1.YEAR, (CASE WHEN D1.S>D2.S THEN 'INCREASE' ELSE 'DECREASE' END) AS TREND
FROM
  (SELECT YEAR, SUM(SALES) AS S FROM T1 GROUP BY YEAR) AS D1,
  (SELECT YEAR, SUM(SALES) AS S FROM T1 GROUP BY YEAR) AS D2
WHERE D1.YEAR = D2.YEAR-1;

위 쿼리에서 두개의 서브 쿼리는 동일한 서브 쿼리 문장을 사용하고 있다. 하지만 일반적인 DBMS는 그걸 인지할만큼 똑똑하지 않다. 그래서 DBMS 옵티마이저는 “SELECT YEAR, SUM(SALES), …” 문장을 두번 실행해서 각각 D1과 D2 임시 테이블을 채우게 될 것이다.  이렇게 동일한 문장을 두번 수행하는 작업은 성능적으로도 상당히 문제가 될 수 있다. WITH 절을 사용하게 되면, 이런 제한 사항이 없어지고 서브 쿼리는 단 한번만 수행하게 된다.

WITH D AS (SELECT YEAR, SUM(SALES) AS S FROM T1 GROUP BY YEAR)
SELECT D1.YEAR, (CASE WHEN D1.S>D2.S THEN 'INCREASE' ELSE 'DECREASE' END) AS TREND
FROM
 D AS D1,
 D AS D2
WHERE D1.YEAR = D2.YEAR-1;

하지만  MySQL에서는 WITH 절이 지원되지 않는다. 하지만 VIEW를 이용하면 WITH절을 에뮬레이션할 수 있다.

CREATE VIEW D AS (SELECT YEAR, SUM(SALES) AS S FROM T1 GROUP BY YEAR);

SELECT D1.YEAR, (CASE WHEN D1.S>D2.S THEN 'INCREASE' ELSE 'DECREASE' END) AS TREND
FROM
 D AS D1,
 D AS D2
WHERE D1.YEAR = D2.YEAR-1;
DROP VIEW D;

VIEW 대신 D 테이블을 일반 테이블로 생성할 수도 있다. 이때 D 테이블을 임시 테이블로 사용할 수는 없다. MySQL에서는 하나의 쿼리 문장에서 임시 테이블은 단 한번만 참조될 수 있다는 제한 사항이 있기 때문이다. (http://dev.mysql.com/doc/refman/5.7/en/temporary-table-problems.html)

이제 조금 더 복잡한 형태의 WITH 절 사용법(재귀적인 형태)을 살펴보자.
SQL 표준에 의하면, 재귀적인 형태를 사용하기 위해서는 “WITH RECURSIVE”를 사용해야 한다. 하지만 일반적인 DBMS에서는 RECURSIVE 키워드는 생략할 수 있는 것으로 보인다.

WITH RECURSIVE는 매우 강력한 기능을 제공하는데, 예를 들어서 오라클 RDBMS의 CONNECT BY와 동일한 형태의 쿼리를 처리할 수도 있다. WITH RECURSIVE를 이해하기 위해서 employees 라는 테이블(아주 전통적인 WITH RECURSIVE 예제용 테이블)을 생각해보자. (실제 오라클 RDBMS에서도 이제는 SQL 표준 형태인 WITH 절을 지원하므로, 더 이상 오라클 RDBMS에서도 CONNECT BY .. START WITH .. 구문을 사용하지 않아도 된다.)

CREATE TABLE EMPLOYEES (
  ID INT PRIMARY KEY,
  NAME VARCHAR(100),
  MANAGER_ID INT,
  INDEX (MANAGER_ID),
  FOREIGN KEY (MANAGER_ID) REFERENCES EMPLOYEES(ID)
);

INSERT INTO EMPLOYEES VALUES
(333, "Yasmina", NULL),
(198, "John", 333),
(29, "Pedro", 198),
(4610, "Sarah", 29),
(72, "Pierre", 29),
(692, "Tarek", 333);

데이터를 간단히 살펴보면, Yasmina는 CEO이며 John과 Tarek의 보스는 Yasmina이며, Pedro의 보스는 John이다. 그리고 Sarah와 Pierre의 보스는 Pero이다. 매우 큰 회사라면, 이 테이블의 레코드 건수는 몇 천에서 몇 만건이 될 것이다.

이제 각 사원에 대해서 “얼마나 많은 사람들이 직간접적으로 조직 관계를 구성하는지”를 알아내고자 한다. 이를 위해서 우선 매니저가 아닌 사람들의 목록을 만들고자 하는데, 간단히 서브 쿼리를 이용해서 매니저인 사람의 목록을 만들고 NOT IN (subquery)를 이용해서 매니저가 아닌 사람들의 목록을 조회할 수 있다.

SELECT ID, NAME, MANAGER_ID, 0 AS REPORTS
FROM EMPLOYEES
WHERE ID NOT IN (
  SELECT MANAGER_ID FROM EMPLOYEES WHERE MANAGER_ID IS NOT NULL
);

그리고 이 결과를 새로운 테이블 EMPLOYEES_EXTENDED에 저장하자. EMPLOYEES_EXTENDED 테이블의 마지막에는 “이 사원에게 직간접적으로 보고를 하는 사원 수를  저장하는” REPORTS 컬럼을 추가했다. 즉 REPORTS 컬럼의 값이 0인 사원은 매니저가 아닌 것이다. 이제 아래와 같은 쿼리를 이용해서 첫번째 레벨의 매니저(매니저가 아닌 사원의 직접적인 차상위 매니저)를 구할 수 있게 되었다.

SELECT M.ID, M.NAME, M.MANAGER_ID, SUM(1+E.REPORTS) AS REPORTS
FROM EMPLOYEES M 
  JOIN EMPLOYEES_EXTENDED E ON M.ID=E.MANAGER_ID
GROUP BY M.ID, M.NAME, M.MANAGER_ID;

위 쿼리 각 사원(매니저가 아닌)의 직접적인 보스 사원 정보를 0건 1건 이상을 만들어내게 된다. 이때 REPORTS 컬럼의 값이 1인 경우는 매니저가 아닌 사원을 의미하며, 2이상인 경우는 자신에게 보고하는 사원중에서 매니저가 아닌 사원의 수를 리턴하게 된다.

이제 EMPLOYEES_EXTENDED 테이블을 모두 지우고, 바로 위의 쿼리 결과를 EMPLOYEES_EXTENDED 테이블에 채워넣자. 이제 EMPLOYEES_EXTENDED 테이블에는 1차 레벨의 매니저로 채워지게 된다. 그리고 다시 똑같은 쿼리를 실행해보자. 그러면 그 결과로 2차 레벨의 매니저 목록을 조회할 수 있게 된다. 이 과정을 계속 반복하면 결국 Yasmina 레코드 한건만 EMPLOYEES_EXTENDED 테이블에 채워지게 될 것이다. 하지만 마지막 과정에서 SELECT 쿼리는 (E.MANAGER_ID가 NULL일 것이므로) 아무런 레코드를 만들어내지 않게 될 것이다.

지금까지의 과정을 간략히 정리해보면, EXMPLOYEES_EXTENDED 테이블은 매니저가 아닌 사원 그리고 1차 레벨 매니저 그리고 그 다음에는 2차 매니저  그리고 반복해서 조회한 N차 매니저를 저장하는 일종의 임시 버퍼로 사용된 것이다. 여기에서 우리는 재귀적인 기능을 사용한 것이다. 하지만 여기에서 한 가지 문제점은 이렇게 EMPLOYEES_EXENDED 테이블(임시 버퍼)에 저장되었던 결과들을 어떻게 병합(UNION ALL)할 것인지이다.

매니저가 아닌 사원의 목록이 재귀적인 처리의 시작이었는데, 이를 “앵커 멤버(Anchor Member)” 또는 “시드(Seed)”라고 한다. 그리고 재귀적으로 반복해서 실행되는 SELECT 쿼리는 “재귀 멤버(Recursive Member)”라고 한다. 완전히 WITH 절 쿼리는 아래와 같다.

WITH RECURSIVE
# The temporary buffer, also used as UNION result:
EMPLOYEES_EXTENDED
AS
(
  # The seed:
  SELECT ID, NAME, MANAGER_ID, 0 AS REPORTS
  FROM EMPLOYEES
  WHERE ID NOT IN (SELECT MANAGER_ID FROM EMPLOYEES WHERE MANAGER_ID IS NOT NULL)
UNION ALL
  # The recursive member:
  SELECT M.ID, M.NAME, M.MANAGER_ID, SUM(1+E.REPORTS) AS REPORTS
  FROM EMPLOYEES M JOIN EMPLOYEES_EXTENDED E ON M.ID=E.MANAGER_ID
  GROUP BY M.ID, M.NAME, M.MANAGER_ID
)
# what we want to do with the complete result (the UNION):
SELECT * FROM EMPLOYEES_EXTENDED;

MySQL은 아직 WITH RECURSIVE 기능을 제공하지 않는다. 하지만 일반적인 스토어드 프로시져를 이용하면 아래와 같이 간단하게 에뮬레이션할 수 있다.

CALL WITH_EMULATOR(
"EMPLOYEES_EXTENDED",
"
  SELECT ID, NAME, MANAGER_ID, 0 AS REPORTS
  FROM EMPLOYEES
  WHERE ID NOT IN (SELECT MANAGER_ID FROM EMPLOYEES WHERE MANAGER_ID IS NOT NULL)
",
"
  SELECT M.ID, M.NAME, M.MANAGER_ID, SUM(1+E.REPORTS) AS REPORTS
  FROM EMPLOYEES M JOIN EMPLOYEES_EXTENDED E ON M.ID=E.MANAGER_ID
  GROUP BY M.ID, M.NAME, M.MANAGER_ID
",
"SELECT * FROM EMPLOYEES_EXTENDED",
0,
""
);

눈치채고 있듯이, 스토어드 프로그램의 각 파라미터는 WITH 표준 문법의 각 멤버(“임시 버퍼의 이름”, “시드를 위한 쿼리”, “ 재귀 멤버를 위한 쿼리”, “최종 결과를 어떻게 할 것인지”)를 입력으로 사용하고 있다.  마지막 두개 파라미터는 0과 빈 문자값은 지금은 무시하도록 하자.

아래는 스토어드 프로그램 실행 결과이다.

+------+---------+------------+---------+
| ID   | NAME    | MANAGER_ID | REPORTS |
+------+---------+------------+---------+
|   72 | Pierre  |         29 |       0 |
|  692 | Tarek   |        333 |       0 |
| 4610 | Sarah   |         29 |       0 |
|   29 | Pedro   |        198 |       2 |
|  333 | Yasmina |       NULL |       1 |
|  198 | John    |        333 |       3 |
|  333 | Yasmina |       NULL |       4 |
+------+---------+------------+---------+
7 rows in set

Pierre와 Tarek 그리고 Sarah는 아무도 직속 멤버가 없으며, Pedro는 2명의 직속 멤버를 가지고 있다. 하지만 Yasmina는 2번 결과 셋에서 두번 나타났다. 이는 우리의 알고리즘이 매니저가 아닌 사원으로부터 시작했기 때문이다. 즉 나무(Yasmina는 나무의 뿌리)의 가지에서부터 재귀적으로 데이터를 가져왔기 때문이다. 그래서 마지막 쿼리를 이제 조금 바꿔 보았다.

CALL WITH_EMULATOR(
"EMPLOYEES_EXTENDED",
"
  SELECT ID, NAME, MANAGER_ID, 0 AS REPORTS
  FROM EMPLOYEES
  WHERE ID NOT IN (SELECT MANAGER_ID FROM EMPLOYEES WHERE MANAGER_ID IS NOT NULL)
",
"
  SELECT M.ID, M.NAME, M.MANAGER_ID, SUM(1+E.REPORTS) AS REPORTS
  FROM EMPLOYEES M JOIN EMPLOYEES_EXTENDED E ON M.ID=E.MANAGER_ID
  GROUP BY M.ID, M.NAME, M.MANAGER_ID
",
"
  SELECT ID, NAME, MANAGER_ID, SUM(REPORTS)
  FROM EMPLOYEES_EXTENDED
  GROUP BY ID, NAME, MANAGER_ID
",
0,
""
);

이제서야 제대로 된 결과가 나왔다.

+------+---------+------------+--------------+
| ID   | NAME    | MANAGER_ID | SUM(REPORTS) |
+------+---------+------------+--------------+
|   29 | Pedro   |        198 |            2 |
|   72 | Pierre  |         29 |            0 |
|  198 | John    |        333 |            3 |
|  333 | Yasmina |       NULL |            5 |
|  692 | Tarek   |        333 |            0 |
| 4610 | Sarah   |         29 |            0 |
+------+---------+------------+--------------+
6 rows in set

이제 스토어드 프로시져의 실제 코드를 살펴보자. PreparedStatement를 이용하는 동적 쿼리가 상당히 사용된 것을 알 수 있다. 이 스토어드 프로시져는 특별히 해결하기 어려운 제한 사항들을 가지고 있지는 않으므로 다른 용도(대표적으로 각 부서의 조직도를 조회하는 재귀 쿼리)의 재귀적 쿼리에서도 충분히 활용할 수 있을 것이다. 자세한 내용은 스토어드 프로그램 코드의 주석을 참조하자.

# Usage: the standard syntax:
#   WITH RECURSIVE recursive_table AS
#    (initial_SELECT
#     UNION ALL
#     recursive_SELECT)
#   final_SELECT;
# should be translated by you to 
# CALL WITH_EMULATOR(recursive_table, initial_SELECT, recursive_SELECT,
#                    final_SELECT, 0, "").

# ALGORITHM:
# 1) we have an initial table T0 (actual name is an argument
# "recursive_table"), we fill it with result of initial_SELECT.
# 2) We have a union table U, initially empty.
# 3) Loop:
#   add rows of T0 to U,
#   run recursive_SELECT based on T0 and put result into table T1,
#   if T1 is empty
#      then leave loop,
#      else swap T0 and T1 (renaming) and empty T1
# 4) Drop T0, T1
# 5) Rename U to T0
# 6) run final select, send result to client

# This is for *one* recursive table.
# It would be possible to write a SP creating multiple recursive tables.

delimiter ;;

CREATE PROCEDURE with_emulator(
  recursive_table VARCHAR(100),         # name of recursive table
  initial_select VARCHAR(65530),        # seed a.k.a. anchor
  recursive_select VARCHAR(65530),      # recursive member
  final_select VARCHAR(65530),          # final SELECT on UNION result
  max_recursion INT UNSIGNED,           # safety against infinite loop, use 0 for default
  create_table_options VARCHAR(65530)   # you can add CREATE-TABLE-time options
  # to your recursive_table, to speed up initial/recursive/final SELECTs; example: "(KEY(some_column)) ENGINE=MEMORY"
)
BEGIN
  DECLARE new_rows INT UNSIGNED;
  DECLARE show_progress INT DEFAULT 0; # SET to 1 to trace/debug execution
  DECLARE recursive_table_next VARCHAR(120);
  DECLARE recursive_table_union VARCHAR(120);
  DECLARE recursive_table_tmp VARCHAR(120);

  SET recursive_table_next  = CONCAT(recursive_table, "_next");
  SET recursive_table_union = CONCAT(recursive_table, "_union");
  SET recursive_table_tmp   = CONCAT(recursive_table, "_tmp");

  # IF you need to reference recursive_table more than
  # once in recursive_select, remove the TEMPORARY word.

  # 0) cleaning for previous procedure failed
  SET @str = CONCAT("DROP TABLE IF EXISTS ", recursive_table_next);
  PREPARE stmt FROM @str;
  EXECUTE stmt;
  SET @str = CONCAT("DROP TABLE IF EXISTS ", recursive_table_union);
  PREPARE stmt FROM @str;
  EXECUTE stmt;
  SET @str = CONCAT("DROP TABLE IF EXISTS ", recursive_table_tmp);
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  # 1) create and fill T0
  SET @str = CONCAT("CREATE TEMPORARY TABLE ", recursive_table, " ", create_table_options, " AS ", initial_select);
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  # 2) create U
  SET @str = CONCAT("CREATE TEMPORARY TABLE ", recursive_table_union, " LIKE ", recursive_table);
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  # 3) create T1
  SET @str = CONCAT("CREATE TEMPORARY TABLE ", recursive_table_next, " LIKE ", recursive_table);
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  IF max_recursion = 0 THEN
    SET max_recursion = 100; # a default to protect the innocent
  END IF;

  recursion: REPEAT
    # add T0 to U (this is always UNION ALL)
    SET @str = CONCAT("INSERT INTO ", recursive_table_union, " SELECT * FROM ", recursive_table);
    PREPARE stmt FROM @str;
    EXECUTE stmt;

    # we are done IF max depth reached
    SET max_recursion = max_recursion - 1;
    IF not max_recursion THEN
      IF show_progress THEN
        SELECT CONCAT("max recursion exceeded");
      END IF;
      LEAVE recursion;
    END IF;

    # fill T1 by applying the recursive SELECT on T0
    SET @str = CONCAT("INSERT INTO ", recursive_table_next, " ", recursive_select);
    PREPARE stmt FROM @str;
    EXECUTE stmt;

    # we are done IF no rows in T1
    SELECT ROW_COUNT() INTO new_rows;
    IF show_progress THEN
      SELECT CONCAT(new_rows, " new rows found");
    END IF;

    IF NOT new_rows THEN
      LEAVE recursion;
    END IF;

    # Prepare next iteration:
    # T1 becomes T0, to be the source of next run of recursive_select,
    # T0 is recycled to be T1.
    SET @str = CONCAT("ALTER TABLE ", recursive_table, " RENAME ", recursive_table_tmp);
    PREPARE stmt FROM @str;
    EXECUTE stmt;

    # we use ALTER TABLE RENAME because RENAME TABLE does not support temp tables
    SET @str = CONCAT("ALTER TABLE ", recursive_table_next, " RENAME ", recursive_table);
    PREPARE stmt FROM @str;
    EXECUTE stmt;

    SET @str = CONCAT("ALTER TABLE ", recursive_table_tmp, " RENAME ", recursive_table_next);
    PREPARE stmt FROM @str;
    EXECUTE stmt;

    # empty T1
    SET @str = CONCAT("TRUNCATE TABLE ", recursive_table_next);
    PREPARE stmt FROM @str;
    EXECUTE stmt;
  UNTIL 0 END REPEAT;

  # eliminate T0 and T1
  SET @str = CONCAT("DROP TEMPORARY TABLE ", recursive_table_next, ", ", recursive_table);
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  # Final (output) SELECT uses recursive_table name
  SET @str = CONCAT("ALTER TABLE ", recursive_table_union, " RENAME ", recursive_table);
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  # Run final SELECT on UNION
  SET @str = final_select;
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  # No temporary tables may survive:
  SET @str = CONCAT("DROP TEMPORARY TABLE ", recursive_table);
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  # We are done :-)
END ;;

delimiter ;


여기까지의 글은 Oracle의 MySQL 개발자인 "Guilhem Bichot"의 블로그 게시물을 번역한 것입니다.

“Guilhem Bichot”의 블로그 내용에 몇 가지를 추가해보면…

일반적으로 조직도를 조회하는 재귀 쿼리를 위해서 Top-Down 트리를 조회하는 쿼리는 아래와 같이 실행할 수 있다. 각 그룹별로 트리를 순회(Tree traversal)하는 형태의 결과와 같이 레코드를 정렬하기 위해서 sort_key 라는 컬럼도 같이 추가해서 구현해본 예제이다. Sort_key 컬럼에 저장되는 값은 SQL 문장을 보면 쉽게 이해할 수 있을 것으로 보이므로, 자세한 설명은 생략하도록 하겠다.
(물론, 이 경우에도 M-M 관계의 트리가 생성되는 경우라면, GROUP BY가 필요할 수 있을 것으로 보인다.)

CALL with_emulator(
"e$emp",
"
  SELECT id, name, manager_id, 1 as lv, @_seq:=0 as sort_key
  FROM emp
  WHERE manager_id is null
",
"
  SELECT m.id, m.name, m.manager_id, e.lv+1 as lv, CONCAT(e.sort_key, LPAD(@_seq:=@_seq+1, 5, '0')) as sort_key
  FROM emp m JOIN e$emp e ON m.manager_id=e.id
",
"
  SELECT id, name, manager_id, lv, sort_key
  FROM e$emp
  ORDER BY sort_key
",
0,
"(id INT NOT NULL, name VARCHAR(100), manager_id INT, lv INT, sort_key VARCHAR(100), KEY ix_sortkey(sort_key)) ENGINE=MEMORY"
);

+------+---------+------------+------+------------------+
| id   | name    | manager_id | lv   | sort_key         |
+------+---------+------------+------+------------------+
|  333 | Yasmina |       NULL |    1 | 0                |
|  198 | John    |        333 |    2 | 000001           |
|   29 | Pedro   |        198 |    3 | 00000100003      |
|   72 | Pierre  |         29 |    4 | 0000010000300004 |
| 4610 | Sarah   |         29 |    4 | 0000010000300005 |
|  692 | Tarek   |        333 |    2 | 000002           |
+------+---------+------------+------+------------------+
6 rows in set (0.01 sec)



그리고 아래 프로시져는 “Guilhem Bichot”의 블로그에 공유된 프로시져에, 내부적으로 사용되는 임시 테이블중에서 중간 테이블과 최종 테이블의 구조를 다르게 생성하도록 조금 수정해 본 것이다. 중간 과정에서 필요한 테이블에는 인덱스가 있으면 더 성능이 느려지지만, 마지막 결과를 저장하는 최종 테이블에는 인덱스가 있으면 더 효율적인 경우에는 이런 구성이 더 효율적일 수도 있을 것으로 보인다.
또한 추가로 임시 테이블과 쿼리를 위한 세션 변수 변경 및 복구와 SQL Error에 대한 처리 몇 가지를 더 추가한 것이다.

DELIMITER ;;

DROP PROCEDURE with_emulator2;;

CREATE PROCEDURE with_emulator2(
  recursive_table VARCHAR(100),         # name of recursive table
  initial_select VARCHAR(65530),        # seed a.k.a. anchor
  recursive_select VARCHAR(65530),      # recursive member
  final_select VARCHAR(65530),          # final SELECT on UNION result
  max_recursion INT UNSIGNED,           # safety against infinite loop, use 0 for default
  working_table_options VARCHAR(500),   # you can add CREATE-TABLE-time options for intermediate working table
  final_table_options VARCHAR(500)      # you can add CREATE-TABLE-time options for final table
  # to your recursive_table, to speed up initial/recursive/final SELECTs; example: "(KEY(some_column)) ENGINE=MEMORY"
)
BEGIN
  DECLARE loop_count INT UNSIGNED DEFAULT 0;
  DECLARE new_rows INT UNSIGNED;
  DECLARE show_progress INT DEFAULT 0; # SET to 1 to trace/debug execution
  DECLARE recursive_table_next VARCHAR(120);
  DECLARE recursive_table_union VARCHAR(120);
  DECLARE recursive_table_tmp VARCHAR(120);

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ## Restore session variables
    SET @@tmp_table_size = @orig_tmp_table_size;
    SET @@max_heap_table_size = @orig_max_heap_table_size;
    SET @@sort_buffer_size = @orig_sort_buffer_size;

    SET @str = CONCAT("DROP TEMPORARY TABLE IF EXISTS ", recursive_table);
    PREPARE stmt FROM @str;
    EXECUTE stmt;

    SET @str = CONCAT("DROP TEMPORARY TABLE IF EXISTS ", recursive_table_next);
    PREPARE stmt FROM @str;
    EXECUTE stmt;

    SET @str = CONCAT("DROP TEMPORARY TABLE IF EXISTS ", recursive_table_tmp);
    PREPARE stmt FROM @str;
    EXECUTE stmt;

    SET @str = CONCAT("DROP TEMPORARY TABLE IF EXISTS ", recursive_table_union);
    PREPARE stmt FROM @str;
    EXECUTE stmt;

    ## Resignaling the origin exception to user
    RESIGNAL;
  END;

  SET recursive_table_next  = CONCAT(recursive_table, "_next");
  SET recursive_table_union = CONCAT(recursive_table, "_union");
  SET recursive_table_tmp   = CONCAT(recursive_table, "_tmp");

  ## Backup session variables
  SET @orig_tmp_table_size = @@tmp_table_size;
  SET @orig_max_heap_table_size = @@max_heap_table_size;
  SET @orig_sort_buffer_size = @@sort_buffer_size;

  ## Update new session variables for with_emulator
  SET @@tmp_table_size = 1024*1024; # 1MB
  SET @@max_heap_table_size = 1024*1024; # 1MB
  SET @@sort_buffer_size = 1024*1024; # 1MB

  # IF you need to reference recursive_table more than
  # once in recursive_select, remove the TEMPORARY word.
  # 1) create and fill T0
  SET @str = CONCAT("CREATE TEMPORARY TABLE ", recursive_table, " ", working_table_options, " AS ", initial_select);
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  # 2) create U
  ## 작업 테이블과 최종 테이블의 구조를 다르게 가져가기 위해서는 initial_select를 두번 실행할 수밖에 없다.
  ## 작업 테이블에는 인덱스가 필요없지만 최종 테이블에는 인덱스가 필요한 경우가 많으므로, 원본 쿼리대신 recursive_table_union 테이블을 (recursive_table 복사가 아니라) initial_select를 이용해서 다시 생성하도록 수정함.
  ## ORIG :: SET @str = CONCAT("CREATE TEMPORARY TABLE ", recursive_table_union, " LIKE ", recursive_table);
  ## initial_select가 아주 가벼운 쿼리라면 아래 쿼리를 실행해도 무방하지만, initial_select 쿼리가 무겁다면 위의 원본 쿼리가 좋음.
  SET @str = CONCAT("CREATE TEMPORARY TABLE ", recursive_table_union, " ", final_table_options, " AS ", initial_select);
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  # 3) create T1
  SET @str = CONCAT("CREATE TEMPORARY TABLE ", recursive_table_next, " LIKE ", recursive_table);
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  IF max_recursion = 0 THEN
    SET max_recursion = 100; # a default to protect the innocent
  END IF;

  recursion: REPEAT
    IF loop_count > 0 THEN ## Copy recursive_table to recursive_table_union if this is not the first loop
      # add T0 to U (this is always UNION ALL)
      SET @str = CONCAT("INSERT INTO ", recursive_table_union, " SELECT * FROM ", recursive_table);
      PREPARE stmt FROM @str;
      EXECUTE stmt;
    END IF;

    # we are done IF max depth reached
    SET loop_count = loop_count + 1;
    IF loop_count > max_recursion THEN
      IF show_progress THEN
        SELECT CONCAT("max recursion exceeded");
      END IF;
      LEAVE recursion;
    END IF;

    # fill T1 by applying the recursive SELECT on T0
    SET @str = CONCAT("INSERT INTO ", recursive_table_next, " ", recursive_select);
    PREPARE stmt FROM @str;
    EXECUTE stmt;

    # we are done IF no rows in T1
    SELECT ROW_COUNT() INTO new_rows;
    IF show_progress THEN
      SELECT CONCAT(new_rows, " new rows found");
    END IF;

    IF NOT new_rows THEN
      LEAVE recursion;
    END IF;

    # Prepare next iteration:
    # T1 becomes T0, to be the source of next run of recursive_select,
    # T0 is recycled to be T1.
    SET @str = CONCAT("ALTER TABLE ", recursive_table, " RENAME ", recursive_table_tmp);
    PREPARE stmt FROM @str;
    EXECUTE stmt;

    # we use ALTER TABLE RENAME because RENAME TABLE does not support temp tables
    SET @str = CONCAT("ALTER TABLE ", recursive_table_next, " RENAME ", recursive_table);
    PREPARE stmt FROM @str;
    EXECUTE stmt;

    SET @str = CONCAT("ALTER TABLE ", recursive_table_tmp, " RENAME ", recursive_table_next);
    PREPARE stmt FROM @str;
    EXECUTE stmt;

    # empty T1
    SET @str = CONCAT("TRUNCATE TABLE ", recursive_table_next);
    PREPARE stmt FROM @str;
    EXECUTE stmt;
  UNTIL 0 END REPEAT;

  # eliminate T0 and T1
  SET @str = CONCAT("DROP TEMPORARY TABLE ", recursive_table_next, ", ", recursive_table);
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  # Final (output) SELECT uses recursive_table name
  SET @str = CONCAT("ALTER TABLE ", recursive_table_union, " RENAME ", recursive_table);
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  # Run final SELECT on UNION
  SET @str = final_select;
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  # No temporary tables may survive:
  SET @str = CONCAT("DROP TEMPORARY TABLE ", recursive_table);
  PREPARE stmt FROM @str;
  EXECUTE stmt;

  # We are done :-)

  ## Restore session variables
  SET @@tmp_table_size = @orig_tmp_table_size;
  SET @@max_heap_table_size = @orig_max_heap_table_size;
  SET @@sort_buffer_size = @orig_sort_buffer_size;
END ;;

DELIMITER ;




CALL with_emulator2(
"e$emp",
"
  SELECT id, name, manager_id, 1 as lv, @_seq:=0 as sort_key
  FROM emp
  WHERE manager_id is null
",
"
  SELECT m.id, m.name, m.manager_id, e.lv+1 as lv, CONCAT(e.sort_key, LPAD(@_seq:=@_seq+1, 5, '0')) as sort_key
  FROM emp m JOIN e$emp e ON m.manager_id=e.id
",
"
  SELECT id, name, manager_id, lv, sort_key
  FROM e$emp
  ORDER BY sort_key
",
0,
"(id INT NOT NULL, name VARCHAR(100), manager_id INT, lv INT, sort_key VARCHAR(100)) ENGINE=MEMORY",
"(id INT NOT NULL, name VARCHAR(100), manager_id INT, lv INT, sort_key VARCHAR(100), KEY ix_sortkey(sort_key)) ENGINE=MEMORY"
);

+------+---------+------------+------+------------------+
| id   | name    | manager_id | lv   | sort_key         |
+------+---------+------------+------+------------------+
|  333 | Yasmina |       NULL |    1 | 0                |
|  198 | John    |        333 |    2 | 000001           |
|   29 | Pedro   |        198 |    3 | 00000100003      |
|   72 | Pierre  |         29 |    4 | 0000010000300004 |
| 4610 | Sarah   |         29 |    4 | 0000010000300005 |
|  692 | Tarek   |        333 |    2 | 000002           |
+------+---------+------------+------+------------------+
6 rows in set (0.02 sec)