초보 개발자의 프로그래밍 공부방

-성능이란?-

성능은 기본적으로 '빠르기' 를 중심으로 한 개념입니다.

성능은 2가지 지표 (매트릭스)에 의해 측정됩니다.

한 가지는 '처리 시간' 또는 '응답 시간' 이라고 불리는 지표이고, 다른 한가지는 '처리율' 입니다.

-처리시간-

이들은 어떤 특정 처리의 시작부터 종료까지 걸린 시간을 나타냅니다.

-처리율-

시스템에서는 '특정 처리(트랜잭션)를 단위 시간에 몇 건 처리가 가능한가' 에 대한

측정 단위를 나타냅니다.

시스템의 자원용량을 결정하는 요인

-버틀넥 포인트(병목)-

동시 실행 처리수가 증가 할수록 준비해야 할 자원도 증가하며 한 가지 자원이라도 한계에 이른 시점에서 성능이 나빠지기 시작합니다.

즉, 응답 시간이 상승하기 시작하고, 반대로 처리율이 떨어지기 시작합니다. 이때 최초로 한계에 이른 자원을 '버틀넥포인트' 또는 '병목' 이라고 합니다.

-데이터베이스가 병목되는 이유-

-테이블에 대한 액세스 방법-

-데이터베이스가 결과를 통지하는 과정-

-인덱스의 구조-

인덱스는 데이터베이스의 성능 향상 수단으로 가장 일반적인 방법입니다.

응답 시간이 늦은 SQL이 발견되면 우선 인덱스로 해결할 수 없는지를 검사하는 것이 튜닝의 제1선택입니다.

인덱스의 인기가 높은 이유는 다음 3가지 입니다.

쉽게 말하는 인덱스는 비용 대비 성능이 높은 방법입니다.

인덱스가 나무 형태를 하고 있는 것을 일반적으로 'B-tree' 라고 합니다.

B-tree는 관계형 데이터베이스에서 튜닝의 기본이 되는 인덱스 입니다.

-B-tree의 구조-

그림에서 원으로 표시된 한개의 데이터를 '노드' 라고 합니다.

가장 위의 노드를 '루트 노드' 라고 하는데, '뿌리' 라는 의미입니다.

가장 아래 노드를 '리프 노드' 라고 하고, '잎' 이라는 의미입니다.

중간 노드는 가지라는 뜻에서 '브랜치 노드' 로 부릅니다.

-B-tree의 장점-

장점 한 가지는 어떤 값에 대해서도 같은 시간에 결과를 얻을 수 있다. (균일성)

데이터양에 증가할수록 우수한 개선 효과를 발휘한다.

-인덱스 작성이 역효과가 날때-

-인덱스를 만들 때 기준-

-DBMS의 3가지 구조-

DBMS에서 데이터를 보존하는 기억장치는 대부분 하드디스크입니다.

하드디스크에서 지속성을 실현하려면 쓰기를 전부 '동기화쓰기' 로 하면 좋겠지만, 데이터베이스의 쓰기는

기억장치의 임의 장소에 무작위로 액세스해서 쓰기를 수행하기 때문에 동기화 쓰기는 느려서 성능 면에서

실용적이지 않습니다.

그래서 지속성과 성능이 양립하도록 일반적으로 DBMS에서는 3가지 구조를 사용합니다.

-로그 선행 쓰기(WAL)-

데이터베이스의 데이터 파일 변경을 직접 수행하지 않고, 우선 로그로 변경 내용을 기술한 로그 레코드를 써서

동기화하는 구조입니다.

MySQL 에서는 이 로그를 'InnoDB 로그' 로 부릅니다.

WAL에는 다음과 같은 이점이 있습니다.

-데이터베이스 버퍼-

커밋 시에는 WAL에 변경 내용을 쓰기 때문에 데이터 파일의 변경 내용은 트랜잭션이 커밋되면서 동시에 동기화할

필요가 없습니다.

그렇다고 트랜잭션마다 버퍼를 취해 비동기적인 쓰기를 하면 로그와 데이터 파일 간 일관성을 유지하기가 어렵습니다.

그래서 일반적인 DBMS에서는 '데이터베이스 버퍼' 를 준비해 데이터 파일로의 입력을 데이터베이스 버퍼 경유로

일원화해서 단순화하고 있습니다.

이 때문에 효율적으로 데이터의 일관성을 유지할 수 있게 됩니다.

MySQL의 경우 갱신의 흐름은 다음과 같습니다.

-크래시 복구-

WAL과 데이터베이스 버퍼, 데이터베이스 파일 3가지가 연계 플레이로 지속성을 담보하면서 현실적인 성능으로 DBMS가 동작하고 있습니다.

크래시 (예를 들면, MySQL 서버의 비정상적 종료)가 발생한 경우의 복구.

크래시가 발생하면 다음과 같은 상태가 됩니다.

-PITR이란?-

일반적인 DBMS에서는 데이터베이스에 실행된 갱신을 기록한 로그를 보존해서 그것을 복원한 데이터베이스에 순차 반영해 백업 이후의

임의의 시점으로 복원할 수 있습니다.

이처럼 임의의 시점에서의 데이터 변경을 포함한 복원을 'PITR (Point - in - time Recovery)' 라고 부릅니다.

PITR에 이용되는 로그의 이름과 특성은 DBMS마다 다르다.

-아카이브 지정이란?-

PITR에 이용하는 로그는 대부분 앞에서 설명한 WAL을 이용합니다.

이 때문에 WAL을 크래시 복구에만 이용한다면 체크포인트 이전의 로그는 불필요하게 되어 해당 디스크 영역은

삭제하거나 재이용할 수 있습니다. (다시 이용되는 경우가 대부분)

하지만 이렇게 되면 PITR을 수행하고 싶을 때에 필요한 로그가 없는 사태가 발생하게 됩니다.

따라서 크래시 복구용으로는 불필요한 로그도 PITR 용으로 보존이 필요할 수 있으며 이를 위한 모드가 '아카이브 지정' 입니다.

-바이너리 로그란?-

MySQL에서 PITR에는 '바이너리 로그'를 이용하는데, 앞의 로그 선행 쓰기에 나왔던 InnoDB 로그는 이용하지 않는지 궁금할 수 있습니다.

실은 InnoDB 로그는 InnoDB 전용 크래시 복구에만 이용되고, PITR에는 MySQL 전체 (InnoDB에 한정하지 않고) 에서 이용하는

바이너리 로그를 채용합니다.

-백업-

장애가 발생하면 데이터베이스의 데이터는 이용할 수 없게 됩니다.

이런 사태에 신속히 대응하기 위해 데이터베이스가 정상적인 상태일 때 현재 이용하는 데이터를 복제해서 어딘가 다른 장소에 옮겨

두어야 합니다. 이 옮겨진 데이터를 '백업 데이터' 라고 하며 이 데이터를 얻어내는 과정을 '백업' 이라고 부릅니다.

장애가 발생하면 신속하게 백업한 데이터에서 데이터베이스의 데이터를 이용할 수 있는 상황까지 복구합니다.

이것이 '복원 (Restore)' 입니다.

백업을 수행하는 데는 다음 3가지 관점이 있습니다.

-테이블 이란?-

각각의 행이 어떤 공통적인 특징을 가진 집합이어야 한다.

또한 테이블은 현실 세계를 반영한다. ('개념' 이나 '집합' 으로)

-열 이란?-

개체의 속성을 의미한다.

-기본키-

테이블 안에 있는 객체를 구분할 수 있는 키.

또한 기본키는 중복되거나 NULL 값이 올 수 없다. (다른 객체를 구분할 수 없기 때문)

그리고 기본키는 값이 바뀌면 곤란하다. (변경 후 값의 유일성을 보증할 수 없고, 과거 데이터와의 결합이 어렵다.)

-정규형-

데이터의 갱신이 발생한 경우에도 부정합이 발생하기 어려운 테이블의 형태 (이상현상을 방지하기 위해 테이블을 무손실 분해하는것)

-제 1정규형-

테이블 셀에 복합적인 값을 포함하지 않는다 (하지만 관계형 데이터베이스에서는 이 정의에 반하는 테이블을 작성하는 것이 기술적으로 불가)

복합적인 값을 셀에 넣으면 기본키가 행의 값을 고유하게 특정할 수 없기 때문.

-비 제1정규형 테이블-

-함수 종속성-

기본키와 다른 열 사이에 성립하는 함수적인 유일성을 관계형 데이터베이스에서는 '함수 종속성' 이라고 하며 다음과 같이 열을

중괄호 ({ })로 감싸서 화살표(->)로 연결해 표현 합니다.

-제 2정규형-

제 1정규형을 만족하고 부분함수 종속성을 제거한 정규형을 말한다.

(기본키는 현재 고객 기업 ID와 주문번호 인데, 고객기업 ID만으로도 기업명과 고객 기업규모를 특정할수 있어 주문번호 열이 쓸데없는 값이 되기 때문)

이와 같은 부분함수 종속이 존재할 경우 해당 키와 종속하는 열만 다른 테이블로 만들어 외부로 꺼내야 합니다.

비정규형 테이블을 사용할 때 같은 내용의 행이 복수 행 존재할수 있으므로 일부가 잘못 등록될 위험이 있기 때문에 제 2정규형을 사용한다.

-비 제2정규형 테이블-

-제 3정규형-

제 2정규형을 만족하고, 이행 함수 종속을 제거한 정규형을 말한다.

(예를 들어 아래 테이블에서 앞으로 관리하고 싶은 업계는 석유, 건설, 바이오 외에도 시스템 또는 유통 등 다양하다고 해도 지금은

이 테이블에 해당 업계를 등록할 방법이 없습니다. 이 테이블이 '기업' 이라는 단위의 집합을 반영하고 있어서 '유통' 이라는 업계에 속한

기업과 거래를 하지 않은 이상 해당 업계의 레코드를 만들 수 없기 때문입니다.)

-비 제3정규형 테이블-

-ER 다이어그램-

정규화를 수행하면 테이블이 나누어져 그 수가 증가하게 되는데 '갱신 이상'의 위험을 없애는 것이 목적이지만, 결과적으로 테이블 수가

늘어나는 것도 사실입니다. 이런 테이블 간의 관련성을 한눈에 알 수 있게 고안된 기술이 ER 다이어그램 입니다.

Entity(엔티티, 실체)란 '테이블' , 'Relationship(릴레이션십, 관계, 관련성)' 이란 '테이블 간의 관계' 를 의미합니다.

이를 표시하는데 그래픽으로 이해하기 쉽도록 해주는 기술이 ER 다이어그램 입니다.

-엔터티란?-

간단히 설명하면 테이블과 같고 사각형으로 표시합니다.

복잡한 정보를 간략화하기 위한 기술.

위 절반의 부분에는 '기본키'를 아래 절반의 부분에는 '기타 열(기본키가 아닌 열)'을 기재합니다.

-릴레이션십이란?-

외래키가 존재하는 테이블은 해당 열이 다른 테이블의 기본키 열을 참조하는 것을 의미합니다.

이 관련성을 '릴레이션십' 이라고 합니다.

-트랜잭션-

데이터베이스를 사용하는 실제 시스템이나 애플리케이션에서는 단일 쿼리만으로 조작하는 일은 거의 없고

복수의 쿼리를 연속적으로 사용해 일관성 있는 형태의 한 단위로 취급해야한다.

이러한 한 덩어리의 쿼리 처리 단위를 '트랜잭션'이라고 합니다.

MySQL에서는 트랜잭션을 사용할 수 없는 단순한 구조의 'MyISAM형'과 일반적인 DBMS와 똑같은

트랜잭션 구조를 사용할 수 있는 'InnoDB형' 2종류의 테이블을 이용할 수 있습니다.

이번 장에서는 트랜잭션을 사용하기 위해 InnoDB형 테이블을 작성합니다.

-트랜잭션의 4가지 특성(앞글자를 따서 'ACID 특성'이라고 한다.)-

SQL 문은 몇가지 키워드와 테이블명, 열명 등을 조합해 하나의 문장을 만들어 쿼리의 내용을 기술하는데, 이중 키워드는

처음부터 의미나 사용법이 정해져 있는 특별한 영단어입니다. SQL문은 DBMS에 줄 수 있는 명령의 종류에 따라

다음 3가지로 구분됩니다.

-MySQL의 특성-

-트랜잭션 격리 수준별 외관-

-잠금 타임아웃이란?-

'갱신' 과 '참조'는 서로를 블록하지 않지만, '갱신' 과 '갱신' 이 부딪치는 경우에는 나중에 온 갱신이 잠금 대기 상태가 됩니다.

잠금을 건 쪽이 언제 잠금을 풀지 알 수 없어서 잠금 해제를 기다리고 있는 쪽에서는 잠금을 기다리거나 기다리지 않거나,

기다린다면 어느 정도 기다릴지를 (초수 지정이나 무한으로 기다린다) 설정할 수 있습니다.

이때 잠금 대기로 타임아웃이 발생하는 경우 DBMS로부터 롤백되는 단위가 다를 때가 있는데, 해당 트랜잭션 전체를

롤백하는 경우와 쿼리만 롤백하는 것입니다.

MySQL 에서는 잠금 대기로 타임아웃이 발생하면 롤백되는 것은 기본으로 오류가 발생한 쿼리 입니다.

트랜잭션 전체를 롤백하고 싶다면 다음 방법으로 할 수 있습니다.

-교착 상태란?-

예를 들어, 트랜잭션 A가 테이블 a의 잠금을 얻고 트랜잭션 B가 테이블 b의 잠금을 얻었다고 해보겠습니다.

이 잠금을 유지한 채 서로 잠금을 건 자원에 잠금이 필요한 처리(INSERT/UPDATE/DELETE)를 실행하면 아무리

기다려도 상황이 바뀌지 않는 상태가 되는 것을 '교착 상태' 라고 합니다.

-교착상태의 빈도를 낮추는 대책-

데이터베이스 외부 관리 툴 설치 (HeidiSQL 설치)

광고창 때문에 경고창이 발생하므로 '우클릭' ->'다른이름으로 저장' 을 누르면 된다!

출처

https://www.heidisql.com/

-커넥션-

로그인해서 프롬프트가 표시되었다는 것은 로그인 전과 로그인 후로 사용자와 MySQL의 관계가 변했다는 것을 의미합니다.

이는 사용자와 MySQL이 접속되었다. 즉 연결되었다는 뜻입니다.

이 연결이라는 것을 시스템 세계에서는 '커넥션' 이라고 부릅니다.

-프롬프트란?-

'사람에게 무언가를 하라고 재촉할때 사용하는 말' 입니다.

따라서 'mysql>'이란 MySQL이 사용자를 향해서 '명령을 입력하라'고 재촉하는 것입니다.

또한, 프롬프트 문자열은 DBMS의 설정을 수정하면 사용자가 좋아하는 문자열로 바꿀 수 있습니다.

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

-로그오프-

전화를 거는 것이 '로그인'이라면 전화를 끊는 것에 해당하는 행위는 '로그오프' 입니다.

로그오프를 하면 MySQL과의 커넥션이 끊어집니다.

로그오프 명령은 프롬프트 상태에서 'quit'라고 입력하고 'Enter' 키를 누르면 간단하게 실행됩니다.

-관리 명령이란?-

DBMS는 SQL이외에도 다양한 용도의 명령어를 준비해 두고 있는데, 이것을 '관리 명령' 이라고 합니다.

관리 명령은 DBMS가 정상적으로 동작하는지 감시하거나 DBMS가 이상한 동작을 할 때 문제 해결을 위해

정보수집을 하는 등의 용도로 사용합니다.

해당 명령의 목록은 DBMS의 메뉴얼에 기재되어 있고, 래퍼런스 문서도 있어서 이를 읽어보면 어떠한 정보를 어떤

명령어로 얻을 수 있는지는 알 수 있습니다.

관리 명령에서 중요한 점은 2가지 입니다.

-SQL 문과 관리 명령의 구분법-

-스키마-

데이터베이스에서 폴더에 해당하는 개념.

'틀' 이라는 의미입니다.

테이블은 실제로 몇 개의 스키마 속에 저장되는 형식을 취하고 있습니다.

스키마도 폴더처럼 사용자가 자유롭게 만들 수 있어서 용도별로 나누거나

보여주고 싶지 않은 사용자에게는 접근할 수 없도록 제한을 건 스키마를 만드는 등의

관리를 수행하는 것도 가능합니다.

스키마의 상위에는 또 하나의 계층으로 '데이터베이스'가 있습니다.

데이터베이스에는 '계층'을 표시하는 의미도 있기 때문입니다.

-인스턴스-

데이터베이스보다 한 층 더 위에 있는 '인스턴스'란 개념이 있습니다.

이것은 물리적 개념으로, DBMS가 동작할 때의 단위입니다.

그래서 OS 입장에서는 '프로세스' 라고도 부릅니다.

DBMS에 따라서는 이것을 '서버 프로세스' 또는 단순히 '서버'로 부르는 경우도 있습니다.

-관계형 데이터베이스의 계층 구조-

-아키텍처-

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

하드웨어와 미들웨어의 구성을 가리킨다.

-아키텍처의 역사-

1. Stand-alone : 데이터베이스만으로 시스템이 성립하는 가장 간단한 방법

2. 클라이언트 / 서버 : 클라이언트와 서버로 계층을 분리하여 상호 간에 네트워크로 접속하는 서버

3. Web 3계층 : 클라이언트 / 서버를 더욱더 발전시킨 것

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

-클러스터란?-

신장전략처럼 동일한 기능의 컴포넌트를 병렬화하는 것을 '클러스터링' 이라고 부릅니다.

'클러스터'는 사물이나 사람의 집합을 가리키는 말로, 포도 등의 '송이'란 의미도 있습니다.

이 의미처럼 시스템 세계에서는 '동일한 기능의 컴포넌트를 복수 개 준비해 한 개의 기능을 실현한다' 는 의미로 사용합니다.

또한, 클러스터 구성으로 시스템의 가동률을 높이는 것을 '여유도 를 확보한다' 또는 '다중화'라고 지칭합니다.

-단일 장애점-

다중화되어 있지 않아서 시스템 전체 서비스의 계속성에 영향을 주는 컴포넌트를 '단일 장애점'이라고 합니다.

또한 단일 장에점의 신뢰성이 시스템 전체의 가용성을 결정합니다.

-DB와 다른 서버의 차이-

데이터베이스는 데이터를 장기간 보존하는 매체가 필요합니다.

이것이 기본적으로 데이터를 일시적으로 처리할 뿐인 웹 서버나 애플리케이션 서버와 다른 점 입니다.

웹 서버나 애플리케이션 서버는 한창 처리 중일 때는 일시적으로 데이터를 보존하기도 하지만, 처리가 끝난 후까지 계속 데이터를

보존할 필요는 없습니다.

하지만 데이터베이스는 대량의 데이터를 영구적으로 보존해야 하고 그에 따른 성능도 요구되기 때문에

데이터를 보존하는 매체에 필요한 요건이 높습니다.

-기본적인 다중화-

Active-Active : 클러스터를 구성하는 컴포넌트를 동시에 가동한다. 시스템 다운 시간이 짧다. 성능이 좋다.

Active-Standby : 클러스터를 구성하는 컴포넌트 중 실제 가동하는 것은 Active, 남은 것은 대기하고 있는다.

ㄴCold-Standby : 평소에는 Standby DB가 작동하지 않다가 Active DB가 다운된 시점에 작동하는 구성이다.

ㄴHot-Standby : 평소에도 Standby DB가 작동하는 구성이다.

-리플리케이션-

Active-Active와 Active-Standby 클러스터 구성에서는 서버 부분은 다중화할 수 있어도 저장소 부분은 다중화할 수 없어서 데이터를 다중화하지

않는 공통적인 단점이 있습니다.

즉, 저장소가 부서질 경우에는 데이터를 잃게 됩니다.

물론 저장소도 보통은 내부 컴포넌트가 다중화되어 있지만, 데이터 센터 전체가 지진으로 붕괴하거나 화재가 난다면 끝입니다.

이런 상황에 대응하기 위한 클러스터 구성이 '리플리케이션' 입니다.

이는 DB서버와 저장소 세트를 복수로 준비하는 것을 말합니다.

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

테이블 2개를 생성해서 JOIN검색 해보기

1. 학생정보 테이블 생성

2. 학과인원 테이블 생성

3. [학생정보] 테이블과 [학과인원] 테이블에서 학과명이 같은 튜플을 JOIN 하여 학과, 학생수를 검색