MySQL - 인덱스 정리! (클러스터 인덱스, 논 클러스터 인덱스)

인덱스는?

인덱스가 없더라도 데이터베이스를 '작동'하는데는 문제가 없습니다. 하지만 인덱스는 데이터베이스의 성능(속도)를 크게 좌우하는 요소입니다. 데이터베이스가 크면 클 수록, 인덱스가 반드시 필요해질 것입니다. (책이 두꺼울 수록 원하는 내용을 찾기 힘들고, 정렬이나 <찾아보기>, <목차> 등이 필요해지는 원리)

 

인덱스를 사용하는 SQL을 만들어 효율적으로 사용한다면 매우 빠른 응답 속도를 얻을 수 있고, 쿼리의 부하가 줄어들기 때문에 시스템 전체 성능이 향상됩니다. 하지만 인덱스는 데이터베이스에 추가적인 공간을 할당해줘야 하고, 잘못 사용할 경우 성능이 오히려 크게 떨어질 수 있습니다. (ex. 변경 작업이 자주 일어나는 경우, 인덱스가 적절하지 않은 경우)

 

인덱스의 종류

MySQL에서 인덱스의 종류는 두 가지가 있습니다.

 

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

 

클러스터형 인덱스(Clustered Index)는 사전에서 가나다 순으로 찾기 좋게 정렬되어 있고, 책의 모서리에 ㄱ, ㄴ, ㄷ 이런 표시를 이용해서 찾을 수 있는 방식과 유사합니다.

 

즉, 실제 데이터 페이지가 클러스터 인덱스를 기준으로 재정렬됩니다. 즉, 정렬된 데이터를 기준으로 범위를 좁혀가면서 탐색할 수 있습니다.

 

논 클러스터형 인덱스 (Non-Clustered Index)

 

논 클러스터형 인덱스(Non-Clustered Index)라고도 불리는 보조 인덱스는 책의 <목차>나 <찾아보기> 페이지를 펴서 원하는 내용의 페이지를 확인할 수 있는 방식과 유사합니다.

 

즉, 실제 데이터 페이지는 건드리지 않고 인덱스를 기반으로 정렬된 새로운 저장소를 만듭니다. 그 후에 저장소에서 인덱스를 가지고 탐색할 데이터의 주소를 알아낸 후에, 그 주소를 사용해서 실제 데이터에 접근하게 됩니다.

 

각 인덱스의 차이

클러스터형 인덱스는 인덱스에 맞춰서 행이 자동으로 정렬되고, 테이블당 하나만 사용할 수 있습니다. 반면에 보조 인덱스는 정렬기능은 따로 가지지 않고, 테이블당 여러개 사용할 수 있습니다.

 

테이블의 열에 개인키(Primary Key)를 설정하면 자동으로 해당 열에 클러스터형 인덱스가 생성됩니다. 그리고 클러스터형 인덱스가 있는 개인키(Primary Key)열에 의해 오름차순(Default)으로 정렬됩니다.

CRATE TABLE tbl1 (a INT PRIMARY KEY);
SHOW INDEX FROM tbl1;

간단하게 테이블을 하나 생성하고 인덱스의 상태를 확인하겠습니다.

Non_unique Index는 Unique Index면 0, 아니면 1입니다. Key_name은 인덱스의 이름입니다. 이름을 지정하지 않은 개인키를 지정해서 PRIMARY라고 나왔고, 보조 인덱스는 열 이름 또는 키 이름으로 표기됩니다. Seq_in_dex는 해당 열에 여러개의 인덱스가 있을 때 순서를 나타내는데 대부분 1입니다. Null은 Null값 허용 여부인데, 비어있으면 NO 입니다. Cardinality는 중복되지 않은 데이터 개수를 의미합니다. 데이터를 하나 삽입하면 1로 바뀔 것입니다. Index_type은 MySQL에서 사용하는 기본적인 구조인 B-Tree(균형 트리)가 나옵니다.

ALTER TABLE tbl1 ADD b INT UNIQUE;
SHOW INDEX FROM tbl1;

테이블에 유니크 제약 조건을 가진 열을 추가했습니다. 테이블의 열에 Unique 제약 조건을 지정하면 해당 열에 자동으로 보조 인덱스가 생성됩니다.

인덱스의 상태에서 보조 인덱스가 하나 생겼습니다. 이름을 따로 지정하지 않아 Key_name이 열 이름이 된 것을 알 수 있습니다.

 

UNIQUE 열에  NOT NULL까지 지정 한다면 보조 인덱스가 아닌 클러스터형 인덱스로 지정됩니다. (여기서 PRIMARY KEY가 존재한다면 PRIMARY KEY를 가진 열이 클러스터형 인덱스가 되고 , UNIQUE NOT NULL의 열은 보조 인덱스가 됩니다. 즉, 이때는 PRIMARY KEY의 열을 기준으로 정렬됩니다.)

 

*제약 조건의 설정은 보통 테이블의 생성문이나 ALTER문에서 생성합니다. 데이터가 입력되기 전에 제약 조건을 설정하는 것은 문제가 안되지만, 많은 데이터가 입력된 상태에서 Unique나 Primary를 지정하면 인덱스를 구성하는데 많은 시간이 소요됩니다. 즉, 실제 서비스에서는 인덱스 관련 작업을 할 때는 주의해야합니다.

 

B-Tree

MySQL의 B-Tree에서는 페이지를 트리(자료구조)에서 노드와 같은 개념으로 사용합니다. 한 개 페이지는 MySQL에서는 16Kbyte, 다른 DBMS는 다른 크기도 사용하고, 물론 설정하면 바꿀 수 있습니다.

 

만약 어떤 서비스의 회원이 100000명 있다고 가정합니다. ID가 'EBS123'인 사람의 개인 정보를 조회할 때, 모든 회원의 정보를 하나씩 검색하면 평균적으로 50000명의 정보를 조회해야 합니다. (회원이 100000명이기 때문)

트리의 개념처럼 A로 시작하는 ID, B로 시작하는 ID, C로 시작하는 ID. 이렇게 구분한다면. E가 나오기 전인 A,B,C,D 네 번만 조회해도 E로 시작하는 ID가 아닌 90000만 여개의 ID를 제외할 수 있을 겁니다. 즉, 데이터의 양이 늘어날수록 인덱스의 중요성과 효율은 기하급수적으로 올라갑니다.

 

하지만 이는 데이터를 검색할 때는 효율 적이지만 데이터를 변경(INSERT, UPDATE, DELETE) 할때는 성능이 나빠질 수 있습니다. 특히 INSERT할 때 성능이 많이 떨어지는 경우가 많습니다. 데이터를 하나 집어 넣는 것 뿐인데도, 정렬이 다시 되어야 하고, 페이지가 포화이면 페이지를 분할해야 하고,  루트페이지도 포화면 루트페이지도 중간페이지로 분할해야 하고, 그러면 페이지에 있는 모든 데이터들의 구조를 전부 바꿔야 합니다.

 

클러스터형, 보조 인덱스의 장단점

＃ 클러스터형 인덱스

• 생성시에 데이터 페이지 전체가 다시 정렬되므로, 서비스가 운영중에 클러스터형 인덱스를 생성하면 큰 부하
• 인덱스 자체의 리프 페이지가 곧 데이터이므로, 인덱스에 데이터가 포함되어 있음
• 보조 인덱스보다 검색 속도가 빠르고, 변경(INSERT, UPDATE, DELETE)는 느림
• 평균적으로 보조 인덱스보다 훨씬 빠르지만, 테이블에 한 개만 생성할 수 있음

＃ 보조 인덱스

• 생성시에 데이터 페이지는 건드리지 않고, 별도의 페이지에서 인덱스를 구성하는 작업을 실행
• 보조 인덱스의 인덱스 자체의 리프 페이지는 데이터 페이지의 주소 값
• 클러스터형 인덱스보다 검색 속도는 느리고, 변경(INSERT, UPDATE, DELETE)은 덜 느림
• 보조 인덱스는 여러 개 생성할 수 있지만, 충분히 고려하고 사용해야 함

* 클러스터형 인덱스와 보조 인덱스를 혼합해서 사용할 경우에는, 열의 자리수가 작은 것을 클러스터형 인덱스에 저장하는 것이 바람직합니다. 클러스터형 인덱스를 지정한 열의 내용은 보조 인덱스에도 저장되기 때문에 차지하는 공간이 커지기 때문입니다.

 

인덱스를 사용할 때 주의할 점

• WHERE절에 사용되어야만 인덱스를 주로 사용함.
• 자주 사용되지 않으면 성능 저하를 초래할 수 있음. (INSERT만 주구장창 하는 시스템이라면, 사용해보지도 못하고 데이터 입력에 걸리는 작업량만 많아집니다.)
• 검색할 데이터가 전체 데이터의 20% 이상이라면, MySQL에서 인덱스를 사용하지 않음. (강제로 사용할 시 성능 저하를 초래할 수 있음) - 전체 페이지의 대부분을 읽어야 하고, 인덱스 관련 페이지도 읽어야 해서 작업량이 큼)
• WHERE절에 인덱스가 있는 열 이름에는 함수나 연산을 가하면 MySQL이 인덱스를 사용하지 않음. (WHERE count*10=100은 인덱스를 사용하지 않고, WHERE count=100/10은 인덱스를 사용함)
• 인덱스의 데이터 중복도가 높으면 성능 저하를 초래할 수 있음. (성별의 경우 M, F 데이터 종류가 두 가지이다. 이런 경우도 인덱스로 효과를 얻기 어려움)
• 전체 작업 중 데이터 변경의 비율이 높다면 인덱스 사용을 다시 고민해봐야 한다. (인덱스는 읽기에서만 성능을 향상시키고 변경에서는 부담을 주기 때문)
• JOIN절에 자주 사용되는 열에는 인덱스의 효율이 좋음.
• 사용하지 않는 인덱스는 제거하는 것이 바람직함. (실무에서 사용하지 않는 보조 인덱스를 몇개 삭제했을 때 성능이 향상되는 경우도 많음)
• 인덱스는 MySQL의 성능에 매우 매우 큰 영향을 미치므로 심사숙고하여야 한다.

 

---

 

이미지 참고: https://velog.io/@gillog/SQL-Clustered-Index-Non-Clustered-Index