Code Complete

대시 기호 제거 후 UNHEX() 함수를 사용하여 16바이트 숫자로 변환 후 BINARY(16) 칼럼에 저장, 해당 값은 HEX() 함수를 이용하면 16진수로 볼 수 있다.

일반적으로 CHAR(32)에 저장했을 때보다 저장 용량이 절반으로 줄어들고 비교 속도가 빨라진다.

현재 프로젝트에서 사용 중인 MySql에서 내림차순 인덱스를 지원하지 않는다.

내림차순 정렬 후 페이지 단위로 셀렉트해야하는 경우 오름차순으로 정렬된 인덱스 때문에 내부적으로 불필요한 정렬이 계속 발생한다.

그러면 어떻게하면 되느냐.. 그냥 MSSql을 사용하자.. 

그럴 수 없다면 꼼수를 사용하면 되는데..

DateTime 형식의 내림차순 인덱스를 만든다고 한다면 위의 그림을 참고하자.

Min, Max는 DateTime 형식의 Min, Max 값을 생각하고 숫자 1, 2번은 데이터 발생 시점으로 생각한다면,

일반적으로 RegDateTime가 오름차순으로 구성되어 있을 것이고 (내림차순 인덱스가 지원되지 않는다) 필요한 데이터를 내림차순 정렬 후

페이지 단위로 셀렉트한다면 DBMS에서는 정렬 후 해당 페이지 단위만큼 셀렉트할 것이다.

당연히 복합 인덱스 컬럼이 오름차순으로 셋팅되어 있다면 이후에 발생한 2번이 두번째에 구성되는게 맞다. 

그러면 여기서 바라보는 시각을 비틀어 발생 시각이 아니라 Max까지 남은 시간을 생각해보면, 2번 데이터가 발생한 시점에 Max까지 

남은 시간 3번이 4번보다 더 적은 것을 알 수 있을 것이다.

답은 나왔다. 인덱스 컬럼을 3, 4번으로 사용하면 오름차순 인덱스만 지원하는 MySql의 특성을 만족 시키면서 필요한 데이터를 내림차순으로

얻을 수 있다. 

DBMS에서 정렬이 발생하면 왜 안되는지는 찾아보면 아래 링크

링크 : 정렬이 왜 나빠?

링크 : 

1. 1. 데이터 파일의 구조
2. 2. DBMS 메모리 구조
3. 3. 인덱스 기본
4. 4. 정렬을 제거하는 방법
5. 5. 프로시져 캐시 경합 확인하기
6. 6. Loop 쿼리 제거하기
7. 7. 튜닝 결과 샘플 보기

데이터베이스 작업 요청에 따른 Call 의 종류를 살펴보면

• Parse Call : SQL 파싱을 요청하는 Call
• Execute Call : SQL 실행을 요청하는 Call
• Fetch Call : SELECT 문의 결과 데이터 전송을 요청하는 Call

일반적으로 서버에서 DB에 쿼리를 요청하는 코드는 

응답이 돌아올 때까지 해당 쓰레드는 Blocking 상태로 다음 작업을 할 수 없다.

만약 바인딩 변수로 대상 아이템의 시퀀스를 받아 해당 아이템 정보를 얻는 쿼리가 있다고 가정하고 이 쿼리를 이용하여 아이템 30개의 정보를 얻는 서버 로직을 생각해보자.

일반적으로 아래와 같은 형식으로 작업 할 것이다.

이런 형태의 코드는 DBMS 내부적으로 Parse Call, Execute Call 이 각각 30회씩 발생하고, 추가로 30회의 응답 대기가 발생한다.

Loop 쿼리는 대부분의 시간을 네트워크 구간에서 소비하거나 Call 이 발생할 때마다 OS로부터 CPU와 메모리 리소스를 할당 받으려고 대기하면서 소비한다.

만약 One 쿼리를 만들어 실행 시킨다면

Parser Call, Execute Call 이 각각 1회씩만 발생된다. 추가로 가장 큰 이득은 Blocking 상태 (네트워크 구간 + 리소스 할당 대기 + 쿼리 처리)가 1회로 끝나는 것이다.

One SQL은  대상 그룹을 문자열로 넘겨 받아 파싱 후 테이블 변수로 만들어 조인한다.

만약 테이블 변수와 조인을 하게 되는 대상 테이블이 동일한 페이지에 정렬되어 Page 접근이 적어진다면^[각주:1] 조인 효율은 극대화 될 것이다.

One Call 쿼리에 30개의 대상 아이템 시퀀스를 문자열로 넘긴다면 대략  "1, 2, ..., 30"  이런 형식이 될 테고

해당 문자열을 파싱해서 테이블 변수로 만드는 쿼리는 

대략 이런 형식이 될 것이다.

만약 테이블 변수를 사용하지 않고 파싱 중간중간 결과를 Select 한다면 TDS 패킷 수량이 많아진다. 

즉 DB에서 서버에 전달하는 패킷이 더 많아지기 때문에 불필요한 비용이 발생할 수도 있다. 

조인 대상 테이블 구조에 따라 랜덤 액세스 비용이 발생할 수도 있기 때문에 꼼꼼히 살펴볼 필요는 있지만 Blocking 상태에서 소모되는 시간보다는 훨씬 적을 것이다.

상대적인 IO 비용

링크 : 

1. 1. 데이터 파일의 구조
2. 2. DBMS 메모리 구조
3. 3. 인덱스 기본
4. 4. 정렬을 제거하는 방법
5. 5. 프로시져 캐시 경합 확인하기
6. 6. Loop 쿼리 제거하기
7. 7. 튜닝 결과 샘플 보기

MS SQL의 시스템 공유 메모리 영역을 Memory Pool 이라고 한다.

여러 쓰레드가 동시에 액세스 할 수 있는 메모리 영역이다.

캐시 영역은 매우 다양한데 모든 DBMS가 공통적으로 사용하는 캐시 영역은 위와 같다. 

메모리를 공유하기 위해 액세스 직렬화 메커니즘이 사용된다.

MSSQL은 쓰레드 기반 아키텍쳐이기 때문에 Worker Thread 전용 메모리 영역이 없다. 쓰레드는 부모 프로세스의 메모리 영역을 사용한다.

DB 버퍼 캐시

 데이터 파일로부터 읽어 들인 데이터 Page를 담는 영역을 DB 버퍼 캐시라고 한다.

인스턴스에 접속한 모든 사용자 스레드는 Worker Thread를 통해 DB 버퍼 캐시의 버퍼 블록을 동시(내부적으로 버퍼 LOCK을 통한 직렬화)에 액세스할 수 있다. 

Direct Path Read 메커니즘이 작동하는 경우를 제외하면, 모든 페이지 읽기는 버퍼 캐시를 통해 이루어진다.

즉, 읽고자 하는 페이지를 먼저 버퍼 캐시에서 찾아본 후 없으면 디스크에서 읽는다. 디스크에서 읽을 때에도 먼저 버퍼 캐시에 적재한 후에 읽는다. 

데이터 변경도 버퍼 캐시에 적재된 페이지를 통해 이루어지며, 변경된 블록을 주기적으로 데이터 파일에 기록하는 작업은 Lazy writer 가 한다.

버퍼 블록의 상태

-       Free 버퍼: 비어있거나 (Clean 버퍼), 데이터가 있지만 파일과 동기화되어 덮어써도 무방

-       Dirty 버퍼: 캐시 된 후 변경이 발생했지만 동기화가 되지 않은 상태

-       Pinned 버퍼: 읽기 or 쓰기 작업이 진행 중인 버퍼 블록

데이터 파일 (디스크)  -> 로드 -> Free 버퍼 확보 -> 변경 -> Dirty로 상태 변경 -> 동기화 -> Free

LRU 알고리즘 (Least recently used)

캐시는 유한 자원, 사용 빈도가 높은 데이터 블록 위주로 캐시를 구성하는 알고리즘

모든 버퍼 블록 헤더를 LRU 체인에 연결 후 사용 빈도 순으로 정렬된 상태를 유지

FREE 버퍼가 필요한 순간에 액세스 빈도가 낮은 순으로 데이터 블록을 캐시에서 밀어낸다.

프로시저 캐시

LRU 알고리즘 사용

캐싱된 SQL과 생성계획의 재사용성을 높이는게 중요하다.

테이블, 인덱스, 파일그룹, 데이터 파일, Heap/Index 구조 오브젝트, 익스텐트, 사용자, 제약에 관한 메타 정보를 저장한다.

SQL 파싱 순서

실행 계획을 메모리 풀에 캐싱해 두는 이유는 하드 파싱 비용이 비싸기 때문이다.

실행 계획 경우의 수를 계산하는 방법은 테이블이 n개일 때 조인 경우의 수는 

해당 결과에 조인 종류, 스캔 경우의 수, 테이블 스캔 or 인덱스 스캔 여부 확인, 인덱스를 탄다면 어떤 인덱스를 탈지... 모든 경우의 수를 따지면 하드 파싱은 매우 비싸진다.

때문에 한번 만들어둔 실행 계획을 다시 사용하는 것을 소프트 파싱이라고 한다.

소프트 파싱의 키 값은 SQL 문장 그 자체 (문자열이 이름이다.) 이며 별도의의 SQL ID를 부여하는 DBMS의 경우도 SQL ID와 SQL 문자이 1:1로 대응한다.

소프트 파싱을 잘 활용하기 위해서는 실행 계획을 공유하지 못하는 경우를 이해할 필요가 있다.

- 공백 문자 또는 줄바꿈

- 대소문자 구분

- 주석

- 스키마 명시

- 조건절 비교값

위의 예시들 중 가장 큰 문제가 되는 경우는 마지막 경우이다. 만약 로그인 쿼리나 아이템 페이지 목록을 얻는 쿼리 등을 작성한다고 생각해보면 프로시저 캐시는 다른 이름의 동일한 기능을 하는 실행계획으로 가득찰 것이다. 위와 같은 쿼리를 Literal SQL 이라고 한다. Literal SQL은 프로시저 캐시 효율과 밀접한 관계가 있다.

http://databaser.net/moniwiki/pds/OracleServer/Oracle_Hard_Parsing.pdf

바인드 변수 사용하기

- 실행 계획을 캐시에 저장 후 실행 시점에 값을 바인딩

사용 시 파싱 소요 시간과 메모리 사용량이 감소한다. (CPU, 메모리 사용률)

리터럴 변수 변수 사용은 라이브러리 캐시 경합을 발생시켜 시스템 성능을 저하시킨다.

바인드 변수 사용 시 주의 사항

변수 바인딩 시점이 쿼리 최적화 후, 옵티마이저는 조건절 칼럼의 데이터 분포가 균일하다고 가정한다.

칼럼에 대한 히스토그램 정보가 프로시저 캐시에 있지만 사용할 수 없다. 때문에 균일하지 않을 때 성능이 다르게 나타날 수 있다.

바인드 변수 부작용 극복 노력

Parameter Sniffing - 첫 수행 시 바인드 값을 훔쳐보고 칼럼 분포를 이용해 실행 계획 결정하는 방법

첫 실행 값에 따라 성능이 달라지는 등의 문제가 있다. 보통 해당 기능은 사용하지 않는다.

https://technet.microsoft.com/ko-kr/library/ms191006(v=sql.105).aspx

링크 : 

1. 1. 데이터 파일의 구조
2. 2. DBMS 메모리 구조
3. 3. 인덱스 기본
4. 4. 정렬을 제거하는 방법
5. 5. 프로시져 캐시 경합 확인하기
6. 6. Loop 쿼리 제거하기
7. 7. 튜닝 결과 샘플 보기

개선 방법은 대부분 인덱스 수정 (순서 변경, 추가, 삭제)

더 좋은 해결 방법이 있다면 서버 비지니스 로직 변경 + 인덱스 & 쿼리 튜닝

Loop 쿼리는 One 쿼리로 변경 후 서버 비지니스 로직 변경 등..

내용 : 카드 삭제

문제 : 클러스터형 인덱스 파편화 심화 유발, 스레드당 최대 30번의 Execute Call 발생

개선방향 : 삭제 플래그 형식으로 변경, 삭제코드를 array로 넘긴 후 파싱 Execute Call 감소

쿼리 수정

-    삭제 대상 시퀀스를 문자열로 조합 후 쿼리에 전달 해당 쿼리는 파싱 후 처리

-    수정 후 Execute Call 은 스레드당 무조건 한 번

내용 : 경험치 증/감

문제 : 조건문 적용 순서 오류, 불필요한 SELECT, 스키마 누락

개선 방향

-    조건문을 직관적으로 변경

-    UPDATE 구문 수정으로 첫번째 SELECT 제거

-    스키마 추가

결과

-    SELECT문 2 -> 1

-    수행 시간 12% 감소

내용 : 업그레이드 카드 목록 얻기 (레전드 만들기)

문제 : 랜덤 액세스 발생이 심각함

개선 방향 : 랜덤 액세스 제거 & 정렬 제거, 비클러스터형 인덱스 수정

결과

-    랜덤 액세스 제거

-    인덱스 순서를 변경하면 정렬 제거가 가능하다. (CharacterNO ASC 두 번째로)

-    해당 쿼리에 맞는 인덱스 추가 가능 ( INSERT I/O 비용 0.01 증가 )

내용 : 업그레이드 카드 목록 얻기 (특정 클래스만

문제 : 랜덤 액세스 발생 심각함

개선 방향 : 랜덤 액세스 제거 & 정렬 제거, 비클러스터형 인덱스 수정

결과

-    랜덤 액세스 제거

-    정렬 제거 (특정 클래스만 탐색하기 때문에 가능)

-    처리 비용 89% 감소

내용 : 캐릭터 카드 목록을 얻는다.

문제 : 랜덤 액세스 심각, 불필요한 업데이트 구문, 정렬

개선 방향 : 랜덤 액세스 제거, 불필요한 구문 제거, 비클러스터형 인덱스 수정

결과

-    랜덤 액세스 제거

-    UPDATE 구문 제거

-    처리 비용 46% 감소  (정렬 제거 시 58% 감소)

내용 : 강화 재료 카드 목록을 얻는다.

문제 : 보유 수량만큼 INNER JOIN, 클러스터형 인덱스와 조인, 랜덤 액세스, 정렬

       총 네 번의 INNER JOIN이 발생

개선 방향 : 불필요한 JOIN, 랜덤 액세스, 정렬 제거,  인덱스 수정

결과

-    INNER JOIN 감소 ( 4 -> 1)

-    랜덤 액세스 제거

-    정렬 제거

-    처리 비용 88% 감소

튜닝전 UPS_GetCharacterCardList_Enchant 실행 계획

튜닝 후 UPS_GetCharacterCardList_Enchant 실행 계획

운영툴 대회채널 결과 페이지 튜닝전 실행 계획

운영툴 대회채널 결과 페이지 튜닝 후 실행 계획

파란 박스로 체크한 결과가 튜닝 후 쿼리 실행 시 결과를 얻는데 걸리는 시간(ms)

링크 : 

1. 1. 데이터 파일의 구조
2. 2. DBMS 메모리 구조
3. 3. 인덱스 기본
4. 4. 정렬을 제거하는 방법
5. 5. 프로시져 캐시 경합 확인하기
6. 6. Loop 쿼리 제거하기
7. 7. 튜닝 결과 샘플 보기

Page

 I/O가 이루어 지는 단위, 데이터 R/W 논리적 단위

하나의 레코드에서 하나의 칼럼만을 읽으려고 해도 레코드가 속한 페이지 전체를 읽는다.

SQL 성능 지표 중 액세스하는 Page 수는 매우 중요하다. 옵티마이저의 판단에 가장 큰 영향을 준다.

MSSQL Page 크기는 8KB

Extent

 파일그룹으로부터 공간을 할당하는 단위

테이블이나 인덱스에 데이터를 입력하다가 공간이 부족해지면 해당 오브젝트가 속한 파일그룹으로부터 추가적인 공간을 할당 받는데 이때, 정해진 익스텐트 크기의 연속된 블록을 할당 받는다.

예를 들어 8KB Page인 상태에서 64KB 단위로 익스텐트를 할당하도록 정의했다면, 공간이 부족할 때마다 파일그룹으로부터 8개의 연속된 페이지를 Heap/Index 구조 오브젝트에 할당해 준다. 익스텐트 내의 페이지는 인접하지만 익스텐트끼리는 인접하지 않는다.

MSSQL은 8페이지 익스텐트만 사용하고 페이지는 8KB 고정이기 때문에 익스텐트의 크기는 항상 64KB 이다.

Heap/Index 구조 오브젝트 (세그먼트)

 테이블, 인덱스처럼 저장 공간을 필요로 하는 데이터베이스 오브젝트

저장 공간을 사용한다는 것은 한 개 이상의 익스텐트를 사용한다는 뜻이다.

File Group

 세그먼트를 담는 컨테이너, 여러 데이터 파일로 구성된다.

임시 데이터 파일

 대량의 정렬이나 해시 작업 수행 중에 메모리가 부족해지면 중간 결과 집합을 저장하는 용도로 사용한다. MSSQL은 하나의 tempdb 만 사용한다. 전역 리소스이다.

트랜잭션 로그

 버퍼 캐시에 가해지는 모든 변경 사항을 기록하는 파일로 데이터베이스마다 하나씩 생기며 확장자는 .ldf 이다.

Fast Commit 매커니즘^[각주:1]을 사용한다. 

내부적으로 '가상 로그 파일' 이라 불리는 더 작은 단위의 세그먼트로 나뉘며, 세그먼트가 너무 많아지지 않도록 (조각화) 옵션을 지정하는게 좋다.

예를 들어, 애초에 넉넉한 크기로 만들어 자동 증가가 발생하지 않도록 하거나, 어쩔 수 없이 자동 증가한다면 증가하는 단위를 크게 지정하는 것이 좋다.