Mysql的sql优化

一.查询优化

我们都知道，在建立索引的时候，要考虑where后面的查询条件字段、order by 排序后面的字段、group by 分组排序后面的字段，对他们的字段建立合适的索引，但是我们需要思考怎么建立合适的索引，或者建立索引之后怎么合理使用呢？

1.1 where 条件优化

where后面的查询条件字段优化参考：Mysql的索引数据结构、sql性能分析工具、索引使用和设计原则

1.2 order by 优化

Using filesort:通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。
Using index:通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高
显然第二种排序性能要比第一种性能好。
我们在创建索引时，如果没有指定索引的升序还是降序排列，创建的索引默认是升序排列的。
接下来我将举一个例子来说明，假如有一个sys_user表，先执行以下语句，查看表中建立了哪些索引：

show index from sys_user;

执行结果如下：
在这里插入图片描述
现在我们查询用户，根据username排序，sql如下：

explain select id, username from sys_user order by username;

执行结果如下：
在这里插入图片描述
这时我们为email和phone两个字段创建一个联合索引，来测试一下效果，执行以下sql：

create index idx_su_email_phone on sys_user(email, phone);
show index from sys_user;

执行结果如下，可见联合索引创建成功，并且2个字段都是升序建立的索引：
在这里插入图片描述
现在通过不同的排序sql，来看以下执行结果：

查询以下4个字段，但是password是没有建立索引的
explain select id, email, phone, password from sys_user order by email, phone

执行结果如下：
在这里插入图片描述
出现以上的原因就是因为没有覆盖索引，覆盖索引的意思就是你查询的字段已经全部建立了索引。

查询以下3个字段，都是建立的索引
explain select id, email, phone, password from sys_user order by email, phone

查询结果如下：
在这里插入图片描述

还是查询以下3个字段，都建立索引，只是排序的时候都指定asc
explain select id, email, phone from sys_user order by email asc , phone asc

执行结果如下：
在这里插入图片描述

还是查询以下3个字段，都建立索引，只是排序的时候email指定asc，phone指定desc
explain select id, email, phone from sys_user order by email asc , phone desc

执行结果如下：
在这里插入图片描述

还是查询以下3个字段，都建立索引，只是排序的时候email指定desc，phone指定asc
explain select id, email, phone from sys_user order by email desc , phone asc

执行结果如下：
在这里插入图片描述

还是查询以下3个字段，都建立索引，只是排序的时候email指定desc，phone指定desc
explain select id, email, phone from sys_user order by email desc , phone desc

执行结果如下：
在这里插入图片描述

order by 总结：
1.根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则；
2.尽量使用覆盖索引；
3.多字段排序，一个升序，一个降序，此时需要注意联合索引在创建时的规则；
4.如果不可避免的出现filesort,大数据量排序时，可以适当增大排序缓冲区的大小 sort_buffer_size(默认256k)

1.3 group by 优化

在平时开发之中我们经常使用group by进行分组统计，那么我们需要思考group by的工作原理是什么呢，以及性能瓶颈的时候，我们应该怎么进行优化呢？
在讲解之前，我们先看一下下面的例子：
假如有一个如下的用户表，我们需要统计每个省份多少人
在这里插入图片描述
执行以下sql:

select province, count(*) from user group by province

执行结果如下：
在这里插入图片描述
我们先用explain查看一下执行计划（Mysql的索引数据结构、sql性能分析工具、索引使用和设计原则）：

explain select province, count(*) from user group by province

执行结果如下：
在这里插入图片描述 Extra 这个字段的Using temporary表示在执行分组的时候使用了临时表、Using filesort表示使用了排序
group by 怎么就使用到临时表和排序了呢？我们来看下这个SQL的执行流程。
上面使用group by的语句执行流程如下：
1.创建内存临时表，表里有两个字段province和count；
2.全表扫描user的记录，依次取出city = 'X’的记录。
3.判断临时表中是否有为 city='X’的行，没有就插入一个记录 (X,1);
4.如果临时表中有city='X’的行的行，就将x 这一行的num值加 1；
5.遍历完成后，再根据字段city做排序，得到结果集返回给客户端。
如下图所示的执行流程：
在这里插入图片描述
在这里我们需要思考以下几个问题：

1. group by一定要配合聚合函数一起使用嘛？
2. group by的字段一定要出现在select中嘛?
3. group by导致的慢SQL问题如何优化和解决？

问题1：group by 就是分组统计的意思，一般情况都是配合聚合函数如（count(),sum(),avg(),max(),min())一起使用。
count() 数量
sum() 总和
avg() 平均
max() 最大值
min() 最小值
如果没有配合聚合函数使用可以吗？
我用的是Mysql 5.7 ，是可以的。不会报错，并且返回的是，分组的第一行数据。
比如下面的语句：

select id, name, age from user group by province;

执行结果如下：
在这里插入图片描述
从上面的结果可以看出返回的数据条数只有4条，但实际是远远大于4条，是因为上面返回的是分组之后，每组的第一条数据。

问题二：比如上面的那个sql，group的字段没有出现在select中，是不会有问题的，当然不同的数据库版本不一样。

问题三：group by使用不当，很容易就会产生慢SQL 问题。因为它既用到临时表，又默认用到排序。有时候还可能用到磁盘临时表。如果执行过程中，会发现内存临时表大小到达了上限（控制这个上限的参数就tmp_table_size，会把内存临时表转成磁盘临时表。如果数据量很大，很可能这个查询需要的磁盘临时表，就会占用大量的磁盘空间。
我们可以从两个方向上去优化，1.在业务不需要分组排序的时候，在分组的时候我们不进行排序；2.不使用临时表。
我们可以在groub by 后面加上 order by null 就不会进行排序，sql如下：

 explain select province, count(*) as num from user group by province order by null

如下图执行计划结果，extra 没有Using filesort, 只有Using temporary，表示走了临时表，没有走排序了。
在这里插入图片描述

我们一起来想下，执行group by语句为什么需要临时表呢？group by的语义逻辑，就是统计不同的值出现的个数。如果这个这些值一开始就是有序的，我们是不是直接往下扫描统计就好了，就不用临时表来记录并统计结果啦?
解决办法是在，group by 后面的字段加索引，如下图所示的sql,给province字段先加上索引：

create index idx_u_province on user (province)

使用如下语句查看user表建立的索引：

show index from user;

执行结果如下, 给province字段加上了索引：
在这里插入图片描述
此时，我们在使用下面sql语句执行：

explain select province, count(*) as num from user group by province

执行计划如下：
在这里插入图片描述
此时的extra只有using index, 走了索引，没有走临时表和缓冲排序了。

group by 优化总结：
1.如果group by 后面的字段没有加索引，一般会走临时表和默认使用排序；
2.如果没有排序的要求，我们可以使用order by null 不进行排序，此时只走临时表；
3.我们在建立索引的时候，尽量把group by 后面的字段考虑进来，并且是考虑覆盖索引，就是建立组合索引的时候select后面的字段跟group by 后面的字段保持一致。

1.4 count优化

对于count计数来说，执行以下语句,针对不同的存储引擎，count执行效率不一样

explain select count(*) from tb_user;

MyISAM:把一个表的总行数存在了磁盘上，因此执行count() 的时候回直接返回这个数，效率很高；
InnoDB：在执行count()的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。
优化思路：自己计数，用redis,每次执行插入操作的时候，就进行+1
count的几种用法及哪个性能高
count(主键值)：InnoDB引擎会遍历整张表，把每一行的主键id值取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（主键不可能为null）。
count(字段)：没有not null 约束：InnoDB引擎会遍历整张表把每一行的字段值取出来，返回给服务层，服务层判断是否为null,不为null,计数累加；有not null约束，InnoDB引擎会遍历整张表把每一行的字段值取出来，返回给服务层，直接按行进行累加。
count(1):InnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字’1’进去，直接按行进行累加。
count():InnoDB并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。
count()>count(1)>count(主键)>count(字段)

二. 插入数据

批量插入
对于有多条数据的插入的情况，采用批量插入的方式，因为进行一次插入操作就会连接一次数据库，如果不一次性插入就会有大量的连接数据库的IO操作，耗时，会很慢，一般在mapper.xml中批量插入的语法如下：

    <insert id="方法名" >
        INSERT INTO 表明
            (字段1，字段2，字段3，....)
        VALUES
        <foreach collection="req" item="item" separator=",">
            (#{item.字段值1}, #{item字段值2}, #{item.字段值3}, ...)
        </foreach>
    </insert>

手动提交事务

start transaction;
insert into ...........;
insert into ...........;
insert into ............;
commit;

因为mysql插入一条数据就会提交一次事务，如果有多条，就要执行多条提交事务的操作，所以进行手动提交事务，数据插入完成之后在提交事务。

三. 主键优化

主键设计原则：
1.满足业务需求的情况下，尽量降低逐渐的长度。
2.插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键；
3.尽量不要使用UUID做主键或者其他自然主键，如身份证号，应为太长，会占用大量