[DB] 인덱스(index) / MySql에서 사용법

인덱스(index)

데이터베이스에서 인덱스는 테이블의 검색 속도를 향상시키기 위한 자료구조이다.

 

책의 맨처음에 나오는 목차처럼 해당 컬럼이 어디에 있는지 저장하여, 해당부분만 검색할 수 있게 하여 검색속도를 향상시킨다.

 

장점

• select절 성능을 향상(무조건은 아님)
• 그 결과 쿼리의 부하가 줄어서, 시스템 전체 성능 향상으로 이어짐

 

단점

• insert,update,delete절 성능 하락
  • 레코드(로우)가 추가,삭제,수정되면 생성된 인덱스도 동기화해주기때문
• 추가적인 데이터베이스 공간 필요(약 10%정도)
• 처음 인덱스를 생성하는데 시간이 많이 소요

 

 

인덱스의 종류

클러스터형 인덱스(Clustered Index)

• '영어 사전'과 같은 느낌
• 책의 내용 자체가 순서대로 정렬되어있어서 인덱스 자체가 책의 내용과 같은 것을 뜻함
• 테이블당 한 개의 클러스터형 인덱스 생성 가능
• 행 데이터를 인덱스로 지정한 열에 맞춰 자동 오름차순 정렬해줌

보조 인덱스(Secondary Index)

• 책 뒤에 '찾아보기'가 있는 일반책
• 책과 같이 '찾아보기'가 별도로 있고, '찾아보기'로 찾은 후에 표시된 페이지로 가야 실제 찾는 내용이 있음
• 테이블 당 여러개 보조 인덱스 생성 가능
• MySQL이 아닌 다른 DBMS에선 비클러스터형 인덱스(Nonclustered Index)라고도 부름

 

 

자동으로 생성되는 인덱스

인덱스는 테이블의 열(컬럼) 단위에 생성

하나의 열에 인덱스를 생성할수도, 여러 열에 하나의 인덱스를 생성할 수도 있음.

 

 

1) 하나의 열에 인덱스 생성

이러한 테이블(dept)에 열 하나당 인덱스 생성시 총 3개의 인덱스 생성 가능

테이블엔 하나의 PRIMARY KEY를 가질 수 있는데, Primary key로 지정한 컬럼은 자동으로 클러스터형 인덱스가 생성된다.

 

SHOW INDEX FROM dept;

 

위의 명령어로 인덱스 상태 확인

column_name: deptno에 인덱스 생성된것 확인 가능.

Key_name: PRIMARY로 된 것은 클러스터형 인덱스를 의미. 보조 인덱스의 경우 컬럼의 이름 또는 키 이름으로 표기.

 

만약 테이블에 UNIQUE 제약 조건을 걸어두면, 해당 컬럼은 보조 인덱스가 자동으로 생성된다.

 

UNIQUE로 제약조건에 NOT NULL을 걸면 클러스터형 인덱스가 된다.

만약 PRIMARY랑 UNIQUE NOT NULL이 같이 있을경우, UNIQUE는 보조 인덱스로 생성된다.

즉, PRIMARY가 우선임.

 

그리고 클러스터형 인덱스로 지정된 열은 자동으로 오름차순 정렬된다.

만약, PRIMARY KEY를 변경한 경우, 자동으로 그 열에 맞게 인덱스가 정렬됨. 함부로 변경 시에 MySQL에서 많은 작업이 일어남.

 

인덱스의 내부 구조

B-Tree(Balanced Tree)

• 균형 잡힌 트리 데이터 구조
• 노드는 데이터가 존재하는 공간
• 루트노드는 가장 상단, 리프노드는 제일 마지막 존재하는 노드.
• MySQL에선 노드에 해당되는 것이 페이지
• 페이지는 16Kbyte 크기의 최소한의 저장 단위.
  • 작은 데이터를 넣더라도 한개 페이지를 차지
• 루트노드에서 확인 후, 그에 알맞은 노드들로 이동하며, 최종적으로 원하는 데이터를 찾게 됨.
• 전체 검색에 비해 효율적인 검색이 가능해짐
• 책에서는 B-Tree라고 되어있지만, 리프노드에 데이터가 들어가는 것과 MySQL은 InnoDB를 쓰는데 InnoDB는 B+Tree로 이뤄져있으므로 B+Tree라고 보는게 맞는 것 같다..
• B-Tree 와 B+Tree참조 블로그

 

 

B-Tree 구조에서 데이터 삽입 시 벌어지는 일

1) III 삽입

리프노드에 공간이 있어서 들어감

 

2) GGG 삽입

리프노드 공간 부족으로 새 페이지에 데이터 분할.

루트 노드에도 해당 페이지 등록.

 

3) PPP, QQQ삽입

리프노드도 분할되고, 루트노드도 공간이 다 참으로서, 나눠지면서 새로운 루트 노드 생성.

 

위 과정들로 알 수 있는건, 데이터의 변경이 발생할 시 많은 작업들이 일어남.(INSERT,UPDATE,DELETE)

 

클러스터형 인덱스와 보조 인덱스 구조

클러스터형 인덱스

보조 인덱스

클러스터형 인덱스는 인덱스 자체의 리프 페이지가 곧 데이터.

보조 인덱스는 데이터 페이지는 그냥 두고, 별도의 인덱스 페이지를 구성.

 

그로인해, 클러스터형은 보조 인덱스보다 검색 속도가 더 빠르지만, 데이터 입력/수정/삭제는 더 느림.

보조 인덱스는 클러스터형 인덱스보다 데이터 입력/삭제/수정은 빠르지만, 검색 속도는 더 느림.

 

 

혼합형(클러스터형 + 보조)

혼합형의 경우 클러스터형은 변화 없고,

보조형은 기존엔 리프 페이지가 '데이터 페이지의 주소값'을 가졌었는데, 현재는 클러스터형 인덱스의 키 값을 가지게 된다.

 

예를 들어 '임재범'이란 Name을 가진 사람의 주소를 알고 싶다면 아래와 같이 실행된다.

SELECT addr From mixedtbl WHERE name = '임재범'; 

 

1. (페이지 10 확인) 보조 인덱스 루트페이지 '은지원'보다 큰 값이므로, 200번 페이지 참조

2. (페이지 200 확인) '임재범'은 클러스터형 인덱스의 키 값 LJB임을 확인 후, 클러스터형 인덱스 루트 페이지 참조

3. (페이지 20 확인) 'LJB'는 'KBS'보단 크고 'SSK'보단 작으므로 1001번 페이지 확인

4. (페이지 1001 확인) 'LJB'값을 찾고 그에 해당하는 주소가 '서울' 인것을 찾아냄

 

이러한 구조로 설정한 이유

데이터가 추가되었을때, 클러스터형 인덱스는 페이지 분할 등의 작업 발생으로 데이터 페이지가 변경.

보조 인덱스의 기존의 방식인 '데이터 페이지 주소값'이 담겨 있다면, 데이터 페이지 재구성으로 인해 페이지 번호 및 오프셋이 대폭 변경된다. 그로 인해 엄청난 시스템의 부하를 발생할 수 있게 된다.

 

현재와 같은 구조일 경우,

보조 인덱스 검색 후 클러스터형 인덱스를 검색해야하기때문에 손해를 볼 수 있지만,

데이터 삽입 등으로 인한 대폭 재구성하는 부하는 걸리지 않음.

 

 

인덱스 사용법

인덱스 생성

CREATE [ UNIQUE : FULLTEXT : SPATIAL] INDEX index_name

[index_type]

ON tbl_name (key_part, ...)

[index_option]

[algorithm_option : lock_option] ...

• []안에 것들은 추가 옵션
• UNIQUE는 고유한 인덱스를 만들 것인지 결정. 동일한 데이터 값이 입력될 수 없음.
• 디폴트는 UNIQUE가 생략된 중복이 허용되는 인덱스다.
• FULLTEXT: 전체 텍스트 인덱스, SPATIAL: 점,선,명 등 공간 데이터와 관련된 인덱스 생성에 쓰임.
• CREATE INDEX로 생성하는건 보조 인덱스로 생성됨.
• 그 외의 옵션들은 별도로 공부해볼것.
• 생성된 인덱스를 실제 적용시키려면 ANALYZE TABLE tbl_name 으로 테이블을 분석/처리해줘야함.

 

인덱스 제거

DROP INDEX index_name ON tbl_name
[algorithm_option : lock_option] ...

• 기본 키로 설정된 클러스터형 인덱스 이름은 항상 'PRIMARY'로 되어있으므로, 삭제할때 index_name 부분에 PRIMARY라고 써주면 됨
• ALTER TABLE로 기본 키를 제거해도 클러스터형 인덱스가 제거됨
• 클러스터형과 보조 인덱스가 같이 쓰인 경우엔, 보조 인덱스부터 제거하는게 좋음
  • 위의 혼합형을 보면, 보조 인덱스가 클러스터형을 참조하고 있기 때문이다.

 

 

인덱스 성능비교 확인 명령어

• SHOW GLOBAL STATUS LIKE 'Innodb_pages_read';
  • 읽은 페이지 수 반환
  • 쿼리 실행전 과 실행후에 해당 명령어를 넣어서 쿼리로 인해 페이지를 얼마나 읽었는지 확인 가능

 

 

인덱스를 사용해야하는 경우

사용하면 좋은 인덱스

• 규모가 큰 테이블
• 데이터의 변경이 자주 발생하지 않는 컬럼
• join,where,order by가 자주 사용되는 컬럼
• 데이터의 중복도가 낮은 컬럼(카디널리티가 높다)
  • 카디널리티는 상대적인 개념으로 값의 분산도를 나타냄
  • 특정 컬럼이 많은 종류의 값을 가지고 있다면 카디널리티가 높은 것.
  • 즉 주민등록번호과 이름 컬럼이 있을때, 주민등록번호 컬럼은 고유의 값을 가지고 있기때문에, 중복도는 낮고 카디널리티는 이름 컬럼에 비해 높다.
  • 이름 컬럼은 중복된 값을 가질 수 있기때문에, 주민등록번호 컬럼에 비해 중복도는 높고 카디널리티는 낮다

 

참조

이것이 MySQL이다 책 참조