쿼리의 실행계획을 확인할 때, 각 컬럼에 표시되는 값 중에서 특별히 주의해서 확인해야 하는 항목이 있다. 아래 내용을 확인하자.
6.4.1 Select_type 컬럼의 주의 대상
- DERIVED : FROM절에서 사용된 서브쿼리로부터 발생한 임시 테이블을 의미한다. 임시 테이블은 메모리 or 디스크에 저장되며, 디스크에 저장될 때 성능이 떨어지는 점을 유의하자.
- UNCACHEABLE SUBQUERY : FROM 절 외 부분에서 사용하는 서브 쿼리는 가능하면 MySQL 옵티마이저가 최대한 캐시되어 재사용 될 수 있도록 유도한다. 하지만 사용자 변수나 일부 함수가 사용된 경우 캐시 기능을 사용할 수 없기 때문에, 사용자 변수 제거 혹은 다른 함수로 대체하는 방법을 통해 사용 가능할지 검토해보자.
- DEPENDENT SUBQUERY : FROM 절 외 부분에서 사용하는 서브 쿼리가 자체적으로 실행되지 못하고, 외부 쿼리에서 값을 전달받아 실행되는 경우 표기되는 현상이다. 이는 서브 쿼리가 먼저 실행되지 못하고, 서브 쿼리가 외부 쿼리 결과의 값에 의존적이기 때문에 전체 쿼리의 성능을 느리게 만든다. 가능하다면 외부 쿼리와의 의존도를 제거해주자.
6.4.2 Type 컬럼의 주의 대상
- All, index : ALL은 테이블 풀스캔을 의미하며, index는 인덱스 풀 스캔을 의미한다. 둘 다 대상의 차이만 있지 풀스캔 작업을 하기 때문에, 따르게 결과를 가져오기는 어렵다. 일반적인 OLTP 환경에 적합한 방식은 아니므로, 새로운 인덱스를 추가하거나 쿼리의 요건을 변경해 이런 접근 방법을 제거해주는 것이 좋다.
6.4.3 Keys 컬럼의 주의 대상
쿼리가 인덱스를 사용하지 못할 때 실행 계획의 Key 컬럼에 아무 값도 표시되지 않는다. 쿼리가 인덱스를 사용할 수 있도록 인덱스를 추가하거나, WHERE 조건을 변경해주는 것이 좋다.
6.4.4 Rows 컬럼의 주의 대상
쿼리가 실제 가져오는 레코드 수보다 훨씬 더 큰 값이 Rows 컬럼에 표기되는 경우, 쿼리가 인덱스를 정상적으로 사용하고 있는지, 그리고 그 인덱스가 충분히 작업 범위를 좁혀 줄 수 있는 컬럼으로 구성되어 있는지 검토해보는 것이 좋다. 인덱스가 효율적이지 않다면 충분히 식별성(카디널리티가 높은)을 갖고 있는 컬럼을 선정해 인덱스를 생성하거나 쿼리의 요건을 변경하자.
LIMIT가 포함된 쿼리라고 하더라도, Rows와는 연관이 없다는 점을 유의하자.(LIMIT 건수보다 Rows 컬럼에 더 큰 수치가 표현되더라도, 성능상 아무런 문제가 없고 최적화된 쿼리일 수도 있다)
6.4.5 Extra 컬럼의 주의 대상
실행 계획의 Extra 컬럼에는 쿼리를 실행하면서 처리한 주요 작업에 대한 내용이 표시되기 때문에, 쿼리를 튜닝할 때 중요한 단서가 되는 내용이 많이 표시된다.
쿼리가 요건을 제대로 반영하고 있는지 확인해야 하는 경우
- Full scan on NULL key
- Impossible HAVING
- Impossible WHERE
- Impossible WHERE noticed after reading const tables
- No matching min/max row
- No matching row in const table
- Unique row not found
위와 같은 코멘트가 Extras 컬럼에 표기된다면, 우선 쿼리가 요건을 제대로 반영해서 작성됐거나 버그가 발생할 가능성은 없는지 확인해야 한다. 혹은 DEV DB에 테스트용 레코드가 있는지 확인해보자. 이 항목들은 성능과는 관계가 없고, 그런 레코드가 없음이라는 의미가 강하기 때문에 이 쿼리로 인한 버그의 가능성이 있을지를 집중적으로 검토해보는 것이 좋다. 물론 쿼리가 업무적인 요건을 제대로 반영하고 있다면 무시해도 된다.
쿼리의 실행 계획이 좋지 않은 경우
- Range checked for each record
- Using filesort
- Using join buffer
- Using temporary
- Using where
위와 같은 코멘트가 표기된다면, 먼저 쿼리를 최적화할 수 있는지 검토해보는 것이 좋다. “Using where”의 경우 대부분의 쿼리에서 표시되긴 하지만, 만약 실행 계획의 Rows 컬럼의 값이 실제 SELECT 되는 레코드 건수보다 상당히 높은 경우, 보완해서 Rows컬럼의 값과 실제 SELECT 되는 레코드 건수의 차이를 줄여주자. 쿼리의 실행계획에서 이런 문구가 사라지는 것이 베스트이긴 하지만, 그렇지 않더라도 성능상 허용 가능하다면 넘어가도 좋다.
쿼리의 실행 계획이 좋은 경우
- Distinct
- Using index
- Using index for group-by
위 코멘트는 보통 최적화되어 처리되고 있음을 알려주는 지표라고 생각하자. 특히 “Using Index”는 쿼리가 커버링 인덱스로 처리되고 있음을 알려주는 것이며, MySQL에서 제공할 수 있는 최고의 성능을 보여준다. 만약 쿼리를 아무리 최적화해도 성능 요건에 미치지 못한다면, 인덱스만으로 쿼리가 처리(커버링 인덱스)되는 형태로 유도해보는 것도 좋다.
