검색결과 리스트
분류 전체보기에 해당되는 글 88건
- 2016.08.12 MySql에서 내림차순 정렬 인덱스 사용하기
- 2015.08.04 스펠나인
- 2015.05.20 컨테이너 순환문에서는 전위 증가 연산자를 사용하자
- 2015.05.20 Loop 쿼리를 제거하고 One 쿼리를 작성해야 하는 이유
- 2015.05.04 프로시저 캐시 분석 쿼리
- 2015.04.28 데이터 정렬 제거하기
- 2015.04.15 인덱스 사용 현황 분석
- 2015.04.14 트랜잭션 로그 확인
- 2015.04.14 SQL 랜덤 함수
- 2015.04.13 테이블 크기를 얻는 쿼리 작성
글
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에서 정렬이 발생하면 왜 안되는지는 찾아보면 아래 링크
링크 : 정렬이 왜 나빠?
설정
트랙백
댓글
글
스펠나인
2015년 6월 1일 ~ 2016년 11월 11일, 스펠나인 개발팀
링크: https://play.google.com/store/apps/details?id=com.ftt.spell9.kr.gl&hl=ko
사용 언어 및 라이브러리 : C#, asp.net, MySql, Redis
개발툴 : VS2013, SQLyog, MySQL Workbench 6.3, Perforce
간단한 업무 내용
- 컨텐츠에 대한 서버, DB 전반에 걸친 설계 및 구현
- 정산 시스템 제작
- 개발 편의 툴 작업
개발 컨텐츠 목록
- 보상 시스템
- 출석부 시스템
- 우편 시스템
- 실시간 이벤트 시스템
- 실시간 상점 시스템
- 친구 시스템
- 도전과제 시스템
- 아레나 시스템
- 길드 대전 시스템
- 아레나, 길드 대전 정산 시스템
- 서버 오류 수집 시스템
설정
트랙백
댓글
글
컨테이너 순환문에서는 전위 증가 연산자를 사용하자
링크 : 어셈블리 기본
int i = 0;
int iPrefix = ++i; // 전위연산
int iPostfix = i++; // 후위연산
해당 코드의 결과는 프로그래머라면 누구나 알 수 있을 것이다.
그런데 두 연산자는 속도의 차이가 있을까?
필자는 전위 연산자가 더 빠르다고 배웠기 때문에 습관적으로 전위 연산자를 사용하고 있었는데 정말로 그런지 어셈코드를 확인해 보기로 했다.
코드를 보면 알 수 있지만 대입할 때 연산자 구현 차이1만 있을 뿐 속도차이는 없다는 것을 알 수 있다.
그렇다면 객체에서는 어떨까?
객체의 경우 전위 연산자 처리가 훨씬 단순하다는 사실을 알 수 있다. 단지 스택 메모리를 추출 후 서브루틴으로 분기한다.
그에비해 후위 연산자는 상대적으로 처리가 복잡하고 코드를 잘 보면 컨테이너 소멸자까지 호출되고 있다.
Why??
std::list 코드를 열어보면
전위 연산자는 자신을 증가 시킨 후 참조형으로 리턴하고 후위 연산자는 임시 개체에 상태를 복사한 후 자신을 증가 시키고
복사된 임시 객체를 리턴 시키고 있다. 어셈코드가 설명이 되는 부분이다.
결론 int, float, char 등 내부 타입의 경우 성능상 차이가 없지만 객체는 전위 연산자를 사용하는게 더 빠르다.
- 증가 후 대입 대입 후 증가 [본문으로]
설정
트랙백
댓글
글
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
RECENT COMMENT