发布于 由admin

MySQL编程进阶之：`LIKE ‘%keyword%’`的性能问题与全文索引的替代方案。

各位观众老爷们，晚上好！我是你们的老朋友，今天咱们来聊聊MySQL里一个既让人爱又让人恨的家伙：LIKE '%keyword%'。

开场白：爱恨交织的LIKE

LIKE '%keyword%'，这玩意儿在模糊搜索里简直是居家旅行必备，哪里需要模糊搜，哪里就有它的身影。你想想，用户在搜索框里随便敲几个字，你的系统就能嗖嗖嗖地把相关结果都找出来，用户体验简直不要太好。

但是，就像所有美好的事物一样，LIKE '%keyword%'也有它的阴暗面——性能。当你的数据量小的时候，它可能还能凑合用，但是当数据量蹭蹭蹭地往上涨，几百万、几千万甚至上亿的时候，LIKE '%keyword%'就会变成你的噩梦。你会发现，查询速度慢到让人怀疑人生，CPU占用率高到服务器风扇狂转。

所以，今天咱们就来扒一扒LIKE '%keyword%'的皮，看看它到底是怎么拖慢速度的，以及，更重要的是，有什么更好的办法来替代它。

LIKE '%keyword%'的性能瓶颈：全表扫描

要理解LIKE '%keyword%'的性能问题，首先要明白它背后的原理。当你使用LIKE '%keyword%'的时候，MySQL实际上会进行全表扫描。

啥叫全表扫描？简单来说，就是MySQL会一行一行地检查你的数据表，看看每一行是不是包含你搜索的关键词。这就像你在一堆书里找一本包含特定词语的书一样，你必须从第一本开始，一本一本翻，直到找到为止。

如果你的数据表只有几百行，那全表扫描可能还能接受。但是如果你的数据表有几百万行，甚至更多，那全表扫描就会变得非常耗时。因为MySQL需要读取每一行的数据，然后进行比较，这会消耗大量的CPU和IO资源。

更糟糕的是，LIKE '%keyword%'通常无法使用索引。索引就像书的目录一样，可以帮助你快速找到想要的书。但是LIKE '%keyword%'的查询条件是模糊的，MySQL无法利用索引来加速查询，只能乖乖地进行全表扫描。

代码示例：LIKE '%keyword%'的慢查询

为了更直观地感受LIKE '%keyword%'的性能问题，咱们来写一个简单的代码示例。

首先，创建一个包含大量数据的表：

CREATE TABLE `article` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `title` varchar(255) NOT NULL DEFAULT '' COMMENT '文章标题',
  `content` text NOT NULL COMMENT '文章内容',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章表';

然后，插入100万条数据：

-- 模拟插入数据的存储过程 (这里仅为伪代码，实际需要编写MySQL存储过程)
DELIMITER //
CREATE PROCEDURE insert_article(IN num INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= num DO
    INSERT INTO article (title, content) VALUES (CONCAT('文章标题', i), CONCAT('文章内容', REPEAT('测试', 100), i));
    SET i = i + 1;
  END WHILE;
END //
DELIMITER ;

CALL insert_article(1000000);

现在，执行一个LIKE '%keyword%'的查询：

SELECT * FROM article WHERE content LIKE '%测试12345%';

你会发现，这个查询需要花费很长的时间才能完成。这就是LIKE '%keyword%'的威力，它能让你的数据库瞬间瘫痪。

全文索引：救星来了！

既然LIKE '%keyword%'这么慢，那有没有什么更好的办法来替代它呢？答案是肯定的，那就是全文索引。

全文索引是一种特殊的索引，它可以帮助你快速地搜索文本数据。与LIKE '%keyword%'不同，全文索引不需要进行全表扫描，它可以利用索引来加速查询。

全文索引的原理：倒排索引

全文索引的原理是倒排索引。倒排索引是一种将文档中的词语映射到文档ID的数据结构。

举个例子，假设你有以下两个文档：

  • 文档1： "The quick brown fox jumps over the lazy dog"
  • 文档2： "The lazy cat sleeps on the mat"

那么，倒排索引就会是这样的：

  • The: 1, 2
  • quick: 1
  • brown: 1
  • fox: 1
  • jumps: 1
  • over: 1
  • lazy: 1, 2
  • dog: 1
  • cat: 2
  • sleeps: 2
  • on: 2
  • mat: 2

当你搜索"lazy"的时候，全文索引会直接找到包含"lazy"的文档ID：1和2，然后返回这两个文档。

使用全文索引的步骤

要在MySQL中使用全文索引，需要经过以下几个步骤：

  1. 创建全文索引

    在创建表的时候，可以指定哪些列需要创建全文索引：

    CREATE TABLE `article` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
 `title` varchar(255) NOT NULL DEFAULT '' COMMENT '文章标题',
 `content` text NOT NULL COMMENT '文章内容',
 `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
 PRIMARY KEY (`id`),
 FULLTEXT INDEX `idx_content` (`content`) -- 创建全文索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章表';

    或者，在已有的表上创建全文索引：

    ALTER TABLE `article` ADD FULLTEXT INDEX `idx_content` (`content`);

  2. 使用MATCH...AGAINST进行查询

    使用MATCH...AGAINST语法来进行全文搜索：

    SELECT * FROM article WHERE MATCH (content) AGAINST ('测试12345');

    MATCH (content)指定要搜索的列，AGAINST ('测试12345')指定要搜索的关键词。

全文索引的注意事项

  • FULLTEXT索引只能用于MyISAM和InnoDB存储引擎。MySQL 5.6 以后InnoDB存储引擎开始支持全文索引。
  • FULLTEXT索引只能用于CHAR、VARCHAR和TEXT类型的列。
  • 全文索引有最小长度限制，默认情况下，最小长度为4个字符。这意味着，如果你的关键词长度小于4个字符，全文索引可能无法正常工作。可以通过修改ft_min_word_len参数来调整最小长度。
  • 全文索引会忽略停用词。停用词是指那些在文本中频繁出现，但是没有实际意义的词语，比如"the"、"a"、"is"等等。MySQL有一个默认的停用词列表，你可以根据自己的需要修改这个列表。
  • 全文索引的搜索结果可以按照相关性排序。MATCH...AGAINST语法会返回一个相关性分数，你可以使用ORDER BY子句按照相关性分数进行排序。

代码示例：全文索引的快速查询

让我们再次使用之前的article表，这次我们使用全文索引来进行查询：

SELECT * FROM article WHERE MATCH (content) AGAINST ('测试12345');

你会发现，这个查询的速度比使用LIKE '%keyword%'快得多。这就是全文索引的威力，它可以让你在海量数据中快速地找到你想要的结果。

更高级的全文索引技巧

除了基本的全文搜索之外，MySQL还提供了一些更高级的全文索引技巧：

  • 布尔全文搜索

    布尔全文搜索允许你使用布尔运算符（比如+、-、>、<）来组合多个关键词。

    例如，要搜索包含"测试"但不包含"12345"的文章，可以使用以下查询：

    SELECT * FROM article WHERE MATCH (content) AGAINST ('+测试 -12345' IN BOOLEAN MODE);
    • +表示必须包含
    • -表示必须不包含
    • >表示提高相关性
    • <表示降低相关性

  • 查询扩展

    查询扩展允许你根据初始查询结果，自动扩展查询范围。

    例如，要搜索与"测试"相关的文章，可以使用以下查询：

    SELECT * FROM article WHERE MATCH (content) AGAINST ('测试' WITH QUERY EXPANSION);

    查询扩展会根据初始查询结果，找到一些与"测试"相关的词语，然后将这些词语添加到查询条件中，从而扩大查询范围。

其他替代方案：ngram 分词

除了全文索引，还有一些其他的替代方案可以用来解决LIKE '%keyword%'的性能问题。其中，比较常用的是 ngram 分词。

ngram 分词是一种将文本分割成固定长度的片段的技术。例如，对于字符串 "hello"，如果使用 2-gram 分词，就会得到以下片段：

  • he
  • el
  • ll
  • lo

然后，你可以将这些片段存储到数据库中，并建立索引。当你进行模糊搜索的时候，可以将搜索关键词也进行 ngram 分词，然后搜索包含这些片段的记录。

ngram 分词的优点是实现简单，适用于各种语言。缺点是索引体积较大，且搜索精度可能不高。

代码示例：ngram 分词

这里给出一个简单的 ngram 分词的示例（仅为演示，实际应用需要更完善的处理）：

  1. 创建辅助表:
CREATE TABLE `article_ngram` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `article_id` bigint(20) unsigned NOT NULL COMMENT '文章ID',
  `ngram` varchar(255) NOT NULL COMMENT 'ngram片段',
  PRIMARY KEY (`id`),
  INDEX `idx_article_id` (`article_id`),
  INDEX `idx_ngram` (`ngram`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文章ngram表';

  1. 插入数据 (需要编程实现): 将 article 表中的内容进行 ngram 分词，然后插入到 article_ngram 表中。 这里以2-gram为例。

  2. 查询:

-- 假设要搜索 '测试12'
SELECT DISTINCT a.id, a.title, a.content
FROM article a
JOIN article_ngram ng1 ON a.id = ng1.article_id AND ng1.ngram = '测试'
JOIN article_ngram ng2 ON a.id = ng2.article_id AND ng2.ngram = '试12';

这个查询会将 "测试12" 分成 "测试" 和 "试12" 两个片段，然后分别搜索包含这两个片段的文章。

总结：选择合适的方案

LIKE '%keyword%'虽然简单易用，但是在处理海量数据的时候，性能会变得非常糟糕。为了解决这个问题，我们可以使用全文索引、ngram 分词等替代方案。

选择哪种方案取决于你的具体需求：

方案 优点 缺点 适用场景
全文索引 搜索速度快，相关性排序，支持布尔搜索，查询扩展 只能用于特定存储引擎和数据类型，需要维护停用词列表 需要高性能模糊搜索，对搜索精度要求较高，数据量较大
ngram 分词 实现简单，适用于各种语言 索引体积较大，搜索精度可能不高 对性能要求不高，对搜索精度要求不高，数据量较小

希望今天的讲座能帮助你更好地理解LIKE '%keyword%'的性能问题，并选择合适的替代方案。记住，没有银弹，只有最合适的解决方案。

感谢各位的观看！下次再见！

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注