mysql如何像这样调整自联接表？ [英] How to tune self-join table in mysql like this?

问题描述

我有此表格，我想从中选择日期。该查询花了2分钟才能运行400万条记录。我不确定可以从此查询中挤出多少。

I have this table which I'm trying to select from and to date. The query took 2 min to run on 4 million records. I'm not sure how much more I can squeeze out of this query.

    SELECT c.fk_id, c.from_date, c.fk_pb, MIN(o.from_date) AS to_date  
    FROM TABLE_X c  
        INNER JOIN TABLE_X o ON c.fk_id =  o.fk_id AND c.fk_pb = o.fk_pb  
WHERE o.from_date > c.from_date  
        GROUP BY c.fk_id, c.from_date, c.fk_pb

有已经是from_date，fk_pb和fk_id的索引。

There are indexes on from_date, fk_pb and fk_id already.

架构是这样的。

+-----------------------------+---------------+------+-----+---------+-------+
| Field                       | Type          | Null | Key | Default | Extra |
+-----------------------------+---------------+------+-----+---------+-------+
| FK_ID                       | int(11)       | YES  | MUL | NULL    |       |
| FK_PB                       | int(11)       | YES  | MUL | NULL    |       |
| FROM_DATE                   | date          | YES  | MUL | NULL    |       |
| TO_DATE                     | date          | YES  |     | NULL    |       |
+-----------------------------+---------------+------+-----+---------+-------+

我知道我完全不应该在MySQL中使用自连接，但是数据是这样的，我正在努力寻找从中选择和更新日期的最佳方法表。如果还有其他方法可以使它更快，那就太好了。

I know I should not use self-join at all in MySQL, but the data comes like this and I'm trying to find the best way to select from and to date out of this table. If there's anything else I could do to make this one faster that'd be great.

非常感谢。

已更新

+----+-------------+-------+------+----------------------------------------------------------------------+-------------------------+---------+----------------------------------------+---------+----------------------------------------------+
| id | select_type | table | type | possible_keys                                                        | key                     | key_len | ref                                    | rows    | Extra                                        |
+----+-------------+-------+------+----------------------------------------------------------------------+-------------------------+---------+----------------------------------------+---------+----------------------------------------------+
|  1 | SIMPLE      | c     | ALL  | IDX_FK_PB,IDX_FK_ID,IDX_FRM_DATE                                     | NULL                    | NULL    | NULL                                   | 4527750 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | o     | ref  | IDX_FK_PB,IDX_FK_ID,IDX_FRM_DATE                                     | IDX_FK_ID               | 5       | db.c.FK_ID                             |     110 | Using where                                  |
+----+-------------+-------+------+----------------------------------------------------------------------+-------------------------+---------+----------------------------------------+---------+----------------------------------------------+

推荐答案

在所有相关列中添加索引可以加快速度：

Adding an index to all relevant columns speeds this up:

INDEX(FK_ID, FK_PB,FROM_DATE)

哪个表现更好，因为：

• MySQL可以为
  <$ c $的所有行使用索引c> c ，因此不需要为此返回表（在索引中添加不在列中的列会再次使其变慢）。


• MySQL在索引合并，因此常常选择不使用它（幸运的是），并且当它使用时通常不是最佳选择。


• 好吧，该索引涵盖了您搜索的所有内容（在此情况下，join on =搜索）的速度比MySQL在单独的列上选择索引之一（限制最严格的索引， SHOW INDEX FROM tablename 可以显示基数）更快使用并必须扫描其他列中的值。

• MySQL can use the index for all the rows for c, so it doesn't need to go back to the table for this (adding a column not in the index would slow it down again a bit).
• MySQL is pretty bad at index merging, and so often chooses not to use it (luckily), and when it does it's often suboptimal.
• Well, an index covering all you search for (in this case, join on = searching for) is faster then MySQL electing one of the indexes (the most restrictive one, SHOW INDEX FROM tablename can show you the cardinality) on the separate columns to use and having to scan for the values in the other columns.

这篇关于mysql如何像这样调整自联接表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！