[mysql] MYSQL 5.7의 기본 JSON 지원 : MYSQL에서 JSON 데이터 유형의 장단점은 무엇입니까?

SELECT * FROM t1
WHERE JSON_EXTRACT(data,"$.series") IN ...

이와 같은 식 또는 함수 내에서 열을 사용하면 쿼리를 최적화하는 데 도움이되는 인덱스를 사용하는 쿼리의 가능성이 사라집니다. 위에 표시된 쿼리는 강제로 테이블 스캔을 수행합니다.

“효율적인 액세스”에 대한 주장은 잘못된 것입니다. 이는 쿼리가 JSON 문서가있는 행을 검사 한 후 JSON 구문의 텍스트를 구문 분석하지 않고도 필드를 추출 할 수 있음을 의미합니다. 그러나 행을 검색하려면 여전히 테이블 스캔이 필요합니다. 즉, 쿼리는 모든 행을 검사해야합니다.

비유하자면, 이름이 “Bill”인 사람들을 전화 번호부에서 검색하는 경우 전화 번호부의 모든 페이지를 읽어야합니다. 비록 이름이 강조 표시되어있어 더 빨리 찾을 수 있습니다.

MySQL 5.7에서는 테이블에 가상 열을 정의한 다음 가상 열에 인덱스를 만들 수 있습니다.

ALTER TABLE t1
  ADD COLUMN series AS (JSON_EXTRACT(data, '$.series')),
  ADD INDEX (series);

그런 다음 가상 열을 쿼리하면 인덱스를 사용하고 테이블 스캔을 피할 수 있습니다.

SELECT * FROM t1
WHERE series IN ...

이것은 좋지만 JSON 사용의 요점을 놓친 것입니다. JSON 사용의 매력적인 부분은 ALTER TABLE을 수행하지 않고도 새 속성을 추가 할 수 있다는 것입니다. 그러나 인덱스를 사용하여 JSON 필드를 검색하려면 어쨌든 추가 (가상) 열을 정의해야합니다.

그러나 JSON 문서의 모든 필드에 대해 가상 열과 인덱스를 정의 할 필요는 없으며 검색하거나 정렬하려는 필드 만 정의 할 수 있습니다. 다음과 같이 선택 목록에서만 추출해야하는 다른 속성이 JSON에있을 수 있습니다.

SELECT JSON_EXTRACT(data, '$.series') AS series FROM t1
WHERE <other conditions>

일반적으로 이것이 MySQL에서 JSON을 사용하는 가장 좋은 방법이라고 말하고 싶습니다. 선택 목록에서만.

다른 절 (JOIN, WHERE, GROUP BY, HAVING, ORDER BY)의 열을 참조 할 때 JSON 문서 내의 필드가 아닌 기존 열을 사용하는 것이 더 효율적입니다.

2018 년 4 월 Percona Live 컨퍼런스에서 How to Use JSON in MySQL Wrong 이라는 강연을 발표했습니다 . 가을에 Oracle Code One에서 업데이트하고 반복 할 예정입니다.

JSON에는 다른 문제가 있습니다. 예를 들어, 내 테스트에서는 동일한 데이터를 저장하는 기존 열에 비해 JSON 문서를위한 저장 공간이 2-3 배 더 필요했습니다.

MySQL은 주로 MongoDB 로의 마이그레이션에 대해 사람들을 설득하기 위해 새로운 JSON 기능을 적극적으로 홍보하고 있습니다. 그러나 MongoDB와 같은 문서 지향 데이터 저장소는 기본적으로 데이터를 구성하는 비 관계형 방법입니다. 관계형과 다릅니다. 나는 하나가 다른 것보다 낫다고 말하는 것이 아니라 다른 유형의 쿼리에 적합한 다른 기술 일뿐입니다.

JSON이 쿼리를 더 효율적으로 만들 때 JSON을 사용하도록 선택해야합니다.

새로운 기술이나 패션을위한 기술을 선택하지 마십시오.

편집 : MySQL의 가상 열 구현은 WHERE 절이 가상 열 정의와 정확히 동일한 식을 사용하는 경우 인덱스를 사용하도록되어 있습니다. 즉, 가상 컬럼이 정의되어 있으므로 다음 은 가상 컬럼의 인덱스를 사용해야합니다.AS (JSON_EXTRACT(data,"$.series"))

SELECT * FROM t1
WHERE JSON_EXTRACT(data,"$.series") IN ...

이 기능을 테스트하여 표현식이 JSON 추출 기능인 경우 어떤 이유로 작동하지 않는다는 사실을 발견 한 경우를 제외하고는. JSON 함수가 아닌 다른 유형의 표현식에서 작동합니다.

답변

{
  "en":"this is English name",
  "zh":"this is Chinese name"
   ...(other languages)
}

[
  {
    "filed1":"value",
    "filed2":"value"
    ...
  },
  {
    "filed1":"value",
    "filed2":"value"
    ...
  }
  ...
]

{ "name": "value" }

{ "name": "value", "newName": "value" }

UPDATE `JsonTable` SET `JsonColumn` = JSON_SET(`JsonColumn`, "newName", JSON_EXTRACT(`JsonColumn`, "name"))