정리중 - 4

디스크에 데이터는 순서대로 저장되지 않습니다. 여기저기 나뉘어 저장되게 됩니다.

그래서 데이터를 하나하나 전부 조건을 검사해서 가져오면 검색 성능이 매우 구려집니다.

 

그래서 데이터베이스는 데이터의 위치 인덱스를 만들어두고 사용합니다. 마치 책 뒤의 색인(index)처럼요.

 

그럼 모든 데이터를 indexing하면 성능이 미친듯이 좋아진다고 생각할 수도 있습니다.

과연 그럴까요? 예를 들어봅시다. 600장 짜리 책인데 인덱스가 300장 정도 되고 모든 단어의 위치가 적혀있다고 생각해보세요. 색인을 해놓은 의미가 전혀 없어집니다. 자주 사용하는 데이터를 인덱싱 해야합니다.

 

* 참고로 인덱스도 결국 디스크에 저장됩니다. DBMS의 가장 중요한 데이터 무결성은, 인덱스도 포함되기 때문입니다.

 

풀 테이블 스캔은 인덱스를 거치지 않고 해당 테이블을 디스크 페이지 단위로 전부 가져오는 것을 의미합니다.

풀 인덱스 스캔은 현재 가지고 있는 인덱스를 전부 조회해서 찾는 것을 의미합니다.

인덱스의 마지막 노드 (리프 노드)는 디스크에 저장된 데이터의 위치를 가르키고 있습니다.

 

# 인덱스의 트레이드 오프

인덱스는 조회 성능을 크게 올려줍니다. 하지만 삽입, 수정, 삭제 성능은 떨어지게 됩니다. 그 이유는 레코드를 수정할 때 마다 '인덱스'도 함께 동기화 해주어야 하기 때문입니다.

 

그럼에도 불구하고 DB에서 인덱스를 적극적으로 사용하는건, 보통 Insert, delete, update보다 조회 Select가 압도적으로 많기 때문입니다.

데이터베이스는 기본적으로 B-Tree 인덱스와 단일 검색을 위한 Hash 인덱스를 사용합니다.

 

 

# InnoDB의 인덱스 구조

InnoDB도 B-Tree를 베이스로 인덱스를 구성합니다.

다만 InnoDB는 데이터 파일을 저장할 때, 프라이머리 키를 인덱스로 정렬하여 관리합니다.

즉 PK 자체가 주소 역할을 하게 됩니다. 그래서 Range Scan이 매우 빠릅니다.

 

MySQL에서는 PK와 Secondary Key Index (PK를 제외한 키)를 사용합니다.

 

# B-Tree 기반 인덱스 사용에 영향을 미치는 요소

• 인덱스 키 값의 크기 (InnoDB 기본값 16KB)
  전체적인 인덱스의 크기가 늘어납니다. 만약 너무 커서 한 페이지에 담을 수 없다면, 2번 읽어와야 합니다.
  
  
• B-Tree의 깊이
  찾는 단계가 많아져서 오버헤드가 증가합니다.
  길이를 직접적으로 조절할 수는 없습니다. 레코드가 대용량이라 깊이가 늘어난다면 인덱스의 키 값 크기를 줄여서 깊이를 최대한 얕게 만들어야 합니다.
  
  
• Selectivity(선택도)
  인덱스로 정한 키값이 얼마나 골고루 퍼져있는가 (고유한가)에 대한 척도입니다.
  Hash Function처럼 골고루 퍼져있어야 쓸모없는 데이터를 읽지 않아 성능의 향상을 가져올 수 있습니다.
• 읽어야 하는 레코드 수
  트리의 깊이랑 같은 내용이 아닌가 싶지만, 이는 '꼭 인덱스를 사용해야 하는가?'에 대한 척도입니다.
  B-Tree 특성상 인덱스를 사용하는 것은 무조건 1번 이상의 읽기가 추가됩니다.
  테이블 레코드수가 100만개인데, 60만개를 읽는 작업이라면 인덱스를 적용하지 않는게 더 빠릅니다.
  통계적인 수치로 20% 미만일 때에는 그냥 읽는게 더 빠릅니다. 실제로 옵티마이저가 그렇게 최적화 해줍니다.
  
  
• 유니크 속성
  찾는 속성값이 고유한가는 매우 중요한 문제입니다.
  딱 1개만 존재한다는 보장이 있다면, 하나를 찾으면 바로 동작을 멈추면 됩니다.
  하지만 그런 보장이 없다면 최적화할 수 없고 모든 레코드를 다 뒤져야겠죠.

 

# 인덱스 사용에 대한 분류

• 인덱스 Range Scan
  전체를 읽지 않고 인덱스에서 해당 범위에 해당하는 일부만 읽어서 검색하는 방법입니다.
  흔히 인덱스를 타서 성능이 향상되었다는건 인덱스 레인지 스캔을 의미합니다.
  SELECT * FROM employees WHERE first_name BETWEEN 'A' AND 'C';
  
• 인덱스 Full Scan
  인덱스를 처음부터 끝까지 다 읽는 방법입니다. 효율적이지 않은 경우입니다.
  데이터베이스는 인덱스를 기본적으로 사용하기 때문에, Index Full Scan이라고 표현합니다.

• Loose Index Scan
  레인지 스캔처럼 일부만 읽는데, 한번에 쭉 읽는게 아니라 중간 값을 뛰어넘고 듬성 듬성 읽는 방법입니다.
  
  
• Index Skip Scan
  특정 조건에서 동작하는 스캔 방식입니다. 예를 들어 아래의 쿼리를 사용한다고 생각해봅시다.
  SELECT gender, birth_date FROM employees WHERE birth_date >= '1996-01-01';
  성별은 [남, 여] 2가지 값 밖에 없습니다. 그래서 (gender, birth_date)로 인덱스가 잡혀있다면 인덱스의 이점을 이용하기 어렵습니다. 순서상 성별이 앞에 있기 때문이죠.
  
  이 때 Index Skip Scan을 활성화 되어있다면 인덱스를 사용할 수 있도록 최적화 해줍니다.
  앞의 값(gender)의 범위가 작다는 것을 알 수 있을 때 MySQL은 다음과 같이 두 쿼리로 만들어서 최적화합니다.
  ... WHERE gender='남' and birth_date >= '1996-01-01'
  ... WHERE gender='여' and birth_date >= '1996-01-01'
  이렇게 인덱스를 사용할 수 있게 쿼리를 쪼개고, 이를 합쳐서 더 빠른 결과를 만들도록 최적화 해줍니다.
  
  * 또는 쿼리에서 필요한 데이터가 모두 인덱스에 표시되어 있어 굳이 디스크에서 페이지를 불러올 필요가 없는 경우(커버링 인덱스) Index Skip Scan 최적화를 사용할 수 있습니다. 
  
  * 다만 이 경우는 쿼리를 최적화하는게 더 낫습니다. 키 값의 범위 (선택도)가 높지 않다는 걸 알기 어렵기 때문에. gender가 2개라고해서 반드시 Skip Scan이 동작할거라고 보장할 수 없기 때문입니다.

 

#  Multi Column Index (또는 다중 복합 인덱스)

위의 Index Skip Scan의 예제에서도 보았지만, 인덱스 컬럼이 여러개인 경우 앞선 컬럼에 의존하게 됩니다.

그래서 실제 서비스를 구축할 때 인덱스 설계와 인덱스 순서는 매우 중요합니다.

 

@ 정렬 방향

인덱스를 생성할 때 기존에는 정렬방향 지정이 불가능했지만, MySQL8.0 부터는 가능합니다.

지정이 불가능한 5.X 대 버전이라면, 쿼리에 정렬 방향을 섞어서 쓰면 인덱스를 타지 않는다는 것만 기억해주세요.

SELECT * FROM dept_emp WHERE dept_no='d001' AND emp_no >= 1000; -- 사용하는 쿼리

1. (dept_no, emp_no) -- 인덱스 1
2. (emp_no, dept_no) -- 인덱스 2

단순한 Select 문에 위의 2가지 인덱스를 생성한다고 가정해봅시다. 보시다싶이 그냥 순서만 다릅니다.

 

1번의 경우 dept_no 순서로 인덱스가 정렬되어있어서 d001을 찾고 그냥 쭉 읽어버리면 끝납니다.

2번의 경우 emp_no 순서로 정렬되어 있기 때문에 먼저 emp_no>=1000 조건을 찾고, dept_no가 d001이 맞는지 일일이 비교해야 합니다.

 

# 인덱스를 타지 못하는 조건

• NOT-EQUAL을 사용하는 경우 ( NOT IN, NOT BETWEEN, IS NOT NULL )
  인덱스 구조의 특성상, 부정형은 어쩔 수 없이 범위를 좁히는 식으로 일일이 비교해야 합니다. 인덱스 불가능.
• LIKE 뒷부분 일치
  B-TREE 인덱스는 앞을 기준으로 정렬되어 있기 때문에, 뒷부분 일치는 인덱스를 이용할 수 없습니다. 풀스캔ㄱ
  
  
• 스토어드 함수나 연산자로 칼럼이 변경된 경우
  칼럼이 변형되면 인덱스에 적용할 수 없습니다.
  마찬가지로 칼럼 비교조건에 Not-Deterministic 스토어드 함수를 사용한 경우, 매번 호출해야해서 인덱스 불가.
• 데이터 타입이 다른 경우 (인덱스 칼럼 타입을 변환해야 비교가 가능한 경우)
  이 역시도 매번 변환을 해야하면 인덱스를 이용할 수 없습니다.
  
  
• 문자열 데이터 타입이 다른 경우
  문자열이라고 다 똑같이 될 거같지만, 이 역시도 문자 인코딩 방식이 같은지 확인해야합니다.
  당연히 'UTF8'과 'euc-kr'을 비교하면 인덱스를 타지 못합니다. 변환해야하니까요.