24.12.08 CS

CS

이것이 컴퓨터 과학이다.

CHAPTER 06. 데이터베이스

RDBMS의 기본

• 관계형 데이터베이스의 핵심은 테이블의 구성과 관계에 있다.
• 테이블의 구성: 필드와 레코드
  • RDBMS 내의 레코드들은 테이블의 형태를 이룬다. 각 필드로 사용 가능한 데이터 유형이 정해져 있는데, 이를 필드 타입이라고 한다. 그리고 테이블 내의 특정 레코드를 식별할 수 있는 필드의 집합을 키라고 한다. 키는 레코드의 식별뿐만 아니라 테이블 간의 참조에도 사용된다. 즉, 필드 타입과 키를 이해하면 레코드를 구성하는 데이터의 유형을 이해할 수 있고, 테이블 내 특정 레코드를 식별할 수 있다.
  • 필드 타입
    • RDBMS의 테이블 필드에는 다양한 데이터 형식(타입), 즉 필드 타입이 저장될 수 있다.
  • 키
    • 테이블의 레코드를 식별할 수 있는 하나 이상의 필드를 키라고 한다. 키는 테이블의 각 레코드를 식별하는 용도로 사용되기도 하지만, 테이블 간의 참조를 위해 사용되기도 하고 테이블의 접근 속도를 높이기 위해 사용되기도 한다.
    • 후보 키(candidate key)는 테이블의 한 레코드를 식별하기 위한 필드의 최소 집합을 의미한다. 특정 레코드를 유일하게 식별한다는 점에서 유일성을 갖추었다고 표현한다. 후보 키는 하나 이상의 필드로 구성될 수 있으며, 후보 키에 포함된 필드 중 하나라도 생략하면 레코드를 고유하게 식별할 수 없게 된다. 이때 불필요한 필드가 키에 포함되어 있지 않고, 최소한의 정보로 레코드를 식별한다는 점에서 최소성을 갖추었다고도 표현한다. 즉, 후보 키는 유일성과 최소성을 모두 만족하는 키다.
    • 복합 키(composite key)는 두 필드 이상으로 구성된 후보 키를 이야기한다.
    • 기본 키(primary key)는 한 레코드를 식별하도록 선정되어 테이블당 하나만 존재할 수 있는 키를 이야기 한다. 여러 후보 키중에서 테이블의 레코드를 대표하도록 선택된 키이다. 후보 키의 일부이기 때문에 유일성과 최소성을 모두 만족해야 하고, 여러 필드로 구성된 기본 키도 존재 할 수 있다. 중복된 값이 없어야 하고 반드시 값이 존재해야 하며, 레코드를 구분하기 위한 최소한의 정보만으로 이루어져 있어야 한다. 기본 키는 NULL을 가질 수 없다.
    • 외래 키(foreing key)는 다른 테이블의 기본 키를 참조하는 필드로, 테이블 간의 참조 관계를 형성할 때 사용하는 키이다.
• 테이블의 관계
  • 외래 키를 통해 테이블 간 참조가 가능하다.
  • 일대일 대응 관계
    • 하나의 레코드가 다른 테이블의 레코드 하나에만 대응되는 경우를 의미한다.
  • 일대다 대응 관계
    • 하나의 레코드가 다른 테이블의 여러 레코드와 대응될 수 있는 경우를 의미한다.
  • 다대다 대응 관계
    • 한 테이블의 여러 레코드가 다른 테이블의 여러 레코드와 대응되는 경우를 의미한다.
• 무결성 제약 조건
  • 무결성이란 일관되며 유효한 데이터의 상태를 말한다. 무결성 제약 조건이란 데이터베이스에 저장된 데이터의 일관성과 유효성을 유지하기 위해 마땅히 지켜야 하는 조건을 의미한다. 무결성 제약 조건에는 대표적인 종류 4가지가 있다.
    • 도메인 제약 조건(domain constraint)
      • 테이블이 가질 수 있는 필드 타입과 범위에 대한 규칙이다. 각각의 필드 데이터는 원자 값을 가져야 하고, 지정된 필드 타입을 준수해야 하며, 값의 범위나 기본값이 지정되었을 경우 그를 따라야 한다.
    • 키 제약 조건(key constraint)
      • 레코드를 고유하게 식별할 수 있는 키로 지정된 필드에 중복된 값이 존재해서는 안 된다.
    • 엔티티 무결성 제약 조건(entity integrity constraint)
      • 기본 키로 지정한 필드는 고유한 값이어야 하며, NULL이 되어서는 안된다는 규칙이다. 기본 키와 관련한 제약 조건이므로 기본 키 제약 조건이라고도 부른다.
    • 참조 무결성 제약 조건(referential integrity constraint)
      • 외래 키를 통해 다른 테이블을 참조할 때 데이터의 일관성을 지키기 위한 제약 조건으로, 외래 키는 참조하는 테이블의 기본 키와 같은 값을 갖거나 NULL을 가져야 한다는 규칙이다.

SQL

• 데이터 정의 언어(DDL)
  • CREATE
    • CREATE 명령은 데이터베이스, 테이블, 혹은 뷰나 인덱스, 그외 사용자 까지 데이터베이스에서 관리될 수 있는 다양한 대상을 정의한다.
    • CREATE TABLE을 통해 필드 타입 우측 혹은 CREATE TABLE문 하단에 키워드를 명시함으로써 특정 필드가 지켜야 할 제약 조건을 명시할 수 있다.
  • ALTER
    • CREATE TABLE문을 통해 생성된 테이블에 새로운 필드를 추가하거나 기존의 필드를 수정/삭제할 수 있고, 제약 조건을 또한 새롭게 추가하거나 수정/삭제할 수 있다
  • DROP
    • 테이블이나 데이터베이스를 삭제할 수 있다.
  • TRUNCATE
    • 테이블의 구조를 유지한 채로 테이블의 모든 레코드를 삭제한다.
• 데이터 조작 언어(DML)
  • INSERT
    • 테이블에 레코드를 삽입한다. 테이블 이름이라는 테이블에 필드에 맞는 값들을 삽입하는 명령이다. 삽입할 값이 지정되지 않은 필드의 경우, 기본값이 있다면 기본값으로 채워지고 기본값이 없다면 NULL로 채워진다.
    • 레코드 삽입 시 유의할 점은 무결성 제약 조건을 지켜야 한다는 점이다.
  • UPDATE/DELETE
    • 각각 레코드를 수정하고 삭제하는 SQL명령이다. WHERE 조건식은 생략이 가능하지만, 일반적으로 대부분의 UPDATE문에서 사용된다. 특정 조건에 부합하는 레코드만 선별하기 위한 일종의 필터이다. SET에서 명시되는 =는 대입 연산자이지만, WHERE 절에서 명시되는 =는 비교 연산자라는 점에 유의하자
  • SELECT
    • 삽입된 레코드를 조회하는 명령이다. 테이블 내 레코드를 다양하게 정렬하거나 필터링하여 조회하는 것도 가능하다.
    • SELECT 뒤에는 하나 이상의 필드 이름이 명시될 수 있으며, FROM 절에는 조회하고자 하는 테이블의 이름을 명시한다. WHERE 절에 명시되는 조건은 UPDATE문과 DELETE문에서 사용된 조건식과 같다.
  • GROUP BY
    • 특정 필드를 기준으로 필드를 그룹화하기 위해 사용되며, 연산/집계 함수와 함께 사용되는 경우가 많다.
  • HAVING
    • GROUP BY절로 그룹화된 결과에 조건을 적용하기 위해 사용된다. WHERE절과 유사하지만 WHERE절에 명시되는 조건식이 그룹화되기 전 개별 레코드에 대한 조건식이라면, HAVING 절에 명시되는 조건식은 그룹화된 레코드에 대한 조건식이라는 점이 다르다.
  • ORDER BY
    • 특정 필드를 기준으로 데이터를 정렬하는 데 사용된다.
  • LIMIT
    • 조회할 레코드 수를 제한하기 위해서 사용된다.
  • SELECT문의 문법의 순서와 실제 실행 순서에 차이가 있다.
    • FROM->WHERE->GROUP BY->HAVING->SELECT->ORDER BY->LIMIT
• 트랜잭션 제어 언어(TCL)
  • COMMIT
    • 데이터베이스에 작업 반영
  • ROLLBACK
    • 작업 이전의 상태로 되돌린다.
  • SAVEPOINT
    • 롤백의 기준점을 설정한다.
• 데이터 제어 언어(DCL)
  • GRANT
    • 사용자에게 권한 부여
  • REVORK
    • 사용자에게 권한 회수

효율적 쿼리

• 서브 쿼리와 조인
  • 서브 쿼리는 다른 SQL문이 포함된 SQL문을 의미하며 조인은 2개의 테이블을 하나로 합치는 것을 의미한다.
  • 서브 쿼리
    • 내부에 다른 SQL문이 포함되어 있는 SQL문이다. 서브 쿼리는 또 다른 서브 쿼리를 포함할 수 있다. 서브 쿼리에는 대표적인 2가지 유형이 있다.
      • SELECT문 안에 SELECT문이 포함된 서브 쿼리
      • DELETE문 안에 SELECT문이 포함된 서브 쿼리
  • 조인
    • 여러 테이블을 하나로 합치는 것을 의미한다.
    • INNER 조인
      • 테이블 A와 B의 레코드 중 조인 조건을 모두 만족하는 레코드를 결과로 반환한다.
    • LEFT OUTER 조인
      • 테이블 A의 모든 레코드를 포함하고, 조인 조건을 만족하는 테이블 B의 레코드를 결과로 반환한다. 조건을 만족하지 않는 B테이블의 필드는 NULL로 채워진다.
    • RIGHT OUTER 조인
      • 테이블 B의 모든 레코드를 포함하고, 조인 조건을 만족하는 테이블 A의 레코드를 결과로 반환한다. 조건을 만족하지 않는 A테이블의 필드는 NULL로 채워진다.
    • FULL OUTER 조인
      • 테이블 A와 B의 모든 레코드를 포함하고, 조인 조건을 만족하지 않는 경우에 상대 테이블의 필드를 NULL로 채워 반환한다.
• 뷰
  • 뷰는 SELCET문의 결과로 만들어진 가상의 테이블이다. SELECT문의 결과를 뷰로 생성한 뒤, 해당 뷰에 다양한 SQL문을 실행해 볼 수 있다. 주로 테이블에 대한 SQL문을 단순화하기 위해 사용된다.
  • 뷰는 쿼리의 단순화, 재사용성을 높이기 위해 많이 사용된다. 여러 테이블을 조인하거나 복잡한 조건식을 사용한 SQL문을 하나의 뷰로 만들어 두면, 이후 복잡한 SQL문을 반복적으로 작성하는 대신에 대보 단순하게 동일한 결과를 얻을 수 있다.
  • 뷰를 사용할 때 유의할 점은 조회에는 제한이 없지만, 삽입과 수정, 삭제등이 불가능할 수도 있다는 점이다. 특히 여러 테이블을 SELECT한 결과로 만들어진 뷰의 경우, 삽입/수정/삭제 연산이 제약 조건을 어기기 쉽기 때문이다. 따라서 뷰는 조회를 목적으로 사용되는 경우가 많다.
• 인덱스
  • 인덱스는 RDBMS의 성능을 향상시키는 가장 대중적인 방법이다. 검색 속도 향상을 목적으로 만드는 하나 이상의 테이블 필드에 대한 자료구조이다.
  • 특정 필드에 대한 인덱스를 생성하면 인덱스를 기준으로 원하는 레코드에 더 빠르게 접근할 수 있다.
  • MySQL에서는 인덱스의 종류를 크게 클러스터형 인덱스와 세컨더리 인덱스로 구분한다.
    • 클러스터형 인덱스(Clustered index)란 테이블당 하나씩 만들 수 있는 인덱스로, 기본 키다. 테이블 내에 기본 키로 지정된 필드는 기본적으로 클러스터형 인덱스로 간주된다. 만일 기본 키가 지정된 필드가 없는 경우 NOT NULL 제약 조건과 UNIQUE 제약 조건이 있는 필드를 클러스터형 인덱스로 간주한다.
    • 클러스터형 인덱스가 아닌 인덱스를 세컨더리 인덱스(secondary index)라고 한다. 그래서 논클러스터형 인덱스라고도 부른다. 세컨더리 인덱스는 클러스터형 인덱스와는 달리, 테이블당 여러 개가 존재할 수 있지만 클로스터형 인덱스를 활용하는 검색보단 일반적으로 느리다.
  • 인덱스 생성과 관리에는 부작용이 있다. 인덱스의 저장 공간과 생성 시간을 고려해야 한다. 인덱스도 엄연히 여러 데이터를 포함하는 자료구조이므로 인덱스가 차지하는 공간이나 생성 시간이 점점 커질 수 있다. 또한 인덱스는 조회의 성능은 향상시킬 수 있지만 그 외의 작업에 대해서는 성능 향상을 가져오지않고, 오히려 떨어뜨리는 원인이 되기도 한다. 새로운 데이터를 삽입하거나 기존 데이터를 수정/삭제할 때 인덱스에 대한 작업도 동시에 이루어져야 하기 떄문에 추가적인 자원과 연산이 필요하기 때문이다.