mysql - 如何处理 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 端元素，我会非常高兴。

Answer 1

如果由于某些原因您不能使用不同的数据库管理系统或在集群上进行分区来寻求解决方案，您仍然可以做三件主要的事情来从根本上提高您的性能(并且它们有效当然也与集群结合):

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

就是这样。仅当您对细节感兴趣时才阅读其余部分:)

还在读书吗？好吧，开始吧:MyISAM 是基石，因为它是迄今为止最快的引擎。与其使用常规 SQL 语句插入数据行，不如将它们批量放入一个文件中，insert that file定期(根据需要经常进行，但最好在应用程序允许的情况下进行)。这样你就可以每分钟插入一百万行。

接下来会限制你的是你的键/索引。当这些无法放入您的内存中时(因为它们太大了)，您将体验到插入和查询的巨大减速。这就是将数据拆分到多个表的原因，所有表都具有相同的模式。每个表都应该尽可能大，一次加载一个表时不会填满你的内存。确切的大小当然取决于你的机器和索引，但应该在 5 到 5000 万行/表之间。如果您简单地测量插入一大堆行所花费的时间，寻找它显着减慢的那一刻，您就会发现这一点。当您知道限制时，每次您的最后一个表接近该限制时，立即创建一个新表。

多表解决方案的结果是，当您需要一些数据时，您将不得不查询所有表而不是只查询一个表，这会稍微减慢您的查询速度(但如果您“仅“有十亿左右的行)。显然，这里也需要进行优化。如果有一些基本的东西可以用来分离数据(比如日期、客户或其他东西)，你可以使用一些结构化模式将它分成不同的表，这样你甚至不用查询表就可以知道某些类型的数据在哪里。使用该知识仅查询可能包含所请求数据等的表。

如果您需要更多调整，请选择 partitioning , 正如 Eineki 和 oedo 所建议的那样。

此外，您会知道所有这些都不是胡乱猜测:我目前正在对我们自己的数据进行一些类似这样的可扩展性测试，这种方法为我们创造了奇迹。我们每天设法插入数千万行，查询需要大约 100 毫秒。