MySQL 실행계획 보는 방법

쿼리튜닝을 위한 인덱스 특징(innoDB, B-Tree)

인덱스는 정렬되어 있다.

• 왼쪽 컬럼인 column_1 순으로 먼저 정렬되고, column_1이 같다면 column_2 순으로 정렬된다.
• 따라서 인덱스를 기준으로 조회한 데이터는 인덱스 컬럼 순으로 정렬되어 있다.

 

인덱스는 스캔하는 방식으로 처리된다.

중간중간 데이터를 읽는 것이 아니라, 시작지점을 잡고 종료지점까지 쭉 스캔한다.

 

인덱스는 메모리 위에 존재한다.

• column_1 = 'A'인 레코드 중에 column_3 이 필요하다면?
  • 실제 column_1 = 'A'인 레코드를 모두 가져온 다음 물리 주소를 파악해서 물리 주소를 기반으로 디스크에서 값을 가져온다.
• 모든 컬럼 값을 인덱스에 올려놓을 수는 없기 때문에 성능적으로 타협하면서 세팅해야 한다.

 

성능 좋은 Where 절의 조건 = 인덱스를 잘 활용하는 조건

• 인덱스는 정렬되어 있으므로, 정렬된 데이터를 최대한도로 활용한다.(추가 정렬 작업을 수행하지 않도록)
• 인덱스는 스캔하는 방식으로 처리되므로, 적절한 범위를 스캔할 수 있도록 구성한다.
• 인덱스는 메모리 위에 존재하므로, 가능한 인덱스 내의 데이터를 조회한다.

• A로 끝나는 문자열에 대한 시작지점, 끝지점을 적당히 설정할 수 없기 때문에 모든 데이터를 읽어 들이게 된다.
• B로 시작하는 문자열 조회는, B로 시작하는 문자열의 시작지점과 끝지점을 알 수 있기 때문에 불필요한 데이터를 읽지 않을 수 있다.

 

실행계획

쿼리튜닝을 위한 실행계획 보는 법

• select 쿼리 앞에 explain을 붙여서 실행한다.
  • 더 상세한 결과를 원한다면 explain extended, explain partitions 가 있다.
• update / delete/ insert 구문은 실행계획을 확인할 방법이 따로 없다.
  • 동일한 where 절을 가지는 select 구문의 실행계획을 확인하여 대략적으로 파악할 수 있다.

 

실행계획 표기

id

• select 쿼리 별로 부여되는 식별자 값
• 서브쿼리를 사용하는 경우에는 id가 2 이상으로 표기될 수 있다.

 

select_type

• 각 단위 select 쿼리(id별로 구별되는 쿼리)가 어떤 타입의 쿼리인지 표기한다.
• 색칠된 코멘트는 개선이 필요할 수 있는 코멘트에 해당한다.

 

UNCACACHEABLE

• 사원번호를 random으로 추출한다음 union All하고 있는데 rand()는 항상 값이 달라지므로 캐시 할 수 없다.

 

• 무작위 정렬이 필수적인 테이블이라면 임의의 값을 저장하는 컬럼을 만들고 데이터를 생성할 때마다 무작위 값을 넣어준다.
• 무작위 컬럼에 인덱스를 생성하여 성능을 올린다.

 

dependent

• dependent는 독립적으로 수행할 수 없어, 외부 쿼리 결과에 의존하는 쿼리이다.

 

• dependent는 join으로 해결할 수 있다.

 

derived

• from 절에서 서브쿼리가 사용되는 경우에 해당한다.
• 데이터가 적은 경우에는 문제가 없으나 데이터가 커지면 문제가 생긴다.

 

cf) 임시 테이블

• 조회한 레코드를 추가적으로 정렬하거나 그룹핑해야 할 때, 사용하는 일시적인 테이블
  • 인덱스를 잘 사용했다면, 추가적인 정렬이 필요 없으므로 임시테이블을 사용하지 않는다.
• 기본적으로는 메모리 위에서 동작하지만, 지정된 크기를 넘어서는 데이터가 조회되는 경우 디스크를 사용하게 됩니다.
  • 디스크를 사용하는 임시테이블은 큰 속도저하가 있으므로 사용하지 않도록 튜닝이 필요합니다.

임시테이블을 사용하는 조건들이 꽤나 복잡해서 임시테이블을 사용하지 않는 조건을 먼저 알아보자.

• ORDER BY와 GROUP BY는 가능한 같은 인덱스에 존재하는 컬럼들로 처리한다.
• ORDER BY와 GROUP BY는 가능한 인덱스 컬럼의 순서대로 정렬/그룹핑을 진행한다.
  • 추가적인 정렬/그룹핑을 최소화한다.
• 필요한 값만 조회한다. (SELECT * FROM 금지)
  • 불필요한 데이터를 조회해서 임시테이블이 디스크에서 사용되는 상황을 방지한다.
• 정렬이 필요한 경우라면 DISTINCT 보다는 GROUP BY를 사용한다.
• 불필요한 서브쿼리는 가능한 조인으로 변경한다.

아래의 경우 임시테이블을 사용하게 된다.

• 추가적인 정렬/그룹핑이 필요한 경우
  • ORDER BY와 GROUP BY에 명시된 컬럼이 다른 경우
  • ORDER BY나 GROUP BY에 명시된 컬럼이 조인의 순서상 첫 번째 테이블이 아닌 경우
  • DISTINCT와 ORDER BY가 동시에 존재하는 경우
  • DISTINCT가 인덱스로 처리되지 못 한 경우
• UNION ALL이 사용된 경우
• 쿼리의 실행 계획에서 select_type이 “DERIVED” 인 경우

 

type

• MySQL 서버가 어떤 방식으로 레코드를 읽었는지를 표시해주는 컬럼으로 튜닝 시에 중요하게 확인해 봐야 하는 컬럼
• ALL 을 제외하고는 모두 인덱스를 사용하지만, 그렇다고 모두 효율적인 것은 아니다.
• 위 표는 효율적인 순서대로 작성되었다.(빨간색은 매우 나쁨)
  • index는 인덱스를 사용했다는 게 아니라 인덱스 풀스캔했다는 뜻으로 문제가 있다는 의미다.

 

인덱스 레인지 스캔

• 특정 범위의 데이터만 조회하는 방식
• 리프노드의 시작 위치를 찾고 해당 지점부터 마지막 범위에 해당하는 위치까지 순차적으로 조회
• 조회해야 하는 데이터가 너무 많아지는 경우, 풀 테이블 스캔이 더 효율적일 수 있다.
  • 데이터가 너무 많으면 사실상 인덱스를 다 스캔하는 것과 별 차이가 없기 때문이다.
  • 이런 경우를 막기 위해 where절을 잘 설정해야 한다.

 

인덱스 풀 스캔

• 인덱스를 효율적으로 활용하지 못한 스캔 방식
• 인덱스의 데이터를 전부 조회한다.
• 인덱스 풀 스캔으로 처리되지 않기 위해서는 인덱스 컬럼의 왼쪽 컬럼부터 조건으로 사용해야 한다.
  • where 절 내에서의 순서는 상관없다.
• 파일 I/O가 필요한 경우라면, 매우 비효율적으로 동작한다.

 

루스(Loose) 인덱스 스캔

• 인덱스는 기본적으로 정렬되어 있기 때문에 집합함수 중 정렬을 활용할 수 있는 함수들(MAX, MIN)은 인덱스를 모두 훑지 않고 건너뛰면서 스캔한다.
• 반대로 AVG, COUNT 집합 함수들은 인덱스 전체를 스캔해야 하는데 이런 경우를 Tight 인덱스 스캔이라고 한다.

 

풀 테이블 스캔

• 테이블 전체의 데이터를 스캔하는 방식
• where 절이나 on 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우(인덱스를 사용할 수 없는 경우)
• 인덱스 레인지 스캔을 할 수 있더라도, 옵티마이저가 풀 테이블 스캔이 더 효율적이라고 판단하는 경우
  • B-Tree를 샘플링해서 저장해 둔 통계정보를 기준으로 판단
  • 인덱스 레인지 스캔으로 조회해야 하는 레코드 수가 전체 레코드 수의 20~25%일 경우

 

possible_keys, key

• possible_keys : 쿼리를 처리할 때 사용될 법했던 인덱스의 목록
• key : 실제로 선택된 인덱스
• 인덱스는 옵티마이저가 자체적으로 판단하여 선택하지만 필요한 경우 force index 등의 예약어를 통해 강제할 수 있다.

 

extra

• 튜닝 시에 중요하게 확인해야 하는 컬럼

 

요구사항 확인이 필요한 경우

• 쿼리가 요구사항을 제대로 반영하고 있는지 확인해야 하는 코멘트
• 성능적으로는 문제가 없지만 DB 내에 필요한 값이 존재하지 않는 경우를 나타낸다.

 

실행 계획이 좋지 못한 경우

• 해당 코멘트가 조회되면 더 최적화할 수 있는지 검토가 필요하다.
• 불필요한 작업들을 추가로 하는 경우가 있을 수 있다.

 

실행 계획이 좋은 경우

 

해결 방식 예시