MySQL 임시 테이블 설명: 생성, 사용, 삭제 + 모범 사례 (SQL 예제 포함)

1. (초보자 친화) MySQL 임시 테이블이란? 일반 테이블과의 차이점

소개

MySQL로 데이터를 관리할 때 데이터를 일시적으로 저장해야 할 때가 있습니다. 예를 들어, 대용량 데이터셋을 처리할 때 작업 중간 결과를 저장하고 싶을 수 있습니다. 이런 경우 임시 테이블(Temporary Table)이 매우 유용합니다.

이 글에서는 MySQL 임시 테이블의 기본 메커니즘과 일반 테이블과의 차이점을 설명합니다.

1-1. 임시 테이블이란?

임시 테이블은 데이터베이스 세션(연결) 동안에만 존재하는 특수한 테이블입니다.
일반 테이블과 달리 세션이 종료되면 자동으로 삭제되므로 일시적인 데이터를 저장하기에 적합합니다.

임시 테이블의 주요 특징

• 세션당 격리 임시 테이블은 생성한 세션 내에서만 접근할 수 있으며, 다른 세션에서는 참조할 수 없습니다.
• 세션 종료 시 자동 삭제 임시 테이블은 세션이 종료될 때 자동으로 사라지며, 명시적으로 DROP하지 않아도 됩니다.
• 동일한 이름의 임시 테이블을 생성할 수 있음 일반 테이블과 달리 다른 세션에서 동일한 이름의 임시 테이블을 생성 할 수 있습니다.

1-2. 일반 테이블과의 차이점

임시 테이블과 일반 테이블은 다음과 같이 다릅니다.

어느 것을 사용해야 할까요?

• 데이터를 일시적으로만 필요하고 처리 후 폐기해도 되는 경우 → 임시 테이블
• 데이터를 영구적으로 보관하고 나중에 재사용하고 싶은 경우 → 일반 테이블

예를 들어, 대규모 데이터 분석이나 일시적인 집계와 같은 작업에 임시 테이블이 매우 유용합니다.

1-3. 임시 테이블이 필요한 경우

MySQL 임시 테이블은 특히 다음과 같은 상황에서 유용합니다.

1) 쿼리 성능 향상

예를 들어, 복잡한 JOIN 작업을 수행할 때 미리 중간 데이터를 저장할 임시 테이블을 생성하면 처리 시간을 줄일 수 있습니다.

예시: JOIN 오버헤드 감소

CREATE TEMPORARY TABLE temp_users AS
SELECT id, name FROM users WHERE status = 'active';

대상 데이터를 먼저 임시 테이블에 저장한 뒤 JOIN을 수행하면 성능이 향상될 수 있습니다.

2) 데이터를 일시적으로 저장

애플리케이션이 데이터를 일시적으로 관리해야 할 때도 임시 테이블이 유용합니다.
예를 들어, 사용자가 검색한 데이터를 임시 테이블에 저장하고 세션 종료 시 삭제하도록 할 수 있습니다.

3) 배치 처리용 중간 테이블

대량 데이터를 처리할 때 임시 테이블을 중간 테이블로 사용하면 프로세스의 안정성을 높일 수 있습니다.

1-4. 임시 테이블의 제한 사항

임시 테이블은 편리하지만 몇 가지 제한 사항이 있습니다.

1) 세션 종료 시 자동 삭제

임시 테이블은 세션이 종료될 때 자동으로 삭제되므로 영구적인 데이터 저장에는 적합하지 않습니다.

2) 다른 세션에서 접근 불가

임시 테이블은 생성한 세션 내에서만 사용할 수 있어 다른 사용자나 프로세스와 공유할 수 없습니다.

3) 동일한 이름의 일반 테이블과 충돌 가능성

동일한 이름의 일반 테이블이 존재할 경우, 그 이름으로 임시 테이블을 생성하면 일반 테이블이 일시적으로 보이지 않게 되므로 주의가 필요합니다.

CREATE TEMPORARY TABLE users (id INT, name VARCHAR(255));
SELECT * FROM users; -- This query references the temporary table data

위와 같이 임시 테이블을 생성하면 해당 이름의 일반 테이블은 임시 테이블이 사라질 때까지 접근할 수 없습니다. 테이블 이름을 신중히 선택하세요.

요약

MySQL 임시 테이블은 일시적인 데이터 저장 및 쿼리 최적화를 위한 편리한 기능입니다.
일반 테이블과의 차이점을 이해하고 적절히 활용하면 데이터를 보다 효율적으로 처리할 수 있습니다.

✔ 빠른 요약

• 임시 테이블은 세션이 종료될 때 자동으로 제거됩니다
• 일반 테이블과 달리 세션마다 격리됩니다
• 임시 저장 및 쿼리 성능 향상에 적합합니다
• 세션이 종료되면 데이터가 사라지므로 영구 저장에는 적합하지 않습니다
• 다른 세션에서는 접근할 수 없으며, 동일한 이름의 일반 테이블과 충돌할 수 있습니다

2. (샘플 코드 포함) MySQL에서 임시 테이블을 생성하는 방법

소개

이전 섹션에서는 임시 테이블의 기본 개념과 일반 테이블과의 차이점을 설명했습니다. 이번 섹션에서는 임시 테이블을 생성하는 방법과 그 안의 데이터를 다루는 방법을 살펴보겠습니다.

임시 테이블을 만드는 것은 간단하지만 올바른 구문을 사용하지 않으면 예상대로 동작하지 않을 수 있습니다. 이 섹션에서는 기본 구문, 기존 테이블에서 생성하기, 그리고 임시 테이블을 확인하는 방법을 자세히 설명합니다.

2-1. 임시 테이블의 기본 구문

임시 테이블을 만들려면 CREATE TEMPORARY TABLE 문을 사용합니다.

기본 구문

CREATE TEMPORARY TABLE table_name (
    column_name data_type [constraints],
    column_name data_type [constraints],
    ...
);

CREATE TABLE과 거의 동일한 구문이지만 TEMPORARY 를 추가하면 임시 테이블이 됩니다.

예시: 임시 테이블에 사용자 정보를 저장하기

CREATE TEMPORARY TABLE temp_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

이 temp_users 테이블은 현재 세션에서만 유효하며 세션이 종료될 때 자동으로 제거됩니다.

2-2. 기존 테이블의 데이터를 기반으로 임시 테이블 생성

기존 테이블의 데이터를 기반으로 임시 테이블을 생성할 수도 있습니다.

구문

CREATE TEMPORARY TABLE temp_table_name AS
SELECT * FROM existing_table WHERE condition;

예시: 활성 사용자만 임시 테이블에 저장하기

CREATE TEMPORARY TABLE active_users AS
SELECT id, name, email FROM users WHERE status = 'active';

이 방법은 users 테이블에서 status = 'active'인 사용자만 추출하여 active_users라는 새로운 임시 테이블에 저장합니다.

핵심 포인트

• 기존 테이블의 데이터를 그대로 복사합니다
• 컬럼 데이터 타입이 자동으로 설정됩니다
• 인덱스는 복사되지 않으므로 필요에 따라 명시적으로 추가해야 합니다

2-3. 임시 테이블 데이터 확인 방법

테이블 목록

SHOW TABLES;

하지만 임시 테이블은 일반 SHOW TABLES 결과에 나타나지 않습니다.

임시 테이블 구조 확인

DESC temp_users;

or

SHOW CREATE TABLE temp_users;

이를 통해 임시 테이블의 컬럼 구조와 제약 조건을 확인할 수 있습니다.

2-4. 임시 테이블에 데이터 삽입

임시 테이블에 데이터를 삽입하는 것은 일반 테이블과 동일합니다.

데이터 삽입

INSERT INTO temp_users (name, email) VALUES 
('Taro Tanaka', 'tanaka@example.com'),
('Hanako Sato', 'sato@example.com');

데이터 확인

SELECT * FROM temp_users;

이를 통해 데이터가 임시 테이블에 저장되었는지 확인할 수 있습니다.

2-5. 임시 테이블 생성 시 주의사항

1) 테이블 이름 충돌 주의

임시 테이블을 일반 테이블과 동일한 이름으로 생성하면 임시 테이블이 우선권을 갖게 되며, 일반 테이블은 일시적으로 접근할 수 없게 됩니다.

CREATE TEMPORARY TABLE users (id INT, name VARCHAR(50));
SELECT * FROM users; -- This returns data from the temporary table

따라서 임시 테이블 이름에는 “temp_”와 같은 접두사를 사용하는 것이 권장됩니다.

2) 인덱스가 자동으로 상속되지 않음

기존 테이블에서 데이터를 복사할 때 인덱스가 자동으로 적용되지 않습니다. 필요하다면 인덱스를 명시적으로 추가해야 합니다.

ALTER TABLE temp_users ADD INDEX (email);

3) 임시 테이블을 만들기 위한 권한이 필요합니다

임시 테이블을 만들려면 CREATE TEMPORARY TABLES 권한이 필요합니다.

GRANT CREATE TEMPORARY TABLES ON database_name.* TO 'user'@'localhost';

이 권한이 없으면 임시 테이블을 만들 수 없습니다.

요약

이 섹션에서는 임시 테이블을 만드는 방법을 설명했습니다.

✔ 빠른 요약

• CREATE TEMPORARY TABLE 로 임시 테이블 생성
• 기존 테이블의 데이터를 복사하여 만들 수도 있습니다
• 세션이 종료될 때 자동으로 제거됩니다
• 인덱스가 자동으로 적용되지 않으니 주의하세요
• 이름 충돌을 방지하려면 “temp_”와 같은 접두사를 사용하세요
• 적절한 권한(CREATE TEMPORARY TABLES)이 필요합니다

3. MySQL 임시 테이블에서 데이터 조작하기 (INSERT, UPDATE, DELETE)

소개

이전 섹션에서는 MySQL에서 임시 테이블을 만드는 방법을 설명했습니다.
이 섹션에서는 특정 SQL 명령을 사용하여 임시 테이블에 데이터를 삽입, 업데이트 및 삭제하는 방법을 설명합니다.

임시 테이블은 일반 테이블과 동일한 데이터 작업을 지원하지만, 기억해 두어야 할 중요한 주의사항이 있으며 이를 또한 다룰 것입니다.

3-1. 임시 테이블에 데이터 삽입 (INSERT)

임시 테이블에 데이터를 추가하려면 일반 테이블과 마찬가지로 INSERT INTO 문을 사용합니다.

기본 구문

INSERT INTO temp_table_name (column1, column2, ...) 
VALUES (value1, value2, ...);

예시: 사용자 정보 추가

CREATE TEMPORARY TABLE temp_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO temp_users (name, email) 
VALUES 
('Taro Tanaka', 'tanaka@example.com'),
('Hanako Sato', 'sato@example.com');

INSERT…SELECT 로 기존 데이터 삽입

기존 테이블에서 데이터를 가져와 임시 테이블에 삽입할 수도 있습니다.

INSERT INTO temp_users (id, name, email)
SELECT id, name, email FROM users WHERE status = 'active';

이 방법을 사용하면 활성 사용자만 임시 테이블에 저장할 수 있습니다.

3-2. 임시 테이블의 데이터 업데이트 (UPDATE)

임시 테이블의 데이터를 변경하려면 UPDATE 문을 사용합니다.

기본 구문

UPDATE temp_table_name 
SET column_name = value
WHERE condition;

예시: 사용자의 이름 업데이트

UPDATE temp_users 
SET name = 'Ichiro Tanaka'
WHERE email = 'tanaka@example.com';

조건에 맞는 행을 대량 업데이트

예를 들어, 특정 도메인의 이메일 주소를 example.jp 로 변경하려면 다음과 같이 작성할 수 있습니다:

UPDATE temp_users 
SET email = REPLACE(email, 'example.com', 'example.jp')
WHERE email LIKE '%@example.com';

3-3. 임시 테이블에서 데이터 삭제 (DELETE)

데이터를 삭제하려면 DELETE 문을 사용합니다.

기본 구문

DELETE FROM temp_table_name WHERE condition;

예시: 특정 사용자의 데이터 삭제

DELETE FROM temp_users WHERE email = 'tanaka@example.com';

모든 행 삭제 (TRUNCATE와의 차이점)

모든 행을 삭제하려면 다음과 같이 작성할 수 있습니다:

DELETE FROM temp_users;

반면에 일반 테이블에서는 TRUNCATE TABLE을 사용하면 종종 더 빠르게 모든 행을 삭제할 수 있습니다. 하지만 MySQL에서는 임시 테이블에 TRUNCATE를 사용할 수 없습니다.

TRUNCATE TABLE temp_users; -- Error (cannot be used on temporary tables in MySQL)

따라서 임시 테이블의 모든 행을 제거하려면 DELETE를 사용해야 합니다.

3-4. 임시 테이블에서 데이터 조작 시 주의사항

1) 세션이 종료되면 데이터가 사라짐

임시 테이블은 세션(연결)이 종료될 때 자동으로 제거됩니다, 따라서 지속적인 데이터 저장이 필요한 경우에는 적합하지 않습니다.

2) 다른 세션에서는 접근 불가

임시 테이블은 이를 생성한 세션 내에서만 유효하며, 다른 세션에서는 접근할 수 없습니다.

SELECT * FROM temp_users;

다른 세션에서 이 SQL을 실행하면 “Table ‘temp_users’ doesn’t exist” 오류가 발생합니다.

3) 임시 테이블의 인덱스는 자동으로 적용되지 않습니다

CREATE TEMPORARY TABLE ... AS SELECT ... 로 테이블을 생성하면,
원본 테이블의 인덱스가 상속되지 않습니다. 필요하다면 ALTER TABLE을 사용해 인덱스를 수동으로 추가하세요.

ALTER TABLE temp_users ADD INDEX (email);

요약

이 섹션에서는 임시 테이블에 대한 데이터 조작(INSERT, UPDATE, DELETE)을 다루었습니다.

✔ 빠른 요약

• INSERT 사용: 데이터를 추가합니다 ( INSERT INTO ... VALUES / INSERT INTO ... SELECT )
• UPDATE 사용: 데이터를 수정합니다 (조건부 업데이트 및 REPLACE() 활용)
• DELETE 사용: 데이터를 삭제합니다 ( DELETE FROM ... WHERE ; TRUNCATE는 허용되지 않음)
• 세션이 종료될 때 임시 테이블이 제거됩니다
• 다른 세션에서는 접근할 수 없습니다
• 인덱스가 자동으로 상속되지 않으며, 필요하면 수동으로 추가해야 합니다

4. MySQL 임시 테이블은 자동으로 삭제되나요? 수동으로 삭제하는 방법

소개

일반 테이블과 달리 MySQL 임시 테이블(Temporary Table)은 세션이 종료될 때 자동으로 제거됩니다. 그러나 수동으로 삭제해야 할 경우도 있습니다.

이 섹션에서는 자동 제거가 어떻게 작동하는지와 임시 테이블을 수동으로 삭제하는 방법을 자세히 설명합니다.

4-1. 임시 테이블의 자동 제거 작동 방식

1) 세션 종료 시 자동으로 제거됩니다

MySQL 임시 테이블은 이를 생성한 세션(데이터베이스 연결)이 종료될 때 자동으로 제거됩니다.
따라서 일반적으로 수동으로 삭제할 필요가 없습니다.

예시: 세션 종료 시 자동 제거

-- Create a temporary table in a new session
CREATE TEMPORARY TABLE temp_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100)
);

-- Insert data
INSERT INTO temp_users (name, email) VALUES ('Taro Tanaka', 'tanaka@example.com');

-- End the session (disconnect the MySQL client)
EXIT;

이 시점에서 임시 테이블 temp_users는 자동으로 제거됩니다.

2) 세션이 계속되는 동안 임시 테이블은 유지됩니다

임시 테이블은 세션별로 관리되므로 세션이 열려 있는 한 제거되지 않습니다.

SELECT * FROM temp_users; -- Data can be retrieved if the session is still active

즉, MySQL 클라이언트를 닫거나 프로그램이 연결을 끊을 때까지 임시 테이블은 메모리에 남아 있습니다.

4-2. 임시 테이블을 수동으로 삭제하는 방법

임시 테이블을 수동으로 삭제할 수도 있습니다.
MySQL에서는 DROP TEMPORARY TABLE을 사용해 임시 테이블을 제거합니다.

1) DROP TEMPORARY TABLE 사용

DROP TEMPORARY TABLE temp_users;

이 명령은 즉시 임시 테이블 temp_users를 제거합니다.

2) IF EXISTS 추가하여 오류 방지

테이블이 존재하지 않을 경우 IF EXISTS를 사용해 오류를 방지할 수 있습니다.

DROP TEMPORARY TABLE IF EXISTS temp_users;

이 구문은 테이블이 존재하지 않아도 오류를 방지합니다.

3) 일반 DROP TABLE과 차이점

일반 DROP TABLE을 사용해 임시 테이블을 삭제하려고 하면 다음과 같은 오류가 발생할 수 있습니다:

DROP TABLE temp_users;

오류:

ERROR 1051 (42S02): Unknown table 'temp_users'

MySQL은 일반 테이블과 임시 테이블을 별도로 관리하므로, 임시 테이블을 삭제할 때는 DROP TEMPORARY TABLE을 사용해야 합니다.

4-3. 임시 테이블이 삭제되었는지 확인하는 방법

1) SHOW TABLES로는 확인할 수 없습니다

임시 테이블은 SHOW TABLES 출력에 나타나지 않습니다.

SHOW TABLES;

→ 임시 테이블은 목록에 표시되지 않습니다

2) INFORMATION_SCHEMA를 사용해 확인

INFORMATION_SCHEMA를 조회하여 임시 테이블이 존재하는지 확인할 수 있습니다.

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME = 'temp_users';

이 쿼리가 결과를 반환하면 임시 테이블이 존재함을 나타냅니다.

4-4. 임시 테이블을 삭제할 때 주의사항

1) 임시 테이블은 세션마다 다릅니다

여러 세션에서 동일한 이름의 임시 테이블을 만들 수 있습니다.
다른 세션에서 만든 임시 테이블은 삭제할 수 없습니다.

예시

-- Created in session A
CREATE TEMPORARY TABLE temp_data (id INT);

-- Attempt to drop in session B
DROP TEMPORARY TABLE temp_data;

오류가 발생합니다:

ERROR 1051 (42S02): Unknown table 'temp_data'

임시 테이블은 생성한 세션에서만 삭제할 수 있습니다.

2) 동일한 이름의 일반 테이블과 충돌 가능성

임시 테이블이 일반 테이블과 같은 이름을 가지고 있으면,
임시 테이블이 우선권을 갖고 일반 테이블은 보이지 않게 됩니다.

예시

-- A regular table (users) exists
SELECT * FROM users;

-- Create a temporary table with the same name
CREATE TEMPORARY TABLE users (id INT, name VARCHAR(50));

-- This now references the temporary table users
SELECT * FROM users;

해결책:

• 임시 테이블에 temp_와 같은 접두사를 사용하여 이름 충돌을 방지합니다.

요약

이 섹션에서는 임시 테이블 제거 방법과 삭제 방법에 대해 설명했습니다.

✔ 빠른 요약

• 세션이 종료될 때 임시 테이블은 자동으로 제거됩니다
• 세션이 활성화된 동안 유지됩니다
• 수동으로 삭제하려면 DROP TEMPORARY TABLE을 사용합니다
• 오류를 방지하려면 IF EXISTS를 추가합니다
• SHOW TABLES로 임시 테이블을 확인할 수 없습니다
• 임시 테이블은 생성한 세션에서만 삭제할 수 있습니다
• 동일한 이름의 일반 테이블과 충돌을 피하려면 접두사를 사용합니다

5. MySQL 임시 테이블의 5가지 실용적 사용 사례 (성능 최적화 포함)

소개

MySQL 임시 테이블을 사용하면 중간 데이터를 저장하고 복잡한 쿼리를 단순화하여 데이터베이스 성능을 향상시킬 수 있습니다.

이 섹션에서는 임시 테이블의 다섯 가지 실용적 사용 사례를 소개합니다.
실제 시나리오에서 어떻게 활용할 수 있는지와 샘플 SQL을 함께 설명합니다.

5-1. 쿼리 성능 최적화 (JOIN 오버헤드 감소)

문제

대용량 데이터셋을 처리할 때 JOIN 연산을 직접 실행하면 성능이 저하될 수 있습니다.

해결책

JOIN을 수행하기 전에 임시 테이블을 사용해 대상 데이터를 미리 필터링하면 처리 오버헤드를 줄일 수 있습니다.

예시: 활성 사용자에 대한 주문 데이터 조회

-- First, store only active users in a temporary table
CREATE TEMPORARY TABLE temp_active_users AS
SELECT id, name FROM users WHERE status = 'active';

-- Perform JOIN using the temporary table
SELECT o.order_id, t.name, o.total_price
FROM orders o
JOIN temp_active_users t ON o.customer_id = t.id;

이점

• 전체 users 테이블이 아니라 활성 사용자만 대상으로 함으로써 JOIN 작업량을 줄입니다
• 주 쿼리를 단순화하여 가독성을 향상시킵니다

5-2. 임시 집계 처리

문제

같은 집계 쿼리를 반복해서 실행하면 성능이 저하될 수 있습니다.

해결책

집계 결과를 한 번 임시 테이블에 저장하여 불필요한 반복 계산을 방지합니다.

예시: 월별 매출 데이터를 임시 테이블에 저장

-- Calculate monthly total sales and store in a temporary table
CREATE TEMPORARY TABLE temp_monthly_sales AS
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, SUM(total_price) AS total_sales
FROM orders
GROUP BY month;

-- Retrieve aggregated results
SELECT * FROM temp_monthly_sales WHERE total_sales > 100000;

이점

• 집계 데이터를 여러 번 재사용
• 중복 계산을 피함으로써 성능 향상

5-3. 배치 처리용 중간 데이터 저장

문제

대량 업데이트 또는 삭제를 수행할 때, 처리 중 오류가 발생하면 데이터가 일관성 없는 상태가 될 수 있습니다.

Solution

임시 테이블을 사용하여 중간 데이터를 저장하고 데이터 일관성을 유지합니다.

Example: 특정 조건에서 주문 데이터 업데이트

-- Store target rows in a temporary table
CREATE TEMPORARY TABLE temp_orders AS
SELECT order_id, total_price FROM orders WHERE status = 'pending';

-- Perform update based on the temporary table
UPDATE orders o
JOIN temp_orders t ON o.order_id = t.order_id
SET o.total_price = t.total_price * 1.1; -- Increase price by 10%

Benefits

• 선택된 데이터만 안전하게 업데이트
• 업데이트 전후 데이터를 쉽게 검증

5-4. Per-User Temporary Data Management

Problem

임시 사용자별 데이터를 일반 테이블에 저장하면 시간이 지남에 따라 불필요한 데이터가 누적될 수 있습니다.

Solution

임시 테이블은 세션이 종료될 때 자동으로 데이터를 제거하므로 유지 관리 오버헤드가 사라집니다.

Example: 검색 결과를 임시 테이블에 저장

-- Store user-specific search results
CREATE TEMPORARY TABLE temp_search_results AS
SELECT * FROM products WHERE category = 'electronics';

-- Display search results
SELECT * FROM temp_search_results;

Benefits

• 세션이 종료될 때 데이터가 자동으로 제거
• 세션 중에 임시 검색 결과를 재사용 가능

5-5. Choosing Between Temporary Tables and Views

Problem

자주 실행되는 쿼리를 최적화할 때, 특히 임시 데이터 저장이 필요하다면 임시 테이블을 사용할지 VIEW를 사용할지 고민될 수 있습니다.

Solution

• 데이터가 자주 변경되지 않을 경우 → VIEW 사용 (VIEW)
• 데이터가 자주 변경되거나 물리화가 필요할 경우 → 임시 테이블 사용

Example: 임시 테이블 사용

CREATE TEMPORARY TABLE temp_high_value_customers AS
SELECT customer_id, SUM(total_price) AS total_spent
FROM orders
GROUP BY customer_id
HAVING total_spent > 50000;

SELECT * FROM temp_high_value_customers;

Example: 뷰 사용

CREATE VIEW high_value_customers AS
SELECT customer_id, SUM(total_price) AS total_spent
FROM orders
GROUP BY customer_id
HAVING total_spent > 50000;

Benefits

• 임시 테이블은 데이터를 물리적으로 저장하므로 성능 향상이 가능
• 뷰는 쿼리 재사용에 편리하지만 대용량 데이터셋에서는 성능이 저하될 수 있음

Summary

이 섹션에서는 MySQL 임시 테이블의 다섯 가지 실용적인 사용 사례를 소개했습니다.

✔ Quick recap

1. 쿼리 성능 최적화 (JOIN 오버헤드 감소) → JOIN을 수행하기 전에 필요한 데이터만 임시 테이블에 저장
2. 임시 집계 처리 → 반복 계산을 피하기 위해 집계 결과를 저장
3. 배치 처리용 중간 데이터 → 대규모 업데이트를 안전하게 처리
4. 사용자별 임시 데이터 관리 → 세션 종료 시 데이터가 자동으로 제거
5. 임시 테이블과 뷰 선택 → 변경이 잦은 데이터는 임시 테이블, 안정적인 쿼리 재사용은 뷰 사용

6. Three Important Precautions When Using MySQL Temporary Tables Safely

Introduction

MySQL 임시 테이블은 세션마다 독립적으로 동작하며 특정 조건에서 자동으로 제거되기 때문에 편리한 기능입니다. 그러나 부적절하게 사용하면 성능 저하나 예상치 못한 오류가 발생할 수 있습니다.

이 섹션에서는 임시 테이블을 안전하게 사용하기 위한 세 가지 중요한 주의사항을 설명합니다.

6-1. Precaution 1: Do Not Over-Rely on Automatic Removal

Problem

임시 테이블은 세션이 종료될 때 자동으로 제거되기 때문에 명시적으로 삭제할 필요가 없다고 생각할 수 있습니다. 그러나 이로 인해 때때로 의도치 않은 문제가 발생할 수 있습니다.

Examples of issues

• 장기 실행 연결이 메모리를 계속 소비합니다
• 세션이 열려 있는 한, 임시 테이블은 제거되지 않고 데이터베이스 자원을 계속 소비합니다.
• 명시적으로 삭제하지 않으면 설계 결함이 발생할 수 있습니다
• 배치 프로세스가 예기치 않게 재연결되면 임시 테이블이 사라져 오류를 일으킬 수 있습니다.

해결책

• 더 이상 필요하지 않을 때 DROP TEMPORARY TABLE을 사용하여 임시 테이블을 명시적으로 삭제하세요
• 장기 실행 연결(예: 배치 작업)에서는 임시 테이블을 주기적으로 삭제하세요

예제: 임시 테이블을 명시적으로 삭제하기

DROP TEMPORARY TABLE IF EXISTS temp_users;

핵심 포인트

• 테이블이 존재하지 않을 경우 오류를 방지하기 위해 IF EXISTS를 추가하세요.

6-2. 주의사항 2: 일반 테이블과의 이름 충돌 피하기

문제

일반 테이블과 동일한 이름으로 임시 테이블을 생성할 수 있습니다. 그러나 그렇게 하면 일반 테이블이 일시적으로 보이지 않게 됩니다.

문제 예제

-- A regular users table exists
SELECT * FROM users;

-- Create a temporary table with the same name
CREATE TEMPORARY TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- This now returns data from the temporary table, not the regular one
SELECT * FROM users;

임시 테이블이 존재하는 한, 동일한 이름의 일반 테이블이 숨겨집니다, 이는 예기치 않은 데이터 검색 오류를 초래할 수 있습니다.

해결책

• 임시 테이블 이름에 “temp_”와 같은 접두어를 사용하세요
• 임시 테이블과 일반 테이블을 구분하기 위한 명확한 명명 규칙을 채택하세요

예제: 안전한 임시 테이블 생성

CREATE TEMPORARY TABLE temp_users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

이점

• temp_ 접두어를 사용하면 일반 users 테이블과의 충돌을 방지합니다.
• 애플리케이션 코드에서 테이블을 구분하기 쉽게 만듭니다.

6-3. 주의사항 3: 인덱스와 제약 조건이 자동으로 상속되지 않음

문제

CREATE TEMPORARY TABLE ... AS SELECT ...를 사용하여 테이블을 생성하면, 원본 테이블의 인덱스와 제약 조건이 상속되지 않습니다, 이는 성능 저하를 초래할 수 있습니다.

문제 예제

-- Regular users table (with indexes)
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) UNIQUE,
    name VARCHAR(50)
);

-- Create temporary table (indexes are NOT inherited)
CREATE TEMPORARY TABLE temp_users AS
SELECT id, email, name FROM users;

이 경우, PRIMARY KEY와 UNIQUE 제약 조건이 temp_users로 전달되지 않습니다, 이는 검색을 느리게 하고 중복 데이터를 허용할 수 있습니다.

해결책

• 임시 테이블 생성 후 인덱스를 명시적으로 추가하세요
• CREATE TEMPORARY TABLE로 열을 수동으로 정의할 경우, 생성 시 인덱스를 지정하세요

예제: 인덱스를 수동으로 추가하기

CREATE TEMPORARY TABLE temp_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) UNIQUE,
    name VARCHAR(50)
);

ALTER TABLE temp_users ADD INDEX idx_email (email);

이 접근 방식으로 원본 테이블과 유사한 인덱스를 가진 임시 테이블을 생성할 수 있습니다.

요약

이 섹션에서 임시 테이블을 안전하게 사용하는 세 가지 중요한 주의사항을 설명했습니다.

✔ 빠른 요약

1. 자동 제거에 과도하게 의존하지 마세요

• DROP TEMPORARY TABLE을 사용하여 임시 테이블을 명시적으로 삭제하세요
• 장기 실행 세션에서 주기적으로 제거하세요

1. 일반 테이블과의 이름 충돌 피하기

• 동일한 이름의 일반 테이블이 존재하면 임시 테이블이 우선합니다
• temp_와 같은 접두어를 사용하여 명확히 구분하세요

1. 인덱스와 제약 조건이 자동으로 상속되지 않습니다

• CREATE TEMPORARY TABLE ... AS SELECT ...를 사용하면 인덱스가 손실됩니다
• 생성 후 인덱스를 수동으로 추가하세요

이 점들을 염두에 두면 MySQL 임시 테이블을 안전하게 활용하면서 데이터베이스 성능을 향상시킬 수 있습니다.

7. MySQL 임시 테이블에 대한 10가지 자주 묻는 질문 (FAQ)

소개

이 섹션에서는 MySQL 임시 테이블에 대한 10가지 자주 묻는 질문에 답합니다.
그 작동 방식, 제한 사항, 성능 고려 사항 및 실전 시나리오에서의 문제 해결을 다룹니다.

7-1. 기본 사양에 관한 질문

Q1. 임시 테이블을 다른 세션에서 접근할 수 있나요?

A. 아니요, 접근할 수 없습니다.
임시 테이블은 생성한 세션 내에서만 유효하며 다른 세션에서는 접근할 수 없습니다.

-- Created in Session A
CREATE TEMPORARY TABLE temp_users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- Attempt to access from Session B (results in error)
SELECT * FROM temp_users;

오류:

ERROR 1146 (42S02): Table 'temp_users' doesn't exist

세션 간에 데이터를 공유해야 한다면 일반 테이블을 사용해야 합니다.

Q2. 임시 테이블이 디스크에 저장되나요?

A. 일반적으로 메모리에 저장되지만 특정 조건에서는 디스크로 이동할 수 있습니다.
테이블 크기가 tmp_table_size 또는 max_heap_table_size를 초과하면 MySQL은 InnoDB 또는 MyISAM을 사용해 임시 테이블을 디스크에 생성할 수 있습니다.

SHOW VARIABLES LIKE 'tmp_table_size';

성능을 향상시키려면 tmp_table_size를 적절히 설정하십시오.

Q3. 임시 테이블에 인덱스를 만들 수 있나요?

A. 예, 가능합니다.
일반 테이블과 마찬가지로 PRIMARY KEY 또는 INDEX를 정의할 수 있습니다.

CREATE TEMPORARY TABLE temp_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) UNIQUE,
    name VARCHAR(50)
);

ALTER TABLE temp_users ADD INDEX idx_email (email);

하지만 CREATE TEMPORARY TABLE ... AS SELECT ...를 사용할 경우 인덱스가 상속되지 않으므로, 직접 추가해야 합니다.

7-2. 성능 및 동작에 관한 질문

Q4. MySQL 8.0에서 임시 테이블에 변화가 있나요?

A. MySQL 8.0은 WITH 절을 사용한 공통 테이블 식(CTE)을 도입했습니다.
MySQL 8.0부터는 명시적으로 임시 테이블을 만들지 않고도 CTE를 사용해 임시 결과 집합을 처리할 수 있습니다.

WITH temp_users AS (
    SELECT id, name FROM users WHERE status = 'active'
)
SELECT * FROM temp_users;

CTE를 임시 테이블 대신 사용하면 쿼리를 단순화하고 메모리 사용량을 줄일 수 있습니다.

Q5. 임시 테이블과 MEMORY 테이블의 차이점은 무엇인가요?

A. MEMORY 테이블은 세션이 끝난 뒤에도 지속되는 반면, 임시 테이블은 지속되지 않습니다.
세션이 종료되면 임시 테이블은 삭제되지만, MEMORY 테이블은 서버가 재시작될 때까지(또는 명시적으로 삭제될 때까지) 남아 있습니다.

CREATE TABLE memory_table (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) ENGINE=MEMORY;

어떤 경우에 사용해야 할까요?

• 임시 테이블: 단기, 세션 범위 처리
• MEMORY 테이블: 서버 수준 지속성을 갖는 고속 접근

7-3. 삭제 및 문제 해결에 관한 질문

Q6. DROP TABLE을 사용해 임시 테이블을 삭제할 수 있나요?

A. 아니요, DROP TEMPORARY TABLE을 사용해야 합니다.
임시 테이블을 삭제할 때는 항상 DROP TEMPORARY TABLE을 사용하십시오.

DROP TEMPORARY TABLE temp_users;

일반 DROP TABLE을 사용하면 오류가 발생할 수 있습니다.

Q7. SHOW TABLES가 임시 테이블을 표시하지 않는 이유는 무엇인가요?

A. SHOW TABLES는 임시 테이블을 목록에 표시하지 않습니다.
임시 테이블의 존재 여부를 확인하려면 INFORMATION_SCHEMA를 조회하십시오.

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME = 'temp_users';

결과가 반환되지 않으면 임시 테이블이 이미 삭제된 것일 수 있습니다.

요약

이 섹션에서는 MySQL 임시 테이블에 대한 10가지 자주 묻는 질문을 다루었습니다.

✔ 빠른 요약

1. 임시 테이블은 다른 세션에서 접근할 수 없습니다
2. 임시 테이블은 메모리에서 생성되지만 크면 디스크로 이동할 수 있습니다
3. AS SELECT를 사용할 경우 인덱스를 수동으로 정의해야 합니다
4. CTE(WITH)는 MySQL 8.0 이상에서 사용할 수 있습니다
5. MEMORY 테이블과 달리, 임시 테이블은 세션 종료 시 사라집니다
6. DROP TEMPORARY TABLE을 사용하여 삭제합니다
7. SHOW TABLES는 임시 테이블을 표시하지 않습니다

8. 요약: MySQL 임시 테이블을 효과적으로 사용하기 위한 핵심 포인트

소개

MySQL 임시 테이블은 중간 데이터 저장 및 쿼리 성능 최적화를 위한 강력한 도구입니다.
여기에서는 이 가이드 전체에서 논의된 핵심 포인트를 요약합니다.

8-1. MySQL 임시 테이블의 기본 개념

임시 테이블이란?

• 세션마다 독립적으로 존재합니다
• 세션이 종료될 때 자동으로 제거됩니다
• 일반 테이블처럼 INSERT, UPDATE, DELETE를 지원합니다

기본 생성 구문

CREATE TEMPORARY TABLE temp_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100)
);

주요 사용 사례

• 임시 데이터 저장
• 쿼리 성능 최적화
• 배치 처리용 중간 테이블
• 사용자별 임시 데이터 관리

8-2. 임시 테이블의 장점

1) 쿼리 성능 향상

• JOIN 작업량 감소
• 반복 계산을 줄이기 위해 사전에 집계 수행
• 불필요한 데이터를 제외하여 쿼리 단순화

예시: JOIN 오버헤드 감소

CREATE TEMPORARY TABLE temp_active_users AS
SELECT id, name FROM users WHERE status = 'active';

SELECT o.order_id, t.name, o.total_price
FROM orders o
JOIN temp_active_users t ON o.customer_id = t.id;

2) 임시 저장 및 세션 기반 관리

• 세션 종료 시 자동으로 제거됩니다
• 단기 데이터 저장에 이상적입니다
• 다른 세션에 영향을 주지 않고 독립적으로 데이터 조작 가능

예시: 검색 결과의 임시 저장

CREATE TEMPORARY TABLE temp_search_results AS
SELECT * FROM products WHERE category = 'electronics';

SELECT * FROM temp_search_results;

3) 안전한 데이터 업데이트

• 배치 처리 시 중간 테이블로 유용합니다
• 데이터 업데이트 중 백업 역할을 할 수 있습니다
• 테스트 데이터셋 생성에 효과적입니다

예시: 안전한 데이터 업데이트

CREATE TEMPORARY TABLE temp_orders AS
SELECT order_id, total_price FROM orders WHERE status = 'pending';

UPDATE orders o
JOIN temp_orders t ON o.order_id = t.order_id
SET o.total_price = t.total_price * 1.1;

8-3. 단점 및 주의사항

1) 세션 종료 시 데이터 사라짐

• 영구 저장에 적합하지 않습니다
• 장기 보존을 위해 일반 테이블을 사용하세요

2) 세션 간 공유 불가

• 다른 연결에서 접근할 수 없습니다
• 사용자 간 데이터 공유가 필요할 경우 일반 테이블을 사용하세요

3) 인덱스와 제약 조건이 자동으로 상속되지 않음

• CREATE TEMPORARY TABLE ... AS SELECT ...는 인덱스를 생성하지 않습니다
• 필요한 경우 인덱스를 수동으로 추가하세요

  ALTER TABLE temp_users ADD INDEX idx_email (email);
  

8-4. 안전한 사용을 위한 모범 사례

✅ 더 이상 필요하지 않을 때 명시적으로 삭제하세요

DROP TEMPORARY TABLE IF EXISTS temp_users;

✅ 일반 테이블과 이름 충돌을 피하세요

• temp_ 접두사를 사용하세요

  CREATE TEMPORARY TABLE temp_users (...);
  

✅ 성능을 고려하여 설계하세요

• 테이블이 커져 디스크로 이동하면 tmp_table_size 조정을 고려하세요

  SHOW VARIABLES LIKE 'tmp_table_size';
  

8-5. 임시 테이블 vs 대안 (뷰와 CTE)

임시 테이블을 사용할 시점과 뷰(VIEW) 또는 CTE(공통 테이블 표현식)를 사용할 시점을 고려하는 것도 중요합니다.

요약

이 가이드에서는 MySQL 임시 테이블의 모든 핵심 측면을 다루었습니다.

✔ 빠른 요약

• 세션이 종료될 때 임시 테이블은 자동으로 제거됩니다
• JOIN 및 집계 오버헤드를 줄여 성능 최적화에 도움을 줍니다
• 배치 처리, 임시 검색 결과 및 테스트 데이터에 유용합니다
• 세션 간에 공유할 수 없으며, 필요할 경우 인덱스를 수동으로 추가해야 합니다
• 임시 테이블, 뷰, CTE 중 선택함으로써 유연한 데이터 관리를 할 수 있습니다

You have now completed all sections of the MySQL temporary table guide! 🎉
Use this reference to effectively leverage temporary tables in your MySQL projects.