MySQL 인덱스

 

인덱스 

  데이터베이스 분야에 있어서 테이블에 대한 동작의 속도를 높여주는 자료 구조

  책의 <찾아보기> 와 같은 개념 

 

 

장점

•  검색 속도가 무척 빨라질 수 있다.
•  그 결가 해당 쿼리의 부하를 줄어들어서, 결국 시스템 전체의 성능이 향상된다.

단점

• 인덱스가 데이터베이스 공간을 차지해서 추가적인 공간이 필요해지는데, 대략 데이터베이스 크기의 10% 정도의 추가 공간이 필요하다.
• 처음 인덱스를 생성하는데 시간이 많이 소요될 수 있다.
• 데이터 변경 작업이 자주 일어날 경우에는 오히려 성능이 많이 나빠질 수도 있다.

 

인덱스의 종류 

 

클러스터형 인덱스

• 영어 사전과 같은 책 
• 테이블당 한 개만 생성할 수 있다.
• 행 데이터를 지정한 열에 맞춰서 자동 정렬

 

보조 인덱스 

• 책 뒤에 <찾아보기>가 있는 일반 책 
• 테이블당 여러 개를 생성할 수 있다.

 

a 열 에 key_name이 PRIMARY 로 된 클러스터형 인덱스를 의미한다. 

Non_unique 열이 0이면 Unique 인덱스, 1이면 Nonunique 인덱스를 의미

Key_name 은 index_name 과 같은 의미로 인덱스 이름이다.

PRIMARY 로 표기되면 클러스터형 인덱스로 보면 된다. 보조 인덱스는 Key_name 부분에 열의 이름 또는 키 이름으로 표기된다.

Seq_in_index 는 해당 열에 여러개의 인덱스가 설정되었을 때의 순서를 나타낸다.

Null 은 NULL 값의 허용 여부인데 비어 있으면 NO의 의미한다. 

Cardinality 는 중복되지 않는 데이터 개수가 들어 있다.

 

 

B-Tree 

 

노드 - 트리 구조에서 데이터가 존재하는 공간

루트노드 - 노드의 가장 상위 노드 

리프노드 - 제일 마지막에 존재하는 노드 

 

이 노드에 해당되는 것이 페이지이다. 페이지란 16Kbyte 크기의 최소한의 저장 단위.

 

페이지 분할

INSERT 작업이 일어날 때 성능이 급격히 느려질 수 있다. 그 이유는 페이지 분할이라는 작업이 발생되기 때문이다.

 

 

클러스터형 인덱스 

• 생성 시 데이터 페이지 전체가 다시 정렬된다.
• 인덱스 자체의 리프 페이지가 곧 데이터
• 보조 인덱스보다 검색 속도는 빠르지만 데이터의 입력/수정/삭제는 더 느리다.
• 성능이 좋지만 테이블에 한 개만 생성할 수 있다. 

보조 인덱스 

• 데이터 페이지는 그냥 둔 상태에서 별도의 페이지에 인덱스를 구성한다.
• 리프 페이지는 데이터가 아니라 데이터가 위치하는 주소 값이다.  클러스터형보다 검색 속도는 더 느리지만
  데이터의 입력/수정/삭제는 덜 느리다.
• 여러 개 생성 할 수 있다.
• 전체 데이터의 대량 20% 이상을 스캔하는 경우에는 MYSQL 이 인덱스를 사용하지 않고 테이블 검색을 실시한다.

 

 

인덱스를 생성하는 경우와 그렇지 않는 경우

 

1. 인덱스는 열 단위에 생성된다.
2. WHERE 절에서는 사용되는 열에 인덱스를 만들어야 한다.
3. WHERE 절에 사용되더라도 자주 사용해야 가치가 있다.
4. 데이터 중복도가 높은 열은 인덱스를 만들어도 별 효과가 없다.
5. 외래 키를 지정한 열에는 자동으로 외래 키 인덱스가 생성된다.
6. JOIN에 자주 사용되는 열에는 인덱스를 생성해 주는 것이 좋다.
7. INSERT/UPDATE/DELETE가 얼마나 자주 일어나는지를 고려해야 한다.
8. 클러스터형 인덱스는 테이블당 하나만 생성할 수 있다.
9. 클러스터형 인덱스가 테이블에 아예 없는 것이 좋은 경우도 있다.
10. 사용하지 않는 인덱스는 제거하자. - ANALYZE TABLE 구문으로 인덱스의 재구성을 통해서 조각화를 최소화해야만 시스템의 성능을 최상으로 유지시킬 수 있을 것이다.

 

Reference 

  이것이 mysql 이다.