当在where中使用全文而不是order by时,带有子查询的mysql查询需要的时间要长得多

iklwldmw  于 2021-06-15  发布在  Mysql
关注(0)|答案(1)|浏览(232)

我有一个查询,它有时运行得非常快,有时运行得非常慢,这取决于查询中匹配全文布尔搜索的结果数。
查询还包含一个子查询。
没有子查询,主查询总是快速的。
子查询本身也总是很快的。
但他们在一起很慢。
从where子句中删除全文搜索,而改为按全文搜索排序,速度非常快。
所以当在where中使用全文搜索时,它的速度很慢。
下面是简单易读的概述,具体查询如下。
我已经在底部包含了这个模式,但是如果没有我的数据集很难复制,不幸的是我不能共享数据集。
我在示例查询中包含了计数和增量,以给出所涉及数据大小的一些指示。
实际上,我有一个解决方案,只需接受包含不相关数据的结果,然后在php中过滤掉这些数据。但是我想了解为什么我的查询执行得很差,以及如何在mysql中解决这个问题。
特别是,我搞不懂为什么全文搜索的顺序是“按”,而不是“在哪里”。

我想要的查询(慢)

我有一个类似这样的查询:

select 
  *, 
  MATCH (name) AGAINST ('Old Tra*' IN BOOLEAN MODE) AS relevance_score 
from 
  `app_records` 
where 
  `id` in (
    select 
      distinct(app_record_parents.record_id) 
    from 
      `app_group_records` 
      inner join `app_record_parents`
        on `app_record_parents`.`parent_id` = `app_group_records`.`record_id` 
    where 
      `group_id` = 3
  ) 
  and
    MATCH (name) AGAINST ('Old Tra*' IN BOOLEAN MODE)
order by 
  `relevance_score` desc 
limit 
  10;

此查询需要10秒钟。
这对于这种查询来说太长了,我需要看毫秒。
但是这两个查询单独运行时运行得非常快。

子对象将自行选择

select distinct(app_record_parents.record_id) 
from
   `app_group_records` 
   inner join
      `app_record_parents` 
      on `app_record_parents`.`parent_id` = `app_group_records`.`record_id` 
where
   `group_id` = 3

子选择本身需要7毫秒,有2600个结果。

不带子选择的主查询

select 
  *, 
  MATCH (name) AGAINST ('Old Tra*' IN BOOLEAN MODE) AS relevance_score 
from 
  `app_records` 
where 
  MATCH (name) AGAINST ('Old Tra*' IN BOOLEAN MODE)
order by 
  `relevance_score` desc 
limit 
  10;

没有子选择的主查询需要6毫秒,可能有2971个结果(显然有10个限制)。

速度更快,结果更少

同样的查询,但是匹配“oldtraf”而不是“oldtra”需要300毫秒。
当使用“老特拉夫”和“老特拉夫”时,结果的数目明显不同。

完整查询结果

“旧茶”:9
“老特拉福德”:2

与全文搜索匹配的记录

“旧茶”:2971
“老特拉福德”:120

删除where可以解决问题

删除where并返回按相关性分数排序的所有记录非常快,仍然给了我想要的体验:

select 
  *, 
  MATCH (name) AGAINST ('Old Tra*' IN BOOLEAN MODE) AS relevance_score 
from 
  `app_records` 
where 
  `id` in (
    select 
      distinct(app_record_parents.record_id) 
    from 
      `app_group_records` 
      inner join `app_record_parents`
        on `app_record_parents`.`parent_id` = `app_group_records`.`record_id` 
    where 
      `group_id` = 3
  )
order by 
  `relevance_score` desc 
limit 
  10;

但是我需要在代码中过滤掉不相关的结果

我在php中使用它,所以我现在可以过滤我的结果来删除任何相关性得分为0的结果(例如,如果只有2个匹配项,那么仍然会包含8个相关性得分为0的随机结果,因为我没有使用where)。

array_filter($results, function($result) {
    return $result->relevance_score > 0;
});

显然,这是非常快,所以这不是一个真正的问题。

但我还是不明白我的问题出在哪里。

因此,我有一个修复如上所述。但我还是不明白为什么我的查询速度慢。
很明显,从全文搜索中可能得到的结果的数量造成了一个问题,但究竟为什么以及如何绕过这个问题是我无法理解的。

表架构

这是我的table

CREATE TABLE `app_records` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `app_models_name_IDX` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=960004 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `app_record_parents` (
  `record_id` int(10) unsigned NOT NULL,
  `parent_id` int(10) unsigned DEFAULT NULL,
  KEY `app_record_parents_record_id_IDX` (`record_id`) USING BTREE,
  KEY `app_record_parents_parent_id_IDX` (`parent_id`) USING BTREE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `app_group_records` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `group_id` int(10) unsigned NOT NULL,
  `record_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

关于查询正在执行的操作的注解

子查询正在获取属于组\u id 3的记录\u id的列表。
所以,虽然app\u记录中有960004条记录,但只有2600条属于第3组,正是针对这2600条记录,我试图查询与“old tra”匹配的名称,
所以子查询得到了2600个记录id的列表,然后我做了一个 WHERE id IN <subquery> 从应用程序记录中获取相关结果。

编辑:使用联接同样慢

仅仅添加使用联接也有同样的问题。“old tra”需要10秒,“old traf”需要400毫秒,在where中不使用全文搜索时速度非常快。

SELECT 
  app_records.*, 
  MATCH (NAME) AGAINST ('Old Tra*' IN BOOLEAN MODE) AS relevance_score 
FROM 
  `app_records` 
  INNER JOIN app_record_parents ON app_records.id = app_record_parents.record_id 
  INNER JOIN app_group_records ON app_group_records.record_id = app_record_parents.parent_id 
WHERE 
  `group_id` = 3 
  AND MATCH (NAME) AGAINST ('Old Tra*' IN BOOLEAN MODE) 
GROUP BY 
  app_records.id 
LIMIT 
  10;
o4tp2gmn

o4tp2gmn1#

app_record_parents 没有 PRIMARY KEY ; 因此可能有不必要的重复对。
没有最佳索引。
请参阅以下几点提示。
也许 app_group_records 又多又多?
你在找什么 Old Tra* 任何地方 name ? 如果没有,那为什么不用呢 WHERE name LIKE 'Old Tra% . 在这种情况下,添加 INDEX(name) .
注意:什么时候 FULLTEXT 如果涉及,就先挑选。请提供 EXPLAIN SELECT 来证实这一点。
这个公式可能更快:

select  *,
        MATCH (r.name) AGAINST ('Old Tra*' IN BOOLEAN MODE) AS relevance_score
    from  `app_records` AS r
    WHERE MATCH (r.name) AGAINST ('Old Tra*' IN BOOLEAN MODE)
      AND EXISTS ( SELECT 1
              FROM app_group_records AS gr
              JOIN app_record_parents AS rp  ON rp.parent_id = gr.record_id
              WHERE gr.group_id = 3
                AND r.id = rp.record_id )
    ORDER BY relevance_score DESC
    LIMIT 10

索引:

gr:  (group_id, record_id)  -- in this order
r:   nothing but the FULLTEXT will be used
rp:  (record_id, parent_id)  -- in this order

相关问题