RDB index 원리

인덱스란 무엇인가? 라고 검색하면, 색인과 목차를 말하고 사전을 말하곤 한다. 그런식의 비유는 좋은 비유이나 직관적으로 와닿지는 않는 것도 사실이다.

index는 눈에보이는 테이블이 존재 하지 않다보니, 글로만 공부하게 되면 다소 머릿속에 그 형태가 그려지지 않는다.

아래와 같은 mysql서버에 구성된 회원정보 테이블이 있다고 하자.

id(pk)	name	email(unique)	password
1	kim	test@naver.com	abc1234
2	kim	test2@naver.com	abc1234
...	...	...	...
1000	park	test1000@naver.com	abc1234

위 테이블 컬럼의 구분자로서 id를 primary key를 지정하였고, email 주소는 중복될수는 없기에 unique키로 지정하였다.

이렇게 pk와 unique키를 설정하면 데이터의 성격을 결정짓는것 뿐만 아니라 index도 자동으로 생성하게 된다. 인덱스를 자동으로 생성한다는 것은 인덱스(목차) 테이블을 각각 mysql 내의 특정 공간에 구성한다는 것을 의미한다.

인덱스 생성과 select시 작동 원리는 아래와 같다.

1)2개의 인덱스 공간을 생성 ( id 인덱스, email 인덱스)

2)각각의 인덱스 공간은 root-branch-leaf등의 계층구조로 구성
-계층 구조로 구성된다는 것의 의미는 아래의 그림과 같이 index테이블의 계층적 구성을 의미한다. 아래의 계층구조는 id의 인덱스 테이블이다. 이 계층구조는 편의상 root와 branch만을 그린 것이고, 데이터의 양과 엔진의 설정에 따라 더 다층구조를 가질수도 있고 더 짧은 구조를 가질수도 있다.

3) index의 작동 원리

ex)where id=667; 의 조건으로 조회 한다고 했을때, 인덱스가 없다면 db는 풀스캔을 해야하고 id의 1부터 시작해서 666, 667번째로 원하는 값을 찾을수가 있다.

-그런데 index를 걸게 되면, index는 root페이지부터 검색을 하는데 root페이지의 첫번째 row인 1부터 시작해서 2번째 row인 100 -> 3번째 row인 200 -> 300 -> 600까지 검색을 하고 난 뒤, 667은 600보다는 크고 700보다는 작으니까, 이 root페이지의 branch페이지 안에 원하는 값이 있겠구나 라는 로직을 통해 root페이지에서 대략적으로 7번의 조회를 거쳐 branch 페이지로 넘어가게 된다.

-branch페이지에서는 600부터시작을하고, 667까지니까 600, 601 - > 667까지 대략적으로 67번정도를 조회한 후에 원하는 데이터를 찾게 된다. 즉 7번 + 67 대략적으로 70번 남짓의 조회로 원하는 데이터까지 이르게 된다.

-만약 데이터가 1000개가 아니라 훨씬 많은 데이터가 있다면 위 계층구조는 더 다층구조로 나뉘어야만 할 것이다. 그래야만 한 branch내에서 조회해야 하는 횟수를 줄일수가 있다. 한 branch당 데이터가 1000개씩 있다면 한 branch내에서 검색해야 할 양이 점점 늘어나지 않겠는가?

4)인덱스는 where조건에 조건을 걸어줘야 인덱스를 활용하게 된다.

위의 id인덱스 테이블구조(b-트리)와 마찬가지로 email인덱스테이블 또한 동일한 구조로 구성이 되게 된다. 그런데, 만약 where id=667을 했는데, email 인덱스 페이지를 뒤지고 있다면 의미가 있을까? email 인덱스 페이지는 a~z 같은 문자열로 오름차순 정렬된 페이지로 구성이 되어 있을것이다. 이는 숫자로 오름차순 구성된 id의 순서와는 아무런 상관이 없다.

반대로 where email ='test@naver.com'; 을 하였을때 id 인덱스 페이지를 뒤진다면 그것또한 전혀 의미가 없을 것이다. email='원하는문자열' 로 검색을 할때에는 원하는 문자를 빠르게 찾는 것이 목적이고, 이는 문자열로 정렬되어 구간 구간 페이지를 나눠 놓은 email인덱스를 타야지만이 빠른 속도로 검색을 할 수 있기 때문이다.

그래서 mysql 엔진이 어떤 index를 타야하는지 알려주기 위해서라도 where조건에 원하는 인덱스 조건을 넣어줘야지 원하는 인덱스를 활용할 수 있는 것이다. 즉, id인덱스가 존재하고 id인덱스를 통해 빠르게 검색하고 싶다면, where id=667을 해주어야 하고, email인덱스가 존재하고 email인덱스를 통해 빠르게 해당 email을 갖고 있는 row를 검색하고 싶다면 where email='test@naver.com'을 해줘야 한다는 것이다.