인덱스를 설명하기 전에 디스크 읽기 방식을 설명하고 디스크 읽기 방식이 어떻게 인덱스가 어떻게 쿼리에 영향을 미치는지 살펴봅니다. 디스크 읽기 방식 컴퓨터는 전기적 장치와 기계적 장치로 구성됩니다. CPU나 메모리같은 전기적 장치의 성능은 빠르게 발전해왔지만 HDD 같은 기계적 장치의 성능은 상당히 제한적으로 발전했습니다. 최근 SSD같은 전기적 장치를 사용하지만 아직 데이터 저장 매체는 컴퓨터에서 가장 느린 부분입니다. 데이터베이스 서버 입장에서 디스크 I/O가 가장 큰 병목을 가져오고 데이터베이스의 성능 튜닝은 어떻게 디스크 I/O를 줄이느냐가 관건입니다. 랜덤 I/O와 순차 I/O 랜덤 I/O는 하드 디스크 드라이브의 플래터(원판)을 돌려서 읽어야할 데이터가 저장된 위치로 디스크 헤더를 이동시킨 다..
MySQL 5.6 버전부터 데이터 암호화를 지원했다. 처음에는 데이터 파일(테이블 스페이스)에 대한 암호화만 제공했다. MySQL 8.0 버전부터 리두 로그, 언두 로그, 바이너리 로그 등도 암호화 기능을 제공하기 시작했다. MySQL 서버의 데이터 암호화 Transparent Data Encryption MySQL 서버는 디스크 저장(at rest) 단계에서 데이터 암호화를 제공한다. 즉, 스토리지 엔진의 I/O 레이어에서 데이터 암호화 및 복호화를 한다. 결국 InnoDB 버퍼 풀에는 평문 데이터 페이지만 존재하기 때문에 MySQL 서버 및 사용자가 테이블의 암호화 유무를 구별할 필요가 없다. 2 단계 키 관리 MySQL 서버는 데이터 파일 암호화에 마스터 키와 테이블스페이스 키를 사용한다. 테이블스..
데이터가 압축되면 InnoDB 버퍼풀로 읽어야 할 데이터 페이지가 줄어들고 백업 및 복구 시간도 단축된다. 페이지 압축 file-per-table 테이블 스페이스에 저장된 테이블에 제공하는 데이터 페이지 단위 압축이다. MySQL 서버가 디스크에 저장하는 시점에 데이터 페이지를 압축한다. 반대로 디스크에서 데이터 페이지를 읽는 시점에 데이터 페이지를 압축 해제한다. InnoDB와 Disk 간 I/O 시점에 데이터 페이지를 압축, 압축 해제를 한다. 즉, 버퍼 풀의 데이터 페이지는 압축 해제된 상태이다. 그래서 MySQL 서버의 내부 코드는 압축 여부와 관계없이 투명(Transparent)하게 동작한다. 그래서 페이지 압축은 Transparent Page Compression이라고도 불린다. 한 테이블은 ..
잠금 잠금은 동시성을 제어하기 위한 기능이다. 여러 커넥션에서 동시에 동일한 자원(레코드나 테이블)을 요청할 경우 순서대로 한 시점에는 하나의 커넥션만 변경 가능하다. MySQL에서 사용되는 잠금은 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나뉜다. MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 주지만, 스토리지 엔진 레벨의 잠금은 스토리지 엔진간 상호 영향을 주지 않는다. MySQL 서버 글로벌 락 글로벌 락은 MySQL에서 제공하는 잠금 중 가장 범위가 큰 잠금이다. 일단 한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT를 제외한 대부분의 DDL, DML 실행은 대기 상태가 된다. 아래 명령어를 사용하면 글로벌 락을 획득할 수 있다. mysql> FLUSH TABLES WIT..
트랜잭션 트랜잭션은 작업의 완전성을 보장해 주는 것이다. 논리적인 작업 셋이 완전히 적용되거나 아무것도 적용되지 않아야 한다. 트랜잭션이 없다면 작업 셋의 일부만 변경되는 Partial update가 발생해 문제가 생길 수 있다. InnoDB 스토리지 엔진은 트랜잭션을 지원하지만 MyISAM이나 MEMORY 스토리지 엔진은 트랜잭션을 지원하지 않는다. AUTO-COMMIT을 활성화하고 MyISAM 테이블과 InnoDB 테이블을 생성 mysql> SET auto-commit=ON; mysql> CREATE TABLE tab_myisam( fdpk INT NOT NULL, PRIMARY KEY (fdpk) ) ENGINE=MyISAM; mysql> CREATE TABLE tab_innodb( fdpk INT..
어댑티브 해시 인덱스 InnoDB 스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스이다. B-Tree 인덱스 VS 어댑티스 해시 인덱스 어댑티브 해시 인덱스는 B-Tree의 검색 시간을 줄여주기 위해 도입되었다. 일반적인 인덱스는 B-Tree로 되어있다. B-Tree는 데이터 탐색을 위해 Root부터 Branch를 거쳐 Leaf까지 탐색을 해야한다. 하지만 해시 인덱스는 데이터를 즉시 찾아갈 수 있다. 해시 인덱스는 인덱스 키 값과 해당 인덱스 키 값이 저장된 데이터 페이지 주소의 쌍으로 관리된다. 인덱스 키 값은 B-Tree 인덱스의 고유 번호와 B-Tree 인덱스의 실제 키 값 조합으로 생성된다. 즉, 모든 B-Tree 인덱스는 하나의 어댑티브 해시 인덱스에 저장되며, ..
언두 로그 InnoDB 스토리지 엔진은 트랜잭션과 격리수준을 보장하기 위해 DML(INSERT, UPDATE, DELETE)로 변경되기 이전 버전의 데이터를 별도로 백업한다. 백업된 데이터를 언두로그(Undo Log)라 한다. 언두로그가 어떻게 사용되는지 간단히 살펴보자. 트랜잭션 보장 트랜잭션이 롤백되면 트랜잭션 도중 변경된 데이터를 변경 전 데이터로 복구해야 한다. 이때 언두 로그를 사용한다. 격리 수준 보장 특정 커넥션에서 데이터를 변경하는 도중 다른 커넥션에서 데이터를 조회하면 트랜잭션 격리 수준에 맞게 변경 중인 레코드를 읽지 않고 언두로그의 데이터를 읽어서 반환한다. 즉, 격리 수준에 따라 보여주는 데이터가 다르다. 언두 로그 모니터링 대용량 데이터 처리 MySQL 5.5 이전 버전에서는 언두..
InnoDB 버퍼 풀 디스크의 파일이나 인덱스 정보를 메모리에 캐시해두는 공간이다. 쓰기 지연을 위한 버퍼로도 사용된다. DML을 통한 데이터 변경은 디스크의 여러 곳에 저장된 레코드를 변경한다. 이는 디스크의 랜덤 I/O를 발생시킨다. 따라서 쓰기 지연을 통해 랜덤 I/O를 줄여 성능을 향샹시킬 수 있다. 데이터 페이지 InnoDB가 디스크와 데이터를 주고 받는 최소 단위를 데이터 페이지라 한다. 데이터 페이지에는 최소 하나의 행이 포함될 수 있다. 하나의 행이 너무 크다면 다음 페이지를 포인터로 쪼개서 데이터 페이지는 전송한다. 구조 InnoDB 버퍼 풀은 메모리 공간을 페이지 단위로 쪼개서 관리한다. 쪼개진 조각을 관리하기 위해 LRU 리스트, Flush 리스트, Free 리스트라는 3개의 자료구조..
