[MySQL] 인덱스 2

인덱스 1은 여기에서 확인하실 수 있습니다.

클러스터링 인덱스

---

클러스터링 인덱스는 테이블의 프라이머리 키에 대해서만 적용되는 내용이다. 즉, 프라이머리 키 값이 비슷한 레코드끼리 묶어서 저장하는 것을 클러스터링 인덱스라고 표현한다.

여기서 중요한 것은 프라이머리 키 값에 의해 레코드의 저장 위치가 결정된다는 것이다. 프라이머리 키 값이 변경된다면 그 레코드의 물리적인 저장 위치가 바뀌어야 한다는 것을 의미한다. 일반적으로 InnoDB와 같이 항상 클러스터링 인덱스로 저장되는 테이블은 프라이머리 키 기반의 검색이 매우 빠르지만, 레코드의 저장이나 프라이머리 키의 변경이 상대적으로 느리다는 특징이 있다.

위 그림의 클러스터링 인덱스 구조를 보면 클러스터링 테이블의 구조 자체는 일반 B-Tree와 비슷하지만, 세컨더리 인덱스를 위한 B-Tree의 리프 노드와는 달리 클러스터링 인덱스의 리프 노드에는 레코드의 모든 칼럼이 같이 저장되어 있다.

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

InnoDB 테이블(클러스터링 테이블)의 모든 세컨더리 인덱스는 해당 레코드가 저장된 주소가 아니라 프라이머리 키 값을 저장하도록 구현되어 있다.

아래 employees 테이블에서 first_name 칼럼으로 검색하는 경우 프라이머리 키로 클러스터링된 InnoDB와 그렇지 않은 MyISAM에서 어떤 차이가 있는지 한번 살펴보자.

mysql> CREATE TABLE employees (
                  emp_no INT NOT NULL,
                  first_name VARCHAR(20) NOT NULL,
                  PRIMARY KEY (emp_no),
                  INDEX ix_firstname(first_name)
              );

mysql> SELECT * FROM employees WHERE first_name = 'Aamer';

• MyISAM: ix_firstname 인덱스를 검색해서 레코드의 주소를 확인한 후, 레코드의 주소를 이용해 최종 레코드를 가져옴
• InnoDB: ix_firstname 인덱스를 검색해 레코드의 프라이머리 키 값을 확인한 후, 프라이머리 키 인덱스를 검색해서 최종 레코드를 가져옴

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

장점

• 프라이머리 키(클러스터링 키)로 검색할 때 처리 성능이 매우 빠름(특히, 프라이머리 키를 범위 검색하는 경우 매우 빠름)
• 테이블의 모든 세컨더리 인덱스가 프라이머리 키를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많음

단점

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

클러스터링 인덱스의 장점은 빠른 읽기(SELECT)이며, 단점은 느린 쓰기(INSERT, UPDATE, DELETE)라는 것을 알 수 있다. 일반적으로 웹 서비스와 같은 온라인 트랜잭션 환경에서는 쓰기와 읽기 비율이 2:8 또는 1:9 정도이기 때문에 조금 느린 쓰기를 감수하고 읽기를 빠르게 유지하는 것이 매우 중요하다.

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

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

클러스터링 테이블의 경우 모든 세컨더리 인덱스가 프라이머리 키(클러스터링 키)를 포함하기 때문에 프라이머리 키의 크기가 커지면 세컨더리 인덱스도 자동으로 크기가 커진다. 일반적으로 테이블에 세컨더리 인덱스가 4~5개 정도 생성된다는 것을 고려하면 세컨더리 인덱스 크기는 급격히 증가한다.

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

InnoDB 테이블에서는 프라이머리 키를 정의하지 않으면 내부적으로 일련번호 칼럼을 추가하는데, 이렇게 자동으로 추가된 칼럼은 사용자에게 보이지 않기 때문에 사용자가 전혀 접근(사용)할 수가 없다.

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

여러 개의 칼럼이 복합으로 프라이머리 키가 만들어지는 경우 프라이머리 키의 크기가 길어지게 된다. 하지만 프라이머리 키의 크기가 길어도 세컨더리 인덱스가 필요하지 않다면 그대로 프라이머리 키를 사용하는 것이 좋다.

유니크 인덱스

---

유니크는 인덱스라기보다는 제약 조건에 가깝다고 볼 수 있다. 말 그대로 테이블이나 인덱스에 같은 값이 2개 이상 저장될 수 없음을 의미하는데, MySQL에서는 인덱스 없이 유니크 제약만 설정할 방법이 없다.

인덱스 읽기

많은 사람들이 유니크 인덱스는 1건만 읽으면 되지만, 유니크하지 않은 세컨더리 인덱스에서는 레코드를 한 건 더 읽어야 하므로 느리다고 이야기한다. 하지만 유니크하지 않은 세컨더리 인덱스에서 한 번 더 해야 하는 작업은 디스크 읽기가 아니라 CPU에서 칼럼값을 비교하는 작업이기 때문에 성능상 영향이 거의 없다고 볼 수 있다.

유니크하지 않은 세컨더리 인덱스는 중복된 값이 허용되므로 읽어야 할 레코드가 많아서 느린 것이지, 인덱스 자체의 특성 때문에 느린것이 아니라는 것이다. 즉, 레코드 1건을 읽는데 0.1초가 걸렸고 2건을 읽을 때 0.2초가 걸렸다고 했을 때 후자를 느리게 처리됐다고 할 수 없는 것과 같은 이치다. 읽어야 할 레코드 건수가 같다면 성능상의 차이는 미미하다.

인덱스 쓰기

새로운 레코드가 INSERT되거나 인덱스 칼럼의 값이 변경되는 경우에는 인덱스 쓰기 작업이 필요하다. 유니크 인덱스의 키 값을 쓸 때는 중복된 값이 있는지 없는지 체크하는 과정이 한 단계 더 필요하기 때문에 유니크하지 않은 세컨더리 인덱스의 쓰기보다 느리다.

InnoDB 스토리지 엔진에는 인덱스 키의 저장을 버퍼링하기 위해 체인지 버퍼가 사용되므로, 인덱스의 저장이나 변경 작업이 상당히 빨리 처리되지만 유니크 인덱스는 반드시 중복 체크를 해야 하므로 작업 자체를 버퍼링하지 못한다. 이 때문에 유니크 인덱스는 일반 세컨더리 인덱스보다 변경 작업이 더 느리게 작동한다.

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

단순히 더 좋은 성능을 위해 불필요하게 유니크 인덱스를 생성하지 않는 것이 좋다. MySQL의 유니크 인덱스는 일반 인덱스와 같은 역할을 하므로 중복해서 인덱스를 생성할 필요는 없다. 즉, 유니크 인덱스도 일반 세컨더리 인덱스와 같은 역할을 동일하게 수행할 수 있으므로 아래와 같이 세컨더리 인덱스(ix_nickname)를 중복으로 만들어 줄 필요는 없다.

mysql> CREATE TABLE tb_unique (
                  id INT NOT NULL,
                  nick_name VARCHAR(100),
                  PRIMARY KEY (id),
                  UNIQUE ux_nickname(nick_name),
                  INDEX ix_nickname(nick_name)
              );

요약

• 클러스터링 인덱스

  • 클러스터링 인덱스는 프라이머리 키 값이 비슷한 레코드끼리 묶어서 저장하는 형태로 구현된다.
  • 클러스터링 인덱스는 프라이머리 키 기반의 검색이 매우 빠르지만, 레코드의 저장이나 프라이머리 키의 변경이 상대적으로 느리다
  • 클러스터링 테이블의 모든 세컨더리 인덱스는 해당 레코드가 저장된 주소가 아닌 프라이머리 키 값을 저장한다.
  • 클러스터링 인덱스의 장점은 빠른 읽기(SELECT)이며, 단점은 느린 쓰기(INSERT, UPDATE, DELETE)이다.
  • 프라이머리 키의 크기가 커지면 세컨더리 인덱스도 자동으로 크기가 커진다.
  • 프라이머리 키를 정의하지 않으면 내부적으로 일련번호 칼럼을 추가하는데, 이 칼럼은 사용자가 접근할 수 없다.

• 유니크 인덱스

  • 유니크 인덱스와 유니크하지 않은 인덱스는 읽어야 할 레코드 건수의 차이일 뿐이지, 성능상의 차이는 미미하다.
  • 유니크 인덱스는 저장이나 변경 시에 반드시 중복 체크를 하기 때문에 버퍼링을 하지 못한다. 그로 인해 일반 세컨더리 인덱스보다 더 느리게 작동된다.
  • 유니크 인덱스는 일반 인덱스와 같은 역할을 하므로 중복해서 인덱스를 생성할 필요가 없다.

Reference
백은빈, 이성욱. 『Real MySQL 8.0』. 위키북스, 2022