前言：
mysql优化是java开发人员必备的技能之一，虽然可能比不上专业的DBA，但是一些常用的以及基本的mysq优化的知识还是需要知道，今天从总结一些常用的mysql优化的知识，并且是从实战的过程中来使用这些优化的技巧，简单、好用、且干货满满，在阅读本篇文章之前，最好是了解mysql执行计划的一些知识，大家可自行查找资料。直接上干货
整个优化分为如下：
- 需要在表中建立索引
- 不要在索引上做任何的操作
- 不等号（!= 、<>）要谨慎使用
- 尽量使用覆盖索引
- like查询要注意
- 字符串需要加引号
- UNION代替OR
- NULL或者NOT NULL的影响
- 尽量全值匹配
- 最佳左前缀法则
- 范围条件放最后面
下面我们一条条的来详细解读
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（不管字段需要多长）这是非常低级的错误
发布者：全栈程序员-站长，转载请注明出处：https://javaforall.net/111230.html原文链接：https://javaforall.net
