Database Explain을 통한 최적화 분석

Database Explain을 통한 최적화

MySQL 쿼리 최적화는 데이터베이스 성능을 향상시키기 위해 매우 중요한 작업입니다. 쿼리 최적화의 주요 기법 중 일부는 다음과 같습니다:

1. 인덱스 사용 최적화: 자주 사용하는 열에 인덱스를 추가하여 검색 속도를 높이는 방법.
2. 조인(JOIN) 최적화: 적절한 조인 방식을 선택하거나, 불필요한 조인을 피함으로써 성능을 개선.
3. 쿼리 재작성: 복잡한 서브쿼리를 단순화하거나, 더 효율적인 구조로 변경.
4. 쿼리 캐싱: 동일한 쿼리의 반복 실행을 캐싱하여 성능 향상.
5. 실행 계획(Execution Plan) 분석: EXPLAIN 명령어를 통해 쿼리 실행 계획을 분석하고, 비효율적인 부분을 찾아 최적화.

---

EXPLAIN 사용법

EXPLAIN 명령어는 MySQL에서 쿼리의 실행 계획을 분석하는 데 사용됩니다. 이를 통해 쿼리가 어떻게 수행될 것인지, 각 테이블이 어떻게 접근될 것인지를 파악할 수 있습니다. 이를 통해 쿼리의 성능 문제를 찾아내고 최적화할 수 있습니다.

EXPLAIN 사용법

EXPLAIN 명령어는 쿼리 앞에 추가하여 사용합니다. 예를 들어:

EXPLAIN SELECT product_id, COUNT(*) AS total_sales
FROM sales
GROUP BY product_id
ORDER BY total_sales DESC
LIMIT 10;

이 명령어는 쿼리가 어떻게 실행될지를 설명해주는 정보를 반환합니다. 반환된 결과는 여러 열(columns)로 구성된 테이블 형태로 제공되며, 각 열은 쿼리의 실행 계획에 대한 정보를 포함하고 있습니다.

EXPLAIN 결과의 각 필드 설명

EXPLAIN 명령어를 실행했을 때 반환되는 주요 필드는 다음과 같습니다.

1. id:
   • 쿼리의 각 부분을 구별하는 식별자입니다.
   • id가 클수록 먼저 실행됩니다. id가 동일하다면 위에서부터 순차적으로 실행됩니다.
2. select_type:
   • 쿼리의 유형을 나타냅니다.
   • 주요 유형은 다음과 같습니다:
     • SIMPLE: 서브쿼리가 없는 단순 쿼리.
     • PRIMARY: 가장 외곽의 쿼리.
     • SUBQUERY: SELECT문 안에 포함된 서브쿼리.
     • DERIVED: 서브쿼리가 FROM 절에 사용된 경우, 즉 파생 테이블을 의미합니다.
     • UNION: UNION을 사용하는 경우 각 부분을 구분합니다.
3. table:
   • 쿼리에서 참조되는 테이블의 이름입니다.
4. partitions:
   • 사용된 파티션을 보여줍니다. 파티션이 없는 경우 NULL로 표시됩니다.
5. type:
   • MySQL이 테이블에 접근하는 방식(접근 방법)을 나타냅니다. 성능 최적화에서 중요한 요소입니다.
   • 접근 방식의 효율성 순위는 다음과 같습니다 (위에서 아래로 갈수록 좋지 않음):
     • system: 테이블에 하나의 행만 있을 때.
     • const: 상수값처럼 하나의 행만 조회할 때.
     • eq_ref: 조인 시, 인덱스의 모든 부분이 사용되는 경우.
     • ref: 인덱스를 사용하지만 정확히 하나의 값은 아닌 경우.
     • range: 인덱스를 사용하여 범위를 조회하는 경우 (BETWEEN, <, >).
     • index: 인덱스를 전체 스캔하는 경우.
     • ALL: 테이블을 전체 스캔하는 경우로, 성능이 가장 나쁩니다.
6. possible_keys:
   • 쿼리에서 사용할 수 있는 인덱스를 나타냅니다. 쿼리를 최적화하기 위해 이 열에 표시된 인덱스를 참고할 수 있습니다.
7. key:
   • 실제로 사용된 인덱스입니다. 이 값이 NULL이면 인덱스를 사용하지 않았다는 뜻입니다.
8. key_len:
   • 사용된 인덱스의 길이를 나타냅니다. 길이는 바이트 단위로 표시되며, MySQL이 얼마나 많은 부분을 인덱스에서 사용했는지를 보여줍니다.
9. ref:
   • 조인 조건에서 어떤 컬럼이나 상수를 사용하여 검색했는지 보여줍니다. 예를 들어, 어떤 테이블의 특정 컬럼과 다른 테이블의 컬럼을 매칭했을 때 그 정보가 들어갑니다.
10. rows:
    • MySQL이 실행 시 예상하는 결과 행의 개수를 나타냅니다. 쿼리가 얼마나 많은 행을 스캔할지 가늠할 수 있는 중요한 지표입니다.
11. filtered:
    • (rows와 함께 사용) 필터링된 결과의 비율을 퍼센트로 나타냅니다. 값이 높을수록 많은 결과가 필터링되지 않고 통과하는 것을 의미합니다.
12. Extra:
    • 쿼리 최적화에 대한 추가적인 정보를 나타냅니다. 중요한 정보로는 다음이 있습니다:
      • Using index: 인덱스만으로 데이터를 처리했으며, 테이블에 접근할 필요가 없다는 것을 의미합니다. 이는 성능에 매우 좋은 신호입니다.
      • Using where: 조건절을 사용해 결과를 필터링하고 있음을 의미합니다.
      • Using temporary: 결과를 저장하기 위해 임시 테이블을 사용합니다. 이는 성능에 부정적 영향을 줄 수 있습니다.
      • Using filesort: 결과를 정렬하기 위해 파일 정렬을 수행했음을 의미하며, 성능상 좋지 않은 신호입니다.

예시 결과 해석

아래는 EXPLAIN 결과의 예시와 해석입니다:

id select_type table type possible_keys key key_len ref rows Extra

1

SIMPLE

sales

ref

idx_product_id

idx_product_id

4

const

100

Using where; Using index

• id: 1로 하나의 단순 쿼리임을 의미합니다.
• select_type: SIMPLE로 서브쿼리가 없는 단순 쿼리임을 나타냅니다.
• table: sales 테이블을 조회하고 있음을 보여줍니다.
• type: ref로, 인덱스를 사용하여 특정 값을 찾고 있음을 의미합니다.
• possible_keys: idx_product_id 인덱스를 사용할 수 있음을 나타냅니다.
• key: 실제 사용된 인덱스는 idx_product_id입니다.
• key_len: 4로 인덱스의 길이가 4바이트임을 의미합니다.
• ref: const로, 특정 상수 값을 참조하고 있음을 나타냅니다.
• rows: 약 100개의 행을 스캔할 것으로 예상됩니다.
• Extra: Using where; Using index는 조건 필터링과 인덱스를 사용하여 효율적으로 쿼리를 실행하고 있다는 의미입니다.

---

최적화 예제

다음은 복잡하고 성능이 좋지 않은 쿼리를 어떻게 최적화할 수 있는지 설명하는 예시입니다:

예제 쿼리 (최적화 전)

SELECT *
FROM orders
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE status = 'inactive'
);

위의 쿼리는 서브쿼리를 사용하여 'inactive' 상태의 고객을 검색한 후, 해당 고객의 주문 내역을 가져오고 있습니다. 이 방식은 성능이 저하될 수 있는데, 특히 customers 테이블에 레코드가 많을 경우 서브쿼리로 인해 쿼리가 느려집니다.

최적화 과정

1. 인덱스 추가:
   • customers 테이블의 status 컬럼에 인덱스를 추가합니다. 이렇게 하면 'inactive' 고객을 검색하는 속도가 빨라집니다.

CREATE INDEX idx_status ON customers (status);

1. JOIN을 이용한 쿼리 재작성: 서브쿼리를 사용하지 않고 JOIN을 사용하여 쿼리를 재작성하면 MySQL이 더 효율적으로 실행할 수 있습니다.

SELECT orders.*
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
WHERE customers.status = 'inactive';

이 방식은 MySQL이 JOIN을 통해 두 테이블을 결합한 후 조건을 필터링하기 때문에 성능이 향상됩니다.

1. 실행 계획 분석: EXPLAIN 명령어를 사용하여 최적화 전과 후의 쿼리 실행 계획을 비교합니다.

EXPLAIN SELECT *
FROM orders
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE status = 'inactive'
);

EXPLAIN SELECT orders.*
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
WHERE customers.status = 'inactive';

최적화 후의 쿼리에서 테이블 접근 방식이 어떻게 개선되었는지 확인할 수 있습니다. 예를 들어, JOIN 방식이 IN 서브쿼리보다 rows 수가 줄어들고, Using index 키워드가 표시될 수 있습니다.

최적화된 결과

최적화된 쿼리는 다음과 같은 이점을 제공합니다:

• 검색 속도 향상: JOIN을 사용함으로써 쿼리 플래너가 더 나은 최적화 전략을 세울 수 있게 됩니다.
• 인덱스 사용 최적화: 상태에 대한 인덱스를 추가하여 'inactive' 고객을 빠르게 찾을 수 있습니다.
• 효율적인 데이터 접근: 서브쿼리 없이 직접적으로 테이블 간의 결합을 통해 데이터를 검색하므로, 전체 성능이 개선됩니다.

---

다른 예시로, 쿼리에서 불필요하게 중복된 데이터를 검색하거나 서브쿼리를 사용하는 상황을 최적화하는 방법을 다뤄보겠습니다.

예제 쿼리 (최적화 전)

다음과 같은 쿼리를 생각해 봅시다. 이 쿼리는 sales 테이블에서 특정 날짜 범위에 발생한 총 매출을 계산하는데, 두 번의 서브쿼리를 사용하여 데이터를 얻고 있습니다.

SELECT
    (SELECT SUM(total_amount) FROM sales WHERE sale_date >= '2023-01-01' AND sale_date <= '2023-06-30') AS total_sales_first_half,
    (SELECT SUM(total_amount) FROM sales WHERE sale_date >= '2023-07-01' AND sale_date <= '2023-12-31') AS total_sales_second_half;

이 쿼리는 같은 테이블을 두 번 접근하기 때문에, 데이터 양이 많은 경우 비효율적이고 실행 속도가 느릴 수 있습니다. 서브쿼리의 중복 접근을 줄이고, 쿼리를 단순화할 필요가 있습니다.

최적화 과정

1. 조건을 그룹화하여 한 번의 스캔으로 처리:
   • 동일한 테이블을 여러 번 접근하는 대신, CASE 문을 사용해 한 번의 집계를 통해 두 값을 모두 계산할 수 있습니다.

SELECT
    SUM(CASE WHEN sale_date >= '2023-01-01' AND sale_date <= '2023-06-30' THEN total_amount ELSE 0 END) AS total_sales_first_half,
    SUM(CASE WHEN sale_date >= '2023-07-01' AND sale_date <= '2023-12-31' THEN total_amount ELSE 0 END) AS total_sales_second_half
FROM sales;

이 방법을 통해 sales 테이블을 한 번만 스캔하게 되므로, 읽어야 하는 데이터의 양이 줄어들고 쿼리 성능이 개선됩니다.

1. 인덱스 추가:

• sale_date 열에 인덱스를 추가하여 날짜 범위 필터링을 효율적으로 수행할 수 있게 합니다.

CREATE INDEX idx_sale_date ON sales (sale_date);

1. 실행 계획 분석:
   • EXPLAIN 명령어를 사용하여 최적화 전과 후의 실행 계획을 비교해 봅니다.

EXPLAIN SELECT
    (SELECT SUM(total_amount) FROM sales WHERE sale_date >= '2023-01-01' AND sale_date <= '2023-06-30') AS total_sales_first_half,
    (SELECT SUM(total_amount) FROM sales WHERE sale_date >= '2023-07-01' AND sale_date <= '2023-12-31') AS total_sales_second_half;

EXPLAIN SELECT
    SUM(CASE WHEN sale_date >= '2023-01-01' AND sale_date <= '2023-06-30' THEN total_amount ELSE 0 END) AS total_sales_first_half,
    SUM(CASE WHEN sale_date >= '2023-07-01' AND sale_date <= '2023-12-31' THEN total_amount ELSE 0 END) AS total_sales_second_half
FROM sales;

최적화된 쿼리에서 테이블의 전체 스캔 횟수가 줄어드는 것을 확인할 수 있습니다.

최적화된 결과

최적화된 쿼리는 다음과 같은 장점을 가집니다:

• 성능 향상: 테이블을 한 번만 스캔하여 계산을 수행하므로, 데이터 양이 많을 때 성능이 크게 개선됩니다.
• 단순화된 쿼리 구조: CASE 문을 사용하여 조건별 집계를 한 번에 수행할 수 있어, 가독성이 높아지고 유지보수도 쉬워집니다.
• 인덱스 활용: sale_date 컬럼에 인덱스를 추가하여 필터링 시 효율성을 높였습니다.

---

이번에는 다중 테이블 조인에서 비효율적인 조인을 최적화하는 예시를 들어보겠습니다.

예제 쿼리 (최적화 전)

아래 쿼리는 orders, customers, 그리고 products 테이블을 조인하여 고객의 주문 정보를 가져오는 쿼리입니다:

sql
Copy code
SELECT orders.order_id, customers.customer_name, products.product_name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
JOIN products ON orders.product_id = products.product_id
WHERE customers.country = 'USA' AND products.category = 'Electronics';

이 쿼리는 다음과 같은 문제가 있습니다:

1. 모든 조건이 마지막까지 필터링되기 때문에 처음부터 많은 데이터를 조인하고 결과적으로 불필요하게 많은 데이터를 조작하게 됩니다.
2. 테이블 조인의 순서나 필터링 방식이 비효율적일 수 있습니다.

최적화 과정

1. 조인의 순서 최적화:
   • 필터링 조건이 많은 테이블을 먼저 조인하여 조인의 데이터 양을 줄일 수 있습니다. 예를 들어, customers에서 country 조건을 먼저 필터링하는 것이 좋습니다.
   최적화된 쿼리:

SELECT orders.order_id, customers.customer_name, products.product_name
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
JOIN products ON orders.product_id = products.product_id
WHERE customers.country = 'USA' AND products.category = 'Electronics';

customers 테이블에서 country = 'USA' 조건을 적용하여 고객의 범위를 줄인 후, 나머지 조인 작업을 진행하므로 처리해야 하는 데이터의 양이 줄어듭니다.

1. 인덱스 추가:
   • customers 테이블의 country 열, products 테이블의 category 열에 인덱스를 추가하면 해당 조건을 통해 더 빠르게 필터링할 수 있습니다.

CREATE INDEX idx_country ON customers (country);
CREATE INDEX idx_category ON products (category);

1. 실행 계획 분석:
   • EXPLAIN 명령어를 사용하여 조인의 순서가 최적화되었는지 확인하고, 데이터 접근 방식이 개선되었는지 확인합니다.

EXPLAIN SELECT orders.order_id, customers.customer_name, products.product_name
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
JOIN products ON orders.product_id = products.product_id
WHERE customers.country = 'USA' AND products.category = 'Electronics';

EXPLAIN 결과를 보면, 쿼리의 조인 순서가 고객 데이터를 먼저 필터링한 후 조인을 진행하도록 변경된 것을 확인할 수 있습니다. 이는 처리할 데이터 양을 줄이는 데 효과적입니다.

1. 조건별 필터링 순서 변경:
   • 조건에 따라 필터링을 효율적으로 수행하기 위해 STRAIGHT_JOIN을 사용하여 조인의 순서를 고정할 수 있습니다. 이렇게 하면 MySQL이 임의로 조인 순서를 변경하지 않게 하여 최적화된 순서대로 처리하게 합니다.

SELECT orders.order_id, customers.customer_name, products.product_name
FROM customers
STRAIGHT_JOIN orders ON customers.customer_id = orders.customer_id
STRAIGHT_JOIN products ON orders.product_id = products.product_id
WHERE customers.country = 'USA' AND products.category = 'Electronics';

이 방식은 쿼리의 성능을 조정할 때 유용할 수 있으며, 특히 데이터 양과 필터링 조건에 따라 조정이 필요할 때 효과적입니다.

최적화된 결과

최적화된 쿼리는 다음과 같은 장점을 제공합니다:

• 조인 순서 개선: 먼저 고객 데이터를 필터링함으로써 이후 조인에서 처리해야 할 데이터 양을 줄여 성능을 향상시킵니다.
• 인덱스 사용을 통한 필터링 속도 향상: 조건에 사용되는 열에 인덱스를 추가하여 빠르게 데이터를 찾을 수 있게 했습니다.
• STRAIGHT_JOIN 사용: 조인의 순서를 명확히 지정하여 쿼리 최적화를 더 효과적으로 수행할 수 있습니다.

---

이번에는 복잡한 서브쿼리와 집계 연산이 결합된 쿼리를 최적화하는 예시를 다뤄보겠습니다.

예제 쿼리 (최적화 전)

다음 쿼리는 employees 테이블에서 각 부서(department)별로 직원들의 평균 급여를 구한 다음, 특정 급여 이상의 부서 이름과 평균 급여를 출력합니다:

SELECT department_name, avg_salary
FROM (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) AS dept_avg
JOIN departments ON dept_avg.department_id = departments.department_id
WHERE avg_salary > 50000;

이 쿼리는 서브쿼리로 부서별 평균 급여를 구한 다음, 결과를 상위 쿼리에서 사용하고 있습니다. 서브쿼리의 중복 접근과 큰 테이블에서 집계 연산을 여러 번 수행하기 때문에 성능에 영향을 줄 수 있습니다.

최적화 과정

1. WITH 절을 사용한 공통 테이블 표현식(CTE) 사용:
   • 중첩 서브쿼리를 WITH 절을 사용한 공통 테이블 표현식(CTE)로 바꾸면 가독성을 높이고 쿼리 최적화를 돕습니다.
   최적화된 쿼리:

WITH dept_avg AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT departments.department_name, dept_avg.avg_salary
FROM dept_avg
JOIN departments ON dept_avg.department_id = departments.department_id
WHERE dept_avg.avg_salary > 50000;

WITH 절을 사용하여 쿼리를 구조화하면 MySQL이 쿼리를 더 효과적으로 최적화할 수 있습니다.

1. 인덱스 추가:
   • 집계 연산의 속도를 높이기 위해 employees 테이블의 department_id 열에 인덱스를 추가합니다.
   • 또한 departments 테이블의 department_id 열에도 인덱스를 추가하여 JOIN의 성능을 높입니다.

CREATE INDEX idx_department_id ON employees (department_id);
CREATE INDEX idx_department_id_dept ON departments (department_id);

1. HAVING 절 사용으로 쿼리 단순화:
   • 서브쿼리를 없애고, GROUP BY 후 HAVING 절을 사용해 필요한 조건을 바로 필터링합니다.

   SELECT departments.department_name, AVG(employees.salary) AS avg_salary
   FROM employees
   JOIN departments ON employees.department_id = departments.department_id
   GROUP BY employees.department_id
   HAVING avg_salary > 50000;
   

   HAVING 절을 사용하면 서브쿼리를 없애고, 쿼리를 한 단계로 단순화하여 전체적인 성능을 향상시킬 수 있습니다.
2. 실행 계획 분석:

• EXPLAIN 명령어를 사용하여 최적화 전과 후의 실행 계획을 비교합니다.

EXPLAIN WITH dept_avg AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT departments.department_name, dept_avg.avg_salary
FROM dept_avg
JOIN departments ON dept_avg.department_id = departments.department_id
WHERE dept_avg.avg_salary > 50000;

EXPLAIN SELECT departments.department_name, AVG(employees.salary) AS avg_salary
FROM employees
JOIN departments ON employees.department_id = departments.department_id
GROUP BY employees.department_id
HAVING avg_salary > 50000;

최적화된 쿼리에서 EXPLAIN 결과를 보면, 서브쿼리 대신 HAVING 절을 사용한 방식이 더 적은 데이터 스캔과 효율적인 테이블 접근을 가능하게 합니다.

최적화된 결과

최적화된 쿼리는 다음과 같은 장점을 제공합니다:

• 쿼리 단순화: HAVING 절을 사용해 서브쿼리를 없애고 쿼리를 단순화했습니다.
• 인덱스를 통한 효율적인 필터링: employees와 departments 테이블에 적절한 인덱스를 추가하여 JOIN과 집계 연산의 성능을 향상시켰습니다.
• 가독성 향상: CTE와 HAVING 절을 사용해 쿼리의 가독성을 높이고, 유지보수가 쉬워졌습니다.

이처럼 쿼리의 복잡성을 줄이고, 필터링과 집계 연산의 최적화를 통

---

결론

이와 같은 최적화는 데이터베이스의 구조와 데이터 양에 따라 다른 방식으로 접근할 수 있지만, 인덱스 추가, 서브쿼리 제거 및 JOIN 사용 등은 일반적으로 성능을 크게 향상시킬 수 있는 기법입니다.