MySQL EXPLAIN 실행 계획에서 possible_keys와 key 컬럼의 차이점 및 쿼리 성능 최적화 활용법

MySQL EXPLAIN 실행 계획에서 possible_keys와 key 컬럼은 인덱스 사용과 관련된 서로 다른 정보를 제공합니다. 이를 이해하고 적절히 활용하면 쿼리 성능을 효과적으로 최적화할 수 있습니다.

possible_keys와 key 컬럼의 차이점

• possible_keys: MySQL이 해당 테이블에서 쿼리 실행 시 사용할 수 있는 가능한 인덱스 목록을 나타냅니다. 즉, WHERE 절이나 JOIN 조건에 의해 활용될 수 있는 모든 관련 인덱스가 여기에 표시됩니다. 만약 NULL이라면, 쿼리 조건에 맞는 인덱스가 없음을 의미합니다.
• key: 실제로 MySQL 옵티마이저가 쿼리 실행에 선택하여 사용한 단일 인덱스을 나타냅니다. 이 인덱스는 possible_keys 중 하나일 수도 있고, 때로는 possible_keys에 없지만 쿼리에서 선택한 컬럼을 모두 포함하는 커버링 인덱스일 수도 있습니다. 만약 NULL이라면, 인덱스를 사용하지 않고 전체 테이블 스캔을 수행한 것입니다.

쿼리 성능 최적화를 위한 활용 방법

1. possible_keys 확인: EXPLAIN 결과에서 possible_keys가 NULL인 경우, 쿼리 조건에 적합한 인덱스가 없다는 뜻이므로, WHERE 절에 자주 사용되는 컬럼에 적절한 인덱스를 생성하는 것이 성능 개선에 도움이 됩니다.
2. key 컬럼 분석: 실제 사용된 인덱스(key)가 쿼리 성능에 적합한지 확인합니다. 만약 key가 NULL이거나 비효율적인 인덱스가 선택되었다면, 인덱스 힌트(FORCE INDEX, USE INDEX, IGNORE INDEX)를 활용해 옵티마이저의 인덱스 선택을 조정할 수 있습니다.
3. 커버링 인덱스 활용: key 컬럼에 possible_keys에 없는 인덱스가 표시되는 경우, 이는 커버링 인덱스가 선택된 것으로, 쿼리에 필요한 모든 컬럼을 인덱스가 포함하여 테이블 접근 없이 인덱스만으로 데이터를 조회하는 최적화입니다. 이런 인덱스 설계가 성능 향상에 유리합니다.
4. EXPLAIN 반복 확인: 인덱스 생성 또는 힌트 적용 후 EXPLAIN을 다시 실행하여 possible_keys와 key 컬럼의 변화를 확인하고, 쿼리 실행 계획이 개선되었는지 검증합니다.

요약하면, possible_keys는 사용 가능한 인덱스 후보군을 보여주고, key는 실제로 선택된 인덱스를 나타냅니다. 이를 통해 인덱스가 적절히 설계되고 선택되었는지 판단하고, 필요 시 인덱스 추가나 힌트 적용으로 쿼리 성능을 최적화할 수 있습니다.

참고 자료

• 10.8.2 EXPLAIN Output Format