[DB 6편] MySQL 인덱스 성능 관련 정리 (prefix, 복합인덱스)

목차는 DB 목차 에 있습니다.

[DB 6편] MySQL 인덱스 성능 관련 정리 (prefix, 복합인덱스)

인덱스 Prefix

• MySQL 에서는 인덱스를 설정할 때, 컬럼의 prefix 일부를 인덱스로 설정하는 기능을 제공하고 있습니다.
• prefix 일부를 사용한다는건 인덱스의 길이가 줄어든다는 의미이며, 인덱스의 길이가 줄어든다는건 인덱스 데이터에 접근할 때, 빠르게 접근이 가능할 확률을 높인다는 의미입니다. 저장하는 공간도 줄어듭니다.
• 예를 들면 다음과 같습니다.
  • Alter table test add key (index_test_column(3));
  • test 테이블의 index_test_column 컬럼의 앞의 3글자만 인덱스로 설정합니다.

인덱스 prefix 주의사항

1. 컬럼 전체를 인덱스로 사용하지 않고, prefix 로 사용한다는건 책의 목차 내용이 잘려서 나오는 것과 똑같습니다.

   • 목차 내용이 일부만 있는 상태에서 원하는 데이터를 찾는 것은 일반적으로 어려운 일입니다.

   • 그렇기에 인덱스 prefix 를 결정할 때, 저장되는 데이터의 성격과 실제 데이터 등을 보고 컬럼 전체를 사용할 때랑, prefix 사용할 때의 비율이 비슷한 것을 찾아야 합니다.

2. order by 쿼리, group by 쿼리, 커버링 인덱스에 prefix 인덱스를 사용하지 못합니다.

인덱스 prefix 를 사용하면 좋은 예시

• 웹 세션을 저장하거나 토큰을 저장할 떄, prefix 를 사용하면 좋을 것입니다.
• 왜냐하면 세션이나 토큰의 구성방식은 보통 규칙성이 없이 고르게 분포되도록 설계하기 때문입니다.

복합 인덱스

• 복합 인덱스란 인덱스 키를 여러개 잡는 것입니다.
• 이 때, 복합 인덱스의 열순서를 어떻게 정하는지에 따라 성능이 달라질 수 있습니다.
• MySQL InnoDB 기준으로 인덱스 저장원리를 이해할 필요가 있습니다.
  • 인덱스를 저장할 때, 열순서로 저장합니다.
  • 예를 들어, A,B,C 의 복합인덱스가 있는데 B, C 만 조회 조건이 걸리면 조회가 안됩니다.

복합 인덱스 배경

• where 조건에 A,B,C 3개의 조회 조건이 있을 때, 각각에 대해서 인덱스를 만든다면 성능상 안좋을 수 있습니다.
• MySQL 의 경우 인덱스 병합 전략을 이용해서 위 상황을 해결해볼려 할 수 있지만 인덱스 병합을 썼을 때, 성능이 좋게 나오는지에 대한 분석 및 판단을 해야해서 시간이 소요됩니다.
• 그렇기에 처음부터 인덱스를 설계할 때, A,B,C 조회 조건 전부 인덱스를 생성하는게 아니라 복합 인덱스를 썼을 때, 다른 도메인에 영향을 안주는지, CUD 부하가 늘어나도 괜찮은지 등을 복합적으로 고려해 사용하는 것이 좋습니다.

복합 인덱스 열순서를 정하는 기준

• 데이터 선택성 비율이 좋은 컬럼순서로 정하는 것이 좋습니다.
• 데이터 분포도를 봤을 때, 어떤 컬럼으로 조회했을 때, 효율적으로 찾을수 있는지 판단해서 정하면 됩니다.