mysql - 有什么我可以做的优化此mysql查询吗？

Question

我希望你们中的一些mysql专家能帮助我优化mysql搜索查询。。。
首先，一些背景：
我正在开发一个带有搜索功能的小练习mysql应用程序。
数据库中的每个练习可以属于任意数量的嵌套类别，并且每个练习还可以具有任意数量的与其关联的searchtag。
这是我的数据结构（为了可读性而简化）

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（没有全文搜索）。
练习、searchtags和类别的ID列已编入索引。
“exerciseSearchtags”和“exerciseCategories”是多对多联接表，分别表示练习和searchtags以及练习和类别之间的关系。exerciseID和searchtagID列都在exerciseSearchtags中编制了索引，exerciseID和categoryID列都在exerciseCategories中编制了索引。
下面是一些练习标题、类别标题和searchtag标题数据的示例。这三种类型的标题中都可以有多个单词。

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

如果您还提供了一些数据，特别是来自您每个表的一些示例关键字和示例titles，那么我可能能够提供更好的答案，这样我们就可以了解您试图实际匹配的内容。但我会尽力回答你所提供的。
首先让我用英语输入我认为你的查询将做什么，然后我将分解原因和解决方法。

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，那么您将扫描2600-5700万行。如果 E是100万， C或 S大约是1000，那么您将扫描超过1万亿行。所以不，这根本不能很好地扩展。
1）忽略子查询内部的左联接，因为这些查询上的WAS子句强制它们是正常联接。这对性能影响不大，但会混淆您的意图。
2）RLIKE（及其别名REGEXP）从不使用索引AFAIK，因此它们不会进行缩放。我只能在没有示例数据的情况下进行猜测，但我会说，如果您的搜索需要在单词边界上进行匹配，则需要对数据进行规范化。即使你的标题看起来像是要存储的自然字符串，搜索其中的一部分也意味着你真的把它们当作一个词的集合。因此，您要么使用mysql的全文搜索 capabilities要么将标题分成单独的表，每行存储一个单词。每个单词的一行显然会增加存储空间，但会使查询变得非常简单，因为您似乎只进行整个单词的匹配（而不是类似的单词、词根等）。
3）你的最后一个左连接是导致我公式中 E x C部分的原因，你将在每次练习中做相同的工作 C次。现在，不可否认，在大多数查询计划下，每个类别的子查询都将被缓存，因此实际上并不像我所建议的那样糟糕，但在每种情况下都不是这样，所以我给出了最坏的情况。即使您可以验证是否有适当的索引，并且查询优化器已避免所有这些额外的表扫描，您仍将返回大量冗余数据，因为您的结果如下所示：

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

因为每个练习行对于每个练习类别条目都是重复的，即使您没有从一个或多个练习类别返回任何内容（并且第一个子查询中的categories.ID实际上引用了该子查询中连接的类别，而不是外部查询中的类别）。
4）由于大多数搜索引擎都是使用分页返回结果的，所以我猜您只需要第一个X结果。如果在查询中添加一个限制X，或者更好的是限制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

按相关性描述排序
相关性大于0
限制$start，$results
我通常不建议有一个有条件的条款，但它不会比你更糟。。。或者像……等等。
这解决了我的问题1、3、4，但剩下2个问题。根据您的示例数据，我可以想象每个表最多只有几十个条目。在这种情况下，RLIKE的低效率可能还不够痛苦，不值得每行优化一个单词，但您确实询问了有关扩展的问题。只有完全相等（ title = ?）查询或以查询开头（ title LIKE 'foo%'）才能使用索引，如果要放大任何表中的行，索引是绝对必要的。不管使用什么正则表达式，RLIKE和REGEXP都不符合这些条件（而您的查询是类似“contains”的查询，这是最坏的情况）。（需要注意的是 title LIKE CONCAT(?, '%')不够好，因为mysql发现它必须计算一些东西，而忽略了它的索引。您需要在应用程序中添加“%”。）