Mysql 인덱스

인덱스란?
번역을하면 바로 색인이라는 단어로 번역된다.
색인은, 검색하면 책속의 낱말이나 어떤 챕터나 구절들을 빠르게 찾아볼 수 있게 쪽수 정보를 나타내주는 것을 뜻한다.
이 개념을 DB에 적용시킨 것이다.

인덱스는 어떻게 구성하는가?

인덱스는 CREATE INDEX 키워드로 구성이 가능하다.

Team 이라는 테이블이 이렇게 있다.

id(primary key)	name	member_id	grade
1	홍길동	1	1
2	가나다	2	2
3	고길동	3	3
4	나길동	4	4

 

이름만으로 인덱스를 구성하고 싶다면 아래와 같이 수행해주면 된다.
CREATE INDEX <인덱스명> 테이블(칼럼);

CREATE INDEX team_index team(name);

이렇게 해주게 되면 name의 오름차순 순서로 정렬되게 된다.

왜 정렬이 되는가?
기본적으로 Mysql에서는 BTree 자료구조로 인덱스를 구성하게 된다.
b트리는 이진트리와 같게 기본적으로 정렬을 통해 구성해주게 된다.
-> 데이터 탐색에 용이하도록 구성하는 것이다.

인덱스를 생성하면 아래와같이 주소값을 참조하고 있는 구성이 완료된다.
| name | 주소값 |
|------|-----------------------|
| 팀1 | name이 팀1인 어떤 데이터의 주소값 |
| 팀2 | name이 팀2인 어떤 데이터의 주소값 |

이렇게 되었을 때 다시 SELECT 쿼리를 수행하게되면,

SELECT * FROM team WHERE name = '팀1';

name만을 인덱스 구성한 인덱스 주소 구성을 바라보게 될 것이다.
여기서 이제 트리구조의 탐색 알고리즘이 뒷받침하여 탐색하게 되는데,
가장 가운데 row부터 맞는지 검색을 들어가서 팀1 조건에 부합하는 데이터를 찾게된다.

찾게되면 name이 팀1 인 데이터가 예시에선 지금 2개로 구성했으니, 2개만을 조회해서 결과 반환을 해주게 될 것이다.

이런식으로 탐색을 빠르게 해줄 수 있다.

그럼 한개만의 칼럼만 인덱스를 구성할 수 있나요?

놉. 그렇지는 않다.
예를 들어서 팀에 속한 멤버가, 항상 등번호를 갖고있어야하고 적어도 그 팀에있는 멤버인 홍길동, 1번은 같이 묶여다닌다.
그렇게 된다면 인덱스에 그 둘을 같이 묶어 구성해주는 것이다.
말로 표현한걸 도식화하면 아래와 같다.
| member_id | number | 주소값 |
|-----------|--------|--------------------------|
| 1 | 1 | id가 1이며 등번호도 1인 데이터의 주소값 |
이게 근데 겹칠 수 있는 데이터라면 일반적인 인덱스로 구성시킬 수 있겠지만,
등번호가 유니크한 고유값으로 묶인다면 Unique Index를 구성해줄 수 있다.

CREATE INDEX member_index member(name, number);

인덱스로 탐색한 이후는?

자, 이제 우리는 인덱스로 탐색하는 방법을 조금은 안 것 같다.
여기서 인덱스가 유니크가 아닌경우를 좀 더 보려고한다.
일단 유니크 인덱스가 아니라면, 같은 조건으로 묶여있는 데이터가 여러건 있다는 것인데,
이미 인덱스에서 한번 체로 거른 수준처럼 데이터가 걸러졌는데 이후는 full scan을 수행하여 그 데이터들 중 완전한 조건에 부합하는 데이터만 추려 조회하여 결과를 내준다.

이렇게 되니까 인덱스 참 좋은것 같은데 그럼 칼럼마다 다 생성해주면 좋은거 아닌가?

놉. 그렇지 않다.
왜냐면 데이터는 테이블 자체에 저장이 될텐데, 인덱스는 처음에 b-tree 구조이고 정렬을 한다고 했었다.
인덱스를 많이 구성하게 되면, 그 인덱스들이 원하는 조건대로 재정렬을 해주어야 하기 때문에 성능 저하가 발생할 수 있다.
그리고 이 인덱스가 구성되는게 논리적인게 아니고 주소값을 참조하는 값들이 계속 생성하여 디스크에 저장되기 때문에 저장 용량의 한 부분을 차지하게 된다.

Where 조건에서 복합 칼럼 인덱스를 안타게 구성할 수도 있는지?

당연히 구성해볼 수 있다.
이것은 테이블을 만들고 실제 select 후 실행계획을 분석해보도록 하자.

demo2 테이블 DDL

자 테이블 구조는 위와같이 DDL을 정의해놓은 상태이다.

이제 EXPLAIN 키워드를 사용해서 인덱스를 타는지 안타는지 보게 될 것이다.
일단 기본적으로 member_id와 grade 팀순위를 묶었다.
-> 대충 구성하려고 하다보니 이상한 데이터 구조가 되어버렸다. ㅋㅋㅋㅋㅋㅋㅋ
일단 이 부분은… 넘어가도록…ㅎㅎ
각설하고!

EXPLAIN SELECT *
FROM test.demo2
WHERE member_id = 1;

EXPLAIN SELECT *
FROM test.demo2
WHERE grade = 1;

 

위의 두개 쿼리를 각각 실행한 결과이다.

위의 DDL에서 member_id를 먼저 구성하고, 그 뒤에 grade를 넣은 인덱스를 구성했다.

member_id를 먼저 구성해주었기 때문에 member_id로 선정렬된 인덱스를 탐색하게 될 것이니

member_id를 조건에 넣어주면 인덱스를 통해 데이터를 추려주는게 가능하다.

member_id를 통한 조회

 

grade를 통한 조회

하지만, 복합으로 구성된 상태에서는 member_id로 선정렬이 되어있기에 grade만을 where조건에 넣어주면 인덱스 탐색이 불가능하여 보는것처럼 인덱스를 타지 못하게 쿼리가 구성된다.

왜 이렇게 됐을까?
인덱스 구성한 ddl을 보면 member_id 로 시작하게 된다.

 

그래서 member_id가 Index에 먼저 구성되어 있기 때문에 우선적으로 추려볼 수는 있는 과정을 거치는 것이다.
그래서 grade만을 조회할 때는 grade로만 정렬이되거나, index의 가장 앞단에 grade로 잡혀있는 둘다 없기 때문에 가장 좋지 않은 Full scan 데이터를 조회하게 되는 것이다.

 

자 그럼 커버링 인덱스는 뭐야?

 

우선 앞의 내용을 천천히 다시 되짚어보자. (누구나 충분히 이해할 수 있을거라고 생각한다.)

 

우리는 여태 인덱스를 구성할 때 인덱스로 정할 n개의 칼럼들 + 주소 참조값을 가진 별도의 인덱스를 구성한다고 했다.

 

근데 해당 조건에 부합하는 row의 모든 데이터가 아니라 인덱스에 포함된 데이터만 조회한다면 사실 테이블 스캔이 필요 없는거 아냐?

-> 이게 바로 커버링 인덱스이다.

 

조건에 부합하는걸 갖고 디스크가서 데이터를 조회할 필요를 줄이기 때문에 성능상으로 굉장히 이득을 볼 수 있다!

 

그러면 이제 인덱스를 구성하기 좋게 만드려는 조건들을 나열해볼 수 있지 않나?

그렇다. 인덱스를 잘 설계하기 위해서는 어떻게 만들어줘야 하는지 이쯤 되면 조금은 이해가 될 수 있어보인다.

 

일단 단일 칼럼 인덱스라면 중복도가 낮은 데이터를 잡아주는게 무조건 유리할 것이다.

-> 이래야 조건에 부합하는 데이터의 Full scan을 하더라도 빠르게 찾아낼 수 있을 것이다.

 

복합 컬럼 인덱스라면...

일단 자주 엮이는 칼럼들을 우선적으로 묶어주는데, 그 조합의 유니크함이 필요할 것이다.

칼럼의 갯수가 너무 많아지면 반대로 또 인덱스 용량이 무거워지기 때문에 좋지 않을 것이다.

 

정리

일단 정리 차원에서 인덱스를 정리해봤다.

이전에 공부했던 것보다 지금 공부하면서 정리하는게 좀 더 많이 이해할 수 있게 된 것 같다.

계속 조금씩 점진적으로 깊게 공부하는 방법을 천천히 체득시켜야겠다.