프라이머리 키에 의함 클러스터링
InnoDB의 모든 테이블은 기본적으로 프라이머리 키를 기준으로 클러스터링 되어 저장됨
키 값의 순서대로 디스크에 저장됨
모든 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용됨
레인지 스캔은 빨리 처리됨
쿼리의 실행 계획에서 프라이머리 키는 기본적으로 다른 보조 인덱스에 비해 비중이 높게 설정
InnoDB 스토리지 엔진과는 달리 myISAM 스토리지 엔진에서는 클러스터링 키를 지원하지 않음
외래키 지원
외래키는 데이터베이스 서버 운영의 불편함 때문에 서비스용 데이터베이스에서는 생서하지 않는 경우도 있음
개발 환경에서는 좋은 가드 역할을 하기도 함
외래키는 부모 자식 테이블의 인덱스 생성이 필요하고, 데이터 유무를 체크 하기 때문에 잠금 및 데드락이 발생 할 수 있어서 주의가 필요함
외래키로 인한 데이터 수정 및 삭제 잠금 해제하는 쿼리
SET foreign_key_checks = OFF;
데이터 일관성을 다시 맞춰주고 외래키를 다시 사용해야 함
MVCC(Multi Version Concurrency Control)
일반적으로 레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능
MVCC는 잠금을 사용하지 않는 일관된 읽기를 제공함
InnoDB는 언두로그를 이용하여 MVCC를 구현
UPDATE문장이 실행되면 커밋 실행여부와 관계없이 InnoDB의 버퍼풀은 새로운 값으로 업데이트 함
작업중인 레코드를 조회 하면 어떻게 보여짐?
SELECT * FROM member WHERE m_id = 12;
MySQL 서버의 시스템 변수에 설정된 격리 수준(Isolation Level)에 따라 다름
격리수준
READ_UNCOMMITTED
InnoDB 버퍼풀이 현재 가지고 있는 변경된 데이터를 읽어서 반환함
커밋이 됐든 아니든 변경된 상태의 데이터를 반환
READ_COMMITTED or 그 이상 수준
커밋되지 않았기 때문에 InnoDB버퍼풀이나 데이터 파일에 있는 내용 대신 변경되기 이전의 내용을 보관하고 있는 언두영역의 테이터를 반환
이 과정을 DBMS MVCC라고 함
트랜잭션이 길어지면 예전데이터가 삭제되지 못하고 언두로그에 오랫동안 남아있게 되면서 불필요한 공간이 늘어나는 상황이 생길 수 있음
COMMIT 명령을 실행하면 InnoDB는 더이상의 변경 작업 없이 지금의 상태를 영구적인 데이터로 만듦
롤백을 실행하면 InnoDB의 언두로그에 있는 백업된 데이터를 버퍼풀로 복구 하고 언두로그를 삭제
잠금없는 일관된 읽기
InnoDB 스토리지 엔진은 MVCC 기술을 이용해 잠금을 걸지 않고 읽기 작업이 가능함
잠금을 걸지 않기 때문에 InnoDB에서 읽기 작업은 다른 트랜잭션이 가지고 있는 잠금을 기다리지 않고 읽기 작업이 가능함
격리 수준이 SERIALIZABLE이 아닌 다른 수준에서는 INSERT와 경결되지 않은 순수한 읽기 작업은 다른 트랜잭션의 변경 작업과 관계없이 항상 잠금을 대기하지 않고 바로 실행됨
특정 사용자가 레코드를 변경하고 아직 커밋하기 전 상태일때 다른 사용자가 SELECT 작업을 방해하지 않음
이를 잠금 없는 일관된 읽기 라고 함
InnoDB에서 변경되기 전의 데이터를 읽기 위해 언두로그를 사용함
트랜잭션이 커밋이나 롤백 적용으로 마무리 되어야 언두로그가 삭제됨(불필요공간 정리)
자동 데드락 감지
InnoDB 스토리지 엔진은 내부적으로 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프(Wait-for List)형태로 관리함
데드락 감지 스레드가 주기적으로 잠금 대기 그래프를 검사해 교착 상태에 빠진 트랜잭션들을 찾아서 그 중 하나를 강제 종료함
어떤 트랜잭션을 먼저 종료할지는 언두로그의 양이고, 언두로그 레코드를 적게 가진 트랜잭션이 일반적으로 롤백의 대상이 됨
언두로그 레코드가 적으면 롤백해도 처리할 양이 적어 서버의 부하가 적게감
innodb_table_locks 시스템 변수를 활성화 하면 InnoDB 스토리지 엔진 내부의 레코드 잠금뿐만 아니라 테이블 레벨의 잠금까지 감지 할 수 있게 됨(가능하면 innodb_table_locks을 활성화 하자)
데드락 감지 스레드는 잠금 목록을 검사해야 하기 때문에 잠금 상태가 변경되지 않도록 잠금 목록이 저장된 리스트에 새로운 잠금을 걸고 데드락 스레드를 찾음
자동화된 장애 복구
MySQL 서버가 시작될때 완료되지 못한 트랜잭션이나 디스크 일부에 기록된 데이터 페이지 등 일련의 복구 작업이 자동으로 진행됨
MySQl 서버가 시작될 때 항상 자동복구를 수행함
MySQL 서버가 기동되고 InnoDB 테이블이 인식된다면 mysqldump를 이용해 데이터를 가능한 만큼 백업하고 그 데이터로 MySQL 서버의 DB와 테이블을 다시 생성하는 것이 좋음
위 방법을 써도 복구가 안되면 백업을 이용해서 다시 구축하는 방법뿐임
InnoDB 복구를 이용하는 것보다 풀 백업과 바이너리 로그로 복구하는 편이 데이터 손실이 저 적을 수 있음
InnoDB 버퍼 풀
Innodb 스토리지 엔진에서 가장 핵심적인 부분
디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간
버퍼풀 크기 설정
MySQL 5.7이상 부터 InnoDB버퍼풀의 크기를 동적으로 조정할 수 있게 됨
가능하면 InnoDB 버퍼풀의 크기를 적절히 작은 값으로 설정해서 조금씩 상황을 봐가면서 증가시키는것이 좋음
운영체제의 전체 메모리 공간이 8GB 미만이라면 50%만 버퍼풀로 설정, 나머지 메모리 공간으로 MySQL 서버와 운영체제, 다른 프로그램 공간으로 설정하면 좋음
InnoDB 버퍼풀은 innodb_buffer_pool_size 시스템 변수로 크기를 설정 가능하고 동적으로 버퍼풀 크기를 확장 가능함
버퍼풀 크기 변경은 크리티컬한 변경이므로 한가한 시점에 해야함
버퍼풀의 구조
InnoDB 스토리지 엔진은 크게 LRU 리스트, 플러시 리스트, 프리 리스트 3개의 자료구조를 관리함
LRU 리스트를 관리하는 목적은 디스크로 부터 한번 읽어온 페이지를 최대한 오랫동안 InnoDB버퍼풀 메모리에 유지해서 디스크 읽기를 최소화 하는 것