관계형 데이터베이스란? + 예제

wannabeking·2022년 3월 28일

mysql 데이터베이스

데이터베이스

목록 보기

1/4

관계형 데이터베이스

관계형 데이터베이스의 중요성

어떤 구조이건 데이터베이스는 꼭 필요한 컴포넌트
이 데이터베이스를 잘 다루는 것이 좋은 개발자가 되기 위해 필요
- 기본은 SQL을 잘 아는 것
백엔드 개발자로써 중요한 부분
- 데이터 모델을 잘 만들고 그걸 프론트 개발자와 공유/협업
- 속도 개선을 위한 쿼리 성능 모니터링하고, 필요시 성능 개선 수행
  - 어떤 경우에는 이를 전담하는 사람: DBA(DataBase Administrator)

관계형 데이터베이스 (1)

구조화된 데이터를 저장하고 질의할 수 있도록 해주는 스토리지
- 엑셀 스프레드시트 형태의 테이블로 데이터를 정의하고 저장
  - 테이블에는 column(열)과 record(행)이 존재

관계형 데이터베이스 (2)

관계형 데이터베이스를 조작하는 프로그래밍 언어가 SQL
- 테이블 정의를 위한 DDL(Data Definition Language)
  - 앞서 보여준 테이블의 포맷을 정의해주는 언어
- 테이블 데이터 조작/질의를 위한 DML(Data Manipulation Language)
  - DDL로 정의된 테이블에 레코드를 추가, 수정, 삭제 혹은 읽어들이기 위해 사용하는 언어

대표적 관계형 데이터베이스

프로덕션 데이터베이스: MySQL, PostgreSQL, Oracle, ...
- OLTP(OnLine Transaction Processing)
- 빠른 속도에 집중, 서비스에 필요한 정보 저장
- MySQL이 바로 우리의 집중 탐구 대상
데이터 웨어하우스: Redshift, Snowflake, BigQuery, Hive, ...
- OLAP(OnLine Analytical Processing)
- 처리 데이터 크기에 집중, 데이터 분석 혹은 모델 빌딩 등을 위한 데이터 저장
  - 보통 프로덕션 데이터베이스를 복사해서 데이터 웨어하우스에 저장

관계형 데이터베이스의 구조

관계형 데이터베이스는 2 단계로 구성됨
- 가장 밑단에는 테이블들이 존재 (테이블은 엑셀의 시트에 해당)
- 테이블들은 데이터베이스(혹은 스키마)라는 폴더 밑으로 구성 (엑셀에서는 파일)
테이블의 구조 (테이블의 스키마라고 부르기도 함)
- 테이블은 레코드(행)들로 구성
- 레코드는 하나 이상의 필드(컬럼, 열)으로 구성
- 필드는 이름과 타입의 속성(primary key)으로 구성됨

SQL 소개

SQL(Structed Query Language)
- 관계형 데이터베이스에 있는 데이터(테이블)를 질의하거나 조작해주는 언어
SQL은 1970년대 초반에 IBM이 개발한 구조화된 데이터 질의 언어
두 종류의 언어로 구성됨
- DDL
  - 테이블의 구조를 정의하는 언어
- DML
  - 테이블에서 원하는 레코드들을 읽어오는 질의 언어
  - 테이블에 레코드를 추가/삭제/갱신해주는데 사용하는 언어
SQL은 빅데이터 세상에서도 중요
- 구조화된 데이터를 다루는한 SQL은 데이터 규모와 상관없이 쓰임
- 모든 대용량 데이터 웨어하우스는 SQL 기반
  - Redshift, Snowflake, BigQuery, Hive
- Spark나 Hadoop도 예외는 아님
  - SparkSQL과 Hive라는 SQL 언어가 지원됨
- 백엔드/프론트엔드/데이터 분야에서 반드시 필요한 기본 기술

SQL의 단점

구조화된 데이터를 다루는데 최적화가 되어있음
- 정규표현식을 통해 비구조화된 데이터를 어느 정도 다루는 것은 가능하나 제약이 심함
- 많은 관계형 데이터베이스들이 플랫한 구조만 지원함 (no nested like JSON)
  - 구글 빅쿼리는 nested structure를 지원함
- 비구조화된 데이터를 다루는데 Spark, Hadoop과 같은 분산 컴퓨팅 환경이 필요해짐
  - 즉 SQL만으로는 비구조화 데이터를 처리하지 못함
- 관계형 데이터베이스마다 SQL 문법이 조금씩 상이

Star schema

Production DB용 관계형 데이터베이스에서는 보통 스타 스키마를 사용해 데이터를 저장
데이터를 논리적 단위로 나눠 저장하고 필요시 조인
스토리지의 낭비가 덜하고 업데이트가 쉬움

Denormalized schema

NoSQL이나 데이터 웨어하우스에서 사용하는 방식
- 단위 테이블로 나눠 저장하지 않음으로 별도의 조인이 필요 없는 형태를 말함
이는 스토리지를 더 사용하지만 조인이 필요 없기에 빠른 계산이 가능

SQL 기본

먼저 다수의 SQL 문을 실행한다면 세미콜론으로 분리 필요
- SQL문1; SQL문2; SQL문3;
SQL 주석
- --: 인라인 한줄짜리 주석, 자바에서 //에 해당
- /* -- */: 여러 줄에 걸쳐 사용 가능한 주석
SQL 키워드는 대문자를 사용한다던지 하는 나름대로의 포맷팅이 필요
- 팀프로젝트라면 팀에서 사용하는 공통 포맷이 필요
테이블/필드이름의 명명규칙을 정하는 것이 중요
- 단수형 vs 복수형
  - User vs Users
- _ vs CamelCasing
  - user_session_channel vs UserSessionChannel

SQL DDL - 테이블 구조 정의 언어

CREATE TABLE
- Primary key 속성을 지정할 수 있음
  - Primary key uniqueness: 기본 키 보장
- 성능향상을 위해 인덱스를 지정할 수 있음
```
CREATE TABLE raw_Data.user_session_channel(
    user int,
    sessionid varchar(32) primary key,
    channel varchar(32)
);
```

DROP TABLE
- DROP TABLE table_name;
  - 없는 테이블을 지우려고 하는 경우 에러를 냄
- DROP TABLE IF EXISTS table_name;
- vs DELETE FROM
  - DELETE FROM은 조건에 맞는 레코드들을 지움 (테이블 자체는 존재)

ALTER TABLE

새로운 컬럼 추가

ALTER TABLE 테이블이름 ADD COLUMN 필드이름 필드타입;

기존 컬럼 이름 변경

ALTER TABLE 테이블이름 RENAME 현재필드이름 to 새필드이름;

기존 컬럼 제거

ALTER TABLE 테이블이름 DROP COLUMN 필드이름;

테이블 이름 변경

ALTER TABLE 현재테이블이름 RENAME to 새테이블이름;

SQL DML - 테이블 데이터 조작 언어

SELECT
- SELECT FROM
  - 테이블에서 레코드와 필드를 읽어오는데 사용
- WHERE를 사용해서 레코드 선택 조건을 지정
- GROUP BY
  - 정보를 그룹 레벨에서 뽑는데 사용
  - DAU, WAU, MAU 계산은 GROUP BY를 필요로 함
- ORDER BY를 사용해서 레코드 순서를 결정하기도 함
- 보통 다수의 테이블에 조인해서 사용하기도 함

레코드 추가/삭제/수정
- INSERT INTO
  - 테이블에 레코드를 추가하는데 사용
- UPDATE FROM
  - 테이블 레코드의 필드 값 수정
- DELETE FROM
  - 테이블에서 레코드를 삭제
  - TRUNCATE는 조건 없이 모든 레코드 삭제

MySQL 역사

1995년 스웨덴 회사였던 MySQL AB에 의해 개발된 관계형 데이터베이스
- 오픈소스로 시작됨
- My는 개발자 중 한 사람의 딸 이름이었음
2008년 썬 마이크로시스템이 MySQL AB를 $1B를 주고 인수
2009년 오라클이 썬을 인수하면서 MySQL이 유료화 여부가 쟁점이 됨
2010년 MySQL의 처음 개발자였던 Monty가 MySQL과 호환이 되는 MariaDB라는 오픈소스 개발

MySQL 종류와 버전

MariaDB
- 오픈소스
- MySQL 5.5에 기반해서 개발됨
- MySQL과 인터페이스는 동일하나 성능은 더 좋음
MySQL
- 두 가지 종류가 존재
  - MySQL Community Server: 오픈소스
  - MySQL Enterprise Server: 유료 버전, 다양한 플러그인 제공
- 최신 버전은 8.0

MySQL 특징

한동안 웹개발 표준 기술 스택 중 하나였음
- LAMP: Linux, Apache, MySQL, PHP
지금도 PostgreSQL와 함께 가장 널리 쓰이는 프로덕션용 관계형 데이터베이스
용량 증대(Scaling) 방식
- Scale-Up: 서버에 CPU와 Memory 추가
- Scale-Out: Master-Slave 구성
  - 일반적으로 클러스터 구성을 이야기하지만 MySQL은 이를 지원하지 못함

클라우드의 정의

컴퓨팅 자원(하드웨어, 소프트웨어 등)을 네트워크를 통해 서비스 형태로 제공
키워드
- "No Provisioning"
- "Pay As You Go"
자원(예를 들면 서버)을 필요한만큼 실시간으로 할당하여 사용한만큼 지불
- 탄력적으로 필요한만큼의 자원을 유지하는 것이 중요

클라우드 컴퓨팅이 없었다면?

서버/네트워크/스토리지 구매와 설정 등을 직접 수행해야 함
데이터센터 공간을 직접 확보(Co-location)
- 확장이 필요한 경우 공간을 먼저 더 확보해야함
그 공간에 서버를 구매하여 설치하고 네트워크 설정
- 보통 서버를 구매해서 설치하는데 적어도 두세달은 걸림
또한 Peak time을 기준으로 Capacity planning을 해야함
- 놀고 있는 자원들이 늘어나는 현상 발생
직업 운영비용 vs 클라우드 비용
- 기회비용

클라우드 컴퓨팅의 장점

초기 투자 비용이 크게 줄어듬
- CAPEX(Capital Expenditure) vs OPEX(Operating Expense)
리소스 준비를 위한 대기시간 대폭 감소
- Shorter Time to Market
노는 리소스 제거로 비용 감소
글로벌 확장 용이
소프트웨어 개발 시간 단축
- Managed Service(SaaS)이용

AWS 소개

가장 큰 클라우드 컴퓨팅 서비스 업체
2002년 아마존의 상품데이터를 API로 제공하면서 시작
- 현재 100여개의 서비스를 전세계 15개의 지역에서 제공
- 대부분의 서비스들이 오픈소스 프로젝트들을 기반으로 함
- 최근 들어 ML/AI 관련 서비스들도 출시
사용고객
- 다수의 상장업체들과 많은 국내 업체들도 사용 시작
다양한 종류의 소프트웨어/플랫폼 서비스를 제공
- AWS의 서비스만으로 쉽게 온라인서비스 생성
- 뒤에서 일부 서비스를 따로 설명

EC2 - Elastic Cloud Compute

AWS의 서버 호스팅 서비스
- 리눅스 혹은 윈도우 서버를 런치하고 로그인 가능
- 가상 서버들이라 전용서버에 비해 성능이 떨어짐
- Bare-metal 서버도 제공하기 시작
다양한 종류의 서버 타입 제공
- 예를 들어 미국 동부에서 스몰타입(t2.small)의 무료 리눅스 서버를 하나 할당 시
  - 시간당 2.3센트의 비용 지불
  - 2GB 메모리, 1 가상코어, 160GB HDD
  - 2012년에는 8.5센트 였음
  - Incoming network bandwidth는 무료지만 outgoing은 유료
세 종류의 구매 옵션
- On-Demand: 시간당 비용
- Reserved: 1년이나 3년간 사용 보장하고 30~40% 할인
- Spot Instance: 일종의 경매방식으로 놀고 있는 리소스들을 보다 싼 비용으로 사용

S3 - Simple Storage Service

아마존이 제공하는 대용량 클라우드 스토리지 서비스
S3는 데이터 저장관리를 위해 계층적 구조를 제공
글로벌 네임스페이스를 제공하기 때문에 탑레벨 디렉토리 이름 선정에 주의
S3에서는 디렉토리를 버킷이라고 부름
버킷이나 파일별로 액세스 컨트롤 가능
Low cost (1TB per month)
- Standard storage: $23
- Glacier storage: $4

Database Services

RDS(Relational Database Service)
- MySQL/MariaDB, PostgreSQL, Aurora
- Oracle, MS SQL Server
DynamoDB
Redshift
ElastiCache
Neptune(Graph database)
ElasticSearch
MongoDB

AI & ML Services

SageMaker
- 딥러닝, 머신러닝 end-toend 프레임워크
Lex
- Conversational Interface(Chatbot service)
Polly
- Text to Speech Engine
Rekognition
- Image Recognition Service

기타 중요 서비스

Amazon Alexa
- Amazon's Voice Bot Platform
Amazon Connect
- Amazon's Contact Center Solution (콜센터 구현)
Lambda
- Event-driven, serverless computing engine
- 서비스 구현을 위해서 EC2 런치할 필요 없음
- Google Cloud에는 Cloud Function이라는 이름으로 존재
- Azure에는 Azure Function이라는 이름으로 존재

Docker란 무엇인가?

예를 들어 MySQL을 다른 OS에서 설치하려면 다양한 변수가 존재
- 즉*설치 과정이 OS와 OS의 버전에 따라 달라지게 됨
- 다양한 다수의 다른 소프트웨어들의 설치가 동반되는 것이 일반적임
Docker
- 특정 프로그램과 그 프로그램을 실행하는데 필요한 기타 소프트웨어들을 하나의 패키지로 만듬으로써 해당 프로그램의 개발과 사용을 도와주는 오픈소스 플랫폼
- 이 패키지를 먼저 파일 시스템 형태로 만드는데 이를 Docker Image라고 함
  - 이 Image는 다른 이들과 공유 가능
  - Docker Image 공유소를 Docker Registry(Docker Hub)라고 부름
- Docker Image를 실행시킨 것을 Docker Container라고 부르며 응용프로그램에 해당

AWS RDS란?

AWS가 제공해주는 다양한 관계형 데이터베이스 서비스
- MySQL/MariaDB
- PostgreSQL
- Oracle
- Amazon Aurora
- Microsoft SQL Server

관계형 데이터베이스 예제 - 웹서비스 사용자/세션 정보

사용자 ID: 보통 웹서비스에서는 등록된 사용자마다 부여하는 유일한 ID
세션 ID: 세션마다 부여되는 ID
- 세션: 사용자의 방문을 논리적인 단위로 나눈 것
  - 사용자가 외부 링크(광고 등)를 타고 오거나 직접 방문해서 올 경우 세션을 생성
  - 사용자가 방문 후 30분간 interaction이 없다가 뭔가를 하는 경우 새로 세션을 생성
- 즉 하나의 사용자는 여러 개의 세션을 가질 수 있음
- 보통 세션의 경우 세션을 만들어낸 접점(경유지)를 채널이란 이름으로 기록해둠
  - 마케팅 관련 기여도 분석을 위함
- 또한 세션이 생긴 시간도 기록
이 정보를 기반으로 다양한 데이터 분석과 지표 설정이 가능
- 마케팅 관련, 사용자 트래픽 관련
- DAU, WAU, MAU 등의 일주월별 Active User 차트
- Marketing Channel Attribution 분석
  - 어느 채널에 광고를 하는 것이 가장 효과적인가?

관계형 데이터베이스 예제 - 우리가 저장하고 싶은 데이터

prod 데이터베이스
- session 테이블
  - id(int) NOT NULL, AUTO_INCREMENT, PRIMARY KEY
  - user_id(int) NOT NULL (user 테이블이 존재하면 FOREIGN KEY도)
  - created(timestamp) NOT NULL, DEFAULT CURRENT_TIMESTAMP
  - channel_id(int) NOT NULL, FOREIGN KEY
- channel 테이블
  - id(int) NOT NULL, AUTO_INCREMENT, PRIMARY KEY
  - channel(varchar(32)) NOT NULL

테이블 필드의 중요 속성

PRIMARY KEY
- 테이블에서 레코드의 유일성을 정의하는 필드: 그것이 기본 키가 되어야 함
  - 예를 들면 사용자 테이블에서 이메일 혹은 주민등록번호
- Composite primary key: 기본 키가 두개 혹은 그 이상의 필드로 정의되는 경우
- 기본 키로 지정된 필드가 있는 경우 데이터베이단에서 중복된 값을 갖는 레코드가 생기는 것을 방지함: primary key uniqueness constraint
Foreign key
- 테이블의 특정 필드가 다른 테이블의 필드에서 오는 값을 갖는 경우
NOT NULL
- 필드의 값이 항상 존재해야하는 경우
DEFAULT value
- 필드에 값이 주어지지 않은 경우 기본값을 정의해줌
- timestamp 타입: CURRENT_TIMESTAMP를 사용하면 현재 시간으로 설정

관계형 데이터베이스 예제 - channel table DDL

CREATE TABLE channel(
	id int not null auto_increment primary key,
    channel varchar(32) not null
);

-- 후자가 Composite primary key 사용하기 편함
CREATE TABLE channel(
	id int not null auto_increment,
    channel varchar(32) not null,
    primary key(id)
);

관계형 데이터베이스 예제 - session table DDL

CREATE TABLE session(
	id int not null auto_increment,
    user_id int not null,
    created timestampe not null default current_timestamp,
    channel_id int not null,
    primary key(id),
    foreign key(channel_id) references channel(id)
);

SELECT를 사용하기 전에

SHOW DATABASES;
USE prod; -- 데이터베이스 이름
SHOW TABLES;

SELECT

테이블(들)에서 레코드들(혹은 레코드 수)을 읽어오는데 사용
WHERE을 사용해 조건을 만족하는 레코드

SELECT [DISTINCT]필드이름1, 필드이름2, ... -- DISTINCT : 유일한 필드, 모든 필드 지칭 : *
INTO 새로운 테이블 이름 -- 테이블 생성 및 복사
FROM 테이블이름
WHERE 선택조건
GROUP BY 필드이름1, 필드이름2, ...
HAVING 선택조건
ORDER BY 필드이름 [ASC|DESC] -- 필드 이름 대신에 숫자 사용 가능
LIMIT N; -- 2,3과 같이 사용하면 2번째 인덱스(최상단 3번째)부터 3개 보여주기

COUNT

테이블의 모든 레코드 수 카운트

SELECT COUNT(value) FROM count_test; -- NULL 값은 제외

CASE WHEN

필드 값의 변환을 위해 사용 가능
- CASE WHEN 조건 THEN 참일때 값 ELSE 거짓일때 값 END 필드이름
여러 조건을 사용하여 변환하는 것도 가능

SELECT channel_id, CASE
    WHEN channel_id in (1, 5, 6) THEN 'Social-Media'
    WHEN channel_id in (2, 4) THEN 'Search-Engine'
    ELSE 'Something-Else'
END channel_type
FROM session;

NULL이란?

값이 존재하지 않음을 나타내는 상수, 0 혹은 ""과는 다름
필드 지정 시 값이 없는 경우 NULL로 지정 가능
- 테이블 정의 시 디폴트 값으로도 지정 가능
어떤 필드의 값이 NULL인지 아닌지 비교는 특수한 문법을 필요로 함
- field1 is NULL 혹은 field1 is not NULL
NULL이 사칙연산에 사용되면 그 결과는?
- SELECT 0 + NULL, 0 - NULL, 0 * NULL, 0 / NULL

WHERE

IN
- WHERE channel_id in (3, 4)
  - WHERE channel_id = 3 OR channel_id = 4
- NOT IN
LIKE
- 대소문자 구별 없이 문자열 매칭 기능을 제공해줌
- WHERE channel LIKE 'G%' -> 'G*'
- WHERE channel LIKE '%o%' -> 'o'
- NOT LIKE
BETWEEN
- 날짜 범위에 사용 가능
위의 오퍼레이터들은 CASE WHEN 사이에서도 사용가능

STRING Functions

LEFT(str,N) : 왼쪽부터 N개의 문자
REPLACE(str, exp1, exp2) : exp1를 exp2로 치환
UPPER(str) : 대문자로 치환
LOWER(str) : 소문자로 치환
LENGTH(str) : 길이 반환
LPAD(str, N, exp), RPAD(str, N, exp) : 왼쪽 or 오른쪽 exp N개 패딩
SUBSTRING(str, start, length) : 문자열 시작지점부터 길이만큼 잘라서 반환
CONCAT(str1, str2, str3, ...) : 합치기

ORDER BY

디폴트 순서는 오름차순 ASC (작은 값이 먼저 나옴)
- ORDER BY 1 ASC
내림차순(Descending)을 원하면 DESC
- ORDER BY 1 DESC
여러개의 필드를 사용해서 정렬하려면
- ORDER BY 1 DESC, 2, 3
NULL 값 순서는?
- NULL 값들은 오름차순 일 경우 처음에 위치, 내림차순 일 경우 마지막에 위치

타입 변환

DATE Conversion:
- NOW
- 타임존 관련 변환
  - CONVERT_TZ(now(), 'GMT', 'Asia/Seoul')
- DATE, WEEK, MONTH, YEAR, HOUR, MINUTE, SECOND, QUARTER, MONTHNAME
- DATEDIFF
- DATE_ADD
- ...
STR_TO_DATE, DATE_FORMAT

SELECT
    created, CONVERT_TZ(created, 'GMT', 'Asia/Seoul') seoul_time,
    YEAR(created) y, QUARTER(created) q, MONTH(created) m, MONTHNAME(created) mn,
    DATE(created) d, HOUR(created) h, MINUTE(created) m, SECOND(created) s
FROM session
LIMIT 10;

2019-01-01 00:06:48 | 2019-01-01 09:06:48 | 2019 | 1 | 1 | January | 2019-01-01 | 0 | 6 | 48

SELECT created,
    DATEDIFF(now(), created) gap_in_days
    DATE_ADD(created, INTERVAL 10 DAY) ten_days_after_created
FROM session
LIMIT 10;

2019-01-01 00:06:48 | 936 | 2019-01-11 00:06:48

SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y'); -- 2013-05-01

Type Casting

1/2의 결과는?
- 정수간의 연산은 정수가 되어야하기 때문에 0
  - 분자나 분모 중에 하나를 float으로 캐스팅해야 0.5가 나옴
  - 이는 프로그래밍 언어에서도 일반적으로 동일하게 동작함
- cast 함수를 사용
  - cast(category as float)
  - convert(expression, float)
```
SELECT cast('100.0' as float), convert('100.0', float);
```

GROUP BY & Aggregate 함수

테이블의 레코드를 그룹핑하여 그룹별로 다양한 정보를 계싼
이는 두 단계로 이뤄짐
- 먼저 그룹핑을 할 필드를 결정 (하나 이상의 필드가 될 수 있음)
  - GROUP BY로 지정 (필드 이름을 사용하거나 필드 일련번호를 사용)
- 다음 그룹별로 계산할 내용을 결정
  - 여기서 Aggregate함수를 사용
  - COUNT, SUM, AVG, MIN, MAX, GROUP_CONCAT ...
    - 보통 필드 이름을 지정하는 것이 일반적 (alias)
월별 세션수를 계산하는 SQL

SELECT
    LEFT(created, 7) AS mon,
    COUNT(1) AS session_count
FROM prod.session
GROUP BY 1 -- GROUP BY mon, GROUP BY LEFT(created, 7)
ORDER BY 1;

가장 많이 사용된 채널은 무엇인가?

SELECT	
    channel_id,
    COUNT(1) AS session_count,
    COUNT(DISTINCT user_id) AS user_count
FROM prod.session
GROUP BY 1 -- GROUP BY channel_id
ORDER BY 2 DESC; -- ORDER BY session_count DESC

가장 많은 세션을 만들어낸 사용자 ID는 무엇인가?

SELECT
    user_id,
    COUNT(1) AS count
FROM pord.session
GROUP BY 1 -- GROUP BY user_id
ORDER BY 2 DESC -- ORDER BY count
LIMIT 1;

월별 유니크한 사용자 수

이게 바로 MAU(Monthly Active User)에 해당
필요한 정보 : 시간 정보, 사용자 정보

SELECT
    LEFT(created, 7) AS mon,
    COUNT(DISTINCT user_id) AS user_count
FROM prod.session
GROUP BY 1; -- GROUP BY mon, GROUP BY LEFT(created, 7)
ORDER BY 1;

월별 채널별 유니크한 사용자 수

필요한 정보 : 시간 정보, 사용자 정보, 채널 정보
먼저 어느 테이블을 사용해야 하는지 생각
Alias를 먼저 이해(s, c)
JOIN이란 결국 서로 다른 테이블에 존재하는 레코드들을 특정 조건을 바탕으로 병합하는 작업

SELECT
    LEFT(s.created, 7) AS mon,
    c.channel,
    COUNT(DISTINCT user_id) AS mau
FROM session s
JOIN channel c ON c.id = s.channel_id;
GROUP BY 1, 2
ORDER BY 1 DESC, 2;

MySQL에서 지원하는 컬럼 타입

Numeric Type
- INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT
- DECIMAL, NUMERIC
- FLOAT, DOUBLE, BIT
**Date and Time Type
- DATE, DATETIME, TIMESTAMP, TIME, YEAR
String Type
- CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, SET
JSON Type
- 다양한 JSON 조작함수를 제공함
**Spatial Type
- 위도와 경도를 중심으로한 위치 관련 타입

INSERT

뒤의 조인에서 사용할 테이블 2개를 생성하고 INSERT로 추가해보자

CREATE TABLE pord.vital (
    user_id int not null,
    vital_id int primary key,
    date timestamp not null,
    weight int not null
);

CREATE TABLE pord.alert (
    alert_id int primary key,
    vital_id int,
    alert_type varchar(32),
    date timestamp,
    user_id int
);

INSERT INTO prod.vital(user_id, vital_id, date, weight) VALUES(100, 1, '2020-01-01', 75);
INSERT INTO prod.vital(user_id, vital_id, date, weight) VALUES(100, 3, '2020-01-02', 78);
INSERT INTO prod.vital(user_id, vital_id, date, weight) VALUES(101, 2, '2020-01-01', 90);
INSERT INTO prod.vital(user_id, vital_id, date, weight) VALUES(101, 4, '2020-01-02', 95);
INSERT INTO prod.vital(user_id, vital_id, date, weight) VALUES(999, 5, '2020-01-02', -1);
INSERT INTO prod.vital(user_id, vital_id, date, weight) VALUES(999, 5, '2020-01-02', 10);

INSERT INTO prod.alert VALUES(1, 4, 'WeightIncrease', '2020-01-02', 101);
INSERT INTO prod.alert VALUES(2, NULL, 'MissingVital', '2020-01-04', 100);
INSERT INTO prod.alert VALUES(3, NULL, 'MissingVital', '2020-01-04', 101);

DELETE

조건을 기반으로 테이블에서 레코드 삭제 혹은 모든 레코드 삭제
- 후자의 경우에도 테이블은 계속 존재
DELETE FROM vs TRUNCATE
- 차이점을 이해하는 것이 중요
- TRUNCATE는 조건없이 모든 레코드 삭제, 속도가 빠른 대신 트랜잭션 사용 시 롤백 불가

DELETE FROM prod.vital WHERE weight <= 0;
DELETE FROM prod.vital;
SELECT * FROM prod.vital;

UPDATE

조건을 기반으로 테이블에서 특정 레코드(들)의 필드 값 수정 가능
예: vital_id가 4인 레코드의 weight을 92로 변경

SELECT * FROM prod.vital WHERE vital_id = 4;

UPDATE prod.vital
SET weight = 92
WHERE vital_id = 4;

SELECT * FROM prod.vital WHERE vital_id = 4;

JOIN이란?

SQL 조인은 두 개 이상의 테이블들을 공통 필드를 가지고 통합
- 스타 스키마로 구성된 테이블들로 분산되어 있던 정보를 통합하는데 사용
JOIN의 결과로 양쪽의 필드를 모두 가진 새로운 테이블을 만들어짐
- 조인의 방식에 따라 다음 두 가지가 달라짐
  - 어떤 레코드들이 선택되는지?
  - 어떤 필드들이 채워지는지?

JOIN 문법

SELECT A.*, B.*
FROM raw_data.table1 A
JOIN raw_data.table2 B ON A.key1 = B.key1 and A.key2 = B.key2
WHERE A.ts >= '2019-01-01';

JOIN 시 고려할 점

먼저 중복 레코드가 없고 Primary Key의 uniqueness가 보장됨을 체크
- 아주 중요함!
조인하는 테이블들간의 관계를 명확하게 정의
- One to one
  - 완전한 one to one : session & session_channel
  - 한쪽이 부분집합이 되는 one to one
- One to many? (order vs order_items)
  - 이 경우 중복이 더 큰 문제됨 -> 증폭!
- Many to one?
  - 방향만 마꾸면 one to many로 보는 것과 사실상 동일
- Many to many?
  - 이는 one to one이나 one to many로 바꾸는 것이 가능하다면 변환하여 조인하는 것이 덜 위험
어느 테이블을 베이스로 잡을지 결정해야 함

JOIN의 종류

INNER JOIN
- 양쪽 테이블에서 매치가 되는 레코드들만 리턴함
- 양쪽 테이블의 필드가 모두 채워진 상태로 리턴 됨
```
SELECT * FROM vital v
JOIN alert a ON v.vital_id = a.vital_id;
```
LEFT JOIN
- 왼쪽 테이블(Base)의 모든 레코드들을 리턴
- 오른쪽 테이블의 필드는 왼쪽 레코드와 매칭되는 경우에만 채워진 상태로 리턴
```
SELECT * FROM vital v
LEFT JOIN alert a ON v.vital_id = a.vital_id;
```
RIGHT JOIN
- LEFT JOIN과 반대
FULL OUTER JOIN
- MySQL은 이를 지원하지 않음
- LEFT JOIN과 RIGHT JOIN을 UNION하는 것으로 대체 가능
- 왼쪽 테이블과 오른쪽 테이블의 모든 레코드들을 리턴
- 매칭되는 경우에만 양쪽 테이블의 모든 필드들이 채워진 상태로 리턴
```
SELECT * FROM vital v
LEFT JOIN alert a ON v.vital_id = a.vital_id
UNION -- (UNION ALL) UNION은 중복 레코드 X, UNION ALL은 중복 레코드 포함
SELECT * FROM vital v
RIGHT JOIN alert a ON v.vital_id = a.vital_id;
```

SELF JOIN

동일한 테이블을 alias 달리해서 자기 자신과 조인

SELECT * FROM vital v1
JOIN vital v2 ON v1.vital_id = v2.vital_id;

CROSS JOIN
- 왼쪽 테이블과 오른쪽 테이블의 모든 레코드들의 조합을 리턴함
```
SELECT * FROM vital v CROSS JOIN alert a;
```

트랜잭션

트랜잭션이란?
- SQL들을 묶어서 하나의 작업처럼 처리하는 것
  - DDL, DML 중 레코드를 수정한 것에만 의미가 있음
  - BEGIN과 END or BEGIN과 COMMIT 사이에 해당 SQL들을 사용
  - ROLLBACK으로 되돌릴 수 있음
예시
- 은행 계좌 이체
  - 인출은 성공했는데 입금이 실패한다면 심각한 오류
  - 인출과 입금을 트랜잭션으로 묶어서 해결
```
BEGIN; -- START TRANSACTION
    A의 계좌로부터 인출;
    B의 계좌로 입금;
END; --COMMIT
```
autocommit
- autocommit = True
  - 모든 레코드 수정이 기본적으로 데이터베이스에 쓰여짐(Commit)
  - 특정 작업을 트랜잭션으로 묶고 싶다면 BEGIN/END 사용
- autocommit = False
  - 모든 레코드 수정이 COMMIT이 호출될 때까지 커밋되지 않음
  - 명시적으로 커밋을 해야함

View

View란?
- 자주 사용하는 SQL 쿼리(SELECT)에 이름을 주고 그 사용을 쉽게 하는 것
  - 이름이 있는 쿼리가 View로 데이터베이스단에 저장됨
    - View가 사용될 때마다 SELECT가 실행되는 것으로 가상테이블이라고 부르기도 함
문법

CREATE OR REPLACE VIEW 뷰이름 AS SELECT...;

SELECT * FROM 뷰이름;

Stored Procedure

Stored Procedure란?
- MySQL 서버단에 저장되는 SQL 쿼리들
  - CREATE PROCEDURE 사용
  - DROP PROCEDURE [IF EXISTS]로 제거
- 매개변수 사용 가능
- 간단한 조건문과 반복문을 통한 프로그래밍 가능
- 디버깅이 힘들고 서버단에 부하를 일으키는 것이 단점
- 인자는 IN, OUT, INOUT
- SQL 안에서 사용 불가능
문법

DELIMITER //
CREATE PROCEDURE 프로시저명(매개변수1, 매개변수2, ...)
BEGIN
    statements;
END //
DELIMITER;

CALL 프로시저명(인자1, 인자2, ...)

Stored Fuction

Stored Function이란?
- 값(Scalar)을 하나 리턴하는 서버쪽 메소드
  - Deterministic
  - Non Deterministic
- 모든 함수의 인자는 IN
- SQL 안에서 사용 가능
문법

DELIMITER $$
CREATE FUNCTION 함수명(인자)
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    DECLARE 변수명 변수타입;
    로직
    RETURN (리턴할 변수명);
END$$

Trigger

Trigger란?
- INSERT/DELETE/UPDATE 실행 전후에 특정 작업을 수행
- NEW/OLD modifier
  - NEW : INSERT와 UPDATE에서만 사용가능
  - OLD : DELETE와 UPDATE에서만 사용가능
문법

CREATE TRIGGER 트리거명
{BEFORE|AFTER}{INSERT|UPDATE|DELETE}
ON 테이블명 FOR EACH ROW
trigger_body;

중요 테이블의 경우 감사(audit)가 필요

CREATE TRIGGER before_update_name_gender
    BEFORE UPDATE ON name_gender
    FOR EACH ROW
  INSERT INTO name_gender_audit
  SET name = OLD.name,
    gender = OLD.gender,
    modified = NOW();
    
-- 사용 예
UPDATRE name_gender
SET name = 'abc'
WHERE name = 'abc2';

SELECT * FROM name_gender_audit;

Explain SQL

Explain SQL이란?
- SELECT/UPDATE/INSERT/DELETE 등의 쿼리가 어떻게 수행되는지 내부를 보여주는 SQL 명령
  - MySQL이 해당 쿼리를 어떻게 실행할지 Execution Plan을 보여줌
  - 보통 느린 쿼리의 경우 문제가 되는 테이블에 인덱스를 붙이는 것이 일반적
문법

EXPLAIN SELECT
  LEFT(s.created, 7) AS mon,
  c.channel,
  COUNT(DISTINCT user_id) AS mau
FROM session s
JOIN channel c ON c.id = s.channel_id
GROUP BY 1, 2
ORDER BY 1 DESC, 2;

Index

Index란?
- 테이블에서 특정 찾기 작업을 빠르게 수행하기 위해서 별도로 만드는 데이터 구조
  - 컴럼별로 만들어 짐
  - PK, FK로 지정된 컬럼은 기본적으로 Index 생성
- INDEX와 KEY는 동의어
- Index는 SELECT/DELETE/JOIN 명령을 빠르게 하지만, INSERT/UPDATE는 느림
  - 테이블에 너무 많은 Index를 추가하면 로딩으로 인한 오버헤드로 인해 성능 저하
문법

CREATE TABLE example (
  id INT NOT NULL AUTO_INCREMENT;
  index_col VARCHAR(20),
  PRIMARY KEY (id),
  INDEX index_name (index_col)
);

ALTER TABLE 테이블명 ADD INDEX (컬럼명);
ALTER TABLE 테이블명 ADD UNIQUE (컬럼명); -- 유니크한 인덱스
ALTER TABLE 테이블명 ADD FULLTEXT (컬럼명); -- 문자열 검색 빠름
ALTER TABLE 테이블명 DROP INDEX (컬럼명);

CREATE UNIQUE INDEX 인덱스명 ON 테이블명(컬럼1, 컬럼2, ...);

wannabeking

내일은 개발왕 😎

다음 포스트