查看: 5176|回复: 2

[原创] 多表join后的排序为什么不走外表的(主键)索引呢?

[复制链接]
认证徽章
论坛徽章:
4
优秀写手
日期:2013-12-18 09:29:102014年新春福章
日期:2014-02-18 16:49:31马上有钱
日期:2014-02-18 16:49:31目光如炬
日期:2018-04-15 22:00:00
发表于 2018-4-9 17:24 | 显示全部楼层 |阅读模式
15pub币
本帖最后由 kimmy257 于 2018-4-9 17:32 编辑

原语句我还是贴一下吧:
SELECT
        psod.*,
        pp.provider_name AS pos_provider_name,
        pso.*,
ppi.NAME AS partner_name,
        pc.*,
        ppv.ver_name,
        ppv.ver_value
FROM
        pos_sync_order_detail psod
        INNER JOIN pos_callback pc ON psod.pos_callback_id = pc.id
        INNER JOIN pos_sync_order pso ON psod.pos_sync_order_id = pso.id
        LEFT JOIN pos_provider_version ppv ON pc.pos_version_id = ppv.id
        LEFT JOIN pos_provider pp ON pc.pos_provider_id = pp.id
        LEFT JOIN pos_platform_info ppi ON pc.partner_no = ppi.partner_no
WHERE
        1 = 1
        order by psod.id desc
        LIMIT 0,
        20;

情况描述如下:

6个表的join连接,外表14W行数据,内表做链接的基本都是主键,或者是建有索引的外键,完成过滤之后,最后会按照外表的主键列id做order by;
但最后这个order by却不走索引,导致产生了temporary和filesort,执行时间从0.03s爆增到2s;
看了一下执行计划,最后那2个表因为只有4行,所以没有走索引,explain的解释也是使用了BNL。
PS:除开那2个只有4行的表以外,其他表都是30W行左右,所以14W行这个已经是小表了。


现在有2种措施
1.强制最后的2个小表都走索引,可以消除最终的filesort;
2.强制外表走主键索引,也可以消除filesort;

疑问
1.准备采用第二种措施,强制外表走索引,这样做会不会有什么不好的影响?
2.为什么2个外表使用BNL之后,会导致最后的排序不走索引呢?或者说为什么驱动表的order by没有走(主键)索引,产生了filesort!

执行计划如图所示
图1是不加强制索引
图2是外表加强制索引
图3是2个小表加强制索引


不加强制

不加强制

对外表强制

对外表强制

对小表强制

对小表强制
论坛徽章:
6
蜘蛛蛋
日期:2013-03-30 23:38:44优秀写手
日期:2015-02-01 06:00:14优秀写手
日期:2015-02-12 06:00:15暖羊羊
日期:2015-04-15 09:25:35弗兰奇
日期:2018-04-07 15:48:37ITPUB18周年纪念章
日期:2018-09-17 10:09:49
发表于 2018-4-11 15:27 | 显示全部楼层
1.pos_sync_order_detail  这个表主键并没有用做关联字段,mysql选择不走主键也是正常的。这个表在pos_callback_id,pos_callback_id 没有索引吗? 没有加上试试
2.是不是应该是pos_sync_order_detail   没有做索引才导致后面两个表用BNL,用BNL 时有由于join buffer不够才导致buffer 不够容纳所有数据导致了filesort?

使用道具 举报

回复
认证徽章
论坛徽章:
4
优秀写手
日期:2013-12-18 09:29:102014年新春福章
日期:2014-02-18 16:49:31马上有钱
日期:2014-02-18 16:49:31目光如炬
日期:2018-04-15 22:00:00
发表于 2018-4-13 14:32 | 显示全部楼层
学徒伟 发表于 2018-4-11 15:27
1.pos_sync_order_detail  这个表主键并没有用做关联字段,mysql选择不走主键也是正常的。这个表在pos_call ...

psod表上的确只有一个主键索引,
我试了下就算给pos_callback_id加上索引也一样走BNL,而且最后没有排序

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

【有奖讨论】解决存储挑战了解一下
奖品:米家车载空气净化器 、米家声波电动牙刷 、小米运动蓝牙耳机

在数字经济时代,井喷式增长的数据,在释放大量商业价值的同时,也随之对企业的IT基础设施带来了不容忽视的挑战!如何存储、管理、使用这些数据呢?这是一条比以往更艰难的路~

活动时间:9月20日-10月11日

参与讨论>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表