Nodap is everywhere.: 2016

2016년 12월 20일 화요일

MySQL 의 ON DUPLICATION KEY UPDATE 에 대해서...

MySQL의 INSERT ~ ON DUPLICATE KEY UPDATE ~ 는 보통 다음과 같이 사용된다.

INSERT INTO TALBE ( a, b, c ) VALUES ( 'a', 'b', 'c' ) ON DUPLICATE KEY UPDATE b = 'b2', c = 'c2';

a field 에 UNIQUE INDEX 가 걸려있고 INSERT 시 a 를 중복해서 넣으려고 하면 b에는 b2, c에는 c2라는 값을 넣는 Query이다.

INSERT 를 한개만 하는 경우라면 위와 같이 Query를 만들어도 되지만,

INSERT가 여러개인 경우는 다음과 같이 프로그램을 만들어야 한다.

INSERT INTO TABLE ( a, b, c, ) VALUES ( 'a1', 'b1', 'c1' ), ( 'a2', 'b2', 'c2' ) ON DUPLICATE KEY UPDATE b = VALUES(b), c = VALUES(c);

각각의 INSERT 데이터가 VALUES 에 매칭되어 업데이트 된다.

2016년 11월 23일 수요일

MySQL 에서 Ranking을 구하는 Query를 만들고 싶다면...

MySQL에는 Rank() 함수가 없다.

기본적으로 MySQL 에는 Oracle이나 MS-SQL같은 RDBMS 에서 지원하는 Rank() 함수가 없다.

그러나 Query를 이용해서 다른 DB들의 Rank() 함수과 같은 효과를 만들 수 있다.

기본 Table 구조

Table의 내용이 다음과 같이 입력되어 있다고 하자.

해당 Table 의 ranking_score 를 이용해 ranking_score가 큰 순서대로 ranking을 만드는 쿼리를 몇 가지 방법으로 만들어보자.

1. COUNT 를 이용해서 만드는 방법

SELECT
ranking_no, ranking_score, ranking_name,
( SELECT COUNT(*) + 1 FROM ranking WHERE ranking_score > b.ranking_score ) AS rank
FROM
ranking_score AS b
ORDER BY
rank ASC

위 Query의 결과는 다음과 같다.

대충 훑어봤을때는 그럭저럭 랭킹을 만들어 주는것 처럼 보인다.

그러나 위와 같은 Query는 Table 안의 데이터가 많아질수록 속도가 저하될 수 밖에 없다.

모든 Row 별로 "SELECT COUNT(*) + 1 FROM ranking WHERE ranking_score > b.ranking_score" Query가 실행되기 때문이다.

또, 동점자의 경우 같은 순위로 표시할거라면 위와 같이 해도 문제가 없지만 동점자도 순위를 매겨야 한다면 위와 같은 Query로는 구현하기 힘들다.

2. MySQL 의 사용자 정의 변수를 이용하여 만드는 방법

SELECT
ranking_no, ranking_score, ranking_name,
( @rank := @rank + 1 ) AS rank
FROM
ranking AS a,
( SELECT @rank := 0 ) AS b
ORDER BY
a.ranking_score DESC;

위 Query의 결과는 다음과 같다.

COUNT를 이용해 만든 결과와 다르게 rank 가 모두 순서대로 등록이 되어 있는것을 확인할 수 있다.

MySQL 에도 프로그램의 변수와 같은 개념으로 "@변수명" 을 이용해 값을 유지할 수 있다.

다만 "@변수명" 은 모든 connect 에 유효한 값이 아니고, 해당 connect session 에서만 유효한 값이다.

자세한건 "http://dev.mysql.com/doc/refman/5.7/en/user-variables.html"를 참고해보자.

Query에 대해서 간단히 설명을 하자면 다음과 같다.

(1) ( SELECT @rank := 0 ) AS b

이 부분은 rank라는 변수에 값을 0 으로 초기화를 해주는 부분이다.

보통 프로그램에서는 $rank = 0 처럼 사용을 하는데, mysql 에서 "=" 는 같다는 의미로 사용이 되고 있기 때문에 값을 넣을때 ":=" 를 이용해서 값을 넣어준다. ( 프로시져에서는 "=" 를 사용한다. )

위 예제 Query에서는 Ranking 의 시작이 1부터이기 때문에 초기값을 0 으로 잡아주었지만 데이터가 많아서 페이징 처리를 해야 한다면 초기값이 0 이 아닌 다른 값이 되어야 할수도 있다.

그럴경우 적절한 값을 넣어주면 된다.

(2) ( @rank := @rank + 1 ) AS rank

이 부분이 각각의 Row 에서 rank 를 +1 씩 해주는 부분이다.

만약 랭킹을 +1 단위가 아닌 + 2 단위로 하고 싶다면 "( @rank := @rank + 2 ) AS rank" 처럼 바꿔주면 된다.

랭킹의 초기값을 20, 랭킹의 증가값을 +2 로 만들어보자.

SELECT
ranking_no, ranking_score, ranking_name,
( @rank := @rank + 2 ) AS rank
FROM
ranking AS a,
( SELECT @rank := 20 ) AS b
ORDER BY
a.ranking_score DESC;

위 Query의 결과는 다음과 같다.

랭킹의 초기값이 0 이 아닌 20으로 설정이 되어있고, 랭킹의 증감이 +2 로 설정되었기 떄문에 22위 부터 랭킹이 만들어 지는것을 확인할 수 있다.

3. MySQL 의 사용자 정의 변수를 이용하여 동점 랭킹을 만드는 방법

사용자 변수를 이용하여 간단하게 만드는 경우는 동점자도 모두 Query 의 ORDER 에 따라 순차적으로 순위가 만들어지는데, 랭킹의 종류에 따라 동점자는 같은 순위로 표시를 하고 싶을때가 있다.

SELECT
ranking_no, ranking_score, ranking_name,
( @rank := @rank + 1 ) AS rank,
( @real_rank := IF ( @last > ranking_score, @real_rank:=@real_rank+1, @real_rank ) ) AS real_rank,
( @last := ranking_score )
FROM
ranking AS a ,
( SELECT @rank := 0, @last := 0, @real_rank := 1 ) AS b
ORDER BY
a.ranking_score DESC;

위와 같이 Query를 실행하면 다음과 같은 결과가 나온다.

rank 와 real_rank 의 값을 비교해보자.

real_rank 에서는 COUNT를 이용한 랭킹 Query 처럼 동점자를 표시해주고 있다.

언뜻 봐서는 Query가 복잡해 보일 수 있지만 그다지 복잡한 내용은 아니다.

(1) ( @real_rank := IF ( @last > ranking_score, @real_rank:=@real_rank+1, @real_rank ) ) AS real_rank

이 부분이 복잡해 보일 수 있는 부분이지만 크게 두개로 나뉜다고 보면 된다.

- IF ( @last > ranking_score, @real_rank:=@real_rank+1, @real_rank )

이 부분은 MySQL의 IF문을 이해한다면 금방 이해할 수 있다.

IF ( 조건, 조건이 TRUE일경우, 조건이 FALSE일 경우)

위 내용대로 Query를 설명한다면 만약 "@last"의 값이 ranking_score보다 큰 경우 "@real_rank:=@real_rank+1" 을, 그렇지 않다면 "@real_rank" 를 반환한다.

즉 "@last"의 값과 현재 row의 ranking_score값을 비교해서 값이 같거나 작은경우는 "@real_rank"를 +1 하지 않고 그 전 단계의 값을 반환한다.

- IF 문을 통해서 반환된 값이 "@real_rank"에 저장된다. 다음번 row에서 ranking_score의 값이 증가하지 않았다면 저장된 값을 그대로 반환할 것이다.

(2) ( @last := ranking_score )

다음번 IF 문을 위해서 "@last" 변수에 최종 ranking_score 값을 저장해 두는 부분이다.

위와 같은 Query를 이용한다면 랭킹을 좀 더 효율적으로 원하는대로 구할 수 있을 것이다.

매번 랭킹을 구하지 말고 어딘가에 저장을 해두자.

아무리 Ranking Query를 효율적으로 만들어도 매번 위와 같이 Query를 날려서 가져오도록 서비스를 구성한다면 사용자가 많아질수록 기하급수적으로 효율이 안좋아 질 수 밖에 없다.

실시간으로 랭킹정보를 보여줘야 한다면 어쩔 수 없겠지만, 가능하다면 매번 랭킹을 계산하기 보다는 계산이 완료된 정보를 다른 Ranking 결과 Table에 저장하여 SELECT만으로 보여주는것이 좋을것이다.

2016년 11월 17일 목요일

내 서비스의 메일 발송 서버에서 발송하는 메일이 스팸메일로 발송이 된다면...

서비스를 운영하다보면 서비스에서 발송하는 메일이 수신 거부되거나 스팸메일로 분류가 되어 곤란한 경우가 발생할 수 있다.

스팸메일로 분류가 되지 않으려면 다음 내용들을 확인해 봐야 한다.

1. SPF 등록

1.1. SPF ( Sender Policy Framework ) 란?

간단히 설명하면 메일 발송 서버의 IP를 DNS에 공개 등록하여 실제 발송 서버의 정보와 일치하는 여부를 확인하는 인증기술 이라고 할 수 있다.

자세한 내용은 https://www.kisarbl.or.kr/whiteip/whiteip_tutorial2.jsp 참고

1.2. SPF 를 등록하는 방법

SPF 는 KISA RBL SITE ( https://www.kisarbl.or.kr ) 에서 제공하는 "SPF 작성 도우미"를 통해 등록하면 쉽게 등록할 수 있다.

등록방법은 다음과 같다.

1.2.1. https://www.kisarbl.or.kr 에 접속

1.2.2. 상단 우측 메뉴 "White Domain 등록" 의 서브 메뉴 중 "SFP작성 도우미" 선택

1.2.3. 도메인을 입력함

이미지의 B 부분에 설명이 나와있듯이 도메인을 입력할때는 서브도메인을 제외한 도메인 자체만 입력을 한다.
예를들어 사이트 주소가 www.abc.com 이라는 도메인을 이용한다면 abc.com 만 입력을 해야 한다.

1.2.4 메일을 발송하는 서버의 IP를 등록한다

실제로 메일을 발송하는 서버들의 IP를 입력한다. IP 가 1개 이상이라면 "추가" 버튼을 클릭하여 최대 12개까지 입력을 할 수 있다.

12개를 넘겨서 입력을 하려고 하면 경고창이 하나 뜨는데 12개 이상 IP를 입력하는 방법에 대해 설명이 되어 있다.

1.2.5. SFP Record 확인

입력을 완료하게 되면 SFP Record 를 어떻게 입력하면 되는지 확인할 수 있다.

주황색으로 표시되는 글씨가 DNS에 입력해야 하는 SPF Record이다.

1.2.6. DNS에 해당 값들을 추가하기

SPF 작성 도우미를 이용하여 확인한 값 ( 주황색 값 ) 들을 각각 다음과 같이 추가한다.

1.2.6.1. DNS를 직접 구축하여 사용하는 경우

https://oops.org/?t=lecture&sb=sendmail&n=9 를 참고하여 zone 파일을 수정한다.

1.2.6.2. domainclub, cafe24, gabia 또는 기타 웹 dns 관리 프로그램을 이용중인 경우

위 화면은 domainclub.kr 의 입력 화면인데, 다른 서비스들도 입력 방식은 대동소이 하다.

TYPE/MXP 에 "TXT", HOST 에 "@.도메인.com", RECORD 에 붉은색 박스안의 값을 따옴표를 제외하고 입력한다.

1.2.7. SPF Record 가 제대로 적용되었는지 확인하기

SPF Record 가 제대로 등록되었는지 확인하는 방법은 (5)번 결과 페이지에도 자세하게 설명이 나와 있다.

설명에 나오는 두 방법 중 하나를 이용하여 SPF Record 가 제대로 등록되었는지 확인해 볼 수 있다.

2. 화이트 도메인 등록

2.1. 화이트 도메인 등록이란?

화이트 도메인을 등록한다는건 등록한 도메인으로는 스팸메일을 발송하지 않으니 대량으로 메일을 발송하더라도 RBL이나 포털에서 차단하지 않도록 사전에 미리 정보를 등록해두는 것이라고 생각하면 된다.

많이들 사용하는 블랙 리스트, 화이트 리스트 에서 사용하는 개념과 비슷한 것이라고 보면 된다.

화이트 도메인 등록을 신청하는것은 KISA RBL SITE 를 통해서 쉽게 할 수 있으나 스팸이력을 확인하는 시간이 최대 2주가 걸린다.

만약 서비스 오픈을 준비중이라면 미리 등록을 해두는것이 좋다.

2.2. 화이트 도메인을 등록하는 방법

화이트 도메인은 KISA RBL SITE ( https://www.kisarbl.or.kr ) 에서 등록할 수 있다.

2.2.1. https://www.kisarbl.or.kr 에 접속

2.2.2. 상단 우측 메뉴의 "White Domain 등록"의 서브 메뉴 중 "등록신청"을 선택

2.2.3. 도메인을 입력한다.

A 항목에서 "사업자/개인" 여부를 선택한 후 도메인을 입력한다.

B 의 내용대로 도메인 앞의 서브 도메인은 제외해야 한다. 예를들어 서비스 도메인이 www.abc.com 이라면 abc.com 만 입력한다.

2.2.4. 정보를 등록한다.

개인 정보 수집에 동의 후 화이트 도메인 등록에 필요한 개인정보를 등록한다.

2.2.5. 등록한 정보를 확인한다.

입력한 내용을 확인한다.

특이하게도 비밀번호를 암호화 하지 않고 입력한 그대로 보여주고 있는데 이건 좀 문제가 있어보인다.

2.2.6. 등록 완료

2주간 스팸 이력을 확인한 후 해당 이력이 없다면 정상적으로 화이트 도메인으로 등록이 된다.

이미 스팸을 발송한 이력이 있는 도메인이라면 화이트 도메인 등록이 거절될 수 있는데, 이런 경우는 직접 연락을 해야 한다.

사이트 하단에 있는 연락처 ( 02-405-4118, 02-405-5118 ) 또는 ksrt@kisa.or.kr 로 메일을 보내서 문의를 하면 된다.

개인적으로는 Email로 문의했을때 상당히 빠르고 친절하게 답변이 와서 좋았던 경험이 있다.

2.3. 등록이 제대로 되었는지 확인하기

상단 "White Domain 등록"의 서브메뉴 중 "등록확인 및 수정"을 선택한다.

2.3.1. 등록시 사용한 정보를 입력한다.

도메인은 www가 없이 입력한다.

2.3.2. 현재 등록 상태를 확인한다.

등록한지 얼마 안된 상태라면 접수 상태가 "스팸이력 확인 중" 으로 표시가 된다. 2주가 넘어서도 상태가 변함이 없다면 문의를 해보는것이 좋다.

IP 신뢰도는 해당 도메인이 연결된 IP가 얼마나 신뢰성이 있는지를 표시하는 값이다. 스팸을 많이 보낸 IP라면 값이 점점 차감되며 값이 0보다 작은 마이너스(-) 값이 되는 경우는 화이트 도메인에서 자동으로 탈락된다.

대량 메일을 자주 보내야 한다면 해당 정보를 자주 확인해 주는것이 좋다.

3. 리버스 도메인 설정

3.1. 리버스 도메인이란?

인터넷에서 특정 사이트를 접속하고자 할때 보통 사람들은 "도메인" 을 입력하여 "DNS 서버" 를 통해 "IP"로 변환하여 해당 사이트를 찾아간다.

기본적으로 "도메인" 을 "IP" 로 등록을 하는것과 반대로 "IP"에 어떤 "도메인"을 이용하는지 설정을 하는것을 "리버스 도메인" 이라고 생각하면 된다.

리버스 도메인을 등록해두면 메일 수신을 하는 서버에서 메일 발송을한 서버의 IP를 이용하여 어떤 도메인에서 보냈는지 확인할 수 있고, 어떤 도메인에서 보냈는지 확인을 할 수 있다면 해당 도메인이 "화이트 도메인" 에 등록이 되었는지 확인하여 정상 수신여부를 결정할 수 있다.

해외에서는 리버스 도메인이 등록되지 않은 IP 에서 메일을 발송하는 경우 대부분 스팸으로 간주되기 때문에 글로벌 서비스를 준비중이라면 반드시 등록을 해야 한다.

3.2. 리버스 도메인을 등록하는 방법

리버스 도메인은 서버에 할당된 IP의 통신사업자에 따라 등록방법이 다른데 IDC에 서버가 있는 경우 또는 호스팅 서비스를 받고 있는 경우는 호스팅 업체, IDC관리 업체에 요청을 하면 대부분 지원을 해준다.

가정용 인터넷 서비스를 이용하여 홈서버를 운용하는 경우 대부분 고정아이피가 아니기 때문에 등록이 불가능하다고 봐야 한다.

3.2.1. KT 회선을 사용하는 경우

kornet 사이트를 이용해 리버스 도메인을 등록할 수 있다.

http://dns.kornet.net 으로 접속을 하여 도메인을 인증 후 등록 신청을 하면 빠르게 승인을 해준다.

사이트가 오래 되어서 IE 호환성보기를 통해 접속을 해야 정상 작동을 한다.

3.2.2. 그밖의 회선을 사용하는 경우

통신사업체에 직접 연락을 하거나 각 통신사업자 홈페이지의 고객센터를 통해서 요청을 하면 된다.

3.3. 리버스 도메인 확인 방법

리버스 도메인의 등록 여부는 nslookup 을 이용하여 확인 해 볼 수 있다.

nslookup

>set type=ptr

>찾고자 하는 서버의 IP 를 입력

하게 되면 리버스 도메인이 등록되지 않은 경우는

** server can't find xxx.xxx.xxx.xxx.in-addr.arpa. : NXDOMAIN

위 메시지가 표시가 된다.

입력한 IP로 연결된 도메인을 찾을 수 없다는 내용이다.

리버스 도메인이 정상 등록된 경우는

xxx.xxx.xxx.xxx.in-addr.arpa name=연결된 도메인

처럼 해당 IP에 할당된 도메인이 표시가 된다.

4. 모든것을 다 했음에도 스팸메일로 발송이 된다면...

위 설정들을 모두 했음에도 스팸메일로 발송이 되고 있다면 이미 해외 RBL 사이트에 도메인이나 IP가 등록되어 있기 때문일 수 있다.

이런경우는 RBL로 등록한 곳에 직접 해제 요청을 해야 한다.

예를들어 http://www.anti-abuse.org/multi-rbl-check/ 이곳에서 조회를 해봤을때 입력한 IP가 또는 도메인이 listed 또는 blocked 등으로 표시가 된다면 해당 IP 또는 도메인은 스팸 발송서버로 차단이 된 상태이다.

차단된 각각의 사이트에 들어가보면 RBL에서 내려달라고 요청을 할 수 있는 메뉴가 있으니 해당 메뉴들을 통해 RBL에서 삭제를 요청하면 된다.