기본 설정파일 그대로 대개 사용하지만 기본설정에서는 몇 가지 문제되는 부분이 있다고 합니다. 검색으로 찾아서 할 수도 있지만 아래 사이트에 필수 값만 입력하면 max connection, 버터 등이 나옵니다. 

(간편, 유용)

PgTune: http://pgtune.leopard.in.ua/


여기서는 WITH ... AS 문을 사용하여 SELECT 결과가 없을 시 INSERT하는 쿼리, 로우가 1개 이상일 경우 UPDATE를 하고 그렇지 않으면 INSERT를 하는 쿼리 등을 설명합니다. 그리고  INSERT INTO .... ON CONFLICT .. DO UPDATE SET .. 문을 사용하여 보다 간편하게 로우가 없으면 INSERT, 존재하면 UPDATE를 하는 기능을 설명합니다.

WITH ... AS

여기서는 WITH ... AS 문에 대해 설명하기 보다 이를 사용해 다른 데이터베이스에 존재하는 upsert기능이나 select 결과가 없으면 insert를 하는 쿼리 등을 만들어 설명하겠습니다.

upsert란 로우의 갯수가 1개 이상일 시, 사용자가 명시한 쿼리대로 레코드의 값을 update하고 로우가 하나도 존재하지 않으면 insert를 하게하는 기능입니다. 

WITH ... AS 는 PostgreSQL은 9.1 이상부터 해당 기능을 지원합니다.

WITH 사용자명 AS (update [테이블명] SET [업데이트할 컬럼] = [업데이트 값] RETURNING *) INSERT INTO [테이블명] (컬럼1, 컬럼2,...) SELECT [삽입할 값] WHERE NOT EXISTS (SELECT * FROM 앞에서 지정한 사용자명)

-- 1. update할 데이터가 존재하지 않으면 insert, 존재하면 update하는 예시
with upsert as (update test1 set id =40 returning *) insert into test1 (id) select 1 WHERE NOT EXISTS (SELECT * FROM upsert)
-- test1테이블의 로우 갯수가 0일 경우, id 컬럼에 1을 insert. 로우 갯수가 0이 아니면 id 컬럼의 값을 40으로 변경
 
-- 응용
with upsert as (update test1 set id =40 returning *) insert into test1 (id) select (SELECT PK FROM test where pk =1) as a WHERE NOT EXISTS (SELECT * FROM upsert) -- select 절에 subquery 사용가능 
 
-- 2. select 결과가 존재하지 않으면 insert 하는 예시
with selected as (select id from test1) insert into test1 (id) select 10 where not exists (select * from selected)
-- test1 의 select 결과가 존재하지 않으면 test1 테이블의 id 컬럼에 10인 로우 insert
 
-- 응용
with selected as (select id from test1), inserted as (insert into test1 (id) select 10 where not exists (select * from selected) returning id)
select id
from selected
union all
select id
from inserted 
-- test1의 select 결과가 존재하지 않으면 insert를 한 후 id를 반환. union all을 하는 부분을 생략하면 insert가 동작할 시, id에 insert한 값이 제대로 return되는 반면 select 결과가 존재해 insert가 되지 않으면 id 값이 return 되지 않음. 따라서 select와 insert 두 서브쿼리를 담고있는 selected 와 inserted를 union하여 id값 return


이와 같이 with ... as를 사용하여 사용자가 필요한 것들을 만들어 낼 수 있습니다.

INSERT INTO .... ON CONFLICT .. DO UPDATE SET ..

위의 upset과는 약간 다르게 로우의 갯수로 update할 지, insert할 지 판단하는 것이 아닌 사용자가 명시한 컬럼으로 중복체크를 한 다음 update 또는 insert를 결정합니다. PostgreSQL에는 9.5버전부터 이 기능을 지원합니다. (9.5 아래버전에는 지원을 하지않는걸로..)

INSERT INTO 테이블 VALUES(값,값1,...,값n) ON CONFLICT (중복체크할 컬럼) DO UPDATE SET 업데이트할 컬럼 = 업데이트할 값
 
-- 예시
insert into test (pk, id ,name) values(1,1,1) on conflict (pk) DO update set id = 10

위 예시를 설명하면 test라는 테이블의 pk, id, name 컬럼에 1이란 값을 각각 insert하려 합니다. 이때 pk값이 중복되면 id컬럼의 값을 10으로 업데이트합니다.


위와 같은 옵션들은 코드를 작성할 때 유용하게 사용할 수 있습니다. 



SELECT datname FROM pg_database -- 전체 데이터베이스 조회
SELECT datname FROM pg_database WHERE datistemplate = false -- 사용자가 생성한 데이터베이스만 조회
select nspname from pg_catalog.pg_namespace -- 현재 db의 전체 스키마 조회
select tablename from pg_tables -- 전체 테이블 조회



char_length  # 문자열의 문자 수
bit_length   # 문자열의 비트 수
octet_length # 문자열의 바이트 수
 
# 예시
 
select char_length('abcd')  # 4
select bit_length('1234')   # 32
select octet_length('1234') # 4


클러스터란?

디스크로부터 데이터를 읽어오는 시간을 줄이기 위해서 조인이나 자주 사용되는 테이블의 데이터를 디스크의 같은 위치에 저장시키는 방법 입니다.

클러스터의 장점

그룹된 컬럼 데이터 행들이 같은 데이터 Block에 저장되기 때문에 디스크 I/O를 줄여 줍니다.

클러스터된 테이블 사이에 조인이 발생할 경우 그 처리 시간이 단축 됩니다.

클러스터 키 열을 공유하여 한번만 저장하므로 저장 영역의 사용을 줄입니다. 

테이블 클러스터링의 특징

클러스터는 데이터 조회 성능을 향상 시키지만 데이터 저장, 수정, 삭제 또는 한 테이블 전체 Scan의 성능을 감소 시킵니다 

클러스터 하기 좋은 테이블

주로 조회가 자주 발생하고 수정이 거의 발생하지 않는 테이블

컬럼안의 많은 중복 데이터를 가지는 테이블

자주 Join되는 테이블 

클러스터 Key가 되기 좋은 컬럼

데이터 값의 범위가 큰 컬럼

테이블 간의 조인에 사용되는 컬럼 

클러스터 key가 되기 나쁜 컬럼

특정 데이터 값이 적은 컬럼

자주 데이터 수정이 발생하는 컬럼

LONG, LONG RAW 컬럼은 포함할 수 없습니다.

클러스터 인덱스와 논클러스터 인덱스

표에서 비교하는 내용들은 아래에서 자세히 설명되어 있습니다.

비교 

 클러스터 인덱스

논클러스터 인덱스

 차이

 물리적으로 행을 재배열

 물리적으로 재배열 하지 않음

 크기

 인덱스 페이지 용량이 넌 클러스터드 인덱스 페이지 용량보다 작다.

 클러스터드 인덱스 페이지 용량보다 큼

 선택도

 30% 이내에서 사용해야 좋은 선택도

 3% 이내에서 사용해야 좋은 선택도

 최대 갯수

 테이블당 1개

 테이블당 249개 

PRIMARY KEY 설정 시, 해당 컬럼은 자동적으로 클러스터 인덱스 생성논클러스터 인덱스를 명시해야 생성 (인덱스페이지를 따로 만듬 - 로그파일에 저장)


point query는 성능발휘되지만 range query는 장담 못함

차이

클러스터 인덱스는 물리적으로 행을 재배열합니다. 이 말 뜻의 의미를 이해하기 위해서 테스트 테이블을 생성한 후 데이터를 저장해 보았습니다.

아래와 같이 LOG_DATE, MEDIA_ID 두 개의 복합키는 자동적으로 클러스터 인덱스로 생성이 됩니다.

CREATE TABLE TBL_CLUSTERED_TEST (
  LOG_DATE CHAR(8) NOT NULL,
  MEDIA_ID CHAR(1) NOT NULL,  
  PROCEEDS DOUBLE DEFAULT NULL,
  PRIMARY KEY (LOG_DATE,MEDIA_ID)
);


다음  5개의 테스트 데이터를 저장합니다. 첫 번째 저장하는 데이터의 날짜는 6월 18일이고, 마지막에 저장되는 데이터의 날짜는 6월 13일입니다.

INSERT INTO TBL_CLUSTERED_TEST (LOG_DATE, MEDIA_ID, PROCEEDS) VALUES ('20130618', 'A', 1000);
INSERT INTO TBL_CLUSTERED_TEST (LOG_DATE, MEDIA_ID, PROCEEDS) VALUES ('20130619', 'A', 1000);
INSERT INTO TBL_CLUSTERED_TEST (LOG_DATE, MEDIA_ID, PROCEEDS) VALUES ('20130619', 'C', 2000);
INSERT INTO TBL_CLUSTERED_TEST (LOG_DATE, MEDIA_ID, PROCEEDS) VALUES ('20130619', 'B', 1000);
INSERT INTO TBL_CLUSTERED_TEST (LOG_DATE, MEDIA_ID, PROCEEDS) VALUES ('20130613', 'B', 3000);


해당 테이블을 select 하면 insert 되어 있는 순서대로 데이터가 누적되지 않습니다. LOG_DATE, MEDIA_ID는 클러스터드 인덱스로 생성이 되어 있기 때문에 물리적으로 LOG_DATE를 정렬한 후 MEDIA_ID를 정렬하게 됩니다. 물리적으로 정렬을 한다는 말은 실제 DB의 데이터 파일에 정렬이 되어 있는 상태로 디스크에 저장이 된다는 것입니다.

 

테이블 조회를 해보면 아래와 같이 데이터가 정렬되어 있는 것을 확인할 수 있습니다. (6월 13일 데이터가 가장 위에 있음)


select * from tbl_clustered_test;
 
| LOG_DATE | MEDIA_ID | PROCEEDS |
+----------+----------+----------+
| 20130613 | B        |     3000 |
| 20130618 | A        |     1000 |
| 20130619 | A        |     1000 |
| 20130619 | B        |     1000 |
| 20130619 | C        |     2000 |


논 클러스터 인덱스는 일반적으로 SELECT문 성능 향상을 위해서 논 클러스터드 인덱스를 생성하여 사용하곤 합니다. 허나, 이 인덱스는 클러스터 인덱스와는 다르게 물리적으로 데이터가 정렬되어 저장되지 않습니다.

크기

논 클러스터 인덱스는 별도의 공간에 인덱스 테이블을 생성하여 데이터를 정렬합니다. 다음의 그림은 논 클러스터 인덱스에서 숫자 8을 어떻게 검색하는지에 대해서 보여줍니다.


다음의 그림은 클러스터 인덱스에서 숫자 8을 어떻게 검색하는 지에 대해서 보여줍니다.


클러스터 인덱스 이미 데이터가 정렬되어 있는 상태로 저장되기 때문에 leaf level의 인덱스 테이블이 필요하지 않습니다. 즉, 클러스터 인덱스는 논 클러스터 인덱스보다 DB 용량을 덜 차지합니다.

선택도

선택도는 테이블의 전체 데이터 집합 중에 특정 조건에 의해 데이터가 걸러지는 비율을 뜻합니다. 여기서 클러스터 인덱스는 테이블의 데이터가 물리적으로 정렬되어 저장되어 있기 때문에 부분 범위 처리에 활용하면 적은 I/O를 통해 원하는 데이터를 추출할 수 있습니다. 예를 들어 7, 8, 9의 데이터를 추출한다고 했을 때 클러스터 인덱스는 단 한 번의 I/O를 통해서 데이터를 추출할 수 있다. 왜냐하면 7, 8, 9는 3번 데이터 페이지에 모두 존재하기 때문입니다. (위 그림 참조)

 

논 클러스터 인덱스는 leaf level 인덱스 페이지에 테이블 데이터의 위치가 저장되어 있기 때문에 테이블 데이터로 직접 access가 가능합니다. 위의 예에서 설명한 것과 같이 논 클러스터 인덱스에서 7, 8, 9의 데이터를 추출하게 된다면 2번의 I/O가 발생합니다. 왜냐하면 7값은 2번 데이터 페이지에 존재하고 8, 9는 3번 데이터 페이지에 존재하기 때문입니다. 그래서 논 클러스터 인덱스는 선택도의 범위가 3% 이내일 때 효율적입니다.

최대 갯수

클러스터 인덱스는 물리적으로 정렬되어 있는 상태로 데이터가 저장되기 때문에 딱 1개만 생성 가능합니다. 만약 2개의 클러스터 인덱스가 생성된다면 데이터 정렬이 꼬이기 때문에 딱 한 개만 생성할 수 있는 것입니다. 이에 반해 넌 클러스터드 인덱스는 인덱스 테이블을 별도로 생성하여 데이터를 정렬하기에  충분히(249개) 생성할 수 있습니다.

용어

Table scan: 전체 검색

Index seek: 원하는 부분만 검색

Point query: 조회되는 데이터가 1~2개

select * from test where name = 'kim'


Range query: 조회되는 데이터가 다수

select * from test where date = '2015-01-01'


Covered query: 조회의 대상과 조회의 결과 컬럼이 동일한 상태. 인덱스 측면에서 가장 빠른 성능

select * from test where name = 'kim' and age = 20 -- Covered query 아님
select name from test where name = 'kim' and age = 20 -- Covered query


'DB' 카테고리의 다른 글

[DB]Connection Pool  (0) 2017.02.11
[SQL] WHERE절에서 IF문처럼 AND 사용  (0) 2017.02.02
[DB] 클러스터  (0) 2016.11.18
[DB] 무결성 제약조건  (0) 2016.11.18
[DB] 트랜잭션, REDO와 UNDO 개념  (0) 2016.11.18
[DB] DDL, DML, DCL 이란?  (1) 2016.11.18

무결성 제약조건이란?

테이블 생성 시에 Constraint(제약조건)를 사용해서 입력하는 자료에 대해서 제약, 즉 규칙을 정해줄 수 있습니다. 이때 정해진 제약에 따라서 데이터가 입력이 됩니다. 만약 제약에 배반된다면 자료 입력이 거부되면서 오류가 납니다.

네이버 지식백과에서는 데이터 무결성에 대해서 다음과 같이 서술하고 있습니다.

무결성 : 데이터 및 네트워크 보안에 있어서 정보가 인가된 사람에 의해서만 접근이나 변경이 가능한 성질
데이터 무결성 : 데이터를 인가하지 않은 방법으로 변경할 수 없도록 보호하는 성질


쉽게 말해서, 권한이 부여된 계정이나 사람만이 접근 가능하고, 정확하고 완전한 데이터들이 저장되어 있는 상태라고 생각하면 됩니다. 여기서 제약조건은 이러한 데이터 무결성을 보장함으로써 이상한 데이터가 저장되지 않도록 하는 기능을 제공합니다.

제약조건

데이터의 무결성을 위해서 데이터베이스에서는 5가지 제약조건(Constraint)를 제공하고 있습니다.

제약조건내용
NOT NULL '필수 입력 사항'을 의미
 UNIQUE 중복성 배제 즉, '유일한 값'으로 존재해야 함을 의미
 PRIMARY KEY(기본키) NOT NULL + UNIQUE로, '테이블에서 대표되는 컬럼'을 의미
 FOREIGN KEY(외래키) '참조하는 테이블에서 존재하는 값만 사용 가능'을 의미
 CHECK '주어진 조건에 해당하는 값만 입력 가능'을 의미

1. NOT NULL

NOT NULL은 INSERT 시, 즉 데이터 입력시에 누락이 되어서는 안되는 부분입니다. NULL값이 들어가게 되면 오류가 납니다.

2. UNIQUE

UNIQUE는 해당 테이블에 있어서 존재하는 값이 유일해야 합니다. 만일 INSERT 또는 UPDATE 시, 제약이 걸려있는 컬럼에 동일한 데이터가 존재한다면 오류가 납니다. 이때, NULL 값에 대해서는 UNIQUE 제약이 적용되지 않습니다.  즉, NULL 값은 데이터로 인식하지 않기때문에 해당 컬럼에 NULL 데이터행이 여러개 존재 가능합니다.

테이블을 만들때 제일 밑에 CONSTRAINTS 제약명 UNIQUE (컬럼2, 컬럼3) 과 같이 CONSTRAINTS의 이름을 지정이 가능합니다. UNIQUE 제약으로 들어가는 컬럼들은 그들의 조합이 유일해야 합니다. 각 컬럼의 데이터의 유일함은 의미가 없고, 조합이 유일해야 합니다. 예를 들면, a라는 컬럼과 b라는 컬럼에 각각 1,2 그리고 1,3 (a 컬럼의 1 값이 동일)의 데이터는 존재가 가능합니다. a 컬럼의 데이터가 같다고 하더라도 그 조합이 다르기 때문에 오류를 일으키지 않습니다.

3. PRIMARY KEY

PRIMARY KEY는 하나의 테이블에 있는 데이터들을 식별하기 위한 기준으로 인식되는 제약조건입니다. 한개의 테이블에 하나만 생성이 가능합니다. PRIMARY KEY는 NOT NULL + UNIQUE 의 속성을 가집니다. 즉, NULL값이 있으면 안되고 해당 컬럼의 데이터는 중복되지 않고 유일해야 합니다. UNIQUE에서와 같이 여러 컬럼들을 조합해서 지정할 수 있습니다.

4. FOREIGN KEY

FOREIGN KEY는 해당 컬럼에 참조하는 테이블로부터 존재하는 값들만 사용한다는 의미의 제약조건입니다. 만일 참조하는 테이블에 해당하는 값이 없을시에는 INSERT시나 UPDATE시에 오류가 나게 됩니다. 외래키는 여러개의 컬럼에 중복적으로 적용 가능합니다. 외래키로 두 테이블이 부모테이블과 자식테이블로 관계를 맺고 있을시에, 자식테이블이 참조하는 데이터는 부모 테이블에서 삭제가 불가능합니다. (FOREIGN KEY의 기본옵션일 때) FOREIGN KEY는 4가지 옵션을 가지고 있는데 이 옵션에 따라 부모테이블에서 삭제할 시, 자식 테이블에서 참조하는 데이터도 삭제가 되도록 만들 수도 있습니다.

# FOREIGN KEY 옵션

NO ACTION

데이터베이스 엔진에서는 오류가 발생하며 부모 테이블의 행에 대한 삭제 또는 업데이트 동작이 롤백됩니다.

CASCADE

부모 테이블에서 해당 행이 업데이트되거나 삭제될 때 참조 테이블에서도 해당 행이 업데이트 또는 삭제됩니다. timestamp 열이 외래 키 또는 참조되는 키의 일부인 경우에는 CASCADE를 지정할 수 없습니다. INSTEAD OF DELETE 트리거가 있는 테이블에는 ON DELETE CASCADE를 지정할 수 없습니다. INSTEAD OF UPDATE 트리거가 있는 테이블에 대해서는 ON UPDATE CASCADE를 지정할 수 없습니다.

SET NULL

부모 테이블에서 행을 업데이트하거나 삭제하면 해당 외래 키를 구성하는 모든 값이 NULL로 설정됩니다. 이 제약 조건을 실행하려면 외래 키 열이 Null을 허용해야 합니다. INSTEAD OF UPDATE 트리거가 있는 테이블에 대해서는 지정할 수 없습니다.

SET DEFAULT

부모 테이블에서 해당 행을 업데이트하거나 삭제하면 외래 키를 구성하는 모든 값이 기본값(Default로 지정한 값)으로 설정됩니다. 이 제약 조건을 실행하려면 모든 외래 키 열에 기본 정의가 있어야 합니다. 열이 Null을 허용하고 명시적 기본값이 설정되어 있지 않은 경우 NULL은 해당 열의 암시적 기본값이 됩니다. INSTEAD OF UPDATE 트리거가 있는 테이블에 대해서는 지정할 수 없습니다.

RESTRICT

자식테이블에 데이터가 남아있는 경우 부모 테이블의 데이터는 수정 또는 삭제를 할 수 없습니다.


CASCADE, SET NULL, SET DEFAULT 및 NO ACTION은 서로 참조 관계를 가진 테이블에서 결합될 수 있습니다. 데이터베이스 엔진 이 NO ACTION을 발견하면 관련된 CASCADE, SET NULL 및 SET DEFAULT 동작을 멈추고 롤백합니다. DELETE 문으로 CASCADE, SET NULL, SET DEFAULT 및 NO ACTION 동작을 결합하면 데이터베이스 엔진 이 NO ACTION을 확인하기 전에 모든 CASCADE, SET NULL 및 SET DEFAULT 동작을 적용합니다.


외래키를 사용한 테이블을 삭제할 때는 제약조건에 어긋나지 않게 순서대로 삭제해야 삭제가 됩니다.
참조하는 레코드 및 테이블 (자식 레코드 및 테이블)-> 참조당하는 레코드 및 테이블 (부모 레코드 및 테이블)

5. CHECK

CHECK는 조건에 부합하는 데이터만 입력이 가능하도록 하는 제약조건입니다. 조건에는 기본연산자나 비교연산자, IN, NOT IN 등등이 사용 가능합니다.

'DB' 카테고리의 다른 글

[SQL] WHERE절에서 IF문처럼 AND 사용  (0) 2017.02.02
[DB] 클러스터  (0) 2016.11.18
[DB] 무결성 제약조건  (0) 2016.11.18
[DB] 트랜잭션, REDO와 UNDO 개념  (0) 2016.11.18
[DB] DDL, DML, DCL 이란?  (1) 2016.11.18
[SQL] SELECT 결과 UPDATE  (0) 2016.11.18

+ Random Posts