MySQL Index 알아보기

- 목차

 

소개.

MySQL 는 데이터 조회의 퍼포먼스를 향상시키기 위해 Index 라는 기능을 사용합니다.
Index 는 B-tree 라는 자료구조를 사용하여 특정 대이터와 데이터의 위치 정보를 저장합니다.
예를 들어, student 라는 테이블이 존재하고 id 라는 칼럼이 존재한다고 가정하겠습니다.
이때 id 칼럼을 위한 index 를 세팅하게되면,
index 는 모든 student 들의 id 값과 id 에 해당하는 student row 의 위치 정보가 저장됩니다.

< Table 과 Index 의 관계 >

table: student
student(id: 1, name: Andy)
student(id: 2, name: David)
student(id: 3, name: Kevin)
student(id: 4, name: Tony) 
student(id: 5, name: Will)

student primary key index:
index(id: 1, ref: location of student1)
index(id: 2, ref: location of student2)
index(id: 3, ref: location of student3)
index(id: 4, ref: location of student4) 
index(id: 5, ref: location of student5)

위 예시는 하나의 테이블과 인덱스의 관계를 설명한 예시입니다.
위 student 테이블의 id 칼럼을 index column 으로 설정하였구요.
Index 는 student 의 id 값과 student row 의 위치 정보를 가집니다.
따라서 student 를 조회할 때 student 의 id 를 안다면 index 를 통하여 데이터의 위치를 쉽게 파악할 수 있습니다.

이렇게 Index 라는 MySQL 기능을 통해서 빠른 데이터 조회가 가능합니다.

Entry of MySQL Index.

Index 를 구성하는 데이터들을 어떻게 생겼을까요?
세부적으로 들어가려면 몇가지 배경지식이 필요하여 관련된 링크를 삽입해두겠습니다.

https://westlife0615.tistory.com/383

B-tree 자료구조 알아보기

https://westlife0615.tistory.com/154

MySQL Tablespace 알아보기

Index 와 B-tree.

먼저 MySQL Index 는 B-tree 자료구조를 사용합니다.
그 이유는 효율적인 Index 의 관리와 데이터 조회 퍼포먼스를 향상시키기 위함입니다.
B-tree 자료구조가 적용된 MySQL Index 의 대략적인 모습은 아래와 같습니다.

< Index 와 B-tree >

table: student
student(id: 1, name: Andy)
student(id: 2, name: David)
student(id: 3, name: Kevin)
student(id: 4, name: Tony) 
student(id: 5, name: Will)
student(id: 6, name: Bruce)

student primary key index:

Root Node(
  {id: 2, ref: location of 2}, 
  {id: 4, ref: location of 4}
)
Child Node1(  
  {id: 1, ref: location of 1}
)
Child Node2(
  {id: 3, ref: location of 3}
)
Child Node3(
  {id: 5, ref: location of 5},
  {id: 6, ref: location of 6}
)

 
1 부터 6 까지의 id 가 추가된 MySQL Index 는 Root Node 하나와 Child Node 3개로 표현됩니다.
각 Node 는 id 값과 id 에 해당하는 데이터의 위치 정보를 가집니다.
모든 Child Node 들은 같은 Tree 구조에서 같은 depth 에 위치합니다.
MySQL Index 는 B-tree 자료구조를 통해서 효율적으로 관리됩니다.

데이터의 위치는 어떻게 표현될까?

MySQL Index 는 칼럼의 값과 해당하는 데이터의 위치 값을 가진다고 말씀드렸습니다.

index(id: 1, ref: location of row)

그럼 row 의 위치는 어떻게 표현될까요?
MySQL 의 테이블과 관련된 데이터들은 Tablespace 라는 영역에 저장됩니다.
Tablespace 는 MySQL 에서 관리하는 파일이라고 생각하시면 되구요.
설정에 따라서 Tablespace 가 여러개 존재할 수 있습니다.
- Rows of Table
- Index
등의 정보들이 Tablespace 라는 파일에 저장되게 됩니다.
그리고 Tablespace 는 논리적인 단위인 Page 들로 구성됩니다.
Page 는 대개 16KB 크기를 가지며,
물리적인 Tablespace 라는 영역 내부에서 논리적인 Page 들이 존재합니다.
각 Page 는 자체적인 Page Number 를 가지구요.
Page Number 가 MySQL 의 Index 에서 사용됩니다.

MySQL Index 의 데이터 정보는 다음과 같이 표현됩니다.
1. 어떤 Tablespace 인지 ( Tablespace 는 여러개 존재할 수 있음 )
2. Page Number 가 무엇인지
3. Page 내에서 몇번째 데이터인지

즉, Tablespace 와 Page Number 그리고 Page 내의 Offset 정보로 해당 Row 의 구체적인 위치를 알 수 있습니다.

 

MySQL Index 종류.

MySQL Index 의 종류에 대해서 알아보겠습니다.

 

Primary Key Index.

Primary key index 는 가장 대표적인 Index 입니다.
테이블 내에서 고유한 칼럼 또는 칼럼들의 조합을 사용합니다.
id 같은 칼럼들이 대표적으로 Primary Key 로 사용되죠.
 
예시를 들어보겠습니다.
 
< CREATE TABLE student Table >

CREATE TABLE `student` (
  `id` varchar(64) NOT NULL,
  `name` varchar(64) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

 
< Insertion Rows of student >

INSERT INTO student(id, name) VALUES('1', 'Andy');
INSERT INTO student(id, name) VALUES('2', 'Bruce');
INSERT INTO student(id, name) VALUES('3', 'Cindy');

 
< Error Duplicate entry >
Primary Key 는 Unique 한 상태를 유지해야하기 때문에 동일한 id 의 row 의 생성을 할 수 없습니다.

INSERT INTO student(id, name) VALUES('1', 'Dennis');
> ERROR 1062 (23000): Duplicate entry '1' for key 'student.PRIMARY'

 

Unique Index.

Unique Index 또한 Primary Key Index 처럼 데이터의 유일성을 보장하는 Index 입니다.
차이점은 Primary Key Index 는 모든 Row 들을 관리합니다.
반면 Unique Index 는 일부 Row 들을 관리합니다.
이러한 배경은 Primary Key Index 는 유일한 값을 가지는 Column 을 대상으로 하기 때문입니다.
즉, 모든 Row 들이 같은 값을 가질 수 없는 Column 을 사용합니다.
 
예를 들어보겠습니다.
 
< CREATE TABLE student Table >

CREATE TABLE `student` (
  `id` varchar(64) NOT NULL,
  `name` varchar(64) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY name_idx(`name`) 
) ENGINE=InnoDB;

 
< Insertion Rows of student >

INSERT INTO student(id, name) VALUES('1', 'Andy');
INSERT INTO student(id, name) VALUES('2', 'Bruce');
INSERT INTO student(id, name) VALUES('3', 'Cindy');

 

< Error Duplicate entry >
Unique Index 또한 Unique 한 상태를 유지해야하기 때문에 동일한 id 의 row 의 생성을 할 수 없습니다.

INSERT INTO student(id, name) VALUES('4', 'Andy');
> ERROR 1062 (23000): Duplicate entry '4' for key 'name_idx'

 

Non-Unique Index.

Non-Unique Index 는 Primary Key Index 와 Unique Index 와 달리 중복을 허용합니다.
그래서 Duplicate Error 와 같은 제약사항이 없습니다.
 
 
< CREATE TABLE student Table >

CREATE TABLE `student` (
  `id` varchar(64) NOT NULL,
  `name` varchar(64) NOT NULL,
  `city` varchar(64) NOT NULL,  
  PRIMARY KEY (`id`),
  KEY name_city_idx(`name`, `city`)
) ENGINE=InnoDB;

 
< Insertion Rows of student >
아래와 같이 name 과 city 칼럼의 조합상 중복이 발생하여도 데이터 생성에 제약이 없습니다.

INSERT INTO student(id, name, city) VALUES('1', 'Andy', 'Seoul');
INSERT INTO student(id, name, city) VALUES('2', 'Andy', 'Busan');
INSERT INTO student(id, name, city) VALUES('3', 'Andy', 'Daegu');
INSERT INTO student(id, name, city) VALUES('4', 'Andy', 'Daegu');
INSERT INTO student(id, name, city) VALUES('5', 'Andy', 'Daegu');
INSERT INTO student(id, name, city) VALUES('6', 'Andy', 'Daegu');
INSERT INTO student(id, name, city) VALUES('7', 'Andy', 'Daegu');
INSERT INTO student(id, name, city) VALUES('8', 'Andy', 'Daegu');