mysql - MySQL 选择了完全错误的索引
问题描述
由于某种原因，MySQL 选择了完全错误的索引。感觉就像它没有检查哪个索引最适合查询。
联系人表上的一些索引：
+----------+------------+---------------------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| 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 分钟（2M 行）。
当我强制idx_contacts_date_modfied或idx_del_date_modified_id索引时，查询需要 0.5 秒。
为了好玩，我尝试删除索引idx_contacts_del_last并重新添加它。之后，mysql选择了一个不同的索引——idx_reports_to_id这意味着MySQL甚至没有尝试选择最佳索引，可能会选择它看到的第一个索引……根据我的观察，第一个索引，它deleted的第一个字段是字段并且是第一个添加的被选中。
所以我删除并重新创建了所有索引，除了我希望查询使用的索引，它最终选择了正确的索引。但是现在我需要一个不同的查询来使用不同的索引，它仍然继续使用我没有重新创建的那个。
有没有一些设置可以让mysql在索引优化上看起来更彻底？我使用 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中有一个错误
设置后
SET SESSION optimizer_switch='block_nested_loop=off';
查询作为一种魅力飞行。
推荐阅读
- asp.net-core - .NET Core - Serilog - IHostBuilder 不包含 UseSerilog() 的定义
- javascript - 如何制作曲线以连接 HTML 和 CSS 中的两个节点？
- javascript - 使用 DOM 操作删除 CSS 'transform'
- linux - 单个 Windows 进程无法使用我设置的所有 80 个可用线程，为什么？
- if-statement - 如何使用“IF 语句”从范围中选择数字？
- c++ - 如何递归地将整数链表拆分为 C++ 中的奇数和偶数列表？
- amazon-web-services - 在开发过程中如何在不产生成本的情况下实施云解决方案？
- spring - 对于 FlowJobBuilder 类型，方法 next(Step) 未定义
- java - Hibernate OneToOne 尝试从 null 一对一属性分配 id
- powershell - Powershell - CSV 导出格式