MySQL(2)
오늘 배운 것
2주차 학습 내용. (4.2 InnoDB 스토리지 엔진 아키텍처 ~ 4.3 MyISAM 스토리지 엔진 아키텍처)
들어가기전에, InnoDB vs MyISAM
| 특징 (Feature) | InnoDB | MyISAM |
|---|---|---|
| 트랜잭션 (Transactions) | 지원 (ACID 준수) COMMIT, ROLLBACK, Savepoint을 통한 데이터 일관성 및 무결성 보장 |
미지원 실행된 쿼리는 즉시 반영되며, 롤백 불가 |
| 잠금 단위 (Locking Granularity) | 행 레벨 잠금 (Row-Level Locking) 데이터 변경 시 해당 행만 잠그므로 동시성 처리에 매우 유리 |
테이블 레벨 잠금 (Table-Level Locking) 데이터 변경 시 테이블 전체가 잠겨 동시성이 떨어짐 (읽기/쓰기 충돌) |
| 외래 키 (Foreign Keys) | 지원 참조 무결성을 통해 여러 테이블 간의 데이터 관계를 보장 |
미지원 테이블 간의 관계를 보장하지 않음 |
| 기본 키 (Primary Key) | 클러스터형 인덱스 (Clustered Index) 기본 키 순서대로 데이터가 물리적으로 정렬되어 저장됨 |
클러스터형 인덱스 미지원 데이터는 삽입 순서대로 저장되고, PK는 별도의 인덱스로 관리 |
| 충돌 복구 (Crash Recovery) | 지원 트랜잭션 로그를 사용하여 서버 비정상 종료 시 데이터 자동 복구 가능 |
미지원 서버 비정상 종료 시 테이블이 손상될 수 있으며, 수동 복구( REPAIR TABLE)가 필요할 수 있음 |
| 백그라운드 스레드 | 사용 Master, I/O, Purge, Page Cleaner 등 다수의 백그라운드 스레드가 내부 작업을 비동기 처리하여 성능 향상 |
미사용 전용 백그라운드 스레드 아키텍처가 없으며, 대부분의 작업을 포어그라운드 스레드가 직접 처리 |
| 데이터 캐시 | 데이터와 인덱스 모두 캐시 버퍼 풀(Buffer Pool)에 데이터와 인덱스를 함께 캐시하여 I/O 성능 향상 |
인덱스만 캐시 키 버퍼(Key Buffer)에 인덱스만 캐시함. 데이터는 OS 캐시에 의존 |
| 기본 엔진 여부 | MySQL 5.5부터 기본 스토리지 엔진 | MySQL 5.5 이전 버전의 기본 엔진 |
| 주요 사용 사례 | 데이터 무결성이 중요하고, 읽기와 쓰기가 빈번한 대부분의 애플리케이션 (OLTP) | 현재는 거의 사용되지 않음. 과거에는 읽기 전용 또는 로그 테이블 등에 제한적으로 사용 |
| 전문 검색 (Full-text Search) | MySQL 5.6부터 지원 | 지원 |
4.2 InnoDB 스토리지 엔진 아키텍처
4.2.1 Primary Key에 의한 클러스터링 (영어사전같이 잘 모아준다)
-
용어 정리
클러스터링인덱스 = 데이터가 테이블에 물리적으로 저장되는 순서를 정의하는 인덱스, PK를 기준으로 데이터를 정렬시키고, 이 경우 B+-Tree에서 리프노드가 데이터 페이지(RowID)를 직접 가리킨다. 데이터를 정렬시키므로 테이블당 1개만 생성할 수 있다. 빠른 Read와 느린 update
논 클러스터링 인덱스 = 세컨더리 인덱스 = 실제 데이터와 함께 묶여있지 않는 인덱스, 별도의 인덱스 페이지 저장공간에서 실제 데이터 페이지를 포인터로 가리킨다. 대상 데이터파일은 정렬되지 않는다. 리프노드는 PK값을 가지고 있는다.
PK값과 ROWID의 차이.
| 구분 | 기본 키 (Primary Key) | ROWID |
|---|---|---|
| 개념 | 논리적 (Logical) | 물리적 (Physical) |
| 정의 주체 | 사용자/개발자가 직접 지정 | 데이터베이스 시스템(DBMS)이 내부적으로 관리 |
| 불변성 | 불변(Immutable). 한번 정해지면 바뀌지 않는 것이 원칙 | 가변(Mutable). 데이터 위치가 바뀌면 언제든 변경 가능 |
| 역할 | 데이터의 무결성 보장, 행(Row)의 고유 식별자 | 데이터가 디스크에 저장된 물리적인 주소값 |
| 비유 | 사람의 주민등록번호 | 사람이 사는 실거-주지 주소 |
InnoDB에서는 모든 테이블은 기본적으로 프라이머리 키를 중심으로 클러스터링 되어 저장된다. 즉, 프라이머리 키 값 순서대로 디스크에 저장되고, 모든 세컨드리 인덱스는 레코드의 주소 대신 PK 값을 논리적인 주소로 사용한다. InnoDB에서 추가로 인덱스를 생성한다면, 다음과 같은 형태로 인덱스가 관리된다.
8절 내용. 인데스 데이터와 테이블 데이터는 별도로 관리된다. InnoDB는 기본으로 B-Tree로 관리하기 때문에 insert마다 클러스터링 되어 데이터가 저장된다.
B-Tree의 루트노드부터 리프노드까지 구성이 되는데, 리프노드는 실제 테이블 데이터로의 포인터를 저장한다. B-Tree인덱스의 키 값은 모두 정렬되어있으나, (InnoDB가 아닌)데이터 파일 레코드는 정렬되어 있지 않다. 그러나 InnoDB의 경우, 테이블은 레코드는 클러스터되어 저장되므로 기본적으로 PK순서로 정렬되어 저장된다.
8.8절 내용과 연관하여, Mysql서버에서 key와 index는 동의어로 사용되어 cluster index==key라고도 한다. PK에 의해 레코드 저장위치가 결정된다(비슷한거끼리 묶기) 따라서 이와 동시에 PK값이 변경된다면 레코드의 물리적인 저장위치도 변경된다. 따라서 PK로 클러스터링된 테이블은 PK의존도가 크므로 PK를 잘 골라야 한다. 사실 클러스터링 인덱스는 인덱스 방식이라기보단 테이블 레코드 저장방식이라 볼 수 있다. 그래서 클러스터링 인덱스== 클러스터링 테이블 이라고도 여긴다.
InnoDB
- 모든 테이블은 PK 를 기준으로 클러스터링되어 저장된다. (기본이 클러스터링 인덱스)
- 모든 세컨더리 인덱스의 리프는 PK값을 지닌다.
MyISAM
- 클러스터링 키를 지원안한다. PK와 세컨더리 인덱스는 구조적으로 차이가 없다. ROWID선정 자체를 insert될때마다 하므로 묶이지가 않는다.
- PK를 포함한 모든 인덱스는 물리적인 레코드의 주소값(ROWID)를 가진다.
InnoDB vs MyISAM
읽기
MyISAM은 인덱스 조회 1회 + 포인터 1회 로 바로 데이터 접근 가능.
InnoDB는 세컨더리인덱스 탐색 1회 + 클러스터링인덱스 1회 (클러스터링 인덱스의 리프가 실제 데이터)
그러나 PK조회, PK의 범위 조회는 데이터가 이미 정렬된 InnoDB가 압승
InnoDB의 버퍼 풀은 데이터 페이지와 인덱스 페이지를 모두 캐시함. MyISAM은 인덱스 페이지만 캐시함. 캐시효율은 InnoDB가 유리함.
쓰기
- 책 내용상 유추해보면 update 발생시 ROWID가 변경되어 모든 인덱스에서 ROWID를 변경해야해서 MyISAM 이 불리하다.
- 거기에 더해서 MyISAM은 테이블단위 LOck을 걸어서 더 불리하다.
- InnoDB는 클러스터링 인덱스 만 바뀐다.
4.2.2 Foreign Key 지원 (데이터 참조 무결성 지원, )
MyISAM이나 MEMORY는 지원하지 않는다.
4.2.3 MVCC(Multi Version Concurrency Control) (구글독스 버전기록처럼 여러 사용자가 동시에 데이터 접근해도 서로 방해하지 않고 필요한 버전의 데이터 보게 하기)
레코드 레벨의 트랜잭션 지원하기. 언두로그 를 통해 지원한다. MySQL 서버의 시스템 변수 transaction_isolation의 값에 따라 작업중인 레코드 중 뭘 보여줄지 결정된다.
4.2.4 잠김 없는 일관된 읽기 (Non Locking Consistent Read) (write가 일어나는 중에도 Lock 안걸고 read하기)
격리수준에 따라 언두로그를 활용해 Reader가 읽을 수 있다.
4.2.5 자동 데드락 감지 (교착상태 해결하기)
4.2.6 자동화된 장애 복구 (작업기록-리두로그를 보고 되살리기)
4.2.7 InnoDB버퍼 풀 (DB의 작업책상, 창고-디스크까지 가지않고 올려두고 빠르게 작업)
디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간이다. 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 한다.
-
4.2.7.1 버퍼풀의 크기 설정
-
4.2.7.2 버퍼 풀의 구조
InnoDB는 버퍼 풀이라는 거대한 메모리 공간을 페이지 크기(innodb_page_size 시스템 변수)의 조각으로 쪼개어 필요할때 각 데이터 페이지를 읽어서 각 조각에 저장한다. 버퍼 풀의 페이지 크기 조각을 관리하기 위해 LRU리스트, Flush리스트, Free 리스트라는 3개의 자료구조를 관리한다.
-
Free 리스트 = 버퍼 풀에서 실제 사용자 데이터로 채워지지 않은 비어있는 페이지들의 목록, 쿼리가 새로 디스크 데이터 페이지를 읽어와야 하는 경우 사용된다.
-
4.2.7.3 버퍼 풀과 리두 로그
-
4.2.7.4 버퍼 풀 플러시 (책상정리, 책상위에서 끝난 작업물을 창고-디스크로 옮긴다.)
-
4.2.7.4.1 플러시 리스트 플러시
-
4.2.7.4.2 LRU 리스트 플러시
-
-
4.2.7.5 버퍼 풀 상태 백업 및 복구 (퇴근시 책상상태를 사진으로 찍어두면 다음 출근시 그대로 복원 가능)
-
4.2.7.6 버퍼 풀의 적재 내용 확인
4.2.8 Double Write Buffer (중요 서류를 금고에 넣기 전에 복사본 만들기)
4.2.9 언두 로그 (수정 전 원본 데이터 보관하는 노트, MVCC에 이용됨)
-
4.2.9.1 언두 로그 레코드 모니터링
-
4.2.9.2 언두 테이블스페이스 관리
4.2.10 체인지 버퍼 (나중에 한번에 처리할 메모장, 일단 메모장에 적어뒀다가 나중에 그 페이지 갈 일 생기면 메모쌓은걸 한번에 반영)
4.2.11 리두 로그 및 로그 버퍼 (작업일지-리두로그와 그 임시 메모지)
-
4.2.11.1 리두 로그 아카이빙
-
4.2.11.2 리두 로그 활성화 및 비활성화
4.2.12 어댑티브 해시 인덱스 (똑같은 데이터를 자주 보면, 전용 지름길-해시인덱스 만들기)
일반적으로 여기서 인덱스 라고 하면 B-Tree인덱스를 의미한다. 근데 이거는 사용자가 수동으로 만든 인덱스가 아니라 자동으로 생성되는거다. innodb_adaptive_hash_index 시스템 변수로 이 기능을 on/off 할 수 있다.
일반적으로 B-Tree 인덱스 검색은 트리이므로 빠를거라고 많이들 생각하지만 사실 이건 상대적인 거고, DB서버가 얼마나 많은 일을 하냐에 따라 B-Tree인덱스에서 값을 찾는게 빨라질수도, 느려질수도 있다. 이런 작업을 몇천개 스레드로 실행하면 CPU 는 엄청난 스케줄링을 하게되고 쿼리 성능이 떨어진다.
AHI(내가 만듬)는 이걸 위한 기능이다. 자주 읽히는 데이터 페이지 키 값을 이용해 해시 인덱스를 만들고 필요할때마다 검색해서 즉시 간다. B-Tree 루트-리프 비용이 줄어든다. 기본적으로 하나의 메모리 객체라서 경합이 심했다. 그러나 8.0부터는 AHI의 파티션기능이 제공된다. AHI의 장단점은 다음과 같다.
도움 안되는 경우
- 디스크 읽기가 많은 경우
- 특정 패턴의 쿼리가 많은 경우(Join, Like 패턴 검색)
- 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우
도움 되는 경우
- 디스크 데이터가 InnoDB 버퍼 풀 크기와 비슷한 경우(디스크 읽기가 많지 않은 경우)
- 동등 조건 검색(동등 비교와 IN 연산자)이 많은 경우 - 아마 해시니까 그런듯
- 쿼리가 데이터중에서 일부 데이터에만 집중되는 경우
AHI라는건 결국 데이터 페이지를 메모리(버퍼 풀)내에서 접근하는 것을 더 빠르게 하는거라서 디스크 I/O는 의미가 없다. 또, 이건 Free Launch가 아니다. 메모리를 쓰고, 이게 활성화 되면 InnoDB는 키 값이 인덱스에 있든 없든 어쨌건 한번 거쳐야 한다. 또 테이블의 삭제나 변경이 일어나면 이 해시를 또 전체를 뒤져봐야해서 불리하다.
AHI가 잘 되고있는지 아닌지를 볼때 이런걸로 통계를 볼 수 있다.
SHOW ENGINE INNODB STATUS\G
...
1.03 hash searches/s, 2.64 non-hash searches/s
총 1.03+2.64 중 1.03이 hit면 약 28%인데, 이정도면 다소 아쉽다.
4.2.13 InnoDB vs MyISAM vs MEMORY 비교
4.3 MyISAM 스토리지 엔진 아키텍처
4.3.1 키 캐시
4.3.2 운영체제의 캐시 및 버퍼
4.3.3 데이터 파일과 프라이머리 키(인덱스) 구조
클러스터링 없이 데이터파일이 Heap공간처럼 활용된다. 레코드는 PK값과 무관하게 Insert되는 순서대로 데이터 파일에 저장된다. 레코드는 모두 ROWID라는 주솟값을 가지는데, PK와 세컨더리 인덱스는 모두 ROWID값을 포인터로 가진다.