“ 在上一篇的文章中,我们介绍了什么是关系型数据库以及MySQL查询优化的大体思路,那今天我们就针对具体的语句来看一下,如何优化MySQL的查询语句。”

关于MySQL的查询优化,我想大家或多或少的都有一些心得和想法,但是你是否真正的去研究过哪些SQL语句值得我们去优化以及优化的效果如何呢?

语句分析

1.在最初的阶段,我们用的最多的就是条件查询where了,通常我们要适当在where的字段上增加索引或者联合索引

这里我们来看一下where条件如何优化,由于本人数据库数据量不多,根据查询时间来判断SQL语句性能好坏有些不客观,这里我是用explain来分析SQL的执行计划。

首先我们看一下where条件执行效果。

这里我选择一个普通的字段sort来作为条件来查询数据(此字段此时没有添加索引)

explain select * from `xcx_know_tree` where sort =1 ;看一下效果,type是all也就是全表扫描,也就是把39行全部都扫描了,而实际上sort等于1的数据只有七条。(type有如下几个类型ALL, index,  range, ref, eq_ref, const, system, NULL。从左到右,性能从最差到最好,也就是ALL是性能最差的。具体的我们暂时就不谈了)

然后我们添加索引看一下效果:

这个时候type是ref了,而且扫描行数就是七行,命中率也就100%了。在数据量及其少的情况下,他们之间速度并没有特别明显的差距,但是可以预知,数据量过大的时候,第一种耗时程度绝对会大大增加。

这里我们需要注意下,既然有等于号,也肯定会使用不等于号,在使用where查询的时候,尽量不要用"!="和"<>"符号,因为使用不等于后,MySQL会放弃使用索引,而进行全表扫描。效果图如下

2.上一条我们在说使用where条件的时候谈到尽量不用 不等于 符号。但是有人会问,如果必须要用呢?有一个字段类型是1到10,我就要取不等于1的,难道我用and拼吗?大家估计都不会这样去做。首先大家需要知道,MySQL查询的数据量超过全表的40%时(网上有说30%的,大致意思相同),就不会采用索引了。也就是或如果不等于1的数据量过大,索引其实本身就是无效的。其次如果不等于1的数量非常少,可以考虑新建一个字段,将不等于1和等于1区分开,在此字段上增加索引,利用该字段进行查询。这里需要大家根据自己的实际业务场景去分析,肯定能找到查询效率高的方案,这一条优化方案其实也是上一条的扩展。

3.在第二条中我们提及到and,说到and我们就不得不提一下or。在MySQL中如果or语句两边的字段不是都增加了索引,那么即使一方有索引,查询仍会成为全表扫描。来看一下例子

这个查询里面sort我们是增加过索引的,但是status我们没有增加索引,执行计划中我们可以看到type变成来ALL,也就是性能最差的那种。这种情况有两种处理方式:

其一是:如果是不同的两个字段,我们给他们都加上索引,这里因为表中ref_id存在上索引,直接用来看效果(增加索引也是有成本的,视情况而定)

这里type变成了index_merge,也就是索引合并,解决了全表扫描的糟糕情况,但是同样增加了维护索引开销。

其二就是:如果是相同的字段,使用union all。(如果or两边连接的是同一字段,即使字段加了索引也会失效)。

union all 就是执行两条SQL语句,然后组合在一起,它的执行计划实际上是两条SQL。但是总共扫描行数是少于全表的行数的。

4.谈到union all 我们也要说一下 union 。Union因为要进行重复值扫描,所以效率低。如果合并没有刻意要删除重复行,那么就使用Union All。

5.模糊查询我们也要慎用,在的题库中我们谈及到,like语句存在索引失效的情况,%的位置决定了索引能否生效,同时在我们也说过,对于Text,LongText这种类型的字段,我们应该使用使用前缀来索引。或者考虑考虑如何去使用全文检索,在MySQL5.7中好像索引类型有全文检索的,大家可以去研究下,这里只给出思路。

6.in和not in大家是否经常使用?使用in和not in同样会使索引失效。如果确定且有限的集合时,我们可以使用in以及not in但是,如果不确定,我们就要考虑使用not exists和exists了。

一下子说太多其实也不好,说一点如果有灵感就赶快去实验实验,在后续的工作学习中加以运用,如果没有的话,还请各位帅哥美女们,期待下一篇文章。

今天小程序更新的题库:

1.为什么set,list,map不实现cloneable和serializable接口

2.AQS的实现原理

3.什么是线程局部变量?

4.Java 中应该使用什么数据类型来代表价格?

5.3*0.1 == 0.3 将会返回什么?true 还是 false

6.JVM 选项 -XX:+UseCompressedOops 有什么作用?为什么要使用?