[MySql] 성능 개선에 관하여_해결 해 보자_1_explain

Explain

Sql문 성능 개선에 빠질수 없는 것 들 중 하나인 Explain 쿼리를 한번 확인해 볼려고 한다.

컬럼 과 설명

id	쿼리의 각 부분을 식별하는 고유 번호입니다. 서브쿼리나 UNION이 있을 경우 여러 id가 있을 수 있으며, 값이 클수록 실행 순서가 나중입니다.
select_type	쿼리의 유형을 나타냅니다. 주요 값은 다음과 같습니다: SIMPLE 단순 SELECT 쿼리 (서브쿼리 없음) PRIMARY 쿼리의 가장 바깥쪽 SELECT 쿼리 UNION UNION 연산자에서 두 번째 이상의 SELECT 쿼리 SUBQUERY 서브쿼리의 SELECT 쿼리 DERIVED 서브쿼리의 결과를 임시 테이블로 사용하는 쿼리
table	쿼리에서 접근하는 테이블의 이름입니다.
partitions	파티셔닝된 테이블을 사용하는 경우, 참조하는 파티션의 이름입니다.
type	테이블 접근 방법을 나타내며, 효율성을 나타냅니다. 효율적인 순서로는 const: 상수 값으로 인식 (매우 효율적) eq_ref: 각 행이 단일 행과만 일치 (효율적) ref: 인덱스의 특정 값과 비교 range: 인덱스를 범위 검색 index: 인덱스를 사용하지만 전체 인덱스를 스캔 ALL: 테이블 전체를 스캔 (비효율적)
possible_keys	쿼리에서 사용할 수 있는 인덱스 목록입니다.
key	실제로 쿼리 실행 시 사용된 인덱스입니다.
key_len	사용된 인덱스의 길이입니다. 인덱스의 일부분만 사용하는 경우 이 값이 감소할 수 있습니다.
ref	인덱스의 어떤 열이 참조되는지를 나타냅니다.
rows	MySQL이 예상하는 결과 행의 수입니다. 이 값이 클수록 성능 저하의 가능성이 있습니다.
filtered	쿼리에서 필터링된 행의 비율입니다. 이 값은 전체 행 수 중 필터링 후의 비율을 나타냅니다.
Extra	쿼리 실행에 대한 추가 정보를 제공합니다. 성능 순서로는 Using index: 인덱스만으로 데이터를 조회함. (매우 성능 좋음) Using where: WHERE 절로 추가 필터링이 이루어짐. Using index condition: 인덱스를 사용하여 조건을 필터링함. Using filesort: 정렬을 위해 파일 정렬을 사용함. Using temporary: 임시 테이블을 사용함.  (매우 성능 나쁨) Distinct: 중복된 결과를 제거하기 위해 DISTINCT를 사용함. (예외. 상황에 따라 다름)

3. 실제 예제 분석

1. 기본 조회

EXPLAIN 
SELECT * 
FROM employees 
WHERE department_id = 10;

 

결과는 다음과 같을 수 있습니다

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	employees	range	department_idx	department_idx	4	NULL	100	100.00	Using where

• type이 range로 나타나면 인덱스를 사용해 범위 검색을 수행함을 의미합니다.
• key가 department_idx로 표시되면 이 인덱스가 실제로 사용됨을 의미합니다.
• Extra에 Using where가 표시되면 WHERE 절에서 추가적인 필터링이 이루어짐을 의미합니다.

이 정보를 바탕으로 쿼리를 최적화하거나 인덱스를 조정하여 성능을 개선할 수 있습니다.

 

2. 인덱스가 없을 때 WHERE 절

EXPLAIN 
SELECT * 
FROM employees 
WHERE department = 'Sales';

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	employees	ALL	NULL	NULL	NULL	NULL	5000	2.00	Using where

 

분석:

• type
  • ALL — 인덱스가 없기 때문에 전체 테이블을 스캔합니다. 이는 비효율적일 수 있으며, 데이터 양이 많을 경우 성능 저하를 초래할 수 있습니다.
• key
  • NULL — 인덱스가 사용되지 않음을 의미합니다.
• Extra
  • Using where — WHERE 절을 사용하여 결과를 필터링합니다.

2. 4개 테이블을 조인할 때

EXPLAIN 
SELECT 	e.name, 
		d.department_name, 
        p.project_name, 
        t.task_name 
FROM	employees e 
JOIN 	departments d 
	ON e.department_id = d.id 
JOIN projects p 
	ON e.project_id = p.id 
JOIN tasks t ON p.task_id = t.id;

결과 예제

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	e	ALL	department_idx	NULL	NULL	NULL	5000	100.00
1	SIMPLE	d	eq_ref	PRIMARY	PRIMARY	4	e.department_id	10	100.00
1	SIMPLE	p	eq_ref	PRIMARY	PRIMARY	4	e.project_id	20	100.00
1	SIMPLE	t	eq_ref	PRIMARY	PRIMARY	4	p.task_id	100	100.00

분석:

• type:
  • ALL (employees) — employees 테이블을 전체 스캔합니다.
  • eq_ref (departments, projects, tasks) — 인덱스를 사용하여 정확한 일치를 찾아 조회합니다. eq_ref는 인덱스를 통해 단일 행을 참조합니다.
• key:
  • NULL (employees) — 인덱스가 사용되지 않음.
  • PRIMARY (departments, projects, tasks) — 각 테이블의 기본 키가 사용됨.
• rows:
  • 5000 (employees) — 전체 행 수.
  • 10, 20, 100 — 조인된 테이블에서 각 단계별로 예측된 행 수.
• Extra: — 이 경우 특별한 추가 정보는 없음.

3. 서브쿼리를 사용했을 때

쿼리 예제:

EXPLAIN 
SELECT	name 
FROM 	employees 
WHERE 	department_id IN (SELECT id 
                            FROM departments 
                            WHERE location = 'New York');

결과 예제:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	employees	ref	department_idx	department_idx	4	subquery	1000	100.00	Using where
2	SUBQUERY	departments	index	location_idx	location_idx	4	NULL	10	100.00	Using where

 

분석:

• select_type:
  • PRIMARY (outer query) — 외부 쿼리.
  • SUBQUERY (inner query) — 서브쿼리.
• type:
  • ref (employees) — 인덱스를 사용하여 결과를 참조합니다.
  • index (departments) — 인덱스를 사용하여 전체 인덱스를 스캔합니다.
• key:
  • department_idx (employees) — 인덱스가 사용됨.
  • location_idx (departments) — 인덱스가 사용됨.
• rows:
  • 1000 (employees) — 외부 쿼리에서 예측된 행 수.
  • 10 (departments) — 서브쿼리에서 예측된 행 수.
• Extra:
  • Using where — WHERE 절로 추가 필터링이 이루어짐.