MySQL 실행 계획의 "Extra" 필드로 튜닝 방법

MySQL의 실행계획을 볼 때 그냥 무시하고 넘어가는 필드가 있습니다. 바로 제일 마지막에 있는 "Extra" 필드죠. 생각보다 이 텍스트를 잘 해석한다면 더 좋은 쿼리 성능을 낼 수 있습니다. 이글에서는 Extra 필드를 이해하고 해석하면 MySQ이 쿼리를 개선할 수 있는 요소에 대해 알아보도록 하겠습니다. 
 
만약 MySQL 실행 계획에 대해 아직 이해하지 못했다면 아래 내용을 먼저 읽고 오는 것을 권장합니다. Extra 필드는 실행 계획의 일부입니다.
 
MySQL 실행계획 보는 법, 성능을 올리는 유일한 방법!!
 

Extra 필드 해석

실행 계획의 "Extra" 필드는 MySQL이 쿼리를 처리하는 방법에 대한 추가 정보를 제공합니다. 추가 정보처럼 보일 수도 있지만 성능 튜닝을 위한 인사이트를 줄 수도 있습니다.

자~ 'Extra' 필드로 판단하는 간딘한 방법을 소개하겠습니다. 나올수 있는 몇 가지 항목에 대해 좋음, 보통, 나쁨으로 표시해 드리면 ‘나쁨’ 부분 대해 튜닝해 주세요. 바로 각 항목의 의미를 살펴 볼까요.
 

MySQL 실행계획 Extra 해석 및 튜닝

Using Where(좋음)

이는 MySQL이 행을 필터링하기 위해 WHERE 절을 적용하고 있음을 의미합니다. 쿼리에서 데이터를 필터링해야 하는 경우 추가 필드에 "Using where"가 표시되면 MySQL이 해당 작업을 수행하고 있음을 의미합니다.
 

Using index(좋음)

인덱스를 사용한다는 정보는 아주 좋습니다. 이는 MySQL이 전체 테이블을 읽을 필요 없이 인덱스에서 직접 데이터를 가져오는 것을 의미합니다. 이런 경우는 칼럼정보가 인덱스에 전부 존재하는 경우입니다. Index Full Scan 같은 개념인데요. 전체 테이블보다 인덱스가 더 작고 검색 속도가 더 빠르기 때문에 더 빠릅니다. 
 

Using index for group-by(좋음)

테이블 접근방식은 Using index 와 갑습니다. MySQL 이 실제 테이블에 대한 어떠한 추가적인 디스크 접근 없이 GROUP BY 나 DICTINCT 쿼리에 사용된 모든 칼럼에 대한 인덱스를 이용한다는 의미입니다. 추가적으로 각각의 group에 단지 몇개의 인덱스 항목만이 읽히도록 가장 효율적인 방식으로 인덱스가 사용합니다. 
 

Using filesort(나쁨)

이 정보는 나쁜 처리의 신호입니다. 파일 정렬은 MySQL이 행을 검색한 후 행을 정렬해야 할 때 발생합니다. 이는 일반적으로 쿼리가 완전히 최적화되지 않았음을 의미합니다.
 
MySQL이 sort를 위해 추가적인 과정을 필요로 한다. sort과정은 조인타입에 따라 모든 행을 검색하고 where절에 의해 매치된 모든 행들의 키값을 저장하기 때문에 느립니다. 그래서 파일 정렬이 아닌 인덱스를 사용하여 데이터가 올바르게 정렬되도록 하면 성능을 향상할 수 있습니다.
 

Using temporary(나쁨)

이것도 또 다른 위험 신호입니다. 이는 MySQL이 중간 결과를 보관하기 위해 임시 테이블을 생성하고 있음을 나타냅니다. 이로 인해 특히 대규모 데이터 세트의 경우 성능이 저하될 수 있습니다. 
 
이는 쿼리 내에 GROUP BY 와 ORDER BY 절이 각기 다른 칼럼을 사용할 때 발생합니다. 적절한 인덱스를 사용하는 등 임시 테이블을 방지하도록 쿼리를 최적화가 필요합니다. 
 

No table(보통)

이 구문은 이것이 반드시 나쁜 것은 아닙니다. 이는 일반적으로 SELECT NOW()와 같이 MySQL이 테이블 액세스가 필요하지 않은 결과를 반환하는 쿼리에 나타납니다. 예를 들어, FROM 구문이 없거나, FROM DUAL 구문이 있을 때도 발생합니다. 
 

Not exists(보통)

MySQL이 LEFT OUTER JOIN 을 할 때 발생합니다. 매치되는 한 행을 찾으면 더 이상 매치되는 행을 검색하지 않는데요. 예를 들어, 아래와 같은 SQL에서 경우에 해당한다.

SELECT *  FROM a_table a LEFT OUTER JOIN b_table b ON a.id=b.id WHERE b.id IS NULL ;

 

range checked for each record (index map: #)(보통)

최적의 인덱스가 없는 차선의 인덱스를 사용한다는 의미입니다. 옵티마이저가 사용하기 좋은 인덱스가 없다고 판단했을 때 대신 선행된 테이블의 칼럼값에 따라 몇몇 인덱스를 사용할 수 있습니다. 이것은 그리 빠르지 않으나 인덱스가 없는 조인의 경우보다는 빠르다.
 

Select tables optimized away(보통)

인덱스, 또는 MyISAM용 COUNT(*)을 사용하되 GROUP BY 구문은 사용하지 않은 채로 처리된 집단 함수(MIN(), MAX())만을 가지고 있을 때 나타납니다.

Using where with pushed condition(보통)

이 텍스트는 NDB Cluster 테이블만 적용됩니다. 이것은 MySQL 클러스터가 인덱스가 되지 않은 칼럼(non-indexed column)과 상수(constant) 간의 직접비교(=)의 효율성을 개선하기 위해서 조건문을 푸시다운(condition pushdown)하는 중이라는 의미를 갖습니다. 이와 같은 경우, 조건문은 동시에 값이 검사 되는 클러스터의 모든 데이터 노드로 푸시다운 됩니다. 

이것은 매치되지 않는 열을 네트워크 전체에 보낼 필요성을 없애 주며, 조건문 푸시 다운을 하지 않는 경우에 비해서 5~10배의 속도 향상을 얻을 수가 있다.
 

그 외의 정보(보통)

• Distinct : MySQL 이 매치되는 첫행을 찾는 즉시 검색을 중단한다는 의미입니다. 
• Full scan on NULL key : 인덱스 룩업(index-lookup) 접속을 할 수 없고 fallback 방식으로 서브 쿼리 최적화를 할 때 나옵니다. 
• Impossible WHERE noticed after reading const tables : 모든 const(system) 테이블 값을 읽었으며, WHERE 구문이 항상 거짓(false) 일 때 나옵니다.
• Using join cache : 테이블을 부분적으로 읽어온 후에, 읽어 온 열을 사용해서 조인을 실행할 경우 나타납니다. 
• Using sort_union(...) , Using union(...) , Using intersect(...) : 이는 인덱스 병합 조인타입에서 Index Scan이 병합되는 형태를 말합니다. 

 

Extra 필드를 이용한 튜닝

1. 가장 튜닝이 필요한 신호

 Extra 필드에서 "Using filesort", "Using temporary"은 제가 앞에서 ’나쁨‘으로 표시했습니다. 이는 쿼리가 느릴 수 있다는 가장 일반적인 신호입니다. 쿼리를 가능한 한 빠르게 하려면, Extra 값의 Using filesort 나 Using temporary가 나오지 않게 주의해야 한다.
 

2. 인덱스 검토

'Using Index'이 표시되지 않고 'Using where'가 표시되면 인덱스를 생성하거나 조정해야 한다는 의미입니다. MySQL은 일부러 Full Table Scan하는 것이 아니라 인덱스를 사용하는 것이 성능에 유리합니다. 
 

3. SQL 조정 후 테스트 반복

잠재적인 문제를 식별한 후 쿼리를 수정합니다. 여기에는 인덱스 추가, 쿼리 재구성 또는 데이터 비정규화가 포함될 수 있습니다. 변경사항으로 인해 실행 계획이 개선되었는지 확인하려면 EXPLAIN을 다시 실행하세요. 최적화를 위해서는 가끔은 반복적으로 해봐야 합니다. 'Extra' 필드에 최적의 결과가 표시될 때까지 쿼리를 계속 수정하고 테스트하세요.

마치며

MySQL 실행 계획의 "Exta" 필드를 이해하고 사용하는 것은 SQL 튜닝의 마지막을 챙기는 것과 같습니다. 뭔가 빠뜨린 부븐이 없는지 보는 의미죠. 이 필드를 잘 관찰하면 비효율성을 찾을 수 있기 때문인데요. MySQL 성능을에 좀 더 개선된 조치를 취할 수 있습니다.
 
MySQL 힌트(Hint) 종류 및 사용법, 꼭 확인해야 할 주의사항

MySQL 힌트(Hint) 종류 및 사용법, 꼭 확인해야 할 주의사항