데이터베이스 8. 인덱스

1. 인덱스 데이터를 빠르게 찾아주는 도구, 실무에서 매우 중요함.

 

2. 클러스터형 인덱스, 보조 인덱스 

클러스터형 인덱스는 기본키로 지정하면 자동으로 생성되는 인덱스. 테이블에 1개만 만들수 있다. 

지정한 열에 자동으로 생성된다. 

 

보조인덱스는 고유키로 지정하면 자동으로 생성되며 여러개를 만들 수 있지만, 자동으로 정렬되지는 않는다.

 

3. 인덱스는 책의 찾아보기 개념과 비슷하다.

대용량일 수 록 더욱 차이가 난다

 

4. 인덱스는 과용할 경우 오히려 느려질 수도 있음

비교를 통해 fullscan이 빠를 지 인덱스를 사용하는 것이 빠를지 생각해 봐야함.

 

5. 인덱스의 장단점 

장점 검색속도가 빨라짐(항상은 아님), 전체 성능 향상

단점 인덱스는 데이터베이스에서 추가 공간을 차지함 평균 10%정도, 처음 인덱스 생성에 시간 소요

데이터 변경(insert, delete, update)이 자주일어나는 환경이라면 성능이 나빠맂ㄹ 수도 있음

 

6. 클러스터형 인덱스

use market_db;

create table table1 (

  col1 int primary key,

  col2 int,

  col3 int

);

show index from table1;

이렇게 테이블 생성 시 기본키를 설정했다면 기본키에 클러스터형 인덱스가 자동생성된다

 

7. 고유인덱스, 단순인덱스

고유인덱스 : 인덱스 값이 중복되지 않는다는 의미 기본키, 고유키로 지정 시 고유인덱스로 생성

단순인덱스 : 인덱스 값이 중복되기도 하는 인덱스

 

8. Non_Unique = 0  이 의미는 Unique라는 의미이다.

즉 고유인덱스라는 의미임.

 

10. Unique컬럼에는 보조인덱스가 자동으로 생성

create table table2(

  col int primary key,

  col int unique,

  col int unique

)

 

show index from table2;

 

테이블 생성 시 unique옵션을 넣으면 보조인덱스가 자동으로 생성된다.

보조인덱스는 테이블 당 여러개 생성될 수 있다.

기본키가 없고 unique not null컬럼이 1개 이상이라면 그 중 1개의 컬럼은 클러스터형 인덱스가 된다

 

11. 클러스터형인덱스는 물리적으로 데이터가 정렬된다.

Use market_dg;

Drop table if exists buy, member;

create table member (

 mem_id char(8),

 mem_name varchar(10),

 addr char(2)

);

insert into member values('111', '111', '111');

insert into member values('112', '112', '112');

insert into member values('1113, '113', '113');

위 처럼 데이터를 삽입하면 삽인한 순서대로 물리적으로 정렬

 

12. 테이블에 기본키 추가 시 클러스터형 인덱스 생성

alter table member add constraint primary key (mem_id);

select * from member;

이렇게 기본키가 생기면 클러스터형 인덱스가 생성된 대로 정렬됨

 

13. 기본키를 바꾼다면?

alter table member drop primark key;

alter table member add constraint primary key(mem_name);

select * from member;

대용량 데이터가 있는 상황에서 기본키를 바꾼다면 시간이 매우 오래 걸릴 수 있음.

 

14. 클러스터형 인덱스에서 insert

insert into member values('111','111','111');

데이터가 가장 뒤에 삽이되는 것이 아닌 물리적으로 재배치되는 것임.

b-tree구조를 가짐

 

15. 보조인덱스는 정렬되지 않는다

보조인덱스 순서대로 정렬되는 것이 아니라 찾아보기 개념이라고 생각해야한다.

create table member(

 mem_id char(8),

 mem_name varchar(10),

 mem_number int

);

 

insert into member values('111', '111', 111);
insert into member values('113', '113', 113);
insert into member values('112', '112', 112);

그냥 넣는대로 들어간다. 정렬이 안된다

 

alter table member add constraint unique(mem_id);

이렇게 unique옵션을 넣어도 정렬과는 무관하다.(입력된 물리적 순서가 변하지 않는다)

insert into member values('112', '112', 112);

이렇게 입력하면 테이블 맨 뒤에 삽입된다.

 

15. 보조인덱스와 성능

보조인덱스는 여러 개 만들 수 있을나 공간을 차지하고 만들 때 마다 시스템 성능을 저하시킴.

꼭 필요한 경우에만 생성

 

16. 인덱스 내부 작동원리

내부적으로 균형트리의 형태로 만들어짐

데이터가 저장되는 공간을 노드라고 함 루트노드, 중간노드, 리프노드로 구성

Mysql에서는 페이지라고 부름 최소저장단위는 16kb, 데이터 1건만 입력해도 1페이지 필요

 

17. 페이지 분할

데이터 입력으로 인해 페이지 범위를 벗어나는 경우 페이지가 2개로 나뉘어지며 페이지분할이 발생

이 때 데이터는 규칙에 의해 적당히 나뉘어 진다.

 

18. 균형트리는 리프페이지에 새로운 페이지가 생성되면  중간 페이지, 루트페이지도 변경될 수 있다.

 

19. 클러스터형 인덱스는 alter table test1 add constraint peimary key(mem_id)로 하면

페이지로 분할되면 insert delete update할 때마낟 페이지들이 변경됩니다.

 

반면 보조페이지는 alter table second add cosntraint unique(mem_id); 를 해도 

select * from test; 결과는 동일하다 페이지를 건드리지 않기 때문

 

20. 보조인덱스를 구성하면 별도의 공간에 인덱스가 마련된다. 

데이터 위치를 페이지번호 +#번호로 구성한다.

 

21. 클러스터형 인덱스에서 데이터 찾기

select mem_name from cluster where mem_id="spc"

클러스터형만 있을 경우 루트페이지 읽음 리프페이지에서 데이터 찾음 순으로 이동

 

22. 보조인덱스에서 데이터 찾기

select mem_name from second where mem_id="spc"

루트 페이지, 리프페이지 데이커페이지 순으로 2개의 인덱스페이지, 1개의 데이터페이지를 읽음

 

23. 인덱스의 실제 사용

show index from member; 

테이블 인덱스 정보 확인

 

show table status from 데이터베이스;

인덱스 저장 바이트 수를 확인한다

show table status like 'member';

특정 테이블 인덱스 저장 바이트 수를 확인하려면 like사용 

'member%'라고 하면 member로 시작하는 모든 테이블을 이용하는 등 활용해서 검색가능

 

data_length는 리프페이지의 크기를 의미한다

index_length는 보조인덱스 크기를 의미한다.

 

자동생성되는 인덱스가 뿐만 아니라 인덱스를 생성도 가능

create index idx_member_addr on member(addr);

show index from member;

member테이블 addr컬럼에 idx_member_addr 단순보조인덱스 생성(중복허용)

하지만 생성 직후에는 인덱스가 생성되었지만 적용은 안됨

 

analyze table member;

show table status like 'member';

바로 적용을 위해서 테이블 분석을 처리함.

 

고유인덱스 생성

create unique index idx_member_number on member(mem_number);

member테이블 mem_number컬럼에 idx_member_number고유인덱스를 생성하겠다는 의미

테이블 컬럼에 unique속성이 없더라도 데이터 중복이 없다면 고유인덱스 가능함(중복 있으면 오류 발생)

고유인덱스가 생성된 이후에는 중복값이 insert될 수 없음

 

24. 많은 중복값을 가지는 경우 

예를 들어 성별의 경우 남성, 여승으로 이루어 진다.

이럴 경우 인덱스를 생성하는 것은 의미가 없고 오히려 악영향을 끼친다

 

25. 인덱스 사용하기

select * from member;

이 문장은 where조건에 index를 지정하지 않았으므로 full scan을 한다

select mem_id, mem_name, addr from member;

이 문장은 인덱스에 사용된 컬럼이 나왔으나 select 옆에 나오므로 full scan이다.

select * from member wehre mem_name='블랙핑크';

이 문장이 where조건에 사용된 컬럼이 등장하므로 인덱스를 사용한다.

 

26. 인덱스 사용유무는 DMBS판단에 따른다

create index idx_member_mem_number on member(mem_number);

analyze table member;

select * from member where mem_number>=7;

이런식으로 where조건이 있어도 인덱스를 사용할지 안할지는 DBMS가 정한다

 

select * from  member where mem_number*2 >= 14;

열연산을 할 경우 인덱스를 사용하지 않음

 

select * form memebr where mem_number >= 14;

열연산이 아닐 경우 인덱스를 사용함

 

27. 인덱스 제거

show index from member; 인덱스 확인

drop index idx_member_mem_name on member; 인덱스 제거

클러스터형 인덱스부터 제거하면 데이터 재구성으로 인해 시간이 길어짐

보조인덱스부터 제거 권장

 

alter table member drop primary key;

클러스터형 인덱스 제거

이 때 이 기본키를 참조하는 외래키가 없어야 한다.

 

자신을 참조하는 테이블들을 확인한 후 FK, PK순으로 제거해야한다.

select table_name, constraint_name

from infromation_schema.referential_constrtraints

where constraint_schema='market_db'

and referenced_table_name='member';

market_db의 member테이블을 참조하는 다른 테이블들 조회

 

만약 buy테이블이 외래키로 참조하고 있다면

alter table buy drop foreign key buy_ibfk_;

alter table member drop primary key;

이런식으로 하면 클러스터형 인덱스 제거 가능

 

프라미머리키가 제가된 상태에서는 그냥 id순으로 정렬된다

ALTER TABLE members MODIFY COLUMN mem_id INT NOT NULL UNIQUE;

프라이머리키가 없는 상태에서 mem_id에 unique not null을 설정함.

이런 경우라도 어떤 순으로 정렬된다는 보장이 사라진다. 클러스터형 인덱스가 없기 때문이다.