이미지 로딩 중...
AI Generated
2025. 11. 5. · 74 Views
MySQL 핵심 개념 완벽 정리
데이터베이스의 기본부터 실무에서 꼭 필요한 핵심 개념까지, MySQL을 처음 시작하는 개발자를 위한 완벽 가이드입니다. 인덱스 최적화, 트랜잭션 처리, 조인 전략 등 실무에서 바로 활용할 수 있는 내용을 친절하게 설명합니다.
목차
- 데이터베이스와 테이블 생성 - 체계적인 데이터 구조 설계의 시작
- SELECT 쿼리와 WHERE 조건 - 원하는 데이터를 정확히 찾아내기
- INSERT와 UPDATE - 데이터 생성과 수정 마스터하기
- JOIN 연산 - 관계형 데이터 연결의 핵심
- 인덱스(INDEX) - 데이터베이스 성능의 게임 체인저
- 트랜잭션(TRANSACTION) - 데이터 일관성의 보호막
- 집계 함수(Aggregate Functions) - 데이터 요약과 통계의 핵심
- 서브쿼리(Subquery) - 쿼리 안의 쿼리로 복잡한 문제 해결하기
1. 데이터베이스와 테이블 생성 - 체계적인 데이터 구조 설계의 시작
시작하며
여러분이 처음으로 웹 서비스를 만들 때, 사용자 정보를 어디에 저장해야 할지 고민해본 적 있나요? 파일에 저장하다가 데이터가 꼬이거나, 검색이 너무 느려서 답답했던 경험 말이죠.
이런 문제는 데이터를 체계적으로 관리하지 않아서 발생합니다. 데이터가 늘어날수록 파일 기반 저장은 성능과 안정성 면에서 한계를 드러냅니다.
동시에 여러 사용자가 접근하면 데이터 충돌이 발생하고, 복잡한 검색 조건을 처리하기도 어렵습니다. 바로 이럴 때 필요한 것이 데이터베이스와 테이블입니다.
체계적인 구조로 데이터를 저장하고, 빠르게 검색하며, 안전하게 관리할 수 있는 기반을 제공합니다.
개요
간단히 말해서, 데이터베이스는 관련된 데이터들을 모아두는 큰 저장소이고, 테이블은 그 안에서 특정 주제의 데이터를 정리하는 엑셀 시트와 같습니다. 왜 이 개념이 필요한지 실무 관점에서 설명하자면, 회원 관리 시스템을 만든다고 가정해봅시다.
사용자 정보, 주문 내역, 상품 정보를 각각 다른 테이블로 분리해서 관리하면 데이터 중복을 줄이고 유지보수가 훨씬 쉬워집니다. 예를 들어, 사용자가 여러 번 주문을 해도 사용자 정보는 한 곳에만 저장하고 주문 테이블에서는 사용자 ID만 참조하면 됩니다.
기존에는 모든 정보를 하나의 큰 파일에 저장했다면, 이제는 주제별로 테이블을 나누어 효율적으로 관리할 수 있습니다. 데이터가 중복되지 않고, 각 테이블은 명확한 목적을 가지게 됩니다.
테이블을 만들 때는 컬럼(열)의 데이터 타입을 정확히 지정하고, 기본 키(Primary Key)를 설정하여 각 행을 고유하게 식별할 수 있도록 합니다. NOT NULL, DEFAULT, AUTO_INCREMENT 같은 제약 조건을 활용하면 데이터 무결성을 보장할 수 있습니다.
이러한 특징들이 안정적인 서비스 운영의 기반이 됩니다.
코드 예제
-- 데이터베이스 생성: 프로젝트 전용 공간 만들기
CREATE DATABASE ecommerce_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 생성한 데이터베이스 선택
USE ecommerce_db;
-- 사용자 테이블 생성: 회원 정보를 저장
CREATE TABLE users (
user_id INT AUTO_INCREMENT PRIMARY KEY, -- 자동 증가하는 고유 ID
email VARCHAR(100) NOT NULL UNIQUE, -- 이메일은 필수이고 중복 불가
username VARCHAR(50) NOT NULL, -- 사용자명은 필수
password_hash VARCHAR(255) NOT NULL, -- 암호화된 비밀번호
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 가입 일시 자동 기록
is_active BOOLEAN DEFAULT true -- 계정 활성화 상태
);
설명
이것이 하는 일: 위 코드는 전자상거래 서비스를 위한 데이터베이스를 생성하고, 그 안에 사용자 정보를 저장할 테이블을 만드는 작업입니다. 이는 모든 데이터 기반 애플리케이션의 첫 번째 단계입니다.
첫 번째로, CREATE DATABASE 명령으로 ecommerce_db라는 독립적인 데이터베이스 공간을 만듭니다. CHARACTER SET utf8mb4를 지정한 이유는 한글, 이모지 등 다양한 언어를 완벽하게 지원하기 위함입니다.
실무에서는 글로벌 서비스를 고려해 반드시 utf8mb4를 사용해야 합니다. 그 다음으로, CREATE TABLE 명령으로 users 테이블을 정의합니다.
user_id는 AUTO_INCREMENT로 설정되어 새 사용자가 등록될 때마다 자동으로 1씩 증가하는 고유 번호를 부여받습니다. email 컬럼에는 UNIQUE 제약 조건을 걸어서 같은 이메일로 중복 가입하는 것을 데이터베이스 레벨에서 방지합니다.
NOT NULL 제약 조건은 필수 입력 항목을 보장합니다. created_at 컬럼은 TIMESTAMP 타입에 DEFAULT CURRENT_TIMESTAMP를 설정하여, INSERT 시 별도로 값을 지정하지 않아도 현재 시각이 자동으로 기록됩니다.
이렇게 하면 "이 사용자가 언제 가입했지?"라는 질문에 언제든 답할 수 있습니다. is_active는 계정 정지 기능을 구현할 때 유용하게 사용됩니다.
여러분이 이 코드를 사용하면 데이터 중복을 방지하고, 입력 오류를 줄이며, 데이터베이스가 자동으로 데이터 무결성을 관리하도록 할 수 있습니다. 애플리케이션 코드에서 일일이 검증하지 않아도 데이터베이스가 1차 방어선 역할을 해주므로 안정성이 크게 향상됩니다.
또한 테이블 구조가 명확하면 팀원들과 협업할 때 의사소통이 원활해지고, 나중에 기능을 추가할 때도 어디에 무엇을 넣어야 할지 바로 알 수 있습니다.
실전 팁
💡 컬럼명은 snake_case를 사용하고, 테이블명은 복수형으로 작성하는 것이 업계 표준입니다 (users, orders, products 등). 일관된 네이밍 규칙은 유지보수를 훨씬 쉽게 만듭니다.
💡 VARCHAR 길이를 지나치게 크게 잡지 마세요. email은 100자, username은 50자 정도면 충분합니다. 불필요하게 큰 값은 메모리와 디스크 공간을 낭비합니다.
💡 비밀번호는 절대 평문으로 저장하면 안 되며, password_hash처럼 해시된 값을 저장한다는 것을 컬럼명으로 명시하면 실수를 방지할 수 있습니다.
💡 created_at, updated_at 컬럼은 거의 모든 테이블에 추가하세요. 데이터 추적과 디버깅에 매우 유용하며, 나중에 추가하려면 기존 데이터 마이그레이션이 필요해 번거롭습니다.
💡 테이블을 만들기 전에 IF NOT EXISTS를 사용하면 이미 테이블이 존재할 때 에러가 발생하지 않아 배포 스크립트를 안전하게 만들 수 있습니다.
2. SELECT 쿼리와 WHERE 조건 - 원하는 데이터를 정확히 찾아내기
시작하며
여러분이 수만 명의 회원 중에서 최근 한 달 동안 가입한 활성 사용자만 찾아야 하는 상황을 상상해보세요. 모든 데이터를 가져와서 애플리케이션 코드로 필터링하면 시간도 오래 걸리고 메모리도 부족해집니다.
이런 문제는 데이터베이스의 강력한 검색 기능을 제대로 활용하지 못해서 발생합니다. 데이터베이스는 수백만 건의 데이터 중에서도 조건에 맞는 것만 빠르게 찾아낼 수 있도록 최적화되어 있는데, 이를 제대로 사용하지 않으면 불필요한 데이터 전송과 처리로 성능이 크게 저하됩니다.
바로 이럴 때 필요한 것이 SELECT 쿼리와 WHERE 조건입니다. 데이터베이스 서버에서 필요한 데이터만 정확히 필터링하여 가져올 수 있게 해줍니다.
개요
간단히 말해서, SELECT는 테이블에서 데이터를 조회하는 명령이고, WHERE는 특정 조건에 맞는 데이터만 걸러내는 필터입니다. 왜 이 개념이 필요한지 실무 관점에서 설명하자면, 대시보드에 "오늘 가입한 신규 회원 수"를 표시해야 한다고 가정해봅시다.
전체 회원 데이터를 다 가져와서 애플리케이션에서 필터링하면 네트워크 대역폭을 낭비하고 응답 속도가 느려집니다. WHERE 조건으로 날짜를 지정하면 데이터베이스가 해당 데이터만 찾아서 보내주므로 효율적입니다.
기존에는 모든 데이터를 메모리로 로드한 후 반복문으로 조건을 확인했다면, 이제는 SQL 쿼리 한 줄로 데이터베이스가 최적화된 알고리즘으로 처리하도록 할 수 있습니다. SELECT의 핵심 특징은 첫째, 원하는 컬럼만 선택할 수 있어 데이터 전송량을 줄일 수 있고, 둘째, WHERE 절로 복잡한 조건을 조합할 수 있으며, 셋째, ORDER BY, LIMIT 등으로 결과를 정렬하고 개수를 제한할 수 있다는 점입니다.
이러한 특징들이 효율적인 데이터 조회의 핵심입니다.
코드 예제
-- 기본 조회: 모든 사용자 정보 가져오기
SELECT user_id, email, username, created_at
FROM users;
-- 조건부 조회: 최근 30일 이내 가입한 활성 사용자만
SELECT user_id, email, username, created_at
FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
AND is_active = true;
-- 복잡한 조건: 특정 도메인 이메일이면서 활성 상태인 사용자
SELECT user_id, email, username
FROM users
WHERE email LIKE '%@gmail.com'
AND is_active = true
ORDER BY created_at DESC
LIMIT 10;
설명
이것이 하는 일: 위 코드는 users 테이블에서 다양한 조건으로 사용자 정보를 조회하는 예제입니다. 전체 조회부터 복잡한 조건 검색까지 단계별로 보여줍니다.
첫 번째로, 기본 SELECT 문은 테이블의 모든 행에서 지정된 컬럼만 가져옵니다. SELECT * 대신 필요한 컬럼만 명시하는 것이 좋습니다.
왜냐하면 불필요한 데이터 전송을 줄여 네트워크 비용과 처리 시간을 절약할 수 있기 때문입니다. 특히 BLOB이나 TEXT 같은 큰 데이터 타입이 포함된 테이블에서는 더욱 중요합니다.
그 다음으로, WHERE 절이 추가되면 조건을 만족하는 행만 반환됩니다. DATE_SUB(NOW(), INTERVAL 30 DAY)는 현재 시각에서 30일을 뺀 날짜를 계산하고, created_at이 그 이후인 행만 선택합니다.
AND 연산자로 is_active = true 조건을 추가하여 활성 사용자만 걸러냅니다. 데이터베이스는 인덱스를 활용해 이 조건들을 빠르게 평가합니다.
세 번째 예제는 LIKE 연산자를 사용한 패턴 매칭입니다. '%@gmail.com'은 "gmail.com으로 끝나는 모든 이메일"을 의미합니다.
ORDER BY created_at DESC는 결과를 가입일 기준 내림차순(최신순)으로 정렬하고, LIMIT 10은 상위 10개만 가져옵니다. 이는 페이지네이션 구현의 기본입니다.
여러분이 이 코드를 사용하면 애플리케이션 성능을 크게 향상시킬 수 있습니다. 데이터베이스가 인덱스를 활용해 빠르게 검색하고, 필요한 데이터만 전송하므로 네트워크 부하가 줄어들며, 애플리케이션 메모리도 절약됩니다.
실무에서는 대부분의 비즈니스 로직이 "특정 조건의 데이터를 찾기"이므로 WHERE 절을 제대로 작성하는 능력이 매우 중요합니다. 또한 쿼리가 명확하면 코드 가독성도 좋아지고, 나중에 요구사항이 바뀌어도 쉽게 수정할 수 있습니다.
실전 팁
💡 SELECT * 는 개발 초기에만 사용하고, 프로덕션 코드에서는 반드시 필요한 컬럼만 명시하세요. 나중에 테이블에 컬럼이 추가되면 예상치 못한 성능 저하가 발생할 수 있습니다.
💡 WHERE 절의 조건 순서는 선택도가 높은 것(결과를 많이 줄이는 것)을 앞에 배치하세요. 예를 들어, is_active보다 created_at 범위 조건이 더 많은 데이터를 걸러낸다면 그것을 먼저 쓰는 것이 좋습니다.
💡 LIKE '%keyword%' 패턴은 인덱스를 사용할 수 없어 느립니다. 가능하면 'keyword%' 형태(시작 부분 매칭)로 작성하거나, 전문 검색(Full-Text Search) 기능을 활용하세요.
💡 날짜 비교 시 함수를 컬럼에 적용하면 인덱스를 못 쓰게 됩니다. WHERE DATE(created_at) = '2025-01-01' 대신 WHERE created_at >= '2025-01-01' AND created_at < '2025-01-02' 형태로 작성하세요.
💡 OR 조건이 많으면 성능이 저하될 수 있습니다. 여러 개의 별도 쿼리로 나누어 UNION으로 합치거나, IN 연산자를 사용하는 것이 더 효율적일 수 있습니다.
3. INSERT와 UPDATE - 데이터 생성과 수정 마스터하기
시작하며
여러분이 회원가입 기능을 구현하는데, 사용자가 입력한 정보를 데이터베이스에 저장하지 못한다면 어떻게 될까요? 또는 사용자가 프로필을 수정했는데 변경 내용이 반영되지 않는다면요?
서비스가 제대로 작동하지 않을 것입니다. 이런 문제는 데이터의 생성과 수정 작업을 제대로 이해하지 못해서 발생합니다.
데이터베이스에 정보를 안전하게 저장하고, 필요할 때 정확히 업데이트하는 것은 모든 CRUD(Create, Read, Update, Delete) 애플리케이션의 핵심입니다. 잘못하면 데이터 손실이나 중복 생성 같은 심각한 문제가 발생할 수 있습니다.
바로 이럴 때 필요한 것이 INSERT와 UPDATE 명령입니다. 새로운 데이터를 생성하고, 기존 데이터를 안전하게 수정할 수 있게 해줍니다.
개요
간단히 말해서, INSERT는 테이블에 새로운 행(레코드)을 추가하는 명령이고, UPDATE는 기존 행의 값을 변경하는 명령입니다. 왜 이 개념이 필요한지 실무 관점에서 설명하자면, 사용자가 회원가입 폼을 제출하면 서버는 INSERT 문으로 새 사용자 레코드를 생성합니다.
나중에 사용자가 비밀번호를 변경하거나 프로필 사진을 업로드하면 UPDATE 문으로 해당 사용자의 정보만 수정합니다. 예를 들어, 전자상거래 사이트에서 주문 상태를 "처리중"에서 "배송완료"로 바꾸는 것도 UPDATE 작업입니다.
기존에는 파일을 열어서 데이터를 추가하거나 찾아서 수정했다면, 이제는 SQL 명령 한 줄로 데이터베이스가 트랜잭션을 보장하며 안전하게 처리합니다. INSERT의 핵심 특징은 첫째, AUTO_INCREMENT 컬럼은 자동으로 값이 채워지고, 둘째, DEFAULT 값이 설정된 컬럼은 생략 가능하며, 셋째, 여러 행을 한 번에 삽입할 수 있어 효율적입니다.
UPDATE는 WHERE 절로 정확히 대상을 지정하고, SET으로 여러 컬럼을 동시에 수정할 수 있습니다. 이러한 특징들이 안전하고 효율적인 데이터 조작을 가능하게 합니다.
코드 예제
-- 단일 사용자 추가: 회원가입 처리
INSERT INTO users (email, username, password_hash)
VALUES ('john@example.com', 'john_doe', 'hashed_password_here');
-- 여러 사용자 한 번에 추가: 대량 데이터 입력 시 효율적
INSERT INTO users (email, username, password_hash) VALUES
('alice@example.com', 'alice', 'hash1'),
('bob@example.com', 'bob', 'hash2'),
('charlie@example.com', 'charlie', 'hash3');
-- 사용자 정보 수정: 특정 사용자의 이름 변경
UPDATE users
SET username = 'john_updated', updated_at = NOW()
WHERE email = 'john@example.com';
-- 조건부 업데이트: 오래된 비활성 계정 정리
UPDATE users
SET is_active = false
WHERE last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR);
설명
이것이 하는 일: 위 코드는 데이터베이스에 새로운 사용자를 추가하고, 기존 사용자 정보를 수정하는 다양한 방법을 보여줍니다. 실무에서 가장 자주 사용되는 패턴들입니다.
첫 번째로, 기본 INSERT 문은 VALUES 절에 명시된 값들을 테이블에 새 행으로 추가합니다. user_id는 AUTO_INCREMENT이므로 생략했고, created_at은 DEFAULT CURRENT_TIMESTAMP이므로 자동으로 현재 시각이 들어갑니다.
명시한 컬럼과 VALUES의 순서와 개수는 반드시 일치해야 합니다. 이 방식은 회원가입, 주문 생성, 게시글 작성 등 모든 "생성" 작업의 기본입니다.
그 다음으로, 다중 행 INSERT는 VALUES 절에 여러 개의 값 튜플을 나열하여 한 번의 쿼리로 여러 행을 삽입합니다. 이는 개별 INSERT를 여러 번 실행하는 것보다 훨씬 빠릅니다.
데이터베이스는 한 번의 트랜잭션으로 처리하고, 네트워크 왕복 횟수도 줄어들기 때문입니다. CSV 파일을 데이터베이스로 가져오거나 초기 데이터를 설정할 때 매우 유용합니다.
UPDATE 문은 SET 절로 변경할 컬럼과 새 값을 지정하고, WHERE 절로 수정 대상을 정확히 지정합니다. 여기서 가장 중요한 것은 WHERE 절입니다.
WHERE를 생략하면 테이블의 모든 행이 수정되므로 매우 주의해야 합니다. updated_at = NOW()를 함께 설정하면 "이 데이터가 언제 마지막으로 수정되었는지" 추적할 수 있어 디버깅과 감사(audit)에 도움이 됩니다.
마지막 예제는 조건에 맞는 여러 행을 한 번에 업데이트합니다. 1년 동안 로그인하지 않은 계정을 자동으로 비활성화하는 배치 작업 같은 것입니다.
이처럼 UPDATE는 단일 행뿐 아니라 조건에 맞는 모든 행을 동시에 수정할 수 있습니다. 여러분이 이 코드를 사용하면 데이터를 안전하고 효율적으로 관리할 수 있습니다.
INSERT로 비즈니스 이벤트(가입, 주문, 댓글 등)를 기록하고, UPDATE로 상태 변화(프로필 수정, 주문 상태 변경 등)를 반영합니다. 다중 행 INSERT를 활용하면 대량 데이터 처리 성능이 크게 향상되고, WHERE 절을 정확히 작성하면 의도하지 않은 데이터 변경을 방지할 수 있습니다.
또한 updated_at 같은 타임스탬프를 함께 업데이트하면 데이터 변경 이력을 추적할 수 있어 문제 발생 시 원인 파악이 쉬워집니다.
실전 팁
💡 UPDATE나 DELETE 실행 전에는 반드시 같은 WHERE 조건으로 SELECT를 먼저 실행해보세요. 몇 개의 행이 영향받을지 확인하면 실수를 방지할 수 있습니다.
💡 여러 행을 INSERT할 때는 한 번에 1000개 이하로 나누는 것이 좋습니다. 너무 많으면 쿼리 자체가 너무 커지고, 트랜잭션 로그가 비대해져 성능이 저하될 수 있습니다.
💡 동시성 문제가 있는 UPDATE는 트랜잭션과 락(LOCK)을 활용하세요. 예를 들어, 재고 감소는 SELECT ... FOR UPDATE로 행을 잠근 후 UPDATE해야 동시 주문에서 재고가 마이너스가 되는 것을 방지할 수 있습니다.
💡 INSERT 실행 후 LAST_INSERT_ID() 함수로 방금 삽입된 AUTO_INCREMENT 값을 가져올 수 있습니다. 이는 "회원가입 후 바로 로그인 처리"처럼 연속된 작업에 필수적입니다.
💡 ON DUPLICATE KEY UPDATE 구문을 사용하면 INSERT 시도 시 중복 키가 있으면 UPDATE로 전환됩니다. "있으면 수정, 없으면 생성" 로직을 한 줄로 처리할 수 있어 매우 편리합니다.
4. JOIN 연산 - 관계형 데이터 연결의 핵심
시작하며
여러분이 전자상거래 대시보드에 "각 사용자의 총 주문 금액"을 표시해야 하는데, 사용자 정보는 users 테이블에, 주문 정보는 orders 테이블에 따로 저장되어 있다면 어떻게 해야 할까요? 각 테이블을 따로 조회한 후 애플리케이션 코드로 합치려면 복잡하고 비효율적입니다.
이런 문제는 관계형 데이터베이스의 핵심 개념인 "정규화"에서 비롯됩니다. 데이터 중복을 줄이기 위해 테이블을 분리하면 저장 공간과 일관성 면에서 이점이 있지만, 조회할 때는 여러 테이블의 정보를 결합해야 합니다.
이를 애플리케이션 레벨에서 처리하면 성능이 크게 떨어집니다. 바로 이럴 때 필요한 것이 JOIN 연산입니다.
데이터베이스가 여러 테이블의 관련된 데이터를 효율적으로 결합하여 하나의 결과 집합으로 만들어줍니다.
개요
간단히 말해서, JOIN은 두 개 이상의 테이블을 공통 컬럼(주로 외래 키)을 기준으로 연결하여 하나의 결과로 조회하는 연산입니다. 왜 이 개념이 필요한지 실무 관점에서 설명하자면, 사용자별 주문 내역을 보여주는 페이지를 만든다고 가정해봅시다.
users 테이블에서 사용자 이름을 가져오고, orders 테이블에서 주문 정보를 가져와야 합니다. JOIN을 사용하면 한 번의 쿼리로 "사용자 이름 + 주문 날짜 + 주문 금액"을 모두 가져올 수 있습니다.
예를 들어, 관리자 대시보드의 복잡한 리포트는 대부분 여러 테이블을 JOIN한 결과입니다. 기존에는 users 테이블을 조회하고, 그 결과를 반복문 돌면서 각 user_id로 orders 테이블을 다시 조회하는 N+1 쿼리 문제가 발생했다면, 이제는 JOIN 한 번으로 데이터베이스가 최적화된 방식으로 모든 데이터를 가져옵니다.
JOIN의 핵심 유형은 첫째, INNER JOIN(양쪽 테이블에 모두 존재하는 데이터만), 둘째, LEFT JOIN(왼쪽 테이블의 모든 데이터 + 매칭되는 오른쪽 데이터), 셋째, RIGHT JOIN(오른쪽 테이블 기준), 넷째, CROSS JOIN(모든 조합)입니다. 실무에서는 INNER JOIN과 LEFT JOIN이 90% 이상을 차지합니다.
이러한 JOIN 유형들을 상황에 맞게 사용하는 것이 효율적인 데이터 조회의 핵심입니다.
코드 예제
-- INNER JOIN: 주문이 있는 사용자만 조회
SELECT u.username, u.email, o.order_id, o.total_amount, o.created_at
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
WHERE o.created_at >= '2025-01-01'
ORDER BY o.created_at DESC;
-- LEFT JOIN: 모든 사용자와 주문 내역 (주문 없어도 표시)
SELECT u.username, COUNT(o.order_id) as order_count,
COALESCE(SUM(o.total_amount), 0) as total_spent
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.username;
-- 다중 JOIN: 사용자 - 주문 - 주문 상품 연결
SELECT u.username, o.order_id, p.product_name, oi.quantity, oi.price
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
WHERE u.user_id = 123;
설명
이것이 하는 일: 위 코드는 사용자, 주문, 상품 등 여러 테이블에 분산된 데이터를 연결하여 의미 있는 비즈니스 정보로 조합하는 예제입니다. 실무에서 가장 중요한 SQL 패턴입니다.
첫 번째로, INNER JOIN은 users와 orders 테이블을 user_id로 연결합니다. ON 절의 조건(u.user_id = o.user_id)이 참인 행들만 결과에 포함됩니다.
즉, 주문이 최소 한 건 이상 있는 사용자만 조회됩니다. 주문이 없는 사용자는 결과에서 제외됩니다.
테이블 별칭(u, o)을 사용하면 쿼리가 간결해지고, 컬럼명이 겹칠 때 명확히 구분할 수 있습니다. 그 다음으로, LEFT JOIN은 왼쪽 테이블(users)의 모든 행을 유지하면서 오른쪽 테이블(orders)에서 매칭되는 데이터를 가져옵니다.
주문이 없는 사용자도 결과에 포함되며, 이 경우 orders 쪽 컬럼은 NULL이 됩니다. COUNT(o.order_id)는 주문 개수를 세고, COALESCE(SUM(o.total_amount), 0)는 NULL을 0으로 변환하여 "주문이 없는 사용자의 총 구매액은 0원"으로 표시합니다.
GROUP BY와 함께 사용하면 사용자별 집계 리포트를 만들 수 있습니다. 세 번째 예제는 3개 이상의 테이블을 연결하는 다중 JOIN입니다.
users → orders → order_items → products 순서로 연결하여 "특정 사용자가 어떤 상품을 몇 개씩 주문했는지" 상세 내역을 가져옵니다. 각 JOIN은 이전 결과와 다음 테이블을 연결하는 방식으로 체인처럼 이어집니다.
실무에서는 5~6개 테이블을 JOIN하는 경우도 흔합니다. 여러분이 이 코드를 사용하면 복잡한 비즈니스 요구사항을 단순하게 해결할 수 있습니다.
N+1 쿼리 문제를 원천적으로 방지하고, 데이터베이스가 인덱스를 활용해 효율적으로 조인을 수행하므로 성능이 우수합니다. 애플리케이션 메모리에서 데이터를 결합할 필요가 없어 코드도 간결해집니다.
또한 JOIN을 잘 활용하면 "이번 달 가장 많이 팔린 상품", "구매 금액 상위 10명 고객" 같은 복잡한 리포트도 한 줄의 SQL로 추출할 수 있어 개발 생산성이 크게 향상됩니다. 데이터베이스는 수십 년간 최적화된 JOIN 알고리즘을 사용하므로 직접 구현하는 것보다 훨씬 빠르고 안정적입니다.
실전 팁
💡 JOIN할 컬럼에는 반드시 인덱스를 생성하세요. 외래 키 컬럼에 인덱스가 없으면 JOIN 성능이 매우 느려집니다. CREATE INDEX idx_orders_user_id ON orders(user_id); 같은 방식으로 생성합니다.
💡 INNER JOIN과 LEFT JOIN의 차이를 명확히 이해하세요. "모든 사용자를 보여주되 주문이 없으면 0으로 표시"는 LEFT JOIN, "주문이 있는 사용자만"은 INNER JOIN입니다.
💡 여러 테이블을 JOIN할 때는 FROM 절의 순서와 JOIN 순서를 논리적으로 배치하세요. 보통 메인 엔티티(users)를 FROM에 두고, 관련 테이블들을 JOIN으로 연결하는 것이 가독성이 좋습니다.
💡 EXPLAIN 명령으로 JOIN 쿼리의 실행 계획을 확인하세요. 어떤 인덱스가 사용되는지, 몇 개의 행을 스캔하는지 알 수 있어 성능 최적화에 도움이 됩니다.
💡 self-join(같은 테이블을 자기 자신과 JOIN)도 가능합니다. 예를 들어, 직원 테이블에서 "각 직원과 그의 매니저 이름"을 조회할 때 users u1 JOIN users u2 ON u1.manager_id = u2.user_id 형태로 사용합니다.
5. 인덱스(INDEX) - 데이터베이스 성능의 게임 체인저
시작하며
여러분이 수백만 건의 주문 데이터에서 특정 사용자의 주문을 찾는 쿼리를 실행했는데, 몇 초씩 걸린다면 어떻게 될까요? 사용자는 답답함을 느끼고, 서버는 CPU를 과도하게 사용하며, 동시 접속자가 많아지면 서비스가 마비될 수도 있습니다.
이런 문제는 데이터베이스가 모든 행을 하나하나 확인하는 "풀 테이블 스캔"을 수행하기 때문에 발생합니다. 책에서 특정 단어를 찾을 때 첫 페이지부터 끝까지 읽는 것과 같습니다.
데이터가 많아질수록 검색 시간은 비례해서 늘어나고, 결국 사용자 경험이 크게 저하됩니다. 바로 이럼 때 필요한 것이 인덱스입니다.
책의 색인처럼, 특정 값이 어디에 있는지 빠르게 찾을 수 있는 별도의 자료구조를 만들어 검색 속도를 비약적으로 향상시킵니다.
개요
간단히 말해서, 인덱스는 테이블의 특정 컬럼 값들을 정렬하여 저장한 별도의 자료구조로, 검색 속도를 수십 배에서 수천 배까지 빠르게 만듭니다. 왜 이 개념이 필요한지 실무 관점에서 설명하자면, 로그인 기능을 구현할 때 WHERE email = 'user@example.com' 조건으로 사용자를 찾습니다.
email 컬럼에 인덱스가 없으면 전체 사용자를 스캔하지만, 인덱스가 있으면 B-Tree 구조를 활용해 로그 시간 복잡도(O(log n))로 찾을 수 있습니다. 예를 들어, 100만 명의 회원 중에서도 0.01초 이내에 찾을 수 있습니다.
기존에는 느린 쿼리를 발견하면 서버 스펙을 업그레이드하거나 캐시를 추가했다면, 이제는 적절한 인덱스 하나로 문제를 근본적으로 해결할 수 있습니다. 인덱스의 핵심 특징은 첫째, WHERE, JOIN, ORDER BY에 사용되는 컬럼에 생성하면 효과적이고, 둘째, 읽기 속도는 빨라지지만 쓰기 속도는 약간 느려지며(인덱스도 함께 업데이트해야 하므로), 셋째, 디스크 공간을 추가로 사용한다는 점입니다.
따라서 모든 컬럼에 무분별하게 인덱스를 만들면 안 되고, 쿼리 패턴을 분석하여 꼭 필요한 곳에만 전략적으로 생성해야 합니다.
코드 예제
-- 단일 컬럼 인덱스: 로그인 쿼리 최적화
CREATE INDEX idx_users_email ON users(email);
-- 복합 인덱스: 날짜 범위 + 상태 조건 최적화
CREATE INDEX idx_orders_date_status ON orders(created_at, status);
-- 유니크 인덱스: 중복 방지 + 검색 최적화
CREATE UNIQUE INDEX idx_users_username ON users(username);
-- 인덱스 사용 전후 비교 쿼리
-- 인덱스 활용하는 쿼리 (FAST)
SELECT * FROM users WHERE email = 'john@example.com';
-- 복합 인덱스 활용하는 쿼리 (FAST)
SELECT * FROM orders
WHERE created_at >= '2025-01-01' AND status = 'completed';
-- 인덱스 확인 및 삭제
SHOW INDEX FROM users;
DROP INDEX idx_users_email ON users;
설명
이것이 하는 일: 위 코드는 쿼리 성능을 극적으로 개선하는 인덱스를 생성하고 관리하는 방법을 보여줍니다. 실무에서 성능 최적화의 가장 효과적인 방법입니다.
첫 번째로, 단일 컬럼 인덱스는 하나의 컬럼만을 대상으로 합니다. idx_users_email 인덱스를 생성하면 MySQL은 email 컬럼 값들을 정렬된 B-Tree 구조로 저장합니다.
이후 WHERE email = '...' 조건이 있는 쿼리는 이 인덱스를 활용해 몇 번의 노드 탐색만으로 원하는 행을 찾습니다. 100만 건 테이블에서도 약 20번의 비교로 찾을 수 있어, 100만 번 스캔하는 것과 비교하면 엄청난 차이입니다.
그 다음으로, 복합 인덱스는 여러 컬럼을 조합하여 생성합니다. (created_at, status) 순서로 만들면 "날짜 범위를 먼저 좁히고, 그 안에서 상태를 필터링"하는 쿼리에 최적화됩니다.
중요한 점은 인덱스의 컬럼 순서입니다. 이 인덱스는 WHERE created_at >= '...' 또는 WHERE created_at >= '...' AND status = '...'에는 효과적이지만, WHERE status = '...'만 있는 쿼리에는 사용되지 않습니다(왼쪽 컬럼부터 매칭되어야 하기 때문입니다).
UNIQUE 인덱스는 중복을 허용하지 않는 제약 조건과 인덱스를 동시에 제공합니다. username은 고유해야 하므로 UNIQUE INDEX가 적합합니다.
데이터베이스가 입력 시 자동으로 중복을 체크하고, 조회 시 빠르게 검색할 수 있습니다. PRIMARY KEY도 내부적으로 UNIQUE INDEX입니다.
SHOW INDEX 명령은 테이블에 어떤 인덱스들이 있는지, 각 인덱스가 어떤 컬럼을 포함하는지 보여줍니다. DROP INDEX는 더 이상 필요 없는 인덱스를 삭제합니다.
사용되지 않는 인덱스는 INSERT/UPDATE 시 불필요한 오버헤드만 발생시키므로 정기적으로 점검하여 정리해야 합니다. 여러분이 이 코드를 사용하면 서비스 성능을 획기적으로 개선할 수 있습니다.
실제 프로덕션 환경에서 인덱스 하나로 쿼리 시간을 5초에서 0.01초로 줄인 사례가 수없이 많습니다. 사용자 경험이 개선되고, 서버 리소스 사용량이 줄어들며, 더 많은 동시 접속자를 처리할 수 있게 됩니다.
특히 데이터가 많아질수록 인덱스의 효과는 더욱 커집니다. 슬로우 쿼리 로그를 분석하여 자주 실행되고 느린 쿼리를 찾아 인덱스를 추가하는 것이 성능 튜닝의 시작입니다.
실전 팁
💡 인덱스를 생성하기 전에 EXPLAIN을 사용하여 쿼리 실행 계획을 확인하세요. type이 ALL(풀 스캔)이면 인덱스가 필요하다는 신호입니다. key 항목에 인덱스명이 표시되면 제대로 사용되는 것입니다.
💡 복합 인덱스의 컬럼 순서는 선택도(cardinality)가 높은 것을 앞에 배치하세요. 예를 들어, (user_id, status)가 (status, user_id)보다 보통 효과적입니다(user_id가 더 다양한 값을 가지므로).
💡 문자열 컬럼에 인덱스를 만들 때는 앞부분만 인덱싱하는 prefix index를 고려하세요. CREATE INDEX idx_email ON users(email(20)); 형태로 앞 20자만 인덱싱하면 공간을 절약할 수 있습니다.
💡 INSERT/UPDATE가 빈번한 테이블에는 인덱스를 최소화하세요. 로그 테이블처럼 쓰기가 많고 읽기가 적은 경우 인덱스가 오히려 성능을 저하시킬 수 있습니다.
💡 외래 키 컬럼에는 항상 인덱스를 생성하세요. JOIN 성능에 직접적인 영향을 미칩니다. MySQL의 InnoDB는 외래 키 제약 조건을 설정하면 자동으로 인덱스를 만들지만, 명시적으로 확인하는 것이 좋습니다.
6. 트랜잭션(TRANSACTION) - 데이터 일관성의 보호막
시작하며
여러분이 온라인 쇼핑몰에서 결제를 진행하는데, 결제는 완료되었는데 재고는 차감되지 않거나, 포인트는 차감되었는데 주문은 생성되지 않는다면 어떻게 될까요? 사용자는 피해를 보고, 비즈니스는 신뢰를 잃게 됩니다.
이런 문제는 여러 개의 데이터베이스 작업이 연관되어 있는데, 중간에 오류가 발생하거나 서버가 다운되어 일부만 실행되기 때문에 발생합니다. 예를 들어, 계좌 이체는 "A 계좌에서 출금"과 "B 계좌에 입금" 두 작업이 모두 성공하거나 모두 실패해야 합니다.
하나만 실행되면 돈이 사라지거나 생겨나는 치명적인 문제가 발생합니다. 바로 이럴 때 필요한 것이 트랜잭션입니다.
여러 작업을 하나의 논리적 단위로 묶어서, 모두 성공하면 확정(COMMIT)하고 하나라도 실패하면 전체를 취소(ROLLBACK)하여 데이터 일관성을 보장합니다.
개요
간단히 말해서, 트랜잭션은 여러 개의 SQL 명령을 하나의 작업 단위로 묶어서 "모두 성공 아니면 모두 취소"를 보장하는 메커니즘입니다. 왜 이 개념이 필요한지 실무 관점에서 설명하자면, 주문 생성 프로세스를 생각해보세요.
① orders 테이블에 주문 생성 ② order_items 테이블에 상품 정보 삽입 ③ products 테이블의 재고 감소 ④ users 테이블의 포인트 차감 등 여러 단계가 있습니다. 이 중 하나라도 실패하면 전체를 취소해야 데이터가 꼬이지 않습니다.
예를 들어, 재고는 줄었는데 주문이 생성되지 않으면 판매 기회를 잃게 됩니다. 기존에는 각 단계마다 성공 여부를 확인하고 실패 시 이전 작업들을 수동으로 되돌리는 복잡한 롤백 로직을 작성했다면, 이제는 트랜잭션 블록으로 감싸기만 하면 데이터베이스가 자동으로 관리합니다.
트랜잭션의 핵심 특징은 ACID 속성입니다. Atomicity(원자성: 모두 성공 or 모두 실패), Consistency(일관성: 데이터베이스가 일관된 상태 유지), Isolation(고립성: 동시 실행되는 트랜잭션들이 서로 간섭하지 않음), Durability(영속성: 커밋된 데이터는 시스템 장애에도 보존됨).
이러한 속성들이 금융 거래, 재고 관리 등 중요한 비즈니스 로직의 정확성을 보장합니다.
코드 예제
-- 트랜잭션 시작
START TRANSACTION;
-- 주문 생성
INSERT INTO orders (user_id, total_amount, status)
VALUES (123, 50000, 'pending');
-- 방금 생성된 주문 ID 가져오기
SET @order_id = LAST_INSERT_ID();
-- 주문 상품 추가
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (@order_id, 456, 2, 25000);
-- 재고 감소
UPDATE products
SET stock_quantity = stock_quantity - 2
WHERE product_id = 456 AND stock_quantity >= 2;
-- 재고가 충분하지 않으면 롤백
-- (위 UPDATE가 0개 행에 영향을 주면 실패)
-- IF ROW_COUNT() = 0 THEN ROLLBACK; ELSE COMMIT; END IF;
-- 모든 작업이 성공하면 커밋 (확정)
COMMIT;
-- 오류 발생 시 롤백 (모두 취소)
-- ROLLBACK;
설명
이것이 하는 일: 위 코드는 주문 생성이라는 비즈니스 로직을 안전하게 처리하기 위해 여러 SQL 명령을 트랜잭션으로 묶은 예제입니다. 실무에서 금융, 전자상거래 등 데이터 정확성이 중요한 모든 도메인에서 필수적인 패턴입니다.
첫 번째로, START TRANSACTION 명령은 새로운 트랜잭션을 시작합니다. 이 시점부터 이후의 모든 SQL 명령은 임시 상태로 실행되며, 아직 다른 사용자에게는 보이지 않습니다(격리 수준에 따라 다름).
트랜잭션이 시작되면 데이터베이스는 내부적으로 언두 로그(undo log)를 기록하기 시작하여 롤백이 필요할 때 원래 상태로 복구할 수 있도록 준비합니다. 그 다음으로, INSERT, UPDATE 등 여러 SQL 명령이 순차적으로 실행됩니다.
LAST_INSERT_ID()는 방금 삽입된 행의 AUTO_INCREMENT 값을 반환하므로, order_id를 변수에 저장하여 다음 INSERT에서 사용할 수 있습니다. 재고 감소 UPDATE는 WHERE 절에 stock_quantity >= 2 조건을 추가하여, 재고가 충분할 때만 업데이트가 실행되도록 합니다.
만약 재고가 부족하면 ROW_COUNT()가 0이 되어 오류 처리 로직에서 ROLLBACK을 호출할 수 있습니다. COMMIT 명령은 트랜잭션 내의 모든 변경 사항을 확정합니다.
이 시점에 데이터는 디스크에 영구적으로 기록되고, 다른 사용자들도 변경 사항을 볼 수 있게 됩니다. COMMIT이 성공하면 Durability 속성에 따라 시스템이 다운되어도 데이터가 보존됩니다.
반대로 ROLLBACK 명령은 트랜잭션 내의 모든 변경 사항을 취소하고 트랜잭션 시작 전 상태로 되돌립니다. 애플리케이션 코드에서는 try-catch 블록으로 감싸서, 예외 발생 시 자동으로 ROLLBACK을 호출하도록 구현하는 것이 일반적입니다.
여러분이 이 코드를 사용하면 비즈니스 로직의 정확성을 보장할 수 있습니다. 부분적으로만 실행되는 "중간 상태"가 절대 발생하지 않으므로 데이터 무결성이 유지됩니다.
동시에 여러 사용자가 같은 상품을 주문하려고 해도 트랜잭션의 Isolation 속성이 데이터 충돌을 방지합니다. 또한 개발자는 복잡한 롤백 로직을 일일이 작성할 필요 없이 ROLLBACK 한 줄로 모든 것을 되돌릴 수 있어 코드가 간결해지고 버그가 줄어듭니다.
금융 시스템에서는 트랜잭션 없이 개발하는 것은 상상할 수 없을 정도로 중요한 개념입니다.
실전 팁
💡 트랜잭션은 가능한 한 짧게 유지하세요. 긴 트랜잭션은 다른 사용자의 쿼리를 블로킹하여 성능 저하를 일으킵니다. 네트워크 호출이나 파일 I/O는 트랜잭션 밖에서 처리하세요.
💡 READ COMMITTED, REPEATABLE READ 같은 격리 수준(isolation level)을 상황에 맞게 설정하세요. MySQL InnoDB의 기본값은 REPEATABLE READ이며, 대부분의 경우 적절합니다.
💡 동시성 문제가 있는 작업은 SELECT ... FOR UPDATE로 행을 잠그세요. 예를 들어, 재고 확인 후 주문을 생성하는 사이에 다른 트랜잭션이 재고를 변경하지 못하도록 막습니다.
💡 애플리케이션 레벨에서는 try-catch-finally 패턴으로 반드시 COMMIT 또는 ROLLBACK이 호출되도록 하세요. 연결이 끊어지면 자동으로 ROLLBACK되지만, 명시적으로 처리하는 것이 안전합니다.
💡 데드락(deadlock)이 발생할 수 있습니다. 두 트랜잭션이 서로 다른 순서로 행을 잠그면 교착 상태가 됩니다. 이를 방지하려면 항상 같은 순서로 테이블/행에 접근하도록 코드를 작성하세요.
7. 집계 함수(Aggregate Functions) - 데이터 요약과 통계의 핵심
시작하며
여러분이 관리자 대시보드에 "총 회원 수", "이번 달 총 매출", "평균 주문 금액" 같은 통계를 표시해야 하는데, 모든 데이터를 가져와서 애플리케이션 코드로 계산한다면 어떻게 될까요? 수백만 건의 데이터를 네트워크로 전송하고 메모리에 로드하느라 시간이 오래 걸리고 서버 리소스도 낭비됩니다.
이런 문제는 데이터 요약 작업을 적절한 곳에서 수행하지 못해서 발생합니다. 데이터베이스는 대량의 데이터를 빠르게 집계하는 최적화된 엔진을 가지고 있는데, 이를 활용하지 않고 모든 데이터를 끌어와서 처리하면 효율성이 크게 떨어집니다.
바로 이럴 때 필요한 것이 집계 함수입니다. COUNT, SUM, AVG, MAX, MIN 등으로 데이터베이스에서 직접 통계를 계산하여 최종 결과만 가져올 수 있게 해줍니다.
개요
간단히 말해서, 집계 함수는 여러 행의 데이터를 하나의 값으로 요약하는 함수들로, 개수 세기, 합계, 평균, 최댓값, 최솟값 등을 계산합니다. 왜 이 개념이 필요한지 실무 관점에서 설명하자면, 월별 매출 리포트를 만든다고 가정해봅시다.
수만 건의 주문 데이터에서 각 월의 총 매출과 평균 주문 금액을 계산해야 합니다. GROUP BY와 집계 함수를 사용하면 데이터베이스가 한 번의 쿼리로 모든 계산을 끝내고 결과만 반환합니다.
예를 들어, 실시간 대시보드의 KPI(핵심 성과 지표) 카드들은 대부분 집계 함수로 계산됩니다. 기존에는 모든 주문 데이터를 가져와서 반복문으로 합계를 계산했다면, 이제는 SUM() 함수 하나로 데이터베이스가 최적화된 알고리즘으로 계산합니다.
집계 함수의 핵심 유형은 첫째, COUNT(개수 세기), 둘째, SUM(합계), 셋째, AVG(평균), 넷째, MAX/MIN(최댓값/최솟값), 다섯째, GROUP_CONCAT(문자열 결합) 등이 있습니다. GROUP BY와 함께 사용하면 그룹별 집계가 가능하고, HAVING 절로 집계 결과를 필터링할 수 있습니다.
이러한 기능들이 복잡한 비즈니스 리포트를 간단한 쿼리로 만들어줍니다.
코드 예제
-- 기본 집계: 전체 통계
SELECT
COUNT(*) as total_users, -- 총 회원 수
COUNT(DISTINCT email) as unique_emails, -- 중복 제거한 이메일 수
MAX(created_at) as latest_signup, -- 가장 최근 가입일
MIN(created_at) as first_signup -- 가장 오래된 가입일
FROM users
WHERE is_active = true;
-- 그룹별 집계: 월별 매출 통계
SELECT
DATE_FORMAT(created_at, '%Y-%m') as month, -- 년-월 형식
COUNT(*) as order_count, -- 주문 건수
SUM(total_amount) as total_revenue, -- 총 매출
AVG(total_amount) as avg_order_value, -- 평균 주문 금액
MAX(total_amount) as max_order -- 최고 주문 금액
FROM orders
WHERE status = 'completed'
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY month DESC;
-- HAVING으로 집계 결과 필터링: 구매 금액 10만원 이상 고객만
SELECT
user_id,
COUNT(*) as order_count,
SUM(total_amount) as total_spent
FROM orders
GROUP BY user_id
HAVING total_spent >= 100000
ORDER BY total_spent DESC;
설명
이것이 하는 일: 위 코드는 대량의 데이터에서 의미 있는 통계와 인사이트를 추출하는 예제입니다. 실무에서 리포트, 대시보드, 분석 도구의 핵심 기능입니다.
첫 번째로, 기본 집계 함수들은 테이블 전체를 대상으로 계산합니다. COUNT(*)은 모든 행의 개수를 세고, COUNT(DISTINCT email)은 중복을 제거한 고유한 이메일 개수를 셉니다.
이는 "실제 활성 사용자가 몇 명인지" 파악할 때 유용합니다. MAX와 MIN은 날짜 컬럼에 사용하면 "서비스 런칭 날짜"나 "최근 활동 시각"을 알 수 있습니다.
이 모든 계산이 데이터베이스 엔진에서 한 번의 테이블 스캔으로 완료됩니다. 그 다음으로, GROUP BY 절이 추가되면 그룹별 집계가 됩니다.
DATE_FORMAT으로 날짜를 "년-월" 형식으로 변환하여 그룹핑 키로 사용하면, 각 월마다 집계 함수가 실행됩니다. 결과적으로 "2025-01월: 주문 150건, 총 매출 500만원, 평균 33,333원" 같은 월별 통계가 생성됩니다.
이는 시계열 차트나 트렌드 분석의 기반 데이터입니다. HAVING 절은 WHERE와 비슷하지만, 집계 함수의 결과를 필터링합니다.
WHERE는 집계 전에 행을 필터링하고, HAVING은 집계 후에 그룹을 필터링합니다. 예를 들어, HAVING total_spent >= 100000은 "총 구매액이 10만원 이상인 고객만" 선택합니다.
이는 VIP 고객 분석이나 타겟 마케팅에 활용됩니다. ORDER BY와 함께 사용하면 "매출 상위 10개 상품", "가입자 수가 가장 많은 월" 같은 랭킹 리포트를 만들 수 있습니다.
집계 함수의 결과 컬럼(별칭)으로 정렬할 수 있습니다. 여러분이 이 코드를 사용하면 복잡한 비즈니스 질문에 빠르게 답할 수 있습니다.
"이번 분기 매출이 얼마인가?", "어떤 고객이 가장 많이 구매했는가?", "월별 성장률은 어떠한가?" 같은 질문을 몇 줄의 SQL로 해결합니다. 데이터베이스가 인덱스를 활용해 빠르게 계산하고, 최종 결과만 전송하므로 네트워크 부하도 최소화됩니다.
또한 쿼리 자체가 비즈니스 로직을 명확하게 표현하므로, 나중에 코드를 보는 사람도 "아, 이건 월별 매출 통계구나"라고 바로 이해할 수 있어 유지보수성이 좋습니다.
실전 팁
💡 COUNT() vs COUNT(column): COUNT()은 NULL 포함 모든 행을 세고, COUNT(column)은 해당 컬럼이 NULL이 아닌 행만 셉니다. 실수하지 않도록 주의하세요.
💡 AVG() 함수는 NULL 값을 제외하고 평균을 계산합니다. 0과 NULL은 다르므로, 평균에 0을 포함시키고 싶다면 COALESCE로 NULL을 0으로 변환하세요.
💡 GROUP BY에 사용된 컬럼은 SELECT 절에도 포함해야 합니다. 그렇지 않으면 오류가 발생하거나 예상치 못한 결과가 나옵니다(SQL 모드에 따라 다름).
💡 대량 데이터의 집계는 느릴 수 있습니다. 실시간 대시보드에는 집계 테이블(summary table)을 미리 만들어두고 정기적으로 업데이트하는 방식을 고려하세요.
💡 HAVING 절보다 WHERE 절이 먼저 실행되므로, 집계 함수가 필요 없는 조건은 WHERE에 두는 것이 성능상 유리합니다. HAVING은 집계 결과를 필터링할 때만 사용하세요.
8. 서브쿼리(Subquery) - 쿼리 안의 쿼리로 복잡한 문제 해결하기
시작하며
여러분이 "평균 주문 금액보다 높은 주문들만 조회"하고 싶은데, 평균을 먼저 계산한 후 다시 쿼리를 작성하는 번거로운 과정을 겪어본 적 있나요? 또는 "가장 최근에 주문한 고객들의 정보"를 가져오려고 여러 단계로 나누어 코딩한 경험이 있나요?
이런 문제는 한 쿼리의 결과를 다른 쿼리의 조건으로 사용해야 하는데, 이를 효율적으로 처리하는 방법을 몰라서 발생합니다. 여러 번의 쿼리를 실행하고 애플리케이션 코드로 조합하면 코드가 복잡해지고, 네트워크 왕복이 늘어나며, 데이터 일관성 문제도 발생할 수 있습니다.
바로 이럴 때 필요한 것이 서브쿼리입니다. 쿼리 안에 또 다른 쿼리를 중첩하여, 한 번의 SQL 문으로 복잡한 로직을 표현할 수 있게 해줍니다.
개요
간단히 말해서, 서브쿼리는 다른 쿼리 안에 포함된 SELECT 문으로, 외부 쿼리의 조건이나 값을 동적으로 제공하는 역할을 합니다. 왜 이 개념이 필요한지 실무 관점에서 설명하자면, "이번 달 주문 금액이 전월 평균보다 높은 고객"을 찾는다고 가정해봅시다.
먼저 전월 평균을 계산하는 쿼리를 실행하고, 그 결과를 애플리케이션에서 받아 다시 WHERE 절에 넣는 방식은 비효율적입니다. 서브쿼리를 사용하면 데이터베이스가 자동으로 평균을 계산하고 그 값으로 필터링까지 한 번에 처리합니다.
예를 들어, "각 카테고리별 최고 가격 상품"이나 "주문이 한 번도 없는 회원" 같은 복잡한 조건도 서브쿼리로 간단히 표현할 수 있습니다. 기존에는 임시 변수에 첫 번째 쿼리 결과를 저장한 후 두 번째 쿼리에서 사용했다면, 이제는 서브쿼리로 한 번에 처리할 수 있습니다.
서브쿼리의 핵심 유형은 첫째, 스칼라 서브쿼리(단일 값 반환), 둘째, 인라인 뷰(FROM 절의 서브쿼리), 셋째, 상관 서브쿼리(외부 쿼리 값 참조), 넷째, EXISTS/IN 서브쿼리(존재 여부 확인) 등입니다. 각 유형은 특정 상황에 최적화되어 있으며, 올바르게 사용하면 복잡한 비즈니스 로직을 우아하게 표현할 수 있습니다.
코드 예제
-- 스칼라 서브쿼리: 평균보다 비싼 주문 찾기
SELECT order_id, user_id, total_amount
FROM orders
WHERE total_amount > (
SELECT AVG(total_amount) FROM orders
);
-- IN 서브쿼리: 주문이 있는 사용자만 조회
SELECT user_id, username, email
FROM users
WHERE user_id IN (
SELECT DISTINCT user_id FROM orders WHERE status = 'completed'
);
-- NOT EXISTS 서브쿼리: 주문이 한 번도 없는 사용자
SELECT u.user_id, u.username, u.email
FROM users u
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.user_id
);
-- 인라인 뷰: 월별 매출과 전월 대비 증가율
SELECT
month,
total_revenue,
LAG(total_revenue) OVER (ORDER BY month) as prev_month,
ROUND((total_revenue - LAG(total_revenue) OVER (ORDER BY month)) /
LAG(total_revenue) OVER (ORDER BY month) * 100, 2) as growth_rate
FROM (
SELECT DATE_FORMAT(created_at, '%Y-%m') as month,
SUM(total_amount) as total_revenue
FROM orders
WHERE status = 'completed'
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
) as monthly_sales;
설명
이것이 하는 일: 위 코드는 한 쿼리의 결과를 다른 쿼리의 조건으로 활용하여, 복잡한 비즈니스 요구사항을 우아하게 해결하는 예제입니다. 실무에서 고급 데이터 분석과 필터링에 필수적인 기법입니다.
첫 번째로, 스칼라 서브쿼리는 괄호 안의 SELECT가 단일 값(평균)을 반환하고, 그 값이 외부 쿼리의 WHERE 조건에 사용됩니다. 데이터베이스는 먼저 서브쿼리를 실행하여 전체 주문의 평균 금액을 계산한 후, 그 값보다 큰 주문들만 필터링합니다.
이는 "이상치 탐지", "고가 상품 분석" 등에 유용합니다. 서브쿼리가 단일 값을 반환하므로 비교 연산자(>, <, =)와 함께 사용할 수 있습니다.
그 다음으로, IN 서브쿼리는 서브쿼리가 여러 값의 리스트를 반환하고, 외부 쿼리는 그 리스트에 포함된 값들만 선택합니다. "완료된 주문이 있는 사용자"를 찾는 것은 사실상 users와 orders를 세미 조인(semi-join)하는 것입니다.
DISTINCT를 사용하여 한 사용자가 여러 주문을 해도 한 번만 반환되도록 합니다. IN 서브쿼리는 JOIN으로도 표현할 수 있지만, 로직이 더 명확하게 읽힙니다.
NOT EXISTS 서브쿼리는 상관 서브쿼리의 대표적인 예입니다. 외부 쿼리의 각 행(u)에 대해 서브쿼리가 실행되며, o.user_id = u.user_id 조건으로 외부 행을 참조합니다.
"이 사용자의 주문이 존재하는가?"를 체크하고, 존재하지 않으면(NOT EXISTS) 결과에 포함됩니다. 이는 "구매 경험이 없는 회원" 타겟 마케팅에 활용됩니다.
SELECT 1은 실제 값은 중요하지 않고 존재 여부만 확인하므로 최적화를 위해 사용하는 관용적 표현입니다. 마지막으로, 인라인 뷰는 FROM 절에 서브쿼리를 사용하여 임시 테이블처럼 동작합니다.
먼저 monthly_sales라는 가상 테이블에 월별 매출을 집계한 후, 외부 쿼리에서 윈도우 함수(LAG)를 사용해 전월 매출과 비교합니다. 이는 "월별 성장률", "트렌드 분석" 같은 시계열 분석에 강력합니다.
인라인 뷰는 복잡한 쿼리를 논리적인 단계로 나누어 가독성을 높이는 데도 유용합니다. 여러분이 이 코드를 사용하면 복잡한 데이터 요구사항을 간결하게 표현할 수 있습니다.
여러 번의 쿼리와 애플리케이션 로직을 한 번의 SQL로 대체하므로 코드가 단순해지고 성능도 향상됩니다. 서브쿼리는 데이터베이스 옵티마이저가 최적의 실행 계획을 선택할 수 있는 여지를 제공하며, 특히 EXISTS나 IN은 인덱스를 효과적으로 활용합니다.
또한 서브쿼리는 쿼리의 의도를 명확히 드러내므로("평균보다 높은 것", "주문이 없는 회원" 등) 코드 리뷰와 유지보수가 쉬워집니다.
실전 팁
💡 서브쿼리가 많은 행을 반환하면 성능이 저하될 수 있습니다. EXPLAIN으로 실행 계획을 확인하고, 필요하면 JOIN으로 재작성하세요. 최신 MySQL은 서브쿼리를 자동으로 JOIN으로 최적화하기도 합니다.
💡 상관 서브쿼리는 외부 쿼리의 각 행마다 실행되므로 느릴 수 있습니다. 가능하면 비상관 서브쿼리나 JOIN으로 대체하세요. 하지만 로직이 명확하다면 가독성을 위해 사용하는 것도 좋습니다.
💡 IN 서브쿼리 대신 EXISTS를 사용하는 것이 더 빠를 수 있습니다. EXISTS는 첫 번째 매칭을 찾으면 즉시 중단하지만, IN은 모든 값을 비교할 수 있기 때문입니다.
💡 서브쿼리에서 LIMIT를 사용할 때는 ORDER BY를 함께 사용하세요. 그렇지 않으면 어떤 행이 선택될지 예측할 수 없어 결과가 매번 달라질 수 있습니다.
💡 인라인 뷰에는 반드시 별칭(alias)을 붙여야 합니다. FROM (SELECT ...) as subquery_name 형태로 작성하세요. 별칭이 없으면 오류가 발생합니다. 이상으로 MySQL 핵심 개념 완벽 정리 카드 뉴스를 완성했습니다. 데이터베이스와 테이블 생성부터 시작하여 SELECT, INSERT/UPDATE, JOIN, 인덱스, 트랜잭션, 집계 함수, 서브쿼리까지 실무에서 가장 중요한 8가지 핵심 개념을 다뤘습니다. 각 카드는 실제 개발 현장에서 바로 활용할 수 있는 구체적인 예제와 팁으로 구성되어 있습니다.