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）

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

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

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

（3）数据量（75w）

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

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

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

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

（5）看下它的执行计划

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

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

（6）新建索引

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

（7）查询耗时

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

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

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

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

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

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

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

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

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

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

type=all导致索引失效

4、尽量使用覆盖索引

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

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

5、like查询要注意

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

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

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

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

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

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

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

6、字符串需要加引号

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

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

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

加了引号之后type=ref

7、UNION代替OR

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

看下使用union的执行计划

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

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

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

再看看union

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

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

8、NULL或者NOT NULL的影响

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

再看下面这个执行计划

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

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

9、尽量全值匹配

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

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

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

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

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

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

10、最佳左前缀法则

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

先来看下表结构

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

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

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

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

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

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

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

11、范围条件放最后面

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

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

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

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

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

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

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/111230.html原文链接：https://javaforall.cn