Mysql PK 생성전략 (+Clusted Index, PostgreSQL Serial)

MySQL vs PostgreSQL: PK와 인덱스의 차이

** MySQL에서 AUTO_INCREMENT 는 짱짱맨이다.

• MySQL에서 AUTO_INCREMENT를 쓰면 클러스터드 인덱스가 만들어지고,
  MySQL 엔진이 알아서 순차적인 숫자를 빠르게 생성해 준다.
• 그냥 이거 하나만 써도 충분하다.
• MySQL이 최적화된 방식으로 AUTO_INCREMENT 값을 생성하니까, 성능적으로 큰 문제 없다..

그런데...

• ID 값이 단조 증가하다 보니까, 다음 ID가 예측 가능하고.
• DELETE 후에도 ID가 재사용되지 않는다. (숫자에 구멍 생김).
• 대량 INSERT 시 AUTO_INCREMENT 락이 걸릴 가능성 있고.
• 분산 시스템에서는 AUTO_INCREMENT 값이 충돌될 가능성 있다.(고 한다...)

**몇 가지 대안을 떠올려볼 수 있겠다.

---

AUTO_INCREMENT의 대안들

*UUID *

• UUID는 글로벌 유니크 ID를 만들기 위해 많이 씀.
• 분산 환경에서는 효과적이지만,
• 일반적인 단일 DB 환경에서는 굳이...? 싶음. (사실 안 써봐서 모름 근데 별로 맘에 안들게 생김)

SEQUENCE + PROCEDURE (PK를 커스텀 가능)

• MySQL에서 시퀀스를 따로 만들어서 NEXTVAL(seq) 호출하면,
  AUTO_INCREMENT랑 거의 똑같이 동작함.

• 다만... 이 방식의 강점은 ID를 커스터마이징할 수 있다는 것임

  • 예를 들어, ORD-100001, USR-200001 같은 프리픽스 포함된 ID 생성 가능함.
  • 여러 테이블에서 같은 시퀀스를 공유할 수도 있음.
  • 중앙에서 ID를 통합 관리할 수도 있음.

  하지만 성능적으로 AUTO_INCREMENT랑 차이 없음 (어차피 둘 다 클러스터드 인덱스라).

---

** 그러면 MySQL의 AUTO_INCREMENT는 개꿀인가?**

MySQL vs PostgreSQL 비교해보자!

백만 개, 천만 개 데이터가 쌓이면...
LIMIT + OFFSET으로 페이지네이션을 할 때 MySQL은 속도가 급격히 느려진다!

** MySQL의 문제점 (InnoDB 클러스터드 인덱스)**

• MySQL의 PK는 클러스터드 인덱스라서, PK 자체가 테이블 전체를 포함하고 있음.
• 즉, PK 인덱스를 탐색하는 것 자체가 테이블 전체를 읽는 것과 동일한 동작을 함.
• OFFSET이 커질수록 쿼리 속도가 미친 듯이 느려짐.
• 왜냐? 처음부터 OFFSET 개수만큼 데이터를 전부 읽고 버린 후, LIMIT 개수를 반환하기 때문!

SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 100000;

1️⃣ PK 인덱스를 탐색해서 첫 번째 행을 찾음 (O(log N))
2️⃣ 첫 번째부터 OFFSET까지 100,000개의 행을 순차적으로 읽음 (O(N))
3️⃣ 앞의 100,000개 데이터를 버리고 이후 10개만 반환 (느림...)

즉, MySQL은 OFFSET이 커지면 어쩔 수 없이 테이블을 싹 훑고 버려야 해서 성능이 나빠짐.

---

** PostgreSQL은 다름! (SERIAL 덕분에 최적화)**

• PostgreSQL에서 SERIAL을 쓰면, 별도의 시퀀스 객체가 만들어짐.
• PK 인덱스는 B-Tree 구조로 관리되며, 테이블과 독립적으로 동작함.
• 인덱스 탐색(Seek)으로 OFFSET 위치를 바로 찾아가기 때문에 MySQL보다 훨씬 빠름.

SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 100000;

1️⃣ PK 인덱스(B-Tree)에서 바로 100,000번째 위치로 점프 (O(log N))
2️⃣ 그 위치에서 10개만 읽어서 반환 (O(1))

**즉, PostgreSQL은 OFFSET까지 모든 데이터를 읽을 필요 없이 바로 찾아갈 수 있다!!!!!!!!

---

결론

• MySQL은 PK가 클러스터드 인덱스라서 OFFSET이 크면 전체 데이터를 읽어야 해서 느려짐.
• PostgreSQL은 PK가 독립적인 인덱스로 관리되고 시퀀스 객체를 활용해서 필요한 부분만 빠르게 조회 가능.
• MySQL에서 이걸 해결하려면 OFFSET을 없애고 WHERE 조건을 추가해서 커서를 활용하는 방법을 써야 함.

---

** 최종 **

• MySQL의 AUTO_INCREMENT는 최적화 잘 되어 있어서 단일 DB 환경에서는 굳이 바꿀 필요 없음.
• 다만, 대량 데이터 페이징 시 MySQL은 PK가 클러스터드 인덱스라 느려지고, PostgreSQL은 SERIAL과 인덱스 탐색을 활용해서 더 빠름.
• MySQL에서도 LIMIT OFFSET이 느릴 경우, WHERE 조건을 추가하거나 서브쿼리로 조회할 컬럼을 줄여주는 방식으로 최적화 가능.

MySQL은 AUTO_INCREMENT만 써도 충분하지만,
대량 데이터 페이지네이션에서는 PostgreSQL이 더 빠르다!
*MySQL에서도 WHERE id > last_id LIMIT 10 나 서브쿼리 써서 비스무리하게 해줄 수 있다. *

https://geronimovelasco.medium.com/how-limit-and-offset-works-behind-the-scenes-in-postgresql-a09c2c063405