깔끔하게 쿼리튜닝하기

쿼리튜닝 기초와 실행계획을 읽는 방법을 알아봅니다.
(23' Google Devfest Songdo)

Transcript

1. 깔끔하게 쿼리튜닝하기 - PostgreSQL Incheon/Songdo
2. Contents 1. Query Tuning 2. Postgresql 3. Query Processing 4. Query Execution Plan 5. Query Tuning Approaches 6. Quiz Time Incheon/Songdo
3. Query Tuning
4. 데이터베이스 성능 최적화 쿼리 튜닝은 왜 하는가?
5. "안녕하세요! 제일 먼저... 아메리카노 주문할건데요, 아니, 아이스 아메리카노 로 바꿔주세요. 그리고 캐러멜 토핑도 넣고, 아니, 그건 생각해보니 좀 달 것 같아서 캐러멜은 취소해주세요. 그리고 샷 추가는 괜찮아 보이지만 일단 그냥 원래대로 주문할게요. 그리고 뜨거운 우유 대신에 아이스 우유로 주문하고 싶 은데, 그건 가능한가요? 얼음은 넣으면 안 되는 거죠? 아, 그리고 초콜릿 모카 는 아이스로 할게요. 얼음을 좀 덜 넣어주세요. 크림은 처음에 많이 했던 거 그 대로 주시면 돼요. 휘핑은 그냥 생각 없이 두 번 추가해주세요. 아, 그리고 블 루베리 머핀 대신에 초콜릿 칩 쿠키로 바꿔주세요!" 어떤 고객이 될 것인가
6. 알아듣기 쉽게 주문하자 “아이스 아메리카노, 초콜릿 모카는 얼음 적게, 크림은 많이, 휘핑 두 번, 초콜릿 칩 쿠키로 변경이요.”
7. 그럼에도 쿼리튜닝을 알아야 하는 이유 • 데이터베이스 안에서 동작방식의 이해 필요성 • 작업 능률 향상 • 복잡한 쿼리 작성시 • AI의 한계 + 보완 • 우리는 백엔드 개발자이기 때문에 화이팅!
8. Postgresql
9. The World's Most Advanced Open Source Relational Database
10. PostgreSQL https://db-engines.com/en/ranking
11. Query Processing
12. 쿼리가 돌아가는 방식 Parser Stage Parser Rewriter Planner Query tree Plan tree Parser tree Executor SQL Statement Chapter 52. Overview of PostgreSQL Internals Transformation
13. 예시 쿼리 SELECT zip, ROW_NUMBER() OVER (ORDER BY price DESC) AS price_rank, RANK() OVER (PARTITION BY type ORDER BY price DESC) AS type_price_rank FROM property_transactions WHERE city = 'SACRAMENTO'
14. Parser Parser Stage Parser Rewriter Planner Query tree Plan tree Parser tree Executor SQL Statement Chapter 52. Overview of PostgreSQL Internals Transformation
15. Parse Tree SelectStmt targetList fromClause whereClause ResTaget RangeVar OpExpr ColumnRef 'city' 'SACRAMENTO' ColumnRef [zip] ResTaget ResTaget WindowDef OrderClause ColumnRef [price] FuncCall [row_number] FuncCall [rank] SortBy [DESC] WindowDef OrderClause ColumnRef [price] SortBy [DESC] Partition Clause [type]
16. Transformation Parser Stage Parser Rewriter Planner Query tree Plan tree Parser tree Executor SQL Statement Chapter 52. Overview of PostgreSQL Internals Transformation
17. Rewriter Parser Stage Parser Rewriter Planner Query tree Plan tree Parser tree Executor SQL Statement Chapter 52. Overview of PostgreSQL Internals Transformation
18. Planner Rewriter Planner Query tree Plan tree Parser tree Executor SQL Statement Chapter 52. Overview of PostgreSQL Internals Parser Stage Parser Transformation
19. Executor Rewriter Planner Query tree Plan tree Parser tree Executor SQL Statement Chapter 52. Overview of PostgreSQL Internals Parser Stage Parser Transformation
20. Explain Analyze
21. Query Execution Plan
22. Parser EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) SELECT zip, ROW_NUMBER() OVER (ORDER BY price DESC) AS price_rank, RANK() OVER (PARTITION BY type ORDER BY price DESC) AS type_price_rank FROM test WHERE city = 'SACRAMENTO';
23. 쿼리 실행계획 알아보기
24. Query Plan
25. Query Plan
26. Query Plan
27. Query Plan
28. Query Plan
29. Query Plan
30. Query Tuning Approaches
31. 쿼리 튜닝을 위한 고려사항 1. Basic 2. Index 3. Scan 4. Join 5. SubQuery 6. View 7. Temp Table
32. 1. Basic - 선택조건의 차이 WHERE city = 'SACRAMENTO' and street like '%S%'; WHERE street like '%S%' and city = 'SACRAMENTO';
33. 1. Basic - Like 조건 WHERE city = 'SACRAMENTO' and street like '%S%'; WHERE city = 'SACRAMENTO' and street like 'S%';
34. Where 조건절의 순서와 Like로 찾는 범위를 줄이자. [정리1]
35. 2. 인덱스 연관이미지
36. 어디에 만들어야 잘했다고 소문이 날까? 그럼 인덱스를..
37. 2. 인덱스를 꼭 만들어야 하나? • 자주 조회되고 쓰기가 자주 일어나지 않는 컬럼 • 값의 중복이 적은 고유한 컬럼 • 인덱스를 만들 컬럼의 데이터 크기 • 연산에 사용될 가능성 • 추가하는 경우 history를 확인하기 쿼리 최적화가 가능한가?
38. 2. Postgresql 인덱스 타입 CREATE INDEX idx_name ON table_name USING btree (column_name); • B-tree • Hash Index • GiST Index • GIN Index • BRIN Index Each index type uses a different algorithm that is best suited to different types of queries. By default, the CREATE INDEX command creates B-tree indexes, which fit the most common situations. The other index types are selected by writing the keyword USING followed by the index type name.
39. 2. Postgresql 인덱스 타입 - B-tree 연관이미지
40. 2. Postgresql 인덱스 타입 - 해시 인덱스 연관이미지
41. 2. Postgresql 인덱스 적용 연관이미지
42. Parser EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01'; [100만건의 레코드를 가지는 orders, customers 테이블]
43. 2. Postgresql 인덱스 적용 - Composite Index CREATE INDEX idx_customer_id_order_date ON orders (customer_id, order_date);
44. 2. Postgresql 인덱스 적용 - Index Merge CREATE INDEX idx_customer_id ON orders (customer_id); CREATE INDEX idx_order_date ON orders (order_date);
45. ++ 그럼... UNION ALL은? SELECT * FROM orders WHERE customer_id = 123; UNION ALL SELECT * FROM orders WHERE order_date > '2023-01-01'; SELECT * FROM orders WHERE customer_id = 123 OR order_date > '2023-01-01'; ++ Clustered Index는? CLUSTER orders USING order_date ;
46. 인덱스 유형을 알고 필요한 컬럼에 인덱스를 설정하자. [정리 2]
47. 3. Scan • Sequential Scan • Index Scan • Index-Only Scan • Bitmap Index Scan • TID Scan • Custom Scan The method used to retrieve data from a table or index. The structure of a query plan is a tree of plan nodes. Nodes at the bottom level of the tree are scan nodes: they return raw rows from a table.
48. 3. Scan - Sequential Scan SELECT * FROM orders WHERE order_status = 'Processing';
49. 3. Scan - Index Scan SELECT * FROM orders WHERE customer_id = 123;
50. 3. Scan - Index Scan 종류 • Index Scan • Index-Only Scan • Bitmap Index Scan
51. 3. Index Scan 안 되게 하는 방법 WHERE city = 'SACRAMENTO' and street like '%S%';
52. 3. Index Scan 안 되게 하는 방법 WHERE city = 'SACRAMENTO' and street like '%S%';
53. 3. Index Scan 안 되게 하는 방법 WHERE substring(city,0,2) like 'SA' and street like '%S%';
54. 결과 데이터 양에 따라 스캔 방식별 효율성이 달라진다. [정리 3]
55. 4. Join Operations • Nested loop join • Hash join • Merge join Logical joins refer to the relational theory aspect of joining tables, defined by SQL JOIN operations like INNER JOIN, LEFT JOIN, etc., focusing on how tables are logically combined based on specified conditions. Physical joins, on the other hand, are about how a database system actually implements these logical join operations, involving methods like nested loops, hash joins, or merge joins, which are concerned with execution efficiency and optimization. • Inner join • Left join • Outer join
56. 4. Join Operations - Nested Loop Join 연관이미지
57. 4. Join Operations - Nested Loop Join
58. 4. Join Operations - Hash Join 연관이미지
59. 4. Join Operations - Hash Join
60. 4. Join Operations - Merge Join 연관이미지
61. 결과값이 작다면 Nested Join이 되도록 인덱스 컬럼을 만들자! [정리 4]
62. Quiz Time
63. 이 친구의 이름은?
64. 쿼리 어떻게 고치면 좋을까요?
65. Thank you!