'🗂 데이터베이스' 카테고리의 글 목록 (2 Page)

🗂 데이터베이스

[토스 SLASH 21] MYSQL HA & DR Topology 2023.05.18
Ubuntu에서 PostgreSQL 외부 접속 허용하기 2023.04.03
[교육] 실전! 데이터모델링 2023.03.17 2
클러스터 테이블과 논클러스터 테이블 2023.03.10
본질식별자와 인조식별자 2023.03.10

[토스 SLASH 21] MYSQL HA & DR Topology

미인주 2023. 5. 18. 09:54

2023. 5. 18. 09:54

✅ 발표 영상

https://www.youtube.com/watch?v=t96l6ry_qmw

✔️ 발표 내용

MMM

HA 오픈소스 솔루션 이름

토스의 Live MySQL 데이터베이스 HA 솔루션으로 사용되고 있음

Google에서 개발된 솔루션

버전 업데이트는 중지되어서, 필요 시 자체 업데이트를 하면서 사용 중

기본 구성도

MASTER-MASTER 01, 02노드만 MMM이 관리하도록 구성

03번 SLAVE도 MMM이 관리하도록 포함시킬 수 있지만, 신규 MASTER로 failover 하는 과정에서 대응되지 않는 케이스가 있어서

이 구성은 사용하지 않음

1. 데몬 2개 : Monitoring, Agent

2. 애플리케이션 서버 failover를 위해 각 데이터베이스 서버에 올라가는 서비스 IP

Monitoring 데몬
- 별도의 호스트에서 모든 모니터링 및 의사 결정과 명령 수행

Agent 데몬
- 각 데이터베이스 서버에서 실행됨
- Monitoring 데몬에서 내려온 명령 수행

Service IP

MMM이 데이터베이스 Role 변경에 따라 이동시켜 애플리케이션 서버의 failover가 이루어지게 함

mond가 하는 주요 모니터링 항목

ping - 호스트 자체가 살아있는지
mysql - MySQL 인스턴스가 살아있는지
rep_threads - 복제 스레드가 정상 작동 중인지
rep_backlog - 복제 스레드의 지연이 정해진 threshold를 넘어섰는지 확인

이 모니터링 항목에서 이상이 있을 시, MMM이 standby MASTER로 failover 진행

DR 처음 구성했을 때 모습

active : DATA-CENTER1

standby : DATA-CENTER2

단방향 복제 구성 : DATA-CENTER 간, standby DATA-CENTER 내의 local MASTER 간

-> IDC failover 시 Role 전환 작업 필요..

BEFORE

신규 active DATA-CENTER의 MySQL 서버를 MASTER-MASTER 구성으로 전환해야 함

DATA-CENTER 간 MySQL 서버의 MASTER-SLAVE 관계도 전환해주어야 함

신규 standby DATA-CENTER의 MySQL 서버는 MASTER-SLAVE 단방향으로 전환해야 함

-> 어려운 작업은 아니지만 번거롭고 부담스러운 작업들 -> 개선 필요!

전환하게 된 배경

DR센터의 본 목적

재난 시 비즈니스 연속성 보장이므로

데이터 센터 failover를 할 경우가 거의 발생하지 않을 거라고 생각했지만

토스의 간편결제 파트너사 증가 -> 무중단 운영의 필요성 증가

이런 상황에서 대규모 네트워크 작업..

-> 무중단 데이터 센터 롤링 작업으로 계획이 됨

-> 데이터베이스 서버도 데이터 센터 failover가 필요해짐

-> 진행하다 보니 데이터베이스 Role 전환 작업에 대한 부담 증가 + 실수로 복제가 깨지는 경우도 발생 + 다운타임 필요 + 위험부담 큰 작업도 발생

-> 데이터 센터 전환 과정을 최대한 간소화시킬 필요가 생김

WISH

1. 양쪽 데이터센터 -> 대칭 구조

2. 데이터센터 failover 시, 애플리케이션 서버 커넥션만 이동하고

3. 데이터베이스 레벨에서는 따로 작업할 것이 없는 구조

MASTER-MASTER 양방향으로 바뀔 부분

1. 기존에 단방향으로 있던 DR 복제 채널

2. DR 센터 내에 MASTER-SLAVE 복재 채널

but,, 두 가지 이슈 발생

1. binlog 중복 전송 이슈

01, 02번 -> log_slave_update 옵션을 켜고 운영

sql_thread가 적용한 트랜잭션도 binlog에 기록하는 옵션

1. actvie 데이터센터 01번에서 발생한 트랜잭션의 binlog 생성

2. DR 채널을 따라 standby 데이터센터 01번으로 전송 및 적용됨

3. 적용 이후, standby 데이터센터 01번 서버 -> 이 트랜잭션에 대한 binlog 생성

4. 이때 01번의 slave로 있는 02, 03번 -> 이 binlog를 복제하고 적용하게 됨

단방향 구성 시 binlog 복제 흐름과 동일함

5. 그런데 02번에서 최종 적용한 binlog가 자체 binlog로 생성이 되고,

6. 01번은 02번의 master면서 slave이기 때문에, 이 binlog를 복제해야 하는지 판단함

이 판단 -> 해당 binlog가 처음 생성된 서버의 server-id와 자신의 server-id가 다른지 비교해서 이루어짐

다를 경우 복제 진행

이 binlog는 데이터센터1에서 처음 생성되었기 때문에 server-id 101을 계속 유지하고 있는 상황

server-id 1001인 01번으로 다시 복제가 되어버림

여기서 적용이 되면 다시 또 03, 02번으로 복제 되는 상황이 생길 수 있음..

실제 값 변경이 없었다면 binlog가 생성되지 않는데,

동일 binlog가 적용된 지 1초도 지나지 않은 상황이기 때문에

4번의 상황까지 나올 확률은 적지만

이론적으로는 무한 루프가 가능해져버림

2. MMM이 복제 채널을 구분하지 못하는 이슈

각 데이터센터의 01번 서버는 DR 양방향 복제 채널이 추가됨으로 인해서

DR 복제 채널과 local master-master 복제 채널이 동시에 생성된 멀티소스복제 상태가 됨

이 경우 MMM은 자신이 모니터링 해야 할 채널이 어떤 채널인지 알지 못하게 됨

1. binlog 무한 루프

방법 1) Gtid로 중복 적용 막기

but,, 당시 바로 Gtid 전환은 어려운 상황

Gtid로 가더라도 binlog의 전송 루프 (No I0 Thread skip)은 발생하게 되고

다시 binlog position 방식으로 전환 시 이슈가 되기 때문에.. 중복 전송 자체를 막을 방법이 필요

방법 2) binlog filter 걸기

2. 어떤 채널?

MMM이 MM 채널에 대해서만 모니터링을 하도록 소스를 수정

현재 DR 구성 모습

#devspace 미인주 님의 블로그입니다.

Ubuntu에서 PostgreSQL 외부 접속 허용하기

미인주 2023. 4. 3. 12:11

2023. 4. 3. 12:11

1. 상황

Ubuntu 인스턴스에 CentOS 도커 컨테이너를 띄우고, 그 안에 PostgreSQL를 설치한 상태

그림으로 나타내보자면 아래와 같다.

내가 하고 싶은 것은

우분투에서 "CentOS에 설치된 PostgreSQL"에 접속해서 데이터를 추출해 MySQL(개발 DB)로 보내는 것!

그냥 접속하려고 했더니 계속 connection 에러가 나서 찾아보니..

이런 경우는 PostgreSQL에서 외부 접속을 허용해주어야 하고,

그러려면 바꿔야 할 설정과 설치해야 할 패키지들이 있어서 과정을 정리해보고자 한다 :)

2. 과정

0. 필요 패키지 설치

# Ubuntu에
sudo apt-get install -y postgresql-client
sudo apt-get install -y postgresql-client-common

# CentOS에
yum install -y firewalld net-tools

1. PostgreSQL이 CentOS 컨테이너의 네트워크 인터페이스에서 수신 대기 중인지 확인 - CentOS 컨테이너에서 실행

netstat -nlt | grep 5432

PostgreSQL이 0.0.0.0:5432에서 수신 대기 중이면 모든 IP 주소에서 연결을 수락할 준비가 되었음을 의미한다.

127.0.0.1:5432에서 수신하는 경우 외부 연결을 허용하도록 PostgreSQL 구성 파일을 수정해야 한다.

2. CentOS 컨테이너의 방화벽에서 PostgreSQL 포트(5432) 열기 - CentOS 컨테이너에서 실행

firewall-cmd --zone=public --add-port=5432/tcp --permanent
firewall-cmd --reload

3. /var/lib/pgsql/{version}/data/pg_hba.conf 수정 - CentOS 컨테이너에서 실행

host    all             all             {인스턴스 퍼블릭 IP}/22     md5

우분투에서 CentOS에 있는 PostgreSQL로 접속하려는 것이기 때문에 우분투 IP를 허용해주어야 한다.

pg_hba.conf 가장 하단에 있는 허용 목록에 우분투 IP를 host로 추가해준다.

4. /var/lib/pgsql/{version}/data/postgresql.conf 수정 - CentOS 컨테이너에서 실행

#listen_addresses = 'localhost' 를 아래로 변경
listen_addresses = '*'

5. PostgreSQL 재시작 - CentOS 컨테이너에서 실행

sudo systemctl restart postgresql-{version}

6. 접속 - Ubuntu 인스턴스에서 실행

psql -h {인스턴스 퍼블릭 IP} -U postgres

접속에 성공한 것을 확인할 수 있다~!!

#devspace 미인주 님의 블로그입니다.

[교육] 실전! 데이터모델링

미인주 2023. 3. 17. 10:49

2023. 3. 17. 10:49

팀 수석님께서 데이터 모델링을 주제로 사내교육을 진행하셔서 참여할 수 있는 기회가 생겼다. 참여형 실습도 있고 완전 유익하고 재밌는 교육이라고 하셔서 기대를 잔뜩 하고 갔다.

학교에서 데이터베이스 설계 수업 때 배운 내용도 새록새록 생각이 나고 최근에 계속 모델링 작업을 하고 있어서 내용이 더 잘 들리기도 하고, 현업 포인트까지 알 수 있는 값진 시간이었다.

다른 부서분들을 만나는 자체도 재밌었는데 다들 잘 참여해주시고, 얘기도 잘 통해서 즐거운 시간이었다. 특히 실습에서 작은 모델들이 모여서 마지막에 하나의 모델로 탄생할 때 감동이었다..🥺 (수석님 존경합니다..)

데이터 표준화의 기본

데이터가 중요하다!

비즈니스의 목적이 바뀌지 않는 한, 중요 데이터는 변하지 않는다.
데이터 구조를 만드는게 개발의 도구일까?
데이터가 목적일 수 있다. 비즈니스 데이터가 그만큼 중요하다.
모델링을 할 때 어떤 점을 주안점으로 둘까?
- 전략과 프로세스가 바뀌어도 데이터는 변하지 않는다
- 데이터만 봐도 업무 규칙을 알 수 있게
  예) 고객 상품 주문 결제 계약 -> 프로세스나 툴이 바뀌어도 모델 구조가 바뀌지 않게 -> 독립적이고 안정적이게

데이터 모델링의 목적

일관성, 무결성, 정확성이 보장된 신뢰할 수 있는 고품질 데이터
가치 있는 정보, 최상의 성능
- 데이터 규칙에 대한 명시적인 의사소통의 도구
  예) 만약 내 집을 짓는다면...? 정확한 제대로 된 설계 도면 인력 물량 공사진행 시공

데이터 모델링의 절차

개념

논리 모델링(비즈니스 중심)
물리 모델링(성능 중심) : DBMS 특성을 고려해서 성능, 주요 인덱스 및 파티션 설계
DB구축 : DDL 작성, 물리 DB 구축, 마이그레이션

데이터 표준 (용어, 도메인, 코드)

데이터운영팀에서 검수 중인 것

논리와 물리 모델링의 결과에 표준화를 적용 -> DA (사람과 가깝고 좀 더 논리적인 부분)
인덱스 설계, 파티션 설계 ~ DB 구축 -> DBA (DBMS와 가까운 사람들, 좀 더 물리적인 부분)

논리 모델링의 기본

검수 시 가장 말씀 드리는 3가지

시간적으로 달라지는 절차적 사고보다는 집합적 사고
중복과 불일치는 NO
애매함 < 명확함

논리 모델의 구성 요소 - 1

엔터티 : 업무가 다루는 어떤 것
관계 : 업무가 다루는 것들 사이에 존재하는 연관
속성 : 각 사물(엔터티)이 가지고 있는 상세한 특성

논리 모델의 구성 요소 - 2

예) 은행
- 엔터티 : 고객 계약 상품 직원
- 관계 : 계약의 주체, 계약의 대상, 상품담당자, ...
- 속성 : 세부 속성들이 필요하고

논리 모델의 구성 요소 - 3

ERD를 그린다면
- 체크리스트를 확인해야 함
  예) 엔터티 : 서비스에서 관리할 필요가 있나? 관리하려는 데이터의 범위는 어디까지? 엔터티인지 속성인지 관계인지

논리 모델링의 기본

ERD 표기법
- 사내에서 사용하는 툴 : PowerDesigner (IE법 사용, ER-Win도 있음)
- Barker법 툴 : DA# (집합 개념을 조금 더 나타낸다는 점)

관계의 표현 - 1

카디널리티(1:1, 1:다, ...), 식별성(식별자 상속, 일반속성 상속), 선택성(0개 nullable 설정)
계층, 순환, BOM / Barker법 : 배타관계

관계의 표현 - 2

부서와 사원 관계의 예시
- 식별 상속 : 한 명의 사원은 여러 부서에 소속될 수 있음 (사원 엔터티의 도출 필요)
- 비식별 상속 : 한 명의 사원은 하나의 부서에 소속될 수 있음

겸직 부서 - 사원소속부서 - 사원

관계의 표현 - 3

직접 종속 관계 만을 표현, 간접 종속 관계는 표현하지 않음 (부모-자식 간만 표현하면 됨)
두 엔터티 간에 둘 이상의 관계가 존재할 수 있음 -> 관계명 표현, 컬럼명 수식어(역할 명 role name) 사용
가능한 모든 관계를 표현
너무 많은 관계선은 피하기 위해 뺄 수도 있음 (공통코드 테이블 등)

데이터 표준화의 기본

일관성 유지
가독성 강화
명확하고 신속한 의사소통, 원하는 시기에 정확한 정보 전달 (개발까지) // 데이터 활용도 증가 정제 비용 감소, 데이터 품질 향상
표준화는 대형 시스템에서는 표준화 담당자가 따로 있을 정도로 어려운 부분

단어

표준 단어

DB 오브젝트의 이름 부여 시 사용하기 위한 원자 단위(최소 의미 단위)의 표현을 미리 정해 놓은 것
우리 회사는 전사 표준이 따로 있지는 않음, 우리 회사의 경우 다양한 서비스가 있기 때문에 전사 표준 관리는 어려움, 우리 회사 특성상 서비스 별 표준, 조직별 표준이 있음
표준 단어(영문, 한글)에는 띄어쓰기, /, _ 등 특수 문자 사용 금지
영문 단어는 약어 혹은 full 단어로 정하여 사용
- 오라클 쓸 때는 컬럼명 길이 제한이 있어서 약어를 많이 썼는데, 요즘엔 mysql 쓰니까 full 단어로 씀
복합 단어는 두 개 이상의 단어를 결합하여 하나의 단어로 사용(blacklist, zipcode)

표준 용어

표준 단어를 조합하여 엔터티명(테이블명)과 속성명(컬럼명)에 사용되는 용어를 구성함
동일한 데이터 집합은 동일한 용어를 사용
용어의 마지막에는 분류어에 속하는 단어를 사용 (수 count, 일자 date ymd, datetime ymdt 등)

도메인

속성에 대한 성질을 그룹핑한 개념
동일 성질을 가진 컬럼의 데이터 타입 및 데이터 길이를 일관되게 관리함
여러 시스템에서 사용되면 공통 도메인을 선정해서 쓰기도 함
텍스트, 숫자 도메인은 최대한 통합

분류어 -> 도메인 -> 데이터 타입(길이)

코드

특수도메인
특정 도메인 값(코드 값)이 미리 정의되어 있는 도메인
공통코드와 목록성코드(조직코드,...)
- 3개 이상의 구분 값을 사용하는 경우 '코드' 도메인 사용
- Yes/No 혹은 True/False의 2가지 값 만을 사용하는 경우 '여부' 도메인 사용
- 기본 : Not Null, default 값 정의를 권장함

명명 규칙

엔터티 명명
- 단수형
- 부모 엔터티와 강하게 엮인 자식 엔터티는 가급적 부모 엔터티의 이름을 상속받음 (게시물첨부파일, 게시물스크랩)
- 관계 엔터티의 경우, 두 엔터티의 이름을 상속받아 명명(주문 상품 -> 주문상품)
- 통계성 테이블의 경우, 통계 주기와 기준이 나타나도록 명명
- 엔터티 명 마지막 단어에서 엔터티의 유형을 나타내도록 정함
- 바람직하지 않은 예 : 회원리스트, 주문처리, 급여관리, 쿠폰목록 -> 데이터 관점에서 생각하고, 기능이나 화면의 이름은 필요없음

속성 명명
- 단어의 뒤를 먼저 보면 되도록 명명

표준 용어 사전
- 각 서비스에 해당하는 단어, 도메인, 코드 표준이 정의되면 이를 바탕으로 표준 용어를 구성 함
- 업무적으로나 DBMS 적용 상 무리가 없는지 검토
- 누락된 단어 및 도메인, 코드 등이 없는지 확인하고 추가 보완 작업을 수행 함

물리 모델링을 하다가 마주치는 고민들

정규화 vs. 반정규화

정규화 : 중복을 안 만들고자 하는 구조
반정규화
- 물리모델링 시 정규화된 논리모델을 조정하여 반정규화를 하는 경우가 있음
- 조상테이블 속성을 자식테이블에 갖다놓는..
- 테이블의 통합/분리, 속성의 추가, 관계의 중복 등이 포함
- 데이터의 정합성과 무결성의 유지를 위한 방안과 노력이 반드시 필요함
- 조회의 성능과 개발의 편의를 위해 반정규화를 한 경우, 그에 따라 변경 작업시의 성능이 저하되는 경우가 많으므로 트레이드-오프를 따져서 적용해야 함

방법

테이블 반정규화

테이블 통합
1:1, 1:N, 수퍼타입/서브타입
테이블 분할
수직, 수평
테이블 추가
통계, 이력, 부분-업데이트가 자주 발생하면 따로 떼어내기, 복제-저장소가 다른 경우 원격지에 있는걸 가져다 놓기

컬럼 반정규화

중복컬럼 추가
테이블 간의 조인을 감소시키기 위해서, 그 컬럼이 잘 바뀌지 않는다는 가정 하에
추출(derived)컬럼 추가
매번 계산되지 않도록 미리 계산해서 갖다놓기(주문총금액)
이력테이블 컬럼 추가
최근 값에 대한 컬럼을 미리 추가해놓기

관계 반정규화

관계의 중복

하기 전에
- 다른 방법이 있는지 고려해보고 도저히 안 되면 반정규화하기
하고 나서
- ERD 등에 해당 내용을 공유하기

통합 vs. 분리

논리모델에서 설계한 수퍼타입과 서브타입을 물리모델로 전환할 경우 트랜잭션 패턴 및 생성되는 데이터 건수 등에 따라 테이블의 통합 혹은 분리를 검토하여 결정

트랜잭션 유형에 의한 결정

개별 트랜잭션 발생 유형
공용 컬럼을 최대한 도출해서 수직분할 (결제수단코드- ...) or 통합 (모든 값을 한 테이블에, join이나 union all이 너무 많이 발생할 것 같을 때)
수퍼타입+서브타입 트랜잭션 발생 유형
수평분할 (주문번호 - 카드결제내역 / 휴대폰결제내역 / 계좌이체결제내역) 결제번호가 겹치는 문제가 있을 수 있음

통합을 고려해 보게 되는 경우

식별자가 동일 할 때
관리하는 정보가 유사할 때
자주 join 되거나 union all 되는 경우
ARC 관계로 정의되는 경우, 상속이 계속 된다면 부모 테이블의 통합이 가능한지 검토

인조 키 vs. 본질 키

인조 키 vs. 본질 키 - 1

PK 선정

그 중 식별자 정의, 생성 주체의 단위를 정하는 것
본질식별자 혹은 인조식별자 사용 여부도 논리 모델링 단계에서 결정하는 것이 바람직함
특징
- 유일성
- 안정성 : 절대 변경되지 않아야 함(~명 등 텍스트 성 정보는 하지 않기)
- 최소성 : 복합 식별자로 구성된 경우 유일성에 기여하지 않는 속성은 제외하고 최소의 조합으로 함
- 활용성 : 업무에서 가장 많이 사용되는 속성 선택, PK는 자식 테이블에 상속이 됨
- 보안성 : 주민등록번호, 여권번호 등

인조 키 vs. 본질 키 - 2

본질식별자

해당 엔터티의 의미상의 주어, 인스턴스를 유일하게 식별하는 최소한의 속성 조합
집합의 인스턴스가 생성되는 단위

인조식별자

쿼리 복잡도 감소
검색에 활용 불가 -> 반정규화의 원인이 되는 경우가 있음
중요 데이터의 중복 가능성

인조 키 vs. 본질 키 - 3

본질식별자 사용을 먼저 고려
인조식별자를 사용한다면, 하위 엔터티에서 다수의 조인이 발생하거나, 반정규화의 원인이 되기도 함
인조식별자를 선택하는 경우
- 본질식별자가 식별자의 조건에 부합하지 않는 경우(로그 성격의 데이터를 보관하는 테이블, 본질식별자를 종종 수정해야 하는 경우)
- 상속 시 식별자의 증가로 복잡도가 지나치게 증가하는 경우(mysql의 경우는 PK의 개수가 성능에 영향을 미침)
- 타 영역에서 자주 참조하는 주요 엔터티의 식별자인 경우 인조 식별자의 사용을 검토해야 함
~번호, ~일련번호, ~ID를 구분해서 쓰기도 함

시스템 속성의 관리

모든 테이블에 공통으로 추가하여 관리할 필수 속성을 정의하는 것
데이터 추적 관리 용도, 마이그레이션 작업 시, 데이터 분리 보관 작업 시 등 중요한 기준 정보로 활용됨
주의 사항
- 업무 용도로 사용하지 않는 것을 권장함(등록일시:데이터의 실제 Insert 일시와 가입일시는 구분해서 쓰기..)

이력의 관리의 형태

이력 관리의 형태 - 1

이력 관리 모델
데이터 복구나 추적이나 고객 응대를 위해서
형태
- 시점이력(변경된 시점에 대한..) 선분이력(유효시작일자, 이런 식으로 구간 관리)
- 분리형 통합형
- row column subject

데이터 모델 검수

앞을 내다보고, 나중에 관리 측면까지 고려함

#devspace 미인주 님의 블로그입니다.

클러스터 테이블과 논클러스터 테이블

미인주 2023. 3. 10. 10:02

2023. 3. 10. 10:02

클러스터 테이블 vs 논클러스터 테이블 비교

클러스터란?

디스크로부터 데이터를 읽어오는 시간을 줄이기 위해서 자주 사용되는 테이블의 데이터를 디스크의 같은 위치에 저장하는 방법
비슷한 레코드들을 동시에 조회하는 경우가 많아 효율적으로 조회하는 방법으로 고안됨
PK가 비슷한 레코드끼리 묶어서 저장하는 것 (MySQL)

클러스터 테이블(인덱스)

테이블당 1개씩만 허용됨
PK 생성 시 그 컬럼은 자동으로 클러스터 인덱스가 생성됨
* 일반 인덱스와의 차이
- 클러스터링 되어 있다는 요소가 추가됨
- 옵티마이저에서 일반 인덱스보다 높은 랭킹
PK를 설정하지 않은 경우는? InnoDB가 다음의 우선순위대로 대체 컬럼을 선택
- UNIQUE NOT NULL 중 첫번째 인덱스를 클러스터 키로 선택
- 자동으로 유니크한 값을 가지도록 AUTO_INCREMENT 컬럼을 내부적으로 추가해 클러스터 키로 선택
  * 하지만, 자동으로 추가되는 컬럼은 사용자에게 보이지 않아서 SQL에서 사용할 수가 없음
PK에 의해 레코드 저장 위치가 결정됨
- PK값이 변경되면 -> 레코드의 물리적으로 행을 재배열함 -> 랜덤 액세스가 많이 발생해서 I/O 증가
- PK를 신중히 결정해야 함
인덱스 페이지의 리프 페이지가 곧 데이터. 즉, 테이블 자체가 인덱스
InnoDB 스토리지 엔진에서만 지원함
장점
- 그룹된 컬럼 데이터 행들이 같은 데이터 블록에 저장되기 때문에 디스크 I/O가 줄어 SELECT 시 처리 성능이 매우 빠름
- 클러스터된 테이블 사이에 조인이 발생하면 처리 시간이 단축됨
- INSERT, UPDATE, DELETE 시 항상 정렬 상태를 유지함
- 이미 정렬된 상태로 저장되기 때문에 인덱스 테이블이 필요하지 않아 논클러스터보다 DB 용량을 덜 차지함
- 테이블의 모든 보조 인덱스가 PK를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많음(커버링 인덱스)
단점
- 논클러스터보다 검색 속도는 더 빠르지만 INSERT, UPDATE, DELETE는 느림

논클러스터 테이블(인덱스)

테이블당 249개 허용됨
레코드의 원본은 정렬되지 않고, 인덱스 테이블만 정렬됨
- 인덱스 페이지는 로그파일에 저장됨
인덱스 자체의 리프 페이지는 데이터가 아니라 데이터가 위치하는 포인터(RID)이기 때문에 클러스터보다 검색 속도는 느리지만 INSERT, UPDATE, DELETE는 더 빠름

비교

쉽게 책에 비유하자면 클러스터 테이블은 페이지를 알기 때문에 바로 그 페이지를 펴는 것(바로 접근)이고, 논클러스터 테이블은 목차에서 찾고자 하는 내용의 페이지를 찾고 그 페이지로 이동하는 것(한번 거쳐서 접근)과 같음

참고링크

#devspace 미인주 님의 블로그입니다.

본질식별자와 인조식별자

미인주 2023. 3. 10. 09:59

2023. 3. 10. 09:59

본질 식별자 vs 인조 식별자 비교

본질 식별자

업무에 의해 만들어진 식별자
장점
- PK 인덱스 활용 가능

인조 식별자

업무적으로 만들어지지는 않지만 본질 식별자가 복잡한 구성을 갖고 있어 인위적으로 만든 식별자
후보 식별자 중 PK로 선정할만 한 것이 없을 때 사용
장점
- 모델이나 SQL이 간단해짐
단점
- 인스턴스 생성 기준을 인조 식별자만으로 판단하기 어려움
- 모델만 보고 업무를 이해하기 어려움, 특히 행위 엔터티에서 가독성이 저하됨
- 유니크 인덱스를 추가로 사용하여 추가적으로 키를 관리해야 함
- 중복 데이터로 인한 품질 문제
  PK를 인조 식별자로 구성한 경우, PK 제약은 인조식별ID에 대해 적용되어 있기 때문에 중복 데이터가 발생하더라도 DBMS에서는 막을 수 없기 때문에 애플리케이션 단에서 처리해야 하는 문제 발생
아래와 같은 경우, 인조 식별자를 사용하는 것이 나을 수 있음
- 복합키의 변경이 빈번할 경우
- 복합키가 4개 이상일 경우
AUTO-INCREMENT 컬럼을 인조 식별자로 사용할 경우
- 보조 인덱스도 필요하고, PK의 크기도 같을 때

결론

PK는 가능하다면 AUTO-INCREMENT 컬럼(인조 식별자)보다는 업무적인 컬럼(본질 식별자)으로 생성할 것
- InnoDB의 PK는 클러스터 키로 사용되며, 이 값에 의해 레코드의 위치가 결정됨
- 즉, PK로 검색하는 경우 논클러스터 테이블에 비해 매우 빠른 처리가 가능하여 InnoDB에서는 성능에 큰 차이가 발생함
- 그러므로 컬럼의 크기가 크더라도 업무적으로 해당 레코드를 대표할 수 있다면 그 컬럼을 PK로 설정하는 것이 좋음
인조 식별자의 남용을 피하고 꼭 필요한 경우에만 사용하는 것을 권장함
- 인조 식별자는 불필요한 인덱스를 추가 생성해야 하고, 추가로 생성한 인덱스는 용량과 DML 성능에 영향을 줄 수 있음

참고링크

#devspace 미인주 님의 블로그입니다.

PREV 이전 1 2 3 4 5 ···7 NEXT 다음

내 블로그 - 관리자 홈 전환	`Q` `Q`
새 글 쓰기	`W` `W`

글 수정 (권한 있는 경우)	`E` `E`
댓글 영역으로 이동	`C` `C`

이 페이지의 URL 복사	`S` `S`
맨 위로 이동	`T` `T`
티스토리 홈 이동	`H` `H`
단축키 안내	`Shift` + `/` `⇧` + `/`

#devspace