DB 꿀팁

1. 컬럼 타입 가이드

• Oracle의 Char 타입의 경우 빈공간이 NULL로 채워지나, Postgresql의 경우 빈공간이 빈값으로 채워짐

2. 테이블 종류 및 사용법

1. 일반 테이블
• 통상적으로 사용하는 일반 테이블

2. Temp 테이블(Temporary 테이블)



• 임시 테이블로 평상시에는 빈 테이블로 존재
• 세션이 들어와서 해당 테이블에 DML을 통해 데이터를 넣으면 테이블 내 데이터를 들고 있다가 세션 종료 시 자동 Truncate
• 동일한 Temp 테이블을 바라보더라도 세션이 다를 경우 독립적으로 존재 및 독립적으로 DML 수행
• in 조건에 다량의 변수가 들어오거나. ETL 과정에서 중간에 집계 및 거처가는 테이블 들로 주로 사용
• 인덱스도 생성 가능하나, 가급적 필요한 데이터만 인입 시키고 인덱스는 미생성 (PK제외) 권장

3. 파티션 테이블



• 하나의 큰 테이블을 쪼갠 여러 자식 테이블로 분리하는 테이블 구조
• 큰 테이블을 특정 기준으로 잘라내어 작은 테이블들로 자르게 되면 데이터 조회시 파티션 키 라는
테이블을 자른 기준 컬럼을 명시해줄 경우 전체 큰 테이블이 아닌 해당하는 파티션 테이블만 조회
• 데이터 사이즈가 크고 자를수 있는 기준이 더 있을 경우 파티션의 파티션, 즉 이중 파티션 구조로도 생성 가능
• 장점으로는 다른 테이블 스페이스를 배정하여 IDC 환경에서 동일 디스크를 자주 접속 하는 것을 방지
• 큰 테이블에 대하여 물리적으론 1개 테이블이나 논리적으로 쪼개어져 있기에 데이터 조회 시 필요한 작은 테이블만 access 하여 성능 개선(12개의 파티션으로 나누어져 있을 경우 실제 데이터의 1/12만 조회)
• 하나의 파티션 테이블을 Truncate 하여도 다른 파티션 테이블에 영향이 없기에 데이터 파기에 용이
• 대량의 데이터 혹은 주기적으로 파기 해야하는 테이블에 대하여 주로 사용하며, 날짜나 값의 범위를 파티션 키로 쓰는
Range 파티션을 주로 사용한다.
• 파티션 테이블을 만들 때 인덱스는 local 타입으로 만들어야 파티션 인덱스로 생성되며, 권한의 경우 각 파티션 테이블 별로 권한을 부여하여야 하는 DBMS 존재
• 무분별한 파티션 테이블은 오히려 성능을 악화시키기에 파티션 테이블 내 데이터가 약 50만건 이상일 경우 생성을 권장 드리며, 파티션키를 통해 나눠지는 기준도 많지 않은 좋은 key 값 선정이 핵심

• * 좋은 파티션 키란?

• > null 값이 유입되지 않고, 데이터가 바뀌지 않는 불변성을 만족해야함

• > 해당 키를 기준으로 각 파티션 데이터가 고르게 분포 되어야 함 (데이터 쏠림 현상 발생 시 파티션 테이블 사용 무의미)

• > 가급적 모든 Sql문에선 해당 파티션 키를 조건에 사용하여야 한다.

3. 인덱스 생성 가이드 (개발 관점)

1. 인덱스의 종류



• Unique index : 인덱스의 값은 해당 테이블에서 유일한 값을 가지는 인덱스
• non unique index : 인덱스의 값이 해당 테이블에서 유일하지 않아도 되는 인덱스
• partition index : 파티션 테이블에서 생성 가능한 인덱스
• Local partition index : 각 파티션 별로 파티션 키를 기준으로 하여 각각의 인덱스가 생성되는 지역 인덱스
• Global partition index : 파티션 테이블의 부모 테이블에 생성하는 인덱스로, 파티션 키가 불필요하고 전체 테이블 스캔이 필요할 경우에 사용하는 인덱스, 전역 인덱스
• single index : 인덱스를 구성하는 컬럼이 1개만 명시되는 인덱스
• Multi index : 인덱스를 구성하는 컬럼이 여러개인 인덱스
• Function based index : 인덱스를 생성할 때 각 컬럼별로 함수 혹은 날짜 변환 등을 사용한 결과 값을 인덱스로 생성
• Partial index : 인덱스를 구성할때 공통적인 where 조건을 인덱스 생성 시 명시하여 필요한 부분만 가지고 있는 인덱스

2. 인덱스 생성 시 주의사항
• 구성하는 인덱스의 크기가 전체 데이터의 15% 이상이 넘지 않아야 한다.
• 인덱스 생성 시 순서가 매우 중요하며 앞에 순서에 있는 컬럼이 조건에 없을 경우 해당 인덱스를 옵티마이저는 잘 선택하지 않는다. (ex: 인덱스(a,b, c)에 대하여 조회조건이 where b ='1' and c ='10' 일 경우 인덱스 미사용)
• 인덱스는 가급적 1개 테이블에 10개 이하로 생성하는 것을 권장
• Null 값이 많은 컬럼은 인덱스로 잡지 않는 것을 권장
• 조회가 빈번하고 DML이 거의 이뤄지지 않을 경우 인덱스를 많이 생성해도 성능상 이슈가 적음
• DML이 빈번하거나 대량 데이터의 변경 들이 있을 경우 인덱스 생성을 최소화 하는 것을 권장
• > DML이 발생하면 인덱스에도 DML이 발생하기에 DML이 이뤄질 때 인덱스가 많을 경우 부하 발생
• 인덱스로 명시된 컬럼이 where 절에서 to_char, dated, decode 등 함수로 변환할 경우 인덱스를 타지 못하기에 이럴 경우 funtion - based index 생성 검토

3. 인덱스로 만드는 컬럼 선정 법



• 조건 절에서 많이 사용되는 컬럼을 선행으로 배치
• 컬럼의 분포도가 높아서 distinct 한 값이 다양할 수록 선행으로 배치
• = 조건을 사용할 수록 앞으로 배치하며, 그 다음은 between, 이후 >,<,<=.>= 순으로 선행으로 배치하기 적합하다
• 데이터 정렬 (order by) 에서 자주 사용하는 컬럼일 수록 선행으로 배치

4. JOIN 종류 및 장단점

1. Nested Loop Join



• 가장 많이 쓰이는 join으로 성능이 안정적인 것이 특징
• 두 테이블을 join 할때 서로의 인덱스를 통해 필요한 부분만큼 추출하여 join
• 선행 테이블의 인덱스를 읽어와서 해당 인덱스 데이터에 해당하는 후행 테이블의 인덱스 값과 매치 하기에 부하가 적다
• 쿼리 조건을 통해 join에 쓰이는 테이블들이 전부 인덱스를 탄다면 옵티마이저는 가급적 NL JOIN을 탐
• 가장 중요한건 선행 테이블(쿼리 앞 부분에 적힌 테이블)의 데이터가 작아야 성능이 좋다
• 선행 테이블의 데이터 양에 따라 join 되는 갯수가 달라지기에 선행 → 후행으로 갈 수록 데이터 양이 많도록 배치 하는 것이 중요
• 적은 데이터 및 인덱스를 잘 탈 경우 NL JOIN이 유리하며, NL JOIN의 경우 성능이 일정하다.
• 일반적인 사용자 조회, 검색 등 수초 내에 끝나는 작업들은 NL JOIN으로 구성하는 것이 유리하다.

2. Hash Join



• 선행 테이블에서 join에 쓸 키 값을 해시 값으로 변환하여 해시 테이블을 생성
• 후행 테이블의 join 키 값을 해시값으로 변환하여 위에 생성된 해시테이블과 비교하여 join 한다.
• 인덱스를 타지 않고 Full scan을 하거나 인덱스를 탓음에도 데이터 양이 많을 때 유리하다
• CPU와 메모리 영역을 다수 사용하기에 서버 상태 및 성능에 따라 쿼리의 성능이 달라진다.
• join을 할 때 = 연산자를 사용할 경우만 사용 가능하다
• 대용량 데이터를 join 할때 hash join을 사용하는 것이 유리할 경우가 있기에, 그럴 경우 인덱스를 타지 않고 full table scan을 하도록 유도하는 방식으로 hash join을 사용한다.
• 대용량 성 배치, 집계 등 수행 시간이 오래 걸리는 쿼리에서 hash를 사용하는 것이 유리하나, hash join이 많은 테이블은 서버의 충분한 CPU, 메모리 확보가 선행되어야 한다.

3. Merge Join



• 선행 테이블, 후행 테이블에 join key에 해당하는 값을 검색하여 불러온 뒤 해당 데이터를 정렬한다.
• 이후 정렬된 key 값을 비교하여 join 하는 방식으로 이루어진다
• hash join과 마찬가지로 대용량 데이터를 join 할때 유리하며 cpu, 메모리를 소모하는 hash join과 다르게 디스크 영역을 사용하기에 성능 적으로 떨어진다.
• 디스크 영역을 사용하며, DB에서 가장 부하가 심한 작업인 order by 가 이뤄지기에 3개의 join 중 가장 성능이 떨어진다.
• join 조건이 = 가 아닌 비교 연산자(>,<,<=,>=) 에서 사용할 수 있으며, 가급적 join 전에 정렬을 끝내고 범위를 최소한으로 줄이는 방식으로 성능을 개선하여야 한다.

5. Slow Query 방지를 위한 가이드

6. 플랜 확인법

• 전체적으로 Plan을 살핀 뒤 Buffer(Block)의 사용량을 파악하여 과도하게 많이 사용된 부분 탐색

• Buffer 의 사용량이 비슷하다면 각 단계 별로 수행 시간을 확인하여 수행시간이 오래 걸린 부분에 대해 확인

• 보통 Plan이 느린 많은 케이스는 아래와 같다.
1. 인덱스 미존재로 인한 Full 테이블 scan
2. 대량 데이터에 대하여 인덱스를 통한 NL JOIN 수행
3. 동일한 테이블이 반복하여 호출
4. 다수의 loop 발생
select 문 안에 함수가 있으면 모든 데이터에 대해서 함수를 다 돌려야해서 오래 걸릴 수 있음
5. 실제 결과 값에 비해 많은 Row가 반환되는 테이블
left join 이런것보다 inner join 으로 바꾸는게 낫다

• type 이 중요함. 위에서부터 빠른 순서. pk나 unique key는 일반 인덱스와 달리 정렬되어 있음

• key가 null이면 풀스캔 탄다는것

7. DB 모니터링 하는 법

• 평상시에는 CPU 50% 이하, peak 시에는 80% 이하여야 함. 그 이상이면 증설해야 함

• 메모리 부족하면 디스크로 가기 때문에(DISK IO) 성능 차이 많이 남. Freeable memory 확보 필요
• Oracle ms 는 메모리 설정을 할 수 있음. Postgresql, Mysql 은 인스턴스 타입 변경이 필요함(메모리 증설)

• DB I/O 가 생긴다는게 메모리가 부족한것

• DB 내 지연이 발생하면 운영 담당자는 긴급 모니터링에 들어간다.

• 긴급 모니터링에서 위의 순으로 단계 별로 체크해가며 모니터링 및 조치를 취한다.
(긴급 조치 법은 8. DB 지연 발생 시 긴급 조치 참고)

• AWS 에서는 성능 개선 도우미에서 오래 걸린 SQL 다 확인할 수 있음

8. DB에 자주 지연을 발생시키는 요소들

나. 지연을 일으키는 요소

9. DB 지연 발생 시 긴급 조치

1. DB 의 LOCK 확인 및 세션 킬 방법
가. Oracle

/* 세션 KILL 권한 확인(ALTER SYSTEM 권한이 존재하여야 함)*/
SELECT * FROM DBA_SYS_PRIVS
WHERE GRANTEE = '계정명‘ ;

/* TM, TX 락 여부 확인(테이블 락, 로우 락) */
SELECT * FROM V$LOCK
WHERE TYPE IN( 'TM‘, TX);

/* LOCK 걸린 개체의 정보 확인 */
SELECT sid, serial#, username
FROM V$SESSION
WHER SID = ‘락걸린SID’;

/* 세션 KILL */
-- ALTER SYSTEM KILL SESSION 'SID , serial# ‘ ;

나. Mssql

/* mode 가 x 대상의 paid 추출*/ EXEC sp_lock
/* Lock 걸린 쿼리 내용 확인*/ DBCC inputbuffer(spid)
/* lock 해제*/ kill spid  -- 이것도 마찬가지로 권한 중요

다. Postgresql

SELECT  t.relname,
        l.locktype,
        page,
        virtualtransaction,
        pid,
        mode,
        granted
FROM pg_locks l,
	 pg_stat_all_tables t
WHERE l.relation = t.relid
ORDER BY relation ASC;

SELECT pg_terminate_backend(pid);

라. Mysql

SELECT * FROM information_schema.`PROCESSLIST`
WHERE Command <> 'Sleep'
AND USER NOT IN ('system user', 'event_scheduler')
ORDER BY TIME DESC
;

kill sid;

2. 오래 걸리는 쿼리 확인
가. Oracle

SELECT * FROM(
SELECT ROUND(ELAPSED_TIME/EXECUTIONS/1000000,3) AS ELAPSED_TIME
          , LAST_ACTIVE_TIME
          , HASH_VALUE
          , SQL_TEXT
  FROM V$SQL
WHERE parsing_schema_name = '스키마명' -- 스키마명 아니면 느림
  AND LAST_ACTIVE_TIME >= TO_DATE('20180307', 'YYYYMMDD')
)
 WHERE ELAPSED_TIME > 7 --초 단위 입력
 order by ELAPSED_TIME desc;

나. Mssql

SELECT TOP 20 qt.text AS SP,
  qs.execution_count,
  qs.execution_count/DATEDIFF(Second, qs.creation_time,
  GetDate()) ,
  qs.total_worker_time/qs.execution_count,
  qs.total_worker_time ,
  qs.total_elapsed_time/qs.execution_count,
  qs.max_logical_reads ,
  qs.max_logical_writes ,
  qs.total_physical_reads ,
  DATEDIFF(Minute, qs.creation_time,
  GetDate())
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = db_id()
 ORDER BY qs.total_worker_time DESC

다. postgresql

SELECT *
from pg_stat_activity
where state = ‘active’
    and application_name = ‘’   -- api 지정
    and current_timestamp - query_start > ‘7 sec’ -- 시간 지정

라. Mysql

SELECT start_time, user_host, query_time, lock_time, rows_sent, rows_examined,
db, CONVERT(sql_text USING utf8 ) sql_text
FROM mysql.slow_log;