处理一个巨大的MYSQL表

Question

希望大家都过得很好。我们有一个名为“posts”的巨大mysql表格。它有大约70,000条记录，大小已经达到了10GB。

我的老板说，必须采取一些措施，使我们能够轻松处理这个巨大的表格，因为如果该表格损坏，那么恢复表格将需要很长时间。而且有时会很慢。

有哪些可能的解决方案，以便在所有方面都更容易地处理此表格？

表格的结构如下：

CREATE TABLE IF NOT EXISTS `posts` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `thread_id` int(11) unsigned NOT NULL,
  `content` longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `first_post` mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci,
  `publish` tinyint(1) NOT NULL,
  `deleted` tinyint(1) NOT NULL,
  `movedToWordPress` tinyint(1) NOT NULL,
  `image_src` varchar(500) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `video_src` varchar(500) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `video_image_src` varchar(500) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `thread_title` text CHARACTER SET utf8 COLLATE utf8_unicode_ci,
  `section_title` text CHARACTER SET utf8 COLLATE utf8_unicode_ci,
  `urlToPost` varchar(280) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `posts` int(11) DEFAULT NULL,
  `views` int(11) DEFAULT NULL,
  `forum_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `subject` varchar(150) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `visited` int(11) DEFAULT '0',
  `replicated` tinyint(4) DEFAULT '0',
  `createdOn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `urlToPost` (`urlToPost`,`forum_name`),
  KEY `thread_id` (`thread_id`),
  KEY `publish` (`publish`),
  KEY `createdOn` (`createdOn`),
  KEY `movedToWordPress` (`movedToWordPress`),
  KEY `deleted` (`deleted`),
  KEY `forum_name` (`forum_name`),
  KEY `subject` (`subject`),
  FULLTEXT KEY `first_post` (`first_post`,`thread_title`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=78773 ;

谢谢您。

更新

注意：虽然我非常感激回复，但几乎所有的答案都是关于优化当前数据库，而不是如何处理大型表的一般性问题。虽然我可以根据得到的回复来优化数据库，但这并不能回答关于处理庞大数据库的问题。现在我们正在谈论约70,000条记录，但在接下来的几个月甚至几周内，我们将会增长数倍。每条记录可能占用300kb的空间。

- Imran Omar Bukhsh

---

@Darhazer - 谢谢你的评论。但是，如果采用InnoDB会有哪些优点呢？我并不是很了解。而MyISAM有哪些缺点呢？为什么应该在MySQL之外处理全文搜索？ - Imran Omar Bukhsh

@Darhazer - 请查看我问题下面的更新部分。 - Imran Omar Bukhsh

@Imran：70K行不是一个巨大的表。有些人会说它很小。一行中有300KB闻起来像是糟糕的数据库设计。我也很想看看其他表的设计。 - ypercubeᵀᴹ

@ypercube：我们有一个爬虫引擎。我们将整个HTML页面存储在数据库中。这是占用最多空间的领域。 - Imran Omar Bukhsh

MyISAM使用表级锁，而InnoDB使用行级锁。如果有大量的插入/更新操作，这对性能会产生巨大影响。此外，MyISAM在写入密集型应用程序中会崩溃（它针对读取进行了优化）。我们使用InnoDB在MySQL中处理数百万条记录、数十GB的表。InnoDB具有许多功能，如事务，这些功能不受MyISAM支持。缺点是它不支持全文索引。将搜索移出数据库可以使数据库服务器免于此操作，这是一个通用的性能提示。 - Maxim Krizhanovsky

Answer 1

我的回答是对前两个评论的补充。

你已经为表的一半创建了索引。但如果你查看一些索引（如发布、删除、移动到WordPress），你会发现它们的值要么是1，要么是0，因此它们的选择性很低（行数除以该列不同值的数量）。这些索引只是浪费空间。

有些东西也没有意义。 tinyint(4) - 这并不能使它成为一个4位整数。那里的数字是显示长度。 tinyint 是1字节，因此它有256个可能的值。我猜这里出了些问题。

另外，仅有75k条记录的大小就达到了10 GB？你是如何测量大小的？还有，你使用的是什么硬件？

针对你更新的问题进行编辑：

有很多方法可以扩展数据库。我将链接一个SO问题/答案，以便您了解可以做什么：在这里。 另一件事是获得更好的硬件。通常，当数据库增加大小时变慢的原因是HDD子系统和可用内存剩余来处理数据集。您拥有的RAM越多-它就越快。

另一件你可以做的事情是将表格分成两个，一个表格保存文本数据，另一个表格保存与系统需要执行某些搜索或匹配相关的数据（在那里放置整数字段）。使用InnoDB，如果通过某种外键连接这两个表格，您将获得巨大的性能提升。由于InnoDB是这样的，主键查找速度很快，您打开了几个新的可能性来处理数据集。如果您的数据越来越大，您可以获得足够的RAM，InnoDB将尝试在RAM中缓冲数据集。有一个有趣的东西叫做 HandlerSocket，它在使用InnoDB并且具有足够RAM的服务器上执行一些神奇的操作。

最终，这真的取决于您需要做什么以及您如何做。由于您没有提到这一点，很难给出应该做什么的估计。我的优化第一步肯定是调整MySQL实例，并备份大表格。

- Michael J.V.

---

数据库文件可在lamp上获得。我可以直接访问它们。这就是我检查文件大小的方式。我正在使用普通的HP Pavilion dm4，i7 2.33mghz运行Ubuntu，4GB RAM。 - Imran Omar Bukhsh

你调整了MySQL的设置吗？ - Michael J.V.

@Michael J.V. - 请查看我问题下面的更新部分。 - Imran Omar Bukhsh

@Michael：+1。我会考虑将表拆分成两个以上的表——您建议的加上一个使用MyISAM引擎的表，以避免失去全文搜索功能。甚至可以将每个位字段（错误地实现为tinyint）拆分到单独的表中，以避免其中任何NULL值。所有这些都肯定可以更好地使用InnoDB引擎。 - ypercubeᵀᴹ

Answer 2

我想你需要修改一些列。

首先，您可以缩小一些varchar变量。

image_src/video_src/video_image_src VARCHAR(500)可能有点太多了。 （我认为100个字符足够了）

thread_title是文本，但应该是VARCHAR（200？）

同样适用于section_title

好的，这是您的问题content longtext

您真的需要在此处使用longtext吗？ longtext最多占用4GB的空间。 我认为，如果您将此列更改为text，它会变得更小。

    TINYTEXT    256 bytes    
    TEXT    65,535 bytes    ~64kb
    MEDIUMTEXT   16,777,215 bytes   ~16MB
    LONGTEXT    4,294,967,295 bytes ~4GB

编辑：我看到你使用了全文索引。我非常确定这节省了很多很多数据。你应该使用另一种机制来搜索全文。

- Michael Koper

---

此外，他的一些int(11)可以被缩减，例如Views、Posts、Visited等等...即使在S/O上，你看到过多少帖子有超过99,999次浏览，更不用说对一个问题的回答了... - DRapp

varchars并不占用全部500字节的内存，只占用存储内容和一些额外字节。我相信文本字段也是如此（尽管使用较大的字段类型存在一些内存问题，例如在预处理语句中）。然而，MyISAM在固定宽度列方面更快。至于将int缩小为short int - 我同意。 - Maxim Krizhanovsky

@Darhazer。你是对的，它不需要额外的硬盘空间。但它确实需要额外的RAM内存。因此，尽可能少地执行它总是更好的选择。来源：http://dev.mysql.com/doc/refman/5.0/en/char.html - Michael Koper

Answer 3

除了Michael的评论之外，慢速可能是基于查询优化程度和匹配适当索引的问题。我建议尝试找出一些比预期时间更长的有问题的查询，并在S/O上发布，看看是否有人可以帮助优化选项。

- DRapp