SQLテーブルにJSONやXMLデータを保存できるのはいつからか

2023-10-31 04:05:34

質問

を使用する場合 SQL または MySQL (またはリレーショナルDB) - データを通常の列に保存する方がインデックスのためや他の目的には良いことは理解していますが...。

ことは、ロードと保存 JSON のデータをロードして保存することは、時にはもっとシンプルで、開発をより簡単にするものです。

生のデータを保存するための黄金律はありますか？ JSON のデータをDBに保存するための黄金律はありますか？

絶対にやってはいけないことなのでしょうか？

どのように解決するのですか？

主な質問事項は

このデータで何をするつもりですか？と
このデータをどのようにフィルタリング/ソート/結合/操作するのですか？

JSON は (XML のように) データ交換、小さなストレージ、一般的に定義された構造には最適ですが、RDBMS 内で実行する一般的なアクションに参加することはできません。ほとんどの場合、JSON データを 通常のテーブル に転送し、必要なときにJSONを再作成する方がよいでしょう。

XML / JSONと 1.NF

正規化の最初のルールは、1つの列に2つ以上の情報を格納しないことです。PersonName" という列に "Mickey Mouse" のような値があるのを見ましたか？あなたはこれを指差して叫ぶのです。 すぐに変更してください!

XMLやJSONはどうでしょうか？これらのタイプは1.NFを壊しているのでしょうか？まあ、イエスでもありノーでもあるのですが...。

完全な構造を保存することは全く問題ありません。 を1つの情報 もしそれが 1ビットの情報 を実際に使ってみてください。あなたはSOAPレスポンスを取得し、将来の参照にこれが必要かもしれないので、それを保存したいと思います（しかし、あなたは このデータを自分自身の処理に使用しない )? ちょうどそれを格納する そのまま !

では を表す複雑な構造 (XML または JSON) があるとします。 (住所、詳細...を含む)。今、あなたはこの を一つのカラムに PersonInCharge . これは間違っているのでしょうか？これはむしろ、XML/JSONの代わりに外部キー参照で適切に設計された関連テーブルに住むべきではありませんか？特に、同じ人が多くの異なる行で発生する可能性がある場合、XML/JSONのアプローチを使用することは間違いなく間違っています。

しかし、今度は、履歴データを保存する必要性を想像してください。あなたは を持続させたい。 が必要です。何日か後に、その人が新しい住所を教えてくれましたか？問題ありません。古い住所はXML/JSONに保存されているので、もし必要なら...。

結論。 データを保存するだけなら問題ないのですがもしこのデータが ユニーク の部分であれば、大丈夫ですが...。

しかし、もしあなたが 内部部品 が定期的に必要な場合や、冗長な二重保存を意味する場合は、大丈夫ではありません......。

物理ストレージ

以下はSQL Serverの場合であり、他のRDBMでは異なる場合があります。

XMLは目に見えるテキストとしてではなく、階層ツリーとして保存されます。これをクエリすると、驚くほど性能が良くなります! この構造は文字列レベルではパースされません!

SQL Server（2016+）のJSONは文字列で生きているため、パースする必要があります。ネイティブのXML型があるように）本当のネイティブJSON型は存在しません。これは後になるかもしれませんが、今のところ、JSON は SQL Server 上の XML ほどパフォーマンス良くはないと考えています（セクション アップデイト 2 ). JSONから値を読み出す必要がある場合、非常に多くの隠し文字列メソッド呼び出しが必要になるでしょう...。

これはあなたにとって何を意味するのでしょうか？

あなたの 愛すべきDBアーティスト :-D は知っている、その格納 JSON をそのまま というのは、RDBMの一般的な原則に反しています。彼は知っている。

JSON はおそらく 1.NF を壊している。
JSONが時間と共に変化する可能性があること（同じカラム、異なるコンテンツ）。
JSONは読みにくく、フィルタリングや検索、結合、ソートが難しい。
そのような操作は、貧しい小さなDBサーバーにかなりの余分な負荷をシフトすること。

いくつかの回避策はありますが（使用しているRDBMSに依存）、そのほとんどは思い通りにいきません...。

質問に対する答えを簡単に言うと

はい

もし、あなたが に格納されているデータを使用したくない場合、そのデータは内に JSON は、高価な操作 (filter/join/sort) のために使用されます。

これを他の だけが存在します。 のような内容になっています。私たちは多くの画像をBLOBとして保存していますが、花のあるすべての画像をフィルタリングしようとは思わないでしょう...。
中身を全く気にしない場合（ただ保存して1つの情報として読むだけ）
構造が可変で、物理的なテーブルを作成するのが難しく、JSONデータで作業する場合。
構造が深くネストされている場合、物理テーブルへの格納は多くのオーバーヘッドになります。

リレーショナルテーブルのデータを使うように内部データを使いたい場合（フィルタ、インデックス、結合...)
重複して保存する場合（冗長性を持たせる）
一般的にはパフォーマンスの問題に直面した場合 (多くの典型的なシナリオで問題に直面することは間違いありません!)

JSONを文字列カラムまたはBLOBとして開始し、必要なときに物理テーブルに変更することができます。私の魔法の水晶玉は、これが明日になるかもしれないと告げています :-D

UPDATE 2: パフォーマンスについての詳細...

以下は、SQL-Server 2016のJSONとXMLのサポートについてです。

ユーザー @mike123 が指摘したマイクロソフトの公式ブログの記事という実験で証明されているようです。 JSONをクエリすることは 10倍速い であり、XMLのクエリ をSQL-Serverでクエリします。

それについていくつか考えてみました。

"experiment"とのいくつかのクロスチェック。

その 実験"実験"は多くのことを測定しますが、XML対JSONの性能は測定しません。 . 同じ (変更されていない) 文字列に対して同じアクションを繰り返し実行することは、現実的なシナリオではありません。
テストされた例は 一般的なステートメントとしては単純すぎる !
読み込まれた値は常に同じであり、使用すらされない。オプティマイザーはこれを見ることになります...
については一言もなく、強大な XQuery をサポートしています! 配列の中から指定された ID を持つ製品を見つけるのですか？JSON は全体を読み込んでから、フィルタとして WHERE を使用する必要があります。 XML は内部の XQuery predicate . について話すのではなく FLWOR ...
実験コード そのまま を私のシステムで実行すると、表示されます。JSON は 3 倍速くなったようです (ただし 10 倍ではありません)。
追加 /text() に XPath は、これを2倍以下 . 関連記事の中で、ユーザーの "Mister Magoo" が既に指摘していることですが クリックベイト というタイトルはそのままです...。
このような簡単なJSONが与えられたので、最速の純粋なT-SQLのアプローチは、以下の組み合わせでした。 SUBSTRING と CHARINDEX :-D

次のコードは、より現実的な実験を示しています。

JSONと同一のXMLに複数の Product (JSONの配列と兄弟ノードの比較)
JSONとXMLを少し変えて（10000の実行数字）、テーブルに挿入しています。
を避けるために、両方のテーブルに対して初期呼び出しがあります。 ファーストコールバイアス
10000件のエントリーを全て読み込み、取得した値を別のテーブルに挿入します。
使用方法 GO 10 を使用すると、このブロックが10回実行され ファーストコールバイアス

最終的な結果は、JSONがXMLよりも遅いことを明確に示しています。 (それほどでもないが、まだ非常に単純な例で約1.5倍)です。

最終的なステートメントです。

不当な状況下で過度に単純化された例では、JSON は XML よりも高速になる可能性があります。
JSON を処理することは 純粋な文字列操作 で、XMLはパースと変換が行われます。これは最初のアクションではかなり高価ですが、一度これが行われると、すべてのスピードアップにつながります。
JSONは 一度だけ アクションの方が良いかもしれません (XMLの内部階層表現を作成するオーバーヘッドを避けることができます)。
まだ非常に単純ですが、より現実的な例として、XML は単純な読み取りでより高速になります。
配列から特定の要素を読み取る必要がある場合、指定された ProductID が配列に含まれるすべてのエントリをフィルタリングする場合、またはパスを上下に移動する場合、JSON は持ちこたえることができません。文字列から完全にパースされなければなりません - あなたがそれにつかむ必要があるたびに...

テストコード

USE master;
GO
--create a clean database
CREATE DATABASE TestJsonXml;
GO
USE TestJsonXml;
GO
--create tables
CREATE TABLE TestTbl1(ID INT IDENTITY,SomeXml XML);
CREATE TABLE TestTbl2(ID INT IDENTITY,SomeJson NVARCHAR(MAX));
CREATE TABLE Target1(SomeString NVARCHAR(MAX));
CREATE TABLE Target2(SomeString NVARCHAR(MAX));
CREATE TABLE Times(Test VARCHAR(10),Diff INT)
GO
--insert 10000 XMLs into TestTbl1
WITH Tally AS(SELECT TOP 10000 ROW_NUMBER() OVER(ORDER BY (SELECT NULL))*2 AS Nmbr FROM master..spt_values AS v1 CROSS APPLY master..spt_values AS v2)
INSERT INTO TestTbl1(SomeXml)
SELECT 
N'<Root>
    <Products>
    <ProductDescription>
        <Features>
            <Maintenance>' + CAST(Nmbr AS NVARCHAR(10)) + ' year parts and labor extended maintenance is available</Maintenance>
            <Warranty>1 year parts and labor</Warranty>
        </Features>
        <ProductID>' + CAST(Nmbr AS NVARCHAR(10)) + '</ProductID>
        <ProductName>Road Bike</ProductName>
    </ProductDescription>
    <ProductDescription>
        <Features>
            <Maintenance>' + CAST(Nmbr + 1 AS NVARCHAR(10)) + ' blah</Maintenance>
            <Warranty>1 year parts and labor</Warranty>
        </Features>
        <ProductID>' + CAST(Nmbr + 1 AS NVARCHAR(10)) + '</ProductID>
        <ProductName>Cross Bike</ProductName>
    </ProductDescription>
    </Products>
</Root>'
FROM Tally;

--insert 10000 JSONs into TestTbl2
WITH Tally AS(SELECT TOP 10000 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Nmbr FROM master..spt_values AS v1 CROSS APPLY master..spt_values AS v2)
INSERT INTO TestTbl2(SomeJson)
SELECT 
N'{
    "Root": {
        "Products": {
            "ProductDescription": [
                {
                    "Features": {
                        "Maintenance": "' + CAST(Nmbr AS NVARCHAR(10)) + ' year parts and labor extended maintenance is available",
                        "Warranty": "1 year parts and labor"
                    },
                    "ProductID": "' + CAST(Nmbr AS NVARCHAR(10)) + '",
                    "ProductName": "Road Bike"
                },
                {
                    "Features": {
                        "Maintenance": "' + CAST(Nmbr + 1 AS NVARCHAR(10)) + ' blah",
                        "Warranty": "1 year parts and labor"
                    },
                    "ProductID": "' + CAST(Nmbr + 1 AS NVARCHAR(10)) + '",
                    "ProductName": "Cross Bike"
                }
            ]
        }
    }
}'
FROM Tally;
GO

--Do some initial action to avoid first-call-bias
INSERT INTO Target1(SomeString)
SELECT SomeXml.value('(/Root/Products/ProductDescription/Features/Maintenance/text())[1]', 'nvarchar(4000)')
FROM TestTbl1;
INSERT INTO Target2(SomeString)
SELECT JSON_VALUE(SomeJson, N'$.Root.Products.ProductDescription[0].Features.Maintenance')
FROM TestTbl2;
GO

--Start the test
DECLARE @StartDt DATETIME2(7), @EndXml DATETIME2(7), @EndJson DATETIME2(7);

--Read all ProductNames of the second product and insert them to Target1
SET @StartDt = SYSDATETIME();
INSERT INTO Target1(SomeString)
SELECT SomeXml.value('(/Root/Products/ProductDescription/ProductName/text())[2]', 'nvarchar(4000)')
FROM TestTbl1
ORDER BY NEWID();
--remember the time spent
INSERT INTO Times(Test,Diff)
SELECT 'xml',DATEDIFF(millisecond,@StartDt,SYSDATETIME());

--Same with JSON into Target2
SET @StartDt = SYSDATETIME();
INSERT INTO Target2(SomeString)
SELECT JSON_VALUE(SomeJson, N'$.Root.Products.ProductDescription[1].ProductName')
FROM TestTbl2
ORDER BY NEWID();
--remember the time spent
INSERT INTO Times(Test,Diff)
SELECT 'json',DATEDIFF(millisecond,@StartDt,SYSDATETIME());

GO 10 --do the block above 10 times

--Show the result
SELECT Test,SUM(Diff) AS SumTime, COUNT(Diff) AS CountTime
FROM Times
GROUP BY Test;
GO
--clean up
USE master;
GO
DROP DATABASE TestJsonXml;
GO

結果（Acer Aspire v17 Nitro Intel i7, 8GB Ram 上の SQL Server 2016 Express)

Test    SumTime 
------------------
json    2706    
xml     1604