[MySQL] 인덱스(index)란?

개요

DB를 사용하면서 데이터의 양(row)에 따라 실행 결과의 속도가 차이가 나는 것을 알고 있었다. 특히 데이터의 양이 증가할수록 실행 속도는 느려지고, JOIN이나 서브 쿼리 사용 시 곱 연산이 일어나 데이터 양이 증가하기 때문에 WHERE 조건에서 필요한 데이터만 추출 후 사용하는 것이 좋다고 알고 있었는데, 보다 쿼리의 성능을 높이는 데 중요한 것은 인덱스를 적재적소로 활용하는 것이었다. 그렇다면 인덱스의 개념과 구조, 그리고 왜 사용하는지?, 사용했을 때 장점과 단점들에 대해 학습하고자 한다.

인덱스(Index)란?

인덱스란 데이터의 저장(INSERT, UPDATE, DELETE) 의 성능을 희생하고 추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색(읽기) 속도를 향상시키기 위한 자료구조이다.

※ 참고

쉽게 예를 들어보면 책 뒷편에 '찾아보기'가 인덱스의 역할과 동일하다고 볼 수 있다.
예를들어 '홍길동'이라는 단어를 찾고싶으면 색인페이지에서 '홍'으로 시작하거나 'ㅎ'으로 시작하는 색인을 찾아보면 빠르게 찾을 수 있다.

영어사전의 알파벳순 정렬도 마찬가지이다.
어떤 영단어를 영어사전에서 찾으려 할때 이미 알파벳 순으로 정렬되어있기 때문에 처음에 해당하는 알파벳 페이지 위치로 가서 검색하면 책뒷편을 뒤지는 것보다 빠르게 찾을수 있다.

위 예시처럼 우리가 책에서 원하는 내용을 찾는다고 하면, 책의 모든 페이지를 찾아보는 것은 오랜 시간이 걸린다. 이처럼 인덱스 없이 전체를 조회하는 것을 Full Scan이라고 한다.

그렇기 때문에 책의 저자들은 책의 맨 앞 또는 맨 뒤에 색인(Index) 을 추가하는데, 책의 색인을 통해 원하는 부분을 빠르게 찾을 수 있다. 그리고 이처럼 인덱스를 활용하여 조회하는 것을 Index Scan이라고 한다.

물론 인덱스가 없더라도 데이터베이스를 '작동' 하는데 있어서는 문제는 없다.

하지만 데이터베이스의 크기가 억대 단위로 크면 클 수록, 인덱스가 반드시 필요해진다. 인덱스는 데이터베이스의 성능(속도)를 크게 좌우하는 요소이기 때문이다.

어떤 항목을 select 하는데 있어 몇분이 걸리던것이 인덱스를 이용해 조회하면 0.초 단위로 끝낼수 있을 정도 이다.

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

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

때문에 개발자나 관리자들은 효율적인 인덱스 설계로 단점을 최대한 보완하는 구조 방법을 끊임없이 고민해야 한다.

지금까지의 인덱스의 특징에 대해 간단히 정리하자면 다음과 같다.

select 검색 속도를 크게 향상 시킨다.
인덱스 생성 시 DB 크기의 약 10% 정도되는 추가 공간이 필요하다.
인덱스 생성 시 시간이 걸린다. (몇분 씩 걸리기도 한다)
insert, update, delete같은 데이터 변경 쿼리가 잦은 경우 paging이 빈번해져 성능이 악화될 수 있다.
데이터 조회에는 플러스지만, 데이터 변경이 자주 일어나면 오히려 성능 감소된다.

Index 구조

Index는 Btree, B+tree, Hash, Bitmap로 구현될 수 있다. 많은 데이터베이스 시스템에서 index는 B+tree구조를 가진다.

특정 컬럼에 대한 인덱스를 생성하면, 해당 컬럼의 데이터들을 정렬하여 별도의 메모리 공간에 데이터의 물리적 주소와 함께 저장된다.

이 때, Index에 저장되는 속성 값을 search-key값이라고 하고 실제 데이터의 물리적 위치를 저장한 값을 pointer라고 한다. 즉, index는 순서대로 정렬된 search-key값과 pointer값만 저장하기 때문에 table보다 적은 공간을 차지한다.

정리해보면 특정 column을 search-key 값으로 설정하여 index를 생성하면, 해당 search-key 값을 기준으로 정렬하여 (search-key, pointer)를 별도 파일에 저장한다. 이를 index라고 한다.

이렇게 인덱스를 생성하였다면 앞으로 쿼리문에 "인덱스 생성 컬럼을 WHERE 조건으로 거는 등"의 작업을 하면 *옵티마이저에서 판단하여 생성된 인덱스를 탈 수가 있다. 만약 인덱스를 타게 되면 아래의 그림과 같이 인덱스를 타게 되고 먼저 인덱스에 저장되어 있는 데이터의 물리적 주소로 가서 데이터를 가져오는 식으로 동작을 하여 검색 속도의 향상을 가져올 수 있다.

※ 참고

[옵티마이저]

옵티마이저는 가장 효율적인 방법으로 SQL을 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심 엔진이다.
컴퓨터의 두뇌가 CPU인 것처럼 DBMS의 두뇌는 옵티마이저라고 할 수 있다. 개발자가 SQL을 작성하고 실행하면 즉시 실행되는 것이 아니라 옵티마이저라는 곳에서 “이 쿼리문을 어떻게 실행시키겠다!”라는 여러가지 실행 계획을 세우고, 최고의 효율을 갖는 실행계획을 판별한 후 그 실행계획에 따라 쿼리를 수행하게 되는 것이다.

인덱스를 활용하면, 데이터를 조회하는 SELECT 외에도 UPDATE나 DELETE의 성능이 함께 향상된다. 그러한 이유는 해당 연산을 수행하려면 해당 대상을 조회해야만 작업을 할 수 있기 때문이다.

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

만약 index를 사용하지 않은 컬럼을 조회해야 하는 상황이라면 앞서 말했다시피 전체를 탐색하는 Full Scan을 수행해야 한다. Full Scan은 전체를 비교하여 탐색하기 때문에 처리 속도가 떨어진다.

인덱스 알고리즘 종류

데이터 저장 방식(알고리즘)별로 구분하는 것은 사실 상당히 많은 분류가 가능하겠지만 대표적으로 B-Tree 인덱스와 Hash 인덱스로 구분할 수 있다. 최근 새롭게 Fractal-Tree 인덱스와 같은 알고리즘도 도입됐다.

가장 보편적인 방식은 B-Tree 방식 알고리즘이다.

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

인덱스 알고리즘 원리에 대해서 깊게 공부하면 효율적인 인덱스 설계에 있어 도움을 주겠지만 이론적인 내용이 방대하여 이 포스팅에서는 자세히 다루지 않고 간단히 소개 정도로 넘어가본다.

※ 중요

실제로는 b+ tree라고 정의된 논문은 존재하지 않는다고 한다. b+tree는 b tree의 변형된 자료구조이며 누군가 이를 'b+tree'라고 명시하지 않고 있지만, 대부분 b tree 형식을 구현한 RDBMS는 b+tree로 구현되어 있다. 그렇다면 왜 b+tree란 말이 생겼나? 라는 의문점이 생길 수가 있는데, 이는 1973년 ibm 아티클에서 최초로 언급이 되었다고 한다. (엄밀하게 말하면 b+tree는 b tree가 아니다. 리프 노드에 모든 데이터를 유지하는 구조이기 때문이다.)

(자료 : https://dl.acm.org/doi/10.1145/356770.356776)

실제로 MySQL, Oracle 공식 문서를 살펴보면 용어는 b-tree이지만 그림 및 설명은 b+tree 구조로 설명되어 있는 것을 확인 할 수 있다. 따라서 아래에서도 b-tree라는 명칭을 사용하지만 구조는 b+tree에 따라 설명하는 것을 참고하기 바란다.

B-Tree 인덱스 알고리즘

인덱스의 일반적인 구조로는 B-Tree 구조가 있다. (공식문서 참고 - MySQL Optimization, Oracle Indexes : B-Tree)

B-Tree는 Balanced Trees이며 정렬을 통한 구조로 검색이 항상 동일한 시간을 갖게끔 만들어준다.

✔️ B-Tree

B-Tree Index는 범위를 기반으로 나뉘어진 정렬된 리스트이다.

이 구조는 두 가지 블록을 갖는데, 바로 검색을 위한 Branch Blocks와 값을 저장하는 Leaf Blocks이다.

✔️ Branch Blocks

상위 수준의 Branch Blocks에는 하위 수준의 Branch Blocks을 가리키는 인덱스 데이터를 포함한다.

예를 들어, 1 level(root - level 0)의 가장 왼쪽 Branch Blocks 에는 0~40라는 범위를 가리키는 인덱스 데이터를 포함하고 있다.

또, 그 내부에는 0~10, 11 ~19... 등의 데이터를 가지고 있는데, 각 항목을 통해 Leaf Blocks을 범위하여금 찾을 수 있게 된다.

✔️ Leaf Blocks

Leaf Block에는 모든 인덱스의 데이터 값과 실제 행을 찾는데 사용되는 rowid를 포함한다.

그래서 그림을 보면 (key, rowid)의 형태를 볼 수 있으며, 각 항목은 (key, rowid) 별로 정렬된다.

그래서 인덱싱의 값만 접근하는 경우에는 인덱스 블록에만 접근을 하고, 다른 데이터를 검색할 때에는 추가적으로 rowid를 이용하여 테이블 행을 찾는다.

덕분에 Min()이나 Max()를 사용할 때 양 끝의 데이터를 불러오면 되기 때문에 성능에 긍정적인 효과를 미칠 수도 있다.

이 때, 그림을 자세히 보면 Leaf Blocks 들 사이를 화살표로 연결해놓은 것을 볼 수 있다. Leaf Blocks 자체도 이중으로 연결되어있다.

수평적 탐색을 위한 장치로, 조건절을 만족하는 데이터를 모두 찾거나 rowid를 얻기 위해 사용된다.

이 인덱스의 최대 장점은 어떤 데이터를 조회하든지, 이에 사용하는 조회 과정의 길이 및 비용이 균등 하다는데 있다.
단, 어떤 데이터를 조회 하든지 Root 에서 부터 Leaf 페이지를 모두 거처야 하기 때문에 데이터가 적은 테이블 등의 단순 조회로 데이터를 조회하는 과정이 대비 조회 속도가 느린 단점이 있다.

정리

위 참고사항에서 설명은 했지만 B-tree를 개선시키고 실제 DB에서 사용되는 B+tree에 대한 정리이다.

B+Tree는 DB의 인덱스를 위해 자식 노드가 2개 이상인 B-Tree를 개선시킨 자료구조이다. B+Tree는 모든 노드에 데이터(Value)를 저장했던 B-Tree와 다른 특성을 가지고 있다.

리프노드(데이터노드)만 인덱스와 함께 데이터(Value)를 가지고 있고, 나머지 노드(인덱스노드)들은 데이터를 위한 인덱스(Key)만을 갖는다.
리프노드들은 LinkedList로 연결되어 있다.
데이터 노드 크기는 인덱스 노드의 크기와 같지 않아도 된다.

데이터베이스의 인덱스 컬럼은 부등호를 이용한 순차 검색 연산이 자주 발생될 수 있다. 이러한 이유로 B-Tree의 리프노드들을 LinkedList로 연결하여 순차검색을 용이하게 하는 등 B-Tree를 인덱스에 맞게 최적화하였다.

※ 참고

물론 Best Case에 대해 리프노드까지 가지 않아도 탐색할 수 있는 B-Tree에 비해 무조건 리프노드까지 가야한다는 단점도 있다.

이러한 이유로 비록 B+Tree는 O(logN) 의 시간복잡도를 갖지만 해시테이블보다 인덱싱에 더욱 적합한 자료구조가 되었다.

아래의 그림은 InnoDB에서 사용된 B+Tree의 구조이다.

실제 InnoDB에서의 B+Tree는 일반적인 구조보다 더욱 복잡하게 구현이 되었다. InnoDB에서는 같은 레벨의 노드들끼리는 Linked List가 아닌 Double Linked List로 연결되었으며, 자식 노드들은 Single Linked List로 연결되어 있다.

Hash 인덱스 알고리즘

Hash 인덱스 알고리즘은 칼럼의 값으로 해시 값을 계산해서 인덱싱하는 알고리즘으로, 매우 빠른 검색을 지원한다.

하지만 값을 변형해서 인덱싱하므로, 해시 인덱스는 동등 비교 검색에는 최적화돼 있지만 범위를 검색한다거나 정렬된 결과를 가져오는 목적으로는 사용 할 수 없다.

주로 인메모리 DB 에서 사용하는 인덱스 종류다.

※ 참고

인메모리 DB 란?
메모리가 디스크 스토리지의 메인 메모리에 설치되어 운영되는 DB 다. 알티베이스, Oracle Timestan, SAP Hana DB 등이 이 분류에 속한다.

해시 인덱스의 장점으로는 실제 키값과는 관계없이 인덱스 크기가 작고 검색이 빠르고 원래의 키값을 저장하는 것이 아니라 해시 함수의 결과만을 저장하므로 키 컬럼의 값이 아무리 길어도 실제 해시 인덱스에 저장되는 값은 4~8바이트 수준으로 상당히 줄어든다.

그래서 타 인덱스 대비 시간복잡도는 O(1)으로 조회 속도가 매우 빠르다. 그러한 이유는 해시가 등호(=) 연산에만 특화되어있다.

그러나 위와는 반대로, 해시 함수는 값이 1이라도 달라지면 완전히 다른 해시 값을 생성하는데, 이러한 특성에 의해 부등호 연산(>, <)이 자주 사용되는 데이터베이스 검색을 위해서는 해시 테이블이 적합하지 않다.

또한 범위로 묶어서 보관하는 인덱스가 아니므로 데이터 개수가 증가 함에 따라 범위로 묶어서 보관하는 인덱스보다 더 큰 저장공간을 필요로 한다.

예를 들면 "나는"으로 시작하는 모든 데이터를 검색하기 위한 쿼리문은 인덱스의 혜택을 전혀 받지 못하게 된다. 이러한 이유로 데이터베이스의 인덱스에서는 B+Tree가 일반적으로 사용된다.

[자료구조] 해시테이블(HashTable)이란?

해싱이란? (Hashing) 해싱이란 임의의 길이의 값을 해시함수(Hash Function)를 사용하여 고정된 크기의 값으로 변환하는 작업을 말한다. 위 그림에서 dog 라는 문자열을 해시함수를 이용해 새로운 값으

s-y-130.tistory.com

Fractal-Tree 알고리즘 (TokuDB)

Fractal-Tree 알고리즘은 B-Tree의 단점을 보완하기 위해 고안된 알고리즘이다.

값을 변형하지 않고 인덱싱하며 범용적인 목적으로 상요할 수 있다는 측면에서 B-Tree와 거의 비슷하지만 데이터가 저장되거나 삭제될 때 처리 비용을 상당히 줄일 수 있게 설계된 것이 특징이다.

인덱스 타입 종류

인덱스는 직접 설정할 수도 있고 자동적으로 생성되기도 한다. 자동적으로 생성되는 경우는 두 가지가 있다.

특정 컬럼에 PK(primary Key)를 설정하거나 Unique 제약조건을 설정할 때 생성된다. 둘 다 인덱스가 생성되기는 하지만, 다른 구조로 생성되는데 PK를 설정하게 되면 Primary(클러스터) 인덱스 (Clustered Index)가 자동 생성되고, Unique 제약조건을 설정하게 되면 Secondary(보조) 인덱스 (Non-Clustered Index)가 자동 생성된다.

간단하게 훑어보자면, 클러스터형 인덱스는 데이터를 직접 포함한다는 점과 저장과 동시에 데이터를 정렬한다는 점이 다르다.

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

각 인덱스의 특징에 따라 mysql에서 사용처가 다르다고 보면 된다.

	클러스터 인덱스	보조 인덱스
속도	빠르다	느리다
사용 메모리	적다	많다
인덱스	인덱스가 주요 데이터	인덱스가 데이터의 사본(Copy)
개수	한 테이블에 한 개	한 테이블에 여러 개(최대 약 250개)
리프 노드	리프 노드 자체가 데이터	리프 노드는 데이터가 저장되는 위치
저장값	데이터를 저장한 블록의 포인터	값과 데이터의 위치를 가리키는 포인터
정렬	인덱스 순서와 물리적 순서가 일치	인덱스 순서와 물리적 순서가 불일치

클러스터 인덱스와 보조 인덱스를 살펴봄에 앞서서 우선 다음과 같은 테이블 데이터를 준비했다.

인덱스가 없는 데이터들은 mysql 내에서 페이지(page) 라는 노드의 16kb 단위로 분할되어 저장된다.

이제 이 데이터들에게 인덱스를 부여하여 왜 인덱스가 있으면 검색속도가 빨라지고 어떨때에는 느리는지 자세히 살펴보자.

※ 참고

MySQL은 데이터를 한곳에다가 다 저장하는것이 아닌, 페이지(page)단위로 쪼개어 저장하는데, 페이지의 크기 기본값은 16KB 정도이다.
페이지 크기는 'SHOW VARIABLES LIKE 'INNODB_PAGE_SIZE' 문으로 확인 할 수 있다.
MB, GB 시대에서 KB는 적게 보일수는 있지만 꽤 많은 데이터를 저장할 수 있는 용량이다.
그래도 필요하다면 INNODB_PAGE_SIZE 환경변수의 값을 4KB, 8KB, 32KB, 64KB 로 변경 할 수 있다.

클러스터 인덱스 (Primary Index)

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

클러스터 인덱스 생성시 페이지 변화

인덱싱을 하면 루트 페이지라는 것이 만들어진다.
루트 페이지는 각 데이터 페이지의 첫번째 데이터만 따와서 모아 매핑시키는 페이지이다.
그리고 데이터 페이지는 자동 정렬이 된다.
데이터 페이지 자체를 인덱스 페이지로 하는 특징이 있다.

클러스터 인덱스를 이용한 데이터 조회 (단일)

만일 JKW (조관우) 만 찾고 싶다면 1 페이지만 뒤지면 조회가 가능하다.

클러스터 인덱스를 이용한 데이터 조회 (범위)

이번엔 데이터 한개가 아닌 여러개의 데이터를 범위로 검색해보자. 유저 아이디가 A ~ J 인 사용자를 모두 검색해본다
역시 1 페이지만 읽으면 된다.
이처럼 정렬이 되어있기 때문에 검색은 무척 빠르다.

클러스터 인덱스를 이용한 데이터 삽입

FNT 데이터를 추가하는데 1000번 페이지에 공간이 없어서 페이지 분할이 일어나 2000번 페이지가 생겨나게 된다.
이처럼 정렬이 되어있기 때문에 오히려 삽입 삭제 등을 할 때, 페이지 분할이나 추가적인 정렬이 필요해 성능이 오히려 나빠지게 된다.

보조 인덱스 (Secondary Index)

이 인덱스는 논 클러스터 인덱스 (non-clustered index) 라고도 불린다.
비클러스터형 인덱스는 물리적으로 데이터를 정렬하지 않은 상태로 저장된다.
개념적으로는 후보키에만 부여 할 수 있는 인덱스다.
(후보키 : 고유 식별 번호, 주민번호 같이 각 데이터를 인식할 수 있는 최소한의 고유 식별 속성 집합)
보조 인덱스의 생성시에는 데이터 페이지는 그냥 둔 상태에서 별도의 페이지에 인덱스를 구성한다.
별도의 페이지에서 인덱스를 구성하니, 클러스터와는 달리 자동 정렬을 하지 않는다.
보조 인덱스의 인덱스 자체의 리프 페이지는 데이터가 아니라 데이터가 위치하는 주소값 (RID)을 갖는다.
- 즉, 데이터를 직접 가지고 있지 않고, 데이터의 위치를 가리킨다.
- ms 공식문서에서는 데이터를 가리키는 포인터를 행 로케이터라고 소개한다.
- 이 때, 자세하게 보면 클러스터형 인덱스를 가리키거나 실제 데이터를 저장한 위치를 가리키는 두 가지 형태가 있다.
  가리키는 대상이 클러스터형 테이블의 경우 행 로케이터는 클러스터형 인덱스 키이고, 데이터 페이지가 존재하는 힙 영역을 가리키는 경우 행에 대한 포인터를 가지고 있다.

클러스터형 보다 검색 속도는 더 느리지만 데이터의 입력/수정/삭제 명령에는 성능이 더 뛰어날 수 있다.
보조 인덱스는 여러 개 생성할 수 있다. 그러나 함부로 사용할 경우에는 오히려 성능을 떨어뜨릴 수 있다.
각 데이터에 대해서 고유 값 (unique) 들이 있는 목록에 생성 할 수 있는 인덱스다. (unique key)

보조 인덱스 생성시 페이지 변화

보조 인덱스 역시 루트 페이지가 만들어진다.
하지만 데이터 페이지에 바로 연결시키지 않고 따로 리프 페이지를 만들어서 매핑을 하고 정렬 시킨다. (RID)
이처럼 추가 공간이 필요하므로 마구 인덱스를 남용하면 공간 낭비로 이어질수도 있다.
데이터 페이지는 변화를 주지 않는다.
따라서 클러스터 인덱스와는 달리 여러개 생성이 가능한 이유이다.

보조 인덱스를 이용한 데이터 조회 (단일)

만일 JKW (조관우) 만 찾고 싶다면 3 페이지를 뒤져야 조회가 가능하다.
만일 데이터가 억대단위 일 경우 클러스터 인덱스에 비해 조회해야할 페이지 조회수가 엄청 차이가 나고 이는 곧 성능에 연결된다.

보조 인덱스를 이용한 데이터 조회 (범위)

유저 아이디가 A ~ J 인 사용자를 모두 검색해본다
무려 5 페이지나 읽어 검색해야 된다. (데이터 페이지는 물리적으로 그대로 두기 때문에)

보조 인덱스를 이용한 데이터 삽입

그냥 페이지 빈곳에 데이터를 넣고 리프 페이지에서 매핑만 하면 되기 때문에, 페이지 분할이 일어나지 않는다.
따라서 '삽입' 적인 측면에서 클러스터 인덱스보다 성능 이점을 가지게 된다.

클러스터 + 보조 인덱스

한 테이블에 클러스터 와 보조 인덱스가 같이 쓰일때의 모습이다.

클러스터 인덱스 생성

클러스터형 인덱스를 생성되는 환경에는 두 가지 방법이 있다.

Primary key 설정하기
Clustered 조건 추가하기

-- 1. Via primary key constraint
ALTER TABLE 테이블명 ADD CONSTRAINT pk_이름
PRIMARY KEY CLUSTERED ([컬럼명, ...]);

-- 1번 예시
ALTER TABLE mixedtbl
ADD CONSTRAINT PK_mixedtbl_userID PRIMARY KEY (userID);

-- 2. Using create index statement
CREATE CLUSTERED INDEX index_이름 ON 테이블명([컬럼명, ...]);

클러스터에 보조 인덱스 생성

클러스터 인덱스 페이지에 보조 인덱스 페이지가 따로 만들어져서 연결됨을 볼 수 있다.

비클러스터형 인덱스를 생성되는 환경에는 두 가지 방법이 있다.

nonclustered 조건 추가하기
기본 Index 설정

-- By using the non-clustered index
CREATE NONCLUSTERED INDEX Index_이름 ON 테이블명 ([컬럼명, ...]);

CREATE INDEX Index_이름 ON 테이블명 ([컬럼명, ...]);

ALTER TABLE mixedtbl
ADD CONSTRAINT UK_mixedtbl_name UNIQUE (name);

클러스터 + 보조 인덱스 조회

'임'재범을 조회하기 위해 어떤식으로 조회가 되는지 살펴보자.
먼저 보조 인덱스 페이지에서 매핑에 따라 가다가 임재범 LJB를 발견하면, 다시 클러스터 인덱스 루트 페이지로 가서 LJB로 검색하여 찾아들어가게 된다.
따라서 총 4페이지를 읽게 된다.

이런식으로 징검다리식으로 구성하는 이유는 데이터 삽입 삭제에 있어 보조 인덱스 페이지의 부담을 많이 줄이기 위해서이다.

위의 Clustered Index + Secondary Index 구성을 보면, 보조 인덱스의 리프페이지에 데이터의 주소가 아닌 PK를 저장한다.

왜 RID를 저장하지 않고 PK를 저장할까?

본래처럼 RID를 저장한다면 Index를 끝까지 탐색하고 데이터로 바로가니깐 3페이지만 읽으면 데이터를 찾을 수 있다.

그러나 PK를 저장해 버려서 루트페이지도 읽고 리프 페이지를 읽어야하니 4페이지를 읽게된다.

오히려 성능이 저하되게 되는데 왜 그런 것일까?

그 이유는 데이터의 RID를 저장하게 되면 삽입 삭제를 할 때 INDEX의 PAGE가 완전 뒤집어져야하기 때문이다.

데이터를 추가하게 되면 데이터가 정렬되어야 하기 때문에 RID가 바뀌게 될 것이고 엄청나게 많은 데이터들의 RID가 바뀌게 될지도 모른다. 그렇게 되면 Secondary INDEX 페이지 또한 싹 갈아엎어야한다.

하지만 PK를 저장해 놓는다면 약간의 정렬만 하면 된다.

즉, 검색으로 얻는 이득 보다, 삽입 삭제시 잃는 성능이 더 크기 때문에 RID말고 PK를 저장하게 구성 된 것이다.

인덱스의 관리

앞서 설명했듯이 인덱스는 항상 최신의 데이터를 정렬된 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있다. 그렇기 때문에 인덱스가 적용된 컬럼에 INSERT, UPDATE, DELETE가 수행된다면 계속 정렬을 해주어야 하고 그에 따른 부하가 발생한다. 이런 부하를 최소화하기 위해 인덱스는 '데이터 삭제'라는 개념에서 '인덱스를 사용하지 않는다'라는 작업으로 이를 대체한다.

INSERT : 새로운 데이터에 대한 인덱스를 추가한다.
DELETE : 삭제하는 데이터의 인덱스를 "사용하지 않는다"는 작업을 진행한다.
UPDATE : 기존의 인덱스를 "사용하지 않음" 처리하고, 갱신된 데이터에 대해 인덱스를 추가한다.

그렇다면 이런 인덱스를 활용하면 무슨 장단점이 있을까?

인덱스를 사용하는 이유 (장점)

Table에 데이터를 지속적으로 저장하게 되면 내부적으로 순서 없이 쌓이게 된다. 이 경우에 특정 조건을 만족하는 데이터를 찾고자 WHERE절을 사용한다면 Table의 row(record)를 처음부터 끝까지 모두 접근하여 검색조건과 일치하는지 비교하는 과정이 필요하다. 이를 Full Table Scan이라고 한다.

하지만 특정 column에 대한 Index를 생성해 놓은 경우 해당 속성에 대하여 search-key가 정렬되어 저장되어 있기 때문에 조건 검색(SELECT ~ WHERE) 속도가 굉장히 빠르다.

즉, 인덱스의 가장 큰 특징은 데이터들이 정렬이 되어있다는 점이다. 이 특징으로 인해 조건 검색이라는 영역에서 굉장한 장점이 된다.

아래 세 가지 이유 모두 데이터들이 정렬되었다는 장점을 관통하는 내용들이다.

① 조건 검색 WHERE 절의 효율성

테이블을 만들고 안에 데이터가 쌓이게 되면 테이블의 레코드(row : 행)는 내부적으로 순서가 없이 뒤죽박죽으로 저장이 된다. 이렇게 되면 WHERE절에 특정 조건에 맞는 데이터들을 찾아낼 때도 레코드의 처음부터 끝까지 다 읽어서 검색 조건과 맞는지 비교해야 한다. 이것을 풀 테이블 스캔 (Full Table Scan), 줄여서 풀 스캔(Full Scan)이라고 한다. 하지만 인덱스 테이블 스캔(Index Table Scan) 시 인덱스 테이블은 데이터들이 정렬되어 저장되어 있기 때문에 해당 조건(WHERE)에 맞는 데이터들을 빠르게 찾아낼 수 있는 것이다. 이것이 인덱스를 사용하는 가장 큰 이유이다.

② 정렬 ORDER BY 절의 효율성

인덱스를 사용하면 ORDER BY에 의한 정렬(Sort) 과정을 피할 수가 있다. ORDER BY는 굉장히 부하가 많이 걸리는 작업이다. 정렬과 동시에 1차적으로 메모리에서 정렬이 이루어지고 만약 메모리보다 큰 작업이 필요하다면 *디스크 I/O도 추가적으로 발생되기 때문이다. 하지만 인덱스를 사용하면 이러한 전반적인 자원의 소모를 하지 않아도 된다. 왜? 이미 정렬이 되어 있기 때문에 가져오기만 하면 되기 때문이다.

※ 참고

[디스크 I/O]

간단하게 말해 우리가 데이터를 작성하고 변경할 때 디스크 즉, HDD에 저장되는 것을 말한다. (디스크 I/O에 대해서는 나중에 다시 공부해볼 필요가 있을 것 같다.)

③ MIN, MAX의 효율적인 처리가 가능하다

이것 또한 데이터가 정렬되어 있기에 얻을 수 있는 장점이다. MIN값과 MAX값을 레코드의 시작 값과 끝 값 한 건씩만 가져오면 되기 때문에 Full Table Scan으로 테이블을 모두 뒤져서 작업하는 것보다 훨씬 효율적으로 찾을 수 있다.

인덱스를 사용하면 무조건 효율이 좋을까? (단점)

인덱스가 주는 혜택이 있으면 그에 따른 부작용도 있다. 인덱스의 가장 큰 문제점은 정렬된 상태를 계속 유지시켜줘야 한다는 점이다. 그렇기에 레코드 내에 데이터 값이 바뀌는 부분이라면 악영향을 미친다.

Index의 단점으로는 크게 두 가지가 있다.

추가 저장공간 필요

index를 생성하면 index 자료구조를 위한 저장 공간이 추가적으로 필요하다. 보통 table의 크기의 10%정도의 공간을 차지한다.

느린 데이터 변경 작업

검색이 아닌 데이터 변경을 할 때, 즉 INSERT, UPDATE, DELETE가 자주 발생하면 성능이 나빠질 수 있다. 그 이유는 보통 B+tree구조의 index는 데이터가 추가 삭제 될 때마다 tree의 구조가 변경될 수 있기 때문이다. 즉, 인덱스의 재구성이 필요하기 때문에 추가적인 자원이 소모된다.

① 인덱스는 DML에 취약

만약 INSERT, UPDATE, DELETE가 빈번한 속성에 인덱스를 걸게 되면 인덱스의 크기가 비대해져서 성능이 오히려 저하되는 역효과가 발생할 수 있다. 그러한 이유 중 하나는 DELETE와 UPDATE 연산 때문이다. 앞에서 설명한대로, UPDATE와 DELETE는 기존의 인덱스를 삭제하지 않고 '사용하지 않음' 처리를 해준다고 하였다. 만약 어떤 테이블에 UPDATE와 DELETE가 빈번하게 발생된다면 실제 데이터는 10만건이지만 인덱스는 100만 건이 넘어가게 되어, SQL문 처리 시 비대해진 인덱스에 의해 오히려 성능이 떨어지게 될 것이다.

또한 INSERT, UPDATE, DELETE를 통해 데이터가 추가되거나 값이 바뀐다면 B+tree구조의 tree 구조가 변경될 수 있기 때문이다. 즉, 인덱스의 재구성이 필요하기 때문에 추가적인 자원이 소모된다.

그리고 위에 사진처럼 인덱스 테이블, 원본 테이블 이렇게 두 군데의 데이터 수정 작업을 해줘야 한다는 단점도 발생한다. 그렇기 때문에 DML이 빈번한 테이블보다 검색을 위주로 하는 테이블에 인덱스를 생성하는 것이 좋다.

② 무조건 인덱스 스캔이 좋은 것은 아니다

검색을 위주로 하는 테이블에 인덱스를 생성하는 것이 좋지만 무조건 검색 시에도 인덱스가 좋은 것은 아니다. 인덱스는 테이블의 전체 데이터 중에서 10~15% 이하의 데이터를 처리하는 경우에만 효율적이고 그 이상의 데이터를 처리할 땐 인덱스를 사용하지 않는 것이 더 낫다.

직관적인 예시를 들자면 1개의 데이터가 있는 테이블과 100만 개의 데이터가 들어 있는 테이블이 있다고 하자. 100만 개의 데이터가 들어있는 테이블이라면 풀 스캔보다는 인덱스 스캔이 유리하겠지만, 1개의 데이터가 들어있는 테이블은 굳이 인덱스 스캔 없이 풀 스캔이 빠를 것이다.

③ 속도 향상을 위해 인덱스를 많이 만드는 것은 좋지 않다.

인덱스를 관리하기 위해서는 데이터베이스의 약 10%에 해당하는 저장공간이 추가로 필요하다. 무턱대고 인덱스를 만들어서는 결코 안 된다는 것이다. 즉, 속도 향상에 비해 단점들의 COST를 비교해서 인덱스를 만들지 말지를 정해야 한다.

💡 Index를 남발하지 말아야 하는 이유

데이터베이스 서버에 성능 문제가 발생하면 가장 빨리 생각하는 해결책이 인덱스 추가 생성이다.
문제가 발생할 때마다 인덱스를 생성하면서 인덱스가 쌓여가는 것은 하나의 쿼리문을 빠르게는 만들 수 있지만,전체적인 데이터베이스의 성능 부하를 초래한다.

조회 성능을 극대화하려 만든 객체인데 많은 인덱스가 쌓여서 INSERT, UPDATE, DELETE 시에 부하가 발생해 전체적인 데이터베이스 성능을 저하한다.

그렇기에 인덱스를 생성하는 것보다는 SQL문을 좀 더 효율적으로 짜는 방향으로 나가야 한다.인덱스 생성은 마지막 수단으로 강구해야 할 문제이다.

인덱스 생성 전략 TIP

인덱스의 원리를 알았으니 이제 언제 어느떄에 인덱스를 써야 효율적으로 사용한것이 되는지 설계의 핵심을 알아보자.

우선 보편적으로 index는 where 절에서 자주 조회되고, 수정 빈도가 낮으며, 카디널리티는 높고, 선택도가 낮은 column을 선택해서 설정하는 것이 가장 좋다.

기준	적합성
카디널리티(Cadinality)	높을수록 적합 (데이터 중복이 적을수록 적합)
선택도(Selecticity)	낮을수록 적합
조회 활용도	높을수록 적합 (where 절에서 많이 사용되면 적합)
수정 빈도	낮을수록 적합

카디널리티
카디널리티란 데이터가 중복되지 않는 정도를 뜻한다. 예를 들어, 주민등록번호는 중복되는 값이 없으므로 cardinality가 높다. 하지만 성별의 경우, [남자/여자] 값이 중복되어 등장하기 때문에 cardinality가 낮다.

선택도
선택도는 데이터에서 특정 값을 잘 골라낼 수 있는 정도를 뜻한다. 선택도가 1이면 모든 데이터가 unique함을 의미한다.

효율적인 인덱스 설계

생성된 인덱스를 가장 효율적으로 사용하려면 데이터의 분포도는 최대한으로 그리고 조건절에 호출 빈도는 자주 사용되는 컬럼을 인덱스로 생성하는 것이 좋다.

인덱스는 특정 컬럼을 기준으로 생성하고 기준이 된 컬럼으로 정렬된 인덱스 테이블이 생성된다. 이 기준 컬럼은 최대한 중복이 되지 않는 값이 좋다. 가장 최선은 PK로 인덱스를 거는 것이라고 할 수 있다.

중복된 값이 없는 인덱스 테이블이 최적의 효율을 발생시키겠고, 반대로 모든 값이 같은 컬럼의 인덱스 컬럼이 된다면 인덱스로써의 가치가 없다고 봐야 할 것이다.

규모가 작지 않은 테이블
- 데이터의 양이 많을 수록 index로 인한 성능향상이 더 크다. 데이터 양이 적다면 index의 혜택보단 손해가 더 클 수 있다.
WHERE 절에 사용되는 열 (WHERE 절에 사용되는 열이라도 자주 사용해야 가치가 있음)
SELECT 절에 자주 등장하는 컬럼들을 잘 조합해서 INDEX로 만들어두면 INDEX 조회 후 다시 데이터에서 조회할 필요가 없으므로 빠르게 검색이 가능하다.
JOIN절 조건으로 자주 사용되는 열에는 인덱스의 효율이 좋음.
ORDER BY 절에 사용되는 열은 데이터 페이지가 자동 정렬됐기 때문에 클러스터형 인덱스가 유리

※ 참고

외래키는 자동으로 외래키 인덱스 만듬

금지해야 할 인덱스 설계

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

※ 참고

데이터의 중복도

중복도가 높은 경우, 인덱스를 사용하는 것이 효율이 없지는 않지만 어차피 데이터를 읽기 위해 많은 페이지를 읽어야 하는 것은 마찬가지이기 때문에 피해야 한다.
예를 들어 성별이라는 컬럼에 INDEX를 만들어두면 남,여 밖에 없기 때문에 중복도는 높고 분포도는 낮다.
따라서 데이터의 종류가 별로 없기 때문에, 남자를 검색할 때 절반이나 되는 ROW를 검색해야하고 결국 모든 ROW를 검색하는 table full scan이 더 나을지도 모른다.
index를 봤다가 데이터를 봤다가 x 100000 을 하는게 더 느릴 수 있기 때문이다.
더군다나 인덱스 관리 비용이나 INSERT 구문으로 인한 성능 저하등을 고려하면 반드시 필요하지는 않다.

인덱스를 사용할 때 주의할 점

데이터 변경(삽입, 수정, 삭제) 작업이 얼마나 자주 일어나는지 고려해야 함.
- insert / update / delete 작업 시, 데이터에 변화가 생기기 때문에 index에서는 매번 정렬을 다시 해야한다. 이에 따른 부하가 발생하기 때문에 수정 빈도가 낮은 column을 index로 설정하면 좋다.
단일 테이블에 인덱스가 많으면 속도가 느려질 수있다. (테이블당 4~5개 권장)
검색할 데이터가 전체 데이터의 20% 이상이라면, MySQL에서 인덱스를 사용하지 않음. (강제로 사용할 시 성능 저하를 초래할 수 있음)
전체 페이지의 대부분을 읽어야 하고, 인덱스 관련 페이지도 읽어야 해서 작업량이 크기 때문이다.
사용하지 않는 인덱스는 제거하는 것이 바람직함. (실무에서 사용하지 않는 보조 인덱스를 몇개 삭제했을 때 성능이 향상되는 경우도 많음)
클러스터형 인덱스는 테이블당 하나만 생성할 수 있음
테이블에 클러스터형 인덱스가 아예 없는 것이 좋은 경우도 있음

※ 참고

INDEX 손익분기점

테이블이 가지고 있는 전체 데이터양의 10% ~ 15%이내의 데이터가 출력 될 때만 INDEX를 타는게 효율적이고, 그 이상이 될 때에는 오히려 풀스캔이 더 빠르다.

인덱스가 안 되는 쿼리

INDEX를 만들어 둔다고 모든 쿼리에서 INDEX를 활용하는 것은 아니다.

인덱스 입힌 컬럼을 가공
- WHERE SUBSTR(컬럼명, 1,4) = ‘2019’
- 해결 → WHERE 컬럼명 LIKE ‘2019%’
인덱스가 있는 열 이름에는 함수나 연산을 가함
- 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’)

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 절에 다른 표현을 사용한 경우

인덱스 문법 정리

인덱스 확인

테이블에 등록된 인덱스 확인

SHOW INDEX
FROM 테이블이름

Table : 테이블의 이름을 표시함.
Non_unique : 인덱스가 중복된 값을 저장할 수 있으면 1, 저장할 수 없으면 0을 표시함.
Key_name : 인덱스의 이름을 표시하며, 인덱스가 해당 테이블의 기본 키라면 PRIMARY로 표시함.
Seq_in_index : 인덱스에서의 해당 필드의 순서를 표시함.
Column_name : 해당 필드의 이름을 표시함.
Collation : 인덱스에서 해당 필드가 정렬되는 방법을 표시함.
Cardinality : 인덱스에 저장된 유일한 값들의 수를 표시함.
Sub_part : 인덱스 접두어를 표시함.
Packed : 키가 압축되는(packed) 방법을 표시함.
Null : 해당 필드가 NULL을 저장할 수 있으면 YES를 표시하고, 저장할 수 없으면 ''를 표시함.
Index_type : 인덱스에 사용되는 메소드(method)를 표시함.
Comment : 해당 필드를 설명하는 것이 아닌 인덱스에 관한 기타 정보를 표시함.
Index_comment : 인덱스에 관한 모든 기타 정보를 표시함.

테이블의 인덱스 크기 확인

show table status like 테이블명

인덱스 생성

Create Table 내

테이블을 생성 (create table) 할때 같이 제약조건과 지정해 주어서 인덱스를 생성하는 방식

CREATE TABLE books (
  -- 같이 지정
  id varchar(5) primary key, -- 기본키 지정 (클러스터 인덱스)
  name varchar(20) unique, -- 인덱스 생성 (보조 인덱스) (중복 비허용)
  writer varchar(20) NOT NULL,
  
  INDEX idx_test (writer asc) -- 인덱스 생성 (보조 인덱스)
);

CREATE TABLE books (
  id varchar(5) primary key,
  name varchar(20) NOT NULL,
  writer varchar(20) NOT NULL,
  
  -- 뒤에 따로 할당
  [CONSTRAINT 제약조건이름] PRIMARY KEY (id), -- 기본키 지정 (클러스터 인덱스)
  [CONSTRAINT 제약조건이름] unique (id) -- 인덱스 생성 (보조 인덱스) (중복 비허용)
  INDEX idx_test (writer asc) -- 인덱스 생성 (보조 인덱스)
);
-- INDEX <인덱스명> ( 칼럼명1, 칼럼명2 )

[ unique 보조 인덱스 와 primary key 클러스터 인덱스 와의 차이 ]

- unique와 primary key는 둘다 중복 비허용
- unique은 null 허용하지만, primary key는 null 허용 안함
- unique은 하나의 테이블에 여러개 올 수 있지만, primary key는 하나만 존재

특정 테이블에 Primary Key 가 존재하면서 UNIQUE KEY 가 존재할 경우
- Primary Key 로 지정된 컬럼은 클러스터드 인덱스가 된다.
- Unique Key 로 지정된 컬럼들은 보조 인덱스가 된다.
- Unique Key 로 지정된 컬럼이 NULL 을 허용하던 허용하지 않던 상관없이 모두 보조 인덱스가 된다.

특정 테이블에 Primary Key 가 존재하지 않으며 특정 컬럼에 UNIQUE NOT NULL 제약이 지정될 경우
- 해당 컬럼은 클러스터드 인덱스가 된다.
- NOT NULL 조건이 없다면 보조 인덱스가 된다.

※ 참고

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

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

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

Create Index 문

인덱스를 생성하는 대표 문법
UNIQUE는 고유한 인덱스를 만들 것인지 결정. 동일한 데이터 값이 입력될 수 없음.
디폴트는 UNIQUE가 생략된 중복이 허용되는 인덱스다.
FULLTEXT : 전체 텍스트 인덱스,
SPATIAL : 점,선,명 등 공간 데이터와 관련된 인덱스 생성에 쓰임.
CREATE INDEX로 생성하는건 보조 인덱스로 생성됨.
생성된 인덱스를 실제 적용시키려면 ANALYZE TABLE tbl_name 으로 테이블을 분석/처리해줘야함.

-- 따로 인덱스 생성

CREATE INDEX 인덱스명 ON 테이블명 (컬럼명); -- 보조 인덱스 생성 (중복 허용)

CREATE UNIQUE INDEX 인덱스명 ON 테이블명 (컬럼명); -- 보조 인덱스 생성 (중복 비허용)

CREATE FULLTEXT INDEX 인덱스명 ON 테이블명 (컬럼명); -- 클러스터 인덱스 생성

CREATE UNIQUE INDEX 인덱스명 ON 테이블명 (컬럼명1, 컬러명2); -- 다중 컬럼 인덱스 생성

ANALYZE TABLE 테이블명; -- !! 생성한 인덱스 적용 !!

Alter ... Add Index 문

create index 문과 같이 똑같이 alter문을 통해 인덱스 생성 가능

ALTER TABLE 테이블이름
ADD INDEX 인덱스이름 (필드이름)
-- 중복을 허용하는 인덱스.
-- 보조 인덱스.
-- 가장 느리지만 인덱스 안한 컬럼 조회하는 것보다 인덱스 붙인 컬럼 조회하는게 더 빠르다. 여러개 노멀키 를 지정할수 있다.


ALTER TABLE 테이블이름
ADD UNIQUE INDEX 인덱스이름 (필드이름)
-- 중복을 허용하지 않는 유일한 키. null 허용. 
-- 보조 인덱스.
-- 고속으로 조회 가능


ALTER TABLE 테이블이름
ADD PRIMARY KEY INDEX 인덱스이름 (필드이름)
-- 중복되지 않은 유일한 키. null 비허용. 
-- 클러스터 인덱스
-- where로 데이터를 조회할때 가장 고속으로 조회


ALTER TABLE 테이블이름
ADD FULLTEXT INDEX 인덱스이름 (필드이름)
-- 풀텍스트 인덱스
-- 긴 문자열 데이터를 인덱스로 검색할 때 사용.

인덱스 사용 확인

내가 쿼리한 select문이 인덱스를 사용해서 조회했는지 확인하는 방법을 워크벤치에서 따로 메뉴를 제공해준다.

select * from 테이블명 where 인덱스적용한컬럼명 = '데이터' ;

인덱스 삭제

보조 인덱스 삭제

DROP INDEX 인덱스이름 ON 테이블이름
-- DROP 문은 내부적으로 ALTER 문으로 자동 변환되어 명시된 이름의 인덱스를 삭제

ALTER TABLE 테이블이름
DROP INDEX 인덱스이름

클러스터 인덱스 삭제 (primary key 삭제)

기본 키로 설정된 클러스터형 인덱스 이름은 항상 'PRIMARY'로 되어있으므로, 삭제할때 index_name 부분에 PRIMARY라고 써주면 됨

※ 참고
인덱스를 모두 제거할 때는 되도록 보조 인덱스 부터 삭제하고 클러스터 인덱스를 삭제하도록 한다.
클러스터 인덱스를 먼저 삭제하면 보조 인덱스가 다시 다 재구성이 되기 때문이다.

DROP INDEX PRIMARY ON 테이블이름

ALTER TABLE 테이블이름
DROP PRIMARY KEY; -- 만일 외래키와 연결이 되어있을 경우 제약조건에 의해 삭제가 안될수 있음

-- 따라서 먼저 외래키를 삭제 후 클러스터 인덱스 (primary key)를 삭제 하면 됨

-- 먼저 외래키명을 얻어서 (constraint_name)
select table_name, constraint_name
from information_schema.referential_constraints
where constraint_shcema = 디비명

alter table 테이블2 drop foreign key 외래키명; -- 외래키 삭제
alter table 테이블 drop primary key; -- 클러스터 인덱스 삭제

인덱스 정렬

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

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

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

다중 컬럼 인덱스

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

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

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

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

단일 인덱스 / 다중 컬럼 인덱스 차이점

Table1 (단일 인덱스)

CREATE TABLE table1(
    uid INT(11) NOT NULL auto_increment,
    id VARCHAR(20) NOT NULL,
    name VARCHAR(50) NOT NULL,
    address VARCHAR(100) NOT NULL,
    PRIMARY KEY('uid'),
    INDEX idx_name(name), -- 단일 인덱스
    INDEX idx_address(address) -- 단일 인덱스
)

Table2 (다중 컬럼 인덱스)

CREATE TABLE table2(
    uid INT(11) NOT NULL auto_increment,
    id VARCHAR(20) NOT NULL,
    name VARCHAR(50) NOT NULL,
    address VARCHAR(100) NOT NULL,
    PRIMARY KEY('uid'),
    INDEX idx_name(name, address) -- 다중 컬럼 인덱스 
)

QUREY문

SELECT * FROM table1 WHERE name='홍길동' AND address='경기도';

table1의 경우에 각각 컬럼(name),(address)에 INDEX가 걸려있기 때문에, MySQL은 name 컬럼과 address 컬럼을 보고 둘 중에 어떤 컬럼의 수가 더 빠르게 검색되는지 판단 후 빠른쪽을 먼저 검색하고 그 다음 다른 컬럼을 검색하게 된다.
table2의 경우 바로 원하는 값을 찾는데 그 이유는 INDEX를 저장할 때 name과 address를 같이 저장하기 때문이다. 즉, name과 address의 값을 같이 색인하고 검색에서도 '홍길동경기도'로 검색을 시도하게 된다.
이렇게 사용할 경우 table1보다 table2의 경우가 더 빠른 검색을 할 수 있다.

그렇지만 다중 컬럼 인덱스를 아래와 같이 하나만 조회에 사용하면 INDEX를 타지 않는다.

SELECT * FROM table2 WHERE address='경기도';

이 경우에는 다중 컬럼 인덱스로 설정되어 있던 name이 함께 검색이 되지 않으므로 INDEX의 효과를 볼 수가 없다.

위에서는 인덱스에 대해 간단히 핵심만 다루고 있지만 PK(기본키, Primary Key)와 인덱스(Index)에 대해서 자세히 알고 있는 것은 굉장히 중요하다. PK(기본키, Primary Key)와 인덱스(Index)에 대한 자세한 내용은 아래 포스팅을 참고.

Q. 데이터를 검색을 할 때 hash table의 시간복잡도는 O(1)이고 b+tree는 O(logn)으로 더 느린데 왜 index는 hash table이 아니라 b+tree로 구현되나?

Hash table을 사용하면 하나의 데이터를 탐색 하는 시간은 O(1)로 b+tree보 다 빠르지만, 값이 정렬되어 있지 않기 때문에 부등호를 사용 하는 query에 대해서는 매우 비효율적이게 되어 데이터를 정렬해서 저장하는 b+tree를 이용한다.

참고

개요