MySQL的查询优化(一)

“ 在上一篇关系型数据库之MySQL的文章中，我们介绍了什么是关系型数据库以及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语句存在索引失效的情况，%的位置决定了索引能否生效，同时在关系型数据库之mysql我们也说过，对于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 有什么作用？为什么要使用？