首页   注册   登录
V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
Coding
V2EX  ›  PHP

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

  •  
  •   echo404 · 2018-12-03 23:10:52 +08:00 · 1489 次点击
    这是一个创建于 376 天前的主题,其中的信息可能已经有所发展或是发生改变。

    最近遇到这么一条查询:
    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";
    分析结果如下: Imgur 整个表大概 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;
    分析结果如下: Imgur 这个 SQL 语句使用联合索引,一共扫描了 8.5w 行,却只花费了 0.047 秒。
    为什么扫描行多的 SQL 查询反而比扫描行数少的 SQL 查询花费时间更短? MySQL 内部的优化机制又为什么会选择第一个 SQL 中的索引进行查询呢? google 一下也没有找到想要的答案,有大佬能解答一下这是为什么么?

    4 回复  |  直到 2019-03-12 15:20:26 +08:00
        1
    wdlth   2018-12-03 23:39:27 +08:00   ♥ 3
    index merge 是合并索引,就是说其实合并了两个索引的结果,像 status 状态这种区分度很少的字段加索引没什么用,几乎等同与全表扫描,自然就慢了……

    MySQL 5.6 版本里面新加了一个参数 eq_range_index_dive_limit,这个会影响 IN 的查询。
        2
    glacer   2018-12-04 00:46:49 +08:00
    你第二次是命中缓存了... 在 SQL 中加上 SQL_NO_CACHE 禁用缓存试试。
        3
    airect   2018-12-04 09:22:30 +08:00
    MySQL 是基于代价的查询优化器,OPTIMIZER_TRACE 看一下。https://github.com/airect/journal/blob/master/MySQL%20%E7%9A%84%20OPTIMIZER_TRACE.md
        4
    cs8814336   277 天前   ♥ 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 是覆盖索引的意思, 就是在通过非聚集索引查询的时候就过滤掉了部分数据,不需要再去聚集索引查询再检索过滤.
    关于   ·   FAQ   ·   API   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   2208 人在线   最高记录 5043   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.3 · 23ms · UTC 05:35 · PVG 13:35 · LAX 21:35 · JFK 00:35
    ♥ Do have faith in what you're doing.