2个类似的查询在MySQL中，2个性能差别很大，为什么？

Question

经过一番挖掘和调试，我们发现了问题。

mysql> explain select count(*) from event where survey_id = 158 and event_datez>'2019-10-30 00:00:00' and event_datez<'2019-11-28 23:59:59' ; # Query takes 4s
+----+-------------+--------------+------------+-------+-----------------------------------------------+------------------+---------+------+---------+----------+------------------------------------+
| id | select_type | table        | partitions | type  | possible_keys                                 | key              | key_len | ref  | rows    | filtered | Extra                              |
+----+-------------+--------------+------------+-------+-----------------------------------------------+------------------+---------+------+---------+----------+------------------------------------+
|  1 | SIMPLE      |        event | NULL       | range | FK_g1lx0ea096nqioytyhtjng72t, i_event_2       | i_event_2        | 6       | NULL | 2975160 |    50.00 | Using index condition; Using where |
+----+-------------+--------------+------------+-------+-----------------------------------------------+------------------+---------+------+---------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select count(*) from event where survey_id = 158 and event_datez>'2019-10-29 00:00:00' and event_datez<'2019-11-28 23:59:59' ; # Query takes 40s
+----+-------------+--------------+------------+------+-----------------------------------------------+------------------------------+---------+-------+----------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys                                 | key                          | key_len | ref   | rows     | filtered | Extra       |
+----+-------------+--------------+------------+------+-----------------------------------------------+------------------------------+---------+-------+----------+----------+-------------+
|  1 | SIMPLE      | event        | NULL       | ref  | FK_g1lx0ea096nqioytyhtjng72t,i_event_2        | FK_g1lx0ea096nqioytyhtjng72t | 9       | const | 16272884 |    12.23 | Using where |
+----+-------------+--------------+------------+------+-----------------------------------------------+------------------------------+---------+-------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

问题是InnoDB更改了查询使用的索引。我的问题很简单，为什么会发生这种情况？

Answer 1

在一本书的索引中，为什么不包括像“the”或“and”这样的常用词呢？因为它会匹配书中的每一页，而且在索引中查找值没有用。你还不如从头到尾读完这本书的所有页。

如果MySQL估计该条件将匹配很大比例的行，MySQL将不会使用索引。确切的阈值没有文档，但根据我的经验，它大约是表的20-25%。请注意，MySQL索引统计信息并不总是完美的；它们是基于抽样数据的估计。

在第二个查询中，日期的范围条件略宽。因此，它匹配更多的行。这可能刚好超过阈值，所以MySQL决定不使用i_event2索引。

select count(*) from event USE INDEX (i_event_2)
where survey_id = 158
  and event_datez>'2019-10-29 00:00:00' 
  and event_datez<'2019-11-28 23:59:59' ;

ALTER TABLE event ADD INDEX i_event_survey_datez (survey_id, event_datez);