MySQL 5.7.6错误选择索引致查询过慢，求优化配置方案

It’s frustrating when the optimizer picks a non-optimal index and tanks your query performance, especially when you know the right indexes exist. Let’s walk through the root causes here and actionable fixes to get MySQL to choose the best index consistently.

Problem Recap #

Your query against the contacts table is choosing idx_contacts_del_last (which doesn’t include your date_modified sort field) leading to a 2-minute runtime, while forcing idx_contacts_date_modfied or idx_del_date_modified_id cuts it to just 0.5 seconds. The pattern shows MySQL is prioritizing indexes with deleted as a prefix, even when they’re not suited for the query.

Configuration Tweaks to Improve Index Evaluation #

These settings will make the optimizer more thorough in calculating index costs, reducing bad choices:

1. Adjust Optimizer Cost Constants #

MySQL’s optimizer weights IO vs CPU costs; tweaking these can make it prioritize indexes that avoid expensive operations like temporary tables or filesorts:

• For SSD storage, lower IO cost values (since SSD is faster than traditional HDD):

  SET GLOBAL innodb_read_io_cost = 2;
  SET GLOBAL innodb_write_io_cost = 4;
  

• Ensure sufficient sort buffer space to reduce the optimizer’s fear of sorting overhead:

  SET GLOBAL sort_buffer_size = 262144; -- 256KB, adjust based on your server memory
  SET GLOBAL read_rnd_buffer_size = 262144;
  

2. Refresh Table Statistics #

Outdated or inaccurate statistics are a common culprit for bad index choices. Update them for the contacts table:

ANALYZE TABLE contacts;

For InnoDB, increase the sample size to get more accurate cardinality estimates:

SET GLOBAL innodb_stats_sample_pages = 64; -- Default is 8; higher = more accurate stats

3. Force Index Dives for Low-Cardinality Columns #

Your deleted column has extremely low cardinality (1), which makes the optimizer rely on rough estimates instead of evaluating actual index utility. Disable the shortcut for range queries:

SET GLOBAL eq_range_index_dive_limit = 0; -- Forces the optimizer to calculate real costs for all candidate indexes

Index Structure Optimizations #

Fixing the index design itself will eliminate the ambiguity for the optimizer:

1. Turn idx_del_date_modified_id into a Covering Index #

This index already matches your WHERE deleted=0 and ORDER BY date_modified DESC clauses—adding id to it makes it a covering index, so MySQL can get all needed data directly from the index (no table lookup):

ALTER TABLE contacts DROP INDEX idx_del_date_modified_id;
ALTER TABLE contacts ADD INDEX idx_del_date_modified_id (deleted, date_modified, id);

This makes this index vastly more efficient, so the optimizer will naturally prefer it.

2. Remove Redundant Low-Value Indexes #

Indexes like idx_contacts_del_last and idx_cont_del_reports have deleted as a prefix but offer no real value for your common queries (since deleted=0 is always true here). Deleting these reduces the optimizer’s candidate pool and avoids bad choices:

DROP INDEX idx_contacts_del_last ON contacts;
DROP INDEX idx_cont_del_reports ON contacts;

Temporary Fix: Index Hints #

While you work on the long-term fixes, you can force the correct index in your query to get immediate performance:

SELECT SQL_NO_CACHE contacts.id, contacts.date_modified contacts__date_modified 
FROM contacts FORCE INDEX(idx_del_date_modified_id)
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;

Verify the Fix #

After making changes, run EXPLAIN on your query to confirm the optimizer is now choosing the correct index:

EXPLAIN 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;

内容的提问来源于stack exchange，提问作者Charlestone