我有一个查询,它有时运行得非常快,有时运行得非常慢,这取决于查询中匹配全文布尔搜索的结果数。
查询还包含一个子查询。
没有子查询,主查询总是快速的。
子查询本身也总是很快的。
但他们在一起很慢。
从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;
1条答案
按热度按时间o4tp2gmn1#
app_record_parents
没有PRIMARY KEY
; 因此可能有不必要的重复对。没有最佳索引。
请参阅以下几点提示。
也许
app_group_records
又多又多?你在找什么
Old Tra*
任何地方name
? 如果没有,那为什么不用呢WHERE name LIKE 'Old Tra%
. 在这种情况下,添加INDEX(name)
.注意:什么时候
FULLTEXT
如果涉及,就先挑选。请提供EXPLAIN SELECT
来证实这一点。这个公式可能更快:
索引: