mysql - 使用全文索引和其他 where 条件进行高效查询

Question

我的问题如下:

我有一个包含 2300 万行(1 年数据)的邮件消息表。
下面是这张表的重要字段

• id (bigint)
• msgtimeutc (bigint)
• 主题(小文本)
• 正文(正文)
• 一些其他需要展示的数据

我有这张表的全文索引:
CREATE FULLTEXT INDEX mailmessage_fulltext ON mailmessage (body,subject)

我需要像这样在 msgtimeutc 上搜索正文和主题中的文本并按日期时间缩小请求:

SELECT M.some_data  
FROM mailmessage M 
WHERE M.MSGTIMEUTC >= 1343651965 AND M.MSGTIMEUTC <= 1344170365 
AND ( MATCH (M.BODY,M.SUBJECT) AGAINST ('test')); 

MySQL

• 不能同时使用 2 个索引(msgtimeutc 上的一个和 fulltextindex)。所以我的查询结束了对 msgtimeutc 的全表扫描
• 我不能在全文索引中包含 msgtimeutc，因为它是一个 bigint，我在上面或下面做的事情

所以查询花费的时间(和 I/O)太长了!

我想像这样做一个相交(示例代码未测试)

SELECT M1.some_data FROM mailmessage M1 
WHERE M1.MSGTIMEUTC >= 1343651965 AND M1.MSGTIMEUTC <= 1344170365 
INTERSECT
SELECT M2.some_data FROM mailmessage M2 
WHERE ( MATCH (M2.BODY,M2.SUBJECT) AGAINST ('test')); 

解释(对不起滚动条):

+----+-------------+-------+----------+---------------------------------+----------------------+---------+------+------+-------------+ 
| id | select_type | table | type     | possible_keys                   | key                  | key_len | ref  | rows | Extra       | 
+----+-------------+-------+----------+---------------------------------+----------------------+---------+------+------+-------------+ 
|  1 | SIMPLE      | M     | fulltext | msgtimeutc,mailmessage_fulltext | mailmessage_fulltext | 0       |      |    1 | Using where |
+----+-------------+-------+----------+---------------------------------+----------------------+---------+------+------+-------------+ 

但是mySQL上不存在

像下面2个这样的其他请求也在做全表扫描

SELECT M.some_data 
FROM mailmessage M 
WHERE 
M.id in ( 
   select m2.id from mailmessage m2 use index(mailmessage_fulltext)
   where (MATCH (m2.BODY,m2.SUBJECT) AGAINST ('test')) 
) 
AND M.MSGTIMEUTC >= 1343651965 AND M.MSGTIMEUTC <= 1344170365;

或

SELECT M1.ATTACHMENTCOUNT AS ATCH_COUNT 
FROM mailmessage AS M1 
INNER JOIN mailmessage AS M2 ON M1.id = M2.id 
WHERE (M1.MSGTIMEUTC >= 1343651965 AND M1.MSGTIMEUTC <= 1344170365) 
AND (MATCH (M2.BODY,M2.SUBJECT) AGAINST ('test'))

两个查询解释计划都显示我只使用了一个索引(全文)

所以我最终可能会按日期以编程方式过滤行......但我更喜欢(关于数量)数据库解决方案

有什么想法吗？

Answer 1

从最新的 MySQL 版本开始，无法将全文索引与其他索引相结合

http://dev.mysql.com/doc/refman/5.6/en/index-merge-optimization.html

Index Merge is not applicable to full-text indexes. We plan to extend it to cover these in a future MySQL release.

您可以尝试使用临时表解决您的问题:

CREATE TEMPORARY TABLE my_search 
  (FULLTEXT INDEX mailmessage_fulltext(body,subject))         
SELECT M.some_data  
FROM mailmessage M 
WHERE M.MSGTIMEUTC >= 1343651965 AND M.MSGTIMEUTC <= 1344170365

然后将其与query匹配

SELECT M.some_data  
FROM my_search M 
WHERE ( MATCH (M.BODY,M.SUBJECT) AGAINST ('test'));

请记住，对于此解决方案，真实表中的全文索引变得完全无用。