主页 / dba / 问题 / 134898
Accepted
Victor Tello
Asked: 2016-04-11 08:43:02 +0800 CST

大量索引 - MySQL vs MongoDB - 迁移

  • 772

所以，我从来没有使用过 MongoDB，我只是阅读了很多关于它的内容，我认为它对我的项目会有好处。另外，我没有太多使用MySQL的经验，而且老实说，我不知道我要问什么。

设想：

MySQL表profile：

  • id = [pk, auto_increment, smallint]
  • user_id = [pk, fk, varchar]
  • category_id = [pk, fk, smallint]
  • role_id = [pk, fk, ti​​nyint]
  • country_id = [pk, fk, smallint]
  • state_id = [pk, fk, smallint]
  • legal_document = [pk, varchar, ?unique]
  • 名称 = [pk, varchar, ?unique]
  • 类型 = [pk，布尔]
  • last_activity = [pk，日期]

当然，所有fk你看到的都是 MySQL 表。然后我正在考虑使用 MongoDB 来存储配置文件信息，profile_info集合应该包含如下文档：

{
  '_id' : 1 (profile_id),
  'address': 'Some street in some state of some country :P',
  'phone': [5555555, 5555555],
  'email': '[email protected]',
   etc...
}

我打算使用 mongodb，因为我的项目需要尽快公开，我们可能会添加一些新的配置文件信息和其他内容，我不想更改表并从具有大量行的工具迁移。

话虽如此，我们可能不得不在 MySQL 表中添加更多pk的 s，所以我正在考虑将所有项目迁移到 MongoDB，我不知道这是否是一个好的举措。

问题：

  • 最好将pks 保留在 MySQL 中，将琐碎的信息保留在 MongoDB 中，或者如果我将所有内容都移到 MongoDB 中会更好吗？

  • 如果我只是迁移整个项目但我保持这样的结构，MongoDB 可能会更快吗？我的意思是喜欢拥有profile和profile_info收藏而不是仅仅profile。

  • 我担心 MongoDB 可以为具有这么多索引的“表/集合”使用多少资源，我希望将磁盘空间和内存保持在最低限度。MySQL 和 MongoDB 之间有关键区别吗？

PS：系统将使用SSD。

PS II：所有表都只是计划好的，还没有写任何东西。我是一个计划非常超前的人，所以请耐心等待我。

mysql index

6 个回答

  1. Best Answer

    鉴于您的描述，我强烈建议不要使用 MongoDB。并不是因为这必然是一个糟糕的选择（尽管我相信你的情况并非纯技术原因）。

    以下是引起我注意的要点。

    数据建模

    尝试将 MongoDB 与没有调整的关系数据模型一起使用几乎总是会导致眼泪和痛苦，除了最微不足道的用例。这是故事更好的结局。更糟糕的结局是赔钱，可能是大手笔。

    这样做的原因是，使用 SQL，您可以识别您的实体及其属性和关系，然后将头撞到墙上几个小时，以使您的左上角超出 JOINS 权限，从而回答您从用例中得出的问题. 同时避免像魔鬼圣水一样的数据冗余。

    使用 MongoDB 进行数据建模的工作方式不同。您可以识别用例和从中衍生的问题，并以某种方式对数据进行建模，以便以最有效的方式回答这些问题。

    由于这有点抽象，让我给你一个

    例子

    让我们假设您有一个名为“chirper”的 Web 应用程序，用户会发出啁啾声。使用 SQL，您现在可以对数据进行建模，生成用户表和 chirp 表之类的东西。

    您遇到的第一个用例是您希望在应用程序主页上显示最新的 10 个啁啾，以及啁啾的用户名。使用 SQL 很简单，您可以对啁啾和用户进行连接，按数据降序对结果进行排序并限制为 10 条记录。

    使用 MongoDB，您将仔细查看您需要什么。由于各种原因，您不想将啁啾嵌入到用户集合中。但是由于您确实需要将啁啾与用户相关联，因此您决定通过用户名这样做，并为您的啁啾集合提出一个“模式”，如下所示：

    {
  _id: ObjectId("570b87a56931b8f21a8bf25c"),
  user: "jdoe",
  date: ISODate("2016-04-11T11:17:08.415Z"),
  text: "Chirp!!!"
}

    而且，奇迹般地，你所要做的就是做一个

    db.chirps.find({}).sort({date:-1}).limit(10)

    它以相对便宜的磁盘空间为代价，在没有 JOINS 的情况下为您提供相同的结果。

    资源限制

    我想将磁盘空间和内存保持在最低限度

    MongoDB 很多，但肯定不是资源友好的。它从来没有打算替代 MySQL——顺便说一下，它是专门设计为相对轻量级的通用 RDBMS。我强烈建议不要出于生产目的在同一台服务器上运行 MongoDB 和其他任何东西。原因是多方面的，但这里是最重要的：

    1. MongoDB 将占用高达 85% - 90% 的可用物理 RAM。这是因为工作集（索引和数据子集）保存在 RAM 中。
    2. 根据您用于 MongoDB 的存储引擎及其配置，甚至 CPU 利用率也可能相当可观。
    3. 负载很重的 MongoDB 需要大量的磁盘 IO。我见过在 RAID0 中使用 SSD 的系统，其中 IO 速率已达到极限。

    暂时不考虑细节，这意味着在您最不想要它的情况下（您的应用程序正在起飞并拥有大量用户），MongoDB 将与应用程序的其他部分争夺资源。

    不要误会我的意思：MongoDB 本身并不是一个资源消耗怪物。但是，如果它完成了预期的工作，即处理大量数据和数据更改，那么您不希望应用程序的某些部分争夺资源。

    话虽如此：有一些方法可以限制 MongoDB 的资源分配并确保遵守这些限制。到目前为止，最知名的可能是 Docker。你必须决定在 Docker 容器中运行 MongoDB 是否值得。

    至于数据文件大小：默认的wiredTiger存储引擎有透明压缩可用。您可以从snappy（默认）或更知名的zlib压缩中进行选择。如上所述，两者都以更高的 CPU 利用率为代价。

    上市时间

    请原谅我，但您表现出缺乏有关 MongoDB 的基本知识。既然你不知道该怎么做，你能做的最好的就是仔细研究每一步，做出决定，冲洗并重复。而且我什至没有考虑到您需要对数据进行重构，并且很可能对您的应用程序持久层进行大量更改。如果我是你并且想要/需要一个快速的上市时间，我会选择我现在所拥有的。万一生产过程中出现问题，我会给自己找一位专门研究 NoSQL 的顾问，以找出各种 NoSQL 数据库中的一个是否适合您的需求，确定所说的 DBMS（可能是也可能不是 MongoDB），让自己成为这方面的专家然后才进行迁移。有专家在您身边。

    行政

    人们常犯的一个错误是，他们认为管理 MongoDB 部署就像让它工作一样容易。它不是。任何 DBA、系统管理员甚至 DevOps（即使在错误的意义上使用）都应该能够运行分片集群。选择合适的维度，发现问题，决定何时扩展，处理重要的问题和错误？没那么多。请注意，后者是高度主观的，并且容易受到相关 DBA 技能的影响。您真的想将生产数据存储在您几乎不了解的系统上吗？

    结论

    恕我直言，MongoDB 不符合您所说的要求。现在改变很可能会增加您的上市时间，而且非常重要，因为您不了解 MongoDB 的复杂性和陷阱。在最坏的情况下，您积累了技术债务，同时降低了应用程序的性能。

    MongoDB 不太适合您的资源限制要求，要找到资源限制的最佳位置以提供可接受的性能需要经验丰富的 DBA 和相当长的时间，在此期间您的应用程序可能会以低于标准的性能运行。

    让我把它放在一些背景下：MongoDB 可能非常适合您的用例，甚至是卓越的。但鉴于您的知识水平和规定的要求，我认为到目前为止更改您的持久性技术是不可行的。

    hth

    • 7

  2. 我认为在决定使用哪种工具来开发项目之前，您需要考虑多个（非常重要的）方面。

    主要目标应该是按原样管理相关数据，这是一项非常有价值的组织资产，实现上述目标的可靠方式是通过基于合理理论的技术手段。

    在这方面，值得一提的是，确定数据库的成功不仅取决于选择的数据库管理系统（DBMS），还取决于许多因素，例如：

    • 其逻辑模型
    • 其物理实现设置
    • 其合格的管理

    由于您正在考虑将 SQL 平台作为暂定的 DBMS，因此这一事实表明了实现关系数据库的意图，因此我将在整个答案中关注这方面。

    尽管EF Codd 博士（图灵奖获得者）早在 1970 年就发表了他的开创性论文《大型共享数据库的关系模型》，但我真的认为他的杰出工作仍然是无与伦比的和最先进的，例如，它是牢固地基于一阶逻辑和集合论。

    当在 SQL 平台中实现时，设计良好的数据库可以获得关系理论提出的许多优点。相反，设计不佳的数据库很容易变得无法运行。话虽如此，重要的是要意识到关系数据库的开发需要对感兴趣的特定业务领域有深入的了解。因此，您必须对所有关注的事物进行分析和分类，而这些任务需要强大的数据建模技能。这样，如果您对业务上下文有清晰的了解和良好的建模能力，您将能够创建一个强大的逻辑数据库结构，准确地表示业务上下文，并且可以轻松扩展和修改。

    一旦你开发了一个稳定的数据库（考虑到你决定使用的 DBMS 的细节）并启动了你的系统，是时候集中精力管理服务器了，正如人们所期望的那样，管理数据，这里是其中数据库管理技能尤为重要。

    因此，如您所知，所有这些都需要一定的经验，您只能通过开展多个项目来获得这些经验，最好是在专业同事或团队的监督下。

    需要考虑的方面

    因此，为了做出明智的决定，您应该：

    • 继续问好问题。
    • 花时间学习关系理论。
      • 在这方面，我强烈推荐 Codd 博士参考书目，这样你就可以直接向关系范式的鼻祖学习。
    • 提高您的数据建模技能。
      • 您可能会发现感兴趣的IDEF1X。它是一种强大且富有表现力的技术，在 1993 年被美国国家标准与技术研究院 (NIST) 定义为标准。
      • 在这篇元文章中，我讨论了一些基本的建模要点，在这个答案中，我处理了一个基本的数据库结构，以防您感兴趣。
    • 评估与 MySQL 相关的功能和限制。
    • 评估其他 SQL 系统并将它们与 MySQL 进行比较。
      • 值得注意的是，各大平台多年来（甚至几十年）都进行了大量优化。
      • 有不同的开源替代方案非常有趣。
    • 研究可用的不同 SQL 方言。
    • 按照理论规定获得 SQL 实践经验，以便您可以看到它们的实际价值。
    • 找出作为 MongoDB 基础的理论。
    • 研究类似于 MongoDB 的工具。
    • 将 MongoDB（和其他类似工具）与 SQL 软件（以及前关系技术）进行比较。

    Profile 表主键定义和索引

    您以特定方式引起我注意的问题的一部分是您将表的所有列定义profile为 PRIMARY KEY，您在以下评论中对此进行了解释：

    [...] 是的，它们是主键，我制作它们pk是因为据我所知，这使它们成为索引并且可以加快该表上的 SELECT 操作。我的意思是我们可能需要向表中添加更多索引 ( pk) profile。我已经读过，用很多行做 ALTER 表可能会持续很长时间并且可能很复杂，我也想做分片或任何可能有助于提高性能的技术。

    因此，有一些关于关系键和索引结构的基本（并且非常相关）点需要澄清。

    主键

    A PRIMARY KEY(PK) 表示一个逻辑元素，它是一个列（或列的组合），其中包含唯一标识相应表中给定行的值。一张桌子不能设置多个 PK。

    在物理层面，PK 通常有一个下属INDEX，除了加快数据检索（正如您正确提到的那样），还有助于确保确定行的唯一性（INDEX实际上是这样说的UNIQUE）。

    备用键

    一个表可以有一个或多个ALTERNATE KEYs（AK），它们也是逻辑成分。AK 是一列（或列组合），它保留了唯一标识相应表中某一行的值，但未被选为 PK。

    可以通过 a 建立 AK UNIQUE CONSTRAINT，这通常由提高检索速度并自然保护行唯一性的物理辅助。 INDEX

    不是（或不属于）主键或备用键定义的列上的索引

    INDEXed如果这种方法加速了您的某些查询，那么不属于（或不属于）PK 或 AK 的列也可以是。因此，您不需要向 PK 添加新列以获得物理优势，您只需将它们合并到复合非唯一列（或在必要时为每个对应列INDEX创建非唯一列）INDEX不将它们添加到 PK 中，因为这样做您将失去 PK 对其上下文含义的定义。

    实体类型、键和含义

    如果在给定上下文中涉及的人已经确定某种事物，即实体类型具有组织意义，那么所述实体类型的每个实例必须通过一个（或多个）的一个（或多个）值来区分它的属性，因此 PK 和 AK 是数据的基本质量，它们完全取决于语义方面。每个实体类型都应设置为数据库结构中的表；每个实体类型实例都应INSERTed作为相应表中的一行。

    因此，我认为有必要声明，就像在服务器内创建数据库和表并不一定意味着此类数据库和表是关系的，将列标记为键并不一定意味着它们实际上是键。因此，由于键是数据的内在特征，它们的识别取决于建模者的能力，它们在服务器中的正确实现取决于建模者的正确声明。

    逻辑和物理

    如您所见，区分逻辑元素和物理元素非常重要。总而言之，逻辑（或抽象）组件直接取决于数据的含义；相反，物理（或具体）构造是一种“在后台”使用的机制，因此 DBMS 可以——例如——促进数据检索，支持数据库创建者所做的逻辑定义，或两者兼而有之。

    基表和派生表（或关系）

    使用 SQL 系统，您可以定义基表（通过 DDLCREATE TABLE语句）来塑造数据库的结构，但这还不是全部，因为一旦您需要检索结果集，您还可以获得多个派生表它组合来自不同表的列，例如，通过表示表的SELECT语句。JOINs您可以将所述派生表定义为VIEWs，并在必要时直接查询它们。这只是 SQL 平台提供的多功能性的一个很好的例子，因为您将始终使用相同类型的结构，即表（或关系）。

    当然，您也可以利用内置的服务器函数来进行不同类型的计算、创建计算列和连接列、获取统计信息并继续创建您在设计时甚至无法想象的查询。

    如果随着时间的推移，数据用户定义了新的感兴趣的上下文事物，您可以通过向数据库中添加新表来完美满足他们的需求，是的，您可以将先前存在的表与新表结合起来并产生全新的派生关系.

    如您所见，关系方法提供的可能性是巨大的。

    加入

    由于JOINs可能看起来有点麻烦，如果您遇到特定查询的问题，您可以来 DBA.SE寻求帮助。有大量的用户非常熟练和经验丰富，而且很可能不止一个人愿意提供他们宝贵的帮助。

    对此，应该说这种操作已经被多家SQL厂商在物理层面进行了高度优化。因此，在合适的条件下（即在精心设计的数据库中执行）JOINs绝对是快速的。

    冗余

    关系数据库存储有关现实世界事实的断言，并且确切的事实发生一次。所以从逻辑的角度来看，多次存储同一个事实是不合理和不必要的。

    冗余最终会导致不一致。例如，假设：

    • 有人在某个数据库中两次保留了相同的信息。
    • 后来，其他人来了，UPDATEs只出现了一次重复。因此，其他事件不再是最新的。
    • 随后，另一个人UPDATEs至今未修改的发生。以这种方式，两个副本在不同的时间点都经历了不同的变化。
    • 然后，当有人有兴趣检索相关信息时，他或她可以找到它的两个不同版本。

    所以：

    • 哪个版本可以被认为是正确、可靠的？
    • 哪一个准确地反映了现实世界？

    As you know, this phenomenon can even have legal implications, a situation that surely is of enormous importance.

    Furthermore, the time and effort that has to be employed to handle such inconsistencies (perhaps by some kind of update synchronization) should be better devoted to tasks that actually produce value for your organization. So, I recommend avoiding their storage by design and keeping the logical consistency of your database intact.

    Tables with big amounts of rows

    There are multiple database instances retaining billions of rows across numerous tables that serve their users at really high speeds, but this, again, is a result of a proper design made by qualified practitioners. So, the problem is not the amount of information stored, but the way in which said information is managed.

    Multiple applications working with the same database

    A relational database is meant to serve multiple application programs at the same time. So you can have, e.g, one or more web apps, one ore more desktop apps and one or more mobile apps, all working toghether with your database simultaneously.

    So —using programming jargon— one must make sure not to couple the database with the code of any of the apps; keep each software component separated from the others but, at the same time, connected.

    • 5

  3. 大部分同意马克的观点，但 Mongo 并没有那么糟糕。使用用户 ID 而不是名称，啁啾表会更好，然后使用正确的调用，您可以从用户集合/表中填充数据，这是在索引字段中的查找，在 Mongo 中非常快。

    索引本身不必担心，只要您保持它们平坦并且仅用于您需要它们的目的。我们的典型查询是一个集合并从三个不同的查询中填充数据，它非常快，并且通过 Node 的异步处理，当我们有很多点击时，它可能会花费服务器 5% 的 IO 性能。我从未见过 SQL 服务器运行得这么快。可能会进行很多调整和优化，但在这里你从一开始就免费获得它。

    而且您的表在大小方面很好，每个用户的所有平面数据都没有问题。即使这个星球上的每个人都创建了 10 个帐户，您也可能只会获得几千兆字节。

    最重要的是表之间的关系，所以一个人关注很多人，或者很多人喜欢一张，这些事情的时间线日志，等等。当你处理这些时，你很快就会遇到空间问题。另外，请始终将它们放在自己的收藏中，不要嵌入喜欢和关注者！迟早你会遇到奇怪的事情，即数据库中的文档不能超过 X 字节或其他任何内容。

    对于可能有 100000 个用户的较小网络，您当前的组合应该可以正常运行，一旦它变得更大，您可能会遇到性能问题。

    在更大规模的网络上，Node+Mongo 的运行速度将比 PHP+Mysql 快得多，因此您现在可能会花一些时间来把它弄好。但如果一切都已经在 MySQL 中完成，您也可以等待稍后的时间。重写表定义应该需要 1-2 天，或者如果您目前只有数据库，您甚至可以使用 Node 中的 MySQL。

    无论您决定什么，都不要在 Mongo 中使用关系数据作为嵌入字段，Mark 有一篇关于它的博客文章。只需将每个 MySQL 表放在一个自己的平面集合中，并使用适当的索引和节点框架中的填充机制，这就像 SQL 中的 LEFT JOIN。

    如果您还没有对所有视图进行编码，我还建议为所有调用创建一个小型 API，这样您就可以在此过程中更轻松地开发和更改或添加前端。

    如果您感到不安全，请始终咨询知道如何处理这些事情的人。甚至会花钱请人做第一步，让你跟上进度。这当然取决于项目，但如果你担心的话，你可以从不错的博客开始。

    以我的经验，尽早减少你拥有的东西比让人们稍后改变它更容易，所以真的建议花几天时间尝试让 node+mongo 运行起来。那里有很好的教程，实际上只是简单的 JavaScript 编码，包括 Mongo 和 Node。没什么好害怕的。

    你已经知道在哪里提问，所以你会做得很好！

    • 1

  4. 这取决于很多东西。您正在构建什么以及您的数据库查询有多复杂？爱好还是专业用途？数据很关键吗？您需要交易安全吗？您是否已经拥有所有数据，采用什么格式？还是你从头开始？你期望有多少用户？有多少请求？您是绑定 PHP 还是可以使用 Node.JS？它是网站还是应用程序？您是在构建 Api 还是使用直接数据库访问？

    旁注：在 mongo 中，您通常使用 ObjectId（如时间戳）而不是 auto_increment 作为主键，因此您不会从 1 开始。

    我不会混合数据库，使用其中一个，两者都能够处理大型数据集。如果怕性能问题，考虑多花点钱租一台功能强大的服务器，还是值得的。但通常数量适中的服务器很无聊，尤其是使用 SSD 时。

    在 MySQL 中 smallint 非常低，使用 bigint 并且不用担心额外的字节：http ://dev.mysql.com/doc/refman/5.7/en/integer-types.html

    • 0

  5. Looks like you have set up so that Mongo will win. The MySQL schema that you have sketched out has a number of inefficiencies. Please provide HOW CREATE TABLE profile.

    Does your PK have 10 columns? I hope not.

    Also, to help with the conversion to Mongo, please provide some of the typical queries.

    Normalizing every column is overkill and inefficient.

    Countries have a perfectly good 2-letter standard abbreviations that take just as much space as a SMALLINT. That is, there is no need to normalize them. And I generally prefer to put the entire "location" in another table, not because of "normalization", but so it can be one lookup instead of three.

    How much data do you have (or will you have)? Once the data is bigger than can be caches (in any database), performance suffers in various ways. Some are predictable; some are avoidable.

    "All tables are planned by not written" -- Are you referring to MySQL? Or Mongo? Or both? If you have no familiarity with either, then it will be a learning curve. Also, Mongo (I think) is lower level -- that is, you have to write more code to get the same effect.

    570b87a56931b8f21a8bf25c smells like some kind of hash? Beware. If the data becomes bigger than RAM, operations, even "simple" ones will become I/O-bound due to the randomness of hashes as IDs.

    You need to better understand indexing before embarking on any database activity. It is probably the most important aspect.

    A PRIMARY KEY (in MySQL) is a unique identifier of the rows. It should contain no more than is necessary for such. Adding extra columns is counter-productive.

    "ASAP" -- Plan on a complete rewrite in a couple of months. I do mean 'complete'. Rushing into a design and implementation without sufficient background will lead to a mess. But if you plan for a rewrite, you will be thinking in that direction, so it won't be so painful to throw away this prototype.

    • 0

  6. 用最坏的情况填充 SQL 表，一群快乐的用户！然后看看它在处理大量查询时的性能如何，然后尝试将数据复制到 Mongo 中。可能会帮助您确定何时最适合切换。我通常和现在一起去！;-)

    也不确定这些天 SQL 提供什么，但是在 Mongo 中，如果集合太满，您可以跨多个服务器进行分片，如果一台服务器无法处理所有查询，则复制集，......最好与您的医生或这样做的人交谈为了生计。

    • -3

相关问题