[MySQL] 인덱스(INDEX)

인덱스(Index)

인덱스는 테이블의 동작속도(조회)를 높여주는 자료구조

인덱스로 데이터의 위치를 빠르게 찾아주는 역할

 

SELECT 명령문의 속도는 빨라질 수 있지만 UPDATE, INSERT, DELETE의 속도는 저하되는 단점이 있음. (Table의 index 색인 정보를 갱신하는 추가적인 비용을 소모하기 때문 == 정렬과 관계있음.)

 

컬럼의 값과 레코드가 저장된 주소를 키와 값의 쌍으로 인덱스를 만들어 놓음.

MYI(MySQL Index) 파일에 인덱스가 저장됨.

 

---

Feature

인덱스는 하나 혹은 여러 개의 컬럼에 대해 설정할 수 있다. (복합 인덱스)

 

WHERE 절을 사용하지 않고 인덱스가 걸린 컬럼을 조회하는 것은 성능에 아무런 영향이 없음.

 

MySQL의 경우 BTREE 알고리즘을 활용.

 

인덱스를 저장하는 데 필요한 디스크 공간은 보통 테이블을 저장하는 데 필요한 디스크 공간보다 작음. (왜냐하면 인덱스는 키-필드만 갖고있기 때문)

---

다중 컬럼 인덱스

다중 컬럼 인덱스는 두개 이상의 필드를 조합해서 생성한 INDEX

 

다중 컬럼 인덱스는 단일 컬럼 인덱스 보다 더 비효율적으로 INSERT/UPDATE/DELETE를 수행하기 때문에 신중해야함 (때문에 UPDATE가 잘 안되는 값을 선정해야함)

 

---

인덱스 생성 전략

• 인덱스는 열 단위에 생성
• WHERE절에서 사용되는 열에 생성.
• WHERE절에 사용되는 열이라도 자주 사용해야 가치가 있음.
• 데이터 중복도가 높은 열에는 인덱스를 만들어도 효과가 없음.(중복도가 낮은 열에 생성)
• 외래키를 설정한 열에는 자동으로 외래키 인덱스가 생성됨.
• 조인에 자주 사용되는 열에는 인덱스를 생성하는 것이 좋음.
• 데이터 변경(INSERT, UPDATE, DELETE) 작업이 얼마나 자주 일어나는지를 고려해야함.
• 클러스터형 인덱스는 테이블당 하나만 생성할 수 있음.
• 사용하지 않는 인덱스는 제거.

---

인덱스의 종류

클러스터 형 인덱스

• 특정 나열된 데이터들을 일정 기준으로 정렬해 주는 인덱스
• 클러스터형 인덱스 생성 시 데이터 페이지 전체가 다시 정렬 >> 이미 대용량의 데이터가 입력된 상태라면 클러스터형 인덱스 생성 시 심각한 부하가 발생.
• 테이블당 하나만 생성 가능. 어느 열에 클러스터형 인덱를 생성하는지에 따라 시스템의 성능이 달라짐.
• 보조 인덱스보다 검색 속도는 더 빠름. 단 입력/수정/삭제는 더 느림.
• MySQL의 경우 Primary Key가 있다면 해당 키를 클러스터형 인덱스로, 없다면 Unique 하면서 Not Null인 컬럼을, 그것도 없다면 임의로 보이지 않는 컬럼을 만들어 클러스터형 인덱스로 지정.

---

보조 인덱스

• 개념적으로 후보키에만 부여 가능한 Index.
• 보조 인덱스 생성 시 데이터 페이지는 그냥 둔 상태에서 별도의 페이지에 인덱스를 구성. (클러스터형과 달리 자동 정렬 X)
• 데이터가 위치하는 주소값(RID)
• 클러스터형 인덱스보다 검색 속도는 느리지만 데이터의 입력/수정/삭제 시 성능 부하가 적음.
• 보조 인덱스는 테이블당 여러 개 생성 가능. (너무 많이 생성 시 오히려 성능 저하)

 

---

문법 정리

테이블에 등록된 인덱스 확인

SHOW INDEX
FROM 테이블이름

 

클러스터 인덱스 생성

ALTER TABLE 테이블이름
ADD CONSTRAINT 인덱스이름 PRIMARY KEY (컬럼이름);

 

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

ALTER TABLE 테이블이름
ADD CONSTRAINT 인덱스이름 UNIQUE (컬럼이름);

 

테이블의 인덱스 크기 확인

SHOW TABLE STATUS LIKE 테이블이름

 

테이블 생성 시 제약조건을 걸어줌으로써 인덱스를 생성

CREATE TABLE books (
  -- 같이 지정
  id varchar(5) primary key, -- 기본키 지정 (클러스터 인덱스)
  name varchar(20) unique, -- 인덱스 생성 (보조 인덱스) (중복 비허용)
  writer varchar(20) NOT NULL,
  
  INDEX idx_test (writer asc) -- 인덱스 생성 (보조 인덱스)
);

 

인덱스 사용 확인

내가 쿼리한 SELECT명령문이 인덱스를 사용해서 조회했는지 확인하는 방법.

SELECT * FROM 테이블이름 WHERE 인덱스를적용한컬럼명 = '데이터';

 

제약조건을 걸면서 동시에 생성하는 법 외에 인덱스를 따로 생성하는 문법

CREATE INDEX 인덱스이름 ON 테이블이름 (컬럼이름); -- 보조 인덱 생성 (중복 허용)

CREATE UNIQUE INDEX 인덱스명 ON 테이블명 (컬럼명); -- 보조 인덱스 생성 (중복 비허용)

CREATE FULLTEXT INDEX 인덱스명 ON 테이블명 (컬럼명); -- 클러스터 인덱스 생성

CREATE UNIQUE INDEX 인덱스명 ON 테이블명 (컬럼명1, 컬러명2); -- 다중 컬럼 인덱스 생성

ANALYZE TABLE 테이블명; -- !! 생성한 인덱스 적용 !!

 

---

정리

인덱스에 대한 용어 자체가 생소했는데 (강의를 졸면서 들었나….) 이번 기회로 대략적인 감이 잡힐 수 있었음.

 

우선 인덱스에는 클러스터형 인덱스, 보조 인덱스 두 가지의 인덱스가 있음.

 

클러스터형 같은 경우에는 PK에 대해 지정되거나 PK가 없는 경우 Unique + Not Null인 컬럼에 지정됨.

 

보조 인덱스의 경우에는 각 데이터에 대해서 고유 값(unique)들이 있는 목록에 생성 가능한 인덱스.

 

인덱스를 생성한다는 것은 ADD CONSTRAINT 명령을 통해 컬럼에 제약조건을 걸어준다는 것과 동일함. (난 또 다르게 생성하는 건줄 알고…)

 

위에서 알 수 있는 대용량 데이터에 대해 클러스터형 인덱스를 생성 시 부하가 크게 걸리는걸 알 수 있는데 이는 한마디로 대용량 데이터에 PK 제약조건걸면 이라는 것으로 직역 가능함.

 

---

Reference

https://inpa.tistory.com/entry/MYSQL-📚-인덱스index-핵심-설계-사용-문법-💯-총정리#인덱스_문법_정리

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