Skip to content

Latest commit

 

History

History
252 lines (221 loc) · 9.37 KB

20210707.md

File metadata and controls

252 lines (221 loc) · 9.37 KB
Raw

DB/OS

인상 깊은 조언

  • join은 안 쓸 수 있다면 안 쓰는게 좋다. 큰 데이터를 처리할 때는 더더욱.
  • 똑같은 결과물일지라도 쿼리 순서에 따라 성능이 달라진다.

MySQL

JOIN과 UNION

  • 두개 이상의 테이블 합칠 때 join on 여러번 쓰는 대신 from 에서 테이블 이름을 comma로 연결하면 inner join
  • union: 쿼리 결과를 하나로 합쳐준다. 기본적으로는 중복을 제거하지만 제거하지 않으려면 union all을 사용한다.

union으로 full outer join 만들기

  • left join한 후 right join을 union한다.
select user.user_id, user.name, addr.addr_name
from user
left join addr
on user.user_id = addr.user_id
union
select addr.user_id, user.name, addr.addr_name
from user
right join addr
on user.user_id = addr.user_id;

Sub Query

  • Query안에 Query를 작성
  • select, from, where절에서 사용이 가능

SELECT 절에서의 Sub Query

  • select에서 column명을 넣어주는 대신 Sub Query를 넣어준다.
# 전체 나라 수, 전체 도시 수, 전체 언어 수를 하나의 row로 출력
use world;

select 
    (select count(name) from country) as country_count,
    (select count(name) from city) as city_count,
    (select count(distinct(language)) from countrylanguage) as language_count
from dual;
  • 원래 MySQL에서는 select와 함께 반드시 from이 와야하는데 딱히 쓸 게 없으면 dual을 써서 from dual을 써줘야 하지만 workbench에서는 안써도 동작

FROM 절에서의 Sub Query

  • 8백만 이상 인구를 가진 도시의 국가 코드, 국가 이름, 도시 이름, 도시 인구 수 출력
    • join을 사용한 방식: 모든 데이터를 합친 후 필터링한다.
select country.code, country.name, city.name, city.population
from country
join city
on country.code = city.countrycode
having city.population >= 800*10000
order by city.population desc;
  • 800만 이상이 되는 city를 받는 subquery를 만든 후 join하면 작업할 데이터 양이 훨씬 적어진다.
# 우리가 필요한 sub query(800만 이상이 되는 city)를 먼저 뽑자
select countrycode, name, population
from city
where population >= 800*10000;

# 이 쿼리를 city라는 이름으로 넣는다.
select country.code, country.name, city.name, city.population
from country
join (
    select countrycode, name, population 
    from city
    where population >= 800*10000
    ) as city
on country.code = city.countrycode
order by city.population desc;

WHERE 절에서의 Sub Query

  • 800만 이상 인구를 가진 도시의 국가 코드, 국가 이름, 국가 인구수, 대통령 이름 출력하기
# 800만이 넘는 인구를 가진 도시의 국가 코드 Sub Query 먼저 뽑기
select distinct countrycode
from city
where population >= 800 * 10000;

# SubQuery 넣기
select code, name, population, headofstate
from country
where code in (
    select distinct countrycode
    from city
    where population >= 800 * 10000
    )
  • distinct는 함수로도 쓸 수 있지만(distinct()) 예약어로 쓸 수도 있다.
  • where code in ()으로 이 조건에 부합하는 값만 필터링 할 수 있다.
select code, name, population, headofstate
from country
where code in ("BRA", "KOR", "IND");

or (any) 과 and ('all')

  • 하나의 조건만 충족해도 되는 경우 any로 or을 나타낸다.
  • 모든 조건을 충족해야 하는 경우 all로 and를 나타낸다.
  • 한국(KOR)이나 브라질(BRA)보다 인구가 많은 국가의 국가코드, 국가명, 인구수 출력
select code, name, population
from country
where population > any (
    select population
    from country
    where code in ("KOR", "BRA")
);

VIEW

  • 가상의 테이블로, 실제 데이터를 저장하고 있지 않다.
  • 특정 데이터를 보여주는 역할만 하므로 수정 및 설정이 불가능하다.
  • from 절에서 테이블 이름 쓰듯이 쓰면서 쿼리문을 줄여주는 장점이 있다.
# view 생성
create view country_10000 as
select code, name
from country
where population >= 10000 * 10000;

# view 사용
select country.code, country.name, city.name, city.population
from city
join country_10000 as country
on country.code = city.countrycode;

INDEX

  • tree 알고리즘을 통해 테이블에서 데이터를 검색할 때 빠르게 검색하게 해준다.
  • 앞에서부터 쭉 찾는 것이 아니라 index를 보고 빨리 찾게끔
    • 장점: 검색속도
    • 단점: 저장공간을 추가로 사용. select를 제외한 쿼리는 느려진다.
  • where절에 들어가는 컬럼을 index로 설정하면 좋다.
  • select를 많이 쓰는 데이터에서 사용하는 것이 좋다.
    • c.f. 로그데이터 등은 select할 일이 없으니 쓰면 안된다.
  • cluster형 인덱스와 보조(secondary) index로 나뉜다
    • 일반적으로 말하는 인덱스는 secondary를 가리킨다.
    • 클러스터형은 데이터 자체가 인덱스로 사용되며, 정렬에 관련된 것일뿐 검색 성능에는 영향이 없다. 행 데이터를 자신의 열을 기준으로 정렬해주는 기능

INDEX 만들기와 EXPLAIN

  • explain: 쿼리에 대한 실행계획을 출력해준다.
create index tdate on salaries(to_date);
explain
select *
from salaries
where to_date < "1986-01-01";

  • rows: filtered만큼 걸러내준 예측치
  • Extra: Using where는 full search, Using index condition은 인덱스 사용
  • 쿼리 속도를 예측해 볼 수 있다.

인덱스 삭제

drop index tdate on salaries;

TRIGGER

  • 누군가 delete from을 한다거나 where절을 안 쓰고 업데이트 치는 등 사고칠 경우를 대비하는 기능
  • update나 delete는 과거 데이터가 보존이 안 되므로 이를 상쇄할 수 있는 백업기능
  • 하루나 3일 등 특정 날짜 단위로 특정 테이블에서 update나 delete 쿼리가 실행되면 현재 작성 중인 테이블을 다른 곳에 insert하는 쿼리가 실행되어 과거 데이터를 보존하도록 한다.
  • 먼저 수정할 채팅 목록을 생성해보자
create database tr;
use tr;
create table chat(
    chat_id int primary key auto_increment,
    msg varchar(200) not null,
    rdate timestamp default current_timestamp
);

insert into chat(msg)
values ("hello"), ("hi"), ("hello"), ("mysql is fun");

백업테이블 만들기

create table backup(
    backup_id int primary key auto_increment,
    chat_id int,
    msg varchar(200),
    rdate timestamp
    backup_date timestamp defualt current_timestamp
);

TRIGGER 설정

  • delete 쿼리 실행 전(before)으로 설정
delimiter |
    create trigger backup_tr
    before delete on chat
    for each row begin
        insert into backup(chat_id, msg, rdate)
        values(old.chat_id, old.msg, old.rdate);
    end |

show triggers;
  • 가장 앞에 있는 hello 메시지를 삭제해야 하는데 모든 hello 메시지 삭제한 경우 backup table에서 가져와 다시 insert를 할 수 있다.
# 실수로 모든 메시지 삭제
delete from chat
where msg="hello";

# 다시 복구
select * from back_up;
insert into chat(chat_id, msg, rdate)
select chat_id, msg, rdate from backup;

Back-up

  • 백업 시 서비스가 운영중인지의 여부(Hot vs. Cold), 백업 방식에 따른 구분(Logical vs. Physical) 등으로 종류를 나눈다.

Hot vs. Cold Backup

  • Hot Backup: 백업 순간에 DB가 실행중
    • 장점: 서비스를 계속할 수 있다
    • 단점: 데이터 변경 시 동기화가 안 될 수 있다.
  • Cold Backup: 백업 시 DB 중단
    • 장점: 안정적으로 백업이 가능
    • 단점: 서비스 운영이 중단. 따라서 사용량 낮은 시간에 실행한다.

Logical vs. Physical Backup

  • Logical: SQL 쿼리문으로 DB를 변환
    • 장점: 실제 DB가 아니라 쿼리문만 가지고 있으면 되므로 적은 용량으로 백업이 가능하며, 문제 발생 시 에러메시지로 파악이 쉽다. OS 호환도 잘 된다.
    • 단점: 문자열 데이터로 변환하는 시간이 소요된다. 시스템 자원을 많이 사용한다.
  • Physical: 실제 데이터 binary file을 그대로 복사
    • 장점: 백업과 복원이 빠르다. 시스템 자원을 적게 사용한다.
    • 단점: 디스크용량을 많이 사용하며, 파일시스템이 다른 OS끼리 호환 X, 에러가 났을 때 이유를 알기 어렵다.

TMUX

  • 터미널 멀티플렉서
  • $ tmux new -s {세션이름}으로 새로 만들고, ctrl+B 떼고 1초 안에 c를 누르면 윈도우가 생긴다.
  • $ tmux attach -t {세션이름}으로 같은 세션에 두 컴퓨터가 접속하여 페어프로그래밍이 가능하다.
  • 나가려면 $ logout

Oauth

  • 모든 API는 Oauth의 표준 프롴토콜을 기반으로 한다.
  • (1)service provider(인스타그램)와 (2)user/client(나)와 (3)3rd-party ocnsumer(인스타 사진 출력 서비스)가 있다고 해보자
    • 2가 따로 다운받아서 보내줄 필요 없이, 3이 1의 DB에서 바로 받을 수 있도록 1, 2에게 허락을 받는 것
    • 1에게는 DB에 대한 request token을, 1, 2에게는 저작권 허락을 위한 access token을 받는다.

느낀 점

  • 문법 배우는 건 재밌는데 어떻게 활용할지가 더 중요하고 어려운 것 같다.
  • TMUX는 아직 효용을 못 느껴서 재미있지가 않았다.