mysql - 文本字段的查询优化

Question

我试图通过在代理列上设置索引来优化 mysql 查询，但我的索引似乎不起作用。这是我的表格:

CREATE TABLE `Test` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `country_id` INT(11) NULL DEFAULT NULL,
    `agent` TEXT NOT NULL COLLATE 'latin1_general_ci',
    PRIMARY KEY (`id`)
)
COLLATE='latin1_general_ci'
ENGINE=InnoDB

alter table test add index index_for_agent (agent(767));

explain select * from test cu WHERE cu.agent REGEXP 'bot|Spider|SiteExpl|crawler'

我怎样才能优化我的查询

Answer 1

如果agent接受一个值并且只有一个值，那么使用in:

select *
from test cu
WHERE cu.agent in ('bot', 'Spider', 'SiteExpl', 'crawler')

test(agent) 上的索引会非常有效。

如果代理采用多个值，则更改数据结构，这样您就有了另一个表，比如test_agent，每个值一行。然后以上将起作用。

如果代理有前导匹配，那么“bot”应该匹配“bottomless pit”，使用like和union all的多个语句:

select *
from test cu
where cu.agent like 'bot%'
union all
select *
from test cu
where cu.agent like 'spider%'
. . .

与字段上的索引一起。

如果您要查找完整的单词，请使用全文索引。请注意，您需要更改最小字长参数，因为默认值为 4，它不会索引“bot”。

如果您需要在字段中搜索随机字符串，那么我已经没有提高性能的想法了。