MySQL如何优化%LIKE%查询?

Question

尝试优化MySQL查询。需要优化的原因是表的增长。现在它有大约30万行，将来会更多。

表结构

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

我已经尝试用FULLTEXT

替换%LIKE%查询

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 

如果这个信息很重要，那么这个查询将有它的LIMIT 15近似于DataProvider和分页。

所以我添加了FULLTEXT index并将假数据放入这个表中，它有~ 100万行。

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

首先认为这是因为表包含相等的行，因为我复制了它们进行测试。这可能吗?FULLTEXT搜索非常慢，因为like搜索给出了不公平的结果——1k而不是200k。这是一个特殊的表格，无论如何都会包含许多类似的单词，如名称和标准短语，如"已添加"one_answers";或"已删除">

如何优化这个%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 "..."

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