[Database] MySQL 인덱스

 InnoDB 엔진 기준으로 작성된 글 입니다.

 

클러스터링 인덱스

출처 https://mangkyu.tistory.com/285

 
MySQL 은 물리적으로 데이터를 저장함에 있어 두 가지 특징이 있다.

• 비슷한 PK 를 가진 데이터는 같은 페이지에 저장된다. 즉, PK 가 데이터 저장 위치를 결정한다. 예를 들면, 그림에서 emp_no 가 PK 일 때, PK 4번 5번은 페이지 번호 9에 같이 저장되어 있고, PK 11 12번은 페이지 번호 10에 저장되어 있다.
• 페이지 안의 데이터는 정렬되어 저장되어 있다. 예를 들면, 그림에서 페이지 번호 안의 데이터는 PK 를 기준으로 오름차순 정렬되어 있다.

이 때, PK 가 비슷한 데이터끼리 물리적으로 저장된다는 이유 때문에 PK 를 `클러스터링 인덱스` 라고 부른다. 
 

인덱스에 대한 오해?

여기서 짚고 넘어가야할 점이 있다. 흔히 CS 공부할 때, '인덱스 트리를 통해 리프 노드에 데이터가 저장된 물리적 위치를확인하여 바로 조회할 수 있도록 해준다` 라고 공부한다. MyISAM 엔진의 경우, index key 에 해당하는 물리적 위치를 바로 조회하여 인덱스 탐색 시간복잡도가 O(logN) 이다. 
하지만 InnoDB 엔진은 바로 물리적 위치를 조회할 수 없으며, 인덱스 탐색 시간복잡도가 O(2logN) 인데, 이 이유를 알아보도록 하자. 
(시간복잡도에서 유추되지만 트리 탐색을 두 번한다.) 
 

InnoDB 엔진은 클러스터링 인덱스 (PK) 를 통해 데이터를 조회한다

출처 https://mangkyu.tistory.com/285

CREATE TABLE users (
    emp_no INT PRIMARY KEY,
    name VARCHAR(100),
    INDEX(name)
);

 
다음과 같은 테이블의 InnoDB 엔진에서 `SELECT * FROM users where name = 'MangKyu'` 를 한다면 어떻게 될까?

1. where 조건 절에 name 이 있으므로 인덱스를 활용할 수 있다. name 인덱스 트리를 탐색하여 리프노드를 살펴보니 찾는 데이터의 PK 가 저장되어 있다. (MyISAM 엔진의 경우, PK 가 아닌 물리적 저장 위치가 저장되어 있기 때문에 다음 스텝으로 넘어가지 않고 바로 조회하면 된다.)
2. PK 인덱스 트리를 탐색한다. 그럼 리프노드에 결과적으로 조회해야될 데이터가 저장되어 있어 이를 조회하면 된다. (물론, 버퍼풀에 있으면 디스크 IO 가 필요없지만, 없는 경우 디스크 IO 가 필요하다.)

다시 살펴보면 처음에는 name index tree 를 탐색했고 그 다음에는 PK index tree 를 탐색했다. name 과 같이 PK 가 아닌 인덱스를 `Secondary Key` 라고 칭한다. 그리고 Secondary Key는 `Non-Clustered Index` 이다. 
 

탐색 순서: Secondary Key -> Primary Key

세 줄 요약

• PK 에 대해서는 항상 인덱스가 자동 생성된다. (그래서 설명은 PK 로 했지만, 만약에 PK 가 존재하지 않는다면? Unique Key 에 해당하는 인덱스를 생성하고 PK 처럼 사용한다.)
• where 절에 PK 가 아닌 인덱스가 조건절로 들어갔다면?
  • PK 가 아닌 인덱스는 Secondary Key 에 해당되며, Secondary Key Index 트리를 먼저 탐색한다. 예를 들어, name="MangKyu" 라면 ManyKyu key 를 통해 트리를 탐색하여 리프노드에서 PK 를 알아낸다. 
  • 알아낸 PK 를 통해, Primary Key Index 트리를 탐색한다. 리프 노드에서 실제 데이터의 물리적 위치를 알아내게 된다.
• where 절에 PK 가 조건절로 들어갔다면?
  • PK 를 통해, Primary Key Index 트리를 탐색한다. 리프 노드에서 실제 데이터의 물리적 위치를 알아내게 된다.

 

그래서 클러스터링 인덱스를 왜 사용하나요?

실제로 데이터를 여러 개 조회할 때, 순차적인 데이터 (id = 1, 2, 3) 를 조회하는 일이 비순차적인 데이터 (id = 1, 100, 59) 를 조회하는 일 보다 잦게 일어난다. 전자를 `순차 IO`, 그리고 후자를 `랜덤 IO` 라고 한다. 일반적으로 순차 IO 조회를 하는 일이 많다는 점에서 착안하여 PK 를 기준으로 클러스터 해두면 IO 작업을 덜 실행해도 된다. 
 
장점

• PK 순으로 정렬되어 있기 때문에 순차 IO 조회 성능이 좋다

단점

• PK 에 항상 의존하기 때문에 PK 의 데이터 크기를 너무 크게 잡으면 안된다
  • PK 의 데이터 크기가 커질수록 클러스터 인덱스의 제한된 한 페이지 내에 들어갈 수 있는 엔트리 수가 줄어든다. 이 때문에 디스크 IO 가 오히려 증가할 수 있다. 또한 버퍼풀에 캐싱할 수 있는 데이터의 개수 또한 줄어든다.  
• insert/update/delete 의 경우 저장되어야할 페이지 번호를 찾고, 해당 페이지 안에서 정렬까지 해야되기 때문에 성능이 좋지 못한다. 
• PK 를 변경하는 경우에도 저장되어야할 페이지 번호와 페이지 내의 위치를 찾고 옮겨야하기 때문에 성능이 떨이진다.

 

커버링 인덱스

MySQL에서 쿼리를 최적화하는 중요한 개념 중 하나로, 인덱스가 쿼리에 필요한 모든 데이터를 포함하고 있어, 실제 데이터 페이지에 접근할 필요 없이 인덱스만으로 쿼리를 처리할 수 있는 경우를 의미한다. 커버링 인덱스는 디스크 I/O를 줄이고 쿼리 성능을 크게 향상시킬 수 있다.
 

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    age INT,
    INDEX(email, age)
);

• •  

email_age_idx 인덱스 테이블 (email, age 순으로 오름차순 정렬)

 
index 로 복합키를 걸어준 후 여러 가지 쿼리를 실행해보도록 하자.

들어가기 전에 Index Full Scan, Index Range Scan, Table Full Scan 를 모른다면 여기를 참고해보도록 하자

Case 1. 복합 인덱스 (a, b) 중 where 절에 a 만 포함된 경우

explain select email from users where email like 'user4%';

실행 결과

• 스캔 종류 : range scan
• 사용된 인덱스 키 : (email, age) 인덱스
• where 절의 index 가 사용됨

인덱스 조건 푸시다운 - select email 이 아니라 select * 이라면? 

explain select * from users where email like 'user4%';

Extra 부분만 달라져있는 모습

Extra 가 Using index condition 으로 바뀐다. select email 의 경우 email 인덱스 테이블만 스캔해도 필요한 데이터를 다 가져올 수 있따. 하지만 select * 의 경우에는 모든 칼럼을 가져와야하기 때문에 MySQL 5.6 이전이라면 인덱스를 사용하지 못하고 Table Full Scan 를 통해 스토리지 엔진으로부터 데이터를 모두 가져온 후 조건으로 걸러낸다.
하지만 MySQL 5.6 버전부터는 인덱스를 최대한 활용하는 쪽으로 바뀌게 된다. email 인덱스를 먼저 평가하고, 해당 조건에 맞는 데이터 중 email 외의 다른 칼럼들을 가져오기 위한 IO 작업을 한다. 이를 `인덱스 조건 푸시다운` 이라고 한다. 
 

• 커버링 인덱스 (Using index):
  • 쿼리에 필요한 모든 열이 인덱스에 포함되어 있어 인덱스만으로 쿼리를 처리할 수 있을 때 발생
  • 데이터 페이지 접근이 필요 없으므로 디스크 I/O가 최소화
• 인덱스 조건 푸시다운 (Using index condition):
  • 인덱스 조건을 먼저 평가한 후 나머지 데이터를 위해 데이터 페이지에 접근할 때 발생
  • 인덱스만으로 쿼리를 처리할 수 없을 때 나타나며, 일부 디스크 I/O가 발생

자세한 설명은 여기를 참고해보도록 하자!

Case 2. 복합 인덱스 (a, b) 중 where 절에 b 만 포함된 경우

(a, b) 상태로 정렬되어 있는 인덱스이기 때문에 b 만 조건절에 주어진다면 인덱스를 활용하지 못한다.

Case 3. 복합 인덱스 (a, b) 중 where b = ? and a = ? 인 경우

explain select * from users where age<40 and email like 'user4%';

where 절의 a, b 순서는 상관없다. DB 옵티마이저가 자동으로 최적화하여 a 인덱스를 range scan 후 b 인덱스 range scan 으로 넘어가도록 최적화 해주기 때문이다. 
 

Case 4. group by + where

해당 글을 참고해보도록 하자. 

where 동등 비교인 경우 인덱스를 타고 아닌 경우 인덱스를 타지 않는다?
많은 블로그에서 잘못 정리하고 있는 내용인 듯하다. 아래의 쿼리 모두 인덱스를 타기 때문이다. 

2024.07.24 수정
MySql 은 데이블의 통계 정보를 갖고 있는다고 한다. (통계 정보는 최신이 아니다.) 통계 정보를 통해 찾는 데이터가 어디 위치에 있는지 파악하고 필요할 경우엔 테이블 풀스캔이 더 빨라 인덱스를 타지 않는다. (결국엔 데이터를 가지러 실제 테이블에 가야하기 때문에)
예를 들면 데이터가 1~1000000까지 고르게 분포하고 data 칼럼에 인덱스가 걸려있을 때, data < 100 은 인덱스를 타는게 더 빠르지만 data > 100 은 인덱스를 타는 것 보다는 풀스캔이 더 빠른다고 판단하여 풀스캔을 하게된다.

explain select age from users where email = 'user4@a.com' group by age;
explain select age from users where email like 'user4%' group by age;

 
 

인덱스와 레코드 락

레코드 락은 레코드 그 자체에 락을 거는 것이 아니라 인덱스에 락을 거는 것이다. 따라서 락을 걸 때 인덱스를 타도록 하는 것이 중요하다.
아래의 두 쿼리를 확인해보자.

Case 1. 인덱스 칼럼에 락이 걸린 경우

email 인덱스를 타서 락을 걸어보도록 하자.

start transaction;
select * from sandbox.users where email like 'user3%' FOR UPDATE;
SELECT * FROM performance_schema.data_locks;

email 이 user3 prefix 인 경우에만 락이 걸림

 

Case 2. 인덱스 칼럼이 아닌 칼럼에 락이 걸린 경우

age 는 인덱스 칼럼이 아니기 때문에 해당 칼럼으로 락을 걸어보도록 하자.

start transaction;
select * from sandbox.users where age=60 FOR UPDATE;
SELECT * FROM performance_schema.data_locks;

모든 row 에 대해 락이 걸림

 

외래키 잠금 경합

외래키로 엮인 부모-자식 테이블의 경우에도 둘 중 하나를 수정하는 경우 인덱스에 락이 걸린다. 이때 서로 관련되어 있기 때문에 부모, 자식 모두 락이 걸리게 되기 때문에 각 테이블에 대한 처리 성능이 현저히 떨어지게 된다.
따라서 사실은 개발자분 오피셜에 따르면 mysql 외래키를 설정하지 않는 것이 국룰이라고 한다.
 
참고
https://tecoble.techcourse.co.kr/post/2021-10-12-covering-index/
https://hudi.blog/db-clustered-and-non-clustered-index/
https://mangkyu.tistory.com/285
https://tech.kakao.com/posts/319
https://jojoldu.tistory.com/474