제 홈페이지의 모든 글은 anti-nhn license에 따릅니다.



대용량 서비스 - 6. 디비관련 프로그래밍 주의 사항

디비와 연동하는 프로그램을 짤 때 주의해야 할 사항을 정리해보겠습니다.


1. SQL Injection 막기

SQL injection 이라는 기초적인 해킹 기법이 있습니다. 파라미터에 이상한 값을 넣어서 원래 의도와 다르게 쿼리가 돌아가게 하는 방법입니다.


일반적으로 로그인 쿼리는 아래와 같이 만듭니다.

String query = "select * from user_table where user='" + request.getParameter("user") + "' and password='" + request.getParameter("password") + "'";

이 query를 실행시킬 때 사용자가 user 라는 파라미터에 

홍길동'  -- 

와 같이 입력을 했다고 치면, 최종적으로 만들어지는 쿼리는

select * from user_table where user='홍길동' -- ' and password='뭐시기'

가 됩니다. -- 뒤는 모두 주석 처리가 되기 때문에 패스워드와 상관없이 로그인이 됩니다. ( -- 는 mysql 기준이고, 다른 디비들은 다른 것으로 알고 있습니다. )

이것을 피하는 방법은  아래와 같이 PreparedStatement 를 쓰는 게 제일 쉽습니다.


PreparedStatement prep = conn.prepareStatement("select * from user_table where user= ? and password = ?");
prep.setString(1, request.getParameter("user"));
prep.setString(2, request.getParameter(" password "));
...

PreparedStatement를 쓰면 위와 같은 시도에 대해서 아래와 같은 쿼리가 만들어집니다.

select * from user_table where user='홍길동\' -- ' and password='뭐시기'

' 앞에 \가 붙어서 sql injection을 막습니다.


2. 쿼리 조합 금지 


코드를 줄이기 위해 쿼리를 조합하는 것은 별로 바람직하지 않습니다.


StringBuilder query = new StringBuilder("select * from user_table where writer = ?");
if(request.getParameter("start_dt") != null){
query.append(" and start_dt >= ").append(request.getParameter("start_dt"));
}
// 대충 쿼리 실행

위와 같은 방식으로 프로그램을 짜면 나중에 문제가 생겼을 때 찾기가 힘듭니다. 위의 경우야 그나마 단순하지만 위와 같이 쿼리를 조합할 경우 최종적으로 만들어지는 쿼리가 무엇인지 알기가 매우 어렵습니다. 복사-붙여넣기 신공을 통해서 조금씩 수정을 하더라도 쿼리를 조합하지 마십시오.

String query = null;
if(request.getParameter("start_dt") == null){
query = "select * from user_table where writer = ?";
}else{
query = "select * from user_table where writer = ? and start_dt >= ?";
}

위와 같이 하면 중복 코드는 있지만 하나의 쿼리를 이해하기 위해서는 한 부분만 보면 됩니다. 또 query 로그에서 찾아낸 문제가 있는 쿼리가 프로그램의 어디에 있는 것인지 쉽게 찾아낼 수 있으며, 그것을 변경했을 때 다른 부분에 미치는 영향을 줄일 수 있습니다.


3. 복잡한 쿼리 금지

복잡한 쿼리를 사용자용 페이지에 만들지 마세요. (사용자가 별로 없는 관리자 페이지에서는 어느 정도 괜찮을 수도 있습니다만... )

진짜 복잡한 쿼리가 필요한 경우는 극히 드뭅니다. 쿼리 한줄에 여러가지를 해결했다고 좋은 것은 아닙니다. 오히려 쿼리가 길면 성능이 떨어질 가능성이 크며, 쿼리를 이해하기도 훨씬 어려워집니다. 인덱스의 작동 방식을 이해하지 못한다면 join도 안 쓰는 게 좋습니다.

대부분의 복잡한 쿼리는 비정규화와 캐쉬 등을 통해서 해결할 수 있습니다. 

user table (사용자 아이디, 사용자 이름) 과 msg table(글쓴 사람, 글 내용) 이 있을 때, "홍길동"이라는 이름을 가진 사람이 쓴 가장 최근글과 홍길동에 대한 사용자 정보를  뽑으면 아래와 같은 쿼리가 나올 겁니다.

select msg.* , t.* from   
(select max(b.msg_id) as max_msg_id, a.* 
from user a left join 
msg b 
on a.user_id = b.writer where a.user_name = '홍길동'
) t 
left join msg 
on msg.msg_id = t.max_msg_id

이 프로세스를 조각내면

1. 사용자 이름으로 사용자 정보 찾기 ( user.user_name 으로 user.* 찾기)
2. 사용자 아이디로 가장 최근글 찾기 (1에서 뽑은 user.user_id 를 msg.writer 에 대입하여 max(msg_id) 찾기)
3. 특정 글번호로 글 내용 찾기 ( 2에서 뽑은 max_msg_id 로 msg.* 찾기)

와 같이 됩니다.

저 3단계는 전부 간단한 쿼리로 만들 수 있으며, 각각을 하나의 캐쉬로 쓰게 되면 DB에 접근도 안 하고 데이터를 뽑아오는 경우도 생길 수 있습니다.

이렇게 단계가 구분되는 프로세스는 조각조각내서 각각을 DataManager로 정의를 하고 DataManager 안에서는 chain of responsibility 를 이용해서 캐쉬를 잘 정리하는 게 좋습니다. 자세한 건 요기 를 참조하세요.

프로그램쪽에서만 보면..

UserData user = UserByName.get("홍길동");
int maxId = MaxMsgIdByUserId.get(user.getUserId());
MessageData msg = MessageById.get(maxId);

와 같이 됩니다. 위에서 3단계로 조각낸 "사람이 인지하는 프로세스"와 "프로그램 코드"는 대략 일치하지만 sql의 흐름은 괄호를 먼저 찾고 그 안에서 무엇을 뽑는지 보고 그걸 괄호 밖에서 어떻게 쓰는 지 보고 또 그 중에서 무엇을 뽑을 것인지를 살피는 등의 과정을 거쳐야 합니다. 

위의 경우는 최종적으로는 user와 msg 변수를 이용해서 필요한 정보를 쓰면 됩니다. 이렇게 하면 복잡한 쿼리보다 훨씬 직관적으로 볼 수 있습니다.

또한 이렇게 분리시키는 것은 캐쉬 재사용성을 높입니다. 어떤 사용자의 요청에 의해 만들어진 캐쉬를 다른 사용자도 공유해서 쓰게 될 가능성이 큽니다.

그리고 테이블에 컬럼이 추가되거나 하는 경우 미치는 영향을 파악하기가 쉽습니다. 저렇게 분리시키면 대부분의 테이블에 접근하는 케이스가 몇 가지 이내로 정리가 됩니다. 따라서 테이블에 접근하는 쿼리 갯수가 줄어들기 때문에 테이블이 변경되었을 때 수정되어야 할 부분도 줄어듭니다.

여러 테이블에서 조인하는 하나의 쿼리가 각각 테이블을 조회하는 여러 개의 쿼리보다 일반적으로 빠르긴 합니다. 하지만 여러 테이블을 조인하는 쿼리는 여러 개의 테이블에 대해서 lock을 잡기 때문에 실제 서비스에서는 더 느린 현상까지 발생할 수 있습니다. 특히 MyISam의 경우는 테이블 단위로 lock이 잡히기 때문에 MyIsam을 쓰는 경우는 더더욱 조인을 삼가하는 게 좋습니다.

A, B 테이블을 조인하는 쿼리는 A에 대한 변경(insert, update ,delete )과 B에 대한 변경 모두를 기다려야 하기 때문에 A,B에 대한 변경 비율이 같다고 치면 lock이 걸릴 확률이 A,B에 대해 쿼리를 따로 날리는 것보다 2배나 높습니다.

복잡한 쿼리가 없어도 모든 것이 가능하진 않습니다만 더 보기 쉽고 빠른 방법이 있는데도 복잡한 쿼리를 고집할 필요는 없습니다.


4. 방대한 데이터 중 너무 오래된 데이터는 제한


아래의 쿼리는 어떻게 돌아갈까요?

select * from msg order by msg_id desc limit 1000000, 10; (백만 , 십)

중간중간에 지워진 글 때문에  

select * from msg where msg_id <= 1000000 order by msg_id desc limit 10; 

로 쓸 수 없다고 가정합니다. 

msg_id에 index가 걸려있다고 하더라도 순서대로 1,000,000 번을 일단 움직여야 합니다. 이 연산은 너무 큽니다. 이런 문제를 가장 손쉽게 해결 하는 방법은 위와 같이 너무 오래된 데이터를 사용자가 못 보도록 미리 validation에서 걸러 버리는 겁니다. 페이지 당 10개의 글이라고 치면 100,000 번째 페이지 쯤 될 겁니다. 일반적으로 이만큼 오래된 데이터는 의미가 없습니다.

어쩔 수 없이 조회를 허락해야 하는 데이터라면, 비정규화를 통해서 해결을 하는 것이 좋습니다. 이런 경우 글을 블럭 단위로 구분하고 각 블럭에 있는 글 갯수를 가지고 있는 테이블을 구성하는 것입니다. 예를들어 글번호 100,000 개 당 하나의 블럭을 유지하는 경우

msg_id 가 1           ~ 100,000 사이에 남아 있는 글이 총 99,990 개 라고 치고 (10개의 글이 삭제되었다고 치면) 
msg_Id 가 100,001~ 200,000 사이에 남아 있는 글이 총 99,900 개 라고 치면 (100개의 글이 삭제)

아래와 같은 테이블에 저장을 하면 됩니다.


블럭의 첫글번호 블럭 안의 글 갯수
199,990
100,000199,900

이런 식으로 하면 1,000,000 번째의 글이 어디있는 지 훨씬 적은 연산으로 추적할 수 있습니다. (최근 데이터 부터 예전 데이터 방향으로 조회를 하면서 글 갯수 합을 내면 됩니다.)

다만 저번에 말씀드린 것처럼 비정규화를 시키면 비정규화 테이블에 대한 데이터 변경 및 관리 등의 비용이 들어갑니다.


5. index 강제하기.


다음의 쿼리가 모두 잘 돌아가기 위해서 필요한 인덱스는 어떤 것들이 있을까요? (msg 테이블의 데이터는 무진장 많다고 칩시다.)

select * from msg where writer = '홍길동' order by msg_id desc limit 10;

select * from msg where writer = '홍길동' and location = '서울' order by msg_id desc  limit 10 ;

첫번째는 ( writer , msg_id ) , 두번째는 (writer, location, msg_id) 입니다.


그러면 아래 쿼리를 돌렸을 때 위 두개 중 어떤 인덱스를 타는 게 좋을까요?

select * from msg where writer = '홍길동' and location != '서울' order by msg_id desc  limit 10 ;

이건 분포도를 따져야 합니다. location에 대한 분포가 적당히 고르게 되어있다고 치면, (writer, msg_id) 로 뽑는 게 좋습니다. (writer, msg_id) 인덱스를 탈 경우 인덱스를 타면서 데이터를 가져와서 location이 서울이 아닌지 확인하면서 데이터를 뽑습니다. 아주 깔끔한 인덱스는 아니지만 ordering을 따로 할 필요가 없기 때문에 크게 느리지는 않을 겁니다.

만약 location에 대한 분포가 서울에 집중되어있다면 (writer, msg_id) 인덱스를 타는 것은 좋지 않습니다. location이 서울인지 아닌지를 확인하기 위해서는 테이블을 다 뒤지기 때문입니다. 그럴 때는 차라리 (writer, location , msg_id) 인덱스에서 앞에 두 개만 써먹고 ordering을 하는 게 나을 수도 있습니다.

그러면 두 개의 인덱스가 잘 잡혀있고, 세번째 쿼리를 실행하라고 컴퓨터한테 시키면 컴퓨터는 어떤 인덱스를 선택할까요? 까봐야 알겠지만 경우에 따라 엉뚱한 짓을 하기도 합니다.

위와 같이 두 가지 이상의 가능한 인덱스가 있을 때 어떤 인덱스가 선택될 지를 컴퓨터에게 위임하지 마십시오. 종종 잘못된 선택을 합니다. mysql 의 경우 use index나 force index 등을 통해서 인덱스를 강제하는 게 필요할 때가 있습니다.(오라클 등 타 디비도 있습니다. 오라클은 만져본 지 하도 오래되서 사용법은 기억이 안나네요.ㅜㅜ ) explain 등으로 확인을 했더라도 파라미터가 바뀌면서(ex> location != '대전' 으로 바꾼다거나.. )  다른 인덱스를 타기도 합니다.

인덱스를 지정하지 않을 경우에 두번째 쿼리 ( writer, location, msg_id  인덱스가 바람직해보이는 거) 도 첫번째 인덱스 (writer, msg_id) 를 타기고 합니다. 조금이라도 혼란의 가능성이 있다면 인덱스를 강제해버리는 게 좋습니다.

일단 explain 으로 봐서 possible_keys 가 여러 개 나오면 index 강제하는 게 젤 편합니다.

by 삼실청년 | 2012/10/06 00:04 | 컴터질~ | 트랙백(1) | 덧글(3)

트랙백 주소 : http://iilii.egloos.com/tb/5683242
☞ 내 이글루에 이 글과 관련된 글 쓰기 (트랙백 보내기) [도움말]
Tracked from at 2014/03/11 00:27
Commented by 민뎅스 at 2012/10/31 22:42
감사 합니다. 잘 봤습니다. ~~
Commented by 삼실청년 at 2012/11/19 21:01
제가 뭐 잘못쓴 거는 없는지 막 걱정되는 글입니다...
남이 정리해 놓은 거 다시 정리하는 건 그나마 쉬운데 첨부터 쓴 것들은 그런 생각들이 계속 들어요. 어디서 또 뻘소리해대는 거 아닌가...
Commented by 김재철 at 2014/01/04 07:28
감사합니다. 많은 도움이되었습니다.

:         :

:

비공개 덧글

◀ 이전 페이지          다음 페이지 ▶