인덱스(Index)와 쿼리 속도는 어떤 관계일까.

https://memoryman.tistory.com/6 프로젝트를 포스팅하다가 쿼리 속도는 어느 부분에서 차이가 발생하는지 궁금하게 되어서 포스팅해요. 쿼리 속도 빠르게 하려면 어떻게 해야하는지 알아볼게요. 

 

시작은 테이블 생성입니다.  

간단하게 이름과 핸드폰 정보 테이블을 생성할게요. 

primary key는 기본키를 identity는 자동증감을 의미해요.

(기본키에는 인덱스가 걸려있고 자동증감은 1부터 1씩 증감하겠다라는 뜻)

create table MemoryManTable(
id int primary key identity(1,1)
, name varchar(100)
, phone varchar(100)
)

 

 

데이터가 가장 문제였는데 랜덤으로 데이터를 만들고 1개씩 insert 하는 방식으로 처리하니

100만개 넣는데 8분 정도 걸렸습니다. ㅋㅋ...

declare @count  int = 1;

while @count <= 1000000
begin 
set @count = @count +1;
insert into MemoryManTable(name,phone)
select CONCAT(CHAR(97 + abs(CHECKSUM(newid())) % 26),
        CHAR(97 + ABS(CHECKSUM(NEWID())) % 26),  
        CHAR(97 + ABS(CHECKSUM(NEWID())) % 26),  
        CHAR(97 + ABS(CHECKSUM(NEWID())) % 26),  
        CHAR(97 + ABS(CHECKSUM(NEWID())) % 26),  
        CHAR(97 + ABS(CHECKSUM(NEWID())) % 26))  as 'name',
		CONCAT('010',RIGHT(rand(),4)
		, RIGHT(rand(),4)) as 'phone'
end

 

여기서 잠깐! 인덱스가 무엇인지 정리하고 가시죠.

목차 같은 녀석입니다. 책에서 목차를 보고 원하는 페이지를 찾아가는 구조랑 같아요.

쿼리 속도에 있어서 아주아주 중요한 요소라고 보시면 될 것 같네요. 

정리하면 인덱스는 데이터 검색 속도를 향상시키기 위해 사용하는 자료구조이며 종류도 상당히 다양합니다.

 

 

Clustered Index(클러스터형 인덱스) 

앞전에 테이블 만들 때 primary key를 기억하시나요?

기본키라고 불리며 적용된 컬럼은 클러스터형 인덱스가 됩니다.

클러스터형 인덱스는 데이터 자체가 정렬된 상태로 저장되는걸 의미합니다. 

id에만 인덱스가 적용되었지만 name,phone도 영향을 받는다는 말이죠.

다시 정리하면 클러스터형 인덱스는 primary key 적용시 생성되는 인덱스로 테이블당 1개만 생성 가능합니다. 유일한 녀석이죠.

인덱스를 사용하기 위해서는 where 조건문에 인덱스가 적용된 컬럼을 사용해야하는데 아래 쿼리가 전형적인 인덱스가 적용된 쿼리라고 볼 수 있습니다. 

select *
from MemoryManTable
where id between 1 and 10000

 

 

Physical Operation, Logical Opertaion 값이 Clustered Index Seek이라고 표현되었는데 이러면 인덱스가 적용된 겁니다.

Physical은 물리적 연산으로 실제로 실행되는 영역이고 Logical은 논리적 연산으로 어떤 방식으로 데이터를 가져올지 결정 합니다. 

 

한번 속도를 비교해보도록 하죠. 

그전에 아래 쿼리 실행시켜주시기 바랍니다. 

alter table MemoryManTable add checkNo int

update MemoryManTable
set checkNo = id

create table noIndex(
id int
,name varchar(100)
,phone varchar(100)
,checkNo int
)

 

아래 쿼리 돌려주세요. ㄱㄱ

--인덱스를 온전히 사용한 경우
select *
from MemoryManTable
where id between 1 and 10000

--클러스터 인덱스 영향으로 인덱스 검색은하지만 전체 인덱스를 검색하는 비효율적 경우
select *
from MemoryManTable
where checkNo between 1 and 10000

--인덱스가 전혀 없는경우
select *
from noindex
where id between 1 and 10000

 

쿼리 순서대로 결과입니다. 

그림은 3개지만 총 4가지 검색 방법이 이루어졌는데요. 

(Clustered Index Seek, Parallelism, Clustered Index Scan, Table Scan)

1번째 Clustered Index Seek 아주 베스트 입니다. 인덱스를 제대로 사용했고 속도도 가장 으뜸이네요.

2번째 Clustered Index Scan 클러스터 인덱스 효과로 인덱스를 검색하기는하지만 전체 인덱스를 검색하기에 Seek 보다 성능이 떨어집니다. 

3번째 Parallelism 병렬처리하는 자료구조로 뜬금 없이 튀어나온 녀석 입니다. 

SQL 서버에서 자동으로 적용되는 기능으로 병렬처리로 처리 속도를 향상 시켜 줍니다. 

Parallelism이 적용되지 않았다면 Index Seek과 Index Scan에서 차이가 많이 났을거에요. 

4번째 Table Scan 최악 입니다. 현재 조회가 Table Scan으로 되어 있다면 당장 인덱스를 추가해야 합니다. 

결과적으로 Index Seek > Index Scan > Table Scan 순으로 정리할 수 있겠네요. 

(Parallelism은 SQL 서버가 컨트롤하는 녀석으로 제외)

 

 

실제로 테스트 해보면서 시간 차이가 크지 않아서 인덱스의 위대함이 체감이 되지는 않네요.

현업에서 사용하는 쿼리는 느린게 한가득인데 말이죠 ㅋㅋ...

테이블의 컬럼이나 데이터가 크지 않아서라고 생각 합니다. 
저의 짧은 지식으로는 인덱스의 위력을 제대로 테스트하지 못하였는데 그래도 하나 확실한건 Table Scan 사용중이라면 당장 튜닝이 필요하다는 겁니다.

오늘도 지식 얻어가면서 메모리!