이것이 MySQL이다. [Chapter 3 - MySQL 전체 운영 실습( 데이터베이스 구축 절차, 테이블, 인덱스, 뷰]

3.1 요구사항 분석과 시스템 설계 그리고 모델링

3.1.1 정보 시스템 구축 절차 요약

정보시스템을 구축하기 위해서는 일반적으로 분석, 설계, 구현, 시험, 유지보수의 5가지 단계를 거친다.

 

1. 분석 단계는 가장 첫 번째 단계로 시스템 분석 또는 요구사항 분석이라고 부른다.

요구사항 분석은 현재 우리가 무엇을 할 것인지를 결정한다. 사용자의 인터뷰와 업무 조사 등을 수행해야한다.

 

2. 설계 단계는 주로 시스템 설계 또는 프로그램 설계라고 부른다. 우리가 구축하고자 하는 시스템을 어떻게 할 것인가를 결정한다.

 

시스템 설계가 끝나면 그 결과 문서들을 개발자에게 넘겨주고 개발자는 설계서에 나온 대로 프로그램을 진행한다.

그렇기 때문에 분석과 설계 과정이 전체의 50% 이상을 차지한다.

 

3.1.2 데이터베이스 모델링과 필수 용어

분석과 설계 과정 중 가장 중요한 과정 중의 하나가 데이터베이스 모델링이다.

데이터베이스 모델링이란 현실세계에서 사용되는 데이터를 MySQL에 어떻게 옮겨 놓을 것인가를 결정하는 과정이다.

데이터베이스 모델링을 하는 주요 이유는 업무 정보를 구성하는 기초가 되는 정보들을 표현함으로 데이터를 조직화 하여 효율적으로 저장할 수 있다.

 

 

데이터 : 하나하나의 단편적인 정보를 뜻한다. 정보는 있으나 아직 체계화 되지 못한 상태이다.

테이블 : 표 형태로 데이터가 저장되는 공간을 뜻한다.

데이터베이스 : 테이블이 저장되는 저장소를 말한다. 각 데이터베이스는 서로 다른 고유한 이름을 가져야 한다.

DBMS : DataBase Management System의 약자로 데이터베이스를 관리하는 시스템 또는 소프트웨어를 말한다.

열 : 테이블에 저장되는 데이터의 속성을 나타낸다.

열 이름 : 각 열을 구분하기 위한 이름으로 각 테이블 내에서는 중복되면 안된다.

데이터 형식 : 열의 데이터 형식을 말한다.

행 : 실질적인 데이터로 테이블에 저장된 데이터의 한 레코드를 나타낸다.

기본 키 열 : 테이블에서 각 행을 고유하게 식별하는데 사용되는 열이다. 기본 키 값을 가지고 있으며 다른 행과 구별된다.

외래 키 필드 : 두 테이블의 관계를 맺어주는 키를 말한다.

SQL : SQL은 관계형 데이터베이스에서 데이터를 조작하고 관리하기 위해 사용되는 언어이다.

 

 

 

3.2 MySQL을 이용한 데이터베이스 구축 절차

3.2.1 데이터베이스 생성

 

CREATE SCHEMA 명령을 통해 데이터베이스를 생성한다.

 

테이블 생성 예시

 

3.2.2 테이블 생성

CREATE TABLE을 통해 테이블을 생성한다.

 

id는 UNSIGNED INT로 음수가 없는 정수이다. PRIMARY를 통해 기본키로 설정하였고, AUTO_INCREMENT는 자동 증가 값으로 INSERT 할 때마다 1씩 증가시켜 준다. NOT NULL은 NULL이 될 수 없다.

name은 VARCHAR로 가변길이 문자열이며 현재는 20바이트까지 넣을 수 있도록 설정했다.

age는 id와 마찬가지로 음수가 없는 정수이다.

 

테이블 생성 예시

 

3.2.3 데이터 입력

 

데이터 입력 예시

id는 insert 할 때마다 자동으로 1씩 증가하기 때문에 따로 추가하지 않아도 된다.

 

 

3.2.4 데이터 활용

1. 테이블의 모든 데이터를 조회하기

SELECT의 기본 형식은 SELECT 열 이름 FROM 테이블 이름 WHERE 조건 형식을 갖는다.

*는 모든 열을 의미한다.

즉 SELECT * FROM [테이블 명]은 테이블의 모든 데이터를 보여주는 의미가 된다.

 

2. 특정 데이터만 조회

 

WHERE 뒤에 원하는 조건를 통해 원하는 것만 조회할 수 있다.

현재 id가 2인 컬럼을 조회하였다.

 

3. 테이블 삭제하기

 

 

 

3.3 테이블 외의 데이터베이스 개체의 활용

테이블 외의 다른 주용한 데이터베이스 개체로는 인덱스, 스토어드 프로시저, 트리거, 함수, 트리거, 커서 등이 있다.

 

 

3.3.1 인덱스

인덱스는 테이블의 특정 열의 값을 기반으로 빠른 데이터 검색을 가능하게한다.

인덱스는 여러 자료구조를 이용해서 구현할 수 있는데 대표적인 자료구조로 해시 테이블과 B+Tree가 있다.

 

해시 테이블

컬럼의 값과 물리적 주소를 key, value의 한 쌍으로 저장하는 자료구조이다.

=연산에 최적화 되어 있기에 부등호 연산이 자주 사용되는 데이터베이스에서는 잘 사용하지 않는다.

 

B+Tree

구조는 ROOT Node, Branch Node, Leaf Node으로 구성되며 계층적 구조를 가지고 있다.

Leaf Node만 인덱스와 데이터를 가지고 있고 나머지 Root Node와 Branch Node는 데이터를 위한 인덱스만 갖는다.

Leaf Node에만 데이터를 저장하고 Leaf Node 끼리 LinkedList로 연결되어 있어 시간 효율이 올라간다.

Root Node에서 경로를 확인 후 알맞은 Node로 이동하여 원하는 데이터가 있는 Leaf Node에 도달한다.

 

장점 

테이블을 조회하는 속도와 그에 따른 성능을 향상시킬 수 있다.

전반적인 시스템 부하를 줄일 수 있다.

 

단점

인덱스를 관리하기 위해 DB 약 10%에 해당하는 저장공간이 필요하다.

인덱스의 크기가 커지면 성능이 오히려 저하되는 역효과가 발생할 수 있다.

 

 

인덱스 생성 예시

 

 

3.3.2 뷰

뷰란 가상의 테이블로 사용자의 입장에서는 테이블과 동일하게 보이지만 실제 데이터를 가지고 있지 않다.

실체는 없는 것이며 진짜 테이블에 링크된 개념이다. 그래서 뷰를 SELECT 하면 결국 진짜 테이블의 데이터를 조회하는 동일한 결과가 된다.

 

뷰를 만드는 이유에 대한 예시

회원 정보가 있는 users와 결제이력이 있는 money 테이블이 있다.

회원 별 결제 이력을 조회하고 싶을 때 개인정보는 제공하고 싶지 않을 경우

users에 회원 정보가 담긴 컬럼을 제외한 컬럼만 조인해서 뷰를 만들고 해당 뷰를 통해 회원별 결제 이력을 조회하면 된다.

 

장점

데이터 조회가 용이하다.

사용자 별 필요한 정보만 제공할 수 있다.

 

단점

뷰에 인덱스를 구성할 수 없다.

뷰를 포함하여 뷰를 만든 경우 연관된 뷰를 삭제하면 생성된 뷰도 삭제된다.

한번 정의된 뷰는 수정 불가능하다.

 

 

뷰 생성 예시

 

 

3.3.3 스토어드 프로시저

스토어드 프로시저란 MySQL에서 제공해주는 프로그래밍 기능을 말한다.

즉, SQL문을 하나로 묶어 편하게 사용할 수 있는 기능이다.

 

장점

보수성이 뛰어나다.

개발 업무를 구분하여 개발할 수 있다.

네트워크 소요 시간을 줄일 수 있다.

 

 

스토어드 프로시저 생성 예시

 

 

3.3.4 트리거

트리거란 데이블에 부착되어서 테이블에 INSERT나 UPDATE, DELETE 작업이 발생되면 자동으로 실행되는 코드를 말한다.

사용자가 직접 호출하는 것이 아닌 데이터베이스에서 자동적으로 호출하는 것이 특징이다.

 

트리거의 사용 예시

회원 정보 테이블과 결제 금액 테이블이 있을 때 어떤 사람이 자신의 결제 금액이 얼만지 물어보았을 경우

실수로 조회하다 결제 금액 컬럼을 삭제한 상황이 발생하면 복구할 수 없다.

이러한 실수를 대비해 자동으로 삭제가 일어날 경우 미리 다른 곳에 삭제될 데이터를 트리거를 통해 자동으로 저장한다. 

 

 

 

3.4 데이터베이스 백업 및 관리

3.4.1 백업과 복원

백업은 현재의 데이터베이스를 다른 매체에 보관하는 작업을 말한다.

복원은 데이터베이스에 문제가 발생했을 때 다른 매체에 백업된 데이터를 이용해서 원상태로 돌려놓는 작업을 말한다.

 

*Workbench를 사용할 경우 86쪽 참고