[MySql] 성능 개선에 관하여_해결 해 보자_2_경험

https://raconer.tistory.com/entry/MySql-%EC%84%B1%EB%8A%A5-%EA%B0%9C%EC%84%A0%EC%97%90-%EA%B4%80%ED%95%98%EC%97%AC%ED%95%B4%EA%B2%B0-%ED%95%B4-%EB%B3%B4%EC%9E%901explain

[MySql] 성능 개선에 관하여_해결 해 보자_1_explain

경험에 따른 Query 튜닝

1. Index 설정 확인

질문.1

예)
Mysql 에서 Index 설정할때

date, id, type을 date_id_type_index로 설정했을때
date, id 만 where절에 검색 한다면

date_id_type_index을 타게 될까?

 

질문1. 정답

date_id_type_index 인덱스가 사용될 수 있다.

> 복합 인덱스는 "선행(prefix) 컬럼"에 대해서도 사용할 수 있기 때문에, 인덱스의 첫 번째 컬럼(date)과 두 번째 컬럼(id)만으로 검색하는 경우에도 이 인덱스가 사용될 수 있다.

* 상세 설명:

-> 인덱스의 구조: date_id_type_index(date, id, type)

인덱스는 date -> id -> type 순서로 구성된 트리 구조로 구성

* 인덱스 활용:

인덱스가 date, id, type 순서로 되어 있으므로,

이 인덱스는 `date`, `date, id`, `date, id, type`으로 검색할 때 모두 사용될 수 있다.

예를 들어

WHERE date = '2024-01-01'
AND id = 123

쿼리를 실행하면 MySQL은 date_id_type_index 인덱스를 사용하여 효율적으로 검색을 수행

*제약 조건:

인덱스는 항상 선행(prefix) 컬럼에 대해서만 유효
만약 id와 type만으로 검색하려고 한다면, 이 인덱스는 사용할 수 없습니다 (date를 건너뛰었기 때문에).

 

질문 2. 

그러면 index가 2개가 있어

1. date_type_index : date, type 
2. date_category_index : date, category

이 순으로 등록 되었는데

where date = '2024-08-10'
and category='ENT'

라고 하면 어떤 인덱스가 실행이 될까?

질문2. 정답

date_category_index 인덱스가 실행이 된다.

하지만 date_category_index가 실행이 안되는 경우가 있다!!

경우 1. 카디널리티(Cardinality)와 인덱스 선택

• MySQL은 인덱스의 카디널리티(중복도가 낮고 유니크한 값이 많은지)를 기반으로 최적의 인덱스를 선택
• 만약 date_type_index의 카디널리티가 date_category_index다 더 높다고 판단되면, 옵티마이저가 date_type_index를 선택할 수도 있다.
• 확인 방법
  • SHOW INDEX FROM your_table_name;

경우 2. 통계 정보 부정확 또는 갱신 필요:

• MySQL의 쿼리 옵티마이저는 인덱스를 선택할 때 테이블의 통계 정보를 기반으로 결정.
• 이 통계 정보가 오래되었거나 정확하지 않다면 옵티마이저가 부적절한 인덱스를 선택할 수 있다.
• 해결 방법
  • ANALYZE TABLE your_table_name;
  • 명령어를 사용해 테이블의 통계 정보를 갱신

경우 3. 옵티마이저 힌트 또는 FORCE INDEX 사용:

• 쿼리 내에서 옵티마이저 힌트 또는 FORCE INDEX를 사용해 특정 인덱스를 강제로 사용하도록 설정된 경우, 이로 인해 date, type 인덱스가 선택될 수 있다.
• 해결 방법
  • 쿼리 내에 이러한 힌트가 사용되고 있는지 확인하고, 불필요하다면 제거하거나 수정

경우 4. 인덱스 포함된 조건에 따라:

• MySQL 옵티마이저가 date, type 인덱스가 더 적합하다고 판단할 다른 이유가 있을 수 있다.
  예를 들어, 데이터 분포나 쿼리 실행 계획에 따라 옵티마이저가 이 인덱스를 더 적합하게 여길 수 있습니다.
• 해결 방법
  • EXPLAIN 명령어를 사용하여 쿼리 실행 계획을 분석하고, 왜 date, type 인덱스가 선택되는지 확인

5. 인덱스의 선행 컬럼이 같은 경우:

• 두 인덱스 모두 date로 시작하므로, 옵티마이저가 첫 번째로 생성된 인덱스(date, type)를 우선적으로 사용할 수도 있다.
  특히 두 인덱스의 첫 번째 컬럼이 동일할 때 발생할 수 있다.
• 해결 방법
  
  • EXPLAIN으로 실행 계획을 분석하거나, 필요시 인덱스를 재구성하거나 제거하여 옵티마이저가 더 적합한 인덱스를 선택하도록 유도

경우 6. 옵티마이저의 휴리스틱 결정:

• MySQL 옵티마이저는 휴리스틱에 따라 때때로 예상치 못한 결정을 내릴 수 있습니다. 이럴 경우 인덱스 사용을 강제하거나 인덱스를 재설계하는 것이 필요할 수 있다.