大型mysql表的规范化/优化结构

Question

我有一个拥有一群用户和一堆“节点”（内容）的网站。每个节点都可以被下载，除了所讨论的特定节点 ID 之外，每个下载都有一个与之关联的“许可证”（因此用户可以下载节点 5 用于“商业用途”或“个人用途”等），如以及每个许可证的价格。

我的目标是以这样的方式跟踪下载，使我能够：

• 获取给定时间段内给定节点 id 和许可证 id 的下载次数（上个月节点 5 在 ' 的时间内被下载了多少次）商业用途'？）。
• 获取给定节点 ID 和许可证 ID 的下载总数。
• 获取给定 node_id 的下载次数，无论许可证如何（“商业用途”和“个人用途”的所有下载组合）。
• 获取满足给定价格标准（即价格= 0，或价格> 0）的给定用户已下载的节点ID（以及相应的许可证ID）。

如果优化无关紧要，则存储的数据很简单，但我的问题是可能轻松增长到数百万行的表的规范化/优化之一。具体假设：

• 下载量为千万级。
• 节点数量达数十万。
• 用户数量达数万。

我对任何“真正的”mysql 工作都很陌生，所以我感谢你的帮助，并指出我愚蠢的地方。这是我到目前为止所得到的：

all_downloads 表

   +-------------+---------+------------+---------+-----------+-------+
   | download_id | node_id | license_id | user_id | timestamp | price |
   +-------------+---------+------------+---------+-----------+-------+

download_id 是该表的唯一键。该表是一个问题，因为它可能有数千万行。

downloads_counted 表

不是通过查询 all_downloads 表来累加给定节点和许可证的下载总数，而是在 cron 运行期间对下载进行计数，并且这些数字单独存储在 downloads_counted 表中

   +---------------------------------------------------------------------------+
   | node_id | license_id | downloads_total | downloads_month | downloads_week |  
   +---------------------------------------------------------------------------+

：许可证 ID 情况是新的（以前只有一个许可证，因此数据库中没有跟踪许可证），所以这就是我现在只是想弄清楚如何使用的情况。过去，node_id 是该表的唯一键。我假设我现在应该做的是将node_id 和license_id 组合成一个唯一的主键。或者是否可以将node_id 保留为该表的唯一键，并获取给定node_id 的所有行，然后在php 中解析结果（分离或合并每个特定许可证的下载）？拥有一个没有唯一键的表是否符合最佳实践？

无论如何，我认为这个表基本上没问题，因为它不应该增长到超过 1 或 200 万行。

返回给定用户的下载的问题

这是我需要帮助的主要领域。我考虑过将 user_id 作为 all_downloads 表中的键，然后简单地查询包含给定 user_id 的所有行。但我担心从长远来看查询这个表，因为它从一开始就会非常大，并且很容易增长到数千万行。

我考虑过创建一个看起来像这样的 user_downloads 表：

   +---------------------+
   | user_id | downloads | 
   +---------------------+

其中 downloads 将是一个由 node_ids 和关联的许可证 id 和价格组成的序列化数组，如下所示（5 是 node_id，并且是 node_ids 顶级数组中的索引） ：

downloads = array('5' = array(license = array('personal', 'commercial'), price = 25))

我意识到将数据数组存储在单个单元格中被认为是不好的做法，并且我不确定这是否会提高性能，因为对于给定用户来说，下载数组很容易增长到数千个。但是，我不确定如何创建另一个表结构，该结构在获取给定用户的下载方面比我的 all_downloads 表更有效。

非常感谢任何和所有的帮助！

======================================

比尔·卡尔文回答的后续问题：

• 不幸的是，时间戳将是存储在 int(11)，而不是日期时间（以符合 Drupal 标准）。我 假设优化并没有真正改变任何东西 不幸的是，

• node_id/license_id/user_id（您对集群主键的想法）是 不保证是唯一的，因为用户可以根据需要多次下载同一许可证下的同一节点。这 是我为每一行拥有唯一的 download_id 的主要原因...... 是否有特殊原因导致 download_id 会损害性能？或者将主键设为 download_id/node_id/license_id/user_id 的集群是否可以接受？或者将 download_id 作为复合键的第一部分会失去其用处吗？

• 您认为拥有 downloads_counted 表仍然有意义，还是会被认为是多余的？我的想法是，它仍然有助于提高性能，因为下载计数（下载总数、本周、本月等）将非常频繁地显示在网站上，并且 downloads_counted 表将行数比 all_downloads 表少一或两个数量级。

我对 downloads_counted 表的想法：

CREATE TABLE downloads_counted (   
 node_id          INT UNSIGNED NOT NULL,   
 license_id       INT UNSIGNED NOT NULL, 
 downloads_total  INT UNSIGNED NOT NULL,  
 downloads_month  INT UNSIGNED NOT NULL,   
 downloads_week   INT UNSIGNED NOT NULL,     
 downloads_day    INT UNSIGNED NOT NULL,  
 PRIMARY KEY (node_id, license_id), 
 KEY (node_id)
) ENGINE=InnoDB;

node_id 上的辅助键用于获取给定 node_id 的所有许可证的所有下载...但是，如果 node_id 已经是复合主键的第一部分，那么这个键是多余的吗？

Answer 1

以下是我设计该表的方式：

CREATE TABLE all_downloads (
  node_id    INT UNSIGNED NOT NULL,
  license_id INT UNSIGNED NOT NULL,
  user_id    INT UNSIGNED NOT NULL,
  timestamp  DATETIME NOT NULL,
  price      NUMERIC (9,2),
  PRIMARY KEY (node_id,license_id,user_id),
  KEY (price)
) ENGINE=InnoDB;

注意我省略了 download_id。

现在您可以运行所需的查询：

• 获取给定节点 ID 和许可证 ID 在给定时间内的下载次数时间段（上个月节点 5 被下载了多少次用于“商业用途”？）。

  从 all_downloads 中选择 COUNT(*) 个，其中 (node_id,license_id) = (123,456) 
  AND 时间戳 > NOW() - 间隔 30 天
  

  这应该充分利用聚集主索引，减少检查的行集，直到时间戳比较仅适用于一小部分。

• 获取给定节点 ID 和许可证 ID 的下载总数。

  SELECT COUNT(*) FROM all_downloads WHERE (node_id,license_id) = (123,456);
  

  与上面一样，这利用了聚集主索引。计数是通过索引扫描完成的。

• 获取给定node_id的下载次数，无论许可证如何（“商业用途”和“个人用途”的所有下载组合）。

  从 all_downloads 中选择 COUNT(*) 个，其中 (node_id) = (123);
  

  同上。

• 获取满足给定价格标准（即价格= 0，或价格> 0）的给定用户已下载的节点ID（以及相应的许可证ID）。

  从 all_downloads 中选择 node_id、license_id，其中价格 = 0 并且 user_id = 789；
  

  这减少了使用 price 上的二级索引检查的行数。然后，您可以利用 InnoDB 中的二级索引隐式包含主键列的事实，因此您甚至不需要读取基础数据。这称为覆盖索引或仅索引查询。

至于您的其他问题：

• 不，定义没有主键约束的表不是一个好的做法。

• 不，将序列化数组存储在单个列中不是一个好习惯。请参阅我对问题的回答“在数据库列中存储逗号分隔的列表真的那么糟糕吗？"

---

时间戳...从优化的角度来看并没有真正改变任何东西？

我更喜欢日期时间而不是时间戳，只是因为日期时间包含时区信息，而时间戳不包含。您始终可以使用 UNIX_TIMESTAMP() 函数。

将主键设为 download_id/node_id/license_id/user_id 的簇可以接受吗？或者将 download_id 作为复合键的第一部分会失去其用处吗？

聚集键的好处是行按照索引的顺序存储。因此，如果您经常根据node_id进行查询，那么将其放在复合聚集索引中会具有性能优势。即，如果您对给定 node_id 的行集感兴趣，那么将它们存储在一起是一个好处，因为您以这种方式定义了聚集索引。

您认为拥有 downloads_counted 表仍然有意义，还是会被认为是多余的？

当然，将汇总结果存储在表中是减少频繁计算经常需要的总数的工作的常见方法。但要谨慎行事，因为需要做一些工作才能使这些总数与真实数据保持同步。如果您需要经常读取预先计算的总计，并且每次更新时多次读取，那么好处会更大。确保将汇总总数视为不如真实下载数据那么权威，并制定计划在总数不同步时重新生成总数。

有些人还将这些聚合放入 memcached 键而不是表中，以便更快地查找。如果memcached中的易失性数据由于某种原因丢失，您可以从下载数据中重新填充它。

 PRIMARY KEY (node_id, license_id), 
 KEY (node_id)
) ENGINE=InnoDB;

如果node_id已经是复合主键的第一部分，那么这个键是多余的吗？

是的。 MySQL 允许您创建冗余索引，这是冗余索引的一个示例。任何可以使用 node_id 上的辅助键的查询都可以轻松地使用主键。事实上，在这种情况下，优化器将永远使用辅助键，因为它更喜欢主键的聚集索引。

您可以使用 pt-duplicate-key-checker 分析数据库中的冗余索引。