MySQL查询性能与优化

2022-03-08 00:00:00 mysql query-optimization

我有以下数据库结构：

CREATE TABLE `posts` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `title` varchar(255) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `tags` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 `seo` varchar(255) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `tags_table_one` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `post_id` int(11) NOT NULL,
 `tag_id` int(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `tags_table_three` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `post_id` int(11) NOT NULL,
 `tag_id` int(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `tags_table_two` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `post_id` int(11) NOT NULL,
 `tag_id` int(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

向此表添加数据后：

帖子(~500k行)
标记(~30k行)
TAG_TABLE_ONE(~5百万行)
TAG_TABLE_TWO(~700k行)
TAG_TABLE_TABLE(~800k行)

这是我尝试的查询，虽然有效，但速度很慢：

SELECT p.title
FROM   `tags_table_one` x
       JOIN `posts` p
         ON x.post_id = p.id
WHERE  `tag_id` IN ( 244, 229, 193, 93 )
GROUP  BY `post_id`
HAVING Count(*) = 4
       AND NOT EXISTS (SELECT 1
                       FROM   `tags_table_one`
                       WHERE  `post_id` = x.post_id
                              AND `tag_id` IN ( 92, 10, 234 ))

我的目标是根据标签(好标签和坏标签)搜索过滤。例如，假设我有AS好标签"244","229","193","93"和As坏标签"92","10","234"。我需要一个mysql查询来按指定标签过滤那些帖子。结果应该返回包含提到的所有好标签和不是提到的所有坏标签的所有帖子(因此它应该包含全部，而不只是一些)。问题是，好标签和坏标签可以从任何TAG_TABLE中选择，所以我认为它需要一个连接或类似的东西，而我没有将其添加到我的查询中，因为我不知道具体如何做。我以前尝试过，结果不好，可能是因为表中有很多记录(在某些情况下，查询花费了30-40秒，这对于执行来说太长了)。有几张唱片很管用，但我不需要。我需要一个优化的数据库/查询，以使其尽可能快。如果您有任何数据库/查询的示例，最好尝试一下。即使MySQL配置有任何更改或我很高兴听到的消息也是如此。

编辑：

查询解释：

编辑2：

我将所有整个数据移动到一个具有类型列的表中，现在我的表结构如下：

CREATE TABLE `tags_table_one` (
 `post_id` mediumint(8) unsigned NOT NULL,
 `tag_id` mediumint(8) unsigned NOT NULL,
 `type` tinyint(1) NOT NULL,
 PRIMARY KEY (`post_id`,`tag_id`,`type`),
 KEY `tag_id` (`tag_id`,`post_id`,`type`),
 KEY `type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

根据@RickJames解决方案，但现在我有以下查询：

SELECT posts.id AS id,
       posts.title
FROM   `tags_table_one`
       INNER JOIN posts
               ON tags_table_one.post_id = posts.id
WHERE  ( `tag_id` IN ( 15, 25, 16, 17,
                       234, 14, 9 )
         AND `type` = 1 )
       AND ( `tag_id` IN ( 81, 48, 56 )
             AND `type` = 2 )
       AND posts.active = '1'
GROUP  BY `post_id`
HAVING Count(*) = "7"

假设我有15, 25, 16, 17, 234, 14, 9和81, 48, 56标记15, 25, 16, 17, 234, 14, 9和type 2。我想返回所有的帖子，其中包含类型1和类型2提到的所有标签。如果有人可以向我展示一个使用NOT IN查询的例子，那将是很棒的，因为我也需要它。所以这是针对不好的标签的。

此时，该查询没有为我返回任何结果。仅当我从查询中删除AND ( tag_idIN ( 81, 48, 56 ) AND类型 = 2 )部分，但这不是我想要的部分，因为没有正确筛选。

编辑%3

我试图管理它，但只收到此无效查询：

SELECT p.id,
       p.title
FROM `posts` p
INNER JOIN tags_table_one t1 ON p.id=t1.post_id
INNER JOIN tags_table_one t2 ON p.id=t2.post_id
INNER JOIN tags_table_one t3 ON p.id=t3.post_id
WHERE p.active='1'
  AND t1.tag_id IN (15, 25, 16, 17, 234, 14, 9) AND t1.type = '1'
  AND t2.tag_id IN (81, 48, 56) AND t2.type = '2'
  AND t3.tag_id IN (47, 51, 355, 71) AND t3.type = '3'
GROUP BY p.id 
HAVING COUNT(t1.tag_id) = 7
AND
HAVING COUNT(t2.tag_id) = 3
AND
HAVING COUNT(t3.tag_id) = 4
ORDER BY p.id DESC

问题出在&q；计数&q；，如果我删除它可以工作，但它不过滤。

解决方案

CREATE TABLE `tags_table_one` (
 `post_id` int(11) NOT NULL,
 `tag_id` int(11) NOT NULL,
 PRIMARY KEY (post_id, tag_id),
 INDEX(tag_id, post_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

备注：

清除id。
综合指数双向，其中一项为主键。

更多讨论：http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

INDEX(post_id), INDEX(tag_id)会更快，但(如您所知)仍然相当慢。

tags可能需要INDEX(name)？

编辑后

SELECT  posts.id AS id, posts.title
    FROM  `tags_table_one`
    INNER JOIN  posts  ON tags_table_one.post_id = posts.id
    WHERE  `tag_id` IN ( 15, 25, 16, 17, 234, 14, 9 )
      AND  `type` = 1 
      AND  posts.active = '1'
    GROUP BY  `post_id`
    HAVING  Count(*) = "7" 

SELECT  posts.id AS id, posts.title
    FROM  `tags_table_one`
    INNER JOIN  posts  ON tags_table_one.post_id = posts.id
    WHERE  `tag_id` IN ( 81, 48, 56 )
      AND  `type` = 2 
      AND  posts.active = '1'
    GROUP BY  `post_id`
    HAVING  Count(*) = "3"

第一个Selects给出具有所有7个tag_id的帖子。其中第二个Selects给出具有第二组tag_id中的全部3个的帖子。

您想要哪一个？

A.所有10个标签的帖子
B.第一组全部7个的帖子，但第二组没有
C.第一组全部7个加上部分第二组的帖子

我们可以做JOIN或LEFT JOIN...IS NOT NULL得到(A)或(B)。(C)将需要更多的处理。

type从何而来？由于您在这两种情况下都是从tags_table_one中读取内容，因此我猜它不是<1/2/3&q；？

当您说AND t2.tag_id IN (19, 684) AND t2.type = 2时，表示您允许它具有19或684。但听起来您似乎希望它同时具有19和684。这将需要不同的SQL。

计划A：将JOIN+IN变为两个JOINs。

B计划：继续JOIN+IN，但使用HAVING count(*) = 2。但这会变得很混乱，因为您在同一查询中有几个这样的子句。

计划C：使用GROUP_CONCAT(tag_id ORDER BY tag_id) = "19,684"(数字按数字顺序)。这还有其他复杂性。

计划D：如果您总是需要所有给定的标记，则

WHERE t1.tag_id IN (15, 223) AND t1.type = 1
  AND t2.tag_id IN (19, 684) AND t2.type = 2
  AND t3.tag_id IN (5) AND t3.type = 4

加

HAVING COUNT(*) = 5

可能会生效。

我觉得D计划最有希望，试试看。

相关文章