[SQL] 불필요한 쿼리 제거 작업

서론

자신이 작업한 코드를 포함한 모든 결과물은 Legacy가 된다.
다만, 개선하려는 시점의 주제가 무엇이냐에 따라 알고리즘 개선이 될지 비즈니스 로직 개선이 될지 결정될 수 있다.
실무를 수행하면서 2년 전 mybatis로 개발된 SQL에서 시스템 고도화에 따라 사용할 수 없거나 불필요한 쿼리가 발견되어 최적화를 위해 해결하던 도중 간단한 이론에 대해 정리해본다.

---

이론적 배경

• SQL 쿼리는 어떻게 동작하는가?
  • 1) FROM : 어느 테이블에서 찾을 것인가?
  • 2) JOIN : 어떤 테이블을 연관지을 것인가?
  • 3) ON : 연관 조건은 어떤 것인가?
  • 4) WHERE : 어떤 필터를 적용할 것인가?
  • 5) GROUP BY : 값을 기준으로 그룹화를 수행할 것인가?
  • 6) HAVING : WHERE과 유사하지만 그룹에 대한 필터를 적용할 것인가?
  • 7) SELECT : 어떤 정보를 호출할 것인가?
  • 8) ORDER BY : 어떤 순서로 호출할 것인가?
  • 9) LIMIT : 어디까지 호출할 것인가?

Visualizing a SQL query

 

• 서브쿼리는 무엇인가?
  • 위에서 언급한 SQL 쿼리가 한 개의 SELECT 문 내에 여러개 존재할 수 있는 부분을 의미한다.
  • 실제 오래된 한방 쿼리의 경우, 분석 단계에서 서브쿼리가 다수 엮여있어 난해한 부분을 맞이할 수 있다.

서브쿼리(subquery)

---

준비

1. 해당 쿼리에 대한 비즈니스 로직에 대한 이해가 충분한가?
   1. 무작정 쿼리를 분석하는 습관은 좋지 않을 수 있다.
   2. 해당 Query가 왜 구성되었는지 먼저 파악하는 것이 중요하며, 이는 겉으로는 불필요한 Logic이 반영되어 있다고 하더라도 비즈니스 관점에서 필요한 내용이 적용되었을 가능성이 있다.
   3. 예를 들면, 3년 전 초기 서비스의 인증일자와 현재 인증일자를 관리하는 Table 및 Column이 다른 경우에 동일 목적을 가지는 데이터를 조회하고 활용하기 위해 서브쿼리를 활용할 수 있다.
2. 작성된 쿼리에 대해서 분석을 완료하였는가?
   1. 1차원적으로는 위에서 언급한 SQL이 어떤 식으로 동작하는지 파악하는 것이다.
   2. 단일 쿼리의 경우 순서가 명확하게 보일 수 있어 분석하기 쉽지만, 서브쿼리가 4개 이상 구성되어 있는 복합 쿼리일 경우에는 무엇을 먼저 분석해야되는지 헷갈릴 경우가 있다.

---

간단한 예제

• MySQL SQL
  
  • 실무에서 활용되는 코드는 변수를 활용하여 조건을 추가할 수 있기에 명세가 조금 다르다.
  • 하지만, 임의 값으로 동일한 SQL을 구성하여 테스트 시 동작에 대한 검증을 수행할 수 있다.

SELECT
    main.order_id,
    main.customer_id,
    main.order_date,
    main.customer_name,
    main.shipping_address,
    main.payment_method,
    main.total_order_value,
    main.total_items,
    main.earliest_delivery,
    main.delivery_duration,
    main.latest_delivery
FROM
    (
        SELECT
            o.order_id,
            o.customer_id,
            o.order_date,
            c.customer_name,
            s.shipping_address,
            pm.payment_method,
            (
                SELECT SUM(quantity * unit_price)
                FROM order_items
                WHERE order_id = o.order_id
            ) AS total_order_value,
            (
                SELECT COUNT(*)
                FROM order_items
                WHERE order_id = o.order_id
            ) AS total_items,
            (
                SELECT MIN(delivery_date)
                FROM shipments
                WHERE order_id = o.order_id
            ) AS earliest_delivery,
            (
                SELECT DATEDIFF(MIN(delivery_date), o.order_date)
                FROM shipments
                WHERE order_id = o.order_id
            ) AS delivery_duration,
            (
                SELECT MAX(delivery_date)
                FROM shipments
                WHERE order_id = o.order_id
            ) AS latest_delivery
        FROM
            orders o
        LEFT JOIN customers c ON o.customer_id = c.customer_id
        LEFT JOIN shipments s ON o.order_id = s.order_id
        LEFT JOIN payment_methods pm ON o.payment_method_id = pm.payment_method_id
        LEFT JOIN order_status os ON o.order_status_id = os.order_status_id
    ) AS main
WHERE
    main.order_date >= '2024-01-01'
    AND main.total_order_value > 1000
    AND main.total_items > 5
ORDER BY
    main.order_date DESC;

 

• SQL Mybatis 적용
  
  • 실제 코드에서는 조건에 따라 유연하게 결과 값을 조절하기 때문에 Mybatis 등 SQL 관련 기능을 사용할 수 있다.
  • 이에, 단순 코드를 가지고 분석하기에는 변수의 적용 범위가 즉시 보이지 않을 수 있어 해당 변수의 Scope를 확인해야 한다.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.mapper.OrderMapper">

    <!-- 복잡한 주문 정보 조회 -->
    <select id="selectComplexOrderInfo" parameterType="map" resultType="map">
        SELECT
            main.order_id,
            main.customer_id,
            main.order_date,
            main.customer_name,
            main.shipping_address,
            main.payment_method,
            main.total_order_value,
            main.total_items,
            main.earliest_delivery,
            main.delivery_duration,
            main.latest_delivery
        FROM
            (
                SELECT
                    o.order_id,
                    o.customer_id,
                    o.order_date,
                    c.customer_name,
                    s.shipping_address,
                    pm.payment_method,
                    (
                        SELECT SUM(quantity * unit_price)
                        FROM order_items
                        WHERE order_id = o.order_id
                    ) AS total_order_value,
                    (
                        SELECT COUNT(*)
                        FROM order_items
                        WHERE order_id = o.order_id
                    ) AS total_items,
                    (
                        SELECT MIN(delivery_date)
                        FROM shipments
                        WHERE order_id = o.order_id
                    ) AS earliest_delivery,
                    (
                        SELECT DATEDIFF(MIN(delivery_date), o.order_date)
                        FROM shipments
                        WHERE order_id = o.order_id
                    ) AS delivery_duration,
                    (
                        SELECT MAX(delivery_date)
                        FROM shipments
                        WHERE order_id = o.order_id
                    ) AS latest_delivery
                FROM
                    orders o
                LEFT JOIN customers c ON o.customer_id = c.customer_id
                LEFT JOIN shipments s ON o.order_id = s.order_id
                LEFT JOIN payment_methods pm ON o.payment_method_id = pm.payment_method_id
                LEFT JOIN order_status os ON o.order_status_id = os.order_status_id
            ) AS main
        <where>
            <if test="orderDate != null">
                AND main.order_date >= #{orderDate}
            </if>
            <if test="minOrderValue != null">
                AND main.total_order_value > #{minOrderValue}
            </if>
            <if test="minItems != null">
                AND main.total_items > #{minItems}
            </if>
        </where>
        ORDER BY
            main.order_date DESC
    </select>

</mapper>

---

결론

1. Legacy라는 단어는 명확하게 기존 개발된 코드를 설명하는 최적 단어라고 생각한다.
2. 경우에 따라, 훌륭한 유산이 될거나 아무도 건드리지 못하는 코드가 될 수 있다고 생각한다.
3. 화이팅 하자.

---

참고 및 인용 출처

- ALEX XU. (2023.11.). Visualizing a SQL query. https://blog.bytebytego.com/p/ep50-visualizing-a-sql-query.
- Tcp School. (2018). 서브쿼리. https://www.tcpschool.com/mysql/mysql_multipleTable_subquery#google_vignette.