如何处理MySQL中的大表？

Question

我有一个用于存储物品及其属性的数据库。属性数量是可扩展的，因此有一个连接表来存储与物品值相关联的每个属性。

CREATE TABLE `item_property` (
    `property_id` int(11) NOT NULL,
    `item_id` int(11) NOT NULL,
    `value` double NOT NULL,
    PRIMARY KEY  (`property_id`,`item_id`),
    KEY `item_id` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

这个数据库有两个目标：存储（首要优先级且需要非常快速，我希望在几秒钟内执行许多插入（数百次）），检索数据（使用item_id和property_id进行选择）（这是第二个优先级，它可以慢一些，但不要太慢，否则会破坏我的数据库使用）。

当前表格中已经拥有16亿条条目，并且简单的计数可能需要长达2分钟...插入速度不足以使用。

我正在使用Zend_Db来访问我的数据，并且真的很高兴您不建议我开发任何PHP端元素。

- AsTeR

---

这个问题并不是真正与PHP相关的，所以我移除了这个标签。 - jigfox

没问题 Jens，你是正确的。 - AsTeR

Answer 1

如果由于某些原因无法使用不同的数据库管理系统或在群集上进行分区来解决问题，仍然有三件主要的事情可以做，以根本性地提高性能（当然，它们也与群集结合使用）：

• 设置MyISAM存储引擎
• 使用"LOAD DATA INFILE filename INTO TABLE tablename"
• 将数据拆分到多个表中

这就是全部。只有在您对详细信息感兴趣时才阅读其余部分 :)

还在阅读吗？那么好吧，接下来就是：MyISAM是基石，因为它是迄今为止最快的引擎。您应该将数据行批处理到文件中，而不是使用常规SQL语句插入数据行，并定期（尽可能少，但应用程序允许的情况下最好）插入该文件。这样，您可以每分钟插入数百万行。

下一个限制你的是键/索引。当它们太大而无法放入内存时，您将在插入和查询时遇到巨大的减速。这就是为什么您需要将数据分成几个具有相同模式的表。每个表应尽可能大，但在一次加载时不要填满内存。确切的大小当然取决于您的机器和索引，但应该在5到50百万行/表之间。您可以通过简单地测量连续插入大量行所需的时间来找到此值，寻找明显减速的时刻。当您知道极限时，每当最后一个表接近该极限时，即动态创建新表。

多表解决方案的后果是，当您需要某些数据时，您必须查询所有表而不仅仅是单个表，这会使您的查询速度略微变慢（但如果您“只”有数十亿行，则不会太慢）。显然，在这里也有优化要做。如果有一些基本的东西可以用来分离数据（如日期、客户或其他东西），则可以使用一些结构化模式将其分成不同的表，即使不查询表格也可以知道某些类型的数据位于何处。利用这种知识仅查询可能包含所请求数据的表等。

如果您需要更多的调整，请使用partitioning，如Eineki和oedo所建议的那样。

此外，为了让您知道这些不是猜测：我目前正在对我们自己的数据进行类似的可扩展性测试，而这种方法对我们来说非常有效。我们每天成功插入数千万行数据，并且查询只需要 ~100 毫秒的时间。

- Jakob

---

摇滚乐，这似乎是最完整的！我不会尝试“load data infile”，我没有任何重写PHP代码的意愿，并且那将强制我这样做。我将尝试分区和更改引擎为MyISAM。 - AsTeR

从5.0升级到5.1后，我首次获得了性能提升。我首先删除了所有外键并使用了20个分区。获取所有属性的简单选择（测试1）：从0.7秒降至0.37秒。 所有项目的计数（测试2）从一分钟以上降至11秒。然后我测试了200个分区： 测试1：0.29秒 测试2：14.86秒最后，我使用了50个分区，改用MyIsam并删除了索引： 测试1：0.24秒 测试2：<0.01秒谢谢大家！ - AsTeR

Answer 2

哇，这是相当大的表格 :)

如果您需要快速存储，您可以将插入批处理并使用单个多个INSERT语句进行插入。但是这肯定需要额外的客户端（php）代码，抱歉！

INSERT INTO `table` (`col1`, `col2`) VALUES (1, 2), (3, 4), (5, 6)...

同时禁用任何你不需要的索引，因为索引会减慢插入命令的速度。

或者你可以考虑对表进行分区：linky

- chris

---

这个想法不错，但我太喜欢Zend_Db了，不想测试它。 - AsTeR

Answer 3

首先，不要使用InnoDb，因为你似乎并不需要它的主要特性，如锁定、事务等。所以请使用MyISAM，这将已经产生一些差异。 然后，如果仍然不够快，请进行一些索引操作，但你应该已经看到了根本性的差别。

- Marc

---

MyISAM在速度方面甚至可能比InnoDB更差。如果这些更新是同时进行的，MyISAM的表级锁定很可能会产生强烈的负面影响。 - bobince

Answer 4

看看Memcache在哪些地方可以应用。还要了解水平分区技术以使表格大小和索引更小。

- webbiedave

---

我已经使用了memcache，但它不符合我的需求。我没有任何需要缓存的东西。我长时间存储数据，然后再进行预处理检索。 - AsTeR

Answer 5

首先：一个有16亿条记录的表似乎有点太大了。我在一些非常繁重的系统上工作，即使是记录所有操作的日志表也不会在多年内变得如此庞大。因此，如果可能的话，请考虑是否可以找到更优化的存储方法。由于我不知道您的数据库结构，所以无法给出更多建议，但我相信肯定有很多优化的空间。16亿条记录实在是太大了。

关于性能的几点建议：

如果您不需要引用完整性检查（这不太可能），则可以切换到MyISAM存储引擎。它速度稍快，但缺少完整性检查和事务支持。

对于其他任何问题，需要更多信息才能给出建议。

- selfawaresoup

---

就像其他人在这里说的一样，我已经读到了MyISAM不会使这个更快，但我会尝试。 - AsTeR

顺便说一下，我没有使用任何innoDB功能。 - AsTeR

Answer 6

你考虑过对表进行分区的选项吗？

- Eineki

---

没有，我认为这可能是一个严重的优化点。 - AsTeR

Answer 7

记住的一件重要事情是，MySQL 的默认安装并未针对此类繁重工作进行配置。确保您已经为您的工作负载进行了调整。

- Quai