PK 에 의한 클러스터링

InnoDB 테이블 클러스터링

• InnoDB 의 모든 테이블을 PK 를 기준으로 클러스터링되어 저장된다.
• 동작 방식
  • PK 값을 순서대로 디스크에 데이터가 저장
• 장점
  • PK 를 사용하여 레인지 스캔이 빠르게 처리될 수 있음.

세컨더리 인덱스와 관계

세컨더리 인덱스?

• 개요
  • 클러스터링 인덱스 (PK) 를 제외한 나머지 모든 인덱스를 말함.
• 주소로 사용됨
  • 세컨더리 인덱스는 레코드의 물리적 주소 대신 PK 값을 논리적 주소로 사용
• 쿼리 실행 계획에서 우선된다
  • PK 는 다른 보조 인덱스보다 쿼리 실행 계획에서 높은 비중을 가진다.

MyISAM 과의 비교

• 클러스터링 키를 지원한다
  • MyISAM 은 클러스터링 키를 지원 X
• PK vs 세컨더리 인덱스
  • MyISAM 은 차이가 없음
• 인덱스의 주소 값
  • MyISAM 테이블의 모든 인덱스는 물리적인 레코드 주소값(ROWID) 를 가지게 됨.

FK 지원

• MyISAM 이나 MEMORY 테이블에서는 외래 키를 사용할 수 없다
• 외래 키는 InnoDB 에서만 사용이 가능하다

잠금과 데드락

• 외래 키로 인한 잠금이 여러 테이블로 전파가 될 수 있다.
• 데드락 발생 가능성이 있어서 주의가 필요하다.

관리 작업과 외래 키

• 수동 데이터 적재, 스키마 변경 시 실패할 수 있다.
• 외래 키가 복잡하게 얽혀 있으면 작업이 어려움.
  • 그래서 foreign_key_checks  속성을 OFF 하여 외래 키 체크 작업을 실시 중지 가능하다

SET SESSION foreign_key_checks=OFF;
-- 작업 실행
SET SESSION foreign_key_checks=ON;

SESSION 범위 설정을 하지 않는 경우, 전역적으로 적용된다.
왠만하면 SESSON 을 사용하여 작업 시 현재 세션에만 체크 기능을 끄도록 하자.

MVCC ( Multi Version Concurrency Control )

• 개요

  • 레코드 레벨의 트랜잭션을 지원하는 DBMS 기능이다.
  • 잠금 없이 일관된 읽기를 제공한다.

• InnoDB 와 MVCC

  • InnoDB 는 언두로드 ( Undo log ) 를 사용하여 MVCC 를 구현한다.
  • 하나의 레코드에 대해 여러 버전을 동시에 관리하는 거임

• MVCC 동작 방식 + READ_COMMITTED 격리 수준의 기준에서

  1. INSERT 실행

     

     • 데이터가 InnoDB 버퍼 풀과 데이터 파일에 저장된다.

  2. UPDATE 실행

     

     • 커밋 여부와 관계없이 InnoDB 버퍼 풀의 데이터가 새로운 값으로 업데이트
     • 변경전 값은 언두 로그에 복사
     • 데이터 파일은 체크포인트 혹은 InnoDB 의 Write 스레드에 의하여 업데이트가 될 수 있음.
       • 백그라운드 스레드에 의해 기록이 됨.
       • 언제 기록되는 지는 시점에 따라 다름.

  3. 다른 사용자가 변경중인 레코드를 조회하는 경우

     • READ_UNCOMMITTED
       • InnoDB 버퍼 풀의 변경된 데이터를 반환 ( 커밋되지 않은 내용을 반환 )
     • READ_COMMITTED
       • 언두 영역의 변경 이전 데이터를 반환 ( MVCC 동작 )

  4. COMMIT 실행시

     • InnoDB 는 현재 상태를 영구적인 데이터로 만든다.

  5. ROLLBACK 실행시

     • InnoDB 는 언두 영역의 백업 데이터를 InnoDB 버퍼 풀로 복구하고, 언두 영역의 내용을 삭제함.

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

• DB 에서 읽기 작업시, 다른 트랜잭션의 작업에 영향을 받지 앟고 읽는 방법
• MVCC 를 사용하여 잠금 없이 데이터를 일관되게 읽을 수 있다.

잠금 없는 일관된 읽기의 동작 방식

• 읽기 작업
  • SELECT 쿼리 는 다른 트랜잭션의 잠금에 영향받지 않고 실행된다.
• 격리 수준
  • READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ 격리 수준에서는 INSERT와 연결되지 않은 순수한 읽기 작업이 잠금 없이 바로 실행
• 언두 로드의 사용
  • 변경 중 ( 커밋 전 ) 데이터에 대한 읽기 작업이 다른 사용자의 작업을 방해하지 않는다.
  • 변경되기 전의 데이터를 읽기 위해 언두 로드 사용

주의

• 트랜잭션이 오랜동안 활성중인 경우 언도 로그를 유지해야하기에 MySQL 서버가 느려지거나 문제가 발생할 수 있음.

자동 데드락의 감지

데드락?

• DB 에서 두 개 이상의 트랜잭션이 서로의 작업이 끝나기를 무한정 기다리는 상황을 말함.
• 예를 들어, A 와 B 두 트랜잭션이 존재하고, A는 B가 끝나기를, B는 A가 끝나기를 기다리는 상황이 발생하는 경우, 둘다 영원히 기다리게 되는 문제가 생긴다.

InnoDB 자동 데드락 감지

• 잠금 대기 목록

  

  • 트랜잭션이 다른 트랜잭션을 기다리는 상황을 잠금 대기 목록 이라는 그래프 형태로 관리한다고 한다.

• 데드락 감지 스레드

  • InnoDB에는 데드락을 자동으로 감지하는 기능이 있음.
  • 주기적으로 잠금 대기 목록을 검사해서 데드락 상황을 찾고, 발견되면 트랜잭션 중에 하나를 강제로 종료시킨다.

데드락 해결 기준

• 언두 로그의 양
  • 강제 종료할 트랜잭션을 결정할 때, 언두 로그(변경 전 데이터 상태를 기록하는 로그)의 양을 기준으로 합니다.
  • 언두 로그 레코드가 적은 트랜잭션이 롤백 대상이 됩니다.

주의

• 성능 문제
  • 동시에 많은 트랜잭션이 실행되거나,
  • 각 트랜잭션이 많은 잠금을 가지고 있을 때,
    • 데드락 감지 스레드의 성능이 저하될 수 있다.
  • 이는 서비스에 악영향을 미칠 수 있다.
• 구글 같은 경우도 innodb_deadlock_detect 이라는 데드락 감지 스레드를 비활성화하고
  • innodb_lock_wait_timeout 이라고 트랜잭션이 타임아웃 시간내에 수행되지 않는다면, 요청이 자동으로 실패하도록 설정함으로 성능을 최적화하였다고 한다.