mysql 정렬 최적화 (using filesort)

 

--sample data

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    salary DECIMAL(10, 2),
    hire_date DATE);
    
alter table employees add index idx1(department_id);
alter table employees add index idx2(salary);
alter table employees add index idx3(hire_date);

-- 예제 데이터 삽입
INSERT INTO employees (name, department_id, salary, hire_date)
VALUES 
    ('Alice', 1, 5000.00, '2020-01-10'),
    ('Bob', 2, 7000.00, '2019-03-15'),
    ('Charlie', 1, 6000.00, '2021-06-01'),
    ('Diana', 3, 8000.00, '2018-11-20'),
    ('Eve', 2, 5500.00, '2022-08-25'),
    ('Frank', 1, 4500.00, '2020-05-05');

 

- 아래 쿼리 조회 및 플랜확인

SELECT * 
FROM employees 
WHERE department_id = 1 
ORDER BY salary DESC;

 

 

using where;

using filesort;

 

> 파일정렬을 사용하며 디스크 i/o 가 발생

 

> 최적화 방법 

1. 복합인덱스 생성

현재 department_id와 salary에 별도 인덱스가 있지만, 정렬을 최적화하려면 복합 인덱스를 사용하는 것이 효과적입니다.

복합 인덱스를 사용하면 MySQL은 데이터를 정렬된 상태로 검색할 수 있으므로 filesort가 제거됩니다.

 

강제로 인덱스 사용하도록 힌트를 적용해 filesort 제거 하였음.

 SELECT *
 FROM employees
 FORCE INDEX (idx_department_salary)
 WHERE department_id = 1
 ORDER BY salary DESC;