검색결과 리스트
SQL에 해당되는 글 23건
- 2016.09.21 MySQL GUID 저장 팁
- 2016.08.12 MySql에서 내림차순 정렬 인덱스 사용하기
- 2015.05.20 Loop 쿼리를 제거하고 One 쿼리를 작성해야 하는 이유
- 2015.05.04 프로시저 캐시 분석 쿼리
- 2015.04.28 데이터 정렬 제거하기
- 2015.04.15 인덱스 사용 현황 분석
- 2015.04.14 트랜잭션 로그 확인
- 2015.04.14 SQL 랜덤 함수
- 2015.04.13 테이블 크기를 얻는 쿼리 작성
- 2015.03.31 DBMS 메모리 구조
글
MySQL GUID 저장 팁
대시 기호 제거 후 UNHEX() 함수를 사용하여 16바이트 숫자로 변환 후 BINARY(16) 칼럼에 저장, 해당 값은 HEX() 함수를 이용하면 16진수로 볼 수 있다.
일반적으로 CHAR(32)에 저장했을 때보다 저장 용량이 절반으로 줄어들고 비교 속도가 빨라진다.
설정
트랙백
댓글
글
MySql에서 내림차순 정렬 인덱스 사용하기
현재 프로젝트에서 사용 중인 MySql에서 내림차순 인덱스를 지원하지 않는다.
내림차순 정렬 후 페이지 단위로 셀렉트해야하는 경우 오름차순으로 정렬된 인덱스 때문에 내부적으로 불필요한 정렬이 계속 발생한다.
그러면 어떻게하면 되느냐.. 그냥 MSSql을 사용하자..
그럴 수 없다면 꼼수를 사용하면 되는데..
DateTime 형식의 내림차순 인덱스를 만든다고 한다면 위의 그림을 참고하자.
Min, Max는 DateTime 형식의 Min, Max 값을 생각하고 숫자 1, 2번은 데이터 발생 시점으로 생각한다면,
일반적으로 RegDateTime가 오름차순으로 구성되어 있을 것이고 (내림차순 인덱스가 지원되지 않는다) 필요한 데이터를 내림차순 정렬 후
페이지 단위로 셀렉트한다면 DBMS에서는 정렬 후 해당 페이지 단위만큼 셀렉트할 것이다.
당연히 복합 인덱스 컬럼이 오름차순으로 셋팅되어 있다면 이후에 발생한 2번이 두번째에 구성되는게 맞다.
그러면 여기서 바라보는 시각을 비틀어 발생 시각이 아니라 Max까지 남은 시간을 생각해보면, 2번 데이터가 발생한 시점에 Max까지
남은 시간 3번이 4번보다 더 적은 것을 알 수 있을 것이다.
답은 나왔다. 인덱스 컬럼을 3, 4번으로 사용하면 오름차순 인덱스만 지원하는 MySql의 특성을 만족 시키면서 필요한 데이터를 내림차순으로
얻을 수 있다.
정렬 키를 만드는 C# 코드
TimeSpan spanTime = DateTime.MaxValue - DateTime.UtcNow;
DateTime orderByKey = DateTime.MinValue + spanTime;
정렬이 발생하지 않는 내림차순 페이지 단위 셀렉트 MySql 쿼리
SELECT
....
FROM table
WHERE Guild_SN = pGuildSN
ORDER BY OrderByKey
LIMIT pStartIndex, pPageSize;
DBMS에서 정렬이 발생하면 왜 안되는지는 찾아보면 아래 링크
링크 : 정렬이 왜 나빠?
설정
트랙백
댓글
글
Loop 쿼리를 제거하고 One 쿼리를 작성해야 하는 이유
링크 :
- 1. 데이터 파일의 구조
- 2. DBMS 메모리 구조
- 3. 인덱스 기본
- 4. 정렬을 제거하는 방법
- 5. 프로시져 캐시 경합 확인하기
- 6. Loop 쿼리 제거하기
- 7. 튜닝 결과 샘플 보기
데이터베이스 작업 요청에 따른 Call 의 종류를 살펴보면
- Parse Call : SQL 파싱을 요청하는 Call
- Execute Call : SQL 실행을 요청하는 Call
- Fetch Call : SELECT 문의 결과 데이터 전송을 요청하는 Call
일반적으로 서버에서 DB에 쿼리를 요청하는 코드는
WorkThread::Func()
{
……
…..
Lib.api_call(_T(“실행 쿼리”));
Blocking...
}
응답이 돌아올 때까지 해당 쓰레드는 Blocking 상태로 다음 작업을 할 수 없다.
만약 바인딩 변수로 대상 아이템의 시퀀스를 받아 해당 아이템 정보를 얻는 쿼리가 있다고 가정하고 이 쿼리를 이용하여 아이템 30개의 정보를 얻는 서버 로직을 생각해보자.
일반적으로 아래와 같은 형식으로 작업 할 것이다.
WorkThread::SelectFunc()
{
...
// Loop Call
for (int i = 0; i < 30; ++i)
{
Lib.api_call(_T("select 쿼리"));
Blocking...
}
}
이런 형태의 코드는 DBMS 내부적으로 Parse Call, Execute Call 이 각각 30회씩 발생하고, 추가로 30회의 응답 대기가 발생한다.
Loop 쿼리는 대부분의 시간을 네트워크 구간에서 소비하거나 Call 이 발생할 때마다 OS로부터 CPU와 메모리 리소스를 할당 받으려고 대기하면서 소비한다.
만약 One 쿼리를 만들어 실행 시킨다면
WorkThread::SelectFunc()
{
...
// One Call
Lib.api_call(_T(“select 쿼리”));
Blocking...
}
Parser Call, Execute Call 이 각각 1회씩만 발생된다. 추가로 가장 큰 이득은 Blocking 상태 (네트워크 구간 + 리소스 할당 대기 + 쿼리 처리)가 1회로 끝나는 것이다.
One SQL은 대상 그룹을 문자열로 넘겨 받아 파싱 후 테이블 변수로 만들어 조인한다.
만약 테이블 변수와 조인을 하게 되는 대상 테이블이 동일한 페이지에 정렬되어 Page 접근이 적어진다면1 조인 효율은 극대화 될 것이다.
One Call 쿼리에 30개의 대상 아이템 시퀀스를 문자열로 넘긴다면 대략 "1, 2, ..., 30" 이런 형식이 될 테고
해당 문자열을 파싱해서 테이블 변수로 만드는 쿼리는
@strArray 는 바인딩 변수
SET NOCOUNT ON;
-- 테이블 변수 가능한 작게 유지할 필요가 있다.
DECLARE @tTemp TABLE (
Seq BIGINT
);
DECLARE @Seq BIGINT = 0;
WHILE CHARINDEX(',', @strArray) <>0
BEGIN
SET @Seq = CAST(SUBSTRING(@strArray, 0, CHARINDEX(',', @strArray)) AS BIGINT)
SET @strArray = SUBSTRING(@strArray, CHARINDEX(',', @strArray) + 1, LEN(@strArray) - CHARINDEX(',', @strArray))
INSERT INTO @tTemp
SELECT @Seq
END
IF 0 < LEN(@strArray)
BEGIN
SET @Seq = 0
SET @Seq = CAST(@strArray AS BIGINT)
INSERT INTO @tTemp
SELECT @Seq
END
SELECT
...
FROM @tTemp AS O
INNER JOIN dbo.targetTable AS I
ON O.Seq = I.Seq
WHERE 튜닝 규칙을 만족하는 범위 한정 조건절 작성
대략 이런 형식이 될 것이다.
만약 테이블 변수를 사용하지 않고 파싱 중간중간 결과를 Select 한다면 TDS 패킷 수량이 많아진다.
즉 DB에서 서버에 전달하는 패킷이 더 많아지기 때문에 불필요한 비용이 발생할 수도 있다.
조인 대상 테이블 구조에 따라 랜덤 액세스 비용이 발생할 수도 있기 때문에 꼼꼼히 살펴볼 필요는 있지만 Blocking 상태에서 소모되는 시간보다는 훨씬 적을 것이다.
상대적인 IO 비용
IO |
비용 |
L1 cache |
3 cycles |
L2 cache |
14 cycles |
RAM |
250 cycles |
DISK |
41000000 cycles |
Network |
240000000 cycles |
- DB는 하나의 레코드에 접근하더라고 레코드가 포함된 Page를 메모리에 로드한 후 로드된 메모리 블럭을 통해 레코드에 접근한다. [본문으로]
설정
트랙백
댓글
글
프로시저 캐시 분석 쿼리
링크 :
- 1. 데이터 파일의 구조
- 2. DBMS 메모리 구조
- 3. 인덱스 기본
- 4. 정렬을 제거하는 방법
- 5. 프로시져 캐시 경합 확인하기
- 6. Loop 쿼리 제거하기
- 7. 튜닝 결과 샘플 보기
-- 프로시저 캐시 경합을 모니터링 할 수 있는 정보를 출력한다.
SELECT text, objtype, refcounts, usecounts, size_in_bytes, disk_ios_count, context_switches_count,
pages_allocated_count, original_cost, current_cost
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
JOIN sys.dm_os_memory_cache_entries e
ON p.memory_object_address = e.memory_object_address
WHERE cacheobjtype = 'Compiled Plan' AND type in ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP')
컬럼을 노출하지 않기 위해 블라인드 처리한 부분들이 있지만 해당 내용은 동일한 동작을 하는 쿼리가 리터럴 SQL로 작성되어 프로시저 캐시를 서로 경합하면서 점유하고 있는 상태의 극히 일부분이다. 대부분 동적 쿼리를 만들면서 바인딩 변수를 매핑하지 않아 생기는 버그들이다. 아니면 관리툴에서 날짜를 스트링으로 처리하던가하는 경우도 있다.
text와 objtype를 참고하여 캐시 경합을 발생 시키는 쿼리들을 찾아서 문제를 제거하자.
-- 실행계획 캐시를 비운다.
dbcc freesystemcache('SQL Plans');
go
쿼리 수정 후 문제가 해결되었는지 확인하기 위해 프로시저 캐시의 실행계획을 삭제한다. 일시적으로 하드파싱이 일어나는 성능 저하가 발생하지만 어차피 우리가 원하는 일이다.
다시 프로시저 캐시를 모니터링 하면서 실행계획이 재사용 가능한지 adhoc 쿼리가 또 발생하는지 등 원하는 결과가 나올 때까지 개선하도록 한다.
관련 링크 :
- sys.dm_os_memory_cache_entries
설정
트랙백
댓글
글
데이터 정렬 제거하기
링크 :
- 1. 데이터 파일의 구조
- 2. DBMS 메모리 구조
- 3. 인덱스 기본
- 4. 정렬을 제거하는 방법
- 5. 프로시져 캐시 경합 확인하기
- 6. Loop 쿼리 제거하기
- 7. 튜닝 결과 샘플 보기
정렬이 비싼 이유
정렬은 우선 데이터를 추출한 후에 특정 칼럼으로 다시 추출한다. 추출한 데이터에 다시 액세스하는 행위 자체가 이미 비용 낭비이다.
작업은 일반적으로 작업 쓰레드 메모리 영역을 사용하지만 대량의 데이터를 처리할 때는 디스크에서 정렬이 이루어진다. (일반적으로 물리적인 디스크 I/O는 비싸다)
정렬이 발생할 때
- ORDER BY
- DISTINCT
- Sort Merge Join
- Hash Join
- 집합 연산자 (UNION ALL 제외)
- 분석 함수
- 그룹 함수 (DBMS 버전에 따라 발생하지 않을 수 있음)
- IN 절의 사용 (수행 방식에 따라 다름)
집합 연산자가 정렬을 수행하는 이유
- 교집합의 모양을 확인하기 위해서는 반드시 정렬이 필요하기 때문이며 UNION ALL이 정렬을 하지 않은 이유는 중복을 허용하는 합집합이기 때문
GROUP BY
- 동일한 데이터를 하나의 값으로 추출하기 때문에 정렬이 필요하다.
SELECT
ROW_NUMBER() OVER( PARTITION BY [GroupCode] ORDER BY [StartDate] DESC )as row
FROM ...
WHERE ...
분석함수
- 분석함수는 해당 함수 옆에 OVER 절을 설정한다. OVER 절은 데이터를 논리적으로 분할하는 PARTITION 절과 해당 논리적인 분할 영역을 정렬하는 ORDER BY 절로 구분한다.
정렬을 제거 할 수 있는 인덱스 구성 방법
점조건 + 점조건 + 정렬의 첫번째 조건 + ... + 정렬의 N번째 조건 + 선분 조건...
설정
트랙백
댓글
글
인덱스 사용 현황 분석
USE 데이터베이스명
DECLARE @dbname NVARCHAR(128) = '데이터베이스명'
SELECT
object_name(T.object_id) AS Table_Name
, I.name AS Index_Name
, I.type_desc
, T.user_seeks
, T.user_scans
, T.user_lookups
, T.user_updates
, T.last_user_seek
, T.last_user_scan
, T.last_user_lookup
, T.last_user_update
FROM (
SELECT *
FROM sys.dm_db_index_usage_stats
WHERE database_id = db_id(@dbname)
) AS T
INNER JOIN sys.indexes AS I
ON T.object_id = I.object_id AND T.index_id = I.index_id
ORDER BY T.user_scans DESC
GO
해당 쿼리는 데이터베이스에서 스캔이 많이 발생한 순서대로 결과를 보는 쿼리이다. 분석에 필요한 정보는 SELECT 항목을 수정하기 바란다.
동적관리뷰의 상세 사항은 MSDN을 참고하기 바란다.
위의 쿼리를 이용하면 스캔일 발생하고 있는 테이블과 인덱스뿐만이 아니라 사용하지 않는 인덱스까지 한눈에 알아볼 수 있다.
설정
트랙백
댓글
글
트랜잭션 로그 확인
select * from sys.database_files
where type_desc = 'LOG'
order by file_id
DBCC LOGINFO
GO
EXEC SP_HELPFILE
GO
복구 모드 변경 쿼리
ALTER DATABASE MOGlobalEventDB SET RECOVERY FULL;
설정
트랙백
댓글
글
SQL 랜덤 함수
DECLARE @min INT = 1
DECLARE @max INT = 100
SELECT ROUND( ( (@max + 1) - @min ) * RAND() + @min , 0, 1)
설정
트랙백
댓글
글
테이블 크기를 얻는 쿼리 작성
DB를 정리할 때 간단한 쿼리를 이용하여 테이블 크기와 라인수를 알아볼 수 있는 쿼리가 필요한 경우가 있다.
호환성 뷰인 sys.sysindexes, sys.sysobjects 를 이용하면 간단한데
호환성 뷰는 지금까지 릴리즈에 사용되었던 여러 시스템 테이블을 뷰 집합으로 구현된 것이다. 메타데이터를 표시하는 시스템 뷰 컬렉션 중 하나이다.
각각의 테이블은 여러 칼럼을 포함하고 있지만 기능 구현에 필요한 간단한 칼럼만 알아보면 된다.
sys.sysindexes
- 현재 데이터 베이스 내의 각 인덱스 및 테이블마다 한 행을 포함한다.
칼럼 |
형식 |
설명 |
id |
int |
인덱스가 속한 테이블의 ID |
indid |
smallint |
인덱스의 ID 0 = 힙 1 = 클러스터형 인덱스 >1 = 비클러스터형 인덱스 |
reserved |
int |
indid = 0 또는 indid = 1인 경우 reserved는 모든 인덱스 및 테이블 데이터에 할당된 페이지 수 행 오버플로가 발생할 경우 정확한 결과가 반환되지 않는다 |
sys.sysobjects
- 데이터 베이스에서 만들어진 각 개체 (제약 조건, 기본값, 로그, 규칙, 저장 프로시저) 당 한 개의 행을 포함한다.
칼럼 |
형식 |
설명 |
name |
sysname |
개체 이름 |
id |
int |
개체 ID |
xtype |
char(2) |
개체 유형 U = 사용자 테이블 기타 등등.. 아주 많다. |
데이터 베이스가 포함한 테이블 크기 알아보기
SELECT
CONVERT(VARCHAR(30), MIN(o.name)) AS t_name
, LTRIM(STR(SUM(reserved) * 8192.0 / 1024.0, 15, 0) + ' KB') AS t_size
FROM sysindexes i
INNER JOIN sysobjects o
ON o.id = i.id
WHERE i.indid IN (0, 1)
AND o.xtype = 'U'
GROUP BY i.id
ORDER BY SUM(reserved) * 8192.0 / 1024.0 DESC
코드에서 reserved 에 8192 를 곱한 이유는 mssql의 페이지 크기가 8KB 이기 때문이다.
데이터 베이스가 포함한 테이블 라인 수 알아보기
SELECT
o.name
, i.rows
FROM sysindexes i
INNER JOIN sysobjects o
ON i.id = o.id
WHERE i.indid < 2
AND o.xtype = 'U'
ORDER BY i.rows DESC
설정
트랙백
댓글
글
DBMS 메모리 구조
링크 :
- 1. 데이터 파일의 구조
- 2. DBMS 메모리 구조
- 3. 인덱스 기본
- 4. 정렬을 제거하는 방법
- 5. 프로시져 캐시 경합 확인하기
- 6. Loop 쿼리 제거하기
- 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로 대응한다.
소프트 파싱을 잘 활용하기 위해서는 실행 계획을 공유하지 못하는 경우를 이해할 필요가 있다.
- 공백 문자 또는 줄바꿈
SELECT * FROM USERDB;
SELECT *
FROM USERDB;
- 대소문자 구분
SELECT * FROM USERDB;
SELECT * FROM userdb;
- 주석
SELECT * FROM USERDB;
SELECT * /* 모든 칼럼*/ FROM USERDB;
- 스키마 명시
SELECT * FROM USERDB;
SELECT * FROM dbo.USERDB;
- 조건절 비교값
SELECT * FROM dbo.USERDB WITH(NOLOCK) WHERE ID = '철수';
SELECT * FROM dbo.USERDB WITH(NOLOCK) WHERE ID = '영희';
위의 예시들 중 가장 큰 문제가 되는 경우는 마지막 경우이다. 만약 로그인 쿼리나 아이템 페이지 목록을 얻는 쿼리 등을 작성한다고 생각해보면 프로시저 캐시는 다른 이름의 동일한 기능을 하는 실행계획으로 가득찰 것이다. 위와 같은 쿼리를 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
RECENT COMMENT