mysql - 查询优化器出现奇怪的 mysql 问题

Question

我有 2 台 mysql 服务器。 1 个节点为主节点，另一个节点作为从节点，从主节点进行复制

这 2 个节点具有相同的数据和架构。然而，当在两个节点上运行时，1 个特定查询的执行方式与 mysql 不同

查询

EXPLAIN SELECT t.*, COUNT(h.id)
                         FROM tags t
                         INNER JOIN tags2articles s
                         ON t.id = s.tag_id
                         INNER JOIN tag_hits h
                         ON h.id = s.tag_id
                         INNER JOIN articles art
                         ON art.id = s.`article_id`
                         WHERE art.source_id IN (SELECT id FROM feeds WHERE source_id = 15074)
                         AND time_added > DATE_SUB(NOW(), INTERVAL 1 DAY)
                         AND t.type = '1'
                         GROUP BY t.id
                         HAVING COUNT(h.id) > 4
                         ORDER BY COUNT(h.id) DESC
                         LIMIT 15

下面是在两个节点上运行的 EXPLAIN 查询的输出。注意主节点正在输出正确的一个

主节点上的输出

+----+--------------------+-------+-----------------+-----------------------------+---------------------+---------+----------------+--------+----------------------------------------------+
| id | select_type        | table | type            | possible_keys               | key                 | key_len | ref            | rows   | Extra                                        |
+----+--------------------+-------+-----------------+-----------------------------+---------------------+---------+----------------+--------+----------------------------------------------+
|  1 | PRIMARY            | art   | ALL             | PRIMARY                     | NULL                | NULL    | NULL           | 100270 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY            | s     | ref             | PRIMARY,FK_tags2articles    | FK_tags2articles    | 4       | art.id         |     12 | Using index                                  |
|  1 | PRIMARY            | h     | ref             | tags_hits_idx               | tags_hits_idx       | 4       | s.tag_id       |      1 | Using index                                  |
|  1 | PRIMARY            | t     | eq_ref          | PRIMARY,tags_type_idx       | PRIMARY             | 4       | s.tag_id       |      1 | Using where                                  |
|  2 | DEPENDENT SUBQUERY | feeds | unique_subquery | PRIMARY,f_source_id_idx     | PRIMARY             | 4       | func           |      1 | Using where                                  |
+----+--------------------+-------+-----------------+-----------------------------+---------------------+---------+----------------+--------+----------------------------------------------+

从节点输出

+----+--------------------+--------+------------ ---+----------------------------+-------------- ---+---------+--------------------+--------+----- -----------------------------------

------+
| id | select_type        | table | type            | possible_keys               | key              | key_len | ref                | rows   | Extra                                        |
+----+--------------------+-------+-----------------+-----------------------------+------------------+---------+--------------------+--------+----------------------------------------------+
|  1 | PRIMARY            | t     | ref             | PRIMARY,tags_type_idx       | tags_type_idx    |  2      | const              | 206432 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY            | h     | ref             | tags_hits_idx               | tags_hits_idx    | 4       | t.id               |      1 | Using index                                  |
|  1 | PRIMARY            | s     | ref             | PRIMARY,FK_tags2articles    | PRIMARY          | 4       | h.id               |      2 | Using where; Using index                     |
|  1 | PRIMARY            | art   | eq_ref          | PRIMARY                     | PRIMARY          | 4       | s.article_id       |      1 | Using where                                  |
|  2 | DEPENDENT SUBQUERY | feeds | unique_subquery | PRIMARY,f_source_id_idx     | PRIMARY          | 4       | func               |      1 | Using where                                  |
+----+--------------------+-------+-----------------+-----------------------------+------------------+---------+--------------------+--------+----------------------------------------------+

我无法理解为什么会存在这种差异。有什么帮助吗？

谢谢

Answer 1

它们可以有不同的索引/键统计信息，这会导致索引使用方面的差异。如果可能(锁定表，因此并不总是建议)运行 ANALYZE TABLE对于所有参与表，查询计划可能是相同的。