2025.10.16 쿼리 튜닝, 인덱스

 

2024.07.10~11 Query Tuning

 

2025.04.16 huge table

 

이전에도 몇 번 다뤘던 주제.

인덱스의 데이터 타입이 클수록 한 데이터 페이지(보통 16KB)에 들어가는 인덱스 수가 줄어들어 더 많은 i/o 발생할 수 있다.

가능한 한 작은 타입을 쓰는 것이 성능상 유리하다.

btree 인덱스에서 복합 인덱스는 선두 컬럼 우선 정렬이다.

예로 (a, b)라면 a로 정렬되고, 같은 a 그룹 안에서만 b가 정렬된다.

그래서 컬럼 순서가 중요하며, where/order by와 맞추면 filesort 없이 정렬을 충족할 수 있다.

 

mysql 8.0에서는 desc 역순 인덱스를 지원한다.

참고로 8.0 이전에도 역순 스캔 자체는 가능했으나, 정순보다는 느리다.

카디널리티가 중요하다.

예로 상품 테이블의 category_id와 created_at을 비교하면, 일반적으로 created_at의 선택도가 더 높을 것이다.

다만 특정 시점에 대량 등록이 몰리는 서비스라면 이 가정이 깨질 수 있다.

데이터 타입 크기도 고려해야 한다. innodb저장 기준으로 bigint는 8바이트, datetime은 8바이트다.

추가로 세컨더리 인덱스 리프에는 (인덱스 키, PK) 가 함께 저장되므로 PK가 길수록 모든 보조 인덱스가 커진다.

lmite이 있는 최신 top 패턴(예로 order by created_at desc limit 10)에서는 정렬 키를 선행으로 둔 (created_at, …) 복합 인덱스가 인덱스 순서만으로 정렬을 만족해 먼저 탐색이 종료되므로 유리하다.

그에 반해 복합 인덱스가 (id, created_at) 일 때 전역 order by created_at 을 만족하려면 선행 컬럼 id가 동등(=) 으로 고정돼 있어야 한다.

id in 쿼리나 id > … 같은 range가 걸리면 여러 범위를 합쳐 전역 정렬을 만들어야 하므로 filesort가 발생하기 쉽다.

이럴 경우 (created_at, id) 처럼 정렬 키를 선두로 두는 인덱스가 유리하다.

 

인덱스의 노든느 key로 정렬되어있고, 다음 페이지를 가르킨다. 세컨더리 인덱스의 리프노드는 pk의 페이지 포인터가 저장되어 있고.

다시 pk에서 리프노드를 읽어 실 데이터를 읽어야한다. 

인덱스 사용은 pk를 찾고 pk에서 데이터를 찾는 과정이다. 랜덤i/o과정이다. 만약 테이블의 상당 부분을 읽어야한다면 인덱스가 아닌 풀스캔이 더 낫다. 순차i/o이기 때문에 디스크 이동 비용이 낮다.

 

 

join이 들어가게 되면  루프 중첩과도 같은 형태가 된다.

mysql은 nested loop join이기 때문에 위의 행위를 중첩으로 하게된다.

만약

 

select *

from a_table as a

join b_table as b on a.b_id = b.id

where a.b_id = 1;

일경우 옵티마이저에 실행계획에 따라 다르겠지만, 예상할 수 있는 범위는 다음과 같다.

a.b_id를 인덱스를 통해 값을 찾고, 그 값을 가지고 다시 드리븐 테이블인 b_table의 값을 찾는다.

b_table또한 인덱스 이면 인덱스 탐색을 거친다.

즉 위의 인덱스 탐색 그림을 2번 하는 것이다. 그 그림은 아래와 같다.

 

 

• https://rebro.kr/169
• https://limycat.tistory.com/4