[혼공S] 5주차 - 인덱스

Chapter 06. 인덱스

미션

기본 미션: p.310 인덱스 생성하고 key_name이 PRIMARY로 출력된 결과 화면 캡처하기.

key_name이 PRIMARY인 인덱스는 기본 키를 설정하면 자동으로 생성되는 클러스터형 인덱스이다.

(내가 딱히 할게 없이 show만 하면 되는듯?)

선택 미션: 인덱스 생성, 제거하는 기본 형식 작성하기

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

-- 인덱스 제거하기
DROP INDEX 인덱스_이름 ON 테이블_이름;

인덱스

• 데이터를 빠르게 찾을 수 있도록 도와주는 도구
• 남용하면 부작용 생김

인덱스의 장단점

장점

• 기존보다 빠른 응답 속도를 얻을 수 있음
• 전체 시스템의 성능 향상단점
• 데이터베이스 안에 추가적인 공간 필요
• 처음에 인덱스를 만들 때 시간이 오래 걸릴 수 있음
• SELECT가 아닌 INSERT, UPDATE, DELETE가 자주 일어나면 오히려 성능 저하로 이어짐

인덱스의 종류

클러스터형 인덱스

• 영어사전, 국어사전
• 이미 책의 내용이 알파벳 순서대로 정렬되어 있음. 별도의 찾아보기가 없음
• 자동으로 정렬됨. 새로 추가되는 데이터도 알아서 정렬됨

보조 인덱스

• 책 뒤의 찾아보기
• 찾아보기에서 찾고 싶은 단어를 찾고, 옆에 표시된 페이지를 펼치면 실제 찾는 내용이 있음
• 자동으로 정렬되지 않음. 새로 추가되는 데이터는 그냥 제일 뒤에 추가됨
• 여러 개 만들 수 있지만 데이터베이스의 공간을 차지하니 꼭 필요한 열에만 생성하는 것이 좋다

자동으로 생성되는 인덱스

• 기본키로 지정하면 자동으로 해당 열에 클러스터형 인덱스가 생성됨
• 기본키는 테이블당 한 개 = 클러스터형 인덱스는 테이블에 한 개만 만들 수 있다.(고유 인덱스, 인덱스의 값이 중복되지 않는다)
• 고유키도 자동으로 보조 인덱스가 생성됨
• 고유키는 여러개 가능 = 보조 인덱스는 여러개 만들 수 있다.(고유 인덱스)

인덱스의 내부 작동

균형 트리

• 데이터가 저장되는 공간: 노드, MySQL에서는 페이지라고 부름. 최소한의 저장 단위, 16Kbyte(16384byte)의 크기
• 하나의 데이터만 작성해도 하나의 페이지를 사용함(노트의 페이지처럼). 데이터를 검색할 때 몇 개의 페이지를 읽었느냐로 효율성 판단
• 루트 노드: 가장 상위 노트, 리프 노드: 제일 마지막에 존재하는 노드, 중간 노드: 루트와 리프 노드 중간의 노드들
• 균형 트리는 루트 페이지부터 검색을 진행해서 효율이 좋다

페이지 분할

• 새로운 페이지를 준비해서 데이터를 나누는 작업
• 특히 INSERT에서 발생됨, 인덱스가 데이터 변경 작업 시 성능이 나빠지는 이유.
• 데이터가 새로 입력되어야 할 페이지에 빈 공간이 없으면 페이지 분할 작업이 일어남

인덱스의 구조

클러스터형 인덱스

• 데이터 페이지가 정렬되고 균형 트리 형태의 인덱스가 생성된다
• 클러스터형 인덱스의 검색이 약간 더 빠르다

보조 인덱스

• 데이터는 그대로 두고 따로 인덱스 페이지가 생성된다
• 각 데이터의 위치는 페이지번호 + #위치로 기록되어 있다

인덱스 생성, 제거, 사용

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

-- 인덱스 제거하기
DROP INDEX 인덱스_이름 ON 테이블_이름;

-- 인덱스 사용하기
ANALYZE TABLE member;    -- 인덱스 적용
SELECT mem_id, mem_name, addr
    FROM member
    WHERE mem_name = '에이핑크';

• 기본 키, 고유 키로 자동 생성된 인덱스는 DROP INDEX로 제거하지 못한다
• WHERE절에 열 이름이 들어있어야 인덱스를 사용한다
• 그러나 WHERE문에서 열에 연산이 가해지면 인덱스를 사용하지 않는다
  • WHERE mem_number *2 >= 14 -> 인덱스 사용하지 않음
  • WHERE mem_number >= 14/2 -> 인덱스 사용
• MySQL은 알아서 판단해서 인덱스를 사용하거나 전체 테이블 검색을 한다
• 클러스터형 인덱스와 보조 인덱스가 섞여 있을 때는 보조 인덱스를 먼저 제거하는 것이 좋다. 클러스터형 인덱스를 먼저 제거하면 데이터를 재구성해서 시간이 더 오래 걸린다.

인덱스를 효과적으로 사용하는 방법

• WHERE 절에서 사용되는 열에 인덱스를 만들어야 한다
• WHERE 절에서 자주 사용해야 가치가 있다
• 데이터의 중복이 높은 열은 인덱스를 만들어도 별 효과가 없다
• 클러스터형 인덱스는 테이블당 하나만 생성할 수 있다
• 사용하지 않는 인덱스는 제거하자