[도서] Real MySQL : 인덱스 - 질문지

디스크 읽기 방식

Q. SSD와 HDD의 순차, 랜덤 I/O의 성능 차이는 어떠한가요?

디스크의 헤더를 움직이지 않고 한 번에 많은 데이터를 읽는 순차 I/O에서는 SSD가 하드 디스크 드라이브보다 조금 빠르거나 거의 비슷한 성능을 보이기도 합니다. 하지만 SSD의 장점은 HDD보다 랜덤 I/O가 훨씬 빠르다는 것입니다.

 

데이터베이스에서 순차 I/O 작업은 그다지 비중이 크지 않고 랜덤 I/O를 통해 작은 데이터를 읽고 쓰는 작업이 대부분이므로 SSD 장점은 DBMS용 스토리지에 최적이라고 볼 수 있습니다.

 

Q. HDD의 랜덤 I/O와 순차 I/O는 어떤 차이가 있나요?

순차 I/O는 디스크에 기록 및 읽기을 위해 디스크 헤드를 1번 움직이지만 랜덤 I/O는 디스크 헤더의 위치를 여러 번 옮기게 됩니다. 디스크의 성능은 디스크 헤더의 위치 이동 없이 얼마나 많은 데이터를 한 번에 기록하느냐에 의해 결정되므로 여러 번 쓰기 또는 읽기 요청을 하는 랜덤 I/O 작업이 작업 부하가 훨씬 더 큽니다.

 

Q. 그럼 SSD는 순차 I/O와 랜덤 I/O의 차이가 없나요?

디스크 원판을 가지지 않는 SSD는 차이가 없을 것으로 예측하지만, 실제로 SSD 드라이버에서도 랜덤 I/O는 순차 I/O보다 전체 스루풋(Throughput)이 떨어집니다. 그래서 SSD 드라이브의 사양에도 항상 순차 I/O와 랜덤 I/O의 성능 비교를 구분해서 명시합니다.

 

Q. 쿼리 튜닝의 목적은 뭐라고 할 수 있는가?

쿼리 튜닝을 통해 랜덤 I/O를 순차 I/O로 바꿔서 실행할 방법은 그다지 많지 않습니다. 일반적으로 쿼리를 튜닝하는 것은 랜덤 I/O 횟수를 줄이는 것이 목적입니다.

 

B-Tree 인덱스

Q. MySQL의 InnoDB 테이블과 다른 RDBMS의 데이터 파일에서 레코드 저장 순서의 차이점은 무엇인가요?

대부분의 RDBMS의 데이터 파일에서 레코드는 임의의 순서로 저장됩니다. 하지만 InnoDB 테이블에서 레코드는 클러스터되어 디스크에 저장되므로 기본적으로 기본키 순서로 정렬되어 저장됩니다. 이는 오라클의 IOT(Index organized table)나 MS-SQL의 클러스터 테이블과 같은 구조를 말합니다. 다른 DBMS에서는 클러스터링 기능이 선택 사항이지만, InnoDB에서는 디폴트로 클러스터링 테이블이 생성됩니다. 클러스터링이란 비슷한 값을 최대한 모아서 저장하는 방식을 의미합니다.

 

Q. InnoDB 테이블의 인덱스의 데이터 파일의 관계는 어떻게 구성되나요?

InnoDB 테이블의 인덱스의 데이터 파일의 큰 특징은 세컨더리 인덱스를 통해 데이터 파일의 레코드를 찾아가는 방법에 있습니다. InnoDB 테이블은 프라이머리 키를 주소처럼 사용하기 때문에 세컨더리 인덱스가 논리적인 주소를 가지고 있습니다.

 

그래서 InnoDB 테이블에서 세컨더리 인덱스를 통해 레코드를 읽을 때 인덱스에 저장돼 있는 프라이머리 키 값을 이용해 프라이머리 키 인덱스를 한 번 더 검색한 후, 프라이머리 키 인덱스의 리프 페이지에 저장돼 있는 레코드를 읽습니다.

 

즉 InnoDB 테이블의 세컨더리 인덱스는 데이터 레코드를 읽기 위해 반드시 프라이머리 키 인덱스를 다시 한번 검색해야 합니다.

 

Q. 인덱스에 키를 추가하는 작업에 왜 비용이 많이 드나요?

리프 노드가 꽉 차서 더는 저장할 수 없을 때는 리프 노드가 분리돼야 하는데, 이는 상위 브랜치 노드까지 처리의 범위가 넓어집니다. 이러한 작업 탓에 B-Tree는 상대적으로 새로운 키를 추가하는 작업에 비용이 많이 드는 것으로 알려져있습니다. 

 

Q. 인덱스 종류에 따라 인덱스 키 추가 작업이 달라질 수 있나요?

InnoDB 스토리지 엔진은 체인지 버퍼를 통해 인덱스 키 추가 작업을 지연시켜 나중에 처리할 수 있습니다. 하지만 프라이머리 키나 유니크 인덱스의 경우 중복 체크가 필요하기 때문에 즉시 B-Tree에 추가하거나 삭제합니다.

 

Q. 인덱스 키 삭제 작업은 어떻게 동작하나요?

해당 키 값이 저장된 B-Tree의 리프 노드를 찾아서 그냥 삭제 마크만 하면 작업이 완료된다. 이렇게 삭제 마킹된 인덱스 키 공간은 계속 그대로 방치하거나 재활용할 수 있습니다. 이러한 마킹 작업 또한 디스크 I./O가 필요한 작업이지만 InnoDB 스토리지 엔진에서는 이 작업 또한 버퍼링되어 지연 처리될 수 있습니다.

 

Q. 인덱스 키 변경 작업은 어떻게 동작하나요?

B-Tree의 키 값 변경 작업은 먼저 키 값을 삭제한 후, 다시 새로운 키 값을 추가하는 형태로 처리됩니다.

 

Q. InnoDB 스토리지 엔진은 디스크에 어떤 단위로 작업을 하나요?

InnoDB 스토리지 엔진은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지 또는 블록이라고 하며, 디스크의 모든 읽기 및 쓰기 작업의 최소 작업 단위가 됩니다. 또한 페이지는 InnoDB 스토리지 엔진의 버퍼 풀에서 데이터를 버퍼링하는 기본 단위이기도 합니다. 인덱스도 페이지 단위로 관리됩니다.

 

Q. B-Tree는 자식 노드는 개수는 어떻게 결정되나요?

이것은 인덱스의 페이지 크기와 인덱스 키 값의 크기에 따라 결정됩니다. 페이지 크기는 시스템 변수로 설정할 수 있지만 기본값은 16KB입니다. 인덱스 키갑 값의 크기가 늘어날 수록 하나의 인덱스 페이지에 저장할 수 있는 노드 개수가 줄어듭니다.

또한 하나의 페이지에 담긴 노드의 개수가 줄어들 수록 읽어야할 페이지 수가 늘어날 수 있으며 전체적인 인덱스 크기도 커집니다.

 

Q. B-Tree의 깊이는 성능에 어떤 영향을 주나요?

B-Tree의 깊이는 인덱스 검색할 때 몇번이나 랜덤하게 디스크를 읽어야 하는지와 직결되는 문제입니다. 하지만 B-Tree의 깊이는 직접 제어할 방법이 없습니다. 다만 키 값의 크기를 가능하면 작게 만드는 것이 깊이를 최대한 작게 만들 수 있는 간접적인 방법입니다.

 

하지만, 실제로는 아무리 대용량 데이터베이스라도 B-Tree의 깊이가 5단계 이상까지 깊어지는 경우는 흔치 않다.

 

Q. 인덱스에서 선택도(기수성)은 인덱스 성능에 어떤 영향을 주나요?

인덱스의 선택도는 키 값 가운데 유니크한 값의 수를 의미합니다. 인덱스 키 값 가운데 중복된 값이 많아질수록 선택도는 낮아집니다. 인덱스는 선택도가 높을수록 검색 대상이 줄어들기 때문에 그만큼 빠르게 처리됩니다.

 

선택도가 좋지 않다고 하더라도 정렬이나 그룹핑과 같은 작업을 위해 인덱스를 만드는 것이 훨씬 나은 경우도 있습니다. 인덱스가 항상 검색에만 사용되는 것은 아니므로 여러 가지 용도로 고려해 적절히 인덱스를 설계해야 합니다.

 

Q. 읽어야 할 레코드 건수는 인덱스 사용에 어떤 영향을 주나요?

인덱스를 통해 테이블의 레코드를 읽는 것은 바로 테이블의 레코드를 읽는 작업보다 높은 비용이 드는 작업입니다. 읽어야 할 레코드 건수가 많다면 어떤 방식을 사용하는 것이 효율적일지 판단해야 합니다.

 

일반적으로 인덱스릍 통해 레코드 1건을 읽는 것이 테이블에서 직접 레코드 1건을 읽는 것보다 4~5배 정보 비용이 더 많이 드는 작업인 것으로 예측하기 때문에 인덱스를 통해 읽어야 하는 건수가 전체 테이블 레코드의 20%~25%를 넘어가면 인덱스를 사용하지않고 직접 테이블을 읽습니다.

 

Q. 다중 칼럼 인덱스 생성 시 주의할 점은 무엇인가요?

다중 칼럼 인덱스에서 인덱스를 구성하는 칼럼의 정렬 순서는 해당 칼럼의 왼쪽에 의존해서 정렬됩니다. 따라서, 인덱스 내에서 각 칼럼의 위치(순서)가 상당히 중요하며, 신중하게 결정해야 합니다.

 

Q. 인덱스 스캔 방향을 정렬에 어떻게 활용할 수 있나요?

만일 인덱스이 오름차순으로 정렬돼있다고 해서 오름차순으로만 값을 읽을 수 있는 것은 아닙니다. 해당 인덱스를 역순으로 읽으면 내림차순으로도 읽을 수 있습니다. 옵티마이저는 이러한 사실을 인지하고 활용합니다.

 

Q. InnoDB의 인덱스 역순 스캔이 정순 스캔에 비해 느린 이유는 무엇인가요?

InnoDB 스토리지 엔진에서 정순 스캔과 역순 스캔은 페이지(블록) 간의 양방향 연결 고리를 통해 전진하느냐 후진하느냐의 차이만 있지만, 실제 내부적으로 인덱스 역순 스캔이 인덱스 정순 스캔에 비해 다음의 이유로 느립니다.

 

• 페이지 잠금이 인덱스 정순 스캔에 적합한 구조입니다
• 페이지 내에서 인덱스 레코드가 단반향으로만 연결된 구조입니다.

 

클러스터링 인덱스

Q. 클러스터링 인덱스와 세컨더리 인덱스의 차이는 무엇인가요?

세컨더리 인덱스의 리프 노드와 달리 클러스터링 인덱스의 리프 노드에는 레코드의 모든 칼럼이 같이 저장돼 있습니다.

 

클러스터링 인덱스는 클러스터링 테이블과 동의어이여 클러스터링 테이블은 그 자체가 하나의 거대한 인덱스 구조로 관리되는 것입니다.

 

이러한 이유로 클러스터링 테이블의 프라이머리 키 변경과 레코드 저장이 느립니다.

 

Q. 프라이머리 키가 없는 InnoDB 테이블은 어떻게 클러스터링 테이블로 구성되나요?

InnoDB 스토리지 엔진이 다음 우선순위대로 프라이머리 키를 대체할 칼럼을 선택합니다.

1. 프라이머리 키가 있으면 기본적으로 프라이머리 키를 클러스터링 키로 선택
2. NOT NULL 옵션의 유니크 인덱스 중에서 첫번째 인덱스를 클러스터링 키로 선택
3. 자동으로 유니크한 값을 가지도록 증가되는 칼럼을 내부적으로 추가한 후, 클러스터링 키로 선택

3번의 내부적인 칼럼은 쿼리 문장에 명시적으로 사용할 수 없고 이것은 우리에게 아무러 혜택을 주지 않습니다. 가능하다면 프라이머리 키를 명시적으로 생성하는 것이 좋습니다.

 

Q. 클러스터링 인덱스의 장점과 단점은 무엇인가요?

장점

• 프라이머리 키로 검색할 때 처리 성능이 매우 빠릅니다.
• 테이블의 모든 세컨더리 인덱스가 프라이머리 키를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많습니다.
  (이를 커버링 인덱스라고 합니다.)

단점

• 세컨더리 인덱스가 클러스터링 키를 갖기 때문에 클러스터링 키 값의 크기가 클 경우 전체적으로 인덱스의 크기가 커짐
• 세컨더리 인덱스를 통해 검색할 때 프라이머리 키로 다시 한번 검색해야 하므로 처리 성능이 느림
• INSERT 할 때 프라이머리 키에 의해 레코드의 저장 위치가 결정되기 때문에 처리 성능이 느림
• 프라이머리 키를 변경할 때 레코드를 DELETE하고 INSERT하는 작업이 필요하기 때문에 처리 성능이 느림

 

유니크 인덱스

Q. 유니크 인덱스와 유니크하지 않은 세컨더리 인덱스는 어떤 차이가 있나요?

유니크는 사실 인덱스로서 특징을가진다 보다 제약 조건에 가깝습니다. 그래서 유니크 인덱스과 유니크하지 않은 일반 세컨더리 인덱스는 사실 인덱스 구조상 아무런 차이점이 없습니다.

 

하지만 인덱스 쓰기 동작 시 유니크 인덱스는 중복된 값이 있는지 없는지 체크하는 과정이 한 단계 더 필요하므로 상대적으로 느립니다. 그리고 중복된 값을 체크 시 인덱스에 읽기 잠금을 사용하고, 쓰기를 할 때 스기 잠금을 사용하는데 이 과정에서 데드락이 빈번히 발생합니다.

 

그리고 InnoDB 스토리지 엔진에서는 인덱스 키의 저장을 버퍼링하기 위해 체인지 버퍼를 사용하지만 유니크 인덱스는 반드시 중복 체크를 해야 하므로 작업 자체를 버퍼링하지 못해 상대적으로 더 느리게 동작하는 원인이 됩니다.

 

외래키

Q. MySQL에서 외래키를 설정하면 어떤 동작이 자동으로 수행되나요?

MySQL에서 외래키는 InnoDB 스토리지 엔진에서만 생성할 수 있으며, 외래키 제약이 설정되면 자동으로 연관되는 테이블의 칼럼에 인덱스까지 생성됩니다. 외래키가 제거되지 않은 상태에서 자동으로 생성된 인덱스를 삭제할 수 없습니다.

 

Q. InnoDB 외래키 관리에 중요한 두 가지 특징은 무엇인가요?

• 테이블의 변경(쓰기 잠금)이 발생하는 경우에만 잠금 경합(잠금 대기)이 발생합니다.
• 외래키와 연관되지 않은 칼럼의 변경은 최대한 잠금 경합(잠금 대기)을 발생시키지 않는다.

 

이 특징은 외래 키 칼럼의 변경 시 데이터 무결성 유지를 위해 잠금을 이용하는데 이 과정에서 잠금 경합으로 쿼리의 동시 처리에 영향을 미칠 수 있음을 고려해야 합니다.