지식과 기술

‘복합키와 기본키 대한 index’에서 복합키의 두번째 컬럼을 찾을 때 값이 유의미한가

doongu 2023. 10. 29. 01:46

목차

1️⃣  개요

2️⃣  테이블 구성

3️⃣  컬럼 데이터 구성

4️⃣  결과

5️⃣  user를 찾을 때(두 번째 요소) composite_index가 더 느린 이유가 뭘까?

  • 실행 계획을 보자.
  • type=range와 type=ALL이 여기서 어떻게 동작과정이 다를까?
  • Optimizer가 왜 저런 type을 지정했을까?
  • 좀 더 구체적으로 알아보자.

 

6️⃣  composite_index의 앞의 요소를 모두 1로 적용하면 어떻게 될까?

7️⃣  결론

1️⃣ 개요

복합키에 대한 index가 유의미 한지 궁금하다. 이번 테스트에서는 (career, user) 이렇게 index(clustered index)를 이루는 경우, user를 찾을 때 유의미 한지 테스트해 본다.

 

2️⃣테이블 구성

비교를 위해 (career)만 index인 테이블과, (career, user) 둘 다 index인 테이블을 준비했다.

 

composite_index

CREATE TABLE composite_index (
career     INT NOT NULL AUTO_INCREMENT,
user     INT NOT NULL,
PRIMARY KEY(career, user)
) ENGINE=MYISAM CHARSET=utf8;

career_index

CREATE TABLE career_index (
career     INT NOT NULL AUTO_INCREMENT,
user     INT NOT NULL,
PRIMARY KEY(career)
) ENGINE=MYISAM CHARSET=utf8;

 

테이블 구성

 

3️⃣ 컬럼 데이터 구성

해당 결과에서 복합키에 대한 조건절이 유의미한 결과를 가지려면, user가 뒤죽박죽 섞이면 된다. python의 shuffle을 이용해 10만 개를 섞은 파일을 만들고 두 insert.py 코드가 읽게 만든다.

아래와 같이 Python을 통해 랜덤 값을 생성했고 

 

랜덤값 생성 python 파일
랜덤값 생성 결과

아래와 같이 python코드를 작성해 동일한 데이터를 각 테이블에 삽입해 줬다.

각 테이블에 데이터를 삽입하는 python 코드

 

4️⃣ 결과

찾고자 하는 값 소요 시간(career_index) 소요 시간(composite_index) 결과
user = 142385 0.45 sec 1.21 sec composite_index가 느리다
user = 1 0.45 sec 1.19 sec composite_index가 느리다
user = 13 0.45 sec 1.21 sec composite_index가 느리다
user = 23 0.46 sec 1.18 sec composite_index가 느리다
user = 19060 0.44 sec 1.18 sec composite_index가 느리다

(career, user)에서 조건 절에 부합하는 user를 찾는 것이 (career, user)에서 찾는 것보다 더 느리다

 

5️⃣ composite_index가 더 느린 이유가 뭘까?

실행 계획을 보자.

composite_index에서 user를 찾는 type=range이고

career_index에서 user를 찾는 type=ALL이다.

 

Optimizer가 왜 저런 type을 지정했을까?

composite_index에서 type=range로 설정된 이유는 (career, user)처럼 복합키를 index로 가지고 있고 user에 대한 쿼리를 수행할 때, MySQL은 앞의 필드(career)를 먼저 고려해야 하기 때문에 type=range를 사용한다. 추가로 Extra에서 skip scan으로 설정이 되어 있는 모습이다.

 

반면에, career_index테이블에서 type=ALL로 설정된 이유는 user 필드에 대한 index 가 없기 때문에 전체 테이블 스캔하는 type=ALL로 설정된 것이다.

 

그래서 type=range인 composit_index는 ' index 테이블을 모두 탐색한다'

반면에 type=ALL인 career_index는 '테이블 전체 행을 처음부터 끝까지 모두 탐색'한다.라고 할 수 있다.

 

type=range와 type=ALL이 여기서 어떻게 동작과정이 다를까?

type=range인 경우 동작과정은 아래와 같다.

우선 멀티 컬럼 index (career, user)는 아래와 같이 index 가 구성되어 있다.

composite index 구조 예시

 

Root node부터 값을 찾아 나간다. 하지만 career에 대해서만 정렬되어 있는 index이다. ( 중복되는 career 값이 없어서 그렇다.) 

 

그래서 '특정' user값을 찾을 때 mysql은 '전체 index '를 탐색한다. 전체 index를 탐색하는 것은 매번 노드(Page)를 가지고 온는 것이므로 이는 Disk I/O가 필요해 type=ALL인 것보다 비효율 적일 수 있다.

 

Extra에서 skip scan으로 설정이 되어 있었다고 해도, 앞 컬럼의 카디널리티가 높으므로 속도가 오래걸릴 수 밖에 없다.(중복도가 낮다)

 

 

좀 더 구체적으로 알아보자.

type=ALL이면 index를 참조하지 않고, 매번 Page 단위의 I/O가 이루어지는데 하나의 행을 읽더라도 Page단위만큼(보통 4KB) 읽어와서 찾는다. (메모리에 올려놓고 찾는다는 의미)

 

그렇게 놓고 계산을 한 번 해보면

 

전체 data가 4.29MB이니까 4KB로 나누면 최악의 경우 1072번의 Page 단위만큼 읽을 수 있다고 생각할 수 있다. (최악의 경우 1072번의 Disk I/O가 발생할 수 있다)

 

B+ tree의 Degree 값이 무엇이냐에 따라 다르겠지만, 결국 index의 한 노드당 이루는 차수의 값이 Page의 크기보다 크지 않을 것이고, 기존 Root Nodes와 Non-leaf nodes를 전체 탐색하는 과정이 추가되므로, 추가적인 시간 소요가 발생한다.

 

6️⃣ composite_index 앞의 요소롤 모두 1로 적용하면 어떻게 될까?

우선 결과는 아래와 같다.

찾고자 하는 값(career가 모두 1일 때) 소요 시간( composite_index )
user = 142385 0.00 sec
user = 1 0.00 sec
user = 13 0.00 sec
user = 23 0.00 sec
user = 19060 0.00 sec

 

career가 모두 동일한 값이기 때문에 user를 기준으로 정렬되며, 탐색 과정에서 index의 효율이 올라간다.

첫 번째 요소의 카디널리티가 아주 높기 때문에, 속도가 매우 빠르게 작동함을 알 수 있다.

type=range로 동작한다. 하지만 Extra에 skip scan이 있다.
career가 모두 1일 때

7️⃣ 결론

데이터가 어떤 형태인가에 따라 다르겠지만, 일반적인 경우에서 composite_key에서 두 번째 요소를 찾을 때 성능이 많이 저하된다.

 

 

참고자료 :

 

데이터베이스 관리 시스템(DBMS)

개념적 데이터베이스 관리 시스템 아키텍처 DBMS 서버는 인스턴스(Instance)와 데이터베이스(Database)로 구성된다. 인스턴스는 메모리 (Memory) 부문과 프로세스(Process) 부문으로 구성된다. 그 외 데이

dataonair.or.kr

 

데이터베이스 I/O 원리

4. 데이터 모델링의 3단계 진행 앞에서 라이브러리 캐시 최적화와 데이터베이스 Call 최소화를 통한 성능 개선 방법을 알아보았다. 본 절에서는 데이터베이스 I/O 효율화 및 버퍼캐시 최적화 방법

dataonair.or.kr

[10분 테코톡] 라라, 제로의 데이터베이스 인덱스 : 

https://www.youtube.com/watch?v=edpYzFgHbqs&t=726s