当前位置: 首页>数据库>正文

执行mysql单表查询语句慢 mysql单表查询性能

在第二章中通过单表单条件,观察了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

执行mysql单表查询语句慢 mysql单表查询性能,执行mysql单表查询语句慢 mysql单表查询性能_主键,第1张

图1

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

 

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

执行mysql单表查询语句慢 mysql单表查询性能,执行mysql单表查询语句慢 mysql单表查询性能_bc_02,第2张

图2

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

 

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

执行mysql单表查询语句慢 mysql单表查询性能,执行mysql单表查询语句慢 mysql单表查询性能_bc_03,第3张

图3

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

 

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

执行mysql单表查询语句慢 mysql单表查询性能,执行mysql单表查询语句慢 mysql单表查询性能_主键_04,第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】

执行mysql单表查询语句慢 mysql单表查询性能,执行mysql单表查询语句慢 mysql单表查询性能_bc_05,第5张

图1

根据id,我们知道这条查询的顺序是先执行子查询,在执行主查询。子查询的type和ref都为const,rows为1,说明没问题,因为子查询的条件为主键索引。

通过type为ALL,rows为994227可以看出慢的原因出现在主查询上,进行了全文检索。索引需要优化

执行mysql单表查询语句慢 mysql单表查询性能,执行mysql单表查询语句慢 mysql单表查询性能_数据库_06,第6张

 图2

这个查询语句因为主查询和子查询type和ref都是const,rows为1,所以无需优化。从查询速度上也能看出来。

执行mysql单表查询语句慢 mysql单表查询性能,执行mysql单表查询语句慢 mysql单表查询性能_执行mysql单表查询语句慢_07,第7张

图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】

执行mysql单表查询语句慢 mysql单表查询性能,执行mysql单表查询语句慢 mysql单表查询性能_主键_08,第8张

图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怎么来分析。

 


https://www.xamrdz.com/database/6xg1928739.html

相关文章: