[MySQL] 인덱스 종류 및 고려사항 (단일, 복합, 클러스터, 논클러스터, 커버드)

인덱스를 사용하는 이유

책에서 목차라고 생각하시면 편합니다.

사용자는 목차를 보고 원하는 페이지를 바로 펼칠 수 있습니다.

즉, 인덱스를 사용하는 가장 큰 이유는 쿼리의 성능을 높이기 위해서 입니다.

테이블에 다수의 레코드들이 정렬되어 있지 않고 무작위로 배열되어 있을 때, 특정 데이터를 찾기 위해서는 해당 테이블 전체를 검색해야 합니다.

인덱스는 무작위 배열된 데이터를 정렬한 뒤, 필요로 하는 데이터만 가지고 오기 때문에 사용자는 빠른 결과를 받을 수 있습니다.

인덱스 생성시 고려사항

그렇다면 무작정 인덱스를 생성하면 좋을까요?

아닙니다.

인덱스 종류에 따라 물리적인 공간을 요구하기 때문에 무분별한 인덱스는 오히려 용량만 차지하게 됩니다.

또한 옵티마이저는 생성한 인덱스 중 어느 인덱스를 선택해야 비용이 낮은지 계산을 해야하기 때문에 이러한 비용도 발생하게 됩니다.

쿼리에서 특정 컬럼들을 조건으로 걸 때, 정렬된 인덱스의 순서가 맞지않다면 이 또한 추가적인 비용이 발생하게 되겠죠.

이런 점들을 고려하여 인덱스를 생성하는게 좋다 안좋다를 판단한 뒤 적절한 경우에 만들어야 합니다.

인덱스 종류

인덱스는 단일, 복합, 클러스터 ,논클러스터 ,커버드 종류 등등이 있습니다.

테이블 생성 및 데이터를 적재하면, 데이터마이그레이션 등이 일어나지 않았을 경우 적재된 순서대로 데이터가 출력됩니다.

Master-mysql> DESC MOZI;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| C1    | int(11)     | YES  |     | NULL    |       |
| C2    | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)


Master-mysql> INSERT INTO MOZI VALUES (1, 'A');
Master-mysql> INSERT INTO MOZI VALUES (10, 'B');
Master-mysql> INSERT INTO MOZI VALUES (3, 'C');
Master-mysql> INSERT INTO MOZI VALUES (4, 'A');
Master-mysql> INSERT INTO MOZI VALUES (3, 'D');
Master-mysql> INSERT INTO MOZI VALUES (3, 'A');
Master-mysql> COMMIT;


Master-mysql> SELECT * FROM MOZI;
+------+------+
| C1   | C2   |
+------+------+
|    1 | A    |
|   10 | B    |
|    3 | C    |
|    4 | A    |
|    3 | D    |
|    3 | A    |
+------+------+
6 rows in set (0.00 sec)

클러스터 인덱스

테이블당 1개만 허용되며, 해당 컬럼을 기준으로 테이블이 물리적으로 정렬됩니다.

테이블이 물리적으로 정렬되기 때문에, 리프노드가 필요없게 되며 추가적인 공간이 필요치 않습니다.

PRIMARY KEY 설정 시 자동으로 생성되며, 이 컬럼은 데이터 변경시 항상 정렬을 유지합니다.

조회성 쿼리 성능은 빠르나, 데이터 작업이 변경 일어날 때 프라이머리 키 관련 작업을 추가적으로 해야하므로 성능이 떨어지게 됩니다.

클러스터 인덱스 사용시 주의사항

클러스터 인덱스를 생성하게 되면, 모든 보조 인덱스가 프라이머리 키를 포함하게 됩니다.

따라서, 프라이머리 키의 크기가 커질수록 보조 인덱스의 크기도 커지게 됩니다.

Master-mysql> ALTER TABLE MOZI ADD PRIMARY KEY (C1, C2);
Query OK, 0 rows affected (0.68 sec)
Records: 0  Duplicates: 0  Warnings: 0

Master-mysql> SELECT * FROM MOZI;
+----+----+
| C1 | C2 |
+----+----+
|  1 | A  |
|  3 | A  |
|  3 | C  |
|  3 | D  |
|  4 | A  |
| 10 | B  |
+----+----+
6 rows in set (0.00 sec)

논클러스터 인덱스 (단일, 복합, 커버드)

테이블당 249개 까지 생성이 가능하며, 테이블의 페이지를 정렬하지 않고, 새로운 공간을 할당합니다.

따라서 클러스터 인덱스보다 많은 공간을 차지하게 됩니다.

단일인덱스

인덱스에 컬럼이 하나만 걸려 있는 경우입니다.

데이터가 많지 않으며 조건에 걸리는 컬럼이 적은 경우에 사용합니다.

Master-mysql> CREATE INDEX IDX_MOZI ON MOZI (C1);
Query OK, 0 rows affected (0.45 sec)
Records: 0  Duplicates: 0  Warnings: 0

복합 인덱스

인덱스에 컬럼이 두개 이상이 걸려 있는 경우입니다.

데이터 가 많고 조건에 걸리는 컬럼들이 많은 경우에 사용됩니다.

Master-mysql> CREATE INDEX IDX_MOZI ON MOZI (C1, C2);
Query OK, 0 rows affected (0.39 sec)
Records: 0  Duplicates: 0  Warnings: 0

복합 인덱스와 클러스터 인덱스 사진을 보고 어 ? 다를게 없네 라고 할 순 있지만,

클러스터 인덱스는 테이블을 물리적으로 정렬하여 실 공간 사용량이 증가하지 않지만

복합 인덱스는 테이블 데이터를 기준으로 새로운 인덱스 공간을 생성 및 데이터를 정렬하기 때문에 공간 사용량이 증가하게 됩니다.

커버드 인덱스

커버드 쿼리라고도 합니다.

쿼리가 인덱스 접근으로 인해 테이블 접근이 필요치 않은 경우입니다.

Master-mysql> CREATE INDEX IDX_MOZI ON MOZI (C1, C2);
Query OK, 0 rows affected (0.39 sec)
Records: 0  Duplicates: 0  Warnings: 0

Master-mysql> SELECT C1, C2 FROM MOZI WHERE C1 = 3 AND C2 = 'A';
+------+------+
| C1   | C2   |
+------+------+
|    3 | A    |
+------+------+
1 row in set (0.03 sec)