[DB] JSON 형태로 데이터를 저장하는 것에 대한 고민

별다른 관리나 가공이 필요없는 부수적인 데이터, JSON으로 저장해도 될까?

DB 설계를 하던 도중에
'굳이 컬럼으로 하나씩 만들어 저장할 필요가 있을까?'라는 의문이 드는 데이터를 발견했다.

예를 들어, '영화 개봉 스케줄'을 관리하는데 해당 영화에 대한 '메모'가 있다고 쳐보자.
그 메모에는 다음과 같은 정보가 들어간다.

original_title (원제) : today is very good
original_creator (원작자) : tom
temp_title (가제) : 오늘은 아주 굿이다
official_title (확정 타이틀) : 끝내주는 오늘

-> (적절한 예시가 안 떠올라서 이렇게 적었는데 어디까지나 얘넨 단순한 '메모' 데이터일 뿐 중요한 데이터가 아니다)

다양한 정보가 들어가지만 어디까지나 '부수적인' 내용의 '메모'이므로,
그냥 select해서 뿌려주기만 하면 되고, 검색을 하거나 어떤 가공을 하거나... 데이터로서의 관리가 필요 없었다.
즉, 데이터라기보단 '값'에 가까운 정보들이었다.

그렇기 때문에 컬럼을 하나씩 주는 게 아깝다는 생각이 들었고,
한 컬럼에 JSON 형태로 넣는 것을 생각했다.

# 예 : movie_memo 컬럼
{
  "original_title" : "today is very good"
  "original_creator" : "tom"
  "temp_title" : "오늘은 아주 굿이다" 
  "official_title" : "끝내주는 오늘"
}

이렇게 JSON 형태로 넣었을 때의 장단점

• 장점:

1. 불필요하게 컬럼을 늘리지 않아도 되고, select 했을 때 컬럼 하나만 가져오므로 DB의 부담이 적다는 것.
2. movie_memo에 들어갈 컬럼이 늘어나도 유연하게 추가 가능

• 단점 :

1. 만약 검색 등의 쿼리를 짜야할 때 굉장히 복잡해진다.
2. DB단에서 데아터 유효성 체크를 못 함

결국 선택의 문제

훗날 이 정보도 데이터로서 활용할 가능성이 있다면 JSON으로 넣으면 안 되고 컬럼으로 넣는 게 맞고, 만약 그렇지 않고 계속 부수적인 값으로 쓸 것 같다면 JSON으로 넣는 것이 나을 것.

나는 이 메모 안에 들어가있는 각각의 정보들(original_title, original_creator, temp_title, official_title)이 따로 활용될 일은 없고,
이 메모 통째로 select, insert, delete, update될 거라고 판단했기 때문에 JSON 형태로 넣는 것을 택했다.

JSON 데이터 타입 지원하지만, String으로 넣기

또 알아보니 mySQL은 JSON을 데이터 타입으로 지원한다.
하지만 이 경우 DB에서 json object를 파싱하기 때문에 db에 부하가 있을 수 있다고 한다.
따라서 String형태로 DB에 넣되, 코드단에서 JSON 형태로 바꿔서 DB에 넣고, DB에서 select해서 가져올 때도 string의 JSON 형태를 가져와서 decode한 후 response에 잘 담아주는 걸로...

더 알아보기

하지만 DB 생초짜인 나는 이렇게 설계하는 게 과연 맞나? 라는 생각이 들어서...
이것저것 자료를 찾아보았다.

결국 정답은 없고 선택만 있을 뿐이지만, 대충 참고할만한 자료들을 정리해 보았다.

1. Storing JSON in database vs. having a new column for each key

• 제한 없이(임의의 문서 크기 제한 제외) 원하는 만큼 필드를 추가할 수 있기를 원한다면 MongoDB와 같은 NoSQL 솔루션을 고려하십시오.

• 관계형 데이터베이스의 경우 값당 하나의 열을 사용합니다. 열에 JSON Blob을 넣으면 쿼리가 사실상 불가능합니다(실제로 작동하는 쿼리를 찾았을 때 고통스럽게 느려집니다).

• 관계형 데이터베이스는 인덱싱할 때 데이터 유형을 활용하며 정규화된 구조로 구현됩니다.

• 참고로 JSON을 관계형 데이터베이스에 저장하면 안 된다는 말은 아닙니다. 실제 메타데이터를 추가하거나 JSON이 쿼리할 필요가 없고 표시용으로만 사용되는 정보를 설명하는 경우 모든 데이터 요소에 대해 별도의 열을 만드는 것은 과도할 수 있습니다.

• 다음은 개인적으로 JSON이 관계형 데이터베이스에서 유용하다고 생각한 몇 가지 예입니다.
  (1) 연락처에 대한 이메일 주소와 전화번호를 저장할 때 JSON 배열에 값으로 저장하는 것이 여러 개의 개별 테이블보다 훨씬 관리하기 쉽습니다.
  (2) 임의의 키/값 사용자 기본 설정 저장(여기서 값은 부울, 텍스트 또는 숫자일 수 있으며 다른 데이터 유형에 대해 별도의 열을 원하지 않음)
  (3) 정의된 스키마가 없는 구성 데이터 저장(Zapier 또는 IFTTT를 빌드하고 각 통합에 대한 구성 데이터를 저장해야 하는 경우)

• 자주 읽거나 쿼리 하려는 데이터에 대해서는 값당 열을 사용해야 합니다. 누군가의 이름을 JSON에 입력하는 것은 이치에 맞지 않습니다. 이름을 기반으로 쿼리할 가능성은 없지만 매우 자주 필요할 가능성이 높기 때문입니다. 이는 응용 프로그램 측에서 많은 낭비적인 디코딩입니다. 데이터가 JSON으로 더 잘 표현된다고 정말로 생각 하지 않는 한 (저를 믿으세요. 아마도 아닐 것입니다) 그것에 의지해서는 안 됩니다.

• "상황에 따라 다릅니다". 열이나 JSON에 데이터를 저장하는 것은 그 자체로 옳고 그름/좋거나 나쁜 것이 아닙니다. 나중에 처리해야 하는 작업에 따라 다릅니다. 이 데이터에 액세스하는 예상 방법은 무엇입니까? 다른 데이터를 상호 참조해야 합니까?

JSON을 사용하는 유혹 중 하나는 스키마 마이그레이션을 피하는 것이기 때문에 팀이 훈련되지 않은 경우 또 다른 키/값 쌍을 JSON 필드에 붙이기가 매우 쉽습니다. 그것에 대한 마이그레이션이 없으며 아무도 그것이 무엇을 위한 것인지 기억하지 못합니다. 그것에 대한 유효성 검사가 없습니다.

JSON은 매력적이고 강력했지만 어느 날 우리는 유연성이 대가를 치르고 갑자기 큰 골칫거리가 되었다는 사실을 깨달았습니다. 때로는 그 점이 정말 빠르게 다가오고 이 ​​디자인 결정 위에 다른 많은 것들을 구축했기 때문에 변경하기가 어려워집니다.

시간이 지남에 따라 새로운 기능을 추가하고 데이터를 JSON에 저장하면 기존 열을 고수할 경우 추가될 수 있는 것보다 더 복잡해 보이는 쿼리가 생성되었습니다. 그런 다음 조인을 만들고 값을 비교할 수 있도록 특정 키 값을 열로 되돌리기 시작했습니다. 잘못된 생각이었습니다. 중복이 생기기 시작했기 때문입니다.

JSON 필드는 이것저것의 작은 조각을 위한 정크 서랍이 되었습니다. 데이터베이스 수준에서 데이터 유효성 검사가 없으며 문서 간의 일관성 또는 무결성이 없습니다. 기존 열에서 엄격한 유형 및 제약 조건을 확인하는 대신 모든 책임을 앱에 부여했습니다.

돌이켜보면 JSON 덕분에 우리는 매우 빠르게 반복하고 무언가를 얻을 수 있었습니다. 그것은 훌륭했다. 그러나 특정 팀 규모에 도달한 후에는 유연성으로 인해 기술 부채의 긴 밧줄에 매달려 후속 기능 진화 진행 속도가 느려졌습니다. 주의하여 사용하십시오.

데이터의 특성이 무엇인지 오랫동안 열심히 생각하십시오. 앱의 기초입니다. 시간 경과에 따라 데이터가 어떻게 사용됩니까? 그리고 그것이 어떻게 변할 가능성이 있습니까?

2. JSON데이터를 어떻게 DB에 넣을까?

• JSON 데이터는 한 컬럼에 집어 넣는 게 좋다.
• VARCHAR는 최대값이 4000이라, JSON으로 넣을 경우 최대값이 넘을 가능성이 있다.
• 따라서 CLOB, BLOB같은 다른 데이터 타입을 알아보기
• 하지만 해당 데이터 타입은 쿼리가 복잡해질 가능성이 있고, DB 마이그레이션하기도 골치 아플 것. 바이너리 데이터가 들어가서 데이터 분석이 힘들 가능성도 있다.
  (참고 : DB 마이그레이션 - 여러 DB를 하나의 DB로 합치거나, 기존의 DB를 다른 DB로 옮기는 경우 등 DB를 이동시키는 작업을 의미함.)

3. DB에 json을 string으로 저장하고 사용하기, camel -> snake 변환

• DB 컬럼 하나를 string으로 선언하고 거기에 json 형태의 string을 넣어서 보관하고, 데이터를 꺼낼 때 파싱해서 쓰는 형태

• jackson 라이브러리를 이용하면 json object로 읽지 않고 spring에서 data class로 만든 다음 바로 object로 읽어올 수 있다.

• 근데 camelCase와 snakeCase를 구분해서 인식하므로, 이와 관련된 어노테이션을 붙여줘야 에러 나지 않음

• mysql에는 json형태의 field type을 제공하기 때문에 애초에 json필드로 만들어도 된다. 이 경우 DB에서 json object를 파싱하기 때문에 db에 부하가 있을 수 있다. 따라서 string으로 넣고 코드 단에서 파싱하는 게 나을 수도 있음. 사용 목적을 고려해서 json field를 쓸 지, string field를 쓸 지 선택하면 되겠다.

4. <MySQL 성능 최적화> - 06. 스키마 설계와 관리 - JSON 데이터

💡 [결론 요약]

1. 속도는 SQL 열로 있을 때가 더 빠르다.
2. 전반적으로 네이티브 SQL 또는 JSON의 사용 판단은 데이터베이스에 JSON을 저장하는 용이성이 성능보다 우월한지 여부에 달려 있다.
3. 해당 데이터에 액세스 횟수가 많으면 많을수록 속도 차이는 더 커진다.

✔️ 스키마 vs JSON

• 시스템 간에 데이터를 교환하기 위한 형식으로 JSON을 사용하는 것이 점점 보편화되고 있다.
• MySQL에는 테이블 내에서 직접 JSON 구조의 일부를 쉽게 조작할 수 있는 기본 JSON 데이터 유형이 있다.
• 순수주의자들은 데이터베이스에 원시 JSON을 저장하는 것이 안티패턴이라고 주장할 수 있다.
  왜냐하면 스키마로 JSON의 필드를 표현하는 것이 이상적이기 때문이다.
• 반면, 신규 사용자는 독립 필드 생성 및 관리를 피하고 JSON 데이터 유형을 보고 짧은 경로를 볼 수 있다.

✔️ 쿼리 속도와 데이터 크기 비교해보기

• 아래와 같은 데이터가 있다고 하자.

  

• 먼저, JSON 데이터 세트를 가져와 항목당 하나의 행으로 변환했다.
  그 결과 비교적 단순해 보이는 스키마가 생성되었다.

• 둘째, 이 JSON을 가져와서 데이터에 적합한 데이터 유형을 사용하여 필드를 열로 변환했다.
  그 결과 다음 스키마가 생성되었다.

🔸 첫 번째 비교 '데이터 크기'

SQL 버전은 3개의 16KB 페이지를 사용하고 JSON 버전은 5개의 16KB 페이지를 사용한다.
JSON 데이터 유형은 공백뿐만 아니라 JSON을 정의하기 위한 추가 문자(중괄호, 대괄호, 콜론 등)를 저장하기 위해 더 많은 공간을 사용한다.

이 작은 예에서는 JSON을 특정 데이터 유형으로 변환하여 데이터 스토리지의 크기를 향상시킬 수 있다.

🔸 두 번째 비교 '쿼리 지연 시간'
데이터 크기가 그다지 중요하지 않은 사례가 있을 수 있다.
둘 사이의 쿼리 지연 시간은 어떻게 측정할까?
SQL에서 단일 열을 모두 선택하는 구문을 사용한다.

SELECT designation FROM asteroids_sql;

InnoDB의 버퍼 풀에 의해 캐시되지 않은 이 쿼리를 처음 실행했을 때 1.14ms(밀리초)의 결과를 얻었다.
두 번째 실행은 메모리에 저장해서 0.44ms를 얻었다.

JSON의 경우 JSON 구조 내부의 필드에 액세스할 수 있다.

SELECT json_data->'$.designation' FROM asteroids_json

마찬가지로, 캐시되지 않은 첫 번째 실행은 1.13ms 내에 실행되었다.
이후 실행은 약 0.80ms였다. VM 환경에 따라 수백 마이크로초의 차이가 발생한다고 이야기하므로, 이 실행 속도에는 합리적인 편차가 있을 것으로 예상한다.

JSON 쿼리가 여전히 두 배 정도 길기는 하지만, 두 쿼리 모두 합리적으로 빠르게 실행되었다고 생각한다.

• 하지만 특정 행에 액세스하는 것은 어떨까?
  단일 행 조회의 경우 인덱스를 사용한다.

ALTER TABLE asteroids_sql ADD INDEX (designaion);

단일 행 조회를 수행할 때 SQL 버전은 0.33ms에 실행되고 JSON 버전은 0.58ms에 실행되므로 SQL 버전에 우위를 제공한다.
이는 쉽게 설명할 수 있는데, 인덱스 InnoDB가 202행 대신 1행을 반환되도록 허용하기 때문이다.

그러나 인덱싱된 쿼리를 전체 테이블 검색과 비교하는 것은 불공평하다.
경쟁을 대등하게 하려면 JSON의 열을 추출한 다음, 추출한 가상 열에 대한 인덱스를 만들어야 한다.

ALTER TABLE asteroid_json ADD COLUMN designation VARCHAR(30)
GENERATED ALWAYS AS (json_data -> "$.designation"), 
ADD INDEX ( designation ) ;

JSON 테이블에 다음과 같은 스키마를 제공한다.

이제 스키마는 json_data 열에서 지정을 위한 가상 열을 생성하고 인덱싱한다.
단일 행 룩업을 다시 실행하여 JSON열 경로 연산자(->) 대신 인덱싱된 열을 사용한다.
필드 데이터는 JSON에서 인용되기 때문에 SQL에서도 인용된 데이터를 검색할 필요가 있다.

SELECT * FROM asteroids_json WHERE designation='"(2010 GW62)"';

이 쿼리는 SQL 버전 0.33ms에 매우 가까운 0.4ms에 실행되었다.

이전의 간단한 테스트 사례에서 사용된 테이블스페이스에서 원시 JSON 문서를 저장하는 대신 SQL 열을 사용하는 것이 주된 요인으로 보인다.

속도는 SQL 열로 있을 때가 더 빠르다.
전반적으로 네이티브 SQL 또는 JSON의 사용 판단은 데이터베이스에 JSON을 저장하는 용이성이 성능보다 우월한지 여부에 달려 있다.

이 데이터에 매일 수백만 번 또는 수십억 번 액세스한다면 속도 차이가 더 커진다.