chapter 9: 인덱스

인덱스의 정의

인덱스는 데이터베이스에서 데이터의 빠른 접근과 검색을 위해 사용되는 자료구조이다.

인덱스의 핵심 개념

• 검색 속도가 무척 빨라질 수 있다.
• 인덱스의 종류로는 클러스터형 인덱스와 보조 인덱스가 있다.
  • 다른 DBMS에서는 클러스터형 인덱스와 비클러스터형 인덱스로 종류를 나누기도 한다. 즉, 보조 인덱스와 비클러스터형 인덱스는 거의 비슷한 개념이다.
• 클러스터형 인덱스는 영어사전으로, 보조 인덱스는 책 뒤의 찾아보기로 비유할 수 있다.
• Primary Key, Unique를 설정한 열에는 자동으로 인덱스가 생성된다.
• 인덱스는 B-Tree 구조를 갖는다.
• 클러스터형 인덱스는 테이블에 1개만 생성할 수 있으며, 보조 인덱스는 테이블에 여러 개를 생성할 수 있다.
• 인덱스의 생성, 삭제를 위해서는 CREATE INDEX/ DROP INDEX문을 사용할 수 있다.
• 클러스터형 인덱스가 보조 인덱스보다 검색 성능이 더 좋다.
• 실제로 데이터베이스에 인덱스를 생성해 놓아도, 인덱스를 사용하는 것이 빠를지 아니면 그냥 Full Table Scan이 빠를지를 MySQL이 알아서 판단한다. 그렇더라도 쓸데 없는 인덱스를 만들어서 발생되는 문제점은 많이 있다.
• 인덱스는 우선 테이블의 열(컬럼) 단위에 생성된다.
• 하나의 열에 인덱스를 생성할 수도 있고 여러 열에 하나의 인덱스를 생성할 수도 있다.

데이터베이스 튜닝
튜닝이란 SQL 서버가 기존보다 더욱 좋은 성능을 내도록 하는 전반적인 방법론을 말한다. (자동차의 성능을 높이기 위한 자동차의 튜닝과 개념이 비슷하다.)
튜닝은 크게 두 가지 관점으로 볼 수 있다. 하나는 응답시간(Response Time)을 빨리하는 것이다. 즉, A라는 사용자가 쿼리문을 실행하면 '얼마나 빨리 결과를 얻는가'의 문제가 관점이 된다. 이것은 사용자의 입장에서는 아주 효과적인 것처럼 보일 수 있지만, 잘 생각해 봐야 한다. A 사용자는 기존에 1분 걸리던 것을 10초 만에 얻게 된다면, 아주 효과적으로 보일 수도 있고 마치 튜닝이 잘 된 것처럼 보여질 수는 있다. 하지만 서버의 입장에서는 기존에는 1만큼의 작업만 하던 것을 100의 작업을 해야 하는 경우도 발생할 수 있다. 이런 경우에는 한 명의 사용자에게는 결과가 빨리 나오겠지만, 전체적인 시스템의 성능은 오히려 나빠질 수도 있다.

또 다른 하나는 서버의 부하량을 최소화하는 것이다.
즉, 한 명 한 명 사용자의 응답시간보다는 서버가 처리하는 총 작업량을 줄임으로써 시스템의 전바적인 성능을 향상시켜 서버가 더 많은 일을 할 수 있도록 하는 것이다.

물론, 사용자의 응답 속도가 빠르다는 것은 서버에서 처리를 조금만 하도록 해서 빨라지는 경우도 많지만, 그렇지 않은 경우도 종종 있으므로 주의해야 한다.

인덱스의 장단점

장점

• 검색은 속도가 무척 빨라질 수 있다. (단, 항상 그런 것은 아니다.)
• 그 결과 해당 쿼리의 부하가 줄어들어서, 결국 시스템 전체의 성능이 향상된다.

단점

• 인덱스가 데이터베이스 공간을 차지해서 추가적인 공간이 필요해지는데, 대략 데이터베이스 크기의 10% 정도의 추가 공간이 필요하다.
• 처음 인덱스를 생성하는데 시간이 많이 소요될 수 있다.
• 데이터의 변경 작업(Insert, Update, Delete)이 자주 일어날 경우에는 오히려 성능이 많이 나빠질 수도 있다.

인덱스의 종류

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

• 테이블당 한 개만 생성할 수 있다.
• 행 데이터를 인덱스로 지정한 열에 맞춰서 자동 정렬한다.
• 클러스터형 인덱스의 생성 시에는 데이터 페이지 전체가 다시 정렬된다.
  • 대용량의 데이터가 입력된 상태라면 시스템 부하가 크다.
• 인덱스 자체의 리프 페이지가 곧 데이터이다.
• 보조 인덱스보다 검색 속도는 더 빠르다.
• 보조 인덱스보다 데이터의 입력/수정/삭제는 더 느리다.

보조 인덱스(Secondary Index)

• 테이블당 여러개를 생성할 수 있다.
• 보조 인덱스 생성 시에는 데이터 페이지는 그냥 둔 상태에서 별도의 페이지에 인덱스를 구성한다.
• 인덱스 자체의 리프 페이지는 데이터가 아니라 데이터가 위치하는 주소값(RID, Row ID)이다.
• 클러스터형 인덱스보다 검색 속도는 더 느리다.
• 클러스터형 인덱스보다 데이터의 입력/수정/삭제는 더 덜 느리다.

OLTP와 OLAP에 인덱스 생성
OLTP(On-Line Transcation Processing)는 INSERT/UPDATE/DELETE가 실시간으로 자주 발생되므로, 꼭 필요한 인덱스만 최소로 생성하는 것이 바람직하다. 하지만, OLAP(On-Line Analytical Processing)는 INSERT/UPDATE/DELETE가 별로 사용될 일이 없으므로 되도록 인덱스를 많이 만들어도 별 문제가 되지 않는다. 만약, 하나의 DB가 OLAP/OLTP 겸용으로 사용된다면 두 개를 분리하는 방법을 고려하는 것이 전반적인 시스템의 성능에 도움이 될 것이다.

제약 조건과 관련되는 인덱스

테이블 생성 시에 제약 조건 Primary Key 또는 Unique를 사용하면 자동으로 인덱스가 자동 생성된다.

자동으로 인덱스가 생성되는지 확인해보자.

• Primary Key
  

• Unique Key
  

• Primary key가 없고, Unique Key에 NOT NULL을 포함하면 클러스터형 인덱스가 된다.
  

• Unique Key에 NOT NULL을 포함해도 Primary Key가 있다면 클러스터형 인덱스는 테이블당 하나만 지정되므로 Primary Key를 우선적으로 클러스터형 인덱스가 생성된다.
  

• PRIMARY KEY로 지정한 열로 데이터가 오름차순 정렬된다.
  

• Primary key가 없고, Unique Key에 NOT NULL을 포함하면 클러스터형 인덱스가 된다는데 진짜인가? 오름차순 정렬이 되나?
  

제약 조건을 설정할 때 주의할 점
제약 조건의 설정은 대개 테이블의 생성 구문에서 하거나 Alter문으로 생성한다. 그러므로 아직 데이터가 입력되기 전에 Primary Key 및 Unique 키의 열에는 인덱스가 생성되어 있기 때문에, 인덱스 자체를 구성하는 시간이 걸리지 않는다. 하지만, 많은 데이터가 입력된 후에 Alter문으로 Unique나 Primary를 지정하면 인덱스를 구성하는 데 많은 시간이 걸릴 수도 있다. 즉, 업무시간에 함부로 기존에 운영되는 대량의 테이블의 인덱스를 생성하면 시스템이 엄청나게 느려져 심각한 상황이 발생될 수도 있으니 주의해야 한다. (최신의 MySQL 버전에서는 이전 버전에 비해 인덱스 생성 속도가 향상되기는 했지만, 그래도 데이터의 양에 따라서 몇 시간이나 그 이상의 시간이 걸리 수도 있다.)

인덱스의 내부 작동

• 클러스터형 인덱스
  루트 페이지를 읽어서 다음 검색할 곳을 찾는다.
  그렇게 리프 페이지까지 찾아가고 리프페이지를 읽어서 데이터를 출력 한다.

• 보조 인덱스
  루트 페이지를 읽어서 다음 검색할 곳을 찾는다.
  그렇게 리프 페이지까지 찾아가고 리프페이지를 읽어서 데이터 페이지의 RID를 알아낸다.
  RID를 이용해 데이터를 출력 한다.

• 클러스터형 인덱스와 보조 인덱스가 혼합되어 있을 경우
  보조 인덱스의 루트 페이지를 읽어서 다음 검색할 곳을 찾는다.
  그렇게 리프 페이지까지 찾아가고 리프페이지를 읽는다.
  클러스터형 인덱스가 없었다면 RID를 알아냈겠지만, 클러스터형 인덱스가 혼합되어 있기 때문에 클러스터형 인덱스의 키 값을 알아낸다.
  다시 클러스터형 인덱스의 루트 페이지를 읽어서 다음 검색할 곳을 찾는다.
  리프 페이지(==데이터 페이지)까지 찾아가고 리프페이지를 읽어서 데이터를 출력 한다.
  
  클러스터형 인덱스와 보조 인덱스를 분리해서 서로 관련 없이 구성한다면 검색에서는 더 우수한 성능을 보일 것이다. 하지만, 치명적인 단점 때문에 그렇게 구성되지 않는다. 클러스터형 인덱스의 페이지 분할이 발생될 때 리프 페이지가 재구성되어서 데이터 페이지의 번호 및 오프셋이 대폭 변경된다. 이로 인해 보조 인덱스 역시 많은 부분이 다시 구성되어야만 한다. 이는 엄청난 시스템의 부하를 발생시킬 소지가 있다. 이러한 이유때문에 서로 관련없이 구성해서 검색의 빠른 성능을 보장 받는것보다 보조 인덱스에서 검색하고나서 클러스터 인덱스를 검색하는 약간의 손해를 감수하고서라도 시스템의 부하를 줄이려고 구성하는 것이다.

혼합 인덱스에서 인덱스 지정 시 고려사항
클러스터형 인덱스를 지정할 열의 자릿수가 크다면 보조 인덱스에 저장되어야 할 양도 더불어서 많아진다. 그러면, 차지하는 공간이 자연히 커질 수밖에 없다. 결국 보조 인덱스와 혼합되어 사용되는 경우에는 되도록이면 클러스터형 인덱스로 설정할 열은 적은 자릿수의 열을 선택하는 것이 바람직하다.

인덱스의 성능

약 30만 개의 데이터로 성능을 테스트 해보자.

특정 값 조회

• 인덱스가 없을 때
  
  Full Table Scan
  2093 - 1060 = 1033

• 클러스터형 인덱스만 있을 때
  
  클러스터형 인덱스 사용
  878 - 875 = 3

• 보조 인덱스만 있을 때
  
  보조 인덱스 사용
  871 - 866 = 5

범위 조회

• 인덱스가 없을 때
  
  Full Table Scan
  1930 - 872 = 1058

• 클러스터형 인덱스만 있을 때
  
  Index Range Scan
  872 - 867 = 5

• 보조 인덱스만 있을 때
  
  Index Range Scan
  query_cost = 449.81
  1501 - 868 = 633

• 클러스터형 인덱스에서 전체 데이터를 읽기
  
  Index Range Scan
  1752 - 864 = 888
  Data Read: 19M

인덱스 힌트는 강제로 사용하는 'USE INDEX(인덱스이름)'와 강제로 사용하지 못하게 하는 'IGNORE INDEX(인덱스이름)' 정도만 알고 있으면 된다.

• IGNORE INDEX(PRIMARY) 힌트를 사용해서 전체 데이터를 읽기
  
  Full Table Scan
  1754 - 866 = 888
  Data Read = 12M

  비록 이 경우에는 데이터를 읽은 양(Data Read)이 인덱스를 사용한 것보다 적기 때문에, 전체 테이블 검색이 효율적으로 보일 수 있다. 하지만, 전체적인 효율은 다양한 시스템의 상황이나 데이터 분포 등에 영향을 받을 수 있기 때문에, 읽은 양이 적다고 반드시 효율적이라고 보기는 어렵다. 이 예에서도 인덱스를 사용한 쿼리가 'Cost Info' 부분의 읽기(Read) 부분만 비교하면 적게 나오기 때문에 효율적이라고 볼 수도 있다.

• IGNORE INDEX(idx_emp_no) 힌트를 사용해서 전체 데이터를 읽기
  
  Full Table Scan
  query_cost = 30188.05
  1929 - 871 = 1058

• 범위를 늘려서 쿼리를 성능 테스트
  
  Full Table Scan
  1937 - 867 = 1070
  인덱스가 있지만 인덱스를 사용하지 않고 전체 테이블을 검색했다.
  MySQL은 인덱스와 Full Table Scan 중 더 빠른 방법을 판단하여 사용한다.

• WHERE 조건이 없는 전체 데이터를 조회
  
  Full Table Scan
  1761 - 873 = 888