Real MySQL 8.0 (1)

외래 키

서버 환경에서는 외래키 관리의 어려움 때문에, 잘 안쓰고 애플리케이션에서 체크한다고 함.

트랜잭션 관리

'InnoDB 버퍼 풀'에 변경 사항 저장, '언두 로그'에 변경 전 값 저장
일반적으로 데이터 파일(디스크)는 ACID 유지로 변경 값 저장
격리 수준에 따라, 변경 전 값을 읽거나 변경 후 값을 읽음.(read committed, read uncommitted)

Adaptive hash index

B+ 트리 인덱스에서 자주 접근 되는 페이지 키값에 해쉬 인덱스를 생성하여, 빠른 접근
디스크 읽기가 적고, 동등 조건 검색 때, 유용
디스크 읽기가 많을 때나, join, like 같이 패턴 검색에는 큰 도움이 안됨.
- 디스크 접근은 해쉬가 아님. InnoDB 버퍼 풀 접근과 상관 있는 것.
하지만 여러 서비스가 같은 해쉬 인덱스를 접근할 때, lock 경합이 발생할 수 있음.
- 경합을 줄이기 위해 인덱스 파티션 기능도 제공함.

특히 단일 랜덤 키 접근이 빈도있게 발생하는 경우라면, B-Tree 를 통하지 않고 데이터에 접근/처리가 가능하기에 좋은 퍼포먼스를 보입니다.

그러나, 자주 사용되는 데이터를 옵티마이저가 판단하여 해시 키로 만들기 때문에 제어가 어려우며, 수 개월 동안 사용되지 않던 테이블일지라도 기존 해시 자료 구조에 데이터가 남아 있게 되면, 테이블 Drop 시 영향을 줄 수 있습니다. 해시 인덱스에 의존하여 트래픽이 주로 처리되는 서비스인 경우 이런 점을 염두해 두고 사용을 해야겠습니다.

출처: MySQL InnoDB의 Adaptive Hash Index 활용

트랜잭션은 최소한의 코드에만 적용할 것.

트랜잭션 안에 불필요한 메일 발송, 로그인 여부 확인 등은 제거하자.
네트워크 작업은 트랜잭션 안에서 제거하는 것이 좋다.

index와 lock

InnoDB는 레코드를 잠그는 것이 아니라, 인덱스를 잠금.
first_name column만 인덱스 테이블에 담겨 있을 때, 아래 쿼리를 실행하면 first_name이 'dj'인 모든 row가 lock이 걸린다.
- UPDATE employees SET hire_date=NOW() WEHER first_name='dj' AND last_name='seo'
인덱스가 하나도 없다면? 테이블의 모든 row가 잠긴다.

auto increment lcok

insert 되는 레코드의 건수를 정확히 파악 가능할 때는 래치(뮤택스)로 매우 빠르게 처리
하지만, insert ... select 처럼 개수를 정확히 파악 불가능하면, auto increment lock 사용
대량 insert 시에는, 여러 개의 자동 증가 값을 미리 할당. 하나의 insert에는 연속, 이후 insert와는 연속이 아닐 수 있음.

NON-REPEATABLE READ

read-committed의 격리 수준에서 발생 가능한 문제.
하나의 트랜잭션 안에서 select 가 2번 실행했을 때, 다른 결과가 나올 수 있는 문제.
- A: BEGIN, SELECT / B: UPDATE / A: SELECT
- 하나의 트랜잭션에서 동일 데이터를 여러 번 읽는 작업에서 큰 문제가 될 수 있음.
REPEATABLE READ 격리 수준에서는 SELECT 도 트랜잭션 내에서 실행됨.
트랜잭션에 번호를 부여하여, 자신 보다 낮은 번호의 트랜잭션에서 변경한 것만 보는 방법으로 해결.
하지만, INSERT의 경우는 부정합 발생 가능.

압축

테이블 압축을 사용
InnoDB 스토리지 엔진에서 압축 적용
압축된 파일의 크기는 예측 불가능함.
- 압축된 결과가 KEY_BLOCK_SIZE 보다 작으면 디스크에 저장, 크면 split해서 다시 압축

암호화

데이터베이스 수준의 암호화가 필요한 경우:

사용자의 생년월일을 애플리케이션 레벨에서 암호화 하여 DB에 저장하는 시나리오 가정. 2001년도에 출생한 사용자를 불러오는 쿼리는 실행할 수가 없다. 하지만, MySQL의 테이터베이스 수준의 암호화는 디스크 입출력 직전에 암호화를 진행하기 때문에, 이러한 제약이 없음. 하지만 DB 서버에 접속하면 평문을 볼 수 있기 때문에, 요구 조건에 따라 둘 다 적절히 사용해야 함.

일반적으로 응용 프로그램은 column 단위 암호화, 데이터베이스는 테이블 단위 암호화
디스크 InnoDB I/O 레이어 사이에서 데이터 암호화, 복호화 실행
InnoDB 버퍼 풀에 적제 된 경우에는, 복호화가 완료된 데이터임. 성능 차이가 없음. 하지만 버퍼 풀에 없는 데이터를 읽는 경우에는 복호화 시간 만큼 쿼리 처리가 지연됨.
압축, 암호화 동시 진행하는 경우에는 압축 암호화가 좋음.
- 암호화된 결과는 랜덤한 바이트 배열이라 압축 효율이 떨어짐
- InnoDB 버퍼 풀은 압축, 압축 해제 모든 상태로 적재 가능하지만, 복호화 상태의 데이터만 적재 가능.

Transparent Data Encryption(TDE)

애플리케이션은 동일하게 작동하고, DBMS가 알아서 암호화/복호화

테이블 생성 시, 다음과 같이 ENCRYPTION 옵션을 추가하면 활성화 가능:

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100)
) ENCRYPTION='Y';