[MySQL] Real MySQL 시즌 1 - Part 1

https://www.inflearn.com/course/real-mysql-part-1

Real MySQL 시즌 1 - Part 1 강의 | 이성욱 - 인프런

Ep.01 CHAR vs VARCHAR

모델링

- 논리 모델링 (업무 전문가)
  - 업무 분석
  - 엔터티 , 속성, 관계 도출
  - 정규화

- 물리 모델링 (DBMS 전문가)
  - DBMS 벤더별 최적 컬럼 타입 선정
  - 접근 패턴 분석
  - 반 정규화
  - 인덱스 전략 수립

- DBMS 전문가가 논리모델링까지 진행하는것은 회사별로 다르긴 하지만 무리가 있음

CHAR vs VARCHAR

 

- 공통점
  - 문자열 저장용 컬럼
  - 최대 저장 가능 문자 길이 명시 (바이트 수 아님)
  - 문자셋에 따라 바이트수가 달라짐 (UTF-8 는 가변길이 문자셋)

- 차이점
  - 값의 실제 크기에 관계없이 고정된 공간 할당 여부
  - 최대 저장 길이 : CHAR(255) vs VARCHAR(16383) -> 6553바이트
  - 저장된 값의 길이 관리 여부 (VARCHAR와 가변 길이 문자셋 사용하는 CHAR는 저장된 값 길이 관리)

    - 0 ~ 255 bytes  length-bytes : 1

    256 ~ 65535 byte lengh-bytes : 2

 

CHAR vs VARCHAR (Latin1)

- char(10) 은 4바이트 문자 저장시, 6바이트 남김

- varchar(10)은 4바이트 문자 저장시, 길이 저장용 1바이트, 문자 데이터 4바이트 로 저장

 

UTF8MB4

 

- VARCHAR(10) : (문자당 바이트 수가 달라도) 문자 셋 관계없이, 꼭 필요한 만큼만 사용

- CHAR(10) : 가변길이 문자 셋인 경우, 길이 저장용 바이트 사용
  예약하는 공간 크기 달라짐

- CHAR 이더라도, 가변길이 문자셋의 경우, 빈 공백 공간이 없을 수도 있음

- CHAR 이더라도 가변길이 문자셋이기 때문에 길이 저장용 1바이트 사용함

- 한글은 3바이트

 

- 일반적으로 알고 있는 구분 기준
  - 고정된 길이의 값 저장은 CHAR 타입, 그 외의 경우 VARCHAR 타입
  - 주민등록번호 저장시 반드시 CHAR 타입? 그렇지 않음 , 길이 저장용 1바이트가 유의미하지 않음

- CHAR 대신 VARCHAR를 사용하면 ?

  - 어떤 경우에는 CHAR 타입의 공간 낭비 심함
    - 저장되는 문자열의 최소 최대 길이 가변 폭이 큰 경우 ( e. g. 1 ~ 100 )

- 하지만 그렇지 않은 경우도 있음

  - 저장되는 문자열의 최소 최대 길이 가변 폭이 작은 경우 ( e.g. 90 ~ 100)

- 저장되는 값의 길이 변동이 크지 않다면 낭비는 크지 않음

 

칼럼 값의 길이 변경시 작동 방법

- 처음 레코드가 저장되었던 공간은 delete marking, 새롭게 빈 공간에 레코드를 저장

- 페이지가 비어있는 경우는 괜찮은데, 페이지의 record를 compaction 하는 과정도 필요함 (fragmentation)

- CHAR의 경우, 길이 변경시 공백으로 되어있던곳에 추가하면 되기 때문에 빈 공간에 레코드를 저장하지 않아도 됨

  ( 레코드의 위치를 옮겨쓰는 일을 줄일 수 있음 )

  => CHAR가 VARCHAR 보다 공간을 더 효율적으로 쓰는 경우가 생김

 

문자열 타입 선정

- VARCHAR 보다는 CHAR 선택해야 하는 경우

  - 값의 가변 길이 범위 폭이 좁고

  - 자주 변경되는 경우 (특히 인덱스된 칼럼인 경우)

- 이런 경우 VARCHAR 사용

  - 데이터 페이지 내부의 조각화 현상 높음
  - CHAR 타입보다 공간 효율이 털어짐

  - 내부적으로 빈번한 Page Reorganize 작업 필요

 

Ep.02 VARCHAR vs TEXT

- 공통점
  - 문자열 속성 값을 저장
  - 최대 65535 Bytes 까지 저장 가능
- 차이점
  - VARCHAR 타입 컬럼에는 지정된 글자 수 만큼만 데이터 저장 가능
  - TEXT 타입 컬럼은 인덱스 생성 시 반드시 Prefix 길이 지정 필요
  - TEXT 타입 컬럼은 표현식으로만 디폴트 값 지정 가능

 

- 일반적인 사용 형태

  - 길이가 짧으면 VARCHAR 타입, 길이가 길면 TEXT 타입

 

- 그렇다면 VARCHAR(5000) VS TEXT ?
  - VARCHAR 타입은 메모리 버퍼 공간을 미리 할당해두며 재활용 가능, TEXT 타입은 그때 그때 필요할 때마다 할당 & 해제

  - 컬럼 사용이 빈번하고 메모리 용량이 충분하다면 VARCHAR 타입 추천
  - ROW 사이즈 제한(65535 bytes) 에 도달할 수 있으므로 적절하게 TEXT 타입과 같이 사용하는 것을 권장

 

- VARCHAR(30) vs VARCHAR(255) ?

  - 실제 최대 사용하는 길이만큼 명시해야 메모리 사용 효율 증가
  - 디스크 공간 효율 차이도 미미하게 존재 ( 1Byte vs 2Bytes )

 

- 저장되는 값의 사이즈가 크면 Off-Page 형태로 데이터가 저장될 수 있음
  -  InnoDB 에서 실제 데이터가 들어있는 Data Page 에 20 Bytes 포인터값만 저장됨 (Off-Page)

- 쿼리에서 Off-Page 컬럼의 참조 여부에 따라 쿼리 처리 성능이 매우 달라짐
  - 필요없으면 select 절에서 해당 컬럼 조회하는 로직을 최소화하는게 좋음

 

- 상대적으로 저장되는 데이터 사이즈가 많이 크지 않고, 컬럼 사용이 빈번하며 DB 서버의 메모리 용량이 충분하다면 VARCHAR 타입 권장

- 저장되는 데이터 사이즈가 큰 편이고, 컬럼을 자주 사용하지 않으며 테이블에서 다른 문자열 컬럼들이 많이 사용되고 있는 경우 TEXT 타입 권장

- VARCHAR 타입을 사용하는 경우, 길이는 실제 사용되는 만큼만 지정

- 쿼리의 SELECT 절에는 가능하면 필요한 컬럼들만 명시하는 것이 좋음
  - 그렇지 않은 경우, 테이블에 대형 데이터 저장 컬럼 존재 시 쿼리 처리 성능 낮음
  - 필요한 컬럼만 명시하면, 커버링 인덱스 방식으로 처리될 가능성 높음

 

 

 

Ep.03 COUNT(*) & COUNT(DISTINCT) 튜닝

- COUNT(*) 쿼리는 SELECT * 쿼리보다 빠를것으로 기대
- ORM에서 자동 생성된 쿼리 실행
  - SELECT COUNT(DISTINCT(id)) as counter FROM tab WHERE fd1= ?;

- 커버링 인덱스 설명 : https://jojoldu.tistory.com/476

 

- COUNT(*) 성능 개선을 위해 Covering Index 통해 개선
  - SELECT COUNT(ix_fd2) WHERE ix_fd1 = ?;

- 모든 쿼리를 Covering Index로 튜닝하는 것은 적절하지 않음

  - WHERE 절 조건이 많을 경우, Index로 커버하려 할 경우 단점이 많음

 

- COUNT(*) : 레코드 건수만 확인
- COUNT(DISTINCT expr) 은 임시 테이블로 중복 제거후 건수 확인

  => 중복 제거 하기 위해 임시테이블을 생성하려고 내부적으로 SELECT -> INSERT or UPDATE 진행 

  => 임시 테이블을 디스크로 옮길수도 있어서 쿼리 성능이 엄청 느려질 수 있음
  => ORM 사용하더라도 DB의 general log를 확인하여 쿼리를 확인하자

 

COUNT(*) 튜닝

- 최고의 튜닝은 쿼리 자체를 제거하는 것

  - 전체 결과 건수 확인 쿼리 제거
  - 페이지 번호 없이, "이전" "이후" 페이지 이동

- 쿼리를 제거할 수 없다면, 대략적 건수 활용

  - 부분 레코드 건수 조회
    - 표시할 페이지 번호만큼의 레코드만 건수 확인
      SELECT COUNT(*) FROM (SELECT 1 FROM table LIMIT 200) z;

  - 임의의 페이지 번호는 표기
    - 첫 페이지에서 10개 페이지 표시 후, 실제 해당 페이지로 이동하면서 페이지 번호 보정

- 통계 정보 활용
  - 쿼리 조건이 없는 경우, 테이블 통계 활용
     SELECT TABLE_ROWS as rows FROM INFORMATION_SCHEMA.tables
    WHERE schema_name = ? AND table_name=?

  - 쿼리 조건이 있는 경우, 실행 계획 활용
    - 정확도 낮음
    - 조인이나 서브쿼리 사용시 계산 난이도 높음
- 성능은 빠르지만, 페이지 이동하면서 보정 필요

- 제거 대상
  - WHERE 조건 없는 COUNT(*)

  - WHERE 조건에 일치하는 레코드 건수가 많은 COUNT(*)

- 인덱스를 활용하여 최적화 대상

  - 정확한 COUNT(*)가 필요한 경우

  - COUNT(*) 대상 건수가 소량인 경우

  - WHERE 조건이 인덱스로 처리될 수 있는 경우

 

 Ep.04 페이징 쿼리 작성

- DB 서버에서 제공하는 LIMIT & OFFSET 구문을 사용하는 경우가 많음

- LIMIT & OFFSET 을 사용하는 경우 오히려 DBMS 서버에 더 많은 부하를 발생시킴

- DBMS 에서 순차적으로 레코드를 읽지 않고 지정된 OFFSET 이후 데이터만 바로 가져올 수는 없음

- LIMIT & OFFSET 구문을 사용하지 않으면서 데이터를 원하는 만큼만 조회해서 가져갈 수 있도록 쿼리를 작성해야함

  - 범위 기반 방식
  - 데이터 개수 기반 방식

 

1. 범위 기반 방식

  - 날짜 기간이나 숫자 범위로 나눠서 데이터를 조회하는 방식
  - 매 쿼리 실행 시 WHERE절에서 조회 범위를 직접 지정하는 형태로, 쿼리에서 LIMIT 절이 사용되지 않음

  - 주로 배치 작업 등에서 테이블의 전체 데이터를 일정한 날짜/숫자 범위로 나눠서 조회할 때 사용

  - 쿼리에서 사용되는 조회 조건도 굉장히 단순하며, 여러 번 쿼리를 나누어 실행하더라도 사용하는 쿼리 형태는 동일

  - 해당 칼럼에 대해 인덱스를 생성해두는게 좋음

2. 데이터 개수 기반 방식 

  - 지정된 데이터 건수만큼 결과 데이터를 반환하는 형태로 구현된 방식
  - 배치보다는 주로 서비스단에서 많이 사용되는 방식
  - 처음 쿼리를 실행할 때와 그 이후 쿼리를 실행할 때 쿼리 형태가 달라짐
  - 쿼리의 WHERE 절에서 사용되는 조건 타입에 따라서 N회차 실행 시의 쿼리 형태도 달라짐

  - ORDER BY 절에는 각각의 데이터를 식별할 수 있는 식별자 칼럼(PK와 같은)이 반드시 포함되어야 함

  - finished_at, id를 묶어서 인덱스를 설정하여 정렬할수도 있음 (범위 조건 사용)

    - N회차 쿼리 작성이 복잡해질수도 있음
      -> 인덱스 설정을 통해 조건문이 복잡해지나 쿼리 성능은 나빠지지 않음

 

Ep.05 Stored Function

- MySQL Function

  - Built-in Function
  - User Defined Function (UDF)
  - Stored Function

 

- DETERMINISTIC VS NOT DETERMINISTIC

  - DETERMINISTIC : 동일 상태와 동일 입력(함수 인자 및 데이터) 으로 호출 -> 동일한 결과 반환
     -> 데이터 테이블이 달라지는 것도 입력이 달라지는것으로 판단함
     -> 스냅샷 테이블을 바라보면 동일한 입력으로 봄
  - NOT DETERMINISTIC : 위와 같지 않은 경우

  - 항상 두가지 상태 중 하나의 Stored Function 으로 존재

 

- NOT DETERMINISTIC은 FULL SCAN 으로 동작함
  - 함수의 결과는 비확정적임
  - 매번 호출 시점마다 결과가 달라질 수 있음
    - 비교 기준 값이 상수가 아니고 변수임
    - 매번 레코드를 읽은 후, WHERE 절을 평가할 때마다 결과가 달라질 수 있음
    - 인덱스에서 특정 값을 검색할 수 없음
    - 인덱스 최적화 불가능

 

- NOT DETERMINISTIC Built-in Function
  - RAND(), UUID(), SYSDATE(), NOW()

 

- NOW() vs SYSDATE()

  - 동일하게 현재 일자와 시간 반환하는 함수
  - 둘 모두 NOT DETERMINISTIC 함수, 하지만 NOW() 함수는 DETERMINISTIC처럼 작동

  - NOW() 함수는, 하나의 Statement 내에서는 , Statement의 시작 시점 반환
  - SYSDATE() 함수는, NOT DETERMINISTIC, 매번 함수 호출 시점 반환

  - SELECT * FROM tab WHERE created_at=SYSDATE(); 는 호출하면 인덱스를 타지못함
  - sysdate-is-now 옵션으로 설정할수 있음

 

- NOT DETERMINISTIC 옵션이 Stored Function 의 기본 설정임

  - 옵션이 명시되지 않으면, 기본적으로 NOT DETERMINISTIC 으로 인식
  - StoredFunction 생성시 기본 옵션 꼭 명시

 

Ep.06 Lateral Derived Table

- Lateral Derived Table이란?

  - Derived Table (파생 테이블) 은 쿼리의 FROM 절에서 서브쿼리를 통해 생성되는 임시 테이블을 의미
  - 일반적으로 Derived Tabel은 선행테이블의 컬럼을 참조할 수 없으나, Lateral Derived Table은 참조 가능
  - 정의된 Dervied Table 앞부분에 LATERAL 키워드를 추가해서 사용
  - 참조한 값을 바탕으로 동적으로 결과 생성

SELECT e.emp_no, s.sales_count, s.total_sales
FROM employees e
LEFT JOIN LATERAL (
	SELECT COUNT(*) AS sales_count,
    		IFNULL(SUM(total_price),0) AS total_sales
    FROM sales
    WHERE emp_no=e.emp_no
 ) s ON TRUE;

 

- 활용 예제

  - 종속 서브 쿼리의 다중 값 반환

    - FROM 절에서 LATERAL 키워드를 사용해 하나의 서브쿼리로 원하는 값들을 모두 조회

    - INNER JOIN은 ON 절이 필수적이지 않아 ON TRUE를 하지 않아도 괜찮음

  - SELECT 절 내 연산 결과 반복 참조
  - 선행 데이터를 기반으로 한 데이터 분석

  - Top N 데이터 조회

 

Ep.07 SELECT ...FOR UPDATE

- SELECT ( REPEATABLE-READ )

  - Non-Locking consistent read (MVCC)

    : 잠금을 걸지 않고 일관된 데이터 읽기를 보장한다.
  - 데이터를 변경할때 Undo 에 이전 데이터를 저장하는데, 동시대에 접근한 트랜잭션에 대해 해당 변경 이력을 참조하여 전달함
    - 격리수준 READ-COMMITED : 최근 COMMIT 데이터 반환

    - 격리수준 REPEATABLE-READ : 트랜잭션 시작 시점 직전에 COMMIT 된 데이터 반환

 

- SELECT .. FOR [UPDATE | SHARE]
  - 격리 수준 무관하게, 항상 최신 커밋 데이터 조회
  - 단순 SELECT와 다른 결과 반환 가능함
  - EXCLUSIVE LOCK 을 걸기 때문에 COMMIT 하기 전에 다른 곳에서 SELECT FOR [UPDATE | SHARE] 를 실행하지 못함
  - AUTO COMMIT 모드에서는 의미가 없음

  - UPDATE 조건문 실행으로 FOR UPDATE와 같이 X-lock 획득

 

- SELECT .. FOR SHARE = S-lock
  - 부모 테이블의 레코드 삭제 방지
  - 부모 테이블의 SELECT와 자식 테이블의 INSERT 시점 사이에, 부모 삭제 방지

  - FOR SHARE 이후 동일 레코드를 update 할 경우, Lock upgrade 가 필요 (S-lock -> X-lock)
    => Deadlock 가능성 높음

    => FOR UPDATE 로 lock을 걸기

 

- JPA Optimistic vs Pessimistic

  - Optimistic Lock ( 변경 시점에 잠금 )

  - Pessimistic Lock (읽는 시점에 잠금 - 미리 충돌 예상)

  - MySQL의 lock 개념 혹은 insert, update lock 개념 이라기 보다는 트랜잭션 단위에서의 잠금 개념임

 

- Lost Update Anomaly

  - LOCK 을 하지 않고 진행하여 데이터가 예상치못하게 바뀜

- JPA Optimistic Lock

  - 레코드의 버전을 할당하여, 원하지 않은 UPDATE 실행 방지
    - version 을 테이블에 칼럼으로 추가하여 update 문에서 version을 조건문에 써서 함께 업데이트함
  - 실제 MySQL 서버가 제공하는 잠금 기법 아님

 

- JPA Pessimistic Lock

  - MySQL 서버의 SELECT .. FOR UPDATE 를 사용
  - Exclusive-lock을 이용하여, 동시 변경 제어

 

Ep.08 Generated 컬럼 및 함수 기반 인덱스

- Generated Column이란?

  - 표현식 으로 정의된 컬럼
  - 정의된 표현식 에 따라 컬럼의 값이 자동으로 생성
    - 표현식 : 고정된 값, 함수 또는 다른 컬럼들에 대한 연산 조합 등이 해당
  - 두가지 종류가 존재
    - 가상 컬럼, Stored 컬럼
  - 기본적으로 VIRTUAL 타입으로 생성 & NULL 값 허용
  - PRIMARY KEY 로는 STORED 타입만 허용

  - 하나의 테이블에서 가상 컬럼과 스토어드 컬럼 혼합해서 사용 가능
- https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html#online-ddl-generated-column-operations

- 가상 컬럼
  - 컬럼의 값을 디스크에 저장하지 않음
  - 컬럼의 값은 레코드가 읽히기 전 또는 BEFORE 트리거 실행 직후에 계산됨
  - 인덱스 생성 가능 
    - 인덱스 데이터는 디스크에 저장됨

 

- 스토어드 컬럼
  - 컬럼의 값을 디스크에 저장
  - 컬럼의 값은 레코드가 INSERT 되거나 UPDATE 될 때 계산되어 저장

  - 인덱스 생성 가능

- ALTER 명령으로 ADD/MODIFY/CHANGE/DROP/RENAME 가능
- 일반 컬럼은 스토어드 컬럼으로 , 스토어드 컬럼을 일반 컬럼으로 변경 가능
  - 가상 컬럼은 일반 컬럼으로 전환 불가

- 스토어드 컬럼 <-> 가상 컬럼 간 변경 불가

- Online DDL 지원하는 작업도 나눠져있음
  - DDL 명령에 실행 알고리즘을 직접 명시하는것을 권장
  - 명시하지 않으면 자동으로 최적의 알고리즘을 찾는데, 의도하지 않게 실행될수도 있음
    ( 명시한 알고리즘이 불가능하면 오류 발생 , INSTANT 알고리즘이 가장 SIMPLE 함 )

- Generated Column 추가, 변경시 WITH VALIDATION, WITHOUT VALIDATION 을 통해
  Generated Column에 대해 유효성 검사 여부를 설정할 수 있음
  - WITHOUT VALIDATION 이 기본 설정이며,
    VALIDATION 설정시 테이블 데이터 복사 발생 (LOCK 이 걸려 운영 DB에서는 서비스장애 발생할수도 있음)

 

- Generated Column 인덱스 사용

  - 일반 컬럼과 동일하게 쿼리에서 인덱스 사용 가능

  - 쿼리에 Generated 컬럼명 대신 표현식을 사용해도 인덱스 사용 가능
  - 대신 표현식은 컬럼에 정의된 표현식과 완전히 일치해야 함
    - 컬럼 : (col1+1) , 쿼리: (1+col1) => 인덱스 사용 불가

  - =, <, <=, >, >=, BETWEEN, IN 연산자 사용시 이러한 최적화가 적용됨

 

- 제한사항

  - 표현식에 아래 항목들은 사용 불가
    : 비결정적 함수, 스토어드 프로그램, 변수, 서브 쿼리
  - INSERT/UPDATE 시 Generated 컬럼에 직접 값을 지정할수 없으며, 지정할 수 있는 값은 "DEFAULT" 만 가능

 

- Function Based Index란?

  - 일반 인덱스는 컬럼 또는 컬럼의 Prefix만 인덱싱 가능

  - 함수 기반 인덱스는 "표현식"을 인덱싱 값으로 사용 가능
  - 쿼리의 조건절에서 컬럼을 가공하는 경우에 유용하게 사용 가능

  - Virtual Generated 컬럼을 자동 생성 후 인덱싱
    - 자동 생성된 Virtual 컬럼은 일반적인 환경에서는 확인 불가 (타입, 이름도 별도 자동으로 지정됨)

  - 활용 예시
    - 문자열 값의 특정 부분에 대해서 조회
      - 이메일 도메인만 따로 뽑아서 인덱싱함
      - 일/월/연도 별 조회

  - 주의 사항
    - 인덱스 생성 후 실행 계획을 반드시 확인
      - 표현식을 정확하게 명시해야 인덱스 사용 가능
    - 표현식 결과의 데이터 타입을 명확하게 확인해서 조건값 지정

      - SELECT {COLUMN} AS EXPR_RESULT; 로 표현식 데이터 타입을 확인할수 있음

    - 기본적으로 일반 인덱스보다 추가적인 계산 비용이 발생
      - 변경이 잦은 컬럼 & 복잡한 표현식 사용시 오버헤드가 커질 수 있음

  - 제한사항

    - 표현식에 비결정적 함수 사용 불가
    - 일반 컬럼 및 Prefix 길이 지정된 컬럼은 키 값으로 지정 불가
    - 공간 인덱스나 전문검색 인덱스는 지원하지 않음
    - Primary Key에 표현식은 포함 불가

 

Ep.09 에러 핸들링

- MySQL 에러 구분

  - Global Error : 서버, 클라이언트 공용으로 발생
  - Server Error : 서버만 발생
  - Client Error : 클라이언트만 발생

 

- 에러 번호로 에러 원인을 좁힐 수 있음

- 일부 Server Error는 Client-side로 전달
  - Client-side에서 보여지는 에러는 Client 또는 Server Error일 수 있음

 

- MySQL 에러 포맷

  - 3개 파트로 구성 : Error No, SQLState, Error Message

 

- Error NO

  - 기존 4자리에서 6자리 문자열까지 있음

  - MySQL에서만 유효한 정보

  - 에러 번호의 구분
    - 1 ~999 : global
    - 1000 ~ 1999 : Server
    - 2000 ~ 2999 : Client
    - 그외 서버 (ex MY-010000 ~ ... )

 

- SQL State

  - 5 글자 영문 숫자로 구성
  - ANSI-SQL에서 제정한 Vendor 비 의존적 에러 코드
  - SQL-STATE는 두 파트로 구분

    - 앞 두글자 => 상태값의 분류
      - 00 : 정상, 01 : 경고, 02 : 레코드 없음, HY : ANSI-SQL에서 아직 표준 분류를 하지 않은 상태 (벤더 의존적 상태 값)
      - 나머지는 모두 에러
    - 뒷 세글자 : 주로 숫자값 (영문 가끔 포함됨)이며, 각 분류별 상세 에러 코드 값

 

- Error Message

  - 사람이 인식할 수 있는 문자열

  - 에러 메시지는 MySQL 버전별로 달라서, 해당 메시지로 어플리케이션 로직을 짜면 안됨

- Error No는 MySQL 스토리지 엔진에 종속적인 경우 많음
  - 때로는 Error no도 에러 처리에 적합하지 않을 수 있음

 

- SQLSTATE 를 통해 에러 핸들링 해야 함
  - 동일 에러에 대해서, 다양한 에러 번호와 에러 메시지가 존재하지만 SQLState는 동일함
  - MySQL 서버의 스토리지 엔진간의 호환성 제공

  - 다른 Vendor DBMS (Oracle, PostgreSQL) 에서도 동일한 SQLState가 나옴 (호환성)

  - 하지만 "HY" (미분류 상태) 인 경우, 버전 업그레이드시, 새로운 카테고리의 SQL State로 변경 가능

 

- DBA 와 에러 논의시, SQL 문장, ERROR 정보 (Error no & SQL State & Message) 를 가지고 논의하는게 좋음

- ORM 사용시 DBMS 에러와 응용프로그램 에러 구분 필요

 

Ep.10 LEFT JOIN 주의사항 및 튜닝

- LEFT JOIN 은 기존 테이블을 먼저 읽고, INNER JOIN은 JOIN 테이블을 먼저 읽고 기존테이블을 읽음

  => Optimizer에 의해 읽는 테이블 순서가 변경될수 있음

- COUNT (*) WITH LEFT JOIN

  -> LEFT JOIN을 하지 않아도 결과가 동일한 경우에는, 불필요한 LEFT JOIN은 제거해서 사용

- LEFT JOIN을 사용하고자 한다면 Driven Table(Inner Table) 컬럼의 조건(조인 조건)은 반드시 ON절에 명시해서 사용 (IS NULL 조건은 예외)

- LEFT JOIN과 INNER JOIN은 결과 데이터 및 쿼리 처리 방식 등이 매우 다르므로, 필요에 맞게 올바르게 사용하는 것이 중요

- LEFT JOIN 쿼리에서 COUNT를 사용하는 경우 LEFT JOIN이 굳이 필요하지 않다면 JOIN은 제거

 

Ep.11 Prepared Statement

- PreparedStatement?
  - Binding Query
  - 장점
    - SQL Injection 방지, 쿼리 파싱 비용 감소 (2번째 이후 실행)

  - 단점
    - 메모리 사용량 증가, 2번의 Network round-trip 필요 (첫번째 실행)

    - Execution-plan은 캐시되지 않음, Parse-Tree만 캐시됨
    - 캐시된 PreparedStatement는 커넥션 내에서만 공유됨

 

- MySQL의 PreparedStatement

  - Client side PreparedStatement
  - Server-side PreparedStatement

  - 모두 SQL-Injection은 막을 수 있음
- JDBC Server-side PreparedStatement

  - useServerPrepStmts=TRUE 인 경우에만 작동 (기본값 FALSE)

  - ORM에서는 TRUE로 기본 설정되는 경우 많음

    => 활성화 하지 않으면 cleint PreparedStatement를 쓰고 있는거임

 

- PreparedStatement vs ConnectionPool
  - MySQL 서버의 PreparedStatement는 하나의 Connection 내에서만 공유됨
  - 모든 PreparedStatement는 Connection 단위로 캐시되어야 함
  - 전체 커넥션이 5000개 이고, 필요한 쿼리 패턴이 100 개 인경우 500000개의 PS객체가 MySQL 서버에 저장되어야함
    (max_prepared_stmt_count = 16382 (default) 임)
    - 해당 수를 넘을 경우 캐시를 정리하고 다시 만드는 과정이 들어감

  - 커넥션의 수를 줄이고 라이프를 길게해야 PS의 캐시를 효율적으로 쓸수있음
  - 쿼리의 복잡도가 크면 PS가 도움이 되지만 단순하면 PS 장점이 경감

- MySQL 서버에서는 Server-side PS가 부작용이 심한 경우 많음
  -> Client-side PS를 권장함

  - Server-side PS가 예상하는것 처럼 성능을 크게 높여주지 않고, 메모리 사용량만 많게됨

 

 

Ep.12 SQL 문장의 가독성 향상

1. DISTINCT를 함수처럼 사용하는 형태는 지양
  - DISTINCT 는 함수가 아니면 괄호와 함께 사용한다고 하더라도 그렇지 않은 경우와 결과가 동일함
  - ex) SELECT DISTINCT(fd1), fd2 ...
2. LEFT JOIN 사용 방법 준수
  - LEFT JOIN 사용 시에는 드리븐 테이블에 조건은 ON절에 명시 (where절이 아닌)

  - count 쿼리에서 필요하지 않을 경우 제거

3. ORDER BY절 없이 LIMIT n,m 문법 사용 지양

  - 쿼리에서 ORDER BY 절 없이 LIMIT이 사용되는 경우 어떤 의도로 작성된 건지 파악이 어려움
4. FULL GROUP BY 형태로 사용
  - SELECT ... GROUP BY 쿼리에서 GROUP BY 절에 명시되지 않은 컬럼을 SELECT 절에서 참조하는 경우 의도에 맞게 집계 함수를 반드시 사용, 또는 불필요한 컬럼인 경우에는 제거
    ex) SELECT fd1, sum(fd2), count(*) FROM tab GROUP BY fd1

  - 보통 FULL GROUP BY 형태 옵션이 DEFAULT로 강제됨 (그렇게 안할 경우 오류 발생 )

5. AND/OR 조건 함께 사용 시 반드시 괄호 명시
  - SQL에서 AND 연산자는 OR 연산자보다 우선순위가 높아서, 괄호가 없는 경우 AND 연산자를 우선해서 처리

  - 가독성을 위해 AND/OR 조건을 쿼리에서 함꼐 사용하는 경우 의도에 맞게 괄호를 반드시 명시해서 사용

6. 데이터 건주 조회는 COUNT(*) 사용

  - 가독성 등을 위해 전체 건수가 필요한 경우, COUNT(*) 를 사용하는 것을 권장