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

인덱스란?

어떤 문제를 해결하기 위한 기술인가

관계형 데이터베이스는 기본적으로 데이터 영속화라는 목적이 있는 기술이다.

쉽게 말하면 어떤 애플리케이션에 필요한 데이터를 유지(저장)하는데 목적이 있다.

그러나 단순히 저장만 하는 게 아니라 저장된 데이터를 가지고 서비스를 해야 한다.

그렇다 보니 수많은 데이터 중에서 특정 조건을 만족하는 데이터를 조회하는데 일일이 검사를 하게 되면 조회 시간이 점차 증가하게 되기 마련이다. (이것은 성능 저하이자 곧 서비스 품질 저하다.)

데이터를 하나하나 전부다 조건을 검사하게 되면 검색 성능이 매우 떨어지는 문제가 발생하는데 이 문제를 해결하기 위한 기술이 바로 인덱스다.

어떻게 해결하는지 쉽게 설명하면, 일반적으로 책 뒤에 "색인"처럼 어느 페이지에 어떤 단어가 있는지 대략적으로 파악할 수 있는 것을 제공하는 것과 비슷하게 해결한다. (대부분의 RDMBS가 유사하게 문제를 해결했다.)

책에서의 색인을 살펴보면 다음과 같은 특징을 갖는다. ✅

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

인덱스는 위와 같은 색인의 특징을 적절히 구현하여 저장하는 데이터가 많아지더라도 조회하는데 성능은 심각하게 낮아지지 않도록 하여 문제를 해결한다.

인덱스의 트레이드오프

조회 성능을 올리는 데는 크게 도움이 되었지만 삽입, 수정, 삭제 성능은 떨어지게 되었다.

왜냐하면 레코드를 추가하거나 삭제, 수정할 때는 생성된 인덱스에도 데이터 동기화를 해줘야 하기 때문에 오버헤드가 생기기 때문이다.

즉 INSERT, UPDATE, DELETE 성능을 희생하는 대신 SELECT 성능을 높이는 방법이라는 것이다.

이렇게 해도 무방한 이유는 대부분의 애플리케이션에서 INSERT, UPDATE, DELETE 보다 SELECT 의 사용 비율이 훨씬 높기 때문이다.

이제 상상 속이 아닌 실제 인덱스 구조가 어떻게 되어있는지 살펴보고 구조가 갖는 특징, 특정 구조이기 때문에 발생하는 문제들 혹은 장점을 이해해보도록 한다.

---

인덱스 구조

B-Tree 인덱스

출처 : Real MySQL 위키북스 백은빈, 이성욱 지음

대부분의 RDBMS 구현체들은 기본적으로 B-TREE 인덱스를 사용한다. 실질적으로 B+TREE 인덱스를 쓴다느니 조금씩 변형해서 쓰느니 하지만 인덱스의 추상적인 개념을 이해하는 것이기도 하고 다른 인덱스도 결국 베이스는 B-TREE 인덱스기 때문에 B-TREE 인덱스를 기준으로 이해해도 충분하다.

인덱스 컬럼(키)을 관리하기 위해서 트리(자료구조)를 이용하는데 최상위에 루트(Root) 노드가 있고 가장 끝단에 실제 레코드의 주소가 저장되어있는 리프(Leaf) 노드가 있다.

루트 노드와 리프 노드 사이에는 브랜치(Branch) 노드가 있다.

루트 노드와 브랜치 노드에는 실제 레코드의 주소는 알 수 없지만 실제 레코드의 주소를 알고 있는 리프 노드에 대한 매핑 정보를 갖고 있다. (같은 트리 레벨(깊이)의 페이지 사이에도 링크가 되어있어 다음 페이지를 확인할 수 있다.)

이를 통해서 트리를 타고 가면서 레코드를 찾는다.

• 레코드를 찾아가는 방식 예시
  • 위 그림에서 "Gad"라는 인덱스 키를 갖는 레코드를 찾는다고 가정해보자.
    1. 부모 노드에서 "Gad"라는 인덱스 키가 있는지 찾아본다.
    2. Aamer는 자식 노드 2번이고, Jaana는 자식노드 3번인 것을 확인할 수 있다. 즉, "Gad"는 없는 것 처럼 보인다. 그러나 기본적으로 정렬되어 있으므로 "Aamer"와 "Jaana" 사이에 "Gad"가 있을 수 있다는 것을 알 수 있다.
    3. "Aamer"가 가리키는 자식 노드 2번 페이지로 가서 다시 탐색하는게 적절하다. ("Jaana"가 가리키는 자식노드 3번 페이지는 인덱스의 '시작 키'가 "Jaana"이기 때문에 여기에는 없다는 것을 알 수 있다.
    4. 마침 페이지 2번에 "Gad"가 있다. 만약 없었으면 앞선 단계와 같이 찾아간다. 자식노드 6번을 가리키므로 자식노드 6번 페이지로 가본다.
    5. 6번 페이지에서는 또 다른 자식 노드의 번호가 아닌 실제 프라이머리 키(기본 키)값을 알려주고 있으니 데이터 파일에서 해당 프라이머리 키를 갖는 레코드를 찾으면 인덱스 키 "Gad"의 정보를 알 수 있다.

마지막에 실제 레코드가 있는 데이터 파일에는 데이터가 순서대로 있지 않는 것도 확인할 수 있다.

이유는 데이터를 순서대로 생성하여 관리하더라도 데이터가 삭제되면 그 자리가 비워질 수밖에 없고 다시 데이터를 생성할 때 그 빈자리를 활용하기 때문이다.

---

InnoDB의 인덱스 구조

앞서 설명한 B-Tree 인덱스는 인덱스에 대한 이해용이고 실질적으로 MySQL에서 가장 많이 쓰는 InnoDB 스토리지 엔진에서 기본으로 하는 인덱스 구조는 아래와 같다.

출처 : Real MySQL 위키북스 백은빈, 이성욱 지음

B-Tree 인덱스와 전혀 다른 구조는 아니고 데이터 파일을 관리하는 게 다르다.

B-Tree 인덱스에서는 데이터 파일이 순차적으로 있지 않고 여러 위치에 뒤죽박죽 순서로 저장되었기 때문에 실제 레코드 수가 몇 개 안되더라도 그것들이 다 다른 위치(페이지)에 있다면 메모리로 읽어와야 할 페이지(단위) 수는 많아져서 I/O에 효율이 떨어질 수 있는데 InnoDB의 인덱스는 데이터 파일에 저장 방식 자체를 프라이머리 키 인덱스로 정렬하여 관리하므로 I/O로 효율이 떨어지는 문제를 해결했다. (아래에서 설명하지만 트레이드오프는 당연히 있다.)

그리고 B-Tree 인덱스의 마지막 노드(리프 노드)에서 프라이머리 키 값이 저장되어 있는데 프라이머리 키 값으로 바로 해당 레코드를 찾을 수 있는 게 아니라 데이터 파일 저장 형식 자체가 인덱스이기 때문에 인덱스(프라이머리 키 인덱스)를 한 번 더 조회해야 실제 레코드에 접근할 수 있는 구조로 되어있다.

MySQL의 인덱스 종류

• 프라이머리 키 인덱스 : 프라이머리 키(컬럼에 NULL과 중복을 허용하지 않음)를 기준으로 만들어지는 인덱스
• 세컨더리 키 인덱스 : 프라이머리 키를 제외한 모든 인덱스
• 앞에서 학습한 InnoDB의 인덱스 구조를 학습했기 때문에 이렇게 예상해볼 수 있다.

프라이머리 키 인덱스는 반드시 존재한다. 왜? 데이터 레코드 저장 자체를 프라이머리 키 인덱스로 저장하기 때문이다.

나아가 프라이머리 키가 모든 테이블에 반드시 존재한다는 것도 알 수 있다. 그래야만 프라이머리 키 인덱스를 만들 수 있기 때문이다.

세컨더리 키 인덱스로 열심히 탐색했을 때 마지막 리프 노드에는 프라이머리 키 값이 있을 것이다.

그리고 이 프라이머리 키 값으로 프라이머리 키 인덱스를 다시 탐색해서 실제 레코드에 접근할 것이라는 것을 예상할 수 있다.

❗기본 키가 없는 테이블을 생성할 수 있지 않나? 할 수 있지만 MySQL은 기본 키를 내부적인 규칙에 의해 생성한다.

1) 기본 키를 찾는다.

2) 지정된 기본 키가 없는 경우 NOT NULL 옵션의 유니크 키(유니크 인덱스) 중 첫 번째 유니크 키를 클러스터 키(기본 키)로 선택한다.

3) 유니크 키도 없는 경우, 내부적으로 자동 증가하는 기본 키를 생성하고 이 키는 사용자에게 노출되지 않고 쿼리에서도 사용할 수 없는 키다.

인덱스 키 검색

앞선 내용을 통해 인덱스의 용도와 구조를 파악했다. 이제는 인덱스 검색 방법을 알아본다. (인덱스 구조를 이해했다면 아래 내용이 자연스럽게 이해가 된다.)

기본적으로 SELECT 뿐만 아니라 UPDATE, DELETE 쿼리에서도 해당 레코드를 찾는데 인덱스가 사용된다.

B-Tree인덱스를 사용하는 경우, = 일치 검색 또는 앞부분 일치를 이용한 검색의 경우 인덱스를 사용할 수 있고 부등호 < , > 비교도 인덱스를 사용할 수 있으나 뒷부분 일치를 이용한 검색은 불가능하다.

🤔 잘 생각해보면 이해가 간다.

인덱스 키가 문자열인 경우 앞에서부터 정렬되어있는데 뒷부분이 일치하는지를 검색하려고 하면 앞에서부터 정렬된 인덱스는 효과가 없기 때문에 사용되지 않게 된다.

부등호 비교나 일치 검색은 당연히 되는 게 정렬되어있기 때문에 어디까지 읽어야 하는지 혹은 정확하게 일치하는 게 걸리면 즉시 응답해주면 되기 때문에 인덱스를 이용할 수 있다.

인덱스의 키 값에 변형이 가해진 '후'에 비교되는 것(함수나 연산으로 얻어진 결과를 키로 정렬 또는 검색 작업)은 인덱스를 이용할 수 없다.

🤔 이 또한 잘 생각해보면 이해가 간다.

인덱스 키로 사용되는 값이 어떤 함수에 의해서 변형이 가해졌으면 더 이상 인덱스로 검색할 수 없는 게 너무 당연하다.

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

• 인덱스 키 값의 크기
  • 디스크에 데이터를 저장하고 읽는 기본 단위는 페이지 또는 블록 단위다.
  • B-Tree의 각 노드가 관리하는 인덱스 데이터의 단위 또한 페이지이다. (InnoDB 기본 값 = 16KB)
  • 각 노드(16KB)에 담길 수 있는 인덱스 (key, value)의 개수는 각 노드가 가질 수 있는 인덱스 키 값의 크기와 연관이 되어있다. (value는 다른 자식 노드의 주소이기 때문에 고정적이다.)
  • 따라서 인덱스 키 값의 크기가 커지면 하나의 노드가 가지고 있을 수 있는 인덱스 키의 개수가 줄어들면서 효율이 떨어지게 된다.
• B-Tree의 깊이
  • 인덱스 트리의 깊이가 깊어질수록 찾아가는 단계가 많아지는 문제(오버헤드)가 증가한다.
  • 물론 인덱스 트리의 깊이를 직접적으로 조절할 수 있는 방법은 없다.
  • 대신 레코드가 대용량이 될수록 깊이가 늘어나기 때문에 인덱스의 키 값의 크기를 줄여서 데이터가 많아지더라도 깊이가 빨리 늘어나지 않도록 하는 게 최선의 방법이다.
• 선택도(Selectivity)
  • 인덱스로 정한 키 값이 얼마나 유니크한가 또는 골고루 퍼져있는가에 대한 내용이다.
  • 인덱스로 조회했을 때 걸러지는 양이 많을수록 쓸데없는 데이터를 읽지 않을 수 있어 성능적으로 이득을 볼 수 있다.
• 읽어야 하는 레코드 수
  • 인덱스를 이용한다는 것 자체가 레코드를 직접 읽는 것보다 단계가 무조건 1개는 더 있다는 것은 사실이다. 그렇기 때문에 잘 생각해야 하는 것이 읽어야 하는 레코드의 수다.
  • 만약 테이블의 레코드 수가 100만 개 인데 절반인 50만개 또는 100만개 전부 다 읽어야 한다고 가정해보자. 그러면 굳이 인덱스를 읽지 않고 바로 레코드를 읽을 수 있다면 읽는 게 이득일 수도 있다. 그래서 통계적인 수치로 전체 레코드 대비 20% 미만의 레코드를 읽을 때 효율적으로 동작한다고 한다. 물론 저장된 레코드 수와 읽어야 할 레코드 수를 예상해서 옵티마이저가 최적화해준다.
• 유니크 속성
  • 여기서 설명하는 게 적절한가는 다른 문제지만 인덱스에서 중요하다.
  • 바로 인덱스에 사용된 컬럼이 유니크한지는 매우 중요한 문제다. 왜냐하면 딱 1개만 존재한다고 제약을 걸었기 때문에 찾는 범위에서 하나를 찾아버리면 그만 찾아도 되는 것을 알려줘서 최적화할 수 있기 때문이다.
  • 반대로 2개 이상 존재한다는 것은 설명 2개만 존재하더라도 최적화할 수 없고 끝까지 찾아봐야 하기 때문이다.

인덱스를 사용에 대한 분류

인덱스 구조에 대해 파악했고 영향을 미치는 요소에 대해서도 대략적으로 파악했다.

그러면 이제 조회에서 인덱스를 조회하기만 하면 무조건 좋은 건가? 그건 아니다. 그래서 다음과 같이 3가지로 분류를 했다.

1. 인덱스 레인지 스캔
   • 구체적으로는 인덱스를 통해 레코드 한 건만 읽는 것과 여러 건을 읽는 것이 다른 이름으로 분리되지만 여기서는 하나로 묶어서 인덱스 레인지 스캔이라고 부른다.
   • SELECT * FROM employees WHERE first_name BETWEEN 'Jeong' AND 'Pro'; 라는 쿼리를 예로 설명해보자. 'first_name' 컬럼으로 인덱스가 생성되어 있으면 인덱스에서 해당 범위에 해당하는 "일부"만 읽어서 검색하는 방법이다. 매우 이상적인 방식으로 통상적으로 "인덱스를 탔다!" 라고하면 인덱스 레인지 스캔이 되었을 때다.
2. 인덱스 풀 스캔
   • 이 방법은 인덱스를 쓰긴 쓰는데 인덱스를 처음부터 끝까지 다 읽는 것으로 인덱스를 이용하는 것이다. 통상적으로 이건 인덱스를 쓰긴 썼지만 "인덱스를 탔다!"라는 범위에 포함되지 않는다. 즉, 인덱스를 이용했지만 효율적이지 않은 경우다.
   • 보통 (A, B, C) 순서로 복합 인덱스를 구성했는데 A부터가 아닌 B나 C컬럼으로 조회하는 경우다. 이 경우에는 그래도 테이블보다 인덱스의 크기가 작기 때문에 테이블 풀 스캔보다는 조금 읽기는 가능할 것 같기에 그나마 정렬된 인덱스를 쓰는 경우다.
3. 루스(Loose) 인덱스 스캔
   • 인덱스 레인지 스캔처럼 일부만 읽는데 한 번에 쭉 읽는 게 아니라 중간에 필요치 않는 인덱스 키 값은 무시하고 듬성듬성 읽는 방법이다.
   • 루스 인덱스 스캔을 사용하려면 특정 조건을 만족해야 하는데 지금은 중요한 게 아니므로 다른 포스트에서 정리한다.
4. 인덱스 스킵 스캔
   • 분명 3가지라고 했는데 4번이 나왔다. 이는 특정 조건에서 만의 동작이기 때문이다.
   • SELECT gender, birth_date FROM employees WHERE birth_date >= '1993-01-01'; 이런 쿼리를 쓰는데 인덱스가 (gender, birth_date) 로 생성되어 있다면 일반적인 경우에 인덱스를 이용할 수 없다. 왜냐하면 순서상 gender 컬럼이 앞에 있기 때문에 뒤에 정렬을 이용하기 어렵다고 판단하기 때문이다.
   • 그러나 "인덱스 스킵 스캔"을 활성화한 상태에서 동일한 쿼리를 사용하면 인덱스를 사용할 수도 있다.
     • 조건은 두 가지를 만족해야 하는데, 첫 번째는 gender의 범위가 적다는 것을 알고 있을 때다. 예를 들어 gender 컬럼에 들어갈 수 있는 값이 'm', 'f' 두 개중 하나라고 가정하면(다른 성도 존중합니다만 지금 그게 포인트가 아님) MySQL이 자연스럽게 WHERE 조건에 gender = 'm' and birth_date ≥ '1993-01-01' 을 넣어주고, gender = 'f' and birth_date ≥ '1993-01-01' 을 넣어줘서 두 쿼리를 각각 실행한 뒤 결과를 합쳐버리는 식으로 최적화 해주는 것이다.
     • 두 번째 조건은 커버링 인덱스일 때다. (커버링 인덱스는 다음 포스트에서 쓰고 링크를 걸건데 쉽게 얘기하면 쿼리에서 필요한 데이터가 모두 인덱스에 있을 때다. (레코드를 참조할 필요가 없는 경우))
     • 다시 말하지만 이렇게 할 수 있는 것은 gender 키 값의 범위 즉 선택도가 높지 않다는 것을 알고 있었기 때문에 가능했던 것이고, 이게 조금이라도 늘어나면 인덱스를 타지 않게될 것이고, 컬럼의 범위가 1~2개라고 인덱스 스킵 스캔을 꼭 보장해주지는 않는다.

멀티 컬럼 인덱스

앞선 컬럼에 의존하는 정렬 순서

실제 서비스를 할 때 인덱스 설계는 아주 중요하다.

앞서 다른 포스트에서 학습한 락(Lock)도 인덱스에 걸기 때문에 인덱스를 잘 설계해야만 데드락에 빠지지 않고 높은 동시성을 가지고 서비스를 할 수 있다.

뿐만 아니라 인덱스의 본질적인 목적인 검색에서도 최대한 인덱스를 이용하여 효율적으로 검색할 수 있기 때문에 인덱스 설계는 아주 중요하다.

실무에서는 주로 하나의 컬럼보다는 여러 개의 컬럼으로 인덱스를 만든다. (= 멀티 컬럼 인덱스 = 다중 컬럼 인덱스 = 복합 인덱스)

멀티 컬럼 인덱스에서 가장 중요한 특성은 정렬이다.

인덱스는 정렬된 순서라는 게 아주 중요한 특징인데 멀티 컬럼 인덱스로 구성하게 되면 정렬이 앞선 컬럼에 의존하여 정렬된다.

쉽게 말하면 멀티 컬럼 인덱스를 생성할 때 앞에 있는 컬럼 순서대로 정렬이 된다는 것이다.

그렇기 때문에 인덱스의 순서를 아주 잘 고려해서 설계해야 한다.

정렬 방향

정렬 방향에는 오름차순(Ascending과 내림차순(Descending)이 있다.

보통 인덱스를 생성할 때 컬럼마다 정렬 방향을 지정해서 인덱스를 생성할 수 있지만 MySQL에서는 5.7 이하 버전에는 불가능했다. (많은 회사가 아직도 MySQL 5.7 이하 버전을 많이 쓰기 때문에 기본 기조는 안된다고 보는게 맞겠다.)

MySQL 8.0부터는 지정이 가능하다.

그러나 지정이 가능하더라도 5.7이하 버전을 쓰고 있는 회사가 많으므로 공부하고 있을 때는 우선 쿼리에서 정렬 방향을 섞어서 쓰면 인덱스를 타지 않을 수 있고 비효율적이라고 알고 있어야 한다......

효율적인 인덱스와 아닌 인덱스

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

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

1. (dept_no, emp_no)
2. (emp_no, dept_no)

인덱스를 생성하는 컬럼 순서만 다른 인덱스다.

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

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

뭐가 더 좋을까? 당연히 1번의 경우가 더 좋다.

1번의 경우 dept_no가 쿼리에서 동등 비교(=)로 인덱스를 찾는 범위를 좁히는데 도움을 준 경우고, 2번의 경우 쿼리에서 단순 조건에 맞는지 비교하는 용도로 쓰였기 때문이다.

인덱스의 범위 조건으로 사용할 수 없는 경우

범위 조건으로 사용되지 못하므로 인덱스를 사용할 수 없는 경우가 많을 것이다. 물론 경우에 따라 인덱스를 사용하긴 하는데 범위 조건으로 사용되지 못해 효율적이지 못한 경우다.

• NOT-EQUAL로 비교된 경우 ("<>", "NOT IN", "NOT BETWEEN", "IS NOT NULL")
  • 인덱스의 구조에서 봤듯이 부정형은 어쩔 수 없이 범위를 좁히는 조건으로 사용할 수 없다. 일일이 비교하는 조건 검사로 이용될 수밖에 없다. 그렇기에 인덱스를 타지 못한다.
• LIKE 뒷부분 일치로 비교된 경우 (ex . "%PRO")
  • 앞을 기준으로 정렬되어 있기 때문에 뒷부분 일치는 인덱스를 사용할 수 없다.
• 스토어드 함수나 연산자로 컬럼이 변경된 경우
  • WHERE SUBSTRING(column,1,1) = 'X'
  • WHERE DAYOFMONTH(column) = 1;
• NOT-DETERMINISTIC 속성의 스토어드 함수가 비교조건에 사용된 경우
  • WHERE column = deterministic_function();
  • NOT-DETERMINISTIC 속성의 스토어드 함수란 매번 스토어드 함수를 실행했을 때 결과가 달라질 것을 의미하는 경우로 이런 특성 때문에 내부적으로 매번 호출해서 비교해야 하기 때문에 NOT-DETERMINISTIC 속성의 스토어드 함수가 비교조건에 사용되면 인덱스를 이용할 수 없다.
• 데이터 타입이 서로 다른 비교(인덱스 컬럼의 타입을 변환해야 비교가 가능한 경우)
  • 값을 변환해야 하는 경우는 상관없는데 인덱스 컬럼의 타입을 변경해야 하는 WHERE 조건을 적용하면 인덱스를 이용할 수 없다. 매번 변환을 해야 하기에...
• 문자열 데이터 타입의 콜레이션이 다른 경우
  • 인덱스 컬럼은 utf8인데 WHERE에 조건에 있는 값은 euckr인 경우에는 콜레이션이 다르므로 인덱스를 탈 수 없다.

정리

• 인덱스는 삽입, 수정, 삭제 쿼리의 성능을 조금 떨어뜨리는 대신 조회 성능을 상승시키는 방법이다.
• InnoDB 스토리지의 인덱스 구조는 B-tree 구조를 기반이고, 추가적으로 데이터 파일 저장 형태도 프라이머리 키(기본 키)를 인덱스 키로 갖는 트리 형태(정렬된 형태)로 저장한다.
• 인덱스 키 값의 크기를 줄이면 메모리에 적재하는 노드(페이지) 당 관리할 수 있는 인덱스가 많아져 효율적으로 사용할 수 있다.
• 인덱스는 싱글 컬럼 인덱스 보다 멀티 컬럼 인덱스로 많이 사용하는데 컬럼 순서에 의존하여 정렬되므로 순서 설계가 매우 중요하다.
• 인덱스를 더 효율적으로 쓰기 위해서 검사하는 방식보다는 범위를 줄여주는 방식으로 생성하고 사용해야 한다.
• 인덱스 구조상 불일치 조건에서는 사용하기 어려우므로 조인이나 일치 조건으로 최적화를 해야 한다.