我可以做些什么来优化这个 mysql 查询吗？

Question

我希望你们中的一些 mysql 专家可以帮助我优化我的 mysql 搜索查询...

首先，一些背景：

我正在开发一个具有搜索功能的小型练习 mysql 应用程序。

数据库中的每个练习可以属于任意数量的嵌套类别，并且每个练习还可以具有任意数量的与其关联的搜索标签。

这是我的数据结构（为了可读性而简化）

TABLE exercises
  ID
  title

TABLE searchtags
  ID
  title

TABLE exerciseSearchtags
  exerciseID -> exercises.ID
  searchtagID -> searchtags.ID

TABLE categories
  ID
  parentID -> ID
  title

TABLE exerciseCategories
  exerciseID -> exercises.ID
  categoryID -> categories.ID

所有表都是InnoDB（没有全文搜索）。

练习、搜索标签和类别的 ID 列已建立索引。

“exerciseSearchtags”和“exerciseCategories”是多对多连接表，分别表示练习和搜索标签以及练习和类别之间的关系。运动 ID 和运动 ID 均是运动 ID。 searchtagID 列已在exerciseSearchtags 中建立索引，exerciseID 和categoryID 列已在exerciseCategories 中建立索引。

以下是练习标题、类别标题和搜索标签标题数据的一些示例。所有三种类型的标题中都可以包含多个单词。

Exercises
    (ID - title)
    1 - Concentric Shoulder Internal Rotation in Prone
    2 - Straight Leg Raise Dural Mobility (Sural)
    3 - Push-Ups 

 Categories
    (ID - title)
    1 - Flexion
    2 - Muscles of Mastication
    3 - Lumbar Plexus

 Searchtags
    (ID - title)
    1 - Active Range of Motion
    2 - Overhead Press
    3 - Impingement

现在，进行搜索查询：

搜索引擎接受任意数量的用户输入的关键字。

我想根据关键字/类别标题匹配、关键字/搜索标签标题匹配以及关键字/练习标题匹配的数量对搜索结果进行排名。

为了实现这一点，我使用以下动态生成的 SQL：

  SELECT 
   exercises.ID AS ID,
   exercises.title AS title, 
   (

    // for each keyword, the following 
    // 3 subqueries are generated

    (
     SELECT COUNT(1) 
     FROM categories 
     LEFT JOIN exerciseCategories 
     ON exerciseCategories.categoryID = categories.ID 
     WHERE categories.title RLIKE CONCAT('[[:<:]]',?) 
     AND exerciseCategories.exerciseID = exercises.ID
    ) + 

    (
     SELECT COUNT(1) 
     FROM searchtags 
     LEFT JOIN exerciseSearchtags 
     ON exerciseSearchtags.searchtagID = searchtags.ID 
     WHERE searchtags.title RLIKE CONCAT('[[:<:]]',?) 
     AND exerciseSearchtags.exerciseID = exercises.ID
    ) +

    (
     SELECT COUNT(1) 
     FROM exercises AS exercises2 
     WHERE exercises2.title RLIKE CONCAT('[[:<:]]',?) 
     AND exercises2.ID = exercises.ID
    )

    // end subqueries

    ) AS relevance

    FROM 
    exercises

    LEFT JOIN exerciseCategories
      ON exerciseCategories.exerciseID = exercises.ID 

    LEFT JOIN categories
     ON categories.ID = exerciseCategories.categoryID

    LEFT JOIN exerciseSearchtags
     ON exerciseSearchtags.exerciseID = exercises.ID 

    LEFT JOIN searchtags
     ON searchtags.ID = exerciseSearchtags.searchtagID

    WHERE

    // for each keyword, the following 
    // 3 conditions are generated

    categories.title RLIKE CONCAT('[[:<:]]',?) OR 
    exercises.title RLIKE CONCAT('[[:<:]]',?) OR 
    searchtags.title RLIKE CONCAT('[[:<:]]',?) 

    // end conditions

    GROUP BY 
     exercises.ID

    ORDER BY
     relevance DESC

    LIMIT 
       $start, $results 

所有这些都运行良好。它根据用户输入返回相关搜索结果。

但是，我担心我的解决方案可能无法很好地扩展。例如，如果用户输入七个关键字搜索字符串，这将导致在相关性计算中产生包含 21 个子查询的查询，如果表变大，这可能会开始减慢速度。

有人对如何优化上述内容有任何建议吗？有更好的方法来实现我想要的吗？我在上述内容中犯了任何明显的错误吗？

预先感谢您的帮助。

Answer 1

如果您还提供了一些数据，特别是每个表格中的一些示例关键字和示例 title，我也许能够提供更好的答案，以便我们了解您想要的内容实际上匹配上。但我会尝试用你所提供的内容来回答。

首先让我用英语输入我认为您的查询会执行的操作，然后我将详细说明原因和解决方法。

Perform a full table scan of all instances of `exercises`
  For each row in `exercises`
    Find all categories attached via exerciseCategories
      For each combination of exercise and category
        Perform a full table scan of all instances of exerciseCategories
          Look up corresponding category
            Perform RLIKE match on title
        Perform a full table scan of all instances of exerciseSearchtags      
          Look up corresponding searchtag
            Perform RLIKE match on title
        Join back to exercises table to re-lookup self
            Perform RLIKE match on title

假设您至少有一些合理的索引，则结果为 E x C x (C + S + 1) ，其中 E 是练习数量， C 是给定练习的平均类别数，S 是给定搜索标签的平均数。如果您至少没有列出的 ID 上的索引，那么它的性能会更差。因此，问题的一部分特别取决于 C 和 S 的相对大小，我目前只能猜测。如果 E 为 1000，C 和 S 各约为 2-3，那么您将扫描 8-21000 行。如果 E 为 100 万，C 为 2-3，S 为 10-15，则您将扫描 26-57 百万行。如果 E 为 100 万，而 C 或 S 约为 1000，那么您将扫描超过 1 万亿行。所以不，这根本无法很好地扩展。

1) 子查询内的 LEFT JOIN 被忽略，因为这些相同查询上的 WERE 子句强制它们成为普通 JOIN。这不会对性能产生太大影响，但它确实会混淆您的意图。

2) RLIKE（及其别名 REGEXP）永远不会使用索引 AFAIK，因此它们永远不会扩展。我只能在没有示例数据的情况下猜测，但我想说，如果您的搜索需要匹配单词边界，那么您需要规范化您的数据。即使您的标题看起来像是要存储的自然字符串，搜索其中的一部分也意味着您实际上将它们视为单词的集合。因此，您应该使用 mysql 的全文搜索功能 否则，您应该将标题分成单独的表，每行存储一个单词。每个单词一行显然会增加您的存储空间，但会使您的查询几乎微不足道，因为您似乎只进行整个单词匹配（而不是相似的单词、词根等）。

3) 您最后的左连接是导致我的公式中的 E x C 部分的原因，您将为每个练习执行相同的工作 C 次。现在，诚然，在大多数查询计划下，子查询将为每个类别进行缓存，因此实​​际上它并不像我建议的那么糟糕，但并非在所有情况下都是如此，所以我给您提供最坏的情况。即使您可以验证是否拥有正确的索引并且查询优化器已经避免了所有这些额外的表扫描，您仍然会返回大量冗余数据，因为您的结果将如下所示：

Exercise 1 info
Exercise 1 info
Exercise 1 info
Exercise 2 info
Exercise 2 info
Exercise 2 info
etc

因为每个练习行都是重复的每个exercisecategory条目，即使您没有从exercisecategory或类别中返回任何内容（并且第一个子查询中的categories.ID实际上引用了该子查询中加入的类别，而不是外部查询中的类别）。

4) 由于大多数搜索引擎使用分页返回结果，我猜你只需要前 X 个结果。在查询中添加 LIMIT X，或者更好的是 LIMIT Y, X，其中 Y 是当前页面，X 是每页返回的结果数，如果搜索关键字返回大量结果，将极大地帮助优化您的查询。

如果您可以向我们提供有关您的数据的更多信息，我可以更新我的答案以反映这一点。

更新

根据您的回复，这是我建议的查询。不幸的是，如果没有全文搜索或索引词，如果您的类别表或搜索标签表非常大，仍然会出现缩放问题。

 SELECT exercises.ID AS ID,
        exercises.title AS title,

        IF(exercises.title RLIKE CONCAT('[[:<:]]',?), 1, 0)
        +
        (SELECT COUNT(*)
           FROM categories
           JOIN exerciseCategories ON exerciseCategories.categoryID = categories.ID
          WHERE exerciseCategories.exerciseID = exercises.ID
            AND categories.title RLIKE CONCAT('[[:<:]]',?))
        +
        (SELECT COUNT(*)
           FROM searchtags
           JOIN exerciseSearchtags ON exerciseSearchtags.searchtagID = searchtags.ID
          WHERE exerciseSearchtags.exerciseID = exercises.ID
            AND searchtags.title RLIKE CONCAT('[[:<:]]',?))

   FROM exercises

按相关性 DESC 排序
具有相关性> 0
LIMIT $start, $results

我通常不会推荐 HAVING 子句，但它不会比你的 RLIKE ... OR RLIKE ... 等更糟糕。

这解决了我的问题 #1、#3、#4，但留下了问题#2 还剩下。鉴于您的示例数据，我想每个表最多只有几十个条目。在这种情况下，RLIKE 的低效率可能还不够痛苦，不值得每行一个单词的优化，但您确实询问了缩放问题。只有完全相等 (title = ?) 查询或开头查询 (title LIKE 'foo%') 才能使用索引，如果您要放大任何表中的行。无论使用什么正则表达式，RLIKE 和 REGEXP 都不符合这些条件（并且您的查询是“包含”之类的查询，这是最坏的情况）。 （重要的是要注意 title LIKE CONCAT(?, '%') 不够好，因为 mysql 认为它必须计算某些内容并忽略其索引。您需要在中添加 '%'您的申请。）

Answer 2

尝试运行查询的解释计划并查看当前不使用索引的行。有策略地为这些行添加索引。

另外，如果可能，请减少查询中 RLIKE 调用的数量，因为这些调用的成本很高。

考虑在数据库前面使用诸如 memcached 之类的东西来缓存结果以减少数据库负载。