1. JOIN 이란?
데이터를 검색하다 보면 테이블 하나만을 이용해서는 원하는 정보를 다 가져오지 못하는 경우가 많습니다. 예를 들어 사원의 급여 정보를 검색하는 경우에 [급여] 테이블에는 사원 번호와 급여 정보만 있는 경우 사원 이름을 같이 검색하고자 한다면 사원 이름을 가지고 있는 [사원] 테이블을 참조해야 합니다. 이렇게 테이블을 서로 연결하여 검색을 할 때 사용되는 것이 JOIN 문 입니다.
JOIN의 종류는 다음과 같이 나눌 수 있습니다.
INNER JOIN
OUTER JOIN
FULL JOIN
CROSS JOIN
이 중에서 가장 많이 사용되는 것이 INNER JOIN 입니다. 다음 [그림 1]을 기준으로 해서 이들 조인의 기능을 살펴보도록 하겠습니다.

[그림 1]
[그림 1]과 같이 [사원] 테이블과 [급여] 테이블이 있습니다. 실제 업무와 다를 수 있지만 조인의 기능을 확인하기 위하여 가정을 하도록 하겠습니다. [사원] 테이블에는 사원 정보가, [대출] 테이블에는 대출 정보가 기록되어 있습니다.
2. INNER JOIN
INNER JOIN은 양쪽의 행들 중에서 서로 연관된 내용만 검색하는 조인 방법입니다. INNER JOIN을 위해서는 우선 [그림 2]처럼 두 테이블에서 공통되지 못한 부분은 JOIN의 대상에서 제외가 됩니다.

[그림 2]
그리고 남아 있는 양쪽의 행들이 다음 [그림 3]과 같이 서로 연관된 행들끼리 결합을 하게 됩니다.

[그림 3]
결국 다음 [그림 4]와 같은 결과를 얻을 수 있게 됩니다.

[그림 4]
위 내용을 INNER JOIN을 이용한 쿼리문으로 표현한다면 다음과 같습니다.
SELECT A.사번, A.이름, B.도서
FROM 사원 A INNER JOIN 대출 B ON A.사번 = B.사번


o 이 쿼리문에서 A와 B 는 [사원] 테이블과 [대출] 테이블을 가리키는 Alias 입니다. 
o ON 부분을 보면 두 테이블을 '사번' 을 이용하여 연결하고 있습니다.

위 쿼리문은 다음과 같이 표현 할 수도 있습니다.
SELECT A.사번, A.이름, B.도서
FROM 사원 A JOIN 대출 B ON A.사번 = B.사번
즉 'INNER' 를 생략하고 단순히 'JOIN' 만 사용해도 'INNER JOIN'으로 인식됩니다. 이것은 대부분의 JOIN 이 INNER JOIN 이기 때문에 사용자의 편의를 위한 것입니다.
3. OUTER JOIN
OUTER JOIN은 한쪽 테이블을 기준으로 해서 조인을 형성합니다. 그래서 기준이 되는 테이블은 조인되는 테이블과 연관성이 없다 하여도 검색의 대상이 됩니다. 이러한 이유로 OUTER JOIN에는 다음과 같이 두가지의 방법이 존재하게 됩니다.
LEFT OUTER JOIN (기본값)
RIGHT OUTER JOIN
만일 [사원] 테이블과 [대출] 테이블을 OUTER JOIN 시킬 때 [사원] 테이블을 기준으로 한다면 다음 [그림 5] 와 같은 결과를 얻게 됩니다.

[그림 5]
위 내용을 쿼리문으로 표현한다면 다음과 같습니다.
SELECT A.사번, A.이름, B.도서
FROM 사원 A LEFT OUTER JOIN 대출 B ON A.사번 = B.사번
하지만 [대출] 테이블을 기준으로 한다면 다음 [그림 6] 과 같은 결과를 얻게 됩니다.

[그림 6]
위 내용을 쿼리문으로 표현한다면 다음과 같습니다.
SELECT A.사번, A.이름, B.도서
FROM 사원 A RIGHT OUTER JOIN 대출 B ON A.사번 = B.사번
4. FULL JOIN
FULL JOIN은 [그림 5]와 [그림 6]의 결합이라고 생각하시면 됩니다. 결합이 되는 양쪽 테이블에서 연관성이 없는 행도 조인의 결과에 포함되기 때문입니다. [사원] 테이블과 [대출] 테이블의 FULL JOIN의 결과는 다음 [그림 7]과 같습니다.

[그림 7]
위 내용을 쿼리문으로 표현한다면 다음과 같습니다.
SELECT A.사번, A.이름, B.도서
FROM 사원 A FULL JOIN 대출 B ON A.사번 = B.사번
5. CROSS JOIN
CROSS JOIN은 한쪽 테이블의 모든 행들에 대하여 다른 쪽 행들이 전부 대입이 되는 형태의 조인입니다. 만일 [사원] 테이블과 [대출] 테이블을 CROSS JOIN 시킨다면 [그림 8] 과 같은 각각 대입되어 [그림 9]와 같은 결과를 얻게 됩니다. 단, [그림 8]은 첫번째 행에 대한 대입만을 보여주고 있는데 이러한 대입이 [사원] 테이블의 모든 행에 대하여 이루어지게 됩니다.

[그림 8]

[그림 9]
결국 [사원] 테이블의 행의 갯수 X [대출] 테이블의 행의 갯수 만큼의 행이 CROSS JOIN의 결과를 얻어지게 됩니다.
위 내용을 쿼리문으로 표현한다면 다음과 같습니다.
SELECT A.사번, A.이름, B.도서
FROM 사원 A CROSS JOIN 대출 B
즉, 두 테이블의 연관성이 전혀 필요없게 되므로 ON 절이 포함되지 않습니다.
6. 정리
SELECT 문을 사용 할 때 가장 이해하기 힘든 부분이 JOIN 이 아닌가 싶습니다. JOIN 기능을 잘 익혀 두면 좀더 효율적인 검색을 할 수 있습니다. OUTER JOIN이나 FULL JOIN, CROSS JOIN은 잘 사용이 되지 않습니다. 그 이유는 많은 사람들이 INNER JOIN 까지만 배우고 더이상은 JOIN에 대하여 배우지 않고 무작정 SELECT 문을 사용하기 때문이 아닌가 싶습니다.
다음 강좌에서는 실제로 JOIN을 사용하는 예제를 보면서 다시한번 JOIN에 대하여 살펴볼 예정입니다. 온라인 설명서(Books Online)을 보시면 JOIN에 대한 방대한 량의 설명이 있습니다. 그 내용을 꼭 참고하여 주시기 바랍니다.


'Major > Database' 카테고리의 다른 글

데이터베이스 - MySql  (0) 2015.11.26
데이터베이스 - 데이터 타입  (0) 2015.11.26
데이터베이스 - 트랜잭션  (0) 2015.11.26
데이터베이스 - SQL  (0) 2015.11.25
데이터베이스 - 관계 대수  (0) 2015.11.25

MySQL의 전체 구조

MySQL 은 다양한 접근 드라이버를 제공하여 거의 모든 언어를 이용해 쿼리를 사용할 수 있도록 지원합니다. 
MySQL 서버는 크게 MySQL 엔진과 스토리지 엔진으로 구분해서 볼 수 있습니다. 그밖에 간단한 특징을 정리해보면 아래와 같습니다. 

  1. Connection Pool 이 접속 제어
  2. SQL Support, Parser, Optimizer, Caches&Buffers 등의 앞단에서 쿼리를 처리
  3. MySQL은 Pluggable Storage Engines를 사용하여 InnoDB, MyISAM, Memory 같은 다양한 스토리지 엔진을 사용할수 있다
  4. 어플리케이션에 따라서 스토리지 엔진을 선택하여 사용할 수 있다.

What is My SQL ?

# My SQL DB 오버뷰
My SQL 서버(DB) = MySQL엔진 + 스토리지 엔진

1.MySQL 엔진
처리

리턴

2.스토리지 엔진



MySQL 엔진

What is My SQL ?

# My SQL 엔진?

# 클라이언트로부터의 접속 및 쿼리 요청을 하는 커넥션
핸들러와 Sql 파서 및 전처리기, 그리고 쿼리의 최적화된
실행을 위한 옵티마이저가 중심을 이룬다 또한 성...

  1. 클라이언트로 부터 접속 및 쿼리 요청을 처리하는 커넥션 핸들러와 SQL 파서 및 전처리기, 옵티마이저가 중심
  2. MyISAM의 키 캐시나 InnoDB의 버퍼 풀과 같은 보조 저장소 기능이 포함
  3. 요청된 SQL문장을 분석하거나 최적화

스토리지 엔진

  1. 데이터를 디스크 스토리지에 저장하거나 읽어오는 역할
  2. 여러개의 엔진을 동시에 사용할 수 있다.
  3. 테이블 생성 정의시 사용할 스토리지 엔진을 지정 할 수 있음 ( ENGINE = INNODB)

    Table 14.1 Storage Engine Features

    FeatureMyISAMMemoryInnoDBArchiveNDB
    Storage limits256TBRAM64TBNone384EB
    TransactionsNoNoYesNoYes
    Locking granularityTableTableRowRowRow
    MVCCNoNoYesNoNo
    Geospatial data type supportYesNoYesYesYes
    Geospatial indexing supportYesNoYes[a]NoNo
    B-tree indexesYesYesYesNoNo
    T-tree indexesNoNoNoNoYes
    Hash indexesNoYesNo[b]NoYes
    Full-text search indexesYesNoYes[c]NoNo
    Clustered indexesNoNoYesNoNo
    Data cachesNoN/AYesNoYes
    Index cachesYesN/AYesNoYes
    Compressed dataYes[d]NoYes[e]YesNo
    Encrypted data[f]YesYesYesYesYes
    Cluster database supportNoNoNoNoYes
    Replication support[g]YesYesYesYesYes
    Foreign key supportNoNoYesNoNo
    Backup / point-in-time recovery[h]YesYesYesYesYes
    Query cache supportYesYesYesYesYes
    Update statistics for data dictionaryYesYesYesYesYes

    [a] InnoDB support for geospatial indexing is available in MySQL 5.7.5 and higher.

    [b] InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature.

    [c] InnoDB support for FULLTEXT indexes is available in MySQL 5.6.4 and higher.

    [d] Compressed MyISAM tables are supported only when using the compressed row format. Tables using the compressed row format with MyISAM are read only.

    [e] Compressed InnoDB tables require the InnoDB Barracuda file format.

    [f] Implemented in the server (via encryption functions), rather than in the storage engine.

    [g] Implemented in the server, rather than in the storage engine.

    [h] Implemented in the server, rather than in the storage engine.

핸들러 API

  1. 각 스토리지 엔진에 쓰기 또는 읽기를 요청하는 handler가 사용하는 API
  2. InnoDB 스토리지 엔진 또한 이 핸들러 API를 이용해 MySQL 엔진과 데이터를 주고 받는다.
  3. SHOW GLOBAL STATUS LIKE 'Handler%' 명령어로 핸들러 API를 통해 얼마나 많은 데이터 작업이 있었는지 확인할 수 있다.

MySQL 스레딩 구조

MySQL 서버는 프로세스 기반이 아닌 스레드 기반으로 동작하며 포그라운드 스레드와 백 그라운드 스레드로 구분됩니다. 

포 그라운드 스레드 (클라이언트 스레드)

  1. MySQL 서버에 접속된 클라이언트 수만큼 존재함
  2. 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리하는 것이 주 임무
  3. 데이터를 MySQL의 데이터 버터나 캐시로 부터 가져오며, 버퍼나 캐시가 없는 경우에는 직접 디스크의 데이터나 인덱스 파일로 부터 데이터를 읽어와서 작업을 처리한다.
  4. 스레드의 개수를 일정하게 유지하게 만들어 주는 파라미터: thread_cache_size
  5. InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리
  6. MyISAM 테이블은 디스크 쓰기까지 처리

백그라운드 스레드

  1. InnoDB 일 경우 백그라운드 스레드 처리가 많은 비중을 차지
  2. 인서트 버퍼 병합, 로그 기록, InnoDB 버퍼 풀의 데이터를 디스크에 기록등을 처리
  3. 로그 스레드와 버퍼의 데이터를 디스크로 내려쓰는 작업을 처리


메모리 할당 및 사용구조

MySql에서 사용되는 메모리 공간을 크게 글로벌 메모리 영역과 로컬 메모리 영역으로 구분할 수 있습니다. 

글로벌 메모리 영역

  1. MySql서버가 시작되면서 운영체제로부터 할당 받는다.
  2. 클라이언트 스레드의 수와는 무관하게 하나의 메모리 공간만 할당 된다.

로컬 메모리 영역

  1. 세션 메모리 영역이라고도 표현하며, 클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역이다.
  2. 클라이언트 스레드가 사용하는 메모리 공간이라고 해서 클라이언트 메모리 영역이라고도 한다.


쿼리 실행 구조

What is My SQL ?

# My SQL 사용과정
1. 클라이언트에서 MySQL에 접속
(ODBC 컨넥터를 통해 DB 와 통신)
2. 쿼리 파싱-> 쿼리 트리-> 최적화-> 실행
3. 스토리지엔진 에게 데이터 조...

What is My SQL ?

# 쿼리 실행 순서
1
2

1 Connection Pool
2 Parser
3 Optimizer
# Caches & buffer

3

Client 에 의해 SQL 요청이 들어오면아래와 같은 순서로 쿼리가 실행됩니다. 

파서

What is My SQL ?

# PARSER
기본 문장
SELECT A, B, C FROM OBJ_POOL WHERE A = 45 AND ...

What is My SQL ?

# Optimizer
Soft parsing VS hard parsing
머지? 먹는건가?
Soft pasing is good!
ex) prepare statement,
Insert A ...

  1. 쿼리 문장을 토큰(MySQL이 인식할수 있는 최소단위의 어휘나 기호)으로 분리해 트리 형태의 구조로 만들어 내는 과정.
  2. 기본문법 오류를 발견하고 사용자에게 전달

전처리기

  1. 파서 트리를 기반으로 쿼리문장의 구조적인 문제점을 확인
  2. 각 토큰을 테이블 이름이나 컬럼명 또는 내장 함수와 같은 개체를 매핑해 해당 객체의 존재 여부와 객체의 접근권한등을 확인하는 과정
  3. 권한상 사용할 수 없는 개체의 토큰은 이 단계에서 걸러진다.

옵티마이저

What is My SQL ?

# Optimizer
기본 문장
SELECT sum(sal)
FROM emp A, dept B
WHERE A.dept_id = B.dept_id
AND B.dept_name = ‘DB팀’...

  1. 사용자가 요청한 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정
  2. 쿼리 변환
  3. 비용 최적화
  4. 실행계획 개선

이후 쿼리실행기는 스토리지 엔진에 따라 SQL 실행 결과를 사용자나 다른 연결요청 모듈에 넘기게 됩니다. 

What is My SQL ?

# mysql 엔진 요약

# Connection Pool
컨넥션 관리하는 애
# SQL Interface
Sql 명령어 이해하는 애
# Parser
Text 를 자료 구조로 만드는애(문...


실행엔진


옵티마이저가 Group by를 처리하기 위해 임시 테이블을사용하기로 결정했다는 가정하에 처리순서를 살펴보면 

1.실행엔진은 핸들러에게 임시테이블을 만들라고 요청 

2.Where 절에 일치하는 레코드를 읽어오라고 핸들러에게 요청 

3.읽어온 레코드 들을 준비한 임시테이블에 저장하라고 요청 

4.임시테이블에서 필요한 방식으로 데이터를 읽어오라고 요청 

5.최종적으로 실행 엔진은 결과를 사용자나 다른 모듈로 넘김 

즉, 실행엔진은 만들어진 계획대로 핸들러에게 요펑해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할을 수행한다. 



쿼리 캐시

What is My SQL ?

# Cache IN Hard ware view
CPU

속도 : 빨라짐
가격 : 증가함
저장량 : 적어짐

RAM

HDD

CACHE
RAM
HDD

속도 : 느려짐
가격 : 내려감
저...
쿼리캐시(Query cache)는 타 DBMS에는 없는 MySql의 독특한 기능중 하나입니다. 

여러가지 복잡한 처리와 꽤 큰 비용을 들여 실행된 결과를 쿼리 캐시에 담아두고, 동일한 쿼리 요청이 왔을때 간단하게 쿼리 캐시에서 
찾아서 바로 결과를 내려줄수 있습니다. 즉, 빈번한 SELECT 쿼리의 성능 향상을 위해 사용합니다. 
쿼리 캐시는 단어의 의미와는 달리 SQL문장을 캐시하는 것이 아니라 쿼리의 결과를 메모리에 캐시해두는 기능으로 아래와 같은 특징이 있습니다. 

  1. 테이블에 변화(INSERT,UPDATE,DELETE)가 일어나게 되면 해당테이블과 관련된 쿼리 캐시내의 쿼리는 초기화
  2. Query_cache_size 환경 변수를 통해서 조절(기본은 비활성화)
  3. SHOW STATUS LIKE 'Qcache_%' 커맨드로 쿼리 캐시 관련 항목 모니터링
  4. RESET QUERY CACHE 커맨드를 통해 수동으로 캐시 삭제 가능
  5. 그 밖에 Query_cache_limit(저장 쿼리 사이즈제한) 나 Query_cache_min_res_unit(쿼리 개시 조각화 사이즈) 등과 같은 옵션을 통해 성능 조절이 가능합니다.


아래는 쿼리 캐시를 내려주기 전 확인 과정입니다. 

  • 요청된 쿼리문장이 쿼리 캐시에 존재하는가?
  • 해당사용자가 그 결과를 볼수 있는 권한을 가지고 있는가?
  • 트랜젝션 내에서 실행된 쿼리인경우 가시범위 내의 트랜잭션에서 만들어진 결과인가?
  • 쿼리에 사용된 기능(내장 함수나 저장함수등)이 캐시돼도 동일한 결과를 보장할 수 있는가?
    • CURRENT_DATE(), SYSDATE(), RAND()등과 같이 호출시점에 따라 결과가 달라지는 요소가 있는가?
    • Prepared Statement 의 경우 변수가 결과에 영향을 미치지 않는가?
  • 캐시가 만들어지고 난후 해당 데이터가 다른 사용자에 의해 변경되지 않았는가?
  • 쿼리에 의해 만들어진 결과가 캐시하기에 너무 크지 않은가?
  • 그 밖에 쿼리 캐시를 사용하지 못하게 만드는 요소가 사용되었는가?


InnoDB 스토리지 엔진



읽어볼만한 자료 : InnoDB vs MyISAM 비교 

InnoDB 의 기본적인 특징은 아래와 같습니다. 

  • ACID 트랜잭션 지원
  • 테이블스페이스당 64TB 저장 지원
  • MyISAM보다 데이터 저장 비율이 낮음
  • 다른 엔진들에 비해서 느린 데이터 로드 속도
  • MVCC/Snapshot read 지원
  • B-tree, clustered 인덱스 지원
  • 데이터와 인덱스 메모리 캐시 지원
  • 외부키 지원
  • 데이터 압축 옵션을 제공하지 않음
  • row레벨 락을 지원 하며 isolation 레벨 지원
  • 자동 에러 복구 기능
  • 백업 및 특정 시점으로 복구 지원
ACID 란? 

원자성(Atomicity) 
- 트래잭션과 관련된 작업들이 모두 수행되었는지 아니면 모두 실행이 안되었는지를 보장하는 능력이다. 자금 이체는 성공할 수도 실패할 수도 있지만 원자성은 중간 단계까지 실행되고 실패하는 일은 없도록 하는 것이다. 

일관성(Consistency) 
- 트랜잭션이 실행을 성공적으로 완료하면 언제나 일관성 있는 데이터베이스 상태로 유지하는 것을 의미한다. 무결성 제약이 모든 계좌는 잔고가 있어야 한다면 이를 위반하는 트랜잭션은 중단된다. 

격리성(Isolation) 
- 트랜잭션을 수행 시 다른 트랜잭션의 연산 작업이 끼어들지 못하도록 보장하는 것을 의미한다. 이것은 트랜잭션 밖에 있는 어떤 연산도 중간 단계의 데이터를 볼 수 없음을 의미한다. 은행 관리자는 이체 작업을 하는 도중에 쿼리를 실행하더라도 특정 계좌간 이체하는 양 쪽을 볼 수 없다. 공식적으로 고립성은 트랜잭션 실행내역은 연속적이어야 함을 의미한다. 성능관련 이유로 인해 이 특성은 가장 유연성 있는 제약 조건이다. 

지속성(Durability) 
- 성공적으로 수행된 트랜잭션은 영원히 반영되야 함을 의미한다. 시스템 문제, DB 일관성 체크 등을 하더라도 유지되야 함을 의미한다. 전형적으로 모드 트랜잭션은 로그로 남고 시스템 장애 발생 전 상태로 되돌릴 수 있다. 트랜잭션은 로그에 모든 것이 저장된 후에만 commit 상태로 간주될 수 있다. 

일관성과 격리성은 쉽게 정의하기 힘들지만, 이 두 가지 속성은 서로 다른 두 개의 트랜잭션에서 동일 데이터를 조회하고 변경하는 경우에도 상호 간섭이 없어야 한다는 것을 의미한다. 



InnoDB 구조와 특성





프라이머리 키에 의한 클러스터링

InnoDB의 모든 테이블은 기본적으로 Primary Key를 기준으로 클러스터링되어 저장된다. 즉,Primary Key 값의 순서대로 디스크에 
저장된다는 뜻이며, 이로 인해 Primary Key에 의한 Range Scan은 상당히 빨리 처리될수 있다. (오라클 DBMS의 IOT(Index Organized Table)와 동일한 구조) 

잠금이 필요없는 일관된 읽기

MVCC(Multi Version Concurrency Control)라는 기술을 이용해 락을 걸지 않고 읽기 작업을 수행한다. 

자동 데드락 감지

InnoDb는 그래프 기반의 데드락 체크 방식을 사용하기 때문에 감지가 용이하고, 감지된 데드락은 관련 트랜젝션 증에서 ROLLBACL이 가장 용이한 트랜젝션, 즉 레코드를 가장 적게 변경한 트렌젝션을 자동적으로 강제 종료해 버린다. 

자동화된 장애복구

MySql서버가 시작될때 완료되지 못한 트랜젝션이나 디스크에 일부만 기록된 데이터페이지 등에 대한 일련의 복구 작업이 자동으로 진행된다. 

오라클의 아키텍처 적용

InnoDB스토리지 엔진은 오라클 DBMS의 기능과 유사한점이 많다. 대표적으로 MVCC기능이 제공된다는것과 언두(UnDo)데이터가 시스템 테이블 스페이스에 관리 된다는 것 등이다. 

InnoDB 버퍼 풀


일반적으로 어플리케이션에서는 INSERT나 UPDATE 그리고 DELETE와 같이 데이터를 변경하는 쿼리는 데이터 파일의 이곳저고에 위치한 레코드를 변경하기 때문에 Random I/O 를 발생시킨다. 하지만 버퍼풀이 이러한 변경된 데이터를 모아서 처리하게 되면 Random I/O 작업의 횟수를 줄일수 있다. 

MyISAM 키 캐시가 인덱스의 캐시만을 처리하는데 비해, InnoDB의 버퍼 풀은 데이터와 인덱스 모두 캐시 하고 쓰기 버퍼링의 역할 까지 모두 처리한다. 그밖에도 InnoDB의 버퍼 풀은 많은 백그라운드 작업의 기반이 되는 메모리 공간이다. 따라서 버퍼 풀의 크기를 설정하는 파라미터 (innodb_buffer_pool_size) 는 신중하게 설정해야 한다. 일반적으로 전체 장착된 물리 메모리의 50~80% 수준에서 버버풀의 메모리 크기를 결정한다. 

언두(UnDo)로그


언두 영역은 UPDATE문장이나 DELETE와 같은 문장으로 데이터를 변경했을 때 변경되지 전의 데이터(이전 데이터)를 보관하는 곳이다. 특정한 데이터를 변경하였다면 사용자가 커밋시에 현재 상태가 그대로 유지되고 롤백하게 되면 언두 영역의 백업데이터를 다시 데이터 파일로 복구하게 된다. 
언두의 데이터는 크게 두가지 용도로 사용되는데, 첫번째 용도가 트랜젝션의 롤백 대비용이다. 두번째 용도는 트랜젝션의 격리 수준을 
유지 하면서 높은 동시성을 제공하는데 사용된다. 

인서트 버퍼(insert buffer)


레코드가 INSERT되거나 UPDATE될때 데이터 파일을 변경하는 작업뿐 아니라 인덱스를 업데이트 하는 작업도 필요하다. 
그런데 인덱스를 업데이트 하는 작업은 랜덤 하게 디스크를 읽는 작업을 필요로 하므로 테이블에 인덱스가 많다면 이 작업은 상당히 
많은 자원을 소모 하게 된다. 
그래서 InnoDB는 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만, 디스크로 부터 읽어 와야 한다면 
임시공간에 저장해 두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상 시키는데, 이때 사용 하는 임시 메모리 공간을 
인서트 버퍼(Insert Buffer)라고 한다. 

리두(ReDo)로그 및 로그 버퍼


데이터를 변경하고 커밋하면 DBMS는 데이터의 ACID를 보장하기 위해 즉시 변경된 내용을 데이터 파일로 기록해야 한다. 
하지만 이러한 데이터 파일 변경작업은 랜덤 하게 디스크에 기록해야 하기 때문에 상당한 자원이 소모된다. 그래서 이러한 부하를 
줄이기 위해 대부분의 DBMS에는 변경된 데이터를 버퍼링 해두기 위해 InnoDb 버퍼 풀 과 같은 장치가 포함되어 있다. 
하지만 이장치 만으로는 ACID를 보장 할수 없는데 이를 위해 변경된 내용을 순차적으로 디스크에 기록하는 로그 파일을 가지고 있다. 
일반적으로 DBMS에서 로그라 하면 이 리두 로그를 지칭하는 경우가 많다. 

MVCC(Multi Version ConCurrency Control)


MVCC의 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는데 있다. 멀티 버전이라는 것은 하나의 레코드에 대해 여러 개의 버전이 동시에 관리된다는 의미이다. 
만약 격리수준이 READ_UNCOMMITED 인 경우 에는 InnoDb 버퍼 풀 이나 데이터 파일로 부터 변경되지 않은 데이터를 읽어서 반환한다. 
즉, 데이터가 커밋됐든 아니든 변경된 상태의 데이터를 반환한다. 그렇지 않고 READ_COMMITTED나 그 이상의 격리 수준인 경우에는 아직 커밋되지 않았기 때문에 InnoDB의 버퍼 풀이나 데이터 파일에 있는 내용 대신 변경되기 전의 내용을 보관하고 있는 언두 영역 
의 데이터를 반환한다. 이러한 과정을 MVCC라고 표현한다. 

트랜젝션이 길어지면 언두에서 관리하는 예전 데이터가 삭제되지 못하고 오랫동안 관리 되어야 하며, 자연히 언두 영역이 저장되는 
시스템 테이블 스페이스의 공간이 많이 늘어나야 하는 상황이 발생할수도 있다. 
커밋이 된다고 언두 영역의 백업 데이터가 항상 바로 삭제되는것은 아니다. 언두 영역을 필요로 하는 트랜젝션이 더이상 없을때 비로소 삭제된다. 

잠금없는 일관된 읽기(Non-Locking consistent read)


InnoDb에서 격리수준이 SERIALIZABLE이 아닌 경우 순수한 SELECT작업은 다름 트랜젝션의 변경 작업과 관계없이 항상 잠금을 대기 하지 않고 바로 실행 된다.
특정 사용자가 레코드를 변경하고 커밋을 수행하지 않았다 하더라도 이 변경 트랜젝션이 다른 사용자의 SELECT 작업을 방해 하지 않는다. 
이를 잠금없는 일관된 읽기 라고 포현하며, 변경되기 전의 데이터를 읽기위해 언두(UnDo)로그를 사용한다. 
오랜시간 동안 활성상태인 트랜젝션에 위해 MySql서버가 느려지거나 문제가 발생할때가 잇는데, 바로 이러한 일관된 읽기를 위해 
언두 로그를 삭제하지 못하고 계속 유지 하기 때문에 발생하는 문제다. 트랜젝션이 시작 됐다면 가능한 빨리 롤백이나 커밋을 통해 트랜젝션을 완료 하는것이 좋다. 


MyISAM 스토리지 엔진

What is My SQL ?

# MYISAM ENGINE
MySQL엔진
MYISAM ENGINE
Key cache
처리

운영체제 시스템
케시 / 버퍼

메모리 영역

운영체제 캐쉬 영역

인덱스 파일
데이터 파일
...
InnoDB의 버퍼풀의 비슷한 역활을 하는 것으로 MyISAM의 키캐시(Key cahe, 키버퍼)가 있다. 
MyISAM 테이블의 인덱스는 키캐시를 이용해 디스크를 검색하지 않고 검색할 수 있으나 MyISAM 테이블의 데이터는 디스크로부터의 I/O를 해결해 줄만한 어떠한 캐시나 버퍼링 기능이 존재하지 않는다. 
따라서 MyISAM 테이블의 데이터 읽기나 쓰기 작업은 항상 운영체제의 디스크로부터 읽고 쓰는 파일에 대한 캐시나 버퍼링 메커니즘을 가지고 있다. 운영체제가 사용할 메모리가 어느정도 보장이 되어야 가능함 
MyISAM의 주로 사용되는 키캐시는 물리 메모리의 40% 이상을 넘지 않게 설정하는것이 좋다고한다. 


MyISAM - 기본적인 특징

  • 비활성화 할수 없는 기본 스토리지 엔진
  • 데이터 저장에 실제적인 제한이 없음(파일시스템의 제한과 동일)
  • 데이터를 매우 효율적으로 저장
  • 빈번한 데이터 사용(Read)의 부하를 잘 소화
  • B-tree,R-Tree, Full-Text 인덱스 지원
  • 특정 인덱스에 대한 메모리 캐시지원(데이터는 캐시하지 않음)
  • 데이터 압축 옵션 제공(압축하게 되면 ReadOnly가 되지만 디스크효율이 높아짐)
  • 지리적 데이터 지원
  • 테이블 레벨의 락
  • 트랜적션 미지원
  • 백업 및 특정 시점으로의 복구 지원
  • 적합한 사용처 : 트레픽이 많은 웹사이트(Read트래픽), 데이터 웨어하우스


InnoDB vs MyISAM

What is My SQL ?

# locking level
TABLE lock

LOCK

Row lock
ROCK
2.ROW
3.ROW
4.ROW
5.ROW
6.ROW

InnoDB의 특징 

  • 트랜젝션 지원
  • 빈번한 쓰기, 수정, 삭제시 처리 능력
  • 디스크, 전원 등의 장애 발생시 복구 성능
  • 동시처리가 많은 환경에 적합
  • Row Level Locking 지원


MyISAM의 특징 

  • 상대적으로 높은 성능
  • 읽기 위주의 요청에 유리
  • 테이블 단위 락킹
  • Full Text Index를 지원하며 테이블 명시에 row count를 가지고 있기 때문에 count(*) 쿼리가 빠르다. 분석이나 로그등이 유리할듯
  • 트랜잭션 지원이 없기 때문에 백업본 없으면 좃망...

CREATE Table 구문 실행시 ENGINE=InnoDB 혹은 ENGINE=MYISAM 으로 엔진을 지정할 수 있다 
(5.5 버전부터는 기본 엔진이 InnoDB로 되어있다) 
물론 메모리로 지정하여 사용할 수 있다. 

What is My SQL ?

# Memory Hard ware view

MYISAM
사전 : 파일명 사전.frm
OS
파일명 사전.frm : 하드웨어 주소 입니다.

What is My SQL ?

# HDD Hard ware view

What is My SQL ?

# HDD Hard ware view

1 번섹터
1.번 블락

EMP TABLE

2.번 블락
3.번 블락
4.번 블락
5.번 블락
6.번 블락
1.번 레코드
2.번 레코드
3.번 레코...

What is My SQL ?

# MYISAM ENGINE
MySQL엔진
MYISAM ENGINE

MYISAM : 사전 테이블 : 사전 파일.FRM

메모리 영역
Key cache
처리

운영체제 시스템
케시 / 버...



NDB 클러스터 스토리지 엔진


NDB는 Network Database 의 줄미말로 네트워크를 통해 데이터 분산을 지원하는 스토리지 엔진입니다. 
부하분산을 하기 위한 방법중 Replication보다는 비교적 최근에 나온 기능입니다. 
NDB Cluster Storage Engine은 MySQL과는 별도의 프로세스로 동작해 클러스터링을 처리합니다. 결과적으로는 데이터 저장부분만 HA 형태로 운영이 가능한 아키텍처입니다. 



NDB 클러스터의 특성

  • 무공유 클러스터링 (데이터를 저장하는 스토리지도 분산되어 관리되기 때문에 하나의 데이터 저장소가 작동을 멈추어도 서비스의 영향이 없다.)
  • 관리노드 ,데이타노드 ,SQL 노드로 구성되는데 3가지의 종류의 노드 모두 이중화 구현 가능함.
  • 메모리 기반의 스토리지 엔진
  • 자동화된 Fail-Over : 모든 구성 노드가 서로의 상태를 계속 체크하고 있어서 가능
  • 분산된 데이터 저장소간의 동기방식(Sync) 복제 : 클러스터의 모든 데이터 노드에 변경된 데이타가 전달되어 완전히 저장되고 나서야 트랜젝션이 완료될수 있음을 의미함
  • 온라인 스키마 변경 : 테이블에 컬럼이나 인덱스를 추가하면서 동시에 INSERT UPDATE 같은 DML 명령문 가능

NDB 클러스터의 아키텍처

  • NDB 클러스터 노드의 종류 : 관리노드 ,데이터노드 ,SQL 노드로 구성 (이중화 될 수 있도록 구현됨 SPOF 방지를 위함)
  • 관리노드 : NDB 클러스터의 전체적인 구저에 대한 정보를 다른 노드에 전달하거나 각 노드의 장애상황을 전파
  • 데이터 노드 : NDB 클러스터의 핵심이자 즐러스터 전반적인 작업을 수행. 데이타를 저장하는 스토리지를 관리
  • SQL 노드 : NDB 클러스터의 접속해 데이터를 읽고 쓰는 방법은 SQL 문법및 C 프로그래밍 언어를 이용해 데이터를 조작할 수도 있다. 전자를 SQL 노드라 하고 후자를 API 노드라고 한다.

클러스터 간의 복제구성

  • MySQL 서버의 바이너리 로그만 클러스터간의 복제를 할수 없고 NDB 클러스터에는 2개 이상의 MySQL 서버가 동시에 쓰기와 읽기용 쿼리를 처리하게된다.
  • 2개 이상의 MySQL 서버로부터 발생한 바이너리 로그를 동시에 발생시점순으로 하나의 슬레이브 MY-SQL 서버로 보낼수 없기 때문에 NDB 클러스터의 데이터 노드는 자기 자신에게 발생한 변경 내용을 SQL 노드로 피드백을 주며 자신의 바이너리 로그를 수정하고 이때 NDB Binlog injector 라는 스레드가 활성화 되는데 이 스레드가 바이너리 로그에 병합하는 역할을 담당한다.

NDB 클러스터의 성능

  • SQL 노드의 갯수가 늘어날수록 성능이 선형적으로 증가한다.
  • 하나의 SQL 노드에 너무 많은 요청이 발생하는 경우 SQL 노드가 제대로 처리하지 못하는 동시성 문제가 있다.

NDB 클러스터의 네트워크 영햘

  • NDB 클러스터는 네트워크 기반으로 작동하기에 네트워크 전송속도가 쿼리 성능에 상당한 영향을 미친다.
  • 성능이 민감한 서비스에 NDB 클러스터를 사용할 경우 네트워크 인터페이스를 일반적인 이더넷 카드가 아닌 SCI로 클러스터 구성을하기도 한다.

NDB 클러스터의 용도

  • 필요한 저장 메모리의 양이 고정적인 주로 세션데이타를 관리하는 솔루션으로 사용

출처 - http://gliderwiki.org/wiki/163, http://www.slideshare.net/resoliwan/1-mysql-v1

'Major > Database' 카테고리의 다른 글

MySql - JOIN  (0) 2016.04.13
데이터베이스 - 데이터 타입  (0) 2015.11.26
데이터베이스 - 트랜잭션  (0) 2015.11.26
데이터베이스 - SQL  (0) 2015.11.25
데이터베이스 - 관계 대수  (0) 2015.11.25
  • 주요 데이터 타입 (출처: https://msdn.microsoft.com/ko-kr/library/ms187752(v=sql.120).aspx)


'Major > Database' 카테고리의 다른 글

MySql - JOIN  (0) 2016.04.13
데이터베이스 - MySql  (0) 2015.11.26
데이터베이스 - 트랜잭션  (0) 2015.11.26
데이터베이스 - SQL  (0) 2015.11.25
데이터베이스 - 관계 대수  (0) 2015.11.25
  • 트랜잭션
    : 프로젝트 설계자 입장에서 보면 데이터베이스 내에서 하나의 그룹으로 처리해야 하는 명령문들을 모아놓은 작업 단위



  • 트랜잭션 특징

    - ACID
     ■ Atomicity (원자성)
      : 트랜잭션의 수행은 원자적이다.(All-OR-Nothing 방식)
      : 트랜잭션의 모든 연산들은 데이터베이스에 정상적으로 수행이 완전히 완료되거나 아니면, 어떠한 연산도 수행되지 않아야한다.

     ■ Consistency (일관성)
      : 트랜잭션 실행을 성공적으로 완료하면, 언제나 일관성 있는 데이터베이스 상태로 유지되어야 한다.

     ■ Isolation (격리성/고립성)
      : 트랜잭션들이 서로 독립성을 보장받으며 수행될 수 있도록 도와준다.
      : 다수의 트랜잭션이 동시에 병행 수행되고 있는 경우, 수행 중인 트랜잭션이 완전히 완료될 때까지 다른 트랜잭션에서 현재 수행 중인 트랜잭션의 중간 수행 결과를 참조 할 수 없도록 막아주는 것이다.

     ■ Durability (영속성/지속성)
      : 트랜잭션이 모든 작업을 성공적으로 수행 완료하여 데이터베이스 내에 반영했다면, 트랜잭션의 결과는 영구적이어야 한다.




  • 트랜잭션 연산

    - commit (완료)

     

    - ROLLBACK (복귀)

     

     


  • 트랜잭션 상태

     


'Major > Database' 카테고리의 다른 글

데이터베이스 - MySql  (0) 2015.11.26
데이터베이스 - 데이터 타입  (0) 2015.11.26
데이터베이스 - SQL  (0) 2015.11.25
데이터베이스 - 관계 대수  (0) 2015.11.25
데이터베이스 - 정규화  (2) 2015.11.24
  • SQL?
     : Structured Query Language
     : 사용자와 데이터베이스 시스템 간에 의사소통을 하기 위한 언어

  • SQL 유형별 종류
     
     
     

     - DDL(Data Definition Language)
      1. 테이블 이름과 칼럼은 A~Z까지의 문자, 0~9까지의 숫자, 그리고 $,#,_(Under Bar)를 사용할 수 있다. 그러나 공백은 사용할 수 없다.
       2. 테이블의 칼럼은 30자를 초과할 수 없고, 예약어를 사용할 수 없다.
       3. 한 테이블 안에서 칼럼 이름은 같을 수 없으며 다른 테이블에서의 칼럼 이름과는 같을 수 있다.

      ■ CREATE
    CREATE TABLE 테이블이름
    ( {열이름 데이터타입 [NOT NULL] [DEFALUT 값], }+
    [PRIMARY KEY (열이름_리스트), ]
    {[UNIQUE (열이름_리스트),]}*
    {[FOREIGN KEY(열이름_리스트) 
     REFERENCES 기본테이블[(열이름_리스트)]
    [ON DELETE 옵션]
    [ON UPDATE 옵션],]}*
    [CONSTRAINT 이름][CHECK(조건식)]);
    NOT NULL: NULL을 허용하지 않음
    UNIQUE: 중복된 값을 허용하지 않고, 항상 유일한 값을 갖도록 함 
    PRIMARY KEY
    : NULL을 허용하지 않음. 중복된 값을 허용하지 않음, NOT NULL 조건과 UNIQUE 조건을 결합한 형태 
    UNIQUE: 조건을 결합한 형태
    FOREIGN KEY: 참조되는 테이블의 칼럼의 값이 존재하면 허용
    CHECK: 저장 가능한 데이터 값의 범위나 조건을 지정해 설정한 값만을 허용


      ■ ALTER
    ALTER TABLE 테이블명
    ([ADD 칼럼명 데이터_타입] [DEFAULT 값] |
     [DROP 칼럼명] [CASCADE] |
     [ALTER 칼럼명 (DROP DEFAULT | SET DEFAULT 값)]);
    

      ■ DROP
    DROP TABLE 테이블명
     
     - DML(Data Manipulation Language)
     
      ■ SELECT
    SELECT [DISTINCT] {*, 열_리스트}
    FROM {테이블_리스트}
    [WHERE 조건]
    [ORDER BY 칼럼명 ASC | DESC]
    [GROUP BY 칼럼명
     HAVING 조건]


      












     - 집계 함수 (출처: http://www.gurubee.net/)

    COUNT

    : COUNT 함수는 검색된 행의 수를 반환 한다.

    1
    2
    3
    4
    5
    6
    -- 검색된 행의 총 수 4개를 반환. 즉 4개의 부서가 존재한다.
    SELECT COUNT(deptno) FROM dept;
     
    COUNT(DEPTNO)
    -------------
                4

    MAX

    : MAX 함수는 컬럼값 중에서 최대값을 반환 한다.

    1
    2
    3
    4
    5
    6
    -- sal 컬럼값 중에서 제일 큰값을 반환. 즉 가장 큰 급여를 반환.
    SELECT MAX(sal) salary FROM emp;
     
    SALARY
    -------
      5000  

    MIN

    : MIN 함수는 컬럼값 중에서 최소값을 반환 한다.

    1
    2
    3
    4
    5
    6
    -- sal 컬럼값 중에서 가장 작은 값 반환. 즉 가장 적은 급여를 반환
    SELECT MIN(sal) salary FROM emp;
     
     SALARY
    -------
        800   

    AVG

    : AVG 함수는 평균 값을 반환 한다.

    1
    2
    3
    4
    5
    6
    7
    8
    -- 부서번호 30의 사원 평균 급여를 소수점 1자리 이하에서 반올림
    SELECT ROUND(AVG(sal),1) salary
    FROM emp
    WHERE deptno = 30;
     
     SALARY
     ------
     1566.7

    SUM

    : SUM 함수는 검색된 컬럼의 합을 반환 한다.

    1
    2
    3
    4
    5
    6
    7
    8
    -- 부서번호 30의 사원 급여 합계를 조회.
    SELECT SUM(sal) salary
    FROM emp
    WHERE deptno = 30;
     
     SALARY
    -------
       9400   

    STDDEV

    : STDDEV 함수는 표준편차를 반환 한다.

    1
    2
    3
    4
    5
    6
    7
    8
    -- 부서번호 30의 사원 급여 표준편차를 반환.   
    SELECT ROUND(STDDEV(sal),3) salary
    FROM  emp
    WHERE deptno = 30;
     
      SALARY
    --------
     668.331 

    집계함수 예

    : 아래는 부서별 사원수, 최대급여, 최소급여, 급여합계, 평균급여를 급여합계 순으로 조회하는 예이다.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    SELECT deptno 부서번호, COUNT(*) 사원수,
           MAX(sal) 최대급여, MIN(sal) 최소급여,
           SUM(sal) 급여합계, ROUND(AVG(sal)) 평균급여
     FROM emp
     GROUP BY deptno
     ORDER BY SUM(sal) DESC;
     
     
    부서번호   사원수  최대급여  최소급여   급여합계   평균급여
    ------- -------- --------- -------- --------- ---------
         20       5      3000       800    10875     2175
         30       6      2850       950     9400     1567
         10       3      5000      1300     8750     2917


    GROUP BY

    • : GROUP BY 절은 데이터들을 원하는 그룹으로 나눌 수 있다.
    • : 나누고자 하는 그룹의 컬럼명을 SELECT절과 GROUP BY절 뒤에 추가하면 된다.
    • : 집계함수와 함께 사용되는 상수는 GROUP BY 절에 추가하지 않아도 된다. (개발자 분들이 많이 실수 함)
    • : 아래는 집계 함수와 상수가 함께 SELECT 절에 사용되는 예이다.
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    -- 부서별 사원수 조회
    SELECT '2005년' year, deptno 부서번호, COUNT(*) 사원수
    FROM emp
    GROUP BY deptno
    ORDER BY COUNT(*) DESC;
     
     
    YEAR     부서번호     사원수
    ------ ---------- ----------
    2005년         30          6
    2005년         20          5
    2005년         10          3

    아래 예제는 부서별로 그룹하여 부서번호, 인원수, 급여의 평균, 급여의 합을 조회하는 예제이다.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    SELECT deptno, COUNT(*), ROUND(AVG(sal)) "급여평균",
           ROUND(SUM(sal)) "급여합계"
      FROM emp
     GROUP BY deptno;
     
     
      DEPTNO   COUNT(*)    급여평균    급여합계
    -------- ---------- ---------- ----------
          30          6       1567       9400
          20          5       2175      10875
          10          3       2917       8750

    아래 예제는 업무별로 그룹하여 업무, 인원수, 평균 급여액, 최고 급여액, 최저 급여액 및 합계를 조회하는 예제이다.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    SELECT job, COUNT(empno) "인원수", AVG(sal) "평균급여액",
           MAX(sal) "최고급여액", MIN(sal) "최저급여액",
           SUM(sal) "급여합계"
    FROM emp
    GROUP BY job;
     
     
    JOB           인원수   평균급여액   최고급여액   최저급여액    급여합계
    ----------- -------- ---------- ---------- ---------- ----------
    CLERK              4     1037.5       1300        800       4150
    SALESMAN           4       1400       1600       1250       5600
    PRESIDENT          1       5000       5000       5000       5000
    MANAGER            3 2758.33333       2975       2450       8275
    ANALYST            2       3000       3000       3000       6000

    • : GROUP BY 절은 집계 함수 없이도 사용 될 수 있다.(DISTINCT와 용도가 비슷해 짐)
    • : 아래 예제를 보면 GROUP BY는 말 그대로 그룹을 나누는 역할을 한다.
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    -- GROUP BY를 이용한 부서번호 조회 예
    SELECT deptno
    FROM emp
    GROUP BY deptno;
     
     
    DEPTNO
    ------
        30
        20
        10

    DISTINCT와 GROUP BY절

    • : DISTINCT와 GROUP BY 개념에 대해서 좀 더 이해를 해보자.
    • : DISTINCT는 주로 UNIQUE(중복을 제거)한 컬럼이나 레코드를 조회하는 경우 사용한다.
    • : GROUP BY는 데이터를 그룹핑해서 그 결과를 가져오는 경우 사용한다.
    • : 하지만 두 작업은 조금만 생각해보면 동일한 형태의 작업이라는 것을 쉽게 알 수 있으며, 일부 작업의 경우 DISTINCT로 동시에 GROUP BY로도 처리될 수 있는 쿼리들이 있다.
    • : 두 기능 모두 Oracle9i까지는 sort를 이용하여 데이터를 만들었지만, Oracle10g 부터는 모두 Hash를 이용하여 처리한다.
    • : 그래서 DISTINCT를 사용해야 할지, GROUP BY를 사용해서 데이터를 조회하는 것이 좋을지 고민되는 경우들이 가끔 있다.

    아래의 예제는 동일한 결과를 반환한다.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    -- DISTINCT를 사용한 중복 데이터 제거
    SELECT DISTINCT deptno FROM emp;
     
     
    -- GROUP BY를 사용한 중복 데이터 제거
    SELECT deptno FROM emp GROUP BY deptno;
     
     
    DEPTNO
    ------
        30
        20
        10

    하지만 곰곰히 생각해 보면 GROUP BY와 DISTINCT는 각자 고유의 기능이 있다

    집계함수를 사용하여 특정 그룹으로 구분 할 때는GROUP BY 절을 사용하며, 특정 그룹 구분없이 중복된 데이터를 제거할 경우에는 DISTINCT 절을 사용 하도록 하자

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    -- 아래와 같은 기능은 DISTINCT를 사용하는 것이 훨씬 효율적이다.
    SELECT COUNT(DISTINCT d.deptno) "중복제거 수",
           COUNT(d.deptno) "전체 수"
    FROM emp e, dept d
    WHERE e.deptno = d.deptno;
     
     
    -- 집계 함수가 필요한 경우는 GROUP BY를 사용해야 한다.
    SELECT deptno, MIN(sal)
    FROM emp
    GROUP BY deptno;

    HAVING 절

    • : WHERE 절에서는 집계함수를 사용 할 수 없다.
    • : HAVING 절은 집계함수를 가지고 조건비교를 할 때 사용한다.
    •   HAVING 절은 GROUP BY절과 함께 사용이 된다.

    아래 예제는 사원수가 다섯 명이 넘는 부서와 사원수를 조회하는 예제이다.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    SELECT b.dname, COUNT(a.empno) "사원수"
    FROM emp a, dept b
    WHERE a.deptno = b.deptno
    GROUP BY dname
    HAVING COUNT(a.empno) > 5;
     
     
    DNAME          사원수
    ------------ -------
    SALES              6

    아래 예제는 전체 월급이 5000을 초과하는 JOB에 대해서 JOB과 월급여 합계를 조회하는 예이다. 단 판매원(SALES)은 제외하고 월 급여 합계로 내림차순 정렬하였다.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    SELECT job, SUM(sal) "급여합계"
    FROM emp 
    WHERE job != 'SALES'      -- 판매원은 제외
    GROUP BY job              -- 업무별로 Group By
    HAVING SUM(sal) > 5000     -- 전체 월급이 5000을 초과하는
    ORDER BY SUM(sal) DESC;   -- 월급여 합계로 내림차순 정렬
     
     
    JOB                  급여합계
    ------------------ ----------
    MANAGER                  8275
    ANALYST                  6000
    SALESMAN                 5600

    - 테이블에 별칭을 부여한 조인 연산

    SELECT s.STU_NAME, c.COU_NAME, c.COU_ID
    FROM STUDENT s, COURES c
    WHERE s.COU_ID = c.COU_ID;
    
    
    


    ■ INSERT
    INSERT INTO 테이블이름 { 칼럼명 } -- 생략 할 경우, CREATE했던 순서대로 삽입 됨
    VALUES({ 칼럼값 })

    ■ UPDATE
    UPDATE 테이블이름
    SET { SET 칼럼명 = 칼럼값 }
    WHERE 조건
    

    ■ DELETE
    DELETE FROM 테이블이름
    [WHERE 조건] -- 생략 할 경우, 모든 행이 삭제
    



  • 질의어


  • 관계 대수


    - 연산자
      

       

        


        
     
      
      

        

      
        
      : 릴레이션 R과 S의 동등 조인 결과는 R과 S의 카티션 곱에 셀렉션을 적용한 결과와 같다.
        

      
      
      
      
      


'Major > Database' 카테고리의 다른 글

데이터베이스 - 트랜잭션  (0) 2015.11.26
데이터베이스 - SQL  (0) 2015.11.25
데이터베이스 - 정규화  (2) 2015.11.24
데이터베이스 - 관계형 데이터베이스  (0) 2015.11.24
데이터베이스 - 데이터 모델  (0) 2015.11.24
  • 정규화(Normalization)
    1. 어떤 관계라도 데이터베이스 내에서 표현이 가능하도록 만드는 것
    2. 관계에서 바람직하지 않은 삽입, 삭제, 갱신 이상이 발생하지 않도록 한다.
    3. 새로운 형태의 데이터가 삽입될 때 관계를 재구성할 필요성을 줄일 수 있다.
    4. 보다 간단한 관계 연산에 기초하여 검색을 보다 효율적으로 할 수 있다.

    - 이상(anomaly) 현상
     : 속성 간에 존재하는 여러 종속 관계를 하나의 릴레이션에 표현함으로써 데이터의 중복으로 인해 발생하는 여러가지 현상
     ■ 삭제 이상
      :
    연쇄삭제에 의한 정보의 손실
      
     ■ 삽입 이상
      : 원하지 않는 정보의 강제 삽입

      
    갱신 이상
     : 중복 데이터의 일부 갱신으로 정보의 모순성 발생
     

    - 함수 종속
      

     : X가 Y를 결정한다, Y가 X에 의해 결정된다, X는 Y의 결정자이다
     : X(determinant)를 결정자, Y를 종속자
     : {학번, 과목번호} -> {성적}은 완전 함수 종속(FFD: Full Functional Dependency), {학번} -> {학년}은 부분 함수 종속(PFD: Partial Functional Dependency)

      

    - 정규화 과정
     : 일반적으로 데이터베이스를 설계할 때에는 BCNF까지만 고려
     : 정규화 과정을 거칠 수록 join 연산으로 인한 성능저하를 가져올 수 있기 때문에 데이터의 정합성과 데이터의 무결성을 우선으로 할지 데이터베이스 구성의 단순화와 성능을 우선으로 할지를 결정
     : 반대 개념으로 역정규화

        

     ■ 제1정규화(1NF)
      : 어떤 릴레이션 R에 속한 모든 도메인이 원잣값(atomic value)만으로 되어 있다.
      


     ■ 제2정규화(2NF)
      : 어떤 릴레이션 R이 제1정규형이고, 키에 속하지 않은 속성 모두가 키에 완전 함수 종속
      : 무손실 분해

        

     ■ 제3정규화(3NF)
      : 어떤 릴레이션 R이 2NF이고, 모든 속성들이 기본키에 이행적 함수 종석(transitive FD)이 아니면 제3정규형에 속한다.
      : 이행적 함수 종속성은 A -> B 와 B-> C의 함수 종속성이 존재하면(A->B
    B->C) 속성 C가 이행적으로 A에 종속 (A->C)

      

      

     ■ 보이스/코드 정규형(BCNF)
      : 릴레이션 R이 제3정규형을 만족하고, 모든 결정자가 후보키이어야한다.

      
      


'Major > Database' 카테고리의 다른 글

데이터베이스 - SQL  (0) 2015.11.25
데이터베이스 - 관계 대수  (0) 2015.11.25
데이터베이스 - 관계형 데이터베이스  (0) 2015.11.24
데이터베이스 - 데이터 모델  (0) 2015.11.24
데이터베이스 - 기초  (3) 2015.11.24
  • 관계형 데이터베이스 (Relational Database)

    - 릴레이션 (Relation)
     

      : 속성, 튜플의 순서는 중요하지 않다.
      : 속성은 더 이상 쪼갤 수 없는 원자값(atomic value)만을 저장
      : 한 릴레이션에 포함된 튜플들은 모두 달라야 한다.

     



     - 차수(degree)
      : 한 릴레이션을 구성하는 애트리뷰트의 개수
      : 릴레이션의 최소 차수는 1
      : 자주 바뀌지 않음

     - 카디날리티(Cardinality)
      : 한 릴레이션 내의 튜플의 수
      : 릴레이션의 최소 카디날리티의 수는 0
      : 시간이 지남에 따라 계속해서 변함

      - 도메인(Domain)
      : 릴레이션에 포함된 각각의 속성들이 취할 수 있는 같은 타입의 원자(atomic) 값들의 집합
       

     - 키(Key) 

      : 한 릴레이션에서 각 튜플을 유일하게 식별하기 위해 사용하는 하나 혹은 그 이상의 속성들의 집합
       ■유일성 : 하나의 키 값으로 하나의 튜플만을 유일하게 식별할 수 있는 성격
       
    최소성 : 모든 레코드들을 유일하게 식별하는데 꼭 필요한 속성으로만 구성되어야 하는 성격

      
      
      
      
    - 외래키(Foreign key)
      : 관계를 맺고 있는 릴레이션 R1, R2에서 릴레이션 R1이 참조하고 있는 릴레이션 R2의 기본키와 같은 R1 릴레이션의 속성
      : 외래키로 지정되면 참조 테이블의 기본키에 없는 값은 입력할 수 없음.

      



    - 개체 무결성
      : 릴레이션에서 기본키를 구성하는 속성은 널(NULL) 값이나 중복 값을 가질 수 없음.

    - 참조 무결성
      : 외래키 값은 NULL이거나 참조 릴레이션의 기본키 값과 동일해야 함
      : 즉, 릴레이션은 참조할 수 없는 외래키 값을 가질 수 없음.


'Major > Database' 카테고리의 다른 글

데이터베이스 - SQL  (0) 2015.11.25
데이터베이스 - 관계 대수  (0) 2015.11.25
데이터베이스 - 정규화  (2) 2015.11.24
데이터베이스 - 데이터 모델  (0) 2015.11.24
데이터베이스 - 기초  (3) 2015.11.24
  • 데이터 모델링(Data Modeling)
     







  • 데이터 모델의 구성요소





    - 엔티티(Entity)
     : 데이터베이스에 자료로 표현하려는 것
     : 개념이나 정보단위 같은 현실 세계의 대상체
     : 유형, 무형의 정보
     : 서로 연관된 하나 이상의 속성으로 구성

    - 속성(Attribute)
     : 데이터의 가장 작은 논리적 단위
     : 각 속성은 엔티티의 특성, 상태 등을 기술

    - 관계(Relation)
     : 데이터의 가장 작은 논리적 단위
     : 각 속성은 엔티티의 특성, 상태 등을 기술


'Major > Database' 카테고리의 다른 글

데이터베이스 - SQL  (0) 2015.11.25
데이터베이스 - 관계 대수  (0) 2015.11.25
데이터베이스 - 정규화  (2) 2015.11.24
데이터베이스 - 관계형 데이터베이스  (0) 2015.11.24
데이터베이스 - 기초  (3) 2015.11.24
  • 데이터베이스 
    : 여러 사용자가 원하는 정보를 얻기 위해서 모아둔 자료의 집합, 관련있는 데이터들의 집합

  • 데이터베이스 관리 시스템(DBMS)
    : 데이터를 편리하게 저장하고 효율적으로 관리하고 검색할 수 있는 환경을 제공해주는 소프트웨어
    : 데이터베이스의 생성과 관리를 담당하는 소프트웨어 패키지
    : 정의, 조작, 제어 기능

  • 데이터베이스 시스템(DBS)
    : 데이터베이스를 통해 데이터를 저장하고 관리하기 위한 목적으로 사용되는 일체의 시스템
    : 데이터베이스와 그를 관리하는 소프트웨어(DBMS, 응용 프로그램) 모두를 칭하는 용어
      

  • 스키마(Schema)
    : 데이터 구조와 제약조건에 대한 명세(specification)를 기술하는 것
    : 개체(entity), 속성(attribute), 관계(relationship)에 대한 정의와 이들이 유지해야 될 제약 조건(constraints)을 포함
    : 3단계 데이터베이스 구조






  • 데이터베이스 시스템의 구성요소




  • 데이터베이스 언어

    - 데이터 정의어(DDL)
     : 스키마를 정의하거나 수정할 목적으로 사용
     : CREATE, ALTER, DROP

    - 데이터 조작어(DML)
     : 데이터베이스 내의 데이터 연산을 위한 언어
     : INSERT, UPDATE, DELETE

    - 데이터 제어어(DCL)
     : 
    데이터베이스 내의 데이터를 올바르고 정확하게 유지
     : 보안, 무결성, 데이터 회복, 병행수행


'Major > Database' 카테고리의 다른 글

데이터베이스 - SQL  (0) 2015.11.25
데이터베이스 - 관계 대수  (0) 2015.11.25
데이터베이스 - 정규화  (2) 2015.11.24
데이터베이스 - 관계형 데이터베이스  (0) 2015.11.24
데이터베이스 - 데이터 모델  (0) 2015.11.24

+ Recent posts