MySQL 쓰면서 하지 말아야 할 것 17가지
*MySQL 쓰면서 하지 말아야 할 것 17가지* 권장사항이다. 이것을 이해하면 당신의 어플리케이션이 더 나은 성능을 발휘할 것이다. 다만 이것이 사람의 실력을 판단하는 척도로 사용되서는 안 될 것
blog.lael.be
13. Deep Scan 고려하지 않는 것
- 검색엔진 크러울러가 쓸고 지나갈 수 있다.
웹 어플리케이션에서 페이징을 사용할 때, 컴색엔진 크롤러(bot)가 모든 페이지를 크롤링할 수 있음
특히 ORDER BY + LIMIT 을 사용하는 쿼리는 성능 저하 유발할 수 있음
크롤러가 수많은 페이지를 요청하면 db는 불필요 연산 수행해야하므로 부하 심해짐;;
ex: 크롤러가 모든 페이지 크롤링하는 경우
SELECT * FROM articles ORDER BY created_at DESC LIMIT 100 OFFSET 0; -- 첫 페이지
SELECT * FROM articles ORDER BY created_at DESC LIMIT 100 OFFSET 100; -- 두 번째 페이지
SELECT * FROM articles ORDER BY created_at DESC LIMIT 100 OFFSET 200; -- 세 번째 페이지
...크롤러가 계속 요청하면 OFFSET이 증가하면서 db는 매번 전체 데이터 읽고 정렬해야함.
OFFSET 10000같은 요청 오면, 처리시간 급격히 느려질 수 있음;;;
크롤러가 싶은 페이지까지 요청하지 않도록 제한 걸거나, 더 효율적인 페이징 사용해야함..
- 이 경우 계속해서 전체 집합을 정렬한 다음 LIMIT로 가져와야 하니 무진장 느려진다.
ORDER BY + LIMIT을 함께 사용할 경우, MySQL은 먼저 전체 결과를 정렬한 후 OFFSET을 계산함.
즉, 페이지가 깊어질수록 쿼리 실행 시간이 급격히 증가함.
특히 인덱스를 활용하지 못하는 경우, MySQL이 모든 데이터를 읽고 정렬해야 하므로 성능이 매우 느려짐
ex: 성능이 느려지는 페이징 쿼리
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10 OFFSET 10000;동작 방식:
1. created_at DESC 기준으로 전체 데이터를 정렬
2. 10000개 스킵 후 10개만 가져옴
3. OFFSET이 클수록 불필요한 데이터 처리가 많아짐 -> 성능 저하
=> OFFSET 사용하지 않는 방법 고려..
- 어떻게든 집합을 작게 줄인 다음 거기서 LIMIT 걸어 가져올 것
Deep Scan을 방지하려면 OFFSET을 줄이고, 가능한 작은 데이터 집합에서 LIMIT을 적용해야 함.
특히, 페이징을 최적화하기 위해 "기본 키(또는 정렬 기준) 기반 페이징"을 사용하는 것이 좋음
ex: Key-based Pagination (효율적 페이징)
SELECT * FROM orders
WHERE created_at < '2024-02-20 00:00:00'
ORDER BY created_at DESC
LIMIT 10;이전 페이지의 마지막 created_at 값을 기준으로 데이터를 조회
OFFSET을 사용하지 않으므로 불필요한 데이터 조회를 방지
인덱스를 활용할 수 있어 성능이 훨씬 향상됨
14. InnoDB 테이블에서 WHERE 조건절 없이 SELECT COUNT(*) 하는 짓
- InnoDB 테이블에서는 조건절 없이 COUNT(*) 하는게 느리다.
InnoDB엔진은 테이블의 전체 행 수를 미리 저장하지 않음.
즉, SELECT COUNT(*) FROM table; 을 실행할 때, InnoDB는 모든 행을 직접 세어야 하므로 성능이 떨어짐.
반면, MyISAM 엔진은 테이블의 행 수를 메타데이터에 저장하므로, COUNT(*)가 즉시 반환됨.
ex: InnoDB에서 느린 COUNT 실행
SELECT COUNT(*) FROM users;InnoDB에서는 내부저긍로 모든 레코드를 읽고 개수를 계산
테이블 크기가 클수록 실행 속도가 급격히 느려짐
ex: MyISAM에서 빠른 COUNT 실행
ALTER TABLE users ENGINE = MyISAM;
SELECT COUNT(*) FROM users;MyISAM에서는 테이블의 행 수를 저장해두므로 바로 결과 반환.
*(MyISAM을 사용하면 빠르지만, InnoDB의 트랜잭션 기능을 포기해야 하므로 적절한 대안을 고려해야 함.)
- 각 레코드의 transaction isolation을 유지하는 MVCC 구현이 복잡해서 그렇다는..
InnoDB는 MVCC(Multi-Version Concurrency Control)을 사용하여 동시성을 관리함.
즉, 트랜잭션이 실행 중이라면 다른 트랜잭션에서는 볼 수 없는 행이 존재할 수도 있음.
이 때문에 MySQL이 단순히 미리 저장된 카운트를 반환할 수 없고, 항상 모든 행을 직접 확인해야 함.
ex: MVCC로 인해 COUNT 성능이 저하되는 경우
START TRANSACTION;
DELETE FROM users WHERE id = 1;
SELECT COUNT(*) FROM users; -- 여전히 삭제된 row가 보일 수도 있음
COMMIT;한 트랜잭션에서 데이터를 삭제해도, 다른 트랜잭션에서는 여전히 해당 데이터가 보일 수 있음
이 떄문에 COUNT(*)를 실행할 때 항상 최신 데이터를 직접 계산해야 함.
=> MVCC를 이해하고, COUNT 최적화 방법을 고려해야 함.
*MBCC란?
InnoDB에서 트랜잭션이 동시에 실행될 떄 데이터 일관성을 유지하는 방법
각 트랜잭션이 "자신만의 스냅샷(버전)"을 보고 작업할 수 있도록 하는 메커니즘.
즉, 하나의 row에 대해 여러 version이 존재할 수 있음.
InnoDB에서 MVCC가 COUNT(*)를 느리게 만드는 이유
- 트랜잭션이 실행 중일 때, 다른 트랜잭션의 변경을 즉시 반영할 수 없음.
MVCC는 각 트랜잭션이 시작된 시점의 "일관된 데이터 스냅샷"을 유지하도록 보장함.
즉, 한 트랜잭션에서 COUNT(*)를 실행하면, 현재 트랜잭션이 볼 수 있는 데이터만 기준으로 개수를 계산해야 함.
따라서 단순히 "테이블 전체 행 개수"를 반환할 수 없음 -> 항상 직접 모든 행을 세야 함.
ex: 두 개의 트랜잭션에서 서로 다른 COUNT 결과를 볼 수 있음.
-- 트랜잭션 1: 데이터 조회
START TRANSACTION;
SELECT COUNT(*) FROM users; -- 예를 들어 1000개가 반환됨
이때, 다른 트랜잭션에서 새로운 데이터를 삽입함
-- 트랜잭션 2: 새로운 데이터 삽입
INSERT INTO users (id, name) VALUES (1001, 'Alice');
COMMIT;
트랜잭션 1에서 다시 COUNT 실행
SELECT COUNT(*) FROM users; -- 여전히 1000개가 반환됨 (새로운 데이터 반영 X)트랜잭션 1은 자신이 시작한 시점의 데이터만 볼 수 있음 -> 새로운 INSERT는 무시됨.
즉, 각 트랜잭션마다 서로 다른 데이터 버전을 보고 있음.
이러한 이유로 MySQL은 단순히 테이블의 총 행 수를 저장해두고 반환할 수 없음.
항상 현재 트랜잭션이 볼 수 있는 행을 직접 세어야함 --> COUNT(*)가 느려짐!
MVCC는 어떻게 구현될까?
InnoDB는 "삭제된 행"도 바로 삭제하지 않음
MVCC에서는 DELETE가 실행되더라도, 기존 데이터가 바로 제거되지 않음
대신 "삭제 플래그"를 설정하고, 나중에 "진짜 삭제"를 수행함 (= Purge Thread).
즉, MySQL이 COUNT(*)를 실행할 때 삭제된 행도 포함할 수 있음 --> 모든 행을 직접 조회해야 함.
==> MVCC를 유지하려면 직접 모든 데이터를 읽고 확인할 수 밖에 없음..
MyISAM과 InnoDB의 COUNT(*) 차이점
| 스토리지 엔진 | COUNT(*) 동작 방식 | 성능 |
| MyISAM | 테이블의 총 행 수를 메타데이터에 저장하여 즉시 반환 | 매우 빠름 |
| InnoDB | MVCC로 인해 모든 행을 직접 읽고 계산해야 함. | 느림 (특히 대형 테이블) |
- 트리거 걸어서 메모리 스토리지 엔진 쓰는 테이블에 통계를 별도로 유지하면 된다.
COUNT(*)가 느리다면, 트리거를 사용하여 행 개수를 별도로 유지하면 된다.
InnoDB 테이블의 전체 행 수를 저장하는 별도 테이블을 만들고, 트리거를 활용하여 값이 자동 갱신되도록 설정
MEMEORY 스토리지 엔진 사용하면 COUNT(*) 조회 성능이 향상됨.
ex: COUNT 값을 저장하는 테이블 생성
CREATE TABLE user_count (
table_name VARCHAR(50) PRIMARY KEY,
row_count INT NOT NULL
) ENGINE = MEMORY;MEMORY 엔진을 사용하여 빠르게 카운트 값을 저장함.
ex: 트리거를 사용하여 행 개수 자동 갱신
DELIMITER //
CREATE TRIGGER after_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
UPDATE user_count SET row_count = row_count + 1 WHERE table_name = 'users';
END //
CREATE TRIGGER after_user_delete
AFTER DELETE ON users
FOR EACH ROW
BEGIN
UPDATE user_count SET row_count = row_count - 1 WHERE table_name = 'users';
END //
DELIMITER ;user 테이블에 데이터가 삽입될 때마다 user_count 테이블의 값을 증가시킴
데이터가 삭제되면 user_count 값을 감소시킴
ex: 빠르게 COUNT 조회
SELECT row_count FROM user_count WHERE table_name = 'users';즉시 결과 반환 가능 (COUNT(*)보다 훨씬 빠름).
해결책 => 트리거와 MEMORY 테이블을 활용하면 COUNT(*) 성능 개선 할 수 있다!
(MVCC의 트랜잭션 격리 보장으로 인한 늦은 COUNT(*)는 메모리 테이블을 활용하거나 트리거를 사용해)
15. 프로파일링이나 벤치마킹 안 하는 것
- 프로파일링 : 병목 찾아내기
프로파일링(Profiling): 어디서 성능 병목이 발생하는지 찾아내는 과정
- 성능이 느려지는 원인을 분석하여 최적화할 수 있음.
- 데이터베이스에서는 쿼리 실행 시간, CPU 사용량, DISK IO, 메모리 사용량등을 분석하는 것이 중요.
ex: 쿼리 프로파일링 활성화
SET profiling = 1; -- 프로파일링 활성화
SELECT * FROM orders WHERE total_price > 1000;
SHOW PROFILES; -- 실행된 쿼리 목록 및 시간 출력
SHOW PROFILE FOR QUERY 1; -- 특정 쿼리의 상세 프로파일 출력SHOW PROFILES --> 실행된 쿼리의 수행시간 표시
SHOW PROFILE FOR QUERY 1 --> 특정 쿼리의 실행 단계별 리소스 사용량 분석
프로파일링을 통해 특정 쿼리가 CPU, DISK IO 등에서 병목이 발생하는지 확인 가능
- 벤치마킹 : 시간에 따른 성능 변화 추이 평가, 부하 견딜 수 있는지 테스트
벤치마킹을 통해 서버가 특정 부하를 견딜 수 있는지 미리 테스트 가능
벤치마킹(Benchmarking): 서버가 특정 부하를 견딜 수 있는지, 성능이 어떻게 변하는지 측정하는 과정
시간이 지남에 따라 성능이 저하되는지 확인하고, 동시 접속자 증가 시 쿼리 성능이 어떻게 변하는지 테스트
ex: mysqlslap을 이용한 벤치마킹
mysqlslap --user=root --password=1234 --concurrency=10,50,100 --iterations=5 --query="SELECT COUNT(*) FROM orders;" --create-schema=test_db --verbose--concurrentcy = 10, 50, 100 --> 동시에 10, 50, 100개의 연결을 실행
--iterations=5 --> 5번 반복하여 평균값 계산
결과를 보고 특정 동시 접속자 수에서 성능 저하가 있는지 확인 가능
- 프로파일링 할 때는 실제 데이터를 옮겨와서 할 것
- 테스트 환경에서는 데이터가 적어서 빠르게 실행될 수 있음.
- 하지만 운영 환경에서는 데이터 양이 많아 쿼리 성능이 다르게 나올 수 있음.
- 따라서 실제 데이터를 테스트 환경으로 가져와서 프로파일링을 수행해야 함.
운영 환경과 동일한 데이터로 테스트해야 정확한 성능을 분석할 수 있음
- 어디가 병목이냐~ Memory? Disk I/O? CPU? Network I/O? OS?
성능 저하의 원인은 여러가지가 있을 수 있음.
병목이 메모리 부족인지, 디스크 IO 문제인지, CPU 사용량이 높은지, 네트워크 문제인지를 파악해야함.
이를 위해 시스템 모니터링 도구와 MySQL 프로파일링 도구를 활용해야 함.
ex: SHOW STATUS로 병목 확인
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads'; -- 메모리 캐시 적중률 확인
SHOW GLOBAL STATUS LIKE 'Slow_queries'; -- 느린 쿼리 실행 횟수 확인
SHOW GLOBAL STATUS LIKE 'Threads_running'; -- 현재 실행 중인 스레드 수 확인어떤 리소스가 병목인지 확인한 후, 해당 부분을 최적화해야 함.
- 느린 쿼리 로그로 남기기
* log_slow_queries=/path/to/log
* log_queries_not_using_indexes
MySQL은 느린 쿼리를 자동으로 기록하여 성능 분석을 쉽게 할 수 있도록 지원함.
특정 기준(예: 실행 시간 1초 이상) 이상 걸리는 쿼리를 로그로 남겨서 분석 가능.
ex: 설정 방법 (MySQL 설정파일 my.cnf)
slow_query_log = 1
slow_query_log_file = /var/log/mysql_slow.log
long_query_time = 1 # 1초 이상 걸리는 쿼리만 로그에 기록
log_queries_not_using_indexes = 1 # 인덱스를 사용하지 않는 쿼리도 기록long_query_time = 1 --> 1초 이상 실행된 쿼리를 기록
log_queres_not_using_indexes = 1 --> 인덱스를 사용하지 않은 쿼리도 로그에 저장
느린 쿼리 로그를 분석하면 병목이 되는 쿼리를 쉽게 찾을 수 있음
- 벤치마킹 시에는 다 고정시키고 변수 하나만 바꿔가면서 해야 함. (쿼리 캐시는 끌 것.)
벤치마킹을 할 때는 다른 변수의 영향을 최소화해야 함.
특히 쿼리 캐시(Query Cache)가 활성화되어 있으면 반복 실행 시 부정확한 결과가 나올 수 있음.
따라서 벤치마킹 전에 쿼리 캐시를 비활성화해야 함.
SET SESSION query_cache_type = OFF;
RESET QUERY CACHE;테스트 환경을 일정하게 유지하고, 하나의 변수만 변경하면서 테스트해야 정확한 결과를 얻을 수 있음.
- 도구를 써라~~
* EXPLAIN
* SHOW PROFILE
* MyTop/innotop
* mysqlslap
* MyBench
* ApacheBench (ab)
* super-smack
* SysBench
* JMeter/Ant
* Slow Query Log
| 도구 | 설명 |
| EXPLAIN | 쿼리 실행 계획 분석 |
| SHOW PROFILE | 실행된 쿼리의 리소스 사용량 분석 |
| MyTOP/inootop | MySQL 실시간 성능 모니터링 |
| mysqlslap | MySQL 벤치마킹 테스트 도구 |
| MyBench | MySQL 성능 측정 및 부하 테스트 |
| ApachBench (ab) | 웹 서버 및 데이터베이스 부하 테스트 |
| super-smack | 대량의 데이터베이스 요청을 시뮬레이션 하는 도구 |
| SysBench | 데이터베이스 및 시스템 성능 테스트 |
| JMeter/Ant | 웹 어플리케이션 및 데이터베이스 성능 테스트 |
| Slow Query Log | 느린 쿼리 로그 분석 |
16. AUTO_INCREMENT 안 쓰는 것
- PK를 AUTO_INCREMENT로 쓰는건 무진장 최적화 되어 있음
* 고속 병행 INSERT 가능
* 잠금 안 걸리고 읽으면서 계속 할 수 있다는!
* 디스크와 페이지 단편화를 줄임
* 메모리와 디스크의 핫 스팟을 생성하여 스와핑을 줄임
1. InnoDB에서 AUTO_INCREMENT는 항상 "끝부분"에 INSERT됨
InnoDB에서 PK는 클러스터링 인덱스(Clusterd Index)로 동작함
즉, PK 자체가 데이터를 저장하는 방식
AUTO_INCREMENT는 항상 가장 마지막 값에 INSERT 되므로, 새로운 레코드는 연속적인 페이지에 저장됨
이렇게 되면 데이터를 빠르게 추가할 수 있고, 디스크 IO를 최소화할 수 있음
ex: AUTO_INCREMENT로 PK사용
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
email VARCHAR(100)
) ENGINE=InnoDB;id가 AUTO_INCREMENT이므로 새로운 레코드가 항상 "테이블 끝"에 삽입됨.
INSERT가 연속된 공간에 배치되므로, 디스크 단편화(쓰기 비용 증가)가 발생하지 않음.
2. 고속 병행 INSERT 가능 (INSERT 성능이 빠름)
InnoDB는 B+Tree 구조로 데이터를 저장함
AUTO_INCREMENT를 사용하면 새로운 레코드는 항상 "맨 마지막 리프 노드"에 삽입됨.
즉, 트리의 중간을 수정하지 않고, 단순히 마지막에 추가만 하면 됨.
반대로, 랜덤 PK를 사용할 경우?
CREATE TABLE users (
id CHAR(36) PRIMARY KEY, -- UUID (랜덤 값)
name VARCHAR(50),
email VARCHAR(100)
) ENGINE=InnoDB;UUID 같은 랜덤 값은 중간에 삽입될 수도 있음 --> B+Tree가 자주 재정렬됨.
새로운 데이터가 트리의 중간이나 앞부분에도 삽입될 수 있어 페이지 분할(Split)이 발생함.
이로 인해 INSERT 속도가 느려지고, 디스크 IO 비용이 증가함.
3. 잠금 없이 읽으면서 INSERT 가능 (InnoDB insert Buffer 활용)
InnoDB는 INSERT Buffer(버퍼 풀)을 활용하여 INSERT 성능을 최적화함.
AUTO_INCREMENT는 항상 마지막에 데이터를 삽입하므로,
다른 트랜잭션이 읽는 중에도 INSERT가 가능함.
즉, Read-Write 충돌이 적어서 성능이 좋아짐.
반면, 랜덤한 PK를 사용하면 트랜잭션간 충돌이 발생할 가능성이 높아짐.
ex: AUTO_INCREMENT와 SELECT의 동시 실행
START TRANSACTION;
SELECT * FROM users WHERE id < 1000;
-- 다른 트랜잭션에서 INSERT 실행 가능
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
COMMIT;AUTO_INCREMENT는 PK 순서가 "연속적"이라, SELECT와 INSERT가 충돌하지 않고 동시 실행될 가능성이 높음.
랜덤한 PK를 사용하면 SELECT 시 B+Tree의 중간을 읽어야 하므로, INSERT와 충돌할 가능성이 높음.
4. 새 레코드를 근처에 놓음으로써 디스크와 페이지 단편화를 줄임
InnoDB의 페이지 크기는 16KB.
AUTO_INCREMENT는 새로운 데이터가 항상 "같은 페이지"에 연속적으로 추가 됨.
즉, 페이지 단편화(Fragementation)가 거의 발생하지 않음.
반면, 랜덤한 PK(UUID, 해시값 등)은 데이터가 여기저기 흩어지므로, 디스크 단편화가 심해짐.
id=1 → 페이지 1에 저장
id=2 → 페이지 1에 저장
id=3 → 페이지 1에 저장
id=4 → 페이지 1에 저장 (단편화 없음)
...
id=10000 → 페이지 20에 저장 (연속된 순서)
5. 메모리와 디스크에 핫 스팟을 생성하고 스와핑을 줄임
"핫 스팟(Hot Spot)": 특정 데이터가 자주 접근되는 부분
InnoDB의 버퍼 풀(Buffer Pool)은 자주 사용되는 데이터를 메모리에 캐싱
AUTO_INCREMENT는 새로운 데이터가 항상 "같은 위치"에 저장되므로, 버퍼 풀 캐시에 최적화됨
반면, 랜덤한 PK는 데이터가 여기저기 흩어져 있어서, 캐시 효율이 떨어지고 스와핑이 많아짐.
ex: AUTO_INCREMENT vs. 랜덤 PK의 캐시 효율 비교
| PK 타입 | 메모리 캐시 효율 | 디스크 스와핑 발생 가능성 |
| AUTO_INCREMENT | 매우 높음 | 적음 (디스크 IO 최소화) |
| 랜덤 PK (UUID) | 낮음 | 많음 (캐시 미스 발생) |
AUTO_INCREMENT는 항상 "같은 위치"에 데이터를 추가 하므로, 버퍼 풀을 효과적으로 활용할 수 있음.
랜덤한 PK는 여기저기 흩어져 있어서, 버퍼 풀 캐시 효율이 떨어지고, 스와핑이 발생할 가능성이 높음
∴
INSERT 속도가 빠름 --> 항상 "끝"에 추가되므로 B+Tree 재구성이 적음
SELECT와 충돌이 적음 --> PK가 연속적이므로 트랜잭션 간 충돌이 적음
디스크 단편화 최소화 --> 데이터가 연속적으로 저장됨
메모리 캐시 효율이 높음 --> 버퍼 풀을 최적화하여 디스크 IO를 줄일 수 있음
랜덤 PK(UUID, 해시)보다 훨씬 빠름 --> B+Tree의 균형을 유지하는 비용이 낮음
- 새 레코드를 근처에 놓음으로써 디스크와 페이지 단편화를 줄임
- 메모리와 디스크에 핫 스팟을 생성하고 스와핑을 줄임
ㅇㅇ
17. ON DUPLICATE KEY UPDATE를 안 쓰는 것
ON DUPLICATE KEY UPDATE를 사용하면 기존 데이터가 존재할 경우 UPDATE하고, 없으면 INSERT하는 로직을 단순화할 수 ㅣㅇㅆ음.
- 불필요한 SELECT로 인한 서버 부하 감소
- INSERT 후 UPDATE 수행 방식보다 5~6% 빠름
- 대량의 데이터 입력 시 성능 차이가 더 커짐
- MySQL 5.7.21 이전 버전에서는 데드락 발생 가능성이 있음.
- 레코드가 있으면 업데이트하고 없으면 인서트하고 이런 코드 필요없다!! 다 날려버려라!!
보통 INSERT하기 전에 레코드가 존재하는지 확인하는 SELECT 쿼리를 실행함.
존재하면 UPDATE, 존재하지 안으면 INSERT
하지만 SELECT --> INSERT 또는 UPDATE 방식은 서버와의 불필요한 왕복 요청이 발생하여 성능 저하가 있음
ex: 기존 방식 (비효율적)
-- 먼저 데이터가 있는지 확인
SELECT COUNT(*) FROM users WHERE id = 1;
-- 있으면 업데이트
UPDATE users SET name = 'Alice' WHERE id = 1;
-- 없으면 삽입
INSERT INTO users (id, name) VALUES (1, 'Alice');- 서버와 불필요하게 왔다 갔다 해야 함. --> 트래픽 증가
- SELECT를 먼저 실행해야 하므로 성능 저하 발생
- INSERT 후 UPDATE 수행 방식보다 5~6% 느림
이 문제를 해결하는 것이 ON DUPLICATE KEY UPDATE !
- 서버에 불필요하게 왔다갔다 할 필요가 없어짐
레코드가 존재하면 UPDATE, 존재하지 않으면 INSERT를 한 번에 처리
즉, 불필요한 SELECT를 제거하여 서버 부하를 줄이고 성능을 향상시킴.
MySQL의 PK 또는 UK를 기반으로 작동함.
ex:
INSERT INTO users (id, name)
VALUES (1, 'Alice')
ON DUPLICATE KEY UPDATE name = 'Alice';동작 방식
1. id =1 이 존재하면 name을 "Alice"로 업데이트
2. 존재하지 않으면 새로운 레코드를 삽입
서버와의 불필요한 왕복을 제거하여 성능 최적화 !
- 5-6% 정도 빠름
ex: 기존 방식(SELECT -> INSERT or UPDATE) vs. ON DUPLICATE KEY UPDATE 성능 비교
| 방식 | 서버 요청 횟수 | 성능 |
| 기존 방식 (SELECT -> INSERT or UPDATE) | 2번 요청 (SELECT + INSERT/UPDATE) | 느림 |
| ON DUPLICATE KEY UPDATE | 1번 요청 (INSERT or UPDATE) | 5~6% 더 빠름 |
SELECT를 먼저 실행할 필요가 없음 --> 서버와의 왕복 감소
UPDATE/INSERT가 내부적으로 최적화되어 있음
대량의 데이터 입력 시 효과가 커짐
∴ 불필요한 SELECT를 제거해서 성능을 개선한다는 것임.
- 데이터 입력이 많다면 더 커질 수 있음
대량의 데이터를 처리할 때, 기존 방식은 SELECT로 인해 서버 부하가 급격히 증가
ON DUPLICATE KEY UPDATE는 INSERT를 시도한 후 충돌이 발생하면 바로 UPDATE하여 성능 최적화 가능.
ex: 대량 데이터 삽입 시 기존 방식과 성능 비교
-- 기존 방식 (SELECT 후 INSERT or UPDATE)
SELECT * FROM products WHERE product_id = 1;
UPDATE products SET stock = stock + 10 WHERE product_id = 1;
INSERT INTO products (product_id, stock) VALUES (1, 10);-- ON DUPLICATE KEY UPDATE 방식
INSERT INTO products (product_id, stock)
VALUES (1, 10)
ON DUPLICATE KEY UPDATE stock = stock + 10;트래픽 많은 환경에서 성능 차이가 더 커짐!
on duplicate key update는 mysql5.7.21이전 버전에서는 데드락을 발생시킬수도 있음.
5.7.21 이전 버전에서는 ON DUPLICATE KEY UPDATE가 데드락(Deadlock)을 유발할 수도 있음.
원인
- MySQL이 INSERT시 AUTO_INCREMENT 값을 증가시킨 후 충돌을 감지하고 UPDATE를 수행하는데,
- 동시에 여러 트랜잭션이 실행되면 데드락이 발생할 가능성이 있음.
ex: 데드락 발생 가능성 (5.7.21 이전 버전)
INSERT INTO orders (id, quantity)
VALUES (1, 10)
ON DUPLICATE KEY UPDATE quantity = quantity + 10;해결법 : 5.7.21 이상 버전 사용하거나, INSERT 대신 REPLACE INTO 사용 고려
기존 데이터를 삭제하고 새로 삽입해야 하는 경우 REPLACE INTO 고려할 수 있음 .
ex: ON DUPLICATE KEY UPDATE vs. REPLACE INTO 차이
| 방식 | 존재하면 업데이트 | 존재하면 삭제 후 다시 삽입 |
| ON DUPLICATE KEY UPDATE | 업데이트만 수행 | 기존 데이터 유지 |
| REPLACE INTO | 기존 데이터 삭제 | 새 데이터 삽입 |
기존 데이터가 있으면 삭제 후 새로운 데이터 삽입 (주의 : AUTO_INCREMENT 증가할 수 있음)
'DB' 카테고리의 다른 글
| 펌)MySQL 쓰면서 하지 말아야 할 것 17가지 [7~12] With GPT (0) | 2025.02.09 |
|---|---|
| 펌)MySQL 쓰면서 하지 말아야 할 것 17가지 [1~6] With Claude (1) | 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 |