develop-hani / real-mysql-study

책 Real MySQL 스터디

Real MySQL Study

👨‍🏫 추가 조사 자료

5장

• InnoDB의 Record Lock에서 왜 인덱스를 잠그나? (궁금해서 찾아봤는데 책 뒷부분에 나왔다.)
• Online DDL
• InnoDB Record Lock에서 테이블에 인덱스가 없다면?

8장

• 페이지 잠금과 정순 스캔
• R-Tree 인덱스에서 포함 관계를 비교하는 함수의 효율성

기타

• PK에 int type 데이터를 사용하는 이유
• oreign Key 사용을 피하는게 추세?!?!

real-mysql-study's Issues

[8장] 페이지 잠금과 정순 스캔

p.246 페이지 잠금이 인덱스 정순 스캔에 적합한 구조에 대한 설명이 나와있지 않아 찾아 보았다.

---

InnoDB 스토리지 엔진에서 페이지 잠금 과정에서 데드락을 방지하기 위해서 B-Tree의 왼쪽에서 오른쪽 순서로 잠금을 획득한다.

• 정순 스캔으로 페이지 잠금
  단순히 다음 페이지를 찾아 잠금을 획득한다.
• 역순 스캔으로 페이지 잠금
  1. 커서의 상태를 저장하고 내부 미니 트랜잭션을 커밋해서 미니 트랜잭션 버퍼를 글로벌 리두 로그 버퍼로 복사
  2. 미니 트랜잭션을 재시작
  3. 커서의 상태를 다시 복구 (이 과정에서 현재 블록이 이동되는 것을 막기 위해서 pinning을 하고 필요에 따라서 현재 블록과 이전 블록(Backward block)의 잠금을 획득)

---

<참고자료>

• MySQL Ascending index vs Descending index

[5장] InnoDB의 Record Lock에서 왜 인덱스를 잠그나?

업데이트 시에 WHERE 절의 column을 이용하여 락을 건다.
예를 들면 SELECT c1 FROM t1 WHERE c1=50 FOR UPDATE에서 t1 테이블의 c1이 50인 모든 레코드에 락을 건다는 것이다.
모든 레코드에 락을 걸기보다 인덱스에만 락을 걸었을 때 잠금의 범위가 작아진다.

FOR UPDATE
가정 먼저 LOCK을 획득한 SESSION의 SELECT 된 ROW들이 UPDATE 쿼리후 COMMIT 되기 이전까지 다른 SESSION들은 해당 ROW들을 수정하지 못하도록 하는 기능

[기타] Foreign Key 사용을 피하는게 추세?!?!

현업에서는 Foreign Key를 사용하지 않는 것이 추세라고 해서 그 이유를 살펴보았다.

---

1. 성능!!!
   대량의 데이터를 다룰 때, foreign key의 validation 검사 -> 부하 -> 성능 저하
2. 정합성 문제
   기존 데이터들이 Referential Integrity 정합성에 맞지 않아 foreign key를 달 수 없을 수 있다.

---

<참고자료>

• Foreign Key 없이 구축하는 관계형 데이터베이스 시스템에 대한 생각

[5장] InnoDB Record Lock에서 테이블에 인덱스가 없다면?

p.168에선 인덱스가 하나도 없는 테이블이더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정한다 고 하는 반면,
p172에선 이 과정에서 테이블에 있는 30여만 건의 모든 레코드를 잠근다 고 나온다.

인덱스를 이용해 잠그는 것과 모든 레코드를 잠그는 것이 상반되는 뜻인 것 같아 생각해 보았다.

---

클러스터 인덱스
PK를 기준으로 정렬되어 있는 인덱스

책에서 예제로 주어진 employees 테이블에서 first_name과 last_name이 PK가 아니므로 모든 레코드를 일일이 탐색하며 살펴보아야한다는 의미로 이해했다.

[8장] R-Tree 인덱스에서 포함 관계를 비교하는 함수의 효율성

p.256을 살펴보면 ST_Distance()와 ST_Distance_Sphere() 함수는 공간 인덱스를 효율적으로 사용하지 못하기 때문에 ... 라고 한다. 이 문장을 처음 읽었을 때 잘 이해가 되지 않았어서 정리해 두었다.

---

MySQL Reference에 따르면 두 함수는 아래와 같이 정의되어 있다.

• ST_Contains(g1, g2)
  g1이 g2를 완전히 포함할 수 있는가, 없는가를 판단한다.
• ST_Distance(g1, g2 [, units])
  매개변수로 주어진 두 geometry의 각 구성 원소들을 조합하여 나올 수 있는 모든 경우를 고려하고 그 중 가장 짧은 거리를 return한다.

R-Tree는 포함관계를 이용해 만들어진 인덱스이다.
따라서 포함관계를 이용해 비교하는 ST_Contains에서는 효율적으로 사용할 수 있다.
그러나 두 geometry의 거리를 비교하는 ST_Distance는 포함관계를 이용하는 것이 아니므로 R-Tree를 효율적으로 사용할 수 없다.

---

<참고자료>

• Spatial Relation Functions That Use Object Shapes

[5장] Online DDL

p.165에서 메타데이터 잠금과 InnoDB의 트랜잭션을 동시에 사용하는 경우를 살펴보며 Online DDL이 나온다.

---

Online DDL
테이블이 변경되는 동안 SELECT 쿼리와 DML을 계속 처리 할 수 있도록 하는 기능

DML이 빈번하게 일어날 때, 변경 내용을 임시로 저장하는 버퍼의 공간 부족으로 실패할 수 있다.

---

<참고자료>

• MySQL Online-DDL

[기타] PK에 int type 데이터를 사용하는 이유

스터디를 진행하며 관련 자료에 대한 소개를 받았는데, 생각해보면 좋을 것 같다.

---

1. 작은 메모리 공간
   같은 데이터를 표현하기 위해 요구되는 메모리 크기 : int < char
   1개의 page에 저장할 수 있는 index 개수 : int > char
   => 더 적은 양의 page 필요하므로 효율적
2. 데이터 조회시 빠른 비교
   int type은 단순히 수를 비교하여 빠른 비교 가능
   반면, char type은 왼쪽부터 문자를 비교해야함
3. 데이터의 무결성
   char type은 white space도 1byte로 취급
   => 공간 효율성 저하 + 무결성 영향

---

<참고자료>

• 우리는 PK에 왜 int형 데이터를 고집할까