DataBase - Plus

 

 

이번 블로그에서는 그동안 정리하지 못한 추가적인 내용에 대해서 정리해보고자 한다.

 

 

1. DDL vs DML vs DCL

모두 기적으로 DBMS에서 사용되는 SQL언어의 종류로 SQL을 위의 세가지의 범주로 구분한다고 이해하면 쉽다.

 

 

 

DDL (Data Definition Language)

 

- 데이터 베이스 구조를 정의하거나 변경하는데 사용된다. 구조는 테이블, 스키마, 제약 조건, 인덱스 등을 의미한다. 

- 객체를 관리 (테이블, 뷰, 인덱스 등)

- 스키마를 정의하고 조작하는데 사용된다.

 

주요 명령어

• CREATE : 데이터베이스 객체 생성
• ALTER : 데이터 베이스 객체의 구조를 변경 
• DROP : 데이터 베이스 객체를 삭제
• TRUNCATE : 테이블 내의 모든 데이터 삭제
• RENAME : 데이터 베이스 객체의 이름 변경

스키마

- 데이터베이스 생성시 생성, 객체의 구조, 관계, 제약조건 등을 정의하는 것

- 필드 이름, 데이터 유형, 길이, 기본키와 같은 제약조건, 다른 테이블과의 관계, 무결성 유지, 보안, 설계 문서화 등의 역할을 한다.

- 모든 테이블을 관리하는 하나의 패키지라고 생각하면 쉽다.

 

 

DML (Data Manipulation Language)

- 데이터를 검색, 삽입, 삭제, 수정하는데 사용된다. 

- 데이터 베이스의 내용을 조작하는데 사용

 

주요 명령어

• SELECT : 데이터를 검색, 조회
• INSERT : 데이터를 삽입
• UPDATE : 데이터를 수정
• DELETE : 데이터를 삭제

 

 

DCL (Data Control Language)

- 데이터 베이스의 접근 권한을 제어하는데 사용한다.

- 주로 테이블에 대한 권한 등을 부여한다.

 

주요 명령어

• GRANT : 데이터 베이스에 객체에 대한 권한 부여
• REVOKE : 데이터 베이스에 객체에 대한 권한 취소

예시

GRANT SELECT, INSERT ON employees TO user1;

- user1에게 테이블 employees에 대한 조회 및 삽입 권한 부여

 

REVOKE SELECT ON employees FROM user1;

- user1이 employees 테이블에 갖고 있는 권한 취소

 

GRANT SELECT ON employees TO user1 WITH GRANT OPTION;

- 권한을 부여할 때 '권한을 다른 사람에게 부여할 수 있는 권한'도 부여

 

 

 

 

2. JOIN

 

 

배경

 

이번에는 조인에 대해 좀 더 자세히 정리해보고 예시와 함께 살펴보려고 한다.

현재, 진행중인 프로젝트가 있다. 여기에서 여러 테이블이 서로 얽혀있다.

총 테이블은 Reservation(가게 예약 정보) - Orders(주문정보) - OrdersMap(주문 음식 정보) 관계로 정의 되어있고 Orders는 Pay(결제 정보)도 외부 테이블로 연결되어 있다.

 

기본키-외래키는 아래와 같다

Reservation : 예약 아이디

Orders : 주문 아이디, 예약 아이디를 통해 Reservation과 연결

OrdersMap : 주문 아이디로 Orders와 연결

Pay : 주문 아이디로 Orders와 연결

 

이러한 경우 데이터를 조회시 문제가 발생한다.

특히, 유명한 N+1문제가 발생할 가능성이 크다. 

하지만, Lazy Loading 설정을 하면 N+1문제를 피할 수 있다. 

 

Lazy Loading : 조회시 해당 테이블만 조회

Eager Loading : 조회시 자식 테이블까지 모두 조회

N+1 문제 : 필요없는 데이터까지 모두 조회

 

각자 장단점이 있다. Lazy Loading시 N+1문제를 피할 수 있지만 항상 필요한 데이터를 따로 구해줘야 한다.

예를 들어, 위에 예시에서 Orders를 조회할 때, Reservation 아이디를 구하고 이를 통해 Orders를 구하는 것과 같이 말이다.

 

이러한 경우 Join이 필요하다.

Eager Loading으로 설정해놓지만 N+1문제를 피할 수 있는 방법이다.

 

예시로는 querydsl에서 fetchJoin()메서드 사용시 lazy loading에서 eager loading으로 변경하라는 의미 이를 통해, N+1 문제인 LeftJoin 시 쿼리를 여러 발생시키는 문제를 쿼리 한번에 모두 불러오는 eager loading으로 해결한다.

 

 

 

Join의 종류

 

예시

Table A (부모) - Table B (자식)

Table A (AId, value1, value2)

Table B (BId, value3, value4, AId)

-> AId로 둘은 연결되어 있음

 

 

 

0. Inner vs Outer

- Null 없음 vs Null 있음

 

 

 

1. Inner Join

- 두개 이상의 테이블에서 일치하는 행만을 선택하여 결과 집합을 생성

- 기본적으로 교집합을 반환하며, 조건에 일치하는 행만 반환

- 만약 Table A와 Table B를 Inner Join하게 되면

-> "부모테이블에서는 자식테이블을 소유한 행, 자식테이블에서는 부모테이블을 소유한 행만 출력된다"

-> "부모 자식 관계가 아니더라도 ON의 조건이 서로 연결된 테이블의 행들을 출력한다."

-> "Select부분에는 부모 테이블, 자식 테이블의 모든 행 출력, 또한 선택도 가능하다" 

 

쿼리문

SELECT *
FROM TableA
INNER JOIN TableB ON TableA.AId = TableB.AId;

querydsl

leftJoin()

- N+1 문제 발생시 "쿼리를 여러번 날리지 말고 하나만 날려 가져오고 싶은 정보 가져와라"라는 의미.

 

 

2. Outer Join

 

2-1. Left Join 

- 첫번째 테이블의 모든 행과 두번째 테이블에서 일치하는 행을 결합한 결과 출력

- 만약 첫번째 테이블의 행들 중 두번째 테이블에 연결된 값이 없는 경우 두번째 테이블 관련 열들을 모두 Null 처리해서 반환

 

쿼리문

SELECT *
FROM TableA
LEFT JOIN TableB ON TableA.AId = TableB.AId;

 

 

2-2. Right Join

- Left 조인과 반대로, 두번째 테이블의 모든 행에 첫번째 테이블의 필드를 추가해서 반환하는 느낌이라고 생각하면 쉽다.

- 마찬가지로, 빈값은 Null 처리

 

쿼리문

SELECT *
FROM TableA
RIGHT JOIN TableB ON TableA.AId = TableB.AId;

 

 

2-3. Full Join

- 왼쪽, 오른쪽 테이블의 모든 행을 출력하는데, 결과로 모든 필드들을 출력가능하고 빈 값은 Null 처리해서 출력한다고 생각하면 된다.

- 참고로 querydsl은 full 조인 제공안함

SELECT *
FROM TableA
FULL JOIN TableB ON TableA.AId = TableB.AId;

 

 

 

3. Self Join

- 하나의 테이블 내에서 자체와 조인 하는 경우에 사용한다.

- 테이블 내에서 관련 정보를 찾거나 계층적 데이터 모델을 처리할 때 유용하다.

- 예시 : 회사 조직도 테이블(직책 - 상관 - 전화번호)가 있을 때, 대리의 상관의 전화번호는?

SELECT *
FROM TableA
SELF JOIN TableB ON TableA.AId = TableB.AId;

 

 

 

4. Cross Join

- 상호 조인

- 두 테이블의 모든 가능한 조합을 생성한다.

- 첫번째 행의 모든 행과 두번째 행의 모든 행을 조합한다.

- 두 테이블의 필드들로 만들 수 있는 조합이라 수가 굉장히 커질 수 있어서 주의해야 한다.

- 예를들어, A는 10행, B도 10행 존재시 100개의 결과 출력

SELECT *
FROM TableA
CROSS JOIN TableB

 

 

(+) 묵시적 조인 vs 명시적 조인

 

DB를 활용해 프로젝트를 하다보면 그냥 Where 절을 연결해서 출력하면 안되나 라는 생각이든다.

즉, 그냥 Where절에 공통 값을 연결하면 똑같은 성능을 내지 않나라는 생각이다.

하지만 이렇게할 경우 크로스 조인이 발생할 가능성이 크다.

이러한 경우를 묵시적 조인이라고 하고 이를 회피하기 위해 Left Join 등 이름을 명시해서 조인을 하는게 좋다

이러한 경우를 명시적 조인이라고 한다.

 

 

 

 

3. Transaction

트랜잭션은 많이 들어봤으리라 생각이든다. 예시로 은행 프로그램을 예시로 들자면, 돈이 없는데 돈을 쓸 수 없을 것이다. 이런 여러가지 상황에서 문제가 있다면 rollBack 처리를 하는 것이 필요하고 이것이 트랜잭션의 기본이라고 생각이든다.

 

Spring에서는 보통 @Transaction 애너테이션을 통해 트랜잭션 관리를 많이한다.

이 애너테이션에 대해서 자세히 정리해보고자 한다.

 

 

클래스 레벨 vs 메서드 레벨

트랜잭션을 어디에 설정하냐에 따라 차이가 발생한다.

보통은 메서드 레벨에 선언하며, 메서드마다 다른 트랜잭션 조건을 설정한다.

하지만, 클래스 레벨에서 선언도 가능하며 모든 메서드에 동일한 트랜잭션을 속성을 적용하거나 클래스 내에서 여러 메서드 간의 트랜잭션 관리 시 사용한다. 클래스 레벨 트랜잭션 적용시 한 메서드에서 롤백 상황 발생시 모든 메서드에 영향을 끼치므로 보통은 메서드 레벨에서 사용한다.

 

 

트랜잭션 롤백 과정

• 1. 시작 : 데이터 베이스의 현재 상태를 나타내는 스냅샷 생성
• 2. 작업 : 사용자가 데이터 베이스에 작업을 수행한다 (CRUD)
• 3. 문제 발생 : 예를 들어, Spring에서는 자바를 기반으로 하기에 RuntimeException이 발생할 수 있고 또 개발자 자체적으로 만든 예외가 발생할 수 있다.
• 4. 트랜잭션 롤백 : 예외가 발생하거나 명시적으로 롤백을 지시하는 경우, 데이터베이스는 트랜잭션의 시작지점으로 롤백되고 변경내용이 이전 상태로 복원된다.
• 5. 데이터 베이스 상태 복원 : 스냅샷을 이용해 데이터 베이스는 초기 상태로 돌아간다.
• 6. 트랜잭션 종료 : 커밋된 경우, 변경사항을 반영하고 롤백된 경우 모두 원상태로 돌아간다.

 

 

트랜잭션 속성 지정 

- 메서드는 클래스의 메서드를 의미

• propagation : 트랜잭션의 전파 동작을 지정한다, 다른 트랜잭션 내에서 해당 메서드를 호출할 때 어떻게 동작할지를 정의한다. 예를 들어, 클래스 레벨로 트랜잭션 설정시 해당 메서드는 이 트랜잭션에서 클래스 단위로 열린 해당 트랜젝션의 동참을 할지 아니면 새로운 트랜잭션을 생성할지 등을 설정한다.
• isolation : 트랜잭션의 격리 수준을 설정한다, 여러 트랜잭션이 동시에 수행될 때, 어떻게 데이터를 공유하고 격리해야할지를 결정 예를들어, 여러명의 사용자가 데이터 베이스를 사용할 때, 하나의 트랜잭션(사용자1)이 데이터에 접근하고 있고 다른 트랜잭션(사용자 2)이 해당 데이터에 접근할 때 어떻게 할지 설정
• timeout : 해당 메서드가 해당 시간을 초과하면 롤백처리
• readOnly : 설정시 데이터를 읽기만 가능
• rollbackFor : 롤백을 발생시킬 오류의 종류를 정의
• noRollbackFor : 롤백 안해도 되는 오류의 종류를 정의

 

추가적인 특징

 

1. rollbackFor 지정안하면 기본적으로 많은 예외에 대해 처리해준다. 하지만 특별한 상황에 대해서는 개발자가 오류를 만들어 처리를 해주어야 한다. 

기본적으로 아래의 예외들은 처리해준다고 한다.

 

2. readOnly = true 설정시, 해당 메서드에서는 데이터를 읽는 쿼리만 날릴 수 있다.

 

3. 아무 설정이 없다면 아래와 같이 설정된다.

 

4. 기본적으로 데이터베이스의 종류마다 Isolation이 다르니 찾아봐야 한다.

 

 

Transaction 전파

특징

- 트랜잭션은 하나의 Connection 객체를 가져와 사용하다고 닫는다.

- @Transactional 의 장점은 여러 트랜잭션을 묶어서 커다란 하나의 트랜잭션 경계를 만들 수 있다는 점이다.

- 이미 트랜잭션이 진행중일 때, 추가 트랜잭션 진행을 어떻게 할지 결정하는 것이 전파 속성을 의미한다.

- 전파 속성에 따라 기존의 트랜잭션에 참여, 별도의 트랜잭션으로 진행, 에러를 발생 시키는 등 여러 선택이 가능하다.

 

물리 트랜잭션과 논리 트랜잭션

• 물리 트랜잭션 : 실제 데이터 베이스에 적용되는 트랜잭션으로, 커넥션을 통해 커밋/롤백 하는 단위
• 논리 트랜잭션 : 스프링이 트랜잭션 메니저를 통해 트랜잭션을 처리하는 단위
• - 모든 논리 트랜잭션이 커밋되어야 물리 트랜잭션이 커밋된다.
• - 하나의 논리 트랜잭션이라도 롤백되면 물리 트랜잭션도 롤백된다.

Spring과 Transation

- 기본적으로 스프링은 멀티 스레드 환경에서 여러 스레드가 같은 메서드 접근 시 서로 다른 트랜잭션을 생성한다.

- 즉, 고려할 부분은 한 스레드가 여러 메서드를 묶을 것이냐 안 묶을 것이냐이다.

 

 

예시

 

물리적 트랜잭션 : A의 계좌에서 돈을 출금하는 과정

논리적 트랜잭션1 : 사용자 A의 계좌 잔액 확인

논리적 트랜잭션2 : 출금

논리적 트랜잭션3 : 이체 내역 로깅

 

물리적 트랜잭션 : B의 계좌에 돈을 입금하는 과정

논리적 트랜잭션1 : 사용자 B의 계좌 잔액 확인

논리적 트랜잭션2 : 입금

논리적 트랜잭션3 : 이체 내역 로깅

 

 

 

 

INDEX

- 기본적으로 인덱스는 책의 목차라고 생각하자.

 

 

장점 및 단점

• 장점
• - SELECT 속도가 빨라진다.
• - 결국 컴퓨터의 부담이 줄어들어 전반적인 시스템의 성능이 향상된다.
• 단점
• - 추가적인 공간을 더 차지한다. 대략, 10~20%를 차지한다.
• - 데이터가 이미 많은 상태에서 인덱스 생성시 시간이 오래 걸린다.

 

종류

• 클러스터형 인덱스
• - 저장상태를 통해 속도향상, 이미 정렬되어 있는 영어사전이나 국어사전을 생각하면 쉽다.
• - PK로 자동으로 생성된다.
• - 테이블당 하나만 존재
• 세컨더리 인덱스
• - 책 뒤의 목차, 인덱스를 생각하면 쉽다.
• - 테이블 생성할때, 해당 필드에 UNIQUE(보조키, 고유키) 설정시 자동으로 보조 인덱스로 설정 및 생성된다.
• - 테이블당 여러개 존재
• 복합 인덱스
• - 여러개의 필드로 인덱스를 만드는 방식
• - 도시, 동 필드가 있고 천안시, 두정동 데이터가 있다면 '도시동'을 만들어 '천안시두정동'로 검색하는 방식

 

 

 

내부 동작

• 균형 트리 사용 : 나무를 거꾸로 뒤집은 구조(루트, 중간, 리프 형태)
• 페이지 분할 : 데이터 추가시 인덱스를 수정하는데, 지정한 인덱스 페이지에 공간이 부족하여 페이지를 나누고 저장하는 방식을 의미, 연속적으로 상위 페이지도 수정을 해야하고 상위 페이지도 페이지가 부족하다면 또 분할이 발생할 수 있어 성능이 저하 됨.

 

클러스터형 vs 세컨더리

클러스터형은 인덱스 조회(루트 노드)->물리적 공간 조회(리프 노드) 과정이고 세컨더리는 인덱스 조회(루트 노드) -> 리프 노드 조회 -> 물리적 공간 조회 과정이다. 즉, 클러스터형은 리프 노드가 바로 물리적 공간으로 연결되기 때문에, 성능이 더 빠르다.

 

 

고려사항

• 인덱스를 너무 많이 만들면 해당 인덱스를 조회하는 횟수도 증가하여 성능이 오히려 떨어진다.
• 데이터가 많은 상태에서 인덱스를 생성하면 시간이 오래걸린다.
• 인덱스를 생성하면 용량을 더 차지한다.
• SELECT에서는 성능이 개선되지만 UPDATE, DELETE, INSERT 작업은 인덱스를 수정해야하고 페이지 분할도 일어날 수 있어 성능이 저하된다.
• 세컨더리 인덱스는 클러스터형 인덱스보다 최소 1회 이상 더 조회하기에 클러스터형 인덱스의 성능이 더 빠르다. 

 

 

 

 

 

(+) 추가 개념 정리

 

View

- 보안 등 여러가지 이유로 특정 쿼리문을 View에 연결해 출력하는 방식

CREATE VIEW member_view AS SELECT * FROM member;
SELECT * FROM member_view;

-> 설정한 쿼리문과 동일한 역할을 수행

 

 

스토어드 프록시저

- Programming의 형태로 쿼리문을 작성하는 방법

 

Query 순서, Distince, Limit

- 순서
SELECT 열이름
FROM 테이블
WHERE 조건식
GROUP BY 열이름
HAVING 조건식
ORDER BY 열이름
LIMIT 숫자

- Limit
// LIMIT 3; 처리시 앞에서부터 3개만 출력
// LIMIT 3, 2; 처리시 앞에서부터 3번째부터 2개, 즉 4,5등 출력

- Distinct
// SELECT DISTINCT user_address FROM member; 처리시 중복값 제거

 

ORDER BY 절

- 결과를 정렬한다.

// Query
ORDER BY user_name DESC;
ORDER BY user_name ASC;

// Querydsl
// 나이로 내림차순, 이름으로 오름차순, 이름이 null이라면 마지막에 출력
.orderBy(user_age.desc(), user_name.asc().nullslast())

 

 

GROUP BY 절

- SUM(), AVG(), MIN(), MAX(), COUNT(), COUNT(DISTINCT) 와 보통 같이 사용된다.

- 여러가게의 회원들이 각각 쿠폰을 가지고 있을 때, 회원이 가지고 있는 전체 쿠폰 수
SELECT SUM(count)
FROM coupon
GROUP BY user_id;

- 지금까지 해당 음식점에서 주문한 음식 아이디별 전체가격
SELECT SUM(price*amount)
FROM orders
GROUP BY user_id;

- 지금까지 해당 음식점에서 주문한 음식 아이디별 전체가격이 10000원 이상
SELECT SUM(price*amount)
FROM orders
GROUP BY user_id
HAVING SUM(prive*amount) >= 10000;

 

INSERT, UPDATE, DELETE 기본, AUTO_INCREMENT PRIMARY KEY

// INSERT
INSERT INTO 테이블 VALUES (값1, 값2, 값3); // 전체 행의 값에 넣는경우
INSERT INTO 테이블 (열1, 열2) VALUES (값1, 값2);
INSERT INTO - SELECT 구문 : 해당 Select 결과를 테이블에 넣는다.

// UPDATE
UPDATE 테이블 SET user_name = '재민', user_age = user_age-1 WHERE user_name = '잼인';

// DELETE
DELETE FROM 테이블 WHERE user_name LIKE '%민';
DELETE FROM 테이블 WHERE user_name LIKE '%민' LIMIT 5; // 앞에서부터 5개만 삭제

// AUTO_INCREMENT PRIMARY KEY는 테이블 생성시 사용
user_id long AUTO_INCREMENT PRIMARY KEY,
user_id long AUTO_INCREMENT PRIMARY KEY=100, // 100부터 생성
ALTER TABLE AUTO_INCREMENT PRIMARY KEY=100; // 테이블 생성 후 변경 가능
SET @@auto_increment_increment = 3; // 테이블 생성 후 값 증가량 설정 가능

// 변수 설정
SET @var1 = 10;

 

데이터 형식

- 데이터 베이스 공간 낭비를 위해 줄이자

 

INT

- TINYINT : -128 ~ 127, 자바의 byte

- TINYINT UNSIGNED : 0~256

- SMALLINT : -32768 ~ 32768, 자바의 short

- INT : -21억 ~ 21억, 자바의 int

- BIGINT : -900경 ~ 900경, 자바의 long

 

CHAR

- CHAR(2) : 두글자까지 공간 불변형

- VARCHAR(10) : 10글자까지 공간 가변형

-> char이 속도가 향상된다, varchar는 공간 효율이 좋다.

-> 따라서, 지역(서울, 경기) 등 이미 알 수 있는 것은 불변형으로, 반대는 가변형으로 설정

 

LONGTEXT : Netflx 자막과 같이 정말 긴 길자

LONGBLOB : 동영상, 사진과 같이 정말 긴 바이너리 글자

 

FLOAT : 소수점 아래 7자리까지 표현

DOUBLE : 소수점 아래 15자리까지 표현

 

DATE : YYYY-MM-DD

TIME : HH:MM:SS

DATETIME : YYYY-MM-DD HH:MM:SS