혼자 공부하는 SQL / Chapter 06 인덱스

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

• 인덱스(Index)
  • 데이터 조회가 빨라지는 도구 (SELECT 응답 속도)
    • 예로 책 맨 뒤에 있는 찾아보기 같은 도구! 키워드나 단어들이 가나다 순으로 정렬되어 있어서 페이지를 금방 찾을 수 있는 것 처럼!
  • 데이터가 많아지면 인덱스를 사용해서 성능을 좋게 할 수 있다. 하지만 인덱스도 공간 필요!
    • 인덱스가 있다고 무조건 좋은 건 아님.. 필요 없는 인덱스를 만들거나 데이터 변경이 자주 되면 오히려 느려질 수도
• 인덱스 종류
  • 클러스터형 인덱스
    • 기본 키로 지정하면 자동 생성, 테이블에 1개만 만들 수 있다.
    • 기본 키로 지정한 열을 기준으로 자동 정렬
    • 예로 사전과 같음
  • 보조 인덱스
    • 고유 키로 지정하면 자동 생성, 테이블에 여러개 만들 수 있다.
    • 자동 정렬은 안된다.
    • 예로 책 뒤 찾아보기 같음
• 인덱스 생성
  • 인덱스는 테이블의 열 단위의 생성, 하나의 열에는 하나의 인덱스 생성
  • 클러스터형 인덱스 생성
    • CREATE 문에서 열 정의할 때 PRIMARY KEY 입력! 이렇게 기본키로 지정하면 자동으로 해당 열에 클러스터형 인덱스가 생성된다. (테이블에 한 개씩만)
  • 고유 인덱스 생성
    • 열 정의시 UNIQUE로 지정! 이렇게 고유 키로 지정하면 자동으로 보조 인덱스가 생성된다. (테이블당 여러 개 가능)
• 인덱스 정보 확인
  • SHOW INDEX from 테이블이름; 으로 인덱스 정보 확인
    • KEY_NAME에 PRIMARY라고 적혀있으면 클러스터형 인덱스!
    • KEY_NAME에 해당 열이름이 적혀있으면 보조 인덱스!

 

06-2 인덱스의 내부 작동

• 균형 트리
  • 클러스터형 인덱스와 보조 인덱스는 내부적으로 균형 트리로 만들어진다.
  • 여기서 균형 트리는 데이터의 구조를 말한며 나무가 거꾸로 되어있는 구조이;다.
  • 상단은 루트, 중간은 줄기, 끝은 리프라고 부른다. 모든 출발은 루트에서 시작!
  • 데이터가 저장되는 공간을 노드라고 한다. (루트 노드, 중간 노드, 리프 노드)
    • MySQL은 노드를 페이지라고 부른다! 최소 저장 단위이며 16Kbyte이다.
• 페이지 분할
  • 인덱스 구성하면 데이터 변경할 때 성능이 나빠질 수 있다고 하였다. 그 이유는 바로 페이지 분할 때문..
  • 페이지 분할은 새로운 페이지를 준비해서 데이터를 나누는 작업을 말한다.
  • 페이지 분할이 일어나면 MySQL이 느려지고 자주 일어나면 성능에 영향!

 

06-3 인덱스의 실제 사용

• 인덱스 SQL 문
  • 인덱스 생성
    • 인덱스 자동 생성 외에 직접 인덱스를 생성하고 싶다면!
    • CREATE [UNIQUE] INDEX 인덱스_이름 ON 테이블_이름 (열_이름) [ASC나 DESC]
      • UNIQUE는 중복이 안되는 고유 인덱스를 만드는 것, 생략하면 중복 허용
  • 인덱스 제거
    • 자동 생성된 인덱스는 아래 SQL문으로 제거 못하고 직접 만든 인덱스만 제거할 수 있다. (자동 생성 인덱스는 ALTER TABLE로 제거)
    • DROP INDEX 인덱스_이름 ON 테이블_이름
  • 인덱스 크기 확인
    • SHOW TABLE STATUS LIKE ‘테이블_이름’;
  • 인덱스 적용
    • 생성한 인덱스를 실제로 적용시키려면 먼저 테이블을 분석/처리 해줘야 한다.
    • ANALYZE TABLE 테이블_이름;
• 인덱스 효과적으로 사용하는 방법
  • 인덱스는 열 단위에 생성, 하나의 열에 하나의 인덱스를 만드는 것이 일반적
  • WHERE 절에 사용되는 열에 인덱스 만들기 (조건에 해당 열이 나와야 인덱스 사용!)
  • 인덱스도 자주 사용해야 가치가 있다.
  • 데이터의 중복이 높은 열은 인덱스를 만들어도 별 효과가 없다.
  • 클러스터형 인덱스는 테이블당 하나만 생성 가능 (기본 키)
  • 사용하지 않는 인덱스는 제거하기

---

미션

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

• 선택 미션
  • 인덱스 생성, 제거하는 기본 형식 작성하기
    • 인덱스 생성 문
      • CREATE [UNIQUE] INDEX 인덱스_이름 ON 테이블_이름 (열_이름) [ASC나 DESC]
    • 인덱스 제거 문
      • DROP INDEX 인덱스_이름 ON 테이블_이름