인덱스란?

데이터베이스에서 테이블에 대한 동작속도를 높여주는 자료구조이다.

데이터베이스에서 말하는 index는 책에 비유하면 목차이다 데이터의 위치를 가르키는 지표와 같은 것.
따라서 빠른 시간내에 원하는 자료를 찾을 수 있다.

데이터베이스에서 index의 구조는 크게 clustered index와 non-clustered index로 나뉜다.

Clustered Index란?

• 물리적으로 행을 재배열 한다.
• pk설정 시 그 컬럼은 자동으로 Clustered Index가 만들어진다.
• 인덱스 자체의 리프 페이지가 곧 데이터, 테이블 자체가 인덱스이다.
• 데이터 입력, 수정, 삭제 시 항상 데이터가 정렬 상태를 유지
• 비 클러스터형 인덱스보다 검색 속도가 더 빠르다. but 데이터 입력, 수정, 삭제는 느림
• 30% 이내에서 사용해야 좋은 선택도를 가진다.

Non-Clustered Index란?

• 테이블당 약 240개의 인덱스를 만들 수 있다.
• 인덱스 페이지는 로그파일에 저장된다.
• 레코드의 원볻은 정렬되지 않고 인덱스 페이지만 정렬됨.
• 인덱스 자체의 리프 페이지는 데이터가 아니라 데이터가 위치하는 포인터이기 때문에 클러스터보단 조회가 느리지만 입력, 수정, 삭제가 빠르다.
• 인덱스를 생성할 때 데이터 페이지는 그냥 둔 상태에서 별도의 인덱스 페이지를 따로 만들기 때문에 용량을 더 많이 차지한다.
• 3% 이내에서 사용해야 좋은 선택도를 가진다.

눈으로 보는 Non-Clustered Index

id	name	age
1	가	2
2	다	1
3	라	4
4	나	3

이 표와 같이 유저 라는 테이블이 있다고 가정할 때
개발자는 id값이 아닌 name으로 정렬을 하고 싶다고 생각 하거나 name으로 조회하는 경우가 많다고 가정을 하면
Non-Clustered Index를 활용하는 방법이 있다.

create index 이름인덱스 on 유저테이블(name);

워크벤치나 커맨드 라인으로 해당 명령어를 써주면

id	name	age
1	가	2
4	나	3
2	다	1
3	라	4

이런식으로 정렬이 된다.

예시로 유저 이름으로 걸었지만, 실제로 유저의 이름으로 걸면 좋지 않다. (중복값이 많으면 성능이 좋지 않음)
실제로 name컬럼에 중복값이 들어간다면

id	name	age
1	가	2
4	나	3
2	다	1
3	라	4
5	라	5

이름으로는 정렬이 되지만 다음 정렬될 테이블을 예측하기 힘들다. (id값이나 age값이 뒤죽박죽이 될 수 있음)

create index 이름아이디인덱스 on 유저테이블(name, id);

따라서 이렇게 두개를 걸면 되긴 하지만 왠지모르게 두개를 동시에 인덱스를 걸면 뭔가 성능이 떨어질거같아 불안하다.

그러므로 인덱스를 걸 때에는 중복이 적고 조회가 잦은 인덱스를 선정하는것이 좋다. (예를들어 대체 인덱스로 username이나 email 추가)

개발하면서 느낀 Clustered Index

1. MySQL을 사용하면 자동으로 PK가 설정이 되어서 테이블이 생성될때마다 정렬해줌.
2. PK만 알고있으면 조회속도가 빨라짐

개발하면서 느낀 Non-Clustered Index

1. sql로 조회를 할 때 해당 컬럼으로 order by 진행 할 시 성능이 현저하게 떨어질 때가 있음.
2. 따라서 미리 정렬된 index를 만들어주면 예를들어 10초짜리 조회인데 9초만에 조회가 가능할 수 있게 만들어줌
3. 하지만 무분별하게 여러개의 컬럼에 index를 걸면 테이블이 쌓이는 순간 정렬되는 index가 특이점이 발생할 수 있음

정리

특징	클러스터드 인덱스	논클러스터드 인덱스
정의	데이터가 정렬된 순서로 저장되는 인덱스	데이터와 별도로 저장되는 인덱스
구조	테이블의 실제 데이터가 인덱스에 포함됨	인덱스에 데이터의 포인터(주소)만 포함됨
속도	조회 속도가 빠르며, 범위 검색에 유리	특정 컬럼에 대한 조회가 빠르지만, 정렬된 검색 시 성능 저하 가능
기본키(PK)	기본적으로 테이블당 하나만 존재할 수 있음	여러 개의 인덱스를 생성할 수 있음
사용 예시	주로 기본 키나 자주 조회되는 컬럼에 사용	자주 검색되는 비주요 컬럼에 사용
공간 효율성	데이터와 인덱스가 동일하므로 공간 효율적	인덱스와 데이터가 분리되어 공간이 더 필요
변경 비용	데이터 삽입/삭제 시 성능 저하 발생 가능	인덱스 추가 및 삭제 시 비용이 발생
특이점	데이터 양이 많아지면 성능 저하가 발생할 수 있음	많은 인덱스를 걸 경우 업데이트 성능 저하 가능