我的问题是以下几点:
我有一个包含 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'));
我的数据库
- 不能同时使用 2 个索引(msgtimeutc 上的索引和全文索引上的索引)。所以我的查询结束在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'))
两个查询都解释计划只显示我使用了一个索引(全文)
所以我最终可能会按日期过滤编程行......但我更喜欢(关于卷)数据库解决方案
知道吗?
从最新的MySQL版本开始,没有办法将全文索引与另一个索引结合起来
http://dev.mysql.com/doc/refman/5.6/en/index-merge-optimization.html
索引合并不适用于全文索引。我们计划在未来的MySQL版本中扩展它以涵盖这些内容。
您可以尝试使用临时表解决问题:
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
然后将其与查询匹配
SELECT M.some_data
FROM my_search M
WHERE ( MATCH (M.BODY,M.SUBJECT) AGAINST ('test'));
请记住,对于此解决方案,实表中的全文索引变得完全无用。