MySQL 실행 계획 : 실행 계획 분석 시 주의사항

MySQL 실행 계획 : 실행 계획 분석 시 주의사항

여기서는 쿼리의 실행 계획을 확인할 때 각 칼럼에 표시되는 값 중에서 특별히 주의해서 확인해야 하는 항목만 간략하게 정리하자.

1. Select_type 칼럼의 주의 대상

DERIVED

DERIVED는 FROM 절에 사용된 서브 쿼리로부터 발생한 임시 테이블을 의미한다. 임시 테이블은 메모리에 저장될 수도 있고 디스크에 저장될 수도 있다. 일반적으로 메모리에 저장하는 경우에는 크게 성능에 영향을 미치지 않지만, 데이터의 크기가 커서 임시 테이블을 디스크에 저장하면 성능이 떨어진다.

UNCACHEABLE SUBQUERY

쿼리의 FROM 절 이외의 부분에서 사용하는 서브 쿼리는 가능하면 MySQL 옵티마이저가 최대한 캐시되어 재사용될 수 있게 유도한다. 하지만 사용자 변수나 일부 함수가 사용된 경우에는 이러한 캐시 기능을 사용할 수 없게 만든다. 이런 실행 계획이 사용된다면 혹시 사용자 변수를 제거하거나 다른 함수를 대체해서 사용 가능할지 검토해 보는 것이 좋다.

DEPENDENT SUBQUERY

쿼리의 FROM 절 이외의 부분에서 사용하는 서브 쿼리가 자체적으로 실행하지 못하고, 외부 쿼리에서 값을 전달받아 실행되는 경우 DEPENDENT SUBQUERY가 표시된다. 이는 서브 쿼리가 먼저 실행되지 못하고, 서브 쿼리가 외부 쿼리의 결과 값에 의존적이기 때문에 전체 쿼리의 성능을 느리게 만드다. 서브 쿼리가 불필요하게 외부 쿼리의 값을 전달받고 있는지 검토해서, 가능하다면 외부 쿼리와의 의존도를 제거하는 것이 좋다.

2. Type 칼럼의 주의 대상

ALL, index

index는 인덱스 풀 스캔을 의미하며, ALL은 풀 테이블 스캔을 의미한다. 둘 다 대상의 차이만 있지 전체 레코드를 대상으로 하는 작업 방식이라서 빠르게 결과를 가져오기는 어렵다. 일반적인 OLTP 환경에 적합한 접근 방식은 아니므로 새로운 인덱스를 추가하거나 쿼리의 요건을 변경해서 이러한 접근 방법을 제거하는 것이 좋다.

3. Key 칼럼의 주의 대상

• 쿼리가 인덱스를 사용하지 못할 때 실행 계획의 Key 칼럼에 아무 값도 표시되지 않는다. 쿼리가 인덱스를 사용할 수 있게 인덱스를 추가하거나, WHERE 조건을 변경하는 것이 좋다.

4. Rows 칼럼의 주의 대상

• 쿼리가 실제 가져오는 레코드 수보다 훨씬 더 큰 값이 Rows 칼럼에 표시되는 경우에는 쿼리가 인덱스를 정상적으로 사용하고 있는지, 그리고 그 인덱스가 충분히 작업 범위를 좁혀 줄 수 있는 칼럼으로 구성됐는지 검토해보는 것이 좋다. 인덱스가 효율적이지 않다면 충분히 식별성을 가지고 있는 칼럼을 선정해 인덱스를 다시 생성하거나 쿼리의 요건을 변경해보는 것이 좋다.

• Rows 칼럼의 수치를 판단할 때 주의해야 할 점은 LIMIT가 포함된 쿼리라 하더라도 LIMIT의 제한은 Rows 칼럼의 고려 대상에서 제외된다는 것이다. 즉 "LIMIT 1"로 1건만 SELECT 하는 쿼리라 하더라도 Rows 칼럼에는 훨씬 큰 수치가 표현될 수도 있으며, 성능상 아무런 문제가 없고 최적화된 쿼리일 수도 있다는 것이다.

5. Extra 칼럼의 주의 대상

실행 계획의 Extra 칼럼에는 쿼리를 실행하면서 처리한 주요 작업에 대한 내용이 표시되기 때문에 쿼리를 튜닝할 때 중요한 단서가 되는 내용이 많이 표시된다. 주요 키워드는 기억했다가 실행 계획상에 해당 단어가 표시될 때는 더 자세히 검토하는 것이 좋다.

쿼리가 요건을 제대로 반영하고 있는지 확인해야 하는 경우

• Full scan on NULL key
• Impossible HAVING(MySQL 5.1부터)
• Impossible WHERE(MySQL 5.1부터)
• Impossible WHERE noticed after reading const tables
• No matching min/max row(MySQL 5.1부터)
• No matching row in const table(MySQL 5.1부터)
• Unique row not found(MySQL 5.1부터)

위와 같은 코멘트가 Extra 칼럼에 표시된다면 우선 쿼리가 요건을 제대로 반영해서 작성됐거나 버그가 생길 가능성은 없는지 확인해야 한다. 또한 개발용 데이터베이스에 테스트용 레코드가 제대로 준비돼 있는지 확인해보는 것도 좋다. 이 항목들은 성능과 관계가 깊지 않고 단지 "그런 레코드가 없음"이라는 의미가 강하게 때문에 이 쿼리로 인한 버그의 가능성이 있을지를 집중적으로 검토하는 것이좋다. 물론 쿼리가 업무적인 요건을 제대로 반영하고 있다면 무시해도 된다.

쿼리의 실행 계획이 좋지 않은 경우

• Range checked for each record (index map: N)
• Using filesort
• Using join buffer (MySQL 5.1부터)
• Using temporary
• Using where

위와 같은 코멘트가 Extra 칼럼에 표시된다면 먼저 쿼리를 더 최적화할 수 있는지 검토해보는 것이 좋다. 마지막의 Using where는 사실 대부분의 쿼리에서 표시되는 경향이 있기 때문에 그냥 지나치기 쉬운데, 만약 실행 계획의 Rows 칼럼의 값이 실제 SELECT되는 레코드 건수보다 상당히 높은 경우에는 반드시 보완해서 Rows 칼럼의 값과 실제 SELECT 되는 레코드의 수의 차이를 최대한 줄이는 것이 중요하다. 쿼리의 실행 계획에서 이러한 문구가 사라질 수 있다면 최선이겠지만 그렇지 않더라도 성능상 허용 가능하다면 넘어가도 좋을 듯하다. 단 반드시 자세히 검토해야 한다는 사실을 잊지 말자.

쿼리의 실행 계획이 좋은 경우

• Distinct
• Using index
• Using index for group-by

여기에 표시된 항목은 최적화되어 처리되고 있음을 알려주는 지표 정도로 생각하자. 특히 두 번째의 Using index는 쿼리가 커버링 인덱스로 처리되고 있음을 알려주는 것인데, MySQL에서 제공할 수 있는 최고의 성능을 보여줄 것이다. 만약 쿼리를 아무리 최적화해도 성능 요건에 미치지 못한다면 인덱스만으로 쿼리가 처리(커버링 인덱스)되는 형태로 유도해보는 것도 좋다.

참고

• Real MySQL