실행 계획 읽기

2. 핵심 원리

2.1 예상 계획과 실제 실행 통계

실행 계획에는 두 종류가 있습니다. 예상 계획은 쿼리를 실행하지 않고 DB가 세운 계획만 보여 줍니다. 실제 실행 통계는 쿼리를 정말 실행하고 각 단계가 처리한 행 수와 시간을 함께 보여 줍니다.

예상 계획은 안전하고 빠르므로 먼저 봅니다. 실제 실행 통계는 쿼리를 진짜 돌리므로 운영에서 무거운 쿼리나 변경 문장에는 조심해서 씁니다. INSERT, UPDATE, DELETE 에 실제 실행 명령을 쓰면 데이터가 바뀝니다.

2.2 DB별 실행 계획 명령

Oracle 은 두 단계입니다. EXPLAIN PLAN FOR 문장; 으로 계획을 PLAN_TABLE 에 저장하고, SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); 로 보기 좋게 꺼냅니다. SQL*Plus 에서는 SET AUTOTRACE 도 있습니다.

MySQL 은 문장 앞에 EXPLAIN 만 붙이면 표가 나옵니다. EXPLAIN FORMAT=TREE 는 8.0.16 부터 트리 모양으로 보여 주고, EXPLAIN ANALYZE 는 8.0.18 부터 실제로 실행해 시간과 행 수를 붙입니다. MSSQL 은 SSMS 그래픽 계획이 기본이고 텍스트로는 SET SHOWPLAN_TEXT ON 을 씁니다. 이 설정을 켜면 이후 문장은 실행되지 않고 계획만 나옵니다.

2.3 주요 연산 이름

DB마다 이름은 다르지만 접근 방식은 네 가지로 묶입니다.

MySQL type 열은 좋은 순서로 const, eq_ref, ref, range, index, ALL 입니다. index 는 인덱스 전체를 훑는다는 뜻이라 ALL 보다는 낫지만 좁혀서 찾는 것은 아닙니다. Oracle 12c 부터는 TABLE ACCESS BY INDEX ROWID 가 BATCHED 로 표시되기도 합니다.

인덱스로 조건에 맞는 행을 찾고 나서 나머지 컬럼을 읽으러 테이블로 한 번 더 가는 단계가 있습니다. Oracle 은 ROWID 접근, MSSQL 은 Key Lookup 이라 부릅니다. 이 왕복이 많으면 인덱스를 써도 느려집니다.

2.4 계획을 읽는 순서

Oracle 의 표는 들여쓰기가 깊은 연산부터 실행되고, 깊이가 같으면 위에 있는 것부터 실행됩니다. 결과는 위로 올라가며 합쳐집니다. 그래서 표의 맨 아래쪽이 아니라 가장 안쪽에 들여쓴 줄에서 읽기 시작합니다.

MySQL 의 EXPLAIN 표는 id 가 큰 행이 먼저이고 id 가 같으면 위에서 아래입니다. MSSQL 텍스트 계획은 Oracle 처럼 들여쓴 트리라 안쪽부터 읽습니다. 그래픽 계획은 오른쪽에서 왼쪽으로 읽습니다.

2.5 예상 행 수와 실제 행 수

계획에서 가장 먼저 볼 숫자는 예상 행 수와 실제 행 수의 차이입니다. Oracle 의 E-Rows 와 A-Rows, MySQL EXPLAIN ANALYZE 의 rows 와 actual rows, MSSQL 그래픽 계획의 Estimated 와 Actual 이 그 짝입니다.

두 숫자가 크게 다르면 DB가 데이터 분포를 잘못 알고 있다는 뜻입니다. 이때는 인덱스를 더하기 전에 통계 정보를 의심합니다. 통계 갱신은 이 카테고리 04 레슨에서 다룹니다.

2.6 H2 EXPLAIN 으로 확인할 수 있는 것

H2 도 EXPLAIN 을 지원하지만 출력 형식이 실제 DB 와 완전히 다릅니다. H2 는 다시 쓴 SQL 아래에 /* PUBLIC.인덱스이름: 조건 */ 주석을 달고, 인덱스를 못 쓰면 tableScan 이라고 적습니다. 이 레슨은 그 주석에서 세 가지만 읽습니다.