3. Database

1. 키(Key) 정리

데이터 베이스 키(Key)의 개념 및 종류

키(Key)는 데이터베이스에서 조건에 만족하는 튜플을 찾거나 순서대로 정렬할 때 다른 튜플들과 구별할 수 있는 유일한 기준이 되는 Attribute(속성)입니다.

• 튜플 : 릴레이션을 구성하는 각각의 행, 속성의 모임으로 구성된다. 파일 구조에서는 레코드와 같은 개념, 튜플의 수 = 카디널리티(Cardinality) = 기수 = 대응수

  • 릴레이션에서는 수많은 튜플들이 있습니다. 고객 릴레이션에 많은 고객들에 대한 튜플이 존재하며 각 튜플들에서는 중복되는 값이 발생할 수 있습니다.

  • 예를 들어 이름, 나이, 사는곳 등이 중복될 수 있는데, 이때 각각의 고객(튜플)을 구분하기 위한 기준이 되는 속성이 필요합니다. 이것을 우리는 "키"라고 하며 속성 또는 속성들의 집합으로 표현할 수 있습니다.

• 키의 종류

  • 슈퍼키, 후보키, 기본키, 대체키, 외래키가 있습니다.

아래 예시의 참고자료

최소성 & 유일성

최소성

→ 키를 구성하는 속성들 중 꼭 필요한 최소한의 속성들로만 키를 구성하는 성질

• 쉽게 설명하면 키를 구성하는 속성들이 진짜 각 튜플을 구분하는데 꼭 필요한 속성들로만 구성되어 있는가를 의미

  • 굳이 없어도 될 속성들을 넣지 말자는 말입니다.

  • 예를 들어 다음과 같은 키(주민번호, 이름, 나이)가 있다면, 물론 현재의 키는 각 튜플을 구분할 수 있습니다.

    • 주민번호, 이름, 나이가 모두 같은 사람을 없을 테니깐요.

    • 근데 생각해보면 이름, 나이가를 빼고도 주민번호만으로 각 튜플을 유일하게 식별할 수 있습니다. 이때 이름, 나이를 빼면 해당 키는 최소성을 만족합니다.

유일성

→ 하나의 키 값으로 튜플을 유일하게 식별할 수 있는 성질

• 여러개의 튜플이 존재할 때 각각의 튜플을 서로 구분할 수 있어야 합니다.

  • 한마디로 각각의 튜플을 유일해야 한다는 의미입니다.

  • 예를 들어 (주민번호, 나이, 사는곳, 혈액형)이라는 속성이 있을 때 나이, 사는곳, 혈액형을 충분히 중복될 수 있는 속성입니다.
    하지만 주민번호는 모두 다르기 때문에 각각의 튜플을 중복되는 속성 값이 존재할 수는 있지만 주민번호는 절대 중복할 수 없습니다.
    이렇게 각각의 튜플을 구분할 수 있는 성질을 유일성이라고 합니다.

키의 종류와 개념

1. 슈퍼키(Super Key) : 유일성 O, 최소성 X

• 슈퍼키는 한 릴레이션 내에 있는 속성들의 집합으로 구성된 키로서 릴레이션을 구성하는 모든 튜플 중 슈퍼키로 구성된 속성의 집합과 동일한 값을 나타내지 않습니다.

• 릴레이션을 구성하는 모든 튜플에 대해 유일성은 만족하지만, 최소성은 만족시키지 못합니다.

• 예시

  • <학생> 릴레이션에서는 '학번', '주민번호', '학번'+'주민번호', '학번'+'주민번호'+'성명' 등으로 슈퍼키를 구성할 수 있습니다.

  • 여기서 최소성을 만족시키지 못한다는 말은 '학번'+'주민번호'+'성명' 가 슈퍼키인 경우 3개의 속성 조합을 통해 다른 튜플과 구별이 가능하지만, '성명' 단독적으로 슈퍼키를 사용했을 때는 구별이 가능하지 않기 때문에 최소성을 만족시키지 못합니다.

    • 즉 뭉쳤을 경우 유일성이 생기고, 흩어지면 몇몇 속성들은 독단적으로 유일성있는 키로 사용할 수 없습니다. 이것을 최소성을 만족하지 못한다고 합니다.

2. 후보키(Candidate Key) : 유일성 O, 최소성 O

• 릴레이션을 구성하는 속성들 중에서 튜플을 유일하게 식별할 수 있는 속성들의 부분집합을 의미합니다.

• 모든 릴레이션은 반드시 하나 이상의 후보키를 가져야합니다.

• 릴레이션에 있는 모든 튜플에 대해서 유일성과 최소성을 만족시켜야합니다.

• 예시

  • <학생> 릴레이션에서 '학번'이나 '주민번호'는 다른 레코드를 유일하게 구별할 수 있는 기본키로 사용할 수 있으므로 후보키가 될 수 있습니다.

• 즉, 기본키가 될 수 있는 키들을 후보키라고 합니다.

3. 기본키(Primary Key) : 후보키 중 선택받은 키

• 후보키 중에서 선택한 주키(Main Key)

• 한 릴레이션에서 특정 튜플을 유일하게 구별할 수 있는 속성

• Null 값을 가질 수 없습니다. (개체 무결성의 첫번째 조건)

• 기본키로 정의된 속성에는 동일한 값이 중복되어 저장될 수 없습니다. (개체 무결성의 두번째 조건)

• 예시

  • <학생> 릴레이션에는 '학번'이나 '주민번호'가 기본키가 될 수 있고, <수강> 릴레이션에는 '학번'+'과목명'으로 조합해야 기본키가 만들어 질 수 있습니다.

    • <수강> 릴레이션에서는 '학번' 속성과 '과목명' 속성은 개별적으로 기본키로 사용할 수 없습니다.

      • 다른 튜플들과 구별되지 않기 때문

  • <학생> 릴레이션에서 '학번'을 기본키로 정의되면 이미 입력된 '1001'은 다른 튜플의 '학번' 속성 값으로 입력할 수 없습니다.

4. 대체키(Alternate Key) : 후보키 중 선택받지 못한 키

• 후보키가 둘 이상일 때 기본키를 제외한 나머지 후보키들을 말합니다.

• 보조키라고도 합니다.

• 예시

  • <학생> 릴레이션에서 '학번'을 기본키로 정의하면 '주민번호'는 대체키가 됩니다.

5. 외래키 (Foreign Key) : 다른 리레이션의 기본키를 참조

• 관계(Relation)를 맺고 있는 릴레이션 R1, R2에서 릴레이션 R1이 참조하고 있는 릴레이션 R2의 기본키와 같은 R1 릴레이션의 속성

• 외래키는 참조되는 릴레이션의 기본키와 대응되어 릴레이션 간에 참조 관계를 표현하는데 중요한 도구로 사용됩니다.

• 외래키로 지정되면 참조 테이블의 기본키에 없는 값은 입력할 수 없습니다. (참조 무결성 조건)

• 예시

  • <수강> 릴레이션이 <학생> 릴레이션을 참조하고 있으므로 <학생> 릴레이션의 '학번'은 기본키이고, <수강> 릴레이션의 '학번'은 외래키입니다.

  • 즉 각 릴레이션의 입장에서 속성은 기본키가 되기도하고, 외래키가 되기도 합니다.

  • <수강> 릴레이션의 '학번'에는 <학생> 릴레이션의 '학번'에 없는 값은 입력할 수 없습니다.

2. SQL - JOIN

데이터베이스에는 여러개의 테이블이 존재합니다. 많은 테이블에서 내가 원하는 정보를 검색할 수 있는 방법이 Join입니다.

• 예를 들어서 사원번호가 100인 사원에 대해 [사원] 테이블에서 이름, 나이, 주소가 필요하고 [봉급] 테이블에서 봉급에 대한 정보가 필요한 경우 [사원], [봉급] 테이블에서 사원번호가 100인 튜플에서 정보를 가져와야 할 것입니다.

  • join을 사용하면 여러 테이블에 걸쳐 존재하는 데이터를 가져올 수 있습니다.

• 설명을 쉽게하기 위해 a속성을 가지는 A테이블, b속성을 가지는 B테이블을 만들었습니다.

  • 두 테이블의 공통된 데이터는 [3,4,5]이며, A테이블에만 있는 데이터는 [1,2], B테이블에만 있는 데이터는 [6,7]입니다.

  

  

Natural Join(자연조인)

• 동등 조인에서 조인이 참여한 속성이 두 번 나오지 않도록 중복된 속성(두 번째 속성)을 제거한 결과를 반환

Inner Join (내부조인)

• A테이블 데이터 ∩ B테이블 데이터

  • 두 테이블에 존재하는 데이터 중에 공통된 데이터만 추출합니다.

  • inner join에서 inner를 삭제해도 유효하며 join을 사용하지 않고도 사용할 수 있습니다.

  

mysql> select * from A inner join B on A.a = B.b;      // 명시적 표현
mysql> select * from A join B on A.a = B.b;            // 명시적 표현
mysql> select * from A,B where A.a = B.b;              // 함축적 표현
+------+------+
| a    | b    |
+------+------+
|    3 |    3 |
|    4 |    4 |
|    5 |    5 |
+------+------+

Outer Join(외부조인)

• A테이블의 데이터 ∪ (A테이블 데이터 ∩ B테이블 데이터)

  • inner join의 경우 무조건 두 테이블의 공통된 값만 가져옵니다. inner join는  A[1,2,3,4,5], B[3,4,5,6,7]에서 무조건 공통된 [3,4,5]만 가져옵니다.

  • outer join을 사용하면 A,B 공통된 값 뿐만 아니라 A데이터 모두 가져올 수 있습니다.

    • left outer join과 right outer join이 있습니다.

• 실제 질의를 작성할 때 outer를 삭제할 수 있습니다.

  • left outer join ㅡ> left join

  • right outer join ㅡ> right join

1. Left Outer Join

• left join도 유효하며 왼쪽 테이블을 기준으로 오른쪽 테이블을 join 합니다.

mysql> select * from A left outer join B on A.a = B.b;
mysql> select * from A left join B on A.a = B.b;
+------+------+
| a    | b    |
+------+------+
|    3 |    3 |
|    4 |    4 |
|    5 |    5 |
|    1 | NULL |
|    2 | NULL |
+------+------+

2. Right Outer Join

• right join도 유효하며 오른쪽 테이블을 기준으로 왼쪽 테이블을 join 합니다.

mysql> select * from A right outer join B on A.a = B.b;
mysql> select * from A right join B on A.a = B.b;
+------+------+
| a    | b    |
+------+------+
|    3 |    3 |
|    4 |    4 |
|    5 |    5 |
| NULL |    6 |
| NULL |    7 |
+------+------+

3. Outer Join에서 공통데이터 제거(차집합)

• 다음과 같이 outer join의 결과값에서 공통데이터를 제거할 수도 있습니다.

mysql> select * from A left join B on A.a = B.b where B.b is null;
+------+------+
| a    | b    |
+------+------+
|    1 | NULL |
|    2 | NULL |
+------+------+

mysql> select * from A right join B on A.a = B.b where A.a is null;
+------+------+
| a    | b    |
+------+------+
| NULL |    6 |
| NULL |    7 |
+------+------+

4. Full Outer Join

• 다음과 같이 A,B 데이터를 한번에 모두 추출할 수 있습니다.

  • 단 mysl의 경우 full outer join을 사용할 수 있기 때문에 다른 방법을 사용해야 합니다.

  • 2번째 방법이 mysql의 경우이며 A테이블의 데이터 + A, B중복을 제거한 right outer join을 사용해야 합니다.

mysql> select * from A FULL OUTER JOIN B on A.a = B.b;
mysql> select * from A left outer join B on A.a = B.b union select * from A right outer join B on A.a = B.b where A.a is null;
+------+------+
| a    | b    |
+------+------+
|    3 |    3 |
|    4 |    4 |
|    5 |    5 |
|    1 | NULL |
|    2 | NULL |
| NULL |    6 |
| NULL |    7 |
+------+------+

5. Full Outer Join에서 공통 부분 제거

mysql> select * from A FULL OUTER JOIN B on A.a = B.b where A.a is null or B.b is null

Cross Join

• 경우의 수를 모두 표현한 방법입니다. 결과값은 N x M 개가 나옵니다.

  

mysql> select * from A cross join B;
+------+------+
| a    | b    |
+------+------+
|    1 |    3 |
|    2 |    3 |
|    3 |    3 |
|    4 |    3 |
|    5 |    3 |
|    1 |    4 |
|    2 |    4 |
|    3 |    4 |
|    4 |    4 |
|    5 |    4 |
|    1 |    5 |
|    2 |    5 |
|    3 |    5 |
|    4 |    5 |
|    5 |    5 |
|    1 |    6 |
|    2 |    6 |
|    3 |    6 |
|    4 |    6 |
|    5 |    6 |
|    1 |    7 |
|    2 |    7 |
|    3 |    7 |
|    4 |    7 |
|    5 |    7 |
+------+------+

Self Join

• cross join는 서로 다른 테이블을 join한 것이지만 self join은 같은 테이블을 join한 것입니다.

  

mysql> select A1.*,A2.* from A A1, A A2;
+------+------+
| a    | a    |
+------+------+
|    1 |    1 |
|    2 |    1 |
|    3 |    1 |
|    4 |    1 |
|    5 |    1 |
|    1 |    2 |
|    2 |    2 |
|    3 |    2 |
|    4 |    2 |
|    5 |    2 |
|    1 |    3 |
|    2 |    3 |
|    3 |    3 |
|    4 |    3 |
|    5 |    3 |
|    1 |    4 |
|    2 |    4 |
|    3 |    4 |
|    4 |    4 |
|    5 |    4 |
|    1 |    5 |
|    2 |    5 |
|    3 |    5 |
|    4 |    5 |
|    5 |    5 |
+------+------+

Semi Join(세미조인)

• 자연 조인을 한 후 두 릴레이션 중 한 쪽 릴레이션의 결과만 반환

• 세미조인은 기호에서 닫힌 쪽 릴레이션의 튜플만 반환

  

출처: https://spidyweb.tistory.com/149

3. SQL Injection

• SQL Injection 이란 악의적인 사용자가 보안상의 취약점을 이용하여, 임의의 SQL 문을 주입하고 실행되게 하여 데이터베이스가 비정상적인 동작을 하도록 조작하는 행위다. 

• 인젝션 공격은 OWASP Top10 중 첫 번째에 속해 있으며, 공격이 비교적 쉬운 편이고 공격에 성공할 경우 큰 피해를 입힐 수 있는 공격이다.

• 여기어때 해킹 사건

  • 2017년 3월에 일어난 “여기어때” 의 대규모 개인정보 유출 사건도 SQL Injection 으로 인해 피해가 발생했다.

 공격 종류 및 방법

• 논리적 에러를 이용한 SQL Injection

  • SQL 공격 기법은 여러 가지가 있는데 논리적 에러를 이용한 SQL Injection은 가장 많이 쓰이고, 대중적인 공격 기법이다.

1) Error based SQL Injection

• 위의 사진에서 보이는 쿼리문은 일반적으로 로그인 시 많이 사용되는 SQL 구문이다. 

• 해당 구문에서 입력값에 대한 검증이 없음을 확인하고, 악의적인 사용자가 임의의 SQL 구문을 주입했다. 주입된 내용은 ‘ OR 1=1 -- 로  

  • WHERE 절에 있는 싱글쿼터를 닫아주기 위한 싱글쿼터와 OR 1=1 라는 구문을 이용해 WHERE 절을 모두 참으로 만들고,
  • -- 를 넣어줌으로 뒤의 구문을 모두 주석 처리한다.

• 매우 간단한 구문이지만, 결론적으로 Users 테이블에 있는 모든 정보를 조회하게 됨으로 써 가장 먼저 만들어진 계정으로 로그인에 성공하게 된다. 

  • 보통은 관리자 계정을 맨 처음 만들기 때문에 관리자 계정에 로그인 할 수 있게 된다. 관리자 계정을 탈취한 악의적인 사용자는 관리자의 권한을 이용해 또 다른 2차피해를 발생 시킬 수 있게 된다.

2) Union based SQL Injection

• SQL 에서 Union 키워드는 두 개의 쿼리문에 대한 결과를 통합해서 하나의 테이블로 보여주게 하는 키워드이다. 

• 정상적인 쿼리문에 Union 키워드를 사용하여 인젝션에 성공하면, 원하는 쿼리문을 실행할 수 있게 된다.

• Union Injection을 성공하기 위해서는 두 가지의 조건이 있다. 

  • 하나는 Union 하는 두 테이블의 컬럼 수가 같아야 한다. 

  • 데이터 형이 같아야 한다.

• 위의 사진에서 보이는 쿼리문은 Board 라는 테이블에서 게시글을 검색하는 쿼리문이다. 

  • 입력값을 title 과 contents 컬럼의 데이터랑 비교한 뒤 비슷한 글자가 있는 게시글을 출력한다. 

  • 여기서 입력값으로 Union 키워드와 함께 컬럼 수를 맞춰서 SELECT 구문을 넣어주게 되면 두 쿼리문이 합쳐서서 하나의 테이블로 보여지게 된다. 

  • 현재 인젝션 한 구문은 사용자의 id와 passwd를 요청하는 쿼리문  

    • 인젝션이 성공하게 되면, 사용자의 개인정보가 게시글과 함께 화면에 보여지게 된다.

• 물론 패스워드를 평문으로 데이터베이스에 저장하지는 않겠지만 인젝션이 가능하다는 점에서 이미 그 이상의 보안위험에 노출되어 있다. 이 공격도 역시 입력값에 대한 검증이 없기 때문에 발생하게 된다.

3) Blind SQL Injection

• Blind SQL Injection은 데이터베이스로부터 특정한 값이나 데이터를 전달받지 않고, 단순히 참과 거짓의 정보만 알 수 있을 때 사용한다. 

• 로그인 폼에 SQL Injection이 가능하다고 가정 했을 때, 서버가 응답하는 로그인 성공과 로그인 실패 메시지를 이용하여, DB의 테이블 정보 등을 추출해 낼 수 있다.

• 위의 그림은 Blind Injection을 이용하여 데이터베이스의 테이블 명을 알아내는 방법이다.

  • (MySQL) 인젝션이 가능한 로그인 폼을 통하여 악의적인 사용자는 임의로 가입한 abc123 이라는 아이디와 함께 abc123’ and ASCII(SUBSTR(SELECT name From information_schema.tables WHERE table_type=’base table’ limit 0,1)1,1)) > 100 -- 이라는 구문을 주입한다.

• 해당구문은 MySQL 에서 테이블 명을 조회하는 구문으로 limit 키워드를 통해 하나의 테이블만 조회하고, SUBSTR 함수로 첫 글자만, 그리고 마지막으로 ASCII 를 통해서 ascii 값으로 변환해준다. 

  • 만약에 조회되는 테이블 명이 Users 라면 ‘U’ 자가 ascii 값으로 조회가 될 것이고, 뒤의 100 이라는 숫자 값과 비교를 하게 된다. 

  • 거짓이면 로그인 실패가 될 것이고, 참이 될 때까지 뒤의 100이라는 숫자를 변경해 가면서 비교를 하면 된다.  

  • 공격자는 이 프로세스를 자동화 스크립트를 통하여 단기간 내에 테이블 명을 알아 낼 수 있다.

4) Time based SQL

• Time Based SQL Injection 도 마찬가지로 서버로부터 특정한 응답 대신에 참 혹은 거짓의 응답을 통해서 데이터베이스의 정보를 유추하는 기법이다. 

• 사용되는 함수는 MySQL 기준으로 SLEEP 과 BENCHMARK 이다.

• 위의 그림은 Time based SQL Injection을 사용하여 현재 사용하고 있는 데이터베이스의 길이를 알아내는 방법이다. 

• 로그인 폼에 주입이 되었으며 임의로 abc123 이라는 계정을 생성해 두었다. 악의적인 사용자가 abc123’ OR (LENGTH(DATABASE())=1 AND SLEEP(2)) -- 이라는 구문을 주입했다. 

  • 여기서 LENGTH 함수는 문자열의 길이를 반환하고, DATABASE 함수는 데이터베이스의 이름을 반환한다.

• 주입된 구문에서, LENGTH(DATABASE()) = 1 이 참이면 SLEEP(2) 가 동작하고, 거짓이면 동작하지 않는다. 

  • 이를 통해서 숫자 1 부분을 조작하여 데이터베이스의 길이를 알아 낼 수 있다. 

  • 만약에 SLEEP 이라는 단어가 치환처리 되어있다면, 또 다른 방법으로 BENCHMARK 나 WAIT 함수를 사용 할 수 있다.

    • BENCHMARK 는 BENCHMARK(1000000,AES_ENCRYPT('hello','goodbye')); 이런 식으로 사용이 가능하다. 이 구문을 실행 하면 약 4.74초가 걸린다.

4) Stored Procedure SQL Injection

• 저장 프로시저(Stored Procedure)

  • 일련의 쿼리들을 모아 하나의 함수처럼 사용하기 위한 것입니다. 

• 공격에 사용되는 대표적인 저장 프로시저는 MS-SQL 에 있는 xp_cmdshell로 윈도우 명령어를 사용할 수 있게 됩니다. 

• 단, 공격자가 시스템 권한을 획득 해야 하므로 공격난이도가 높으나 공격에 성공한다면, 서버에 직접적인 피해를 입힐 수 있는 공격 입니다.

5) Mass SQL Injection

• 2008년에 처음 발견된 공격기법으로 기존 SQL Injection 과 달리 한번의 공격으로 다량의 데이터베이스가 조작되어 큰 피해를 입히는 것을 의미합니다.

• 보통 MS-SQL을 사용하는 ASP 기반 웹 애플리케이션에서 많이 사용된다.

• 쿼리문은 HEX 인코딩 방식으로 인코딩 하여 공격합니다.

• 데이터베이스 값을 변조하여 데이터베이스에 악성스크립트를 삽입하고, 사용자들이 변조된 사이트에 접속 시 좀비PC로 감염되게 합니다. 이렇게 감염된 좀비 PC들은 DDoS 공격에 사용됩니다.

대응방안

입력 값에 대한 검증

• SQL Injection 에서 사용되는 기법과 키워드는 엄청나게 많다. 

  • 사용자의 입력 값에 대한 검증이 필요. 

• 서버 단에서 화이트리스트 기반으로 검증해야 한다. 블랙리스트 기반으로 검증하게 되면 수많은 차단리스트를 등록해야 하고, 하나라도 빠지면 공격에 성공하게 되기 때문이다. 

  • 공백으로 치환하는 방법도 많이 쓰이는데, 이 방법도 취약한 방법이다. 

    • 예를 들어 공격자가 SESELECTLECT 라고 입력 시 중간의 SELECT가 공백으로 치환이 되면 SELECT 라는 키워드가 완성되게 된다. 

      • 공백 대신 공격 키워드와는 의미 없는 단어로 치환되어야 한다.

Prepared Statement 구문사용

• Prepared Statement 구문을 사용하게 되면, 사용자의 입력 값이 데이터베이스의 파라미터로 들어가기 전에DBMS가 미리 컴파일 하여 실행하지 않고 대기한다. 

  • 그 후 사용자의 입력 값을 문자열로 인식하게 하여 공격쿼리가 들어간다고 하더라도, 사용자의 입력은 이미 의미 없는 단순 문자열 이기 때문에 전체 쿼리문도 공격자의 의도대로 작동하지 않는다.

Error Message 노출 금지

• 공격자가 SQL Injection을 수행하기 위해서는 데이터베이스의 정보(테이블명, 컬럼명 등)가 필요하다. 

  -데이터베이스 에러 발생 시 따로 처리를 해주지 않았다면, 에러가 발생한 쿼리문과 함께 에러에 관한 내용을 반환해준다. 

  • 여기서 테이블명 및 컬럼명 그리고 쿼리문이 노출이 될 수 있기 때문에, 데이터 베이스에 대한 오류발생 시 사용자에게 보여줄 수 있는 페이지를 제작 혹은 메시지박스를 띄우도록 해야한다.

웹 방화벽 사용

• 웹 공격 방어에 특화되어있는 웹 방화벽을 사용하는 것도 하나의 방법이다. 

• 웹 방화벽은 소프트웨어 형, 하드웨어 형, 프록시 형 이렇게 세가지 종류

  • 소프트웨어 형은 서버 내에 직접 설치하는 방법

  • 하드웨어 형은 네트워크 상에서 서버 앞 단에 직접 하드웨어 장비로 구성하는 것

  • 프록시 형은 DNS 서버 주소를 웹 방화벽으로 바꾸고 서버로 가는 트래픽이 웹 방화벽을 먼저 거치도록 하는 방법이다.

4. SQL vs NoSQL

SQL, NoSQL 비교하기 위해 자료를 구글링하면 SQL은 관계형 DB, NoSQL은 그와 반대되는 비(Non) 관계형 DB 라는 식으로 설명을 한다.

SQL은 Structured Query Language의 약자로, 그 자체가 DB가 아니다.

<'SQL' - 위키백과>

• SQL

  • 관계형 데이터베이스 관리 시스템(RDBMS : relational database management system)의 데이터를 관리하기 위해 설계된 특수 목적의 프로그래밍 언어

• 흔히 얘기하는 관계형 DB라는 단어는 RDBMS(ex, MySQL)인 것이고, SQL은 RDBMS를 설계하는데 사용된 언어이다.

  • 예전부터 DB는 SQL로 만들어졌기 때문에 SQL이 관계형 DB라는 의미로 사용되는 거라 생각한다.

• SQL(Structured Query Language)은 40년 이상 동안 기본 데이터 저장 메커니즘이었다. 

  • 1990년대 후반 MySQL, PostgreSQL 및 SQLite와 같은 오픈 소스 옵션과 웹 애플리케이션의 등장으로 사용량이 폭발적으로 증가했다.

• NoSQL 데이터베이스는 1960년대부터 존재했지만 최근 MongoDB, CouchDB, Redis 및 Apache Cassandra와 같은 인기 있는 옵션으로 주목을 받고 있다.

Database Schema

• 스키마

  • 데이터베이스의 구조와 제약조건에 관해 전반적인 명세를 기술한 것이다.

    • 개체의 특징을 나타내는 속성(attribute)

    • 속성들의 집합으로 이루어진 개체(entity)

    • 개체 간 존재하는 관계(relationship)에 대한 정의와 이들이 유지해야할 제약조건들을 기술한 것이다.

사용자 관점에 따라 외부, 내부, 개념 스키마로 나뉜다.

1) 개념스키마 = 전체적인 뷰

• 조직체 전체를 관찰하는 입장에서 DB를 정의한 것

  • 따라서 조직의 모든 응용시스템에서 필요로 하는 개체 관계, 그리고 제약조건들을 포함하고 있게 된다.

  • DB를 효율적으로 관리하는데 필요한 접근권한, 보안정책, 무결성 규칙등에 관한 사항들도 추가적으로 포함된다.

  • 따라서 개념스키마를 ‘스키마’ 라고 칭하기도 하며, DB전체를 기술한 것이기 때문에 한 개밖에 존재할 수 없다.

• 개념 스키마의 특징

  • 데이터 베이스의 전체적인 논리적 구조

  • 데이터 베이스에 실제로 어떤 데이터가 저장되었으며 데이터간의 관계는 어떻게 되는가

  • 모든 응용프로그램이나 사용자들이 필요로하는 데이터를 종합한 조직 전체의 데이터베이스

  • 개체간의 관계와 제약조건 명시

  • 데이터 베이스의 접근 권한 보안 및 무결성 규칙에 관한 명세를 정의

  • 단순한 스키마라고 하면 개념스키마를 의미

  • 기관이나 조직체의 관점에서 데이터베이스를 정의

  • 데이터베이스 관리자 (DBA)에 의해 구성

  • 데이터 베이스당 하나만 존재

2) 내부스키마

• 물리적인 저장장치 입장에서 DB가 저장되는 방법을 기술한 것

• 구체적으로 개념 스키마를 디스크 기억장치에 물리적으로 구현하기 위한 방법을 기술한 것

  • 주된 내용은 실제로 저장될 내부레코드 형식, 내부레코드의 물리적 순서, 인덱스의 유/무 등에 관한 것이다.

  • 그러나 DB는 내부 스키마에 의해서 곧바로 구현되는 것이 아니라 내부 스키마에 기술한 내용에 따라 운영체제의 파일시스템에 의해 물리적 저장장치에 기록된다.

• 실무적으로 내부스키마에 의해 DB의 실행 속도가 결정적으로 영향을 받기 때문에 DB의 구축목적에 따라 내부 스키마를 결정해야할 필요가 있다.

• 내부 스키마 특징

  • 데이터 베이스의 물리적 저장구조를 정의

  • 디스크에는 어떤 구조로 저장할 것인가

  • 데이터의 실제 저장방법을 기술

  • 물리적인 저장장치와 밀접한 계층

  • 시스템 프로그래머나 시스템 설계자가 보는 관점의 스키마

3) 외부스키마 = 서브 스키마 - 사용자 뷰

• 사용자나 응용 프로그래머가 개인의 입장에서 필요한 데이터베이스의 논리적 구조를 정의

• 외부 스키마 특징

  • 실세계에 존재하는 데이터들을 어떤 형식, 구조, 배치 화면을 통해 사용자에게 보여줄 것인가

  • 전체 데이터 베이스의 한 논리적 부분 -> 서브 스키마

  • 하나의 데이터베이스에는 여러 개의 외부스키마가 존재가능 & 하나의 외부스키마를 여러 개의 응용프로그램이나 사용자가 공용 가능

  • 같은 데이터베이스에 대해서도 서로 다른 관점을 정의할 수 있도록 허용

  • 일반 사용자는 질의어를 이용 DB를 쉽게 사용

SQL vs NoSQL

schema 관점

• SQL - strict schema

  

  • SQL은 데이터를 저장하기 위해서 스키마가 먼저 정의되어야 한다.

  • SQL에서는 Table에 데이터가 저장된다.

    • Table 구조를 보면 excel sheet가 떠오른다. excel에서 표로 어떤 데이터를 정리한다고 가정하면, 표의 각 항목(ex, id, name)이 Field(혹은 Column)가 되는 것이고, 항목에 맞춰 데이터를 입력한 열이 Recode(혹은 Row)가 된다.

  • SQL은 표에 어떠한 내용이 들어가는지,'항목'을 정의하지 않으면 데이터를 저장 할 수 없다.

    • 즉, 위에서 간단히 언급한,외부/내부/개념 스키마를 정의해야 데이터의 저장을 시작할 수 있다.

• NoSQL - no schema

  

  • NoSQL에서는 Document에 데이터가 저장된다.

    • JSON 혹은 객체의 형태인 key-value로 데이터가 저장된다.

    • Document들이 모여 Collection이 되고,Collection이 모여 Database가 된다.

• 여기서 중요한 점은 스키마를 정의하지 않아도 된다는 점이다.

  • 그래서 어떤 형식으로 데이터를 저장해야 할지 확신이 서지 않는 상황에서는 NoSQL이 적절한 방법이라고 흔히 말한다.

Relation 관점

• SQL - Relational DataBase

  

  • SQL에서 중요한 부분은 관계(Relation)이다.

  • User, Products, Orders Table에 데이터들이 나뉘어 저장되어있다.

    • Orders 테이블을 보면 Maximilian이 Chair를 구매했다는 것을 알 수 있다.

  • 즉, 관계형 데이터는 각 table 간의 관계(join)을 통해 데이터를 파악할 수 있다.

    • 이러한 점은 데이터를 중복 없이 저장할 수 있다는 것이고, 전체 데이터 하나만 관리하면 되기 때문에 데이터의 정확성을 높일 수 있다.

• NoSQL - Non-relational DataBase

  

  • NoSQL에서는 관계란 개념이 없다. 그래서 이름에 붙은 'no'가 붙어있다. 관계형이 아니다!)

  • 위의 예시를 보면,Orders Collection에 관련된 내용이 모두 저장되어 있다.

    • 즉, 확인하고자 하는 데이터가 다 담겨있기 때문에 join을 통해 확인할 필요가 없다.

  • Users, Products Collection처럼, 다른 Collection이 필요하다면 기존 Collection의 데이터를 일부 복제한다.

    • 그래서 Collection 별로 중복된 데이터가 존재한다.

    • 중복된 데이터는 삭제하거나 업데이트할 때 반영이 되지 않을 수 있어 유의해야한다.

SQL 정규화 vs NoSQL 비정규화

서점 데이터베이스에 출판사 정보를 추가하려고 한다고 가정한다.

• 단일 게시자가 둘 이상의 제목을 제공할 수 있으므로 SQL 데이터베이스에서 새 publisher 테이블을 만든다.

• 그런 다음 다음과 같이 레코드를 참조 하는 publisher_id필드를 book테이블에 추가할 수 있다.  

  

  • 이렇게 하면 데이터 중복이 최소화된다. 

  • 우리는 모든 책에 대해 출판사 정보를 반복하지 않고 단지 참조만 한다. 이 기술을 정규화라고 하며 실용적인 이점이 있다. 

  • book데이터를 변경하지 않고 단일 게시자를 업데이트할 수 있다 .

NoSQL에서 정규화 기술을 사용할 수 있다.

• book컬렉션의 문서 

{
  ISBN: 9780992461225,
  title: "JavaScript: Novice to Ninja",
  author: "Darren Jones",
  format: "ebook",
  price: 29.00,
  publisher_id: "SP001"
}

•  publisher컬렉션 의 문서 참조 :

{
  id: "SP001"
  name: "SitePoint",
  country: "Australia",
  email: "feedback@sitepoint.com"
}

그러나 이것이 항상 실용적인 것은 아니다. 문서를 비정규화하고 모든 책에 대해 발행인 정보를 반복하도록 선택할 수 있다.

{
  ISBN: 9780992461225,
  title: "JavaScript: Novice to Ninja",
  author: "Darren Jones",
  format: "ebook",
  price: 29.00,
  publisher: {
    name: "SitePoint",
    country: "Australia",
    email: "feedback@sitepoint.com"
  }
}

이렇게 하면 쿼리 속도가 빨라지지만 여러 레코드에서 게시자 정보를 업데이트하면 훨씬 느려진다.

Performance 관점

• 아마도 가장 논쟁의 여지가 있는 비교인 NoSQL은 SQL보다 빠르다고 정기적으로 인용된다. 

• NoSQL의 더 간단한 비정규화된 저장소를 사용하면 단일 요청에서 특정 항목에 대한 모든 정보를 검색할 수 있다. 

  • 관련된 JOIN이나 복잡한 SQL 쿼리가 필요하지 않는다.

  • 즉, 프로젝트 설계 및 데이터 요구 사항이 가장 큰 영향을 미친다. 

• 잘 설계된 SQL 데이터베이스는 잘못 설계된 NoSQL에 비해 성능이 거의 확실하며 그 반대의 경우도 마찬가지다.

Practicality 관점

• 보안 및 시스템 문제를 살펴보면 가장 인기 있는 NoSQL 데이터베이스는 몇 년 정도 되었다. 

  • 따라서 이미 오래전부터 개발된 SQL 제품보다 문제를 나타낼 가능성이 더 크다.

• 개발자와 시스템 관리자는 최신 데이터베이스 시스템에 대한 경험이 적기 때문에 실수가 발생한다. 

  • NoSQL이 더 신선하게 느껴지거나 불가피하게 스키마 디자인을 피하고 싶기 때문에 NoSQL을 선택하면 나중에 문제가 발생하게 된다.

Scalability 관점

• Scaling

  • 데이터베이스를 얼마나 잘 확장할 수 있나?

    • 데이터베이스가 처리할 수 있는 읽기 및 쓰기 요청 수 

• 스케일링에 관해서는 수직 스케일링 과 수평 스케일링을 구별할 수 있다 .

  1) SQL - Vertical Scaling

  

  • SQL은 수직적으로 확장한다.

  • 수직적 향상은 CPU나 RAM 같은 부품을 업그레이드하거나, 하드웨어를 추가하여 서버의 성능을 향상시키는 것을 의미한다.

    • 하나의 서버의 용량과 성능을 향상하는 것은 일정 수준의 한계가 존재한다.
  
  

  2) NoSQL - Horizontal Scaling

  

  • NoSQL은 수평적으로 확장한다.

  • 수평적 확장 (Horizontal Scalability)은 더 많은 서버를 추가해서서버를 전체적으로 분산시키는 것을 의미한다.

    • 서버의 수를 무한으로 늘리면 DB를 계속 증설할 수 있다.

Sharding(샤딩)

• 같은 테이블 스키마를 가진 데이터를 다수의 DB에 분산하여 저장하는 방법이다.
  
  
• 이 기술을 접목하면 SQL도 수평적 확장을 적용할 수 는 있지만, 실제 구현은 어렵다고 한다. 확장을 고려한다면 수평적 확장으로 구현이 가능한 NoSQL 방식을 적용하는 것이 유리하다.

Property 관점

1) SQL - ACID properties

• SQL은 ACID 특성을 따른다.

  • ACID는 DB의 Transaction(트랜잭션)이 안전하게 수행된다는 것을보장하기 위한 특징이다.

    트랜잭션이란 여러 작업들을 하나로 묶은 단위이다. 하나로 묶인 작업들은 모두 실행되거나, 실행되지 않는다. (all-or-nothing)

  • Atomicity(원자성): 트랜잭션의 작업이 부분적으로 실행되거나 중단되지 않는 것을 보장하는 것이다.(불가능한 최소의 단위인 하나의 원자처럼 동작한다는 의미이다.)

  • Consistency(일관성): 미리 정의된 규칙에서만 수정이 가능한 특성을 의미한다.(숫자 컬럼에 문자열값이 저장이 안되도록 보장한다.)

  • Isolation(고립성): 트랜잭션 수행시 다른 트랜잭션의 작업이 끼어들지 못하도록 보장하는 것이다.

  • Durability(영구성): 성공적으로 수행된 트랜잭션은 영원히 반영이 되는 것을 의미한다.(한번 반영(commit)된 트랜젝션의 내용은 영원히 적용된다.)

2) NoSQL - CAP theorem

• NoSQL은 CAP이론을 따른다.

  CAP이론

  • 분산 시스템에서는 CAP 세 가지 속성 모두를 만족하는 것은 불가능하며, 오직 2가지만 만족할 수 있다는 것으로 정의할 수 있다.

  • Consistency (일관성): 모든 요청은 최신 데이터 또는 에러를 응답받는다.(DB가 3개로 분산되었다고 가정할 때, 하나의 특정 DB의 데이터가 수정되면나머지 2개의 DB에서도 수정된 데이터를 응답받아야 한다.)

  • Availability (가용성): 모든 요청은 정상 응답을 받는다.(특정 DB가 장애가 나도 서비스가 가능해야 한다.)

  • Partitions Tolerance (분리 내구성): DB간 통신이 실패하는 경우라도 시스템은 정상 동작 한다.

Support 관점

• SQL 👍, NoSQL 👎

  • SQL은 역사가 깊은 만큼 NoSQL보다는 적용하는 곳이 많아, 그만큼 전문가도 많고, 관련 기술 Issue에 대한 해결책도 많이 쌓여있다.

  • 반면 NoSQL은 SQL보다는 역사가 깊지 않아 이와 관련된 기술 Issue는 많이 보고되고 있지만, 이 것은 지식 정도로 간주된다.

(상대적으로 NoSQL이 SQL보다 관련 커뮤니티가 작다고 이해하면 될 것 같다.)

Solution 관점

• 장점

  • SQL

    • 명확하게 정의된 스키마, 데이터 무결성 보장

    • 관계를 사용하면 각 데이터를 한 번만 저장할 수 있다.

      • 중복 없음

  • NoSQL

    • 스키마가 없으면 더 많은 유연성을 얻을 수 있다.

      • 저장된 데이터를 언제든지 조정하고 새로운 "필드"를 도입할 수 있다.

    • 데이터는 앱에 필요한 형식으로 저장된다. 이렇게 하면 데이터 가져오기 속도가 빨라집니다.

    • 수직 및 수평 확장이 가능하므로 데이터베이스는 앱에서 발생하는 모든 읽기/쓰기 요청을 처리할 수 있다.

• 단점

  • SQL

    • 유연성 부족, 데이터 스키마를 미리 알고 계획해야 함(나중에 조정하기 어렵거나 불가능할 수도 있음)

    • 관계는 많은 JOIN 문이 포함된 매우 복잡한 쿼리로 이어질 수 있다 .

    • 수평적 확장은 어렵고 종종 수직적 확장만 가능하다. 즉, 어느 시점에서 성장 제한(처리할 수 있는 처리량/성능 관련)에 직면하게 된다.

  • NoSQL

    • 유연성이 향상되면 작업이 엉성해지고 데이터 구조 결정이 연기될 수 있다.

    • 중복 데이터는 데이터가 변경될 경우 여러 컬렉션과 문서를 업데이트해야 함을 의미한다. SQL 세계에서와 같이 한 테이블에 있는 하나의 레코드만 업데이트하는 것이 아니다.

언제 가장 좋을까?

• SQL

  • 명확한 스키마가 중요하며 데이터가 크게 변경되지 않을 때

  • 관계를 맺고있는 데이터가(JOIN된) 자주 변경되는 앱일 경우

• NoSQL

  • 정확한 데이터 요구 사항 또는 데이터 자체가 알려지지 않았거나 변경/확장될 수 있을 때

  • 높은(읽기) 처리량이 필요하지만 데이터를 자주 변경하지 않을 때 (즉, 한 번의 변경에 대해 항상 수십 개의 문서를 업데이트할 필요가 없음).

  • 데이터베이스를 수평으로 확장해야할 때 (즉, 엄청난 양의 데이터를 저장하고 엄청난 읽기 및 쓰기 처리량을 가짐)

💨 분명히 데이터베이스를 다양한 방식으로 구성할 수 있다. 그리고 구조에 따라 일부 문제를 완화하는 NoSQL 데이터베이스로 끝날 수도 있다(예: 중복 쓰기 요청의 양을 줄임). 

💨sql도 요구사항을 만족하면서 복잡한 JOIN문을 만들지 않도록 설계하는 것이 중요하다.

5. 이상(Anomaly)

좋은 관계형 데이터베이스를 설계하는 목적 중 하나가 정보의 이상 현상(Anomaly)이 생기지 않도록 고려해 설계하는 것이다.

이상 현상

• 테이블 내의 데이터 중복성에 의해서 발생되는 데이터 불일치 현상이다.

• 이상 현상 종류

  • 갱신 이상 : 반복된 데이터 중에 일부를 갱신 할 시 데이터의 불일치가 발생한다.

  • 삽입 이상 : 불필요한 정보를 함께 저장하지 않고서는 어떤 정보를 저장하는 것이 불가능하다.

  • 삭제 이상 : 필요한 정보를 함께 삭제하지 않고서는 어떤 정보를 삭제하는 것이 불가능하다.

이상 현상은 정규화(Normalization)을 통해 방지 할 수 있다.

갱신 이상

• 어떤 값을 업데이트 했을때 그 속성의 다른 속성값들과의 불일치가 발생하는 현상

  

  • 위와 같이 첫번째 튜플의 이름 = 김사랑을 김소연으로 고치게 될 경우 3번째 튜플의 내용과 불일치가 발생한다.

  • 이것이 발생되지 않게 하려면 김사랑 값을 갖는 모든 튜플을 일일이 다 찾아서 수정해야한다.

삽입 이상

• 내가 원하는 값만 테이블에 삽입하고 싶은데, 테이블에 필요하지 않은 필드들 때문에 원치 않는 필드의 값도 삽입해야 하는 경우

  

  • 1,2,3번 필드에 대한 값만 테이블에 넣고 싶은데, 테이블이 4개의 필드로 구성되어 있기 때문에 마지막 필드 값을 무엇으로 해야할지 결정 하지 못한다.

삭제 이상

• 원하는 값만 테이블에서 삭제하고 싶은데, 하나의 튜플이 삭제를 원하지 않는 속성값도 갖고 있기 때문에 같이 지워져서 발생하는 문제

  

  • 과목명 = 운영체제, 성적 = 82라는 정보만 삭제하려고한다.

    • 근데 테이블이 더 많은 수의 필드로 구성되어 있기 때문에 이것을 지우려면 지우고 싶지 않은 필드들의 정보도 같이 지워야한다.

6. 인덱스(INDEX)

• RDBMS에서 검색 속도를 높이기 위한 기술

  • 데이터베이스 안의 레코드를 처음부터 풀스캔하지 않고, B+ Tree로 구성된 구조에서 Index 파일 검색으로 속도를 향상시키는 기술이다.(B Tree나 Trie 를 쓰기도 함)

• 추가적인 쓰기 작업과 저장 공간을 활용하여 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조이다.

  • 데이터베이스에서 테이블의 모든 데이터를 검색하면 시간이 오래 걸리기 때문에 데이터와 데이터의 위치를 포함한 자료구조를 생성하여 빠르게 조회할 수 있다.

• 특정 컬럼에 인덱스를 생성하면, 해당 컬럼의 데이터들을 정렬하여 별도의 메모리 공간에 데이터의 물리적 주소와 함께 저장된다.

  

  

  • 인덱스를 활용하면, 데이터를 조회하는 SELECT 외에도 UPDATE나 DELETE의 성능이 함께 향상된다.

    • 해당 연산을 수행하려면 해당 대상을 조회해야만 작업을 할 수 있기 때문이다.

• 만약 index를 사용하지 않은 컬럼을 조회해야 하는 상황이라면 전체를 탐색하는 Full Scan을 사용해야 하기 때문에 처리 속도가 떨어진다.

데이터베이스 파일 구성

• 테이블 생성 시, 3가지 파일이 생성된다.

  • FRM : 테이블 구조 저장 파일

  • MYD : 실제 데이터 파일

  • MYI : Index 정보 파일 (Index 사용 시 생성)

    • 사용자가 쿼리를 통해 Index를 사용하는 칼럼을 검색하게 되면, 이때 MYI 파일의 내용을 활용한다.

DML이 일어났을 때의 상황

• INSERT

  기존 Block에 여유가 없을 때, 새로운 Data가 입력된다.

  → 새로운 Block을 할당 받은 후, Key를 옮기는 작업을 수행한다.

  → Index split 작업 동안, 해당 Block의 Key 값에 대해서 DML이 블로킹 된다. (대기 이벤트 발생)

• DELETE

  <Table과 Index 상황 비교>

  Table에서 data가 delete 되는 경우 : Data가 지워지고, 다른 Data가 그 공간을 사용 가능하다.

  Index에서 Data가 delete 되는 경우 : Data가 지워지지 않고, 사용 안 됨 표시만 해둔다.

  → Table의 Data 수와 Index의 Data 수가 다를 수 있음

• UPDATE

  Table에서 update가 발생하면 → Index는 Update 할 수 없다.

  Index에서는 Delete가 발생한 후, (새로운 작업의 Insert 작업 / 2)배의 작업이 소요되어 힘들다.
  

인덱스(index)의 장점과 단점

• 장점

  • 테이블을 조회하는 속도와 그에 따른 성능을 향상시킬 수 있다.

  • 전반적인 시스템의 부하를 줄일 수 있다.

• 단점

  • 인덱스를 관리하기 위해 DB의 약 10%에 해당하는 저장공간이 필요하다.

  • 인덱스를 관리하기 위해 추가 작업이 필요하다.

  • 인덱스를 잘못 사용할 경우 오히려 성능이 저하되는 역효과가 발생할 수 있다.

    • 만약 CREATE, DELETE, UPDATE가 빈번한 속성에 인덱스를 걸게 되면 인덱스의 크기가 비대해져서 성능이 오히려 저하되는 역효과가 발생할 수 있다.

    • UPDATE와 DELETE는 기존의 인덱스를 삭제하지 않고 '사용하지 않음' 처리를 해준다

    • 만약 어떤 테이블에 UPDATE와 DELETE가 빈번하게 발생된다면 실제 데이터는 10만건이지만 인덱스는 100만 건이 넘어가게 되어, SQL문 처리 시 비대해진 인덱스에 의해 오히려 성능이 떨어지게 될 것이다.

  • 한 페이지를 동시에 수정할 수 있는 병행성이 줄어든다.

  • 데이터 변경 작업이 자주 일어나는 경우, Index를 재작성해야 하므로 성능에 영향을 미친다.

  • 정렬된 상태를 계속 유지해야한다.

상황 분석

• 사용하면 좋은 경우

  1) Where 절에서 자주 사용되는 Column

  2) 외래키가 사용되는 Column

  3) Join에 자주 사용되는 Column

  4) 규모가 작지 않은 테이블

  5) INSERT, UPDATE, DELETE가 자주 발생하지 않는 컬럼

  6) WHERE 또는 ORDER BY에 자주 사용되는 컬럼

  7) 데이터의 중복도가 낮은 컬럼
  

• Index 사용을 피해야 하는 경우

  1) Data 중복도가 높은 Column

  2) DML이 자주 일어나는 Column

7. 트랜잭션(Transaction)

• 트랜잭션이란?

  • 데이터베이스의 상태를 변화시키기 위해 수행하는 작업 단위

  • 상태를 변화시킨다는 것 → SQL 질의어를 통해 DB에 접근하는 것

    • SELECT

    • INSERT

    • DELETE

    • UPDATE

    
    

  • 작업 단위 → 많은 SQL 명령문들을 사람이 정하는 기준에 따라 정하는 것

    • 예시) 사용자 A가 사용자 B에게 만원을 송금한다.

      1. 사용자 A의 계좌에서 만원을 차감한다

         : UPDATE 문을 사용해 사용자 A의 잔고를 변경
         
         

      2. 사용자 B의 계좌에 만원을 추가한다

         : UPDATE 문을 사용해 사용자 B의 잔고를 변경

         
         

    • 현재 작업 단위 : 출금 UPDATE문 + 입금 UPDATE문

      → 이를 통틀어 하나의 트랜잭션이라고 한다.

    • 위 두 쿼리문 모두 성공적으로 완료되어야만 "하나의 작업(트랜잭션)"이 완료되는 것이다. Commit

    • 작업 단위에 속하는 쿼리 중 하나라도 실패하면 모든 쿼리문을 취소하고 이전 상태로 돌려놓아야한다. Rollback

즉, 하나의 트랜잭션 설계를 잘 만드는 것이 데이터를 다룰 때 많은 이점을 가져다준다.

트랜잭션 특징 (ACID properties)

1. 원자성(Atomicity)

• 트랜잭션이 DB에 모두 반영되거나, 혹은 전혀 반영되지 않아야 된다.

2. 일관성(Consistency)

• 트랜잭션의 작업 처리 결과는 항상 일관성 있어야 한다.

• 트랜잭션 수행이 보존해야 할 일관성은 기본 키, 외래 키 제약과 같은 명시적인 무결성 제약 조건들뿐만 아니라, 자금 이체 예에서 두 계좌 잔고의 합은 이체 전후가 같아야 한다는 사항과 같은 비명시적인 일관성 조건들도 있다.

3. 독립성(Isolation)

• 둘 이상의 트랜잭션이 동시에 병행 실행되고 있을 때, 어떤 트랜잭션도 다른 트랜잭션 연산에 끼어들 수 없다.

• 즉, 한 트랜잭션의 중간 결과가 다른 트랜잭션에게는 숨겨져야 한다는 의미

  • 이러한 isolation 성질이 보장되지 않으면 트랜잭션이 원래 상태로 되돌아갈 수 없게 된다.

  • Isolation 성질을 보장할 수 있는 가장 쉬운 방법은 모든 트랜잭션을 순차적으로 수행하는 것이다.

  • 하지만 병렬적 수행의 장점을 얻기 위해서 DBMS는 병렬적으로 수행하면서도 일렬(serial) 수행과 같은 결과를 보장할 수 있는 방식을 제공하고 있다.

4. 지속성(Durability)

• 트랜잭션이 성공적으로 완료되었으면, 결과는 영구적으로 반영되어야 한다.

• 트랜잭션이 성공적으로 완료되어 커밋되고 나면, 해당 트랜잭션에 의한 모든 변경은 향후에 어떤 소프트웨어나 하드웨어 장애가 발생되더라도 보존되어야 한다.

• Commit

  • 하나의 트랜잭션이 성공적으로 끝났고, DB가 일관성있는 상태일 때 이를 알려주기 위해 사용하는 연산

• Rollback

  • 하나의 트랜잭션 처리가 비정상적으로 종료되어 트랜잭션 원자성이 깨진 경우

  • transaction이 정상적으로 종료되지 않았을 때, last consistent state (예) Transaction의 시작 상태) 로 roll back 할 수 있음.

상황이 주어지면 DB 측면에서 어떻게 해결할 수 있을지 대답할 수 있어야 함

트랜잭션 관리를 위한 DBMS의 전략

참고

이해를 위한 2가지 개념 : DBMS의 구조 / Buffer 관리 정책

1. DBMS의 구조

• 크게 2가지 : Query Processor (질의 처리기), Storage System (저장 시스템)

• 입출력 단위 : 고정 길이의 page 단위로 disk에 읽거나 쓴다.

• 저장 공간 : 비휘발성 저장 장치인 disk에 저장, 일부분을 Main Memory에 저장

  • DBMS는 데이터를 고정 길이의 페이지(page)로 저장하며, 디스크에서 읽거나 쓸 때에 페이지 단위로 입출력이 이루어진다.

  • 메인 메모리에 유지하는 페이지들을 관리하는 모듈을 보통 페이지 버퍼(page buffer) 관리자 또는 버퍼 관리자라고 부르는데, DBMS의 많은 주요 모듈 중에서 매우 중요한 모듈 중의 하나이다.

2. 버퍼(Page Buffer Manager) 관리자 또는 버퍼 관리자 (Buffer Manager)

• DBMS의 Storage System에 속하는 모듈 중 하나로, Main Memory에 유지하는 페이지를 관리하는 모듈

  • Buffer 관리 정책에 따라, UNDO 복구와 REDO 복구가 요구되거나 그렇지 않게 되므로, transaction 관리에 매우 중요한 결정을 가져온다.

3. UNDO

• 필요한 이유

  • 수정된 Page들이 Buffer 교체 알고리즘에 따라서 디스크에 출력될 수 있다.

  • 버퍼(Buffer) 교체는 transaction과는 무관하게 buffer의 상태에 따라서, 결정됨.

    • 이로 인해, 정상적으로 종료되지 않은 transaction이 변경한 page들은 원상 복구 되어야 하는데, 이 복구를 UNDO라고 한다.

• 2개의 정책 (수정된 페이지를 디스크에 쓰는 시점으로 분류)

  • steal : 수정된 페이지를 언제든지 디스크에 쓸 수 있는 정책

    • 대부분의 DBMS가 채택하는 Buffer 관리 정책

    • UNDO logging과 복구를 필요로 함.

  • ¬steal : 수정된 페이지들을 EOT (End Of Transaction)까지는 버퍼에 유지하는 정책

    • UNDO 작업이 필요하지 않지만, 매우 큰 메모리 버퍼가 필요함.

4. REDO

• 이미 commit한 transaction의 수정을 재반영하는 복구 작업

• Buffer 관리 정책에 영향을 받음

  • Transaction이 종료되는 시점에 해당 transaction이 수정한 page를 디스크에 쓸 것인가 아닌가로 기준.

• FORCE : 수정했던 모든 페이지를 Transaction commit 시점에 disk에 반영

  • transaction이 commit 되었을 때 수정된 페이지들이 disk 상에 반영되므로 redo 필요 없음.

• ¬FORCE : commit 시점에 반영하지 않는 정책

  • transaction이 disk 상의 db에 반영되지 않을 수 있기에 redo 복구가 필요. (대부분의 DBMS 정책)

💨 정리해보면 DBMS는 버퍼 관리 정책으로 STEAL과 ¬FORCE 정책을 채택하고 있어, 이로 인해서 UNDO 복구와 REDO 복구가 모두 필요하게 된다.

로그

지금까지 설명한 UNDO 복구와 REDO 복구를 위해서 가장 널리 쓰이는 구조는 로그(log)이다. Shadow paging(nilavalagan, 2009)이라고 불리는 복구 기법도 존재하지만, 여기서는 보편적으로 사용되는 로그 기법에 대해서만 설명

• 로그는 로그 레코드의 연속이며 데이터베이스의 모든 갱신 작업을 기록한다.

• 로그는 이론적으로는 안정적 저장 매체(stable storage)에 기록된다고 하는데, 안정적 저장 매체는 어떤 경우에도 절대로 손실이 발생하지 않는 이른바 이상적인 매체이다.

  • 바꿔 말하면 현실 상에서는 존재하지 않는다고 봐야 하는데, RAID 등 인프라 시스템의 도움 외에도 DBMS 자체적으로 여러 벌의 로그를 유지하는 등 안정적 저장 매체처럼 동작하게 하는 기법을 사용하기도 한다.

  • 하지만 대부분 DBMS는 성능 상의 이유로 하나의 로그를 유지한다.

• 로그는 덧붙이는(append) 방식으로 기록되며, 각 로그 레코드는 고유의 식별자를 가진다.

  • 로그 레코드의 식별자

    • LSN(Log Sequence Number) 혹은 LSA(Log Sequence Address)

  • 로그는 항상 뒤에 덧붙이는 방식으로 쓰이기 때문에, 로그 식별자는 단조 증가하는 성질을 가진다.

  • 로그 데이터는 기록할 오브젝트의 타입에 따라서 물리적/논리적 로깅으로 분류할 수 있고, 데이터베이스의 상태 또는 변화를 야기한 전이를 기록하느냐에 따라서 분류할 수 있다.

8. 트랜잭션 격리 수준(Transaction Isolation Level)

Isolation level

• 트랜잭션에서 일관성 없는 데이터를 허용하도록 하는 수준

• Isolation level의 필요성

  • 데이터베이스는 ACID 특징과 같이 트랜잭션이 독립적인 수행을 하도록 한다.

  • 따라서 Locking을 통해, 트랜잭션이 DB를 다루는 동안 다른 트랜잭션이 관여하지 못하도록 막는 것이 필요하다.

    • 하지만 무조건 Locking으로 동시에 수행되는 수많은 트랜잭션들을 순서대로 처리하는 방식으로 구현하게 되면 데이터베이스의 성능은 떨어지게 될 것이다.

    • 그렇다고 해서, 성능을 높이기 위해 Locking의 범위를 줄인다면, 잘못된 값이 처리될 문제가 발생하게 된다.

    • 따라서 최대한 효율적인 Locking 방법이 필요함!

Isolation level 종류

1. Read Uncommitted (레벨 0)

• SELECT 문장이 수행되는 동안 해당 데이터에 Shared Lock이 걸리지 않는 계층

• 트랜잭션에 처리중이거나, 아직 Commit되지 않은 데이터를 다른 트랜잭션이 읽는 것을 허용함

• 사용자1이 A라는 데이터를 B라는 데이터로 변경하는 동안 사용자2는 아직 완료되지 않은(Uncommitted) 트랜잭션이지만 데이터B를 읽을 수 있다

• 데이터베이스의 일관성을 유지하는 것이 불가능함

2. Read Committed (레벨 1)

• SELECT 문장이 수행되는 동안 해당 데이터에 Shared Lock이 걸리는 계층

• 트랜잭션이 수행되는 동안 다른 트랜잭션이 접근할 수 없어 대기하게 됨

• Commit이 이루어진 트랜잭션만 조회 가능

• SQL 서버가 Default로 사용하는 Isolation Level임

• 사용자1이 A라는 데이터를 B라는 데이터로 변경하는 동안 사용자2는 해당 데이터에 접근이 불가능함

3. Repeatable Read (레벨 2)

• 트랜잭션이 완료될 때까지 SELECT 문장이 사용하는 모든 데이터에 Shared Lock이 걸리는 계층

• 트랜잭션이 범위 내에서 조회한 데이터 내용이 항상 동일함을 보장함

• 다른 사용자는 트랜잭션 영역에 해당되는 데이터에 대한 수정 불가능

4. Serializable (레벨 3)

• 트랜잭션이 완료될 때까지 SELECT 문장이 사용하는 모든 데이터에 Shared Lock이 걸리는 계층

• 완벽한 읽기 일관성 모드를 제공함

• 다른 사용자는 트랜잭션 영역에 해당되는 데이터에 대한 수정 및 입력 불가능

선택 시 고려사항

• Isolation Level에 대한 조정은, 동시성과 데이터 무결성에 연관되어 있음

• 동시성을 증가시키면 데이터 무결성에 문제가 발생하고, 데이터 무결성을 유지하면 동시성이 떨어지게 됨

• 레벨을 높게 조정할 수록 발생하는 비용이 증가

낮은 단계 Isolation Level을 활용할 때 발생하는 현상들

• Dirty Read

  • 커밋되지 않은 수정중인 데이터를 다른 트랜잭션에서 읽을 수 있도록 허용할 때 발생하는 현상

  • 어떤 트랜잭션에서 아직 실행이 끝나지 않은 다른 트랜잭션에 의한 변경사항을 보게되는 경우

• Non-Repeatable Read

  • 한 트랜잭션에서 같은 쿼리를 두 번 수행할 때 그 사이에 다른 트랜잭션 값을 수정 또는 삭제하면서 두 쿼리의 결과가 상이하게 나타나는 일관성이 깨진 현상

• Phantom Read

  • 한 트랜잭션 안에서 일정 범위의 레코드를 두 번 이상 읽었을 때, 첫번째 쿼리에서 없던 레코드가 두번째 쿼리에서 나타나는 현상

    • 트랜잭션 도중 새로운 레코드 삽입을 허용하기 때문에 나타나는 현상임

9. 레디스(Redis)

참고

• 빠른 오픈 소스, 인 메모리(In-memory), 키-값 데이터 구조 스토어

• 보통 데이터베이스는 하드 디스크나 SSD에 저장한다. 하지만 Redis는 메모리(RAM)에 저장해서 디스크 스캐닝이 필요없어 매우 빠른 장점이 존재함

• 캐싱도 가능해 실시간 채팅에 적합하며 세션 공유를 위해 세션 클러스터링에도 활용된다.

• RAM은 휘발성 아닌가요? 껐다키면 다 날아가는데..

  • 이를 막기위한 백업 과정이 존재한다.

    • snapshot : 특정 지점을 설정하고 디스크에 백업

    • AOF(Append Only File)

      • 명령(쿼리)들을 저장해두고, 서버가 셧다운되면 재실행해서 다시 만들어 놓는 것

• 데이터 구조는 key/value 값으로 이루어져 있다.

  • 따라서 Redis는 비정형 데이터를 저장하는 비관계형 데이터베이스 관리 시스템이다

캐싱

• 서비스 사용자가 증가했을 때, 모든 유저의 요청을 DB 접근으로만 처리하게 된다면 DB 서버에 무리가 갈 수 밖에 없다. 물론 데이터베이스는 데이터를 디스크에 저장하기 때문에 서버의 장애와는 별개로 데이터를 유지할 수는 있지만, 요청이 증가하는 상황에서는 기존 성능을 기대하기 힘들다.

  • 이런 맥락에서 캐시는 나중에 요청된 결과를 미리 저장해두었다가 빨리 제공하기 위해 사용한다.

• 보통 우리가 사용하는 Redis Cache 는 메모리 단 (In-Memory) 에 위치한다. 따라서 디스크보다 수용력(용량) 은 적지만 접근 속도는 빠르다.

• 일반적인 패턴 : Look aside cache

  1. 웹 서버는 클라이언트 요청을 받아서, 데이터가 존재하는지 캐시를 먼저 확인한다.

  2. Cache 에 데이터가 있으면 그걸 꺼내주는데, 만약 없으면

  3. DB 에서 읽어서 -> 먼저 캐시에 저장한다음 클라이언트에게 데이터를 돌려준다.

• Write Back

  • 데이터를 캐시에 전부 먼저 저장해놓았다가 특정 시점마다 한번씩 캐시 내 데이터를 DB insert 하는 방법이다.

  • insert 를 1개씩 500번 수행하는 것보다 500개를 한번에 삽입하는 동작이 훨씬 빠름에서 알 수 있듯, write back 방식도 성능면에서 뒤쳐지는 방식은 아니다.

    • 하지만 어쨌든 여기서 데이터를 일정 기간동안은 유지하고 있어야 하는데, 이때 이걸 유지하고 있는 storage 는 메모리 공간이므로 서버 장애 상황에서 데이터가 손실될 수 있다는 단점이 있다.

    • 그래서 다시 재생 가능한 데이터나, 극단적으로 heavy 한 데이터에서 write back 방식을 많이 사용한다.

value 5가지

1. String (text, binary data) - 512MB까지 저장이 가능함

2. set (String 집합)

3. sorted set (set을 정렬해둔 상태)

4. Hash

5. List (양방향 연결리스트도 가능)