GROUP_CONCAT을 사용하여, 빠르게 1:N의 데이터를 가져오는건 어떨까요?
개요
데이터베이스에서 1:N의 관계의 데이터가 있을 때, JOIN을 통해 N의 데이터를 추출하는 경우,
1에 대한 값도, N개만큼의 row만큼 복제되어 결과가 출력됩니다.
이는 당연한 JOIN 연산의 역할입니다.
1에 대한 데이터를 N에 맞춰서 표현하고자 하니, N기준에서는 1의 row가 N만큼 복제되어야 정상적으로 표출될 수 있습니다.
하지만, 우리가 실제로 사용하고 싶은데이터는, 대부분 1에 대한 테이블의 1개의 ROW에 그냥 배열의 형태로 N개의 데이터를 가지길 바랍니다.
왜냐하면, 리스트로 순환해서 뷰로 출력해야하는데, 부모가 N번 순회해서 출력되면, 같은 데이터가 너무 많이 표출되어 버리기 때문이죠.
list => [
[0] => [
'id' => 1,
'title' => '반지의 제왕',
'reader' => '라이언'
],
[1] => [
'id' => 1,
'title' => '반지의 제왕',
'reader' => '제이슨'
]
]
PHP
복사
위에서는 ‘반지의 제왕’ 이라는 책을 누가 읽었는지, ‘books’ 테이블과 ‘book_readers’ 테이블을 JOIN해서 가져온 데이터 입니다.
JOIN은 단순히 카테시안곱의 ROW 갯수 만큼 반환하므로, 위와 같은 형태로 반환하게 됩니다.
우리는 이렇게 응답하는 DB의 데이터를 아래와 같이 변환하고 싶습니다.
list => [
[0] => [
'id' => 1,
'title' => '반지의 제왕',
'reader' => [
[0] => '라이언',
[1] => '제이슨'
]
]
]
PHP
복사
이렇게 말이죠..!
방법
[편의상 회원은 정규화 X]
•
books
id | title |
1 | 반지의 제왕 |
2 | 얼음과 불의 노래 |
•
book_readers
id | book_id | reader |
1 | 1 | 라이언 |
2 | 1 | 제이슨 |
•
일반 조인 방식
SELECT *
FROM
books B
JOIN
book_readers BR ON BR.book_id = B.id
SQL
복사
•
결과
B.id | B.title | BR.id | BR.book_id | BR.reader |
1 | 반지의 제왕 | 1 | 1 | 라이언 |
1 | 반지의 제왕 | 2 | 1 | 제이슨 |
•
GROUP_CONCAT을 사용한 방식
SELECT
B.*,
CONCAT(
'[',
GROUP_CONCAT(
JSON_OBJECT(
'reader', BR.reader
)
),
']'
) AS readers
FROM
books B
JOIN
book_readers BR ON BR.book_id = B.id
GROUP BY
B.id
SQL
복사
•
결과
B.id | B.title | readers |
1 | 반지의 제왕 | [{"reader": "라이언"},{"reader": “제이슨"}] |
이런식으로 jsonArray의 형태로 추출할 수 있습니다.
CONCAT(…) ⇒ 입력된 문자열을 연결함. ‘[’, 내용, ‘]’ 으로 연결시켜 구성함.
GROUP_CONCAT(…) ⇒ 그룹핑되어 나오는 row에서 N에 해당 하는 데이터를 separator를 통해서 묶어서 응답함. (그룹핑 되어서 축약되는 경우와, 서브쿼리를 통해 결과 row가 N개 나오는 2가지 경우에 사용할 수 있음.) ⇒ 기본 separator는 ‘,’ 임.
JSON_OBJECT(key1, value1, ….) ⇒ key-value 형태로 데이터를 넣으면, JsonObject 형태로 문자열로 추출합니다.
즉, 우선 JSON_OBEJCT를 통해, 데이터를 JSON형태로 직렬화를 수행합니다.
GROUP_CONCAT을 통해서, N개의 데이터를 ‘,’ 구분자로 연결합니다.
마지막으로, Array를 표현하기 위해서, CONCAT으로 [] 사이에 내용이 담기도록 구성합니다.
이제 이걸 PHP의 배열로 다루기 위해서,
SQL문을 통해 데이터를 추출한 이후에
json_decode() 함수를 통해서 PHP배열로 변환해서 사용할 수 있게 됩니다.
방법2 → 서브쿼리로 추출하기.
SELECT
B.*,
(SELECT
IFNULL(
CONCAT(
'[',
GROUP_CONCAT(
JSON_OBJECT(
'reader', BR.reader
),
']'
)
), '[]'
)
FROM
book_readers BR
WHERE
BR.book_id = B.id
) AS readers
FROM
books B;
SQL
복사
GROUP_CONCAT의 동작은 반드시 GROUP BY만 사용한다고 그룹핑 해주는 함수는 아닙니다.
서브쿼리를 통해 응답되는 row의 갯수가 N개 되는 경우에는 GROUP_CONCAT으로 하나로 합쳐줄 수 있습니다.
심지어, 서브쿼리의 결과가 N개인 경우에 GROUP_CONCAT이 사용되면, 다중 행 반환에 관한 오류를 없애는 것도 가능합니다.
다만, 서브 쿼리의 경우는 이미 FROM에서 데이터를 가져온 상태에서, 하나의 컬럼의 데이터만 서브쿼리를 가져오므로, NULL이 되는 경우를 조심해야 합니다.
이를 위해 IFNULL(값이 있으면 이거, 값이 없으면 이거) 함수를 통해 예상치 못한 동작이 발생하지 않도록 할 수 있습니다.
장점
•
결과 데이터를 카테시안곱이 아닌, 이미 그룹핑 된 형태로 가져오므로, 서버와 DB의 데이터 전달 용량이 대폭 감소함.
단점
•
json을 직접 PHP배열로 변환시켜야 함.
•
SQL문의 가독성이 낮아짐.
마무리
카테시안 곱에 대한 결과가 많아질 경우에는, ( 1:N 중에 N의 데이터가 많은 경우 )
미리 그룹핑하여 데이터를 JSON형태로 출력하는 기법은 매우 유용합니다.
ORM을 사용하면, 이를 자동적으로 맵핑해주게 되죠.
하지만, 결과로 받아야 하는 데이터가 매우 많은 경우 ORM에 의한 객체 맵핑이 되는 오버헤드 발생은 피할 수 없는 운명입니다.
Java진영에서는 MyBatis를 사용하게 되면, 카테시안곱으로 나오는 SQL문의 결과를 특정 key를 지정하여, 1:N의 관계로 객체로 만들어 매핑해주는 방식을 사용할 수 있습니다.
이는 분명히 뛰어난 편리성을 제공하지만, 너무 많은 데이터를 다루어야 하는 경우에는 불필요한 데이터 생성 및 후처리 작업으로 인한 오버헤드가 발생하게 될 것입니다.
PHP를 통해서도 후처리로 그룹핑을 해줄 수도 있겠지만, 이 또한 애초부터 불필요한 데이터를 가공하는 작업에서 오버헤드가 발생하게 됩니다.
분명 익숙치 않은 방식이라, 왜 이런 방식을 사용해야 하는지 이해가 어려울 수 있겠지만,
대용량 데이터를 다루는 경우라면, 이런 방식을 간과할 수 없다고 생각합니다.
감사합니다.