java程序员必须知道的mysql优化的知识

前言：

mysql优化是java开发人员必备的技能之一，虽然可能比不上专业的DBA，但是一些常用的以及基本的mysq优化的知识还是需要知道，今天从总结一些常用的mysql优化的知识，并且是从实战的过程中来使用这些优化的技巧，简单、好用、且干货满满，在阅读本篇文章之前，最好是了解mysql执行计划的一些知识，大家可自行查找资料。直接上干货

整个优化分为如下：

1. 需要在表中建立索引
2. 不要在索引上做任何的操作
3. 不等号（!= 、<>）要谨慎使用
4. 尽量使用覆盖索引
5. like查询要注意
6. 字符串需要加引号
7. UNION代替OR
8. NULL或者NOT NULL的影响
9. 尽量全值匹配
10. 最佳左前缀法则
11. 范围条件放最后面

下面我们一条条的来详细解读

1、需要在表中建立索引

建立索引当然要根据自己的业务情况，可以在使用频率高的字段中建立索引，也可以创建多个索引、联合索引。

我们先来看下以前我们同事设计的一张表，这个表设计存在一些问题，我们一个个来看

（1）表结构（varchar长度全是180）

（2） 索引情况（无索引）

 （3）数据量（75w）

 （4）查询结果需要多长时间

 也还好是吧，0.7秒多就完成了查询

（5）看下它的执行计划

type=all全表扫描，接下来我们给它新建一个索引，然后对比一下

（6）新建索引

（7）查询耗时

 

查询的耗时明显已经不在一个数量级上，随着表越来越大，差距会越大 

 （8）加入索引之后的执行计划

可以看出type=ref，好了这就是建立索引的好处，下面的测试过程可能就不会向上面一样详细了，我就直接对比优化前和优化后的执行计划了，其中性能从大到小的排列是system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

2、不要在索引上做任何的操作

 上面虽然uid是索引，但是使用left导致索引失效，type=all

3、不等号（!= 、<>）要谨慎使用

type=all导致索引失效

 4、尽量使用覆盖索引

上面虽然在索引的上使用了!=号，但是使用了覆盖索引，所以type=index比type=all要快很多，如果需要查询多个字段，可以在使用频率较高的字段使用联合索引

5、like查询要注意

经常听到说不要使用like查询，但是在业务中我们做模糊查询的时候、需要使用like怎么办，看下面

like查询的确会导致type=all导致全表查询，如何优化，尽量将%写后面

 

如果实在是业务需求，需要将%写前面，那我们可以使用覆盖索引

 

 使用覆盖索引可以优化type=index

6、字符串需要加引号

字符串不加引号导致索引失效，全表扫面

 

加了引号之后type=ref

 7、UNION代替OR

看下使用union的执行计划

 

union中有一个是type=ref，所以效率肯定要好点，再看看如果or连接的都是索引呢

再看看union

 

union的type是ref而or是range，根据上面的排序，ref的性能是比range要好的

 8、NULL或者NOT NULL的影响

再看下面这个执行计划

 

查询条件 is null时索引不失效，为 is not null时索引失效。当这种情况下，又想要使用is not null 时同样可以使用覆盖索引优化，具体效果我就不贴上来了，可自行测试

9、尽量全值匹配

就说说如果建立了多个索引或者联合索引时，查询条件应当尽可能的使用多个索引

（1）使用一个索引的查询

（2）使用两个索引的查询条件

 

他们的执行计划的type都是为ref的

10、最佳左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

先来看下表结构

 上面的一张表建了一个联合索引，索引的顺序为uid、style_no、goods_num

当执行下面的执行计划时，看下效果

type=all说明没有使用索引，这就是没有使用最佳左前缀法则，当加入uid时，效果如下

 

 type=ref，同样的道理当去掉style_no时，这是它的索引只有uid生效，而后面goods_num则失效，具体可以看key_len的长度判断（执行计划的知识），这里就不做演示了，可自行操作

11、范围条件放最后面

中间有范围查询会导致后面的索引列全部失效

uid使用了范围查询，所以导致索引全部失效（这里的中间的顺序表示的是索引的顺序，并不是where后面的顺序）比如即使把uid写道where的最后面，还是会导致全部失效

 同样如果第二个索引style_no使用了范围，则会导致goods_num索引也会失效，可自行操作

 

sql语句的优化就暂时先讲到这里，虽然sql优化能提升查询的效率，但是也会存在瓶颈，这就需要我们在设计表的时候要充分思考、不然sql语句再怎么优化可能也达不到最佳的效果。比如我们上面的表的信息中就可以看出varchar类型的长度全部是180（不管字段需要多长）这是非常低级的错误