php - mysql:使用 SET 还是很多列？

Question

我正在使用 PHP 和 MySQL。我有记录:

• 具有各种分层“事件类型”的事件(事件可以有多个类别和子类别，但此类类别和子类别的数量是固定的)(时间戳)

布置餐 table 的最佳方式是什么？我是否应该有一堆列(大约 30 个)，其中包含用于表示是或否的枚举以指示该类别的成员资格？还是应该使用 MySQL SET 数据类型？ http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html

基本上我考虑的是性能，我希望能够检索给定类别的所有事件 ID。只是寻找一些关于最有效方法的见解。

Answer 1

听起来您主要关心的是性能。

几个人建议拆分成 3 个表(类别表加上简单的交叉引用表或更复杂的树层次结构建模方法，如嵌套集或物化路径)，这是我想到的第一件事阅读您的问题。

对于索引，像这样的完全规范化的方法(添加两个 JOIN)仍然具有“相当不错”的读取性能。一个问题是对事件的插入或更新现在可能还包括一个或多个插入/更新/删除到交叉引用表，这在 MyISAM 上意味着交叉引用表被锁定，而在 InnoDB 上意味着行被锁定，因此，如果您的数据库忙于处理大量写入，那么与仅锁定事件行相比，您将遇到更大的争用问题。

就个人而言，我会在优化之前尝试这种完全规范化的方法。但是，我假设您知道自己在做什么，您的假设是正确的(类别永远不会改变)并且您有一个使用模式(大量写入)需要一个不太规范化的扁平结构。这完全没问题，并且是 NoSQL 的一部分。

SET 与“很多列”

因此，关于您的实际问题“SET vs. lots of columns”，我可以说我曾与两家拥有聪明工程师的公司合作(他们的产品是 CRM 网络应用程序……其中一家实际上是事件管理)，他们都对这种静态集合数据使用了“很多列”的方法。

我的建议是考虑您将在此表上执行的所有查询(按频率加权)以及索引的工作方式。

首先，使用“大量列”方法，您将需要为这些列中的每一列建立索引，以便您可以执行 SELECT FROM events WHERE CategoryX = TRUE。使用索引，这是一个超快速的查询。

与 SET 相比，您必须使用按位 AND (&)、LIKE 或 FIND_IN_SET() 来执行此查询。这意味着查询不能使用索引并且必须对所有行进行线性搜索(您可以使用 EXPLAIN 来验证这一点)。查询慢!

这就是 SET 不是一个好主意的主要原因——它的索引仅在您按确切的类别组进行选择时才有用。如果您要按事件选择类别，则 SET 非常有用，但反之则不然。

不太规范化的“很多列”方法(相对于完全规范化)的主要问题是它无法扩展。如果您有 5 个类别并且它们从不更改，那很好，但是如果您有 500 个类别并且正在更改它们，那就是一个大问题。在您的场景中，大约有 30 个永远不会改变，主要问题是每列都有一个索引，因此如果您频繁写入，由于必须更新的索引数量，这些查询会变慢。如果您选择这种方法，您可能需要检查 MySQL 慢速查询日志，以确保没有因为一天中繁忙时间的争用而导致异常的慢速查询。

在您的情况下，如果您的是典型的阅读量大的 Web 应用程序，我认为采用“大量列”方法(出于同样的原因，这两个 CRM 产品所做的)可能是明智的。对于该 SELECT 查询，它绝对比 SET 快。

TL;DR 不要使用 SET，因为“按类别选择事件”查询会很慢。