인덱스

디스크 읽기 방식

데이터베이스 성능 튜닝은 어떻게 디스크 IO를 줄이느냐가 관건일 때가 상당히 많다.

랜덤 IO, 순차 IO와 같은 디스크 읽기 방식을 먼저 알아보자.

 

하드 디스크 드라이브(HDD)와 솔리드 스테이트 드라이브(SSD)

• SSD는 기존 하드 디스크 드라이브에서 데이터 저장용 플래터(원판)를 제거하고 그 대신 플래시 메모리를 장착하고 있다. 그래서 아주 빠르고, 전원이 공급되지 않아도 데이터가 삭제되지 않는다.
• SSD의 장점은 기존 하드 디스크 드라이브보다 랜덤 IO가 훨씬 빠르다는 것이다. 데이터베이스 서버에서 순차 IO 작업은 그다지 비중이 크지 않고, 랜덤 IO를 통해 작은 데이터를 읽고 쓰는 작업이 대부분이므로 SSD의 장점은 DBMS용 스토리지에 최적이다.

RealMySQL 8.0 p215.

 

랜덤 I/O 와 순차 I/O

• 디스크의 성능은 디스크 헤더의 위치 이동 없이 얼마나 많은 데이터를 한 번에 기록하느냐에 의해 결정된다.
• 여러 번 쓰기 또는 읽기를 요청하는 랜덤 I/O 작업이 작업 부하가 훨씬 더 크다.
• 데이터베이스의 대부분 작업은 이런 작은 데이터를 빈번히 읽고 쓰기 때문에 MySQL 서버에서는 그룹 커밋이나 바이너리 로그 버퍼나 InnoDB 로그 버퍼 등의 기능이 내장돼 있다.
• 랜덤 IO를 줄인다는 것은 쿼리를 처리하는 데 꼭 필요한 데이터만 읽도록 쿼리를 개선하는 것을 의미한다.
• 인덱스 레인지 스캔은 데이터를 읽기 위해 주로 랜덤 I/O를 사용하며, 풀 테이블 스캔은 순차 I/O를 사용한다. 그래서 큰 테이블의 레코드 대부분을 읽는 작업에서는 인덱스를 사용하지 않고 풀 테이블 스캔을 사용하도록 유도할 때 도 있다.(순차 I/O가 랜덤 I/O보다 빠름)

RealMySQL 8.0 p216.

인덱스란?

• 책의 마지막에 있는 "찾아보기"가 인덱스에 비유된다면 책의 내용은 데이터 파일에 해당한다고 볼 수 있다.
• DBMS가 데이터베이스 테이블의 모든 데이터를 검색해 원하는 결과를 가져오려면 시간이 오래 걸린다. 그래서 칼럼들의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍으로 삼아 인덱스를 만들어두는 것이다. DBMS의 인덱스는 칼럼의 값을 주어진 순서로 미리 정렬해서 보관한다.
• DBMS의 인덱스는 SortedList와 마찬가지로 저장되는 칼럼의 값을 이용해서 항상 정렬된 상태를 유지한다. 데이터 파일은 ArrayList와 같이 저장된 순서대로 별도의 정렬 없이 그대로 저장한다.
• DBMS의 인덱스는 데이터 저장(CUD) 성능을 희생하고, 읽기 속도를 높이는 기능이다.
• 역할별로 인덱스를 구분한다면, 프라이머리 키와 보조키로 구분할 수 있다.
  • 프라이머리 키는 레코드를 대표하는 칼럼의 값으로 만들어진 인덱스 - NULL을 허용 x, 중복 허용 x
  • 프라이머리 키를 제외한 모든 인덱스는 세컨더리 인덱스
• 데이터 저장 방식(알고리즘) 별로 구분한다면, B-Tree 인덱스와 해시 인덱스로 구분할 수 있다.
  • B-Tree 알고리즘은 가장 일반적으로 사용되는 인덱스 알고리즘으로, B-Tree 인덱스는 칼럼의 값을 변형하지 않고 원래의 값을 이용해 인덱싱하는 알고리즘이다.
  • 해시 인덱스 알고리즘은 칼럼의 값으로 해시 값을 계산해서 인덱싱하는 알고리즘으로, 매우 빠른 검색을 지원한다.
• 데이터 중복 허용 여부로 분류하면, 유니크 인덱스와 유니크하지 않은 인덱스로 구분할 수 있다. 유니크 조건이 중요한 이유는 동등 조건으로 검색한다는 것은 항상 1건의 레코드만 찾으면 더 찾기 않아도 된다는 것을 옵티마이저에게 알려주는 효과를 내기 때문이다.
• 인덱스의 기능별로 분류하면, 전문 검색용 인덱스나 공간 검색용 인덱스 등이 있다.

B-Tree 인덱스

• B-Tree의 "B"는 "Binary"의 약자가 아니라 "Balanced"이다.
• B-Tree는 칼럼의 원래 값을 변형하지 않고 인덱스 구조체 내에서는 항상 정렬된 상태로 유지한다.
• 전문 검색과 같은 특수한 요건이 아닌 경우, 대부분 인덱스는 거의 B-Tree를 사용할 정도로 일반적인 용도에 적합한 알고리즘이다.

구조 및 특성

• 최상위에 하나의 루트 노드, 가장 하위에 있는 노드를 리프 노드, 루트도 리프도 아닌 노드를 브랜치 노드라고 한다.
• 데이터베이스에서 인덱스와 실제 데이터가 저장된 데이터는 따로 관리되는데, 인덱스의 리프 노드는 항상 실제 데이터 레코드를 찾아가기 위한 주솟값을 가지고 있다.
• MyISAM 테이블은 세컨더리 인덱스가 물리적인 주소를 갖고, InnoDB 테이블은 프라이머리 키를 주소처럼 사용하기 때문에 논리적인 주소를 가진다.
• 대부분의 RDBM의 데이터 파일에서 레코드는 특정 기준으로 정렬되지 않고 임의의 순서로 저장된다. 하지만 InnoDB 테이블에서 레코드는 클러스터 되어 디스크에 저장되므로 기본적으로 프라이머리 키 순서로 정렬되어 저장된다.
• InnoDB테이블에서 인덱스를 통해 레코드를 읽을 때는 인덱스에 저장돼 있는 프라이머리 키값을 이용해 프라이머리 키 인덱스를 한 번 더 검색한 후, 프라이머리 키 인덱스의 리프 페이지에 저장돼 있는 레코드를 일는다.

RealMySQL 8.0 p220.

RealMySQL 8.0 p221.

 

B-Tree 인덱스 키 추가 및 삭제

B-Tree 인덱스 키 추가

• B-Tree에 저장될 때는 저장될 키 값을 이용해 B-Tree 상의 적절한 위치를 검색해야 한다.
• 저장될 위치가 결정되면 레코드의 키 값과 대상 레코드의 주소 정보를 B-Tree의 리프 노드에 저장한다.
• 리프 노드가 꽉 찼다면 분리해야 하는데, 상위 노드까지 가야 하므로 쓰기 비용이 많이 든다.

B-Tree 인덱스 키 삭제

• 해당 키 값이 저장된 B-Tree의 리프 노드를 찾아서 그냥 삭제 마크만 하면 된다.
• 삭제 마킹된 인덱스 키 공간은 그대로 방치하거나 재활용할 수 있다.
• 인덱스 키 삭제로 인한 마킹 작업 또한 디스크 쓰기가 필요하므로 이 작업 역시 디스크 I/O가 필요한 작업이다.

B-Tree 인덱스 키 변경

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

B-Tree 인덱스 키 검색

• 인덱스 관리로 인한 추가 비용을 감당하면서 인덱스를 구축하는 이유는 빠른 검색을 위해서다.
• B-Tree 인덱스를 이용한 검색은 100% 일치 또는 값의 앞부분만 일치하는 경우에 사용할 수 있다.
• InnoDB 검색에서는 인덱스가 없으면 불필요하게 많은 레코드를 잠그므로 인덱스 설계가 중요하고 많은 부분에 영향을 미친다.

 

B-Tree 인덱스 사용에 영향을 미치는 요소

인덱스 키 값의 크기

• InnoDB 스토리지 엔진은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지 또는 블록이라고 하며, 디스크의 모든 읽기 및 쓰기 작업의 최소 작업 단위가 된다.
• 인덱스도 페이지 단위로 관리된다.
• MySQL의 B-Tree가 자식 노드를 몇 개까지 가질 수 있는지는 인덱스의 페이지 크기와 키 값의 크기에 따라 결정된다.
• 인덱스 키 값의 크기가 커지면 디스크로부터 읽어야 하는 횟수가 늘어나고, 그만큼 느려진다. 또한 전체적인 인덱스 크기가 커진다는 것을 의미하고, 캐시 영역은 크기가 제한적이기에 인덱스 크기가 커지면 커질수록 메모리에 캐시해 둘 수 있는 레코드 수는 줄어든다. 그렇게 되면 자연히 메모리 효율이 떨어지게 된다.

B-Tree 깊이

• B-Tree 인덱스의 깊이는 중요하지만 직접 제어할 방법은 없다.
• 인덱스 키 값의 크기가 커지면 하나의 인덱스 페이지가 담을 수 있는 인덱스 키 값의 개수가 적어지고, 같은 레코드 건수라고 해도 B-Tree의 깊이가 깊어져서 디스크 읽기가 더 많이 필요하다.

선택도(기수성)

• 선택도(기수성)는 모든 인덱스 키 값 가운데 유니크한 값의 수를 의미한다. 전체 인덱스 키 값 100개인데, 유니크한 값의 수가 10개라면 기수성이 10
• 인덱스 키 값 가운데 중복된 값이 많아지면 기수성은 낮아지고, 선택도도 떨어진다. 인덱스는 선택도가 높을수록 검색 대상이 줄어들기 때문에 그만큼 빠르게 처리된다.
• 인덱스에서 유니크한 값의 개수는 인덱스나 쿼리의 효율성에 큰 영향을 미친다.

읽어야 하는 레코드의 건수

• 인덱스를 통해 읽어야 할 레코드의 건수가 전체 테이블 레코드의 20~25%를 넘어서면 인덱스를 이용하지 않고 테이블을 모두 직접 읽어서 필요한 레코드만 가려내는 필터링 방식으로 처리하는 것이 효율적이다.
• 일반적인 DBMS의 옵티마이저에서는 인덱스를 통해 레코드 1건을 읽는 것이 테이블에서 직접 레코드 1건을 읽는 것보다 4~5배 정도 비용이 더 많이 드는 작업인 것으로 예측한다.

B-Tree 인덱스를 통한 데이터 읽기

인덱스 레인지 스캔

• 가장 대표적인 방식
• 검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방식이다.
• 해당 인덱스를 구성하는 칼럼의 정순 또는 역순으로 정렬된 상태로 레코드를 가져온다. 이는 별도의 정렬 과정이 있는 것이 아니라 인덱스 자체의 정렬 특성 때문이다.
• 인덱스의 리프 노드에서 검색 조건에 일치하는 건들은 데이터 파일에서 레코드를 읽어오는 과정이 필요하다. 레코드 한 건 단위로 랜덤 IO가 한 번씩 일어나므로, 인덱스를 통해 데이터 레코드를 읽는 것은 비용이 많이 드는 작업이다. 인덱스를 통해 읽어야 할 데이터 레코드가 20~25%를 넘으면 인덱스를 통한 읽기보다 테이블의 데이터를 직접 읽는 것이 더 효율적인 방식이다.
• 레코드가 저장된 페이지를 가져와 읽어올 필요가 없는 것이 커버링 인덱스이다. 커버링 인덱스로 처리되는 쿼리는 디스크의 레코드를 읽지 않아도 돼서 랜덤 읽기가 줄어들어 성능이 빨라진다.

인덱스 레인지 스캔의 3단계

• 인덱스에서 조건을 만족하는 값이 저장된 위치를 찾는다. 이 과정을 인덱스 탐색이라고 한다.
• 1번에서 탐색된 위치부터 필요한 만큼 인덱스를 차례대로 쭉 읽는다. 이 과정을 인덱스 스캔이라고 한다.
• 2번에서 읽어 들인 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고, 최종 레코드를 읽어온다. - 커버링 인덱스로 처리되면 이 과정은 생략됨

RealMySQL 8.0 p231.

 

RealMySQL 8.0 p232.

 

인덱스 풀 스캔

• 인덱스의 처음부터 끝까지 모두 읽는 방식이다. ex. 쿼리의 조건절에 사용된 칼럼이 인덱스의 첫 번째 칼럼이 아닌 경우 사용
• 쿼리가 인덱스에 명시된 칼럼만으로 조건을 처리할 수 있는 경우 주로 이 방식이 사용된다.
• 인덱스의 전체 크기는 테이블 자체의 크기보다 훨씬 작으므로 인덱스 풀 스캔은 테이블 전체를 읽는 것보다는 적은 디스크 IO로 쿼리를 처리할 수 있다.

RealMySQL 8.0 p234.

루스 인덱스 스캔

• 듬성듬성하게 인덱스를 읽는 방식이다.
• 루스 인덱스 스캔은 인덱스 레인지 스캔과 비슷하지만, 중간에 필요치 않은 인덱스 키 값은 무시하고 다음으로 넘어가는 형태로 처리한다.
• 인덱스에서 WHERE 조건을 만족하는 범위 전체를 다 스캔할 필요가 없다는 것을 옵티마이저는 알고 있기 때문에 조건에 만족하지 않는 레코드는 무시하고 다음 레코드로 이동한다.
• 일반적으로 GROUP BY 또는 집합 함수 가운데 MAX() 또는 MIN() 함수에 대해 최적화를 하는 경우에 사용된다.

RealMySQL 8.0 p235.

인덱스 스킵 스캔

• (A, B)와 같이 칼럼 조합으로 이루어진 인덱스의 경우, WHERE 조건에 A를 사용하는 조건이 들어가야만 해당 인덱스를 사용하여 검색이 가능했다. 하지만 8.0 버전부터는 B 조건만 사용해도 옵티마이저가 최적화해 주고, 이를 인덱스 스킵 스캔이라고 한다.
• 단점
  • WHERE 조건절에 조건이 없는 인덱스의 선행 칼럼의 유니크한 값의 개수가 적어야 함 만약 유니크한 값의 개수가 많다면 MySQL 옵티마이저는 인덱스에서 스캔해야 할 시작 지점을 검색하는 작업이 많이 필요해지고, 성능이 오히려 느려질 수 있다.
  • 쿼리가 인덱스에 존재하는 칼럼만으로 처리가 가능해야 함(커버링인덱스) 그렇지 않으면 풀 테이블 스캔을 하게 된다.

ex) (gender, birth_date)로 인덱스를 만들었음 -> 쿼리를 birth_date 조건으로만 검색 -> 옵티마이저가 (gender, birth_date)로 쿼리를 다시 실행해 줌

 

주의사항

• gender 칼럼이 ENUM('M', 'F') 타입이기 때문에 이런 처리가 가능한 것은 아니다. 칼럼이 어떤 타입이더라도 MYSQL 서버는 인덱스를 루스 인덱스 스캔과 동일한 방식으로 읽으면서 인덱스에 존재하는 모든 값을 먼저 추출하고 그 결과를 이용해 인덱스 스킵 스캔을 실행한다.
• WHERE 조건절에 조건이 없는 인덱스의 선행 칼럼의 유니크한 값의 개수가 적어야 함
• 쿼리가 인덱스에 존재하는 칼럼만으로 처리 가능해야 함(커버링 인덱스)

RealMySQL 8.0 p238.

 

다중 칼럼(Multi-column) 인덱스

• 두 개 이상의 칼럼으로 구성된 인덱스 (multi-column index, concatenated index)
• 두 개의 칼럼으로 구성된 인덱스에서 두 번째 칼럼은 첫 번째 칼럼에 의존해서 정렬돼 있다. 즉, 두 번째 칼럼의 정렬은 첫 번째 칼럼이 똑같은 레코드에서만 의미가 있다. 그래서 다중 칼럼 인덱스에서는 인덱스 내에서 각 칼럼의 위치(순서)가 중요하다.

RealMySQL 8.0 p240.

B-Tree 인덱스의 정렬 및 스캔 방향

인덱스의 정렬

인덱스를 생성하는 시점에 인덱스를 구성하는 각 칼럼의 정렬을 오름차순 또는 내림차순으로 설정할 수 있다.

• 인덱스 스캔 방향 인덱스 생성 시점에 오름차순 또는 내림차순으로 정렬이 결정되지만 쿼리가 그 인덱스를 사용하는 시점에 인덱스를 읽는 방향에 따라 오름차순 또는 내림차순 정렬 효과를 얻을 수 있다. MySQL 옵티마이저는 인덱스의 읽기 방향을 전환해서 사용하도록 실행 계획을 만들어 낸다.
• 내림차순 인덱스 InnoDB에서 인덱스 역순 스캔이 정순 스캔에 비해 느릴 수밖에 없다.
  • 페이지 잠금이 인덱스 정순 스캔에 적합한 구조이기 때문이다.
  • 페이지 내에서 인덱스 레코드가 단방향으로만 연결된 구조이기 때문이다.
  • 쿼리에서 자주 사용되는 정렬 순서대로 인덱스를 생성하는 것이 잠금 병목 현상을 줄이는 데 도움이 될 것이다.

RealMySQL 8.0 p241.

 

B-Tree 인덱스의 가용성과 효율성

비교 조건의 종류와 효율성

• 다중 칼럼 인덱스에서 각 칼럼의 순서와 그 칼럼에 사용된 조건이 동등 비교인지 아니면 크다 또는 작다 같은 범위 조건인지에 따라 각 인덱스 칼럼의 활용 형태, 효율이 달라진다.
• 작업 범위를 결정하는 조건은 많으면 많을수록 쿼리 처리 성능을 높이지만 체크 조건은 많다고 해서 쿼리의 처리 성능을 높이진 못한다.

인덱스의 사용성

• B-Tree 인덱스의 특징은 왼쪽 값에 기준해서 오른쪽 값이 정렬돼 있다는 것이다.
• 하나의 칼럼으로 검색해도 값의 왼쪽 부분이 없으면 인덱스 레인지 스캔 방식의 검색이 불가능하다.

가용성과 효율성 판단

B-Tree 인덱스 특성상, 작업 범위 결정 조건으로 사용할 수 없는 조건들 (체크 조건으로는 가능)

• NOT-EQUAL로 비교된 경우
• LIKE ‘%??’ (앞부분이 아닌 뒷부분 일치) 형태로 문자열 패턴이 비교된 경우
• 스토어드 함수나 다른 연산자로 인덱스 칼럼이 변형된 후 비교된 경우
• NOT-DETERMINISTIC 속성의 스토어드 함수가 비교 조건에 사용된 경우
• 데이터 타입이 서로 다른 비교
• 문자열 데이터 타입의 콜레이션이 다른 경우

 

R-Tree 인덱스

• 공간 인덱스는 R-Tree 인덱스 알고리즘을 이용해 2차원의 데이터를 인덱싱 하고 검색하는 목적의 인덱스다.
• B-Tree는 인덱스를 구성하는 칼럼의 값이 1차원의 스칼라 값인 반면, R-Tree 인덱스는 2차원의 공간 개념 값이다.

전문 검색 인덱스

• 문서의 내용 전체를 인덱스화해서 특정 키워드가 포함된 문서를 검색하는 전문 검색에는 InnoDB나 MyISAM 스토리지 엔진에서 제공하는 일반적인 용도의 B-Tree 인덱스를 사용할 수 없다.

 

인덱스 알고리즘

• 전문 검색에서는 문서 본문의 내용에서 사용자가 검색하게 될 키워드를 분석해 내고, 빠른 검색용으로 사용할 수 있게 이러한 키워드로 인덱스를 구축한다.
• 문서의 키워드를 인덱싱하는 기법에 따라 크게 단어의 어근 분석과 n-gram 분석 알고리즘으로 구분할 수 있다.

어근 분석 알고리즘

• 어근 분석은 검색어로 선정된 단어의 뿌리인 원형을 찾는 작업이다.
• MySQL 서버에서는 오픈소스 형태소 분석 라이브러리인 MeCab을 플러그인 형태로 사용할 수 있게 지원한다.

n-gram알고리즘

• 형태소 분석이 문장을 이해하는 알고리즘이라면, n-gram은 단순히 키워드를 검색해 내기 위한 인덱싱 알고리즘이다.
• n-gram이란 본문을 무조건 몇 글자씩 잘라서 인덱싱하는 방법이다. 형태소 분석보다 간단하지만, 인덱스 크기가 상당히 크다.
• n글자짜리 토큰으로 구분하고, 토큰들에서 불용어를 걸러낸 후 전문 검색 인덱스에 등록한다.

불용어 변경 및 삭제

• MySQL의 기본 불용어 리스트를 사용하면 더 혼란스러워질 수 있다.
• 전문 검색 인덱스의 불용어 처리 무시하고, 사용자 정의 불용어를 사용할 수도 있다.

전문 검색 인덱스의 가용성

전문 검색 인덱스를 사용하려면 아래 두 가지 조건을 갖춰야 한다.

• 쿼리 문장이 전문 검색을 위한 문법(match … against …)을 사용
• 테이블이 전문 검색 대상 칼럼에 대해 전문 인덱스 보유

함수 기반 인덱스

• 칼럼의 값을 변형해서 만들어진 값에 대해 인덱스를 구축해야 할 때 사용하는 것이 함수 기반 인덱스이다.
• 함수 기반 인덱스는 인덱싱할 값을 계산하는 과정의 차이만 있고, 실제 인덱스의 내부 구조 및 유지관리 방법은 B-Tree 인덱스와 동일하다.
• 함수 기반 인덱스를 구현하는 방법
  • 가상 칼럼을 이용한 인덱스
  • 함수를 이용한 인덱스

가상 칼럼을 이용한 인덱스

• 예를 들어, 두 개의 칼럼을 합쳐서 검색해야 하는 경우, 두 개의 칼럼을 합친 가상 칼럼을 추가하고 해당 가상 칼럼에 인덱스를 생성할 수 있다.
• 가상 칼럼은 테이블에 새로운 칼럼을 추가하는 것과 같은 효과를 내기 때문에 실제 테이블의 구조가 변경된다는 단점이 있다.
• ex) ALTER TABLE user ADD full_name VARCHAR(30) AS (CONCAT(first_name, ' ' , last_name)) VIRTUAL, ADD INDEX ix_fullname (full_name);

함수를 이용한 인덱스

• 함수를 직접 사용하는 인덱스는 테이블의 구조는 변경하지 않고, 계산된 결괏값의 검색을 빠르게 만들어준다.
• 함수 기반 인덱스를 제대로 활용하려면 반드시 조건절에 함수 기반 인덱스에 명시된 표현식이 그대로 사용돼야 한다.
• ex) CREATE TABLE user(~~ PRIMARY KEY(user_id), INDEX ix_full_name ((CONCAT(first_name,  ' ' , last_name)));

멀티 밸류 인덱스

• 전문 검색 인덱스를 제외한 모든 인덱스는 인덱스 키와 데이터 레코드가 1:1 관계를 가진다.
• 멀티 밸류 인덱스는 하나의 데이터 레코드가 여러 개의 키 값을 가질 수 있는 형태의 인덱스다.

클러스터링 인덱스

• 클러스터링 인덱스는 테이블의 프라이머리 키 값이 비슷한 레코드끼리 묶어서 저장하는 것이다.
• 프라이머리 키 값에 의해 레코드의 저장 위치가 결정된다. 프라이머리 키 값으로 클러스터링 된 테이블은 프라이머리 키 값 자체에 대한 의존도가 상당히 크므로 신중히 프라이머리 키를 설정해야 한다.
• 클러스터링 인덱스는 프라이머리 키 값에 의해 레코드 저장 위치가 결정되므로 사실 인덱스 알고리즘이라기보다 테이블 레코드의 저장 방식이라고 볼 수 있다. 그래서 클러스터링 인덱스와 클러스터링 테이블이 동의어로 쓰이기도 한다.
• 프라이머리 키가 없는 경우에는 InnoDB 스토리지 엔진이 프라이머리 키를 대체할 칼럼을 선택한다. - NOT NULL 옵션의 유니크 인덱스 중에서 첫 번째 인덱스를 선택, 자동으로 유니크한 값을 가지도록 증가되는 칼럼을 내부적으로 추가한 후 선택
• 프라이머리 키나 유니크 인덱스가 없는 InnoDB 테이블에서는 아무 의미 없는 값으로 클러스터링 되는데, 이는 아무 이점이 없다. 클러스터링 인덱스는 테이블당 하나만 가질 수 있는 엄청난 혜택이므로 꼭 프라이머리 키를 생성하자.

RealMySQL 8.0 p272.

세컨더리 인덱스에 미치는 영향

• InnoDB 테이블의 모든 세컨더리 인덱스는 해당 레코드가 저장된 주소가 아니라 프라이머리 키 값을 저장하도록 구현돼 있다.
• 조금 복잡하지만 프라이머리 키가 더 큰 장점을 제공하기 때문에 성능 저하에 대해 걱정하지 않아도 된다.

 

클러스터링 인덱스의 장점과 단점

장점

• 프라이머리 키로 검색할 때 처리 성능이 매우 빠름
• 테이블의 모든 세컨더리 인덱스가 프라이머리 키를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많음

단점

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

 

클러스터링 테이블 사용 주의사항

클러스터링 인덱스 키의 크기

• 클러스터링 테이블의 경우 모든 세컨더리 인덱스가 프라이머리 키 값을 포함한다.
• 그래서 프라이머리 키의 크기가 커지면 세컨더리 인덱스도 자동으로 크기가 커진다.

프라이머리 키는 AUTO-INCREMENT보다는 업무적인 칼럼으로 생성

• 칼럼의 크기가 크더라도 업무적으로 해당 레코드를 대표할 수 있다면 그 칼럼을 프라이머리 키로 설정하는 것이 좋다.

프라이머리 키는 반드시 명시할 것

• InnoDB 테이블에서 프라이머리 키를 정의하지 않으면 스토리지 엔진이 내부적으로 일련번호 칼럼을 추가한다.
• 이렇게 추가된 칼럼은 사용자에게 보이지 않아 접근할 수 없다.

AUTO-INCREMENT 칼럼을 인조 식별자로 사용할 경우

• 세컨더리 인덱스도 필요하고 프라이머리 키의 크기도 길다면 AUTO-INCREMENT 칼럼을 추가하고 이를 프라이머리 키로 설정하면 된다. 이를 인조 식별자라고 한다.

유니크 인덱스

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

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

인덱스 읽기

• 유니크하지 않은 세컨더리 인덱스에서 한 번 더 해야 하는 작업은 디스크 읽기가 아니라, CPU에서 칼럼값을 비교하는 작업이라 성능상 영향이 없다.
• 유니크하지 않은 세컨더리 인덱스는 중복된 값이 허용되므로 읽어야 할 레코드가 많아서 느린 것이지 인덱스 자체의 특성 때문에 느린 것이 아니다.

인덱스 쓰기

• 유니크 인덱스의 키 값을 쓸 때는 중복된 값 유무를 체크하는 과정이 필요하다. 그래서 유니크하지 않은 세컨더리 인덱스의 쓰기보다 느리다.
• 유니크 인덱에서 중복된 값을 체크할 때는 읽기 잠금을 사용하고, 쓰기를 할 때는 쓰기 잠금을 사용하는데 이 과정에서 데드락이 아주 빈번히 발생한다.

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

• 유일성이 꼭 보장돼야 하는 칼럼에 대해서는 유니크 인덱스를 생성하되, 꼭 필요하지 않다면 유니크 인덱스보다는 유니크하지 않은 세컨더리 인덱스 생성을 고려하자.

외래키

• 외래키 제약이 설정되면 자동으로 연관되는 테이블의 칼럼에 인덱스까지 생성된다.

외래키 관리 특징

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

 

 

출처

RealMySQL 8.0 1권