https://www.inflearn.com/course/real-mysql-part-2
Real MySQL 시즌 1 - Part 2 강의 | 백은빈 - 인프런
백은빈 | MySQL의 핵심적인 기능들을 살펴보고, 실무에 효과적으로 활용하는 방법을 배울 수 있습니다. 또한, 오랫동안 관성적으로 사용하며 무심코 지나쳤던 중요한 부분들을 새롭게 이해하고,
www.inflearn.com
Ep.13 콜레이션
- 콜레이션이란?
- 문자를 비교하거나 정렬할 때 사용되는 규칙
- 문자집합에 종속적
- 문자와 코드값의 조합이 정의돼있는 것이 문자집합
- MySQL에서 모든 문자열 타입 컬럼은 독립적인 문자집합과 콜레이션을 가질 수 있음
- 사용자가 특별히 지정하지 않는 경우, 서버에 설정된 문자집합의 디폴트 콜레이션으로 자동 설정됨
- MySQL에서의 콜레이션 네이밍 컨벤션
- 문자집합_언어종속_UCA버전_민감도
- 언어종속 : 특정 언어에 대해 해당 언어에서 정의한 정렬 순서에 의해 정렬 및 비교를 수행 (다른 언어들에게는 적용되지 않음)
-> locale code (e.g. "tr") 또는 language( e.g. "turkish") 로 표시됨
- UCA 버전 : Unicode Collation Algorithm Version
-> general 은 커스텀 콜레이션 (MySQL 자체 커스텀 콜레이션)
- 민감도 : ai (Accent-Insensitive) , ci (Case-Insensitive), ks (Kana-sensitive), _bin (Binary)
- 유니코드 기준
- 데이터 저장
- 코드 포인트 값을 인코딩해서 저장
- 일반적으로 많이 사용되는 UTF-8 인코딩 방식
- 코드포인트 범위별로 사용되는 비트수가 다르고, 그에 따라 인코딩 바이트도 다름
- 데이터 비교
- DUCET (Default Unicode Collation Element Table) 에 정의된 가중치 값을 바탕으로 비교
- Primary Weight : 기본 문자 비교, Secondary Weight : 악센트 구분, Tertiary Weight : 대소문자 구분
- 콜레이션에 따라 사용되는 가중치 값이 달라짐
- ai_ci : Primary , as_ci : Secondary, as_cs : Tertiary
- 콜레이션 설정
- MySQL 서버에 설정된 문자집합의 디폴트 콜레이션으로 글로벌하게 설정됨
- 데이터베이스/테이블/컬럼 단위로 독립적으로 지정 가능
- 콜레이션 사용시 주의사항
- 서로 다른 콜레이션을 가진 컬럼들 값 비교시 에러 발생
- 쿼리 WHERE 절에서 콜레이션 변경 시 일반 인덱스 사용 불가
- 인덱스도 기존 컬럼의 collation 과 동일하게 데이터를 비교하고 정렬함
- 함수 기반 인덱스로 collation 변경 인덱스 작성하여 해결할 수 있음
- 고유키도 콜레이션의 영향을 받음
- 고유키의 대소문자가 다른데도 저장되거나 안될 수 있음
- 기본 콜레이션 (utf8mb4_0900_ai_ci) 에서의 한글 비교 문제
- "가" 와 "ㄱ ㅏ"가 동일하게 인식됨
- 그러나 "각" 과 "ㄱ ㅏ ㄱ" 또는 "가ㄱ"은 같다고 인식하지 않음
- 종성 ㄱ 과 한글 문자 ㄱ 이 다름
- 대소문자 구분을 위한 콜레이션 설정
- utf8mb4_bin, utf8mb4_0900_bin, utf8mb4_0900_as_cs 가 대소문자 구분함
- 후행 공백 인식을 원하지 않는 겨우 : utf8mb4_bin
- 후행 공백 인식을 원하거나 특별히 상관 없으면서
- 단순히 대소문자 잘 구분으로 충분 : utf8mb4_0900_as_cs
- 모든 문자 명확히 구분 : utf8mb4_0900_bin
Ep.14 UUID 사용 주의사항
- UUID 는 128bit 2진값
- UUID Version (5개 버전 존재)
- Version-1 & 2
- TimeStamp 기반의 UUID 생성
- Version-3 & 5
- name과 namespace의 MD5 또는 SHA-1 해시 기반의 UUID 생성
- Version-4
- 완전 랜덤한 UUID 생성
- UUID vs B-Tree
- UUID : 시점과는 별개로 랜덤한 값 생성 (7분 10초마다 반복되긴 함), 상대적으로 긴 문자열 (CHAR(32) or VARCHAR(32))
- 해당 UUID 값을 사용하는 칼럼에 인덱스 설정시 B-Tree 인덱스 성능에 안좋게 됨
- UUID 컬럼 인덱스의 경우, 전체 인덱스 크기 만큼의 메모리 필요 (Index WorkingSet)
- UUID는 시간에 따라 단조증가 하지 않음 (UUID Version-6 도 제안이되었으나 Drop됨)
- UUID는 문자열이 아닌 16 Bytes BINARY 이지만 32 CHARS 로 인식하여 저장된경우가 많음
-> 길이가 상당하기 때문에 메모리 버퍼 크기가 많이 필요함
-> BIGINT uid 로 변경하는 방법이 있음
- 정보 노출 우려로 UUID를 사용해야 한다면, Snowflake-uid를 사용하여 예측 불가하게 할 수 있음
- 그러나 수많은 단점이 있어서 추천하지 않음
- 권장방법
- 내부적으로는 AutoIncrement 또는 Timestamp 기반의 프라이머리 키
- 외부적으로는 UUID 기반의 유니크 세컨드리 인덱스를 사용
- PK를 아무 이유없이 UUID로 사용하는것은 자원낭비만 있을 뿐임
Ep.15 풀스캔 쿼리 패턴 및 튜닝
1. 컬럼이 가공되는 경우
- 연산, 함수, 형변환(문자열 칼럼을 숫자랑 '=' 진행함)
- 형변환 우선순위에 따라 id 칼럼이 가공되지 않을수도 있음
- 이러한 요소 고려가 어렵기 때문에 반드시 형에 맞게 쿼리를 작성해야함
2. 인덱싱 되지 않은 컬럼을 조건절에 OR 연산과 함께 사용
- AND 조건은 인덱싱된 컬럼 우선으로 계산하여 인덱싱을 타지만 OR은 인덱싱 타지 않은 칼럼이 하나라도 있으면 FULL 스캔 진행
3. 복합 인덱스의 컬럼들 중 선행 컬럼을 조건에서 누락
- 인덱스 컬럼을 복합적으로 설정할때 순서에 맞게 써야함
4. LIKE 연산에서 시작 문자열로 와일드 카드를 사용
- LIKE 'Esther%'; 과 같이 Prefix 는 인덱싱을 타게됨
5. REGEXP 연산 사용
- 어떻게 할수 없음
6. 테이블 풀스캔이 인덱스 사용보다 더 효율적인 경우
- 데이터의 분포에 따라 인덱싱을 탈지 풀스캔을 할지 optimizer 가 결정하게됨
(번외) NOT Equal 조건과 IS NOT NULL 조건
- NOT Equal, IS NOT NULL 도 데이터 분포도에 따라 적절하게 인덱스를 탐
Ep.16 COUNT(*) vs COUNT(column)
- COUNT(column) 에서 column 이 NULLABLE 하거나 DEFAULT NULL 인것은 카운팅 안함
=> Return a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement
=> COUNT(NULL) => 0
- WHERE 조건 가진 COUNT()
- 커버링 인덱스, Non-커버링 인덱스
- WHERE 조건 없는 COUNT()
- ha_records() 스토리지 API 사용
- ha_index_next() 스토리지 API 사용
-> InnoDB 스토리지 엔진이 여러 API를 제공함
-> 특성에 따라 큰 차이가 있을수 있지만 실행계획은 차이가 없음 (그래서 API로 구분함)
- 실행 계획이 동일하면 성능도 동일한가?
- COUNT(*), COUNT(not_null_column) 는 스토리지 엔진의 ha_records() API를 호출함
- 레코드 건수에 관계없이, 1회만 호출됨
- COUNT(*) 쿼리는 레코드로부터 컬럼 추출 수행하지 않음
- innodb_parallel_read_threads >=2의 경우,
COUNT(not_null_column) 쿼리는 컬럼 추출 수행하지 않음
- COUNT(nullable_column)
- ha_index_next() 스토리지 엔진 API 사용
- 레코드 건수만큼 호출됨
- 주어진 컬럼에 대해서 Eval 작업 필요 (레코드의 컬럼 추출 필요)
- null인지 판단위해 컬럼 추출 작업 필요
COUNT(fd2)는 스토리지 엔진의 ha_index_next() API 를 호출함
- innodb_parallel_read_threads 설정
- MySQL 8.0 버전은 조건없는 COUNT() 쿼리에 대해서 병렬 처리 지원 (기존 쓰레드 4개)
- 병렬 처리를 비활성화하면 결과는?
- 실행 계획은 동일하지만
- 쿼리 성능은 달라짐
- records 에서 컬럼을 추출하는 일이 생김
- COUNT(*) 을 사용하는것이 항상 좋은 결과를 낼 경우가 많음
- 크기가 작은 인덱스를 선택할 가능성이 높음
- 커버링 인덱스란? (Extra -> Using Index => 커버링 인덱스 )
- 쿼리의 성능 향상
- 쿼리가 인덱스를 사용할 수 있도록 튜닝
- 최소의 레코드만 테이블 데이터를 가져오도록 튜닝
- ConveringIndex 실행 계획이 Non-ConveringIndex 보다 실행 계획이 10배 정도 빠름
- 전체 건수가 필요한 경우, COUNT(*)만 사용 권장
Ep.17 SELECT ... FOR UPDATE [ NOWAIT | SKIP LOCKED ]
- SELECT ... FOR UPDATE NOWAIT
- 잠금 대상 레코드가 이미 다른 세션에 의해 잠겨있는 경우, 잠금을 대기하는 것이 아니라 바로 에러를 반환
- innodb_lock_wait_timeout 옵션을 0으로 설정한 것과 유사한 효과 ( 옵션의 기본값은 50 )
- 트랜잭션 내에서 NOWAIT 쿼리를 실행하여 에러가 반환되더라도, 열어둔 트랜잭션은 그대로 유지됨
- SELECT ... FOR UPDATE SKIP LOCKED
- 잠금 대상 레코드 중에 다른 세션에 의해 이미 잠금이 걸려 있는 레코드는 스킵하고, 잠금이 걸려있지 않은 레코드를 잠그고 반환
- 따라서 잠금 대상 레코드가 비결정적(Non-deterministic)으로 정해짐
( 즉 , 쿼리 실행 시 어떤 레코드가 잠금이 걸릴지 예측하기 어려움 )
- 잠금 대상 레코드들이 모두 잠금이 걸려있는 경우에는 빈 결과를 반환
- 반환되는 결과 데이터는 없다하더라도, 경우에 따라 Gap-Lock을 점유할 수 있음
- ORDER BY & LIMIT 절과 함께 많이 사용됨
- 선착순 쿠폰 지급같은 예시
- NOWAIT & SKIP LOCKED with JOIN
- SELECT. FOR UPDATE SKIP LOCKED 를 JOIN 하는 경우에 썼을 경우,
1:N 관계에 있는 테이블이 서로 JOIN 할때 N쪽의 테이블도 모두 SKIP (잠금) 하게 됨
=> OF 테이블 옵션을 두어서 해결 같음 (FOR UPDATE OF c SKIP LOCKED )
- NOWAIT은 잠금 대기가 발생하는 경우가 비즈니스 로직적으로 비정상적인 경우이며, 이 경우 굳이 잠금을 대기하지 않고 바로 빠르게 에러 처리되기를 원하는 경우 사용 가능
- SKIP LOCKED는 선착순 쿠폰 발급이나, 데이터 큐잉 후 배치잡 처리 등에 유용하게 사용 가능
Ep.18 UNION vs UNION ALL
- UNION
- 두개 이상 쿼리 결과의 합집합
- UNION은 결과 집합의 레코드 확장
- 임시테이블을 통한 가공작업이 필요함
- 특정 조건을 만족하면 임시테이블 통한 가공작업 없이 빠르게 가져올 수 있음
- c.f) JOIN은 결과 집합의 컬럼 확장
- UNION ALL vs UNION DISTINCT
- 두 집합의 중복 제거 여부
- UNION ALL 은 중복 허용
- 별도의 가공작업이 필요없어서 DISTINCT 보다 빠르게 결과 반환
- UNION DISTINCT는 중복 허용 안함 (중복 제거 필요)
- 정렬하고 검사하는 작업이 필요함
- 같은 결과를 반환한다면 UNION ALL을 사용하는것을 권장
- 어플리케이션 로직에서 중복을 제거하는 방법이 좋을수도 있음
- UNION DISTINCT
- MySQL 서버에서 중복을 제거하는 방법
- UNION 결과 저장용 임시테이블을 만듦 (기존 테이블들을 SELECT -> INSERT or DROP 진행)
- 레코드의 중복 여부는 PK나 UniqueIndex가 아닌 레코드의 모든 컬럼 값 조합으로 중복 여부 확인
- UNION ?
- 많은 경우, ALL 이나 DISTINCT 키워드가 생략된 UNION 사용
- UNION ALL 에서 "ALL" 이 생략되면, DISTINCT 가 됨
-> UNION ALL 을 쓸수있다면 명시해서 쓸것
Ep.19 JSON 타입 활용
JSON 데이터 타입
- JSON 형식의 데이터를 손쉽게 저장 및 조회, 관리 가능
- 빌트인 함수들을 사용해 JSON 데이터 조작 가능
- 저장된 JSON 데이터의 일부만 업데이트 가능 (부분 업데이트 기능)
- 저장된 JSON 데이터의 특정 키에 대해서 인덱스 생성 가능
JSON 데이터 저장
- 필요에 맞게 컬럼 정의
- 기존 테이블에 JSON 컬럼 추가 시 주의
- DEFAULT 값이 NULL 이면 ALGORITHM=INSTANT 는 가능
- DEFAULT 값이 NULL 이 아니면 COPY 방식으로 진행되어, ONLINE (동작중 DML 불가) 방식 불가
- 함수를 사용해 데이터 저장
- 배열 : JSON_ARRAY 함수 사용
- 객체 : JSON_OBJECT 함수 사용
- 직접 값을 입력해 저장
- 문자열 형태로 입력 '[]' , '{}'
- 객체 데이터 입력시 key 는 "key" 와 같이 쌍따옴표로 입력
- 저장시 유효성 검사 수행
- 유효하지 않으면 에러메시지발생 및 쿼리 실패
- 저장 구조
- 최적화된 바이너리 포맷으로 저장
- 중복된 키-값은 마지막 순서의 데이터로 저장
- JSON 데이터 내 키들을 정렬해서 저장
- 키는 JSON 데이터마다 중복해서 저장되므로, 적당한 길이로 사용하는 것을 권고
- 키 개수, 데이터 사이즈, 키 주소, 값 주소, 키, 값 형태로 저장
JSON 데이터 조회
- JSON Path
- JSON 데이터의 요소를 쿼리하는 표준화된 방법
- 대표적인 연산자
- $ : JSON 데이터 계층의 루트(최상위)를 의미
- . : 객체의 하위 요소들을 참조할 때 사용
- [] : 배열 내의 요소에 접근할 때 사용
- 함수
- JSON_EXTRACT(json_data, path[, path]...)
- 연산자 (함수와 달리 하나의 키 결과만 조회할수 있음)
- Column-path Operator
- json_column -> path
=> JSON_EXTRACT
- Inline-path Operator
- json_column ->> path
=> JSON_UNQUOTE (쌍따옴표, 따옴표를 제거할 결과를 반환함)
- JSON_CONTAINS(target_json, candidate_json[,path])
- target_json에 candidate_json 가 포함돼있으면 True(1), 아니면 False(0) 반환
- path가 주어진 경우에는 지정된 path에 위치한 값에 대해서만 확인
- JSON_OVERLAPS(json_data1, json_data2)
- 두 JSON 데이터가 하나라도 공통된 값을 가지면 True(1), 그렇지 않으면 False(0)를 반환
- value MEMEBER OF(json_array)
- value에 주어진 값이 json_array에 포함되는지 확인해서 True(1) 또는 False(0) 값 반환
JSON 데이터 변경
- MySQL에서 제공하는 다양한 함수들을 사용하여 JSON 데이터를 보다 세밀하게 조작 가능
- JSON_INSERT(), JSON_REPLACE(), JSON_SET(), JSON_REMOVE() 등
- JSON 데이터의 특정 키 값만 변경 시 변경된 키 값에 대해서만 데이터를 업데이트 하는 "부분 업데이트" 최적화 제공
- 불필요하게 전체 데이터를 다시 쓰지 않으므로 쿼리 성능이 향상됨
- 부분 업데이트가 수행되는 조건
- JSON_SET(), JSON_REPLACE(), JSON_REMOVE() 함수만 가능
- 함수의 인자로 주어진 컬럼과 변경 대상 컬럼이 일치해야 함
- 값 변경 시 기존 값을 새로운 값으로 "대체" 되는 형태여야함. 새로운 키-값이 추가되는 변경 등부분 업데이트 처리 불가
- 대체되는 새로운 값은 기존에 저장된 값보다 저장되는 크기가 작거나 같아야함
- JSON_STORAGE_SIZE, JSON_STORAGE_FREE 로 확인 가능
- 기존 저장된 공간보다 크게 저장할 경우, 부분업데이트가 아니라 일반 업데이트 되기 때문에 쿼리 시간이 더 오래걸림
- 바이너리 로그와 부분 업데이트
- MySQL 8.0 에서 바이너리 로그 기본 설정
- log_bin=ON, binlog_format=ROW, binlog_row_image=full, binlog_row_value_option='' (empty string)
- 기본으로 설정된 값은 부분 업데이트 성능을 저하시킬 수 있음
- JSON 데이터가 부분업데이트 되더라도 binary log에 전체가 기록됨
- binlog_format=STATEMENT => 실행 쿼리만 저장함, ROW는 결과값도 기록
JSON 데이터 인덱싱
- 특정 키 값에 대해 인덱싱 가능
- 함수 기반 인덱스 (Function Based Index) 로 인덱스 생성
- 문자열 값을 인덱싱하는 경우 따옴표 및 콜레이션 주의
- inline path를 쓰면 long Text가 추출되는데 메모리 크기를 지정해주지 않아 인덱싱 불가
-> CAST를 통해 저장되는 타입에 맞는 걸로 변환해야함
- 배열 인덱스 사용시 주의사항
- MEMBER OF(), JSON_CONTAINS(), JSON_OVERLAPS() 함수만 배열 인덱스 사용 가능
- 아직 기능이 성숙하지 못해 버그가 존재하는 상황이므로 유의해서 사용
- 기타 여러 제한 사항
- 온라인으로 인덱스 생성 불가
- 커버링 인덱스 & 범위 스캔 불가
- 빈 배열 식별 불가
- 등등 ..
- 문자열 인덱싱시 주의사항
- 따옴표 포함 유무에 따라 같은 조건이라도 쿼리 결과가 달라지므로 주의 필요
- JSON 내 문자열 데이터 처리시 utf8mv4_bin 콜레이션 사용됨 (대소문자 구분 O)
- CAST() 에서는 문자열을 utf8mb4_0900_ai_ci 콜레이션으로 반환 (대소문자 구분 X)
- 이로 인해 쿼리에서 인덱스 사용 여부에 따라 결과 데이터가 다를 수 있음
TEXT 타입 vs JSON 타입
TEXT 타입 (vs JSON 타입)
- 데이터를 저장후 전체 데이터 조회하는 패턴으로 주로 사용
- JSON 형식이 아닌 데이터도 저장될 수 있음
- TEXT 타입 컬럼도 JSON 함수 사용 및 특정 키 값에 대한 인덱스 생성 가능
JSON 타입 (vs TEXT 타입)
- JSON 데이터의 특정 키 값만 주로 조회하고 변경
정규화된 컬럼 vs JSON 컬럼
- 정적 스키마 vs 유연한 스키마
- 데이터의 일관성 및 유지보수 용이성 vs 개발 편의성
- 쿼리 최적화와 인덱싱의 편리성 vs 복잡합 형태의 쿼리와 인덱싱 방식
- JSON 컬럼에 저장되는 데이터 사이즈가 클 수록 조회 성능은 저하됨
- JSON 데이터가 필요한 경우에만 SELECT
Ep.20 데드락
- 생각의 순서대로 프로그램 작성
- DBMS는 다음에 실행될 SQL을 예측하지 못함
- 이런 형태에서 Dead Lock 발생 가능성 높음
- 정렬된 순서대로 처리
- "-" 처리 먼저 "+" 처리는 나중에 수행하던 방식 => user_id 순서대로 처리
- Index (user_id) 순서대로 잠금 실행시, Lock Wait은 발생할 수는 있지만, Deadlock은 없음
- 의문 사항
- 왜 Shared-Lock을 먼저 걸고, Exclusive-Lock을 걸어야 하는가?
- 어떻게 이미 삭제된 레코드에 대해서 Lock을 걸 수 있는가?
- MySQL 서버의 잠금 구현 방법
- Primary key는 1개의 유니크한 값만 허용
- Unique 제약 보장을 위해서, DML은 레코드 존재시 Shared-Lock 필요
- 레코드는 삭제되더라도, Deletion-mark(Tomb stone)만 설정 (필요시까지)
- 이런 이유로 Tx-2와 Tx-3은 존재하지 않은 레코드에 대해서 S-lock & X-lock까지 순서대로 필요
- MySQL Deadlock 감지
- DeadLock detection thread가 모든 트랜잭션이 획득 또는 대기하고 있는 잠금 Graph를 계속 감시
- 동시 트랜잭션이 많은 경우, deadlock 체크 작업으로 인한 대기 발생
- 어떤 회사에서는 DeadLock detection thread를 비활성화하기도 함
- innodb_deadlock_detect = OFF
- innodb_lock_wait_timeout 기본 값은 50초
- innodb_lock_wait_timeout= 2~3초 정도로 조정 검토 필요
- 구글에서는 테이블 및 로직이 단순하기 때문에 deadlock detection thread 로 인한 성능 손실만 있어서 해당 옵션을 끄고 있음
- MySQL Deadlock 처리
- MySQL은 롤백이 쉬운 트랜잭션을 Victim trx로 선정
- 롤백이 쉬운 트랜잭션 == Undo 레코드가 적은 트랜잭션
- Victim으로 선정된 트랜잭션은 강제 롤백 처리
- 남은 트랜잭션은 정상 처리
- 배치 작업과 서비스 쿼리가 경합하면, 항상 배치 프로세스가 살아남게 됨
MySQL Deadlock 해석 어려움
- 동일 SQL 문장이더라도, 항상 동일한 잠금을 사용하지 않음
- 현재 데이터 상태, 동시 실행중인 잠금 경합 DML (실행시점)
- Record 뿐만 아니라, Record 간의 간격 (GAP)도 잠금의 대상
- DeadLock 시점의 관련 트랜잭션을 모두 로깅하지 않음
- 사용하는 잠금이나 상황에 따라서 중간 중간 잠금이 해제되기도 함
- 각 잠금의 Lifecycle이 다름 ( 기본은 트랜잭션 단위 )
- 찰라의 시간차이로 Deadlock이거나 아닐 수 있음
- 잠금의 대상은 모든 인덱스 키
- Deadlock 원인을 찾는것은 매우매우 어려운 일
- 프로그램 코드의 오류로 DeadLock이 발생
- 개발 능력이나 DB 문제로 DeadLock이 발생
- Deadlock이 발생하면, 프로그램 코드 개선 필요
=> 하지만,
- DeadLock은 회피할수 있는 경우도 있지만, 회피할수 없는 경우가 더 많음
- DeadLock이 발생했다고해서, UK PK를 삭제할 수는 없음 (가능하다면 모델링 시점에서 최소화)
- UK는 성능상 장점이 거의 없어서 제거하는게 좋음 (단순 중복 방지)
- DeadLock의 발생 빈도와 서비스 영향도에 따라서 무시 (로깅 및 별도 재처리)
- 프로그램 코드에서의 트랜잭션 재처리 (retry)하는게 더 좋을수도 있음
- Retry 코드를 넣었다고해서, 코드 품질이 낮아지는 것은 아님
Ep.21 Join Update & Join Delete
다른 테이블의 컬럼 값을 참조해서 Update / Delete 하고 싶은 경우
한번에 여러 테이블에 대해 Update / Delete 하고 싶은 경우
- JOIN Update
- VALUES ROW 를 하나의 테이블 형태로 만들고 JOIN 하여 벌크 업데이트 할 수 있음
- JOIN Delete
- JOIN delete, JOIN Update 에서 Optimizer Hint 를 사용할 수 있음
- /*+ JOIN_FIXED_ORDER() +*/
주의사항
- 참조하는 테이블들의 데이터에는 읽기 잠금이 발생하므로, 잠금경합이 발생할 수 있음
- Join Update의 경우 조인되는 테이블들의 관계가 1:N 일때, N테이블의 컬럼 값을 1 테이블에 업데이트하는 경우 예상과는 다르게 처리될 수 있음 ( N:M 관계도 마찬가지 )
- Join Update & Join Delete 쿼리는 단일 쿼리보다 형태가 복잡하므로, 반드시 사전에 쿼리 실행 계획 확인 필요
Ep.22 커넥션 관리
Connection 메모리 사용량
- 타 DB 보다 커넥션이 사용하는 메모리 사용량이 낮음
- 커넥션 스레드가 쿼리를 처리하는게 아니라 공유 스레드가 쿼리를 처리
- 일반적으로 Connection 당 16kb ~ 3MB 메모리 사용
- CPU 사용량이 증가할 수 있음
- Server-side Max (max_connections)
- MySQL : 최대 8k ~ 10k
- postgreSQL : 3k ~ 5k (Process별 점유 메모리 크고, Linux page cache 필요)
- postgreSQL은 메모리가 굉장히 중요함 (미들웨어가 필수적인 상황 mySQL은 그러지 않음)
- MySQL 서버의 연결 처리 및 쓰레드는 가볍지만, 많으면 부담
- Client-side Pool Max(connection)
- 응용 프로그램의 특성에 따라서 상이하지만,
- Connection pool 사용 필수 & 시작 설정은 MAX 20~30
- MAX로 하고 줄이는것은 튜닝이 쉽지 않음
- Min = Max * (70% ~ 90%)
- MIN=MAX 설정이 성능상 좋지만, Connection 부족 현상 판단 어려움
- MySQL 서버의 Connection = Max(conn)인지 아닌지 확인 -> MAX를 늘려야 할지 판단
- 앱서버가 100개이고, MAX(ConnectionPool)=200 이면 20k connection 필요
- (필요시) Middleware를 이용한 Server-side connection 재활용
- Connect Timeout 설정
- Connection Open 또는 Connection Pool 에서 커넥션 가져오는 시간
- 밀리초 이하의 값 설정 권장하지 않음
- 인프라 여건에 맞춰서 3~10초 정도의 시간 설정 사용
- Connection을 가져오지 못할 경우, 일반적으로 다른 Fallback 전략이 없음
- 일시적인 DB 서버 과부하시, Connection 요청 및 취소 반복으로 오히려 부가적인 자원 소모
- Connection 가져오기 실패시, 사용자에게 오류 화면을 보내는 경우라면 Timeout 짧게 할 수 있음
- Query Timeout 설정
- Query Timeout시, 취소 및 동일 쿼리 재요청시 오히려 DB 서버의 부담 가중
- 재요청을 하지 않는다면. Timeout 짧게 할 수 있음
- Idel Timeout 설정
- Connection Pool 사용시 자주 발생하는 에러
- 에러 회피를 위해서 Idel Connection Timeout을 매우 짧게 설정 ( e.g. ~ 60 seconds) 하는것은 권장하지 않음
- DBMS 서버의 커넥션은 이렇게 짧게 사용하고 버리는 자원 아님
- 최소 20 ~30분 이상 설정 권장
- 에러 회피를 위해서 Pool의 Connection Validation 기능 최대한 활용
- 주요 기능의 경우, Retry 로직 구현 권장
- Middleware (Proxy)
- RDS Proxy, MySQL Router, ProxySQL
Ep.23 테이블 파티셔닝
테이블 파티셔닝이란?
- 하나의 테이블을 물리적으로 여러 테이블로 분할해서 데이터를 저장하는 기법
- 사용자는 기존처럼 하나의 테이블로 인식해서 사용 가능
- 테이블의 특정 칼럼이나 계산식을 기준으로 해서 날짜/숫자 범위나 리스트, 해시 형태 등으로 분할 가능
테이블 파티셔닝이 필요한 이유
- 삭제 가능한 이력 데이터들을 효율적으로 관리
- e.g. 로그성 데이터들이 저장되는 테이블에 파티셔닝 적용
- 보관 기간에 따라 일정 기간 지난 데이터들을 제거하는 경우, 데이터 삭제가 아닌 파티션 드랍으로 처리
- 명령문 하나로 손쉽게 처리 가능
- 사용된 디스크 공간을 온전히 반환 가능
- 자원 사용 효율 증가 및 쿼리 성능 향상
- e.g. 게시판과 같이 최근에 저장된 데이터들 위주로 조회하는 경우
- 날짜 범위로 파티셔닝하여 각 파티션이 특정 기간의 데이터만을 보유하도록 설정
- 쿼리가 특정 날짜 범위의 데이터를 요청할 때, MySQL은 조건 범위에 해당 하지 않은 파티션은 쿼리 처리에서 제외
- 이러한 과정을 "파티션 프루닝" 이라고 함
- 물리 메모리에 최근 데이터가 담긴 파티션만 저장하고 접근함으로써 효율적으로 사용 & 쿼리 성능도 향상
파티셔닝 타입
- RANGE, HASH, LIST, KEY
- 일정한 범위로 파티션을 분할해서 사용하는 경우가 대다수
- 제일 많이 사용하는 타입은 RANGE & RANGE COLUMNS
파티션 테이블 사용 제약 및 주의사항
- 외래키 및 공간 데이터 타입, 전문검색 인덱스 사용 불가
- 파티션 표현식에 MySQL 내장 함수들을 사용할 수 있으나, 모두 파티션 프루닝 기능을 지원하는 것은 아님
- TO_DAYS(), TO_SECONDS(), YEAR(), UNIX_TIMESTAMP() 만 지원
- 테이블의 모든 고유키 (PK, UK)에 파티셔닝 기준 컬럼이 반드시 포함되어야 함
- 글로벌 인덱스가 아닌 "로컬 인덱스" 구조
- WHERE 절에 파티셔닝 기준 컬럼에 대한 조건이 포함되어야 필요한 파티션들에만 접근하는 최적화인 "파티션 프루닝"이 적용됨
- 값이 자주 변경되는 컬럼을 파티션 기준 컬럼으로 선정해서는 안됨
- RANGE
- 파티션 표현식에 컬럼 또는 계산식 허용
- 하나의 컬러만 사용 가능
- 계산식 또는 컬럼 모두 정수형 값만 허용
- RANGE COLUMNS
- 파티션 표현식에 컬럼만 허용
- 하나 이상의 컬럼 사용 가능
- 정수형 값 뿐만 아니라 문자형, 숫자형도 모두 가능
- RANGE 사용시 초까지 저장된 timestamp(6) 의 경우, 정수형 변경이 어려워서 FLOOR 함수로 최대정수를 뽑아줘야하는데, 파티션프루닝을 할수 없이 전체 파티션에 접근하게됨
- RANGE COLUMN datetime(6) 를 활용하는게 좋음
파티션 추가
- 마지막 파티션 이후 범위의 신규 파티션을 추가
- MAXVALUE 파티션이 존재하지 않는 경우 : ADD PARTITION
- MAXVALUE 파티션이 존재하는 경우 : REORGANIZE PARTITION
- 기존 파티션들 사이에 새로운 파티션 추가 : REORGANIZE PARTION
파티션 제거
ALTER TABLE user_log DROP PARTITION p202401;
파티션 비우기
ALTER TABLE user_log TRUNCATE PARTITION p202401;
- 주로 접근하는 범위 또는 삭제 대상 범위를 바탕으로 일/주/월/년 등으로 필요에 맞게 파티셔닝
- 예상치 못한 상황을 대비해 MAXVALUE 파티션 사용
- 저장 대상 파티션이 존재하지 않는 경우 에러 발생
- 파티션 추가/삭제 시 잠금(메타데이터 락)이 발생하므로, 가능하다면 트래픽이 적은 시점에 수행
- 필요시 파티션 관리 프로그램 (자동화 스크립트) 개발
파티션 테이블과 인덱스 사용
- 고유키가 아닌 일반 보조 인덱스의 경우 자유롭게 구성해서 생성 가능
- 파티션 별로 동일한 구조의 인덱스가 생성됨
- 파티션 프루닝을 통해 접근 대상 파티션 선정 후 인덱스 스캔
- 파티션 프루닝은 쿼리의 WHERE 절에 파티셔닝 기준 컬럼에 대한 조건이 있어야 가능
- UNIX_TIMESTAMP()나 YEAR() 함수를 사용해도 꼭 표현식과 동일한 형태로 WHERE절에 명시해야 하는건 아님
Ep.24 DBMS 활용 (배치 처리 주의사항)
대용량 작업
- 개발자 생각
- 최대한 굵고 짧게
- 동시에 많은 쓰레드로 빠르게 처리 완료
- DBA 생각
- 가능한 가볍게 ( + 가능한 짧게)
- 소수의 쓰레드로 최소의 DBMS 자원 소모
DBMS 서버는 공유 자원
- 특정 서비스에서 과도한 자원 점유시, 다른 서비스의 처리 지연 유발
- 떄로는 다른 서비스의 쿼리 실패 & 장애 유발
- DBMS 서버 처리 용량에 맞게 동시성 제어 필요
배치 작업 실행시, 서버의 사양별로
- 적절한 개수의 동시성 제어 필요
- 처리가 밀린 경우, 빠르게 처리하되 밀착 모니터링 및 제어 필요
문제 발생시 (해결 어려움)
- CPU 등 자원 사용 급등의 원인 분석 필요
- 장애를 만들기는 쉽지만, 장애 원인을 역으로 분석하는 작업은 시간 소모적임
Long Transaction
- Idel long transaction
- auto_commit = OFF 상태 또는 명시적 트랜잭션(BEGIN TRANSACTION) 사용시
- BEGIN 이후 또는 쿼리 실행 이후 대기 상태로 남은 트랜잭션 (COMMIT 또는 ROLLBACK 실행 전)
- Active long trasaction
- auto_commit 모드 무관하게 오랜시간동안 실행되는 쿼리 실행중인 트랜잭션
MySQL 아키텍처
- Non-locking constitent read 기능은 MVCC를 활용하여 구현
- MVCC는 undo log를 활용
- undo log는 일정 시간 이전은 purge 되어야 함
- undo log는 메모리를 사용하기 때문에 오래된것을 삭제함
- Undo log 의 부작용
- 오래된 트랜잭션은 오래 전의 undo log를 purge 하지 못하도록 함
- Undo log가 많이 쌓이면, 많은 메모리 및 Disk Read/Write를 초래
- 자원 사용률이 높아지고, 쿼리 성능 저하됨
MySQL은 트랜잭션 격리수준이 RepeatableRead 가 기본이며, 변경된 모든 이력이 저장되는 Undo log를 활용하여 MVCC를 구현하고
MVCC를 이용하여 Non-locking constitent read 기능을 지원함
격리된 배치 서버 운영
- OLTP & OLAP 용도 별 DBMS 서버(Replica) 구축
- Long Transaction의 경우 주의 필요
트랜잭션 제어
- 작은 트랜잭션 단위로 나누어서 작업 수행
