MySQL 쓰면서 하지 말아야 할 것 17가지
*MySQL 쓰면서 하지 말아야 할 것 17가지* 권장사항이다. 이것을 이해하면 당신의 어플리케이션이 더 나은 성능을 발휘할 것이다. 다만 이것이 사람의 실력을 판단하는 척도로 사용되서는 안 될 것
blog.lael.be
1. 작게 생각하기
- 조만간 규모가 커질거라면 MySQL ecosystem을 봐야된다.
규모가 큰 서비스란 일반적으로 일일 활성 사용자(DAU) 10만명 이상, 초당 트랜잭션(QPS) 1000건 이상을 의미한다.
이렇게 규모가 커지면 단일 MySQL 인스턴스로는 감당이 어려워지고 확장성이 필수적으로 고려되어야 한다.
MySQL ecosystem이란 복제(Replicaion), 파티셔닝, ProxySQL, MySQL Router 등 확장을 위핸 도구들의 집합을 의미한다.
1. Replication : Master-Slave(Primary-Replica) 복제 통해 읽기 부하를 분산시키는 방식
- Read/Write 분리 : Master는 쓰기, Replica는 읽기를 담당하여 부하 분산
- Semi-Synchronous, Asynchronous 복제 방식 고려
2. Partitioning : 테이블을 여러 개의 파티션으로 분리하여 데이터 관리 최적화
- Range Partitioning : 날짜별, ID 범위별 분할
- Hash Partitioning : 특정 컬럼 값을 해싱하여 균등 분배
3. ProxySQL / MySQL Router: 다중 MySQL 서버를 관리하고 쿼리를 적절히 라우팅하는 프록시 역할 수행
- 트래픽 부하 분산
- Failover 처리
4. Sharding : 데이터를 여러 개의 MySQL 서버에 분산 저장하여 확장성 향상
- 수직 샤딩: 기능 단위로 테이블을 분할 (ex: 주문 데이터, 사용자 데이터 분리)
- 수평 샤딩: 사용자 ID 범위별로 데이터 분배 (ex: ID 1100만 -> 샤드 A, 100만200만 -> 샤드 B)
이런 기술들을 미리 설계에 고려하지 않으면 이후에 시스템을 확장할 때 많은 비용과 시간이 소요될 수 있음
예를 들어 Facebook은 MySQL을 수백 개의 샤드로 나눠 데이터를 저장하고, 캐시로 Memcached를 적극 활용.
- 그리고 캐싱 빡시게 안 하는 메이저 웹사이트는 없다.
캐싱을 빡세게 한다 ==> 다층적 캐시 전략
캐싱을 하면 DB 부하 감소 + 응답 속도 향상
캐싱을 안 하면 DB가 과부화되어 성능 저하 발생 가능성
다층적 캐시 전략
- 애플리케이션 레벨 : Redis/Memcached 사용
- DB 레벨 : Query Cache, Buffer Pool 최적화
- 웹서버 레벨 : Varnish 같은 HTTP 캐시 사용
(실제 사례로 캐싱 없이 직접 DB 조회 시 응답시간 300ms -> 캐싱 적용 후 5ms로 개선된 경우가 있습니다.)
( + Varnish : 웹 서버(예: Apache, Nginx) 앞에서 캐시 서버 역할을 하여 반복되는 요청을 빠르게 응답하도록 도와줍니다. ex: 인기 있는 뉴스 기사 페이지를 캐싱하여, 1만 명이 동시 접속해도 DB 부하 없음.)
캐싱 적용 효과
DB 직접 조회 300ms, Redis 캐시 적용 후 5ms로 단축
- develooper.com의 Hansen PT랑 Ilia 튜토리얼 볼 것
ansen PT, Ilia Alshanetsky는 PHP 및 MySQL 최적화 관련 유명 전문가.
develooper.com은 고성능 PHP/MySQL 튜닝 가이드를 제공하던 사이트 (현재는 비활성화).
Ilia Alshanetsky : PHP 핵심 개발자로, PHP 성능 최적화 및 보안 관련 많은 기여를 한 인물.
현재는 해당 자료들이 최신 버전의 MySQL/PHP에 맞춰 업데이트되지 않아, 최신 정보를 원한다면 Percona, High Scalability Blog, MySQL 공식 문서 등을 참고하는 것이 좋음.
- 처음부터 확장 가능하게 아키텍처 잘 쪼개놔야된다.
확장 가능한 아키텍처
- 트래픽 증가에 따라 성능 저하 없이 확장이 가능한 구조
- 단순히 서버를 추가하는 것이 아니라, 데이터베이스, 캐싱, 애플리케이션 구조를 유연하게 설계
확장성을 고려한 아키텍쳐 설계 원칙
1. 수평 확장(Scale Out)이 가능해야 함.
- 서버를 추가하는 것만으로 성능이 개선되어야 함.
예) Master-Slave Replication으로 읽기 부하 분산, Sharding으로 데이터 분산
2. Stateless(상태 비저장) 설계
- 세션 정보를 서버가 아닌 Redis, Memcached 등에 저장하여 여러 서버에서 동일한 세션을 사용할 수 있도록 함.
3. CQRS(Command Query Responsitility Segregation) 적용
- Read와 Write 로직을 분리하여 읽기 전용 DB 추가해 부하 분산
4. 이벤트 기반 아키텍처 적용
- 메시지 큐(RabbitMQ, Kafka) 등을 활용해 실시간 데이터 처리를 비동기적으로 수행
5. CDN(Content Delivery Network) 활용
- 정적 콘텐츠는 AWS CloudFront, Akamai, Cloutflare 같은 CDN을 활용해 빠르게 제공
트위터 : Follower 데이터를 MySQL 샤딩 + Redis 캐시 조합으로 관리
페이스북 : MySQL + RocksDB + TAO(Graph Database) 조합으로 확장성 확보
- 복제랑 파티셔닝 어떻게 할지 미리 계획 세워놔라.
복제 : 주 서버(Master)에서 변경된 데이터를 복제 서버(Slave)로 동기화
고려사항
1. 비동기 복제 vs 반동기 복제 vs 완전 동기 복제
- 비동기 복제 : Master가 Slave 응답을 기다리지 않음(기본 설정)
- 반동기 복제 : 최소 한 개의 Slave가 데이터 수신 후 응답
- 완전 동기 복제 : 모든 Slave가 데이터 수신 후 응답해야 함 (성능 저하 가능)
2. 복제 지연 문제 해결
- 네트워크 상태, 트랜잭션 부하로 인해 Slave 복제가 지연될 수 있음
- SHOW SLAVE STATUS 로 복제 지연(Second_Behind_Master) 모니터링
파티셔닝: 테이블이 너무 크면 쿼리 속도 느려지고 인덱스 효율 떨어지므로 파티셔닝 활용
# 범위 기반 파티셔닝
CREATE TABLE orders (
id INT NOT NULL,
order_date DATE NOT NULL
) PARTITION BY RANGE(YEAR(order_date)) (
PARTITION p2019 VALUES LESS THAN (2020),
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022)
);# 리스트 기반 파티셔닝
CREATE TABLE users (
id INT NOT NULL,
country VARCHAR(10) NOT NULL
) PARTITION BY LIST COLUMNS(country) (
PARTITION p_usa VALUES IN ('USA'),
PARTITION p_korea VALUES IN ('Korea'),
PARTITION p_japan VALUES IN ('Japan')
);# 해시 기반 파티셔닝
CREATE TABLE logs (
id INT NOT NULL,
message TEXT NOT NULL
) PARTITION BY HASH(id) PARTITIONS 4;
복제와 파티셔닝은 함께 고려해야 함
1. 단순히 복제만 한다고 무조건 성능 좋아지지 않음
- 파티셔닝 + Master-Slave 복제 조합 -> 트랜잭션 처리 속도 향상
2. 데이터가 너무 많으면 파티셔닝 고려해야 함
- 파티셔닝 없이 1억 개 데이터 조회 → 5초 걸림 → 파티셔닝 적용 후 300ms로 단축
ex: 읽기 부하는 복제로 분산, 쓰기 부하는 파티셔닝으로 최적화
- 파일 기반 세션 좀 쓰지마 -_-
기본적으로 PHP나 웹 서버(Apache, Nginx)는 세션을 서버의 파일 시스템에 저장
파일 기반 세션을 사용하면 다음과 같은 문제 발생
1. 동시 접속자가 많아질 경우 : 파일 I/O 병목 발생
2. 로드밸런싱 환경에서 문제 : 서버가 여러 개일 경우, 사용자의 세션이 특정 서버에만 저장되므로 다른 서버에서는 세션을 인식할 수 없음
3. 속도 느림 : 파일을 읽고 쓰는 속도 < 메모리 기반 세션(Redis, Memcached)
대안: Redis 또는 Memcached를 이용한 세션 저장
# Redis를 활용한 세션 저장
Jedis jedis = new Jedis("127.0.0.1", 6379);
HttpSession session = request.getSession();
session.setAttribute("user", "testUser");
// Redis에 세션 저장
jedis.setex("session:" + session.getId(), 1800, "testUser");
// Redis에서 세션 가져오기
String user = jedis.get("session:" + session.getId());장점 :
1. 파일보다 훨씬 빠른 속도
2. 여러 서버에서도 세션 공유 가능
3. TTL(Time-to-Live) 설정으로 자동 만료 가능
파일 기반 세션 to Redis 세션 저장 방식 변경 -> 페이지 로드 속도 향상 + 서벌 부하 감소 예상 가능
- 그렇다고 너무 쓸데없이 크게 생각하지도 말 것
확장성을 고려한다고 처음부터 너무 큰 구조를 설계하는 것은 비효율적.
- YAGNI(You Ain't Gonna Need It) : "당장 필요하지 않다면 설계하지 말라"
- 성능 최적화는 현재 필요한 만큼만 하고, 트래픽 증가 시 확장할 방법을 고민하는 것이 중요
ex: 스타트업 초기부터 MSA 도입...,
적절한 타이밍에 확장을 고려해야 함
- 서비스 초기에는 단순한 구조가 유지보수하기 좋음
- 트래픽이 증가할 때 필요한 부분만 최적화
ex: 초기 단일 DB -> 트래픽 증가 시 복제 + 캐싱 도임
- 특히 성능하고 확장성 구분 못 하면 난감함
성능 vs 확장성 차이
| 구분 | 성능 최적화 | 확장성 최적화 |
| 목적 | 개별 요청 속도 개선 | 트래픽 증가 대응 |
| 방식 | 쿼리 최적화, 인덱스 활용 | DB 샤딩, 로드 밸런싱 |
| 단점 | 트래픽 증가 시 효과 제한 | 초기 오버헤드 발생 |
ex)
| 문제 | 성능 최적화 | 확장성 최적화 |
| DB 읽기 속도 저하 | 인덱스 추가 | Read Replica 추가 |
| DB 쓰기 부하 증가 | 쿼리 튜닝 | Sharding 도입 |
| 서버 응답 속도 문제 | 캐시 활용 | 로드 밸런싱 적용 |
-> DB가 느려지면 무조건 Master-Slave 복제로 해결됨 --> X
-> 복제는 읽기 부하를 줄이는 것이지, 쓰기 성능은 개선되지 않음
-> 쓰기 부하가 많다면 샤딩이나 파티셔닝 고려해야 함.
2. EXPLAIN 사용하지 않기
- SELECT 앞에 EXPLAIN 이라고 붙이기만 하면 되는 것을 (..)
EXPLAIN이란? - MySQL 실행 계획(Excution Plan)을 확인하는 명령어 - 어떤 인덱스를 사용하는지, 테이블을 어떻게 스캔하는지 보여줌 - 성능 최적화를 위해 반드시 사용해야 하는 도구
- 쿼리를 실행하기 전에 MySQL이 어떤 방식으로 데이터 검색하는지 분석 가능
MySQL이 어떻게 쿼리 실행할지 예측 가능
type, key, Extra 컬럼을 보고 성능 분석 가능
- 실행 계획 확인
실행계획(Excution Plan)이란?
- 쿼리가 어떤 방식으로 테이블을 검색하는지 보여주는 계획
- 최적화되지 않은 실행 계획이라면 풀 테이블 스캔(Full Table Scan) 발생 -> 성능 저하
실행 계획 분석에 사용되는 주요 컬럼
| 컬럼명 | 설명 |
| id | 실행 순서 (1부터 시작) |
| select_type | 단순 쿼리 / 서브쿼리 구분 |
| table | 조회하는 테이블명 |
| type | 조인 방식 (ALL, index, range, ref, eq_ref, const, system |
| possible_keys | 사용할 가능성이 있는 인덱스 목록 |
| keys | 실제로 사용된 인덱스 |
| rows | MySQL이 예상하는 검색해야 하는 행 개수 |
| Extra | 추가적인 정보 (Using index, Using where 등) |
- 타입 컬럼에 index 써있는거랑 Extra 컬럼에 index 써있는거랑 “매우 큰” 차이 있음
MySQL에서 인덱스 활용하는 두 가지 방식
| 구분 | 설명 | 최적화 여부 |
| type = index | Full Index Scan (모든 인덱스를 읽음) | X (비효율적) |
| Extra = Using index | Covering Index 사용 (필요한 데이터만 조회) | O (효율적) |
타입 컬럼에 index 가 있으면 full scan, Extra 컬럼에 Using index 가 있으면 Covering index
* 타입에 있으면 Full 인덱스 스캔 (안 좋다.)
Full Index Scan이란?
- type = index 경우 모든 인덱스를 순차적으로 탐색
- 테이블 전체 읽는 것보단 빠르지만, 여전히 비효율적
- 인덱스 키만 탐색하므로 Using where가 같이 나오면 좋지 않은 신호
(type = index, Full Index Scan 발생)
EXPLAIN SELECT name FROM users;| id | select_type | table | type | possible_keys | keys | rows | Extra |
| 1 | SIMPLE | users | index | Null | email_idx | 100000 | Using index |
WHERE 조건이 없으면 전체 인덱스를 읽어야함 -> 모든 인덱스를 탐색하면서 데이터를 가져오기 때문에 느림
* Extra 컬럼에 있으면 Covering 인덱스 찾았다는 의미임 (좋다!)
(type = ref, Covering Index 적용, 최적화)
EXPLAIN SELECT email FROM users WHERE email = 'test@example.com';| id | select_type | table | type | possible_keys | keys | rows | Extra |
| 1 | SIMPLE | users | ref | email_idx | email_idx | 1 | Using index |
필요한 데이터가 인덱스 자체에 저장되어 추가적인 테이블 조회 없이 가져올 수 있는 경우
인덱스에 있는 값만 읽고 추가적인 테이블 조회 필요 없음 -> 성능 향상
- 5.0 이후부터는 index_merge 최적화도 한다.
Index_merge란?
- 여러 개의 인덱스를 병합하여 검색 성능을 최적화하는 방식
- MySQL 5.0 이후 도입
Index_merge 최적화는 OR 조건을 포함한 쿼리에서 유용
단, 필요 없는 인덱스가 많으면 오히려 성능 저하 가능 (주의 필요)
ex)
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com' OR age = 30;| id | select_type | table | type | possible_keys | keys | rows | Extra |
| 1 | SIMPLE | users | ref | email_idx, age_idx |
email_idx, age_idx |
1 | Using union(email_idx, age_idx) |
최적화 방식
- Union -> 여러 인덱스 결과를 합쳐서 검색
- Intersection -> 여러 인덱스에서 공통된 결과만 반환
3. 잘못된 데이터 타입 선택
- 한 메모리 블럭 단위에 인덱스 레코드가 많이 들어갈수록 쿼리가 빨리 실행될 것이다. (중요)
데이터 타입 크기가 작은 것이 중요한 이유
- MySQL은 데이터를 페이지 단위(보통 16KB)로 읽고 씀
- 한 페이지 안에 더 많은 인덱스 레코드를 저장할 수 있으면 성능이 향상됨
- 데이터 타입이 클수록 한 페이지에 저장할 수 있는 인덱스 레코드 수가 줄어들어 성능 저하 발생
ex: 테이블 크키에 따른 성능 차이
- BIGINT (8byte) vs INT (4byte)
1. BIGINT -> 16KB 페이지에 약 2,000개 저장 가능
2. INT -> 16KB 페이지에 약 4,000개 저장 가능
- 즉, INT를 사용하면 한 페이지에 더 많은 데이터를 조회할 수 있어 성능이 향상됨
∴
- 작은 데이터 타입을 사용해야 디스크 I/O가 줄어들고 쿼리 속도가 빨라짐
- 메모리 블록에 더 많은 레코드를 저장하면 인덱스 탐색도 빨라짐
- 아.. 정규화 좀 해 -_-… (이거 정말 충격과 공포인 듯)
왜 정규화 ?
- 데이터 중복을 줄여 저장 공간을 절약하고, 데이터 무결성 유지
- 정규화를 안 하면 중복 데이터로 인해 데이터 일관성이 깨지고, 업데이트 성능이 저하됨
ex:
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_name VARCHAR(255), -- 고객 이름 중복 발생 가능
customer_email VARCHAR(255), -- 이메일 중복 발생 가능
product_name VARCHAR(255)
);
# 고객 정보 중복 저장 -> 데이터 무결성 문제 발생
# 고객 이메일 변경 -> 모든 레코드 수정 필요CREATE TABLE customers (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
email VARCHAR(255) UNIQUE
);
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
product_name VARCHAR(255),
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
# 고객 정보를 별도 테이블에 저장하여 중복 방지 + 데이터 무결성 보장
정규화 하지 않으면
- 데이터 중복, 저장 공간 낭비, 업데이트 성능 저하 문제가 발생
- 1~3차 정규화 적용해 최소한의 정규화 유지해야 ..
- 가장 작은 데이터 타입을 써.. (진짜 BIGINT가 필요하냐고..)
- BIGINT (8byte) vs INT (4byte) -> 메모리 사용량 2배 차이
- 테이블 크기가 커질수록 디스크 I/O 증가 -> 쿼리 성능 저하
- 불필요한 공간 낭비 (99% 경우 INT로 충분)
올바른 데이터 타입 선택 가이드
| 데이터 타입 | 크기 | 사용 사례 |
| TINYINT | 1B | 0~255, 작은 카운트 값 |
| SMALLINT | 2B | 0~65,535, 중간 규모 ID |
| MEDIUMINT | 3B | 0~16,777,215, 큰 카운트 값 |
| INT | 4B | 0~4,294,967,295, 일반적인 ID |
| BIGINT | 8B | 필요할 때만 사용 (10억 이상의 값) |
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT, -- BIGINT가 필요 없음
age TINYINT UNSIGNED, -- 나이는 255 이하이므로 TINYINT 사용
salary MEDIUMINT UNSIGNED -- 연봉 데이터가 수백만 단위라면 MEDIUMINT 사용
);
# 불필요하게 BIGINT를 사용하지 말고, 최소한의 크기로 데이터 타입 설정할 것
# 테이블 크기 줄이면 성능 향상
- 인덱스 걸리는 필드는 정말 최소한으로 데이터 크기를 써야된다고.
인덱스 크기가 작아야 성능이 좋은 이유
- 인덱스는 메모리와 디스크에서 빠르게 검색되도록 설계됨
- 인덱스 크기가 커지면 탐색 속도가 느려지고, 디스크 I/O 부담 증가
- 작은 크기의 인덱스를 사용하면 더 많은 인덱스를 메모리에 로드 가능
오용 (큰 데이터 타입 사용)
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(255) UNIQUE -- VARCHAR(255)에 인덱스를 걸면 비효율적
);
# VARCHAR(255) 인덱스는 메모리와 디스크 공간 많이 차지
# 대안: HASH 값(고정 길이) 사용
올바른 (최적화된 인덱스 적용)
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(255),
product_hash CHAR(32) GENERATED ALWAYS AS (MD5(product_name)) STORED UNIQUE
);
# VARCHAR(255) 대신 CHAR(32) 인덱스를 사용해 인덱스 크기 축소
∴
- 인덱스를 걸 때는 데이터 크기를 최소화해야 성능 최적화 가능
- VARCHAR 대신 정수형 ID 또는 해시 값을 사용하는 것이 효과적
- IP는 INT UNSIGNED로 저장해!! (아주 공감)
IP 주소를 문자열로 저장하면 비효율
- VARCHAR(15) 사용 시 15바이트 + 가변 길이 저장 비용 발생
- 대신 IP를 숫자로 변환하면 INT(4바이트)로 저장 가능
=> 공간 절약 + 빠른 검색 속도 보장
* 이럴 때 쓰라고 INET_ATON 함수가 아예 내장되어 있음.
INET_ATON() & INET_NTOA() 함수 활용
-- 문자열 IP를 정수로 변환
SELECT INET_ATON('192.168.1.1'); -- 결과: 3232235777
-- 정수를 문자열 IP로 변환
SELECT INET_NTOA(3232235777); -- 결과: '192.168.1.1'
올바른 테이블 설계
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
ip_address INT UNSIGNED NOT NULL -- VARCHAR 대신 INT 사용
);
∴ IP 주소를 VARCHAR가 아닌 INT UNSIGNED로 저장하면 성능 향상됨
INET_ATON() & INET_NTOA() 함수를 사용하여 변환 가능
4. PHP에서 pconnect 쓰는 짓
- 아파치에서 좀비 프로세스라도 생기면 그 커넥션은 그냥 증발하는거야..
Java 환경에서 커넥션 문제 (Connection Leak & Orphaned Connections)
- Java에서는 보통 HikariCP, C3P0, Apache DBCP 같은 커넥션 풀을 사용
- 커넥션 풀을 잘못 관리하면 "유령(Orphaned) 커넥션" 또는 "커넥션 누수(Connection Leak)" 발생
- 특히 JVM 프로세스가 비정상 종료되거나, WAS(Apache Tomcat 등)가 강제 종료될 경우
- 풀에 있는 커넥션이 제대로 반환되지 않고 증발
- MySQL 서버에는 여전히 해당 커넥션이 열려 있다고 인식 - 불필요한 커넥션이 쌓여 리소스 낭비
ex: 잘못된 커넥션 관리 (연결이 닫히지 않음)
public void executeQuery() throws SQLException {
Connection conn = dataSource.getConnection(); // 커넥션 가져오기
PreparedStatement stmt = conn.prepareStatement("SELECT * FROM users");
ResultSet rs = stmt.executeQuery();
// 여기서 close()를 호출하지 않으면 커넥션이 풀에 반환되지 않음!
}
# 문제점
- close() 호출 없이 메서드가 종료되면 커넥션이 풀에 반환되지 않음
ㄴ> 지속적으로 커넥션이 소모되고 결국 커넥션 부족(Connection Exhaustion) 발생
해결: try-with-resources를 사용하여 자동으로 커넥션 닫기
public void executeQuery() throws SQLException {
try (Connection conn = dataSource.getConnection();
PreparedStatement stmt = conn.prepareStatement("SELECT * FROM users");
ResultSet rs = stmt.executeQuery()) {
while (rs.next()) {
System.out.println(rs.getString("name"));
}
} // try 블록이 끝나면 자동으로 conn, stmt, rs가 close됨!
}
# try-with-resources를 사용하면 close() 호출 없이도 자동으로 커넥션이 닫힘
커넥션 풀의 리소스를 낭비하지 않고 효율적으로 관리 가능
추가적인 방어 기법 : Connection Timeout 설정
- 혹시라도 유령 커넥션이 생길 경우 MySQL이 자동으로 정리할 수 있도록 설정
# MySQL 서버 측 설정 (my.cnf)
wait_timeout = 28800 # 일정 시간(초) 동안 사용되지 않은 커넥션을 자동 종료
# HikariCP 설정 예제
HikariConfig config = new HikariConfig();
config.setMaximumPoolSize(10);
config.setIdleTimeout(30000); // 30초 동안 미사용 시 커넥션 닫기
config.setMaxLifetime(1800000); // 30분 이상 사용된 커넥션 강제 종료
# 이 설정을 적용하면 WAS가 죽더라도 MySQL이 일정 시간 후 불필요한 커넥션을 자동 정리
∴ Java에서도 커넥션을 잘못 관리하면 유령 커넥션이 발생하여 MySQL 리소스가 낭비됨
try-with-resourcs를 사용하여 커넥션을 자동으로 닫아야 함
HikariCP 등 커넥션 풀 설정을 최적화하여 불필요한 커넥션을 정리해야 함
- 어차피 MySQL 접속 속도는 Oracle이나 PostgreSQL 보다 10~100배 빠르다고.
MySQL이 다른 DB보다 접속 속도 빠른 이유
- MysQL은 기본적으로 가벼운 트랜잭션 관리 구조를 가짐
- Oracle, PostgreSQL은 ACID를 엄격하게 보장하는 구조라서 커넥션 오버헤드가 큼
- MySQL은 커넥션 생성 속도와 쿼리 실행 속도가 상대적으로 빠름
커넥션 성능 비교 (일반적인 경우)
| DBMS | 커넥션 생성 속도 | 트랜잭션 처리 속도 |
| MySQL | 빠름 (100~500μs) | 빠름 (InnoDB의 경우) |
| PostgreSQL | 중간 (500~1000μs) | 강력한 트랜잭션 기능 제공 |
| Oracle | 느림 (1~10ms) | 복잡한 트랜잭션 관리, 보안 기능 |
Java 환경에서의 커넥션 관리 차이점
- MySQL
- getConnection() 속도가 빠름
- 커넥션 풀(HicariCP, C3P0)을 사용하면 거의 지연 없이 커넥션 제공 가능
- Oracle, PostgreSQL
- 풀링 없이는 커넥션 생성 비용이 매우 큼 (JDBC Connection 생성 자체가 무겁기 때문)
- 보통 1개의 커넥션을 최대한 재사용하는 방식이 필요함
PostgreSQL, Oracle을 사용하는 경우 커넥션 생성 시간이 길어
-> HikariCP 같은 커넥션 풀을 적극 활용해야 함
MySQL은 커넥션 생성이 빠르지만, 그래도 풀링을 하는 것이 베스트 프랙티스
∴ MySQL은 기본적으로 커넥션 생성 속도가 빠르므로, 다른 DB보다 상대적으로 유리
하지만 대규모 트래픽에서는 MySQL도 커넥션 풀을 사용해야 성능이 유지됨
Java에서는 HikariCP 같은 커넥션 풀을 필수적으로 사용해야 최적의 성능을 유지 가능
5. 너무 과도한 DB 추상화 계층을 두는 것
- 어디 포팅 열심히 할 거 아니면 추상화 계층 쓰지마 (ADODB, MDB2, PearDB 등)
DB 추상화 계층이란?
- JDBC, JPA, Hibernate, MyBatis 같은 프레임워크를 사용하여 DB 접근을 추상화하는 방식
- 목적
1. DBMS 변경이 용이하도록 (ex: MySQL to PostgreSQL)
2. 코드를 간결하게 유지 (SQL 직접 작성 없이 ORM 사용)
3. SQL 주입 곡역 방지 및 유지보수성 향상
과도한 추상화가 문제인 이유
- 불필요한 추상화 계층을 두면 오히려 성능 저하 + 복잡성 증가
- 예를 들어 JPA/Hibernate를 무분별하게 사용하면 쿼리 최적화가 어려워지고 성능 저하 발생
- DB 변경이 필요한 경우가 거의 없는데도 과도한 추상화를 두면 유지보수 비용만 증가
Java에서 대표적인 DB추상화 계층
| 프레임워크 | 특징 | 과도하게 사용하면? |
| JPA (Hibernate) | 객체와 DB 매핑 (ORM) | 복잡한 쿼리는 최적화 어려움 (N+1 문제 발생 가능) |
| Spring Data JPA | 레포지토리 인터페이스 자동 생성 | 쿼리 튜닝이 힘듦, 동적 쿼리 작성 어려움 |
| MyBatis | SQL을 XML/Annotation으로 관리 | 쿼리 최적화 가능하지만, 유지보수 부담 증가 |
| JDBC (Raw SQL) | 직접 SQL 작성 | 가장 빠르지만, 코드가 복잡해질 수 있음 |
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
User findByEmail(String email);
}
# 문제점
단순 조회에도 JPA를 사용하면 불필요한 Lazy Loading, N+1 문제 발생 가능성
DB 구조가 복잡한 경우 JPA가 최적의 SQL 생성하지 못함
@Query("SELECT u FROM User u WHERE u.email = :email")
User findByEmail(@Param("email") String email);
# 직접 SQL 사용하여 성능을 최적화
ORM이 불필요한 경우 JDBC나 MyBatis를 활용하는 것이 더 적절할 수도
- scale out 가능한걸 쓰라고.
수직 확장 vs 수평 확장
| 개념 | 설명 | 장점 | 단점 |
| Scale-Up (수직 확장) | 단일 서버의 CPU, RAM, Disk를 업그레이드 | 단순한 설정, 유지보수 쉬움 | 서버 성능 한계, 비용 증가 |
| Scale-Down (수평 확장) | 여러 개의 DB 서버로 부하 분산 (Master-Slave, Sharding) |
무한 확장 가능, 트래픽 증가에 유연하게 대응 가능 | 설계 복잡성 증가 |
Scale-Out을 고려해야 하는 이유
- 대용량 트래픽을 처리할 때 단일 DB 서버(Single Node)로는 한계가 있음
- Scale-Up(서버 성능 업그레이드) 방식은 물리적 한계가 존재
- Scale-Down하면 여러 개의 DB 노드를 통해 트래픽 분산 가능
Scale-Out 방식
| 방식 | 설명 | 사용 사례 |
| Read Replica (Master-Slave Replication) | 읽기 부하를 Slave 서버로 분산 | 조회가 많은 서비스 (ex: 뉴스, SNS) |
| Sharding (수평 분할) | 데이터를 여러 DB에 나누어 저장 | 대규모 트랜잭션 서비스 (ex: 쇼핑몰, 결제 시스템) |
| ProxySQL / MySQL Router | SQL 요청을 여러 DB에 자동 분산 | 대규모 서비스 |
# Scale-Up만 고려
spring.datasource.url=jdbc:mysql://single-db-server:3306/mydb
// 단일 DB 서버를 사용하면 트래픽 증가 시 한계에 부딪힘
# Scale-Out 적용
spring.datasource.url=jdbc:mysql://proxy-sql-server:3306/mydb
// ProxySQL을 사용하여 자동으로 Master-Slave로 분산
// Scale-Out을 고려한 구조# Sharding 적용
CREATE TABLE users_1 LIKE users; -- 샤드 1
CREATE TABLE users_2 LIKE users; -- 샤드 2
// 유저 데이터를 여러 개의 테이블로 분산하여 부하 분산
6. 스토리지 엔진 이해 못 하는 것
- 단일 엔진만으로 전체 아키텍처를 결정했다면 대부분 최적이 아님
스토리지 엔진(Storage Engine)이란?
- MySQL에서 데이터를 저장하고 관리하는 방식
- MySQL은 여러 개의 스토리지 엔진을 제공하며, 각 엔진은 성능과 기능이 다름
- 단일 엔진만 사용하면 특정 작업에서 비효율적일 수 있음
# 모든 테이블을 InnoDB로 통일
CREATE TABLE logs (
id INT AUTO_INCREMENT PRIMARY KEY,
message TEXT NOT NULL
) ENGINE=InnoDB;
# 문제점
- 로그 데이터는 읽기보다 쓰기가 많음 -> InooDB는 트랜잭션 오버헤드가 커서 비효율적
- ARCHIVE 엔진을 사용하면 더 나은 성능 가능
# 작업 별로 다른 엔진 사용 (최적화된 접근 방식)
CREATE TABLE logs (
id INT AUTO_INCREMENT PRIMARY KEY,
message TEXT NOT NULL
) ENGINE=ARCHIVE;
# 쓰기 속도가 중요한 테이블은 ARCHIVE 엔진 사용 -> INSERT 성능 향상
- 엔진 별 장단점을 공부할 것
MySQL에서 자주 사용되는 주요 스토리지 엔진 비교
| 엔진 | 특징 | 장점 | 단점 |
| InnoDB | 기본 스토리지 엔진, ACID 트랜잭션 지원 |
트랜잭션 안전성, 외래 키 지원 | 높은 쓰기 부하 시 성능 저하 |
| MyISAM | 간단한 테이블 저장 방식 | 읽기 성능 우수, 낮은 메모리 사용 | 트랜잭션 미지원, 충돌 발생 가능 |
| ARCHIVE | 대량의 데이터 저장 최적화 | 데이터 압축 지원, INSERT 속도 빠름 | UPDATE, DELETE 불가능 |
| MEMORY | 메모리에 데이터 저장 | 빠른 속도, 임시 데이터 처리 최적 | 서버 재시작 시 데이터 손실 |
| NDB (Cluster) | 분산 데이터 저장 | 높은 가용성, 자동 샤딩 지원 | 설정 복잡, 높은 메모리 요구 |
각 스토리지 엔진은 목적이 다르다.
MySQL = InnoDB 가 아니다.
- ARCHIVE : zlib으로 압축해주고 UPDATE 안 되고 로그 Bulk Insert에 유용함.
ARCHIVE 엔진이란?
- 데이터를 압축하여 저장하는 경량 엔진
- UPDATE, DELETE 불가능 -> INSERT, SELECT만 가능
- 로그, 과거 기록 저장에 최적화
ARCHIVE 엔진의 장점
- 데이터를 압축(zlib 사용)하여 저장 -> 디스크 사용량 절감
- INSERT 속도가 빠름 -> 대량 데이터 저장에 적합
적절한 ARCHIVE 엔진 사용 경우
- 로그 저장 (ex: 서버 로그, 사용자 활동 기록 등)
- 과거 데이터 저장 (ex: 1년 전 거래 내역, 주문 기록 등)
CREATE TABLE server_logs (
id INT AUTO_INCREMENT PRIMARY KEY,
log_message TEXT NOT NULL,
log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=ARCHIVE;
- MEMORY : 서버 재시작하면 증발. 인덱스가 HASH나 BTREE로 가능함. 임시, 요약 데이터에 사용.
MEMORY 엔진이란?
- 데이터를 디스크가 아닌 RAM(메모리)에 저장하는 엔진
- 빠른 읽기/쓰기 성능
- 서버가 재시작되면 모든 데이터가 사라짐
장점
- 디스크 I/O가 없기 때문에 매우 fast
- 임시 테이블, 세션 데이터 저장에 적합 (자주 변경되는 임시 데이터를 빠르게 처리할 때 사용)
단점
- 서버가 재시작되면 데이터가 사라짐 (재시작시 사라져도 괜찮은 경우에만 사용)
- 테이블 크기가 커지면 RAM 사용량 증가
CREATE TABLE session_data (
session_id VARCHAR(100) PRIMARY KEY,
user_data TEXT NOT NULL,
last_access TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=MEMORY;* 주간 top X 테이블 같은 것.
주간 TOP 10, 랭킹 시스템에 MEMORY 엔진 사용하는 이유
- 랭킹은 단기적으로 자주 변경되며, 빠른 조회가 필요함
- 매주 새로운 데이터로 교체되므로, 영구 저장이 필요 없음
- 일반적인 InnoDB보다 MEMORY 엔진이 훨씬 빠름
CREATE TABLE weekly_top_posts (
post_id INT PRIMARY KEY,
title VARCHAR(255),
likes INT,
views INT
) ENGINE=MEMORY;
# 매주 새로운 데이터로 업데이트 -> 빠른 조회 성능 필요
# 서버가 재시작되면 다시 데이터를 채우면 되므로 문제 없음
* 하여튼 메모리에 박아넣고 싶은 데이터 있으면..
MEMORY 엔진 사용의 댜표 사례..
| 사용 사례 | 설명 |
| 세션 데이터 저장 | 로그인 상태 유지 (Redis 대체 가능) |
| 임시 테이블 | JOIN 최적화 및 복잡한 연산 속도 향상 |
| 캐싱 테이블 | 자주 조회되는 데이터를 미리 저장하여 응답 속도 개선 |
| TOP 랭킹 데이터 | 일일/주간 인기 콘텐츠 랭킹 |
ex: 쇼핑몰에서 실시간 인기 상품 리스트를 MEMORY 엔진으로 관리
-> 페이지 로딩 속도 향상
'DB' 카테고리의 다른 글
| 펌)MySQL 쓰면서 하지 말아야 할 것 17가지 [13~17] With GPT (0) | 2025.02.09 |
|---|---|
| 펌)MySQL 쓰면서 하지 말아야 할 것 17가지 [7~12] With GPT (0) | 2025.02.09 |
| Mysql PK 생성전략 (+Clusted Index, PostgreSQL Serial) (0) | 2025.02.07 |
| mariadb 컨테이너 exec -it 로 접근 안될때 (0) | 2025.02.07 |
| MySQL root 비밀번호 재설정 (2) | 2024.10.23 |