[MYSQL] 📚 인덱스(index) 핵심 설계 & 사용 문법 💯 총정리

인덱스의 개념

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

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

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

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

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

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

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

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

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

 

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

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

---

인덱스 알고리즘 종류

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

 

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

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

 

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

---

B-Tree 인덱스 알고리즘

mysql에서 사용하는 B-Tree 인덱스는 칼럼의 값을 변형하지 않고, 원래의 값을 이용해 인덱싱하는 알고리즘이다.

 

B-Tree 는 최상위에 하나의 루트 노드가 존재하고 그 하위에 자식 노드가 붙어있는 형태이다.

트리 구조의 가장 하위에는 리프 노드라고 하고 트리구조에서 루트노드도 아니고 리프노드도 아닌 중간의 노드를 브랜치 노드라고 한다. 

 

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

 

자세한 트리 동작 원리를 알고싶다면 아래 영상을 참고하길 바란다.

---

Hash 인덱스 알고리즘

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

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

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

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

 

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

그래서 타 인덱스 대비 조회 속도가 매우 빠르다. 

그러나 위와는 반대로, 각 해쉬값에 주소값을 배정하는 인덱스의 특징에 따라 범위로 조회하는 작업은 느리다.

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

---

Fractal-Tree 알고리즘 (TokuDB)

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

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

---

인덱스 타입 종류

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

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

각 인덱스의 특징에 따라 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 (조관우) 만 찾고 싶다면 2 페이지만 뒤지면 조회가 가능하다.

 

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

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

 

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

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

---

보조 인덱스 (Secondary Index)

• 이 인덱스는 논 클러스터 인덱스 (non-clustered index) 라고도 불린다. 
• 개념적으로는 후보키에만 부여 할 수 있는 인덱스다.
  (후보키 : 고유 식별 번호, 주민번호 같이 각 데이터를 인식할 수 있는 최소한의 고유 식별 속성 집합)
• 보조 인덱스의 생성시에는 데이터 페이지는 그냥 둔 상태에서 별도의 페이지에 인덱스를 구성한다.
• 별도의 페이지에서 인덱스를 구성하니, 클러스터와는 달리 자동 정렬을 하지 않는다.
• 클러스터 인덱스의 리프 페이지는 보조 인덱스의 인덱스 자체의 리프 페이지는 데이터가 아니라 데이터가 위치하는 주소값 (RID)
• 클러스터형 보다 검색 속도는 더 느리지만 데이터의 입력/수정/삭제는 덜 느리다.
• 보조 인덱스는 여러 개 생성할 수 있다. 그러나 함부로 사용할 경우에는 오히려 성능을 떨어뜨릴 수 있다.
• 각 데이터에 대해서 고유 값 (unique) 들이 있는 목록에 생성 할 수 있는 인덱스다. (unique key)

 

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

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

 

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

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

 

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

• 유저 아이디가 A ~ J 인 사용자를 모두 검색해본다
• 무려 5 페이지나 읽어 검색해야 된다.

 

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

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

---

클러스터 + 보조 인덱스

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

 

클러스터 인덱스 생성

ALTER TABLE mixedtbl
ADD CONSTRAINT PK_mixedtbl_userID PRIMARY KEY (userID);

 

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

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

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를 저장하게 구성 된 것이다.

---

인덱스 설계 핵심 TIP

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

 

효율적인 인덱스 설계

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

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

 

금지해야 할 인덱스 설계

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

데이터의 중복도

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

 

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

• 데이터 변경(삽입, 수정, 삭제) 작업이 얼마나 자주 일어나는지 고려해야 함.
• 단일 테이블에 인덱스가 많으면 속도가 느려질 수있다. (테이블당 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해서 사용한다. 

다중 컬럼 인덱스는 단일 컬럼 인덱스 보다 더 비효율적으로 INDEX/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의 효과를 볼 수가 없다.