feat. 디플러스 대표 정원혁 선생님의 SQL Server 강의를 듣고서... 선생님이 하신 말씀 중에 가장 뜻 깊은 얘기를 이번 포스트의 제목으로 삼아 보았다.
현재 여러 IT 회사를 다니면서 DB에 대한 문제해결 및 솔루션을 제시해주는 일을 하고 다니신다고 하셨다.
아이유가 단발이 이쁘니까 나도 이쁠꺼얌!
그렇다. 단발이 모두에게 좋은 스타일은 아니다... 이처럼 DB세상에도 모두 좋은 것은 없다... 이를 테면 인덱스랄까;
와 근데 아이유 진짜 졸라 이뿌다...
1. 인덱스란
1) 정의
데이터베이스 테이블에서 하나 이상의 컬럼에 대한 값의 순서와 위치를 미리 저장한 데이터 구조로, 빠르고 효율적인 검색 작업을 지원합니다. 인덱스는 책의 색인처럼 작동하며, 특정 데이터를 빠르게 찾을 수 있도록 도와줍니다.
정의와 동시에 인덱스의 장점이라고 볼 수 있는 내용이다. 테이블의 검색 성능을 향상시키고 책의 색인(목차)처럼 데이터가 정렬되어 있어서 데이터의 순서와 위치를 미리 저장해 두고 찾아 볼 수 있다.
2) 단점
1, 저장 공간 사용량이 증가한다.
2, DML 성능 저하의 가능성이 있다.
3, 인덱스가 많아질수록 데이터 구조가 복잡해진다.
크게 꼽자면 위의 세 가지가 가장 큰 단점이라고 볼 수 있다. 자세한 내용은 아래 인덱스에는 어떠한 종류들이 있고 어떠한 장·단점과 적재적소에 써야하는지 알아보도록 하자.
"우선, 그 전에 우리는 데이터베이스에서 데이터의 구조가 테이블에 어떠한 형태로 들어가 있는지 파악하여야 한다."
2. 인덱스 구조(feat. B-Tree)
인덱스는 B-Tree(Balanced-Tree) 구조로 구성되어 있다. 말 그대로 뿌리, 가지, 잎 순으로 균형잡힌 구조를 띄는데 이는 마치 나무를 거꾸로 뒤집어 놓은 모양이라고 생각하면 편할거 같다. 여기서 실제 데이터는 Data Level에 존재하게 된다.
실제로 테이블 인덱스가 담겨 있는 말단 페이지 하나를 Leaf Page 혹은 Leaf Node라고 하고 본인은 이하 본문 부터 Page라는 용어로 통일하도록 하겠다.
해서 이 인덱스 구조에서 실제 데이터가 어느 페이지에 있는지 그리고 해당 데이터를 물리적 or 논리적으로 어떻게 검색하는지 에 따라 인덱스의 종류가 나뉘어지느...ㄴ 솰라솰라 ㄴ어ㅜ얼ㄴ풔
결론, 인덱스는 크게 보면 두 가지다.
3. 인덱스의 종류
1) 클러스터 인덱스 (Clustered Index)
사진출처) https://gwang920.github.io/database/clusterednonclustered/
클러스터 인덱스의 가장 큰 특징은 그림과 같이 인덱스의 정보를 저장하는 가장 말단 끝 레벨인 Leaf Level에 테이블의 데이터(Data Page)가 함께 저장되어진다는 것이다.
클러스터 인덱스가 설정되어 있는 테이블은 조회시 Table Scan이 아닌 Index Seek나 Index Scan이 발생하여 빠른 시간안에 찾고자 하는 데이터를 불러올 수 있다. (단, WHERE절에서 컬럼을 Index로 잡았다면)
데이터들의 주소 정보(키값)를 담고 있는 최종 레벨이 즉 Leaf Page가 Data Page와 동일하기 때문에 테이블 스캔을 할 필요 없이 인덱스 시크나 스캔만으로 빠르게 데이터를 훑어 찾아낼 수 있는 것이다.
그래서 인덱스 씨크고 스캔이고 그게 뭔데?
본인도 정원혁 선생님께서 테이블을 만들고 쿼리를 실행해주시면서 알려줬을 때 벙쪘었다... 따라서 3만건 정도의 테이블을 만들고 나서 실험해보니 알게되었달까?
2) 클러스터 인덱스 실행 테스트 - Index Seek
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE,
OrderAmount DECIMAL(10, 2)
);Orders 라는 테이블을 하나 만든다 참고로 PK를 지정하게 되면 테이블에 자동적으로 클러스터드 인덱스가 생성된다.
DECLARE @i INT = 1;
WHILE @i <= 30000
BEGIN
INSERT INTO Orders (OrderID, CustomerID, OrderDate, OrderAmount)
VALUES (
@i,
(ABS(CHECKSUM(NEWID())) % 1000) + 1, -- 1 ~ 1000 사이의 임의의 CustomerID
DATEADD(DAY, @i % 365, '2023-01-01'), -- 임의의 OrderDate (2023년 내의 날짜)
ROUND((ABS(CHECKSUM(NEWID())) % 10000) / 100.0, 2) -- 임의의 OrderAmount (0.00 ~ 100.00)
);
SET @i = @i + 1;
END;이렇게 3만건의 임의의 데이터를 만들어 넣어 주었다 여기까지 GPT형의 도움을 받아 빠르게 진행했다. 고마워요 GPT형~ 정원혁 선생님께서는 AI를 잘 활용하는 것이 더욱 미래지향적인 개발자라고 하셨다. 라고 변명변명~
각설하고 여기서 다음 두 개의 쿼리를 실행해보자
-- 특정 OrderID에 대한 조회
SELECT * FROM Orders WHERE OrderID = 15000;
-- 특정 범위의 OrderDate에 대한 조회
SELECT * FROM Orders WHERE OrderDate BETWEEN '2023-06-01' AND '2023-06-30';
우선, 해당 버튼을 클릭하여 실행 계획 창을 활성화 시켜주고 첫번째 쿼리를 실행하게 되면 아래와 같은 결과를 얻을 수 있다.
좌측은 쿼리 실행 메시지이고 우측은 실행 내용이다.
페이지 I/O가 2회 발생하였으며 이외에 동원된 자원은 없다. 여기서 "실제 읽기"란 디스크를 실제로 읽었다는 뜻이고 "미리 읽기 읽기" 란 (Read-Ahead Reads)로 순차적으로 데이터를 읽어야 하는 경우에 읽어야할 페이지를 미리 읽어 들여와 디스크에 I/O 지연을 줄여 성능을 높이는 방식이란 정도로만 알고 있으면 좋겠다. 사실 나도 잘 모른다...;;
아무튼 3만건의 데이터 중 단일 데이터를 찾는데 3만건을 모두 훑지 않고 단 2회의 디스크 읽기로 찾아낸 셈이다. 난 감탄을 금치 못하였다.
이처럼 Index Seek는 인덱스의 특정 부분만 선택적으로 읽기 때문에 높은 선택도를 필요로 한다. 선택도는 쉽게 말해 해당 테이블에서 내가 찾는 행이 얼마나 "고유한가" 에 대한 얘긴데 자세한 얘기는 다음 포스트를 통해 정리하도록 하겠다.
성능이 아주 효율적으로 일어난 Index Seek의 예시이다.
3) 클러스터 인덱스 실행 테스트 - Index Scan
그렇다면 두번째 특정 범위를 검색하는 OrderDate에 대한 검색을 해보겠다.
-- 특정 범위의 OrderDate에 대한 조회
SELECT * FROM Orders WHERE OrderDate BETWEEN '2023-06-01' AND '2023-06-30';
페이지 I/O가 총 실제읽기 1회 + 미리 읽기 101회로 총 102회가 발생하였으며 앞선 Index Seek와는 다르게 미약하게나마 경과 시간이 더 걸렸다는 점을 알 수 있다.
스캔 수1이 눈에 띄는 데 이는 OrderDate의 해당하는 범위에 인덱스를 스캔하였다는 뜻이다. 그로 인해 데이터를 순차적으로 읽어들여서 확인하여야하는 경우가 되어 버렸고 미리 읽기가 101회 발생, Index Scan이 일어났다고 볼 수 있는 것이다.
다시 말해 낮은 선택도에 의해 쿼리 실행 속도가 조금은 느려졌다고 말할 수 있을 것이다. 물론 방금까지의 예시에서 느려진 정도로는 아무런 문제가 되지 않는다.
선생님께서는 선택도가 테이블의 전체행에서 1% 미만이면 쿼리 실행에 문제가 없을 거라고 하셨다. 클러스터 인덱스를 활용할 때에는 이 선택도에 대한 것을 잘 고려하여 써야 할 것이다.
4) 넌클러스터 인덱스 (Non Clustered Index)
사진출처) https://gwang920.github.io/database/clusterednonclustered/
넌클러스터 인덱스는 Leaf Level과 Data Level이 분리되어 있고 Leaf Level에서 Data Page의 데이터 주소 정보 들고 있다.
그림에서 Leaf Level의 201페이지에 1번 행을 보면 102:1이라는 주소 정보를 담고 있다. 해당 정보를 찾아 Data Page로 이동하면 102페이지에 1번행이 한국이라는 값을 얻을 수 있는 것이다.
단, 넌클러스터 인덱스는 반드시 클러스터 인덱스를 만들고 만들어야 한다.
클러스터 인덱스와 넌클러스터 인덱스를 만드는 쿼리와 방법은 공식 문서가 있긴 하나...
선생님 피셜 공식문서는 설명이 개떡같다고 하셔..ㅅ ㄷ..;; 그래서 괜찮은 블로그를 소개해 볼까 한다.
는 개뿔 GPT 형아 한테 물어보면 잘 알려준답...;
5) 넌클러스터 인덱스 실행 테스트 - Look Up
-- 특정 CustomerID 대한 조회
SELECT * FROM Orders WHERE CustomerID = 500;이번엔 넌클러스터 인덱스로 설정한 CustomerID를 특정해서 조회해 본 결과
?????????????????????????????????????
웬 쌩뚱맞은 중첩 루프 조인과 룩업이라는 것이 나를 반겨 주었다... 아니 반겨준게 맞는건가 당황스럽다;
그런데 생각보다 이러한 과정이 일어난 이유는 간단했다.
1, Index Seek => idx_CustomerID 인덱스를 활용하여 CustomerID = '500'으로 높은 선택도를 주었기에 Index Seek가 발생, 해당하는 OrderID를 조회한 임의에 테이블이 만들어짐
2, Look Up => Index Seek를 통해 찾은 OrderID로 나머지 데이터들을 실제 테이블에서 가져와 임의에 테이블이 만들어짐
3, 조인을 통해 두 임시 테이블을 내부 조인을 걸어 최종 쿼리 결과를 도출해냄
룩업을 통해 실제 테이블에서 데이터를 가져온다라는 뜻이 곧 Leaf Level과 Data Level이 분리되어 있다는 반증인 것이다!
참고, 중첩 루프 조인은 조인중에 가장 흔해 빠진 방식이며 선행 테이블에서 도출해낸 검색결과를 반복해서 후행테이블에서도 검색하는 방식이다. 자세한 내용은 해당 블로그 참조.
6) 넌클러스터 인덱스 실행 테스트 - Index Scan
-- 특정 범위의 OrderDate에 대한 조회
SELECT * FROM Orders WHERE OrderDate BETWEEN '2023-06-01' AND '2023-06-30';자 이번엔 앞서 클러스터 인덱스에서 테스트했던 특정 범위에 대한 조회를 해보겠다.
앞선 클러스터 인덱스의 Index Scan과 동일한 결과가 도출되었다. 이유는 OrderDate라는 컬럼은 클러스터와 넌클러스터 어느 인덱스로도 설정되지 않은 것이였고 데이터 조회를 위해 스캔이 불가피하다는 것이 동일한 결과를 불러온 것이다.
결론, 선택도가 높은 좁은 범위의 검색은 Look Up이 발생하고 선택도가 낮은 넓은 범위에서는 Index Scan이 발생한다.
선생님께서는 말씀하셨다. 테이블의 전체행에서 10%정도의 Size면 넌클러스터 인덱스를 활용해 볼만한 가치가 있다고 하셨다.
4. 비교 분석
● 클러스터 인덱스는 리프 레벨과 데이터 레벨이 동일한 위치에 있고 넌클러스터 인덱스는 그렇지 않다.
● 클러스터 인덱스는 그로 인해 물리적인 성격으로 띄고 넌클러스터 인덱스는 Look Up을 통해 데이터 페이지에 접근하기 때문에 논리적인 성격을 띈다.
● Size는 각각 1%, 10%내의 조회량으로 예상될 때 활용하는 것이 효율적이다.
● 높은 선택도를 가정하였을 때 스캔 방식은 각각 Index Seek와 Look Up으로 나뉜다.
● 클러스터 인덱스는 인덱스 정보를 담을 별도의 공간이 필요하므로 저장 공간에 있어서는 넌클러스터 인덱스보다 불리하다.
이상 지금까지 클러스터, 넌클러스터 인덱스에 대한 강의 내용을 공부하고 풀어보았다. 이처럼 인덱스란 장단점이 있기에 모든 테이블에 적용할 수 없고 비즈니스 로직을 잘 헤아려 보아야 한다. DB세상에 모두 좋은 것은 없다 라는 말을 단편적으로 이해해보는 시간을 가져 보았다. 아직 배운 내용이 엄청 많고 그에 비해 포스트해야 될 내용도 엄청 많다. 다음에는 이 인덱스를 만든 테이블에 DML를 쓰면 어떠한 영향을 미치는지, 밀도와 선택도에 대한 이야기, Search Argument (SARG)를 통한 WHERE절 처리 방법 등을 정리해보겠다! DB세상에 모두 좋은 것은 없다 라는 말을 더욱이 깊게 느껴 질 수 있는 내용이 될 듯하다.
DB때문에 스트레스 받고있는 영혼들에게 조금이나마 도움이 되길 바라며 다들 파이팅!
