最近遇到这么一条查询：
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 一下也没有找到想要的答案，有大佬能解答一下这是为什么么？
This topic created in 2748 days ago, the information mentioned may be changed or developed.
4 replies • 2019-03-12 15:20:26 +08:00
 |
1
wdlth Dec 3, 2018  3
index merge 是合并索引，就是说其实合并了两个索引的结果，像 status 状态这种区分度很少的字段加索引没什么用，几乎等同与全表扫描，自然就慢了……
MySQL 5.6 版本里面新加了一个参数 eq_range_index_dive_limit，这个会影响 IN 的查询。 |
 |
2
glacer Dec 4, 2018
你第二次是命中缓存了... 在 SQL 中加上 SQL_NO_CACHE 禁用缓存试试。
|
 |
3
airect Dec 4, 2018
MySQL 是基于代价的查询优化器，OPTIMIZER_TRACE 看一下。https://github.com/airect/journal/blob/master/MySQL%20%E7%9A%84%20OPTIMIZER_TRACE.md
|
 |
4
cs8814336 Mar 12, 2019 1
表结构要贴.... 首先, 你这个是一个 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 是覆盖索引的意思, 就是在通过非聚集索引查询的时候就过滤掉了部分数据,不需要再去聚集索引查询再检索过滤. |
Select Language