책너두 6기 27일차
백은빈, 이성욱의 Real MySQL8.0 1권 p.277 ~ p.286
내용정리
08 인덱스
8.9 유니크 인덱스
유니크는 인덱스라기보다는 제약 조건에 가깝다고 볼 수 있다. 테이블이나 인덱스에 같은 값이 2개 이상 저장될 수 없다는 의미이다. MySQL에서는 인덱스 없이 유니크 제약만 설정할 방법이 없다. NULL은 특정 값이 아니므로 2개 이상 저장될 수 있다.
8.9.1 유니크 인덱스와 일반 세컨더리 인덱스의 비교
유니크 인덱스와 유니크하지 않은 일반 세컨더리 인덱스는 사실 인덱스의 구조상 아무 차이가 없다
8.9.1.1 인덱스 읽기
유니크 인덱스가 빠르지 않다. ununique 세컨더리 인덱스에서 레코드를 한 건 더 읽어야 하는 것은 디스크 읽기가 아니라 CPU에서 칼럼값을 비교하는 작업이기 떄문에 성능상 영향이 거의 없다. 중복된 값이 허용되므로 읽어야 할 레코드가 많아서 ununique가 느린 것이지, 자체의 특성은 아니다.
8.9.1.2 인덱스 쓰기
유니크 인덱스의 키 값을 쓸 떄는 중복된 값이 있는지 없는지 체크하는 과정이 한 단계 더 필요해서 ununique 세컨더리 인덱스의 쓰기보다 느리다.
8.9.2 유니크 인덱스 사용 시 주의사항
꼭 필요한 경우라면 생성하는 것이 당연하지만, 성능이 좋아질 것이라는 생각에 불필요하게 생성하진 말자. 하나의 테이블에서 같은 칼럼에 유니크 인덱스와 일반 인덱스를 각각 중복해서 생성할 필요는 없다.
8.10 외래키
InnoDB 스토리지 엔진에서만 생성할 수 있으며, 왜래키 제약이 설정되면 자동으로 연관되는 테이블의 칼럼에 인덱스까지 생성된다. 외래키 관리에는 중요한 두 가지 특징이 있다.
- 테이블의 변경(쓰기 잠금)이 발생하는 경우에만 잠금 경합(잠금 대기)이 발생한다.
- 외래키와 연관되지 않은 칼럼의 변경은 최대한 잠금 경합(잠금 대기)을 발생시키지 않는다.
8.10.1 자식 테이블의 변경이 대기하는 경우
자식 테이블의 외래 키 칼럼의 변경(insert, update)은 부모 테이블의 확인이 필요한데, 이 상태에서 부모 테이블의 해당 레코드가 쓰기 잠금이 걸려 있으면 해당 쓰기 잠금이 해제될 떄까지 기다리게 된다. 자식 테이블의 외래키가 아닌 칼럼의 변경은 외래키로 인한 잠금 확장이 발생하지 않는다.
8.10.2 부모 테이블의 변경 작업이 대기하는 경우
자식 테이블의 레코드를 변경하면 자식 테이블의 레코드에 대해 쓰기 잠금을 획득하고 뒤의 작업도 쓰기 잠금이 해제될 떄 까지 기다려야 한다.
데이터베이스에서 외래 키를 물리적으로 생성하려면 위와 같은 잠금 경합까지 고려하여 모델링을 진행해야 한다.
09 옵티마이저와 힌트
MySQL 서버로 요청된 쿼리는 결과는 동일해도 결과를 만들어내는 방법은 다양하다. 따라서 최소 비용, 최적화를 따져봐야 한다. 기본 데이터를 비교해 최적의 실행 계획을 수립하는 작업을 옵티마이저가 담당한다.
9.1 개요
9.1.1 쿼리 실행 절차
쿼리가 실행되는 과정은 크게 세 단계이다.
- 사용자로부터 요청된 문장을 쪼개서 서버가 이해할 수 있는 수준으로 파스 트리(분리)한다. =. SQL Parsing by SQL Parser
- 파스 트리(파싱 정보)를 확인하면서 어떤 테이블의 어떤 인덱스를 이용할지 선택한다.
- 2번에서 결정된 순서를 이용해 스토리지 엔진으로부터 데이터를 가져온다.
서버는 문장 그 자체가 아니라 파스 트리를 이용해 쿼리를 실행한다.
두 번쨰 단계는 다음 내용들을 처리한다.
- 불필요한 조건 제거 및 복잡한 연산의 단순화
- 여러 테이블의 조인에서 읽을 순서 결정
- 각 테이블에 사용된 조건과 인덱스 통계 정보로 이용할 인덱스 결정
- 가져온 레코드를 임시 테이블에 넣고 다시 가공해야 하는지 결정
이 외에도 수많은 처리를 진행한다.
9.1.2 옵티마이저의 종류
옵티마이저는 서버에서 두뇌와 같은 역할이다. 방법에는 최근 사용하는 비용 기반 최적화(CBO, Cost-based optimizer)와 예전에나 많이 썼던 규칙 기반 최적화(RBO, Rule-based optimizer)가 있다. RBO는 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립한다.
9.2 기본 데이터 처리
모든 RDBMS는 데이터를 정렬하거나 그루핑하는 기본 데이터 가공 기능을 가지고 있다.
9.2.1 풀 테이블 스캔과 풀 인덱스 스캔
풀 인덱스 스캔은 이전 장에서 살펴보았다.
풀 테이블 스캔은 인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어서 작업을 처리하는 것이다. 옵티마이저는 보통 다음과 같을 때 선택한다.
- 레코드 건수가 너무 작을때
where절이나on절에 인덱스를 이용할 적절한 조건이 없을 때- 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많을 때
