DataRailroad

# postgresql console 접속
$ psql postgres

# 사용자 확인
$ \du

# 사용자 생서
$ CREATE ROLE postgres WITH LOGIN PASSWORD 'postgres'

# 사용자 권한 부여
$ ALTER ROLE postgres CREATEDB;
$ ALTER ROLE postgres CREATEROLE;

# 새로 만든 유저로 접속
$ psql postgres -U postgres
# -> cmd 창을 보면 postgres=# 에서 postgres => 으로 변환 것을 확인 가능
# '#'은 superuser를 의미, '>' 는 superuser가 아니라는 의미

# 데이터 베이스 생성
$ CREATE DATABASE [데이터베이스명];

# 특정 유저에게 DB의 모든 권한 부여
$ GRANT ALL PRIVIELEGES ON DATABASE [데이터베이스명] TO [유저명];

# 데이터 베이스 리스트 보기
$ \list

# 특정 db로 연결
$ \connect [데이터베이스명];

# 계정 확인
select user, host from mysql.user;

# 권한 부여
GRANT ALL PRIVILEGES ON [DB 이름].* TO '[계정 이름]'@'[계정 host]';
FLUSH PRIVILEGES;

# 권한 확인
SHOW GRANTS FOR '[계정 이름]'@'[계정 host]';

*MySQL에서 bulk insert를 할 때 DB에서 받아 줄 수 있는 최대치를 구하기 위함

*MySQL은 최대 용량으로 DB가 받아 줄 수 있는 양을 결정(개인적으로 확인했을 때)

# 확인 방법
SHOW VARIABLES LIKE 'max_allowed_packet';


# 테스트
INSERT INTO cm_max VALUES ( REPEAT('A', 10000000), REPEAT('가', 10000000));
# 10000000 은 10MB 정도, 만약 max_allowed_packet가 10MB 이상이면 max_allowed_packet 을 줄여서 테스트
# -> SQL Error [1301] [HY000]: (conn=257936) Result of repeat() was larger than max_allowed_packet (16777216) - truncated

# max_allowed_packet 줄이는 방법
SET GLOBAL max_allowed_packet = 128 * 1024 * 1024;

sequence(자동 증가) 값 확인

환경

- DBeaver에서 query 실행.

PostgreSQL에서 setval() 함수를 통해서 현재 seq 값을 확인할 수 있다는 글이 많은데 ERROR가 발생했습니다.

last_value() 함수도 ERROR 발생합니다.

현재까지 성공한 seq 성공 방법은 nextval(’seq name’) 밖에 없습니다. 하지만 이 방법을 사용하면 seq 값이 1 증가하기 때문에 사용할 수 없습니다.

**DBeaver 을 사용하면 table 목록을 열면 Sequences 항목이 나오는데 sequences 를 확인 할 수 있습니다.**

성공

last_value 컬럼을 조회하면  sequence 값을 확인할 수 있었습니다.

select last_value from seq이름

EXISTS 사용 방법

EXISTS는 먼저 쿼리를 조회해서 하나의 레코드를 가지고 오고 나서 서브쿼리를 실행하여 서브쿼리에 대한 결과가 존재하는지를 확인해서 있다면 레코드를 출력해줍니다.

여기서 가장 중요한 것은 서브쿼리에 대한 결과가 NULL이여도 결과가 존재한 것이기 때문에 결과가 True가 되어 레코드를 출력하게 됩니다.

TEST

테스트 해볼 사이트: https://sqltest.net/

SQL Script

CREATE TABLE mysql_test_a ( 
id VARCHAR(30),
name VARCHAR(30) NOT NULL
); 


INSERT INTO `mysql_test_a` (`id`, `name`) 
VALUES ('1', 'abab');
INSERT INTO `mysql_test_a` (`id`, `name`) 
VALUES ('2', 'John');
INSERT INTO `mysql_test_a` (`id`, `name`) 
VALUES ('3', 'dd');
INSERT INTO `mysql_test_a` (`id`, `name`) 
VALUES ('4', 'bb');
INSERT INTO `mysql_test_a` (`id`, `name`) 
VALUES ('5', 'aa');
INSERT INTO `mysql_test_a` (`id`, `name`) 
VALUES ('2', 'omokoe');
INSERT INTO `mysql_test_a` (`id`, `name`) 
VALUES ('6', 'John');

SQL Query

SELECT * FROM mysql_test_a a
where exists (
    select 1 from mysql_test_a b
    where a.id = b.id
    and b.name = 'John'
);

결과

'2', 'John'
'2', 'omokoe'
'6', 'John'

왜 '2', 'omokoe' 가 결과 값에 포함되어 있을까?

앞서 말했드이 먼저 쿼리를 조회하고 나서 서브쿼리를 조회 하는데, 서브쿼리 b 전체 행을 조회합니다. b 테이블에 id 값이 2인데 name에 값이 John인 레코드가 있으면 return True가 되고 레코드에 출력되는 것입니다. 그래서 '6', 'John'도 출력되는 것입니다.

Clustered vs NonClustered (index 개념)

Database에서 index의 구조는 크게 Clustered 와 NonClustered로 나눠집니다.

Clustered

Clustered index는 데이터가 테이블에 물리적으로 저장되는 순서를 정의합니다. 즉, Clustered index는 특정 컬럼을 기준으로 데이터들을 정렬시켜줍니다. 테이블 데이터는 오직 한 가지의 방법으로만 정렬되기 때문에 테이블 당 하나의 Clustered index가 존재합니다.

단점으로는 새로운 데이터를 삽입할 때는 많은 비용이 소모되는 담점이 존재합니다.

주로 사용될 때:

• 테이블 데이터가 자주 업데이트 되지 않는 경우
• 항상 정렬 된 방식으로 데이터를 반환해야하는 경우
• 테이블은 정렬되어있기 때문에 ORDER BY 절을 활용해 모든 테이블 데이터를 스캔하지 않고 원하는 데이터를 조회할 경우
• 읽기 작업이 월등히 많은 경우(Read 속도가 빠릅니다.)

NonClustered

NonClustered index는 군집화 되어있지 않은 index를 말합니다. 즉, 테이블에 저장된 순서에 따라 데이터를 정렬하지 않습니다. 또한 NonClustered index는 별도의 장소에 저장되어 사용됩니다. 그러므로 별도의 공간(약 10%)이 필요합니다.

주로 사용될 때:

• where절이나 join 절과 같이 조건문을 활용하여 테이블을 필터링 하고자할 때
• 데이터가 자주 업데이트 될 때
• 특정 컬럼이 쿼리에서 자주사용 될 때

Procedure와 Function의 차이

• Procedure는 로직을 기술하여 해당 업무 처리를 직접 하는 용도로 많이 사용되고, Function은 로직을 도와주는 목적으로 사용됩니다.
• Procedure은 select, where문 등에서 사용이 불가능 하지만 Function은 사용 가능합니다.
• Procedure은 클라이언트에서 값을 건네받아 서버에서 작업을 한 뒤 클라이언트에게 전달하지만, Function은 클라이언트에서 값을 건네 받고 서버에서 필요한 값을 가져와서 클라이언트에서 작업을 하고 반환합니다.

Procedure이란?

일련의 쿼리를 하나의 함수로 실행할 수 있는 쿼리의 집합입니다. 예를 들어 네이버에서 상품을 구매하기 위해서 로그인, 상품 선택, 상품 구매 등 여러 프로세스를 한 번의 procedure에 담아 실행 할 수 있습니다.

잠정

1. 하나의 요청으로 여러 SQL문을 실행할 수 있습니다.
2. 서버에서 실행되기 때문에 속도가 빠릅니다.
3. 리턴값이 있을 수도, 없을 수도 있습니다.
4. 여러개의 리턴값을 보낼 수 있습니다.

단점

1. 재사용성이 좋지 않습니다.

코드

CREATE OR REPLACE PROCEDURE 프로시저 이름 ( 
	매개변수명1 [ IN || OUT || INOUT ] 데이터타입
	, 매개변수명2 [ IN || OUT || INOUT ] 데이터타입 ... 
) IS||AS 변수, 상수 등 선언 ( 선언부 ) 
BEGIN 실행 문장 ( 실행부 ) 
EXCEPTION 문장 //필수아님 
END ;

호출 방법

CALL 프로시저명(IN파라미터명, @OUT파라미터명)
EXECUTE 프로시저명(IN파라미터명, @OUT파라미터명)

Function이란?

각 프로세스를 수행하기 위해 필요한 기능들입니다.

Function은 리턴값이 필수입니다.

담점

1. 클라이언트에서 실행되기 때문에 프로시저보다는 느리다

코드

CREATE OR REPLACE FUNCTION 함수 이름
     ( 매개변수명1 매개변수1타입,
       매개변수명2 매개변수2타입 ... )
  RETURN 데이터타입
  IS||AS
         변수, 상수 등 선언 ( 선언부 )
  BEGIN
         실행 문장 ( 실행부 )
         RETURN 반환값    //필수
         EXCEPTION 문장   //필수아님
  END ;

호출 방법

SELECT 함수명(IN 파라미터명) FROM DUAL;

참조

https://velog.io/@devjooj/Mysql-Function과-Procedure-차이

https://fomaios.tistory.com/entry/Oracle-함수Function와-프로시저Procedure-차이

Join 속도 개선

Join 속도 개선 전에 먼저 현재 작성한 쿼리가 어떤지 파악해야합니다.

Mysql기반 EXPLAIN을 통해 쿼리에 대해 분석해보겠습니다.

** 추후 다른 DBMS에서 확인하는 방법을 추가 업데이트 하도록 하겠습니다.

EXPLAIN 사용방법

EXPLAIN
SELECT * FROM 테이블명 WHERE 조건

결과

type 설명

• ALL, eq_ref: 조인시 기본 키나 고유키를 사용하여 하나의 값으로 접근(최대 1행만을 정확하게 패치).
• ref: 여러 개의 행을 패치할 가능성이 있는 접근.

컬럼 extra는 옵티마이저가 동작에 대한 힌트입니다.

extra 설명

EXPLAIN을 통해 분석하여 검색 범위 및 옵티마이저 동작에 문제가 없는데도 속도가 느리다면 아래 사항에 대해서 검토 해보면 좋습니다.

- left outer join 등 보다는 inner join 속도가 빠릅니다.

- 테이블에서 행의 순서를 가져올 때 분리해서 가져오는것도 생각해봐야합니다.(여러 테이블과 조인한 상태에서 테이블에 행을 가져오는건 속도 저하를 유발합니다.)

- 스칼라 서브 쿼리는 지양해야합니다.(실무에서 쿼리짜는 도중 스칼라 서브 쿼리를 사용했을 때 속도 차이가 많이 났습니다.)