优化 30 000+ 表上的 LEFT JOIN行

发布于 2024-09-10 00:29:48 字数 1594 浏览 4 评论 0原文

我有一个网站,访问者可以发表评论。我想添加回答评论的功能(即嵌套评论)。

起初,这个查询速度很快,但在我用现有评论(大约 30000 条)填充表后,一个简单的查询如下:

SELECT c.id, c2.id
  FROM (SELECT id
         FROM swb_comments
         WHERE pageId = 1411
         ORDER BY id DESC
         LIMIT 10) AS c
  LEFT JOIN swb_comments AS c2 ON c.id = c2.parentId

花费了 2 秒多的时间,没有 childComments(!)。

如何优化这样的查询?可能的解决方案是 http://www.ferdychristant.com/blog//articles /DOMM-7QJPM7 (滚动到“扁平表模型做得正确”)但这使得分页相当困难(如何限制 1 个查询中的 10 个父评论?)

该表有 3 个索引,id、pageId 和父 ID。

提前致谢!

编辑:

添加表定义。这是完整的定义,与上面的 SELECT 查询有一些差异(即 pageId 而不是 numberId 以避免混淆)

CREATE TABLE `swb_comments` (
    `id` mediumint(9) NOT NULL auto_increment,
    `userId` mediumint(9) unsigned NOT NULL default '0',
    `numberId` mediumint(9) unsigned default NULL,
    `orgId` mediumint(9) unsigned default NULL,
    `author` varchar(100) default NULL,
    `email` varchar(255) NOT NULL,
    `message` text NOT NULL,
    `IP` varchar(40) NOT NULL,
    `timestamp` varchar(25) NOT NULL,
    `editedTimestamp` varchar(25) default NULL COMMENT 'last edited timestamp',
    `status` varchar(20) NOT NULL default 'publish',
    `parentId` mediumint(9) unsigned NOT NULL default '0',
    `locale` varchar(10) NOT NULL,
    PRIMARY KEY  (`id`),
    KEY `userId` (`userId`),
    KEY `numberId` (`numberId`),
    KEY `orgId` (`orgId`),
    KEY `parentId` (`parentId`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=34748 ;

I have a website where visitors can leave comments. I want to add the ability to answer comments (i.e. nested comments).

At first this query was fast but after I populated the table with the existing comments (about 30000) a simple query like:

SELECT c.id, c2.id
  FROM (SELECT id
         FROM swb_comments
         WHERE pageId = 1411
         ORDER BY id DESC
         LIMIT 10) AS c
  LEFT JOIN swb_comments AS c2 ON c.id = c2.parentId

took over 2 seconds, with no childComments(!).

How do I optimize a query like this? On possible solution would be http://www.ferdychristant.com/blog//articles/DOMM-7QJPM7 (scroll to "The Flat Table Model done right") but this makes pagination rather difficult (how do I limit to 10 parent comments within 1 query?)

The table has 3 indexes, id, pageId and ParentId.

Thanks in advance!

EDIT:

Table definition added. This is the full definition with some differences to the above SELECT query, (i.e. pageId instead of numberId to avoid confussion)

CREATE TABLE `swb_comments` (
    `id` mediumint(9) NOT NULL auto_increment,
    `userId` mediumint(9) unsigned NOT NULL default '0',
    `numberId` mediumint(9) unsigned default NULL,
    `orgId` mediumint(9) unsigned default NULL,
    `author` varchar(100) default NULL,
    `email` varchar(255) NOT NULL,
    `message` text NOT NULL,
    `IP` varchar(40) NOT NULL,
    `timestamp` varchar(25) NOT NULL,
    `editedTimestamp` varchar(25) default NULL COMMENT 'last edited timestamp',
    `status` varchar(20) NOT NULL default 'publish',
    `parentId` mediumint(9) unsigned NOT NULL default '0',
    `locale` varchar(10) NOT NULL,
    PRIMARY KEY  (`id`),
    KEY `userId` (`userId`),
    KEY `numberId` (`numberId`),
    KEY `orgId` (`orgId`),
    KEY `parentId` (`parentId`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=34748 ;

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

安静 2024-09-17 00:29:48

问题是,如果 MySQL 需要处理派生查询的结果,则它无法应用索引(这就是 possible_keys 列中为 NULL 的原因)。因此,我建议过滤掉您需要的十条评论:

SELECT * FROM swb_comments WHERE pageId = 1411 ORDER BY id DESC LIMIT 10

然后发送单独的请求以获取每个评论 ID 的答案:

SELECT * FROM swb_comments WHERE parentId IN ($commentId1, $commentId2, ..., $commentId10)

在这种情况下,数据库引擎将能够有效地应用 pageId 和 ParentId 索引。

The issue is that MySQL cannot apply index if it need to deal with a result from a derived query (that's why you have NULL in the possible_keys column). So I suggest to filter out ten comments that you need:

SELECT * FROM swb_comments WHERE pageId = 1411 ORDER BY id DESC LIMIT 10

And after that send separate request to get answers for each comment id:

SELECT * FROM swb_comments WHERE parentId IN ($commentId1, $commentId2, ..., $commentId10)

In this case database engine will be able to apply pageId and parentId indexes efficiently.

椒妓 2024-09-17 00:29:48

如果 Fedorenko 先生是正确的,并且子查询导致了优化器的困难,您能不能尝试...

SELECT c.id, c2.id
    FROM swb_comments c LEFT JOIN swb_comments c2 ON c.id = c2.parentID
    WHERE c.pageId = 1411
    ORDER BY c.id DESC
    LIMIT 10;

看看是否有任何改进?

后来 - 我使用您的定义创建了一个表,用 30,000 个骨架行填充它,并尝试了这两个查询。他们都在很短的时间内完成了,以至于没有人注意到。解释计划就在这里......

mysql> EXPLAIN SELECT c.id, c2.id
               FROM swb_comments c LEFT JOIN swb_comments c2 ON c.id = c2.parentID
               WHERE c.numberId = 1411     ORDER BY c.id DESC     LIMIT 10;
+----+-------------+-------+------+---------------+----------+---------+------------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref        | rows | Extra                       |
+----+-------------+-------+------+---------------+----------+---------+------------+------+-----------------------------+
|  1 | SIMPLE      | c     | ref  | numberId      | numberId | 4       | const      |    1 | Using where; Using filesort |
|  1 | SIMPLE      | c2    | ref  | parentId      | parentId | 3       | books.c.id |   14 |                             |
+----+-------------+-------+------+---------------+----------+---------+------------+------+-----------------------------+

mysql> EXPLAIN SELECT c.id, c2.id
                   FROM swb_comments c LEFT JOIN swb_comments c2 ON c.id = c2.parentID
                   WHERE c.numberId = 1411     ORDER BY c.id DESC     LIMIT 10;
+----+-------------+-------+------+---------------+----------+---------+------------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref        | rows | Extra                       |
+----+-------------+-------+------+---------------+----------+---------+------------+------+-----------------------------+
|  1 | SIMPLE      | c     | ref  | numberId      | numberId | 4       | const      |    1 | Using where; Using filesort |
|  1 | SIMPLE      | c2    | ref  | parentId      | parentId | 3       | books.c.id |   14 |                             |
+----+-------------+-------+------+---------------+----------+---------+------------+------+-----------------------------+

并且正是我所期望的。

这是非常神秘的。

我会再考虑一下,看看是否还有其他可以尝试的方法。

If Mr Fedorenko is correct and the subquery is causing the optimiser difficulties, could you not try...

SELECT c.id, c2.id
    FROM swb_comments c LEFT JOIN swb_comments c2 ON c.id = c2.parentID
    WHERE c.pageId = 1411
    ORDER BY c.id DESC
    LIMIT 10;

and see if it's any improvement?

Later - I have created a table using your definition, filled it in with 30,000 skeletal rows, and tried both the queries. They both complete in too short a time to notice. The explain plans are here...

mysql> EXPLAIN SELECT c.id, c2.id
               FROM swb_comments c LEFT JOIN swb_comments c2 ON c.id = c2.parentID
               WHERE c.numberId = 1411     ORDER BY c.id DESC     LIMIT 10;
+----+-------------+-------+------+---------------+----------+---------+------------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref        | rows | Extra                       |
+----+-------------+-------+------+---------------+----------+---------+------------+------+-----------------------------+
|  1 | SIMPLE      | c     | ref  | numberId      | numberId | 4       | const      |    1 | Using where; Using filesort |
|  1 | SIMPLE      | c2    | ref  | parentId      | parentId | 3       | books.c.id |   14 |                             |
+----+-------------+-------+------+---------------+----------+---------+------------+------+-----------------------------+

mysql> EXPLAIN SELECT c.id, c2.id
                   FROM swb_comments c LEFT JOIN swb_comments c2 ON c.id = c2.parentID
                   WHERE c.numberId = 1411     ORDER BY c.id DESC     LIMIT 10;
+----+-------------+-------+------+---------------+----------+---------+------------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref        | rows | Extra                       |
+----+-------------+-------+------+---------------+----------+---------+------------+------+-----------------------------+
|  1 | SIMPLE      | c     | ref  | numberId      | numberId | 4       | const      |    1 | Using where; Using filesort |
|  1 | SIMPLE      | c2    | ref  | parentId      | parentId | 3       | books.c.id |   14 |                             |
+----+-------------+-------+------+---------------+----------+---------+------------+------+-----------------------------+

and are exactly what I'd expect.

This is very mysterious.

I'll think about it a bit more to see if there's anything else we can try.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文