Introduction

This paper is bonus notes I have researched about database. It was written sometime in Spring 2021.

트랜잭션의 4대 특징

1. Atomicity 원자성 원자성이란, 데이터의 변동은 하나의 액션으로 취급해야 하는 개념이다. 즉, 부분적인 변동은 허용하지 않는다. 예를 들어, 소비자와 사업자 사이의 물물교환 과정에서 원자성이 성립한다고 가정한다면, 소비자의 계좌에서 돈이 빠지는 만큼 동시에 사업자의 계좌에 동일한 양의 돈이 들어온다.
2. Consistency 일관성 일관성이란, 데이터의 변동 전후에도 기존 데이터의 습성이 동일해야 하는 개념이다. 위 예시를 다시 가져와서 설명하자면, 소비자와 사업자의 계좌가 잔액이 무조건 0보다 커야 한다는 습성과 각각 일일 허용 이체 금액이 300만원, 1000만원이라고 가정하자. 일관성이 성립한다고 가정하면, 둘의 물물교환 이후에도 각각의 계좌가 습성을 잃지 않아야 한다. 하지만 주의할 것은, 이 특성이 트랜잭션의 결과가 옳다는 것을 말해주진 않는다.
3. Isolation 고립성 고립성이란, 각 트랜지션은 독립적이다 라는 개념이다. 서로 영향을 미치지 못하고 각 트랜지션은 프롬트된 순서대로 시행된다. 이 개념이 필요한 이유는 트랜잭션들은 평소에 동시다발적으로 프롬트될 경우가 많기 때문이다. 동시다발적으로 프롬트 되는 경우나 순서대로 프롬트되는 경우나 동일한 결과를 가져와야 한다는 확신이 있어야 하기 때문에 이 특성이 중요한 것이다. 즉, 트랜잭션들을 병행적으로 처리해야 한다는 의미이다. 예를 들면, 사업자와 소비자가 물물교환은 2차례에 걸쳐 한다고 치자. 첫 교환에는 금액이 10만원, 두번째 교환에는 금액이 20만원 오가는 상황에서 첫교환 도중에 오래 걸려 두번째 교환을 먼저 하는 경우가 발생해선 안된다.
4. Durability 영구성 영구성이란, 완료된 트랜잭션은 영구적이다 라는 개념이다. 파워 공급이 되지 않거나 네트워크 에러가 발생하여도 트랜잭션이 적용된 값들은 변하지 않는다. 즉, 처리된 트랜잭션 후의 데이터 값들은 비휘발성 메모리에 저장된다. 예를 들면, 사업자와 소비자의 물물교환이 이루어진 후에 은행 시스템이 다운된다고 해서 둘의 계좌 금액이 이전으로 바뀌지는 않는다는 것과 같다.

• 출처 https://www.ibm.com/docs/en/cics-ts/5.4?topic=processing-acid-properties-transactions https://en.wikipedia.org/wiki/ACID https://en.wikipedia.org/wiki/Isolation(databasesystems) https://www.stevenjlee.net/2020/06/26/%EC%9D%B4%ED%95%B4%ED%95%98%EA%B8%B0-%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4-%ED%8A%B8%EB%9E%9C%EC%9E%AD%EC%85%98%EC%9D%98-%EA%B8%B0%EB%B3%B8-%EC%86%8D%EC%84%B1-acid-%EA%B7%B8/

MySQL 인덱스 종류

1. B-tree 데이터베이스에서 가장 흔히 사용되는 인덱스의 일종으로 데이터를 트리 구조로 정렬한 뒤에 탐색을 하여 쿼리에 더 빨리 접근한다. 이진트리와는 다른 점은 각 노드 별로 자녀 노드가 2개로 한정되어 있지 않다는 점이다. 따라서 이진탐색과는 탐색의 높이가 다르다. B-tree 는 가장 많이 사용되는 인덱스인 만큼 왠만한 스토리지 엔진 ( InnoDB, MyISAM, Memory, Heap, NDB … ) 에서 가능하다. Memory 스토리지 엔진에서도 가능하면서 range operators ( BETWEEN, greater than … ) 를 사용하는 경우에는 hash index 보단 B-tree 를 더 선호한다. B-tree 를 응용한 B+tree 도 존재한다. 전자와 후자의 다른 점은 후자는 잎 노드끼리 연결이 되어 있어 풀스캔이 가능하다는 점이다. 그리고 내부 노드에 값만 저장이 되어 있고 포인터는 안되어 있다. 메모리 저장, 처리 속도 측면에서 전자에 비해 효율적이락 볼 수 있다. 사용가능한 스토리지 엔진은 전자와 동일하다.
2. R-tree 다차원적인 데이터 ( 지리, 도형, … ) 을 인덱싱하기 위해 사용되는 트리 구조이다. InnoDB 와 MyISAM 스토리지 엔진에서 사용이 가능하면서 SPATIAL 컬럼을 통해 인덱스를 만들 수 있고, 기능이 제공되지 않는 스토리지 엔진에서는 동일한 키워드로 B-tree 를 만든다.
3. hash index B-tree 와는 달리 equality operators ( Equal, Not Equal ) 를 사용하는 쿼리를 주로 처리하는 인덱스이고 Memory ( 또는 Heap, NDB ) 스토리지 엔진에서 가능하면서도 디폴트 인덱스이다. InnoDB 에서도 비슷한 원리의 인덱스를 사용할 수 있다. 이때는 adaptive hash index 를 테이블에 자동적으로 추가하여 equality operators 를 활용한 쿼리를 더욱 신속하게 처리할 수 있게 된다.
4. FULLTEXT like operator 와 비슷하게 어느 단어나 어절을 조건으로 탐색하기에 유용한 인덱스이다. 일치의 정도를 알고리즘으로 조절할 수 있기에 like operator 보다 훨씬 유연하다. 원래는 MyISAM 에서만 사용할 수 있는 기능이었지만 MySQL 5.6.4 부터는 InnoDB 에서도 사용이 가능하다.

• 출처 https://dev.mysql.com/doc/refman/8.0/en/glossary.html https://www.vertabelo.com/blog/all-about-indexes-part-2-mysql-index-structure-and-performance/#:~:text=Index%20Types%20in%20MySQL%2C%20MyISAM,HASH%20index%20structure%20by%20default.

와일드카드 (like) 기반 검색에 대한 서술 및 약점

와일드카드 기반 검색

like 기반 검색은 데이터가 특정 패턴을 지니고 있느냐 를 조건 삼아 처리하는 쿼리의 일종이다. MySQL 은 이 검색에 와일드카드를 총 2가지 제공한다.

• % : 0 이상의 string character 을 매치한다.
• _ : 오직 한개의 string character 을 매치한다.

아래 예시와 같이 활용된다.

SELECT
    employeeNumber,
    lastName,
    firstName
FROM
    employees
WHERE
    firstName LIKE 'a%';
# Fistname 이 Andy, Anthony 등의 row 를 return 한다.

SELECT
    employeeNumber,
    lastName,
    firstName
FROM
    employees
WHERE
    firstname LIKE 'T_m';
# Fistname 이 Tom, Tim 등의 row 를 return 한다.

약점

가장 크게는 보안 상의 약점이 존재한다. 예를 들어보면, 앱 또는 웹의 링크가 존재한다고 가정하자.

www.example.co.kr/userphoto.php

이때 SQL 쿼리를 아래 전자와 같이 처리된다고 하자.

www.example.co.kr/userphoto.php?name=john
-> SELECT text FROM table WHERE fruit LIKE ‘$name’
-> SELECT text FROM table WHERE fruit=‘$name’

이런식으로 처리되는 사이트라면 아래와 같이 검색할 경우에 어떤 상황이 발생할 지는 모두 예상이 가능하다.

http://www.example.com/userphoto.php?name=a%
http://www.example.com/userphoto.php?name=b%
http://www.example.com/userphoto.php?name=c%

이런 부분을 이용하여 다른 사람의 계정에 로그인할 수 있는 가능성이 발생한다. 또는 다른 사람의 sessionId 나 token 을 가져올 수 있는 가능성도 생긴다. 마지막으로 regular expression 을 다양하게 사용하여 지속적인 쿼리를 통해 사이트를 다운 시킬 수도 있다.
이러한 문제를 방지하기 위해선 개발자는 인풋이나 아웃풋에 제한을 걸어두어야 한다. 인풋 캐릭터 validation, common phrase limitation, response limitation 등의 룰을 적용하여 약점을 보와할 수 있다.

• 출처 https://www.mysqltutorial.org/mysql-like/ https://www.tothenew.com/blog/sql-wildcards-is-your-application-safe/

오라클의 데이터타입과 MySQL의 데이터타입에 대해서 정리 및 비교

• 출처 https://www.xplenty.com/blog/oracle-vs-mysql/ https://www.mysqltutorial.org/mysql-data-types.aspx https://m.blog.naver.com/PostView.nhn?blogId=juyoung_g&logNo=220531910814&proxyReferer=https:%2F%2Fwww.google.com%2F

오라클의 조인타입에 대한 정리 ( 표준 조인과의 연관성 )

SQL 표준 조인이란, 한 개 이상의 테이블로 부터 한개 이상의 컬럼들을 결합하여 새로운 테이블을 만들는 것이다. 표준 조인은 총 4가지로 크게 나눌 수 있다.

• Inner Join
• Self Join
• Cross Join
• Outer Join

각 조인에 대해서 알아보고 오라클의 조인에서는 어떤 방식으로 implement 되어 있는 지 확인해보자.

1. INNER JOIN 가장 흔한 조인의 타입이자 SQL 의 디폴트형 조인이다. 공통된 컬럼을 기준으로 두 테이블의 튜플을 맞붙인 테이블을 도출한다. Inner Join 에는 총 2가지 세부 조인이 존재한다.

   • Equi Join
   • Theta Join

   Equi Join 은 = 연산자를 사용한 조인이고 Theta Join 은 > 와 같은 non-equality 연산자를 조건문으로 활용한 조인이다. 오라클에서는 이에 해당하는 조인은 INNER JOIN 이다. SELECT * FROM T1 INNER JOIN T2 로 조인문을 시행하고 ON 또는 USING 연산자를 조건과 함께 사용하여 Equi 또는 Theta 조인을 실행시킨다. 추가적으로, 오라클에는 Equi Join 인 NATURAL JOIN 이 존재한다. NATURAL JOIN 과 INNER JOIN 의 다른 점은 전자는 ON 과 같은 조건을 넣어주지 않은 조인이라는 것이다. 또한 결과물을 출력할 때, 공통된 컬럼은 피해서 출력한다.

2. SELF JOIN 하나의 테이블 내에서 형태가 다른 레코드를 참조하기 위한 조인이다. 이땐, JOIN 을 사용하지 않고 한 테이블에서 변수를 두개 가져와 SELECT t1.column_name t2.column_name FROM T1 t1, T1 t2형식의 조인문을 사용한다. 뒤에는 WHERE 또는 ON 과 같은 SELECT 조건문을 활용하여 컬럼을 구체화한다.
3. CROSS JOIN 두 테이블을 카테시안 곱한 결과를 출력한다. 두 테이블의 가능한 모든 조인된 테이블을 return 생성한다. 다른 조인 연산자들과는 달리, ON 으로 컬럼의 조건문 을 지정할 수 없다. 오라클에서도 동일하게그 대신 다른 조인과 동일하게 SELECT 의 연산자인 WHERE 절을 통해 결과물 테이블을 구체화할 수 있다.

4. OUTER JOIN Inner Join 은 공통된 컬럼의 매치되는 튜플만 출력하지만 Outer Join 은 매치가 되지 않는 튜플도 return 해준다. 총 3가지의 세부 조인이 있다.

   • Left
   • Right
   • Full

   각 조인은 말 그대로 두 테이블의 위치에 따라 순서대로 매치가 되지 않는 튜플을 모두 출력한다. 예를 들면, Left Join 이면 왼쪽에 배치된 테이블의 매치가 되지 않은 튜플까지 모두 출력해주는 조인이다. 오라클에서는 3개의 조인이 모두 제공되며, SELECT T1.column_name, T2.column_name FROM T1 RIGHT (또는 LEFT 또는 FULL) OUTER JOIN T2 와 같이 사용된다. 여타 조인들과 동일하게 ON 으로 조건문을 넣어준다.

• 출처 https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj29840.html https://www.sqlshack.com/sql-join-overview-and-tutorial/ https://stackoverflow.com/questions/8696383/difference-between-natural-join-and-inner-join

DynamicSQL과 StaticSQL의 차이점

Static SQL 은 런타임 도중에 바뀌지 않는 SQL 문이지만 Dynamic SQL 은 도중에 바꾸는 SQL 문이다. 즉, Dynamic SQL 을 사용하는 경우에는 쿼리가 작동하는 중간에 진입할 수 있다는 말이다. 그러므로 하드코딩된 SQL 문일 수가 없다. 둘은 크게 속도에서 차이가 보인다. Static 인 경우가 당연히 빠른데, 그 이유는 미리 코딩이 되어 있기 때문이다. SQL 문이 돌아가기에 최적화 되어 있다는 뜻이다. 반대로 Dynamic 인 경우는 전자보단 유연하게 상황에 대처할 수 있지만 비효율적일 수 있다. 추가적으로, 시스템 상의 보안으로 인해 Dynamic 인 경우에는 사용할 수 없는 명령문이 몇가지가 존재한다.

• 출처 https://www.informit.com/articles/article.aspx?p=1330223&seqNum=2#:~:text=Static%20SQL%20is%20SQL%20statements,to%20enter%20their%20own%20queries. https://www.geeksforgeeks.org/difference-static-dynamic-sql/

MySQL의 쿼리최적화(Optimizer) 방법 및 힌트

MySQL Optimizer 은 SQL 쿼리문을 최적화하는 플랜을 찾는 것이 목표이다. 사용자는 Optimizer 의 두가지의 요소를 고려하여 최적의 플랜을 찾는 시스템을 조정할 수 있다.

• optimizer_prune_level 을 통해 몇개의 플랜을 스킵할 것인지 지정할 수 있다. 이때, 주의해야할 점은 너무 많은 플랜을 스킵하게 되면 최적의 방법을 찾기 힘들 것이고, 너무 적은 플랜을 스킵하게 되면 쿼리문이 굉장히 오래 걸릴 것이다.
• optimizer_search_depth 을 통해 각 플랜의 탐색의 깊이를 지정할 수 있다. 만약 탐색의 깊이가 너무 크다면 각 플랜을 evaluate 하는 과정이 너무 오래 걸릴 수 있기 때문에 이 변수도 적절한 값을 찾는 것이 중요하다.

Optimizer 사용자화에는 시스템 컨트롤이라는 것이 있는데 플랜의 최적화를 위한 조정과는 다른 개념이다. Optimizer이 모르는 데이터의 특성을 개발자만이 알 수 있는 경우가 존재한다. 그러기 위해선 어느 데이터는 특정 기준을 토대로 플랜을 짜야 한다는 지시를 내려주어야 한다. 이때 Optimizer 시스템을 컨트롤할 수 있는 방법이 있는데, optimizer_switch 와 optimzier hints 로 가능하다. 힌트는 각 쿼리마다 전략을 구체적으로 지정할 수 있다. /*+ ... */ 내부에 써야 힌트로 인식이 된다. SELECT, UPDATE, INSERT, REPLACE, and DELETE 와 같은 명령문 뒤에 코딩해야 한다.

Optimizer 힌트는 총 4가지의 레벨에 적용된다.

* Global: 전체 명령문에 적용

* Query block: 특정한 쿼리문에 적용

* Table-level: 특정 쿼리문에 있는 테이블에 적용

* Index-level: 특정 테이블에 있는 인덱스에 적용

범위 (레벨) 에 따라 사용할 수 있는 힌트는 차트로 documentation 에 정리되어 있다. 각 레벨에 따라 사용되는 Syntax 의 룰도 다르다. 가장 많이 쓰이는 힌트 중 하나는 조인 오더를 modify 해주는 조인 오더 쿼리최적화 힌트이다. 조인이 시행될 때 각 테이블의 플랜을 지정할 수 있고, 두 테이블의 조인 오더도 지정할 수 있다. 조인을 통한 쿼리문을 신속하게 처리할 때 유용하게 쓰인다.

• 출처 https://dev.mysql.com/doc/refman/8.0/en/controlling-query-plan-evaluation.html https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html https://severalnines.com/database-blog/optimizer-hints-faster-mysql-query-execution