23년 1월 13일 sql

sql의 인덱스에 대해서 학습했다.

각 테이블에는 인덱스를 붙일 수 있는데, 인덱스를 붙이면 균형 트리(balanced tree) 노드가 생성되어 빠른 탐색이 가능하다. 인덱스는 각 열에 붙일 수 있다.

하지만 인덱스를 추가하면 검색 속도가 빨라지는 대신 , 인덱스를 붙인만큼 공간을 더 먹고, 데이터 변경 작업(INSERT, UPDATE, DELETE)이 자주 일어나면 성능이 더 떨어질 수 있다.

 

인덱스에는 크게 두가지 종류가 있다.

1.자동으로 생성되는 인덱스

 

1-1.클러스터형 인덱스

어떤 열을 기본 키(Primary key)로 설정하면 자동으로 클러스터형 인덱스가 생성된다.

이 인덱스는 데이터가 정렬 되어 있다.

 

1-2.보조 인덱스

어떤 열을 고유키(Unique)로 설정하면 자동으로 보조 인덱스가 생성된다.

보조 인덱스는 데이터 정렬은 하지 않는다. 책의 색인과 비슷하다고 볼 수 있다.

 

2.사용자가 직접 생성하는 인덱스

사용자가 직접 생성하는 인덱스도 보조 인덱스에 속한다.

 

1.인덱스 생성

인덱스 생성 쿼리문은 다음과 같다.

CREATE [UNIQUE] INDEX 인덱스_이름
ON 테이블_이름 (열_이름) [ASC | DESC];

[]는 선택이다.

예를 들면 다음과 같이 쓸 수 있다.

create index idx_member_addr on member (addr);
create unique index idx_member_mem_name on member (mem_name);

그리고 다음의 쿼리를 써서 값을 불러오면

select * from member;
select mem_id, mem_name, addr from member;
select mem_id, mem_name, addr from member where mem_name = 'name';

인덱스를 사용해 값을 잘 가져온것을 알 수 있다.

 

2.인덱스 확인

다음과 같은 쿼리를 사용해 테이블에 어떤 인덱스가 있는지 알 수 있다.

SHOW INDEX FROM 테이블_이름;

 

3.인덱스 적용

ANALYZE TABLE 테이블_이름;

 

4.인덱스 삭제

DROP INDEX 인덱스_이름 ON 테이블_이름;

인덱스 삭제는 자동으로 생성된 pk나 unique를 삭제해주지 않는다.

어제 추가로 공부하면서 배웠던

ALTER TABLE … DROP CONSTRAINT …를 사용해서 삭제해주면 자동으로 인덱스가 삭제된다.

 

 

인덱스를 효과적으로 사용하려면

1.WHERE 절에서 사용되는 열에 인덱스를 만든다.

2.select문이 자주 사용되는 열에 인덱스를 만든다.

3.데이터 변경작업이 많은 곳에는 인덱스를 만들지 않는다.

4.데이터의 중복이 높은열에 인덱스를 만들지 않는다.

5.사용하지 않는 인덱스는 제거한다.

 

이전에 sql을 배우면서 배우지 못했던 인덱스란 것을 배웠는데, 사실 어디에 쓰이는지 감이 잡히지 않는다. sql을 따로 다룰일이 있으면 모를까, 스프링으로 프로젝트를 진행하면 쓸일이 없을것 같은데, 아직 스프링도 잘 아는것이 아니라서 일단은 외웠다.

sql에 대해서 대략적으로 배우니 이전에 팀프로젝트로 만든 프로젝트가 돌아갈때 나오는 쿼리문들이 어느정도 이해가 간다.

잘은 모르겠지만 스프링으로 웹개발을 진행할때, sql문을 직접 써서 최적화를 해야하는 경우가 있다는데, 그런 작업을 할때 조금이나마 이해를 하고 시작 할 수 있을것 같다.