MySQL中使用OR连接查询条件不会让索引失效

MySQL中使用OR连接查询条件不会让索引失效,第1张

今天听到一种说法,说OR会使索引失效,最好使用UNION代替。查了一下发现的确有些文章这么说。但总觉得没什么道理。直觉上觉得虽然or连接的查询条件不能使用联合索引,但感觉两个单独的索引是可以用的。看了一些文章,感觉一直很疑惑。

于是试了一下,发现果然如此。

这里的type项是index_merge。搜索后发现是MySQL5.0后的新技术,索引合并。index merge 技术简单说就是在用OR,AND连接的多个查询条件时,可以分别使用前后查询中的索引,然后将它们各自的结果合并交集或并集。当然具体是否使用index merge,优化器会自己选择,比如and连接时有联合索引,或干脆全表查询就很快,就没必要使用它了。

具体可以查阅文档: https://dev.mysql.com/doc/refman/5.6/en/index-merge-optimization.html

还是很有意思的。

可能因为mysql这个东西太复杂,感觉sql优化都快成玄学了。网上流传了很多“奇技淫巧”,让人真假难辨。我觉得可能是过去mysql优化不是很好时,大家有一些优化的技巧,但随着mysql更新优化,现在已经成为过时甚至错误的做法。但这些说法还在流传,造成了我这样的新手的困惑。

所以看来小白要学技术,还是要踏实点多看官方文档,多亲自实践,少看博客,少道听途说。

网上有很多人都在谈论or与in的使用,有的说二者没有什么区别,其实不然,估计是测试做的不够,其实or的效率为O(n),而in的效率为O(log2n),当基数越大时,in的效率就能凸显出来了。

有人做了这么一组实验(测试库数据为1000万条记录):A组分别用or与in查询3条记录,B组分别用or与in查询120条记录,C组分别用or与in查询500条记录,D组分别用or与in查询1000条记录.

第一种情况,目标列为主键的情况,4组测试执行计划一样,执行的时间也基本没有区别。

A组or和in的执行时间: or的执行时间为:0.002s in的执行时间为:0.002s

B组or和in的执行时间: or的执行时间为:0.004s in的执行时间为:0.004s

C组or和in的执行时间: or的执行时间为:0.006s in的执行时间为:0.005s

D组or和in的执行时间: or的执行时间为:0.017s in的执行时间为:0.014s

第二种情况,目标列为一般索引的情况,4组测试执行计划一样,执行的时间也基本没有区别。

A组or和in的执行时间: or的执行时间为:0.002s in的执行时间为:0.002s

B组or和in的执行时间: or的执行时间为:0.006s in的执行时间为:0.005s

C组or和in的执行时间: or的执行时间为:0.008s in的执行时间为:0.008s

D组or和in的执行时间: or的执行时间为:0.020s in的执行时间为:0.019s

第三种情况,目标列没有索引的情况,4组测试执行计划就不一样,执行的时间也有了很大的区别。

A组or和in的执行时间: or的执行时间为:5.016s in的执行时间为:5.071s

B组or和in的执行时间: or的执行时间为:1min 02s in的执行时间为:5.018s

C组or和in的执行时间: or的执行时间为:1min 50s in的执行时间为:5.010s

D组or和in的执行时间: or的执行时间为:6min 13s in的执行时间为:5.047s

结论:

in和or的效率,取决目标条件列是否有索引或者是否是主键,如果有索引或者主键性能没啥差别,如果没有索引,in的性能要远远优于or.

项目中可能回遇到or 与  and  连用,我认为or连接的前后是两个条件.所以不能一个条件分开写.

比如 一张表a  有 user_code,shared,del,company_id四个字段  我想查shared=1 且 del=0 或者del=0的数据 且company_id=1, shared是共享的意思,不区分公司,那么可以这样写

例  select  user_code from a where shared=1  and del=0 or del=0


欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/zaji/8363157.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2023-04-15
下一篇 2023-04-15

发表评论

登录后才能评论

评论列表(0条)

保存