mysql - 选择包含所有指定值的 id(通配符冲突问题)MariaDB

Question

相关表 (10.1.29-MariaDB):

  [Table] => blog_search
  [Create Table] => CREATE TABLE `blog_search` (
  `bkey` varchar(28) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `blog_id` int(10) unsigned NOT NULL,
  `follows` int(10) unsigned NOT NULL,
  UNIQUE KEY `bkey` (`bkey`,`blog_id`),
  UNIQUE KEY `blog_id` (`blog_id`,`bkey`) USING BTREE,
  KEY `bkey_follows` (`bkey`,`follows`,`blog_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

bkey 是博客标题中使用的单词。 follows 数据将在每个博客的每个单词中重复。

我有一个在没有通配符的情况下运行良好的选择(由数据库选择的 key=>bkey_follow):

SELECT blog_id
FROM blog_search
WHERE bkey = 'news' OR bkey = 'usa'
GROUP BY blog_id
HAVING COUNT(*) > 1
ORDER BY FOLLOWS DESC
LIMIT 30

但是当我使用通配符时(由数据库选择的 key=>bkey):

WHERE bkey LIKE 'news%' OR bkey LIKE 'usa%'

它仍然能够进行索引搜索，但“新闻”和“新闻通讯”关键字将匹配为 COUNT(*) = 2，但它会尚未找到“美国””

我做了这个新的查询，它的工作原理就像我想要的那样，但是如果最后一个单词有很多结果，查询就会变得慢得多(因为查询是从后到前执行的，并且必须读取 100% 的索引) (key=>blog_id 由数据库选择):

SELECT blog_id
FROM blog_search
WHERE  
  blog_id IN (SELECT blog_id FROM blog_search WHERE bkey LIKE 'news%') 
  AND blog_id IN (SELECT blog_id FROM blog_search WHERE bkey LIKE 'usa%')
GROUP BY blog_id
ORDER BY follows DESC
LIMIT 30

如果我FORCE INDEX (bkey)或者更好的FORCE INDEX (bkey_follows)它会改善一点，比如35%，但仍然比我认为慢10倍应该是可以的。

我想要一个更优化的、没有子查询的查询。像这样的错误查询(但正确的版本):

SELECT blog_id, 0 AS dist1, 0 AS dist2
FROM blog_search
WHERE ( bkey LIKE 'news%' AND 1=dist1=1 )
   OR ( bkey LIKE 'usa%' AND 1=dist2=1 )
GROUP BY blog_id
HAVING (dist1=1 AND dist2=1)
ORDER BY follows DESC
LIMIT 30

其他数据:我正在开发对博客/帖子标题的搜索。我尝试了很多东西:正则表达式、LIKE %...% 和全文索引，我“批准”将其用于相关性排序结果。更有前途的搜索是本问题中讨论的搜索，因为它的速度最好，并且按“关注”对结果进行排序。

Answer 1

你不关心有多少行匹配。许多其他解决方案会导致“爆炸-内爆”综合症。这是您收集大量行的地方，然后通过 GROUP BY 归结为几行。通过使用“半连接”EXISTS，可以避免一半的情况。

SELECT DISTINCT blog_id
    FROM blog_search AS o
    WHERE WHERE bkey LIKE 'news%'
      AND EXISTS ( SELECT blog_id FROM blog_search
                      WHERE bkey LIKE 'usa%'
                        AND blog_id = o.blog_id )
ORDER BY follows DESC
LIMIT 30