🔎 들어가기 전
회사에서 임시테이블을 이용하여 데이터를 조회하는 프로시저를 생성했었다.
그러던 중 특정 테이블을 조회했는데 해당 테이블에 있는 데이터가 조회되지 않았고,
테이블을 조회하기 직전에 호출했던 프로시저 내용이 조회되었다.
응.....? 갑자기 왜 이러지?.. 너무나 당황했다.
1차적으로 든 생각은
내가 호출한 프로시저 내부에서
임시테이블이라고 생성 또는 DROP한 테이블이 임시테이블이 아니라 그냥 테이블이었나?
다행히 임시테이블을 생성한게 맞았고,
실제 테이블명과 같게 생성하여
해당 세션에서는 임시테이블이 우선으로 보여지고 있는 상황이었다.
(이런 실수를 하다니......sql 모드는 이런거 안잡아주나..?)
아무튼 실수를 통해 또 한가지 배웠다.
정말 감사하게도 개발 DB에서 배운게 다행!!
그래서 실수를 절대 반복하지 않기 위해 임시테이블에 대해 포스팅해보려고 한다!
📍Temporary Table(임시테이블) 이란?
[공식 문서 정의]
A TEMPORARY table is visible only within the current session, and is dropped automatically when the session is closed. This means that two different sessions can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.)
즉, 특정 사용자가 접속한 해당 세션에서만 생성하고 조회 가능한 임시 테이블이다.
📌 임시테이블 특징
- DML(SELECT, INSERT, UPDATE, DELETE)을 사용할 수 있다
- 일반테이블처럼 영구적으로 저장되는 테이블이 아니라 임시테이블을 만들고 데이터를 잠깐 처리할 때 사용할 때 유용하다.
즉, 일시적으로 처리할 데이터들을 한번에 모아놓고 처리하고싶다?
그럴 때 임시테이블 사용하면 좋다.
예를 들어, 문자 알림을 보내고 싶은데 특정 조건에 있는 사람한테만 보내고 싶고,
중복된 번호들은 한번만 보내고 싶다!
해당 조건을 select 후 임시테이블에 저장해서 한번 가공된 데이터들을 쫘락 select하면 편하다!- 임시 테이블을 생성했던 해당 세션이 끊기면 아래 이미지와 같이 자동으로 DROP된다.
📌사용법
1. 임시테이블 생성 방법
CREATE TEMPORARY TABLE [IF NOT EXISTS] 테이블명 (create_definition,...) [table_options] [partition_options]
- create_definition
insert할 컬럼과 데이터 타입 정의- IF NOT EXISTS
'table already exists' 오류를 피하기 위해 사용
사용하지 않아도 문법적 오류는 없음
2. 임시테이블 제거 방법
방법 1. 현재 세션 끊기: 임시테이블이 생성된 현재 세션이 끊겼을 시 자동으로 삭제 됨
방법 2. DROP TEMPORARY TABLE 실행
DROP [TEMPORARY] TABLE [IF EXISTS] 테이블명 [, 테이블명] ...
[RESTRICT | CASCADE]참고사항
RESTRICT :테이블 삭제 시 다른 데이터가 삭제할 데이터를 참조하고 있을 경우 변경/삭제가 취소
CASCADE :테이블 삭제 시 다른 데이터가 삭제할 데이터를 참조하고 있을 경우 함께 삭제
임시테이블 사용법은 의외로 간단하다.
테이블과 같이 CREATE해주고, DROP해주고 다른 DDL도 사용가능하다.
그렇다면, 임시테이블은 어떨 때 사용하는 것이 좋을까?
쿼리 성능에 크게 도움을 줄까?
📌임시테이블 사용 시 성능에 크게 도움이 될까?
-
일단, SELECT 시에는 Temporary Table을 사용하는 것은 성능상으로 전혀 도움이 되지 않는다.
테이블을 생성하고, 삭제하고 데이터를 insert하여 join select하는 등 불필요한 단계로 인해 도움이 되지 않는다고 한다. -
하지만, 중간 결과를 저장하거나, 빈도있는 재사용을 위한 목적이라면 큰 효과가 있다.
여러개의 JOIN이 걸려있고 조건이 있는 SELECT를 여러번하는 것 보단
여러개의 JOIN이 걸려있고 조건이 있는 SELECT를 한번 하고 임시테이블에 저장하여 계속 재사용한다면 큰 효율이 있다. -
Update 시에 가장 추천한다고 한다. 동시에 업데이트 처리해야하는 경우 일반 테이블을 사용할 때 보다 한번 가공된 테이블과 조인해서 업데이트할 때 성능이 좋다고 한다.
-
임시테이블 성능 비교 (아래 블로그 참고)
https://gywn.net/2012/06/mysql-temporary-table-effect/
📌 사용 시 주의사항
주의점
임시테이블명은 실제 테이블명과 같은 이름으로 사용하지 않는다.
임시테이블과 일반 테이블명이 같지 않도록 임시 테이블명 앞에 TEMP_테이블명 이렇게 해주는 것이 좋다.
같은 이름으로 생성이 되긴 된다.
하지만 세션이 끊기기 전까지 실제 테이블은 조회할 수 없고 임시테이블이 조회된다.
왜냐?
기본적으로 임시테이블이 우선으로 조회되기 때문이다.
✍️ 결론
대용량 데이터의 처리가 아닐 경우 임시테이블의 성능은 크게 도움이 되지 않는다.
하지만 아래의 상황에서는 임시테이블을 사용하는게 낫다.
IF/ELSEIF/ELSE로 조건이 나누게 된다면 그때 그때 조건에 따라 조인할 것이 아니라 임시테이블로 데이터를 한 번 가공한 후 해당 테이블로 일괄적으로 JOIN할 때?
위에서 말한 것과 같이 재사용할 수 있으니까?! 쿼리 가독성 또한 좋아진다.
아무튼 반성의 시간 끝!!
🧷 참고자료
