MySQL百万级数据表or查询优化

目前公司的订单表有100多万条，使用订单号查询数据时，所需时间大多要10-30秒不等，查看了慢查询日志，发现有的订单查询竟然耗时65秒

我查看了原有的查询语句，发现where后面跟了or查询，虽然3个or都索引，使用explain分析查询结果，发现要扫描近70万行，几乎是全盘扫描一遍，只为获取最多3条数据，效率实在是低下

这3个字段均设置了索引，但or在这个语句中，使索引失效了（主要看最后几行）

使用union all代替or查询，也就是说把3个字段的查询分别做查询，将结果使用union all连接在一起，这样单次查询可以用到索引，效率大大提高

先看一下分析结果

简要的sql语句，查询结果不超80ms

今天听到一种说法，说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.

---