[MySQL] Index 개념

Index

테이블에서 원하는 데이터를 빠르게 찾기 위해 사용하는 자료구조로, 검색에 자주 사용하는 필드 값으로 만들어진 테이블의 사본이라고 생각할 수 있다. 특정 컬럼에 인덱스를 생성하면 해당 컬럼의 데이터를 정렬하여 별도의 메모리 공간에 데이터의 물리적 주소와 함께 저장된다.

 

주로 책의 맨 처음 또는 마지막에 포함되어 있는 색인에 비유된다. 

MySQL은 데이터를 검색할 때 첫 번째 필드부터 차례대로 테이블 전체를 검색한다. 하지만 인덱스를 이용하면 인덱스에서 검색할 데이터가 어디에 있는지 확인하고, 데이터가 저장된 물리적 주소에서 데이터를 가져오는 방식으로 동작하기 때문에 빠른 검색을 기대할 수 있다.

 

 

사용 이유

인덱스의 가장 큰 특징은 데이터가 정렬되어 있다는 점이다.

• WHERE 절의 효율성
• ORDEY BY의 효율성
• 효율적인 MIN, MAX 처리

 

다만 인덱스는 별도의 저장 공간을 필요로 한다.

또한 데이블에 데이터의 삽입, 삭제, 수정 작업이 이뤄지는 경우 인덱스도 함께 수정되어야 한다. 따라서 수정이 잦은 테이블보다 검색이 잦은 테이블에서 사용하는 것을 권장한다.

 

 

SQL

예시 테이블

CREATE TABLE IF NOT EXISTS `docs` (
  `id` int(6) unsigned NOT NULL,
  `rev` int(3) unsigned NOT NULL,
  `content` varchar(200) NOT NULL,
  PRIMARY KEY (`id`,`rev`)
) DEFAULT CHARSET=utf8;

 

인덱스 생성

CREATE INDEX <index_name>
ON <table_name (field, field, ...)>

-- 예시
CREATE INDEX idx_docs_id
ON docs (id);

여러 필드를 가지는 인덱스는 필드의 이름을 쉼표로 나열한다.

 

ALTER 문으로도 테이블에 인덱스를 추가할 수 있다.

ALTER TABLE <table_name>
ADD INDEX <index_name (field, field, ...)>

 

테이블 인덱스 조회

SHOW INDEX
FROM <table_name>;

-- 예시
SHOW INDEX
FROM docs;

일반적인 DBMS에서 PK는 자동으로 Index가 적용된다. 예시 테이블은 id, rev가 PK로 설정되어 있어 PRIMARY라는 이름으로 인덱스가 생성되어 있는 것을 확인할 수 있다.

 

반환되는 인덱스 정보의 필드 값의 의미는 아래와 같다.

1. Table : 테이블의 이름을 표시

2. Non_unique : 인덱스가 중복된 값을 저장할 수 있으면 1, 저장할 수 없으면 0을 표시

3. Key_name : 인덱스의 이름을 표시하며, 인덱스가 해당 테이블의 기본 키라면 PRIMARY로 표시함.

4. Seq_in_index : 인덱스에서의 해당 필드의 순서를 표시

5. Column_name : 해당 필드의 이름을 표시

6. Collation : 인덱스에서 해당 필드가 정렬되는 방법을 표시

7. Cardinality : 인덱스에 저장된 유일한 값들의 수를 표시

8. Sub_part : 인덱스 접두어를 표시

9. Packed : 키가 압축되는(packed) 방법을 표시

10. Null : 해당 필드가 NULL을 저장할 수 있으면 YES를 표시하고, 저장할 수 없으면 ''를 표시

11. Index_type : 인덱스에 사용되는 메소드(method)를 표시

12. Comment : 해당 필드를 설명하는 것이 아닌 인덱스에 관한 기타 정보를 표시 

13. Index_comment : 인덱스에 관한 모든 기타 정보를 표시

 

 

+ 💡 

DBeaver 같은 도구를 사용하다면, TABLE의 DDL 정보에서도 생성한 인덱스 정보를 확인할 수 있다.
DDL에서 INDEX는 PK 아래에 KEY라는 키워드로 나열된다.

CREATE TABLE IF NOT EXISTS `docs` (
  `id` int(6) unsigned NOT NULL,
  `rev` int(3) unsigned NOT NULL,
  `content` varchar(200) NOT NULL,
  PRIMARY KEY (`id`,`rev`),
  KEY idx_docs_id (id)
)

 

인덱스 삭제

DROP INDEX <index_name>
ON <table_name>

# 예시
DROP INDEX idx_docs_id
ON docs;

 

마찬가지로 ALTER 문을 이용해 인덱스를 삭제할 수 있다.

ALTER TABLE <table_name>
DROP INDEX <index_name>

 

인덱스 수정

인덱스는 update 문이 없어, 삭제 후 새로 생성하는 방식으로 수정하면 된다.

 

 

 

참고 문서

https://velog.io/@ansrjsdn/MySQL-INDEX-%EB%9E%80

http://knowtechstuffz.blogspot.com/2015/04/how-sql-indexes-work-internally.html