mysql脚本优化实例分享——规避“隐式类型转换”

TIM图片20180529153056.png

内外表exists关联的一条sql,所有字段均建立了索引,外表跟内表是一对多的关系,外表数据量大概在150w,内表的数据量大概在260w,初次执行的时候执行了2分多钟没有返回结果。

查看执行计划,如图中的左侧部分,发现本来是“主外键关联”关系的字段执行计划的类型居然是range(唯一索引等式关联执行计划的类型应该是ref或者const),且内表居然扫描了399284行(exists的语意是拿外表之前过滤后的结果集逐条与内表做关联)。

一开始竟然怀疑是主从库的配置不一样(生产环境只有从库的查询权限),询问DBA得到的反馈是配置是一样的(也是哈,不然主库发生异常,哪敢切从库呢)。

坐下来再次分析sql,一时竟然没有头绪,但是可以肯定的是内表worksheet_code字段应该是没走索引。于是翻看表结构,WTF——居然是varchar类型!这样就很好的解释了执行计划。

那如何去优化这条sql呢?首先想到的自然是把内表的类型修正为bigint,与外表保持一致,但是这种表结构的变更,必然会引起线上系统的报错;而目前只是帮业务导出一批数据,代价太大(后期必须这么做,这个是长久方案)。其次想到的是增加一个bigint的字段,然后把值更新成worksheet_code,这个代价也不小。后来经高人提点——“为啥总是盯着内表字段类型转换呢?”——窗户纸被捅破,是啊,之所以效率低下还不是发生了内表的隐式类型转换嘛

经过优化,sql在2秒多钟就查出了结果,优化过后的执行计划如图右侧部分。

后面还跟高人探讨了exists的使用场景:exists适用于外表结果集小于内表结果集的场景,否则,应该使用in代替exists结果待验证。其实内外表关联的时候,大多是一对多的关系,除非纯粹依靠内表的条件做过滤,否则通常情况下外表的过滤条件结果集一定会小于内表
另外,根据数据库设置情况,or条件的索引不一定会生效,推荐使用in。如本例中原来使用的是opt_type='6' or opt_type='5'.

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

推荐阅读更多精彩内容

  • 转 # https://www.cnblogs.com/easypass/archive/2010/12/ 08/...
    吕品㗊阅读 9,831评论 0 44
  • 转载自:https://www.cnblogs.com/easypass/archive/2010/12/08/1...
    SkTj阅读 559评论 0 1
  • 一、MySQL架构与历史 A.并发控制 1.共享锁(shared lock,读锁):共享的,相互不阻塞的 2.排他...
    ZyBlog阅读 19,883评论 3 177
  • ——有感于中国达人秀董老太为亡夫真情献唱 到了这样的年龄 许多人已遗忘爱情 你依旧怀抱吉它 重复着初相遇时的命题 ...
    律墨阅读 339评论 23 32
  • 昨晚与朋友x聊天,向我诉说她的婆媳问题,絮絮叨叨一大堆。我感到好笑的同时又十分诧异,我的印象中她是个大大咧咧男孩子...
    瑄和冉阅读 325评论 0 0