[DB]인덱스(index)
인덱스의 개념
인덱스란 데이터의 저장(INSERT,UPDATE,DELETE)의 성능을 희생하고 그 대신에 데이터의 읽기 속도를 높이는 테이블의 동작속도(조회)를 높여주는 자료구조이다.
- 인덱스 자체 역시 하나의 데이터 덩어리 이기 때문에, 데이터베이스에 전체 크기의 10%나 되는 추가 적인 공간을 할당해줘야 하고, 잘못 사용할 경우 성능이 오히려 크게 떨어질 수 있다는 단점이 있다.
B-Tree인덱스 알고리즘
B-Tree 는 최상위에 하나의 루트 노드가 존재하고 그 하위에 자식 노드가 붙어있는 형태이다.
트리 구조의 가장하위에는 리프 노드라고 하고 트리구조에서 루트노드도 아니고 리프노드도 아닌 중간의 노드를 브랜치 노드라고 한다.
이 인덱스의 최대 장점은 어떤 데이터를 조회하든지, 이에 사용하는 조회 과정의 길이 및 비용이 균등하다는 데 있다.
단, 어떤 데이터를 조회 하든지 Root 에서 부터 Leaf페이지를 모두 거처야 하기 때문에 데이터가 적은 테이블등의 단순 조회로 데이터를 조회하는 과정이 대비 조회 속도가 느린 단점이 있다.
Hash 인덱스 알고리즘
Hash 인덱스 알고리즘은 칼럼의 값으로 해시 값을 계산해서 인덱싱하는 알고리즘으로, 매우 빠른 검색을 지원한다.
하지만 값을 변형해서 인덱싱하므로, 해시 인덱스는 동등 비교 검색에는 최적화돼 있지만 범위를 검색한다거나 정렬된 결과를 가져오는 목적으로는 사용 할 수 없다.
주로 인메모리 DB에서 사용하는 인덱스 종류다.
인메모리 DB란?
메모리가 디스크 스토리지의 메인 메모리에 설치되어 운영되는 DB다.
알티베이스, Oracle Timestan, SAP Hana DB등이 이 분류에 속한다.
해시 인덱스의 장점으로는 실제 키값과는 관계없이 인덱스 크기가 작고 검색이 빠르고 원래의 키값을 저장하는 것이 아니라 해시 함수의 결과만을 저장하므로 키 컬럼의 값이 아무리 길어도 실제 해시 인덱스에 저장되는 값은 4~8 바이트 수준으로 상당히 줄어든다.
그래서 타 인덱스 대비 조회 속도가 매우 빠르다.
그러나 위와는 반대로, 각 해쉬값에 주소값을 배정하는 인덱스의 특징에 따라 범위로 조회하는 작업은 느리다
또한 범위로 묶어서 보관하는 인덱스가 아니므로 데이터 개수가 증가 함에 따라 범위로 묶어서 보관하는 인덱스보다 더 큰 저장공간을 필요로 한다.
Fractal-Tree 알고리즘(TokuDB)
Fractal-Tree 알고리즘은 B-Tree의 단점을 보완하기 위해 고안된 알고리즘이다.
값을 변형하지 않고 인덱싱하며 범용적인 목적으로 사요할 수 있다는 측면에서 B-Tree와 거의 비슷하지만 데이터가 저장되거나 삭제될 대 처리 비용을 상당히 줄일 수 있게 설계된 것이 특징이다.
인덱스 타입 종류
인덱스의 타입은 크게 두가지로 나뉘는데 Primary(클러스터)인덱스와 Secondary(보조)인덱스로 나뉘어 진다.
클러스터 인덱스는 처음부터 정렬이 되어있는 영어사전과 같은 개념이고,
보조 인덱스는 책 뒤의 찾아보기의 개념과 비슷하다.
각 인덱스의 특징에 따라 mysql에서 사용처가 다르다고 보면 된다.
| ---- | 클러스터 인덱스 | 보조 인덱스 |
|---|---|---|
| 속도 | 빠르다 | 느리다 |
| 사용 메모리 | 적다 | 많다 |
| 인덱스 | 인덱스가 주요 데이터 | 인덱스가 데이터의 사본(Copy) |
| 개수 | 한 테이블의 한 개 | 한 테이블에 여러 개(최대 약 250개) |
| 리프 노드 | 리프 노드 자체가 데이터 | 리프 노드는 데이터가 저장되는 위치 |
| 저장값 | 데이터를 저장한 블록의 포인터 | 값과 데이터의 위치를 가리키는 포인터 |
| 정렬 | 인덱스 순서와 물리적 순서가 일치 | 인덱스 순서와 물리적 순서가 불일치 |
클러스터 인덱스와 보조 인덱스를 살펴봄에 앞서서 우선 다음과 같은 테이블 데이터를 하나 준비해 준다.
인덱스가 없는 데이터들은 mysql 내에서 페이지(page)라는 노드의 16kb 단위로 분할되어 저장된다.
이제 이 데이터들에게 인덱스를 부여하여 왜 인덱스가 있으면 검색속도가 빨라지고 어떨때에는 느리는지 자세히 살펴보자
MySQL은 데이터를 한곳에다가 다 저장하는것이 아닌, 페이지(page)단위로 쪼개어 저장하는데, 페이지의 크기 기본값은 16KB 정도이다.
페이지 크기는 `SHOW VARIABLES LIKE 'INNODB_PAGE_SIZE'문으로 확인 할 수 있다. MB, GB 시대에서 KB는 적게 보일수는 있지만 꽤 많은 데이터를 저장할 수 있는 용량이다.
그래도 필요하다면 INNODB_PAGE_SIZE 환경변수 값을 4KB, 8KB, 32KB, 64KB로 변경할 수 있다.
클러스터 인덱스(Primary Index)
- 특정 나열된 데이터들을 일정 기준으로 정렬해준느 인덱스다.
그래서 클러스터형 인덱스 생성 시에는 데이터 페이지 전체가 다시 정렬된다. - 하지만 이러한 정렬 특징 때문에, 이미 대용량의 데이터가 입려된 상태라면 클러스터형 인덱스 생성은 심각한 시스템
부하를 줄 수 있다. - 한개의 테이블에
한개씩만만들 수 있다 - 본래 인덱스는 생성 시 데이터들의 배열정보를 따로 저장하는 공간을 사용하나, 클러스터 인덱스는 따로 저장하는 정보
공간을 적게 사용하면서 테이블 공간 자체를 활용한다.
인덱스 자체의 리프 페이지가 곧 데이터이기 때문에 인덱스 자체에 데이터가 포함되어 있다고 볼 수 있다. - 보조 인덱스 보다
검색 속도는 더 빠르다
하지만 입력/수정/삭제는 더 느리다. - MySQL에서는 Primary Key가 있다면 Primary Key를 Clustered INDEX로, 없다면 UNIQUE 하면서 NOT NULL인 컬럼을, 그것도 없으면 임의로 보이지않는 커럼을 만들어 Clustered Index로 지정한다.
클러스터 인덱스 생성시 페이지 변화
- 인덱싱을 하면 루투 페이지라는 것이 만들어진다.
루트 페이지는 각 데이터 페이지의 첫 번째 데이터만 따와서 모아 매핑시키는 페이지다. - 그리고 데이터 페이지는 자동 정렬이 된다.
- 데이터 페이지 자체를 인덱스 페이지로 하는 특징이 있다.
클러스터 인덱스를 이용한 데이터 조회(범위)
- 이번엔 데이터 한개가 아닌 여러개의 데이터를 범위로 검색해보자.
유저 아이디가 A ~ J인 사용자를 모두 검색해본다. - 역시 2페이지만 읽으면 된다.
- 이처럼 정렬이 되어있기 때문에 검색은 무척 빠르다.
클러스터 인덱스를 이용한 데이터 삽입
- FNT 데이터를 추가하는데 1000번 페이지에 공간이 없어저서 페이지 분할이 일어나 2000번 페이지가 생겨나게 된다.
- 이처럼 정렬이 되어있기 때문에 오히려 삽입 삭제 등을 할 때, 페이지 분할이나 추가적인 정렬이 필요해 성능이 오히려 나빠지게 된다.
보조 인덱스(Secondary Index)
- 이 인덱스는
논 클러스터 인덱스(non-clustered index)라고도 불린다. - 개념적으로는 후보키에만 부여 할 수 있는 인덱스다.
(후보키: 고유 식별 번호, 주민번호 같이 각 데이터를 인식할 수 있는 최ㅣ소한의 고유 식별 속성 집합) - 보조 인덱스의 생성시에는 데이터 페이지는 그냥 둔 상태에서
별도의 페이지에 인덱스를 구성한다. - 별도의 페이지에서 인덱스를 구성하니, 클러스터와는 달리 자동 정렬을 하지 않는다.
- 클러스터 인덱스의 리프 페이지는 보조 인덱스의 인덱스 자체의 리프 페이지는 데이터가 아니라 데이터가 위치하는 주소값(RID)
- 클러스터형 보다 검색 속도는 더 느리지만 데이터의 입력/수정/삭제는 덜 느리다.
- 보조 인덱스는
여러 개 생성할 수 있다. 그러나 함부로 사용할 경우에는 오히려 성능이 떨어뜨릴 수 있다. - 각 데이터에 대해서 고유 값(unique)들이 있는 목록에 생성 할 수 있는 인덱스다.(unique key)
보조 인덱스 생성시 페이지 변화
- 보조 인덱스 역시 루트 페이지가 만들어진다.
하지만 데이터 페이지에 바로 연결시키지 않고 따로 리프 페이지를 만들어서 매핑을 하고 정렬 시킨다. - 이처럼 추가 공간이 필요하므로 마구 인덱스를 남용하면 공간 낭비로 이어질 수도 있다.
- 데이터 페이지는 변화를 주지 않는다.
따라서 클러스터 인덱스와는 달리 여러개 생성이 가능한 이유이다.
인덱스 설계 핵심
효율적인 인덱스 설계
- WHERE 절에 사용되는 열(WHERE 절에 사용되는 열이라도 자주 사용해야 가치가 있음)
- SELECT절에 자주 등장하는 컬럼들을 잘 조합해서 INDEX로 만들어두면 INDEX 조회 후 다시 데이터에서 조회할 필요가 없으므로 빠르게 검색이 가능하다.
- JOIN절에 자주 사용되는 열에는 인덱스의 효율이 좋음.
- ORDER BY 절에 사용되는 열은 데이터 페이지가 자동 정렬됐기 때문에 클러스터형 인덱스가 유리
외래키는 자동으로 외래키 인덱스 만듬
금지해야 할 인덱스 설계
- 대용량 데이터가 자주 입력되는 경우,
클러스터형 인덱스의 경우 빈번한 페이징이 일어나기 때문에 부하가 생긴다.
따라서 인덱스가 필요한 경우 primary(클러스터)대신 unique만 설정하는게 좋을 수 있다. - 데이터 중복도가 높은 열은 인덱스 효과가 없다
예를 들어 성별 열에 M, F만 있다고 하면 인덱스를 안쓰는 게 낫다.
따라서 일반 보조 인덱스보다 unique 보조 인덱스가 빠른 이유가 이것이다. - 자주 사용되지 않으면 성능 저하를 초래할 수 있음.
(INSERT만 주구장창 하는 시스템이라면, 사용해보지도 못하고 데이터 입력에 걸리는 작업량만 많아진다.)
데이터 중복도
중복도가 높은 경우, 인덱스를 사용하는 것이 효율이 없지는 않지만 어차피 데이터를 읽기 위해 많은 페이지를 읽어야 하는 것은 마찬가지이기 때문에 피해야 한다.
예를 들어 성별이라는 컬럼에 INDEX를 만들어두면 남, 여 밖에 없기 때문에 중복도는 높고 분포도는 낮다.
따라서 데이터의 종류가 별로 없기 때문에, 남자를 검색할 때 절반이나 되는 ROW를 검색해야 하고 결국 모든 ROW를 검색하는 table full scan이 더 나을지도 모른다.
인덱스를 사용할 때 주의할 점
- 데이터 변경(삽입, 수정, 삭제)작업이 얼마나 자주 일어나는지 고려해야 함
- 단일 테이블에 인덱스가 많으면 속도가 느려질 수 있다. (테이블당 4~5개 권장)
- 검색할 데이터가 전체 데이터의 20% 이상이라면, MYSQL에서 인덱스를 사용하지 않음. (강제로 사용 할 시 성능 저하를 초래할 수 있음)
전체 페이지의 대부분을 읽어야 하고, 인덱스 관련 페이지도 읽어야 해서 작업량이 크기 때문이다. - 사용하지 않는 인덱스는 제거하는 것이 바람직하다.
(실무에서 사용하지 않는 보조 인덱스를 몇개 삭제했을 때 성능이 향상되는 경우도 많음) - 클러스터형 인덱스는 테이블당 하나만 생성할 수 있음
- 테이블에 클러스터형 인덱스가 아예 없는 것이 좋은 경우도 있음
INDEX 손익 분기점
테이블이 가지고 있는 전체 데이터양의 10% ~ 15%이내의 데이터가 출력 될 때만 INDEX를 타는게 효율적이고, 그 이상이 될 때에는 오히려 풀스캔이 더 빠르다.
인덱스 문법 정리
SHOW INDEX
FROM 테이블이름- Table: 테이블의 이름을 표시함.
- Non_unique: 인덱스가 중복된 값을 저장할 수 있으면 1, 저장할수 없으면 0을 표시함.
- key_name: 인덱스의 이름을 표시하며, 인덱스가 해당 테이블의 기본 키라면 PRIMARY로 표시함.
- Seq_in_index: 인덱스에서의 해당 필드의 순서를 표시함
- Column_name: 해당 필드의 이름을 표시함
- Collation: 인덱스에서 해당 필드가 정렬되는 방법을 표시함.
- Cardinality: 인덱스에 저장된 유일한 값들의 수를 표시함
- Sub_part: 인덱스 접두어 표시함
- Packed: 키가 압축되는 (packed)방법을 표시함
- Null: 해당 필드가 NULL을 저장할 수 있으면 YES를 표시하고, 저장할 수 없으면 "를 표시함
- Index_type: 인덱스에 사용되는 메소드(method)를 표시함
- Comment: 해당 필드를 설명하는 것이 아닌 인덱스에 관한 기타 정보를 표시함
- Index_comment: 인덱스에 관한 모든 기타 정보를 표시함
테이블의 인덱스 크기 확인
show table status like 테이블명인덱스 생성
Create Table 내
