Blog
책 리뷰
리얼 MySQL
8장 인덱스

RealMySQL 08장 인덱스

디스크 읽기 방식

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

하드 디스크의 플래터를 돌려서 읽어야 할 데이터가 저장된 위치로 디스크 헤더를 이동시킨 다음 데이터를 읽어야 한다.

  • 랜덤 IO : 데이터의 개수만큼 디스크 헤드를 이동시키는 방식으로 예를 들면 다양한 index 레코드를 조회해서 데이터를 가져와야 하는 상황을 말한다.
  • 순차 IO : 디스크 헤드를 한 번만 이동시키는 방식으로 예를 들면 하나의 index 레코드에 대해서 다수의 데이터를 가져오는 등의 상황을 말한다.

디스크에 자료를 읽고 쓰는 작업에서 디스크 헤드를 이동시키는 작업이 가장 시간이 많이 걸리는 작업이다.
SSD는 기존 하드 디스크 드라이브에서 데이터 저장용 플래터를 제거하고 그 대신 플래시 메모리를 장착하고 있다. 디스크 원판을 기계적으로 회전시킬 필요가 없으므로 아주 빨리 데이터를 읽고 쓸 수 있다.
플래시 메모리는 전원이 공급되지 않아도 데이터가 삭제되지 않는다. 그리고 메모리(D-Ram)보다는 느리지만 기계식 하드 디스크 드라이브보다는 훨씬 빠르다.

인덱스 레인지 스캔은 데이터를 읽기 위해 주로 랜덤 IO를 사용하며, 풀 테이블 스캔은 순차 IO를 사용한다. 그래서 큰 테이블의 레코드 대부분을 읽는 작업에서는 인덱스를 사용하지 않고 풀 테이블 스캔을 사용하도록 유도할 때도 있다. 이는 순차 IO가 랜덤 IO보다 훨씬 많은 레코드를 읽어올 수 있기 때문인데, 이런 형태는 OLTP(On-Line Transaction Processing) 성격의 웹 서비스보다는 데이터 웨어하우스나 통계 작업에서 주로 사용된다.

인덱스란?

  • 결론적으로 인덱스란 데이터의 저장 성능을 희생하고 그 대신 데이터의 읽기 속도를 높이기 위해 사용하는 자료구조이다.
  • 프라이머리 키는 Null 값을 허용하지 않으며 중복을 허용하지 않는 것이 특징이다.
  • 프라이머리 키를 제외한 나머지 모든 인덱스는 **세컨더리 인덱스(Secondary Index)**라고 한다.
  • 해시 인덱스 알고리즘은 값으로 해시값을 계산해서 인덱싱하는 알고리즘으로, 매우 빠른 검색을 지원한다.
    • 하지만 값을 변형해서 인덱싱하므로 전방 일치와 같이 일부만 검색하거나 범위를 검색할 때는 해시 인덱스를 사용할 수 없다.
    • Hash 인덱스는 주로 메모리 기반의 데이터베이스에서 많이 사용한다.
  • 유니크 인덱스에 대해 동등 조건으로 검색한다는 것은 항상 1건의 레코드만 찾으면 더 찾지 않아도 된다는 것을 옵티마이저에게 알려주는 효과를 낸다.

B-Tree 인덱스

  • B-Tree의 B는 Balanced를 의미한다.

구조 및 특성

  • 최상위에 하나의 루트 노드가 존재하고 그 하위에 자식 노드가 붙어 있는 형태다.
  • 트리 구조의 가장 하위에 있는 노드를 리프 노드라고 하고 트리 구조에서 루트 노드도 아니고 리프 노드도 아닌 중간의 노드를 브랜치 노드라고 한다.
  • 인덱스의 리프 노드는 항상 실제 데이터 레코드를 찾아가기 위한 주솟값을 가지고 있다.
  • MyISAM 테이블은 세컨더리 인덱스가 물리적인 주소를 가지는 반면, InnoDB 테이블은 프라이머리 키를 주소처럼 사용하기 때문에 논리적인 주소를 가진다고 볼 수 있다.
    • InnoDB는 인덱스에 저장돼 있는 프라이머리 키 값을 이용해 프라이머리 키 인덱스를 한 번 더 검색한 후, 프라이머리 키 인덱스의 리프 페이지에 저장돼 있는 레코드를 읽는다.
    • 즉, InnoDB 스토리지 엔진에서는 모든 세컨더리 인덱스 검색에서 데이터 레코드를 읽기 위해서는 반드시 프라이머리 키를 저장하고 있는 B-Tree를 다시 한 번 검색해야 한다.

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

인덱스 키 추가

  • 새로운 값이 B-Tree에 저장될 때 테이블의 스토리지 엔진에 따라 새로운 키 값이 즉시 인덱스에 저장될 수도 있고 그렇지 않을 수도 있다.
    • InnoDB 엔진: InnoDB는 인덱스 키 추가 작업을 지연시켜 나중에 처리하는 방법을 사용하며, 이를 통해 성능을 최적화합니다. 이 과정에서 **체인지 버퍼(Change Buffer)**를 활용하여 인덱스 업데이트를 지연 처리합니다.
    • 체인지 버퍼(Change Buffer): InnoDB는 변경해야 할 인덱스 페이지가 버퍼 풀에 없을 경우, 해당 변경 내용을 체인지 버퍼에 임시 저장하고, 이후 적절한 시점에 실제 인덱스에 반영합니다. 이를 통해 디스크 I/O를 줄이고 성능을 향상시킵니다.
    • 중복 여부를 체크해야 하는 유니크 인덱스의 경우, 즉시 추가하거나 삭제해서 데이터 무결성을 유지한다.
  • B-Tree에 저장될 때는 저장될 키 값을 이용해 B-Tree 상의 적절한 위치를 검색해야 한다.
  • 저장될 위치가 결정되면 레코드의 키 값과 대상 레코드의 주소 정보를 B-Tree의 리프 노드에 저장한다.
  • 리프 노드가 꽉 차서 더는 저장할 수 없을 때는 리프 노드가 분리(Split)돼야 하는데, 이는 상위 브랜치 노드까지 처리의 범위가 넓어진다.

인덱스 키 삭제

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

인덱스 키 변경

  • B-Tree 키 값의 변경 작업은 먼저 키 값을 삭제한 후, 다시 새로운 키 값을 추가하는 형태로 처리된다.
  • 키 값의 변경 때문에 발생하는 B-Tree 인덱스 키 값의 삭제와 추가 작업은 앞에서 설명한 절차대로 처리된다.

인덱스 키 검색

  • 100% 일치 또는 값의 앞부분(Left-most part)만 일치하는 경우에 사용할 수 있다.
  • InnoDB 테이블에서 지원하는 레코드 잠금이나 넥스트 키락(갭락)이 검색을 수행한 인덱스를 잠근 후 테이블의 레코드를 잠그는 방식으로 구현돼 있다.
    • 따라서 UPDATE나 DELETE 문장이 실행될 때 적절히 사용할 수 있는 인덱스가 없으면 불필요하게 많은 레코드를 잠근다.

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

  • B-Tree 인덱스는 인덱스를 구성하는 칼럼의 크기와 레코드의 건수, 그리고 유니크한 인덱스 키 값이 개수 등에 의해 검색이나 변경 작업의 성능이 영향을 받는다.

인덱스 키 값의 크기

  • InnoDB 스토리지 엔진은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지(Page) 또는 블록(Block)이라고 하며 디스크의 모든 읽기 및 쓰기 작업의 최소 작업 단위가 된다.
  • B-Tree가 자식 노드를 몇 개까지 가지는지는 인덱스의 페이지 크기와 키 값의 크기에 따라 결정된다.
  • 자식 노드의 개수가 500개라면 인덱스 페이지 한 번으로 해결될 수도 있지만 대부분의 경우 최소한 2번 이상 디스크로부터 읽어야 한다.
  • 결국 인덱스를 구성하는 키 값의 크기가 커지면 디스크로부터 읽어야 하는 횟수가 늘어나고 그만큼 느려진다.

B Tree의 깊이

  • B-Tree 인덱스의 깊이는 상당히 중요하지만 직접 제어할 방법은 없다.
  • 인덱스의 B-Tree 깊이가 3인 경우 키 값이 16 바이트 일 때 최대 2억개 정도의 키 값을 담을 수 있고 키 값이 32 바이트일 경우에는 5천만개로 줄어든다.
  • B-Tree의 깊이는 MySQL에서 값을 검색할 때 몇 번이나 랜덤하게 디스크를 읽어야 하는지와 직결되는 문제다.
  • 결론적으로 인덱스 키 값의 크기가 커지면 커질수록 하나의 인덱스 페이지가 담을 수 있는 키 값의 개수가 적어지고 그 때문에 같은 레코드 건수라 하더라도 B-Tree가 깊어져서 디스크 읽기가 더 많이 필요하게 된다.

선택도(가수성, Cardinality)

  • 모든 인덱스 키 값 가운데 유니크한 값의 개수를 의미한다.
  • 인덱스 키 값 가운데 중복된 값이 많아지면 많아질수록 기수성은 낮아지고 동시에 선택도 또한 떨어진다.
  1. country 칼럼의 유니크 값이 10개일 때:
  • tb_city 테이블에는 10개의 국가(country)와 도시(city) 정보가 저장됨.
  • MySQL 인덱스는 전체 레코드 수를 유니크 값의 개수로 나누어 특정 조건 검색 시 대략 몇 건의 레코드가 일치할지 예측 가능.
  • country='KOREA' 조건으로 검색 시 약 1000건(10,000/10) 예상. 불 필요하게 많은 999건의 city에 대해서도 탐색 과정을 거침.
  1. country 칼럼의 유니크 값이 1000개일 때:
  • tb_city 테이블에는 1000개의 국가와 도시 정보가 저장됨.
  • 동일한 방식으로 country='KOREA' 검색 시 약 10건(10,000/1,000) 예상. 9건의 불필요한 city 탐색 과정을 거침.
  1. 결론:
  • 두 경우 모두 최종적으로 1건의 데이터를 읽어오지만, 인덱스 효율성은 유니크 값의 개수에 영향을 받음.
  • 유니크 값이 적을수록 쿼리 효율이 낮아질 가능성이 있음.

읽어야 하는 레코드의 건수

  • 일반적으로 옵티마이저는 인덱스를 통해서 레코드를 읽는 것이 테이블에서 직접 레코드 1건을 읽는 것보다 4~5배 정도 비용이 많이 드는 작업으로 예측한다.
  • 즉, 인덱스를 통해 읽어야 할 레코드의 건수가 전체 테이블 레코드의 20~25%를 넘어서면 인덱스를 이용하지 않는다. 👉 (Full Table Scan)

인덱스를 통한 데이터 읽기

인덱스를 이용한 레인지 스캔

  • 루트 노드에서부터 비교를 시작해 브랜치 노드를 거치고 최종적으로 리프 노드까지 찾아 들어가야만 비로서 필요한 레코드의 시작 지점을 찾을 수 있다.
  • 일단 시작해야 할 위치를 찾으면 그때부터는 리프 도느의 레코드만 순서대로 읽으면 된다.
  • 이후 레코드 한 건 한 건의 단위로 랜덤 IO가 한 번씩 읽어난다.
  • 랜덤 IO가 많이 일어날 수 있기 때문에 인덱스를 통해 데이터 레코드를 읽는 작업은 비용이 많이 드는 작업으로 분류된다.
  • 그리고 인덱스를 통해 읽어야 할 데이터 레코드가 20~25%를 넘으면 인덱스를 통한 읽기보다 테이블의 데이터를 직접 읽는 것이 더 효율적인 처리 방식이 된다.
  • 그리고 이 과정이 스킵되는 경우가 있는데 디스크에서 들고와야 하는 정보가 인덱스에 다 있는 경우, 즉 커버링 인덱스를 사용하는 경우는 이 과정이 스킵된다.

인덱스 풀 스캔

  • 먼저 인덱스 리프 노드의 제일 앞 또는 제일 뒤로 이동한 후, 인덱스의 리프 노드를 연결하는 링크드 리스트를 따라서 처음부터 끝까지 스캔하는 방식을 의미한다.
  • 인덱스뿐만 아니라 데이터 레코드까지 모두 읽어야 한다면 절대 이 방식으로 처리되지 않는다.

루스 인덱스 스캔

  • 루스 인덱스 스캔은 중간에 필요치 않은 인덱스 키 값은 무시(SKIP)하고 다음으로 넘어가는 형태로 처리한다.
  • 일반적으로 GROUP BY 또는 집합 함수 가운데 MAX() 또는 MIN() 함수에 대해 최적화를 하는 경우에 사용된다.
SELECT depth_no, MIN(emp_no)
FROM dept_emp
WHERE dep_no BETWEEN 'd002' AND 'd004'
GROUP BY dept_no;
  • 이 인덱스는 (dept_no, emp_no) 조합으로 정렬까지 돼 있어서 dept_no 그룹별로 첫 번째 레코드의 emp_no 값만 읽으면 된다.
  • 즉, 인덱스에서 where 조건을 만족하는 범위 전체를 다 스캔할 필요가 없다는 것을 옵티마이저는 알고 있기 때문에 조건에 만족하지 않는 레코드는 무시하고 다음 레코드로 이동한다.

스킵 인덱스 스캔

  • 다중 컬럼 인덱스가 걸려있는 경우 첫 번째 컬럼에 의존해서 두 번째 컬럼이 정렬되기 때문에 컬럼의 순서가 매우 중요하지만, 이를 무시하고 두 번째 컬럼만으로 인덱스를 사용할 수 있게 해주는 방식이다.

  • 예외조건

    • WHERE 조건절에 조건이 없는 인덱스의 선행 칼럼의 유니크한 값의 개수가 적어야함
    • 쿼리가 인덱스에 존재하는 칼럼만으로 처리가 가능해야함.

  • 인덱스 조회 최적화 기능, Skip Scan에 대해 알아보자 (opens in a new tab)

  • 좀 더 세부적인 동작을 보자면 아래와 같은 인덱스가 있을 때, 인덱스를 사용하지 못하는 쿼리가 인덱스를 사용하는 쿼리 여러 개로 나눠서 실행된다고 볼 수 있다.

ALTER TABLE employees ADD INDEX idx_gender_hire_date(gender, hire_date);
 
-- // 인덱스를 사용하지 못하는 쿼리
SELECT * FROM employees WHERE hire_date = '1990-01-01';
-- // 아래 두 쿼리로 나눠서 실행 됨
SELECT * FROM employees WHERE gender = 'M' AND hire_date = '1990-01-01';
SELECT * FROM employees WHERE gender = 'F' AND hire_date = '1990-01-01';

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

  • 컬럼을 2개 이상 사용하는 인덱스를 다중 컬럼 인덱스라고 한다.
  • 컬럼을 지정한 순서가 중요하다.

함수 기반 인덱스

가상 컬럼을 이용한 인덱스

아래와 같은 테이블이 있는데,

CREATE TABLE user (
  user_id BIGINT,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  PRIMARY KEY(user_id)
);

MySQL 버전부터 다음과 같이 가상 칼럼을 추가하고 그 가상 칼럼에 인덱스를 생성할 수 있게 됐다.

ALTER TABLE user
--// VIRTUAL 외에도 SORTED 라는 옵션도 있다.
ADD COLUMN full_name VARCHAR(100) AS (CONCAT(first_name, ' ', last_name)) STORED,
ADD INDEX idx_full_name(full_name);

해당 가상 칼럼에 인덱스를 생성할 수 있다. 새로운 칼럼을 추가하는 것과 같은 효과를 내기 때문에 실제 테이블의 구조가 변경된다는 단점이 있다.

함수를 이용한 인덱스

MySQL8.0 버전부터는 다음과 같이 테이블의 구조를 변경하지 않고, 함수를 직접 사용하는 인덱스를 생성할 수 있게 됐다.

CREATE TABLE user (
  user_id BIGINT,
  first_name VARCHAR(10),
  last_name VARCHAR(10),
  PRIMARY KEY(user_id),
  INDEX ix_fullname ((CONCAT(first_name, ' ', last_name)))
);

함수를 직접 사용하는 인덱스는 테이블의 구조 변경 없이 생성할 수 있다. 하지만 인덱스를 사용할 때 WHERE 조건 절에 정확한 표현식을 사용해야 한다. 예를 들어,

EXPLAIN SELECT * FROM user WHERE CONCAT(first_name, ' ', last_name) = 'John Doe';

이런 쿼리라고 해도 space bar의 숫자 때문에 인덱스를 안탈 수도 있다.

멀티밸류 인덱스

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

CREATE TABLE user (
  user_id BIGINT AUTO_INCREMENT PRIMARY_KEY,
  first_name VARCHAR(10),
  last_name VARCHAR(10),
  credit_info JSON,
  INDEX mx_creditscores ((CAST(credit_info->'$.credit_scores' AS UNSIGNED ARRAY)))
);
  • 조회 쿼리
SELECT * FROM user WHERE 360 MEMBER OF(credit_info->'$.credit_scores');
EXPLAIN SELECT * FROM user WHERE 360 MEMBER OF(credit_info->'$.credit_scores');

클러스터링 인덱스

MySQL에서 클러스터링 인덱스는 InnoDB 스토리지 엔진에서만 지원한다. 프라이머리 키 값이 변경된다는 얘기는 그 레코드의 물리적인 저장 위치가 바뀐다는 의미다. 프라이머리 키 값에 의해 레코드의 저장 위치가 결정되므로 사실 인덱스 알고리즘이라기보다 테이블 레코드의 저장 방식이라고 볼 수 있다.
프라이머리 키 값으로 정렬되어 저장된 경우만 "클러스터링 인덱스" 또는 "클러스터링 테이블"이라고 한다.

프라이머리 키가 없는 경우

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

세컨더리 인덱스

클러스터링 키 값이 변경될 때마다 데이터 레코드의 주소가 변경되고 그때마다 해당 테이블의 모든 인덱스에 저장된 주솟값을 변경해야하는 불상사를 막기위해 모든 세컨더리 인덱스는 해당 레코드가 저장된 주소가 아니라 프라이머리 키 값을 저장하도록 구현되있다.

클러스터스 인덱스 장단점

  • 장점
    • 프라이머리 키로 처리할 때 처리 성능이 매우 빠름
    • 테이블 모든 세컨더리 인덱스가 프라이머리 키를 가지고 있기 때문에 인덱스만으로 처리될 수 있는 경우가 많음(이를 커버링 인덱스라고 함)
  • 단점
  • 테이블의 모든 세컨더리 인덱스가 클러스터링 키를 갖기 때문에 클러스터링 키 값이 클 경우 전체적으로 인덱스의 크기가 커짐
  • 세컨더리 인덱스를 통해 검색할 때 프라이머리 키로 다시 한 번 검색해야 하기 때문에 처리 성능이 느림
  • INSERT 할 때 프라이머리 키에 의해 레코드의 저장 위치가 결정되므로 성능 느림
  • 프라이머리 키를 변경할 때 레코드를 DELETE하고 INSERT하는 작업이 발생하므로 성능 느로

프라이머리 키 사용시 주의사항

프라이머리 키는 반드시 선언해라라는 내용인거 같음

유니크 인덱스

사실상 제약조건에 가깝다. NULL도 저장될 수 있는데 1개만(NULL 두 개면 안됨) 저장된다. MySQL에서 프라이머리 키는 기본적으로 NULL을 허용하지 않는 유니크 속성이 자동으로 부여된다.

인덱스 읽기

1개만 읽을거냐 vs 여러개를 읽을거냐의 차이가 있는거지 사실상 읽기 성능은 크게 안다르다.

인덱스 쓰기

중복된 값을 체크할 때 읽기 잠금, 쓰기할 때 쓰기 잠금이 걸리기 때문에 데드락이 아주 빈번히 발생한다. 그래서 쓰기 작업이 느리다.

주의사항

성능이 더 좋아질 것으로 생각하고 불필요하게 유니크 인덱스를 생성하지 않는 것이 좋다.

외래키 인덱스

자식을 수정 중일 때 해당 자식과 연관있는 부모에 쓰기 잠금이 걸리고 부모를 수정 중일 때 연관있는 자식에 모드 쓰기 잠금이 걸려서 성능상 좋지 않다.

Reference

7장 데이터 암호화9장 옵티마이저와 힌트