MySQL 解析器到底是如何选择索引的？

[echo404]

最近遇到这么一条查询：
SELECT * from t_o2o_tran_pay where merchant_id in (3719) AND store_id in (1020,1001,1024,1027,1036,1013,1035,1038,1030,1082,1094,1021,1048,1026,1028,1046,1042,1025,1090,1017,1152,1043,1032,1040,1022,1045,1087,1044,1088,1154,1050,1086,1033,1049,1153,1151,1039,1098,1099,1023,1015,1031,1029,1002,1096,1016,1037,1014,1008,1060,1150,1034,1010) AND trade_status = 1 AND pay_total > 0 AND order_time BETWEEN "2018-11-30 00:00:00" AND "2018-11-30 23:59:59";
分析结果如下: 整个表大概 4000W 数据，这个 SQL 扫描 4.5W 行，花费 40 多秒。
然后，我强制使用了另一个索引，SQL 如下：
SELECT * from t_o2o_tran_pay force index(rpt_query) where merchant_id in (3719) AND store_id in (1020,1001,1024,1027,1036,1013,1035,1038,1030,1082,1094,1021,1048,1026,1028,1046,1042,1025,1090,1017,1152,1043,1032,1040,1022,1045,1087,1044,1088,1154,1050,1086,1033,1049,1153,1151,1039,1098,1099,1023,1015,1031,1029,1002,1096,1016,1037,1014,1008,1060,1150,1034,1010) AND trade_status = 1 AND pay_total > 0 AND order_time BETWEEN "2018-11-30 00:00:00" AND "2018-11-30 23:59:59" limit 20;
分析结果如下： 这个 SQL 语句使用联合索引，一共扫描了 8.5w 行，却只花费了 0.047 秒。
为什么扫描行多的 SQL 查询反而比扫描行数少的 SQL 查询花费时间更短？ MySQL 内部的优化机制又为什么会选择第一个 SQL 中的索引进行查询呢？ google 一下也没有找到想要的答案，有大佬能解答一下这是为什么么？

[wdlth]

index merge 是合并索引，就是说其实合并了两个索引的结果，像 status 状态这种区分度很少的字段加索引没什么用，几乎等同与全表扫描，自然就慢了……

MySQL 5.6 版本里面新加了一个参数 eq_range_index_dive_limit，这个会影响 IN 的查询。

[glacer]

你第二次是命中缓存了... 在 SQL 中加上 SQL_NO_CACHE 禁用缓存试试。

[airect]

MySQL 是基于代价的查询优化器，OPTIMIZER_TRACE 看一下。https://github.com/airect/journal/blob/master/MySQL%20%E7%9A%84%20OPTIMIZER_TRACE.md

[cs8814336]

表结构要贴.... 首先, 你这个是一个 range 查询(in 是一种特殊的 range 查询, 两边值相等) ,会通过 range 优化器优化, 考虑使用 index dive(精确,会进去 b+tree 看节点数量,但是费时) 还是 index statistics.(通过索引的大致数量估算, analyse table 会精确这个数字, 当 range 数量过多会用他).(eq_range_index_dive_limit 决定了 range 数量大多少会用后者) 估算出来一个值,综合地估算了代价,包括了 io 等各种(还有是非聚集索引到聚集索引的代价), 来决定他使用哪个索引, 甚至直接走不命中的聚集索引,就是全表扫描. 不幸的是,优化器用了一个坏索引.

从你 force_index 来看, force_index 是不会走 range optimizer 了, 里面有一个 index condition 是覆盖索引的意思, 就是在通过非聚集索引查询的时候就过滤掉了部分数据,不需要再去聚集索引查询再检索过滤.