MySQL - InnoDB & MyISAM 스토리지 엔진 아키텍처

MySQL 5.5이후부터 InnoDB엔진이 기본엔진으로 채택 MySQL 5.5까지 MySQL 시스템 테이블은 MyISAM MySQL 8.0이후부터는 모두 InnoDB 엔진 사용, MyISAM에서만 제공하던 전문검색, 좌표검색도 InnoDB에서 제공

InnoDB 스토리지 엔진 아키텍처

https://dev.mysql.com/doc/refman/8.0/en/innodb-architecture.html

• 레코드를 PK 기준으로 정렬하여 디스크에 데이터 저장
• PK를 통해서만 레코드 접근 가능
• PK인덱스 자동 생성
• PK를 통한 범위 검색이 빠름
• InnoDB는 PK를 설정하지 않으면 내부적으로 PK를 자동 생성하여 클러스터링
  • 단, 자동으로 생성된 PK는 사용자가 사용이 불가

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

• MVCC(Multi Version Concurrency Control)
  
  • Undo영역을 이용하여 하나의 레코드에 대해 다양한 버전이 관리됨
  • 레코드 단위의 트랜잭션 지원
    
    1. 데이터 COMMIT시 새롭게 생성된 데이터는 InnoDB Buffer Pool에 들어간다.
    2. id = 1의 hobby 칼럼을 update 하면 변경 전의 칼럼 값은 undo log에 복사된다.
    3. 트랜잭션 격리 수준마다 조회 결과가 다르게 됨.
  • 트랜잭션 격리 수준 글 참고 - 2022.10.19 - [DB] - Transaction Isolation 레벨

잠금 없는 일관된 읽기(Non-Locking Consistent Read)

InnoDB에서는 MVCC특징 때문에 읽기 작업은 다른 트랜잭션이 가진 Lock을 기다리지 않고 읽기 작업이 가능

격리수준이 Serializable이 아닌경우 select작업은 다른 트랜잭션 변경 상관없이 대기하지 않고 작업을 바로 수행

Undo영역 & Redo영역

Undo로그 영역	Redo로그 영역
변경되기 이전의 데이터 백업	COMMIT완료된 데이터 백업
트랜잭션 보장 (ROLLBACK 시 Undo영역의 데이터 복원)	서버 비정상 종료 시 Redo로그에 백업된 데이터 복원
트랜잭션 격리 수준에 맞게 백업된 데이터 변환

• MySQL 5.5버전까지는 Undo로그 영역의 사용 공간이 한번 늘어나면 MySQL 서버를 새롭게 구축하지 않는 이상 줄일 수 없음
• 8.0 버전으로 업그레이드되면서 MySQL 서버가 필요한 시점에 사용 공간을 자동으로 줄여준다.

InnoDB Buffer Pool

• InnoDB 스토리지 엔진의 핵심 부분
• MySQL5.7 이후부터는 InnoDB 버퍼 풀 사이즈 동적으로 적용 가능

InnoDB Buffer Pool 역할

1. 데이터 캐싱
   • 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐싱하는 공간
   • SQL 요청 결과를 일정 크기 단위로 캐싱 -> INDEX, 테이블 데이터 캐싱
   • InnoDB는 일정 크기로 캐싱한 단위 페이징 시 LRU알고리즘 사용
2.  쓰기 지연 버퍼
   • Dirty Page : INSERT, UPDATE, DELETE명령으로 변경된 페이지
   • InnoDB는  랜덤 I/O를 줄이기 위해 생성된 Dirty Page를 모았다가 한 번에 디스크에 반영 -> Buffer역할
3. Adaptive Hash Index
   1. 인덱스 키, 페이지 주소 값으로 구성된 인덱스
   2. 클라이언트가 자주 요청하는 값에 대해 InnoDB가 인덱스 자동 생성
   3. innodb_adaptive_hash_index시스템 변수를 이용하여 자동생성 여부 활성화 및 비활성화 가능

Buffer Pool 구조

버퍼풀의 메모리 공간을 페이지 크기(시스템 변수에 저장된 값 : innodb_page_size)의 조각으로 쪼개 저장

버퍼 풀 페이지 관리 시 아래와 같은 자료구조로 관리

1. LRU(Least Recently Used) 리스트
2. 플러시(Flush) 리스트
3. 프리(Free) 리스트

---

MyISAM 스토리지 엔진 아키텍처

https://velog.io/@jsj3282/5.-MyISAM-%EC%8A%A4%ED%86%A0%EB%A6%AC%EC%A7%80-%EC%97%94%EC%A7%84-%EC%95%84%ED%82%A4%ED%85%8D%EC%B2%98

• InnoDB에서는 모든 테이블이 PK로 클러스터링 되지만, MyISAM에서는 클러스터링 키 지원하지 않음  
  • MyISAM에서는 레코드가 PK와 상관없이 insert작업 때마다 순서대로 데이터 파일에 저장된다.
  • 레코드는 ROWID라는 물리적 주 솟값을 가지며, PK와 세컨더리 인덱스 모두 데이터 파일에 저장된 레코드의 row Id값을 포인터로 가짐
• 테이블 단위 LOCK
• 클러스터링, 트랜잭션, 외래 키 지원하지 않음
• MyISAM의 키 캐시는 인덱스만 캐싱
• 전문 검색, 좌표 검색 지원