mysql - 添加 FULLTEXT 索引 : didn't make much difference , 是否会索引旧数据？

Question

我有一个使用 like 条件的查询，它正在杀死我的服务器(这个查询是我在慢查询日志中经常得到的) 顺便说一句，我的表有大约 12 万行 - 请忽略语法错误

select * from `images` where `category` like `%,3,%` and ( `tags` like '%,tag1,%' or `tags` like '%,tag2,%' or `tags` like '%,tag3,%' or `tags` like '%,tag4,%')

我现在不想改变查询和数据库设计，所以我决定切换到 myisam 并对 tags 列使用全文索引。

之后服务器负载没有太大变化，mysql 仍然不时使用高达 90% 的 cpu(8 个诅咒中有 1 个)。

所以我想知道，这个全文索引...它会索引旧数据(在添加此索引之前)吗？因为它发生得非常快而且我的 table 有点大。

或者它只适用于新存储的数据？

Answer 1

现有数据已编入索引，但根据 Pyrce 的建议，使用 LIKE %[token]% 的查询无法利用全文索引。

像这样重写你的条件，这严格等同于你的初始查询(忽略 stopwords and the likes ):

WHERE MATCH(tags) AGAINST ('tag1 tag2 tag3 tag4' IN BOOLEAN MODE)

但是，您应该更专注于规范化您的结构。在字段中存储非标量值(例如逗号分隔值)违反了非常 first normal form .

新建image_tag表，与images建立1-N关系。常规索引将允许即时查询。

CREATE TABLE image_tags (
    image_id INT,
    tag VARCHAR(50),
    PRIMARY KEY (image_id, tag),
    FOREIGN KEY (image_id) REFERENCES images(id), -- replace with "images" table's actual primary key
    KEY(tag, image_id) -- this index may be superfluous, check EXPLAIN
);

SELECT images.*
FROM images
JOIN image_tags ON image_id = images.id
WHERE tag IN ('tag1', 'tag2', 'tag3', 'tag4');

重复 images.category。