MySQL选择完全错误的索引

Question

由于某种原因，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                                                         |
+----+-------------+--------------------------+------------+--------+--------------------------------------------------------------------------------------------------------------------------------+----------------------------+---------+-------------------------------------------+---------+----------+---------------------------------------------------------------------+

Answer 1

原来mysql有bug https://bugs.mysql.com/bug.php?id=69721

设置后

SET SESSION optimizer_switch='block_nested_loop=off';

查询就像魅力一样飞翔。