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

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

  •  
  •   echo404 · 76 天前 · 830 次点击
    这是一个创建于 76 天前的主题,其中的信息可能已经有所发展或是发生改变。

    最近遇到这么一条查询:
    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 一下也没有找到想要的答案,有大佬能解答一下这是为什么么?

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

    MySQL 5.6 版本里面新加了一个参数 eq_range_index_dive_limit,这个会影响 IN 的查询。
        2
    glacer   76 天前
    你第二次是命中缓存了... 在 SQL 中加上 SQL_NO_CACHE 禁用缓存试试。
        3
    airect   76 天前
    MySQL 是基于代价的查询优化器,OPTIMIZER_TRACE 看一下。https://github.com/airect/journal/blob/master/MySQL%20%E7%9A%84%20OPTIMIZER_TRACE.md
    关于   ·   FAQ   ·   API   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   3239 人在线   最高记录 4346   ·  
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.3 · 17ms · UTC 04:21 · PVG 12:21 · LAX 20:21 · JFK 23:21
    ♥ Do have faith in what you're doing.
    沪ICP备16043287号-1