MySQL 인덱스

인덱스란?

" 인덱스 == 정렬 "

 

인덱스는 결국 지정한 컬럼들을 기준으로 메모리 영역에 일종의 '목차'를 생성하는 것.

insert, update, delete(command)의 성능을 희생하고 대신 select (Query)의 성능을 향상시킨다.

 

update, delete 행위가 느린거지, 

update, delete를 하기 위해 해당 데이터를 조회하는 것은 인덱스가 있으면 빠르게 조회된다.

 

인덱스가 없는 컬럼을 조건으로 update, delete 를 하게 되면 굉장히 느려 많은 양의 데이터를 삭제 해야하는 상황에선 인덱스로 지정된 컬럼을 기준으로 진행하는 것을 추천한다.

 

 

 

 

인덱스 키 값의 크기

InnoDB (MySQL)은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지라고 하며,

인덱스 역시 페이지 단위로 관리 된다.

 

페이지는 16KB로 크기가 고정되어 있다.

인덱스의 키는 길면 길수록 성능상 이슈가 있다.

 

 

 

 

인덱스 컬럼 기준

1개의 컬럼만 인덱스를 걸어야 한다면, 해당 컬럼은 카디널리티(Cardinality)가 가장 높은 것 을 잡아야 한다는 점.

 

카디널리티(Cardinality)란 해당 컬럼의 '중복된 수치'를 나타낸다.

예를 들어 성별, 학년 등은 카디널리티가 낮다고 얘기하고,

반대로 주민등록번호, 계좌번호 등은 카디널리티가 높다고 얘기한다.

 

인덱스로 최대한 효율을 뽑아내려면, 해당 인덱스로 많은 부분을 걸러내야 하기 때문이다.

 

만약 성별을 인덱스로 잡는다면, 남/녀 중 하나를 선택하기 때문에 인덱스를 통해 50%밖에 걸러내지 못한다.

하지만 주민등록번호나 계좌번호 같은 경우엔 인덱스를 통해 데이터의 대부분을 걸러내기 때문에 빠르게 검색이 가능하다.

 

 

 

 

Optimizer

Sql 쿼리를 호출하면 DBMS에 존재하는 Optimizer가 SQL 실행계획을 만들어준다.

옵티마이저는 2가지 종류로 나눠지는데

 

- RBO(Rule Based Optimizer)

미리 정해진 규칙대로만 쿼리 수행

인덱스가 존재하면 무조건 인덱스를 탐

예측 가능하기 때문에 설계하기 쉽지만 그만큼 쿼리 자체를 잘 짜야함

- CBO(Cost Based Optimizer)

통계 정보 (Record 수, Index 컬럼 값 갯수)를 기반으로 옵티마이저가 생각하는 가장 효율적인 쿼리를 수행

인덱스가 존재해도 Table Full Scan이 더 효율적이라고 생각하면 Full Scan

예측이 힘들기 때문에 우리가 생각한 실행계획과 달리 Slow Query 가 발생할 수 있음

(특히 로컬, 개발 환경과 실제 운영 환경에서는 데이터의 갯수가 달라 같은 쿼리여도 다르게 수행될 수 있음)

 

예를 들어 where ~ and ~ 조건을 사용할 때 Index 컬럼 순서를 지키지 않았지만 자동으로 인덱스를 태우는 것도 다 CBO 덕분. 하지만 여러 인덱스가 존재할 때 A 인덱스를 타는게 더 효율적임에도 B 인덱스를 타는 경우가 있다.

이런 경우에 사용하는 것이 Index Hint 이다.

 

 

 

 

Index Hint

인덱스 힌트는 이름 그대로 옵티마이저가 인덱스를 선택할 때 도움을 준다.

특정 인덱스를 사용하지 않길 원하면 Ignore, 사용하길 원하면 use, force 를 사용할 수 있다.

 

- use index

DB 옵티마이저에게 지정한 인덱스를 사용하라고 권장

하지만 만약 Table Scan이 더 빠르다면 옵티마이저는 인덱스 대신 Table Scan 수행 가능

- force index

Table Scan 이 더 효율적이어도 무조건 인덱스 사용

index 를 사용할 수 없는 쿼리 (인덱스가 걸려있지 않은 컬럼이 조건)인 경우에만 다른 방법 선택 가능

 

 

 

 

 

인덱스 힌트 Index Hints (version 8.0)

Mysql 에서 제공하는 Index Hints를 쓰면 강제적으로 할당한 Index를 이용하여 쿼리가 실행이 된다.

인덱스 힌트는 select 및 update 문에 적용된다. 다중 delete문에서도 작동하지만 단일 테이블에서는 작동하지 않는다.

select * from table1 user index (col1_index, col2_index)
where col1=1 and col2=2 and col3=3;

select * from table1 ignore index (col3_index)
where col1=1 and col2=2 and col3=3;

 

 

---

 

참고 사이트

 

https://jojoldu.tistory.com/243

[mysql] 인덱스 정리 및 팁

https://dev.mysql.com/doc/refman/8.0/en/index-hints.html

MySQL :: MySQL 8.0 Reference Manual :: 8.9.4 Index Hints

https://helloino.tistory.com/77

Mysql Index HInts

https://bcp0109.tistory.com/374

MySQL Optimizer 와 USE INDEX vs FORCE INDEX