================
인덱스의 사용(1)
================
이제부터 본격적으로 물리적인 요소에 대해서 알아볼 것입니다. 이제까지는 거의 논리적,
개념적인 것을 주로 이야기했었지요. 이제부터 직접 몸에 와 닿는 것에 대해서 씨부려
보겠습니다. 여기서 주의해야 할 것은 이제는 특정 DBMS에만 해당되는 이야기를 할 수
밖에 없다는 것입니다. 여러 DBMS를 대상으로 이야기 한다는 것이 너무나도 벅차거덩요..
이제 MSSQL Server를 주로 이야기하고 가끔은 다른 DBMS에 대해서도 언급할 것입니다.
저는 오라클하구 MSSQL 밖에 잘 모릅니다. DB2도 아주 조금 알고 있기는 하지만 너무
허접스럽죠..그러니 이러한 점을 염두해 두시기 바랍니다. 글고 6.5 버전이하에 대해서는
언급하지 않겠습니다.
<참고>야시의 PC 정보
CPU: AMD 1.33G
RAM: 1G
HDD: 60G 7200 rpm
OS : Windows 2000 Server
DBMS Version: MSSQL Server 2000
앞서서 인덱스가 무엇인가에 대해서 살펴보았습니다. 요즘은 인덱스를 지원하지 않는
DBMS는 없지요. 그만큼 인덱스라는 것이 정보를 요구하는 사용자에게 적시에 정보를
제공하기 위해서 즉, 제대로된 정보를 제공하기 위해서는 꼭 필요하다는 것입니다.
MSSQL Server는 두가지 인덱스를 제공합니다. 클러스터드 인덱스와 논클러스터드 인덱스..
간단히 설명하자면 클러스터드 인덱스는 책 뒤의 찾아보기나 사전을 생각하면 되겠습니다.
어떤 용어를 찾을때나 영어단어를 찾을때를 생각해보세요. 그 많은 용어나 단어에서 어떻게
원하는 정보를 재빨리 찾을 수 있는지.. 그 원리와 비슷함돠. 논클러스터드 인덱스는
책의 목차와 비슷합니다. 즉, 원하는 단어나 용어를 찾기 위해서는 책의 목차를 가지고
대충 어디쯤에 있는지를 파악한 조금더 노가다를 해서 찾아야 하지요.
클러스터드 인덱스는 트리구조의 맨 마지막 부분에 데이터가 있습니다. 즉, 리프레벨에
데이터가 있다는 것입니다. 논클러스터드 인덱스의 경우는 리프레벨 한단계더 밑에 실제
데이터가 존재하는 것입니다.
개념은 이렇구요..더 자세한 부분은 도움말, 책, 웹문서를 더 참조하시기 바랍니다.
이제 실습을 해보도록 하겠습니다. 먼저 실습을 위해서 하나의 테이블을 만들고 데이터를
삽입하도록 하겠습니다. 제대로된 실습을 위해서 200만건을 가지고 있는 테이블을 만들
도록 하겠습니다. 아래의 스크립트를 돌리기 전에 충분한 디스크 공간을 확보하기 바람돠.
저의 경우 54초가 걸렸습니다. 실제 데이터는 80메가 정도인데 로그가 400메가 정도
됩니다. 그러니 550메가 정도의 여유는 있어야 합니다.
<실습용 테이블 만들기>
USE Northwind
GO
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'INDEX_TEST' AND Type = 'U')
DROP TABLE INDEX_TEST
GO
CREATE TABLE INDEX_TEST(
id int identity(1,1) primary key,
OrderID int,
ProductID int,
UnitPrice money,
Quantity int,
Discount real
)
GO
DECLARE
@i int
SET @i = 1
BEGIN
SET NOCOUNT ON
WHILE(@i < 1000)
BEGIN
INSERT INTO INDEX_TEST
SELECT OrderID+@i, ProductID+@i, UnitPrice +@i ,
Quantity+@i, Discount +@i
FROM [Order Details]
SET @i = @i + 1
END
SET NOCOUNT OFF
END
GO
SELECT COUNT(*) FROM INDEX_TEST
-----------
2152845
먼저 인위적으로 풀스캔을 유도해 보겠습니다. id 컬럼을 기본키로 잡아주었기 때문에
클러스터드 인덱스를 자동으로 생성했습니다. 일단 정확하게 하기 위해서 인덱스를
재생성하도록 하겠습니다.
먼저 sp_help 저장프로시저를 이용해서 인덱스의 정보를 얻습니다.
exec sp_help index_test -- 테이블에 대한 정보 보기
index_name
-------------------------
PK__INDEX_TEST__440B1D61
인덱스를 다시 만듭니다. 이 작업도 조금 오래 걸립니다. 16초가 걸렸습니다.
DBCC DBREINDEX (index_test, 'PK__INDEX_TEST__440B1D61', 100)
이제 다음과 같이 인덱스가 잡혀있는 컬럼에 대해서 변형을 가해 인덱스를 사용해서 검
색하지 않고 오로지 스캔만 이용해서 검색을 하게 하도록 하겠습니다.
USE Northwind
GO
SET STATISTICS IO ON -- 입출력 양을 알아보기 위해서...
-- 전체 테이블의 10%를 가져온다.
SELECT * FROM INDEX_TEST
WHERE rtrim(id) < 2152845*0.1
----
결과
----
'INDEX_TEST' 테이블. 스캔 수 1, 논리적 읽기 수 9893, 물리적 읽기 수 0, 미리 읽기 수 0.
검색한 데이터는 빼고 물리적인 양만을 보았습니다. 여기서 주목해서 볼 것은 논리적 읽기 수입니다.
숫자가 의미하는 것은 몇 페이지(페이지당 8KB)만큼 읽었는지를 말하는 것입니다.
그렇다면 rtrim()함수를 쓰지 않고 옵티마이저가 인덱스를 사용하게끔 쿼리를 날려보겠습니다.
SELECT * FROM INDEX_TEST
WHERE id < 2152845*0.1 -- 고의적으로 인덱스 컬럼을 변형시켰다.
----
결과
----
'INDEX_TEST' 테이블. 스캔 수 1, 논리적 읽기 수 990, 물리적 읽기 수 0, 미리 읽기 수 0.
앞선 결과에서는 논리적 읽기 수가 9893이고 지금은 990 입니다. 이를 계산해 보면..
select cast((9893*8) as varchar) + 'KB' as '풀스캔의 경우' ,
cast((990*8) as varchar) + 'KB' as '인덱스를 사용한 경우'
풀스캔의 경우 인덱스를 사용한 경우
-------------------------------- --------------------------------
79144KB 7920KB
(1개 행 적용됨)
거의 10배에 가까운 차이가 나는 것을 알 수 있습니다. 즉, 처음의 쿼리는 79MB를 읽어서
원하는 데이터를 찾았고, 인덱스를 이용했을 때는 7.9MB만을 읽엇 찾은 것입니다. 실로
엄청난 차이지요. 이러한 것은 테이블이 커짐에 따라서 엄청난 차이를 볼 수 있습니다.
지금의 경우는 범위 검색을 했는데 만약 단일행 검색의 경우는 엄청난 차이가 나겠지요?
해보까요?
SELECT * FROM INDEX_TEST
WHERE id = 1
id OrderID ProductID UnitPrice Quantity Discount
----------- ----------- ----------- --------------------- ----------- ------------------------
1 10249 12 15.0000 13 1.0
(1개 행 적용됨)
'INDEX_TEST' 테이블. 스캔 수 1, 논리적 읽기 수 3, 물리적 읽기 수 0, 미리 읽기 수 0.
SELECT * FROM INDEX_TEST(index = 0) -- 이것은 힌트다. 인덱스를 사용하지 않겠다고 옵티마이저에게 일러두는것
WHERE id = 1
id OrderID ProductID UnitPrice Quantity Discount
----------- ----------- ----------- --------------------- ----------- ------------------------
1 10249 12 15.0000 13 1.0
(1개 행 적용됨)
'INDEX_TEST' 테이블. 스캔 수 1, 논리적 읽기 수 9908, 물리적 읽기 수 0, 미리 읽기 수 0.
엄청난 차이입니다. 무려 3000배나 넘게 입/출력 양이 많습니다. 이게 바로 인덱스의
힘인 것입니다. 이렇게 보면 인덱스가 무작정 좋아 보일 수도 있습니다. 그러나 인덱스
자체가 하나의 군더더기임니다. 즉, 옵션이라는 것이죠. 시스템 전체적으로 볼 때는 이
것도 시스템의 자원을 소모하는 요소라는 것입니다. 흠...오늘은 이정도로 하고.....
마치도록 하겠습니다.
이것도 다 아는거라구요? ㅎㅎ
모르는 사람도 있겠죠머..^^
슈퍼아이피
10-12-31 12:09
0개
636회
MSSQL
인덱스의 사용의 이득
추천
0
비추천
0
댓글목록
등록된 댓글이 없습니다.