当前位置: 首页 > 面试题库 >

什么时候可以将JSON或XML数据保存在SQL表中

东门彬
2023-03-14
问题内容

当使用SQLMySQL(或与此相关的任何关系数据库)时-我知道将数据保存在常规列中对于索引和其他目的更好。

JSON有时,加载和保存数据要简单得多-并使开发更容易。

是否有用于JSON在数据库中保存原始数据的“黄金法则” ?

这样做绝对是不好的做法吗?


问题答案:

主要问题是

  • 您将如何处理这些数据?和
  • 您如何过滤/排序/合并/处理此数据?

JSON(如XML)非常适合数据交换,小型存储和通用定义的结构,但它不能参与在RDBMS中运行的典型操作。在大多数情况下,将JSON数据传输到 普通表中
并在需要时重新创建JSON 会更好。

XML / JSON和 1.NF

规范化的第一条规则规定,决不要将多于一位的信息存储到一列中。您看到带有“ Mickey Mouse”之类的值的“
PersonName”列吗?您指向此并哭了: 立即更改!

XML或JSON呢?这些类型是否会破坏1.NF?好吧,是的,不是…

如果实际上只存储 一小部分信息, 则可以将一个完整的结构存储为 一小部分信息
。您会得到一个SOAP响应并想要存储它,因为您可能需要它作为以后的参考(但您 不会将这些数据用于自己的进程 )?只需 按原样 存储它即可!

现在,想象一个 代表人复杂结构(XML或JSON) (及其地址,更多详细信息…)。现在,将其
放在的一列中PersonInCharge。错了吗 难道这不应该存在于具有外键引用而不是XML /
JSON的经过适当设计的相关表中吗?特别是如果同一个人可能出现在许多不同的行中,那么使用XML / JSON方法肯定是错误的。

但是现在想象一下需要存储历史数据。您要在给定的时间段内 保留 该人的数据。几天后,对方告诉您新地址?没问题!如果您需要,旧地址将保存在XML /
JSON中…

结论: 如果存储数据只是为了保留它,就可以了。如果这些数据是 唯一的 部分,那没关系。
但是如果您需要定期使用 内部零件 ,或者这意味着多余的重复存储,那就不好了。

实体储存

以下内容适用于SQL Server,在其他RDBM上可能有所不同。

XML不是存储为您看到的文本,而是存储为层次结构树。查询这是惊人的好表现!不会在字符串级别解析此结构!
SQL
Server(2016+)中的JSON存在一个字符串中,必须进行解析。没有真正的本机JSON类型(例如,有本机XML类型)。这可能会在以后出现,但是现在我假设JSON在SQL
Server上的性能不如XML(请参阅 UPDATE 2 一节)。任何需要从JSON读取值的操作都将需要大量隐藏的字符串方法调用…

这对您意味着什么?

可爱的数据库艺术家 :-D知道, _ 按原样*_ 存储 _JSON *_违反RDBM的通用原则。 他知道,

  • JSON很可能会破坏1.NF
  • JSON可能会随时间变化(同一列,不同内容)。
  • JSON不易阅读,并且很难对其进行过滤/搜索/加入或排序。
  • 这样的操作会将相当多的额外负载转移到可怜的小型DB服务器上

有一些解决方法(取决于您所使用的RDBMS),但是大多数方法都无法按照您希望的方式工作…

简而言之,您的问题的答案

  • 如果您 不想使用数据,该数据存储 JSON中以进行昂贵的操作(过滤器/联接/排序)。
    您可以像存储任何其他 仅存在的 内容一样存储它。我们将许多图片存储为BLOB,但是我们不会尝试过滤所有带有花朵的图片…

  • 如果您完全不打扰里面的内容(只需将其存储并读取为一点信息即可)

  • 如果结构是可变的,这将使创建物理表变得更加困难,然后将其与JSON数据一起使用。
  • 如果该结构是深层嵌套的,则物理表中的存储将产生大量开销

没有

  • 如果要像使用内部数据一样使用内部表的数据(过滤器,索引,联接…)
  • 如果要存储重复项(创建冗余)
  • 通常:如果您遇到性能问题(可以肯定的是,在许多典型情况下您都会面对它们!)

您可以在字符串列中以JSON开头或以BLOB开头,并在需要时将其更改为物理表。我的魔幻水晶球告诉我,这可能是明天:-D

更新:有关性能的更多信息…

以下内容解决了SQL Server 2016中的JSON和XML支持

用户@
mike123指向微软官方博客上的一篇文章,该文章似乎在实验中得到了证明,与在SQL Server中 查询XML相比
查询JSON的 速度快10倍

关于此的一些想法:

与“实验”进行一些核对:

  • “实验”的措施很多,但不是XML与JSON的性能 。反复对相同(不变)的字符串重复进行相同操作是不现实的情况
  • 一般而言 ,经过测试的示例非常 简单
  • 读取的值始终相同,甚至不使用。优化器将看到此…
  • 关于强大的XQuery支持一言不发!在数组中找到具有给定ID的产品?JSON需要读取全部内容,并在使用之后使用过滤器WHERE,同时XML允许内部使用XQuery predicate。不说FLWOR
  • 在“实验”的代码 作为是 我的系统上带来了:JSON似乎更快(但不是10倍)为3倍。
  • 添加/text()到会将其XPath减少到小于2x。在相关文章中,用户“ Mister Magoo”已经指出了这一点,但是 点击诱饵的 标题仍然没有改变。
  • 通过“实验”中提供的简单JSON,最快的纯T-SQL方法是SUBSTRINGand 的组合CHARINDEX:-D

以下代码将显示更实际的实验

  • 使用一个JSON和一个以上具有多个XML的相同XML Product(JSON数组与同级节点)
  • JSON和XML稍有变化(10000个运行数字),并已插入表中。
  • 两个表都有初始调用反对表,以避免 首次调用偏见
  • 读取所有10000个条目,并将检索到的值插入到另一个表中。
  • 使用GO 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


 类似资料:
  • 问题内容: 我有一个需要保存的JSON数组。我当时正在考虑对其进行序列化,但是最好将它另存为SharedPreferences中的字符串,然后在需要读入它时对其进行重建? 问题答案: Java中的JSON对象无法立即实现可序列化。我已经看到其他人扩展了该类以允许这样做,但是对于您的情况,我只建议将JSON对象存储为字符串并使用其toString()函数。我已经成功了。 并找回它: http://d

  • 问题内容: 我的数据库中有几个小实体,它们表示为带有两列的小表:id和name。此类实体的示例:国家/地区,大洲。 每当这些实体的名称无关紧要时,是否应该创建一个枚举类型? 问题答案: 讨厌用一个问题回答一个问题,但这要视情况而定。您期望值多久更改一次,并多久发布一次代码? 枚举类型将需要更改代码。纯数据库表将更容易更改。枚举类型更便于编码。 对于不经常发布的版本,或者如果您经常有新的/删除的/更

  • 已弃用。这种方法本质上是不安全的。使用thread.Stop停止线程会导致它解锁它锁定的所有监视器(作为未检查的ThreadDeath异常向堆栈上传播的自然结果)。如果以前由这些监视器保护的任何对象处于不一致的状态,则损坏的对象将对其他线程可见,从而可能导致任意行为。stop的许多用法应该被简单修改某个变量以指示目标线程应该停止运行的代码所取代。目标线程应该定期检查这个变量,如果变量指示它要停止运

  • 问题内容: 在最近的项目中,我建议在测试工具代码中捕获RuntimeException并将其记录下来。该代码处理来自数据库的一系列输入,并且我不希望由于任何一个输入(空值,非法参数等)失败而导致测试停止。不用说,我的建议引起了热烈的讨论。 捕获任何一种RuntimeException是否可以接受?如果是,那么可以捕获RuntimeExceptions的其他方案还有哪些? 问题答案: 捕获此异常的原

  • 问题内容: 有效的Java建议我们不要这样做 。总是对的吗? 在许多情况下,仅捕获身体。 如果我不接听电话,如何检查发生的地点? 而且,如果我抓住了但身体空了,那我们那时就无法获得任何堆栈信息,可以吗? 更新 我分析了在Google Android源AOSP4.2.2_r1.2中捕获RuntimeException的统计信息。 有249个RuntimeException捕获,并且以下是catch-

  • 问题内容: 我的要求是仅显示跨数据库从数据库检索的一组值。我正在使用jQuery。 问题答案: 如果满足以下任一条件,则将XML优先于JSON: 您需要消息验证 您正在使用XSLT 您的消息中包含很多标记文字 您需要与不支持JSON的环境进行互操作 当所有这些都成立时,在XML上偏爱JSON: 不需要验证消息,或者验证消息的反序列化很简单 您不是要转换邮件,也不是转换邮件的反序列​​化很简单 您的