Jins' Dev Inside

 처음 학부생 때 공부를 위해서 혹은 실무에서 관계형 데이터 베이스를 접하고, 직접 설계하는 일은 쉽지 않다.

많은 DB 테이블들이 직관적으로 나타나있고 관계 역시 이해하기는 어렵지 않지만 이를 구성하고자 한다면 성능, 데이터의 정합성, 중복 배제 등 고려해야할 내용은 많다. 물론 그걸 다 잘하기는 매우 힘들며, 비즈니스 특성에 따라 트레이드 오프를 감안하여 작성하게 된다. ^^;;

 처음 DB 설계를 해보려는 이들에게 이번 정리 내용의 포스팅은 큰 도움이 될 수 있다.

 * RDB 설계 시작하기 5가지 단계

(1) DB의 목적을 명확히 한다. (Define the purpose of the DB)

(2) 수집한 데이터를 적절히 나눌 수 있는 기준이 될 Primary Key 를 구성한다.

(3) 테이블 간의 관계를 구상한다. 가장 쉬운 관계의 형성은 PK를 중심으로 생각하는 것이다. 

- One to many : Parent 테이블의 모든 value는 많은 Child table의 Record를 가질 수 있으나 Child table의 모든 Value는 Parent table에서 단 하나의 Record에 대응되어야 한다.

- Many to many : Many to many의 관계는 일반적으로 다수의 one to many 관계들로 이루어질 수 있어야 한다.

- One to one : 주로 동등한 레벨의 정보에 대한 Column 분리 용도로 사용된다.

(4) Refine and Normalize the Design(정규화)

 Column의 추가나 Optional data 처리를 위한 table 분리 등.

- 1NF : 기본적으로 필요한 속성들을 하나의 entity로 모아놓고 하나의 속성을 UID로 선정했다면 0NF 상태이고, 여기서 Repeating group을 제거하면 1차 정규화 형식이라 한다.

- 2NF : 속성들이 PK의 일부에 대해서만 종속적이라면 이 Part key dependency를 제거하여 2NF 형식이 된다.

- 3NF : 2NF에서 Key가 아닌 다른 속성에 종속적일 때 Inter-data dependency라 하며 이를 제거하면 3NF 형식이 된다.

- BCNF : Key 내의 속성이 key 내의 다른 속성에 종속적이라면 Inter-key dependency라 하며 제거하면 BCNF 형식이 된다.

(5) Denormalize : 순수히 성능만을 위한 기법이다.

- Comined table : 자주 조인하는 테이블의 경우 조인 성능부하를 없애기 위해 미리 Join된 형태로 table에 합침. 그럴 수 없는 경우 Join overhead는 Index나 SQL Plan을 조정한다.

- Derived data : 계산해서 얻을 수 있는 값을 굳이 칼럼으로 따로 빼준다.

- Artificial key : 적절한 PK가 없거나 인덱싱이 힘든 경우 간단한 AK를 만들어 해결하고 PK column은 일반속성화 한다. (예를들어 굳이 나뉘어져있지 않은 기수 번호를 인공적으로 Key로 만들어주어 부여하고 이를 인덱싱하여 성능을 개선한다.)

- Denormalization 의 예시. 유저 정보 테이블 id, name, address. 유저 성적 테이블 id, score. 두 테이블의 조인이 빈번하다면 두 테이블을 합칠 수 있음. Id, name, address, score.

 * 추가적으로 좀 더 자세한 설명이 필요하다면 다음 링크가 정말 잘 정리되어있다.  http://www.ntu.edu.sg/home/ehchua/programming/sql/relational_database_design.html

data에 대한 데이터를 말하는 것으로, 다른 데이터를 설명해준다.

 정보를 효율적으로 찾기 위해서 일정한 규칙에 따라 컨텐츠에 부여되는 데이터이다.

 데이터 자체를 좀더 적은 정보량으로 표현하기 위하여 만들어졌으며, 이는 인터넷에서 문서들을 찾기 위한 일종의 태그 역할을 수행하기도 한다.

 즉, 메타데이터를 기준으로 문서를 구분할 수 있는 일종의 Unique 한 Key 역할을 할 수 있으며 이로 인해 주로 분석이나 분류 목적으로 따로 쓰이기도 한다.

실무에서도 주로 서버를 구성할 때, 관리하는 데이터는 이 meta data가 되며, meta data를 이용한 참조 혹은 인덱스로 실제 데이터를 가리키는 데 사용한다.

메타 데이터의 쓰임새는 워낙 무궁무진하며 혹자는 메타 데이터에 대한 설계 능력이 data를 설계하는 중요한 역량이라고 말하기도 하더라.

* 실무에서도 많이 쓰이는 용어이기 때문에 간단하지만 알아둘 필요가 있다.

SQL이란 DBMS에서 데이터를 질의하기 위해 만들어진 언어이다. 크게 다음과 같이 3가지로 분류된다.

 - DDL (Data Definition Language) : 데이터를 정의하기 위한 언어로, CREATE, DROP, ALTER, TRUNCATE 등으로 시작하는 Query 문이 속한다.
 - DML (Data Manipulation Language) : 데이터를 다루기 위한 언어로 흔히 말하는 CRUD 에 해당하는 SELECT, INSERT, UPDATE, DELETE 등 Query 문이 속한다.
 - DCL (Data Control Language) : 데이터를 관리하기 위한 언어로 주로 사용자에게 권한을 부여하거나 철회할 수 있는 GRANT, REVOKE 등의 Query 문이 있다.

실제로 사용하면서 유용하게 썼던 SQL 쿼리문들을 정리해두었다. 실무에서도 응용해서 사용하면 크게 무리는 없었던 것 같다.

인터넷을 돌아다니다 발견할 수 있는 Geek 한 것들을 모아두진 않았다.

-        접속 방법

 : mysql -u root -p (dbname)

-        비밀번호 변경

 : mysqladmin -u root password 새로운 비밀번호

-        테이블의 생성

 : create table {테이블이름}({칼럼명} {칼럼타입});

(ex) CREATE TABLE member (

id int(11) NOT NULL,

);

-        구조 보기

 : desc 테이블 / explain 테이블  /  show create table {테이블명}

-        이름 변경

 : rename table {테이블명A} to {테이블명B}

-        삭제

 : drop table {테이블명}

-        레코드 삽입

 : Insert into {테이블명} values(v1, v2) / Insert into table(col1, col2) values(v1, v2);

-        조회

 : select * from table {테이블명}

> AS : 칼럼의 이름을 달리 명명해서 출력. (ex) Col1 as 'name'

> Desc : 내림차순, Asc : 오름차순 (ORDER BY)

> LIMIT 10 : 0~10 까지 레코드 수 제한. / LIMIT 100, 10 : 100~110까지 레코드 범위

-        수정

 : Update {테이블명} set col1 = 칼럼1 where 조건

-        삭제

 : Delete from {테이블명} where 조건

-        칼럼 추가

 : Alter table {테이블명} add col3 varchar(255) not null.

-        칼럼 삭제

 : Alter table {테이블명} drop col3

-        칼럼 수정

 : Alter table {테이블명} modify col3 char(50) not null.

-        In : 원하는 필드값만을 선택 추출하는데 사용되는 그룹 조건문

-        조인

(1)  Inner join

 : Select * from tableA inner join tableB on tableA.col1 = tableB.col1

 => tableA의 col1과 tableB의 col1이 일치하는 데이터만을 출력. ON 절의 조건이 일치하는 조인테이블의 결과만을 출력한다.

(2)  Outer join

 : Select * from tableA left outer join tableB on tableA.col1 = tableB.col1

 => tableA.col1이 존재하나 tableB.col1이 존재하지 않으면 tableB.col1 = NULL인 상태로 출력. 조인하는 테이블의 ON 절 조건 중 한쪽의 모든 데이터를 가져옴(LEFT JOIN , RIGHT JOIN) 양쪽(FULL JOIN)

-        내장함수 Benchmark

 : Select Benchmark(반복횟수, 실행쿼리)

(ex) Select Benchmark(100, (“select * from table”)); => 해당 쿼리를 100번 반복한 벤치마크 결과를 출력.

-        DISTINCT

 : 주로 UNIQUE한 COLUMN이나 TUPLE을 조회할 때 사용되는 키워드. 칼럼을 DISTINCT 를 이용하여 조회한다면 중복을 제거한 값들을 바로 얻을 수 있다. 단 이 때, 여러 개의 칼럼을 지정한다면 칼럼의 조합이 중복되는 것을 제외한다. DISTINCT는 함수처럼 WHERE이 아닌 HAVING 조건식에도 사용이 가능하다.

(ex) Select DISTINCT email from table;

(ex) SELECT class FROM courses GROUP BY(class) HAVING count(distinct student) >= 5;

-        GROUP BY

 : 데이터를 그루핑해서 결과를 가져오는 경우 사용. 내부적으로 중복값을 배제한채 정렬된 결과를 가져온다. 주로 HAVING과 같이 사용되며 그룹으로 묶어서 자체 정렬한다. 좀 더 정확히는 그룹의 대표값을 정렬해서 가져온다. 그렇기 때문에 모든 컬럼에 대해 단순 SELECT 하는 쿼리문에는 쓰기 적절치 않으며 테이블 내에서 데이터를 가공할 때 사용하기가 좋다. 예를 들어 accountType에 따라 해당하는 accountName의 row수를 그루핑 하고 싶다면 다음 쿼리를 사용해보자. Select accountType, COUNT(accountName) from accounts group by(accountType);

-      HAVING

 : HAVING은 GROUP BY 와 같이 쓰이는 구문으로 GROUP BY의 조건문이라 할 수 있다. 위의 쿼리에서 COUNT가 1개 이상인 내용만 쿼리를 하는데 다음처럼 사용 가능하다. SELECT accountType, COUNT(accountName) FROM accounts GROUP BY(accountType) HAVING COUNT(accountName) > 1; HAVING의 시점은 GROUPING이 끝난 이후이고 WHERE 절과 다르게 HAVING 절은 통계함수를 포함할 수 있다.

HAVING은 () 를 안 싸는 것이 좋다. 버전에 따라 오작동 위험이 있는듯하다 ;;

WHERE 구문과 같이 사용할 때, WHERE 구문이 먼저 적용되고 난 다음의 조건 결과에 대해 GROUP BY ~ HAVING 조건문이 걸린다. HAVING 조건문은 그룹화되어진 필드들에 대해 적용된다.

-      SubQuery 사용법

 : 복잡한 쿼리문을 만들 때 많이 사용하게 되는 구문이 서브쿼리문이다. 서브쿼리의 사용은 Nested Loop 를 돌기 때문에 사용에 주의하자.

(ex) SELECT accountInfo from accounts where accountName in (select accountName from accountNames);       //accountNames 테이블에 있는 이름에 대해서만 accountInfo를 조회하는 쿼리(Validation)

간단한 쿼리문 들이라 대부분 자주 쓰다보니 외워진 상태이지만, SQL에 막 입문하는 사람이거나 쿼리문에 익숙하지 않은 분들은 이 내용만 알게되어도 어느정도 복잡한 쿼리문도 다룰 수 있을 것이다.

사실 MySQL은 이미 프로그래밍을 모르는 사람들도 들어봤을 정도로 너무 유명한 Database라 MySQL이 무엇인가를 얘기하는 건 진부한 주제일 지 모른다.

그렇듯 MySQL은 우리가 흔히 "데이터베이스" 라고 하는 시스템의 표준과 같은 소프트웨어이며 그만큼 RDBMS 중 세계적으로 가장 널리 사용되고 있는 소프트웨어이다.

RDBMS에 대해서 간단히 언급을 하자면, 저장한 데이터들 간의 관계를 명시하는 "관계형 데이터 모델링" 을 지원하는 DataBase Management System 이다. 데이터는 테이블에 명시된 여러 Column 값들을 포함하는 Tuple 또는 Record 로 구성되어 Row를 이룬다. 말이 좀 복잡하지만, 간단히 얘기하면 정의된 포맷대로 나열된 데이터의 목록이라고 생각하면 쉽다. 가령 Id 와 Password 를 저장하는 유저 정보의 데이터는 다음과 같이 나타내어질 수 있다.

위의 그림에서 테이블이 나타내는건 유저의 ID와 Password 이다. 이것이 일반적인 DBMS, 즉 데이터베이스 관리 시스템의 모습이다. ID 와 PASSWORD는 테이블을 구성하는 데이터를 묘사(Description)하는 데 기준이 되는 Column 이고, 그 아래의 Record 들은 실제 데이터가 담긴다. 적층된 데이터의 행들이 ROW 이다.

그렇다면 여기에 "관계" 를 추가해보자.

왼쪽 그림과 같은 기존의 유저 ID 관리 테이블에 이번엔 오른쪽의 유저 정보 테이블을 추가했다. 

그런데 자세히 보면, 같은 Column 에 같은 데이터를 갖는 Row를 확인 할 수 있다. 가령 "a123de" 라는 유저와 "jinsp" 라는 유저는 양쪽 테이블 모두에 있으며, 왼쪽 테이블에서는 해당 ID의 비밀번호를, 오른쪽 테이블에서는 해당 ID의 이름과 나이 정보를 알 수 있다.

이렇게 두 테이블은 ID라는 데이터를 기준으로 하는 "관계(Relation)" 를 갖고 있다고 할 수 있으며 RDBMS 는 이런 기초적인 관계에 대한 고민에서 출발한다.

MySQL은 이러한 RDBMS 중에서도 가장 널리 쓰이고 잘 알려져있으며 심지어 무료로 제공되는 훌륭한 소프트웨어라고 할 수 있다. (그 외에도 장점은 많지만, 차차 기술하도록 한다.)

실제로 실무에서도 대부분 MySQL 을 사용하며, 같은 갈래에서 나온 MariaDB나, PostgreSQL 과 같은 DBMS들도 MySQL 에서 상당 부분 아이디어를 공유한다. (물론 각 DB들에 따라 특색은 있다.)

MySQL 을 설치하게 되면 패키지 형태의 프로그램이 깔리게 되며, 이는 여러 가지 단위 모듈들로 구성되어 있다.

<그림 - 출처 MySQL 홈페이지>

엔진을 담당하는 MySQL 은 DB 자체이며, DB를 접근하기 위한 서버와 클라이언트의 형태를 제공한다. 기본적으로 DB에 접근하기 위한 인터페이스를 서버로 두고 있다. 이 서버는 기본 설정에 따르면 localhost IP 에 3306 포트로 열려있으며 이를 통해 MySQL 클라이언트가 이에 접근(Connect) 하여 질의(Query) 하는 구조로 되어 있다. 이때 질의하는 언어는 SQL이다.

즉, 외부에서 드라이버 또는 통신으로 접근 시에도 마찬가지로 TCP/IP 를 통해 MySQL 패키지의 클라이언트 또는 서버로 접근하여 MySQL DB 자체에 접근하게 되는 것이다. (당연해보이는 개념이지만, 개발 시에 SDK 형태로 제공되는 Driver 의 동작원리를 이해하는 것은 중요하다.)

MySQL Server 를 설치하게 되면, MySQL 서버 프로그램으로 MySQL 서비스와 mysqld 프로그램, MySQL 클라이언트 프로그램으로 mysql.exe(CLI 인터페이스), Workbench, slap, mysqladmin 등이 같이 설치된다.