SQL 성능 튜닝이 필요한 이유와 튜닝 방법에 대해서는 이전 포스팅을 참고 - SQL 성능 튜닝이 필요한 이유와 튜닝 방법 - 인덱스

오늘은 실행 계획에 대해 알아보자.

실행 계획

실행 계획이란 SQL이 실행될 때 데이터베이스 옵티마이저가 테이블 접근 순서, 인덱스 사용 여부, 조인 방식 등을 결정한 실행 흐름을 의미한다.
실행 계획을 분석하여 불필요한 Full Scan, 인덱스 미사용, 비효율적인 조인 등이 있는지 확인할 수 있고, 이를 바탕으로 SQL 성능 개선을 할 수 있다.

---

실행 계획을 확인하는데는 두 가지 방법이 있다. EXPLAIN과 EXPLAIN ANALYZE를 사용하는 것이다.

EXPLAIN

간단한 예시 테이블을 만든 후 EXPLAIN으로 실행 계획을 조회하면 아래와 같은 항목을 볼 수 있다. 먼저 필수적으로 이해해야 하는 값들만 먼저 알아본 후, 나머지 값들은 추후에 공부해볼 예정이다.

• type: 조인/조회 방식
• possible keys: 옵티마이저가 사용할 수 있는 인덱스 후보 목록
• key: 실제로 사용된 인덱스 이름
• rows: 이 실행 계획에서 예상되는 읽을 row 수(추정치)
• Extra: 추가정보, Using filesort, Using temporary등이 성능 저하 신호일 수 있음

여기서 주의할 점은 rows, filtered의 값은 정확한 수치가 아닌 추정값이기 때문에 오차가 있을 수 있다는 것이다.

EXPLAIN ANALYZE

EXPLAIN ANALYZE로 실행 계획에 대한 자세한 정보를 조회하고 정보를 해석하는 방법에 대해 알아보자.

EXPLAIN ANALYZE는 쿼리를 실제로 실행하면서 실제로 걸린 시간, 실제 처리된 row 수, 버퍼 읽기 등을 출력한다. 실행 계획 트리를 아래에서 위로 해석하면 쿼리가 어떤 순서로 실행되며 가장 오래 걸린 부분이 어디인지 확인하기 쉽다.

• Table scan on users: users 테이블을 스캔하는데 걸리는 시간과 접근한 데이터 행의 수(rows)를 알 수 있다.
• Filter:(users.department='Sales"): 필터링을 통해 데이터를 추출했고 조건은 users 테이블 department 컬럼이 Sales라는 것을 알 수 있다.

type 의미

1. ALL - Full Table Scan

2. index - Full Index Scan

3. const - Primary Key 또는 Unique Index 단일값 조회

4. range - 범위 조건 인덱스 조회

범위 조건이란 BETWEEN, 부등호(<, >, ≤, ≥), IN, LIKE 등을 뜻한다. 이 방식은 인덱스를 활용하기 때문에 효율적이지만 데이터를 조회하는 범위가 클 경우 성능 저하의 원인이 되기도 한다.

5. ref - 인덱스 컬럼 기반 동등조건(=) 조회

---

참고
업무에 바로 쓰는 SQL 튜닝
비전공자도 이해할 수 있는 MySQL 성능 최적화 입문/실전(SQL 튜닝편)