내가 테이블의 PK를 정할때를 생각해보면 ..

• 일반적으로는 auto_increment seq를 사용하는 것 같다. => 왜 ?
  • 음 .. 유의미한 값을 사용하게되면, 추후 요구사항이나 상황이 변함에 따라 PK를 변경해야할 일이 생길 수 있음 => PK 변경하면 뭐가 안좋음 ? => seq말고도 유의미하지 않은 값들은 여럿있지 않나 ?

PK 목적 ?

• row를 유일하게 식별
  • 왜 식별해야할까 ?
• PK가 없으면 ?
  • mysql에서는 (내부적으로) 자동으로 생성됨

PK 변경하면 뭐가 안좋음 ?

• 인덱스 재구성 ?

###

seq말고도 유의미하지 않은 값들은 여럿있지 않나 ?

• UUID ?

참고 자료

• https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html

============================================

• InnoDB 스토리지 엔진에서 인덱스는 빠른 검색이나 정렬 등의 목적으로도 사용되지만 InnoDB 내부적으로는 레코드 잠금의 기준으로도 사용된다.

MySQL 인덱스 종류

기본 인덱스 (Primary Index)

• 테이블의 기본 키(Primary Key)에 자동으로 생성되는 인덱스.
• 기본 키 값은 항상 유일하며, 테이블의 각 행을 고유하게 식별.
• MySQL의 대표적인 저장 엔진인 InnoDB에서는 기본 인덱스가 클러스터드 인덱스(Clustered Index)로 구현됩니다.
• 클러스터드 인덱스는 데이터 자체가 인덱스 구조 안에 저장되며, B+ 트리로 관리됩니다.
• 데이터 검색 및 정렬 최적화.
• 레코드의 물리적 저장 순서를 유지.

유니크 인덱스 (Unique Index)

• 중복 값을 허용하지 않는 인덱스.
• 유일한 데이터 값을 보장하기 위해 사용.
• 유니크 인덱스는 보조 인덱스와 동일한 자료구조를 사용하지만, 중복 값이 삽입되면 제약 조건 위반 에러를 발생시킴.

보조 인덱스 (Secondary Index)

• 기본 키가 아닌 다른 열에 대해 생성되는 인덱스.
• 유일 인덱스(Unique Index)와 일반 인덱스(Non-Unique Index)로 나뉨.
• InnoDB에서는 보조 인덱스도 B+ 트리로 구현.
• 보조 인덱스의 리프 노드는 기본 키 값(Primary Key)을 포함하여 실제 데이터를 조회할 때 기본 키를 통해 다시 접근(백 트래킹)할 수 있도록 함.
• WHERE 절, ORDER BY, GROUP BY에서 자주 사용되는 컬럼에 대해 성능 최적화.

클러스터 인덱스란?

• 클러스터 인덱스는 데이터 자체가 인덱스 구조의 리프 노드에 저장된 인덱스입니다.
• 테이블의 기본 키(Primary Key)에 대해 자동으로 생성되며, 기본 키를 기준으로 데이터가 정렬되어 저장됩니다.
• InnoDB에서는 기본 인덱스(Primary Index)가 항상 클러스터 인덱스입니다.
• 클러스터 인덱스는 데이터 정렬을 유지해야 하므로, 한 테이블에 하나만 생성할 수 있습니다.
• 보조 인덱스(Secondary Index)는 리프 노드에 기본 키 값을 저장하여 클러스터 인덱스를 통해 실제 데이터를 검색합니다.

• InnoDB 스토리지 엔진에서는 모든 세컨더리 인덱스 검색해서 데이터 레코드를 조회하기 위해 반드시 key 값을 저장하고있는 B+ Tree를 다시 한번 탐색

• 데이터 삽입:
  • 데이터가 항상 기본 키 순서대로 저장되기 때문에, 삽입 시 데이터가 정렬된 위치에 들어가야 하며, 추가적인 디스크 연산이 발생할 수 있습니다.
• 데이터 삭제:
  • 클러스터 인덱스에서 데이터를 제거하면 해당 데이터가 포함된 리프 노드에서도 삭제됩니다.
• 데이터 갱신:
  • 기본 키가 변경될 경우, 데이터의 물리적 위치도 변경되어야 하므로 비효율적입니다.
  • 따라서 기본 키는 변경되지 않는 값으로 설정하는 것이 좋습니다.

기본 키가 없으면 클러스터 인덱스도 없나 ?

InnoDB는 테이블에 반드시 하나의 클러스터 인덱스를 유지해야 합니다. 기본 키가 없을 경우, 클러스터 인덱스를 생성하기 위해 다음 우선순위에 따라 동작합니다:

1. UNIQUE 제약 조건이 있는 첫 번째 NOT NULL 컬럼:

• 테이블에 유니크 제약 조건(UNIQUE)이 있는 NOT NULL 컬럼이 있다면, 해당 컬럼을 클러스터 인덱스로 사용합니다.

2. InnoDB가 내부적으로 생성한 숨겨진 Row ID:

• 위 조건을 만족하는 컬럼이 없으면 InnoDB는 내부적으로 숨겨진 Row ID를 생성합니다.
• 이 Row ID는 6바이트 크기의 정수로, 행마다 고유한 값을 가지며 클러스터 인덱스로 사용됩니다.

B+Tree 구조

B는 Balanced 또는 Binary로 알려져있다.

!image

• B 트리의 변형으로, 리프 노드에만 데이터를 저장.
• 리프 노드가 순차적으로 연결되어(Linked List) 있어 범위 검색과 정렬 작업에 유리.
• 대부분의 MySQL 인덱스(InnoDB의 기본 인덱스와 보조 인덱스)는 B+ 트리로 구현.
• 삽입, 삭제, 갱신 시 데이터가 자동으로 정렬됨.

• 효율적인 검색과 범위 쿼리에 적합.

• 장점
  • 리프 노드를 제외하고 데이터를 담아두지 않기 때문에 메모리를 더 확보함으로써 더 많은 key들을 수용
  • 하나의 노드에 더 많은 key들을 담을 수 있기에 트리의 높이는 더 낮아진다.
  • 풀 스캔 시, B+tree는 리프 노드에 데이터가 모두 있기 때문에 한 번의 선형탐색만 하면 되기 때문에 B-tree에 비해 빠르다. B-tree의 경우에는 모든 노드를 확인해야 한다.

B-Tree

!image

!image

Q. 루트 노드 페이지는 무조건 1개야 ?

네, 루트 노드(Root Node)는 B+Tree의 구조적 특성상 항상 1개의 페이지로 유지됩니다. 이는 MySQL에서 사용하는 InnoDB의 B+Tree 인덱스에서도 동일합니다.

루트 노드의 역할

• 루트 노드는 트리의 최상단에 위치하며, 트리 전체를 관리하는 진입점 역할을 합니다.
• 모든 탐색, 삽입, 삭제 작업은 루트 노드에서 시작됩니다.
• 트리의 균형을 유지하기 위해, B+Tree는 항상 1개의 루트 노드를 유지합니다.

데이터 증가와 루트 노드

데이터 삽입으로 리프 노드와 중간 노드가 분할될 때

• 데이터가 많아지면 리프 노드가 꽉 차고 분할(split)됩니다.
• 분할된 리프 노드의 키가 중간 노드로 전파됩니다.
• 중간 노드가 가득 차고 분할되면, 새로운 키가 루트 노드로 전파됩니다.

루트 노드 자체의 분할

• 루트 노드가 가득 차면 루트 노드 자체가 분할되고, 새로운 루트 노드가 생성됩니다.
• 이 경우 기존 루트 노드의 두 부분이 새 루트 노드의 자식으로 이동합니다.

결과

• 루트 노드는 항상 1개의 페이지로 유지되며, 트리의 높이가 1 증가합니다.

인덱스와 테이블 설계

인덱스는 어떤 데이터를 기준으로 하는게 좋을까 ?

• 수정/삭제가 너무 빈번하면 안좋을 것 같다.
  • 인덱스 구조를 재정렬 해야할 수 있다.
• 조회에 많이 쓰일만한거 (배달 관련 서비스라면, 가게이름, 메뉴명 같은거)
• 서비스 특성상 쓰기/수정 성능이 더 중요하면 인덱스를 최소화하는게 좋을 것으로 생각
• 보조 인덱스의 경우 스캔한 모든 행에 대한 잠금을 획득하기 때문에 이것도 잘 고려해야될듯

Q. unique 키가 변경되는 경우와 pk가 변경되는 경우 인덱스 관점에서 어떤 차이가 있어 ?

UNIQUE 키가 변경되는 경우

• UNIQUE 키의 리프 노드에는 해당 컬럼의 값과 함께 PK 값이 저장됨(PK를 통해 클러스터 인덱스와 데이터에 접근).
• 보조 인덱스의 리프 노드에서 변경된 UNIQUE 키 값을 업데이트하고, 이에 따라 인덱스를 재구성.
• 보조 인덱스는 PK를 참조하므로, PK 값에는 영향을 주지 않음.
• UNIQUE 키의 값이 변경되면, 보조 인덱스의 정렬이 유지되도록 기존 노드를 제거하고 새로운 노드를 삽입.
• 인덱스 크기와 데이터 변경 횟수에 따라 성능에 영향을 미칠 수 있음.
• 보조 인덱스에서 변경된 UNIQUE 키를 통해 여전히 PK를 참조하므로, 클러스터 인덱스와 데이터 자체에는 영향을 미치지 않음.
• UNIQUE 키는 특정 컬럼만 변경되므로, 변경 범위가 PK보다 제한적.
• UNIQUE 키 변경은 클러스터 인덱스의 재구성을 초래하지 않으므로 상대적으로 효율적.
• 보조 인덱스가 많아질수록 변경 비용이 누적.

PK 변경시

• 클러스터 인덱스는 테이블의 데이터 정렬을 PK 기준으로 유지하므로, PK 변경은 테이블 전체에 영향을 미침.
• 모든 보조 인덱스의 리프 노드도 PK를 참조하므로, PK 변경은 보조 인덱스의 갱신을 유발.
• 클러스터 인덱스는 PK 값으로 테이블 데이터를 정렬하여 저장하므로, PK 변경은 전체 데이터의 재정렬을 초래.
• 테이블 크기가 클수록 비용이 크게 증가.
• PK 변경 시 모든 보조 인덱스도 갱신되어야 함.
  • 보조 인덱스의 크기가 클수록 성능에 큰 영향을 미침.

Q. student라는 테이블에 학생번호가 primary key로 있고 학년, 반, 이름, 성별 컬럼으로 구성되어 있고, (학년 - 반)은 유니크 키일때, 100만건의 데이터가 있으면 B+Tree에 어떤식으로 저장될까 ?

• 컬럼
  • student_id: Primary Key
  • grade: 학년
  • class: 반
  • name : 이름
  • gender: 성별
• Primary Key: student_id (클러스터형 인덱스)
• Unique Key: grade, class (보조 인덱스)

Primary Key 인덱스 (클러스터형 인덱스)

Primary Key 인덱스는 클러스터형 인덱스로, 리프 노드에 실제 데이터 행이 저장됩니다.

• 즉, student_id에 의해 데이터가 정렬되며, 리프 노드에 테이블의 나머지 모든 컬럼 (grade, class, name, gender)의 값이 함께 저장됩니다.

루트 노드

• student_id 값의 최상위 범위를 기준으로 중간 노드 또는 리프 노드를 가리킵니다.
• 예를 들어, student_id가 1부터 1,000,000까지 있다고 가정하면, 루트 노드에는 데이터 분할 기준이 되는 키 값들이 저장됩니다.
  • 예: [100000, 200000, …]

중간 노드

• 데이터 범위를 나누는 키 값만 포함하고, 하위 노드를 가리키는 포인터를 가집니다.
• 예: [100000] → 첫 번째 블록 (1~100000) [200000] → 두 번째 블록 (100001~200000)

리프 노드

• 리프 노드에는 정렬된 student_id 값과 함께 전체 데이터 행이 저장됩니다.
  • 예: 리프 노드에 저장된 데이터:
    • student_id = 1: (1, 1, 1, ‘Alice’, ‘F’)
    • student_id = 2: (2, 1, 1, ‘Bob’, ‘M’)
• 100만 건의 데이터가 정렬된 상태로 저장됩니다.
• 리프 노드는 서로 연결 리스트(Linked List)로 연결되어 있어 순차적 검색이 가능합니다.

보조 인덱스 (Secondary Index)

• Unique Key (grade, class)를 생성하면, 보조 인덱스로 관리됩니다.
• 보조 인덱스는 리프 노드에 해당 행의 Primary Key(student_id) 값만 저장하며, 실제 데이터는 Primary Key 인덱스를 통해 접근합니다.

루트 노드

• grade, class 조합으로 데이터를 분할합니다.
• 예: 루트 노드에 저장된 키:
  • [1-1, 2-1, 3-1] -> (grade=1, class=1 / grade=2, class=1 등)

중간 노드

• 중간 노드는 하위 노드를 탐색하기 위한 grade, class 값만 저장합니다.

리프 노드

• 리프 노드에는 정렬된 grade, class 조합과 해당 행의 student_id가 저장됩니다.
• 예:
  • 리프 노드 데이터:
    • (1, 1) → 1000 (student_id)
    • (1, 2) → 1050 (student_id)
    • (2, 1) → 2000 (student_id)

보조 인덱스를 통한 검색 과정

• 예: WHERE grade = 1 AND class = 1 쿼리
  • 보조 인덱스에서 (1, 1)을 검색합니다.
  • 해당 리프 노드에서 student_id 값을 가져옵니다.
  • Primary Key 인덱스를 사용해 student_id를 기반으로 전체 행 데이터를 가져옵니다.

데이터 분포와 노드 크기 계산

MySQL에서 기본 페이지 크기는 16KB(16,384바이트)입니다. 한 페이지(노드)에 저장 가능한 데이터 수는 인덱스 키 크기와 데이터 크기에 따라 다릅니다.

루트 노드 : 키 값 + 포인터 저장

• 행당 데이터 크기 = 4 (student_id) + 8 (포인터) = 12바이트
• 16384/12 = 약 1365
• 즉, 한 페이지당 1365개의 쌍 저장 가능

중간 노드 : 키 값 + 포인터 저장

• 루트 노드와 동일

리프 노드 : 실제 데이터 행 저장

• Primary Key (student_id, 4바이트)
• 나머지 컬럼 데이터 (grade, class, name, gender)
• 행당 데이터 크기 = 4 (student_id) + 4 (grade, class) + 50 (name, gender 등) ≈ 60바이트
  • 16KB/60BYTES = 16,384/60 = 약 273개
  • 즉, 하나의 페이지에 약 273개의 데이터 들어간다.

클러스터 인덱스 트리 깊이 계산

• 테이블에 100만 건의 데이터가 있다고 가정하고, 클러스터 인덱스를 계산
  • 리프노드 : 100만 / 273 = 3667 페이지
  • 중간노드 : 3667 / 1365 = 3페이지
  • 루트노드 : 3 / 1365 = 1페이지

InnoDB 스토리지 기준

Q. mysql를 사용하면 데이터가 실제 어떤식으로 저장되고, 데이터가 많이 쌓일수록 디스크와 어떤 관련이 있는지도 알려줄래 ?

1. 데이터 저장 방식

• InnoDB는 데이터를 테이블 스페이스라고 불리는 파일에 저장합니다.
• 이 테이블 스페이스는 실제로는 데이터 파일(ibdata1) 및 개별 테이블 파일(.ibd) 형태로 디스크에 저장됩니다.
• 테이블 스페이스에는 데이터와 인덱스가 함께 저장됩니다.
• 각 테이블마다 하나의 .ibd 파일이 생성되며, 이 파일에 테이블 데이터와 인덱스가 저장됩니다.
  • 저장 디렉토리 : SHOW VARIABLES LIKE ‘datadir’;

구조

• 데이터 페이지 : 기본 저장 단위로, 16KB 크기의 페이지 단위로 데이터를 관리합니다.
• 인덱스 구조 : B+Tree 구조로 인덱스를 저장하며, 이를 통해 빠른 데이터 조회가 가능합니다.
• 버퍼 풀 : 자주 사용되는 페이지를 메모리에 캐싱해서 성능을 최적화합니다.

2. 데이터 양 증가와 디스크 사용

데이터가 많이 쌓이게 되면 MySQL과 디스크 사이에 다음과 같은 상관관계가 발생합니다.

디스크 공간

• 테이블에 레코드가 많이 쌓이면 .ibd 파일의 크기가 계속 커집니다.
• 인덱스도 레코드가 많아질수록 B+Tree 구조의 노드가 늘어나면서 공간을 더 차지하게 됩니다.

디스크 I/O

• 쓰기 작업
  • 새로운 데이터 삽입, 업데이트 시 디스크에 데이터를 기록합니다.
• 읽기 작업
  • 데이터를 조회할 때, 캐시에 없는 페이지를 디스크에서 읽어와야 합니다.
• 디스크 정렬 및 인덱스 관리
  • 인덱스는 효율적인 검색을 위해 B+Tree 구조를 유지해야 하므로, 데이터가 많아질수록 정렬 및 재구성 작업이 필요합니다.

디스크 조각화

• InnoDB는 테이블을 16KB 페이지 단위로 관리하지만, 데이터가 삭제되거나 업데이트되면 공간이 비게 되어 디스크 조각화가 발생할 수 있습니다.
  • 이런 경우에는 OPTIMIZE TABLE 명령을 실행하여 테이블을 재정렬하고 디스크 공간을 최적화할 수 있습니다.

※ 인덱스 크기와 데이터 삽입

B+Tree 인덱스와 데이터 삽입 비용

B+Tree의 데이터 삽입은 다음과 같은 과정을 거칩니다

1. 삽입할 위치 탐색 : 인덱스의 루트 노드에서 시작하여 리프 노드까지 탐색합니다.
2. 리프 노드에 삽입: 리프 노드에 빈 공간이 있으면 데이터를 삽입합니다.
3. 노드 분할 (Split): 리프 노드에 여유 공간이 없다면, 노드를 두 개로 나누고 상위 노드에 새로운 키를 추가합니다.

노드 분할 비용 (Split)

• B+Tree는 균형 트리를 유지해야 하므로 노드가 가득 차면 분할(Split)이 발생합니다.
• 인덱스가 커질수록 노드에 데이터가 많이 채워져 있어 Split 발생 빈도가 늘어날 수 있습니다.
• Split 비용
  • 리프 노드 데이터를 반으로 나눕니다.
  • 상위 노드(부모 노드)에 새로운 키를 추가합니다.
  • 부모 노드도 가득 찬 경우 재귀적으로 Split이 발생합니다.

디스크 I/O 비용

• MySQL의 B+Tree는 데이터를 페이지 단위 (기본 16KB)로 관리합니다.
• 데이터 삽입 시, 페이지가 메모리에 없는 경우 디스크에서 페이지를 읽어와야 하며 삽입 후 변경된 페이지를 다시 디스크에 써야 합니다.
• 인덱스 크기가 커질수록
  • 메모리에 캐싱되지 않은 페이지가 증가합니다.
  • 더 많은 디스크 I/O가 발생합니다.

3. 성능에 미치는 영향

데이터 양이 증가하면 다음과 같은 성능 저하가 발생할 수 있습니다.

• 읽기 성능
  • 인덱스가 비효율적이거나 메모리에 캐시되지 않은 경우 디스크에서 데이터를 읽는 데 시간이 소요됩니다.
• 쓰기 성능
  • 대량의 데이터 삽입 시 디스크 I/O 병목이 발생할 수 있습니다.
• 인덱스 유지
  • 데이터가 많을수록 인덱스 생성 및 유지에 추가 리소스가 필요합니다.
• 디스크 사용량
  • 큰 테이블이나 로그 파일로 인해 디스크 공간이 부족하면 전체 DB의 성능이 저하될 수 있습니다.

Q. 리프 노드가 추가되거나, 인덱스 값 변경으로 인해 트리에서 재정렬이 일어날때 왜 성능상 좋지 않은거야 ?

1. 리프 노드 추가 및 재정렬 과정

B+Tree에서 리프 노드에 삽입할 공간이 없을 때, 노드를 두 개로 나누는 Split이 발생합니다.

Split

• 기존 노드의 데이터를 반으로 나눕니다.
• 상위 노드(부모 노드)에 새로운 키와 포인터를 삽입합니다.

• 부모 노드도 가득 차면 다시 재귀적으로 Split이 발생합니다.

• 이 과정에서 다음과 같은 비용이 발생합니다.
  • 데이터 복사 : 리프 노드의 데이터를 새로운 노드로 복사해야 합니다.
  • 상위 노드 수정 : 부모 노드에 키와 포인터를 추가해야 하므로 상위 노드가 수정됩니다.
  • 재귀적 분할 : 부모 노드도 가득 차면 트리의 높이가 증가할 수 있습니다.

디스크 I/O 비용

• MySQL의 InnoDB는 데이터를 페이지 단위(16KB)로 관리합니다.
• 노드가 수정되면 변경된 페이지를 디스크에 다시 기록해야 합니다.
• 리프 노드와 부모 노드가 모두 수정되므로 디스크 I/O 비용이 급증합니다.
• 즉, 노드 분할이 자주 일어나면 디스크 쓰기와 메모리 복사가 빈번해져 성능이 저하됩니다.

2. 인덱스 값 변경으로 인한 재정렬

Q. 인덱스 없는 경우 데이터 스캔 과정 ?

Q. 새로운 인덱스 생성시 테이블 잠금 ?

Q. 메모리에 페이지 캐싱하는 과정 ?

• 캐시미스 ?

Q. 파티셔닝과 샤딩은 각각 언제 고려하면 좋을까 ?