MariaDB 실행 계획 분석 방법

MariaDB 데이터베이스 시스템에서 쿼리 성능 문제를 해결하고 최적화하는 데 있어 실행 계획 분석은 매우 중요한 과정이다. 실행 계획은 MariaDB가 SQL 쿼리를 실행하기 위해 수행하는 단계별 절차를 상세히 보여주며, 이를 통해 쿼리의 효율성을 파악하고 잠재적인 성능 병목 지점을 식별할 수 있다. 이 보고서는 MariaDB 실행 계획을 생성하고 분석하는 방법, 성능 병목 지점을 진단하는 방법, 그리고 실행 계획 분석을 기반으로 쿼리 성능을 최적화하는 전략을 상세히 설명한다.

 

MariaDB 실행 계획 분석 방법

 

1. 실행 계획 생성 방법

MariaDB에서 쿼리의 실행 계획을 확인하는 가장 기본적인 방법은 EXPLAIN 명령어를 사용하는 것이다. SELECT, UPDATE, 또는 DELETE 문 앞에 EXPLAIN 키워드를 추가하면 MariaDB는 쿼리를 실제로 실행하는 대신 옵티마이저가 선택한 실행 계획에 대한 정보를 출력한다.

 

예를 들어, 다음과 같은 쿼리의 실행 계획을 보려면,

EXPLAIN 
SELECT * FROM employees 
WHERE department_id = 5;

 

MariaDB는 쿼리 실행 단계, 사용될 인덱스, 조인 유형 등 다양한 정보를 테이블 형태로 보여준다.

이 외에도 추가적인 정보를 얻기 위해 다음과 같은 EXPLAIN 명령어의 변형을 사용할 수 있다:

 

• EXPLAIN EXTENDED: 표준 EXPLAIN 출력 외에도 쿼리 옵티마이저가 수행한 추가적인 최적화 정보를 제공한다. SHOW WARNINGS 명령어를 함께 사용하면 옵티마이저가 쿼리를 어떻게 재작성했는지 확인할 수 있다.
   
• EXPLAIN PARTITIONS: 파티셔닝 된 테이블과 관련된 쿼리의 실행 계획을 분석할 때 유용하며, 어떤 파티션이 쿼리에서 사용될지 보여준다.
   
• ANALYZE: EXPLAIN과 유사하지만, 쿼리를 실제로 실행한 후 실행 계획과 함께 실제 실행 통계(예상 행 수와 실제 행 수 비교, 필터링 비율 등)를 제공하여 옵티마이저의 예측 정확도를 평가하는 데 도움을 준다. ANALYZE FORMAT=JSON을 사용하면 더 자세한 정보를 JSON 형태로 확인할 수 있다. 주의할 점은 ANALYZE UPDATE 또는 ANALYZE DELETE는 실제로 데이터를 변경한다는 것이다.
   
• SHOW EXPLAIN FOR CONNECTION connection_id: 특정 연결에서 현재 실행 중인 쿼리의 실행 계획을 보여준다. 이는 장시간 실행되는 쿼리의 진행 상황이나 실행 방식을 실시간으로 확인하는 데 유용하다.

 

2. 실행 계획 분석 컬럼 분석

실행 계획 확인

 

EXPLAIN 명령어 실행 결과는 여러 컬럼으로 구성되어 있으며, 각 컬럼은 쿼리 실행 계획의 특정 측면에 대한 정보를 제공한다. 주요 컬럼과 그 의미는 다음과 같다.

• id: 쿼리 내의 각 SELECT 문에 대한 순차적인 식별 번호를 나타낸다. 주로 서브쿼리나 조인이 포함된 복잡한 쿼리에서 연산 순서를 파악하는 데 유용하다. 번호가 작을수록 먼저 실행되는 단계를 의미하며, 동일한 id 값을 갖는 행은 서로 관련되어 있음을 나타낸다.
   
• select_type: 각 SELECT 쿼리의 유형을 나타낸다. SIMPLE (서브쿼리나 UNION이 없는 단순 SELECT), PRIMARY (가장 바깥쪽 SELECT), SUBQUERY (서브쿼리), DERIVED (FROM 절의 서브쿼리), UNION, UNION RESULT 등 다양한 값이 존재하며, 쿼리의 복잡성을 이해하는 데 도움을 준다.
   
• table: 해당 단계에서 접근하는 테이블의 이름 또는 별칭을 나타낸다. 서브쿼리에 대한 임시 테이블은 <subquery#> 형태로 표시될 수 있다.
   
• type: MariaDB가 테이블에서 행을 찾는 접근 방식 또는 조인 유형을 나타낸다. 성능에 큰 영향을 미치는 컬럼으로, 주요 값과 그 의미는 다음과 같다.

값	설명	성능 영향	잠재적 최적화
ALL	전체 테이블 스캔. 테이블의 모든 행을 읽어 일치하는 행을 찾는다.	매우 비효율적 (특히 큰 테이블의 경우)	적절한 인덱스 추가
index	인덱스 전체 스캔. 테이블 데이터를 읽지 않고 인덱스만 전체적으로 스캔한다.	ALL보다는 낫지만, 인덱스가 크면 비효율적일 수 있음	더 효율적인 인덱스 사용 또는 쿼리 개선
range	인덱스를 사용하여 특정 범위 내의 행을 검색한다.	비교적 효율적	범위 조건 개선 또는 인덱스 조정
ref	비고유 인덱스 또는 고유 인덱스의 접두사를 사용하여 일치하는 행을 찾는다.	효율적 (인덱스 접두사가 많은 행과 일치하지 않는 경우)	인덱스 컬럼 순서 조정
eq_ref	고유 인덱스를 사용하여 정확히 하나의 행을 찾는다.	매우 효율적 (최상의 조인 유형 중 하나)	조인 조건 및 인덱스 확인
const, system	테이블에 0 또는 1개의 일치하는 행이 있다.	매우 효율적	-
NULL	MariaDB가 조인을 최적화하여 테이블 접근이 필요하지 않다.	매우 효율적	-
index_merge	여러 인덱스를 사용하여 결과를 병합한다.	상황에 따라 효율적일 수 있지만, 과도한 사용은 성능 저하를 유발할 수 있음	쿼리 또는 인덱스 재설계
index_subquery, unique_subquery	서브쿼리를 인덱스 조회로 변환하여 사용한다.	효율적	-
ref_or_null	ref와 유사하지만, NULL 값도 검색한다.	상황에 따라 효율적	-
fulltext	전문 검색 인덱스를 사용한다.	전문 검색에 효율적	-
filter	로우 ID 필터링 최적화를 사용하여 두 번째 인덱스를 사용한다.	상황에 따라 효율적	-

• possible_keys: 쿼리 실행에 사용할 수 있는 인덱스 목록을 보여준다.
   
• key: 실제로 쿼리 실행에 사용된 인덱스를 나타낸다. NULL 값은 인덱스가 사용되지 않았음을 의미한다. 일반적으로 possible_keys 컬럼에 나타난 인덱스 중 하나이다.
   
• key_len: 사용된 인덱스의 길이를 바이트 단위로 나타낸다. 복합 인덱스의 경우, 쿼리에서 실제로 사용된 인덱스 컬럼의 길이를 파악하는 데 유용하다.
   
• ref: 인덱스 키 값을 비교하는 데 사용된 컬럼 또는 상수를 나타낸다. 상수 값이 사용된 경우 const로 표시될 수 있다.
   
• rows: MariaDB가 쿼리를 실행하기 위해 각 키 조회마다 예상하는 처리 행 수를 나타낸다. 이 값이 클수록 쿼리 성능이 저하될 가능성이 높다. 일반적으로 값이 낮을수록 효율적인 쿼리 실행을 의미한다.
   
• filtered: (ANALYZE 결과에서) 테이블 조건에 의해 필터링된 예상 행의 백분율을 나타낸다. 값이 100에 가까울수록 필터링 효율성이 높다는 것을 의미한다.
   
• Extra: 쿼리 실행에 대한 추가 정보를 제공한다. 성능 개선에 대한 힌트를 얻을 수 있는 중요한 칼럼이며, 주요 값과 그 의미는 다음과 같다.   
   

값	설명	성능 영향	잠재적 조치
Using index	필요한 정보가 모두 인덱스에 포함되어 테이블 데이터에 접근할 필요가 없다.	매우 효율적	-
Using where	테이블에서 행을 읽은 후 WHERE 절을 적용하여 필터링한다.	인덱스 사용 없이 전체 테이블 스캔 후 필터링하는 경우 비효율적일 수 있음	인덱스 추가 또는 쿼리 재작성
Using temporary	쿼리 결과를 저장하기 위해 임시 테이블을 생성한다 (주로 GROUP BY 또는 ORDER BY 절에서 발생).	성능 저하의 원인이 될 수 있음	인덱스 추가 또는 쿼리 재작성
Using filesort	ORDER BY 절을 만족시키기 위해 별도의 정렬 작업을 수행한다.	성능 저하의 원인이 될 수 있음	정렬에 사용되는 컬럼에 인덱스 추가
Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access)	조인 버퍼를 사용하여 조인을 수행한다.	작은 테이블의 경우 괜찮지만, 큰 테이블에는 비효율적일 수 있음	조인 컬럼에 인덱스 추가
No tables used	쿼리가 테이블에 접근하지 않고도 결과를 반환할 수 있다.	매우 효율적	-
Impossible WHERE	WHERE 절이 항상 거짓이다.	-	쿼리 수정
No matching row in const table	const 조인에서 일치하는 행이 없다.	-	조인 조건 확인
Using index condition	인덱스를 사용하여 WHERE 절의 일부를 평가할 수 있다.	효율적	-

 

3. 실행 계획 분석을 통한 성능 병목 지점 진단

실행 계획 결과를 주의 깊게 분석하면 쿼리 성능을 저해하는 다양한 병목 지점을 식별할 수 있다.

• 비효율적인 접근 방식: type 컬럼의 값이 ALL 또는 index인 경우, 특히 큰 테이블에서 이러한 값이 나타나면 전체 테이블 스캔 또는 전체 인덱스 스캔이 수행되고 있다는 의미이므로 성능 병목 지점일 가능성이 매우 높다 . 이는 데이터베이스가 쿼리 조건에 맞는 행을 찾기 위해 테이블이나 인덱스의 모든 항목을 읽어야 하기 때문에 느리고 리소스 집약적인 작업이다.  
   
• 인덱스 누락 및 비효율적인 활용: key 컬럼이 NULL이면 쿼리에서 인덱스를 전혀 사용하지 않았다는 것을 의미하며 , possible_keys 컬럼에 인덱스 후보가 있음에도 불구하고 key 컬럼이 NULL이거나 예상과 다른 인덱스가 사용되었다면 적절한 인덱스가 없거나 비효율적으로 사용되고 있을 가능성이 높다 . 이는 옵티마이저가 쿼리 조건에 맞는 인덱스를 찾지 못해 전체 스캔과 같은 비효율적인 방법을 선택하게 되는 원인이 된다.  
   
• 높은 예상 처리 행 수: rows 컬럼의 값이 높으면 MariaDB가 쿼리를 만족시키기 위해 많은 수의 행을 검사할 것으로 예상한다는 것을 나타낸다 . 이는 최종 결과 집합이 훨씬 작을 것으로 예상되더라도 마찬가지이며, 쿼리 성능 저하의 주요 원인이 될 수 있다. 인덱스를 사용하더라도 (key가 NULL이 아니더라도) rows 값이 높다면 인덱스의 선택성이 낮거나 쿼리 조건과 완전히 일치하지 않아 여전히 많은 행을 검사해야 할 수 있다.  
   
• 비효율적인 조인 방식: type 컬럼은 조인 연산의 효율성을 반영하기도 한다. 조인 쿼리에서 type 값이 ALL 또는 index로 나타나면 조인이 효율적으로 인덱스를 활용하지 못하고 있다는 것을 의미하며, 이는 성능 저하로 이어진다 . 효율적인 조인 유형 (ref, eq_ref)은 인덱스를 사용하여 테이블 간에 빠르게 일치하는 행을 찾는 반면, 비효율적인 유형은 전체 스캔을 수행해야 할 수 있다.  
   
• 임시 테이블 생성 및 파일 정렬: Extra 컬럼에 "Using temporary" 또는 "Using filesort"가 나타나면 성능 병목 지점일 가능성이 높다 . 임시 테이블 생성은 추가적인 디스크 I/O 및 메모리 사용을 유발하며, 파일 정렬은 특히 큰 데이터셋에서 CPU 집약적인 작업이다. 이러한 작업은 종종 더 나은 인덱싱이나 쿼리 재작성을 통해 피하거나 최적화할 수 있다.

 

4. 실행 계획 기반 MariaDB 쿼리 성능 최적화 전략

• 효율적인 인덱스 설계 및 관리: 쿼리의 WHERE 절과 조인 조건에 자주 사용되는 컬럼에 인덱스를 생성하는 것은 가장 기본적인 최적화 방법이다 . 여러 컬럼이 함께 자주 사용되는 경우에는 복합 인덱스를 생성하는 것이 단일 컬럼 인덱스보다 더 효율적일 수 있다 . 복합 인덱스를 생성할 때는 쿼리에서 사용되는 컬럼 순서를 고려해야 한다 . 다만, 인덱스가 너무 많으면 데이터 삽입, 수정, 삭제 작업의 성능을 저하시킬 수 있으므로 필요한 인덱스만 유지 관리하는 것이 중요하다 . EXPLAIN 명령어를 정기적으로 사용하여 인덱스 활용도를 점검하고, 불필요한 인덱스는 제거하는 것이 좋다 . 데이터 양과 작업 부하가 변화함에 따라 실행 계획을 지속적으로 모니터링하고 분석하여 인덱스를 개선해 나가야 한다 .  
   
• 쿼리 재작성: 실행 계획에서 비효율적인 부분이 발견되면 쿼리를 재작성하여 성능을 향상시킬 수 있다 . 예를 들어, 서브쿼리가 비효율적인 경우 이를 JOIN 구문으로 변경하거나, 복잡한 쿼리를 더 간단한 형태로 분해하는 것이 도움이 될 수 있다 . 불필요한 컬럼을 조회하지 않고 필요한 컬럼만 선택하는 것도 데이터 전송량과 처리량을 줄여 성능을 개선하는 방법이다 . JOIN 구문을 사용할 때 내부 조인과 외부 조인을 필요한 경우에만 사용하고, 조인 순서를 고려하는 것도 성능에 영향을 미칠 수 있다 .  
   
• 파티셔닝: 매우 큰 테이블의 경우 파티셔닝을 통해 테이블을 더 작고 관리하기 쉬운 조각으로 나누어 쿼리 성능을 향상시킬 수 있다 . EXPLAIN PARTITIONS 명령어를 사용하여 파티셔닝된 테이블에 대한 쿼리의 실행 계획을 분석하고, 어떤 파티션이 사용되는지 확인할 수 있다 .  
   
• 쿼리 캐싱: 자주 실행되는 동일한 쿼리의 결과를 캐싱하여 데이터베이스 부하를 줄이고 응답 시간을 개선할 수 있다 . MariaDB는 쿼리 계획 캐시 (Query Plan Cache, QPC)와 같은 기능을 제공하여 쿼리 성능을 최적화할 수 있도록 지원한다 .  
   
• 데이터 모델링 및 스키마 설계: 쿼리 성능은 쿼리 자체뿐만 아니라 데이터 모델링 및 스키마 설계에도 큰 영향을 받는다 . 효율적인 데이터 모델은 더 빠르고 효율적인 쿼리를 가능하게 한다 .  
   
• MariaDB 버전 업데이트: MariaDB는 각 버전마다 쿼리 옵티마이저를 개선하고 있다 . 새로운 버전에서는 더 나은 실행 계획을 생성하거나 새로운 최적화 전략을 도입할 수 있으므로, MariaDB 버전을 최신 상태로 유지하는 것이 전반적인 데이터베이스 성능 향상에 도움이 될 수 있다. 다만, 버전 업데이트 후에는 중요한 쿼리의 실행 계획을 다시 확인하여 변경된 사항을 파악하는 것이 좋다 . 옵티마이저 힌트를 사용하여 특정 쿼리에 대한 실행 계획을 강제할 수도 있지만 , MariaDB는 옵티마이저 자체를 개선하여 이러한 힌트의 필요성을 줄여나가고 있다 .

 

5. MariaDB 실행 계획 분석 도구 활용 

• MariaDB 내장 도구: EXPLAIN, ANALYZE, SHOW EXPLAIN 명령어는 MariaDB에서 제공하는 기본적인 실행 계획 분석 도구이다. 이러한 명령어들의 다양한 옵션과 출력 결과를 숙지하는 것이 중요하다.   
   
• 온라인 EXPLAIN 분석기: MariaDB Knowledge Base에서는 EXPLAIN 및 EXPLAIN EXTENDED 결과를 다른 사람과 공유할 수 있는 온라인 EXPLAIN 분석기를 제공한다 .  
   
• GUI 기반 데이터베이스 관리 도구: DBeaver, dbForge Studio for MySQL과 같은 GUI 기반 도구들은 실행 계획을 시각적으로 표현하여 분석을 더 쉽게 만들어준다 . 예를 들어, DBeaver에서는 Ctrl+Shift+E 단축키나 컨텍스트 메뉴를 통해 실행 계획을 확인할 수 있으며, 유료 버전에서는 고급 그래프 시각화 기능을 제공한다 . dbForge Studio for MySQL은 MariaDB에서도 작동하며, 쿼리 프로파일링 기능을 통해 더 자세한 성능 분석을 지원한다 .  
   
• 기타 시각화 도구: Hackathon-2022-TiVP/execution-plan-visualizer와 같은 오픈소스 프로젝트는 MariaDB를 포함한 다양한 데이터베이스의 실행 계획을 시각적으로 보여주는 도구를 제공한다 . PawSQL Plan Visualizer (PPV)는 MySQL을 지원하지만, 제공된 정보만으로는 MariaDB 지원 여부를 확인하기 어렵다 . SILOTA는 MariaDB 실행 계획 분석 또는 시각화 기능을 직접적으로 제공하는지는 명확하지 않지만, SQL 편집 및 시각화 기능을 제공하는 클라우드 기반 도구이다 .

 

6. 실제 쿼리 실행 계획 분석 예시

다음은 실제 쿼리의 실행 계획을 분석하고 최적화하는 예시이다.

 

예시 1: 매출 합계 쿼리

다음 쿼리는 특정 기간 동안의 총 매출을 계산하는 쿼리이다.

SELECT SUM(order_items.price * order_items.quantity) AS revenue
FROM orders
JOIN order_items ON orders.order_id = order_items.order_id
WHERE orders.order_date BETWEEN ‘2023-01-01’ AND ‘2023-02-28’;

 

이 쿼리의 초기 실행 계획을 분석했을 때, orders 테이블의 order_date 컬럼에 인덱스가 없어 전체 테이블 스캔이 발생하고 있음을 확인했다고 가정해 보자. 이를 해결하기 위해 orders 테이블의 order_date 컬럼에 인덱스를 추가한다.

CREATE INDEX idx_order_date ON orders(order_date);

 

인덱스 추가 후 실행 계획을 다시 분석하면 orders 테이블 접근 방식이 전체 테이블 스캔에서 인덱스 범위 스캔 (range)으로 변경되어 쿼리 성능이 향상될 것이다.

 

예시 2: 특정 부서 직원 조회 쿼리

다음 쿼리는 특정 department_id를 가진 직원을 조회하는 쿼리이다.

SELECT * FROM employees WHERE department_id = 5;

 

 

초기 실행 계획에서 employees 테이블의 type이 ALL이고 key가 NULL인 것을 확인했다면, 이는 인덱스가 사용되지 않고 전체 테이블 스캔이 수행되고 있다는 의미이다. employees 테이블의 department_id 컬럼에 인덱스를 추가하여 이 문제를 해결할 수 있다.  

CREATE INDEX idx_department_id ON employees(department_id);

 

인덱스를 추가한 후 실행 계획을 확인하면 type이 ref로 변경되고 key 컬럼에 idx_department_id가 표시되어 인덱스가 효율적으로 사용되고 있으며, 예상 처리 행 수도 크게 감소한 것을 확인할 수 있다.

 

예시 3: ANALYZE 활용

다음 ANALYZE 명령어는 옵티마이저의 예측과 실제 실행 성능을 비교하는 방법을 보여준다.

ANALYZE 
SELECT * FROM tbl1
WHERE key1 BETWEEN 10 AND 200 AND col1 LIKE 'foo%';

 

ANALYZE 결과에서 rows (예상 행 수)와 r_rows (실제 읽은 행 수), filtered (예상 필터링 비율)와 r_filtered (실제 필터링 비율)을 비교하여 옵티마이저의 예측 정확도를 평가하고 잠재적인 통계 문제나 인덱스 개선 필요성을 파악할 수 있다.

칼럼	초기 EXPLAIN 출력	인덱스 추가 후 EXPLAIN 출력	개선 사항
type	ALL	ref	전체 테이블 스캔에서 인덱스 기반 조회로 변경됨
key	NULL	idx_department_id	department_id 인덱스가 사용됨
rows	1,000,000	100	예상 처리 행 수가 크게 감소함
Extra	Using where	Using where	Sheets로 내보내기

 

7. 결론 및 성능 향상을 위한 추가 고려 사항

MariaDB 쿼리 성능을 최적화하기 위해서는 실행 계획 분석이 필수적이다. EXPLAIN 명령어를 통해 얻은 실행 계획 정보를 바탕으로 비효율적인 접근 방식, 누락된 인덱스, 높은 예상 처리 행 수, 비효율적인 조인 방식, 임시 테이블 생성 및 파일 정렬 발생 여부 등을 진단하고, 이에 따른 최적화 전략(인덱스 설계 및 관리, 쿼리 재작성, 파티셔닝, 쿼리 캐싱 등)을 수립해야 한다.

 

실행 계획 분석은 일회성 작업이 아니라 지속적으로 수행해야 하는 과정이다. 데이터베이스 작업 부하와 데이터 분포는 시간에 따라 변할 수 있으므로, 정기적으로 쿼리 성능을 모니터링하고 실행 계획을 분석하여 성능 저하를 사전에 방지하고 최적의 상태를 유지해야 한다. 느린 쿼리를 식별하기 위해 슬로우 쿼리 로그를 활성화하고 , EXPLAIN 명령어를 사용하여 해당 쿼리의 실행 계획을 분석하는 것이 좋은 방법이다. 또한, 데이터 모델링 및 스키마 설계는 쿼리 성능에 큰 영향을 미치므로, 데이터 모델을 신중하게 설계하고 필요에 따라 정규화 또는 비정규화를 고려해야 한다. MariaDB 버전을 업데이트할 때는 옵티마이저의 변경 사항을 확인하고, 중요한 쿼리에 대해서는 실행 계획을 다시 분석하여 잠재적인 성능 변화에 대비하는 것이 중요하다.