책너두 (Real MySQL 8.0 1권) 26일차 (~284p)

요약

• 유니크 인덱스에 대해 이해하게 됨.
• 유니크 인덱스와 일반 세컨더리의 차이를 이해하게 됨.
  • 인덱스 읽기 관점에서, 성능 차이는 미미함. (일반 세컨더리 인덱스에서 데이터를 더 많이 읽어오더라도, 속도 자체는 거의 동일함)
  • 인덱스 쓰기 관점에서 중복 체크 때문에 유니크 인덱스가 더 느림.
• 유니크 인덱스 생성시 주의사항을 이해하게 됨.
  • 중복된 인덱스를 적용할 필요가 없다.
  • 유일성이 꼭 보장되어야 하는 칼럼에 대해서 유니크 인덱스를 생성하되, 꼭 필요하지 않으면 유니크 인덱스보다는 유니크하지 않은 세컨더리 인덱스를 생성하는 방향으로 가자.
• 외래키에 대한 내용을 이해하게 됨.
  • InnoDB 스토리지 엔진에서만 사용 가능.
  • 외래키로 인한 부모, 자식의 쓰기 잠금 경합이 발생할 수 있음.
    • 쿼리 처리 성능이 떨어질 수 있으므로 신중히 모델링해야 함.
• 옵티마이저에 대해 이해하게 됨.
  • 쿼리의 실행계획에 대한 최적의 방법을 제시함.
• 쿼리의 실행 절차에 대해 이해하게 됨.
  • MySQL 엔진과 스토리지 엔진에서 해당 내용의 과정을 이해하게됨.
• 옵티마이저 종류를 알게 됨.
  • 규칙 기반 최적화 방법 → 사용 X
  • 비용 기반 최적화 방법 → 대부분 얘를 사용

메모

유니크 인덱스

• 테이블이나 인덱스에 같은 값이 2개 이상 저장될 수 없음.
• MySQL 에서 인덱스 없이 유니크 제약만 설정할 방법이 없음.
• 유니크 인덱스에서 NULL 도 저장될 수 있음.
  • NULL은 특정 값이 아니므로 2개 이상 저장될 수 있음.
• MySQL에서 프라이머리 키는 기본적으로 NULL을 허용하지 않는 유니크 속성이 자동으로 부여됨.
• MyISAM 이나 MEMORY 테이블에서 프라이머리 키는 NULL이 허용되지 않는 유니크 인덱스와 같음.
• InnoDB 테이블의 프라이머리 키는 클러스터링 키의 역할도 하므로 유니크 인덱스와 근본적으로 다름.

유니크 인덱스와 일반 세컨더리 인덱스의 비교

• 유니크 인덱스와 유니크 하지 않은 일반 세컨더리 인덱스는 사실, 인덱스 구조상 아무런 차이가 없음.
• 유니크 인덱스와 세컨더리 인덱스의 읽기, 쓰기를 성능 관점에서 살펴보자.

인덱스 읽기

• 유니크 인덱스가 빠르다고 생각하지만 그건 사실이 아님.
• 유니크 인덱스는 1건만 읽으면 되고, 세컨더리 인덱스는 레코드를 한 건 더 읽어야 하므로 느리다고 생각함.
  • 유니크하지 않은 세컨더리 인덱스의 경우, 디스크 읽기가 아니라 CPU 에서 칼럼 값을 비교하는 작업이기에 성능상 영향이 거의 없음.
  • 유니크하지 않은 세컨더리 인덱스는 중복된 값이 허용되므로, 읽을 레코드가 많아서 느린 것이지 인덱스 자체 특성 때문에 느린 것이 아님.
• 유니크 인덱스와, 일반 세컨더리 인덱스의 실행 계획은 다름.
  • 하지만, 1개의 레코드를 읽냐, 2개 이상의 레코드를 읽냐의 차이만 있을 뿐, 읽어야할 레코드 건수가 같다면 성능상의 차이는 미미함.

인덱스 쓰기

• 새로운 레코드가 INSERT 되거나 인덱스 칼럼 값이 변경되므로 인덱스 쓰기 작업이 필요함.
  • 유니크 인덱스는 키 값을 쓸 때, 중복 값이 있는지 체크하는 과정이 한 단계 더 필요함.
  • 그래서 유니크하지 않은 세컨더리 인덱스 쓰기보다 느림.
  • 심지어, MySQL 에서 유니크 인덱스에서 중복 값 체크할 때 읽기 잠금을 사용하고, 쓰기할 때 쓰기 잠금을 이용함
    • 이 과정에서 데드락이 빈번히 발생함.
  • 또, InnoDB 스토리지 엔진에서 인덱스 키의 저장을 버퍼링 하기 위해 체인지 버퍼(Change Buffer)가 사용됨.
    • 유니크 인덱스는 중복 체크를 해야하므로 작업 자체를 버퍼링 하지 못함.
• 따라서 유니크 인덱스는 일반 세커더리 인덱스보다 변경 작업이 더 느리게 작동함.

유니크 인덱스 사용 시 주의사항

• 꼭 필요한 경우에는 유니크 인덱스를 생성하는 것은 당연함.
  • 성능이 좋아질 것으로 생각하고 불필요한 유니크 인덱스 생성은 하지 않는게 좋음.
• 하나의 테이블에서 같은 칼럼에 유니크 인덱스와 일반 인덱스를 중복해서 생성할 필요는 없음.
  • 유니크 인덱스가 일반 세컨더리 인덱스와 같은 역할을 동일하게 수행함.
• 똑같은 칼럼에 대해 프라이머리 키와 유니크 인덱스를 동일하게 생성하는 경우도 있는데, 이 또한 불필요한 중복임.
• 유니크 인덱스는 쿼리의 실행 계획이나 테이블의 파티션에 미치는 영향도 있음. → 10장 ‘실행 계획’, 13장 ‘파티션’ 에서 살펴보자.
• 결론은, 유일성이 꼭 보장되어야 하는 칼럼에 대해서 유니크 인덱스를 생성하되, 꼭 필요하지 않으면 유니크 인덱스보다는 유니크하지 않은 세컨더리 인덱스를 생성하는 방법을 고려해 보자.

외래키

• MySQL에서 외래키는 InnoDB 스토리지 엔진에서만 생성할 수 있음.
• 외래키 제약이 설정되면 자동으로 연관되는 테이블의 칼럼에 인덱스까지 생성됨.
• 외래키가 제거되지 않은 상태에서 자동으로 생성된 인덱스를 삭제할 수 없음.
• InnoDB의 외래키 관리에 중요한 두 가지 특징이 있음.
  • 테이블의 변경(쓰기 잠금)이 발생한 경우에만 잠금 경합(잠금 대기)이 발생한다.
  • 외래키와 연관되지 않은 칼럼의 변경은 최대한 잠금 경합(잠금 대기)을 발생시키지 않는다.
  • p279~281에 관련 예제가 잘 설명되어 있음.
• 데이터베이스에서 외래 키를 물리적으로 생성하려면 위 현상으로 인한 잠금 경합까지 고려해 모델링을 진행하는 것이 좋음.
  • 이러한 잠금이 다른 테이블로 확장되면 그만큼 전체적으로 쿼리의 동시 처리에 영향을 미치기 때문

옵티마이저

• MySQL 서버로 요청된 쿼리는 동일함.
  • 내부적으로 그 결과를 만들어내는 방법은 매우 다양함.
  • 이러한 다양한 방법 중, 어떤 방법이 최적이고 최소의 비용이 소모될지 결정해야 함.
• MySQL에서 쿼리를 최적으로 실행하기 위해, 각 테이블의 데이터가 어떤 분포로 저장되어 있는지 통계 정보를 참조한다.
  • 기본 데이터를 비교해서 최적의 실행 계획을 수립하는 작업이 필요함.
  • MySQL 서버를 포함한 대부분의 DBMS에서는 옵티마이저가 이 기능을 담당함.
• MySQL 에서는 EXPLAIN 이라는 명령으로 쿼리의 실행 계획을 확인할 수 있음.
  • 실행 계획에 표시되는 내용을 제대로 이해하려면, MySQL 서버 옵티마이저가 실행하는 최적화에 대해 어느정도 지식을 갖춰야 함.
• MySQL 서버가 사용자 요청을 처리하기 위해 데이터를 가공하는 기본 절차와 빠른 성능을 보장하기 위해 수행하는 최적화에 대해 살펴보자.
• 어떤 DBMS든, 쿼리 실행 계획을 수립하는 옵티마이저는 가장 복잡한 부분임.
  • 옵티마이저가 만들어내는 실행 계획을 이해하는 것 또한 상당히 어려움.
  • 하지만 실행 계획을 이해할 수 있어야만 실행 계획의 불합리한 부분을 찾아내고 더 최적화된 방법으로 실행 계획을 수립하도록 유도할 수 있음.

쿼리 실행 절차

• MySQL 서버에서 쿼리가 실행되는 과정은 크게 세 단계로 나눌 수 있음.
  1. 사용자로부터 요청된 SQL 문장을 잘게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리(파스 트리)함.
  2. SQL의 파싱 정보(파스 트리)를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 이용하여 테이블을 읽을지 선택한다.
  3. 두 번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져온다.
• 첫 번째 단계를 “SQL 파싱(Parsing)” 이라고 함. MySQL 서버의 “SQL 파서”라는 모듈로 처리함.
  • SQL 문장이 문법적으로 잘못되었다면, 이 단계에서 걸러짐.
  • 이 단계에서 “SQL 파스 트리”가 만들어 짐.
    • MySQL 서버는 SQL 문장 그 자체가 아니라 SQL 파스 트리를 이용하여 쿼리를 실행함.
• 두 번째 단계는 첫 번째 단계에서 만들어진 SQL 파스 트리를 참조하면서 다음과 같은 내용을 처리함.
  • 불필요한 조건 제거 및 복잡한 연산의 단순화
  • 여러 테이블의 조인이 있는 경우, 어떤 순서로 테이블을 읽을지 결정한다.
  • 각 테이블에 사용된 조건과 인덱스 통계 정보를 이용하여 사용할 인덱스를 결정한다.
  • 가져온 레코드들을 임시 테이블에 넣고, 다시 한번 가공해야 하는지 결정한다.
• 이 두 번째 단계는, “최적화 및 실행 계획 수립” 단계임.
  • MySQL 서버의 “옵티마이저”에서 처리됨.
  • 두 번째 단계가 완료되면 쿼리의 “실행 계획”이 만들어짐.
• 세 번째 단계는 수립된 실행 계획대로 스토리지 엔진에 레코드를 읽어오도록 요청한다.
  • MySQL 엔진에서는 스토리지 엔진으로부터 받은 레코드를 조인하거나 정렬하는 작업을 수행함.
• 1, 2단계는 MySQL 엔진에서 처리하고, 3단계는 MySQL 엔진과 스토리지 엔진이 동시에 참여하여 처리함.

옵티마이저의 종류

• 옵티마이저는 데이터베이스 서버에서 두뇌와 같은 역할을 담당함.
• 옵티마이저는 현재 대부분의 DBMS 가 선택하고 있는 비용 기반 최적화(Cost-based optimizer, CBO) 방법과 예전 초기 버전의 오라클 DBMS에서 많이 사용했던 규칙 기반 최적화 방법(Rule-based optimizer, RBO)로 크게 나눌 수 있음.
  • 규칙 기반 최적화 : 대상 테이블의 레코드 건수나 선택도 등을 고려하지 않고, 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립하는 방식임.
    • 이 방식은 통계 정보를 조사하지 않고 실행 계획이 수립되므로 같은 쿼리에 대해서 항상 같은 실행 방법을 만들어 낸다.
    • 하지만 사용자 데이터 분포도가 매우 다양하기에, 규칙 기반 최적화는 이미 오래전부터 많은 DBMS에서 거의 사용되지 않음.
  • 비용 기반 최적화 : 쿼리 처리를 위한 여러 가지 가능한 방법을 만든다. 각 단위 작업의 비용(부하) 정보와 대상 테이블의 예측된 통계 정보를 이용해 실행 계획별 비용을 산출함.
    • 산출된 실행 방법별로 비용이 최소로 소요되는 처리방식을 선택하여 최종적으로 쿼리를 실행함.
    • 현재 대부분의 RDBMS가 비용 기반의 옵티마이저를 채택하고 있고, MySQL도 그렇다.