[Real MySQL] 인덱스

디스크 읽기 방식

---

“데이터베이스의 성능 튜닝은 어떻게 디스크 I/O를 줄이느냐”가 관건

HDD(Hard Disk Drive) vs SSD(Solid State Drive)

• HDD는 원판을 사용해 데이터를 읽지만, SSD는 플래시 메모리를 사용해 데이터를 읽는다.
• 순차 I/O : SSD가 HDD보다 조금 빠르거나 비슷한 성능
• 랜덤 I/O : SSD가 HDD보다 훨씬 빠르다.
  • 데이터베이스 서버 역시 순차 I/O 작업의 비중보다 랜덤 I/O 비중이 훨씬 크기 때문에 SSD의 장점은 DBMS용 스토리지에 최적이라고 할 수 있다.

순차 I/O는 3개의 페이지를 디스크에 기록하기 위해 1번의 시스템 콜을 요청

• 디스크에 기록해야 할 위치를 찾기 위해 디스크의 헤드를 1번 움직임(HDD 관점)

랜덤 I/O는 3개의 페이지를 디스크에 기록하기 위해 3번의 시스템 콜을 요청

• 디스크에 기록해야 할 위치를 찾기 위해 디스크의 헤드를 3번 움직임(HDD 관점)

“디스크의 성능은 디스크 헤더의 위치 이동 없이 얼마나 많은 데이터를 한번에 기록하느냐”에 의해 결정

• 데이터베이스에서는 대부분 작은 데이터를 빈번히 쓰고 읽는다.
• MySQL에서는 최적화를 위해 그룹 커밋, 바이너리 로그 버퍼, InnoDB 로그 버퍼 등의 기능을 사용

SSD에서 조차 순차I/O와 랜덤I/O 간의 성능 차이가 존재

 

쿼리 튜닝이란

• 랜덤 I/O 자체를 줄이는 것
• 쿼리를 처리하는 데 꼭 필요한 데이터만 읽도록 쿼리를 개선

인덱스란?

---

인덱스 : 책의 마지막에 있는 “찾아보기”

• 책의 내용 : 데이터 파일
• 인덱스에 적혀 있는 페이지 번호 : 데이터 파일에 저장된 레코드의 주소

DB에서의 인덱스 : 데이터베이스의 컬럼의 값과 해당 레코드가 저장된 주소를 Key-Value 형식으로 만든 것

• 컬럼(Key) 값을 기반으로 정렬이 되어 있다.

DB의 인덱스 자료구조 : SortedList

• 데이터 저장 과정이 복잡
• 데이터 검색이 빠름

DB의 데이터 자료구조 : ArrayList

• 데이터 저장이 빠름
• 데이터 검색이 느림

즉 DB의 인덱스는 데이터의 저장(INSERT, UPDATE, DELETE) 성능을 희생하고 데이터의 읽기(SELECT) 속도를 높이는 기능

• SELECT 쿼리 문장의 WHERE 절에 사용되는 컬럼이라고 해서 전부 인덱스로 생성하면 데이터 저장 성능이 떨어지고 인덱스의 크기가 비대해져 오히려 성능 하락이 발생할 수 있다.

구분

인덱스 역할별

• Primary Index(Primary Key)
  • 레코드를 대표하는 컬럼의 값으로 만들어진 인덱스
  • Primary Index는 테이블에서 해당 레코드를 식별할 수 있는 기준값
  • “식별자”라고 불리며 NULL 값과 중복을 허용하지 않는다.
• Secondary Index(Secondary Key)
  • Primary Index를 제외한 나머지 모든 인덱스를 의미
  • Unique Index의 경우는 대체키(Alternate Key)라고 불리기도 한다.

Super Key : 각 행을 유일하게 식별할 수 있는 하나 또는 그 이상의 속성 집합

Candidate Key : Super Key의 최소 속성 집합

Primary Key : 후보키 중 임의로 선택된 하나의 키

Alternate Key : Candidate Key - Primary Key

 

알고리즘 별(데이터 저장 방식 별)

• B-Tree
  • 컬럼값을 변형하지 않는 인덱싱
• Hash
  • 컬럼값으로 해시값을 계산해 인덱싱
  • 매우 빠른 검색을 지원
  • 기존 컬럼값을 변형(해싱)해서 사용하기 때문에 Prefix 일치와 같이 값의 일부만 검색하거나 범위를 검색할 때에는 사용할 수 없다.
  • 메모리 기반 데이터베이스에서 많이 사용

데이터 중복 허용 여부

• 유니크 인덱스(Unique Index)
  • 인덱스의 값(컬럼값)이 중복되는 경우가 없다.
• 유니크하지 않은 인덱스(Non-Unique Index)
  • 인덱스의 값(컬럼값)이 중복되는 경우가 있다.

Optimizer에서는 중요한 특성

• 유니크 인덱스에서 동등조건(=)을 사용한다면, 1건의 데이터만 찾으면 더 이상 검색하지 않아도 된다는 것을 의미

인덱스 기능 별

• 전문 검색용 인덱스
• 공간 검색용 인덱스
• …

B-Tree 인덱스

---

DB 인덱싱 알고리즘 중 가장 일반적으로 사용되고, 가장 먼저 도입된 알고리즘

• B+ Tree, B* Tree 등의 변형 Tree가 주로 사용됨

B-Tree 구조

B-Tree는 컬럼의 원래 값을 변형시키지 않고, 인덱스 구조체 내에서 항상 정렬된 상태로 유지

• 인덱스의 리프 노트는 항상 실제 데이터 레코드를 찾아가기 위한 주솟값을 갖고 있다.

• 인덱스는 테이블의 키 컬럼의 값만 갖고 있다.
• 따라서 나머지 컬럼을 읽으려면 데이터 파일에서 해당 레코드를 찾아서 데이터를 읽어야 한다.

Index Key-Value

InnoDB 스토리지 엔진의 경우, 인덱스값이 바로 레코드의 주소를 가리키고 있는 것이 아닌, 프라이머리 키 값과 매핑이 되어 있다.

• 즉 “인덱스 → 프라이머리 키 값 → 레코드” 순서로 값을 읽어온다.

기존 MyISAM의 경우 인덱스값과 레코드 주소가 바로 매핑되어 있었다.

• 즉 “인덱스 → 레코드” 순서로 값을 읽어올 수 있다.

• 각 방식의 장단점은 클러스터링 인덱스에서 설명

인덱스 키 값의 크기

InnoDB 스토리지 엔진의 데이터 저장 기본 단위 : Page, Block

• 디스크의 모든 읽기 및 쓰기 작업의 최소 단위
• 디스크의 모든 읽기 및 쓰기 작업의 최소 작업 단위
• 인덱스도 결국 페이지 단위로 관리

B-Tree의 자식 노드의 개수

• 인덱스의 페이지 크기와 키 값의 크기에 따라 결정된다.
  • innoDB의 기본 page size : 16KB
  • index 키가 16byte, 자식 노드 주소 영역이 평균 12byte 정도로 구성된다고 한다면
  • 하나의 인덱스 페이지에 16*1024/(12+16) = 585개의 노드를 가질 수 있다.
  • 즉, 자식 노드를 585개를 가질 수 있는 B-tree
• 즉, 인덱스 키의 크기가 32byte로 두배가 늘어난다면 한 페이지에 인덱스 키 372개를 저장할 수 있다.
• 이때 SELECT 쿼리를 통해 레코드 500개를 읽어야 한다면 전자는 한 번의 페이지(1번의 DISK I/O)를 통해서 값을 가져올 수도 있지만, 후자는 최소 두 번의 페이지(2번의 DISK I/O)를 통해 값을 가져와야 한다.
  • 인덱스를 구성하는 키 값의 크기가 커지면 디스크로부터 읽어야 하는 횟수가 늘어나고, 그만큼 느려진다는 것을 의미

B-Tree 깊이

• 위에서 결정된 B-Tree 자식 노드의 개수를 기준으로 자식 노드를 더 이상 늘릴 수 없으면 B-Tree 깊이를 늘린다.
• 위의 전자 예시에서 Depth 3인 B-Tree는 585585585 = 최대 2억 개의 노드를 가질 수 있다.
• 따라서 인덱스 키의 크기에 따라 B-Tree의 깊이도 조정이 될 수 있다.
  • B-Tree의 깊이가 깊어질수록 Disk IO가 더 많이 발생한다.

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

Selectivity:선택도 (Cardinality:기수성)

선택도(기수성) : 모든 인덱스 키 값 가운데 유니크한 값의 수

• 선택도가 높다는 것은 유니크한 값의 수가 많다
  • 특정 인덱스 값으로 검색을 했을 때, 나오는 레코드의 수가 더 적다.
  • 최종 검색 대상이 더 적어진다.
  • 성능 상 이점
• 선택도가 낮다는 것은 유니크한 값의 수가 적다.
  • 특정 인덱스 값으로 검색을 했을 때, 나오는 레코드의 수가 많다.
  • 최종 검색 대상이 더 많아진다.
  • 성능 상 단점

즉, 인덱스에서 유니크한 값의 개수는 인덱스나 쿼리의 효율성에 큰 영향을 미친다.

인덱스를 통해 레코드를 읽는 것은 인덱스를 거치지 않고 바로 테이블의 레코드를 읽는 것보다 비용이 높다.

• 실제로 DBMS의 옵티마이저에서는 인덱스를 통해 레코드 1건을 읽는 것이 테이블에서 직접 레코드 1건을 읽는 것보다 4~5배 비용이 많이 든다.
• 인덱스는 “값이 어디에 저장되어 있는지를 빠르게 찾는 것”이지 “값을 빠르게 읽는 것”은 아님

따라서 인덱스를 통해 읽어야 할 레코드의 건수가 전체 테이블 레코드의 20~25%를 넘어가면 인덱스를 이용하지 않고 테이블을 모두 직접 읽어서 필요한 레코드만 가려내는 “필터링” 방식으로 처리하는 것이 효율적

B-Tree를 통한 데이터 읽기 방식 : 인덱스 레인지 스캔

인덱스를 통해 한 건 이상의 레코드를 읽어나가는 스캔 방식

• SELECT * FROM employees WHERE first_name BETWEEN 'Ebbe' AND 'Gad';

• 위와 같이 시작 부분을 B-Tree에서 찾고 마지막 부분이 나올 때까지 leaf 노드를 통해 이동
• leaf 노드에 저장되어 있는 레코드 주소를 읽어 랜덤 디스크 I/O를 수행하면서 값을 가져오게 된다.

• 이때 기본적으로 B-Tree에 first_name이 정렬되어 저장되어 있기 때문에 결과도 자동으로 정렬이 되어서 반환된다.
• 만약 SELECT문에서 필요로 하는 컬럼이 인덱스값에 포함되어 있는 경우, 랜덤IO가 발생하지 않아 읽기 성능이 빨라진다.
  • 이를 “커버링 인덱스”라고 한다.
  • SELECT **first_name** FROM employees WHERE first_name BETWEEN 'Ebbe' AND 'Gad';

B-Tree를 통한 데이터 읽기 방식 : 인덱스 풀 스캔

인덱스 레인지 스캔과 마찬가지로 인덱스를 사용해 레코드를 읽지만, 인덱스의 처음부터 끝까지 모두 읽는 스캔 방식

• 쿼리가 인덱스에 명시된 컬럼만으로 조건을 처리할 수 있는 경우 해당 방식이 주로 사용된다.
  • 테이블을 직접 처음부터 끝까지 읽는 것보다, 인덱스를 읽는 것이 더 효율적이기 때문

• 인덱스 레인지 스캔보다는 느리지만, 테이블 풀 스캔 보다는 빠르다.

B-Tree를 통한 데이터 읽기 방식 : 루스 인덱스 스캔

인덱스 레인지 스캔과 비슷하게 작동하지만 중간에 필요치 않은 인덱스 키 값은 스킵하고 다음으로 넘어가는 방식으로 스캔

• 일반적으로 GROUP BY 또는 집합 함수 가운데 MAX()나 MIN() 함수에 대해 최적화하는 경우 사용된다.

dept_emp 테이블에 dept_no와 emp_no 두개의 컬럼으로 인덱스가 생성되어 있는 상태에서 아래 쿼리를 수행하는 경우

SELECT dept_no, MIN(emp_no)
FROM dept_emp
WHERE dept_nmo BETWEEN 'd002' AND 'd004'
GROUP BY dept_no;

• 옵티마이저는 각 dept_no의 첫 번째값이 emp_no의 가장 작은 값이라는 것을 알고 있다.

B-Tree를 통한 데이터 읽기 방식 : 인덱스 스킵 스캔

특정 인덱스를 WHERE절에 지정해주지 않아도 인덱스 레인지 스캔을 할 수 있도록 해주는 스캔 방식

• 기존 mySQL에서는 인덱스가 여러개 설정되어 있는 경우에서 WHERE 절에 모든 인덱스에 대한 조건이 없는 경우에는 “풀 테이블 스캔”이나 “인덱스 풀 스캔”을 수행
• mySQL8.0부터는 WHERE절에 모든 인덱스 조건이 없어도 인덱스 레인지 스캔이 가능하도록 해주는 인덱스 스킵 스캔 기능이 추가

MySQL 옵티마이저는 WHERE절에 없는 인덱스 컬럼의 유니크한 값을 모두 조회해 주어진 쿼리에 조건을 추가해 쿼리를 다시 수행하는 형태로 인덱스 스킵 스캔 수행

employees 테이블에는 gender, birth_date가 INDEX로 설정되어 있다고 가정

• ALTER TABLE employees ADD INDEX ix_gender_birthdate (gender, birth_date);

이때 두 컬럼에 대해 모두 WHERE절에 조건을 넣으면 인덱스 레인지 스캔이 수행된다.

SELECT * FROM employees WHERE gender="M". AND birth_date>="2023-12-20";

• 이때 가져와야 하는 데이터가 많은 경우(필터링되는 값이 적은 경우), 테이블 풀 스캔이 수행된다.

gender에 대해서만 WHERE절에 조건을 넣으면, 인덱스 동등 비교를 통해 레코드를 가져온다.

SELECT * FROM employees WHERE gender="M";
>> '1', 'SIMPLE', 'employees', NULL, 'ref', 'ix_gender_birthdate', 'ix_gender_birthdate', '1', 'const', '9900', '100.00', 'Using index condition'

birth_date에 대해서만 WHERE절에 조건을 넣으면, 인덱스 스킵 스캔 또는 테이블 풀 스캔이 수행된다.

// 전체 조회의 경우 인덱스 스킵 스캔 수행 X
SELECT * FROM employees WHERE birth_date>="2023-12-20"
>> '1', 'SIMPLE', 'employees', NULL, 'ALL', NULL, NULL, NULL, NULL, '19801', '33.33', 'Using where'

---

// 커버링 인덱스의 경우 인덱스 스킵 스캔 수행 O
SELECT gender, birth_date FROM employees WHERE birth_date>="2023-12-20"
>> '1', 'SIMPLE', 'employees', NULL, 'range', 'ix_gender_birthdate', 'ix_gender_birthdate', '4', NULL, '6599', '100.00', 'Using where; Using index for skip scan'

• 커버링 인덱스의 경우에만 인덱스 스킵 스캔이 수행된다.

위의 경우에서 인덱스 스킵 스캔은 다음과 같은 쿼리로 재처리 된다.

SELECT gender, birth_date FROM employees WHERE gender='M' AND birth_date>="2023-12-20"
SELECT gender, birth_date FROM employees WHERE gender='F' AND birth_date>="2023-12-20"

인덱스 스킵 스캔은 새로 도입된 기능이기 때문에 다음과 같은 제약 사항이 존재

• 커버링 인덱스의 경우에만 수행됨
• WHERE 조건절에 조건이 없는 인덱스의 선행 컬럼의 유니크한 값의 개수가 적어야 함
  • 유니크한 값의 개수가 매우 많다면 오히려 쿼리 성능이 떨어질 수 있다.

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

두 개 이상의 컬럼이 인덱스로 같이 설정되어 있는 인덱스를 의미

• 인덱스의 두 번째 컬럼은 첫 번째 컬럼에 의존해서 정렬되어 있다.
• 따라서 다중 컬럼 인덱스에서는 인덱스 내의 컬럼 위치가 상당히 중요

인덱스 스캔 방향

MySQL8.0부터는 다중 컬럼 인덱스의 각 컬럼별로 정렬 순서를 혼합해서 사용할 수 있게 되었다.

CREATE INDEX ix_teamname_userscore ON employees (team_name ASC, user_score DESC);

MySQL에서는 인덱스를 통해 데이터를 순차적으로 가져올 때, 정순이든 역순이든 첫 번째 레코드만 읽어서 반환할 수 있다.

• 역순인 경우 마지막 값을 읽도록 설정되어 있다.

하지만 InnoDB에서는 인덱스 역순 스캔이 인덱스 정순 스캔에 비해 느리다.

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

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

다중 컬럼 인덱스에서 각 컬럼의 순서와 컬럼에 사용된 조건에 따라 성능이 달라진다.

SELECT * 
FROM dept_emp
WHERE dept_no="d002" AND emp_no >= 10114;

• CASE A : INDEX (dept_no, emp_no)
  • dept_no=’d002’ AND emp_no>=10114 지점을 찾고, dept_no≠’d002’가 아닐 때까지 값을 레인지 스캔하면 된다.
  • dept_no=’d002’ AND emp_no>=10114 지점에서부터 읽은 레코드 모두 필요한 레코드
• CASE B : INDEX (emp_no, dept_no)
  • emp_no>=10114 AND dept_no=’d002’ 지점을 찾고, 이후 레코드를 모두 읽으면서 dept_no=’d002’인지 확인을 해야 한다.

인덱스의 조건에는 다음과 같은 종류가 있다.

• 작업 범위 결정 조건 : 케이스 A와 같이 작업의 범위를 결정하는 조건
• 체크 조건 / 필터링 조건 : 케이스 B의 dept_no='d002'와 같이 비교 작업의 범위를 줄이지 못 하고 단순히 거름종이 역할만 하는 조건

B-Tree 인덱스 특성상 “작업 범위 결정 조건”으로 사용될 수 없는 케이스는 다음과 같다.

• NON-EQUAL로 비교된 경우
  • WHERE column <> ‘N’
  • WHERE column NOT IN (10, 11, 12)
  • WHERE column IS NOT NULL
• LIKE “%??” : 뒷부분 일치 형태로 문자열 배턴이 비교된 경우
  • WHERE column LIKE ‘%승환’
  • WHERE column LIKE ‘%승%’
• 스토어드 함수나 다른 연산자로 인덱스 컬럼이 변형된 후 비교된 경우
  • WHERE SUBSTRING(column, 1, 1) = ‘X’
  • WHERE DAYOFMONTH(column) = 1
• NOT-DETERMINISTIC 속성의 스토어드 함수가 비교 조건에 사용된 경우
  • WHERE column = deterministic_function()
• 데이터 타입이 서로 다른 비교(인덱스 컬럼의 타입을 변환해야 비교가 가능한 경우)
  • WHERE char_column = 10
• 문자열 데이터 타입의 콜레이션이 다른 경우
  • WHERE utf8_bin_char_column = euckr_bin_char_column

다중 컬럼 인덱스의 경우 다음과 같은 경우에서 작업 범위 결정 조건을 사용할 수 없다.

• column_1 컬럼에 대한 조건이 없는 경우
• column_1 컬럼의 비교 조건이 위의 인덱스 작업 범위 결정 조건에서 사용될 수 없는 케이스 중 하나인 경우

다중 컬럼 인덱스의 경우 다음과 같은 경우에서 작업 범위 결정 조건을 사용할 수 있다.

• column_1 ~ column_(i-1) 컬럼까지 모두 동등 비교(= 또는 IN)로 비교
• column_i 컬럼에 대해 다음 연산자 중 하나로 비교
  • 동등 비교(= 또는 IN)
• 크고 작다 형태(> 또는 <)
• LIKE 좌측 일치 패턴(LIKE “승환%”)

인덱스 종류

---

함수 기반 인덱스

일반적으로 컬럼의 값 앞부분 일부나 전체에 대해서만 인덱스 생성이 허용된다.

하지만 컬럼의 값을 변형해서 만들어진 값에 대해 인덱스를 구축해야 하는 경우 사용

• 가상 컬럼을 이용한 인덱스
• 함수를 이용한 인덱스

가상 컬럼을 이용한 인덱스

ALTER TABLE user
	**ADD full_name VARCHAR(30) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL,**
	ADD INDEX ix_fullname (full_name);

• VIRTUAL 명령어를 통해 가상의 컬럼을 생성해서 해당 컬럼에 인덱스를 지정

SELECT *
	FROM user
	WHERE full_name="Matt Lee";

• 다음과 같은 쿼리를 수행하는 경우 ix_fullname 인덱스를 이용하게 된다.

가상 컬럼은 테이블에 새로운 컬럼을 추가하는 것과 같은 효과

• 실제 테이블의 구조가 변경된다는 단점이 존재

함수를 이용한 인덱스

MySQL8.0부터는 테이블의 구조 변경 없이 함수를 직접 사용하는 인덱스 사용이 가능

CREATE TABLE user(
	...
	INDEX ix_fullname ((CONCAT(first_name, ' ', last_name)))
);

하지만 함수 생성 시 명시된 표현식과 쿼리의 WHERE 조건절에 사용된 표현식이 반드시 같아야 한다.

# 인덱싱 가능
SELECT *
	FROM USER
	WHERE CONCAT(first_name, ' ', last_name) = "Matt Lee";

---
# 인덱스 불가능 : 중간 공백 문자가 없음
SELECT *
	FROM USER
	WHERE CONCAT(first_name, '', last_name) = "Matt Lee";

멀티 밸류 인덱스

하나의 데이터 레코드가 여러 개의 키 값을 가질 수 있는 인덱스 형태

• JSON 포맷의 데이터 내부의 값에 대해 인덱싱을 지원하기 위함
• MySQL 8.0부터 가능

CREATE TABLE user(
	...
	credit_info JSON,
	**INDEX my_creditscores ( (CAST(credit_info->'$.credit_scores' AS UNSIGNED ARRAY)) )**
);

멀티 밸류 인덱스를 활용하기 위해서는 일반 조건이 아닌 반드시 JSON 전용 조건을 사용해야 한다.

• MEMBER OF()
• JSON_CONTAINS()
• JSON_OVERLAPS()

SELECT *
	FROM user
	WHERE 360 MEMBER OF(credit_info->'$.credit_scores');

클러스터링 인덱스

MySQL에서 클러스터링은 테이블의 레코드를 비슷한 것(프라이머리 키 기준)들끼리 묶어서 저장하는 형태

• 테이블 당 하나의 클러스터링 인덱스를 가질 수 있다.

클러스터링 인덱스는 테이블의 프라이머리 키에 대해서만 적용되는 내용

• 프라이머리 키 값이 변경된다면 그 레코드의 물리적인 저장 위치가 바뀌어야 한다는 것을 의미
• 프라이머리 키 기반의 검색이 매우 빠르지만, 레코드의 저장이나 프라이머리 키의 변경이 상대적으로 느리다.

• 기존 다른 인덱스(세컨더리 인덱스)와 다른 점은, 리프 노드에 모든 컬럼의 값이 저장되어 있다는 점이다.
  • MySQL의 세컨더리 인덱스의 리프 노드에는 프라이머리 키 값이 저장되어 있다.

프라이머리 키가 없는 InnoDB 테이블에서 클러스터링 테이블을 만드는 방법

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

자동으로 추가된 컬럼의 경우 사용자에게 해당 컬럼이 노출되지 않으므로 클러스터링으로 인한 혜택을 받을 수 없다.

• 따라서 가능하다면 프라이머리 키를 명시하는 것이 좋다.

InnoDB의 세컨더리 인덱스의 리프 노드가 프라이머리 키 값을 저장하도록 구현되어 있는 이유

• InnoDB 외의 MyISAM과 같은 DB는 데이터 레코드가 저장된 주소인 ROWID가 바뀌지 않는다.
  • 따라서 프라이머리 인덱스나 세컨더리 인덱스가 ROWID를 바라보고 있으면 해당 레코드의 값을 바로 가져올 수 있다.
• InnoDB의 경우 데이터 레코드가 저장된 주소인 ROWID가 클러스터링 인덱스에 의해 변경될 수 있다.
  • 따라서 세컨더리 인덱스가 ROWID를 바라보고 있는 상태에서 클러스터링 인덱스에 의해 ROWID가 변경되면 모든 세컨더리 인덱스의 리프 노드의 ROWID 값을 변경시켜줘야 한다.
  • 이러한 오버헤드를 방지하기 위해 세컨더리 인덱스의 리프 노드에서는 프라이머리 키 값을 가지고 있도록 한다.
• MyISAM : 인덱스 검색 → 레코드 주소 확인(ROWID) → 최종 레코드
• InnoDB : 인덱스 검색 → 프라이머리 키값 확인 → 레코드 주소 확인(ROWID) → 최종 레코드

하지만 이러한 오버헤드보다 클러스터링 인덱스에 의한 장점이 더 크다.

클러스터링 인덱스 장단점

장점

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

단점

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

즉, 클러스터링 인덱스는 빠른 읽기와 느린 쓰기이다.

클러스터링 인덱스 유의사항

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

• InnoDB에서의 프라이머리 키 기반 검색은 매우 빠르기 때문에, 특정 컬럼의 크기가 크더라도 업무적으로 해당 레코드를 대표할 수 있다면 그 컬럼을 프라이머리 키로 설정하는 것이 좋다.
  • 하지만 현업에서는 프라이머리 키를 항상 대체키(AUTO-INCREMENT)를 사용하도록 권장하는 쪽도 있다.
  • 프라이머리 키의 변경 가능성, 테이블 복잡도, 인덱스 크기 증가 등의 이유

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

• 프라이머리 키를 명시하지 않으면 클러스터링 인덱스의 장점을 활용하지 못 할 수 있기 때문

유니크 인덱스

테이블이나 인덱스에 같은 값이 2개 이상 저장될 수 없음을 의미하는 인덱스

• MySQL에서는 인덱스 없이 유니크 제약만 걸 수 없다.

유니크 인덱스와 기타 세컨더리 인덱스와의 구조상 차이점은 없다.

• 하지만 읽기/쓰기 성능 상의 차이가 존재

읽기

• 유니크 인덱스와 유니크하지 않은 세컨더리 인덱스의 읽기 차이는 “읽는 레코드의 차이” 때문이지, 인덱스 자체의 특성 때문이 아니다.
• 따라서 읽기 성능의 큰 차이가 존재하지 않음

쓰기

• 유니크 인덱스의 경우 해당 값을 쓰기 전에 해당 값이 유니크한지 검증을 해야 한다.
• 따라서 쓰기 성능의 경우 유니크 인덱스가 유니크 하지 않은 세컨더리 인덱스보다 낮다.

유일성이 반드시 보장되어야 하는 컬럼에 대해서는 유니크 인덱스를 생성하되, 꼭 필요하지 않다면 유니크 인덱스보다는 유니크하지 않은 세컨더리 인덱스를 생성하는 것도 고려

외래키

서로 다른 테이블 간의 제약 사항을 설정하기 위한 인덱스

InnoDB의 외래키 관리에는 중요하는 두 가지 특징이 있다.

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

실습

데이터 세팅

기본 환경

• 맥북 M1

테이블 정보

calls 테이블 사용

• 각 call을 기록하는 테이블
• call_id, status, created_at, updated_at 등의 필드가 존재

데이터 정보

약 백만건의 데이터가 저장되어 있다고 가정

• 실제로는 더미 데이터 중 중복되는 데이터를 제거하기 때문에 약 95만 건 정도의 데이터가 존재

DROP TABLE IF EXISTS calls;
create table calls
(
    id         bigint                                   NOT NULL,
    call_id    bigint                                   NOT NULL,
    status     ENUM ('SUCCESS', 'FAIL_TIMEOUT', 'STOP') NOT NULL,
    created_at datetime                                 NOT NULL,
    updated_at datetime                                 NOT NULL
) ENGINE = INNODB;

DROP PROCEDURE IF EXISTS insertDummyCalls;
DELIMITER $$
CREATE PROCEDURE insertDummyCalls()
BEGIN
    declare i INT DEFAULT 1;
    declare created_at DATETIME DEFAULT '2000-01-01T00:00:00';
    declare rand_call_id INT;

    WHILE i <= 1000000
        DO
            SET rand_call_id = FLOOR(RAND() * 1000000000);
            INSERT INTO calls(id, call_id, status, created_at, updated_at)
            VALUES (i,
                    rand_call_id,
                    CASE FLOOR(RAND() * 3)
                        WHEN 0 THEN 'SUCCESS'
                        WHEN 1 THEN 'FAIL_TIMEOUT'
                        WHEN 2 THEN 'STOP'
                        END,
                    created_at,
                    DATE_ADD(created_at, INTERVAL 7 DAY));

            SET created_at = DATE_ADD(created_at, INTERVAL 1 HOUR);
            SET i = i + 1;
        END WHILE;

    -- 중복 제거
    DELETE
    FROM calls
    WHERE calls.id IN (SELECT *
                       FROM (SELECT distinct c1.id
                             FROM calls c1
                                      INNER JOIN calls c2
                                                 ON c1.call_id = c2.call_id AND c1.id > c2.id) as t);
END$$
DELIMITER $$

CALL insertDummyCalls;

인덱스를 설정하지 않은 상태에서 데이터 조회

call_id 기준 동등비교 조회

select *
from calls
WHERE call_id = 162746945;

-- 실행시간 : 291ms
-- 조회 방식 : 테이블 풀 스캔(ALL)

call_id 기준 범위비교 조회

select *
from calls
WHERE call_id BETWEEN 100000000 AND 200000000;

-- 95247건 데이터 조회
-- 실행시간 : 97ms
-- 조회 방식 : 테이블 풀 스캔(ALL)

select *
from calls
WHERE call_id BETWEEN 100000000 AND 110000000;

-- 9378건 데이터 조회
-- 실행시간 : 91ms
-- 조회 방식 : 테이블 풀 스캔(ALL)

created_at 기준 동등비교 조회

select *
from calls
WHERE DATE(created_at) = '2023-12-20';

-- 실행시간 : 290ms
-- 조회방식 : 테이블 풀 스캔(ALL)

created_at 기준 범위비교 조회

select *
from calls
WHERE DATE(created_at) BETWEEN '2023-12-20' AND '2024-12-20';

-- 실행시간 : 194ms
-- 조회방식 : 테이블 풀 스캔(ALL)

인덱스 설정 후 데이터 조회

ALTER TABLE calls
    ADD INDEX ix_call_id (call_id);

ALTER TABLE calls
    ADD INDEX ix_created_at_date ((DATE(created_at)));

call_id 기준 동등비교 조회

select *
from calls
WHERE call_id = 162746945;

-- 실행시간 : 61ms
-- 조회방식 : 인덱스 조회

call_id 기준 범위비교 조회

select *
from calls
WHERE call_id BETWEEN 100000000 AND 200000000;

-- 95247건 데이터 조회
-- 실행시간 : 101ms
-- 조회 방식 : 테이블 풀 스캔(ALL)

select *
from calls
WHERE call_id BETWEEN 100000000 AND 110000000;

-- 9378건 데이터 조회
-- 실행시간 : 86ms
-- 조회 방식 : 인덱스 범위 스캔(RANGE)

created_at 기준 동등비교 조회

select *
from calls
WHERE DATE(created_at) = '2023-12-20';

-- 실행시간 : 70ms
-- 조회방식 : 인덱스 스캔

created_at 기준 범위비교 조회

select *
from calls
WHERE DATE(created_at) BETWEEN '2023-12-20' AND '2024-12-20';

-- 실행시간 : 110ms
-- 조회방식 : 인덱스 레인지 스캔(RANGE)