꿈을 향하여 질주하기

  이전 포스트에서 PostgreSQL 12를 설치해보았습니다.

  이제 SQL Shell (psql)을 사용한 기본 테스트 작업으로 데이터베이스가 정상적으로 작동하는지 확인해보도록 하겠습니다.

1. SQL Shell (psql) 실행

  윈도우 시작 메뉴의 PostgreSQL 디렉토리 내 Command line 기반으로 데이터베이스를 컨트롤할 수 있는 SQL Shell (psql)이 존재합니다.

  이 SQL Shell을 사용하여 사용자, 테이블스페이스, 데이터베이스, 테이블을 생성하고 샘플 데이터를 입력하는 테스트를 진행해보도록 하겠습니다.

  SQL Shell을 클릭하여 프로그램을 실행하면 아래와 같이 접속하고자 하는 PostgreSQL의 접속 정보를 입력해야 합니다.

  접속하고자 하는 PostgreSQL의 Server IP(혹은 도메인), Database 이름, Port 번호, User명, 비밀번호 등을 입력합니다. 만약 대괄호 안에 명시된 기본 값이 접속 정보와 일치한다면 별다른 입력없이 엔터만 입력하면 됩니다.

  비밀번호를 입력하면 데이터베이스 명이 표시된 프롬프트를 만날 수 있습니다.

  참고로 아래 예제와 같이 명령 프롬프트에서 직접 psql을 실행할 수도 있습니다.

> psql -h 호스트명 -d 데이터베이스명 -p 포트번호 -U 사용자명

2. 사용자 계정 생성

  데이터베이스를 설치한 후에는 운영을 위한(테스트 용도인 경우에도 마찬가지입니다.) 사용자 계정과 데이터베이스를 신규로 생성하여 사용하는 것이 좋습니다. DBMS에서 기본적으로 제공해주는 데이터베이스나 사용자를 사용하는 것은 보안상의 이슈가 될 수 있으며, 관리적인 측면에서도 효율성이 떨어지기 때문입니다.

  그럼 사용자 계정을 생성해보도록 하겠습니다.

  'CREATE USER' SQL을 이용하여 사용자 계정을 생성할 수 있으며, 비밀번호를 지정하여 사용자 계정을 생성하는 문법은 아래와 같습니다.

CREATE USER 사용자명
  [PASSWORD '비밀번호'];

  아래는 'postadmin'이라는 사용자 계정을 생성하는 SQL 예제입니다. 계정의 비밀번호는 'postAdmin'로 지정하였으며, SUPERUSER 권한을 부여해줬습니다.

 CREATE USER postadmin PASSWORD 'postAdmin' SUPERUSER;

  참고로 psql에서 '\du' 명령을 사용하면 접속한 데이터베이스 서버 내 사용자 목록과 각 사용자에게 부여된 롤을 확인하실 수 있습니다.

  더 자세한 CREATE USER 구문 문법은 아래 링크에서 확인하실 수 있습니다.

* URL : https://www.postgresql.org/docs/12/sql-createuser.html

3. 테이블스페이스 생성

  'CRAETE TABLESPACE' 구문으로 테이블스페이스를 생성할 수 있으며, 필수적으로 LOCATION 키워드를 사용하여 데이터 파일이 저장될 위치(디렉토리)를 명시해줘야 합니다.

CREATE TABLESPACE 테이블스페이스명
  [OWNER 사용자명]
  LOCATION '데이터파일명';

  아래는 TBS_TEST라는 테이블스페이스를 생성하는 예제입니다. 테이블스페이스의 소유자는 postadmin으로 지정하였으며, 데이터 파일이 저장될 위치는 'D:\tbs_postgresql'로 설정하였습니다.

CREATE TABLESPACE TBS_TEST
OWNER postadmin
LOCATION 'D:\tbs_postgresql';

  테이블스페이스 생성이 완료되면 '\db' 명령어를 사용하여 생성된 테이블스페이스 목록을 확인하실 수 있습니다.

  더 자세한 CREATE TABLESPACE 구문 문법은 아래 링크에서 확인하실 수 있습니다.

* URL : https://www.postgresql.org/docs/12/sql-createtablespace.html

4. 데이터베이스 생성

  'CRAETE DATABASE' 구문으로 데이터베이스를 생성할 수 있습니다.

CREATE DATABASE 데이터베이스명
    [ [ WITH ]
      [ OWNER = 사용자명 ]
      [ ENCODING = 캐릭터셋인코딩 ]
      [ TABLESPACE = 테이블스페이스명 ] ]; 

  아래는 testdb라는 데이터베이스를 생성하는 예제입니다. 데이터베이스의 소유자는 postadmin으로 지정하였으며, 인코딩은 'UTF8', 테이블스페이스는 tbs_test로 설정하였습니다.

CREATE DATABASE testdb
    WITH  
    OWNER = postadmin 
    ENCODING = 'UTF8' 
    TABLESPACE = tbs_test;

  데이터베이스 목록은 '\list' 명령어를 사용하여 확인할 수 있으며, '\c 데이터베이스명' 명령어를 사용하여 접속 데이터베이스를 변경할 수 있습니다.

  더 자세한 CREATE DATABASE 구문 문법은 아래 링크에서 확인하실 수 있습니다.

* URL : https://www.postgresql.org/docs/12/sql-createdatabase.html

5. 테이블 생성

  'CREATE TABLE' 구문으로 테이블을 생성할 수 있습니다. 

CREATE TABLE 테이블명 (
  [ { 컬럼명 데이터타입 } [ , ... ] ]

  테스트용으로 테이블 test_tab을 생성해보겠습니다. test_tab 테이블은 숫자형 ID 컬럼과 문자형 NAME 컬럼으로 구성됩니다.

CREATE TABLE test_tab (
  id INTEGER,
  name VARCHAR(30)
);

  '\dt' 명령을 통해 데이터베이스 내 테이블 목록을 확인할 수 있습니다.

  더 자세한 CREATE TABLE 구문 문법은 아래 링크에서 확인하실 수 있습니다.

* URL : https://www.postgresql.org/docs/12/sql-createtable.html

6. 데이터 입력 및 조회

  INSERT 구문으로 생성된 테이블에 데이터를 입력하며, 입력된 데이터는 SELECT 구문으로 조회가 가능합니다.

  아래는 test_tab 테이블에 테스트용 데이터 1 row를 삽입하는 예제입니다.

INSERT INTO test_tab VALUES (1, 'ABCDE');

  입력된 데이터는 아래 예제와 같이 SELECT문으로 조회가 가능합니다.

 SELECT * FROM test_tab;

7. pgAdmin 사용

  pgAdmin은 PostgreSQL에서 제공해주는 그래픽 기반 관리 툴 프로그램입니다.

시작 메뉴를 통해 pgAdmin을 실행하실 수 있으며, 웹 브라우저에서 URL(http://127.0.0.1:59594/browser)을 입력하여 직접 접속할 수도 있습니다.

  pdAdmin에 접속하면 아래와 같이 화면 왼쪽의 Browser를 통해서 데이터베이스 서버 내 데이터베이스, 사용자 롤, 테이블스페이스 등을 계층 구조로 탐색할 수 있습니다.

  직접 쿼리문을 사용하여 데이터 관리/조회 작업도 가능합니다.

  pgAdmin 페이지 상단 메뉴 중 'Tools'을 클릭하면 아래와 같이 하위 메뉴가 펼쳐집니다.

  위 하위 메뉴 중 'Query Tool'을 클릭하면 아래와 같이 쿼리를 작성하여 실행하고 결과도 확인할 수 있는 패널이 생성됩니다.

  Query editor에 TEST_TAB 테이블을 조회하는 SELECT문을 작성한 후, 도구 모음 중 실행 버튼(▶)을 클릭하거나 'F5'를 입력하면 쿼리가 실행됩니다.

  쿼리 실행 결과는 위와 같이 브라우저 하단에서 그리드 형태로 확인이 가능합니다.

  이상으로 postgreSQL의 기본적인 사용법을 살펴보았습니다. 더 자세한 내용은 아래 URL을 참고하시면 됩니다.

[Ref.] https://www.postgresql.org/docs/12/index.html

        https://www.postgresql.org/docs/current/app-psql.html

  윈도우 10 환경에서 PostgreSQL 12 버전을 설치해보도록 하겠습니다.

  이 포스트를 작성하는 시점에 PostgreSQL 13 Beta 2 버전이 릴리즈(2020. 06.25)되었는데요. 아직은 베타 버전이므로 안정성을 생각해서 하위 버전인 12 버전을 다운로드 받아 설치하겠습니다.

1. 설치 파일 다운로드

* URL : https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

  위 URL에 접속하면 아래와 같은 다운로드 페이지를 확인하실 수 있습니다.
  아래 페이지에서 12.3 버전, WIndows x86-64의 'Download' 버튼을 클릭하면 설치 파일을 다운로드 받을 수 있습니다.

  만약, 소스 빌드를 통해 PostgreSQL을 설치하거나, 전문가용 설치 파일을 다운로드 하고자하는 경우에는 하단의 URL에 접속하여 파일을 다운로드 하셔야 합니다.

  > URL : https://www.postgresql.org/download/

2. PostgreSQL 설치

  다운로드가 완료된 설치 파일을 실행합니다.

  설치 파일을 실행하면 아래와 같이 Welcome 메세지를 확인하실 수 있습니다.

  'Next' 버튼을 클릭하여 다음 단계로 넘어갑니다.

  PostgreSQL을 설치할 경로를 입력 후 'Next' 버튼을 클릭합니다.

  설치할 컴포넌트를 선택하는 화면입니다.

  PostgreSQL 서버 외에 아래 추가적인 컴포넌트를 설치할 수 있습니다.

  - pgAdmin : 데이터베이스를 관리하고 개발하기 위한 그래픽 기반 툴.
  - Stack Builder : 추가로 설치해서 사용 가능한 툴과 드라이버를 관리하는 패키지 매니저. 
                       예) 관리, 통합, 마이그레이션, 복제, 공간 연산, 커넥터 등.
  - Command Line Tools : SQL Shell (psql)

  설치할 컴포넌트 선택이 완료되면 'Next' 버튼을 클릭합니다.

  데이터 파일을 저장할 경로를 설정 후 'Next' 버튼을 클릭합니다.

  데이터베이스 Superuser 계정의 비밀번호와 비밀번호 확인 값을 입력 후 'Next' 버튼을 클릭합니다.

  서버의 기본 포트 번호를 설정합니다. 기본 값은 5432입니다.

  포트 설정 후 'Next' 버튼을 클릭하면 Locale 설정 화면을 볼 수 있습니다.

  Locale을 [Default locale]로 선택하면 OS에 설정된 locale 정보를 따라가게 됩니다.

  'Next' 버튼을 클릭하면
  지금까지 설정한 설치 정보를 최종적으로 확인할 수 있습니다.

  설치 정보가 정상인지 확인했다면 'Next' 버튼을 클릭하여 설치를 시작합니다.

  설치가 시작되며, Progress bar를 통해 진행률을 확인하실 수 있습니다.

  설치가 정상적으로 완료되면 아래와 같은 다이얼로그를 확인하실 수 있습니다.

  설치 마법사가 종료된 후 Stack Builder를 실행하여 추가적인 드라이버나 툴을 설치하려면 위 화면 가운데의 체크 박스를 선택합니다.

  Stack Builder에 대해서는 별도의 포스트에서 언급할 계획이니, 여기서는 체크 박스를 해제하도록 하겠습니다.
  'Finish' 버튼을 클릭하면 PostgreSQL 설치가 마무리됩니다.

3. postgreSQL 실행

  PostgreSQL 설치 후 윈도우 시작 메뉴를 확인하면 아래와 같이 시작 메뉴가 생성된 것을 확인하실 수 있습니다.

  위 메뉴 중 pgAdmin을 이용해 데이터베이스에 접속해보도록 하겠습니다.

  pgAdmin을 실행하면 아래와 같이 웹브라우저에서 pgAdmin 웹페이지(기본 URL : http://127.0.0.1:59594/browser)를 확인하실 수 있습니다.

  설치시 설정한 super 사용자 계정 비밀번호를 입력 후 'OK' 버튼을 클릭합니다.

  비밀번호를 입력하여 로그인을 하면
  아래와 같이 접속된 서버 목록 및 서버 정보(데이터베이스, Role, 테이블스페이스 등)와 서버의 상태를 확인할 수 있는 대시보드를 보실 수 있습니다.

4. PostgreSQL 시작 및 중지

  윈도우윈도 환경에서의 PostgreSQL은 윈도우 서비스를 통해 서비스 시작/중지 작업을 진행할 수 있습니다.

  윈도우 시작 메뉴에서 서비스를 검색하거나 'services.msc'를 실행합니다.

  서비스 프로그램이 실행되면 오른쪽 서비스 목록 중에 'postgresql-x64-12'를 확인하실 수 있습니다.

  postgresql-x64-12를 선택 후 오른쪽 버튼을 클릭 후 나타난 팝업 메뉴 중 '속성'을 클릭하여 서비스 정보를 확인합니다.

  postgreSQL를 설치하면 기본적으로 서비스에 등록되며, 윈도우 가동 시 자동으로 서비스가 시작되게 설정되어 있습니다.
 postgreSQL을 종료하기 위해서는 위 속성 다이얼로그 내 '중지' 버튼을 클릭하면 됩니다.

  postgreSQL 서비스가 중지되면 아래 '서비스 상태'가 '중지됨'으로 변경됩니다.  

  다시 서비스를 시작하기 위해서는 속성 다이얼로그 내 '시작' 버튼을 클릭하면 됩니다.

 postgreSQL 서비스가 정상적으로 가동되었다면 아래 다이얼로그처럼 '서비스 상태'가 '실행 중'으로 변경된 것을 확인하실 수 있습니다.

  postgreSQL의 시작과 중지 작업은 속성 다이얼로그 외에도
  아래 화면에 표시된 툴바나 목록 왼쪽의 메뉴를 통해서도 작업이 가능합니다.

  비효율적인 PL/SQL(Function)을 제거하여 성능을 높인 쿼리 예제를 살펴보도록 하겠습니다.
  이 포스트에 사용된 예제는 부하를 유발하는 PL/SQL를 보여주기 위해 임의로 작성된 쿼리입니다. 최대한 가독성을 높이기 위해 짧게 작성하고자 노력했으며, 이로 인하여 조금은 억지스러운 면도 있지만 설명을 위한 예제이니 참고 바랍니다.

  먼저 튜닝 전 쿼리를 살펴보도록 하겠습니다.

SELECT SALE_DT
     , PROD_NO
     , FNC_GET_PROD_NM(PROD_NO) AS PROD_NM
  FROM PROD_SALE
 WHERE SALE_DT = '20191201';

  PROD_SALE 테이블 데이터 중 SALE_DT 값이 '20191201'인 데이터의 SALE_DT, PROD_NO, PROD_NM 값을 추출하고 있습니다. 이 중 PROD_NM 값은 FNC_GET_PROD_NM 함수를 호출한 결과 값을 출력합니다.

  위 쿼리에서 사용된 함수 FNC_GET_PROD_NM의 내용은 아래와 같습니다.

CREATE OR REPLACE FUNCTION FNC_GET_PROD_NM (V_PRODNO IN NUMBER) 
          RETURN VARCHAR2
IS
V_PROD_NM VARCHAR2(100);
V_PROD_STTS_CD VARCHAR2(10);
V_RES_NM VARCHAR2(100);
BEGIN
SELECT PROD_NM, PROD_STTS_CD
    INTO V_PROD_NM, V_PROD_STTS_CD
     FROM PROD 
    WHERE PROD_NO = V_PRODNO ;

IF V_PROD_STTS_CD = '10' THEN
  V_RES_NM := '[판매중] ' || V_PROD_NM;
ELSIF V_PROD_STTS_CD IN ('30','40') THEN
 V_RES_NM := '[판매종료] ' || V_PROD_NM;
ELSIF V_PROD_STTS_CD IN ('50','60') THEN
 V_RES_NM := '[판매예정] ' || V_PROD_NM;
ELSE
 V_RES_NM := '[기타] ' || V_PROD_NM;
END IF;

RETURN V_RES_NM; 
END; 

  FNC_GET_PROD_NM 함수는 입력받은 인자 값 V_PRODNO에 매칭 되는 상품명(더 정확하는 상품 상태 값과 상품명)을 반환하는 함수입니다. 
  PROD 테이블에서 PROD_NO 컬럼의 값이 입력받은 인자 값 V_PRODNO와 일치되는 데이터의 PROD_NM과 PROD_STTS_CD 값을 추출하여 V_PROD_NM과 V_PROD_STTS_CD 변수에 각각 입력합니다. IF문을 이용해 V_PROD_STTS_CD 값에 따라 상품상태값과 상품명(V_PROD_NM)을 합친 문자열을 V_RES_NM에 대입하고, 마지막에 V_RES_NM 값을 반환하게 됩니다.

  위 쿼리의 AUTOTRACE 결과를 살펴보겠습니다.

  AUTOTRACE의 Statistics를 보면 recursive calls이 과도하게 발생한 것을 확인할 수 있습니다. recursive calls는 오라클 내부적으로 필요한 작업이나 SQL문을 수행할 때 발생합니다. 예를 들어, 데이터를 Insert시 공간이 부족하면 recursive calls로 공간을 동적으로 할당하는 작업을 하게 됩니다. 여기서는 PL/SQL 내의 SQL문을 수행을 위해 recursive calls를 호출하였습니다.

  SELECT절에 사용된 PL/SQL의 실행 횟수는 SELECT 되는 데이터의 건수와 비슷합니다. SELECT되는 각 로우의 값을 입력 인자 값으로 해서 PL/SQL을 수행하게 됩니다. PL/SQL 결과가 캐시에 저장되어 있다면 PL/SQL 수행 없이 캐쉬 내 결과 값을 반환하게 되므로 항상 SELECT 되는 데이터 건수와 동일한 것은 아닙니다.

  PROD_SALE 테이블에서 SALE_DT 값이 '20191201'인 데이터는 251,896건입니다. 251,896건의 recursive calls이 발생했으니 데이터 건수만큼 함수가 호출되었음을 확인할 수 있습니다.
  여기서 가장 큰 문제는 FNC_GET_PROD_NM 함수를 호출할 때마다 함수 내부의 쿼리문을 반복해서 실행하게 된다는 것입니다. SQL 최적화를 위해서는 블럭 액세스를 줄여야 하는데, 함수내 쿼리문을 실행하기 위해 PROD 테이블을 여러 번 액세스하게되어 블럭 액세스(consistent gets)가 과도하게 증가하였습니다.

  위 쿼리를 최적화하기 위해서는 과도한 블럭 액세스를 유발하는 함수를 제거해야 합니다.

  아래는 위 튜닝 전 쿼리에서 함수 제거를 위해 함수 내 PROD 테이블 조회를 조인으로 풀어낸 쿼리입니다.

SELECT S.SALE_DT
     , S.PROD_NO
     , CASE WHEN PROD_STTS_CD = '10' THEN '[판매중] ' || P.PROD_NM
            WHEN PROD_STTS_CD IN ('30','40') THEN '[판매중] ' || P.PROD_NM
            WHEN PROD_STTS_CD IN ('50','60') THEN '[판매중] ' || P.PROD_NM
            ELSE '[기타] ' || P.PROD_NM END  AS PROD_NM
  FROM PROD_SALE S
     , PROD P
 WHERE S.PROD_NO = P.PROD_NO
   AND SALE_DT = '20191201'
;

  PROD_SALE 테이블의 데이터는 튜닝 전 쿼리처럼 SALE_DT 값이 '20191201'인 데이터만 추출합니다. 그리고 FNC_GET_PROD_NM 함수 대신에 ROD_SALE 테이블과 PROD 테이블을 PROD_NO 키를 이용해 조인을 하고 있습니다. SELECT절의 PROD_NM 항목은 함수 내 연산처럼 PROD_STTS_CD 값에 따라 상태 값을 CASE문을 이용해 분기하여 PROD_STTS_CD에 따른 상품상태값과 상품명(PROD_NM)을 합친 결과로 출력됩니다.

  위 쿼리의 AUTOTRACE 결과 값은 아래와 같습니다.

  PROD_SALE테이블과 PROD테이블을 조인하는 형태로 쿼리를 수정하여 블럭 액세스가 5.3%로(797,850블럭에서 42,664블럭) 감소되었습니다.

  실행계획을 보면 PROD_SALE 테이블과 PROD 테이블을 FULL TABLE SCAN 하여 HASH 조인했음을 알 수 있습니다. 두 테이블 중 크기가 상대적으로 작은 PROD 테이블이 BUILD 테이블이 되어 해쉬 테이블을 만들고 PROD_SALE 테이블이 PROBE 테이블이 되어 해시 조인했음을 확인할 수 있습니다. 

  해시 조인을 수행하였기 때문에 튜닝 후 쿼리에서는 PROD 테이블을 한 번만 액세스 하였습니다.
  튜닝 전 쿼리에서는 함수 실행을 위해 SELECT 건수만큼 PROD 테이블을 액세스 하여 과도한 블럭 액세스를 유발하였는데, 쿼리를 조인으로 풀어 이러한 과도한 블럭 액세스를 제거한 것입니다.

  공간 연산 함수를 이해하기에 앞서 아래 포스트를 먼저 참고하실 것을 권장드립니다.

  공간 연산 함수는 두 공간 객체의 연산 결과로 새로운 공간 객체를 반환해주는 함수입니다.

  MySQL에서 제공해주는 공간 연산 함수 중에서 자주 사용되는 함수는 아래와 같습니다

  MySQL에서 공간 연산 함수의 테스트를 위해 아래 테스트 데이터를 사용자 변수에 저장하도록 하겠습니다.

SET @p0 = POINT(0,0);

SET @p1 = POINT(1,1);

SET @p2 = POINT(2,2);

SET @l1 = ST_GeomFromText('LineString(1 1, 3 3, 5 5, 10 10, 14 14)');

SET @l2 = ST_GeomFromText('LineString(2 2, 8 8)');

SET @b1 = ST_GeomFromText('Polygon((1 1,1 10,10 10,10 1,1 1))');

  위에서 정의한 사용자 변수를 가지고 아래와 같이 공간 연산 함수를 테스트해보았습니다.

SELECT ST_AsText(ST_Intersection(@p0, @p1)), ST_AsText(ST_Intersection(@b1, @p2));

SELECT ST_AsText(ST_Union(@p0, @p1)), ST_AsText(ST_Union(@l1, @p1));

SELECT ST_AsText(ST_Difference(@p0, @p1)), ST_AsText(ST_Difference(@l1, @p2));

SELECT ST_AsText(ST_Buffer(@b1,0));

SELECT ST_AsText(ST_Envelope(@l2));

SELECT ST_AsText(ST_StartPoint(@l1));

SELECT ST_AsText(ST_EndPoint(@l1));

SELECT ST_AsText(ST_PointN(@l1,3));

  테스트 결과 화면은 아래와 같습니다.

  이전 포스팅에서는 공간 데이터 타입에 대해 알아봤습니다.

  이제 공간 데이터 타입으로 저장된 공간 데이터를 이용해 연산을 할 수 있는 공간 함수에 대해 알아보겠습니다.

  공간 함수는 공간 관계 함수와 공간 연산 함수로 구분 질 수 있는데, 이 중에서 공간 관계 함수는 두 공간 객체 간의 관계를 일반 데이터 타입(Boolean 또는 숫자)으로 반환해주는 함수입니다.

  MySQL에서 제공해주는 공간 관계 함수 중에서 자주 사용되는 함수는 아래와 같습니다.

  공간 관계 함수의 테스트를 위해 아래 테스트 데이터를 사용자 변수에 저장하도록 하겠습니다.

SET @p0 = POINT(0,0);

SET @p1 = POINT(1,1);

SET @p2 = POINT(2,2);

SET @l1 = ST_GeomFromText('LineString(1 1, 3 3)');

SET @l2 = ST_GeomFromText('LineString(2 2, 4 4)');

SET @b1 = ST_GeomFromText('Polygon((1 1,1 10,10 10,10 1,1 1))');

  위에서 정의한 사용자 변수를 가지고 아래와 같이 공간 관계 함수를 테스트해보았습니다.

SELECT ST_Equals(@p1, @p1), ST_Equals(@p1, @l1);

SELECT ST_Disjoint(@b1, @p0), ST_Disjoint(@b1, @p2);

SELECT ST_Within(@p2, @b1), ST_Within(@b1, @p2);

SELECT Overlaps(@l1, @l2), Overlaps(@l1, @p1);

SELECT ST_Intersects(@b1, @p2), ST_Intersects(@b1, @p1);

SELECT ST_Contains(@b1, @p2), ST_Contains(@p2, @b1);

SELECT ST_Touches(@b1, @p1), ST_Touches(@b1, @p2);

SELECT ST_Distance(@p1, @p2);

  테스트 결과 화면은 아래와 같습니다.

이어서, 다음 포스트에서는 공간 연산 함수에 대해 설명드리도록 하겠습니다.

  이전 포스팅에서 공간 데이터베이스의 필요성에 대해 RDBMS와 비교 설명을 해보았습니다.

  그럼, 본격적으로 공간 데이터베이스에 대해 알아보겠습니다.

  공간 데이터베이스. 

  쉽게 설명하면 X, Y 좌표로 구성된 공간 데이터를 저장하고 연산할 수 있는 기능을 제공해주는 데이터베이스라고 생각하시면 됩니다. 다른 말로, 공간 데이터의 저장 형태를 구분하는 공간 데이터 타입과 공간 데이터를 연산할 수 있는 공간 함수가 제공되는 데이터베이스라고 표현 가능합니다.

  우리가 일반적으로 사용하고 있는 RDBMS인 Oracle, MariaDB, MySQL, PostgreSQL 등에서 공간 데이터를 처리하기 위한 기능을 제공하고 있습니다. 이 솔루션을 이용해 공간 데이터베이스를 생성/관리할 수 있는 겁니다. 심지어 Hive에서도 관련 Library를 설치하면 공간 데이터를 저장하고 공간 연산이 가능합니다.

  그럼, 공간 데이터 타입과 공간 함수를 알면 공간 데이터베이스를 정복하게 되는걸까요? 저는 그렇다고 생각합니다. 가장 기본적인 공간 데이터 타입과 공간 함수를 알면 쉽게 공간 데이터를 처리할 수 있으니까요. 가장 기본적인 기초만 알면 그 후의 응용문제는 쉽게 풀어갈 수 있다고 생각합니다.

  참고로, 공간 데이터 타입과 함수는 Open Geospatial Consortium(OGC, www.ogc.org)에서 표준을 정의하고 있습니다. 아래 링크를 클릭하시면 표준 문서를 다운로드 하실 수 있습니다.
   > 표준 문서 다운로드 : http://portal.opengeospatial.org/files/?artifact_id=25354

  이제 공간 데이터베이스의 기초인 공간 데이터 타입에 대해 알아보겠습니다. 여기서는 MySQL에서 제공하는 공간 데이터 타입을 기준으로 설명을 진행할 것입니다.

  자주 사용되는 공간 데이터 타입은 아래와 같습니다.

  MySQL에서 공간 데이터 타입의 컬럼을 추가하고, 공간 데이터를 입력해보도록 하겠습니다.

CREATE TABLE GEO_TAB
( 
  GID  INT(10),
  PT   POINT,
  LS   LINESTRING
);

  위 SQL은 테스트용 테이블 GEO_TAB을 생성하는 DDL문입니다. GEO_TAB 테이블은 일반 INT형 GID 컬럼, POINT 공간데이터 타입인 PT 컬럼과 LINESTRING 공간데이터 타입인 LS 컬럼으로 구성됩니다.

  이제 위에서 생성한 GEO_TAB 테이블에 데이터를 입력해보도록 하겠습니다. 

SET @g1 = 'POINT(10 10)';
SET @g2 = GeomFromText( 'LINESTRING(5 5, 10 5, 15 10)' );
INSERT INTO GEO_TAB VALUES ( 1, GeomFromText(@g1), @g2 );

  데이터 입력 예제의 가독성을 위해 사용자 변수를 사용하여 데이터를 입력해보았습니다.
  사용자변수 g1에는 POINT형 공간 데이터를 입력하기 위한 공간 데이터 문자열을 저장합니다. 사용자 변수 g2에는 LINESTRING형 공간 데이터를 저장합니다.
  이 사용자 변수의 값을 GEO_TAB 테이블에 입력을하는데 공간 데이터의 입력도 일반 RDBMS처럼 INSERT문을 사용합니다.
  이 때, 공간 데이터 타입은 Binary 형태로 저장되기 때문에 문자열을 저장한 사용자 변수 g1은 공간 데이터 타입으로 변환해주는 GeomFromText 함수를 사용하여 Binary로 변환 후 저장해야 합니다.
(* GeomFromText 함수는 MySQl 8.0에서 Remove되었으며, ST_GeomFromText 함수를 사용하면 됩니다. )

  공간 데이터를 조회하기 위해서는 Binary 데이터를 텍스트로 변환하는 AsText함수를 사용하면 됩니다.
(* AsText 함수는 MySQl 8.0에서 Remove되었으며, ST_AsText 함수를 사용하면 됩니다. )

SELECT GID, AsText(PT), AsText(LS) FROM GEO_TAB; 

  아래 그림은 MySQL에서 테스트한 결과 화면입니다.

  이어서, 아래 포스트에서 공간 관계 함수와 공간 연산 함수에 대해 설명드리도록 하겠습니다.

  혹시 아래 나열된 서비스나 시스템을 사용해보신 적이 있으신가요?

버스정보시스템(버스 실시간 도착 알림)
차량 네비게이션 시스템
지하철 앱
지도 앱
차량 호출 서비스
가까운 상점 찾기
지역 쿠폰 수신
SNS 등록(장소 정보 입력)
...

  위 서비스/시스템을 한 번이라도 사용하신 적이 있다면 직/간접적으로 공간 데이터와 조우하신 겁니다.

  공간 데이터란 말 그대로 이 세상에 존재하는 위치 및 영역을 X, Y 좌표로 표현한 데이터입니다.
  예를 들어, 내가 지금 서 있는 위치(점), 지하철 노선(선), 건물 영역(다각형) 등을 X, Y 좌표(예: 경도, 위도)로 표현할 수 있습니다. 이러한 공간 데이터를 활용하여 위와 같은 위치 기반 서비스가 제공될 수 있는 겁니다.

  스마트 폰 없이는 살 수 없는 시대를 살아가고 있는 우리는 매일 공간 데이터를 소비하고 생산하며 살아가고 있습니다. 매일매일 생산되고 있는 대용량의 공간 데이터. 생산되는 데이터의 양이 증가될수록 데이터를 관리하는 기술 또한 발전이 필요합니다. 공간 데이터도 마찬가지입니다. 일반적인 데이터(숫자, 문자, 날짜)와는 표현 방식이나 연산 방식이 상이하기 때문에 별도의 저장/관리 기술이 필요로 합니다.

  우선, 일반 RDBMS에서 공간 데이터를 저장하고 연산하는 방법을 살펴보도록 하겠습니다.

  선이나 다각형 데이터는 연속된 점 좌표로 구성됩니다. 이러한 다수의 점 좌표를 일반 RDBMS에서 저장하는 방법은 두 가지가 존재합니다.
  1) 좌표 엔터티를 별도로 생성하고, 하나의 좌표를 하나의 로우로 저장. 좌표를 구별할 수 있는 별도의 식별자가 필요함.
  2) 점 좌표 구분자를 사용하여 하나의 문자열로 저장

  아래 ERD는 상점 정보와 상점이 위치한 건물의 정보를 저장하기 위한 엔터티를 보여주고 있습니다.
  왼쪽은 1) 번 방식으로 설계한 ERD이며, 오른쪽은 2) 번 방식으로 설계한 ERD입니다.

  1)번 방식으로 설계한 엔터티 중에서 좌표 값을 저장하는 건물영역좌표에는 아래와 같이 좌표 정보가 저장될 것입니다.  

  위와 같이 좌표 값을 저장하기 위한 하위 엔터티를 생성하는 것은 추가적인 저장 공간을 필요로 합니다. 그리고 하나의 건물영역좌표가 다수의 로우로 표현되기에 다수의 블록에 분산되어 저장될 가능성이 높습니다. 이는 관련 테이블 액세스 시 블록 액세스가 증가되어 성능 부하가 존재할 수 있게 됩니다.

  2) 번 방식으로 설계한 엔터티 중에서 좌표 값을 저장하는 건물에는 아래와 같이 좌표 정보가 저장될 것입니다. 연속된 점 좌표를 콤마(,)를 이용하여 구분해 공간 데이터를 저장하고 있습니다.

  위 데이터를 이용해 공간 연산을 하기 위해서는 문자열 형태(x1 y1, x2 y2, … xn yn)인 좌표 값을 점 좌표(x, y)로 변환하는 작업이 선행되어야 합니다.

  아래 사용자 위치에서 가까운 순서대로＇GAS STATION＇목록을 조회하는 SQL (Oracle)을 살펴보겠습니다. 

WITH BLDS AS (
     SELECT S.STORE_ID
          , S.STORE_NM
          , B.BLD_ID
          , B.BLD_ADDR
          , B.BLD_AREA_CRD
       FROM STORE S
          , BLD B
      WHERE S.STORE_TYPE = 'GAS STATION'
        AND S.BLD_ID = B.BLD_ID
)
SELECT STORE_ID
     , STORE_NM
     , BLD_ID
     , BLD_ADDR
     , DIST 
  FROM (
        SELECT STORE_ID
             , STORE_NM
             , BLD_ID
             , BLD_ADDR
             , DIST
             , ROW_NUMBER() OVER(PARTITION BY BLD_ID ORDER BY DIST) CRD_RN
          FROM (
                SELECT STORE_ID
                     , STORE_NM
                     , BLD_ID
                     , BLD_ADDR
                     , SQRT(ABS(POWER(REGEXP_SUBSTR(BLD_CRD, '[^ ]+', 1, 1) - :USER_X_CRD, 2))+ ABS(POWER(REGEXP_SUBSTR(BLD_CRD, '[^ ]+', 1, 2) - :USER_Y_CRD, 2))) AS DIST
                  FROM (
                        SELECT STORE_ID
                             , STORE_NM
                             , BLD_ID
                             , BLD_ADDR
                             , BLD_CRD_SEQ
                             , REGEXP_SUBSTR(BLD_AREA_CRD, '[^,]+', 1, BLD_CRD_SEQ) AS BLD_CRD
                          FROM BLDS
                             , (SELECT LEVEL AS BLD_CRD_SEQ
                                  FROM DUAL
                               CONNECT BY LEVEL <= ( SELECT MAX( LENGTH(BLD_AREA_CRD) - LENGTH(REPLACE(BLD_AREA_CRD, ',', ''))) + 1 FROM BLDS ) ) L
                     ) A
                 WHERE BLD_CRD IS NOT NULL
             ) B
     ) C
 WHERE C.CRD_RN = 1
 ORDER BY DIST;

  건물영역좌표(BLD_AREA_CRD)를 다수의 로우로 변경하기 위해 CONNECT BY LEVEL을 사용해 카테시안조인을 하였습니다. 그리고 점 좌표를 콤마(,) 기준으로 분리하기 위해 정규식인 REGEXP_SUBSTR 함수를 사용하였습니다.
  위와 같은 방식으로 쿼리를 작성하다 보니 가독성이 떨어지고 공간 연산을 위한 수식이 복잡해집니다.

  그럼 공간 데이터베이스에서 공간 데이터를 저장하고 연산하는 방법을 살펴보겠습니다.

  아래 ERD는 건물영역의 좌표 값을 POLYGON 데이터 타입인 건물Polygon 속성에 저장하는 건물 엔터티를 보여주고 있습니다.

  건물 엔터티에는 아래와 같이 데이터가 저장될 것입니다.

  그리고 두 공간 객체 간의 거리를 계산하는 ST_DISTANCE 함수를 사용하여, 사용자 위치에서 가까운 순서대로＇GAS STATION＇목록을 조회하는 SQL (MySQL)을 살펴보겠습니다. 

SELECT S.STORE_ID
     , S.STORE_NM
     , B.BLD_ID
     , B.BLD_ADDR
     , B.BLD_POLYGON
     , ST_DISTANCE(B.BLD_POLYGON, POINT(@USER_X_CRD, @USER_Y_CRD) ) DIST
  FROM STORE S
     , BLD B
 WHERE S.STORE_TYPE = 'GAS STATION'
   AND S.BLD_ID = B.BLD_ID
 ORDER BY DIST;

  일반 RDBMS에서의 예와 비교하여 쿼리가 단순해지고 가독성이 좋아진 것을 확인하실 수 있습니다. 또한, 다양한 공간 함수를 제공하여 복잡한 연산도 쉽게 처리가 가능합니다.

  아래 쿼리를 살펴보도록 하겠습니다.

SELECT CNT1, CNT2, CNT3, CNT4, CNT5, CNT6, CNT7, CNT8 
 FROM (
       SELECT (SELECT COUNT(*) FROM CHG_LOGS WHERE REG_DT=:B1 AND ST_CD='0' AND LOG_LVL='1') CNT1
            , (SELECT COUNT(*) FROM CHG_LOGS WHERE REG_DT=:B1 AND ST_CD='1' AND LOG_LVL='1') CNT2
            , (SELECT COUNT(*) FROM CHG_LOGS WHERE REG_DT=:B1 AND ST_CD='2' AND LOG_LVL='1') CNT3
            , (SELECT COUNT(*) FROM CHG_LOGS WHERE REG_DT=:B1 AND ST_CD='3' AND LOG_LVL='1') CNT4
            , (SELECT COUNT(*) FROM CHG_LOGS WHERE REG_DT=:B1 AND ST_CD='4' AND LOG_LVL='1') CNT5
            , (SELECT COUNT(*) FROM CHG_LOGS WHERE REG_DT=:B1 AND ST_CD='5' AND LOG_LVL='1') CNT6
            , (SELECT COUNT(*) FROM CHG_LOGS WHERE REG_DT=:B1 AND ST_CD='6' AND LOG_LVL='1') CNT7
            , (SELECT COUNT(*) FROM CHG_LOGS WHERE REG_DT=:B1 AND ST_CD='7' AND LOG_LVL='1') CNT8
         FROM DUAL
    ) A;

  CHG_LOGS 테이블의 조건별 건수를 출력하는 쿼리입니다.
  ST_CD 값 별 데이터 건수를 계산하기 위해 스칼라 서브 쿼리 안에서 CHG_LOGS 테이블을 액세스하고 있습니다. 문제는 유사한 스칼라 서브 쿼리가 총 8개가 존재한다는 점입니다. 다시 말해서, CHG_LOGS 테이블을 8번 중복해서 액세스 한다는 뜻입니다.

  위 쿼리의 AUTOTRACE 결과를 확인해보겠습니다.

  위 쿼리의 실행계획을 살펴보면 아래와 같은 패턴(인덱스 스캔 및 테이블 랜덤 액세스)을 8번 반복해서 수행한 것을 확인할 수 있습니다.

  위와 같은 반복적인 액세스 작업 때문에 과도한 블럭 읽기(109,718블럭)가 발생하고 이로 인하여 쿼리 수행 시간도 길어지게 되었습니다.

  그럼, 어떤 방식으로 중복 액세스를 제거하여 쿼리 성능을 높일 수 있는지 확인해보도록 하겠습니다.

  아래는 튜닝 후 쿼리입니다.

SELECT NVL( SUM( CASE WHEN ST_CD='0' THEN 1 ELSE 0 END), 0) AS CNT1
     , NVL( SUM( CASE WHEN ST_CD='1' THEN 1 ELSE 0 END), 0) AS CNT2
     , NVL( SUM( CASE WHEN ST_CD='2' THEN 1 ELSE 0 END), 0) AS CNT3
     , NVL( SUM( CASE WHEN ST_CD='3' THEN 1 ELSE 0 END), 0) AS CNT4
     , NVL( SUM( CASE WHEN ST_CD='4' THEN 1 ELSE 0 END), 0) AS CNT5
     , NVL( SUM( CASE WHEN ST_CD='5' THEN 1 ELSE 0 END), 0) AS CNT6
     , NVL( SUM( CASE WHEN ST_CD='6' THEN 1 ELSE 0 END), 0) AS CNT7
     , NVL( SUM( CASE WHEN ST_CD='7' THEN 1 ELSE 0 END), 0) AS CNT8
  FROM CHG_LOGS A
 WHERE REG_DT=:B1
   AND LOG_LVL='1';

  중복 액세스를 피하기 위해 스칼라 서브 쿼리를 제거하였습니다. CHG_LOGS 테이블은 한 번만 액세스 하도록 변경하였으며, WHERE 절에는 각 스칼라 서브 쿼리에서 동일하게 적용되었던 조건(REG_DT=:B1 AND LOG_LVL='1')을 입력하였습니다.
  그리고 CASE문을 이용하여 ST_CD 별 데이터 건수를 계산하였습니다.

  위와 같이 변경된 쿼리의 AUTOTRACE 결과는 아래와 같습니다.

  실행계획을 살펴보면 IX_CHG_LOG 인덱스와 CHG_LOGS 테이블을 한번씩 액세스하고 있는 것을 확인하실 수 있습니다. 액세스 중복이 제거되어 실행시간(0.02초)과 블럭 읽기(13,714블럭)가 감소된 것을 확인하실 수 있을 것입니다. 물론, 중복 제거의 방법으로 함수를 사용하여 CPU의 사용량이 증가하게 됩니다. 하지만, 쿼리에서 가장 큰 부하는 블럭 읽기이기 때문에 부하 측면에서 CPU 사용량은 상대적으로 낮은 비용이라고 판단할 수 있습니다.

  지금까지 살펴봤던 SQL 튜닝 방법이 집합적 사고의 한 예라고 볼 수 있습니다. 데이터 블럭 하나하나가 퍼즐 조각이며, 각 퍼즐 조각을 최대한 적게 활용하여 멋진 작품인 결과물을 만들어내는 게 집합적 사고에 의한 쿼리문 작성인 거죠. 많은 분들이 시간적 여유를 갖고 적은 퍼즐 조각으로 멋진 작품을 탄생시키는 기쁨을 느껴보셨으면 좋겠습니다.

  데이터의 가치가 높아지고 데이터 저장/관리 기술이 발전하면서 다양한 데이터베이스 소프트웨어가 출시되고 있습니다.

  Hadoop 클러스터링 환경에 기반한 Hive, HBase, Spark SQL는 빅데이터 수집/분석에 대한 요구사항의 증대와 같이 사용량도 늘어가고 있습니다.
  인프라 운영에 대한 리스크를 줄여주고 빠른 서비스 구축이 가능하다는 장점을 내세워 급속도로 성장을하고 있는 클라우드 서비스 플랫폼이 증가하면서 클라우드 환경의 데이터베이스도 사용이 증가하고 있습니다. 기존의 데이터베이스가 클라우드 환경에서도 서비스를 시작하는 경우도 있지만, Amazon DynamoDB, Google BigQuery, Microsoft Azure SQL Database 처럼 클라우드 플랫폼 전용 데이터베이스 서비스도 생겨나고 있습니다.
  그 외에도 다양한 형태의 데이터를 관리하기 위해 NoSQL, Graph DB, Time Series DB 등의 데이터베이스도 사용량이 증가하고 있습니다.

  하지만, 아직도 DBMS 시장에서 우위를 차지하고 있는 것은 역시 RDBMS입니다.

  제가 예상하건데 표 형태로 데이터를 보고 싶어하는 사용자의 욕망이 바닥으로 추락하지 않는 한 RDBMS는 끝까지 건재할 것입니다.

  아무튼, 이번 포스팅에서는 이 RDBMS의 성능 최적화 전략에 대해서 정리해보고자 합니다. 

  우선, 'RDBMS'의 성능이 좋다는 기준은 무엇일까요?
  RDBMS의 목적이 동시성 제어로 다수의 사용자가 빠르게 원하는 데이터를 저장하고 추출하는 것이니, 얼마나 많은 사용자가 얼마나 빨리 데이터를 처리할 수 있느냐가 성능의 기준이 되겠습니다.
  실제로 SI 프로젝트에서도 위와 같은 기준으로 성능 부하 테스트를 진행하고 있습니다.

  그럼 쿼리 실행 속도가 매우 빠르면 성능상으로 문제가 없는 걸까요?
  실행 속도가 빠르다는 건 표면상의 성능 기준이라고 할 수 있습니다. 정확하게 얘기하면  쿼리 실행 시 내부적으로 얼마나 많은 블럭을 I/O했는지가 성능을 판가름하는 중요한 지표가 됩니다. 모든 시스템에서 디스크 I/O는 상대적으로 느린 속도로 인하여 많은 비용을 유발하는 원인으로 인식하고 있습니다. 그래서 디스크 I/O를 줄이기 위해 버퍼에 데이터를 로딩하여 사용합니다. 하지만 버퍼는 크기의 한계가 있기 때문에 디스크 I/O를 완전히 제거할 수는 없습니다.
  최소한의 블럭 I/O로 버퍼의 활용도를 높이고 디스크 I/O를 줄이는 것이 실행 속도를 빠르게 하고 성능을 높일 수 있는 궁극적인 방법이 되겠습니다.

  이 포스팅에서 언급하는 내용들은 거의 다 블럭 I/O를 줄이기 위한 방법들이라고 이해하시면 됩니다.
  그럼 본격적으로 RDBMS의 성능에 대해 얘기해보겠습니다. 

  필자가 생각하는 RDBMS에서의 성능 최적화 전략은 다음과 같습니다.

- RDBMS 성능 최적화 전략 -

1. 파라미터 튜닝 (Parameter tuning)
2. 조인 전략 (Join strategy)
3. 인덱스 전략 (Index strategy)
4. 데이터 아키텍처 전략 (Data architecture strategy)
5. SQL 튜닝 (SQL tuning)

1. 파라미터 튜닝 (Parameter tuning)

  파라미터(또는 Config, 환경설정) 튜닝을 제일 먼저 언급하는 이유는 시스템 구축 초기에 완료해야 할 작업이기 때문입니다.

  모든 솔루션은 설정 파일을 이용해 각 사이트 환경에 적합한 파라미터 값을 설정할 수 있는 기능을 제공합니다. (예를 들어 오라클은 init.ora, MySQL는 my.cnf 파일이 이에 해당됩니다.) 일반적인 파라미터 항목은 버퍼/캐쉬 크기, 로그 기록 위치, 블럭(페이지) 크기, 부가 기능 사용여부 또는 설정 값 등이 있을 수 있습니다.

  여러 파라미터 중에서 초기 데이터베이스 생성 시에만 설정이 가능한 항목들이 있고, 데이터베이스 생성 후에도 변경이 가능한 항목들도 존재합니다. 그렇다고 추후에 변경 가능한 항목들을 마음껏 변경할 수 있다는 뜻은 아닙니다. 특히, 운영 중인 시스템의 설정 값은 더욱 더 심사숙고 해야 합니다.
  검증되지 않은 설정 값 변경으로 기존에 잘 수행되던 쿼리의 실행계획이 변경되어 악성 쿼리가 난무하는 시스템을 만나게 될 수 도 있기 때문입니다.

  그러면 최적화를 위해 파라미터를 어떻게 변경해야 할까요?

  최선의 방법은 reference를 참조를 해서 연구하고 테스트 후 적용하는겁니다. 각 DBMS 솔루션은 사용자들을 위한 커뮤니티나 서포트 페이지를 통해서 고객 문의를 지원해주고 있습니다. 그곳에서 빈번하게 수정해야 된다고 언급되는 파라미터가 있다면 테스트 후 적용해 볼 가치가 높은 편이라고 볼 수 있습니다.

  물론 설정 값을 변경하는 것은 그저 타이핑을 하면 되는 쉬운 것이지만, 그 값을 어떤 항목으로 채워야 하는지 알아내는 것은 쉬운 일이 아닙니다. 이럴 때는 솔루션 고객센터나 데이터베이스 컨설팅 업체의 도움을 받아야 할 것입니다. 이것도 비용상의 문제로 어렵다면 DBMS 선택 시 안정화된 버전을 설치하는 것이 쉬운 방법이 될 것입니다.
  파라미터를 변경하는 주된 이유는 아직 안정화가 안된 버전에서 신규 기능에 의해 잘 알려지지 않은 버그를 해결하기 위한 경우가 많기 때문입니다.

2. 조인 전략 (Join strategy)

  일반적으로 우리가 알고 있는 조인 종류(Type)는 다음과 같습니다.

  위 조인 종류는 두 테이블을 조인 시 한 쪽에만 존재하는 데이터를 결과 데이터 셋에 포함시킬지 여부에 따라 조인을 구분한다고 생각하시면 됩니다. 자세한 내용은 아래 포스팅을 참고 바랍니다.

  그리고 조인 방식(Method)에 의해서도 조인을 구분할 수 있습니다.

  데이터베이스 내부에서 각각의 Row를 매핑 시 사용되는 방식에 의해 조인을 구분할 수 있는겁니다.
  쿼리 실행 시 옵티마이저가 다양한 조인 방식 중에서 어떤 방식을 채택하느냐에 따라 쿼리 성능이 크게 달라질 수 있게 됩니다. 일반적으로 데이터 건수가 적은 경우에는 Nested loops join을 사용하고, 데이터 건수가 많은 경우에는 Hash join을 사용합니다. 그리고 조인 대상 테이블 중에 데이터 건수가 적은 테이블부터 액세스를 해야 더 좋은 성능을 보장할 수 있습니다.

  자세한 조인 방식에 대한 설명은 아래 포스팅을 참고 바랍니다.

3. 인덱스 전략 (Index strategy)

  인덱스는 테이블의 데이터를 빠르게 액세스하기 위한 목적으로 만들어진 객체입니다.
  흔히 비유하기를 책 분류기호를 이용해 서가에 순서대로 정리되어 있는 책을 찾는 방법과 비슷하다고 설명합니다.

  인덱스에서는 책의 분류기호 대신에 테이블의 컬럼 값을 사용해 데이터 위치를 검색합니다. 컬럼의 값을 B-Tree 구조체에 입력하고, 루트노드부터 리프노드까지 짧은 액세스 단계를 거쳐 검색 대상 컬럼의 값이 위치한 로우의 위치(Rowid)를 빠르게 찾을 수 있게 됩니다. ( 이 포스팅의 주 목적이 B-Tree가 아닌 관계로 상세한 입력/검색 절차는 생략하겠습니다. )

  데이터를 빠르게 검색할 수 있는 인덱스, 그렇다고 테이블 하나에 다수의 인덱스를 생성하게 되면 테이블에 데이터 입력/변경/삭제 시 인덱스의 변경 작업량이 증가하여 성능 부하의 원인이 될 수 있습니다.

  인덱스를 무조건 많이 생성하는 것보다, 인덱스의 활용도가 높아지도록 조인이나 필터링 조건으로 많이 사용되는 컬럼(흔히 액세스 패턴을 확인한다고 합니다.) 중심으로 인덱스를 생성하는 것이 중요합니다.
  즉, 자주 사용되는 쿼리문을 분석한 액세스 패턴 결과를 토대로 인덱스 컬럼을 선정해야 합니다.

  인덱스 컬럼을 선정할 때도 단일 컬럼을 선정하는 것보다는 복합 컬럼을 선정하는 것이 성능상 유리합니다. 일반적인 조회 쿼리를 보면 조인이나 필터링 조건에 하나의 컬럼만 사용되는 경우는 극히 드물기 때문입니다. 최대한 인덱스에서 조건에 맞지 않는 로우를 필터링해야만 인덱스에서 rowid를 이용해 테이블에 액세스(Random access)하는 횟수를 줄일 수 있기 때문입니다. 인덱스를 거쳐 테이블의 블럭을 읽은 횟수가 전체 테이블의 블럭 숫자보다 많다면 Full table scan이 더 유리할 것입니다.

  그리고 인덱스 컬럼의 순서도 중요합니다. 선행 컬럼의 분포도가 좋다면 인덱스 블록 액세스를 최소화 할 수 있기 때문입니다. 선행 컬럼의 분포도가 좋지 않아서 검색 조건의 값이 여러 리프 노드 블럭에 존재하는 것보다는, 좋은 분포도로 인해 적은 리프 노드 블록에 존재하는 것이 인덱스의 블럭 액세스를 줄이는 방법입니다.

  이 외에도 인덱스 스캔 방법 선택도 성능을 위해 고려해야할 사항입니다.
  오라클에서 제공하는 인덱스 스캔 방법은 아래와 같습니다.

   위 인덱스 스캔 방법에 대해서는 추후에 별도로 포스팅하도록 하겠습니다.

4. 데이터 아키텍처 전략 (Data architecture strategy)

  우선 데이터 아키텍처의 정의부터 살펴보겠습니다.

[ dbguide.net ]
데이터아키텍처란 기업의 모든 업무를 데이터 측면에서 처음부터 끝까지 체계화하는 것이다.
데이터아키텍처 전문가란 효과적인 데이터아키텍처 구축을 위해 전사아키텍처와 데이터품질관리에 대한 지식을 바탕으로 데이터 요건분석, 데이터 표준화, 데이터 모델링, 데이터베이스 설계와 이용 등의 직무를 수행하는 실무자를 말한다.

[ wikipedia.org ]
Data architecture is composed of models, policies, rules or standards that govern which data is collected, and how it is stored, arranged, integrated, and put to use in data systems and in organizations.

  지극히 학문적인 정의라서 쉽게 가슴에 와닿지는 않을 수도 있습니다.
  그럼 위 정의를 기반으로 데이터 아키텍처에서 고려해야 할 항목들을 정리해 보도록 하겠습니다. 제가 지금 생각나는 대로 기술하는 것이기 때문에 아래 항목들이 100% 정답이라고 단정 지을 수는 없습니다.

데이터 요구분석
데이터베이스 선택 (ex : RDBMS, NoSQL, 그래프 DB, 임베디드DB, etc.)
서버/클러스터 구성 (ex: 단일 서버, HA구성, 클러스터 구성, RAC, Max scale, etc.)
파일 시스템 선택 (Oracle : OS Filesystem vs ASM vs Raw device)
데이터베이스 저장 엔진 선택 (MariaDB : Innodb, Aria, XtraDB, etc.)
데이터 표준화 (단어/용어/도메인)
데이터 모델링 (개념/논리/물리, 주제영역)
데이터 간 관계 정의
데이터 인터페이스 정의
데이터 수집 방법 및 범위 (ex : App. 생성 데이터, 외부 연계, 통계 데이터, 시스템 운영 데이터, etc.)
오브젝트 사용 정책 (ex: 트리거 사용 여부, PL/SQL 사용 여부, FK 설정 여부, 최대 인덱스 생성 허용 갯수, etc.)
명명 규칙 (ex: 테이블명, 테이블스페이스명, 인덱스명, 사용자계정명, etc.)
데이터베이스 사용자 계정/권한 정책
데이터베이스 접근 제어 정책
데이터 보관 주기
데이터 로그 관리
데이터 백업 전략
...

  위 항목들 외에도 제가 지금 생각하지 못한 데이터 아키텍처 항목들이 더 많을 수도 있습니다.

  그리고 위 항목들은 하나의 데이터베이스를 사용한다는 전제하에 List-up을 한 것입니다. 요즘에는 시스템 규모가 커지고 있기 때문에 한 시스템에서도 다양한 데이터베이스를 사용하도록 설계하는 곳도 많습니다.

  예를 들어, MariaDB/MySQL의 replication기능으로 데이터를 여러 서버에 복제 해두고 특정 사용자는 특정 서버의 데이터베이스를 액세스 하도록 구성하여 부하가 분산되게 구성할 수 있습니다. 이 때, 사용자가 어떤 서버의 데이터베이스를 사용해야 하는지 알려주는 역할은 동시 접근성이 좋은 오라클이 할 수 있는것입니다.

  사용자가 처음 시스템에 접속하면 오라클한테 '어느 서버를 이용하면 되나요?'라고 문의하고, 오라클이 'N번 서버의 데이터베이스를 이용하세요.'라고 응답하면, 그 후로 사용자는 계속 N번 서버의 데이터베이스와 통신을 하게 되는 겁니다.

  위의 데이터베이스 구성 방법은 단순한 예시일 뿐입니다. 요즘 MariaDB에서도 MaxScale으로 부하 분산 기능이 강화되었으니 MariaDB만으로도 안정적으로 운영이 가능 할 것입니다.

  아무튼, 데이터 아키텍처 설계라는게 단순히 데이터 모델만 그려내는 작업만이 아니라 더 포괄적으로 정책이나 성능 향상을 위한 방법까지도 정의해야 합니다.

  본론으로 들어가서, 데이터 아키텍처의 성능 향상 방법, 구체적으로 물리 데이터 모델의 성능 향상 방법에 대해 논의하고자 합니다.

  필자가 생각하는 물리 데이터 모델의 성능 향상 방법(의 일부)은 아래와 같습니다.

4-1) Partitioning

  데이터베이스에서 파티션은 크기가 큰 테이블이나 인덱스를 관리하기 용이하도록 작은 단위로 쪼개서 저장하는 단위입니다.

  한 회사에서 10년간의 거래 내역을 엑셀로 관리하고 있는데, 주로 거래 내역을 입력하고 확인하는건 최근 한달간의 데이터라고 가정해보겠습니다.
  만약, 전체 거래 내역이 하나의 엑셀 파일에 저장되어 있다면 어떻게 될까요? 엑셀 파일이 너무 커서 원하는 데이터를 찾는 것도 어려울 것이고, 컴퓨터의 자원을 많이 소비하여 처리 속도도 느려질 것입니다.
  이 때, 엑셀 파일을 월 단위로 나눠서 저장하게 되면 어떻게 될까요? 전체 테이터를 저장한 파일에 비해 경량의 엑셀 파일이 될 것입니다. 물론 데이터를 찾고 입력하는 속도도 빨라질 것이고요.

  위와 같이 데이터베이스에서 기준에 의해 데이터를 분리하여 저장/관리하는 것이 파티션의 역할입니다.
  기준에 의해 데이터가 분리되어 있기 때문에 검색 조건에 기준 컬럼 조건이 포함되어 있다면 전체 테이블 데이터가 아니라 조건에 맞는 파티션 데이터만 액세스(Partition pruning이라고 합니다.)하게 되므로 블록 액세스가 감소됩니다.

  아래 예제를 살펴보겠습니다.

  테이블에서 YYYYMM 컬럼 값이 '201903'인 데이터를 조회하는 쿼리를 실행했습니다. 일반 테이블에서 YYYYMM='201903'인 값을 찾기 위해서는 전체 테이블을 탐색해야 합니다. 
  만약, YYYYMM 컬럼 값을 기준으로 파티션이 된 테이블에서 YYYYMM='201903'인 값을 찾는다면 YYYYMM='201903'에 해당하는 파티션만 탐색하게되므로 블록 액세스가 감소됩니다.

  데이터베이스에서 성능 향상을 위한 기본이 블록 액세스를 줄이는 것이니, 파티션을 사용하면 데이터베이스의 성능이 향상 될 수 있게됩니다.

  그리고 오라클에서 테이블이나 인덱스를 파티셔닝하게되면 파티션 별로 세그먼트라는 논리적 저장 구조가 분리되어 관리되고, 데이터 파일도 분리(파티션 별 테이블스페이스 설정 작업이 별도로 필요합니다.)되게 됩니다. 물리적으로도 데이터 I/O에 대한 부하가 감소되는 겁니다.

  그러나 파티셔닝으로 인해 성능 향상이 기대 된다고 모든 테이블을 파티션 테이블로 생성해야 하는 것은 아닙니다.
  크기가 작은 테이블을 파티셔닝하게 되면 관리 포인트가 증가하게 되고, 여러 세그먼트에 분산되어 저장되기 때문에 테이블의 전체 블럭 갯수가 증가할 수 있게 됩니다. 데이터가 대용량이고 기준 조건(파티션 키 컬럼)으로 조회가 빈번한 테이블만 파티션 대상이 됩니다.

  만약 파티션 대상 테이블을 선정했다면 테이블 별 파티션 전략도 수립해야 합니다.
  아래 목록은 오라클에서 지원하는 파티션의 종류입니다. 데이터 요건과 테이블 액세스 패턴을 고려하여 각 테이블에 적합한 파티션 전략을 선택하면 됩니다.

  인덱스도 파티셔닝에 따라 아래와 같이 구분되어 질 수 있습니다.

  인덱스 파티션도 테이블 파티션과 마찬가지로 데이터 요건과 테이블 액세스 패턴을 고려하여 각 테이블에 적합한 파티션 전략을 선택해야 합니다. 
  일반적으로 Local Partitioned Index나 Nonpartitioned Index의 생성 비율이 높은 편이며, Local Nonprefixed Index는 실무에서는 거의 볼 수 없는 구조입니다.

4-2) View와 UDF 사용 최소화 (At least view and UDF)

  View는 테이블과는 다르게 별도의 저장 공간이 존재하지 않는 객체입니다. 단지 View 생성 시 정의한 SQL문이 데이터베이스에 저장되어 있으며, 사용자가 뷰를 조회 할 때 마다 저장된 SQL문을 실행하여 결과를 보여주게 됩니다.

  View를 사용하게 되면 아래와 같은 장점이 있습니다.
  첫 번째, 정보 보안성을 강화할 수 있습니다. 예를 들어, 고객 테이블에서 주민등록번호 등과 같은 개인 정보는 일반 데이터베이스 사용자에게 공개할 수 없다는 보안 정책이 존재할 수 있습니다. 이런 경우 고객 테이블에서 주민등록번호 컬럼을 제외한 나머지 컬럼만 조회하는 View를 생성하고 일반 사용자는 View를 통해 주민등록번호가 제외된 고객 정보를 조회할 수 있도록 처리할 수 있습니다.
  두 번째, 쿼리 작성의 복잡도를 줄여줍니다. 아주 복잡한 수식이 사용된 쿼리문이나 길이가 긴 쿼리문을 View로 생성할 수 있습니다. 복잡한 쿼리문 대신에 View를 사용하게 되면 쿼리문이 간결해져서 가독성이 좋아집니다.

  물론, View의 단점도 존재합니다.
  첫 번째, 관리 포인트가 많아집니다. 너무 많은 View 생성을 남발하게되면 당연히 관리해야할 객체가 증가하게 되어 이 또한 관리 포인트가 됩니다. 더 큰 문제는 View에서 액세스하고 있는 테이블이 변경되면 View에도 영향이 있을 수 있다는 겁니다. View 쿼리문에 직접 명시된 컬럼이 변경 대상이 아니라면 큰 이슈는 없을 것입니다. 하지만, 'SELECT * FROM 테이블명'과 같이 전체 컬럼 조회를 뜻하는 *(Asterisk)를 사용한 경우에는 관련 쿼리문을 수정해야 하는 사태가 발생할수도 있습니다.
  두 번째, 성능 부하의 원인이 될 수 있습니다. View 조회 시 데이터 필터링을 위해 WHERE 조건을 사용 할 수 있습니다. 정상적인 경우라면 Optimizer가 View의 Where 조건절도 테이블의 필터링 조건으로 인식하여 인덱스 사용 등의 최적화된 실행 계획을 생성하게 될 것입니다. 하지만 View안의 쿼리가 복잡한 경우 Optimizer는 우선 View를 실행 한 후 마지막에 Where 조건을 필터링하여 결과를 출력할 수도 있습니다. 출력 결과는 1~2건인데 내부적으로는 Full table scan이 발생할 수도 있다는 겁니다.

  위와 같은 이유로 필자는 보안을 위한 View외의 무분별한 View 생성은 최소화해야 된다는 원칙을 가지고 있습니다.

  UDF(User Defined Function, PL/SQL)도 마찬가지입니다.
  복잡한 계산 수식을 절차적으로 해결하기 위해 PL/SQL을 사용하여 UDF을 생성하는 경우가 있습니다. 물론, UDF를 사용하면 쿼리문이 간결해지는 장점이 있습니다.

  하지만, UDF는 View처럼 성능 부하를 유발하는 폭탄이 될 수도 있습니다.
  SELECT절에 사용된 UDF의 호출 횟수는 데이터 결과 셋의 건수와 동일합니다. 데이터 결과 셋의 건수가 10만건이라면 UDF는 10만번 수행이 되게 됩니다. 수행 횟수가 증가하니 UDF내 쿼리의 실행 횟수도 증가하여 결국 과도한 블럭 액세스를 유발하게 됩니다.

  UDF처럼 SELECT절에서 사용되는 서브 쿼리(스칼라 서브 쿼리)의 사용 여부에 따른 블럭 읽기 횟수를 비교해보도록 하겠습니다.

  위의 두 쿼리는 PROD_SALE 테이블에서 SALE_DT 값이 '20200301'인 데이터를 추출하는 쿼리입니다. PROD_SALE 데이터 중 PROD_NO에 매핑되는 PROD_NM 값을 추출하기 위해 PROD 테이블을 액세스 합니다.
  왼쪽 튜닝 전의 쿼리에서는 스칼라 서브 쿼리를 이용해 PROD 테이블의 PROD_NM 컬럼 값을 추출합니다.
  오른쪽 튜닝 후의 쿼리에서는 PROD 테이블을 조인하여 PROD_NM 컬럼 값을 추출합니다.

  위 쿼리의 AUTOTRACE 실행 결과는 아래와 같습니다.

  왼쪽 스칼라 서브 쿼리를 사용한 쿼리의 consistent gets 값은 '169,858'입니다.
  오른쪽 조인을 사용한 쿼리의 consistent gets 값은 '44,835'입니다.
  스칼라 서브 쿼리 사용 시 블럭을 읽은 횟수가 약 4배정도 많은 것을 확인 할 수 있습니다.

  위 스칼라 서브 쿼리처럼 SELECT 절에 사용되는 FUNCTION과 PROCEDURE도 마찬가지입니다. 데이터의 출력 결과 건수만큼 실행되게 되어 블럭 읽기가 증가하게 됩니다. 만약, FUNCTION이나 PROCEDURE 내 쿼리에서 Full table scan이 발생한다면 데이터 건수만큼 Full table scan이 발생하게 됩니다. 심각한 성능 저하의 원인이 되는거죠.

   이 문제를 해결하는 방법은 위 스칼라 서브쿼리처럼 FUNCTION과 PROCEDURE을 제거입니다.
  구체적인 예제는 아래 포스트를 참고하시면 됩니다.

4-3) 데이터 모델 변경(Change data model)

  데이터 모델 설계가 중요한 이유 중의 하나는 설계가 완료된 이후에는 변경이 어렵다는 점입니다.

  건축 설계를 예를 들어보겠습니다. 높은 빌딩이 완공되어 완공식을 하는데, 건물주가 1층 로비의 천장이 너무 낮다는 의견을 내놓았습니다. 완공된 건물의 1층 로비 천장을 높이기 위해 2층 바닥을 뚫기라도 해야 한다는 건가요? 절대 불가능한 일인 것입니다.

  데이터 모델도 마찬가지입니다. 오픈된 시스템의 설계 오류를 발견하더라도 큰 구조의 변경은 쉽지 않은게 현실입니다. 더군다나 24시간 365일  서비스를 하는 시스템이라면 더더욱 힘든 일이죠.

  그래도 성능 이슈를 해결하기 위해 어쩔수 없이 데이터 모델을 변경을 선택해야 하는 경우도 발생합니다.

  집계성 테이블의 경우는 데이터 모델 관점에서는 데이터 중복에 해당되므로 설계 시 최대한 생성을 배제해야 할 대상입니다. 하지만, OLTP시스템에서도 월 판매 현황 같은 집계성 데이터를 자주 조회하는 경우도 존재합니다. 이런 요건이 있다면 데이터가 중복이더라도 데이터베이스 성능을 위해 집계 테이블을 생성하는 것을 고려할 수 있습니다.  집계 테이블을 생성하고 데이터베이스 사용량이 적은 새벽 시간에 배치를 수행하면 사용량이 많은 peek 시간에 데이터 집계 쿼리로 인한 부하를 피할 수 있게 됩니다.

  그리고 거래 내역 등 크기가 큰 테이블에 파티션이 적용되지 않았거나 파티션 키가 잘못 정의된 경우도 조심스럽게 모델 변경을 고려 할 수도 있습니다.
  이런 규모의 테이블은 시스템 내에서 활용도가 높을 수 있으므로, 단순한 테이블 변경으로 작업을 마무리 할 수는 없을 것입니다.
  테이블이 변경되면 테이블과 관련된 SQL의 모든 실행 계획이 변경될 가능성이 큽니다. 어제까지만해도 잘 수행되던 쿼리가 테이블 변경으로 순식간에 악성쿼리로 변경될 가능성이 있기 때문입니다.
  또한 기존의 데이터도 유지해야 하므로 이전 테이블에서 변경된 테이블로 데이터를 이관하는 작업도 필요합니다.

  따라서, 이러한 대용량의 테이블의 구조를 변경하기 위해서는 사전 작업이 필요합니다. 우선 임시 테이블에 데이터를 적재하여 쿼리 테스트를 진행해야 합니다. 기존의 쿼리를 테스트하여 실행 계획 상에 문제가 없는지 확인이 필요합니다. 쿼리상의 문제가 없음이 확인된 후에야 현행 데이터 이관까지 포함된 테이블 변경작업을 진행할 수 있습니다. 추가로 테이블 통계 정보를 생성하거나 기존 테이블에서 데이터를 이관하는 것도 잊지 말아야 합니다.

5. SQL 튜닝 (SQL tuning)

  운영중인 데이터베이스에서 성능 저하가 발생하였을 때, 성능 최적화를 위해 선택할 수 있는 가장 큰 대안은 SQL 튜닝일 것입니다. 위에서 언급한 것처럼 운영 중인 시스템의 모델을 변경하거나 인덱스를 추가/변경하는 작업은 영향도가 큰 작업이므로 실제로 적용할 수 있는 기회는 많지 않습니다.

  SQL을 튜닝하는 구체적인 방법은 아래와 같습니다.

1) Use hint

  옵티마이저가 항상 최적의 실행 계획을 제시하는 것은 아닙니다. 조인 대상 데이터 건수가 적은데 NL조인이 아닌 Hash 조인을 수행하거나, 엉뚱한 인덱스를 액세스하는 실행 계획이 수립 될 수도 있습니다.
  이런 경우 SQL에 힌트를 사용하여 쿼리의 실행계획을 변경할 수 있습니다.

  오라클에서 제공하는 주요 힌트는 아래와 같습니다.

2) Rewrite SQL

  누누히 얘기하지만 SQL의 성능 판단 기준은 실행 시간과 블럭 액세스 횟수입니다.

  위 기준으로 악성 쿼리를 검출해보면 데이터를 절차적으로 처리하도록 작성된 쿼리를 발견하기도 합니다. 동일한 테이블을 여러 번 액세스하거나, 데이터를 줄일 수 있는 부분을 놓치고 있는 경우가 있는거죠. 물론 개발 시간이 촉박한 상황에서는 절차적인 방식으로 쿼리를 작성하고 쿼리 수행 결과가 정상이면 바로 쿼리 작성을 완료하고 넘어가는 일이 비일비재하다는 것을 알고 있습니다. 이러한 상황적 한계로 SQL 튜너가 별도로 필요한 것이고요.

  아무튼 SQL 쿼리문은 집합적인 사고로 데이터 액세스를 최소화 할 수 있는 방법을 사용해 작성되어야 합니다. 절차적으로 작성된 쿼리문은 최대한 집합적 관점에서 다시 재작성이 필요합니다.

  SQL 튜닝 예제는 아래 포스트를 참고 바랍니다. 

3) Remove UDF

 위 4-2) View와 UDF 사용 최소화 (At least view and UDF)에서 언급했듯이 UDF는 쿼리문을 간결하게 만들어주지만, 잘못 사용되면 성능 부하의 큰 원인이 됩니다. UDF사용으로 성능 부하를 일으키는 SQL문은 당연히 해당 UDF를 제거하는 작업을 해야 합니다.

  구체적인 UDF 제거 방법은 위 "4-2) View와 UDF 사용 최소화 (At least view and UDF)"에서 링크한 포스트를 참고 바랍니다.

  감사합니다.