首页 文章

在数据库中存储JSON与为每个键创建一个新列

提问于
浏览 次
141

我正在实现以下模型，用于在我的表中存储用户相关数据 - 我有2列-- uid （主键）和 meta 列，它以JSON格式存储有关用户的其他数据 .

uid   | meta
--------------------------------------------------
 1     | {name:['foo'], 
       |  emailid:['foo@bar.com','bar@foo.com']}
--------------------------------------------------
 2     | {name:['sann'], 
       |  emailid:['sann@bar.com','sann@foo.com']}
--------------------------------------------------

这是一种比单列每属性模型更好的方式（性能方面，设计方面），其中表将包含许多列，如 uid ， name ， emailid .

我喜欢第一个模型，你可以添加尽可能多的字段，没有限制 .

此外，我想知道，现在我已经实现了第一个模型 . 如何对其执行查询，例如，我想获取所有名称为'foo'的用户？

Question - 在数据库中使用-JSON或每个字段的列存储用户相关数据（请记住数字不固定）的更好方法是什么？另外，如果实现了第一个模型，如何查询数据库如上所述？我是否应该同时使用这两个模型，将查询可能在一个单独的行中搜索的所有数据和其他数据存储在JSON中（是不同的行）？

更新

由于我需要执行搜索的列数不会太多，因此使用这两个模型是否明智？我需要搜索的数据的每列密钥和其他人的JSON（在同一个MySQL数据库中）？

mysql sql sql-server database optimization

10 回答

  • 24

    简短的回答你必须在它们之间混合，使用json获取你不会与它们 Build 关系的数据，如联系人数据，地址，产品变量

    回复于
  • 1

    2017年6月4日更新

    鉴于这个问题/答案已经获得了一些人气，我认为值得更新 .

    当这个问题最初发布时，MySQL不支持JSON数据类型，并且PostgreSQL中的支持还处于起步阶段 . 从5.7开始，MySQL now supports a JSON data type（以二进制存储格式）和PostgreSQL JSONB已经成熟 . 这两种产品都提供了可以存储任意文档的高性能JSON类型，包括支持索引JSON对象的特定键 .

    但是，我仍然支持我的原始声明，即在使用关系数据库时，您的默认首选项应该仍然是每列值列 . 关系数据库仍然 Build 在假设其中的数据将被很好地规范化的基础上 . 查看计划程序在查看列时比在查看JSON文档中的键时具有更好的优化信息 . 可以在列之间创建外键（但不能在JSON文档中的键之间创建） . 重要的是：如果您的大多数架构都具有足够的易变性以证明使用JSON，那么您可能至少需要考虑关系数据库是否是正确的选择 .

    也就是说，很少有应用程序是完美的关系或面向文档 . 大多数应用程序都有两者兼而有之 . 以下是我个人在关系数据库中发现JSON有用的一些示例：

    • 存储联系人的电子邮件地址和电话号码时，将其作为值存储在JSON数组中比多个单独的表更容易管理

    • 保存任意键/值用户首选项（其中值可以是布尔值，文本或数字，并且您不希望为不同的数据类型分别具有列）

    • 存储没有定义架构的配置数据（如果您正在构建Zapier或IFTTT并且需要为每个集成存储配置数据）

    我确信还有其他人，但这些只是一些简单的例子 .

    原始答案

    如果您真的希望能够无限制地添加任意数量的字段（除了任意文档大小限制），请考虑使用NoSQL解决方案，例如MongoDB .

    对于关系数据库：每个值使用一列 . 将JSON blob放在列中使得查询几乎不可能（当您实际找到有效的查询时，会非常慢） .

    关系数据库在索引时利用数据类型，并且旨在使用规范化结构实现 .

    作为旁注：这不是添加真正的元数据，或者如果您的JSON描述的信息不需要查询且仅用于显示，那么为所有数据点创建单独的列可能会有些过分 .

    回复于
  • 12

    像大多数事情“它取决于” . 将数据存储在列或JSON中本身并不正确或错误/好或坏 . 这取决于你以后需要做什么 . 您预测的访问此数据的方式是什么？你需要交叉引用其他数据吗？

    其他人已经很好地回答了技术上的权衡 .

    没有多少人讨论过您的应用和功能随着时间的推移而发展，以及这种数据存储决策如何影响您的团队 .

    因为使用JSON的诱惑之一是避免迁移模式，因此如果团队没有规范，那么将另一个键/值对粘贴到JSON字段中非常容易 . 它没有迁移，没有人记得它是什么 . 没有验证 .

    我的团队在postgres的传统专栏中使用了JSON，起初它是切片面包以来最好的东西 . JSON很有吸引力，也很强大，直到有一天我们才意识到这种灵活性需要付出代价，这突然成为一个真正的痛点 . 有时这一点很快就会迅速增加，然后变得很难改变，因为我们在这个设计决策之上已经 Build 了很多其他的东西 .

    加班，添加新功能，使用JSON中的数据导致查看比查看传统列时可能添加的更复杂的查询 . 因此，我们开始将某些键值捕获回列中，以便我们可以进行连接并在值之间进行比较 . 馊主意 . 现在我们有重复 . 一个新的开发人员会加入并混淆？我应该挽回的 Value 是多少？ JSON one还是列？

    JSON字段变成了垃圾抽屉，用于这个和那个小部分 . 没有数据库级别的数据验证，文档之间没有一致性或完整性 . 这将所有责任推到了应用程序中，而不是从传统列中获取硬类型和约束检查 .

    回顾过去，JSON允许我们快速迭代并获得一些东西 . 太棒了 . 然而，在我们达到某个团队规模之后，它的灵活性也使我们能够忍受长长的技术债务，从而减缓后续功能演变的进程 . 谨慎使用 .

    仔细思考数据的性质是什么 . 它是您的应用程序的基础 . 如何随着时间的推移使用数据 . 它怎么可能改变？

    回复于
  • 0

    只是折腾它，但WordPress有这种东西的结构（至少WordPress是我观察它的第一个地方，它可能起源于其他地方） .

    它允许无限密钥，并且比使用JSON blob更快搜索，但不如某些NoSQL解决方案快 .

    uid   |   meta_key    |   meta_val
----------------------------------
1         name            Frank
1         age             12
2         name            Jeremiah
3         fav_food        pizza
.................

    EDIT

    用于存储历史/多个键

    uid   | meta_id    |   meta_key    |   meta_val
----------------------------------------------------
1        1             name            Frank
1        2             name            John
1        3             age             12
2        4             name            Jeremiah
3        5             fav_food        pizza
.................

    并通过以下方式查询：

    select meta_val from `table` where meta_key = 'name' and uid = 1 order by meta_id desc
    回复于
  • 4

    这种方法的缺点正是你提到的：

    因为每次你需要对它进行文本搜索时，它会使搜索速度非常慢 .

    而是每列的值匹配整个字符串 .

    您的方法（基于JSON的数据）适用于您不需要搜索的数据，只需要与您的常规数据一起显示 .

    Edit: 为了澄清，上面的内容适用于经典的关系数据库 . NoSQL在内部使用JSON，如果这是期望的行为，则可能是更好的选择 .

    回复于
  • 0

    基本上，您使用的第一个模型称为基于文档的存储 . 你应该看看流行的 NoSQL document-based database like MongoDB and CouchDB . 基本上，在基于文档的数据库中，您将数据存储在json文件中，然后您可以查询这些json文件 .

    第二个模型是流行的关系数据库结构 .

    如果你想使用像MySql这样的关系数据库，那么我建议你只使用第二个模型 . There is no point in using MySql and storing data as in the first model .

    要回答你的第二个问题， there is no way to query name like 'foo' if you use first model .

    回复于
  • 0

    似乎你主要是犹豫是否使用关系模型 .

    就目前而言，您的示例可以很好地适应关系模型，但当您需要使此模型发展时，问题可能会出现 .

    如果您的主实体（用户）只有一个（或几个预定的）属性级别，您仍然可以在关系数据库中使用实体属性值（EAV）模型 . （这也有其优点和缺点 . ）

    如果您预计您将使用您的应用程序搜索较少的结构化值，那么MySQL可能不是最佳选择 .

    如果您使用的是PostgreSQL，那么您可能会获得两全其美的效果 . （这实际上取决于这里数据的实际结构...... MySQL不仅仅是建议替代方案 . ）

    实际上，PostgreSQL可以构建（不可变的）函数的索引（MySQL不能据我所知），在最近的版本中，你可以use PLV8 on the JSON data directly在感兴趣的特定JSON元素上构建索引，这样可以提高查询的速度 . 搜索该数据 .

    EDIT:

    由于我不需要执行搜索的列太多，使用这两个模型是否明智？我需要搜索的数据的每列密钥和其他人的JSON（在同一个MySQL数据库中）？

    混合这两个模型不一定是错误的（假设额外的空间可以忽略不计），但是如果你不确保两个数据集保持同步，它可能会导致问题：你的应用程序必须永远不更改一个而不更新另一个 .

    实现此目标的一种好方法是让触发器执行自动更新，方法是在进行更新或插入时在数据库服务器中运行存储过程 . 据我所知，MySQL存储过程语言可能缺乏对任何类型的JSON处理的支持 . 具有PLV8支持的PostgreSQL（以及可能具有更灵活的存储过程语言的其他RDBMS）应该更有用（使用触发器自动更新关系列与以相同方式更新索引非常相似） .

    回复于
  • 8

    你试图适应非关系模型到关系数据库中，我认为使用NoSQL数据库（例如MongoDB）会更好 . 没有预定义的模式符合您对字段数量没有限制的要求（请参阅典型的MongoDB集合示例） . 查看MongoDB documentation以了解您如何查询文档，例如：

    db.mycollection.find(
    {
      name: 'sann'
    }
)
    回复于
  • 143

    一些时间加入桌面将是一个开销 . 让我们说OLAP . 如果我有两个表，一个是ORDERS表，另一个是ORDER_DETAILS . 为了获得所有订单详细信息，我们必须连接两个表，这将使查询更慢，因为表中没有行增加让我们说数百万左右 . 左/右连接比内连接慢得多 . 我想如果我们在相应的ORDERS条目中添加JSON字符串/ Object将避免使用JOIN . 添加报告生成会更快......

    回复于
  • 37

    正如其他人指出的那样，查询会更慢 . 我建议至少添加一个'_ID'列来进行查询 .

    回复于

相关问题