MySQL选择完全错误的索引 [英] MySQL chooses completely wrong index
问题描述
由于某种原因，MySQL选择完全错误的索引.感觉好像没有检查哪个索引最适合查询.
For some reason, MySQL chooses completely wrong indexes. It feels like it doesn't check which index is the best for the query.
联系人表上的一些索引:
Some indexes on the contacts table:
+----------+------------+---------------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+---------------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| contacts | 0 | PRIMARY | 1 | id | A | 2227424 | NULL | NULL | | BTREE | | |
| contacts | 1 | idx_contacts_date_modfied | 1 | date_modified | A | 261152 | NULL | NULL | YES | BTREE | | |
| contacts | 1 | idx_contacts_id_del | 1 | id | A | 2228229 | NULL | NULL | | BTREE | | |
| contacts | 1 | idx_contacts_id_del | 2 | deleted | A | 2228229 | NULL | NULL | YES | BTREE | | |
| contacts | 1 | idx_contacts_date_entered | 1 | date_entered | A | 286622 | NULL | NULL | YES | BTREE | | |
| contacts | 1 | idx_cont_last_first | 1 | last_name | A | 783981 | NULL | NULL | YES | BTREE | | |
| contacts | 1 | idx_cont_last_first | 2 | first_name | A | 1434526 | NULL | NULL | YES | BTREE | | |
| contacts | 1 | idx_cont_last_first | 3 | deleted | A | 1434526 | NULL | NULL | YES | BTREE | | |
| contacts | 1 | idx_contacts_del_last | 1 | deleted | A | 1 | NULL | NULL | YES | BTREE | | |
| contacts | 1 | idx_contacts_del_last | 2 | last_name | A | 830164 | NULL | NULL | YES | BTREE | | |
| contacts | 1 | idx_cont_del_reports | 1 | deleted | A | 1 | NULL | NULL | YES | BTREE | | |
| contacts | 1 | idx_cont_del_reports | 2 | reports_to_id | A | 1 | NULL | NULL | YES | BTREE | | |
| contacts | 1 | idx_cont_del_reports | 3 | last_name | A | 830164 | NULL | NULL | YES | BTREE | | |
| contacts | 1 | idx_reports_to_id | 1 | reports_to_id | A | 1 | NULL | NULL | YES | BTREE | | |
| contacts | 1 | idx_del_id_user | 1 | deleted | A | 1 | NULL | NULL | YES | BTREE | | |
| contacts | 1 | idx_del_id_user | 2 | id | A | 2228229 | NULL | NULL | | BTREE | | |
| contacts | 1 | idx_del_id_user | 3 | assigned_user_id | A | 2228229 | NULL | NULL | YES | BTREE | | |
| contacts | 1 | idx_cont_assigned | 1 | assigned_user_id | A | 2 | NULL | NULL | YES | BTREE | | |
| contacts | 1 | idx_contact_title | 1 | title | A | 1 | NULL | NULL | YES | BTREE | | |
| contacts | 1 | idx_contact_mkto_id | 1 | mkto_id | A | 1 | NULL | NULL | YES | BTREE | | |
| contacts | 1 | idx_contacts_first_last | 1 | first_name | A | 265736 | NULL | NULL | YES | BTREE | | |
| contacts | 1 | idx_contacts_first_last | 2 | last_name | A | 1453136 | NULL | NULL | YES | BTREE | | |
| contacts | 1 | idx_contacts_first_last | 3 | deleted | A | 1453136 | NULL | NULL | YES | BTREE | | |
| contacts | 1 | idx_contacts_assigned_del | 1 | assigned_user_id | A | 2 | NULL | NULL | YES | BTREE | | |
| contacts | 1 | idx_contacts_assigned_del | 2 | deleted | A | 2 | NULL | NULL | YES | BTREE | | |
| contacts | 1 | idx_contacts_tmst_id | 1 | team_set_id | A | 1 | NULL | NULL | YES | BTREE | | |
| contacts | 1 | idx_contacts_tmst_id | 2 | deleted | A | 1 | NULL | NULL | YES | BTREE | | |
| contacts | 1 | idx_del_date_modified_id | 1 | deleted | A | 1 | NULL | NULL | YES | BTREE | | |
| contacts | 1 | idx_del_date_modified_id | 2 | date_modified | A | 265687 | NULL | NULL | YES | BTREE | | |
+----------+------------+---------------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
查询:
SELECT SQL_NO_CACHE contacts.id,
contacts.date_modified contacts__date_modified
FROM contacts
INNER JOIN
(SELECT tst.team_set_id
FROM team_sets_teams tst
INNER JOIN team_memberships team_membershipscontacts ON (team_membershipscontacts.team_id = tst.team_id)
AND (team_membershipscontacts.user_id = '5daa2e92-c347-11e9-afc5-525400a80916')
AND (team_membershipscontacts.deleted = 0)
GROUP BY tst.team_set_id) contacts_tf ON contacts_tf.team_set_id = contacts.team_set_id
LEFT JOIN contacts_cstm contacts_cstm ON contacts_cstm.id_c = contacts.id
WHERE contacts.deleted = 0
ORDER BY contacts.date_modified DESC,
contacts.id DESC
LIMIT 21;
由于某种原因，编译器选择索引idx_contacts_del_last，其中包含的字段甚至不在查询中！查询大约需要2分钟(200万行).
For some reason, the compiler chooses index idx_contacts_del_last which contains field which is not even in the query! and the query takes around 2 minutes (2M rows).
当我强制idx_contacts_date_modfied或idx_del_date_modified_id索引时，查询需要0.5s.
When I force idx_contacts_date_modfied or idx_del_date_modified_id index, the query takes 0.5s.
为了娱乐，我尝试删除索引idx_contacts_del_last并再次添加.之后，mysql选择了一个不同的索引-idx_reports_to_id，这意味着MySQL甚至没有尝试选择最佳索引，并且可能选择了它看到的第一个索引...从我的观察中，第一个索引具有字段deleted作为选择第一个字段并添加到第一个字段.
For fun, I tried to delete the index idx_contacts_del_last and add it again. After that, mysql chose a DIFFERENT index - idx_reports_to_id implying that MySQL does not even try to choose optimal index and probably chooses the first index which it sees... From my observation the first index, which have field deleted as the first field and was added first is chosen.
因此，我删除并重新创建了除我希望查询使用的索引以外的所有索引，并最终选择了正确的索引.但是现在我需要一个不同的查询来使用不同的索引，并且它仍然继续使用我没有重新创建的索引.
So I dropped and recreated all indexes except the one I want the query to use and it finally chooses the correct one. But now I need a different query to use a different index and it still keeps using the one I didn't recreate.
是否有一些设置可以使mysql在索引优化上看起来更加彻底?我使用mysql 5.7.6
Is there some setting which can make mysql look more thoroughly on index optimization? I Use mysql 5.7.6
查询是系统生成的，我无法更改
说明:
+----+-------------+--------------------------+------------+--------+--------------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------------------------------------+---------+----------+---------------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------------------+------------+--------+--------------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------------------------------------+---------+----------+---------------------------------------------------------------------+
| 1 | PRIMARY | contacts | NULL | ref | idx_contacts_del_last,idx_cont_del_reports,idx_del_id_user,idx_contacts_tmst_id,idx_del_date_modified,idx_del_date_modified_id | idx_contacts_del_last | 2 | const | 1114111 | 100.00 | Using temporary; Using filesort |
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 50.00 | Using where; Using join buffer (Block Nested Loop) |
| 1 | PRIMARY | contacts_cstm | NULL | eq_ref | PRIMARY | PRIMARY | 144 | sugarcrm.contacts.id | 1 | 100.00 | Using index |
| 2 | DERIVED | team_membershipscontacts | NULL | ref | idx_team_membership,idx_teammemb_team_user,idx_del_team_user | idx_team_membership | 145 | const | 2 | 99.36 | Using index condition; Using where; Using temporary; Using filesort |
| 2 | DERIVED | tst | NULL | ref | idx_ud_set_id,idx_ud_team_id,idx_ud_team_set_id,idx_ud_team_id_team_set_id | idx_ud_team_id_team_set_id | 144 | sugarcrm.team_membershipscontacts.team_id | 1 | 100.00 | Using index |
+----+-------------+--------------------------+------------+--------+--------------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------------------------------------+---------+----------+---------------------------------------------------------------------+
推荐答案
结果证明mysql中存在错误 https://bugs.mysql.com/bug.php?id=69721
Turns out there is a bug in mysql https://bugs.mysql.com/bug.php?id=69721
设置后
SET SESSION optimizer_switch='block_nested_loop=off';
查询飞速发展.
这篇关于MySQL选择完全错误的索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
