SQL 레벨업
추천 대상 : 쿼리를 효율적으로 짜고 싶은 SQL 초보자
추천 정도 : ★ ★ ★
메모 : 쿼리를 짜다보면 굉장히 길어지거나(가독성이 나쁘거나) 비효율적인 쿼리가 작성되기도 하는데, 어떻게 쿼리를 짜는 게 더 효율적인지, 테이블 설계는 어떻게 해야 하는지, 윈도우 함수가 어떻게 도움을 줄 수 있는지를 다루고 있다. 설렁설렁 볼만 한 책이다. 망치를 가진 사람에게는 모든 문제가 못으로 보인다는 말이 인상 깊었다. 항상 그렇지만 이번 메모는 유독 나 보기 편한 식으로만 정리해서 이 정리가 무슨 의미가 있을지는 모르겠지만.. 일단 업로드는 하였다.
발췌
1장 DBMS 아키텍쳐
쿼리 평가 엔진 : 사용자로부터 입력받은 SQL 구문을 분석하고, 어떤 순서로 기억장치의 데이터에 접근할지를 결정(실행계획,실행 플랜)
접근 메서드 : 실행계획에 기반을 둬서 데이터에 접근하는 방법
버퍼 매니저 : 메모리 영역(버퍼)을 관리하는 것
디스크 용량 매니저 : 어떻게 데이터를 저장할지를 관리
트랜잭션 매니저와 락 매니저 : 트랜잭션은 여러 사람들이 동시에 데이터베이스에 접근할 때 관리하는 단위. 락매니저는 트랜잭션의 정합성을 유지하면서 실행시키고 필요한 경우 데이터에 락을 검
리커버리 매니저 : 데이터를 정기적으로 백업하고 문제가 일어났을 때 복구
2장 DBMS와 버퍼
데이터를 버퍼에 어떠한 식으로 확보할 것인가에서 트레이드 오프 발생
공짜밥은 존재할까?
많은 데이터를 영속적으로 저장하려 하면 속도를 잃고. 속도를 얻고자 하면 많은 데이터를 영속적으로 저장하기 힘듬
DBMS와 기억장치의 관계
- 하드디스크(HDD) : 2차 기억장치. 그렇게 장점도, 그렇게 단점도 없음
- 메모리 : 디스크에 비해 기억 비용이 비쌈
- 버퍼를 활용한 속도 향상 : 데이터 일부라도 메모리에 올리는 것은 성능 향상 때문
- 버퍼 or 캐시 : 성능향상을 목적으로 데이터를 저장하는 메모리
- 버퍼매니저 : 데이터를 어떻게 어느 정도의 기간 동안 올릴지를 관리
메모리 위에 있는 두 개의 버퍼
- 데이터 캐시 : 디스크에 있는 데이터의 일부를 메모리에 유지하기 위해 사용
- 로그 버퍼 : 갱신처리와 관련이 있음. 갱신과 관련된 구문이 실행되면 곧바로 저장소의 데이터가 변경되는 것이 아니라 일단 로그 버퍼 위에 변경 정보를 보내고 이후에 디스크를 변경(성능 향상을 위해)
4. 메모리의 성질이 초래하는 트레이드오프
- 휘발성 : 메모리에는 데이터의 영속성이 없음
- 휘발성의 문제점 : 장애 발생시 메모리의 데이터가 모두 삭제되어 데이터 부정합을 발생시킴. 이를 회피하고자 커밋 시점에 반드시 갱신 정보를 로그 파일(영속적인 저장소에 존재)에 씀으로써 장애가 발생해도 정합성을 유지하게 함.
- 커밋 : 갱신 처리를 확정하는 것. 동기접근으로 지연이 발생할 가능성이 있음.
- DBMS : 커밋된 데이터를 영속화함
- 동기ㅓ리를 하면 데이터 정합성이 높아지지만 성능이 낮아짐
5. 시스템 특성에 따른 트레이드오프
- 데이터 캐시와 로그 버퍼의 크기 : 갱신 처리에 값비싼 메모리를 사용하는 것보다는, 자주 검색하는 데이터를 캐시에 올려놓는 것이 좋다
- 검색과 갱신 중에서 중요한 것 : 적절한 판단을 위해 데이터베이스가 어떠한 생각에 기반을 둬서 리소스를 배분하고 있는지를 이해하는 것이 굉장히 중요
6. 추가적인 메모리 영역 ‘워킹 메모리’
- 언제 사용될까?
워킹 메모리 : 정렬 또는 해시 관련 처리에 사용되는 작업용 영역. 정렬은 ORDER BY 구, 집합 연산, 윈도우 함수 기능 사용 시 실행. 해시는 주로 테이블 등의 결합에서 해시 결합이 사용되는 때 실행
- 부족하면 무슨 일이 일어날까?
메모리에서 작동하고 있을 때는 빠르게 움직이다가 메모리가 부족해지는 순간 갑자기 느려지는 순간적인 변화가 일어남
3강 DBMS와 실행 계획
권한 이양의 죄악
HOW를 의식하지 않고 사용하는 것 때문에 성능 문제로 고생하는 경우도 꽤 있음
데이터에 접근하는 방법은 어떻게 결정할까?
- 파서(parser) : 구문 분석하는 역할. SQL 구문을 검사하고 정형적인 형식으로 변환해줌. 일반적인 프로그래밍 언어의 컴파일 시점에서도 실행되는 것.
- 옵티마이저(optimizer) : 데이터의 접근법. 인덱스 유무, 데이터 분산 또는 편향 정도, DBMS 내부 매개변수 등의 조건을 고려해서 선택 가능한 많은 실행 계획 작성하고 이들의 비용을 연산하고 가장 낮은 비용을 가진 실행 계획을 선택. RDB가 데이터 접근의 절차 이향 결정을 자동화하는 이유는 이런 귀찮은 일을 기계적으로 처리해주기 위해서
- 카탈로그 매니저(catalog manager) : DBMS의 내부 정보를 모아놓은 테이블들로, 테이블 또는 인덱스의 통계 정보가 저장되어 있음. 통계 정보라고 부르기도 함.
- 플랜 평가 : SQL 구문에서 여러 개의 실행 계획을 세운 뒤 그것을 받아 최적의 실행 결과를 선택하는 것이 플랜 평가.실행 계획이라는 것은 인간이 읽기 쉽게 만들어진 '계획서'. 성능이 좋지 않은 구문이 있을 때 실행 계획을 읽고, 수정 방안을 고려할 수 있음.
옵티마이저와 통계 정보- 옵티마이저를 잘 사용하는 것이 중요. 카탈로그 매니저가 관리하는 통계 정보에 대해서는 데이터베이스 엔지니어가 항상 신경 써줘야 함- 카탈로그에 포함된 통계 정보 : 각 테이블의 레코드 수, 각 테이블의 필드 수와 필드의 크기, 필드의 카디널리티, 필드 값의 히스토그램(어떤 값이 얼마나 분포되어 있는가?), 필드 내부에 있는 NULL 수, 인덱스 정보- 문제가 생기는 경우는 이러한 카탈로그 정보가 테이블 또는 인덱스의 실제와 일치하지 않을 때
최덕의 실행 계획이 작성되게 하려면- 테이블의 데이터가 많이 바뀌면 카달로그의 통계 정보도 함께 갱신해야 한다
4강 실행 계획이 SQL 구문의 성능을 결정
실행 계획 확인 방법- 구문이 지연될 때는 제일 먼저 실행 계획을 살펴볼 것
이름 명령어
Oracle set autotrace traceonly
Microsoft SQL Server SET SHOWPLAN_TEXT ON
DB2 EXPLAIN ALL WITH SNAPSHOT FOR SQL구문
PostgreSQL EXPLAIN SQL 구문
MySQL EXPLAIN EXTENDED SQL 구문
테이블 풀 스캔의 실행 계획- DMBS에 공통적으로 나타나는 부분 : 조작 대상 객체, 객체에 대한 조작의 종류, 조작 대상이 되는 레코드 수- 조작 대상 객체 : 여러 개의 테이블을 사용하는 SQL 구문에서는 어떤 객체를 조작하는지 혼동하지 않게 주의 필요. 테이블 이외에도 인덱스, 파티션, 시퀀스 등 모든 객체가 올 수 있음- 객체에 대한 조작의 종류- 조작 대상이 되는 레코드 수 : Rows라는 항목에 출력됨. 옵티마이저가 실행 계획을 만들 때 설명했던, 카탇로그 매니저로부터 얻은 값.
인덱스 스캔의 실행 계획- 레코드 수가 적으면 인덱스 스캔을 사용하는 것이 효율적
간단한 테이블 결합의 실행 계획 : - 결합 시에는 세 가지 종류의 알고리즘을 사용1) Nested Loops(중첩 반복) : 한쪽 테이블을 읽으면서 레코드 하나마다 결합 조건에 맞는 레코드를 다른 쪽 테이블에서 찾는 방식. 이중 반복으로 구현 됨.2) Sort Merge : 결합 키로 레코드를 정렬하고, 순차적으로 두 개의 테이블을 결합하는 방법. 결합전에 전처리로 정렬을 수행해야 하는데 이때 작업용 메모리로 2강의 '추가적인 메모리연역 워킹 메모리' 에서 다루었던 워킹 메모리 사용.3) Hash : 결합 키 값을 해시값으로 맵핑- 객체에 대한 조작의 종류
5강 실행계획의 중요성
옵티마이저는 완벽하지 않기 때문에 수동으로 실행계획을 수정해야 하는 때도 생긴다
어떤 테이블 설정이 효율적인지 어떤 SQL 구문이 주어졌을 때 어떤 실행계획이 나오는지 예측할 수 있어야 함
- 캐시는 다양한 계층에서 사용되기 때문에 데이터베이스에 메모리를 얼마나 할당할지 판단할 때는 어디까지나 ‘물리 메모리의 범위에서 가능한 많이’가 원칙
2장 SQL 기초
6강 SELECT 구문
(생략)
뷰 생성 (자주 사용하는 SQL 구문을 저장)
CREATE VIEW [뷰 이름\ ([필드이름1], [필드이름2] …) AS
테이블 모습을 한 SELECT 구문
뷰를 지정하지 않고 전체 풀 쿼리를 다 쓰면 서브쿼리
7강 조건 분기, 집합 연산, 윈도우 함수, 갱신
INTERSECT 교집합
EXCEPT 차집합
8강 UNION을 사용한 쓸데없이 긴 표현
UNION보다 CASE가 낫다
CASE WHEN ~~ WHEN ~~
9강 집계와 조건 분기
SUM(CASE WHEN ~~ THEN ~ ELSE ~~ END)
혹은
CASE WHEN COUNT(*) = 1 THEN ~~
WHERE과 HAVING에서는 조건 분기를 안 하는 게 좋다
10강 그래도 UNION이 필요한 경우
1. UNION을 사용할 수밖에 없는 경우
여러 개의 테이블에서 검색한 결과를 머지하는 경우
UNION을 사용하는 것이 성능적으로 더 좋은 경우
UNION을 사용했을 때 좋은 인덱스를 사용하지만 이외의 경우에는 테이블 풀 스캔이 발생하는 경우
인덱스가 WHERE구에서 필드조합을 사용할 때 빠르게 만들어줌
11강 절차 지향형과 선언형
1. 구문 기반과 식 기반
2. 선언형의 세계로 도약
4장 집약과 자르기
12강 집약
1. 여러 개의 레코드를 한 개의 레코드로 집약
GROUP BY로 집약했을 때 SELECT에서 입력할 수 있는 것
- 상수
- GROUP BY 에서 사용한 집약 키
MAX(CASE WHEN ~~ THEN ~~ ELSE ~~)
식으로 MIN,MAX 같은 집약 함수를 사용해야 한다
13강 자르기
GROUP BY : 자르기, 집약 동시 수행
GROUP BY 도 수식 기준으로 자를 수 있음
PARTITION BY에서도 수식 사용 가능
5장 반복문
14강 반복문 의존중
15강 반복계의 공포
16강 SQL에서는 반복을 어떻게 표현할까?
포인트는 CASE 식과 윈도우 함수CASE와 윈도우 함수로 반복을 대신
17강 바이어스의 공죄
6장 결합
18강 기능적 관점으로 구분하는 결합의 종류
크로스 결합 - 모든 결합의 모체수학에서의 데카르트 곱. 가능한 모든 조합을 구하는 연산. 이러한 결과가 필요한 경우가 실무네서 없고 비용이 많이 드는 연산이라 사용되지 않음
내부 결합 - 왜 '내부' 라는 말을 사용할까데카르트 곱의 부분 집합이라는 뜻. 상관 서브 쿼리로 대체 가능
외부 결합 - 왜 '외부' 라는 말을 사용할까
외부 결합과 내부 결합의 차이외부 결합 결과가 크로스 결합 결과의 부분 집합이 아닌 이유는 외부 결합이 마스터 테이블의 정보를 모두 보존하고자 NULL을 생성하기 때문
자기 결합 - '자기'란 누구일까?같은 테이블 혹은 같은 뷰를 사용해 결합연산의 대상으로 무엇을 사용하는지에 따른 분류이기 때문에 자기 결합 + 크로스 결합, 자기 결합 + 외부 결합 같은 조합 가능자기 결합을 수행하는 경우에는 같은 테이블에 별칭을 붙여 각기 다른 테이블인 것처럼 다룸
19강 결합 알고리즘과 성능
결합 알고리즘의 종류
1) nested Loops : 가장 빈번한 알고리즘.
2) Hash : 그 다음 중요한 알고리즘
3) Sort Merge : 위의 두 가지보다 중요성이 떨어짐
데이터 크기, 또는 결합 키의 분산에 따라 옵티마이저가 알고리즘 선택
DBMS에 따라 지원하는 알고리즘이 다름
Nested Loops- Nested Loops의 작동중첩반복을 사용하는 알고리즘결합 대상 테이블(=구동 테이블, 외부 테이블)에서 레코드를 하나씩 반복해가며 스캔함. 구동 테이블의 레코드 하나마다 내부 테이블의 레코드를 하나씩 스캔해서 결합 조건에 맞으면 리턴. 이 과정을 모든 레코드에 반복실행 시간이 레코드 수에 비례한 번의 단계에서 처리하는 레코드 수가 적으므로 다른 알고리즘에 비해 메모리 소비가 적다모든 DBMS에서 지원함구동 테이블이 적을수록 성능이 좋아짐- 구동 테이블의 중요성내부 테이블의 결합 키 필드에 인덱스가 존재하면 DBMS가 내부 테이블을 완전히 순환하지 않아도 되기 때문에 반복을 어느 정도 건너 뛸 수 있음내부 테이블의 결합키 인덱스가 사용된다면 내부 테이블의 반복을 생략할 수 있으므로 Nested Loops가 빨라짐‘구동 테이블이 작은 Nested Loops’ + ‘내부 테이블의 결합키에 인덱스’- Nested Loops의 단점결합키가 내부 테이블에 대해 유일하지 않은 경우 지연이 발생
Hash- Hash의 작동일단 작은 테이블을 스캔하고 결합 키에 해시 함수를 적용해서 해시값으로 변환- Hash의 특징결합 테이블로부터 해시 데이터를 만들어서 사용하므로 Nested Loops에 비해 메모리를 크게 소모함메모리가 부족하면 저장소를 사용하므로 지연 발생출력된ㄴ 해시값은 입력값의 순서를 알지 못하므로 동치 결합에만 사용 가능- Hash가 유용한 경우Nested Loops에 적절한 구동 테이블이 없는 경우내부 테이블에서 히트 되는 레코드 수가 너무 많은 경우Nested Loops의 내부 테이블에 인덱스가 없는 경우
Sort Merge- Sort Merge의 작동결합 대상 테이블들을 각각 결합키로 정렬하고 일치하는 결합 키를 찾으면 결합함- Sort Merge의 특징Hash보다 더 많은 메모리를 소모하기도 함Hash와 다르게 부등호를 사용한 결합에도 사용할 수 있음(부정 조건은 제외)테이블을 정렬하므로 한쪽 테이블을 모두 스캔해야 결합을 완료할 수 있음- Sort Merge가 유용한 경우다른 두 알고리즘을 우선적으로 고려해라
의도하지 않은 크로스 결합
20강 결합이 느리다면
상황에 따른 최적의 결합 알고리즘일단 Nested Loops 잘 안 되면 Hash
실행 계획 제어- DBMS별 실행 계획 제어힌트구를 사용하거나 Mysql 경우는 제어가 불가능
흔들리는 실행 계획
7장 서브쿼리
21강 서브쿼리가 일으키는 폐해
서브쿼리의 문제점- 연산 비용 추가- 데이터 I/O 비용 발생- 최적화를 받을 수 없음쿼리 내용이 정말 서브쿼리를 사용하지 않으면 구현할 수 없는 것인지를 항상 생각해야 함
서브쿼리 의존증- 상관 서브쿼리는 답이 될 수 없다- 윈도우 함수로 결합을 제거
장기적 관점에서의 리스크 관리- 알고리즘 변동 리스크- 환경 요인에 의한 지연 리스크내부 테이블 결합 키에 인덱스가 존재하면 성능이 크게 개선됨
서브쿼리 의존증 - 응용편
서브쿼리는 정말 나쁠까?
22강 서브쿼리 사용이 더 나은 경우
결합과 집약 순서결합과 관련된 쿼리일 때
8장 SQL의 순서
23강 레코드에 순번 붙이기
기본 키가 한 개의 필드일 경우- 윈도우 함수를 사용- 상관 서브쿼리를 사용
기본 키가 여러 개의 필드로 구성되는 경우
24강 레코드에 순번 붙이기 응용
25강 시퀀스 객체, IDENTIFY 필드, 채번 테이블
9장 개신과 데이터 모델
26강 갱신은 효율적으로
27강 레코드에서 필드로의 갱신
28강 필드에서 레코드로 변경
29강 같은 테이블의 다른 레코드로 갱신
INSERT와 UPDATE 어떤 것이 좋을까?UPDATE에 비해 INSERT SELECT가 성능적으로 나음
30강 갱신이 초래하는 트레이드오프
망치라는 도구만을 가진 사람에게는 모든 문제가 못으로 보인다
31강 모델 갱신의 주의점
32강 시야 협착 : 관련 문제
33강 데이터 모델을 지배하는 자가 시스템을 지배한다
10장 인덱스 사용
34강 인덱스와 B-tree
만능형 : B-tree데이터를 트리 구조로 사용대부분은 B+tree 구조 사용루트와 리프의 거리를 가능한 일정하게 유지하려고 함
기타 인덱스비트앱 인덱스 : 데이터를 비트 플래그로 변환해서 저장하는 형태의 인덱스. 갱신할 때 오버헤드가 크다
35강 인덱스를 잘 활용하려면
카디널리티와 선택률필드의 카디널리티와 선택률 : 어떤 필드에 대해 인덱스를 작성할 것인지의 기준카디널리티 : 값의 균형. 유일값이면 높고 모든 값이 동일하면 낮음선택률 : 특정 필드값을 지정했을 때 테이블 전체에서 몇 갱의 레코드가 선택 되는지 나타내는 개념
인덱스를 사용하는 것이 좋은지 판단하려면첫번째는 카디널리티가 높을 것 값이 평균치에서 많이 흩어져 있을수록 좋은 인덱스 후보두번째는 선택률이 낮을 것. 한 번의 선택으로 레코드가 조금만 선택되는 것(5%~10% 이하)
36강 인덱스로 성능 향상이 어려운 경우
압축 조건이 존재하지 않음
레코드를 제대로 압축하지 못하는 경우- 입력 매개변수에 따라 선택률이 변동하는 경우 : 날짜, 점포 등
인덱스를 사용하지 않는 검색 조건- 중간 일치, 후방 일치의 LIKE 연산자 : LIKE를 사용할 경우는 인덱스는 전방일치 밖에 안 됨- 색인 필드로 연산하는 경우- IS NULL을 사용하는 경우- 부정형을 사용하는 경우
37강 인덱스를 사용할 수 없는 경우 대처법
외부 설정으로 처리 - 깊고 어두운 강 건너기- UI 설계로 처리
외부 설정을 사용한 대처 방법의 주의점
데이터 마트로 대처- 특정한 퀔리에서 필요한 데이터만을 저장하는 상대적으로 작은 크기의 테이블
데이터 마트를 채택할 시 주의점- 데이터 신선도 : 데이터 신선도가 중요한 경우 이 방법을 쓰기 어려움- 데이터 마트 크기 : 테이블의 크기를 줄일 수 없다면 데이터 마트를 만들어도 빨라지지 않음- 데이터 마트 수 : 너무 많이 만들면 관리가 어려움- 배치 윈도우
윈도우 온리 스캔으로 대처로우 지향 저장소의 DBMS에 유사적으로 컬럼(필드) 기반 버방소를 실현하는 것
인덱스 온리 스캔의 주의 사항- DBMS에 따라 사용할 수 없는 경우도 있음- 한 개의 인덱스에 포함할 수 있는 필드 수에 제한이 있음- 갱신 오버 헤드가 커진다- 정기적인 인덱스 리빌드가 필요- SQL 구문에 새로운 필드가 추가 된다면 사용할 수 없다
