MySQL 실행계획 분석 (4) - ref , rows , filtered

intro

 

 

---

예시 테이블

 

 

---

ref : 인덱스에서 무엇과 비교하는지 

 

type 컬럼이 ref 이거나 eq_ref 라면 , MySQL 은 인덱스를 사용해 데이터를 찾고 있다는 뜻 입니다. 

이때 , 그래서 무엇과 비교해서 찾았는데에 대한 답이 ref 컬럼에 있습니다. 

 

크게 다음 3가지가 올 수 있습니다. 

• const 
• db_name.table_name.column_name
• func 

 

1. const  

 

 

쿼리에서 직접적으로 상수 값('user9969@example.com') 을 조건으로 주었을 때 나타납니다. 

 

 

2. db_name.table_name.column_name

 

SELECT c.name, la.amount
FROM loan_applications la
JOIN customers c ON la.customer_id = c.id
WHERE la.status = 'PENDING';

 

위의 쿼리의 실행은 아래와 같이 진행됩니다. 

 

1. loan_application (driving)  에서 , status 가 PENDING 인 행을 찾습니다. 

2. 찾은 행의 id (la.customer_id) 와 일치하는 행을 customers(driven) 에서 찾습니다. 

 

 

다음 실행 계획에서 1행은 1번 과정 , 2행은 2번 과정을 나타냅니다. 

 

즉 , 드라이빙 테이블에서 읽은 컬럼 값을 드리븐 테이블의 인덱스와 비교할 때 ref 컬럼은 db_name.table_name.column_name 이며 , 드라이빙 테이블에서 찾은 조인하는 컬럼명을 나타냅니다. 

 

3. func

 

가장 주의 깊게 살펴봐야 하는 컬럼 입니다.

값에 어떠한 가공이 일어난 상태로 인덱스 비교가 수행될 때 나타납니다. 

 

SELECT * FROM customers c
JOIN loan_applications la ON c.id = la.customer_id + 1;

 

위의 쿼리의 실행은 아래와 같이 진행됩니다.

 

1. loan_application (driving)  에서 , 모든 행을 찾습니다.

2. 찾은 행에서 customer_id 에 더하기 1 연산을 합니다. 

3. 더하기 1 연산을 한 id 와 일치하는 customer_id 를 customers(driven) 에서 찾습니다. 

 

실행 계획을 보면 , 2행의 ref 가 func 인것을 알 수 있습니다. 

 

func 행이 보인다면 , 값에 어떠한 가공이 매번 일어나고 있다는 뜻이고 이는 성능을 저하시키고 , 옵티마이저의 최적화를 방해 합니다. 

 

특히 , 의도치 않은 타입 변환도 포함하기에 주의해서 봐야 합니다. 

예를들면 문자열 컬럼과 숫자 컬럼을 조인한다면 MySQL 이 내부적으로 CAST 함수를 씌워버립니다. 

이때도 func 가 나타납니다. 

---

rows : 옵티마이저가 예측한 체크하는 행의 갯수 

 

옵티마이저는 현재 테이블에 있는 레코드의 통계 정보를 바탕으로 실행 계획을 짭니다. 

이때 , rows 는 실행계획 대로 할 때 얼마나 많은 레코드를 읽고 체크해야 하는지 옵티마이저의 예측치를 의미합니다. 

 

 

1. index range scan

 

SELECT * FROM loan_applications WHERE amount > 980000;

 

index range scan 을 할 경우 , 인덱스를 타고 딱 필요한 부분만 골라서 읽기에 , 예측한 조사해야 할 행 수가 적습니다. 

 

 

2. full table scan

SELECT * FROM loan_applications WHERE amount > 100000;

 

 

full table scan 을 할 경우 , 전체 행을 다 열어보아야 한다고 판단하기에 , 예측한 조사해야 할 행 수가 많습니다. 

---

filtered : 옵티마이저가 예측한 반환될 데이터의 비율

 

rows 컬럼을 통해 옵티마이저가 얼마나 많은 데이터를 뒤져야 하는지 알았다면 , filtered 컬럼은 그중 실제 결과물로 반환될 데이터의 비율이 얼마나 될지 예측한 값 입니다. 

 

SELECT * FROM loan_applications 
WHERE amount > 950000            
  AND applied_at >= '2024-01-01';

 

 

인덱스를 타고 521 건의 데이터를 읽어야 한다고 예측합니다. (rows : 521) 

그리고 (AND applied_at >= '2024-01-01') 조건을 통해 33.33 % 가 필터링 될 것이라고 예측합니다. (filtered : 33.33) 

최종적으로 옵티마이저는 (521) * (0.33) = 약 172 건이 반환될 것이라고 생각한 것 입니다. 

 

 

예측치 이므로 실제와는 다릅니다. 

 

이 컬럼은 다음 2가지 역할을 하기에 중요 합니다. 

 

• Driving , Driven 테이블의 근거를 보여줌 
• 복합 인덱스의 필요성을 알려줌

 

1. Driving , Driven 테이블의 근거를 보여줌 

 

조인 성능의 핵심은 누구를 먼저 읽고 (Driving) , 누구를 나중에 읽을 것 (Driven) 인가 ? 입니다. 

옵티마이저는 각 테이블의 rows 와 filter 를 곱해 "조인 횟수를 가정 적게 만드는 쪽" 을 선택 합니다. 

 

 

 

 

 

 

 

 

2. 복합 인덱스의 필요성을 알려줌