mysql - 如何对 mySQL 全文表进行分段？

Question

我有一个包含全文的 mysql 表。每天它的大小将增加 30K 数据项。恐怕当数据大小增加到许多 GB 时，查询速度会降低。我的情况允许我将搜索限制为最近的一百万个数据项。

首先我尝试了 partitioning table , 但 mysql 不支持 fulltext和 partition同时。

http://dev.mysql.com/doc/refman/5.5/en/partitioning-limitations.html

有些 friend 建议我使用其他的dbms产品，比如sphinx , solr , 或 oracle .但出于预算原因，我宁愿使用 mySQL。 (原谅我的固执)。

那么，如何优化mysql全文表搜索呢？随着数据量的增加，如何保持可接受的查询速度？

1. 划分表格？ (然后使查询复杂化)

2. 只能通过id限制查询？ SELECT * FROM table WHERE (MATCH (some_field) AGAINST ('+search_words' IN BOOLEAN MODE)) AND id>last_id-1000000 AND id<last_id Order By date DESC

3. 另一个好建议？谢谢。

Answer 1

这里有一种方法可以解决您的问题。它不是 super 漂亮，但会非常高效。

创建您的表的两个副本。将第一个命名为 text_current，将第二个命名为 text_archive。

将新到达的数据行加载到 text_current 中。这样您的当前数据就可以用于搜索。

写两个存储过程:一个叫做 copy_text，从 text_current 中选择较旧的行，并将它们插入 text_archive，另一个叫做 something像从 text_current 中删除旧行的 purge_text。然后，从 mySql 作业运行这两个存储过程。

如果我是你，我会在每晚 03:00 运行一次 copy_text 作业，并复制前一天任何时间(午夜到午夜)到达的所有项目。

我会每周运行一次 purge_text 作业，并清除 text_current 中超过 35 天的所有内容。 (一天3万条记录，百万条记录需要33.3天)。

这种将数据从当前表迁移到存档表的方法有一些方便的功能。

1. 很安全——大多数记录都在当前和存档中表。
2. 它是确定性的——使用日期而不是 ID 可以让您选择要精确归档的项目。
3. 如果您需要，它会在未来提供缓慢但有效的“搜索文件”功能你的产品。
4. 它保持当前数据表的大小相对可行，同时允许您使用 mySql 的精细全文搜索功能。
5. 效率很高——迁移的艰苦工作每天一次完成，而不是通过在查询中处理 ID 号而不断进行。

下面是当 record_date 列是 unix 时间戳时如何按日期选择记录。如果您在任何给定的一天运行它，它将选择从前一天午夜开始的记录，直到但不包括当天午夜的记录。

...
WHERE text_current.record_date >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY))
  AND text_current.record_date < UNIX_TIMESTAMP(CURDATE())
...

在一天中的什么时间运行此查询并不重要，它总是从午夜运行到午夜。这就是 CURDATE() 的美妙之处——它意味着今天午夜。 (NOW() 的意思是，好吧，现在；它就像 CURDATE() 但也有时间。但是您不希望那样，因为您想在午夜进行归档- 到午夜。如果您使用 NOW()，您将在运行存储过程时遇到一些小的不可预测性。

(不要试图对时间范围使用 BETWEEN 运算符；它具有包容性，但您希望时间范围的结束时间不包含在内。)

同样，如果您想删除超过 35 天的所有内容，请执行此操作。

DELETE 
 FROM text_current
 WHERE text_current.record_date < UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 35 DAY))

看看进展如何？当您进行归档和清除时，您总是根据插入的日期选择记录，而不考虑插入的时间。这就是我所说的确定性。