[MYSQL] TEMPORARY TABLE 이란

 

 최근 특정 업체와 결제 서비스에 관련해 연동작업을 진행하면서 대량 발송을 위해 배치작업이 요구되는 사항이 발생하였습니다.

대량 발송 시 요구되었던 작업이 발송을 하기 위해 건수 및 발송에 대한 정보를 읽고 임시테이블에 저장한 후 그것을 발송시켜야 하는 것이었습니다. 여기서 만약 3만 건을 요청한다고 한다면, 3만건에 대한 배치작업을 하기위해 3만건의 데이터가 생길 것이고 단지 1회 조회용을 위한 데이터라면 불필요한 테이블과 데이터가 생성될 수 있습니다.

이러한 데이터 생성을 방지하기 위한 것이 Temporary table 입니다.

 

MYSQL Temporary table 은 다음과 같은 특징을 가지고 있습니다.

1. temporary table 은 CREATE TEMPORARY TABLE 구문을 사용해서 만들어집니다. TEMPORARY 가 CREATE 와 TABLE 사이에 들어갔다는 것에 주의해야합니다.
2. MYSQL 은 temporary table 을 session 이 끝나거나 connection 이 종료되었을 때 자동으로 삭제합니다. 물론, 더 이상 테이블을 사용하지 않는다면 명쾌하게 DROP TABLE 을 사용하여 제거할 수 있습니다.
3. temporary table 은 오직 접근 가능한 client 만 접근할 수 있습니다. 다른 client들은 오직 특정 client 만 table 을 볼 수 있기 때문에 특별한 오류 없이 같은 이름의 temporary table 을 생성할 수 있습니다. 하지만, 같은 session 에 두개의 temporary tables 는 같은 이름의 table 이 공유될 수 없습니다.

 

 database 에 일반 table 과 같은 이름의 temporary table 을 생성할 수 있습니다. 예를들어, 만약 employess 라는 temporary table 을 같은 database 에 같은 이름의 table 이 존재하는 상태에서 생성하였다면, 기존에 있는 employess 테이블은 접근할 수 없게 됩니다. employees table 에서의 모든 query 는 temporary table 의 employees 로 querying 됩니다.

비록 temporary table 이 같은 이름의 테이블을 생성할 수 있지만, 추천되지는 않습니다. 왜냐하면 혼란을 야기시킬 수 있으며, 예상치 못한 손실을 잠재하고 있기 떄문입니다.
예를들어, connection 되어있던 database 가 connection 을 잃고, 자동적으로 reconnect 한다면 temporary table 과 일반 테이블과 구별할 수 없기 때문입니다. 그러므로, DROP TABLE 구문을 실행하게 되면 temporary table 이 삭제되는 것이 아니라, 일반 테이블이 삭제될 수 있습니다. 이러한 문제를 피하기 위해서, temporary table 을 삭제할 때 DROP TEMPORARY TABLE 구문을 사용해야 합니다.

 

 

MYSQL CREATE TEMPORARY TABLE 예시

    CREATE TEMPORARY TABLE table_name( 
        column_1_definition,
        column_2_definition,
        ...,
        table_constraints
    );

    CREATE TEMPORARY TABLE top_costomers
    SELECT p.customerNumber,
            c.customerName, 
            ROUND(SUM(p.aount), 2) sales
    FROM payments p
    INNETER JOIN customers c ON c.customerNumber = p.customerNumber
    GROUP BY p.customerNumber
    ORDER BY sales DESC
    LIMIT 10;

DROP TEMPORARY TABLE 예시

DROP TABLE table_name 형식으로 삭제할 수 있지만, 위에서 설명했던 이유와 같이 TEMPORARY 를 붙여주는 것이 좋습니다.

    DROP TEMPORARY TABLE table_name;

 

 만약, conntection pooling 이나 persistent connection 기반의 application 을 개발하고 있다면, TEMPORARY TABLE application 이 종료되었을 떄 자동으로 삭제된다는 것을 보장해주지 않습니다.  왜냐하면, application 이 사용하는 database connection 이 아직 connection pool 이 다른 client 가 재사용하기 위해 유지되어 삭제가 되지 않을 수 있기 떄문입니다. 그러므로, 더 이상 사용하지 않는다면 사용이 끝난 TEMPORARY TABLE 은 DROP 문을 사용하여 명시적으로 삭제해주는 것이 좋습니다.

 최근 3만건 발송을 위해 1000건씩 30번 발송하는 테스트를 TEMPORARY TABLE 을 생성하여 진행하였습니다. 생성하여 조회 후 업데이트 치고나서 connection 이 끊어지면서 잘 삭제가 되다가 1만건 정도 지나고 나서부터 쿼리 시간이 지연되는 문제가 발생하였습니다. 자세히 확인해 보니 1만건 이전까지는 1000개의 ROW 가 잘 생성되어 삭제되고 있다가, 어느 순간 이후부터는 2000, 3000, 9000건이 쌓이면서 시간이 지연되는 현상이었습니다. 초기에 트래픽이 적을 때는 connection 이 정상적으로 끊기면서 TEMPORARY TABLE 이 삭제가 원할하게 진행되었지만, 트래픽이 증가하면서 처리가 밀리는 현상이 발생하여 connection 을 계속 물고있게 되면서 삭제가 발생하지 않는 문제가 발생하였습니다. 그 후 TEMPORARY TABLE 을 강제로 DROP TEMPORARY TABLE 을 사용하여 삭제함으로써 문제를 해결할 수 있었습니다.

 TEMPORARY TABLE 은 자동으로 생성하고, 삭제해주는 구조를 가지기 때문에 용도와 필요성에 따라 잘 구분하여 사용할 필요가 있습니다. 다음 블로그에서 이와 같은 이슈가 잘 정리되어 있으므로 한번 확인해보고 사용하는걸 추천드립니다. 작성된 블로그를 보게되면, 시간대를 일반 TABLE 과 DML 을 구분하여 시간을 비교한 것을 볼 수 있습니다.

 UPDATE 문에서는 TEMPORARY TABLE 이 TPS 가 훨씬 빠른 것을 알 수 있지만, SELECT 에서는 일반 테이블이 TPS 가 우수한 것을 볼 수 있습니다.

https://gywn.net/2012/06/mysql-temporary-table-effect/

 

이처럼, 어떤 DML 을 더 많이 사용하는지, 쌓이는 데이터가 정말 dummy 데이터가 맞는지, 얼마나 많은 양을 저장할지에 대해서 조사한 후 구분하여 사용해야 합니다.

 

 

 

참고 사이트 

http://www.mysqltutorial.org/mysql-temporary-table/

https://www.tutorialspoint.com/mysql/mysql-temporary-tables.htm