이것이 MySQL이다

이제 처음부터 끝까지 직접 만들어서 운영중인 서비스가 벌써 운영 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;

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_ 로 시작하는 다양한 공간 데이터 형식의 함수를 제공한다.