SQL 레벨업

* 배경

 

통계 화면 쿼리를 만드는데, GROUP BY를 통한 COUNT를 사용하였습니다. 또한 다양한 서브쿼리들을 사용하면서 조금 더 SQL을 효율적이고 잘 작성하고 싶어졌습니다. 그래서 서점에서 SQL책을 둘러보다가, 발견하여 구매했습니다. 특히, INDEX 관련한 설명이 잘 나와있고 중급단계의 내용들이 잘 정리되어 있어서 좋았습니다.

 

https://www.kyobobook.co.kr/product/detailViewKor.laf?mallGb=KOR&ejkGb=KOR&barcode=9788968482519 

SQL 레벨업 - 교보문고

 

*알게 된 점

 

1. "조건 분기를 WHERE 구로 하는 사람들은 초보자다. 잘하는 사람은 SELECT 구만으로 조건 분기를 한다" (P111)

-- 나쁜 예시
SELECT item_name, year, price_tax_ex AS price
	FROM Items
    WHERE year <=2001
UNION ALL
SELECT item_name, year, price_tax_in AS price
	FROM Items
    WHERE year >= 2002;


-- 좋은 예시
SELET item_name, year,
      CASE WHEN year<=2001 THEN price_tax_ex
           WHEN year>=2002 THEN price_tax_in END AS price
FROM Items;

 

 

2. 집계의 조건 분기도 CASE 식을 사용한다 (p121)

SELECT prefecture,
		SUM(CASE WHEN sex='1' THEN pop ELSE 0 END) AS pop_men,
        SUM(CASE WHEN sex='2' THEN pop ELSE 0 END) AS pop_wom
    FROM Population
    GROUP BY prefecture;
    
    
SELECT emp_name,
		CASE WHEN COUNT(*) = 1 THEN MAX(team)
        	 WHEN COUNT(*) = 2 THEN '2'개를 겸무'
             WHEN COUNT(*) >=3 THEN '3개 이상을 겸무'
        END AS team
    FROM Employees
 	GROUP BY emp_name;

 

WHERE에서 조건분기를 한 사람과 마찬가지로 HAVING에서 조건분기를 하지 않는다.

 

 

3. UNION은 다른 테이블을 합칠 때에 주로 사용한다. (p126)

 

 

4. OR와 IN 절은 성능상 비슷하다. (p126)

 

 

5. UNION으로 3회 인덱스 스캔 VS 1회 테이블 풀스캔의 경우 WHERE 조건으로 선택되는 레코드의 수가 충분히 작고 테이블이 크다면 UNION이 더 유리하다

 

 

6.  크로스 결합이 실무에서 사용되지 않는 이유

- 이러한 결과가 필요한 경우가 거의 없다

- 비용이 매우 많이 드는 연산이다

(p207)

 

 

7. 내부결합과 상관 서브쿼리 중에 내부결합을 사용하는게 낫다.

상관 서브쿼리를 스칼라 서브쿼리로 이용하면 결과 레코드 수만큼 상관 서브쿼리 실행해 비용이 높다(p211)

 

-- 내부 결합
SELECT E.a, E.b, E.c, D.d 
	FROM Employees E INNER JOIN Departments D
    ON E.dept_id = D.dept_id;

-- 상관 서브쿼리
SELECT E.a, E.b, E.c,
	(SELECT D.d
    FROM Departments D
    WHERE E.dept_id = D.dept_id) AS d
FROM Employees E;

 

 

8.  SQL의 튜닝 기본은 구동테이블(drivin table)이 작으면서 내부테이블(inner table)의 결합 키에 인덱스를 조합 하는  것이다. 그런데, 결합 키로 내부테이블에 접근할 때 히트되는 레코드가 너무 많으면 지연이 일어나므로 주의한다.(p223)

 

 

9. 윈도우 함수로 결합을 줄이면 좋다.

SELECT cust_id, seq, price
	FROM(SELECT cust_id, seq, price,
    	ROW_NUMBER()
        OVER (PARTITION BY cust_id,
        		ORDER BY seq) AS row_seq
        FROM Receipts) WORK
WHERE WORK.row_seq = 1;

 

상관 서브쿼리는 저장소의 I/O를 유발한다. I/O 양을 감소시키는 것이 SQL 튜닝의 가장 기본이다.

(p251)

 

 

10.  Nedsted Loops의 내부 테이블 결합 키에 인덱스가 존재하면 성능이 크게 개선된다.(p253)

 

 

11. '윈도우 함수로 결합을 제거'와 마찬가지로 '테이블 접근과 결합을 얼마나 줄일 수 있는지' 중요하다(p256)

 

SELECT cust_id,
	SUM(CASE WHEN min_seq=1 THEN price ELSE 0 END)
    - SUM(CASE WHEN max_seq = 1 THEN price ELSE 0 END) AS diff
FROM (SELECT cust_id, price,
			ROW_NUMBER() OVER (PARTITION BY cust_id
            					ORDER BY seq) AS min_seq,
            ROW_NUMBER() OVER (PARTITION BY cust_id
            					ORDER BY seq DESC) AS max_seq
                                FROM Receipts) WORK
WHERE WORK.min_seq=1
      OR WORK.max_seq=1
GROUP BY cust_id;

 

12. 결합 대상 레코드 수를 집약하는 편이 I/O 비용을 더 줄일 수 있다(p265)

'사전에 결합 레코드 수를 압축한다' 라는 방법을 알아두면 된다.

 

 

13.  기본키가 필드 1개인 경우 순번 붙이기 - ROW_NUMBER()를 이용한다(p270)

 

SELECT student_id,
		ROW_NUMBER() OVER (ORDER BY student_id) AS seq
FROM Weights;

 

 

14. 기본키가 여러개의 필드로 구성되는 경우 - ORDER BY에 추가한다(p273)

 

SELECT student_id,
		ROW_NUMBER() OVER (ORDER BY class, student_id) AS seq
FROM Weights2;

 

 

15. 그룹마다 순번을 붙이는 경우 - PARTITION BY를 사용한다(p274)

 

SELECT class, student_id,
		ROW_NUMBER() OVER (PARTITION BY class OPRDER BY studnet_id) AS seq
FROM Weights2;

 

16. 인덱스는 카디널리티(값의 균형)가 높을수록, 선택률(특정 필드값이 테이블 전체의 몇개인지)이 낮을수록 좋다.

 

 

17. 인덱스 사용이 효과가 없는 경우(p361)

- WHERE 절이 없는 구문

- 레코드 압축이 안되는 경우(WHERE절에서 선택한 갯수가 너무 많음)

- 입력 매개변수의 선택률이 너무 크게 다른 경우(0.1% ~ 50%까지)

 

18. 인덱스를 사용하지 않는 검색 조건(p363)

- like를 중간, 후방일치로 사용하는 경우

ex)'%대공원%', '%대공원'

-> (교정) '대공원%'

 

- 색인 필드로 연산하는 경우

ex) WHERE col_1 * 1.1 > 100;

-> (교정) WHERE col_1 > 100/1.1;

 

- IS NULL을 사용하는 경우

ex) WHERE col_1 IS NULL;

 

- 색인 필드에 함수를 사용하는 경우

ex) WHERE LENGTH(col_1) = 10;

 

- 부정형을 사용하는 경우(<>, !=, NOT IN)

 

19. EXISTS를 사용할 수 있는 경우에는, 일반적으로 결합 대신 EXISTS를 사용하는 결합으로 변경하는 편이 성능 향상에 더 낫다.(p.398)

 

20. 집약보다 결합을 먼저 사용했을 때 레코드 수를 크게 줄일 수 있으므로 성능 이점이 있다.(p.400)