mysql 대용량처리

mysql 대용량처리 (페이지가 없어서 구글에 저장된 페이지를 잽싸게 가져옴 ㅇㅇ )

개요
일반적으로 MYSQL에서 자료가 많아지면 LIMIT절을 이용하여 자료를 분할하여 가져오는 방식으로 쓰인다. 
여기서 발생하는 문제점으로, LIMIT는 첫번째 값만큼 자료를 '무시'하고 직접 가져오는게 아니라 실제로 모든 값을 읽되 가져올 필요가 없는 부분은 '스킵'해가는 방식이기 때문에 거대한 자료의 끝에 위치한 부분을 조회하려 하는 경우 심각한 부하가 일어날 수 있다.
ex)note_list라는 테이블에 로우가 1억개가 있고, 9999만9990의 자료부터 10개를 가져온다고 칠 경우 쿼리는 아래 형태가 된다.
SELECT * FROM note_list LIMIT 99999990, 10
보통은 저 쿼리에서 앞에 수만큼 건너뛰고 뒤에 수만큼 가져오는 것이라고 생각하기 쉽지만, 사실은 전부 읽되 앞에 수만큼을 스킵해버리고 뒤에 수만큼 가져오기 때문에 결과적으로는 1억개의 자료를 모두 읽는 것과 다를 바가 없게 된다.

해결책
해결책은 여러가지 방법이 존재한다.
아래는 그 방법 중 시도해본 세가지이다.
(1) 로우의 조회 범위를 구하여 해당 범위만큼 PK를 BETWEEN 연산으로 가져오기
인덱스를 타기 때문에 자료가 많아도 상당히 빠른 조회가 가능하다.
ex) SELECT * FROM note_list WHERE no BETWEEN 10001 AND 10010
여기서 no는 note_list의 PK자 Auto Increment 값이다.
단, PK가 무조건 순차적으로 저장이 되어있어야 가능하며 만약 중간에 비어버리는 값이 
생기는 경우 자료를 올바르게 가져올 것이란 보장을 할 수 없다.
ex) 위의 쿼리를 기반으로, 만약 no가 10001인 로우와 no가 10007인 로우의 자료가 없다면
의도된 쿼리대로라면 총 10개의 자료가 조회되어야 하지만 8개가 조회되어 버린다.
이 단점을 보완하려면 총 자료가 원하는 갯수가 될 때 까지 초기 BETWEEN 범위에서 계속 
늘려가면서 조회 쿼리를 반복해서 날리던지, 비어있는 값들을 구하여 BETWEEN의 범위를 
보정해야 한다.
(2) LIMIT를 사용하되, 조회 범위를 제한하여 가져오기
LIMIT를 사용하면 (1)에서처럼 중간에 비는 값이 있어도 갯수만큼 가져오기 때문에 (1)에서
의 문제는 해결할 수 있다.
다만 조회 범위를 제한하는 방법이 마땅치가 않다는 문제점이 있다.
(1)과 (2) 모두 로우의 순차정보를 담고 있는 컬럼이 존재한다면 무리없이 구현 가능하다.(ORACLE로 치면 ROWNUM과 비슷한 요소의 컬럼)
다만 특정 로우가 삭제될 경우, 해당 로우 위에 존재하는 모든 로우의 순차정보값을 갱신해야한다는 문제점이 존재한다.
ex) 로우가 100만개 들어있는 테이블에서 맨 처음 들어간 자료(순차정보가 1인 자료)를 삭제한다면, 남은 99만9999개의 순차정보를 모두 -1 시켜버려야하므로 과부하가 발생한다

(3) 로우를 특정 사이즈만큼 묶고, 묶은 값을 구분할 수 있는 컬럼과 테이블을 추가하여 관리
하는 방법
(2)의 방법에서 파생되었으며 실제로 조회 범위를 제한할 수 있는 방법으로, 기존에 있는 
테이블에 별개의 구분값 컬럼을 줘서 해당 구분값으로 조회 범위를 제한하는 방법이다.
이후의 설명은 (3)을 사용한다는 조건 하의 설명이다.


조회범위의 제한 
해당 컬럼의 이름은 segment 이고 해당 segment의 정보를 담은 note_list_게시판ID_segment 라는 테이블이 추가로 존재한다.

로우를 묶는다?
자료가 1억개 있다고 치자.
해당 1억개의 자료에서 LIMIT를 하는 것은 DB에 큰 부하를 준다고 위에서 설명했지만, 조회 범위가 정해진 상태에서 LIMIT를 하는 것은 그다지 부하를 주지 않는다.
즉, 1억개의 자료를 1만개씩 분할하고 해당 분할된 값을 구분할 수 있는 컬럼을 넣은 뒤 쿼리를 다음처럼 변경한다면 자료가 아무리 많아도 조회 범위가 정해져 있기 때문에 부하가 적다.
SELECT * FROM note_list WHERE segment = 7 LIMIT 9990, 10

세그먼트의 정보를 담는 테이블의 구조
CREATE TABLE `note_list_silver_segment` (
`no` int(11) NOT NULL AUTO_INCREMENT, -- 세그먼트 번호
`count` int(5) NOT NULL, --해당 세그먼트의 값을 갖는 게시물의 갯수
PRIMARY KEY (`no`)
) 

고려해야 할 점
(1) 세그먼트와 세그먼트끼리 걸치는 경우가 있기 때문에 해당 부분을 계산해 한다.
특정 테이블의 가장 최신 세그먼트의 값이 5이며 세그먼트 5의 게시물 갯수가 5개 있고, 
세그먼트 4의 게시물 갯수가 1만개 있을 경우 자료의 조회 범위는 세그먼트 5개의 게시글 
5개 + 세그먼트 4의 게시글 5개가 되어야 한다.
(2) 게시물 갯수의 변동이 있을 때마다 세그먼트 테이블의 count값도 같이 변경해줘야 함


구현 순서
(1) 현재 조회해야 하는 페이지를 기준으로 조회 범위를 구해야 한다.
조회 범위는 페이지-1한 값에 조회갯수를 곱한 값을 전체 게시물 갯수에서 뺀 값으로
구할 수 있다.
ex)전체 갯수가 34245개이고 조회하려는 페이지가 11페이지이고 조회갯수는 10일 경우, 페이지-1한 값은 10이며 여기에 10을 곱하면 100이 되고 이 값을 14245에서 빼면 실제 조회 범위는 34145가 된다.
(2) 조회 범위를 구했다면 해당 조회 범위가 어떤 세그먼트에 속하는지 구해야 한다.
세그먼트의 목록을 불러온 다음, 세그먼트의 count를 처음부터 더해가면서 만약 더해지는 
그 값이 조회 범위보다 커지거나 같아진다면 해당 세그먼트가 현재 페이지의 세그먼트이
다.
ex) 아래와 같이 자료가 존재할 경우
no count
------------------------------------
4 4148
3 9998
2 10000
1 9999
조회 페이지가 11이라면 조회 범위는 34145가 된다.
아래서부터 count를 더해간 뒤 조회 범위와 비교를 한다.
9999 >= 34145 -> FALSE
19999 >= 34145 -> FALSE
29997 >= 34145 -> FALSE
34245 >= 34145 -> TRUE
이로써 조회 범위 34145의 소속 세그먼트는 4라는 것을 알 수 있다.

(3) 세그먼트 내에서의 조회 범위를 구해야 함
현재 조회해야 하는 세그먼트 내에서 LIMIT 해야 하는 범위를 구해야한다.
공식은 다음과 같다.
현재 세그먼트의 게시물 합 - (현재 세그먼트 이전에 있는 세그먼트의 게시물 갯수 합 - (현재 조회 페이지 * 조회갯수)) - 조회갯수
ex)자료 형태가 위와 같다면
-> 4148 - (4148 - (11 * 10)) - 10
-> 4148 - (4148 - 110) - 10
-> 4148 - 4038 - 10 
-> 100
그러므로 쿼리는
SELECT * FROM note_list WHERE segment IN (4, 3) ORDER BY no DESC LIMIT 100, 10
이 된다.
※ 맨 처음 세그먼트 조회가 아닌 경우는 위에서 설명했던 값이 걸쳐있는 경우가 있기 때문에 현재 세그먼트와 이전 세그먼트 모두 조회대상에 포함시켜야 한다.(고려해야할 점 1 참고)

답글 문제
위에서의 모든 전제는 답글이라는 요소를 배제하고 작성되었다. 그래서 답글 처리를 생각해봐야한다.
답글 처리에는 크게 두가지 방식이 있을 수 있다.
(1) 실제 테이블에 답글 대상이 되는 게시물 밑에 끼워넣게 배치하기
이 경우 테이블 내의 로우 배치가 달라짐으로써 이런 저런 문제점을 야기할 수 있다.
(2) 일반 게시물처럼 쓰이되 답글과 답글이 아닌 글을 구분지을 수 있게 하기
별도의 컬럼이 필요하다.
여기서는 (2)의 경우로 구현을 한다고 치겠다.

답글의 구현
답글과 답글이 아닌 글의 구분을 위해 2개의 컬럼을 추가했다.
(1) parent_no : 답글의 대상이 되는 게시글의 고유번호를 담는다. 답글이 아닌 글의 경우는 이 
값이 0으로 들어간다.
(2) depth : 답글의 깊이를 나타낸다. 보통 답글은 1이 설정되며 답글의 답글은 2, 답글의 답글
의 답글은 3 이런 식으로 증가하게 된다.
답글은 세그먼트 정보 테이블에 count 정보에 포함시키지 않는데, 페이징을 용이하게 하기 위함이다.

답글을 게시글 조회 결과에 더하기
(1) 위에서 정의된대로 조회하려는 페이지의 게시글을 가져온다.
(2) 가져온 게시글 목록의 no를 parent_no로 갖는 글들을 가져온다.
(3) 루프를 돌며 답글을 게시글에 알맞게 끼워맞춰준다.