현 RDBMS 들은 INDEX(색인:책갈피)를 이용이 일반화 된 상태이고 이를 모르는다는 건 라스베가스에서도 상상 할수 없는 일이다.
그럼에도 불구하고 대부분의 쿼리 작성을 하는 개발자들의 경우 정확한 개념없이 INDEX를 인지하고 말하며 소비한다.
오늘은 INDEX의 기본 메커니즘을 알아보도록 하자.
인덱스의 분류는 다각도로 할 수 있으나 저장 위치를 기준으로 clustered index와 흔히 말하는(묵시적으로) nonclustered index가 있다.
clustered index 라는 건 말 그대로 테이블 자체 데이터의 정렬을 말하는 것이다.
당연히 테이블 자체 컬럼의 정렬이니 테이블당 1개밖에 만들 수 없는 것이다.
그래서 종종 clustered index가 존재하지 않는 테이블에서 주사용 index 중 정렬 컬럼이 많은 것을 찾아 clustered index로 변경시 size면에서 index 하나를 그냥 삭제한 것과 동일한 효과를 볼 수 있는 것이다.
가장 흔한 경우가 PK설정인데 테이블 설정시 PK는 별도의 정의가 없는 경우 clustered index 가 된다.
그외 일반적인 인덱스(nonclustered index)들은 별도의 페이지들로 구성된다는 것.
상식이지만 분명 인지하고 있어야 마구잡이로 생성된 인덱스를 정리할때 기본 중 기본이다.
오늘은 index의 정렬과 노드 구성 그리고 검색 매커니즘까지만 정리해 보자.
딱딱한 전문용어 보다는 예를 들어 보자.
(직접 구현하지 않고 상상력으로 구성된 점 이해 바란다. 기준은 MS-SQL이다.)
가령 아래와 같은 테이블이 있다고 가정하자
CREATE TABLE DBO.TEST
(
col_cd INT not null
, col_nm VARCHAR(16) null
, CONSTRAINT PK_DBO_TEST PRIMARY KEY CLUSTERED (col_cd)
);
DECLARE @V_CNT INT = 1001;
WHILE @V_CNT < 9999
BEGIN
INSERT INTO DBO.TEST VALUES(@V_CNT, 'TEST' + CONVERT(VARCHAR(8), @V_CNT));
SET @V_CNT = @V_CNT + 1;
END
SELECT * FROM DBO.TEST;
여기서 MS-SQL DMV를 이용해서 해당 PK의 페이지 구성을 알아보자
아래의 쿼리로 확인이 가능하다.
SELECT
p.object_id,
i.name AS index_name,
p.index_id,
p.partition_number,
p.row_count AS row_count,
p.used_page_count AS used_pages,
p.reserved_page_count AS reserved_pages
FROM
sys.dm_db_partition_stats AS p
JOIN
sys.indexes AS i ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE
p.object_id = OBJECT_ID('DBO.TEST');
8998개의 row count 에 49페이지에 나누어 데이터가 정렬 상태로 입력되었고
나머지 공간은 헤더정보 정도로 보면 되겠다.
| object_id | index_name | index_id | partition_number | row_count | used_pages | reserved_pages |
| 1951032615 | PK_DBO_TEST | 1 | 1 | 8,998 | 49 | 52 |
혹 마음이 급해서 page나 extent의 개념이 없다면 아래 링크로 돌아가기 바란다.
https://hongbosal.tistory.com/24
page하나가 8kb 이 page 8개가 모여서 extent 컴공두 아니라 관심없다고 할 수 있으나 이 기초가 있어야 편안하게 이해할 수 있다고 본다.
부디 위 표를 보고 아~~~~ 하고 이해가 되었기를 빈다.
이번에 노드(Node)에 대해서 알아보자.
노드는 3단계라 보면 된다.
root -> branch(MS에서는 중간노드라 한다.) -> leaf
이 3단계(B*Tree 라 한다.) 노드를 이해하면 인덱스가 왜 정렬인지 이해는 끝난 것과 같다.
눈치가 빠른 사람은 느낌 오겠지만 마지막 단계인 leaf node 가 page라고 생각해도 좋다.
물론 data page와는 다를 수 있으나 clustered index 라면 동일하다고 볼 수 있다.
최 하단계는 8kb(다시 말하지만 오라클은 좀더 확장성이 있다.) 이다.
위 예제를 보면 leaf node cluster형 PK이므로 page 가 49개 존재한다는 의미이다.
그것도 pk컬럼인 col_cd을 ASC 방향으로 이쁘게 정렬된 상태의 묶음으로 말이다.
그러면 root와 하위 branch node는 어떻게 구성될까?
정렬이 되어있으니 각각 하위노드 묶음의 주소값을 저장하고 있으면 된다.
오라클에서는 root와 branch 에는 각 노드를 찾아가기 위한 주소 정보(DBA; Data Block Address : MS 라면 block대신 page라고 하면 되지 싶긴 하다 ㅎㅎ)가 저장되어 있다.
이 주소정보에 실제 KEY값인 col_cd의 min, max값을 주소값으로 가지고 있다고 생각해 보자.
가령 위 표애서 branch A 내 첫번째(branch 1) 주소값에는 min(1), max(183) 가 저장되었다고 본다는 것이다.
가령 아래와 같은 조회 쿼리를 어떠한 매커니즘으로 찾아가는 지 확인해 보자.
SELECT * FROM DBO.TEST WHERE col_cd = 5769;
1. root 에 접근한다.
2. R1 주소에서 key 의 min(1), max(5579)를 비교(부등식)하여 존재(포함)여부 판단 : no~~~
3. R2 주소에서 key 의 min(5580) , max(9998)를 비교 (부등식)하여 존재 (포함) 여부 판단 : yes !!
4. R2 주소에 해당하는 branch C로 접근
5. branch 1 주소에서 key 의 min, max를 비교 (부등식)하여 존재 (포함) 여부 판단 : yes !!
6. branch 1 주소에 해당하는 page32 로 접근
7. page32 읽어서 data를 취득한다.(여기서 인덱스가 nonclustered index이고 출력에 필요한 모든 컬럼을 포함하지 않는다면 랜덤엑세스 과정이 추가로 필요하게 된다.)
랜덤 엑세스가 기본적으로 시퀀스 엑세스보다 스피드가 느리긴 하지만
순서데로 1001 부터 5769 까지 순차적으로 읽고 비교하고의 과정을 거치기 보다는 6단계만에 원하는 데이터에 접근하는 index seek 방식이 월뜽히 바를것이고
이 row count가 커질수록 체감 속도 차이는 엄청날 수 있다.
위 매커니즘의 선행조건은 "정렬(sort)"이다.
주관적이지만 누눈가 내게 index가 무엇이냐고 묻는다면 단연코 index는 sort라고 말한다.
기본적인 기본인 이 부분을 이해하지 못하고 인덱스를 활용한 쿼리를 최적으로 작성할 수도 없고 당연히 인덱스를 생성해서도 안된다.
big history 책들에서 우리가 배우는 것은 누적과 진화 그리고 변이이다.
누적되는 정보가 없이 발전은 존재할 수 없다.
DBMS의 아부지 찰스 버크먼의 튜링상 수상 소감중에 한 말을 인용한다.
컴퓨터 중심이 아닌, 데이터베이스 중심으로 관점을 전환하면, 정보 시스템 분야를 새롭게 이해할 수 있다.
개발자들의 순환문과 조건문에 길들여진 개발자들에게 이제 필요한건 데가주망이다.
이 글은 이비니어스( www.evinious.co.kr )의 소유이며 불펌은 허락하지 않습니다.
'데이터베이스 INDEX' 카테고리의 다른 글
| Sequence access vs Random access (2) | 2025.02.11 |
|---|
