主页 / dba / 问题 / 312517
Accepted
Blackbam
Asked: 2022-05-25 06:01:46 +0800 CST

在大型 MySQL 生产数据库中使用前导通配符进行 LIKE 查询

  • 772

根据 Amazon RDS 中的见解，大型 MySQL 生产数据库中的以下查询导致高负载（约 50.000.000 个条目）：

SELECT * FROM entities WHERE status='ready' AND user_id='81663729'  AND (primary_name LIKE '%mysearch%' OR additional_names LIKE '%mysearch%')  ORDER BY id DESC  LIMIT 0, 100000

负责它的列：

common_name: VARCHAR(255) Additional_names: VARCHAR(2000) （注意：不幸的是，这是一个逗号分隔的字符串，每个条目包含零到数百个名称，这是可怕的数据库设计）

这个查询表现不佳并不奇怪：前导通配符使索引或多或少不可用（primary_name被索引，additional_names不是）。一些用户在这个表中有很多条目（目前每个用户最多有 2.000.000 个条目，条目数量正在增长） - 所以查询中的 user_id 不会挽救它的性能。

我的问题是什么可能是解决这个问题的最佳方法。

方法一：MySQL FULLTEXT 索引

创建全文索引：

CREATE FULLTEXT INDEX domain ON entities (primary_name,additional_names);

新查询：

SELECT * FROM entities WHERE status='ready' AND user_id='81663729'  AND MATCH (primary_name,additional_names) AGAINST ('mysearch') IN NATURAL LANGUAGE MODE ORDER BY id DESC  LIMIT 0, 100000;

Pro：最容易迁移和实施？

反对：不确定这是否符合要求？创建该索引可能需要大量时间。

方法二：MySQL JSON

第 1 步：使用 JSON 添加附加列

ALTER TABLE entities ADD additional_names_j JSON AFTER additional_names;

第 2 步：所有新实体都作为 json 插入到新的 JSON 列以及旧列中。

第 3 步：可能会运行几天的脚本确保所有旧实体在 JSON 列中都有其条目。

第 4 步：调整应用程序以使用 JSON 列。

第 5 步：删除旧的附加名称列。

询问：SELECT * FROM entities WHERE status='ready' AND user_id='81663729' AND 'mysearch' member of ORDER BY id DESC LIMIT 0, 100000;

优点：搜索 JSON 会更好，并且可以在没有逗号爆炸之类的情况下提取名称。

反对：不确定是否可以对 JSON 列进行部分匹配？此外，努力是相当高的。

方法 3：创建关系表和名称表

这基本上意味着我们有一个带有 id 和 name 的表和 am:n 关系表。

优点：将是漂亮和干净的设计。

Contra：必须与正在运行的应用程序并行存在并填充，在这种情况下，它将导致应用程序逻辑发生巨大变化。

我向专家提出的问题是：哪些方法能最好地解决这种情况——为什么？

注意：该表以每秒约 5 个条目的速度增长，不允许停机。我愿意接受任何其他方法来使这个查询更快，我目前不知道！

mysql query-performance

1 个回答

  1. Best Answer

    方法 1：全文索引不能完成LIKE条件所能做的所有事情。我看到阻止使用全文的最常见问题是 MySQL 的实现只索引整个单词，所以如果您的搜索是部分单词，它将无济于事。

    创建索引需要时间，但如果避免停机很重要，您绝对应该熟悉使用pt-online-schema-change或gh-ost。这些都是免费工具，允许更改架构（包括添加索引），同时不会阻止客户端使用表。我们在上一份工作中使用 pt-osc 在高峰时段每周运行数百次模式更改。

    方法 2：单独添加 JSON 列将无济于事，无需为要搜索的表达式创建索引。你之前在 MySQL 中使用过 JSON 吗？在您决定之前，您应该对您使用的 MySQL 版本进行概念验证，看看它是否有效。在 MySQL 中使用 JSON 有很多限制和注意事项。我通常建议不要使用 JSON。

    进一步阅读：

    方法 3：这也称为倒排索引。它可以很好地工作，但是正如您所说，它需要一些工作来维护倒排索引，在添加数据时添加值。上面的评论建议使用触发器，它确实有效，但认为这也算作添加更多代码。

    这些方法中的哪一种能最好地解决这种情况？

    如果全文索引满足您需要支持的搜索需求，我更喜欢全文索引。优点是维护索引所需的代码最少，这是一个很大的优势。

    但每次我听到“哪个最好？”的问题时。我知道这是个错误的问题。没有适用于所有情况的“最佳”解决方案。您的项目有自己的要求，您需要选择适合这些要求的解决方案。任何解决方案都可能最适合一个人的应用程序，但不适用于另一个应用程序。

    • 1

相关问题