[혼자 공부하는 SQL] 6장

Chapter6 인덱스

6-1 인덱스 개념을 파악하자

 

인덱스: 데이터를 빠르게 찾을 수 있도록 도와주는 도구

• 클러스터형 인덱스(Clustered Index): 기본 키로 지정 시 자동 생성, 테이블에 1개 자동 정렬
• 보조 인덱스(Secondary Index): 고유 키로 지정 시 자동 생성, 여러 개 가능 but 자동 정렬 x

인덱스의 장단점

• 장점: SELECT문으로 검색하는 속도가 빨라져 컴퓨터의 부담이 줄어들며 전체 시스템의 성능이 향상된다
• 단점: 인덱스도 공간을 차지해서 데이터베이스 내부 추가 공간이 필요하다

인덱스 정보 확인: SHOW INDEX FROM table;

 

확인문제

1. 인덱스는 SELECT문을 빠르게 처리합니다
2. 인덱스는 추가적인 공간이 필요합니다
3. 보조 인덱스는 고유 키를 설정하면 자동 생성됩니다
4. 고유 인덱스는 중복 값 허용 x
5. 테이블당 1개

6-2 인덱스의 내부 작동

 

클러스터형 인덱스와 보조 인덱스는 모두 내부적으로 균형 트리로 만들어진다

• 균형트리: 자료 구조에 나오는 범용적으로 사용되는 데이터의 구조, 나무를 거꾸로 표현
  • 노드: 균형 트리 구조에서 데이터가 저장되는 공간
    
    • • • • • • •  
    • 루트 노드: 가장 상위 노드
    • 중간 노드: 루트와 리프 사이의 노드
    • 리프 노드: 마지막 노드
  • MySQL에서는 페이지라고 부른다

페이지 분할: 데이터를 입력할 때, 입력할 페이지에 공간이 없어서 새로운 페이지로 데이터를 나누는 작업

 

확인문제

1.  4번
   • 루트 노드: 가장 상위 노드
   • 중간 노드: 루트와 리프 사이의 노드
   • 리프 노드: 마지막 노드
   • 페이지: 16KB 크기의 최소한의 저장 단위
2. 인덱스를 구성하게 되면 데이터의 변경 작업(INSERT, UPDATE, DELETE)시에 성능이 나빠지는 단점이 있습니다. 특히 INSERT 작업이 일어날 때 더 느리게 입력될 수 있는데, 이유는 페이지 분할이라는 작업이 발생되기 때문입니다. 페이지 분할 작업이 일어나면 MySQL이 느려지고 너무 자주 일어나면 성능에 큰 영향을 줍니다.
3. 클러스터형 인덱스를 생성하면 데이터는 오름차순으로 정렬, 보조 인덱스는 자동 정렬 X

 

6-3 인덱스의 실제 사용

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

UNIQUE는 중복이 안 되는 고유 인덱스를 만드는 것인데, 생략하면 중복이 허용된다. CREATE UNIQUE로 인덱스를 생성하려면 기존에 입력된 값들에 중복이 있으면 안 된다. 그리고 인덱스를 생성한 후에 입력되는 데이터와도 중복될 수 없으니 신중해야 한다.

 

ASC 또는 DESC는 인덱스를 오름차순 또는 내림차순으로 만들어준다. 기본은 ASC로 만들어지며, 일반적으로 DESC로 만드는 경우는 거의 없다.

# 인덱스 제거
DROP INDEX 인덱스_이름 ON 테이블_이름

기본키, 고유 키로 자동 생성된 인덱스는 DROP INDEX로 제거 X 

ALTER TABLE문으로 제거 가능

 

단순 보조 인덱스

보조 인덱스는 단순 보조 인덱스와 고유 보조 인덱스로 나뉜다. 단순 보조 인덱스는 중복을 허용(Non_unique = 1)하며 고유 보조 인덱스와 반대이다. 

 

ANALYZE TABLE문

 

 

 

 

인덱스를 사용했는지 여부는 결과 중 [Execution Plan] 창을 확인하면 됩니다

 

 

 

 

 

 

인덱스 제거

• DROP INDEX 문을 사용하면 됨
• 기본 키, 고유 키로 자동 생성된 인덱스는 DROP INDEX로 제거하지 못한다. 이 경우 ALTER TABLE 문으로 기본 키나 고유 키를 제거하면 자동으로 생성된 인덱스도 제거할 수 있다.

확인문제

1. 2번
2. • 인덱스를 생성하는 SQL - CREATE INDEX
   • 인덱스를 제거하는 SQL - DROP INDEX
   • 테이블에 생성된 인덱스 이름과 열을 확인하는 SQL - SHOW INDEX
   • 인덱스의 할당된 크기를 확인하는 SQL - SHOW TABLE STATUS
3. 클러스터형 인덱스는 1개, 보조 인덱스는 여러 개 만들 수 있다
4. • Full Table Scan은 인덱스를 사용하지 않았다는 의미
   • 인덱스를 사용하면 Single Row, Index Range Scan은 다양한 형태로 표시된다
   • 인덱스가 있어도 Full Table Scan을 할 수 있다
5. 
   
   • 인덱스는 열 단위에 생성된다
   • 중복도가 높으면 인덱스의 효과가 없다