데이터베이스 인덱스를 알아보자

인덱스란

pk,uk로 자동 생성

-탐색 조건에 따라 레코드들을 빠르게 검색하기 위해 사용하는 보조 접근 구조

-보조 접근 경로를 제공하는 디스크상의 별도의 파일

-인덱스 필드를 기반으로 레코드들을 탐색할 수 있는 경로를 제공

-select 의 where,join 을 사용할때 인덱스를 사용하여 쿼리를 빠르게 하는것이 목적

**delete,insert,update쿼리는 노노 느려짐

장점

-기본 키는 자동으로 인덱스

-데이터 형식에 따라 인덱스 가능 여부가 다름

-데이터 검색과 정렬 속도 향상

단점

-인덱스 된 필드에서 데이터 추가,수정,삭제할 때 성능 저하

-인덱스가 디비 공간을 차지해 추가 공간 필요로 하며 .mdb파일 크기도 늘어남

**쿼리속도는 1초 빨라지는데 추가하는건 2초느려짐

-데이터 변경이 자주 일어나면 인덱스 재생성이 필요해질 수 있음

만들지 말아야 할 경우

-데이터 중복도(같은 데이터가 있는 것 성별같은거)가 높은 경우에는 효율성이 떨어진다

-데이터에 대한 수정,삭제,삽입이 자주일어나면 안 하는게 좋음

만들어야 할 경우

-where절에 자주 쓰이는 컬럼

-join에 자주 쓰이는 컬럼

왜자꾸 insert,delete,update에 인덱스가 악영향을 끼친다고 하는걸까?

insert : 인덱스의 block들이 하나에서 두 개로 나누어지는 현상인 insert split이 발생 할 수 있다.

예를 들어서 새로운 데이터가 어떤 블록에 저장해야한다고 하자. 근데 이 블록은 이미 꽉 차버린 것!

그래서 아래와같이 일부 데이터를 새로운 블록에 보내버리고 데이터를 받아들이는 것이다.

이게 무엇이 문제인가!

*Redo : DataBase에서 일어난 모든 변화를 저장하는 메모리 공간

-새로운 블록을 할당받아야되고...키도 옮겨야하고...Redo에 모든수행 과정이 기록되어야하고... 후 할일이 너무많다       

*DML : 삽입,삭제,검색,갱신

-스플릿이 이루어지는 동안 키 값을 보호하기 위해서  DML 이 블로킹됨

 

delete : 인덱스가 삭제된 경우에는 데이터가 지워지지 않고 사용 안된 표시만 해준다

원래는 데이터가 삭제되면 그 공간을 다른 데이터가 사용할 수 있다. 근데..인덱스는 그냥 나 사용안됨~ 이러고 마는것...허걱

update : 인덱스는 이 개념이 아예 없다. 삭제->삽입 이 두가지가 일어나는 것이다 이것만 봐도....알겠다

인덱스의 종류

B-Tree : [위키 B트리 설명 링크]

이진트리를 확장해 하나의 노드가 가질 수 있는 자식 노드가 최대 2개보다 큰 트리구조, 자료를 정렬된 상태로 보관하고 삽입,삭제를 시간복잡도로 할 수 있다.(?)

위 그림에서 컬럼 최소 값이 의미 하는 것은 Root,Branch모두 하위 블럭의 최소 컬럼 값을 저장해서 최소값 기준으로 블럭을 찾아가는것이다.

예를들어 Root에서 20 과 80이라는 컬럼 값이 있다고하자.

나는 이때 40이라는 값을 찾고싶다. 그렇다면 20정보가 담겨있는 블록을 가면된다. 80이 그다음 최소값이니깐.

이렇게 해서 최소값만 쭉쭉 찾아가면 데이터에 접근할 수 있는 것이다.

장점은 어느 row던지 동일한 수 만큼의 블록에 접근한다는것이다.

1.Unique Index : 중복데이터가 없어서 성능이 뛰어나다. create unique index [index name] on [Table](col 1 ASC|DESC,col2,...);

인덱스 생성 후 데이터를 넣어보면 아래와 같이 오류가 발생한다.

2.Non Unique Index : 위에랑 반대로  인덱스 생성할때 unique옵션만 빼면 된다. 중복 값  가능

3.Function Based Index : where,join에 오는 컬럼으로 인덱스를 만들어야한다. ( 오라클은 된다 ) 오라클이 설치가 안되어있어서..나중에 해보자

mysql은 무슨짓을해도 오류를 뱉어낸다.언제나 그렇듯이.. 방법은 있다. 방법링크  하지만 나는 그저 기본적으로 제공하는지에 대해서만 궁금하다 지금은

4.내림차순 그냥 조건에 desc넣어주면 된다

create index idx on Table(col desc);

5.결합 인덱스

두개의 컬럼으로 인덱스를 만드는 것이다. where 에서 and로 묶는걸 만드는경우가 많음 잘못생성하면 성능 저하

2.BTMAP : 데이터 값의 종류가 적고 중복 데이터가 많을 경우에 사용 , 데이터 변경량이 적어야 함 어떤 데이터가 어디에있다는 지도정보를 Bit로 표시(1,0으로 표시해서 1인데만 찾아가는것)

create BITMAP index [인덱스명] on [테이블명(컬럼명)]

비트맵 인덱스를 생성하면 컬럼값의 종류대로 map이 생성된다.예를 들어 아래와 같이

col1 : 10110

col2 : 11000

근데 여기서 col3을 추가하면 기존 비트맵 인덱스를 모두 수정해야 한다..뜨헉...이것이 큰 문제점이라고 한다 비트맵 인덱스는 게다가 블록단위로  lock도 걸어버린당..

많은 글에서도 알 수 있지만 대게로 b-tree index를 사용한다고 한다.

http://runtoyourdream.tistory.com/137