SQL 레벨업

1장 : DBMS 아키텍처 개요

1장 : DBMS 아키텍처 개요

DBMS 내부의 기능

1. 쿼리 평가 엔진
   → 핵심!
   입력 받은 엔진은 사용자로 부터 입력받은 sql 구문을 분석하고 어떤 순서로 기억장치의 데이터에 접근할지를 결정합니다. 이때 결정 되는 계획을 실행 계획이라 부른다.
   이 책에서 중요하게 이야기하는 성능과도 깊은 관련이 있는 모듈이다.
2. 버퍼 매니저
3. 디스크 용량 매니저
4. 트랜잭션 매니저와 락 매니저
5. 리커버리 매니저

2장 : DBMS의 버퍼

DBMS와 기억장치의 관계

HDD

• 현재 대부분의 dbms는 HDD에 저장한다.
• 2차 기억장치
• 좋은 장점도 나쁜 단점도 없는 매체

메모리

• 디스크에 비해 기억 비용이 비싸ㅈ다.
• 데이터 베이스 내부 데이터를 모두 메모리에 올리는것을 불가능 하다.

버퍼를 활용한 속도 향상

• DBMS가 일부라도 데이터를 메모리에 올려 성능향상을 한다.
• 자주 사용하는 데이터는 메모리에 올려 빠르게 접근한다.
• 메모리에 데이터를 저장해 디스크의 접근을 줄이고 이렇게 하여 I/O 시간을 줄인다.
• 이렇게 성능향상을 목적으로 데이터를 저장하는 메모리를 버퍼, 캐시라고 부른다.
• 이 책에서는 버퍼 == 캐시로 사용한다.

메모리의 두개의 버퍼

데이터를 유지하기 위해 사용하는 메모리 종류

• 데이터 캐시
  • 디스크에 있는 데이터의 일부를 메모리에 유지하기 위해 사용하는메모리 영역
  • 만약 데이터 캐시에 데이터가 저장되어있다면 디스크에 접근하지 않아도 된다
  • 데이터 베이스 세계에는 ‘디스크를 건드리는 자는 불행해진다’ 라는 오래된 격언이 있다.
• 로그버퍼
  • 갱신처리와 (insert, delete, update, merge)와 관련이 있다.
  • dbms는 갱신과 관련된 sql 구문을 사용자로부터 받으면 바로 데이터를 변경하지 않고 로그버퍼위에 변경정보를 보내고 이후 디스크 변경을 수행한다.
    • 왜 이렇게 하는가
      ⇒ 성능을 높이기 위해서
      저장소는 검색 뿐만 아니라 갱신을 할때도 상당한 시간이 소모된다. 따라서 한 번 메모리에 갱신정보를 받은 시점에 사용자에게는 sql 구문의 실행이 끝났다 하고 내부적으로 관련된 일을 처리한다.
  • 데이터 베이스의 갱신처리는 sql구문의 실행 시점과 저장소에 갱신하는 시점에 차이가 있는 비동기 처리이다.

하지만 결국 DBMS는 저장소의 느림을 어떻게 보완할것인가 라는 것을 계속해서 고민해온 미들웨어 이다.

만약 빨랐다면 이런 귀찮은 구조를 사용하지 않아도 됐을것이다.

메모리 사용시의 트레이드 오프

메모리 사용시의 추가적인 단점

휘발성

영속적이지 않다.

미래에 메모리가 싸져도 대체할 수 없다.

가장 큰 단점들

• 장애 발생시 메모리에 있던 데이터가 모두 사라져버려 데이터 부정합을 발생 시킨다. 하지만 실제 데이터는 디스크에 있어서 다시 읽어들이면 된다. 따라서 휘발성이 중요하다.
• 하지만 로그파일은 메모링 위에 있다 이때문에 문제가 발생할 수도 있다.
  • 이를 해결하기 위해 DBMS는 커밋시점에 갱신정보를 로그파일에 씀으로써 장애가 발생해도 정합성을 유지할 수 있게한다. 커밋이란 갱신처리를 확정한다. DBMS는 커밋된 데이터를 영속화 한다.
  • 반대로 말하면 커밋때는 반드시 디스크에 동기 접근이 일어나고 결국 여기서 지연이 발생할 가능성이 있다.
    • 동기처리 : 정합성 O, 성능 X
    • 비동기처리 : 정합성 X, 성능 O

로그버퍼의 사이즈가 왜 더 작을까?

• 더 작아서 문제가 될지는 성능검증을 해보기 전까지는 알 수가 없다
• 보통 DB는 기본적으로 검색을 메인으로 처리한다고 가정한다.
• 갱신처리에 값 비싼 메모리를 많이 사용하는것보다는 자주 검색하는 데이터를 캐시에 올리는게 더 좋다고 생각하는것
• 실제 많은 DBMS가 물리메모리에 여유가 있다면 데이터 캐시를 많이 할당할것을 추천한다.
• 만약 갱신이 더 많은 시스템의 경우는 튜닝을 통해서 로그버퍼의 크기를 키우는 등의 역할을 해야한다.

검색 vs 갱신

적절한 판단을 통해 튜닝해야한다.

만약 로그 버퍼가 크게 잡혀있다면 갱신처리와 관련해 큰 부하가 걸릴것을 고려한 설계임을 알 수 있고 캐시버퍼가 크게 되어있다면 검색 처리와 관련된 처리가 중심이라는것을 알 수 있다.

워킹 메모리

앞선 2개의 메모리 영역외의 일반적 메모리 영역을 하나 더 가지고 있다.

정렬 또는 해시 관련 처리에 사용되는 영역으로 워킹메모리라 부르며 정렬은 order by 구, 집합 연산, 윈도우 함수 등에 사용되면 해시는 테이블등의 결합에서 해시 결합이 사용되는 때 실행됩니다.

이 영역이 중요한 이유는 만약 이 영역이 다루려는 데이터양보다 작아 부족해지는 경우가 생기면 대부분의 DBMS가 저장소를 사용하기 떄문이다. (메모리가 부족하면 디스크를 사용)

→ 부족하면 무슨일이 일어날까?

속도가 느려진다.(갑자기 느려지는 순간적인 변화가 일어난다)

여러개의 sql 구문을 동시에 실행하면 메모리가 넘칠 수 있다. 부하검사를 진행하여 메모리 부족이 있는지 판단하기 전까지는 알기 힘들다.

DBMS는 메모리가 부족하다고 해서 죽지는 않는다 어떻게든 하려 한다.

3장 DBMS와 실행 계획

sql 언어의 특징

절차적 언어는 데이터를 어떻게 접근할지 HOW를 책임지고 기술한다.

비절착적 언어인 RDB는 WHAT을 기술하는것으로 축소되었다.

→ 왜 ? 비즈니스 전체의 생산성 향상을 위해서

→ 하지만 성능문제를 고민하여 내부절차를 확인해야한다.

데이터 베이스 접근방식

파서

쿼리를 파싱한다.

데이터 베이스의 이름, 컬럼과 같은 처음에 잘못된 오류를 미리 걸러낸다.

sql구문을 정형적인 형식으로 변환해준다.

옵티 마이저

접근법을 최적화한다.

실행 계획을 작성하고, 비용을 연산한다.

카탈로그 매니저

DBMS의 내부정보를 모아놓은 테이블들로 테이블또는 인덱스의 통계정보가 저장되어있다.

데이터 베이스 엔지니어가 항상 신경써줘야 하는 부분

카탈로그 내에는 테이블의 레코드수, 필드수 필드크기 , 필드의 카디널리티, 분포정도, NULL수, 인덱스 정보등을 가직 있고 옵티마이저가 실행계획을 작성한다.

이 때문에 카탈로그의 내용이 갱신되지 못한 경우는 이전의 정보를 바탕으로 최적의 계획을 세워 최적의 플랜을 기대할 수 없다.

이 때문에 올바른 통계정보가 모이는것은 중요하다. 수동갱신 뿐만 아니라 데이터를 크게 갱신하는 배치 처리가 있는경우 JobNet을 조합하는 경우도 많다.

플랜평가

성능이 좋지 않은 SQL 구문이 있을때 실행계획을 읽고 수정방안등을 고려할 수 있다.

4장 : 실행 계획이 SQL 구문의 성능을 결정

데이터 양이 많은 테이블에 접근하거나 복잡한 SQL 구문을 실행하면 반응 지연이 발생하는 경우가 있다.

5장 : 실행 계획 확인 방법

각각의 명령어를 통해 확인할 수 있다.

postgresql → EXPLAIN , Oracle → set autotrace traceonly

• 실행 계획에 포함되는 내용
  • 조작 대상 객체
  • 객체에 대한 조작 종류
  • 조작 대상이 되는 레코드 수

실행 계획에 포함된 실행시간, 실행비용은 절대 평가의 지표로 쓰는것은 곤란하다. 추정값은 절대 지표로 사용하면 안된다. 구현에 따라서 실제 실행 시간을 출력하는 경우도 있다. (메뉴얼 참고)

예시

Select * 
from shops
where shot_id = '00050';

• 조작 대상 레코드 수
  • 1개 , where에서 지정해서 접근대상은 반드시 하나의 레코드
• 접근대상 객체와 조작
  • 인덱스 스캔 : 모집합 레코드 수에서 선택되는 레코드 수가 적다면 풀스캔 보다 빠르게 접근을 수행한다. 풀스캔은 데이터 양에 비례해서 처리비용이 늘어나지만 인덱스 사용시에는 B-tree가 모집합의 데이터양에 비해 대수함수적 으로 처리비용이 늘어나기 때문이다.

테이블 결합의 실행계획

• nested loops
  • 한 쪽 테이블을 읽으면서 레코드 하나마다 결합 조건에 맞는 레코드를 다른 쪽 테이블에서 찾는 방식이다. 절차지향적 구현시 이중반복 이여서 중첩반복 이라는 이름이 붙었다.
• sort merge
  • 결합 키(ID)로 레코드를 정렬하고 순차적으로 두개의 테이블을 결합한다.
  • 결합 전 전처리로 정렬을 수행하고 워킹메모리에서 다루었던 워킹메모리를 사용한다.
• Hash
  • 결합 키값을 해시값으로 맵핑하는 방법, 작업용 메모리 영역을 사용한다.

실행계획을 읽는법

트리구조, 중첩단계가 깁을 수록 먼저 실행된다. 위에서 아래로 실행된다.

실행 계획의 중요성

최근의 옵티마이저 성능은 우수하다.

하지만 복잡한 문제를 옵티마이적에게 정보를 제대로 주지 못하는 문제가 발생하는경우와 같은 상황에서는 성능이 좋지 않다.

이런경우 수동으로 실행계획을 변경할 수 있다.

💡 컬럼

DBMS의 Sql Server는 메모리를 자동으로 조정한다. 윈도우와 SqlServer를 같은 벤더(마이크로소프트)가 개발해서, 두 개를 밀접하게 연계할 수 있기 때문이다.

2장 : SQL 기초

6장 : Select 구문

검색 == 질의 == query

Select 구

어떤 방법으로 선택할지 일절 쓰여 있지 않다. 이 방법은 DBMS에게 맡긴다.

사용자는 단지 어떤 데이터가 필요한지만 알린다.

💡 NULL 불명한 데이터를 `공란` 으로 취급한다.

From 구

from [테이블 이름]

테이블 이름이 적히지 않아도 되는경우는 select 1 과 같은 상수 조회의 경우이다.

Where 구

추가적인 조건을 지정할 수 있는 방법이다.

~라는 경우를 나타내는 관계 부사 이다.

사용 연산자

연산자	의미
' = '	~와 같음
' <> '	~와 같지 않음
' >= '	~이상
' > '	~보다 큼

SELECT name, adderss, age
From address
Where address = '서울시' AND age >= 30; -- or도 가능함

IN, OR 조건 간단하게 작성

-- 성별이 '남성'이거나 '여성'인 사용자 검색 (IN 사용)
SELECT * FROM 사용자
WHERE 성별 IN ('남성', '여성');

-- 성별이 '남성'이거나 '여성'인 사용자 검색 (IN 사용)
SELECT * FROM 사용자 WHERE 성별 = '남성' OR 성별 = '여성';

null조회 예시

-- 사용자 테이블 예제 (이름과 이메일은 NULL 허용)
CREATE TABLE 사용자 (
    사용자_ID INT PRIMARY KEY,
    이름 VARCHAR(50),
    이메일 VARCHAR(100)
);

-- 사용자 데이터 삽입 (일부 사용자는 이름 또는 이메일이 NULL)
INSERT INTO 사용자 VALUES
(1, '홍길동', 'hong@example.com'),
(2, '김철수', NULL),
(3, NULL, 'lee@example.com'),
(4, '박미영', NULL),
(5, '정기석', 'jung@example.com');

-- 이름이 NULL인 사용자 검색
SELECT * FROM 사용자
WHERE 이름 IS NULL;

-- 이메일이 NULL인 사용자 검색
SELECT * FROM 사용자
WHERE 이메일 IS NULL;

-- 이름 또는 이메일 중 하나라도 NULL인 사용자 검색
SELECT * FROM 사용자
WHERE 이름 IS NULL OR 이메일 IS NULL;

💡 SELECT 구문은 절차 지향형 언어의 함수

SELECT구문은 일종의 읽기 전용의 함수
입력과 출력 자료형 : 테이블(관계) 이외에는 어떤 자료형도 없다 → 관계가 닫혀있다는 의미의 미로 폐쇄성이라 한다.

Group by

데이터

-- 주문 테이블 예제
CREATE TABLE 주문 (
    주문_ID INT PRIMARY KEY,
    사용자_ID INT,
    금액 DECIMAL(10, 2),
    주문일 DATE
);

-- 주문 데이터 삽입
INSERT INTO 주문 VALUES
(1, 1, 100.50, '2022-01-05'),
(2, 2, 75.20, '2022-01-08'),
(3, 1, 50.00, '2022-01-10'),
(4, 3, 120.75, '2022-01-12'),
(5, 2, 30.90, '2022-01-15');

-- 사용자별 주문 금액의 합계를 조회
SELECT 사용자_ID, SUM(금액) AS 총주문금액
FROM 주문
GROUP BY 사용자_ID;

-- 주문일별 주문 건수를 조회
SELECT 주문일, COUNT(*) AS 주문건수
FROM 주문
GROUP BY 주문일;

-- 사용자별 주문 금액의 평균을 조회하되, 평균이 70보다 큰 경우만 출력
SELECT 사용자_ID, AVG(금액) AS 평균주문금액
FROM 주문
GROUP BY 사용자_ID
HAVING 평균주문금액 > 70;

이러한 그룹을 통해서 집계가 가능하다.

1. COUNT(): 그룹 내에서 행의 수를 계산합니다.
   sql SELECT 부서, COUNT(*) AS 직원수 FROM 직원 GROUP BY 부서;

   부서	직원수
   인사	3
   영업	2
   개발	4

2. SUM(): 그룹 내에서 특정 열의 합을 계산합니다.

   부서	총급여
   인사	20000
   영업	15000
   개발	30000

3. SELECT 부서, SUM(급여) AS 총급여 FROM 직원 GROUP BY 부서;
4. AVG(): 그룹 내에서 특정 열의 평균을 계산합니다.

   부서	평균평점
   영업	4.2
   개발	4.6

5. SELECT 부서, AVG(평점) AS 평균평점 FROM 프로젝트 GROUP BY 부서;
6. MIN(): 그룹 내에서 특정 열의 최소값을 계산합니다.

   부서	최소나이
   인사	25
   영업	28
   개발	30

7. SELECT 부서, MIN(나이) AS 최소나이 FROM 직원 GROUP BY 부서;
8. MAX(): 그룹 내에서 특정 열의 최대값을 계산합니다.
9. SELECT 부서, MAX(경력년수) AS 최대경력 FROM 직원 GROUP BY 부서;

부서	최대경력
인사	8
영업	6
개발	10

Group by()의 ()는 키를 지정하지 않는다는 뜻이다.

보통은 생략할 수 있어 생략하지만 일반적인 코드를 볼때 Group by()이 있다고 생각하고 보는것이 좋다.

⇒ GROUP BY () 자르는 기준이 없다 라고 보는것이 좋다. (일부 DBMS 는 지원하지 않는다)

Having 구

where구가 레코드에 조건을 지정한다면 having은 집합에 조건을 지정하는 기능이다.

-- 부서별로 평균 연봉이 50000 이상인 부서를 찾는 쿼리
SELECT 부서, AVG(연봉) AS 평균연봉
FROM 직원
GROUP BY 부서
HAVING 평균연봉 >= 50000;

ORDER BY 구

select만 하는 경우는 순서가 없이 엉터리로 출력한다. (DBMS마다 다르다)

따라서 명시적으로 순서를 지정해줘야한다.

-- 사용자 테이블을 부서명으로 오름차순으로 정렬하는 쿼리
SELECT *
FROM 사용자
ORDER BY 부서 ASC;

-- 부서별로 평균 연령이 30세 이상이고, 부서명으로 내림차순으로 정렬하는 쿼리
SELECT 부서, AVG(나이) AS 평균연령
FROM 사용자
GROUP BY 부서
HAVING 평균연령 >= 30
ORDER BY 부서 DESC;

뷰와 서브쿼리

자주 사용하는 SELECT구문은 텍스트 파일에 따로 저장해 둬도 좋을것이다. 이렇게 SELECT구문을 데이터베이스 안에 저장하는것이 뷰이다.

하지만 테이블과 달리 내부에 데이터를 보유하지는 않고 뷰는 단순히 SELECT구문을 저장한것 뿐이다.

뷰 생성 방법

CREATE VIEW [뷰이름]([필드이름1],[필드이름2] ...) AS 

-- 뷰 생성: 연봉이 70000 이상인 사용자만을 조회하는 뷰
CREATE VIEW 뷰_고연봉사용자 AS
SELECT * FROM 사용자
WHERE 연봉 >= 70000;

-- 생성한 뷰 조회
SELECT 연봉 FROM 뷰_고연봉사용자;

익명 뷰

-- 익명 뷰를 사용한 조회: 연봉이 70000 이상인 사용자만을 조회
SELECT * FROM (
    SELECT * FROM 사용자
    WHERE 연봉 >= 70000
) AS 익명뷰;

-- 명명된 뷰를 정의하기보다는 즉석에서 필요한 일회성 작업에 유용

From 구에 직접 지정하는 SELECT 구문을 서브쿼리 라고 부른다.

서브쿼리를 매개변수로 사용하는 예제 코드

-- 부서별로 평균 연봉이 높은 사용자 조회
SELECT *
FROM 사용자
WHERE 연봉 > (
    SELECT AVG(연봉)
    FROM 사용자
    GROUP BY 부서
    HAVING 부서 = '개발'
);

7장 : 조건 분기, 집합 연산, 윈도우 함수, 갱신

CASE

작동은 switch 조건문과 거의 유사하다.

when 구의 평가식 부터 평가되고 조건이 맞으면 THEN 구에 지정식이 리턴되면 CASE 식 전체가 종료된다.

만약 조건이 맞지 않으면 다음 WHEN구로이동해 같은 처리를 반복한다.

단순 CASE ≤ 검색 CASE 이다.

SELECT 
    이름,
    CASE 
        WHEN 소속도 LIKE '서울%' THEN '수도권'
        WHEN 소속도 LIKE '경기%' THEN '수도권'
        WHEN 소속도 LIKE '인천%' THEN '수도권'
        WHEN 소속도 LIKE '부산%' THEN '경상도'
        WHEN 소속도 LIKE '대구%' THEN '경상도'
        WHEN 소속도 LIKE '광주%' THEN '전라도'
        WHEN 소속도 LIKE '대전%' THEN '충청도'
        WHEN 소속도 LIKE '울산%' THEN '경상도'
        ELSE '기타'
    END AS 대규모지역
FROM 기도자;

이 코드는 일종의 교환 코드

CASE는 SELECT, WHERE, GROUP BY, HAVING, ORDER BY구와 같은 어디에서나 사용가능하다.

UNION

단순 union ⇒ 중복된 레코드가 생략된다.

SELECT *
FROM Address 
UNION
SELECT *
FROM ADddress2 

만약 중복제거를 원하지 않는다.

SELECT *
FROM Address 
UNION ALL
SELECT *
FROM ADddress2 

INTERSECT

교집합을 구한다. 중복된것은 제거된다.

SELECT *
FROM Address 
INTERSECT
SELECT *
FROM Address2

EXCEPT

차집합을 구한다, 순서가 중요하다.

SELECT *
FROM Address
EXCEPT 
SELECT *
FROM Address2

윈도우 함수

집약 기능이 없는 GROUP BY구 이다.

GROUP BY가 자르기 + 집약이였다면

윈도우 함수는 자르기 기능만 있다.

윈도우 함수는 주로 윈도우(범위) 내에서 행 간의 계산을 수행하는 데 사용됩니다.

예를 들어, 각 행에 대해 이전 행들과의 비교, 누적 합산 등을 계산할 때 유용합니다.

PARTITION BY라는 구로 수행된다.

SELECT 
    사용자_ID,
    이름,
    부서,
    급여,
    AVG(급여) OVER (PARTITION BY 부서) AS 부서별평균급여
FROM 사용자;

-- 윈도우 함수를 사용하여 각 부서에서 사용자 수를 계산하는 예제 (비현실적인 사용) -> 주로 group by

SELECT
    department,
    COUNT(*) OVER (PARTITION BY department) AS "Number of Users by Department"
FROM users;

-- 윈도우 함수를 사용하여 각 부서에서 총 급여를 계산하는 예제 (비현실적인 사용) -> 주로 group by
SELECT
    department,
    SUM(salary) OVER (PARTITION BY department) AS "Total Salary by Department"
FROM users;

-- RANK()를 사용하여 급여 순위를 매기는 쿼리
-- 1,2,3,4,5 (동일한것에 대해 동일 급여를 매기고 등수를 건너뛴다 1,2,2,4,4)
SELECT
    이름,
    급여,
    RANK() OVER (ORDER BY 급여 DESC) AS 급여순위
FROM 사용자;

-- ROW_NUMBER()를 사용하여 급여에 따라 행 번호를 부여하는 쿼리
-- 1,2,3,4,5 (동일한것에 대해 유일한 순서를 매긴다. 동일 값이여도 고유한 순서를 매긴다 1,2,3,4,5)
SELECT
    이름,
    급여,
    ROW_NUMBER() OVER (ORDER BY 급여 DESC) AS 행번호
FROM 사용자;

-- DENSE_RANK()를 사용하여 급여 순위를 밀집순위로 매기는 쿼리 
-- 1,2,3,4,5 (동일한것에 대해 동일 급여를 매기고 등수를 건너뛰지 않는다 1,2,2,3,3과 같이 한다
SELECT
    이름,
    급여,
    DENSE_RANK() OVER (ORDER BY 급여 DESC) AS 밀집순위
FROM 사용자;

트랜잭션과 갱신

SQL은 쿼리를 위한 언어이기에 데이터를 갱신하는것은 부가적인 기능이다.

insert(삽입)

값은 순서대로 제공해야한다. (데이터 형식등의 오류가 발생할 수 있다)

NULL을 삽입하는 경우 그대로 NULL을 입력한다. (따옴표로 감싸면 문자열로 인식된다)

INSERT INTO 테이블명 (열1, 열2, ...)
VALUES (값1, 값2, ...);

여러개의 줄을 함꺼번에 입력하는 기능을 지원하기도 한다( multi-row insert)

-- 사용자 테이블에 여러 사용자를 동시에 삽입하는 예제
INSERT INTO 사용자 (이름, 부서, 급여)
VALUES
    ('김철수', '영업', 70000),
    ('이영희', '인사', 75000),
    ('박미영', '영업', 82000);

모든 DBMS를 지원하지는 않을 수 있다.

오류가 발생했을때 찾기가 어렵다.

delete(제거)

DELETE FROM 테이블명
WHERE 조건;

DELETE [특정 필드] FROM [테이블 이름] --오류가 발생한다. 특정 필드만 제거할 수 없다.
DELETE * FROM [테이블 이름] -- 오류 발생
-- 특정 필드만 지우고 싶다면 UPDATE사용
-- 테이블 이라는 상자는 남아있으므로 계속해서 데이터는 사용할 수 있다. 

update(갱신)

등록된 데이터를 변경

-- 두 개의 필드를 별도로 나열하여 업데이트하는 예제
UPDATE 테이블명
SET 열1 = 값1, 열2 = 값2
WHERE 조건;

-- 괄호로 묶어서 필드를 나열하여 업데이트하는 예제
UPDATE 테이블명
SET (열1, 열2) = (값1, 값2)
WHERE 조건;

3장. SQL의 조건 분기

8장 : UNION을 사용한 쓸데없이 긴 표현

union은 성능적인 측명에서 큰 단점을 가지고 있다. 내부적으로는 여러개의 select 구분을 실행하는 것으로 해석된다.

union시 table access full이 발생하여 읽어들이는 테이블의 크기에 따라 성능이 성현으로 증가하게 된다. 테이블의 크기가 커지게 되면 캐시 히트율도 낮아져 더욱 성능의 차이가 크게 나타난다.

SELECT
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    WHEN score >= 70 THEN 'C'
    ELSE 'D'
  END AS grade
FROM students;

조건분기를 where 구로만 하는 사람들은 초보자다. 잘 하는 사람은 select 구 만으로 조건 분기를 한다.

sql의 성능이 좋은지 나쁜디는 반드시 실행 계획 레벨에서 판단해야한다.

union을 사용한 분기는 select 구문을 기본단위로 분기하고 있다.

반면 case 식을 사용한 분기는 문자 그래도 ‘식’을 바탕으로 하는 사고 이다. ⇒ SQL을 마스터 하는 열쇠중 하나이다.

⇒ 이런 경우 문제를 절차지향으로 해결한다면 어떤 if로 해결할 수 있을까 보다 이것을 sql의 case로는 어떻게 해결할 수 있지? 가 더 적합하다.

9장 집계와 조건분기

지역별 남녀 인구를 각 지역별로 여성과 남성의 인구를 구하여라

1. 집계 대상으로 조건분기

개선 전 :

SELECT prefecture, SUM(pop_mem) AS pop_men, SUM(pop_wom) AS pop_wom
FROM ( SELECT prefecture, pop AS pop_mem, null AS pop_wom
                FROM Population
                WHERE sex = '1'
                UNION 
            SELECT prefecture, pop AS pop_mem, null AS pop_wom
                FROM Population
                WHERE sex = '2') TMP 
GROUP BY prefecture;

개선 후 :

SELECT prefecture,
                sum(CASE WHEN sex ='1' THEN pop ELSE 0 END) AS pop_mem,
                sum(CASE WHEN sex ='2' THEN pop ELSE 0 END) AS pop_wom
FROM population
GROUP BY prefecture;

2. 집약 결과로 조건 분기

테이블에 emp_id team_id emp_name team이 있는 테이블이 있어 여기서 1개의 팀에 속한 사람은 속한 팀의 이름을 출력하고 팀이 2개 인 경우에는 2개를 겸무 라고 출력하고 3개 이상인 경우네는 3개 이상을 겸무 라고 출력하는 경우

개선 전 :

SELECT
  emp_name,MAX(team) AS team
    FROM Employees 
    GROUP BY emp_name
    HAVING COUNT(*) = 1 
UNION

SELECT
      emp_name, '2개를 겸무' AS team
    FROM Employees 
    GROUP BY emp_name
    HAVING COUNT(*) = 2 
UNION
SELECT
      emp_name,'3개 이상을 겸무' AS team
    FROM Employees 
    GROUP BY emp_name
    HAVING COUNT(*) >= 3 

개선 후 :

SELECT
  emp_id,
  emp_name,
  CASE
    WHEN COUNT(*) = 1 THEN team
    WHEN COUNT(*) = 2 THEN '2개를 겸무'
    WHEN COUNT(*) >= 3 '3개 이상을 겸무'
  END AS team_status
FROM
  employees
GROUP BY
  emp_name

10장 그래도 UNION을 필요한 이유

1. union을 사용할 수 밖에 없는 경우
   1. 머지 대상이 되는 select 구문들에서 사용하는 테이블이 다른 경우
2. UNION을 사용하는 것이 성능적으로 더 좋은경우
   1. uniona . 인덱스를 추가
   2. CREATE INDEX IDX_1 ON ThreeElements(date_1, flg_1); CREATE INDEX IDX_2 ON ThreeElements(date_2, flg_2); CREATE INDEX IDX_3 ON ThreeElements(date_3, flg_3);
   3. SELECT key, name, date_1,flg_1, date_2,flg_2, date_3,flg_3 FROM threeElements WHERE date_1 = '2013-11-01' AND flg_1 = 'T' UNION SELECT key, name, date_1,flg_1, date_2,flg_2, date_3,flg_3 FROM threeElements WHERE date_2 = '2013-11-01' AND flg_2 = 'T' UNION SELECT key, name, date_1,flg_1, date_2,flg_2, date_3,flg_3 FROM threeElements WHERE date_3 = '2013-11-01' AND flg_3 = 'T'
   4. or ⇒ 인덱스 스캔이 사용되지 않는다
   5. SELECT key, name, date_1, flg_1, date_2, flg_2, date_3, flg_3 FROM threeElements WHERE (date_1 = '2013-11-01' AND flg_1 = 'T') OR (date_2 = '2013-11-01' AND flg_2 = 'T') OR (date_3 = '2013-11-01' AND flg_3 = 'T')
   6. in
   7. SELECT key, name, date_1, flg_1, date_2, flg_2, date_3, flg_3 FROM threeElements WHERE ('2013-11-01', 'T') IN ((date_1, flg_1), (date_2, flg_2), (date_3, flg_3));
   8. case
   9. SELECT key, name, date_1,flg_1, date_2,flg_2, date_3,flg_3 FROM threeElements WHERE CASE WHEN date_1 = '2013-11-01' THEN flg_1 WHEN date_2 = '2013-11-01' THEN flg_2 WHEN date_3 = '2013-11-01' THEN flg_3 ELSE NULL END = 'T';
3. 테이블에 key, name, date_1 , flg_1, date_2 , flg_2, date_3 , flg_3 중에 flg가 T인 것들에 대한 날짜들만 출력하는 쿼리 작성하는법

3회의 인덱스 스캔 ( union ) vs 1회의 테이블 풀스캔 (or ,in ,case)

하지만 테이블이 크고 where 조건으로 선택되는 레코드의 수가 충분히 작다면 UNION이 더 빠릅니다. 따라서 UNION을 사용하는 경우가 더 빠를 수도 있다.

11장 절차 지향형과 선언형

SQL 초보자는 절차지향 세계에서 살고 있다 + 구문

SQL 중급자 선언적인 세계 + 식

4장 : 집약과 자르기

12장 집약

sql에는 5개의 집약 함수가 있다.

1. count
2. sum
3. avg
4. max
5. min

여러개의 레코드를 한 개의 레코드로 집약

Group BY구로 집약했을때 SELCET 구에 입력할 수 있는것

• 상수
• Group BY 구에서 사용한 집약 키
• 집약 함수

아래는 여러개의 레코드를 한개의 레코드로 집약한다는것을 잘 보여준 예제이다.

SELECT id,
    MAX(CASE WHEN data_type ='A' THEN data_1 ELSE NULL END) AS data_1,
    MAX(CASE WHEN data_type ='A' THEN data_2 ELSE NULL END) AS data_2,
    MAX(CASE WHEN data_type ='B' THEN data_3 ELSE NULL END) AS data_3,
    MAX(CASE WHEN data_type ='B' THEN data_4 ELSE NULL END) AS data_4,
    MAX(CASE WHEN data_type ='C' THEN data_5 ELSE NULL END) AS data_5,
    MAX(CASE WHEN data_type ='C' THEN data_6 ELSE NULL END) AS data_6
FROM NonAggTbl
GROUP BY id;

이런 집약 쿼리의 실행계회은 어떻게 될까?

스캔 → group by 집약 (해시 알고리즘)

group by는 해시 or 정렬이지만 요즘에는 해시알고리즘을 많이 사용한다.

정렬 또는 해시를 위해 PGA라는 메모리 영역을 사용한다. 이때 PGA 크기가 집약 대상 데이터 양에 비해 부족하면 일시 영역(저장소)를 사용해 부족한 만큼 채운다. 이걸 Temp 탈락 이라고 한다. 해당 현상이 발생화면 성능이 극단적으로 떨어진다. 따라서 연산 대상 레코드 수가 많은 GROUP BY구를 사용하는 SQL에서는 충분한 성능검증을 진행해야 한다. 최악의 경우 SQL이 비정상적으로 종료할 수 있다.

합쳐서 하나

여러개의 레코드로 한 개의 범위를 커버

모든 연령 범위의 사람들에게 제공할 수 있는 제품

SELECT product_id
FROM priceByAge
GROUP BY product_id
HAVING SUM(high_age - low_age + 1) = 101;

여러 레코드에서 운영된 날을 연산

SELECT room_ngr,
        SUM(end_date -start_date) AS working_days
FROM HoterRooms
GROUP BY room_nbr
HAVING SUM(end_date - start_date) >= 10;

13장 자르기

자르기와 파티션

첫 문자 알파벳마다 몇 명의 사람이 존재하는지 계산

SELECT SUBSTRING(name, 1, 1)AS label,
        COUNT(*)
    FROM Persons
GROUP BY SUBSTRING(name, 1, 1);

label	COUNT(*)
A	4
B	2
J	3
M	1

이렇게 Group BY 구로 잘라 만든 하나하나의 부분 집합을 수학적으로 ‘파티션’이라고 부릅니다.

자르기의 기준이 되는 키를 GROUP BY 구와 SELECT 구 모두에 입력하는것이 포인트이다.

어린이, 성인, 노인 분리 계산

SELCET CASE WHEN age<20 THEN '어린이'
                    WHEN age BETWEEN 20 AND 69 THEN '성인'
                    WHEN age>= 70 THEN '노인'
                    ELSE NULL END AS age_class
            COUNT(*)
FROM PERSONS 
GROUP BY CASE WHEN age<20 THEN '어린이'
                    WHEN age BETWEEN 20 AND 69 THEN '성인'
                    WHEN age>= 70 THEN '노인'
                    ELSE NULL END AS age_class

Partition by 구를 사용한 자르기

SELECT name,
        age,
        CASE WHEN age<20 THEN '어린이'
                    WHEN age BETWEEN 20 AND 69 THEN '성인'
                    WHEN age>= 70 THEN '노인'
                    ELSE NULL END AS age_class
        RANk() OVER(PARTITION BY  CASE WHEN age<20 THEN '어린이'
                        WHEN age BETWEEN 20 AND 69 THEN '성인'
                        WHEN age>= 70 THEN '노인'
                        ELSE NULL END
        ORDER BY age) AS age_rank_in_class
FROM persons
ORDER BY age_class, age_rank_in_class 

groupby 와 partition by구에는 사실 집약 이라는 기능을 제외하면 차이가 없다.

group by 구는 입력 집합을 집약하므로 전혀 다른 레벨의 출력으로 변환하지만 partition by 구는 입력에 정보를 추가할 뿐이므로 원본 테이블 정보를 완전히 그대로 유지합니다 .

group by 구가 식을 매개변수로 받는 이상 partition by 구 또한 마찬가지라는 것은 논리적으로 아무 문제 없는 결론이다.

5장 : 반복문

15장 반복계의 공포

반복계의 단점 : 성능

처리해야하는 레코드 수가 많아질 수록 성능 차이가 벌어진다.

반복계는 포장게에 성능적으로 질 수밖에 없다.

그럴수 밖에 없는 이유

1. SQL 실행의 오버헤드
   1. 전처리
      1. SQL구문을 네트워크로 전송
      2. 데이터 베이스 연결
      3. SQL 구문 파스
      4. SQL 구문의 실행 계획 생성, 평가
   2. 후처리
      1. 결과 집합을 네트워크로 전송⇒ 이런 이유로 비용이 얼마나 들어갈지 몰라 작은것 100개보단 큰거 1개를 선호한다.
2. 병렬 분산이 힘들다 .
   1. 반복계는 리소스 사용 효율이 나쁘다.
3. 데이터 베이스의 진화로 인한 혜택을 받을 수 없다.
   1. DBMS의 버전이 올르 수록 옵티마이저는 보다 효율적으로 실행 계획을 세우고, 데이터에 고속으로 접근할 수 있는 아키텍처를 구현한다.

포장계의 SQL은 반복계에 비해 복잡하다.

하지만 포장계의 SQL구문은 튜닝 가능성이 굉장히 높으므로 제대로 튜닝한다면 처음과 비교하여 현격한 성능 차이가 있을것입니다.

하지만 반복계는 느리고 튜닝가능성도 거의 없다 ( 치명적 단점 )

반복계를 빠르게 만드는 법

• 반복계를 포장계로 다시 작성
  • 하지만 실제 해당 방법을 사용하는것은 어렵다.
• 각각의 SQL을 빠르게 수정
  • 튜닝 가능성이 제한된다.
• 다중화 처리
  • 가장 희망적 선택지
  • CPU또는 디스크 같은 리소스에 여유가 있고 처리를 나눌 수 있는 키가 명확하다면 처리를 다중화 해서 성능을 선형에 가깝게 스케일 할 수 있습니다.

반복계의 장점

1. 실행 계획의 안정성
   1. 실행 계획이 단순하다 == 핻당 실행 계회의 변동성이 거의 없ㄷ.
2. 예상 처리시간의 정밀도
   1. 처리시간 = 한번의 실행 시간 * 실행 횟수
3. 트랜잭션 제어가 편리
   1. 트랜잭션의 정밀도를 세세하게 제어할 수 있다.
   2. 중간까지 실행한 이후에 이어서 할 수 있다.(문제 발생 이후부터 다시 실행 가능, 포장계는 불가능)

16장 SQL에서는 반복문을 어떻게 표현할까

(코드 위주, 책 참고)

1. 포인트는 CASE식과 윈도우 함수

2. 최대 반복 횟수가 정해진 경우

코드 5-8은 FROM 절 내부 부터 설명하겠습니다.
첫 번째 CASE WHEN에서 pcode에 따른 hit_code를 계산합니다. 이후 윈도우 함수중 MIN함수를 통해 hit_code값들중의 최소값을 찾습니다. 이후 over 절에서 hit_code를 순서로 정렬하여 윈도우를 생성합니다.
이렇게 한 이후 외부 쿼리에 의해 hit_code 와 같은 min_code를 찾아 값을 출력합니다.
즉 이렇게 하여 각 우체번호에 따라 코드를 부여하고 코드중 가장 작은 값에 대해서만 같은 데이터를 출력하도록 합니다.(즉 가장 작은 값이 여러개인 경우는 여러개의 로우를 출력합니다)
즉 이렇게 하여 스캔 횟수 줄이고 원하는 우편번호와 가장 가까운 우편번호를 출력할 수 있게 됩니다.

SELECT pcode, district_name
FROM (
  SELECT 
    pcode,
    district_name,
    CASE 
      WHEN pcode = '1234' THEN 0
      WHEN pcode LIKE '123%' THEN 1
      WHEN pcode LIKE '12%%' THEN 2
      WHEN pcode LIKE '1#%%' THEN 3
      ELSE NULL 
    END AS hit_code,
    MIN(
      CASE 
        WHEN pcode = '1234' THEN 0
        WHEN pcode LIKE '123%' THEN 1
        WHEN pcode LIKE '12%%' THEN 2
        WHEN pcode LIKE '1#%%' THEN 3
        ELSE NULL 
      END
    ) OVER (ORDER BY 
      CASE 
        WHEN pcode = '1234' THEN 0
        WHEN pcode LIKE '123%' THEN 1
        WHEN pcode LIKE '12%%' THEN 2
        WHEN pcode LIKE '1#%%' THEN 3
        ELSE NULL 
      END
    ) AS min_code
  FROM PostalCode
) AS FOO
WHERE hit_code = min_code;

3. 반복 횟수가 정해지지 않은 경우

1. 인접 리스트 모델과 재귀 쿼리
2. 중첩 집합 모델
   1. 인접 리스트 모델
   2. 중첩 집합 모델
   3. 경로 열거 모델

17장 바이어스의 공죄

SQL은 ‘절차 지향형에서의 탈출’을 목표로 설계된 언어이다.

하지만 이러한 절차적 계층을 은폐하는것이 SQL의 이념이다.

하지만 정말로 RDB에서 고성능을 실현하고 싶다면 절차 지향적인 바이어스를 때어내고 자유로워질 필요가 있다.

반복계와 포장계의 장단점을 파악하고 어떤 처리 방식을 채택할지를 냉정하게 판단해야한다.

SQL이 가진 강력한 도구와 튜닝방법을 활용하려면 집합 지향의 사고방식을 가져야한다.

6장 : 결합

18장 : 기능적 관점으로 구분하는 결합의 종류

기능적 관점

• 크로스 결합
• 내부 결합
• 외부결합

결합 조건

• 등가, 비등가 결합

자기 결합

자연 결합

• 내부 결합이면서 등가 결합
• 결합 조건을 따로 기술하지 않고 암묵적으로 같은 이름의 필드가 결합되는 구조
• but 추천하지 않는다.
  • 확장성이 좋지 않다(필드 이름의 변경하는 경우 등에 대해)
  • 가독성이 좋지 않다(테이블 구조를 파악하지 않는 경우 어렵다)
• using을 사용하는 방법도 있지만 추천하지 않는다. (등가 조건만 표현 가능, 테이블 필드 이름 다른 경우 불가능)

크로스 결합

• 실무 사용 기회 없다.
• 데카르트 곱 연산으로 ( 부서 테이블 2개 * 사람 테이블 4명 ⇒ 8개의 조합)을 모두 계산한다.
• 이런 결과가 필요한 경우 없고 비용이 많이 든다.

⇒ 이런일이 왜 발생할까?

테이블 3개이상 연결하는 경우 where 조건에 명시하지 않는 경우가 발생한다. 이런경우 크로스 결합이 되는 경우가 있다.

내부 결합

SELECT E.emp_id, E.emp_name, E.dept_id, D.dept_name
    FROM Employees E INNER JOIN Departments D
        ON E.dept_id = D.dept_id

• 가장 많이 사용되는 조합
• 테카르트 곱의 부분집합
• 크로스 결합으로 결과를 내고 결합 조건으로 필터링 하는 것 → 하지만 이렇게 무식하게 하지 않는다.
• 내부 결합과 같은 기능을 하는 상관 서브쿼리
  • dept_id 부서테이블의 기본키로 이를 조건으로 지정하면 레코드가 한개로 한정될것이 보장한다. 따라서 기본키를 사용하면 상관 서브쿼리를 스칼라 서브쿼리(리턴값이 하나의 단일값) 으로 사용할 수 있습니다.
  • SELECT E.emp_id, E.emp_name, E.dept_id, (SELECT D.dept_name FROM Departments D WHERE E.dept_id = D.dept_id)AS dept_name -- 여기서 한다. FROM Employees E;

⇒ 기본적으로는 결합을 사용하는 것이 좋고 상관 서브쿼리를 스칼라 서브쿼리로 사용하면 레코드 수만큼 상관 서브쿼리를 실행해 비용이 높아진다.

외부 결합

한쪽 테이블의 결과는 조인되지 않아도 모두가지고 있다.

키를 모두 가진 레이아웃의 리포트를 만드는경우 자주 사용한다.

• 오른쪽 외부 결합 (RIGHT OUTER JOIN)
• 왼쪽 외부 결합 (LEFT OUTER JOIN)
• 완전 외부 결합

자기 결합

생성되는 결과를 기준으로 분류하는 것이 아니라 연산의 대상으로 무엇을 사용하는지에 따른 분유이다. 자기 결합은 ‘자기결합 + 크로스 결합’, ‘자기 결합 + 외부 결합’ 으로 가능하다.

19장 : 결합 알고리즘과 성능

옵티마이저가 선택 가능합 결합 알고리즘 크게 3개

• Nested Loops
• Hash
• Sort Merge

옵티 마이저가 어떤 알고리즘을 선택할지 여부는 데이터 크기 또는 결합 키의 부산이라는 요인에 의존한다. 그중 NestedLoop가 각종 결합 알고리즘의 기본이 된다. 그다음이 Hash, Sorted Merge이다.

근데 각 DBMS마다 지원하는 알고리즘이 바뀔 수 있으므로 최신 동향에는 주의를 기울이다.

Nested Loops

본질적으로 이중반복과 같은 의미이다

1. 결합 대상 테이블에서 레코드를 하나씩 반복해 가며 스캔한다. 이 테이블이 구동테이블, 외부 테이블이다. 다른 테이블이 내부 테이블이다.
2. 구동 테이블 레코드 하나마다 내부 테이블의 레코드를 하나씩 스캔해서 결합 조건에 맞으면 리턴한다.
3. 이러한 작동을 구동 테이블의 모든 레코드에 반복한다.

• 접근되는 레코드 수는 R(A) * R(B)가 된다.
• 한 번의 단계에서 처리하는 레코드 수가 적어 Hash, sort merge에 비해 메모리 소비가 적다
• 모든 DBMS지원

구동 테이블이 작을수록 Nested Loops의 성능이 좋아진다.

이준 반복의 외측과 내측이 반복 처리가 비대칭이다.

왜 구동 테이블이 작을수록 성능적으로 좋을까?

내부테이블의 결합 키 필드에 인덱스가 존재한다는 전제를 기반으로 한다.

만약 테이블의 결합 키 필드에 인덱스가 존재하면 해당 인덱스를 통해 DBMS는 내부테이블을 완전히 순회 하지 않고 내부 반복을 어느정도 건너 뛸수 있게 해준다.

⇒ 즉 내부 테이블의 결합 키 인덱스가 사용되지 않으면 구동 테이블이 작아봤자 아무런 장점이 없다.

하지만 결합 키가 내부테이블에 유일하지 않은 경우 인덱스로 내부테이블에 접근하는 경우라도 여러개의 레코드가 히트되어 해당 부분을 반복해야한다.

따라서 SQL 튜닝의 기본은

‘ 구동 테이블이 작은 Nested Loops ‘ + ‘내부 테이블의 결합 키에 인덱스 ‘

⇒ 어떤 결합 키에 인덱스를 작성해야하는지 초기단계부터 고민해야한다.

하지만 앞서 설명한 결합키가 내부 테이블에 대해 유일하지 않는 경우 성능 저하가 발생할 수 있다. 이런경우 반복 해야하는 수가 늘어지기에 발생한다.

해결방법

1. 구동테이블로 큰테이블을 선택하게 하여 항상 하나의 레코드로 접근하는 것을 보장되게 한다.
2. 해시알고리즘

Hash 알고리즘

1. 일단 작은 테이블을 스캔 하고 결합키에 해시함수를 적용해서 해시값으로 확인한다.
2. 다른 테이블(큰 테이블)을 스캔하고 결합 키가 해시값에 존재하는지를 확인한다.

해시테이블이 워킹 메모리에 저장되기에 작은 테이블을 선택하는 것이 효율적이다.

Hash를 사용하는 경우는 두 테이블의 크기가 별로 차이나지 않는 경우 선택하는것이 좋다.

특징

• 결합 테이블로부터 해시테이블을 만들어 NL에 비해 메모리 소비가 크다
  • OLTP(사용자 요구에 시스템이 곧바로 응답해야 하는 처리)에서는 사용하면 안된다. (지연이 발생함)
  • 따라서 동시 처리가 적은 간 배티 또는 BI/DWH와 같은 시스템에 한해 사용하는 것이 기본적략이다.
• 메모리 부족시 저장소(하드디스크)를 사용해 지연이 발생한다.
• 출력되는 해시값은 등치 결합에만 사용할 수 있다(입력값의 순서를 알지 못함)
• 양쪽 테이블의 레코드를 전부 읽어야 하므로 테이블의 규모가 크다면 풀스캔 시간을 고려해야한다.

유용한 경우

• NL에서 적절한 구동 테이블이 존재하지 않는 경우
• 앞선 NL의 단점에서 본것처럼 구동 테이블로 사용할만한 작은 테이블은 있지만 내부테이블에서 히트되는 레코드수가 많은 경우
• NL의 내부 테이블에 인덱스가 존재하지 않거나 추가하기 어려운 경우
• 즉 Nested Loops가 효율적으로 작동하지 않는 경우

Sort Merge

• Nl, Hash가 모두 불가능한 경우
• Sort merge는 결합 대상 테이블들을 각각 결합 키로 정렬하고 일치하는 결합키를 찾으면 결합한다.

1. 대상 테이블을 모두 정렬 해야하기에 Nl보다 많은 메모리를 소비, Hash는 한쪽만 해시테이블을 만들어서 Hash보다 많은 메모리를 사용하기도 한다. 메모리 부족으로 Temp탈락이 발생하면 I/O비용이 늘어나 지연이 발생할 위험도 있다.
2. <, > ,≤ ≥ 모두에 사용가능 하지만 부정 조건(<>)결합에서는 사용할 수 없다.
3. 테이블키로 정렬되어있다면 정렬을 생략할 수 있지만 레코드의 물리적위치를 알고있어야 할때이다. 구현 의존적이다. (구현을 그렇게 한 경우)
4. 테이블을 정렬 하므로 한쪽테이블을 모두 스캔한 시점에 결합을 완료할 수 있다.

유효한 경우

• 테이블 정렬에 많은 시간과 리소스를 요구할 가능성이 있다. 결합 자체에 걸리는 시간은 나쁘지않다.
• ⇒ 그냥 Nl , hash를 먼저 고려해라

의도하지 않은 크로스 결합

• 삼각결합의 경우 나타난다.

SELECT A.col_a B.col_b C.col_c
    FROM Table_A A
        INNER JOIN Table_B B
            ON A.col_a = B.col_b
        INNER JOIN Table_C C
            ON A.col_a = C.col_c

Table_b - Table_c에 결합 조건이 존재하지 않는다.

• NL이 선택되는 경우 ⇒ 문제될건 없다.
• 크로스 결합이 선택되는 경우
  • Table B, Table C를 먼저 결합하고 TableA를 결합하는 순서로 수행된다.
  • 이런경우 크로스 결합을 수행하여 비 효율적이다.
  • 왜 크로스 결합이 선택될까?
    • 테이블의 크기에 의해 선택된것을 예상

회피하는 방법

1. 결합 조건이 존재하지 않는 테이블 사이에 불필요한 결합 조건을 추가햊누다.

SELECT A.col_a B.col_b C.col_c
    FROM Table_A A
        INNER JOIN Table_B B
            ON A.col_a = B.col_b
        INNER JOIN Table_C C
            ON A.col_a = C.col_c
            AND C.col_c = B.col_b; --table b, table c의 결합 조건 추가 이렇게 하여 크로스 결합이 회피될 수 있다.

20장 결합이 느리다면?

1. 상황에 따르 최적의 결합 알고리즘을 선택어떤 알고리즘은 선택해도 괜찮NL, 인덱스 만들기대규모 - 대규모기본 NL, 배치/BI/DWH Hash, Hash사용시에는 Temp탈락에 주의
2. NL : ‘작은 구동 테이블’ + ‘내부 테이블의 인덱스’
3. 일단 해시 , 결합 키로 정렬되어있다면 SortMerge
4. 하지만 내부테이블의 결합대상 레코드가 많으면 구동테이블과 내부테이블을 바꾸거나 Hahs를 사용할것을 권장
5. 소규모 - 대규모
6. 소규모 - 소규모
7. 실행 계획 제어
   • Oracle
     • 힌트 구
   • PostGresSql
     • hint
   • mysql
     • nestedLoop만 지원
8. 어떻게 제어하지? (235P)

실행계획을 사용자가 제어할 때의 리스크

• 이를 제어하기 위해 나온것이 성능 비용기반에 따른 동적 실행 계획이다.

1. 흔들리는 실행 계획
   1. 실행계획을 옵티마이저에게 맡겨도 최적이 되지 않는 경우가 있다.
   2. 데이터 양의 증가 등에 따라 통계정보가 변했을때 일정한 역치를 넘으면 옵티마이저가 실행계획을 변화시키면서 일어난다. 사전에 예측하기 어렵고 돌발적인 슬로다운을 일으킨다.

SQL의 성능 변동 위험을 줄이려면 되도록 결합을 피해야한다.

비정규화하라는 것인가? ⇒ 다른 대체 수단을 사용하는 방법이 있다 (책의 후반부)

7장 : 서브쿼리

21장 서브쿼리가 일으키는 폐해

1. 서브쿼리의 문제점

서브쿼리가 실체적인 데이터를 저장하고 있지 않다는 점에서 기인한다.

• 연산 비용 추가
• 데이터 I/O 비용발생
• 최적화를 받을 수 없음
  • 옵티마이저가 쿼리를 해석하기 위해 필요한 정보를 서브쿼리에서 얻을 수 없습니다.

1. 서브쿼리 의존증

• 서브쿼리를 사용한 방법
  1. 코드가 복잡해서 읽기 어렵다는 것
  2. 성능
• 상관 서브쿼리는 답이 될 수 없다.
• 윈도우 함수로 결합을 제거
  • 테이블 접근횟수를 1회로 줄이는 것
  • SQL 튜닝에서 가장 중요한 부분 I/O를 줄이는것

1. 장기적 관점에서 리스크 관리 (결합 쿼리 사용시 두개의 불안정 요소가 있습니다)
   • 알고리즘 변동 리스크
     • 테이블의 크기 등을 고려하여 옵티마이저가 자동으로 결정한다. 그러다 양이 늘어나면 sort merge, hash등이 선택된다 이런경우 변동리스크를 안아야 한다.
   • 환경 요인에 의한 지연 리스크
     • 결합을 사용한다는 것은 장기적 관점에서 고려해야 할 리스크가 늘어난다.
     • nested loop 방법은 index가 존재해야하고, sort merge 또는 hash가 선택되어 temp 탈락이 발생하는 경우에 대해서
2. 서브쿼리 의존증
   • 다시 서브쿼리 의존증
   • 레코드 간 비교에도 결합은 불필요

서브쿼리 자체가 나쁜것은 아니다. 써야하는 경우가 잃다.

생각의 보조도구로 생각하고 바텀업 타입의 사고방식과 굉장히 좋은 상성을 가지고 있다.

22장 서브쿼리 사용이 더 나은 경우

1. 결합과 집약 순서
   1. 결합 부터 하고 집약을 하는 방법
   2. 집약을 하고 결합하는 방법

선택시 가장 중요한 포인트는 결합 대상 레코드 수이다.

이때 TEMP 탈락이 발생하지 않는다면 집약을 먼저 하고 결합하는것이 좋다.

8장:SQL의 순서

23장 : 레코드에 순서 붙이기

1. 기본키가 한개의 필드인 경우
   1. 윈도우 함수를 이용
   2. SELECT id, ROW_NUMBER() OVER(ORDER BY id)AS seq FROM weights;
   3. 상관 서브쿼리를 사용
   4. SELECT id, (SELECT COUNT(*) FROM Weights W2 WHERE W2.id <= W1.id) AS seq FROM Weights W1;
2. 기본 키가 여러개의 필드로 구성되는 경우
   1. 윈도우 함수를 이용
   2. SELECT id, student_id, ROW_NUMBER() OVER(ORDER BY id, student_d)AS seq FROM weights;
   3. 상관 서브쿼리를 사용
   4. SELECT id, student_id (SELECT COUNT(*) FROM Weights W2 WHERE (W2.id,W2.student_id) <= (W1.id,W1.student_id)) AS seq FROM Weights W1;
3. 그룹마다 순번을 붙이는 경우
   1. 윈도우 함수를 이용(id를 기준으로 붙이는 경우)
   2. SELECT id, student_id, ROW_NUMBER() OVER(PARTITION BY id ORDER BY student_d)AS seq FROM weights;
   3. 상관 서브쿼리를 사용
   4. SELECT id, (SELECT COUNT(*) FROM Weights W2 WHERE (W2.class = W1.class AND W2.id <= W1.id,W1.student_id) AS seq FROM Weights W1;
4. 순번과 갱신
   1. 윈도우 함수를 사용
   2. UPDATE Weights3 SET seq= (SELECT seq FROM (SELECT class, student_id, ROW_NUMBER( OVER (PARTITION BY class ORDER BY student_id) AS seq FROM Weights3) SeqTb1 WHERE Weights3.class = SeqTb1.class AND Weights3.student_id = SeqTb1.student id);
   3. 상관서브쿼리를 사용
   4. UPDATE Weights3 SET seq = (SELECT COUNT (*) FROM Weights3 W2 WHERE W2.class =Weights3.class AND W2.student_id <- Weights3.student _id);

24장 레코드에 순번 붙이기 응용

1. 중앙값 구하기
   1. 집합 지향적 방법
   2. SELECT AVG(weight) FROM (SELECT W1 .weight FROM Weights W1, Weights W2 GROUP BY W1 weight HAVING SUMCASE WHEN W2.weight >= W1.weight THEN I ELSE 0 END) >= COUNT (*) / 2 AND SUM(CASE WHEN W2.weight <= W1.weight THEN 1 ELSE O END) >= COUNT (*) / 2 ) TMP;
   3. 절차 지향적 방법
   4. SELECT AVG(weight) FROM (SELECT weight, 2 * ROW NUMBER() OVER(ORDER BY weight) - COUNT (*) OVER() AS diff FROM Weights) TMP WHERE diff BETWEEN O AND 2;
   5. SELECT AVG(weight) AS median FROM (SELECT weight, ROW_NUMBER() OVER (ORDER BY weight ASC, student_id ASC) AS hi, ROW_NUMBER() OVER (ORDER BY weight DESC, student_id DESC) AS lo, FROM Weights) TMP WHERE hi IN (lo, lo +1, lo -1);
2. 순번을 사용한 테이블 분할
   1. 단절 구간 찾기
   2. 집합 지향적 방법
   3. SELECT (N1. num + 1) AS gap-start, '~' (MIN(N2.num) - 1) AS gap_end FROM Numbers N1 INNER JOIN Numbers N2 ON N2.num > N1.num GROUP BY N1.num HAVING (N1.num + 1) < MIN(N2.num);
   4. 절차 지향적 방법
   5. SELECT num +1 AS gap start, '~' (num + diff - 1) AS gap_end FROM (SELECT num, MAX(num) OVER(ORDER BY num ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) - num FROM Numbers) TMP (num, diff) WHERE diff <> 1:
3. 테이블에 존재하는 시퀀스 구하기
   1. 집합 지향적 방법
   2. SELECT MIN(num) AS low, '~' MAX(num) AS high FROM (SELECT N1 .num, COUNT (N2.num) - N1.num FROM Numbers N1 INNER JOIN Numbers N2 ON N2.num <= N1.num GROUP BY N1 .num) N(num, gp) GROUP BY gp;
   3. 절차 지향적 방법
   4. SELECT low, high FROM (SELECT low, CASE WHEN high IS NULL THEN MIN(high) OVER (ORDER BY seq ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) ELSE high END AS high FROM (SELECT CASE WHEN COALESCE (prev_diff, 0) <> 1 THEN num ELSE NULL END AS low, CASE WHEN COALESCE(next_diff, 0) <>1 THEN num ELSE NULL END AS high, seq FROM (SELECT num, MAX(num) OVER(ORDER BY num ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) - num AS next_diff, num - MAX(num) OVER(ORDER BY num ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev diff, ROW_NUMBER() OVER (ORDER BY num) AS seg FROM Numbers) TMP1 ) TMP2) ТМР3 WHERE low IS NOT NULL;

25장 : 시퀀스 객체, IDENTITY 필드, 채번 테이블

모두 최대한 사용하지 않기

1. 시퀀스 객체
   1. 문제점
      1. 유일성
      2. 연속성
      3. 순서성
   2. 해당 문제 해결을 위한 CACHE, NOORDER객체가 있음
      1. cache : 새로운 값이 필요할 때마다 메모리에 읽어들일 필요가 있는 값의 변수를 설정하는 옵션이다.
         1. 시스템에 장애가 생기면 연속성 담보할 수 없음, 빈숫자가 생길 수 있음
      2. Noorder : 순서성을 담보하지 않아서 오버헤드를 줄여준다.
      3. 연속성, 순서성이 담보돼지 않아도 된다면 CACHE, NOORDER를 채택하여 개선할 수 있다.
2. IDENTITY 필드
   1. 자동 순번 필드
   2. 성능적, 기능적 측면에서 시퀀스 객체보다 심각한 문제를 가짐
   3. IDENTITY 필드는 특정 테이블과 연결되어 CACHE, NOORDER을 지정할 수 없거나 특정제하적으로 사용가능해서 이점은 없다.
3. 채번 테이블
   1. 요즘에는 잘 사용하지 않음
   2. 직접 시퀀스 객체를 구현한것으로 성능이 좋지 않음
   3. 어플리케이션에서 자체적으로 순번을 부여하고 시퀀스 객체의 락 메커니즘을 활용한 방법임
   4. 개선 방법이 없음

9장 : 갱신과 데이터 모델

26장 : 갱신은 효율적으로

1. Null 채우기

반복계를 사용하지 말것

상관 서브쿼리를 사용하는것이 낫다.

27장 : 레코드에서 필드로의 갱신

1. null채우기
   1. 필드를 하나씩 갱신 (성능 👎🏼)
   2. 다중 필드 할당
      1. 트레이드 오프가 있음(정렬을 해야한다.)
2. Not Null제약이 걸려있는 경우
   1. update 구문 사용
   2. merge 구문 사용
      1. 상관 서브쿼리를 여러개 사용할 때와 달리 성능이 악화될 위험이 없다.

28장 : 필드에서 레코드로 변경

29장 : 같은 테이블의 다른 레코드로 갱신

1. 상관 서브쿼리 사용
   1. SIGN 부호 조사 함수를 활용하여 진행할 수 있음
2. 윈도우 함수
   1. PARTITION BY를 활용하여 진행할 수 있음
3. Insert vs update
   1. insert가 고속처리에 유리함, 또한 자시참조를 허가하지 않는 데이터 베이스에서도 사용할 수 있음, 단점은 같은 크기와 구조를 가진 데이터를 두개 만들어야 함.

30장 : 갱신이 초래하는 트레이드 오프

1. SQL을 사용하는 방법
2. 모델 갱신을 사용하는 방법

⇒ 모델을 잘 만들어야 한다!

31장 : 모델 갱신의 주의점

1. 높아지는 갱신 비용
   1. 검색 부하를 갱신 부하로 미루는 꼴
2. 갱신 까지의 시간 랙 발생
   1. 성능과 실시간성의 트레이드 오프 발생
3. 모델 갱신 비용 발생
   1. 이후에 큰 문제를 일으키는 핫스팟을 잘 고려해야함

32장 : 시야 협착 : 관련 문제

넓은 시야를 가져야 한다.

33장 : 데이터 모델을 지배하는 자가 시스템을 지배한다.

테이블은 처음 설계가 매우 중요하고

데이터 모델이 코드를 결정하지 코드가 데이터 모델을 결정하지 않는다.

10장 : 인덱스 사용

34장 : 인덱스와 B-tree

만능형 : B-tree

데이터를 트리구조로 저장하는 형태의 인덱스

대부분의 데이터 베이스에서는 이 B-Tree를 사용하고

특히 B-Tree가 아닌 수정된 버전인 B+tree를 채택한다.

B+tree가 더 좋은 이유는 루트와 리프의 거리를 가능한 일정하게 유지하려 하여 균형이 잘 잡혀 검색 성능이 안정적이다. 또한 트리의 깊이도 대게 2-4수준으로 일정하고 정렬 상태를 유지하여 이분탐색을 통해 검색 비용을 줄일 수 있다. 이를 통해 집약 함수 등에서 요구되는 정렬을 하지 않은 채 넘어갈 수도 있다.

기타 인덱스

비트맵 인덱스 : 비트 프롤그로 변환해서 저장하는 형태의 인덱스 ( 카디널리티가 낮은 필드에 대해 효과를 발휘한다)

해시 인덱스 : 키를 해시 분산해서 등가 검색을 고속으로 실행하고자 만들어진 인덱스 이다 .

하지만 등가 검색 외에는 효과가 거의 없고 범위 검색을 할 수 없다는 점에 거의 사용되지 않고 지원하는 구현도 일부에 불가하다.

35장 : 인덱스를 잘 활용하려면

B+tree 인덱스의 장점은 키값 사이에 검색 속도의 분균형이 거의 없어 데이터 양이 증가해도 속도가 갑자기 악화하는 일이 없다. 또한 등호 뿐만 아니라 부등호를 사용한 검색 조건에 사용할 수 있다.

1. 카디널리티와 선택률
   1. 카디널리티는 높은것이 좋다 (값의 종류가 많아야 인덱스가 효율적으로 동작한다)
   2. 선택률은 특정 필드값을 지정했을대 테이블 전체에서 몇개의 레코드가 선택 되는 지를 타나내는 개념이다카디널리티가 낮은 인덱스를 선택하는 경우는 값의 중복이 많기 떄문에 특정 값을 찾는데 효과적이지 않을 수 있습니다. (즉 거의 테이블 전체를 스캔하는것과 유사하게 진행될 수 있습니다)또한 B+tree내에서 동일한 값을 찾은후 링크드 리스트로 연결된 노드를 따라가면 계속하여 진행하기에 오히려 비효율적일 수 있습니다. (추가적인 노드 탐색이 많다)
2. B+tree의 인덱스를 확인하는 경우 값의중복이 많다면 일치하는 값들이 많아지게 되고 오히려 인덱스를 사용한는것이 비효율적일수 있습니다. 이런 경우 옵티마이저가 인덱스를 전체 테이블을 스캔하게 될 수 있고 인덱스를 사용하는것보다 더 많은 I/O작업이 일어날 수 있어 성능이 떨어집니다.
3. 좋은 인덱스
   1. 카디널리티가 높을것! (분산률이 높은것이 좋다)
   2. 선택률이 낮을것 (선택률이 5프로 미만이라면 인덱스를 작성할 가치가 있지만 10프로보다 높다면 테이블 풀스캔이 낫다)

36장 : 인덱스로 성능 향상이 어려운 이유

1. 압축 조건이 존재하지 않음
   1. where 절에 아무것도 없는 경우
2. 레코드를 제대로 압축하지 못하는 경우
   1. 선택률이 낮아야 한다.
   2. where 절에 적힌걸로 선택되는 데이터가 너무 높다면 의미가 없다.
   3. 입력 매개변수에 따라 선택률이 변동하는 경우
      1. 매개변수에 의해 선택률이 변하는 경우가 있다.
3. 인덱스를 사용하지 않는 검색 조건
   1. like를 사용하는 경우와 같이따라서 특정 값으로 시작하는 데이터를 찾는 것은 쉽지만, 특정 값으로 끝나는 데이터를 찾는 것은 어려워집니다. '%' 와일드카드가 앞에 위치하는 경우에는 검색 대상이 모든 값으로 시작하는 것이기 때문에, B+ 트리에서는 해당 검색을 효율적으로 처리할 수 없습니다. 따라서 Like연산이 사용될때 인덱스를 사용하고자 한다면 전방일치하여 찾는데이터가 많은 경우에만 인덱스를 생성하는 것이 좋습니다.
   2. BTree의 특징상 자식 노드의 데이터들은 노드 데이터를 기준으로 왼쪽에서 오른쪽으로 작은값 큰값이 정렬되어있습니다.
      즉 인덱스의 작동방식과 특성에 의헤 전방 일치에만 인덱스를 적용할 수 있습니다.
   3. 색인 필드로 연산하는 경우
   4. IS NULL을 사용하는 경우
   5. 부정형을 사용하는 경우

37장 : 인덱스를 사용할 수 없는 경우 대처법

1. 외부 설정으로 처리
   1. UI등을 고려하여 생성될 쿼리를 생각해야한다.
2. 데이터 마트로 대처
   1. 특정한 쿼리에서 필요한 데이터만을 저장하는 상대적으로 작은 크기의 테이블을 의미합니다.
   2. 고려해야할 점
      1. 데이터 신선도 ( 동기 시점의 문제)
      2. 데이터 마트 크기
         1. 잘라낸 데이터의 양이 비슷하다면 굳이 데이터 마트를 할 필요가 엇ㅂ다.
      3. 데이터 마트수
         1. 관리등을 고려하여 너무 지나치게 의존하지 않는것이 좋다.
      4. 배치 윈도우
         1. 어느 정도 규모의 갱신이 발생할 떄 통계정보도 다시 수집해야한다.
   3. 데이터 마트는 주의 하여 사용해야 한다.
3. 인덱스 온리 스캔으로 대처
   1. SQL구문이 접근하려는 대상의 I/O감소를 목적으로 한다.
   2. 인덱스만을 스캔대상으로 하는 검색을 사용할 수 있게 된다.
   3. 주의 사항
      1. DBMS에 따라 사용할 수 없는 경우도 있따.
      2. 한개의 인덱스에 포함할 수 있는 필드수의 제한이 있다.
      3. 갱신 오버헤드가 커진다.
      4. 정기적인 인덱스 리빌드가 필요하다.
      5. SQL구문에 새로운 필드가 추가된다면 사용할 수 없다.