mysql - 同一集群中的不同解释计划

Question

我对此查询有疑问:

SELECT 
    uca.user_activity_id,
    uca.user_call_id,
    uca.call_activity_id,
    uca.user_activity_token,
    uc.call_group_id,
    uc.user_id
FROM users_calls_activities uca
INNER JOIN users_calls_activities uca2 ON uca2.user_activity_id = uca.user_activity_is_validated_with
    AND aux.user_call_id = 1744136
INNER JOIN users_calls uc ON uc.user_call_id = uca.user_call_id;

我们在 Azure 中有一个包含 percona 服务器 (5.6.29) 的集群，该集群有 5 个节点(从 0 到 4)。节点0-3和4之间的区别在于，第一个节点在平衡器中，而节点4在平衡器之外(但在集群中)

问题在于，在其中四台服务器(节点 0-3)中，查询非常慢(15 秒)，而在另一台服务器(节点 4)中，查询非常快(0,002)

Afaik，解释计划应该是相同的，但我执行EXPLAIN，结果是这样的:

节点 0-3(慢)

+----+-------------+-------+------+-------------------------------------------------------------+--------------+---------+-------------------------------+---------+---------------------------------------+| id | select_type | table | type | possible_keys                                               | key          | key_len | ref                           | rows    | Extra                                 |+----+-------------+-------+------+-------------------------------------------------------------+--------------+---------+-------------------------------+---------+---------------------------------------+|  1 | SIMPLE      | uca2  | ref  | PRIMARY,user_call_id,user_call_id_2                         | user_call_id | 4       | const                         |       1 | Using index                           ||  1 | SIMPLE      | uc    | ALL  | PRIMARY,user_call_id                                        | NULL         | NULL    | NULL                          | 2098152 | Using join buffer (Block Nested Loop) ||  1 | SIMPLE      | uca   | ref  | user_call_id,user_call_id_2,is_validated_with               | user_call_id | 4       | db.uc.user_call_id            |       1 | Using where                           |+----+-------------+-------+------+-------------------------------------------------------------+--------------+---------+-------------------------------+---------+---------------------------------------+

节点 4(快速)

+----+-------------+-------+--------+-------------------------------------------------------------+---------------------------------+---------+-----------------------------------+---------+-----------------------+| id | select_type | table | type   | possible_keys                                               | key                             | key_len | ref                               | rows    | Extra                 |+----+-------------+-------+--------+-------------------------------------------------------------+---------------------------------+---------+-----------------------------------+---------+-----------------------+|  1 | SIMPLE      | uca2  | ref    | PRIMARY,user_call_id,user_call_id_2                         | user_call_id                    | 4       | const                             |       1 | Using index           ||  1 | SIMPLE      | uca   | ref    | user_call_id,user_call_id_2,is_validated_with               | is_validated_with               | 5       | db.uc2.user_activity_id           | 2755595 | Using index condition ||  1 | SIMPLE      | uc    | eq_ref | PRIMARY,user_call_id                                        | PRIMARY                         | 4       | db.uca.user_call_id               |       1 | NULL                  |+----+-------------+-------+--------+-------------------------------------------------------------+---------------------------------+---------+-----------------------------------+---------+-----------------------+

我注意到在慢速中索引没有被使用。所以我检查了索引:

节点0:

+-----------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table           | Non_unique | Key_name             | Seq_in_index | Column_name          | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-----------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| users_calls     |          0 | PRIMARY              |            1 | user_call_id         | A         |     2099153 |     NULL | NULL   |      | BTREE      |         |               || users_calls     |          1 | call_group_id        |            1 | call_group_id        | A         |       16659 |     NULL | NULL   |      | BTREE      |         |               || users_calls     |          1 | user_call_begin_date |            1 | user_call_begin_date | A         |     1049576 |     NULL | NULL   | YES  | BTREE      |         |               || users_calls     |          1 | user_call_begin_date |            2 | user_call_end_date   | A         |     2099153 |     NULL | NULL   | YES  | BTREE      |         |               || users_calls     |          1 | user_call_id         |            1 | user_call_id         | A         |     2099153 |     NULL | NULL   |      | BTREE      |         |               || users_calls     |          1 | user_call_id         |            2 | user_id              | A         |     2099153 |     NULL | NULL   |      | BTREE      |         |               || users_calls     |          1 | user_id              |            1 | user_id              | A         |       91267 |     NULL | NULL   |      | BTREE      |         |               || users_calls     |          1 | user_id              |            2 | call_id              | A         |     2099153 |     NULL | NULL   |      | BTREE      |         |               || users_calls     |          1 | user_id              |            3 | user_call_status     | A         |     2099153 |     NULL | NULL   |      | BTREE      |         |               || users_calls     |          1 | fk_users_calls_calls |            1 | call_id              | A         |       23067 |     NULL | NULL   |      | BTREE      |         |               |+-----------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

节点4:

+-----------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table           | Non_unique | Key_name             | Seq_in_index | Column_name          | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-----------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| users_calls     |          0 | PRIMARY              |            1 | user_call_id         | A         |     2091476 |     NULL | NULL   |      | BTREE      |         |               || users_calls     |          1 | call_group_id        |            1 | call_group_id        | A         |       26813 |     NULL | NULL   |      | BTREE      |         |               || users_calls     |          1 | user_call_begin_date |            1 | user_call_begin_date | A         |     1045738 |     NULL | NULL   | YES  | BTREE      |         |               || users_calls     |          1 | user_call_begin_date |            2 | user_call_end_date   | A         |     2091476 |     NULL | NULL   | YES  | BTREE      |         |               || users_calls     |          1 | user_call_id         |            1 | user_call_id         | A         |     2091476 |     NULL | NULL   |      | BTREE      |         |               || users_calls     |          1 | user_call_id         |            2 | user_id              | A         |     2091476 |     NULL | NULL   |      | BTREE      |         |               || users_calls     |          1 | user_id              |            1 | user_id              | A         |       53627 |     NULL | NULL   |      | BTREE      |         |               || users_calls     |          1 | user_id              |            2 | call_id              | A         |     2091476 |     NULL | NULL   |      | BTREE      |         |               || users_calls     |          1 | user_id              |            3 | user_call_status     | A         |     2091476 |     NULL | NULL   |      | BTREE      |         |               || users_calls     |          1 | fk_users_calls_calls |            1 | call_id              | A         |       15608 |     NULL | NULL   |      | BTREE      |         |               |+-----------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

我的第一个问题是为什么索引不同？它应该是相同的，因为两者都在同一个集群中，对吗？

为什么执行计划不同？两者都在同一个集群中，所以应该是相同的

我应该使用FORCE INDEX还是STRAIGHT_JOIN？

Answer 1

答案隐藏在两个解释输出中 - 在节点 4 上，第二个连接使用 uca 表上的索引 is_validated_with，但预期的行数为 2755595，大于另一个计划上的 uc 表的完整扫描的行数。

根据可用信息，很难确定，但由于节点 0-3 正在积极使用，而节点 4 则没有，我的猜测是优化器用于决定查询计划的统计信息可能不再反射(reflect)表的实际状态。您可以尝试在所有节点上的所有三个表上运行 ANALYZE TABLE，我怀疑您会看到生成的相同计划(假设所有节点具有相同的数据)。

除了使用 FORCE_INDEX 之外，您还可以调整 optimizer flags试图支持一个计划而不是另一个计划，但通常最好是解决根本问题，因为您可能现在解决这个问题，但稍后会被其他问题所困扰。