https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html
클러스트링 인덱스, 세컨더리 인덱스
- MYSQL 서버에서 클러스트링은 테이블의 레코드를 비슷한 것(보통 프라이머리 키를 기준으로)들끼리
페이지로 묶어서 저장
이는 지역성의 원리에도 부합하는 것을 의미입니다. https://en.wikipedia.org/wiki/Locality_of_reference - 키값이 물리적인 위치에 영향을 주는 방식
- 클러스트링 인덱스는 InnoDB에서만 지원하고 나머지 스토리지 엔진에서 지원하지 않음(MyISAM Storage Engine의 PK는 물리적위치가 고정적)
일반적으로 클러스트링 인덱스로 저장되는 테이블은 프라이머리 키 기반의 검색이 매우 빠름
대신 저장이나 프라이머리 키의 변경이 상대적으로 느립니다.
MyISAM Engine이나 InnoDB Engine을 제외한 Storage Engine들은
프라이머리 키나 인덱스 키값을 변경한다고 해서 데이터 레코드의 실제 물리적인 위치가 변경되지는 않습니다.
Insert될 때 데이터의 끝에 저장되며 한번 결정된 위치는 변경되지 않으며 저장한 주소(ROW-ID)를 사용하게 됩니다.
InnoDB의 키 변환
InnoDB는 키의 물리적 위치도 함께 변경됩니다.
InnoDB의 경우 물리적인 주소가 변경되므로 세컨더리 테이블은 클러스터링 테이블의 논리적 주소를 참조합니다.
물리적인 주소를 참조한다면 변경이 생기면 참조하는 세컨더리 테이블의 참조 위치가 모두 변경되어야 합니다.
추가적인 주의사항
클러스트링 테이블의 경우 모든 세컨더리 인덱스가 프라이머리 키값을 포함합니다.
즉 프라이머리 키의 크기가 커지면 모든 세컨더리 테이블의 레코드도 자동으로 커집니다.
프라이머리 키 크기가 10Byte인 경우 10Byte X 세컨더리 테이블의 수 X 레코드의 수
100만 건 기준으로 47MB를 차지합니다.
실제 업무에서는 테이블의 개수가 월등히 많으므로 주의가 필요합니다.
멀티 밸류 인덱스
기본적으로 전문 검색 인덱스를 제외한 모든 인덱스는 레코드 1건이 1개의 인덱스 키값을 가집니다.(인덱스와 레코드는 1:1 관계)
- 멀티 밸류 인덱스는 하나의 데이터 레코드가 여러 개의 키 값을 가질 수 있는 형태(원래는 정규화에 위배)
- JSON 타입만 지원하는 MySQLDMS MongoDB와 많이 비교, 하지만 8.0부터 배열 형태의 인덱스도 지원하고 MongoDB와 차이가 줄어듬ㄷ
INSERT INTO user VALUES (1, 'Matt', 'Lee', '{"credit_scores":[360, 353 351]}')
유니크 인덱스
유니크는 인덱스 + 제약 조건에 가깝다.
말그래도 동일한 값 2개 이상 저장될 수 없음을 의미하며 MySQL에서는 인덱스 없이 유니크 제약만 설정할 방법이 없습니다.
❓ Null도 저장될 수 있는데, Null은 특정 값이 아니므로 2개 이상 저장될 수 있습니다.
😎 그렇다면 세컨더리 인덱스 테이블과의 차이는?
유니크 인덱스와 유니크하지 않은 일반 세컨더리 인덱스는 구조상 아무런 차이점이 없습니다.
조회부분에서도 성능적인 큰 차이가 없으며 변경 부분에서는 일반적인 세컨더리 인덱스에서는 성능을 위해 체인지 버퍼를 활용해 I/O요청을 아끼지만 유니크 인덱스의 경우 디스크의 값에 중복이 있는지 확인해야 하기 때문에 버퍼링을 할 수 없습니다.
게다가 유니크 인덱스에서 중복된 값을 체크하기 위해 읽기 잠금을 사용됩니다.
이러한 이유들 때문에 인덱스를 변경하는 부분에서는 일반 세컨더리 인덱스보다
느리게 동작을 합니다.
외래키
MySQL에서 외래키는 InnoDB만 생성할 수 있습니다.
외래키 제약이 설정되면 자동으로 연관되는 테이블의 컬럼에 인덱스까지 설정됩니다.
외래키 관리의 중요한 두 가지 특징
- 테이블의 변경(쓰기 잠금)이 발생하는 경우에만 잠금 경합(잠금 대기)이 발생
- 외래키와 연관되지 않은 컬럼의 변경은 최대한 잠금 경합(잠금 대기)을 발생시키지 않는다.
자식 테이블의 변경을 대기, 부모 테이블이 변경을 대기 2가지 경우가 존재합니다.
자식 테이블이 대기
Session1 : Begin;
Session1 : UPDATE tb_parent SET fd='changed-2' WHERE id=2;
[Session1이 쓰기잠금 획득]
Session2 : UPDATE tb_child SET pid=2 WHERE id=100;
부모 테이블이 대기
Session1 : Begin;
Session1 : UPDATE tb_child SET fd='changed-100' WHERE id=100;
[Session1이 쓰기잠금 획득]
Session2 : DELETE FROM tb_parent WHERE id=1;Q. 책에서는 Cascade 때문에 잠금이 걸린다고 하지만 걸지않아도 잠금이 걸린다.. 왜지?
실제 발생한 외래키 Lock 전파에 관한 포스팅
https://tecoble.techcourse.co.kr/post/2022-11-01-mysql-dead-lock/
