MySQL优化可以解决吗？

Question

MySQL优化可以解决吗？

4

我有一个表格，保存着关于“交易”的数据。交易是已关闭或未关闭的，并在“is_closed”列中标记（当然，已被索引）。大约有10,000个“未关闭”行和10,000,000个“已关闭”行。每个“未关闭”行大约每秒更新一次（仅更新非索引字段），而一旦行被“关闭”，它将永远不会再次被更新（仅读取）。

我从不需要运行处理未关闭和已关闭行的选择，所以问题是：是否应该将表分为两个具有相同结构的表（未关闭和已关闭）？

单个表的缺点是每秒钟要在10,000,000行表上运行一次更新。而两个表的缺点是存在某种代码重复，并且在关闭“交易”时，需要将其从一个表中删除并添加到另一个表中。

- user502967

1

如果我理解正确，您有一个查询语句如下：UPDATE t1 SET c1 = 'ham', c2 = 'spam' WHERE is_closed = 0;这可能会使用索引，所以我不明白为什么会有问题。表似乎相对较小。是否有任何显示它有多“慢”的数字？或者 EXPLAIN 输出呢？ - geertjanvdk

行被设置为is_closed的频率有多高？更新索引是否成为瓶颈？MyISAM？InnoDB？ - Marcus Adams

5个回答

1

我认为你可以通过状态列分区表格，这样逻辑上就只有一个表格。列表分区在你的情况下似乎是合适的。如果需要的话，你还可以进一步对“关闭”分区进行子分区。

- a1ex07

1

如果关闭与开放的比率> = 1000（如您所述），那么最好制作两个不同的表格。

您可以通过使用多态来避免代码重复。您可以通过名称为AbstractDeal的抽象基类创建一个AbstractDeal，并扩展两个具体类OpenDeal和CloseDeal。您可以将这两个具体类分别映射到您的表格中。

我希望这会是一个更好的解决方案。

- Muhammad Haris Altaf

1

将它们分成两个表。我看不出有什么缺点：

两个表的缺点是会有某种程度的代码重复

那又怎样？你的目标是性能，而不是减少代码行数。

当“交易”关闭时，我需要将它们从一个表中删除并添加到另一个表中。

多写几行代码。一些UPDATE过程将被转换为INSERT/DELETE过程。

优点如下：

两个表格中都少了一个索引。
任何包含 open/closed 标志的复合索引将更窄。
更重要的是，高流量表中的所有索引都会小得多。

- ypercubeᵀᴹ

0

只要两个表在同一个表空间中，将表拆分或分区几乎没有任何好处——代码简洁性的优势强烈支持将其保留在一个表中。

InnoDB将进行行级锁定，因此您不会出现已关闭的交易阻塞未完成的交易的情况。

- Eugen Rieck

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Marcus Adams · Accepted Answer

像你这样的布尔（真和假，或1和0）列在某些情况下是可以的，但如果你发现自己正在为这样的列建立索引，那么你可能已经越过了界限。

如果值分布均匀（50%真和50%假），MySQL甚至不会使用索引，除非它是一个覆盖索引。通过二级索引查找每一行数据的成本很高，因为返回的数据集占比较大，所以MySQL将执行简单的表扫描。

在你的情况下，由于你正在查询更小的分布（1%假），MySQL实际上可能会利用索引。

然而，你必须想知道为什么要在索引中存储那么多根本没有被使用的true值，而它们只会减慢索引更新速度，浪费空间。

...修改后...

相反，考虑以另一张表格的形式将索引存储在外部。考虑添加一个名为open_deals的表格，其结构如下，其中deal_id是deals和open_deals的主键：

deal_id
----------
100
121
135

要获取您的未完成交易，请按照以下步骤操作：

SELECT deals.*
FROM open_deals
STRAIGHT_JOIN deals
  ON deals.deal_id = open_deals.deal_id

我们使用直接连接，因为我们总是知道我们将从左到右进行连接，并且我们可以节省MySQL的思考时间。

由于open_deals仅包含单个索引列，因此索引将充当覆盖索引。在配置正确、强大的服务器上，索引将存储在内存中，因此表格将非常快。

内部连接将类似于使用原始辅助索引，但不会有所有未使用值的开销。

为了获得最佳性能，请确保新值附加到open_deals表的末尾，或者换句话说，所有新值都应大于最后一个值，但您已经这样做了。

要将交易设置为打开状态，请将其附加到open_deals表中，要将其标记为关闭，请从open_deals表中删除id。

这里的优点是您无需在表格之间移动记录，也无需更新其他索引（在InnoDB的聚簇索引中更糟糕）。这里唯一需要更新的索引是open_deals表上相当小的索引。