【mysql优化实战】第三章 单表多条件查询的速度以及explain分析

在第二章中通过单表单条件，观察了100万条记录的情况下，各种查询的查询速度和explain的分析。
这一章，将通过单表多条件的情况下，继续观察100万条记录的情况下，查询的效果，比如：

在上一章中，我们可以看到

select passwd from big_table where uid=102194

会瞬间查询出结果，那么，如果现在在条件中再添加一个，会是怎样的结果呢？比如

select passwd from big_table where uid=102194 and sex = 0

这一章，就来测试一下，并且来观察explain的结果，进行分析。这一章中，不在使用select *显示所有字段了。因为在上一章已经说明了select * 会影响一些速度，而且也建议使用*。

针对多条件查询，使用了如下的查询语句来作为例子，观察了其查询速度

-- 索引 and =号的
select passwd from big_table a where uid = 102194 and sex = 1 【1】【耗时：0.000s】
select passwd from big_table a where sex = 1 and uid=102194   【1】【耗时：0.000s】
# 索引 or =号的
select passwd from big_table a where uid=102194 or sex = 1    【2】【耗时：1.783s】#速度慢

-- 非索引 and =号的
select passwd from big_table a where passwd='cbc17949e5259a2433873b5c11ee7b59' and sex = 1【3】【耗时：1.493s】#速度慢
select passwd from big_table a where passwd='cbc17949e5259a2433873b5c11ee7b59' and sex in(1) 【4】【耗时：1.749s】#速度慢
select passwd from big_table a where passwd='cbc17949e5259a2433873b5c11ee7b59' and exists( select passwd from big_table where uid=102194) 【5】【耗时：1.727s】#速度慢
explain select passwd from big_table a where uid='102194' and exists( select passwd from big_table where sex = 0) 【耗时：0.003s】 
-- 非索引 or =号的
select passwd from big_table a where passwd='cbc17949e5259a2433873b5c11ee7b59' or sex = 0 【6】【耗时：1.619s】#速度慢
select passwd from big_table a where passwd='cbc17949e5259a2433873b5c11ee7b59' or sex in(1) 【7】【耗时：1.645s】#速度慢
select passwd from big_table a where passwd='cbc17949e5259a2433873b5c11ee7b59' or exists( select passwd from big_table where uid=102194) 【8】【耗时：1.727s】#速度慢

分别执行上面查询语句后，发现只有使用了主键索引uid并且用了and的条件的查询，速度才是可以接受的（也就是只有【1】）。其他的都会查询速度慢。

至于为什么，我们现在通过explain来看一下：

explain select passwd from big_table a where uid = 102194 and sex = 1 

图1
结论：type和ref都为const，并且rows为1，key为primary说明使用了主键索引，出现这几个内容说明执行速度可以接受，无需优化。
 

explain select passwd from big_table a where uid=102194 or sex = 1 

图2
结论：type为ALL和rows为994227说明是全文检索，扫描了994227条记录找到结果，当type为ALL时，那就因该必须进行优化才行。可以看出当使用or以后条件中的主键索引uid失效了。

 

explain select passwd from big_table a where passwd='cbc17949e5259a2433873b5c11ee7b59' and sex = 1【3】

图3
结论：图3与图2进行explain后的结果是一样的。所以同理需要优化。and两边的条件因为没有主键索引，所以key没有primary，变成了全文检索。

 

explain select passwd from big_table a where passwd='cbc17949e5259a2433873b5c11ee7b59' and sex in(1) 【4】

图4
结论：explain结果与图3相同。同样需要优化

 

#注意下面这3个exsits相关的sql，注意观察为什么【5.1】查询速度慢，而【5.2】和【5.3】查询速度块
explain select passwd from big_table a where passwd='cbc17949e5259a2433873b5c11ee7b59' and exists( select passwd from big_table where uid=102194) 【5.1】

explain select passwd from big_table a where uid='102194' and exists( select passwd from big_table where uid=102194) 【5.2】

explain select passwd from big_table a where uid='102194' and exists( select passwd from big_table where sex = 0) 【5.3】

explain select passwd from big_table a where uid='102194' or exists( select passwd from big_table where sex = 0) 【5.4】

图1
根据id，我们知道这条查询的顺序是先执行子查询，在执行主查询。子查询的type和ref都为const，rows为1，说明没问题，因为子查询的条件为主键索引。
通过type为ALL，rows为994227可以看出慢的原因出现在主查询上，进行了全文检索。索引需要优化
 图2
这个查询语句因为主查询和子查询type和ref都是const，rows为1，所以无需优化。从查询速度上也能看出来。

图3
从图3上看，id为2的子查询的type为ALL，rows为994227，第一感觉应该是查询速度会慢，但是实际执行发现查询速度很快。这事为什么呢？这是因为我们的多个条件之间使用了and，然后主查询用到了主键索引uid，mysql内部进行了优化，所以速度快。这点上要注意。有全表扫描的子查询时，不一定查询的速度就一定慢。
 

explain select passwd from big_table a where uid='102194' or exists( select passwd from big_table where sex = 0) 【5.4】

图4

图4与图3相比仅仅把and变为了or，结果从explain上可以看出整个查询效率全变了。type都是ALL，都进行了全文检索，自然执行效率就低下，查询时间就长了，需要优化了。

explain select passwd from big_table a where passwd='cbc17949e5259a2433873b5c11ee7b59' or sex = 0 【6】
explain select passwd from big_table a where passwd='cbc17949e5259a2433873b5c11ee7b59' or sex in(1) 【7】
explain select passwd from big_table a where passwd='cbc17949e5259a2433873b5c11ee7b59' or exists( select passwd from big_table where uid=102194) 【8】

上面这几个就不在多讲了，因为都没用到主键索引，而且还是用了or，所以都需要优化，可以自行执行看下explain的结果。

----------------------------------------------------------------------------------------------------------------------------------------------------
最后，进行一个本章总结：
1.当多条件中出现or时，条件中的索引主键将失效，有可能会导致查询速度变慢
2.当多条件时，不一定子查询中的type为ALL，执行速度就慢，还需要考虑用的是and还是or，是否有主键索引，如上图3

下一章，第四章，我们将看一下多表查询的效率，并且看一下用explain怎么来分析。