Chap06 인덱스

Chapter 06 인덱스

---

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

• 인덱스는 데이터를 빠르게 찾을 수 있도록 도와주는 도구이다.
  • 클러스터형 인덱스
    • 기본 키를 지정하면 자동 생성되며 테이블에 1개만 만들 수 있다.
    • 기본 키로 지정한 열을 기준으로 자동 정렬된다.
  • 보조 인덱스
    • 고유 키로 지정하면 자동 생성되며 여러개를 만들 수 있다.
    • 자동 정렬되진 않는다.

• 인덱스의 개념
  • 책의 찾아보기와 같은 개념으로, 데이터의 양이 많아지면 필수적이다.
• 인덱스의 문제점
  • 굳이 인덱스를 만들 필요 없이 전체 탐색을 통해 찾는게 더 효율적인 경우도 있을 수 있다.
  • 즉 필요 없는 인덱스를 만들어 데이터베이스가 차지하는 공간이 더 늘어나고, 인덱스로 찾는게 더 느려진다.

• 인덱스의 장,단점
  • 장점
    • 기존보다 아주 빠른 응답 속도
    • 결과적으로 전체 시스템의 성능 향상
    • SELECT 검색 속도 증가
  • 단점
    • 인덱스가 공간을 차지 해 데이터베이스 안 추가적인 공간이 필요
    • 인덱스를 초기에 만드는데 시간이 오래 걸림
    • SELECT가 아닌 데이터의 변경 (DELETE, INSERT, UPDETE) 가 자주 일어나면 오히려 성능이 나빠질 수 있음

• 인덱스의 종류

  • 클러스터형 인덱스

  • 보조 인덱스

  • 클러스터형 인덱스는 영어사전과 같고, 보조 인덱스는 책의 뒤 찾아보기와 같다. 즉, 클러스터형은 책 내용 자체가 이미 알파벳 순으로 정렬이 되어 있는 것이다.

• 기본키로 정의한 열에 대해 클러스터형 인덱스가 자동으로 생성된다.

  • 기본 키는 테이블 당 하나만 존재 가능하기 때문에, 클러스터형 인덱스는 테이블에 1개만 남ㄴ들 수 있다.
  • Non_Unique 값이 0 이면 false, 1이면 true 인데, 클러스터형 인덱스의 Non_unique 값은 0 이다. 즉, 중복이 허용되지 않는다.

• 고유 키로 생성되는 인덱스도 살펴보자. 우선 UNIQUE로 고유 키를 지정해주자.

  CREATE TABLE table2  (
    col1  INT  PRIMARY KEY,
    col2  INT  UNIQUE,
    col3  INT  UNIQUE
  );
  SHOW INDEX FROM table2;

• 확인해보면, Non_Unique가 모두 0이고, key_name에 우리가 고유 키로 설정해준 col의 번호가 적혀있는 것을 확인할 수 있다.

• 자동으로 정렬되는 클러스터형 인덱스

  • 클러스터형 인덱스의 특징
    • 기본 키 열을 기준으로 자동 정렬됨.
    • 비교를 위해 그냥 테이블을 만들어 결과를 출력해보면 데이터를 입력한 순서대로 나옴.
    • 이제 그 테이블을 ALTER 을 통해 PRIMARY KEY를 지정해주면 영어 단어 순으로 정렬되어 나온다.
    • 이젠 데이터를 추가해도 알아서 기본 키 기준으로 정렬되어 나온다.

• 정렬되지 않는 보조 인덱스
  • 보조 인덱스의 특징
    • 고유 키로 지정하면 보조 인덱스가 생성된다. 또, 여러 개의 보조 인덱스를 설정할 수 있다. 보조 인덱스는 책 뒤의 찾아보기와 유사하게 생각하면 되는데, 찾아보기 가 있다고 해서 책의 순서가 정렬이 되는 것은 아니다. (본문의 순서나 내용이 바뀌지는 않는다.)
    • UNIQUE 를 통해 생성한 테이블에 고유 키를 지정해 주어도 출력순서가 바뀌지 않음을 알 수 있다.
    • 보조 인덱스를 만들 때마다 데이터베이스의 공간을 차지하기 때문에 전반적으로 시스템에 나쁜 영향을 미칠 수 있다.
    • 적절한 보조 인덱스를 설정하는 것이 중요.

---

06 - 2 인덱스의 내부 작동

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

• 균형 트리

  • 균형 트리에서 데이터가 저장되는 공간을 노드 라고 함
  • MySQL 에선 노드를 페이지 라고 부른다.
  • 균형트리는 SELECT 에서 뛰어난 성능을 보인다.
  • 루트 페이지 부터 검색을 시작해 리프 페이지에서 데이터를 찾는다.

• 균형 트리의 페이지 분할

  • SELECT 시에는 더 빠르지만, 데이터 변경 (INSERT, UPDATE, DELETE) 작업 시 성능이 나빠진다.
  • 이미 리프 페이지가 다 차있는 상황에서 새로운 데이터를 입력하면, 리프 페이지 중간에 새로 데이터가 삽입 되어야 하므로 새로운 리프 페이지를 하나 생성하고, 기존의 리프 페이지 뒷부분을 새로 생성한 페이지로 옮겨야 하므로 작업이 많이 발생한다.

• 인덱스의 구조
  • 클러스터형 인덱스 구성
    • Primary key 지정 시 자동으로 클러스터형 인덱스로 구성이 되고, 이는 영어사전 같은 개념으로 데이터가 자동으로 정렬되게 된다.
  • 보조 인덱스 구성
    • 반면 보조 인덱스의 경우 unique 지정 시 설정되는 것으로, 책의 찾아보기와 같은 개념이라 하였다.
    • 사전순으로 정렬되지 않기 때문에 데이터 페이지는 입력 순으로 되어있고, 책 뒤에 별도의 찾아보기 부분이 있는것 처럼 새로 보조 인덱스가 생성이 된다. 이 보조 인덱스에는 페이지 번호 + 위치 가 저장되어 있다.

---

06 - 3 인덱스의 실제 사용

• 인덱스를 생성할 땐 CREATE INDEX
• 인덱스를 제거할 땐 DROP INDEX

• CREATE INDEX 인덱스 생성 문법

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

• UNIQUE 는 중복이 안되는 고유 인덱스를 만드는 것으로 ,생략하면 중복이 허용된다.

• ASC, DESC 는 인덱스를 오름차순, 내림차순으로 만들어 진다.

• DROP INDEX 인덱스 제거 문법

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

• 기본 키, 고유 키로 자동 생성된 인덱스는 DROP INDEX로 제거하지 못한다.
• ALTER TABLE 로 기본키, 고유키를 제거 한 후에는 생성되었던 인덱스를 제거할 수있다.

인덱스 생성 예시 코드.

USE market_db;
SELECT * FROM member;

SHOW INDEX FROM member;

SHOW TABLE STATUS LIKE 'member';

CREATE INDEX idx_member_addr 
   ON member (addr);

SHOW INDEX FROM member;

SHOW TABLE STATUS LIKE 'member';

ANALYZE TABLE member;
SHOW TABLE STATUS LIKE 'member';

CREATE UNIQUE INDEX idx_member_mem_number
    ON member (mem_number); -- 오류 발생

CREATE UNIQUE INDEX idx_member_mem_name
    ON member (mem_name);

SHOW INDEX FROM member;

INSERT INTO member VALUES('MOO', '마마무', 2, '태국', '001', '12341234', 155, '2020.10.10');

ANALYZE TABLE member;  -- 지금까지 만든 인덱스를 모두 적용
SHOW INDEX FROM member;

• 최소 단위가 1페이지 이기 때문에 16kb 가 할당 되어 있다.

• SHOW TABL STATUS 는 테이블에 생성된 인덱스 크기를 확인할 수 있다.

• 인덱스를 생성한다고 바로 적용되는것이 아니기 때문에,

  • ANALYZE TABLE 문을 사용해 테이블을 분석/처리 해주어야 한다.
    

• 인덱스를 숫자의 범위로 사용할 수도 있다.

  SELECT mem_name, mem_number 
    FROM member 
    WHERE mem_number >= 7; 
  

SELECT mem_name, mem_number
FROM member
WHERE mem_number >= 1;

SELECT mem_name, mem_number
FROM member
WHERE mem_number*2 >= 14;

SELECT mem_name, mem_number
FROM member
WHERE mem_number >= 14/2;

```

• 인덱스를 만들었음에도 인덱스를 사용해 검색을 하는 경우가 있고 아닌 경우가 존재한다.

• MySQL이 자체적으로 인덱스 검사와 전체 테이블 검사 중 더 나을 것 같다고 생각하는 것으로 조사한다. 위처럼 1명 이상인 경우의 경우는 대부분의 행이 해당되기 때문에 MySQL이 전체 테이블 조사로 검색을 한다.

• WHERE 문에 연산이 들어가게 되면 인덱스를 사용하지 않는다.

• 연산을 사용하지 않기 위해 14/2 로 하니, 전체 테이블 검색을 하지 않고 인덱스 사용을 했음을 확인 할 수 있다.

• 인덱스 제거

  • 클러스터형 인덱스와 보조 인덱스가 섞여 있을 땐 보조 인덱스를 먼저 제거하는 것이 좋다.
  • 또, 외래 키 관계를 먼저 제거한 후 기본 키를 제거해야 한다.