[SQL][DB] 그 외 데이터베이스 구성 요소
뷰 (View)
뷰는 테이블에서 특정 필드들의 데이터만 가져와 볼 수 있도록 하는 가상의 테이블이다. “가상”의 테이블이라 하는 이유는, 실제 테이블은 당연히 데이터를 보유하는 것과 달리, 뷰는 데이터를 보유하지는 않고 그저 원본 테이블로부터 SELECT문을 통해 가져온 데이터를 보여주는 역할을 하기 때문이다.
테이블에 SELECT ~ WHERE문을 쓰는 것과 달리, 뷰는 처음부터 특정 필드의 데이터들만을 가져오는 방식이기에 테이블에서 직접 SELECT ~ WHERE문을 사용하여 특정 데이터만을 가져오는 방식보다 데이터 접근성이 더 좋은 편이라고 한다.
또한, 매번 똑같은 SELECT 쿼리문을 통해 특정 데이터들을 검색할 때가 많다면 매번 쿼리문을 작성하는 것 보단 아예 이를 뷰로 만들어 사용하면 더 편리하게 사용할 수 있다. 즉, 뷰를 사용하면 복잡한 쿼리문을 단순화하고, 재사용성을 확보할 수 있다.
원본 테이블로부터 특정 필드들만을 가져올 수 있다는 점 때문에, 민감한 정보가 들어있는 특정 필드 내 정보를 보여주지 않기 위해서 뷰가 사용된다. 즉, 보안성을 위해 사용된다. DB에 접근하는 사람들은 이 뷰를 사용하게 하면 보안이 필요한 데이터로의 접근을 방지할 수 있게되는 것이다. 뷰도 테이블이기에 사용자도 뷰인지를 분간할 수 없다.
하나의 테이블에 여러 뷰를 사용할 수 있으므로, 하나의 테이블을 여러 관점에서 보고자 할 때 뷰가 유용할 수 있다.
이러한 특징들로 인해 뷰는 보통 DB 관리자가 보안 설정, 테이블 관리 등을 할 때 주로 쓰인다고 한다.
뷰에는 두 종류가 있다고 한다.
- 단순 뷰 : 하나의 테이블과 연결된 뷰
- 복합 뷰: 둘 이상의 테이블들과 연결된 뷰로, 예를 들면 두 테이블을 조인한 결과를 뷰로 만들 때 이 뷰는 복합 뷰가 된다.
뷰를 생성하는 쿼리문은 다음의 형식을 띤다.
CREATE VIEW 뷰_이름
AS
SELECT 문
SELECT문을 통해 테이블로부터 가져온 데이터를 그대로 뷰로 만드는 방식인 것이다. 이렇게 생성된 뷰는 “가상”이긴 하나 어찌됐건 테이블이므로, 일반 테이블처럼 사용하면 된다.
뷰를 간단히 실습해보자. 다음은 이전에 만들었던 회원 정보가 담긴 site_users 테이블 내 특정 필드들의 데이터만을 가져와 뷰로 만드는 쿼리문이다.
-- 보통 뷰 이름은 vw로 시작하는 게 관례라고 한다.
CREATE VIEW vw_users
AS
SELECT class_number, mileage, username, aver_purchase, recomm_by
FROM site_users
ORDER BY class_number, mileage DESC, aver_purchase DESC;
/*
MariaDB [sql_practice]> show tables;
+------------------------+
| Tables_in_sql_practice |
+------------------------+
| classes |
| loyalty |
| site_users |
| site_users_backup |
| tblperson |
| vw_users |
+------------------------+
6 rows in set (0.001 sec)
뷰가 추가되었다.
*/
-- 뷰 사용
SELECT * FROM vw_users;
/*
+--------------+---------+-----------------+---------------+-----------------+
| class_number | mileage | username | aver_purchase | recomm_by |
+--------------+---------+-----------------+---------------+-----------------+
| 1 | 4640 | lscaddinge | 656 | NULL |
| 1 | 2632 | hshickleh | NULL | NULL |
| 1 | 2596 | dionsj | 761 | NULL |
| 1 | 1549 | pcopozio0 | 616 | NULL |
| 1 | 1546 | atailourb | NULL | eszanto1 |
| 2 | 4238 | egudgion3 | NULL | dmulgrewc |
| 2 | 4058 | kmardle8 | NULL | eszanto1 |
| 2 | 3492 | dmulgrewc | 807 | bleggis7 |
| 2 | 1282 | fwillimonta | NULL | lfree6 |
| 3 | 4632 | gfittes4 | 592 | jmatuszkiewicz2 |
| 3 | 1396 | bleggis7 | NULL | NULL |
| 4 | 4908 | ksimeolid | NULL | lfree6 |
| 4 | 2954 | aledesg | 850 | NULL |
| 4 | 2712 | eszanto1 | NULL | NULL |
| 4 | 2235 | dbuttrum5 | NULL | egudgion3 |
| 5 | 4042 | dsummerside9 | 249 | NULL |
| 5 | 3712 | lglandersi | 100 | good123 |
| 5 | 3486 | srenonf | 322 | jmatuszkiewicz2 |
| 5 | 1891 | lfree6 | 547 | NULL |
| 5 | 1129 | jmatuszkiewicz2 | 855 | lglandersi |
+--------------+---------+-----------------+---------------+-----------------+
20 rows in set (0.014 sec)
*/
예제 1-1
한 가지 주의할 점은, 뷰에 DML 작업을 할 때이다. 뷰는 앞서 언급했듯, 원본 테이블로부터 특정 필드들만을 가져와 보여주는 일이 다반사이므로, 이 뷰를 원본 테이블로 착각하고 뷰에 보여진 필드들에만 새 데이터를 추가한다면, 원본 테이블 내 다른 필드들에는 NULL값이 주어지는 것과 마찬가지이다. 이로 인해 이상한 형태의 데이터가 삽입될 수 있고, 설령 NN 속성이 부여되어도 뷰만 볼 수 있는 사용자 입장에서는 에러가 나도 무슨 이유로 에러가 나는지 단번에 파악하기 힘들다는 단점이 있다. 참고로 뷰는 “가상” 테이블이기에 뷰에 직접 DML 작업을 한다고 해서 뷰의 데이터가 변경되는 것이 아니라, 뷰에 연결된 원본 테이블에 내에서 변경 작업이 이뤄진다. 애초에 뷰는 데이터를 담고 있지도 않다.
인덱스(Index)
인덱스는 대용량의 데이터들로부터 SELECT문을 통해 원하는 데이터들을 검색할 때 검색 속도를 향상시켜주는 도구이다.
인덱스도 메모리 공간을 따로 차지한다. 따라서 인덱스를 불필요하게 생성하거나 남발하지 않도록 하는 것이 좋다.
또한, 데이터 검색이 아닌 DML 언어를 이용한 데이터 변경 작업이 빈번하면 오히려 검색 성능이 나빠지니 주의해야 한다.
인덱스는 테이블 내 특정 필드에 적용하는 방식인데, 만약 특정 필드에 중복되는 데이터들이 많으면 검색 속도가 느려질 수 있으니 되도록 중복되는 데이터들이 없는 필드에 적용하는 것이 좋다.
인덱스는 특정 데이터 검색 시의 검색 속도를 향상시켜주는 도구이니 사용 시 WHERE 절과 같은 조건절과 같이 사용하는 것이 좋다. 만약 사용하지 않고 전체 데이터를 출력하면 이는 인덱스를 적용한 의미가 없게 되기 때문이다. 또한 WHERE 절을 이용하여 자주 검색하게 되는 필드에 인덱스를 적용해야 인덱스의 제 성능을 발휘할 수 있다.
인덱스에는 크게 두 가지 종류가 있다.
- 클러스터형 인덱스(Clustered Index) : 기본 키가 지정된 필드에 자동으로 생성되는 인덱스이다. 기본 키는 기본 적으로 한 테이블에 한 개의 필드에만 생성 가능하므로, 클러스터형 인덱스도 한 테이블 당 하나만 생성할 수 있다. 기본 키로 지정한 필드를 자동으로 정렬하여 보여준다.
- 보조 인덱스 (Secondary index) : 고유 키로 지정한 필드에 자동 생성되는 인덱스이다. 고유 키는 기본 키와 달리 한 테이블에 여러 필드들에 독립적으로 지정할 수 있으므로 보조 인덱스도 한 테이블에 여러 개가 존재할 수 있다. 클러스터형 인덱스와는 달리 보조 인덱스는 필드에 적용해도 자동으로 정렬해주진 않는다.
위 인덱스들을 살펴보기 위해 다음의 예제를 준비하였다.
CREATE TABLE players (
id int,
username varchar(10),
score int
);
INSERT INTO players VALUES
(4, 'goodday123', 1123),
(2, 'kimquel00', 2341),
(3, 'mython', 844),
(1, 'javas123', 1500);
/*
MariaDB [sql_practice]> select * from players;
+------+------------+-------+
| id | username | score |
+------+------------+-------+
| 4 | goodday123 | 1123 |
| 2 | kimquel00 | 2341 |
| 3 | mython | 844 |
| 1 | javas123 | 1500 |
+------+------------+-------+
4 rows in set (0.000 sec)
*/
예제 2-1
위 예제에서 생성한 테이블에는 아무런 제약조건도 걸려 있지 않음을 알 수 있다. 이로 인해 데이터가 삽입된 순서 그대로 데이터들이 존재하는 것을 볼 수 있다.
여기서 특정 데이터 하나를 검색해보자. 단, SELECT문 앞에 EXPLAIN 키워드를 작성한 상태로.
MariaDB [sql_practice]> EXPLAIN SELECT * FROM players WHERE id = 4;
+------+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | players | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+------+-------------+---------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.000 sec)
예제 2-2
위 쿼리문의 실행결과 표를 보면, possible_keys와 key 필드에 NULL값이 주어져 있으면 해당 테이블에는 기본 키나 고유 키가 없으므로 인덱스도 설정되어 있지 않다는 뜻으로 해석할 수 있다. rows 필드의 값이 4인데, 이는 WHERE절을 통해 특정 데이터를 검색할 때 테이블 내 모든 데이터들을 검색했다는 뜻이다. 이렇게 처음부터 끝까지 모든 데이터들을 검색하는 방식을 Full scanning 방식이라 한다. 만약 해당 테이블 내 데이터가 수만 수백만 건이였다면 검색 속도가 매우 느렸을 것이다.
이제 이 테이블의 id에 기본 키를 지정해보자.
ALTER TABLE players
ADD CONSTRAINT pk_id PRIMARY KEY (id);
예제 2-3
MariaDB [sql_practice]> desc players;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| username | varchar(10) | YES | | NULL | |
| score | int(11) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.025 sec)
MariaDB [sql_practice]> select * from players;
+----+------------+-------+
| id | username | score |
+----+------------+-------+
| 1 | javas123 | 1500 |
| 2 | kimquel00 | 2341 |
| 3 | mython | 844 |
| 4 | goodday123 | 1123 |
+----+------------+-------+
4 rows in set (0.000 sec)
예제 2-3 실행결과
기본 키 설정 후 다시 해당 테이블을 살펴보면 우리가 기본 키 지정 외에는 아무것도 하지 않았음에도 id 필드의 값이 자동으로 오름차순 정렬되어 있는 것을 볼 수 있다. 이는 기본 키가 설정된 필드에 클러스터형 인덱스가 자동 생성되어 적용되었다는 것을 의미한다.
이제 아까와 똑같이 특정 데이터를 검색해보자.
MariaDB [sql_practice]> EXPLAIN SELECT * FROM players WHERE id = 4;
+------+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | players | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+------+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.001 sec)
예제 2-4
아까와는 달리 possible_keys, key에 ‘PRIMARY’란 값이 부여되어 있고, rows 필드엔 1이 적혀져 있다. 즉, 데이터 검색 시 인덱스를 이용했다는 것이고, 데이터를 단 한 번만 검색하여 찾았다는 것을 의미한다. 즉, 데이터 검색 속도가 향상되었음을 확인할 수 있다.
사용자가 직접 인덱스를 생성하여 이를 기존 테이블의 특정 필드에 적용시킬 수 있다. 인덱스를 생성하는 문법은 다음과 같다.
CREATE INDEX idx_인덱스이름
ON 테이블명(필드);
Transaction
예를 들어, A라는 사람이 B라는 사람에게 돈을 송금한다고 가정해보자. A는 돈을 자신의 계좌에서 인출하여 B에게 돈을 송금했다. 그런데 알 수 없는 에러가 발생하여 B의 계좌에는 돈이 들어오지 않았다. 사실상 A, B 모두 돈을 잃은 것이나 다름없다. 이 과정을 자세히 뜯어보면 송금을 위해 우선 계좌로부터 돈을 “인출”하는 과정과 이를 다른 사람의 계좌에 “송금”하는 과정으로 나뉠 수 있다. 즉 하나의 과정에서 두 가지 작업이 연속적으로 이루어지고 있다. 이 때, 하나의 작업이라도 수행되지 않으면 치명적인 결과를 일으킨다. 그렇다면 어떻게 하는 게 좋을까? 일단 “인출”, “송금”과 같은 여러 작업들을 하나의 작업 단위로 묶어버린 후, 여러 작업들 중 하나라도 실패하면 그 이전 상태로 돌리고, 모두 성공하면 이를 결과에 반영하는 것이 좋겠다. 그러면 중간에 에러가 나도 그 이전 상태로 바로 되돌리면 되기에 치명적인 오류를 방지할 수 있게 된다.
DB에서의 Transaction(트랜잭션)이 딱 이러한 역할을 한다. transaction이 원래 “거래”를 뜻하는데, DB에서의 Transaction은 데이터베이스의 상태를 변화시키기 위해 수행하는 더 이상 분할 불가능한 논리적 작업 단위를 의미한다. 여러 작업들을 transaction이라는 하나의 작업 세트로 묶은 것이라고 보면 된다. 이렇게 여러 작업들을 하나로 묶어 처리하는 것은 DML 명령어를 통해 데이터를 조작하는 과정에서 치명적인 오류를 방지하여 데이터 안전성을 확보하기 위함이다. 앞서 transacion의 정의에 “데이터베이스의 상태를 변화”라는 말의 의미가 바로 이것이다. 즉, 달리 말하면 trasaction에는 DML 명령어를 통해 데이터를 조작하는 행위가 수반된다. (SELECT문인 DQL도 transaction 안에 들어갈 수 있다)
하나의 transaction에는 둘 이상의 쿼리문들이 포함되어 있으며, 모든 쿼리문들이 정상적으로 작업을 수행하면 이를 결과에 반영하고, 중간에 하나라도 오류가 나면 이러한 것들을 결과에 반영하는 것이 아닌, transaction을 수행하기 전으로 되돌아간다.
어떤 작업들을 transaction이란 작업단위로 묶을지는 사람이 임의로 정하는 기준에 따른다. 쉽게 말하면 transaction 설계에는 사용자의 판단이 중요하다는 것이다.
이러한 transaction을 위해선 당연하겠지만 transaction 내 각 작업들의 실행 및 처리 결과를 log로 기록해놔야 한다. 그래야 저장이든 롤백이든 할 수 있기 때문이다. 이러한 로그를 transaction log라 하는데, 이로 인해 DB에는 실제 데이터들 뿐만 아니라 log도 포함되어 있다고 한다. 주로 DML 작업이 하나하나 실행될 때마다 그 이력이 log에 기록된다고 한다.
transaction 작업은 하나의 데이터에 여러 작업들이 동시에 실행되면 데이터 안전성을 확보할 수 없으므로, 작업도 한 번에 하나씩, 즉 직렬적으로 진행된다. 따라서 transaction 작업은 동기적(synchronous)으로 진행된다. 즉, 하나의 작업이 끝나야 다음 작업이 실행되는 방식이다.
Transaction 제어 명령어
- commit : transaction의 모든 작업들이 완료되었을 때, 이 결과를 영구히 저장하는 명령어. 결과를 영구히 저장하기에 이미 저장된 결과에 대해서는 롤백을 할 수 없다. 트랜잭션이 모두 성공적으로 마쳐도 이 명령어를 입력해야 결과물이 영구히 저장되므로 commit을 잊지 말자.
- rollback : Transaction 작업에 차질이 생겨 트랜잭션 작업을 시작하기 전으로 되돌리는 명령어. 마지막 커밋 시점으로 되돌리는 것과 같다.
Transaction status (트랜잭션 상태)
transaction 작업 과정에서 여러 상태들이 존재할 수 있다. 단계별로 정리하면 다음과 같다.
- 활성(Active) : transaction이 실행되면 “활동”상태가 되는데, 이는 작업들이 정상적으로 실행 중인 상태를 의미한다. 여기서 작업의 성공 여부에 따라 트랜잭션 상태는 두 분기점으로 나뉜다.
- 작업 성공 시
- 부분 완료 (Partially Committed) : 트랜잭션 내 모든 작업들이 실행되었으나 아직 이를 commit하지 않은 상태.
- 완료 (Committed) : 트랜잭션 내 모든 작업들을 성공적으로 마치고 이를 commit을 통해 영구히 저장까지 한 상태.
- 작업 실패 시
- 실패(Failed) : 트랜잭션 실행 중 어떠한 이유로 오류가 발생하여 중단된 상태.
- 철회(Aborted) : 트랜잭션이 실패하여 실패 상태에 있을 때, 트랜잭션 실행 이전 시점으로 되돌린 상태.
![사진 1. 트랜잭션 상태를 단계별로 나타낸 도식. References [6] 참조](https://media.geeksforgeeks.org/wp-content/uploads/20230915172115/Transaction-in-dbms.png)
사진 1. 트랜잭션 상태를 단계별로 나타낸 도식. References [6] 참조
Transaction의 특징 (ACID)
Transaction에는 다음의 4가지 특징이 있다. 각 특징의 영어 앞글자를 떠서 ACID라고도 한다.
- 원자성 (Atomicity) : 트랜잭션이 DB에 모두 반영되든가, 아니면 전혀 반영되지 않는 특성. All or Nothing. 여러 작업들이 마치 원자처럼 하나의 더 이상 분할 불가능한 작업 단위로 묶이기 때문에 이러한 특성을 가진다.
- 일관성 (Consistency) : 트랜잭션의 작업 처리 결과는 항상 일관성이 있어야 한다. 데이터베이스에 모호성(ambiguity)이 없어야 하며, 트랜잭션 작업이 commit되면 하나의 안정적인 데이터베이스 상태에서 다른 안정적인 데이터베이스 상태로 변해야 한다.
- 독립성 (Isolation) : 서로 독립적인 둘 이상의 트랜잭션이 동시에 실행될 때 트랜잭션 간 간섭이 없어야 한다. 이로 인해 하나의 트랜잭션이 다른 트랜잭션의 작업이 끝나기도 전에 그 결과를 참조할 수가 없다.
- 지속성 (Durablity) : 트랜잭션이 성공적으로 완료한 경우 그 결과는 영구적으로 반영되어야 한다.
문법
트랜잭션을 위한 문법은 다음의 구조를 띤다.
START TRANSACTION;
-- DML 쿼리문들을 여기에 작성
COMMIT -- 트랜잭션 결과를 DB에 영구적으로 반영.
-- OR
START TRANSACTION;
-- DML 쿼리문들을 여기에 작성
ROLLBACK -- 트랜잭션 시작하기 이전으로 되돌린다.
또한, MariaDB에서는 기본적으로 auto commit이 설정되어 있다. 즉, 사용자가 DB에서 실행하는 모든 작업들이 자동으로 저장된다는 뜻이다.
오토 커밋 설정 여부는 다음의 쿼리문을 통해 확인할 수 있다.
SELECT @@autocommit;
-- @@은 sql에서 전역 변수이다. 전역 변수는 위치에 상관없이 어디에서나 사용할 수 있는 변수.
-- @@autocommit은 mariadb에서 이미 정의한 변수.
/*
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1: 자동 커밋 설정됨.
0: 자동 커밋 설정안됨.
*/
예제 3-1
오토 커밋 설정 변경은 다음의 쿼리문을 통해 할 수 있다.
-- 오토 커밋 해제
SET @@autocommit = FALSE;
/*
SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.000 sec)
*/
예제 3-2
만약 오토 커밋을 해제한 경우, 모든 DB 작업들이 영구히 저장되지 않으므로 반드시 작업 처리 후에는 COMMIT 명령어를 명시하는 것이 좋다. 이 상태에서는 작업 결과를 취소하고 싶을 때 언제든지 ROLLBACK 명령어도 사용할 수 있다.
References
[1] 에이콘아카데미(강남) 강의
[2] 우재남, “혼자 공부하는 SQL”, (한빛미디어, 2021)
[3] View
[DB/Postgres] 뷰 테이블(View Table) 이해하고 활용하기
[4] Transaction
[MYSQL] 📚 트랜잭션(Transaction) 개념 & 사용 💯 완벽 정리
[5] Transaction
DB 트랜잭션(Transaction) | 👨🏻💻 Tech Interview
[6] transaction
Transaction in DBMS - GeeksforGeeks
This content is licensed under
CC BY-NC 4.0
댓글남기기