#devspace

6장. SQL 문의 기본

SELECT 문의 이해

1. SELECT
2. INSERT, UPDATE, DELETE
3. VIEW
4. SUBQUERY
5. JOIN
6. 체크포인트

🧐 SQL을 왜 사용할까?

요즘은 ORM으로 작업을 많이 하니까 SQL.. 자세히 몰라도 되는거 아닐까? SQL을 알아야 하는 이유는 무엇일까?

❗️ ORM은 SQL을 쉽게 사용하기 위하여, 접근성을 위해 만들어진 하나의 도구

• ORM이 왜 나오게 되었는지
• SQL 동작원리는 어떻게 된건지
• ORM이 없을 때는 어떻게 해야하는지

제일 중요한 것은 가장 기본적인 것, DB에 직접 접근해서 데이터를 열람하거나 필터링 할 수 있어야 합니다.

SELECT 문

• MySQL CLI를 실행하여 로그인을 한 후

• MySQL 서버에 있는 데이터베이스 목록 표시

mysql> show databases;

• 데이터베이스 사용

mysql> use DB명;

• 해당 데이터베이스의 테이블 목록 표시

mysql> show tables;

연산자 우선순위(MySQL)

SQL의 기초적인 기술 규칙

• SQL 문의 마지막에 딜리미터를 붙인다 (대부분 세미콜론)
• 키워드는 case-sensitive 하지 않다 (select와 SELECT는 같다)
• 정수는 그대로 쓴다. 문자열, 날짜 및 시각은 작은 따옴표로 감싼다
• 단어는 반각 스페이스나 개행으로 구별한다

DISTINCT

선택한 행에서 중복된 값이 있고 이를 없애려고 하는 경우

SELECT DISTINCT 컬럼명 FROM 테이블명 WHERE 조건;

ORDER BY

SELECT ~ FROM 테이블명 ORDER BY 정렬키1[, 정렬키2, ... DESC/ASC];

• 행의 순서를 확실히 같게 하려면 행의 정렬키를 unique하게 정해야 함
  • 정렬키가 같은 값의 행이 복수 개 존재한다면 그 행들의 순서는 일정하지 않기 때문
• 기본값 : ASC(오름차순)

집약함수

기본적으로 NULL을 제외하고 집계

COUNT 함수만은 'COUNT(*)'로 표기(NULL을 포함한 전체 행 집계)

• COUNT 테이블 행수
• SUM 테이블 수치 데이터 합계
• AVG 테이블 수치 데이터 평균
• MAX 테이블 임의열 데이터 중 최대값
• MIN 테이블 임의열 데이터 최소값
• (MySQL only) GROUP_CONCAT 문자열 결합, 콤마로 구분

# GROUP_CONCAT과 DISTINCT로 중복 회피
# 중복이 없어지고 행정구역이 1회만 조회
mysql> SELECT GROUP_CONCAT(DISTINCT district) FROM CITY WHERE COUNTRYCODE='KOR';

GROUP BY

데이터를 몇 개의 그룹으로 나눠서 집약하는 것

SELECT ~ FROM 테이블명 GROUP BY 컬럼명1 [, 컬럼명2, ...];

• GROUP BY로 지정한 열 : 집약 키, 그룹화 키
• 복수 열을 콤마로 구분해 지정

HAVING

그룹마다 집약한 값을 조건으로 선택하는 것

SELECT ~ FROM GROUP BY ~ HAVING 그룹의 값에 대한 조건;

작성 순서

1. SELECT
2. FROM
3. WHERE
4. GROUP BY
5. HAVING
6. ORDER BY

실행 순서

1. FROM
2. ON, WHERE
3. JOIN, GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
7. LIMIT (MySQL) / ROWNUM (Oracle)

UPDATE, INSERT, DELETE 문

UPDATE

UPDATE 테이블명 SET 컬럼명1=값1[,컬럼명2=값2,...] WHERE 조건;

• 갱신하는 열에 디폴트 값이 있는 경우 : 값 대신 'DEFAULT'  키워드를 지정하면 기본값으로 갱신됨

INSERT

• INSERT는 행 단위로 수행되므로 테이블 정의를 정확히 확인 후 실행되어야 함
  • \G는 ; 대신에 사용할 수 있는 딜리미터로 결과를 세로로 보기 쉽게 함

SHOW CREATE TABLE 테이블명\G

# 테이블 정의 자체가 아닌 단순히 열 정보 조회용 (Oracle 호환)
DESC 테이블명;

• INSERT문

# 기본
INSERT INTO 테이블명(컬럼1[,컬럼2,...]) VALUES (값1[,값2,...]);

# Multi row insert (MySQL)
INSERT INTO city (name, code, district) VALUES ('Gimpo', 'KOR', 'Kyonggi'), 
('Seongnam', 'KOR', 'Kyonggi'), ('Hwaseong', 'KOR', 'Kyonggi');

# 자주 사용되는 구문
INSERT INTO 테이블1 SELECT FROM 테이블2; # SELECT문의 결과 값을 레코드로 입력

DELETE

DELETE FROM 테이블명 WHERE 조건;

VIEW

가상의 테이블, 접근이 허용된 데이터만 보여줄 수 있음(테이블과 동일하지만 테이블과 같은 데이터는 가지고 있지 않음)

VIEW 언제 쓸까? VIEW의 이점

설계를 변경할 수 없을 때 뷰로 데이터를 간소화하자!

1. 편리성 : 복잡한 SELECT 문을 일일이 매번 기술할 필요가 없다. 데이터 저장 없이(기억장치의 용량을 사용하지 않고) 실현할 수 있다.
2. 보안성 : 필요한 열과 행만 사용자에게 보여줄 수 있다. 민감한 컬럼은 마스킹하기
3. 독립성 : 기존 테이블 구조가 변경되면 뷰도 함께 변경, 갱신 시에도 뷰 정의에 따른 갱신으로 한정할 수 있다. 뷰를 제거(DROP VIEW)해도 참조하는 테이블은 영향을 받지 않는다.

VIEW 작성

CREATE VIEW 뷰이름(열명1[, 열명2, ...]) AS SELECT문;

VIEW 입력, 갱신 제한

• 갱신 불가
  • 어떤 행이 대응하는지 모르거나 어떤 값을 넣으면 좋을지 모르는 경우
• 삽입 불가
  • 2가지 이상 테이블을 조합해 작성한 뷰를 갱신할 때 어느 테이블을 갱신하면 좋을지 알 수 없는 경우
  • 선택된 열 이외의 열에 기본값도 없고 NULL도 허용되지 않는 상황 (해당 열에 넣을 수 있는 값이 없는 경우)

SUBQUERY

하나의 SQL 문에 포함되어 있는 또 다른 SQL 문

주의사항

1. 괄호로 감싸서 사용할 것
2. ORDER BY를 사용하지 못한다
3. 단일 행 또는 복수 행 비교 연산자와 함께 사용할 수 있다

SELECT		-- 스칼라 서브쿼리
FROM		-- 인라인 뷰
WHERE		-- 중첩 서브쿼리 등
HAVING		-- 중첩 서브쿼리 등
ORDER BY	-- 스칼라 서브쿼리


-- 테이블2의 정보를 뽑아서 그 데이터를 테이블1에 넣어준다.
-- value()들어갈 자리를 서브쿼리로 대체 했다.
INSERT INTO table1 (SELECT * FROM table2);


-- 인턴의 정보를 구해와서 삭제한다. 
DELETE FROM employee 
WHERE id = (SELECT id FROM employee where office_worker = '인턴' );


-- 인턴에 정보를 구해와서 급여를 10만원 인상한다.
UPDATE employee SET salary=(salary+100000)
WHERE id = (SELECT id FROM employee where office_worker = '인턴' );

• 스칼라 서브쿼리 : 한 행만 반환
• 인라인 뷰 : SQL문이 실행될 때만 생성되는 뷰이기 때문에 DB에 저장되지 않음 (동적, 임시적)
• 단일 행 서브쿼리 : =, <, <=, >, >=, <> 연산자로 얻은 서브쿼리 결과 1개 이하 행을 반환
• 복수 행(중첩) 서브쿼리 : IN, ANY, ALL, EXISTS 등의 연산자로 얻은 서브쿼리 결과 여러개 행을 반환

JOIN

2개 이상의 테이블을 결합하여 쿼리를 실행하는 것

ON 을 이용해서 결합조건을 지정해주어야 함

INNER JOIN

ON 으로 지정한 결합 조건에 일치하는 행만을 2개의 테이블로부터 가져오는 것

SELECT 선택할 열 목록 FROM 테이블1 INNER JOIN 테이블2 ON 결합조건;

OUTER JOIN

한 쪽 테이블을 기준으로 전체 행을 표시하고 다른 테이블은 조건에 맞는 행이 있으면 표시하는 것

• 특별한 이유(쿼리 자동 생성으로 테이블1, 테이블2의 쿼리에서 순서를 변경할 수 없는 경우 등)가 아니면 LEFT OUTER JOIN 사용

SELECT 선택할 열 목록 FROM 테이블1 LEFT OUTER JOIN 테이블2 ON 결합조건;

SELECT 선택할 열 목록 FROM 테이블1 RIGHT OUTER JOIN 테이블2 ON 결합조건;

체크포인트 (SQL 코딩의 기술 참조)

데이터 필터링과 검색

• LIKE 제대로 쓰기
  • LIKE '%keyword%'처럼 전후방 모두 와일드카드를 사용하면 인덱스를 사용할 수 없다.
  • LIKE는 최대한 자세하게
• 일치하거나 누락된 레코드를 찾아낼 때
  • 이론적으로는 EXISTS가 NOT IN보다 빠름
  • 또는 LEFT JOIN을 사용하고 WHERE에서 NULL값을 찾는 Frustrated JOIN 사용하기

GROUP BY

1. GROUP BY 절의 작동 원리를 이해하자
   • 집계 함수를 하나도 사용하지 않을 때 GROUP BY 절은 SELECT DISTINCT와 동일하게 수행된다
   • 집계 수행 전 WHERE 절이 적용된다
   • GROUP BY 절은 필터링된 데이터 집합을 집계한다
   • HAVING 절은 집계된 데이터를 다시 필터링한다
   • ORDER BY 절은 변형된 데이터 집합을 정렬한다
   • SELECT 절에서 집계 함수나 집계 계산에 포함되지 않은 컬럼은 GROUP BY 절에 명시해야 한다

1. GROUP BY 절은 간단하게 만들자
   • HAVING 절의 진정한 힘은 한 그룹의 집계 결과를 다른 집계 값과 비교하는 능력에 있다.
   • 별칭으로 SELECT 절에서 계산을 수행하거나, HAVING 절에 사용할 수 없다. 해당 표현식을 그대로 재사용해야 한다. (?)
2. 복잡한 문제를 해결하려면 GROUP BY나 HAVING 절을 사용하자

SELECT SUM(col) AS total
GROUP BY ...
HAVING SUM(col) ...;

1. GROUP BY 절 없이 최댓값, 최솟값을 찾자

서브쿼리

1. 서브쿼리를 어디에 사용할 수 있는지 알아두자
   • 일반적으로 테이블 이름을 쓸 수 있는 곳이라면 어디에나 서브 쿼리를 이용할 수 있다. 
   • 테이블 서브쿼리: FROM, JOIN 등에 사용
   • 단일 컬럼 서브쿼리: IN, NOT IN 조건에 사용
   • 스칼라 서브쿼리

1. 연관성 있는 서브쿼리와 연관성 없는 서브쿼리의 차이점을 파악하자
2. 서브쿼리 대신 조인을 사용해 더 효율적인 쿼리를 작성하자

SELECT NAME
FROM BEERSTYLE
WHERE CountryFK IN (
    SELECT CountryID
    FROM Countries
    WHERE CountryNm = "Belgium"
);

SELECT S.NAME
FROM STYLE AS S
INNER JOIN Countries AS c
    ON S.CountryFK = C.CountryID
WHERE C.CountryNm = "Belgium";

조인

1. LEFT JOIN의 오른쪽 데이터를 올바르게 걸러내자

1. OUTER JOIN에서는 잘못된 결과를 내는 COUNT(*) 함수를 사용하지 말자 
   • '*'를 사용해 COUNT를 하면 row 자체를 카운트한다. 즉 모든 컬럼의 값이 NULL로 채워져 있어도 카운트가 이루어진다. 반면에 컬럼명을 사용해 COUNT를 하면 해당 컬럼의 값이 NULL이면 카운트하지 않는다. 
   • NULL 값이 있는 로우를 포함해 모든 로우의 개수를 세기 : COUNT(*)
   • 컬럼 값이 NULL이 아닌 로우의 개수만 세기 : COUNT(컬럼명)

• EXPLAIN, STRAIGHT JOIN(왼쪽 테이블부터 읽는 조인), SEMI JOIN(메인 쿼리 테이블과 서브쿼리 결과를 조인)
• 조인되어야 할 각 집합의 처리범위와 순서에 따라 영향을 받는다.
  • 가장 좁은 범위를 먼저 처리할수록 조인 효율은 증가

참고

https://mozi.tistory.com/233

https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-%EC%84%9C%EB%B8%8C%EC%BF%BC%EB%A6%AC-%EC%A0%95%EB%A6%AC

5장. DBMS를 조작할 때 필요한 기본 지식

조작하기 전에 알아두어야 할 것

⬇️ MySQL 설치방법 ⬇️

2023.01.18 - [인프라/NHN Cloud] - NHN Cloud 인스턴스에 MySQL 설치하기 (1) - 압축파일 이용

2023.01.18 - [인프라/NHN Cloud] - NHN Cloud 인스턴스에 MySQL 설치하기 (2) - Yum 이용

2023.01.18 - [인프라/NHN Cloud] - NHN Cloud 인스턴스에 MySQL 설치하기 (3) - 설정

MySQL과 커넥션 만들기

로그인

로그인 성공: MySQL이라는 DBMS에 사용자가 로그인해서 조작할 수 있게 된 상태

mysql> : 프롬프트, 사용자 명령을 입력 받을 수 있는 상태

커넥션

로그인해서 프롬프트가 표시되었다는 것

→ 로그인 전과 로그인 후로 사용자와 MySQL의 관계가 변했다

→ 사용자와 MySQL이 접속되었다 (=연결되었다)

→ 커넥션 (Connection)

• 커넥션이 유지된다 = 데이터베이스와 무언가 주고 받을 수 있다
• 커넥션이 성립되었음을 표시하는 문장

• connection id : MySQL이 커넥션에 붙인 번호
• MySQL은 동시에 여러 개의 커넥션을 유지하는 것이 가능
• 커넥션 번호로 사용자를 구분

• 커넥션의 상태를 조사하는 명령

mysql> show status like 'Thread_connected';

• Thread_connected : 커넥션(사용자)의 수

세션

커넥션 시작과 종료까지의 단위

생성 시점 : 커넥션이 확립된 뒤

커넥션과 세션은 1:1로 대응되고, 생성과 소멸 시점이 같기 때문에 잘 구분하지 않음

로그오프

• 커넥션 종료

mysql> quit
Bye

SQL과 관리 명령의 차이

관리 명령

• DBMS는 SQL문 이외에도 '관리 명령'이 있다
• DBMS가 정상적으로 동작하는지 감시하거나 DBMS가 이상한 동작을 할 때 문제 해결을 위해 정보수집을 하는 등의 용도
• 관리 명령의 종류나 문법을 DBMS에 따라 다르다 → DBMS 매뉴얼에 기재되어 있고, 레퍼런스도 있음
  • SQL문은 반드시 'SELECT, INSERT, DELETE, UPDATE' 중 하나의 단어로 시작
  • 이 외의 단어로 시작하면 관리 명령
• MySQL 관리 명령 예시

# 가동된 시점부터의 경과시간(초)
mysql> show status like 'Uptime';

# 실행한 SQL 문의 수
mysql> show status like 'Queries';

관계형 데이터베이스의 계층

인스턴스

• 물리적 개념, DBMS가 동작할 때의 단위
• OS 입장에서는 프로세스 / DBMS 입장에서는 서버 프로세스 또는 서버로 부름
• 인스턴스 아래에 복수 개의 데이터베이스 존재할 수 있음

데이터베이스

• 데이터베이스를 관리하는 기능의 집합체 + 계층을 표시하는 의미
• 데이터베이스 아래에는 복수의 스키마 존재

스키마

• 폴더, 틀
• 1개의 스키마 아래에 복수 개의 테이블 존재
• 스키마 별로 용도별로 나누거나 권한 관리 수행 가능

오브젝트

• 최하위 계층
• 구성 : 테이블, 인덱스, 저장 프로시저(다양한 함수나 처리를 일괄로 다룸) 등

DBMS별 계층 구조

• 3계층 : Oracle, MySQL
  • 데이터베이스와 스키마를 동일한 것으로 간주
  • Oracle은 4계층으로 되어 있지만, 인스턴스 아래에 한 개의 데이터베이스만 만들 수 있기 때문에 실질적으로 3계층
• 4계층 : SQL Server, DB2, PostgreSQL
  • ANSI 표준 SQL
• 구현 사양은 개발사의 의향에 따라 달라짐

4장. 데이터베이스와 아키텍처 구성

견고하고 고속의 시스템을 구축하기 위해

1. 데이터베이스 아키텍처
2. 클러스터링
3. 리플리케이션
4. 샤딩

데이터베이스의 아키텍처

💡 아키텍처란

시스템을 만들기 위한 물리 레벨의 조합

➡️ 하드웨어와 미들웨어의 구성 = 어떤 기능을 가진 서버를 준비하고 어떠한 저장소나 네트워크 기기와 조합할 것인지?

1. 역사와 개요

아키텍처의 역사

'아키텍처'라는 말은 추상적이어서 딱 들었을 때 이미지를 떠올리기가 쉽지 않다.

그래서 구체적인 시스템 구성을 예로 들어보려고 한다.

데이터베이스에 관한 아키텍처의 역사는 다음 3단계로 나누어진다.

1) Stand-alone (~1980년대)

2) 클라이언트/서버 (1990년대~2000년)

3) Web 3계층 (2000년~현재)

1) Stand-alone

• 특징
  • 데이터베이스가 동작하는 머신이 LAN이나 인터넷 등의 네트워크에 접속하지 않고 독립되어 동작하는 구성
  • DBMS와 애플리케이션의 소프트웨어는 같은 DB 서버에서 동작 → 사용자는 물리적으로 DB 서버가 설치된 장소까지 접근해야 함
• 장점
  • 구축이 간단해서 소규모 작업이나 테스트에 적합
  • 보안이 매우 높음 (아무도 만나지 않으면 전염병 감염의 위험이 낮은 것과 같음)
• 단점
  • 물리적으로 떨어진 장소에서 접근할 수 없다
  • 복수 사용자가 동시에 작업할 수 없다
  • 가용성이 낮다
  • 확장성이 부족하다 (서버가 성능의 한계에 달하는 경우 머신 자체 성능을 올리는 것외에 개선 수단이 없다)

2) 클라이언트/서버

• 특징
  • DB 서버 1대에 복수 사용자가 접속하는 구성
  • C/S, 클라 서버 또는 2계층 구성이라고 부르기도 함
• 확장
  • 기업이나 조직 내 닫힌 네트워크에서 이용되어 외부를 거쳐서 접속하는 일은 없었음
  • 조직 내에서 제한된 용도의 시스템으로 이용됨
  • 인터넷 시대에는 결정적으로 부적합한 몇 가지 단점을 가지고 있음
• 단점
  • 보안 위험 : 인터넷에서 직접 DB에 접속하기 때문에
  • 관리비용 문제 : 클라이언트마다의 환경을 고려해서 애플리케이션을 배포하기 어려움 → Web 3계층 제시됨

3) Web 3계층

• 웹 서버 + 애플리케이션 서버 + 데이터베이스 서버
• 웹 서버
  • 클라이언트로부터 HTTP 요청을 받아서 애플리케이션 서버로 넘기고 결과를 클라이언트에 반환
  • Apache, IIS
• 애플리케이션 서버
  • 비즈니스 로직을 구현한 애플리케이션이 동작
  • 웹 서버와 연계된 요청을 처리하고 필요하면 DB 서버에 접속해서 데이터 추출하고 결과를 웹 서버로 반환
  • Tomcat, WebLogic, WebSphere
• 특징
  • 사용자로부터 직접적인 접속 요청 → only 웹 서버 : 애플리케이션과 데이터베이스 계층의 보안이 높아짐
  • 애플리케이션 계층에 비즈니스 로직 집중 → 애플리케이션 관리 비용이 낮아짐
  • 현재 웹 시스템에서의 표준

2. 가용성과 확장성의 확보

🧐 어떻게 하면 정지하지 않는 시스템을 만들 수 있을까?

아키텍처 설계에서 견고한 시스템을 만들기 위해 가장 중요한 점은 가용성 이다.

가용성을 높이는 2가지 전략

• 심장전략 : 시스템을 구성하는 각 컴포넌트의 신뢰성을 높여 장애 발생률을 억제해서 가용성을 높임 (고품질-소수전략)
• 신장전략 : '사물은 언젠가 망가진다'는 전제로 여분을 준비해 둠, 물량작전 (저품질-다수전략)
• 관계
  • 현재는 신장전략 노선 위주, FT 서버 등에서는 심장전략 이용 중
  • 심장전략과 신장전략은 중첩되며, 심장전략도 사실은 신장전략에 의해 실현되는 중

💡 클러스터란

• 동일한 기능의 컴포넌트를 복수 개 준비해 한 개의 기능을 실현
• 신장전략처럼 동일한 기능의 컴포넌트 병렬화하는 것 → 클러스터링
• 클러스터 구성으로 시스템의 가동률을 높이는 것 → 여유도(Redundancy)를 확보한다, 다중화

예를 들어, 어떤 서버의 고장률이 10%라고 하자.

시스템 전체 장애 발생률은 전 서버가 동시에 고장났을 때 한하기 때문에

서버가 증가할수록 장애 발생률은 아래와 같이 줄어든다.

• 서버 1대 = 10% (0.1)
• 서버 2대 = 1% (0.1 * 0.1)
• 서버 3대 = 0.1% (0.1 * 0.1 * 0.1)

💡 가동률 = 100% - 장애 발생률

💡 단일 장애점이란

SPOF(Single Point of Failure), 다중화되어 있지 않아서 시스템 전체 서비스의 계속성에 영향을 주는 컴포넌트

쇠사슬의 강도는 가장 약한 고리의 강도로 결정된다 = 단일 장애점의 신뢰성이 시스템의 전체의 가용성을 결정한다

신뢰성과 가용성

신뢰성 : 하드웨어나 소프트웨어 고장 빈도를 나타내는 개념 → 시스템을 구성하는 컴포넌트에 대해 

가용성 : 사용자 입장에서 볼 때 시스템을 어느정도 사용할 수 있는지 → 시스템 전체에서 사용자 눈높이에 맞춰 생각

→ 신뢰성이 낮은 컴포넌트를 사용한다 하더라도, 다중화한다면 시스템 전체의 가용성을 높일 수 있다.

DB 서버의 다중화 - 클러스터링

💡 다중화란

고가용성(HA)이라고 부르기도 하는데,

예를 들면 DB 서버 2대에서 1대가 고장이 나도 나머지 1대가 동작하도록 해서 서비스의 정지를 막는 것과 같다.

DB 서버의 다중화

DB 서버는 오랫동안 클러스터링이 어려운 컴포넌트로 인식됨

데이터를 보존하는 영속(Persistence) 계층이기 때문에

DB와 다른 서버의 차이

1. 웹 서버, 애플리케이션 서버 : 일시적인 처리
2. DB서버 : 대량의 데이터를 영구적으로 보존해야 하고 그에 따른 성능도 요구되어 필요 요건이 높음

DB서버(계산이나 업무 로직 처리)의 아키텍처는 저장소(데이터 보존)와 묶어서 생각

데이터는 항상 갱신되기 때문에 다중화를 유지하는 중에 데이터 정합성도 중요하게 의식해야 함

가장 기본적인 다중화

가장 간단한 다중화 구성 = DB 서버 여러개 + 저장소 1개

1) Active-Active

클러스터를 구성하는 컴포넌트를 동시에 가동

• 장점
  • 시스템 다운 시간이 짧음 : DB 서버 여러 대가 동시에 동작하고 있어서 1대가 다운되더라도 남은 서버가 처리를 계속해 시스템 전체가 정지하는 것을 방지
  • 성능이 좋음 : DB 서버 대수가 증가하면 동시에 가동하는 CPU나 메모리도 증가해서 성능도 향상됨 (저장소가 버틀넥이 되기 때문에 생각만큼 향상되지 않기도..)
  • Oracle RAC(Real Application Clusters), DB2 pureScale
• 단점 : 비싸다

💡 Heartbeat이란

Standby DB 서버에서 일정 간격으로 Active DB에 이상이 업는지 조사하기 위한 통신

2) Active-Standby

클러스터를 구성하는 컴포넌트 중 실제 가동하는 것은 Active, 남은 것은 대기(Standby)

• Cold-Standby : 평소에는 Standby DB가 작동하지 않다가 Active DB가 다운된 시점에 작동
• Hot-Standby : 평소에도 Standby DB가 작동하는 구성
  • 전환 시간이 짧은 만큼 라이선스료가 높음 (Active-Active보다는 저렴)

가용성과 성능이 좋은 순서

1. Active-Actvie
2. Active-Standby (Hot-Standby)
3. Active-Standby (Cold-Standby)

DB 서버와 데이터의 다중화 - 리플리케이션

💡 리플리케이션이란

DB 서버와 저장소 세트를 복수로 준비하는 것

• Active-Active, Active-Standby 클러스터는 서버는 다중화할 수 있지만, 저장소는 다중화하지 않는 단점을 보완
• 저장소도 내부 컴포넌트가 다중화되어 있지만(RAID), 리플리케이션은 재난 상황을 대응하기 위한 클러스터 구성임
  • DB서버와 저장소가 동시에 사용 불능일 때, 서비스를 지속하는 것이 가능하다는 점에서 가용성이 높은 아키텍처
• Oracle Data Guard, DB2 HADR, MySQL(부하 분산용에 가까움)

💡 RAID란

Redundant Array of Independent Disks, 저장소 내부의 컴포넌트를 다중화하는 기술

기본적으로 클러스터링과 동일하게 단일 장애점을 제거하여, 디스크를 병렬로 나열해 한개가 망가져도 데이터 소실이 발생하지 않도록 함

리플리케이션에서 주의할 점

• Active와 Standby 저장소의 데이터 정합성을 유지할 것
  • Active 측 저장소의 데이터 - 항상 사용자로부터 갱신됨 → Standby 측 데이터를 늘 최신화하지 않으면 정합성이 깨짐
  • 최신화를 위한 동기화를 일정 주기로 할 때, 갱신 주기를 얼마로 할 것인가와 성능 사이에 트레이드 오프 발생
• 구성을 피라미드형으로 (= 손자나 증손자 세트를 만드는 것)
  • 데이터가 오래 되어도 참조만 하면 된다는 처리를 손자나 증손자 세트에서 하여 부모에 걸리는 부하를 분산함

성능을 추구하기 위한 다중화 - Shared Nothing

💡 Share Disk란

• Active-Active 구성 DB = 여러대의 DB서버가 1대의 디스크 저장소를 공유 → 저장소 부분에서 병목이 발생한다.
• DB서버를 늘려도 처리율이 언젠가 한계점에 도달한다.
  • 저장소는 공유 자원이라서 쉽게 늘리기 어렵고, DB서버 대수가 증가할수록 DB 서버 간의 정보공유를 위한 동기화 오버헤드가 발생하기 때문

💡 Share Nothing이란

'아무것도 공유하지 않는다'라는 의미로 네트워크 이외의 자원을 모두 분리하는 방식

• 장점 : 서버와 저장소 세트를 늘리면 병렬처리 때문에 성능이 선형적으로 향상
• 단점 : 각각의 DB 서버가 동일한 1개의 데이터에 액세스할 수 없음 → 커버링 구성을 고려해야 함
• Google이 개발한 Shared Nothing 구조 = 샤딩(Sharding)

정리

가용성, 신뢰성, 재해대책, 성능, 보안 같은 비기능적인 요건에 대해서도 고려

아키텍처는 변경이 어렵기 때문에 신중히 결정

고기능의 아키텍처를 채용한다면 예산, 시간과 인적 자원 같은 제약 요건도 생각

Transaction의 특성

트랜잭션은 DBMS에서 데이터를 조작하는 최소한의 작업(unit of work)
트랜잭션은 아래의 4가지 특성인 ACID를 보장해야 한다.

• 원자성(Atomicity)
• 일관성(Consistency)
• 격리성(Isolation)
• 지속성(Durability)

Transaction Isolation Level

그런데 ACID를 엄격하게 지키다 보면 동시성(Concurrency)가 매우 떨어지는 경우가 발생한다.
그래서 DB 엔진은 ACID를 희생해서 동시성을 얻을 있는 방법을 제공하는데 그것이 바로 트랜잭션 격리 레벨이다.
격리성을 덜 지키는 level을 사용할수록 문제 발생 가능성은 커지지만 동시에 더 높은 동시성을 얻을 수 있다.

일관성(Consistency)와 동시성(Concurrency)

ACID 중 일관성이 완전히 보장될 경우, 여러 클라이언트의 요청을 받는 데이터베이스의 특성상 동시성이 저해(응답의 지연이 발생)될 수 있다.

• 일관성과 동시성 -> 트레이드 오프 관계
  • 일관성과 동시성의 균형을 설정하는 것이 중요하다.

• 동시성 제어 = 동시에 실행되는 트랜잭션의 수를 최대화하면서 무결성을 유지하는 것
  • 낙관적 동시성 제어
    같은 데이터를 동시에 수정하지 않을 것으로 가정한다. 데이터를 읽는 시점에 락을 걸진 않지만 수정하는 시점에서 기존에 읽어온 데이터가 다른 사용자에 의해 변경되었는지 재검사가 필요하다.
  • 비관적 동시성 제어
    같은 데이터를 동시에 수정할 것으로 가정한다. 데이터를 읽는 시점에서 락을 걸고 조회, 갱신 완료 시까지 락을 유지한다.

락(Lock)

트랜잭션 처리의 순차성을 보장하기 위한 방법

트랜잭션에 걸린 Lock은 트랜잭션이 commit 되거나 rollback 될 때 함께 unlock 된다.

• Shared Lock (Read Lock), 공유락
  데이터를 읽을 때 사용되는 Lock으로 공유락은 공유락끼리 동시에 접근이 가능하다. Write는 불허하고, Read는 해당 Critical Section에 접근이 허용된다는 것
  (Read Lock은 Read에만 열려있는 것)
• Exclusive Lock (Write Lock), 배타락
  데이터를 변경할 때 사용되는 Lock으로 트랜잭션이 완료될 때까지 유지된다. Lock이 해제될 때까지 조회를 포함한 다른 트랜잭션은 해당 리소스에 접근할 수 없다.

격리 레벨과 발생할 수 있는 문제

- Read Uncommitted

가장 낮은 격리 수준으로 커밋되지 않은 데이터를 읽을 수 있다.

- Read Committed

커밋된 데이터만 읽을 수 있으며, Dirty Read가 발생하지 않는다.

- Repetable Read

한 번 조회한 데이터를 반복해서 조회해도 같은 데이터가 조회된다. Dirty Read, Non Repeatable Read가 발생하지 않는다.

- Serializable

가장 엄격한 격리 수준으로 Dirty Read, Non Repeatable Read, Phantom Read 모두 발생하지 않는다.

✅ 공식문서
4.4.3 mysql_ssl_rsa_setup — SSL/RSA 파일 생성
6.3.3.1 MySQL을 사용하여 SSL 및 RSA 인증서 및 키 생성

SSL이란?

1994년 Nescape 사에서 개발한 보안기술로 Secure Sockets Layer의 약자이다.

현재는 표준 보안기술로 사용되고 있으며 SSL을 사용시 스니핑과 피싱을 방지할 수 있다.

SSL/RSA 파일 생성

MySQL 설치시 버전별로 SSL 필수 여부가 달라진다.

• SSL을 사용하는 보안 연결을 지원하고, 암호화되지 않은 연결에서 RSA를 사용하는 보안 암호 교환을 지원하는데 필요한 SSL 인증서 및 키 파일과 RSA 키 쌍 파일을 생성한다(해당 파일이 없는 경우).
• 기존 SSL 파일이 만료된 경우 새 SSL 파일을 생성할 수 있다.

mysql_ssl_rsa_setup

• openssl 명령을 사용하므로 머신에 OpenSSL이 설치되어 있어야 사용이 가능하다.
• 필요한 파일을 쉽게 생성해주어서 SSL 사용 장벽을 낮춰준다.
  • 그러나 mysql_ssl_rsa_setup에 의해 생성된 인증서는 자체 서명이 되어 있어 크게 안전하지 않다.
  • mysql_ssl_rsa_setup에 의해 생성된 인증서로 경험을 쌓은 후, 등록된 인증 기관에서 CA 인증서를 받는 것이 좋다.

SSL 및 RSA 인증서 및 키 생성

OpenSSL을 사용하여 컴파일된 MySQL 배포판의 경우, MySQL 서버에 시작시 누락된 SSL 및 RSA 파일 자동 생성 기능이 있다.

결론

8.0.4 버전부터 Enterprise Edition과 Community Edition 모두 OpenSSL로 통합되어 8.0.4 이상 버전을 설치할 경우 OpenSSL 생성 과정을 생략할 수 있다.