MySQL index 설계, 튜닝시 고려사항 (covering, index dive, prefix index)

서론

---

어플리케이션의 성능을 고려했을 때 알고리즘, 쿼리 자체도 영향을 많이 끼치지만 인덱스는 단연 굉장히 높은
비중을 차지한다.

 

인덱스 설계, 튜닝을 할 때 어떤 부분들을 고려해야 하고 개선할 수 있는지 공부하기 위해
인프런의 MySql 성능 최적화 라는 강의와 MySQL 도큐먼트를 공부하고 정리하기 위해 이 글을 작성하였다.

 

(해당 강의에는 다양한 최적화 방법을 다루고 있지만 이 글에서는 index 파트에 대해 다룰 것이다.)

 

 

본론

---

카디널리티 확인 방법

카디널리티는 다들 잘 알고있겠지만 다시 한번 정의를 설명하자면 특정 데이터 집합에서 유니크한 값의 개수이다.

 

그냥 특정 컬럼의 카디널리티는 select count(distinct(column)) from table 이러한 쿼리로 구할 수 있다.

만약 인덱스를 생성하게 되고 (보통 복합 인덱스) 해당 인덱스의 카디널리티를 확인하고 싶다면

인덱스 생성이 안되있을 경우

SELECT  *
FROM    mysql.innodb_index_stats
WHERE   `database_name` = 'test'
AND     `table_name` = 'orders';

인덱스 생성을 했을 경우

show index from orders;

 

이런식으로 확인할 수 있다.

 

 

복합 인덱스를 설계할 때 고려해야 하는 요소

높은 카디널리티를 선행으로 배치하는 것도 일반적으로 좋은 방식이지만 아래 사항들도 같이 고려를 해야한다.

• 자주 사용하는 쿼리들의 컬럼인가
• 조인에 사용되는 컬럼인가
• 인덱스의 선행 칼럼이 범위 기반의 쿼리로 많이 사용되는가
• 슬로우 쿼리에 사용되는 컬럼들

이 중 범위 기반으로 사용되는 필드가(order_date) 선행 칼럼으로 사용되었을 때, 비교적 후행 칼럼으로 사용되었을 때를 직접 비교해보자.

 

데이터 셋은 카디널리티를 유사하게 하기 위해 날짜는 1000년으로 범위를 만들었고 사람수도 3000명을 기준으로 100만개의 데이터를
생성했다. (보통은 user_id가 카디널리티가 높지 않을까)

//실행 계획문
explain analyze select * from orders
where customer_id = 3905
and order_date > '2023-11-04'
order by order_date desc
limit 10;

//범위 인덱스 선행
create index idx_order_date_customer_id on orders(order_date, customer_id);

-> Limit: 10 row(s)  (cost=100600 rows=10) (actual time=140..140 rows=0 loops=1)
-> Index range scan on orders using idx_order_date_customer_id over ('2020-11-04' < order_date) 



//범위 인덱스 후행
create index idx_customer_id on orders(customer_id, order_date);

-> Limit: 10 row(s)  (cost=0.71 rows=1) (actual time=0.0153..0.0153 rows=0 loops=1)
-> Index range scan on orders using idx_customer_id over (customer_id = 3905 AND '2020-11-04' < order_date) 

 

당연히 기간의 범위에 따라 달리지고 데이터에 따라 달라지겠지만 order_date가 카디널리티가 10배가 높은데도 불구하고 해당 예시에서도 굉장히 큰 차이가 있음을 볼 수 있다.

 

무조건 카디널리티가 높은 컬럼을 선행으로 하는 것이 아닌 다양한 상황들을 고려하여 인덱스를 설계해야함을 알 수 있다.

 

 

커버링 인덱스

 

인덱스를 사용해서 조회할 때 필요한 값들이 모두 인덱스 테이블에 존재한다면 추가적인 데이터 접근을 할 필요가 없어진다.

이러한 조회 쿼리의 모든 필드를 가지고 있는 인덱스를 커버링 인덱스라고 한다.

 

간단하게 일반 인덱스 사용과, 커버링 인덱스 사용을 비교해 보자 (해당 데이터는 다른 차이날 조건을 없애기 위해 customer_id를
제외하고 모두 unique하게 만들었다.)

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
);

//커버링 적용 x
create index idx_customer_order_date on orders(customer_id, order_date);

-> Limit: 10 row(s)  (cost=3.85 rows=10) (actual time=0.377..0.38 rows=10 loops=1)
-> Index lookup on orders using idx_customer_order_date (customer_id=84831) (reverse)  
    (cost=3.85 rows=11) (actual time=0.376..0.378 rows=10 loops=1)


//커버링 적용    
create index idx_covering on orders(customer_id, order_date, total_amount);

-> Limit: 10 row(s)  (cost=2.12 rows=10) (actual time=0.0173..0.0195 rows=10 loops=1)
-> Covering index lookup on orders using idx_covering (customer_id=84831) (reverse)  
    (cost=2.12 rows=11) (actual time=0.0165..0.0181 rows=10 loops=1)

결과에서 볼 수 있듯이 디스크 접근을 안함으로 대략 30배가 넘는 차이가 남을 볼 수 있다.

 

 

index dive

index dive에 대한 최적화를 얘기하기 전에 index dive를 간단히 설명하자면

쿼리가 실행될 때 여러가지 과정을 거치는데

1. 쿼리 분석 및 파싱 - MySQL 서버는 쿼리를 분석하고 파싱하여 문법적으로 올바른지 검사한다.
2. 최적화 - 옵티마이저는 가능한 여러 실행 계획을 고려하여 가장 효율적인 실행 계획을 선택한다.
   이때 인덱스, 조인 순서, 조인 방법 등을 고려하여 최적의 실행 계획을 결정한다.
3. 실행 계획 수립 - 선택된 최적의 실행 계획에 따라 MySQL은 실제로 쿼리를 실행할 방법을 결정한다.
   이 단계에서 테이블 액세스 방법, 인덱스 사용, 조인 순서 등이 결정된다.
4. 실행

대략적으로 이러한 과정이다.

 

이때 옵티마저는 실행 계획을 선택할 때 통계 정보도 활용하지만 실제 데이터를 샘플링 하여 계획을 수립하는데
이를 index dive라고 한다.

 

보통 index dive는 큰 리소스를 소모하지 않지만 특정한 경우 굉장히 많은 리소스를 소모할 수 있고 이때
튜닝이 필요해질 수 있다.

With index dives, the optimizer makes a dive at each end of a range and uses
the number of rows in the range as the estimate. For example,
the expression col_name IN (10, 20, 30) has three equality ranges
and the optimizer makes two dives per range to generate a row estimate.
Each pair of dives yields an estimate of the number of rows that have the given value.

출처: MySQL Docs

 

설명에서 볼 수 있듯이 index dive는 in 절에서 각 값마다 첫번째 값부터 끝까지 값을 각각 조회하고
해당 값들로 데이터의 양을 추정한다.

 

in절에 1~2개만 있다면 문제가 없겠지만 100개, 200개가 넘어가면 굉장히 큰 리소스를 소비하게된다.
이런 상황에서는 어떻게 개선할 수 있을까?

 

공식문서를 보면 MySQL 8.0 이전에는 eq_range_index_dive_limit 라는 환경변수를 통한 것이 아니면
index dive를 스킵할 수 없었다.

 

8.0 이후로는

• 1개의 테이블에 쿼리를 날리는 경우
• FORCE INDEX를 사용하는 경우
• 서브 쿼리가 없을경우
• DISTINCT, GROUP BY, ORDER BY 가 없을 경우
• 인덱스가 FULL TEXT 인덱스가 아니고 Unique Constraint 가 아닐경우

index dive가 스킾될 수 있다고 한다.

 

그 외로는

• eq_index_range_dive_limit 변수를 조정해서 특정 값을 넘어가면 index dive 스킵이 가능
• range_optimizer_max_mem_size 의 경우 지정 값 보다 메모리에 더 많은 in 값이 오면 실행계획이 포기되고 인덱스가 사용되지 않는다.

간단하게 index dive가 일어날 때 안일어 날 때의 성능을 비교해보자 (in안에 숫자가 굉장히 많다는 가정)

SELECT *
FROM orders
WHERE customer_id IN (101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111...200)

SELECT *
FROM orders FORCE INDEX (idx_customer_order)
WHERE customer_id IN (101, 102, 103, 104, 105, 106, 107, 108, 109....200)

 

극단적인 예시긴 하지만 이렇게 100개 가량의 수가 있을 때 index dive가 일어났을 때와
안일어났을 때를 비교하면

 

 

굉장히 큰 차이가 남을 알 수 있다.

 

prefix index

 

prefix index는 칼럼의 전체 값이 아닌 일부 접두사를 사용하여 인덱스를 생성하는 방법으로
BLOB, TEXT, VARCHAR 와 같은 전체 값을 인덱싱 하기 힘들 경우 사용한다.

 

이때 접두사의 길이를 정해진 한도내로 설정이 가능한데 적절히 인덱스 크기를 조절해야 한다.

• 접두사가 너무 길다 -> 사실상 인덱스의 의미가 없어진다.
• 접두사가 너무 짧다 -> 한 인덱스에 값들이 너무 많이 들어가 성능이 개선되지 않는다.

접두사 길이에 따른 인덱스 크기 변화를 간단한 예시로 보면

SELECT COUNT(*) AS c, left(city,1) as prefix
FROM world_cities
GROUP BY prefix
ORDER BY c DESC;

SELECT COUNT(*) AS c, left(city,3) as prefix
FROM world_cities
GROUP BY prefix
ORDER BY c DESC;

SELECT COUNT(*) AS c, left(city,6) as prefix
FROM world_cities
GROUP BY prefix
ORDER BY c DESC;

SELECT COUNT(*) AS c, left(city,9) as prefix
FROM world_cities
GROUP BY prefix
ORDER BY c DESC;

 

내가 사용한 데이터 셋은 8~9부터 유의미한 차이가 발생하지 않았다.

 

결론

---

이렇게 인덱스를 생성하거나 튜닝할 시 고려할만한 요소들을 간단하게 공부하고 실제로 적용해보았다.  

 

추후 진행할 토이 프로젝트나 실무에서도 공부한 인덱스 내용를을 적용할 수 있는 상황이 왔으면 좋겠다!  다음 포스트에서는 인덱스 외 다른 요소들을 통해 최적화 시키는 방법을 정리할 예정이다.