14. 효율적인 SQL 코드 작성법

학습주제

• 효율적인 SQL 코드 작성하기
  • 테이블을 집합으로 생각하기
  • *, % 사용 지양하기
  • 데이터 타입 잘 확인하기
  • JOIN 시 유의할 점
  • 가독성 높이기

주요 메모 사항 소개

• 테이블을 집합으로 생각한다.
  • 최대한 작게 만들어 놓고 JOIN 한다.
  • 즉 WHERE 절을 이용하여 최대한 필터링 하는것이 효율적이다.

    WITH customer as (
    	SELECT customer_id, customer_name, customer_tier
        FROM Customers
        WHERE customer_id = 1 -- <- 처럼 WHERE문을 사용하여 최대한 필터링을 거친 후
        					--		사용하는 것이 좋다
    ),
    order_info as (
    	SELECT customer_id, order_id, amount
        FROM Orders
        WHERE customer_id = 1
    )
    SELECT c.customer_id, customer_name, customer_tier,
    count(distinct order_id) as odr_cnt, sum(amount) as total_purchase
    FROM customer c INNER JOIN order_info o on
    c.customer_id = o.customer_id
    GROUP BY 1, 2, 3
    ORDER BY 4 DESC
    --better
    -----------------------------------------------------------------------
    
    WITH customer as (
    	SELECT customer_id, customer_name, customer_tier
        FROM Customers
    ),
    order_info as (
    	SELECT customer_id, order_id, amount
        FROM Orders
        WHERE customer_id = 1
    )
    SELECT c.customer_id, customer_name, customer_tier,
    count(distinct order_id) as odr_cnt, sum(amount) as total_purchase
    FROM customer c INNER JOIN order_info o on
    c.customer_id = o.customer_id
    GROUP BY 1, 2, 3
    ORDER BY 4 DESC​

 

• *, % 사용 지양한다.
  • LIMIT 걸고 조회한다.

    

  • 파티션이 있는 테이블인지 확인하고, 파티션을 필터 조건으로 걸고 조회한다.

    

  • 칼럼 수가 많은 테이블을 조회할 때 SELECT *  사용 지양한다.
    - 위의 사진 처럼 SELECT 문에 본인이 필요한걸 찾아서 적어주는게 효율적 입니다.
  • LIKE 사용 시 % 제한적으로 사용한다.

    SELECT product_id, name
    FROM products
    WHERE name LIKE '23FW%'
    LIMIT 10
    
    ------------------------------
    
    SELECT product_id, name
    FROM products
    WHERE name LIKE '23FW__' -- <- 이런식으로 % 사용을 지양합니다.
    LIMIT 10​

 

• 데이터 타입 잘 확인하기
  • 묵시적 형변환 : 비교하고자 하는 값이 서로 다른 타입일때, DB가 알아서 TYPE을 맞춘 후 비교하는 것
    - 형변환에 걸리는 시간만큼 쿼리가 비효율적이 된다.
    - 쿼리가 복잡해지면 문제 해결 시 시간이 오래 걸린다.
  • 비교 연산자를 쓸 때 타입을 확인한다.

    

    비교 연산자 사용시 type을 먼저 확인한 후  맞는 type으로 비교하여 묵시적 형변환을    예방한다.

  • WHERE 절에서 왼쪽 컬럼에 함수 적용 지양한다.

    

    위의 코드는 문제가 없지만 DATE_FORMAT 함수를 date절에 사용하면 문제가 발생한다.  이유는 WHERE 절에서 컬럼쪽에 함수를 사용할 경우 index (데이터 읽기속도에 연관) 사용 불가능하다.   또한 큰 테이블 컬럼에 함수를 적용하면 수행시간만큼 컬럼이 무거워 진다.

• JOIN 시 유의할 점
  • 1. JOIN 하는 테이블 간의 관계를 고려한다.
    https://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model

    

    E-R 모델을 잘 파악해야합니다.

  •  데이터 중복이 있는지 확인한다.
    - 큰 테이블끼리 결합하면 속도가 느려지고, 결과 값이 달라질 수 있다.
    
    
  • 여러가지 쿼리 방식을 고려하자

    SELECT user_name, product_id
    FROM (
    	SELECT user_name, clk_index, product_id,
    row_number() over(PARTITION BY user_name
    ORDER BY clk_index ASC) as rownum
        FROM clikcs
        )
    WHERE rownum = 1
    
    --------------------------------------------
    WITH clk as (
    	SELECT user_name, product_id, clk_index
        FROM clicks
    ),
    mini as (
    	SELECT user_name, min(clk_index) as min_inx
        FROM clicks
        GROUP BY 1
    )
    SELECT clk.user_name, clk.product_id
    FROM clk INNER JOIN mini ON clk.user_name =
    mini.user_name AND clk.clk_index = min_idx
    
    ---------------------------------------------
    SELECT c.user_name, product_id
    FROM clicks c
    	INNER JOIN(
    		SELECT user_name, min(clk_index) as 
    min_idx
    		FROM clicks
            GROUP BY 1
    	) g
        ON c.user_name = g.user_name
        AND c.clk_index = min_idx

     위의 3가지 코드는 전부 동일한 값을 출력하는 코드 입니다. 이중 2번 코드가 가독성이 좋고 제일 빠르나 상황에 따라 맞게 사용하면 좋을 것 같습니다.
• 가독성 높이기
  • 서브쿼리 보다는 WITH 구문이 가독성이 좋다.
  • WITH 절을 사용할 때, 각 블록 이름을 잘 지정하자
  • 쿼리가 복잡해 지면 중간중간 주석을 작성하자.

    WITH odr_cnt as ( -- 고객별 구매 수
    	SELECT c.customer_id, count(distinct order_id) as odr_cnt,
    sum(amount) as total_purchase
    	FROM Customers c INNER JOIN Orders o on
    c.customer_id = o.customer_id
    	GROUP BY 1
        ORDER BY 2 DESC
    )​

    이런식으로 WITH 문과 주석을 활용하여 가독성을 높이면 좋습니다.

 

공부하며 어려웠던 내용 

• JOIN을 사용할때 효율성을 높히려면 상당히 고려할게 많다는 것을 느꼈다.
  위의 내용을 토대로 깔끔한 코드 작성을 연습해야겠다.