스터디그룹 > ORACLE,MYSQL DB관련 > INDEX 사용하기 mssql,oracle,mysql

INDEX 사용하기

by kkaok
2003-06-24

인덱스의 활용

만약 우리가 집을 짓는데 제일 먼저 해야 할 일이 뭘까? 그것은 아마도 설계일 것이다. 기초가 되는 기본 설계를 잘 해야만이 그 후 작업이 원활하고 최적화가 되는 것이다.
프로그래머는 java, servlet, jsp뿐 아니라 최소한의 데이터베이스에 대해서도 알아야 한다. 게시판, 카운터, 일정관리 등을 만드는데 DB 설계를 다른 사람이 해줄 수도 있겠지만 대부분이 개발자가 설계하고 만들기 때문이다. 여기서는 DB에 대해 심도 깊은 공부를 할 수는 없지만 최소한의 지식에 대해 배우기로 한다.

인덱스란 무엇인가?

SQL서버에서 테이블을 만들고 데이터를 추가, 수정, 삭제 할 때 데이터의 레코드는 내부적으로 아무런 순서 없이 저장된다. 이때 데이터 저장영역을 Heap이라고 한다. Heap에서는 인덱스가 없는 테이블의 데이터를 찾을 때 무조건 전체 데이터 페이지의 처음 레코드부터 끝 페이지의 마지막 레코드까지 다 읽어서 검색조건과 비교하게 된다. 이런 식의 데이터 검색방법을 테이블 스캔(table scan) 또는 풀 스캔(full scan)이라고 한다. 이럴 경우 양이 많은 테이블에서 일부분의 데이터만 불러 올 때 풀 스캔을 하면 처리 성능이 떨어진다. 즉 인덱스는 데이터를 select 할 때 빨리 찾기 위해 사용된다.

인덱스 생성 시 고려할 점

인덱스를 생성 시에는 where 절과 join, order by 등과 관련된 칼럼중 사용 빈도가 높고 키 값의 선별도가 좋은 칼럼에 사용해야 한다. 반대로 사용 빈도가 낮고 칼럼의 선별도가 나쁜, 예를 들어 한 칼럼의 값이 true/false, 성별(M/F) 등에는 인덱스를 사용하지 않는 것이 좋다 또 테이블이 작거나 자주 갱신 될 때도 사용하지 않는 것이 좋다.

인덱스의 생성

인덱스에는 크게 clustered와 nonclustered 인덱스로 나눌 수 있다. clustered 인덱스는 물리적 정렬로 DB에 데이터를 입력 시 이것을 기준으로 입력이 된다. 따라서 한 테이블에 오직 하나만 존재 할 수 있으며 table을 열었을 때 order by를 사용하지 않아도 데이터가 clustered 인덱스에 따라 정렬이 되어 있는 것을 확인 할 수 있다. 물리적으로 정렬이 되어 있는 만큼 가장 빠른 처리를 한다. nonclustered 인덱스는 clustered 인덱스와는 달리 중복된 값을 가지면 한 테이블에 여러 개를 생성 할 수 있다. 이 외에 unique에 대해 알아보겠다. unique는 말 그대로 중복을 허용하지 않는 값을 보호 할 때 사용한다. 예를 들어 회원 관리 프로그램에서 아이디가 중복되는 것을 막고자 한다면 이 옵션을 사용하면 된다. 우리가 자주 사용하는 primary key의 경우에는 clustered와 unique 특성을 갖게 하는 제약키이다.

① mssql

인덱스생성 문법

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
INDEX index_name ON table (column [,...n])
[WITH
[PAD_INDEX]
[[,] FILLFACTOR = fillfactor]
[[,] IGNORE_DUP_KEY]
[[,] DROP_EXISTING]
[[,] STATISTICS_NORECOMPUTE]
]
[ON filegroup]

인덱스의 생성 예제

create clustered index ix_kkaok on kkaok(seq)
width DROP_EXISTING, FILLFACTOR = 80

kkaok 테이블에서 seq 칼럼에 대해 ix_kkaok라는 클러스터드 인덱스를 생성하라는 명령문이다.

DROP_EXISTING 옵션은 기존에 같은 이름의 인덱스가 존재한다면 삭제하고 생성하라는 의미이며 Fillfactor 값은 명시하지 않으면 기본적으로 100% 페이지에 채워 넣으라는 뜻이다. 지금은 80% 채워 넣고 20% 여유공간을 가지라는 의미이다.

결합 인덱스의 생성 예제

create index ix_kkaok2 on kkaok(seq,re_step)

seq와 re_step 두 개의 칼럼에 인덱스를 생성하였다. 각각의 칼럼에 대해 내림차순 정렬과 오름차순 정렬 옵션을 줄 수 있지만 mssql 7.0에서는 내림차순 정렬을 지원하지 않는다.

인덱스 보기

exec sp_helpindex '테이블이름'

인덱스 제거

drop index 'table.index'[,...n]

인덱스의 삭제 예제

drop index kkaok.ix_kkaok

제약키의 변경

primary key는 인덱스가 아니고 제약키이다. 따라서 인덱스를 삭제하는 방법으로 삭제 할 수는 없다. 제약키는 다음의 방법으로 삭제 또는 변경 할 수 있다.

제약키의 삭제
alter table kkaok drop constraint PK_kkaok

제약키의 추가변경
alter table kkaok add constraint PK_kkaok primary key(seq)

② oracle

인덱스생성 문법

CREATE [BITMAP | UNIQUE]
INDEX index_name ON table (column[, column]...) ;

인덱스의 생성 예제

create unique index ix_kkaok_indx on kkaok(seq);

kkaok 테이블에서 unique 한 값으로 seq 칼럼에 대해 ix_kkaok_indx라는 인덱스를 생성하라는 명령문이다.

결합 인덱스의 생성 예제

create index ix_kkaok_indx2 on kkaok(seq,re_step);

seq 와 re_step 두 개의 칼럼에 인덱스를 생성한다. 각각의 칼럼에 대해 내림차순과 오름차순 정렬 옵션을 줄 수 있으나 hint를 사용시 정렬이 안되는 경우가 있어서 정렬과 관련된 것은 hint에서 처리를 하면 된다.

인덱스 보기

select index_name, index_type from user_indexes;

인덱스 제거

drop index 인덱명

인덱스의 삭제 예제

drop index ix_kkaok_indx;

제약키의 삭제 예제

alter table kkaok drop constraint kkaok_pk_re_step;

제약키의 추가 예제

alter table kkaok add constraint kkaok_pk_indx2 unique(seq);

③ mysql

인덱스생성 문법

BLOB과 TEXT 타입을 제외한 모든 mysql의 칼럼 타입은 인덱스로 사용될 수 있다. 한 테이블에는 16개의 인덱스를 생성할 수 있으며 최대 인덱스의 길이는 256바이트이다. 또한 인덱스에 사용되는 칼럼은 NOT NULL이어야 한다. 인덱스의 생성은 테이블을 생성할 때 다음처럼 생성하면 된다.

KEY [인덱스명] (col1(length),col2(length),...)

인덱스의 생성 예제

CREATE TABLE test (
id CHAR(20) NOT NULL,
name CHAR(200) NOT NULL,
PRIMARY KEY (id),
KEY index_name (name(200)));

인덱스의 추가 예제

ALTER TABLE test ADD INDEX index_id (id)

인덱스 제거

ALTER TABLE [테이블명] DROP INDEX [삭제할 인덱스명]

인덱스의 삭제 예제

ALTER TABLE test DROP INDEX index_name

PRIMARY KEY의 삭제

ALTER TABLE test DROP PRIMARY KEY

PRIMARY KEY의 추가

ALTER TABLE test ADD PRIMARY KEY (id)