전문가칼럼

DBMS, DB 구축 절차, 빅데이터 기술 칼럼, 사례연구 및 세미나 자료를 소개합니다.

이병국의 개발자를 위한 DB 이야기: 튜닝(35회) : 개발자를 위한 튜닝 실전(5편)

전문가칼럼
DBMS별 분류
Etc
작성자
dataonair
작성일
2017-01-26 00:00
조회
6372




◎ 연재기사 ◎


물탱크 구조로 알아본 오라클의 블록 옵션 ‘PCTFREE와 PCTUSED’


이산가족 찾기 생방송을 통해 배우는 DB 원리


개발자에게 맞는 DB 공부방법 찾기: 물리적 분류와 논리적 분류 그리고 인덱스


데이터베이스 인덱스의 오해와 진실


쉬운 것이 올바른 것이다. ‘인덱스 끝장리뷰’ (상)


쉬운 것이 올바른 것이다. ‘인덱스 끝장리뷰’ (하)


누구도 알려주지 않았던 ‘오라클 인덱스 생성도’의 비밀


누구도 알려주지 않았던 ‘오라클 쿼리 작성의 비법’


퀴리 최적화 및 튜닝을 위한 오라클 공정쿼리 작성법


만능 쿼리와 한 방 쿼리


오라클 옵티마이저 ‘CBO와 RBO’ 이해하기


재미있는 DB 이야기 ‘60갑자와 쿼리’


그림으로 배우는 ‘오라클 조인의 방식’ 이야기


반드시 알아야 하는 오라클 힌트절 7가지


오라클 플랜을 보는 법


개발자들의 영원한 숙제 ‘NULL 이야기’


알면 유용한 오라클 기능 ‘GATHER_PLAN_STATISTICS’


알면 유용한 오라클 기능들


오라클 DICTIONARY를 활용한 DB툴 프로그램 ‘FreeSQL’


이제는 말할 수 있다: 주식 자동매매 프로그램(상)


이제는 말할 수 있다: 주식 자동매매 프로그램(하)


개발자들이 자주 접하는 오라클 에러 메세지


재미있는 DB 이야기 ‘사라진 날짜를 찾아라’


오라클 랜덤 함수와 사용자 정의 함수


그림으로 배우는 ‘공정쿼리와 인덱스 생성도’


이병국의 개발자를 위한 DB 이야기: 디폴트 세팅의 함정과 오라클 파라미터


재미있는 DB 이야기 ‘놀라운 마방진의 세계’


오라클 운반 최소 단위 BLOCK


이병국의 개발자를 위한 DB 이야기: 이세돌과 알파고의 세기의 대결


이병국의 개발자를 위한 DB 이야기(30회) : DB 엔지니어의 가볍게 읽는 세상 이야기


이병국의 개발자를 위한 DB 이야기: 튜닝(31회) : 개발자를 위한 DB 튜닝 실전(1편)


이병국의 개발자를 위한 DB 이야기: 튜닝(32회) : 개발자를 위한 튜닝 실전(2편)


이병국의 개발자를 위한 DB 이야기: 튜닝(33회) : 개발자를 위한 튜닝 실전(3편)


이병국의 개발자를 위한 DB 이야기: 튜닝(34회) : 개발자를 위한 DB 튜닝 실전(4편)


이병국의 개발자를 위한 DB 이야기: 튜닝(35회) : 개발자를 위한 튜닝 실전(5편)


이병국의 개발자를 위한 DB 이야기: 페이징 처리에 대한 이해 (36회)


보기 좋은 떡이 먹기도 좋다 - 좋은 쿼리 좋은 성능


테이블의 수직분할과 수평분할에 대한 이해


DB 성능 제고를 위한 채번의 이해와 방식별 장단점 비교


이병국의 개발자를 위한 DB 이야기: 마지막회 : ‘개발자를 위한 DB 이야기’ 연재를 마치며



이병국의 개발자를 위한 DB 이야기: 튜닝(35회)

개발자를 위한 튜닝 실전(5편)



성공과 실패의 경험을 나누자, 용기와 희망을 나누자

개발업무를 시작으로 IT계에 입문했던 필자가 10년 가까이 DB엔지니어로서 활동하면서 얻은 경험과 지식을 나누고자 한다. DB를 자주 접하는 SW 개발자뿐 아니라, DB 전문가를 꿈꾸는 대학생에서DB 분야에 입문한지 1~2년 된 기입문자가 쉽게 이해할 수 있도록 비유를 통해 쉽게 접근해볼 계획이다. 물론 전문가들이라도 다시 한번 개념을 정립하는 의미에서 필요한 내용이 될 수 있다.

전체적으로 DB의 기본 원리와 개념을 이해하고 테이블, 인덱스, 쿼리, 튜닝, 플랜 등 개발자들이 알아야 하는 DB 전분야에 대해 쉽게 이해하도록 설명하겠다. DB 기술서적이나 번역서보다는 조금 더 부드럽게 접근할 계획이다. 그렇다고 흔히 서점에서 만날 수 있는 개발자 위주의 SQL 소개서도 아니다. 이 연재는 시리즈로 나갈 것이다. 연재를 끝까지 읽는 독자라면 준전문가 수준의 DB 원리를 아는 것을 목표로 한다.



튜닝실전 1편에서는 대용량 데이터를 처리하는 배치 쿼리에 있어서 인덱스를 사용하지 말아야 하는 경우에 대해서 중요하게 설명하였고, 튜닝실전 2편에서는 소규모 데이터를 처리하는 온라인 쿼리에 있어서 인덱스를 정확히 사용해야 하는 경우에 대하여 주로 설명하였다. 튜닝실전 3편에서는 공정쿼리 작성에 따른 튜닝의 선제적이고 예방적인 효과에 대해서 설명하였다. 지난 연재에 이어서 이번 연재에서는 필자가 필드에서 경험한 내용을 바탕으로 하여 구체적이고 실무적인 튜닝 방법에 대하여 설명할 예정이다.



INDEX를 사용하지 않는 경우: 컬럼 변형 시

인덱스가 생성된 컬럼이라도 다양한 경우에 의해서 인덱스가 사용되지 않는 경우가 많다. 아래 쿼리는 오라클 함수 사용으로 인하여 컬럼 변형이 일어난 경우다. 이때 인덱스는 사용되지 않는다.



SELECT * FROM 주문
WHERE SUBSTR(상품코드, 1, 2) = ‘01’



해결 방안은 다음과 같이 크게 세 가지가 있다.

1. 상품코드 컬럼에 SUBSTR 함수를 사용한다는 것은 하나의 컬럼에 여러 가지 의미가 내포 되어 있음을 의미한다. 설계 단계부터 상품코드 컬럼 분리를 검토해야 하는 경우이다. 하나의 컬럼에 여러 가지 의미를 담지 않아야 한다. 또한 여러 컬럼을 합쳐서 하나의 의미가 되지 않도록 해야 하는 것은 설계 시에 중요하게 고려되어야 할 내용 중 하나다. 하나의 컬럼에는 오로지 하나의 의미만을 담고 있는 것이 가장 좋다.

2. 쿼리문을 수정해 오라클 함수로 인한 컬럼의 변형을 사전에 방지할 수 있다.



SELECT * FROM 주문
WHERE 상품코드 LIKE ‘12%’



3. 함수 기반 인덱스를 생성한다. 하지만 관리상의 문제로 인하여 DBA가 싫어할 수도 있고, 여러 가지 이유로 인하여 DB 기술 지침을 통해 사용을 제한하는 회사도 있을 수 있다.

인덱스가 생성된 컬럼이라도 다양한 경우에 의해 인덱스가 사용되지 않는 또 다른 경우의 쿼리를 살펴보자. 아래 쿼리 역시 오라클 함수 사용으로 인하여 컬럼 변형이 일어난 경우다.



SELECT * FROM 주문
WHERE TO_CHAR(주문일자, ’YYYYMMDD’) = ‘20161214’



해결 방안은 역시 세 가지가 있다.

1. 주문일자 컬럼은 날짜와 관련된 컬럼이지만 반드시 날짜 데이터 타입을 사용할 필요는 없다. 날짜 타입을 쓰는 장점보다 오히려 단점이 많은 경우에는 문자 데이터 타입을 사용하는 것이 더 유리할 수 있다.

2. 쿼리문 수정을 통하여 오라클 함수로 인한 컬럼의 변형을 사전에 방지할 수 있는데 그 방법은 조건절의 컬럼에 함수를 사용하는 것이 아니라 조건절의 값에 함수를 사용해야 한다.

3. 함수 기반 인덱스를 생성하는 것이다.

아래 쿼리 역시 오라클 함수 사용으로 인하여 컬럼 변형이 일어난 경우다.



SELECT * FROM 주문
WHERE NVL(주문금액, 0) > 0



해결 방안은 테이블 생성시 주문금액 컬럼의 디폴트 값을 0으로 설정하여 NULL 값이 발생하지 않도록 예방하는 것이 필요하다. 혹은 함수 기반 인덱스를 생성하는 것이다.



INDEX 사용하지 않는 경우: 타입 변형 시

인덱스가 생성된 컬럼이라도 다양한 경우에 의하여 인덱스가 사용되지 않는 경우가 많다. 아래 쿼리는 조건절의 상수 값에 따라서 데이터 타입의 변형이 일어난 경우다. 이때 인덱스는 사용되지 않는다.



SELECT * FROM 주문
WHERE 상품코드 = 1234



해결 방안은 두 가지가 있다.

1. 상품코드 컬럼의 데이터 타입은 문자형인데 조건절의 상수값이 숫자이므로 타입의 변형이 일어난다. 따라서 인덱스가 존재함에도 불구하고 인덱스를 사용하지 못하는 경우다. 처음 테이블을 설계할 때부터 컬럼명은 상품번호로 하고 타입은 숫자형으로 했으면 더 좋았을 것이다. 컬럼값에 따라서 컬럼명을 결정하거나 데이터 타입 선언 시에 주의해야 함을 알 수 있다.

2. 쿼리문 수정을 통하여 상수값에 의한 타입의 변형을 사전에 방지할 수 있다.



SELECT * FROM 주문
WHERE 상품코드 = ‘1234’



INDEX 사용하지 않는 경우: NULL 사용 시

인덱스가 생성된 컬럼이라도 다양한 경우에 의해 인덱스가 사용되지 않는 경우가 많다. 아래 쿼리처럼 NULL을 사용한 경우다. 이때 인덱스는 사용되지 않는다.



SELECT * FROM 주문
WHERE 반품일자 IS NULL

SELECT * FROM 주문
WHERE 반품일자 IS NOT NULL



인덱스는 알 수 있는 값에 대해 인덱싱한 값이며, NULL은 모르는 값에 대한 표현이므로 이 둘은 서로 마주할 수 없는 관계다. 결코 NULL은 인덱스와 관련될 수 없다.



INDEX 사용하지 않는 경우: 부정형 사용 시

인덱스가 생성된 컬럼이라도 다양한 이유로 인덱스가 사용되지 않는 경우가 많다. 아래 쿼리처럼 부정형 연산자를 사용한 경우다. 이때 인덱스는 사용되지 않는다.



SELECT * FROM 테이블
WHERE 컬럼 <>
SELECT * FROM 테이블
WHERE 컬럼 !=



INDEX 사용하지 않는 경우: LIKE 사용 시

인덱스가 생성된 컬럼이라도 다양한 이유 때문에 인덱스가 사용되지 않는 경우가 많다. 아래 쿼리처럼 LIKE를 사용한 경우다. 이때 인덱스는 사용되지 않는다.



SELECT * FROM 테이블
WHERE 컬럼 LIKE ‘%ABC’

SELECT * FROM 테이블
WHERE 컬럼 LIKE ‘ABC%’



첫 번째 쿼리는 인덱스를 사용하지 않으며 두 번째 쿼리는 인덱스를 사용할 수도 있지만 조건 값이 얼마나 조회 대상 범위를 줄일 수 있는가가 관건이다.



INDEX 경합이 발생하는 경우

동시에 여러 개의 인덱스가 경합하는 경우 성능 문제를 야기할 수 있다. 아래 쿼리는 인덱스 경합이 발생하는 일반적인 사례이다.



SELECT * FROM 조직
WHERE 지역단조직번호 =
AND 지점조직번호 =



각각의 조건절에 인덱스가 존재한다면 인덱스 경합이 발생하여 INDEX MERGE가 유발될 수 있다. 이런 경우 반드시 나쁘다고 하기엔 무리가 있지만, 일반적으로 최적의 성능이 아닐 가능성이 훨씬 더 높다. 해결 방안은 크게 세 가지가 있다.

1. 인덱스 경합을 해결하는 가장 일반적인 방법은 인덱스 조정이다. 각각의 단일 인덱스를 결합 인덱스로 변경하는 것이다.

기존의 단일 인덱스 : 지역단조직번호, 지점조직번호
변경된 결합 인덱스 : 지역단조직번호 + 지점조직번호

2. 우위에 있는 인덱스에 힘을 실어주는 방법이다. 아래 쿼리와 같이 오라클 함수를 사용해서 컬럼 변형을 일으켜 열등한 인덱스를 사용하지 못하게 하는 방법이 있다.



SELECT * FROM 조직
WHERE RTRIM(지역단조직번호, ‘’) =
AND 지점조직번호 =



3. 힌트절을 사용하여 실제 사용할 인덱스를 직접 선택할 수 있다.



SELECT /*+ INDEX(조직, IDX_지점조직번호) */
* FROM 조직
WHERE 지역단조직번호 =
AND 지점조직번호 =



INDEX 회피하는 방법

지금까지 인덱스를 사용함에 있어서 주의해야 할 점에 대해서 알아 보았다. 인덱스는 사용하기 위해서 만들지만 때로는 사용을 원치 않는 경우도 있다. 이번 연재의 내용을 반대의 경우로 적용해 보면 컬럼 변형, 타입 변형, 힌트절 사용 등 다양한 방법으로 인덱스를 회피할 수도 있음을 우리는 안다.

지금까지 튜닝 실전과 관련된 내용을 5편에 걸쳐서 소개했다. 하지만 연재를 통해 필자가 강조하고 싶은 핵심 내용은 다음과 같이 두 가지로 추려진다.

첫째, 조회 대상의 범위를 줄이는 방법에 대해서 고민해야 한다. ? 대상에 대한 고민(양적)
둘째, 인덱스를 잘 활용해 찾는 속도에 대한 고민을 해야 한다. ? 속도에 대한 고민(질적)

다음 회에는 페이징에 관해 소개하겠다. 일반적인 게시판에 대한 내용이지만 잘못된 기법을 사용 한다면 시스템은 큰 부하를 일으키게 된다. 페이징 처리 튜닝은 실무에서 가장 기본적이면서도 중요한 튜닝 기법이다. (다음 회에 계속)