좋고 나쁨을 판단하는 기준

MySQL - SQL 튜닝의 실행 계획 파헤치기(1)에서는 SQL 실행 계획에서 어떤 정보를 확인할 수 있는지 살펴보았다. 그렇다면, SQL 튜닝이 필요한지 아닌지에 대한 기준은 어떻게 세워야 할까? 실행계획에서 어떤 항목들을 참고해야 할까? 어느 상황에나 적용할 수 있는 명확한 정답은 없지만, 대개 select_type, type, extra 항목을 참고하여 SQL 튜닝 대상을 검토한다.

select_type의 판단 기준

• 좋음: SIMPLE, PRIMARY, DERIVED
• 나쁨: DEPENDENT, UNCACHEABLE

type 항목의 판단 기준

• 좋음: system, const, eq_ref
• 나쁨: index, all

extra 항목의 판단 기준

• 좋음: Using index
• 나쁨: Using filesort, Using temporary

확장된 실행 계획 수행

MySQL에서는 확장된 실행 계획을 수행함으로써, EXPLAIN으로 확인한 실행 계획 이외의 추가 정보를 확인할 수 있다.

EXPLAIN FORMAT = TRADITIONAL

TRADITIONAL은 단순히 EXPLAIN 키워드만 사용할 때와 같은 실행 계획 정보가 출력된다.

EXPLAIN FORMAT = TRADITIONAL 
SELECT * FROM 사원 WHERE 사원번호 BETWEEN 10001 AND 100000
;

EXPLAIN FORMAT = JSON

JSON 형태로 추가된 실행 계획 항목을 확인할 수 있다.

EXPLAIN FORMAT=JSON
SELECT * FROM 사원 WHERE 사원번호 BETWEEN 10001 AND 100000
;

# 실행결과
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "30045.91"
    },
    "table": {
      "table_name": "사원",
      "access_type": "range",
      "possible_keys": [
        "PRIMARY"
      ],
      "key": "PRIMARY",
      "used_key_parts": [
        "사원번호"
      ],
      "key_length": "4",
      "rows_examined_per_scan": 149490,
      "rows_produced_per_join": 149490,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "15096.91",
        "eval_cost": "14949.00",
        "prefix_cost": "30045.91",
        "data_read_per_join": "14M"
      },
      "used_columns": [
        "사원번호",
        "생년월일",
        "이름",
        "성",
        "성별",
        "입사일자"
      ],
      "attached_condition": "(`tuning`.`사원`.`사원번호` between 10001 and 100000)"
    }
  }
}

EXPLAIN FORMAT = ANALYZE

지금까지 출력된 실행 계획은 모두 예측된 실행 계획에 관한 정보였으나, 실제 측정한 실행 계획 정보를 확인하려면 ANALYZE 키워드를 활용한다. 실제 수행된 소요 시간과 비용을 측정하여 실측 실행 계획과 예측 실행 계획 모두를 확인하려면 EXPLAIN ANALYZE 키워드를 사용한다. MySQL 8.0.18 이상 버전에서 SELECT문 대상으로 수행할 수 있다.

EXPLAIN ANALYZE
SELECT * FROM 사원 WHERE 사원번호 BETWEEN 10001 AND 100000
;

# 실행결과
-> Filter: (`사원`.`사원번호` between 10001 and 100000)  (cost=30046 rows=149490) (actual time=0.0154..114 rows=90000 loops=1)
    -> Index range scan on 사원 using PRIMARY over (10001 <= 사원번호 <= 100000)  (cost=30046 rows=149490) (actual time=0.0142..71.5 rows=90000 loops=1)

프로파일링이란?

프로파일링(profiling)은 SQL 문에서 병목 지점을 찾고자 사용하는 수단을 가리킨다. slow query나 문제가 있다고 의심되는 SQL 문의 원인을 확인할 수 있다.

SQL 프로파일링 실행하기

프로파일링을 실행하기 위해, 먼저 DB에 접속하여 설정값을 확인한다. 기본적으로 비활성화(OFF)되어 있으므로, 직접 활성화를 해야 한다.

SHOW VARIABLES LIKE 'profiling%';

SET 키워드로 프로파일링을 활성화(ON) 상태로 변경하자. 접속한 세션에 한해서만 적용되므로 다른 접속 세션에는 영향을 미치지 않는다.

SET profiling = 'ON';

이제 프로파일링을 수행할 SQL 문을 출력한 후, 프로파일링된 쿼리 목록을 확인해 본다.

# SQL 문 실행
SELECT * FROM 사원 WHERE 사원번호 = 100000;

# 프로파일링된 쿼리 목록 확인
SHOW PROFILES;

아래 이미지의 예시에서는 5번 쿼리가 실제 확인할 대상이다.

만약 특정 쿼리 ID에 대해서만 프로파일링된 상세 내용을 확인하고자 한다면, 쿼리 ID를 입력하여 다음과 같은 문법으로 결과를 확인한다.

일반적으로 Status에서 Duration에 해당하는 값이 높게 나타난다면 문제가 될 소지가 높은 구간으로 볼 수 있다.

프로파일링 결과 실행하기

프로파일링을 통해 출력된 결과에서 각 항목별 의미를 파악하고, 각 항목마다 원인과 해결책을 도출할 수 있다.

• starting: SQL 문 시작
• checking permissions: 필요 권한 확인
• Opening tables: 테이블을 열기
• After opening tables: 테이블을 연 이후
• System lock: 시스템 잠금
• Table lock: 테이블 잠금
• init: 초기화
• optimizing: 최적화
• statistics: 통계
• preparing: 준비
• executing: 실행
• Sending data: 데이터 보내기
• end: 끝
• query end: 질의 끝
• closing tables: 테이블 닫기
• Unlocking tables: 잠금 해제 테이블
• freeing items: 항목 해방
• updating status: 상태 업데이트
• cleaning up: 청소

프로파일링의 추가 정보를 확인하고자 한다면, 다음의 키워드를 SHOW PROFILE 구문 뒤에 작성하여 OS 수준의 확장된 정보를 제공받을 수 있다.

• ALL: 모든 정보를 표시
• BLOCK IO: 블록 입력 및 출력 작업의 횟수를 표시
• CONTEXT SWITCHES: 자발적 및 비자발적인 컨텍스트 스위치 수를 표시
• CPU: 사용자 및 시스템 CPU 사용 기간을 표시
• IPC: 보내고 받은 메시지의 수를 표시
• PAGE FAULTS: 주 페이지 오류 및 부 페이지 오류 수를 표시
• SOURCE: 함수가 발생하는 파일 이름과 행 번호와 함께 소스 코드의 함수 이름을 표시
• SWAPS: 스왑 카운트 표시

ALL 키워드는 단계별로 CPU, Block I/O, 컨텍스트 스위칭, 페이지 폴트 등의 상세 정보를 제공한다.

CPU 키워드는 SQL 수행 단계별로 점유한 CPU 양을 사용자 기준 CPU, 시스템 기준 CPU로 분리해서 상세 정보를 제공한다.

BLOCK IO는 데이터 접근 단위인 블록 수준의 입력, 출력에 관한 정보를 출력한다.

마지막으로, 확장된 프로파일링 출력 항목은 다양한데 각 항목과 그 의미는 아래와 같다.

• QUERY_ID: Query_ID
• SEQ: 동일한 QUERY_ID를 갖는 행의 표시 순서를 보여주는 일련번호
• STATE: 프로파일링 상태
• DURATION: 명령문이 현재 상태에 있었던 시간(초)
• CPU_UESR: 사용자 CPU 사용량(초)
• CPU_SYSTEM: 시스템 CPU 사용량(초)
• CONTEXT_VOLUNTARY: 자발적 컨텍스트 전환의 수
• CONTEXT_INVOLUNTARY: 무의식적인 컨텍스트 전환의 수
• BLOCK_OPS_IN: 블록 입력 조작의 수
• BLOCK_OPS_OUT: 블록 출력 조작의 수
• MESSAGES_SENT: 전송된 통신 수
• MESSAGES_RECEIVED: 수신된 통신 수
• PAGE_FAULTS_MAJOR: 메이저 페이지 폴트의 수
• PAGE_FAULTS_MINOR: 마이너 페이지 폴트의 수
• SWAPS: 스왑 수
• SOURCE_FUNCTION: 프로파일링된 상태로 실행되는 소스 코드의 기능
• SOURCE_FILE: 프로파일링된 상태로 실행되는 소스 코드의 파일
• SOURCE_LINE: 프로파일링된 상태로 실행되는 소스 코드의 행

• 출처: 업무에 바로 쓰는 SQL 튜닝