I have a problem with this query:
我有这个查询的问题：
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;
We have a cluster with percona server (5.6.29) with 5 nodes(from 0 to 4) in Azure. The difference between nodes 0-3 and 4 is that, the first ones are in a balancer and the node 4 is out of the balancer (but in the cluster)
我们有一个带有percona服务器的集群（5.6.29），在Azure中有5个节点（从0到4）。节点0-3和4之间的区别在于，第一个节点在平衡器中，节点4在平衡器之外（但在集群中）
The problem is that in four of the servers (nodes 0-3) the query is really slow (15 sec) and in the other one (node 4)the query is really fast (0,002)
问题是在四个服务器（节点0-3）中查询真的很慢（15秒）而在另一个（节点4）中查询真的很快（0,002）
Afaik, the explain plan should be the same but i execute an EXPLAIN and the result is this:
Afaik，解释计划应该是相同的，但我执行一个EXPLAIN，结果是这样的：
Nodes 0-3 (Slow)
节点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 | +----+-------------+-------+------+-------------------------------------------------------------+--------------+---------+-------------------------------+---------+---------------------------------------+
Node 4 (Fast)
节点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 | +----+-------------+-------+--------+-------------------------------------------------------------+---------------------------------+---------+-----------------------------------+---------+-----------------------+
I notice that in the slow one the index is not being used. so i checked the indexes:
我注意到在慢速中索引没有被使用。所以我检查了索引：
Node 0:
节点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 | | | +-----------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
Node 4:
节点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 | | | +-----------------+------------+----------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
My first question is why are the indexes different? it should be the same due to both are in the same cluster right?
我的第一个问题是为什么索引不同？它应该是相同的，因为两者都在同一个集群中吗？
Why the execute plans are different? Both are in the same cluster so, it should be the same
为什么执行计划不同？两者都在同一个集群中，它应该是相同的
Should i use FORCE INDEX or STRAIGHT_JOIN?
我应该使用FORCE INDEX还是STRAIGHT_JOIN？
1
The answer is hidden in the two explain outputs - on node 4, the second join is using the index is_validated_with on the uca table, but the expected # of rows is 2755595, which is greater than the number of rows for a full scan of the uc table on the other plan.
答案隐藏在两个解释输出中 - 在节点4上，第二个连接在uca表上使用索引is_validated_with，但预期的行数为2755595，这大于完全扫描的行数。另一个计划的uc表。
With the available information it's hard to say for sure, but since nodes 0-3 are seeing active use while node4 isn't, my guess is that the statistics that the optimizer uses to decide on query plans may no longer reflect the actual state of the table. You could try to run ANALYZE TABLE on all three tables on all nodes and I suspect you'll see the same plan generated (assuming all nodes have the same data).
有了可用的信息，很难肯定，但由于节点0-3看到活动使用而node4不是，我的猜测是优化器用来决定查询计划的统计数据可能不再反映实际状态桌子。您可以尝试在所有节点上的所有三个表上运行ANALYZE TABLE，我怀疑您将看到生成相同的计划（假设所有节点具有相同的数据）。
In addition to using FORCE_INDEX, you could also tweak the optimizer flags to try to favor one plan over another, but it's generally much better to fix the underlying issue, as you might fix this problem now only to get bitten by something else later on.
除了使用FORCE_INDEX之外，您还可以调整优化程序标志以尝试支持一个计划而不是另一个计划，但通常要更好地修复底层问题，因为您现在可能已经解决了这个问题，以后再被其他问题所困扰。
0
I think you have user_call_id field on both tables. If you change your query like this it will force server to use indexes:
我认为你在两个表上都有user_call_id字段。如果您更改这样的查询，它将强制服务器使用索引：
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 uca2.user_call_id = 1744136
INNER JOIN users_calls uc ON uc.user_call_id = uca.user_call_id uc.user_call_id = 1744136
WHERE uca.user_call_id = 1744136;
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有