인덱스 - 1

인덱스 이론

개념

DBMS에서의 인덱스는 저장 성능을 희생하고 검색 성능을 높이는 기능입니다.

1000페이지가 넘는 토비의 스프링 같은 책과 비유하자면 다음과 같습니다

• 책의 맨 처음이나 끝에 존재하는 색인 = DBMS의 인덱스
• 책의 내용 = DBMS의 데이터 파일
• 책의 색인을 통해 알 수 있는 페이지 번호 = DBMS의 데이터 파일에 저장된 레코드의 주소
• 책의 색인은 사전 순으로 정렬 = DBMS의 인덱스도 일정 기준으로 정렬 가능

종종 책의 목차를 인덱스로 비유하는데 정확히는 아닙니다. 왜냐하면 책의 목차는 문자열 등 특정 기준으로 정렬되어 있지 않기 때문입니다.

색인의 특징

색인의 특징을 자세히 알아보겠습니다.

• 책에 있는 모든 용어가 아닌 자주 찾아보는 핵심 용어만 간추려서 정리했습니다.
  • 효율적인 검색을 위하여 데이터베이스에서도 자주 찾는 컬럼 즉, 검색 조건에 자주 등장하는 컬럼을 인덱스로 생성해놓을 수 있습니다.
• 알파벳(a, b, c ,...)순서 또는 한글(ㄱ, ㄴ, ㄷ, ...)순서 등의 형태로 정렬되어있습니다.
  • 정렬은 제일 중요한 부분입니다. 순서를 갖도록 정렬해두어 특정 데이터(ex. "memory")를 찾을 때 접근하는 속도를 굉장히 빠르게 할 수 있습니다. 뿐만 아니라 원하는 데이터가 더 이상 없다는 것을 알 수 있습니다.
    • 알파벳 순서로 정렬되어있으면 'm'로 시작하는 용어를 모아둔 곳으로 바로 찾아가면 됩니다.
    • 'm'으로 시작했는데 "member" 다음에 "merge" 라는 단어가 있으면 알파벳 순서상 그 사이에 "memory"가 있어야 하지만 없으므로 색인에 원하는 데이터("memory")가 없다는 것을 알 수 있기 때문입니다.
• 용어를 직접 요약해서 설명하기보다 어느 페이지에 있는지 간단한 페이지 번호만 나타냅니다.
  • 어느 페이지(위치)에 있는지만 알려줌으로써 색인이 단순하게 표현되어 색인 페이지 수를 줄일 수 있습니다.
• 책 전체는 수 백 페이지지만 색인 정보가 있는 페이지는 몇 페이지 안됩니다.
  • 색인 페이지가 극단적으로 많아져 총 600페이지 짜리 책에 색인 페이지만 200페이지가 된다고 가정해봅시다. 이때, 특정 용어를 찾기 위해서 봐야 할 색인 페이지는 당연히 많아질 수밖에 없고, 경우에 따라서 그냥 전체 페이지를 뒤져 찾는 게 빠를 수도 있습니다. 그렇기 때문에 색인의 크기 역시도 중요합니다.
• 너무 여러 곳에 등장하는 용어는 색인에 나타내지 않습니다.
  • 모든 페이지에 "인덱스"라는 단어가 들어간다고 치면 색인 페이지에 1p, 2p, 3p, ... 이렇게 되고 총 600p 라면 색인 페이지 한 면을 혼자 차지할 것입니다. 이렇게 되면 색인의 본래 의도에 적합하지 않게 됩니다.

 

인덱스가 필요한 예시

서점에서 책 찾기

서점에 책을 구분할 수 있는 번호, 이름, 카테고리, 위치를 저장한 테이블이 있습니다. 

 

SELECT name, location
FROM book_store
WHERE category = 'java'

어떤 사람이 카테고리가 'java'인 책을 찾을려면 위와 같은 쿼리를 작성합니다.

 

현재는 인덱스가 없으므로, 처음부터 끝까지 full scan을 수행하면서 Java 책을 찾아야 하는데, 이러한 방법은 비효율적입니다.

 

CREATE INDEX index_category ON book_store(category)

데이터를 찾는 기준인 category와 DB가 쉽게 찾아갈 수 있도록 rowid를 포함한 2개의 칼럼으로 인덱스를 만듭니다.

 

※ 인덱스도 결국 하나의 테이블입니다. 모든 칼럼을 인덱스로 넣어버리면 원본 테이블과 같아지므로 공간 낭비가 발생합니다.

 

💡 PK를 지정하지 않은 테이블에 인덱스를 걸면 어떻게 될까?
보통 B-Tree 인덱스를 많이 사용하지만 Mysql의 InnoDB의 실질적인 인덱스 구조는 B-Tree 인덱스와 데이터 파일을 관리하는 방법이 다릅니다. B-Tree와 관련된 자세한 사항은 추후 정리하겠습니다.

InnoDB의 인덱스 구조는 기본적으로 데이터를 저장하고 인덱싱하기 위해 Primary Key를 요구합니다.
명시적인 PK가 없는 경우 InnoDB는 다음과 같은 우선 순위로 칼럼을 선택합니다.
1. NOT NULL 속성을 가지고 있는 유니크 인덱스 중에서 첫번째 인덱스를 클러스터 키(기본 키)로 선택
2. 자동으로 유니크한 값을 가지고 증가하는 컬럼(6 Byte)의 rowid를 내부적으로 추가한 후, 클러스터 키로 선택
2번째 우선순위로 인해 생성된 칼럼 같은 경우 사용자에게 노출되지 않기 때문에 쿼리에서 명시적으로 사용할 수 없습니다. 또한 이렇게 생성된 칼럼은 Int 혹은 BigInt보다 큰 값입니다. 유니크 인덱스가 전혀 없는 InnoDB 테이블에서는 아무런 의미없는 숫자값으로 클러스터링 되고 있는 것이며, 이것은 우리게에 아무런 혜택을 주지 않습니다. 

InnoDB 테이블에서 프라이머리 키는 클러스터링 인덱스의 기준이 됩니다. 클러스터링 인덱스는 (InnoDB 테이블별로) 단 한번만 가질 수 있는 엄청난 혜택이므로 가능하다면 프라이머리 키를 명시하는 것이 좋습니다.

Auto Increment로 지정된 칼럼을 PK로 쓰면 UUID와 비교하여 아래와 같은 장점이 있습니다.
- UUID와 같은 랜덤한 값으로 사용한다면 Row가 insert될 때 마다 재정렬을 해야합니다. 하지만 Auto Increment는 재정렬할 필요가 없어 삽입이 빠릅니다.
- UUID를 사용하는 것에 비해 공간이 절약됩니다.
> 최근에는 DBMS가 발전되고 하드웨어가 좋아져서 unique의 무결성만 보장할 수 있다면 UUID 방식을 사용해도 큰 문제는 없다고 합니다.

하지만 분산형 환경일때 각 DB에 동기화 설정이 필요하다면 데이터 일관성에 문제가 있다는 단점이 존재한다고 합니다. 하지만 일반적인 분산 DB환경의 경우 write 하는 master는 하나를 두고 read 하는 여러 대의 slave DB에 그 값을 동기화 하도록 하기 때문에 별다른 문제가 발생하지 않습니다. 만약 write를 분산처리 하면서 서로 동기화 되는 경우에도 master 2대 까지는 각각 홀/짝수로 증가하도록 해서 해결할 수 있습니다.

 

category	id
…		…
C++		10000
…	…
java		1
java		4
java		4222
java		9999
javascript	3
…		…
python		2
python		4223

생성한 인덱스 테이블이며, category 부분이 문자열 순서대로 정렬되어 있습니다.

이렇게 세팅을 하면, Java 책이 처음으로 등장하는 시점에서 더 이상 나오지 않는 시점까지만 탐색을 수행하면 됩니다. 전체 Java 책 정보를 알고 싶을 때는 여기서 탐색한 row_id를 가지고 실제 쿼리를 날리면 되며, 탐색 과정에서 B-Tree 자료 구조의 이점 덕분에 탐색 속도도 빠릅니다.

인덱스에서 찾아낸 rowid값인 1, 4, 4222, 9999를 데이터베이스에서 조회하여 원하는 데이터를 찾을 수 있습니다.

SELECT name, location
FROM book_store
WHERE row_id IN (1, 4, 4222, 9999)

category = 'java'로 검색하는 것과 무슨 차이가 있을까요?

row_id는 데이터를 삽입할 때 DB 내부에서 자동으로 생성하는 값(Auto increment가 설정된 칼럼)이며,

해당 row의 고유한 주소 값을 가리킵니다. 따라서, 해당 데이터의 위치가 어디있는지 일일이 찾지 않아도 row_id를 통해 DB에서 가장 빠르게 데이터를 찾아낼 수 있습니다.

💡 인덱스를 만드는 이유는 바로 이 rowid 를 기준으로 데이터를 탐색할 수 있도록 유도해서 쿼리의 성능을 향상 시키기 위함입니다.

 

언제 인덱스를 사용해야 할까?

• 규모가 작지 않은 테이블
• INSERT, UPDATE, DELETE가 자주 발생하지 않는 컬럼
• JOIN이나 WHERE 또는 ORDER BY, GROUP BY에 자주 사용되는 컬럼
• 데이터의 중복도가 낮은 컬럼 (카디널리티가 높은 컬럼)
  • 예를 들어 성별이라는 컬럼이 있다고 생각해봅시다. (성별은 남자와 여자만 있다고 가정)
  • 이때 성별에 인덱스를 걸어 봤자 탐색할 수 있는 값이 2개 밖에 없으므로 하나의 성별이 붙은 데이터를 검색하는 데 운이 없으면 Full Scan을 할 수도 있습니다.
  • 또한, 인덱스는 내부적으로 Key, Value의 이진 트리 형태로 데이터를 저장하는데, Key가 중복되어 여러 개 존재하면 검색할 대상이 증가합니다.
  • 이러한 이유로 데이터의 중복도가 낮아서 분포도가 높은 컬럼에 대해 인덱스를 사용해야 합니다.

 

인덱스의 장점과 단점

장점

• 테이블을 조회하는 속도와 그에 따른 성능을 향상 시킬 수 있습니다.
• 전반적인 시스템의 부하를 줄일 수 있습니다.

단점

• 인덱스를 관리하기 위해 DB의 약 10%에 해당하는 저장 공간이 필요합니다.
• 인덱스를 관리하기 위해 추가 작업이 필요하고 인덱스를 타는지, 안타는지 고려해서 쿼리를 작성해야 합니다.
• 인덱스를 잘못 사용할 경우 오히려 성능이 저하되는 역효과가 발생할 수 있습니다.
  • 만약 INSER, DELETE, UPDATE가 빈번한 속성에 인덱스를 걸게 되면 인덱스의 크기가 비대해져서 성능이 떨어집니다.
  • 이러한 이유 중 하나는 DELETE와 UPDATE 연산 때문입니다.
  • UPDATE와 DELETE는 기존 인덱스를 삭제하지 않고, '사용하지 않음' 처리를 하고 갱신된 데이터에 대해 인덱스를 추가합니다.
  • 만약 어떤 테이블에 UPDATE와 DELETE가 빈번하게 발생한다면 실제 데이터는 10만 건이지만, 인덱스는 100만 건이 넘어가게 되어 SQL문 처리 시 비대해진 인덱스에 의해 오히려 성능이 떨어지게 되는 것입니다.

 

장점보다 단점이 많은데 왜 인덱스를 써야할까요? 왜냐하면 일반적인 OLTP(Online Transaction Processing) 시스템에서 데이터 조회 업무가 90% 이상이기 때문입니다. 결국, 조회 업무의 검색 속도 향상은 시스템 부하를 감소시킵니다.

 

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

 

클러스터형 인덱스

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

보조 인덱스

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

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

 

클러스터  + 보조 인덱스

클러스터와 보조 인덱스를 같이 사용할 경우 보조 인덱스의 rowid는 클러스터드 인덱스의 키값을 가지게 됩니다.

예를들어 보조 인덱스가 걸린 이름 칼럼의 '유병수'라는 이름을 조회해봅시다.

박훈 < 유병수 < 이수선 이므로 넌 클러스터드 중간 레벨의 2페이지로 가게 됩니다.

유병수를 찾으니 유병수는 클러스터드 인덱스 10번 키값을 가지고 있습니다.

10이라는 클러스터 인덱스로 가니 데이터페이지 4에 있다고 합니다. 해당 페이지로 가서 '유병수'를 찾습니다.

 

UPDATE, DELETE에서의 인덱스 활용

인덱스를 활용하면, 데이터를 조회하는 SELECT 외에도 UPDATE나 DELETE의 성능이 함께 향상됩니다.

이유는 해당 연산을 수행하려면 해당 대상을 조회해야만 작업을 할 수 있기 때문입니다.

※ 인덱스가 없는 컬럼을 조건으로 update, delete를 하게 되면 굉장히 느려 많은 양의 데이터를 삭제 해야하는 상황에선 인덱스로 지정된 컬럼을 기준으로 진행하는것을 추천드립니다.

// Mang이라는 이름을 업데이트해주기 위해선 Mang을 조회해야한다.
UPDATE USER SET NAME = 'MangKyu' WHERE Name = 'Mang';

where 조건시 주의할 점이 있는데 하단의 인덱스 조회시 주의 사항에서 추가로 설명하겠습니다.

 

인덱스 특징

1. Index의 키의 크기는 되도록 작게 설계해야 성능에 유리 합니다.

• 디스크에 데이터를 저장하고 읽는 기본 단위는 페이지 또는 블록 단위입니다.
• B-Tree의 각 노드가 관리하는 인덱스 데이터의 단위 또한 페이지입니다. (InnoDB 페이지 기본 값 = 16KB)
  • 페이지에 작은 데이터를 한 개만 저장하더라도 한개 페이지를 차지하게 됩니다. 
  • innodb_page_size 시스템 변수를 이용해 4KB ~ 64KB 사이의 값을 선택할 수는 있습니다.
• 각 노드(16KB)에 담길 수 있는 인덱스 (key, value)의 개수는 각 노드가 가질 수 있는 인덱스 키 값의 크기와 연관이 되어있습니다. 
  • 인덱스 키의 크기가 16 Byte 라고 하고, 자식노드(Branch, Leaf)의 주소가 담긴 크기가 12 Byte 정도로 잡아본다고 가정합니다.
  • 16*1024 / (16+12) = 585로 인해 하나의 페이지에는 585개가 저장될 수 있습니다.
  • 하지만 인덱스의 키가 32 byte로 커지게 된다면?
  • 16*1024 / (32+12) = 372로 되어 372개만 한 페이지에 저장할 수 있게 됩니다.
  • 조회 결과로 500개의 row를 읽을때 16byte일때는 1개의 페이지에서 다 조회가 되지만, 32byte일때는 2개의 페이지를 읽어야 하므로 이는 성능 저하가 발행하게 됩니다.

2. 활용도가 높을 수록 인덱스 설정에 좋은 컬럼입니다.

해당 컬럼이 실제 작업에서 얼마나 활용되는지에 대한 값입니다.
수동 쿼리 조회, 로직과 서비스에서 쿼리를 날릴 때 WHERE 절에 자주 활용되는지를 판단하면 됩니다.

추가로 조인의 연결고리가 되는 칼럼을 INDEX로 구성하는게 좋습니다.

 

3. MySQL InnoDB는 nullable 컬럼을 인덱스 키로 사용할 수 있습니다. 다만 인덱스 생성 비용이라든지 문제가 있을 수 있으니 자제하는 것이 좋다고 한다. 자세한 사항은 링크를 확인 하시면 됩니다.

 

인덱스 사용 기법

디스크 I/O를 최소화하고 대부분의 연산을 데이터베이스 버퍼에서 처리할 수 있도록 질의 처리 과정에서 액세스하는 페이지 수를 최소화하는 것이 튜닝의 핵심입니다. 액세스하는 페이지 수가 적으면 자연스럽게 물리적으로 디스크에서 읽어야 할 페이지 수도 줄기 때문에, 데이터베이스 버퍼 히트율(DB buffer hit ratio)이 높아져서 데이터베이스의 전체적인 성능이 높아집니다. 결론은 인덱스 스캔 과정에서 액세스해야 할 페이지 수를 줄일 수 있는 기법을 알아야 합니다.

 

 

1. 다중 컬럼 인덱스 생성시 순서를 고려해야 합니다.

SELECT * FROM dept_emp WHERE dept_no='d001' AND emp_no >= 1000;

# 인덱스를 생성하는 컬럼 순서만 다름
# A.(dept_no, emp_no)
# B.(emp_no, dept_no)

위와 같은 쿼리가 쓰인다고 가정하고 두 가지의 인덱스를 생성했을 때 효율을 비교해봅시다.

 

A의 경우 'dept_no'로 우선 정렬되어 있기 때문에 'd001'의 값을 갖는 인덱스를 찾고 emp_no가 1000보다 큰 부분을 찾으면 'dept_no'가 바뀌지 않을 때까지 쭉 읽어버리면 끝납니다.

이처럼 작업의 범위를 결정하는 조건을 작업 범위 결정 조건이라 합니다.

 

B의 경우 'emp_no'로 우선 정렬되어 있기 때문에 1000보다 큰 부분을 찾은 후 쭉 읽으면서 'dept_no'가 'd001'이 맞는지 일일이 대조해보면서 버릴 건 버리고 읽을 건 읽고 해야 끝납니다.

이처럼 비교 작업의 범위를 줄이지 못하고 단순히 거름종이 역할만 하는 조건을 필터링 조건이라 합니다.

 

※ A, B를 통해 정렬이 앞선 컬럼에 의존하여 정렬된다는 것을 확인할 수 있습니다. 즉 앞선 칼럼으로 인덱스를 찾는 범위를 좁히는게 중요합니다.

 

케이스 A 처럼 작업 범위를 결정하는 조건이 많을 수록 쿼리의 처리 성능을 높입니다.

 

2. 선택도(기수성)가 높은 칼럼순으로 지정하는게 좋습니다.

 Index scan이 Table 순차 sacn보다 항상 빠르지 않습니다. 보통 선택도(selectivity)가 5~10% 이내인 경우 Index scan이 우수합니다.

S (selectivity) = d/n
d = 서로 다른 값의 수 (# of distinct values)
n = 테이블의 전체 레코드 수 

선택도는 아래와 같이 계산합니다.

= (컬럼의 특정 값의 row 수 * 100) / 테이블의 총 row 수
= (컬럼의 값들의 평균 row 수 * 100) / 테이블의 총 row 수

예를 들어, 10개 rows를 가지는 ‘학생’ 테이블에 ‘학번’, ‘이름’, ‘성별’ 컬럼이 있다고 해봅시다.
학번은 고유하고, 이름은 2명씩 같고, 성별은 남녀 5:5 비율입니다.

• ‘학번’의 선택도 = (1 * 100) /10 = 10%
  • SELECT COUNT(1) FROM '학생' WHERE '학번' = 1; (모두 고유하므로 특정 값: 1)
• ‘이름’의 선택도 = (2 * 100) /10 = 20%
  • SELECT COUNT(1) FROM '학생' WHERE '이름' = "김철수"; (2명씩 같으므로 특정 값: 2)
• ‘성별’의 선택도 = (5 * 100) / 10 = 50%
  • SELECT COUNT(1) FROM '학생' WHERE '성별' = F; (5명씩 같으므로 특정 값: 5)

하단 쿼리 결과 값이 1에 가까울수록(0.9이상 권고) 인덱스 칼럼으로 적합합니다.

SELECT count(distinct INDEX_COLUMN) * 100 / count(*)
FROM TABLE;

 

3. 커버링 인덱스

커버링 인덱스라는건 어떤 SQL에서 원하는 출력 데이터와 조건 데이터가 모두 인덱스에 존재 하는 경우를 이야기 합니다. B-Tree 스캔으로만 모든 데이터를 알 수 있기때문에 인덱스에 있는 주소값을 참조하여 데이터 블록으로 가지 않고 출력까지 가능 한 경우를 이야기 합니다.

CREATE INDEX `idx_member_email_id_age` ON `member` (email, id, age);

SELECT email, id, age
FROM member
WHERE email LIKE 'probitanima1%'
GROUP BY id, age;

확인결과 Using Index를 확인할 수 있습니다.

 

커버링 인덱스에 대한 자세한 내용은 추후 포스팅 하겠습니다.

 

인덱스 조회시 주의 사항

1. B-Tree인덱스 사용시 다음가 같은 경우에 인덱스를 사용할 수 있습니다.

• 일치 검색(=) : 되도록 동등비교를 사용하는게 좋습니다.
• 앞부분 일치를 이용한 검색(like '값%')

다음과 같은 경우는 인덱스를 사용할 수 없습니다.

• 뒷부분 일치('%값' 혹은 '%값%')를 이용한 검색
• 부등호<,>
• '!=', IS NOT NULL, NOT BETWEEN과 같은 부정형 조건

2. AND연산자는 각 조건들이 읽어와야할 ROW수를 줄이는 역할을 하지만, or 연산자는 비교해야할 ROW가 더 늘어나기 때문에 풀 테이블 스캔이 발생할 확률이 높습니다.

 

3. 인덱스로 사용된 컬럼값 그대로 사용해야만 인덱스가 사용됩니다.

• 인덱스는 가공된 데이터를 저장하고 있지 않습니다.
• where salary * 10 > 150000; 는 인덱스를 못타지만, where salary > 150000 / 10; 은 인덱스를 사용합니다.
• 컬럼이 문자열인데 숫자로 조회하면 타입이 달라 인덱스가 사용되지 않습니다. 정확한 타입을 사용해야만 합니다.

4. 검색할 데이터가 전체 데이터의 20% 이상이라면, MySQL에서 인덱스를 사용하지 않음. (강제로 사용할 시 성능 저하를 초래할 수 있음) - 전체 페이지의 대부분을 읽어야 하고, 인덱스 관련 페이지도 읽어야 해서 작업량이 큼)

 

5. 사용하지 않는 인덱스는 제거하는 것이 바람직함. (실무에서 사용하지 않는 보조 인덱스를 몇개 삭제했을 때 성능이 향상되는 경우도 많음)

 

 

다음은 인덱스를 사용할 수 있는 쿼리 튜닝 예시 입니다.

인덱스 실습

 

 

 

출처

[mysql] 인덱스 정리 및 팁

Table 작성 시 PK를 무조건 사용해야 하는 이유

MySQL 인덱스 구조와 원리의 이해

인덱스란.md

[면접 대비] 데이터베이스 - 인덱스

인덱스 정리 및 팁

MySQL 쓰면서 하지 말아야 할 것 17가지

Mysql AUTO_INCREMENT

성능 향상을 위한 SQL 작성법

커버링 인덱스

[MySQL] 클러스터링 인덱스