MySQL使用FORCE INDEX出现异常耗时与索引失效问题咨询

1. 为什么使用FORCE INDEX (idx_host)时，MySQL没有使用指定索引？ #

MySQL的行构造器匹配语法(col1, col2) IN ((val1, val2), ...)要使用索引，要求索引的列前缀必须和行构造器的列顺序、列数量完全匹配。你指定的idx_host是仅包含host_name的单列索引，而你的查询条件是(ip, host_name)的双列组合匹配，该索引根本无法覆盖查询条件的前置列ip，就算强制指定也没有办法被优化器选用，因此最终还是走了全表扫描。

2. 为什么走全表扫描的FORCE INDEX (idx_host)执行速度反而比使用unq_ip_host索引更快？ #

核心差异是IO类型的开销差异：

• 走unq_ip_host联合索引时，MySQL 5.7对行构造器IN的3000个组合会转换为多个range条件查询，每次查询需要先定位二级索引的对应条目，再通过主键ID回表查询整行数据（因为你用了select *需要读取全字段），这一过程产生的是随机IO。普通机械盘随机IO每秒仅能支持数百次查询，3000次左右的随机回表开销远大于预期，最终拉满了总耗时。
• 全表扫描是顺序IO，28万行的小表做全表顺序读取，机械盘每秒可以轻松处理十万行以上，总耗时反而远低于随机IO带来的开销。

另外EXPLAIN给出的rows、filtered都是统计预估数值，和实际执行的开销存在偏差，不能直接作为耗时判断的唯一依据。

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