MySQL

MySQL

• 한동안 웹개발 표준 기술 스택 중의 하나였음
• LAMP : Linux, Apache, MySQL, PHP

• 지금도 Postgres와 함께 가장 널리 쓰이는 프로덕션용 관계형 데이터베이스

• 용량 증대 (Scaling) 방식 1
• MySQL은 한 대 서버에서 운영되기 때문에
• Scale-Up : 서버에 CPU와 Memory 추가 ⇒ 한계가 있음
• 이 때 생각해볼 것은 웹 서버에 필요한 데이터들만 있는 지를 확인해보아야 함

• 용량 증대 (Scaling) 방식 2
• 일반적으로 클러스터 구성을 이야기하나 MySQL은 이를 지원하지 못함(MySQL은 기본적으로 한 대에서 돌아가도록 작성되어 있기 때문)



• Scale-Out : Master-Slave 구성 (MySQL에서의 Scale-Out은 이와 같은 방식으로, 읽기의 용량만 커지는 것임)



• MySQL 하나가(Master) 가지고 있는 내용을 다수의 Slave에 복제를 함으로써 읽기 같은 경우 더 큰 용량을 지원해 주는 것임
• Slave 들 앞에 LoadBalancer를 붙여서 하나로 묶어서 이용하는 방식으로.
• MySQL 자체가 핸들할 수 있는 데이터의 크기가 커지는 것은 아님(Master가 처리할 수 있는 최대 양으로 제약 있음)
• Writing 을 Slave에 못한다는 제약이 존재함(모든 쓰기 작업은 Master에 해야함)

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

세션

• 사용자 ID : 보통 웹서비스에서는 등록된 사용자마다 부여하는 유일한 ID

• 세션 ID : 세션마다 부여되는 ID

• 사용자의 방문을 논리적인 단위로 나눈 것
• 사용자가 외부 링크(보통 광고)를 타고 오거나 직접 방문해서 올 경우 세션을 생성
• [시간 단위] 사용자가 방문 후 30분간 interaction이 없다가 행동을 하는 경우 새로 세션을 생성

• 즉 하나의 사용자는 여러 개의 세션을 가질 수 있음

• 보통 세션의 경우 세션을 만들어낸 접점(경유지)를 채널이란 이름으로 기록해둠
• 마케팅 관련 기여도 분석을 위함

• 또한 세션이 생긴 시간도 기록

예시

• 사용자 ID 100번 : 총 3개의 세션(파란 배경)을 갖는 예제
• 세션 1: 구글 키워드 광고로 시작한 세션. 해당 세션의 채널 정보는 구글
• 세션 2: 페이스북 광고를 통해 생긴 세션
• 세션 3: 네이버 광고를 통해 생긴 세션

• 해당 세션 정보와 채널 정보를 활용하여 어느 채널에 더 많은 광고를 할 지를 분석할 수 있음

CREATE TABLE channel (
	id int not null auto_increment,
	channel varchar(32) not null,
	primary key(id) -- primary key(id, channel ...) 이런식으로 composite primary key 구성가능
);

CREATE TABLE session(
	id int not null auto_increment primary key, --이 방식은 composotie 구성 불가
	user_id int not null,
	created timestamp not null default current_timestamp,
	channel_id int not null,
	foreign key(channel_id) references channel(id)
);

MySQL 에서 지원하는 컬럼 타입

시간을 출력하는 함수

• SYSDATE() — 쿼리가 끝난 후 현재 시각

• NOW() — 쿼리가 시작하는 순간의 시각

• CURRENT_TIMESTAMP() ↔  NOW() (동일함)

• CURRENT_DATE() — 년. 월. 일 까지만 나옴

MySQL의 Safe Update 모드

• 기본적으로 safe update 모드로 되어 있어, update나 delete에 pk 로 명시 안해주면 삭제가 안됨

• 풀어주어야 한번에 delete 한번에 update가 가능함

MySQL의 스토리지 엔진 아키텍처

1. 클라이언트의 접속을 허용하여 전용 스레드 할당. 접속이 끊긴 상태에서는 스레드가 다음 접속에 대비해 캐시 영역(Conection Pool)에 보관됨. 이에 따라 접속/절단의 오버헤드를 줄임

2. SELECT 문이 이전에 실행 것과 세세한 부분까지 다르지 않으면 검색을 실제로 하는 대신 미리 캐시해 놓은 검색 결과를 그대로 반환(쿼리 캐시)

3. 쿼리 캐시에 히트하지 않으면 전송된 SQL문이 구문으로서 올바른지 확인을 함(구문 분석[파싱] 처리)

4. 구문 분석이 완료되면 SQL 문의 실행 계획을 결정. SQL 문의 실행 계획은 테이블에 액세스하는 방법을 규정하는 것으로 구체적으로는 인덱스 사용 여부 이를 사용하는 경우 어떤 인덱스를 사용할지와 조인 시에 어떤 테이블을 먼저 읽어야 할지 등을 결정함(최적화)

SQL

TRUNCATE

• TRUNCATE 시, foreign key constraint 때문에 실행이 안될때?

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE table1;
TRUNCATE table2;

SET FOREIGN_KEY_CHECKS = 1;

Character Set과 Collations

• Character Set의 정의
• A repertoire of characters/graphemes
• A value given to each character/grapheme (codepoint)
• encoding, 값에 대한 binary 표현을 정의
• encoding : character나 grapheme 을 어떻게 binary로 표시할지 규정한 방법을 의미함
• 예: ASCII, ISO-8859-1/Latin-1, EUC-KR 등
• encoding 방식에 따라 저장하지 못하는 글자가 있을 수 있음

Query History 보기

실행중인 쿼리를 보는것

select * from information_schema.PROCESSLIST ;