MySQL 인덱스가 무시되는 이유와 실행계획 최적화 방법

MySQL 인덱스 무시 원인 분석

MySQL에서 인덱스를 생성했음에도 불구하고 Full Scan이 발생하는 경우는 생각보다 흔합니다. 이러한 현상은 몇 가지 이유로 발생할 수 있으며, 이를 이해하기 위해서는 MySQL 옵티마이저가 인덱스를 선택하는 방식에 대한 이해가 필요합니다. 옵티마이저는 쿼리를 실행하기 위한 최적의 경로를 찾기 위해 여러 요소를 고려합니다.

첫 번째로, 쿼리의 WHERE 조건이 인덱스와 적절하게 매칭되지 않을 경우 인덱스가 무시될 수 있습니다. 예를 들어, WHERE 절에 사용된 조건이 인덱스 컬럼과 일치하지 않거나, 사용된 연산자가 인덱스의 특성에 맞지 않으면 옵티마이저는 인덱스를 사용하지 않고 Full Scan을 선택할 수 있습니다.

복합 인덱스의 컬럼 순서

복합 인덱스는 여러 컬럼을 조합하여 생성된 인덱스입니다. 이 경우, 컬럼의 순서가 매우 중요합니다. 인덱스는 왼쪽에서 오른쪽으로 작동하기 때문에, WHERE 절의 조건이 인덱스의 첫 번째 컬럼에 대해 정확히 매칭되어야만 인덱스가 효과적으로 사용됩니다.

예를 들어, INDEX (A, B) 형태의 복합 인덱스가 있을 때, WHERE A = ? AND B = ? 조건은 인덱스를 잘 활용하지만, WHERE B = ? AND A = ? 조건은 인덱스를 사용하지 못할 가능성이 큽니다. 따라서 복합 인덱스의 컬럼 순서를 정의할 때는 쿼리의 특성을 고려하여 신중하게 결정해야 합니다.

선택도 및 통계 정보

인덱스의 선택도는 데이터베이스에서 인덱스가 얼마나 효과적으로 특정 데이터를 찾는지를 나타내는 지표입니다. 선택도가 낮은 인덱스는 데이터의 분포가 고르게 퍼져 있지 않기 때문에 옵티마이저가 인덱스를 사용하지 않을 수 있습니다.

또한, MySQL은 통계 정보를 사용하여 인덱스 사용 여부를 결정합니다. 통계 정보는 인덱스에 대한 데이터의 분포를 기반으로 하며, 이 정보가 최신 상태가 아닐 경우 옵티마이저가 잘못된 판단을 내릴 수 있습니다. 따라서 ANALYZE TABLE 명령어를 사용하여 통계 정보를 주기적으로 업데이트하는 것이 중요합니다.

정렬 조건의 영향

정렬 조건도 인덱스 사용 여부에 큰 영향을 미칩니다. ORDER BY 절이 있는 쿼리에서 인덱스가 적절하게 사용되지 않으면, 옵티마이저는 인덱스를 건너뛰고 Full Scan을 선택할 수 있습니다.

예를 들어, 인덱스가 정렬된 상태를 반영하지 않으면, 쿼리 실행 시 데이터가 정렬되지 않은 상태로 반환될 수 있습니다. 따라서 인덱스를 정의할 때는 가능한 정렬 조건과 일치하도록 설계하는 것이 좋습니다.

실행 계획 최적화 체크리스트

• 인덱스를 생성하기 전, 쿼리의 WHERE 절과 JOIN 조건을 검토하라.
• 복합 인덱스의 컬럼 순서를 쿼리의 사용 패턴에 맞게 설정하라.
• 인덱스의 선택도를 분석하고, 필요 시 인덱스를 재구성하라.
• 통계 정보를 주기적으로 업데이트하여 옵티마이저의 판단이 정확하도록 하라.
• 쿼리에서 ORDER BY 절과 인덱스의 정렬 조건이 일치하는지 확인하라.