데이터베이스를 지탱하는 기술 - 2

전성운·2023년 5월 31일

DB

목록 보기

2/2

테이블 설계와 릴레이션

데이터를 어떻게 사용하느냐 라는 업무 요구사항이 명확하게 되어 있어야 하고, 그 요구사항에 따라서 애플리케이션 데이터 항목을 밝혀내야 한다.
데이터 항목을 완벽하게 밝히는 것은 불가능 하기에 나중에 손쉽게 추가할 수 있는 디자인도 중요하다.

참조 무결성 제약

정규화를 진행 시. (책에서는 emp 테이블과 dept테이블) emp테이블에 부서 번호를 입력할 때 그 값이 dept 테이블에 있는지에 대한 여부를 자동으로 체크해준다. 만약 존재하지 않다면 오류가 반환된다.
만약 다른 서버에 있는 테이블에 대한 무결성 제약을 확ㅇ니해야 할 시 동일한 기능을 애플리케이션에서 구현하여서 확인한다.

테이블 설계의 타당성 검증

한 row의 한 field에 대해서 여러개의 값을 넣고 싶다면? 현재의 상태로는 불가능하다.

새로운 row를 생성하는 방법?

emp 테이블에서 동일 인물로 기본 키가 서로 다른 복수의 레코드가 존재하게 된다.
같은 이름과 이메일 주소의 레코드가 복수로 나오게 된다.

연속적인 번호의 열 도입하기

Field를 여러개 만들어놓고 필요하면 가져가서 사용하는 방식. (겸임 정보를 취급하기 위하여 열을 추가하는 접근 방식.)
열 개수가 지나치게 증가하면 낭비가 많이지므로 주의해야 한다.

하나의 열에 여러 부서의 정보를 등록하기

SET 형식의 집합형을 도입해야 하는데 이 SET 형식 자체가 지원되지 않는 DB도 있고, SET 형식 자체가 검색 조건(인덱스)로서 사용하기 어렵다.

1:N 관계 두개 도입하기

사원 번호와 부서 번호라는 ID밖에 없는 테이블을 새로 만드는 것이다!
이런 테이블을 매핑 테이블이라고 한다.
기본 키 값이 자주 바뀐다 : 이 매핑 테이블은 전근 등이 발생했을 때 기본 키 값이 변경된다. (겸임 시작일, 종료일) 이라는 문자열을 추가하여 기본 키를 변경하지 않는 형태로 하는 경우도 많다.)
레코드 수가 많아진다 : 나중에 정말 수억 단위의 레코드에 도달할 수도 있게 된다. 이러한 경우 테이블을 분할하여 복수의 시스템에서 갖도 있도록 하는 접근 방식이 사용되기도 한다.

정규화 이론의 기본.

제1정규형

제1정규형이 아닌 테이블: 테이블 구성에서 중복, 반복, 복합값 등을 포함하는 구조

제2 정규형

제2정규형이 아닌 테이블: 기본 키가 여러 열로 구성되어 있고 그 중 일부 열의 값에 의해서만 결정되는 열이 있눈 경우.

제3정규형

원칙적으로 테이블의 모든 열은 기본 키 값에 따라 단 하나로 결정되어야 한다.
제3정규형이 아닌 테이블: 어떤 한 field가 결정되면 다른 field가 자동으로 결정되는 field가 있을 때, 그 field들이 분리되어 있지 않고 하나의 거대한 테이블 안에 있을 때.

SQL 문의 특징과 이를 잘 다루는 법

CREATE

CREATE TABLE EMP (
	emp_id INTEGER UNSIGNED PRIMARY KEY,
    emp_name VARCHAR(50) NOT NULL,
    emp_roman VARCHAR(50) NOT NULL,
    emp_email VARCHAR(100) NOT NULL,
    INDEX(emp_roman)
    PRIMARY KEY (emp_id)
);
CREATE TABLE dept(
	dept_id SMALLINT UNSIGNED PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL,
    dept_tel VARCHAR(20) NOT NULL
    PRIMARY KEY (dept_id)
);
CREATE TABLE emp_dept (
	emp_id INTEGER UNSIGNED,
    dept_id SMALLINT UNSIGNED,
    PRIMARY KEY (emp_id, dept_id)
);

TIMESTAMP형의 경우 4바이트로 2037년까지의 값을 다룰 수 있지만, CHAR이나 VARCHAR형으로 다루게 되면 10바이트 이상을 소모하게 된다.
한번 만든 테이블은 쉽게 바꿀 수 없으므로 한 번 만들때 제대로 만들자.

INSERT

INSERT INTO emp (emp_id, emp_roman, emp_name, emp_email) VALUES(1, 'Mitsuru', '미쯔루', 'mitsuru@ex.com');

INSERT INTO emp (emp_id, emp_roman, emp_name, emp_email) VALUES(내용), (내용);

SELECT

인덱스를 사용할 수 없는 조건으로 검색하게 되면 'emp 전체 레코드를 읽고 조건 값과 일치하는 레코드만을 돌려주는' 처리를 하게 된다.
이는 테이블이 큰 경우 매우 무거운 처리이다. (DB상의 효율이 나쁜 처리는 보통 나쁜 SELECT문에 의해서 발생한다!)

UPDATE

UPDATE emp_dept SET dept_id=101 WHERE emp_id=1 AND dept_id=100;

DELETE

DELETE FROM emp WHERE emp_id=1;

RDMBS에서는 TRUNCATE TABLE이라는 고속의 제거 방법을 제공하므로 이를 사용하면 좋다.

TRUNCATE TABLE emp;

JOIN

SELECT emp.emp_email, dept.dept_name
	FROM emp, dept, emp_dept
    WHERE emp_roman LIKE 'Chie%' AND emp.emp_id = emp_dept.emp_id
    AND emp_dept.dept_id=dept.dept_id;

애플리케이션은 데이터베이스 서버와는 다른 컴퓨터에서 작동한다. JOIN을 통해서 여러 SQL문을 하나의 SQL문으로 합치자.

분산 데이터베이스 환경과 조인 궁합

샤딩 : 트래픽이 매우 커서 한 개의 서버에서 처리를 충분히 끝낼 수 없을 때 테이블 단위로 여러 서버에 분산함.
대규모가 될 것으로 예상되는 애플리케이션은 샤딩으 필요성을 처음부터 염두에 두고 거대하게 될 것 같은 테이블과 조인을 하지 않도록 애플리케이션을 작성한다.

SQL 문의 실행 효율 의식하기.

적절한 인덱스가 사용되고 있는지 확인.

인덱스를 전혀 사용할 수 없는 경우 테이블 전체의 레코드를 검색해야만 결과 일치 여부를 판정할 수 있다. (테이블이 거대한 경우 이 작업은 매우 무거워진다)

EXPLAIN

SQL문의 실행 방식이 적잘하지 여부를 실제로 실행하지 않고 판정만 해주는 기능.
이 때 rows의 값은 실제로 DB 레코드에 엑세스되는 횟수가 된다(추정치)

쿼리 분석 도구

관리계 명령

초당 몇 차례의 쿼리를 실행하고 있는가?
초당 몇개의 레코드를 읽고 있는가?
초당 몇 차례의 무거운 쿼리가 실행되고 있는가?

전성운

대학생

이전 포스트