mysql - INNER JOIN 导致查询执行时间很长

Question

我有一个很大的表，有数百万条记录。下面的查询仅需 0.2 秒即可执行，这很好。

SELECT ch1.*
FROM citizens_history ch1
WHERE ch1.update_id_to = ( 
    SELECT MAX( ch2.update_id_to ) 
    FROM citizens_history ch2
    WHERE ch2.id = ch1.id
)
ORDER BY ch1.experience DESC
LIMIT 100

但是，当我尝试添加 INNER JOIN 时，就像这样

SELECT ch1.*, upd.*
FROM citizens_history ch1
INNER JOIN updates upd ON upd.id = ch1.update_id_to
WHERE ch1.update_id_to = ( 
    SELECT MAX( ch2.update_id_to ) 
    FROM citizens_history ch2
    WHERE ch2.id = ch1.id
)
ORDER BY ch1.experience DESC
LIMIT 100

执行需要很长时间。在第一种情况下，我认为 MySQL 正在这样做:

1. 使用索引按经验排序
2. 适用于何处
3. 找到 100 条记录时结束

在第二种情况下，我假设会发生这种情况:

1. 使用索引按经验排序
2. 连接数百万行中的每条记录<--慢
3. 适用于何处
4. 找到 100 条记录时结束

您对我如何优化它有什么建议吗？

编辑:快速查询和慢速查询的解释:

+----+--------------------+-------+-------+---------------+------------+---------+--------------+------+-------------+
| id | select_type        | table | type  | possible_keys | key        | key_len | ref                 | rows | Extra       |
+----+--------------------+-------+-------+---------------+------------+---------+--------------+------+-------------+
|  1 | PRIMARY            | ch1   | index | NULL          | experience | 3       | NULL         |  100 | Using where |
|  2 | DEPENDENT SUBQUERY | ch2   | ref   | id            | id         | 3       | db.ch1.id    |    1 | Using index |
+----+--------------------+-------+-------+---------------+------------+---------+--------------+------+-------------+

+----+--------------------+-------+-------+-------------------------------------+----------+---------+--------------+------+----------------------------------------------+
| id | select_type        | table | type  | possible_keys                       | key      | key_len | ref          | rows | Extra                                        |
+----+--------------------+-------+-------+-------------------------------------+----------+---------+--------------+------+----------------------------------------------+
|  1 | PRIMARY            | upd   | index | PRIMARY                             | datetime | 5       | NULL         |  389 | Using index; Using temporary; Using filesort |
|  1 | PRIMARY            | ch1   | ref   | PRIMARY,update_id_to,update_id_to_2 | PRIMARY  | 4       | db.upd.id    |  112 | Using where                                  |
|  2 | DEPENDENT SUBQUERY | ch2   | ref   | id                                  | id       | 3       | db.ch1.id    |    1 | Using index                                  |
+----+--------------------+-------+-------+-------------------------------------+----------+---------+--------------+------+----------------------------------------------+

Answer 1

重写第一个查询如下，然后按要求修改...

SELECT ch1.*
  FROM citizens_history ch1
  JOIN 
     ( SELECT id
            , MAX(update_id_to) max_update_id_to 
         FROM citizens_history 
        GROUP 
           BY id
     ) ch2
    ON ch2.id = ch1.id
   AND ch2.max_update_id_to = ch1.update_id_to
 ORDER 
    BY ch1.experience DESC
 LIMIT 100

如果您仍在苦苦挣扎(在性能方面)，请为上述内容提供 EXPLAIN 以及所有相关表的正确 DDLS。