mysql - 大型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 | 
   +---------------------+

其中下载将是一个序列化的 node_ids 数组以及相关的许可证 ID 和价格，如下所示(5 是 node_id 并且将是 node_ids 顶级数组中的索引):

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

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

非常感谢任何和所有帮助!

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

Bill Karwin 回答的后续问题:

不幸的是，时间戳将是存储在
int(11)，而不是日期时间(符合 Drupal 标准)。一世
假设这并没有真正改变任何优化
立场？

node_id/license_id/user_id(您对集群主键的想法)是
不保证是唯一的，因为用户可以根据需要在相同的许可证下多次下载相同的节点。这个
是我为每一行拥有唯一的 download_id 的主要原因......
有一个下载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 为“商业用途”下载了多少次？)。

SELECT COUNT(*) FROM all_downloads WHERE (node_id,license_id) = (123,456) 
AND timestamp > NOW() - INTERVAL 30 DAY

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

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

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

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

获取给定 node_id 的下载次数，而不考虑许可证(“商业用途”和“个人用途”的所有下载)。

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

同上。

获取满足给定价格标准(即价格 = 0，或价格 > 0)的给定用户下载的节点 ID(和相应的许可证 ID)。

SELECT node_id, license_id FROM all_downloads WHERE price = 0 AND user_id = 789;

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

至于你的其他问题:

不，定义没有主键约束的表不是一个好习惯。

不，将序列化数组存储在单列中不是一个好习惯。请参阅我对“Is storing a comma separated list in a database column really that bad?”问题的回答

timestamp ... doesn't really change anything from an optimization standpoint?

我更喜欢日期时间而不是时间戳，因为日期时间包含时区信息，而时间戳不包含。您始终可以使用 UNIX_TIMESTAMP() 函数将查询结果中的日期时间转换为 UNIX 时间戳整数。

would it be acceptable to make the primary key a cluster of download_id/node_id/license_id/user_id? Or will having the download_id as the first part of the compound key throw off its usefulness?

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

Do you think it still makes sense to have a downloads_counted table, or would that be considered redundant?

当然，将汇总结果存储在表格中是减少频繁计算经常需要的总数的常用方法。但要明智地这样做，因为要使这些总数与真实数据保持同步需要做一些工作。如果您需要经常阅读预先计算的总数，并且每次更新时需要多次阅读，那么好处会更大。确保您将汇总的总数视为不如实际下载数据的权威性，并计划在它们不同步时重新生成总数。

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

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

is this key redundant, though, if node_id is already the first part of the compound primary key?

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

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