10장: 실행 계획

10.1 통계 정보

10.1.1 테이블 및 인덱스 통계 정보

• 영구적 통계 관리: MySQL 5.6부터 통계 정보를 메모리가 아닌 mysql 데이터베이스의 innodb_index_stats, innodb_table_stats 테이블에 저장하여 서버 재시작 후에도 일관된 실행 계획을 유지할 수 있습니다.
• 수집 정보: 테이블의 전체 레코드 건수와 프라이머리 키 크기, 인덱스별 유니크한 값의 개수(기수성) 등을 수집합니다.
• 갱신 시점: 테이블이 처음 열리거나, 레코드가 대량(약 1/16 이상)으로 변경될 때, 또는 ANALYZE TABLE 명령 시 자동으로 갱신됩니다.
• 설정 제어: STATS_PERSISTENT로 영구 보관 여부를, STATS_AUTO_RECALC로 자동 갱신 여부를 테이블 단위로 설정할 수 있습니다.

10.1.2 히스토그램

• 도입 배경: 인덱스된 칼럼의 기수성 정보만으로는 부족했던 데이터 분포도(데이터가 어디에 몰려 있는지)를 파악하기 위해 8.0에서 도입되었습니다.
• 종류:
  • 싱글톤(Singleton): 칼럼값 개별로 레코드 건수를 관리하며, 주로 유니크한 값의 개수가 적은 경우 사용됩니다.
  • 높이 균형(Equi-Height): 칼럼값의 범위를 균등한 개수로 나누어 관리하며, 유니크한 값이 많은 경우 사용됩니다.
• 활용: 옵티마이저가 특정 범위의 데이터가 얼마나 많은지 정확히 파악하여 더 효율적인 조인 순서를 결정하게 돕습니다.

10.1.3 코스트 모델(Cost Model)

• 작동 방식: 디스크 읽기, CPU 연산, 메모리 작업 등 쿼리 처리에 필요한 각 단위 작업의 비용을 수치화해 최적의 실행 계획을 찾습니다.
• 구성 테이블: server_cost(인덱스 찾기, 비교 등 일반 작업)와 engine_cost(데이터 페이지 읽기 등 엔진 작업) 테이블에서 비용을 관리합니다.
• 세부 항목: 디스크 데이터 페이지 읽기(io_block_read_cost), 메모리 페이지 읽기(memory_block_read_cost), 레코드 평가(row_evaluate_cost) 등이 포함됩니다.
• 조정: 관리자는 하드웨어 성능에 맞춰 이 비용값들을 직접 변경할 수 있지만, 기본값으로도 충분히 잘 최적화되어 있습니다.

10.2 실행 계획 확인

MySQL 서버의 실행 계획은 DESC 또는 EXPLAIN 명령으로 확인할 수 있습니다.

10.2.1 실행 계획 출력 포맷

• 다양한 포맷 지원: MySQL 8.0부터는 FORMAT 옵션을 통해 실행 계획의 표시 방법을 TABLE, JSON, TREE 형태 중에서 선택할 수 있습니다.
• 옵션 통합: 이전 버전의 PARTITIONS나 EXTENDED 옵션은 8.0 버전부터 기본 출력 내용에 통합되어 더 이상 별도로 사용할 필요가 없게 되었습니다.

10.2.2 쿼리의 실행 시간 확인

• EXPLAIN ANALYZE: MySQL 8.0.18부터 도입된 기능으로, 실행 계획만 보여주는 EXPLAIN과 달리 쿼리를 실제로 실행하여 결과를 측정합니다.
• 상세 정보 제공: 단계별로 실제 소요된 시간(actual time), 처리한 레코드 건수(rows), 반복 횟수(loops) 등을 실측치로 보여줍니다.
• 출력 형식: 결과는 항상 들여쓰기로 호출 순서를 나타내는 TREE 포맷으로만 표시됩니다.
• 주의사항: 실제 쿼리를 실행하기 때문에 실행 시간이 너무 오래 걸리는 쿼리는 먼저 일반 EXPLAIN으로 분석하고 어느 정도 튜닝을 거친 후 사용하는 것이 권장됩니다.

10.3 실행 계획 분석

• 10.3.1 id 칼럼: 단위 SELECT 쿼리별 식별자입니다. 조인되는 테이블은 같은 id를 갖지만, 서브쿼리나 UNION은 다른 id가 부여됩니다.
• 10.3.2 select_type 칼럼: 쿼리의 종류(SIMPLE, PRIMARY, DERIVED, UNION 등)를 나타냅니다.
• 10.3.3 table 칼럼: 접근 중인 테이블 이름이며, 임시 테이블은 <derivedN>이나 <unionN,M> 등으로 표시됩니다.
• 10.3.4 partitions 칼럼: 파티션 프루닝을 통해 실제로 접근한 파티션 목록을 보여줍니다.
• 10.3.5 type 칼럼: 테이블 접근 방식입니다. const, eq_ref, ref, range, index, ALL 순으로 성능이 우수합니다.

10.3 실행 계획 분석

• 10.3.1 id 칼럼: 단위 SELECT 쿼리별로 부여되는 식별자입니다. 하나의 SELECT 문장에서 여러 테이블을 조인하면 id가 모두 같지만, 서브쿼리나 UNION이 포함되면 해당 단위 쿼리들은 서로 다른 id를 부여받습니다.
• 10.3.2 select_type 칼럼: 단위 SELECT 쿼리가 어떤 타입인지 나타냅니다. 대표적으로 서브쿼리가 없는 SIMPLE, 가장 바깥쪽의 PRIMARY, FROM 절의 서브쿼리로 만든 임시 테이블인 DERIVED, 그 외 SUBQUERY나 UNION 등이 있습니다.
• 10.3.3 table 칼럼: 실행 계획은 단위 쿼리 기준이 아니라 테이블 기준으로 표시되므로, 어떤 테이블에 접근 중인지 보여줍니다. <derivedN>이나 <unionN,M>처럼 괄호로 둘러싸인 이름은 해당 id를 가진 실행 결과로 만든 임시 테이블을 의미합니다.
• 10.3.4 partitions 칼럼: 쿼리 처리를 위해 파티션 프루닝을 통해 실제로 접근한 파티션 목록을 보여줍니다. 만약 특정 파티션만 읽었다면 그 파티션의 이름이 표시됩니다.
• 10.3.5 type 칼럼: MySQL 서버가 테이블의 레코드를 어떤 방식으로 읽었는지 나타내는 '접근 방법'입니다. 성능이 우수한 순서대로 system, const, eq_ref, ref, range, index, ALL 등이 있으며, ALL은 인덱스를 쓰지 않는 풀 테이블 스캔을 의미합니다.

• 10.3.6 possible_keys 칼럼: 옵티마이저가 최적화를 위해 검토했던 인덱스 후보 목록일 뿐이며, 실제 튜닝에 큰 도움은 되지 않습니다.
• 10.3.7 key 칼럼: 최종 실행 계획에서 실제로 사용된 인덱스를 의미하며, 의도한 인덱스가 잘 쓰이고 있는지 확인하는 가장 중요한 지표입니다.
• 10.3.8 key_len 칼럼: 선택된 인덱스에서 실제 사용된 키의 길이를 바이트 단위로 나타내며, 다중 칼럼 인덱스에서 몇 개의 칼럼까지 사용되었는지 파악할 때 유용합니다.
• 10.3.9 ref 칼럼: 인덱스 비교 작업에 사용된 입력값이 표시되며, 상수일 경우 const, 다른 칼럼일 경우 그 칼럼명이 표시되고 연산이 포함되면 func로 나타납니다.
• 10.3.10 rows 칼럼: 쿼리 처리를 위해 옵티마이저가 읽어야 할 것으로 예측한 레코드 건수이며, 통계 정보를 바탕으로 한 예측치이므로 실제 결과와는 차이가 있을 수 있습니다.
• 10.3.11 filtered 칼럼: 인덱스 조건 외의 나머지 조건에 의해 걸러지고 남은 레코드의 비율(%)을 나타내며, 이 비율이 낮을수록 필터링이 많이 되었음을 의미합니다.
• 10.3.12 Extra 칼럼: 쿼리 처리에 대한 상세 최적화 정보가 담기며, 'Using index'(데이터 파일 읽기 없는 커버링 인덱스), 'Using filesort'(인덱스 없는 정렬), 'Using temporary'(임시 테이블 사용) 등 성능에 직접적인 영향을 미치는 정보들이 표시됩니다.