MySQL如何优化%LIKE%查询？

Question

正在尝试优化MySQL查询。需要优化的原因是表的增长。目前它大约有300K行，将来会更多。

表结构

CREATE TABLE `activity_log` (
  `id` int(11) UNSIGNED NOT NULL,
  `date_created` datetime NOT NULL,
  `user_id` int(11) UNSIGNED NOT NULL,
  `event_id` smallint(6) UNSIGNED NOT NULL,
  `activity_by` tinyint(4) NOT NULL DEFAULT '0' COMMENT '''0'' - by client himself; ''-1'' - by admin; other - ap_user.id;',
  `text` text NOT NULL,
  `notes` text NOT NULL,
  `ip` char(46) DEFAULT NULL
) ENGINE=Aria DEFAULT CHARSET=utf8;

ALTER TABLE `activity_log`
  ADD PRIMARY KEY (`id`),
  ADD KEY `client_id` (`user_id`),
  ADD KEY `event_id` (`event_id`),
  ADD KEY `date_created` (`date_created`),
  ADD KEY `ip` (`ip`);
ALTER TABLE `activity_log` ADD FULLTEXT KEY `text` (`text`);

ALTER TABLE `activity_log`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2435463;
COMMIT;

查询需要优化：

SELECT a.id,a.activity_by,a.user_id,a.date_created,
            DATE_FORMAT(a.date_created,'%e %b, %Y') as date,
            DATE_FORMAT(a.date_created,'%H:%i') as time ,
            a.text,a.notes,e.color,e.link_icon,  u.id as user_id, u.login, r.role 
                  FROM `activity_log` a
                        LEFT JOIN `ap_user` u ON a.activity_by = u.id
                        LEFT JOIN `ap_role` r ON u.role_id = r.id,
                  `activity_log_events` e
                  WHERE 1
                  AND a.event_id=e.id
                  AND a.text LIKE '%test tester%'
         ORDER BY `date_created` DESC, `id` DESC

我已经尝试将%LIKE% query替换为FULLTEXT

SELECT a.id,a.activity_by,a.user_id,a.date_created,
        DATE_FORMAT(a.date_created,'%e %b, %Y') as date,
        DATE_FORMAT(a.date_created,'%H:%i') as time ,
        a.text,a.notes,e.color,e.link_icon,  u.id as user_id, u.login, r.role 
              FROM `activity_log` a
                    LEFT JOIN `ap_user` u ON a.activity_by = u.id
                    LEFT JOIN `ap_role` r ON u.role_id = r.id,
              `activity_log_events` e
              WHERE 1
              AND a.event_id=e.id
    AND MATCH (a.text) AGAINST ('test tester*' IN BOOLEAN MODE) ORDER BY `date_created` DESC, `id` DESC 

在结束时，如果此信息很重要，则此查询将具有DataProvider和分页的LIMIT 15近似值。

所以我添加了FULLTEXT index，并在这个表中放置了假数据，它有大约一百万行。

奇怪的是，like查询为100万行的表提供了1300个结果，而MATCH查询为同一个表提供了200_000。

首先，我认为这是因为表中包含相同的行，因为我复制了它们以进行测试。这是可能的吗?由于这个原因，FULLTEXT搜索非常慢，并且like搜索给出了不公平的结果- 1k而不是200k。这是一个特定表，无论如何都会包含许多类似的单词，如名称和标准短语，如“已添加”或“已删除”。

如何优化这个%like%查询？有没有可能没有FULLTEXT呢？如果没有-我到底在FULLTEXT查询方面做错了什么？

谢谢你的帮助。

Answer 1

ORDER BY是模棱两可的；我很惊讶它没有给您一个语法错误。有两个带有id的表。

有没有使用Aria而不是InnoDB的原因？

考虑更改为

('+test +tester*' IN BOOLEAN MODE)

还要考虑一下

('+"test tester*"' IN BOOLEAN MODE)

并且，要与LIKE进行复核

MATCH(...) AGAINST(...)
AND text LIKE "..."

最后一个建议处理一些情况，在这种情况下，全文将有效地找到“单词”，然后LIKE确保它们在一起。例如为了避免匹配"test or tester“(因为中间的单词)。或者“詹姆斯·史密斯和鲍勃·多伊尔”