MySQL - INDEX 정리

MySQL의 INDEX 관련 공부를 하던 중 '이것이 MySQL이다' 라는 책에 대한 강의가 유튜브에 올라온것을 보고 참고하였다. DB에서 데이터를 가져오는데 많은 시간이 걸리기 때문에 백엔드에서 데이터베이스 튜닝은 매우 중요하다. 그중에서 많은 비중을 차지하는 INDEX에 대해 정리하였다.

# unique not null -> clustered index로 사용됨
create table tbl2(
    a int unique not null,
    b int unique,
    c int unique
);

create table tbl3(
    a int primary key,
    b int unique not null,
    c int unique not null
);

show index from tbl3;

Key_name: PRIMARY_KEY(Clustered index)는 'PRIMARY'로, UNIQUE(Secondary Index)는 컬럼 이름으로 지정

• PRIMARY_KEY 없이 보조 인덱스로만 구성된 테이블을 생성할 수 있음
• 책의 색인과 같이 테이블에 있는 데이터를 빠르게 찾을 수 있도록 함
• 한 페이지의 크기는 16k Bytes
• 전체 데이터에서 중복되는 데이터가 적을 때 사용하는 것이 좋다
  • 남성 또는 여성 두 개로만 분류되는 컬럼은 인덱스로 사용하기 부적합
    → cardinality(중복도)가 높을 수록 적합

 

INDEX 장점

• SELECT 쿼리문을 이용해 데이터를 빠르게 가져올 수 있음
• 전체 데이터 중 10~15% 내의 데이터를 가져오고자 할 때
  • 그 이상의 데이터는 Full Scan이 더 좋은 성능을 보일 수 있음
• 자주 사용 되는 특정 컬럼(여러 컬럼을 조합하기도 함)
• 조건절(WHERE)에 자주 등장하는 컬럼
• ORDER BY 절에 자주 사용되는 컬럼
  • INDEX는 정렬되어 저장되기 때문에 빠르게 사용 가능

 

INDEX 단점

• 저장 공간을 차지 - DB의 10%정도를 index에서 사용
• 처음 인덱스를 생성하는데 시간이 소요됨
• INSERT, SELECT, DELETE 시 많은 시간이 소요됨

 

INDEX 종류(MySQL 기준)

Clustered Index(영어 사전)

• 데이터가 정렬된(PK 또는 UNIQUE NOT NULL 컬럼을 기준으로 오름차순) 상태(유지)로 저장
• 테이블 당 한 개만 생성
• 제약조건 PRIMARY KEY에 의해 자동으로 생성
• 제약조건 UNIQUE NOT NULL에 의해 자동으로 생성
  • 단, 한 테이블에 PRIMARY KEY와 UNIQUE NOT NULL이 함께 있을 경우, PRIMARY KEY를 Clustered Index로 사용
• 데이터 검색 순서
  루트 페이지 → 리프 페이지(= 데이터 페이지)
  = 리프 페이지가 곧 데이터 페이지

FNT - '푸니타', KAI - '카아이'를 추가로 입력할 경우

• 리프 페이지가 모두 차있을 경우 페이지 분할이 일어난다 → DB부하가 걸린다

 

Secondary Index(Non Clustered Index - 책의 '찾아보기')

• 테이블에 여러개의 보조 인덱스를 생성할 수 있음
• 제약조건 UNIQUE에 의해 자동으로 생성
• 별도의 인덱스가 생성되지만, 원본 데이터는 변경되지 않음
• 데이터 검색 순서
  루트 페이지 → 리프 페이지 → 데이터 페이지(Heap page)
  • Heap: 정렬 기준 없이 구성된 테이블(입력된 순서대로 저장)
  • 리프 페이지는 데이터 페이지를 가리키는 주소값(RID)을 가지고 있다.

FNT - '푸니타', KAI - '카아이'를 추가로 입력할 경우

• 리프 페이지가 모두 차있더라도 페이지 분할이 일어나지 않는다.
  ⇒ INSERT, DELETE, UPDATE 성능이 Clustered Index보다 낫다.

SELECT 구문에는 Clustered Index가, INSERT, UPDATE, DELETE 구문에는 Secondary Index가 더 좋은 성능을 발휘한다.

 

한 테이블에 Clustered 및 Secondary Index가 혼합되어있을 경우

# mixed index
create database if not exists testDB;
use testdb;
create table mixedTbl(
    userID char(8) not null,
    name varchar(10) not null,
    addr char(2)
);

insert into mixedTbl value('LSG', '이승기', '서울');
insert into mixedTbl value('KBS', '김범수', '경남');
insert into mixedTbl value('KKH', '김경호', '전남');
insert into mixedTbl value('JYP', '조용필', '경기');
insert into mixedTbl value('SSK', '성시경', '서울');
insert into mixedTbl value('LJB', '임재범', '서울');
insert into mixedTbl value('YJS', '윤종신', '경남');
insert into mixedTbl value('EJW', '은지원', '경북');
insert into mixedTbl value('JKW', '조관우', '경기');
insert into mixedTbl value('BBK', '바비킴', '서울');

# userID를 PK로 지정 -> 클러스터 인덱스 생성(루트 페이지 + 리프 페이지(데이터 페이지))
alter table mixedTbl
    add constraint PK_mixedTbl_userID
        primary key (userID);

# name을 UNIQUE로 지정 -> 보조 인덱스 생성
alter table mixedTbl
    add constraint UK_mixedTbl_name
        unique (name);

show index from mixedTbl;

• 만일 혼합 인덱스에서 보조 인덱스가 데이터 페이지의 주소를 가리키지 않고 PK를 가리킬 경우, 데이터 페이지에 새로운 값이 입력되면 보조 인덱스가 가리키고 있는 주소값이 모두 변경되어야 함
  → INSERT, DELETE, UPDATE의 성능이 매우 떨어지게 됨

 

인덱스 생성 & 변경 + 삭제

# Syntax 
# 생성
CREATE INDEX index_name ON table_name (column_list)
# 기본적으로 create index를 이용할 경우 보조 인덱스가 생성된다.

# 삭제
DROP INDEX index_name on table_name

# 반드시 보조 인덱스 삭제 후 클러스터 인덱스 삭제
# 1.secondary index 삭제
drop index UK_mixedTbl_name on mixedTbl;
drop index idx_mixedTbl on mixedTbl;
drop index idx_mixedTbl_name_addr on mixedTbl;

# 2.clustered index 삭제(pk)
alter table mixedTbl
    drop primary key;

# 3. 만일 다른 테이블에서 클러스터 인덱스(pk)를 참조하고 있을 경우 에러 발생
# -> pk를 참조하는 테이블(referTbl)의 제약조건 이름 확인
select table_name, constraint_name
from information_schema.REFERENTIAL_CONSTRAINTS
where CONSTRAINT_SCHEMA = 'sqlDB';
# -> 참조하는 테이블의 외래키와 클러스터 인덱스의 외래키 제거
alter table referTbl
    drop foreign key refertbl_ibfk_1;
alter table mixedTbl
    drop foreign key;

• 기본 키로 설정된 클러스터 인덱스의 이름은 'PRIMARY'
  • 클러스터형 인덱스를 삭제할 경우 'DROP INDEX PRIMARY ON table_name
• ALTER TABLE을 이용해 기본 키를 제거할 경우 클러스터형 인덱스가 제거 됨
• 혼합 인덱스에서 인덱스를 전부 제거하고자 할 경우, 보조 인덱스부터 삭제

INDEX를 이용한 테스트 -Notion

REFERENCES

[이것이 MySQL이다] 09. MySQL 인덱스(1)

[이것이 MySQL이다] 09. MySQL 인덱스(2)

[이것이 MySQL이다] 09. MySQL 인덱스(3)

[이것이 MySQL이다] 09. MySQL 인덱스(4)

[mysql] 인덱스 정리 및 팁