MySQL INDEX 관리

Recommanded Free YOUTUBE Lecture: <% selectedImage[1] %>

yundream
2023-03-11
2023-03-11
1727

## Index
데이터베이스에서 색인(Index)는 데이터베이스 **테이블에 대한 쿼리 성능을 향상**시키기 위해서 사용하는 데이터 구조다.

기본개념은 원하는 위치를 빠르게 찾기 위해서 사용하는 책의 색인과 유사하다. 데이터베이스의 색인을 이용하면 테이블에서 데이터를 빠르게 찾을 수 있다.

테이블은 여러개의 컬럼(column)으로 구성되는데, 컬럼별로 인덱스를 생성 할 수 있다. 특정 필드에 대한 인덱스를 생성하면, 해당 필드를 검색할 때 전체 테이블을 스캔하는 대신 일치하는 행을 빠르게 찾을 수 있다.

색인 구조는 **B-Tree**, **해시 테이블(Hash table)** 혹은 **비트맵**과 같은 다양한 방식으로 구성할 수 있다. 색인 유형은 데이터의 특성과 쿼리 패턴에 따라 달라진다.
1. B-Tree : 데이터베이스 색인에서 일반적으로 사용하는 균형잡힌트리(balanced tree structure)다. B-Tree는 색인된 데이터를 트리형식의 계층 구조로 구성한다. 각 노드에는 노드에 대한 값과 포인터의 범위를 포함하며, 이 트리구조를 따라서 관련데이터를 빠르게 찾을 수 있다. 이런 특성에 따라서 범위 쿼리 및 부분일치등에 적합하다.
2. 해시 테이블 : 해시테이블은 해시함수를 사용해서 색인에 키를 맵핑한다. 해시 테이블의 경우 색인데이터가 메모리에 저장되기 때문에 메모리기반 색인에 자주사용한다. 해시테이블은 전체 데이터가 정확히 일치할 경우 빠르게 찾지만 범위 쿼리와 부분일치에는 비효율적이다.
3. 비트맵 : 값의 존재여부를 일련의 비트로 나타내는 색인방식이다. 비트맵은 크기의 제한이 있기 때문에 low cardinality(개별 값이 제한적인) 데이터에는 적합하지만 cardinality가 높은 데이터에는 비효율적이다.

## CREATE INDEX 문법
```sql
CREATE INDEX index_name
ON table_name (column1, column2, ...);
```

## CREATE UNIQUE INDEX 문법
```sql
CREATE UNIQUE INDEX index_name  
ON table_name (column1, column2, ...);
```
UNIQUE INDEX는 **하나의 컬럼 혹은 두 개 이상의 조합된 컬럼의 색인 값이 동일한 값을 가지지 않는 제약조건**을 가지는 색인이다. 즉 Unique Index는 해당 열의 값이 테이블의 모든 행에서 고유해야 한다. 중복데이터를 입력하려하면 **Duplicate entry** 에러가 발생한다.

## Example
#### CREATE INDEX
유저 정보를 저장하고 있는 테이블이 있다. 
```sql
CREATE TABLE User (
  id int NOT NULL,
  name varchar(32) NOT NULL,
  email varchar(32) NOT NULL,
  address varchar(128) NOT NULL
);
```
여기에는 **email** 컬럼도 있는데, 유저가 늘어나면서 email로 검색하는 속도가 느려지고 있다. 그래서 email 컬럼에 대한 색인을 만들기로 했다. 데이터베이스 관리자는 **idx_email** 이름으로 색인 테이블을 만들었다.
```sql
CREATE INDEX idx_email
ON User (email);
```

사용자들이 name과 email을 조합하여 검색하는 경우가 많아지고 있다. 그래서 name과 email을 조합하여 색인을 만들어서 쿼리 속도를 높이라는 요구사항이 전달됐다. 관리자는 idx_name_email 이름의 색인을 만들었다.
```sql
CREATE INDEX idx_name_email 
ON User (email, name);
```

#### SHOW INDEX
**SHOW INDEX** 쿼리로 특정 테이블의 색인 정보를 확인할 수 있다. User Table의 색인정보를 확인해보자.
```sql
SHOW INDEXES FROM User;
```
![mysql show index](https://docs.google.com/drawings/d/e/2PACX-1vRhd6ICXE76MxBR2deR5DXhWgBPU20K-7lc_Qz6xsEuMz5PZ3pLyj24ra55yAmrEhGRq_2V92TUp3cN/pub?w=1306&h=153)

#### DROP INDEX
**DROP INDEX** 쿼리로 색인을 삭제할 수 있다. 색인 테이블은 색인정보를 저장하기 위한 추가적인 저장 공간이 필요하다. 관리자는 더 이상 사용하지 않는 idx_name_email 색인 테이블을 삭제하기로 했다. 
```sql
DROP INDEX idx_name_email ON User;
```

## 참고
MySQL과 관련된 다른 문법들은 [SQL Study With MYSQL - 목차](https://www.joinc.co.kr/w/sql_study_with_mysql_index)를 참고하자.

Search For:

BY TAGS

Recent Posts

Archive Posts

Tags

About

Get in Touch

Categories