时间戳

首页 > 解决方案 > 数据库查询慢

mysql - 数据库查询慢

问题描述

由于我的网站数据库变得非常大，某些查询的性能变得很糟糕。某些查询的执行时间超过 30 秒。我想知道是否有人可以帮助我优化查询或就如何提高性能提出建议？我已经为所有外键和 ID 设置了索引。

SELECT p.*
     , u.unique_id
     , u.nick_name
     , u.avatar_thumb
     , t.desc as tag_desc
     , pt.post_id as tag_post_id 
  from tt_post_tags pt
  LEFT 
  JOIN tt_posts p
    ON p.id = pt.post_id
 RIGHT 
  JOIN tt_users u 
    ON p.user_id = u.user_id
  LEFT 
  JOIN tt_tags t
    ON t.name = "gameday"
 WHERE pt.name = "gameday"
 ORDER 
    BY create_date DESC
 LIMIT 100

上述查询需要 29 秒才能完成。如果我从查询中删除“create_date DESC”，它将在 0.3 秒内运行。我已经向 create_date 添加了一个索引，但查询运行仍然需要 30 秒。tt_posts 表包含大约 160 万条记录。

我的数据库有以下表格：Posts、Users、Tags 和 PostTags。

Posts 表包含用户表的外键。

标签表包含每个标签的唯一 ID 和名称

Post_tags 表包含来自标签表的外键以及标签所针对的帖子的外键。

如果不太容易理解，我明天可以附上一张图表。希望有人可以帮助我。提前致谢。

CREATE TABLE `tt_posts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `post_id` bigint(30) NOT NULL,
  `user_id` bigint(30) NOT NULL,
  `create_date` datetime NOT NULL,
  `cover` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `duration` int(10) DEFAULT NULL,
  `desc` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  UNIQUE KEY `post_id` (`post_id`),
  KEY `user_id` (`user_id`),
  KEY `create_date` (`create_date`)
) ENGINE=InnoDB AUTO_INCREMENT=4641550 DEFAULT CHARSET=utf8

解释选择

CREATE TABLE `tt_tags` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `tt_tag_id` BIGINT(30) NULL DEFAULT NULL,
    `name` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `name` (`name`),
    UNIQUE INDEX `tt_tag_id` (`tt_tag_id`),
    INDEX `tt_tag_id_key` (`tt_tag_id`),
    INDEX `name_key` (`name`)
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB

和

CREATE TABLE `tt_post_tags` (
    `post_id` INT(11) NOT NULL,
    `name` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    INDEX `post_id` (`post_id`),
    INDEX `name` (`name`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

和

CREATE TABLE `tt_users` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT(30) NOT NULL,
    `unique_id` VARCHAR(190) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `nick_name` VARCHAR(100) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `avatar` VARCHAR(190) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `signature` TEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
    PRIMARY KEY (`id`),
    UNIQUE INDEX `user_id` (`user_id`),
    UNIQUE INDEX `unique_id` (`unique_id`),
    INDEX `unique_id_index` (`unique_id`),
    INDEX `user_id_index` (`user_id`)
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB

标签： mysqlsql

解决方案

在我看来，您查询的主要问题是左右外连接的混合。老实说，你能正确阅读这个吗？

单独的第一个连接似乎很奇怪。您将帖子外部加入其帖子标签。但是没有帖子的帖子标签甚至可以存在吗？它指的是什么？（反过来会更有意义：也选择没有标签的帖子。）如果我在这里没记错的话，您的连接将呈现为纯粹的内部连接。在您的 where 子句中，您进一步将此结果限制为发布名为“gameday”的标签。

然后你右外加入用户。我们避免使用右外连接，因为它的可读性远低于左外连接，但是您可以选择所有用户，即使是那些没有“gameday”帖子标签的用户。

然后你离开外部加入所有“比赛日”标签。这看起来与其他表完全无关（即，您要么找到“gameday”标签，要么不找到）。但是在您的解释中，您说“Post_tags 表包含来自标签的外键”，所以我推测您的帖子标签表中没有 tag_id，但名称实际上是标签 ID（因此也是您的帖子标签中的外键桌子）。这又引出了一个问题：为什么没有相关标签的帖子标签还会存在？可能这是不可能的，所有这一切都归结为纯粹的内部连接。（我建议在这里使用 atag_id而不是两个表中的名称，只是为了便于阅读。列名name隐藏了外键关系。）

在您的查询中，您没有显示 post tags 表的任何信息，但我看到您 select pt.post_id as tag_post_id，这当然只是p.id as tag_post_id又一次。我想这是一个错字，你想显示pt.id as tag_post_id吗？

我了解您希望查看所有用户，但只对“游戏日”帖子标签感兴趣。这使得编写查询有点复杂。我可能只会选择用户并外部加入完整的帖子标签信息。

您create_date没有资格使用表格。我想它是帖子表中的一列？

这是我提出的查询：

select
  gdp.*,
  u.unique_id,
  u.nick_name,
  u.avatar_thumb
from tt_users u 
left join
(
  select
    p.*,
    t.desc as tag_desc,
    pt.id as tag_post_id
  from tt_tags t
  join tt_post_tags pt on pt.name = t.name
  join tt_posts p on p.id = pt.post_id
  where t.name = 'gameday'
) gdp on gdp.user_id = u.user_id
order by p.create_date desc;

我这边有很多猜测，所以这个查询可能仍然与您需要的有点不同。我不知道。

现在让我们看看访问了哪些表列，以便为查询提供良好的索引。让我们特别看一下我们收集所有帖子标签的子查询：

  1. 我们只想要“比赛日”标签。由于这似乎是 tt_tags 的主键，因此应该已经有一个唯一索引tt_tags(name)。
  2. 作为外键，还应该有一个索引tt_post_tags(name)。这很好，但是因为我们想继续加入post_id，所以在索引中也有这个是有益的：create unique index idx on tt_post_tags(name, post_id). 但是，由于这是表的自然键，因此该索引也应该已经存在以确保数据完整性。如果尚不存在，请尽快提供。
  3. 最后我们加入tt_posts它的主键（即应该有一个索引tt_posts(id)）。再说一遍：我们在这里无事可做。

您选择所有用户并选择所有“比赛日”标签。然后，您必须将所有找到的标签加入用户，这已经是一些工作了。您可以将其想象为首先对所有找到的标签进行user_id排序以便加入。然后，您想按发布日期对结果进行排序。这意味着 DBMS 必须再次对所有结果行进行排序。分拣需要时间；就是那样子。结果包含多少行？如果我们谈论要排序的数百万行，那么这可能仍然很慢。如果许多帖子标签是“比赛日”标签，那么即使是索引也可能无法帮助读取表格，而 DBMS 可能会改为进行完整的顺序表格读取。确保统计数据是最新的（https://dev.mysql.com/doc/refman/8.0/en/analyze-table.html）。

推荐阅读