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

지금까지 MySQL에서 쿼리를 처리하는 방식이나 실행 계획에 대해 살펴보겠습니다. 쿼리의 실행 계획만으로도 상당히 내용이 많아서 모두 기억하자면 상당히 힘들 것입니다. 그래서 여기서는 쿼리의 실행 계획을 확인할 때 각 칼럼에 표시되는 값 중에서 특별히 주의해서 확인해야 하는 항목만 간략하게 정리했습니다.

Select_type 칼럼의 주의 대상

---

DERIVED

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

UNCACHEABLE SUBQUERY

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

DEPENDENT SUBQUERY

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

Type 칼럼의 주의 대상

---

ALL, index

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

Key 칼럼의 주의 대상

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

Rows 칼럼의 주의 대상

---

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

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

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에서 제공할 수 있는 최고의 성능을 보여줄 것입니다. 만약 쿼리를 아무리 최적화해도 성능 요건에 미치지 못한다면 인덱스만으로 쿼리가 처리(커버링 인덱스)되는 형태로 유도해보는 것도 좋습니다.

출처: https://12bme.tistory.com/168?category=682920 [길은 가면, 뒤에 있다.]