인덱스(index)란? 인덱스 알고쓰기!

인덱스란?

인덱스란 데이터의 저장(INSERT, UPDATE, DELETE) 의 성능을 희생하고 그 대신에 데이터의 읽기 속도를 높이는 테이블의 동작속도(조회)를 높여주는 자료구조이다.

따라서 사이트의 성능개선의 가장 기본은 효율적인 인덱스 설정이다.

인덱스를 사용하는 SQL을 만들어 효율적으로 사용한다면 매우 빠른 응답 속도를 얻을 수 있고, 쿼리의 부하가 줄어들기 때문에 시스템 전체 성능이 향상되는 효과를 얻는다.

그러나 인덱스 자체 역시 하나의 데이터 덩어리 이기 때문에, 데이터베이스에 전체 크기의 10%나 되는 추가적인 공간을 할당해줘야 하고, 잘못 사용할 경우 성능이 오히려 크게 떨어질 수 있다는 단점이 있다.

 (ex. 변경 작업이 자주 일어나는 경우, 인덱스가 적절하지 않은 경우)

 

인덱스 저장방식 (알고리즘)

데이터 저장 방식별로 구분하는 것은 사실 상당히 많은 분류가 가능하겠지만, 가장 보편적인 방식은 B-Tree 방식 알고리즘을 다루겠다.

인덱스는 B-tree구조를 사용하며 데이터가 정렬된 상태를 유지한다. 때문에 어떤 값에 대해서도 같은 시간에 결과를 얻을 수 있게 된다 (트리의 높이가 다른 경우 약간의 차이는 있을 수 있지만 O(logN)의 시간을 가진다). 

 

인덱스 타입 종류

인덱스의 타입은 크게 두가지로 나뉘는데 Primary(클러스터) 인덱스 와 Secondary(보조) 인덱스로 나뉘어 진다.

클러스터 인덱스는 처음부터 정렬이 되어있는 영어 사전 과 같은 개념이고, 보조 인덱스는 책 뒤의 찾아보기 의 개념과 비슷하다.

 

클러스터 인덱스 (Primary Index)

• 클러스터형 인덱스는 테이블 전체가 정렬된 인덱스가 되는 방식
  그래서 클러스터형 인덱스 생성 시에는 데이터 페이지 전체가 다시 정렬된다.
• 하지만 이러한 정렬 특징 때문에, 이미 대용량의 데이터가 입력된 상태라면 클러스터형 인덱스 생성은 심각한 시스템 부하를 줄 수 있다.
• 한개의 테이블당 한개만 만들 수 있다. 특정 컬럼을 PK로 지정하면 클러스터형 인덱스를 생성, 혹은 Unique + Not null로 지정해도 클러스터형 인덱스를 생성, PK가 우선으로 선정됨
• 본래 인덱스는 생성 시 데이터들의 배열정보를 따로 저장하는 공간을 사용하나, 클러스터 인덱스는 따로 저장하는 정보 공간을 적게 사용하면서 테이블 공간 자체를 활용한다.
• 인덱스 자체의 리프 페이지가 곧 데이터이기 때문에 인덱스 자체에 데이터가 포함되어있다고 볼 수 있다.
• 보조 인덱스 보다 검색 속도는 더 빠르지만 입력/수정/삭제는 더 느리다.
• MySQL에서는 Primary Key가 있다면 Primary Key를 Clustered INDEX로, 없다면 UNIQUE 하면서 NOT NULL인 컬럼을, 그것도 없으면 임의로 보이지않는 컬럼을 만들어 Clustered Index로 지정한다.

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

• 이 인덱스는 보조 인덱스 (Secondary index) 라고도 불린다. 
• 별도의 페이지에서 인덱스를 구성하니, 클러스터와는 달리 자동 정렬을 하지 않는다.
• 개념적으로는 후보키에만 부여 할 수 있는 인덱스다.
  (후보키 : 고유 식별 번호, 주민번호 같이 각 데이터를 인식할 수 있는 최소한의 고유 식별 속성 집합)
• 보조 인덱스의 생성시에는 데이터 페이지는 그냥 둔 상태에서 별도의 페이지에 인덱스를 구성하여 실제 데이터를 가지고 있지 않다.
• 클러스터형 보다 검색 속도는 더 느리지만 데이터의 입력/수정/삭제는 덜 느리고 여러 개 생성할 수 있지만 많을수록 오히려 성능을 떨어뜨릴 수 있다. (테이블당 3~4개 권장)
• 각 데이터에 대해서 고유 값 (unique) 들이 있는 목록에 생성 할 수 있는 인덱스다. (unique key)

인덱스 설계 핵심 TIP

인덱스의 원리를 알았으니 실전으로 인덱스를 효율적으로 사용하는 설계의 핵심을 알아보자.

 

효율적인 인덱스 설계

• WHERE 절에 사용되는 열 (WHERE 절에 사용되는 열이라도 자주 사용해야 가치가 있음)
• 중복도가 낮은 컬럼에 INDEX를 설정해야 한다
• SELECT 절에 자주 등장하는 컬럼들을 잘 조합해서 INDEX로 만들어두면 INDEX 조회 후 다시 데이터에서 조회할 필요가 없으므로 빠르게 검색이 가능하다.
• JOIN절에 자주 사용되는 열에는 인덱스의 효율이 좋음.
• ORDER BY 절에 사용되는 열은 데이터 페이지가 자동 정렬됐기 때문에 클러스터형 인덱스가 유리
• 외래키는 자동으로 INDEX가 걸린다.

 

금지해야 할 인덱스 설계

• 조회보다 대용량 데이터가 자주 입력되는 경우,
  클러스터형 INDEX 경우 빈번한 페이징이 일어나기 때문에 부하가 생긴다.
  따라서 INDEX 필요한 경우 primary(클러스터) 대신 unique만 설정하는 게 좋을 수 있다.
• 데이터 중복도가 높은 열은 INDEX 효과가 없다.
  예를 들어 성별 컬럼에 M, F만 있다고 하면 INDEX를 안쓰는 게 낫다. INDEX를 봤다가 데이터를 봤다가 하여 성능이 느려진다
  따라서 일반 보조 인덱스보다 unique 보조 인덱스가 빠른 이유가 이것이다.
• 자주 조회되지 않으면 성능 저하를 초래할 수 있음. (INSERT만 주구장창 하는 시스템이라면, 사용해보지도 못하고 데이터 입력에 걸리는 작업량만 많아진다.

 

인덱스 주의할 점(금지와 연계)

• 데이터 변경(삽입, 수정, 삭제) 작업이 얼마나 자주 일어나는지 고려해야 함.
• 단일 테이블에 인덱스가 많으면 속도가 느려질 수있다. (테이블당 3~4개 권장)
• 검색할 데이터가 전체 데이터의 20% 이상이라면, MySQL에서 인덱스를 사용하지 않음. (강제로 사용할 시 성능 저하를 초래할 수 있음)
• 사용하지 않는 인덱스는 제거하는 것이 바람직함. 
• 테이블에 클러스터형 인덱스가 아예 없는 것이 좋은 경우도 있음
• 테이블이 가지고 있는 전체 데이터양의 10~15% 이내의 데이터가 출력될때만 INDEX를 타는게 효율적

 

인덱스가 안 되는 쿼리

INDEX가 있다고 모든 쿼리에서 INDEX를 활용하는 것은 아니다.

• 인덱스 입힌 컬럼을 가공
  • WHERE SUBSTR(컬럼명, 1,4) = ‘2019’
  • 해결 → WHERE 컬럼명 LIKE ‘2019%’
• 인덱스 컬럼을 OR절 사용 비교는 비교해야할 Row를 늘리는 역할을 하여 풀스캔이 발생할 확률이 높다.
  • WHERE 컬럼명 = 'A' OR 컬럼명 = 'B'
  • 해결 →  WHERE 컬럼명 IN ('A','B')
• 인덱스가 있는 열 이름에는 함수나 연산을 가함
  • WHERE count*10=100
  • 해결 →  WHERE count=100/10
• 인덱스 컬럼의 묵시적 형변환(같은 타입으로 비교해야함)
  • WHERE 컬럼명 = ‘20190730’
  • 해결 → WHERE 컬럼명 = TO_DATE(‘20190730’, ‘YYYYMMDD’)
• 인덱스 컬럼 부정형 비교.
  • WHERE 컬럼명 != ‘10’
  • 해결 → WHERE 컬럼명 IN(‘20’, ‘30’)
• LIKE %가 앞에 위치.
  • WHERE 컬럼명 LIKE ‘%2019’
  • 해결 → or 조건 사용 WHERE 컬럼명 IN(‘102019’,‘202019’,‘302019’)
• between, like, <, > 등 범위 조건은 해당컬럼은 인덱스를 타지만, 그 뒤 인덱스 컬럼들은 인덱스가 사용되지 않음.

 

ORDER BY 와 GROUP BY에 대한 인덱스

INDEX는 ORDER BY와 GROUP BY에도 영향을 끼치는데 다음과 같은 경우에는 INDEX를 타지 않는다.

• ORDER BY 인덱스컬럼1, 컬럼2 : 복수의 키에 대해서 ORDER BY를 사용한 경우
• WHERE 컬럼1='값' ORDER BY 인덱스 컬럼 : 연속하지 않은 컬럼에 대해 ORDER BY를 실행한 경우
• ORDER BY 인덱스컬럼1 DESC, 인덱스컬럼2 ASC : DESC와 ASC를 혼합해서 사용한 경우
• GROUP BY 컬럼1 ORDER BY 컬럼2 : GROUP BY와 ORDER BY의 컬럼이 다른 경우
• ORDER BY ABS(컬럼) : ORDER BY 절에 다른 표현을 사용한 경우

 

제약 조건을 설정할 때 인덱스 성능 주의점

보통 제약 조건의 설정은 대개 테이블의 생성 구문(create)에서 하거나, 테이블 생성하고 뒤에 alter문으로 따로 진행한다.
그러므로 아직 테이블에 데이터가 입력되기 전에 primary key 및 unique 키의 열에는 인덱스가 생성되어져 있기 때문에, 인덱스 자체를 구성하는 시간이 걸리지 않는다.

하지만 많은 데이터가 입력된 후에 alter문으로 unique 나 primary를 지정하면 인덱스를 구성하는데 많은 시간이 걸릴 수도 있다. (페이지 분할하고 다시 정렬하고 하니까)
즉, 업무시간에 함부로 기존에 운영되는 대량의 테이블의 인덱스를 생성하면 시스템이 엄청나게 느려져 심각한 상황이 발생될 수도 있으니 주의해야 한다. (데이터의 양에 따라서 몇 시간이나 그 이사의 시간이 걸릴 수도 있다.)

인덱스 정렬

• 인덱스를 생성할 때 인덱스에 포함되는 필드의 정렬 방식을 설정할 수 있다.
• DESC 키워드를 사용하면 내림차순으로 정렬되며, ASC 키워드를 사용하면 오름차순으로 정렬된다.

sql

CREATE INDEX 인덱스이름
ON 테이블이름 (필드이름 DESC) -- 인덱스 지정한 필드이름은 내림차순으로 정렬됨

CREATE INDEX 인덱스이름
ON 테이블이름 (필드이름 ASC) -- 인덱스 지정한 필드이름은 오름차순으로 정렬됨

다중 컬럼 인덱스

다중 컬럼 인덱스는 두개 이상의 필드를 조합해서 생성한 INDEX이다. (MySQL은 INDEX에 최대 15개 컬럼으로 구성 가능)

1번째 조건과 이를 만족하는 2번째 조건을 함께 INDEX해서 사용한다. 

다중 컬럼 인덱스는 단일 컬럼 인덱스 보다 더 비효율적으로 INDEX/UPDATE/DELETE를 수행하기 때문에 신중해야한다.

때문에 가급적 UPDATE가 안되는 값을 선정 해야한다.

email, id 컬럼에 각각 단일 인덱스가 걸려있는 경우  어떤 컬럼의 수가  더 빠르게 검색되는지 판단 후 빠른쪽을 먼저 검색하고 그다음 다른 컬럼을 검색하는데 다중 컬럼 인덱스의 경우 INDEX를 저장할 때 email,id를 같이 저장하기 때문에 같이 색인하고 검색이 더빠르다

 

실제로 조회 쿼리 사용시 인덱스를 태우려면 최소한 첫번째 인덱스 조건은 조회조건에 포함되어야 한다.
첫번째 인덱스 컬럼이 조회 쿼리에 없으면 인덱스를 타지 않는다.

 

주의) 그렇지만 다중 컬럼 인덱스를 순서가 안맞거나 이빨빠지게 조회하면 인덱스의 효과를 기대하기 어렵다.

최근엔 개선됐다고 하지만 맞춰주는게 맘편함!