대략적인 구조는 위 그림과 같다.
InnoDB의 모든 테이블은 기본적으로 프라이머리 키를 기준으로 클러스터링 되어 저장된다. 즉, PK 값의 순서대로 디스크에 저장된다 또한 모든 세컨더리 인덱스(보조키)는 레코드의 주소 대신 PK의 값을 논리 주소로 사용한다.
따라서 PK를 이용한 인덱스 레인지 스캔은 상당히 빨리 처리된다. 때문에 실행 계획을 새울때 옵티마이저는 PK를 다른 세컨더리 인덱스에 비해 더 높은 우선순위를 두고 선택하려고 한다.
이런 구조를 오라클에서는 IOT(Index Organized Table)이라고 하는데, InnoDB에서는 특별한게 아니고 일반적인 구조이다.
외래키는 MyISAM이나 MEMORY 엔진에서는 지원하지 않는다. 오직 InnoDB에서만 지원한다. 사실 FK는 운영하기 불편해서 생성하지 않는 경우도 자주 있다. 그렇다 하더라도 적어도 개발 환경에서는 좋은 가이드 역할을 한다.
InnoDB에서 외래 키는 부모 테이블과 자식 테이블 모두 해당 칼럼에 인덱스 생성이 필요하고, 잠금이 여러 테이블로 전파되며 그로 인해 데드락이 발생할때가 많다. 따라서 개발할때도 주의해야 한다.
또한 키가 복잡하게 얽혀있는 경우 수동으로 데이터를 적재하거나 스키마를 변경하는 작업이 실패하기 쉽다. 이럴때는 foreign_key_checkes 시스템 변수를 꺼서 임시적으로 외래 키 관계에 대한 체크 작업을 멈출 수도 있다.
이 기능을 중지한 상태에서 뭔가 작업했다면 반드시 정합성을 맞춰준 후에 이 기능을 다시 활성화 해야 한다. 기능이 중지되면 캐스케이딩 옵션도 비활성화되기 때문에 직접 정합성을 맞처줘야 한다.
MVCC의 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기 제공이다. 레코드 레벨의 트랜잭션을 지원하는 DBMS는 대부분 다 제공한다.
InnoDB는 언두 로그(Undo log)를 이용해 이 기능을 구현한다. Multi Version이란 말은 하나의 레코드에 대해 여러 개의 버전이 동시에 관리된다는 의미이다.
동시에 관리되는 레코드의 여러 개의 버전중 무엇이 조회될까? 이건 트랜잭션 격리 수준을 어떻게 해놨냐에 따라 다르다. READ_UNCOMMITTED 인 경우에는 InnoDB 버퍼 풀이 현재 갖고 있는 변경된 데이터를 읽어서 반환한다. 즉 커밋이 안되었어도 변경된 상태라면 이걸 반환해준다.
한편 READ_COMMITEED 나 그 이상의 격리수준일 경우에는 버퍼 풀이나 데이터 파일에 있는 내용이 아니라, 변경되기 이전의 내용을 보관하고 있는 언두 영역의 데이터를 반환한다. 이렇게 레코드의 내용을 여러 버전으로 갖고 있어서 Multi Version이라고 함.
UPDATE 쿼리가 실행되면 버퍼 풀은 즉시 새로운 데이터로 변경되고, 기존 데이터는 언두 영역으로 복사된다. 이 상태에서 COMMIT 하면 지금의 상태를 영구적인 데이터로 만들어버린다. 하지만 ROLLBACK 하면 InnoDB는 언두 영역에 있는 데이터를 버퍼 풀로 다시 복구하고, 언두 영역의 내용을 삭제해버린다.