이제 처음부터 끝까지 직접 만들어서 운영중인 서비스가 벌써 운영 9개월차를 맞이하고 있다. 아무래도 초기 서비스이기에 최초 런칭 이후에도 수없이 많은 A/B 테스트들이 있었고 그만큼 많은 변화들이 있었다.
이번 업데이트에서는 지금까지 모든 유저에게 쌓여왔던 데이터들을 새로운 테이블로 마이그레이션 하는 작업이 포함되었는데, 그렇다 보니 여러모로 Mysql 을 아직 잘 모르고 있구나, 하는 부족함이 느껴졌었다. 그래서 이번 기회에 Mysql 관련 읽으면 좋은 책을 찬찬히 읽고 알았던 내용들, 몰랐던 내용들을 찬찬히 정리해보는 시간을 갖으려 한다.
원페이지에 정리하느라 다소 긴 글이 되었지만, Mysql 관련해서 막힐 때 한 곳에서 cmd + F 명령어로 쉽게 찾아보고 싶어서 일부러 한 페이지에 정리를 해두었다.
Mysql
•
Oracle 사에서 제작한 DBMS 소프트웨어로 오픈소스임
Workbench
•
수행시간이 긴 쿼리를 수행할 때에는 edit > preference 에서 timeout interval 관련 설정을 0으로 두면 시간제한이 없어진다.
모델링
•
현실 세계 -> DBMS 의 데이터베이스 개체로 옮기는 과정
•
모델링하는 과정 살펴보기 - 쇼핑몰 (p140)
◦
고객
◦
구매기록
권한
•
사용자 생성, 권한부여, 삭제
데이터타입
•
숫자
◦
bit, tinyint, smallint, mediumint, int, bigint
◦
unsigned 예약어를 붙여주어 양수만 표현할 수 있다.
▪
사라진 음수범위만큼 양수범위의 표현범위가 늘어난다.
◦
float, double, decimal
▪
decimal : 정확한 값을 표현. ex) decimal(9,2)
▪
float, double : 근사치를 표현.
•
문자
◦
char, varchar
▪
char : 고정 자리수
•
insert, update 시에 더 좋은 성능을 발휘한다.
▪
varchar : 가변 자리수
▪
binary, barbinary
▪
tinytext, text, mediumtext, longtext
▪
텍스트 데이터
•
tinyblob, blob, mediumblob, longblob
▪
사진, 음성, 파일 등의 이진 데이터
▪
enum
▪
set
◦
일부 DBMS 에서는 한글(유니코드 2byte)과 영어(영문자 1byte)에 메모리를 다르게 할당하여, 보통 영어는 varchar, char 를 사용하고 한글은 nvarchar, nchar를 할당하여 테이블을 설계한다.
◦
하지만 mysql 에서는 varchar(10) 의 경우, 영어나 한글 모두 10자까지만 허용이 되도록 되어있기 때문에 mysql 에서는 굳이 nvarchar, nchar 데이터타입을 사용할 필요가 없다.
•
날짜와 시간
◦
date
◦
time
◦
datetime
◦
timestamp
◦
year
•
기타데이터
•
geometry : 공간데이터 형식. 선, 점, 다각형과 같은 공간 데이터 개체를 저장하고 조작
•
json : json 문서를 저장
형변환
•
명시적인 형변환
◦
cast(), convert()
▪
두 함수는 형식만 다르고 거의 같은 역할을 한다
•
암시적인 형변환
◦
‘100’ + ‘200’ = 300 : 문자열 -> 숫자
◦
concat(‘100’, ‘200’) = 100200 : 문자열+문자열
◦
concat(100, ‘200’) = 100200 : 정수 -> 문자열
데이터
•
데이터로 NULL 입력시, char 타입에서는 공간 전체를 차지하는 반면 varchar 타입에서는 공간을 사용하지 않는다. 따라서 NULL 값 입력이 많은 컬럼은 varchar 타입으로 지정하는 것이 유리하다.
DDL
•
데이터베이스 개체 (데이터베이스, 테이블, 뷰, 인덱스, 트리거, 프로시저 등) 를 생성, 삭제, 변경하는 언어
•
create, alter, drop
•
DDL 은 트랜잭션이 적용되지 않는다. 롤백불가함
•
테이블 복사
◦
CREATE TABLE … SELECT
▪
FK 등의 제약조건은 복사되지 않고 테이블 구조와 내부 데이터만 복사된다
•
ROLLUP 으로 총합, 중간합계
select num, groupName, sum(price * amount) as 비용
from buytbl
group by groupName, num
with rollup;
SQL
복사
DML
•
데이터를 조작하는 언어
•
select, insert, update, delete
•
transaction 이 발생한다.
DCL
•
사용자에게 어떤 권한을 부여하거나 빼앗을 때 사용하는 구문
•
grant, revoke, deny 등
SELECT
INSERT
•
데이터의 삽입
◦
insert into table values (datas);
◦
insert into (column names) values (datas)
•
auto_increment
◦
자동증가
◦
시작점 다시 초기화하기
▪
alter table <table name> auto_increment=?;
◦
증가폭 커스텀하기 (default : 1)\
▪
set @@auto_increment=3;
•
대량데이터 삽입
◦
insert into <table> select ... ;
•
ignore
◦
insert 시에 해당 구문이 오류가 나도 다음 구문이 실행된다.
◦
insert ignore into member values ();
•
on duplicate key update
◦
insert 구문 실행 시, 키가 중복되면 데이터를 업데이트 하도록 한다.
◦
insert into member values () on duplicate key update name=?, addr=?;
UPDATE
•
데이터의 수정
•
update user set name = ?, phone_number = ? where 조건;
DELETE
•
delete from 테이블 where 조건
•
실습(p225) : 대용량 테이블 삭제하기
◦
아래의 세 개의 쿼리의 수행시간 비교하기
▪
delete from user;
▪
drop table user;
▪
truncate table user;
◦
결과
▪
delete > truncate > drop 순으로 시간이 오래 걸렸다.
▪
delete : DML 이기 때문에 transaction 이 발생함.
▪
truncate, drop : DDL 이기 때문에 로그 기록하지 않아서 수행시간이 엄청 빠름
▪
따라서 테이블이 필요없는 경우 drop, 테이블이 필요한 경우 truncate 로 전체 데이터를 삭제하는 편이 좋다.
with and CTE
•
뷰, 파생테이블, 임시테이블 등을 간결하게 표현하기 위해 사용하는 구문
•
with CTE_테이블이름(열이름) AS ( 쿼리문 ) select 열 이름 from CTE_테이블이름;
•
즉 서브쿼리 등을 쓸 때 복잡한 구문을 간결하게 표현해주는 장점이 있다.
•
중복적으로 사용이 가능하다
◦
vs. View
◦
view : 계속 남아있어서 다른 구문에서도 사용될 수 있다.
◦
cte : 해당 구문이 실행되면 소멸된다. 재사용 불가하다.
SQL 문에 조건걸기
•
where
•
부등호 관계연산자 사용
•
between and
•
in()
•
like ‘%word%’
•
Any, All, Some 과 서브쿼리
•
서브쿼리를 이용해서 조건절 데이터를 넣을 때, 사용할 수 있는 키워드들
◦
ANY = SOME : > ANY
◦
여러개의 결과 중 하나만 만족해도 됨
◦
select * from usertbl where heigh > any (select height from usertbl from addr = ‘경남’);
▪
결과 : 173 혹은 170보다 큰 경우가 모두 출력됨
▪
ALL : > ALL
▪
여러개의 결과를 모두 만족시켜야함
•
select * from usertbl where heigh > all (select height from usertbl from addr = ‘경남’);
•
결과 : 173보다 크고, 170보다 큰 경우가 모두 출력됨
•
= ANY() = in ()
정렬 - Order by
•
ASC, DESC
•
위치 : 항상 SELECT 문의 제일 마지막에 위치해야한다.
•
mysql 성능을 떨어뜨릴 위험이 있으므로 꼭 필요한 경우가 아니라면 사용하지 않는게 좋다.
중복제거 - DiSTINCT
출력 개수를 제한 - LIMIT
•
limit n : n개만 출력
•
limit 시작, 개수 : limit 0, 5
•
limit 개수 offset 시작 : limit 5 offset 0
그룹함수 - GROUP BY, HAVING, 그리고 집계함수
•
group 함수(집계함수)가 쓰인 쿼리에 조건을 걸려면 where 가 아니라 having 을 이용한다.
•
집계함수 종류
◦
sum()
◦
avg()
◦
min()
◦
max()
◦
count()
▪
count(distinct)
◦
stdev() : 표준편차
◦
var_samp() : 분산
◦
example
▪
select user_id, sum(amount) from users group by user_id;
▪
최대키, 최소키를 가진 사람 이름과 함께 출력하기
▪
select name, height from user where height = (select max(height) from user) or height = (select min(height) from user);
Schema
•
데이터베이스/스키마 선택하기
•
현재 데이터베이스/스키마 내부의 모든 테이블 조회
•
데이터베이스 개체 이름 규칙
◦
64자 제한
◦
소문자 기준. 대문자도 소문자로 인식됨
◦
linux 에서는 반드시 소문자로 사용해야함
◦
예약서 사용불가 : ex) select
자주쓰이는 함수들
•
제어흐름
◦
IF(수식, 참, 거짓)
◦
IFNULL(수식1, 수식2) : null 이면 수식1, null이 아니면 수식2
◦
NULLIF(수식1, 수식2) : 수식1 = 수식2 면 null, 다르면 수식1
◦
case … when … else … end as ‘alias’
•
문자열함수
◦
ASCII(아스키코드) = 숫자
◦
CHAR(숫자) = 아스키코드 문자
◦
length(문자열)
◦
concat(문자열, 문자열, 문자열)
◦
concat_ws(구분자, 문자열, 문자열) : 구분자와 함께 연결해줌
SQL 프로그래밍
•
변수 사용하기
◦
SET @변수
▪
전역변수처럼 사용
▪
워크밴치 종료후에는 소멸된다.
▪
예시
set @변수명 = ?; select 변수명;
◦
스토어드 프로시저 or 함수 내부
◦
declare 로 변수 선언 후 내부에서는 그냥 변수명만 사용함. 지역변수 같은 느낌
◦
Prepare + execute 으로 limit 구문에 변수 사용이 가능하다. (p268 참고)
테이블과 뷰 | Table and View
•
테이블
◦
Mysql 5.0 부터 테이블 압축 기능이 추가되었다.
▪
압축 테이블에 데이터를 입력하면 시간이 그냥 테이블의 2배정도 소용된다.
▪
하지만 테이블 상태를 보면 평균 행 길이나 데이터 길이가 2배정도 작다.
▪
테이블 생성할때만 지정하면 그 이후로는 일반 테이블과 사용방법이 같다.
▪
대용량 데이터가 가득한 테이블에 적용하면 유리할 듯
◦
임시테이블 Temporary table 이라는 것이 있다.
▪
mysql 세션 내에서만 존재하므로 워크벤치 세션 창을 닫으면 사라진다.
▪
기존 일반적인 테이블과 같은 이름을 사용할 수 있다.
▪
하지만 해당 이름으로 데이터를 조회할 경우, 임시테이블이 우선순위를 갖게되어 기존 테이블 데이터는 조회되지 않는다.
▪
활용 아이디어
•
데이터 마이그레이션 처럼 임시로 테이블이 존재해야하는 경우들에서 종종 사용할 수 있을 것 같다.
•
뷰 view
◦
테이블과 동일하게 생각하고 접근해도 된다. 비슷한 개념. 테이블에서 필요한/자주쓰이는 컬럼만 뽑아놓은 엑기스 같은 느낌
◦
뷰는 보통 읽기 전용으로 많이 사용됨. 하지만 수정도 가능은 함! → 별로 추천하지 않음
▪
테이블, 뷰 두 군데서 데이터를 수정할 수 있다면 혼란스러울 수 있다.
◦
장점
▪
보안에 도움이 된다. 개인정보인 출생년도, 연락처 등 민감한 정보는 제외하고 조회할 수 있다.
▪
매번 사용되는 특정 컬럼을 뽑는 복잡한 쿼리를 단순화할 수 있다.
•
테이블 스페이스
◦
각 테이블이 실제 저장되는 물리적인 공간을 의미함
◦
기본적으로는 모든 테이블이 시스템 테이블스페이스에 저장됨
◦
대용량 테이블의 경우 별도의 테이블스페이스에 저장하는 것이 성능에 효과적이다.
인덱스 | Index
•
개념
◦
데이터를 좀 더 빠르게 찾을 수 있도록 해주는 도구
◦
반대개념 : 전체 테이블 검색 full table scan
•
주의할 점
◦
불필요한 인덱스를 만들 경우에는 용량만 더 차지하고 오히려 검색속도가 느려진다.
•
장점
◦
검색속도 향상
◦
쿼리의 부하가 줄어서 전체적인 시스템 성능향상 가능
•
단점
◦
데이터베이스의 10% 정도 크기의 추가공간이 필요하다.
◦
처음 인덱스 생성시 시간 소요가 된다.
◦
변경작업이 자주 일어날 경우에는 오히려 성능이 많이 나빠질 수 있다.
•
종류
◦
클러스터형 : 영어사전
▪
테이블 당 1개 생성 가능
▪
인덱스로 지정한 열에 맞춰서 자동 정렬한다.
◦
보조 : 책 뒤의 찾아보기
▪
테이블 당 여러개 생성 가능
•
PK, Unique 에는 자동으로 인덱스가 설정됨
◦
PK, Unique + NotNull 에는 자동으로 클러스터형 인덱스가 설정된다.
◦
Unique 에는 자동으로 보조 인덱스가 설정된다.
◦
PK vs Unique + NotNull : PK 우선적으로 클러스터형 인덱스가 설정됨
◦
PK 지정한 열로 데이터가 오름차순으로 정렬된다.
•
주의할 점
◦
신규로 생성할 때 인덱스가 적용되는 건 시간소요가 적다.
◦
기존 운영되고 있는 테이블에 PK 나 Unique 키를 지정해서 인덱스를 지정하는 것은 시간소요가 클 수 있으니 주의해야한다.
•
B-Tree 구조를 갖는다. (p384)
◦
균형 잡힌 트리 : 노드
◦
Mysql 에서는 노드 대신 페이지라는 개념을 사용함. 한 페이지당 16kb 저장가능.
◦
인덱스를 구성할 때 B-tree 구조를 갖는다.
◦
검색시 뛰어난 성능 발휘 - 검색 대상을 효과적으로 좁힌다.
◦
페이지 분할
▪
INSERT, UPDATE, DELETE 등 데이터의 수정이 잦은 테이블에서는 오히려 역효과 발생 가능
▪
페이지 분할이 일어나기 때문임!
•
생성과 삭제
◦
CREATE INDEX
◦
DROP INDEX
◦
삭제 시에는 보조 → 클러스터 순서로
◦
PK 에 설정된 인덱스는 Alter 문으로만 삭제가 가능하다.
•
성능비교 (p394)
◦
보통의 경우 클러스터형 인덱스 > 보조 인덱스
◦
클러스터형 페이지 구조
▪
루트 페이지
▪
리프 페이지 = 데이터페이지
◦
보조 인덱스 페이지 구조
▪
루트 페이지
▪
리프 페이지 : 데이터 페이지의 주소값
▪
데이터페이지 (heap 영역)
◦
테이터 읽기 / 검색속도
▪
클러스터형 > 보조
◦
데이터 입력 / 수정 / 삭제
▪
보조 > 클러스터형
▪
보조 인덱스는 정렬을 하지 않기 때문에 데이터 수정시에 더 유리
•
클러스터형 인덱스
◦
인덱스 생성 시 데이터 페이지 전체가 다시 정렬됨 → 대용량 데이터일 경우 부하가 심할 수 있으니 주의
◦
인덱스 자체의 리프 페이지 = 데이터 : 인덱스 자체에 데이터가 포함되어있다.
•
보조 인덱스
◦
데이터 페이지는 그대로 두고 별도의 페이지에서 인덱스를 구성함
◦
리프 페이지 = 데이터가 위치하는 주소값
◦
한 테이블 당 여러개 생성 가능하지만 남용할 경우 성능 저하 심각
•
혼합해서 사용하기
•
주의할 점
◦
데이터의 종류가 적은 열에는 사용하지 않는게 나을 수 있다.
프로시저 | Stored Program - Procedures
•
Mysql 에서 제공하는 프로그래밍 기능
•
매개변수 사용 가능, 호출은 CALL 로
•
특징 / 장점
◦
Mysql 성능을 향상시킬 수 있다.
▪
긴 쿼리를 서버 → DB 로 전달하지 않고 미리 생성된 프로시저를 호출하는 것만으로도 충분히 큰 성능향상에 도움이 된다.
▪
Mysql 은 호출될때마다 컴파일 되기 때문에 그 효과가 크지는 않지만, 다른 DBMS 는 1회 호출 때만 컴파일 되고 이후에는 메모리에 로딩되어 호출되므로 그 효과가 상당하다.
◦
유지관리가 편하다
◦
모듈식 프로그래밍이 가능하다.
◦
보안을 강화할 수 있다.
▪
테이블에 별도로 접근권한을 주지않고 프로시저에만 줄 수 있다.
▪
필요한 정보만 조회하는 쿼리를 미리 프로시저로 만들어두면 제한된 정보만 노출할 수 있다.
함수 | Stored Program - Function
•
함수는 반환하는 값이 반드시 있다.
•
프로시저와의 차이점
◦
프로시저의 파라미터와 달리 IN, OUT 을 사용할 수 없다. 모두 입력 파라미터로 사용된다.
◦
RETURNS 문으로 반환할 데이터 형식을 정하고, RETURN 문으로 하나의 값을 반환해야한다.
◦
프로시저가 CALL 로 호출되는 것에 반해, 함수는 SELECT 문장 안에서 호출된다.
◦
프로시저에서는 SELECT 문을 사용할 수 있지만, 함수 내에서는 집합결과를 반환하는 SELECT 를 사용할 수 없다.
◦
프로시저는 여러 SQL 문이나 숫자계산 등의 다양한 용도로 사용되지만, 함수는 어떤 계산을 통해서 하나의 값을 반환하는데 주로 사용된다.
커서 | Stored Program - Cursor
•
커서는 일반 프로그래밍의 파일처리와 비슷한 방법을 제공한다.
•
프로세스
◦
파일을 연다
◦
처음 데이터를 읽는다.
◦
파일의 끝까지 반복한다.
◦
파일을 닫는다.
트리거 | Stored Program - Trigger
•
트리거는 테이블의 DML 문이 작동하면 자동으로 실행되도록 설정한다.
•
쉽게 말하자면 테이블에 부착되는 프로그램 코드
•
부착된 테이블에 이벤트가 발생하면 자동으로 실행된다.
•
따로 직접 실행은 불가하며 오직 이벤트가 발생할 때에만 동작한다.
•
사용사례
◦
A 테이블에서 기록 삭제전, B 테이블로 마지막 기록을 옮겨적어두면 나중에 A 테이블에서의 기록이 문제가 되어 확인이 필요해도 B테이블에서 확인할 수 있다.
•
종류
◦
AFTER
▪
해당 작업 후에 동작
◦
BEFORE
▪
해당 작업 전에 동작
•
DML 에만 동작한다.
◦
DELETE 과 유사하게 Truncate 문이 사용되지만, TRUNCATE 시에는 트리거가 동작하지 않는다.
•
트리거가 생성하는 임시 테이블
◦
NEW
▪
INSERT, UPDATE 시에 변경할 새로운 데이터를 잠깐 저장해놓는 테이블
▪
이 테이블을 조작하면 입력되는 새로운 값을 다른 값으로 대치시킬 수 있다.
◦
OLD
▪
DELETE 와 UPDATE 수행 전에 예전 값이 저장된다.
◦
입력될 새로운 데이터는 NEW, 예전 데이터는 OLD 를 참고하면 된다.
•
BEFORE 트리거의 사용
◦
테이블 변경이 가해지기 전에 작동되는 트리거
◦
미리 해당 트리거를 부착해놓으면 입력될 데이터 값을 미리 확인해서 문제가 있을 경우에 다른 값으로 변경시킬 수 있다.
•
기타 트리거에 관한 내용
◦
다중 트리거
▪
한 테이블에 동일한 트리거가 여러개 부착되어있는 것
▪
예 ) AFTER INSERT 트리거가 2개이상 부착되어있을 수 있다.
◦
중첩 트리거
▪
트리거가 또 다른 트리거를 작동하는 것
▪
고객 → 구매 → 물품
▪
시스템 성능에 좋지 않은 영향을 미칠 수 있다.
▪
연결된 트리거 중 하나가 실패하면 전체가 롤백되기 때문임
•
작동순서
◦
하나의 테이블에 여러개의 트리거가 부착되어있다면 작동 순서를 정할 수 있다.
전체 텍스트 검색
•
긴 문자의 텍스트 데이터를 빠르게 검색하기 위한 기능
•
전체 텍스트 인덱스는 신문기사와 같이 텍스트로 이루어진 문자열 데이터의 내용을 가지고 생성한 인덱스를 말한다.
•
일반 SELECT 문의 WHERE 절에 MATCH() AGAINST() 를 사용하면 된다.
•
인덱스 생성
◦
Inno DB, MyISAM 테이블만 지원한다.
◦
char, varchar, text 의 열에만 생성이 가능하다.
◦
인덱스 힌트의 사용이 일부 제한된다.
◦
여러개 열에 full text 인덱스를 지정할 수 있다.
•
중지단어
◦
검색에서 무시할만한 단어는 아예 인덱스로 지정하지 않는다.
◦
예) 이번, 아주, 모두, 꼭
•
쿼리
◦
where 절에서 MATCH() AGAINST() 를 사용한다.
◦
자연어 검색 지원 : IN NATURAL LANGUAGE MODE
▪
단어가 정확한 것을 검색해준다.
◦
불린모드 검색
▪
IN BOOLEAN MODE
▪
정확히 그 단어만 포함된 내용을 검색한다.
파티션
•
파티션은 대량의 테이블을 물리적으로 여러 개의 테이블로 쪼개는 것을 말한다.
•
파티션을 나눈다고 해서 무조건 효율적인 것은 아니다. 데이터의 분포 특정이나 자주 사용되는 쿼리문에 따라서 효율에 차이가 발생한다.
•
사용예시
◦
10년간의 데이터가 저장되었을 경우
▪
과거의 기록은 주로 조회용
▪
작년 이전 - 올해 데이터 서로 다른 파티션으로 저장
◦
월별로 업데이트가 잦은 데이터 : 월별로 파티션 테이블 구성
•
테이블 생성시, 파티션 키를 함께 지정
◦
데이터 입력시에 지정된 파티션 키에 의해서 데이터가 각각의 파티션에 입력된다.
•
파티션 삭제시에는 파티션 데이터도 같이 삭제되므로 주의해야한다.
•
파티션 데이터를 모두 삭제할 때에는 아예 파티션을 삭제하는게 더 효율적이다.
•
주의사항
◦
파티션 적용된 테이블에는 외래키를 적용할 수 없다.
◦
프로시저, 스토어드 함수, 사용자 변수 등을 파티션 함수나 식에 사용할 수 없다.
◦
임시 테이블은 파티션 기능을 사용할 수 없다.
◦
파티션 키에는 일부 함수만 사용할 수 있다.
◦
개순ㄴ 최대 8129개까지만 지원됨
◦
레인지 파티션은 숫자형의 연속된 범위를 사용하고
◦
리스트 파티션은 숫자 혹은 문자형의 연속되지 않은 하나하나씩 파티션 키 값을 지정한다.
▪
리스트 파티션에서는 MAXVALUE 를 사용할 수 없다.
공간데이터
•
GIS 는 지도와 텍스트 정보를 컴퓨터에서 활용하는 분야이다.
•
Mysql 5.0 부터 공간데이터를 위한 Geometry 데이터 형식을 지원한다.
•
공간 데이터 입력은 ST_GeomFromTex() 함수를 사용하여 입력할 수 있다.
•
ST_ 로 시작하는 다양한 공간 데이터 형식의 함수를 제공한다.