개발 이론/DB

[mySQL] 인덱스 종류와 커버링 인덱스 (2)

dal_been 2024. 3. 7. 14:25
728x90

지난 블로그에 인덱스와 복합인덱스에 대해서 기록해보았다.

오늘은 인덱스 종류와 커버링 인덱스에 관해서 이야기해볼예정이다.

 

Explain type, extra 설명

 

앞선 내용을 설명하기 전에 인덱스를 확인할때바다 type과 extra를 봤다. 이에 관해서 조금 이야기를 나눠보자.

이에 대해서 간단하게 정리해둔 블로그가 있다. 

 

여기서는 주요키워드만 볼 것이다.

1. type

  • all을 빼고는 인덱스를 탄다는 것알 알면된다.
  • index -> index full scan : 수직적 탐색 없이 인덱스 리프 블록 처음부터 끝까지 수평적으로 탐색한다
    • 데이터 검색을 위한 최적의 인덱스가 없을때 차선으로 선택됨
    • 예를 들어 인덱스 선두 칼럼이 조건절에 없으면 옵티마이저는 먼저 Table full scan을 하고자하지만 대용량 데이터 테이블의 경우 부담이 되어 Index full scan방식을 택함
  • range -> index range scan : 일반적인 액세스 방식으로 인덱스 루트에서 리프 블록까지 수직적으로 탐색하고, 필요한 만틈 수평적으로 탐색을 함
    • index range scan을 하려면 선두 칼럼을 가공하지 않은 상태로 조건절에 사용하면됨

2. extra

  • Distinct: 중복을 제거하는 경우
  • Using where: WHERE로 필터링한 경우
  • Using temporary: 데이터 중간 결과를 위해 임시 테이블을 생성한 경우 (보통 DISTINCT / GROUP BY / ORDER BY가 포함되면 임시 테이블 생성)
  • Using index: 커버링 인덱스를 사용한 경우
  • Using filesort: 데이터를 정렬한 경우

 

Clustered Index와 NonClustered Index

 

1. Clustered Indx

  • 테이블 당 1개만 허용
  • PK설정시 해당 컬럼에 대해 자동으로 Clustered Index가 생성됨
  • 물리적으로 테이블이 배열되어 있음
  • 실제 테이블의 row위치를 알고 있음

2. NonClustered Index

  • 여러개 생성가능
  • 물리적으로 레코드를 정렬하지 않은 상태로 데이터 페이지가 구성됨 -> 별도의 장소에 인덱스 페이지가 생성되는 것임
  • 인덱스 칼럼의 갑들과 Clustered key값이 포함되어있음 -> NonClustered 로 찾은후 ClusteredKey값으로 데이터 블록을 찾아야함

 

Clustered Key 1) PK
2) PK가 없을땐 유니크키
3) 1~2 둘다 없을 경우 6byte의 Hidden Key를 생성 (rowid)		 테이블당 1개만 존재 가능
Non Clustered Key 일반적인 인덱스 여러개 생성 가능

 

 

커버링 인덱스

 

커버링 인덱스랑 무엇인가??

인덱스 스캔만으로 결과가 나가는 것을 말한다. 즉 인덱스 스캔이후 추가적인 테이블 탐색이 필요가 없는 경우다.

그니까 select, where, order by, group by 등에 사용되는 모든 칼럼이 인덱스 구성요소인 경우를 말하는 것이다.

 

특정 컬럼 자주 조회하거나 읽기 성능이 필요한 경우에 커버링 인덱스를 사용한다.

다만 커버링 인덱스를 사용할때는 주의할 점이 있다.

  • 인덱스 크기 : 인덱스가 커지면 인덱스 블록에 들어갈 수 있는 데이터가 줄어든다 -> 그만큼 스캔 탐색 비용이 증가하는 것이다.
  • 쓰기 비용 : 인덱스에 추가된 칼럼이 자주 업데이트 되는 경우, 추가적인 쓰기 비용이 발생한다
  • 카디널리티 : 카디널리티가 낮은 값을 인덱스로 추가하면, 읽기선능이 향상되지 않을 수 있다

이런 부분들을 주의하여 커버링 인덱스를 생성해야한다.

 

예를 들어보자

CREATE TABLE orders (
                        id INT AUTO_INCREMENT PRIMARY KEY,
                        customer_id INT NOT NULL,
                        order_date DATE NOT NULL,
                        total_amount DECIMAL(10, 2) NOT NULL
);

create INDEX idx_customer_id_order_date on orders(customer_id,order_date);
create INDEX idx_covering on orders(customer_id,order_date,total_amount);

select * from orders
         where customer_id = 8485731
         ORDER BY order_date desc
         LIMIT 10;

 

 

1. idx_customer_id_order_date 인덱스를 사용하는 경우

약 0.0134ms 로 나온다

 

2. idx_covering 인덱스를 사용하는 경우

약 0.00908 ms로 나온다

 

 

커버링 인덱스 사용으로 성능이 좋아진 것을 알 수 있다.

커버링 인덱스에 대해 정리해봤다. 이전에 공부했을때 이해가 안됬는데.. 좀 인내심을 가지고 공부해보니.. 개념자체는 별개 아니더라..ㅎㅎㅎ

 

https://jojoldu.tistory.com/476

https://choiblack.tistory.com/53

https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=gglee0127&logNo=221336088285