Mysql sql优化

插入优化

1️⃣ 用批量插入代替单条插入

insert into 表明 values(1, 'xxx')
insert into 表明 values(2, 'xxx')
...
改为使用👇
insert into 表名 values(1, 'xxx'), (2, 'xxx')...

2️⃣ 手动提交事务

start tranaction;
insert into 表名 values(1, 'xxx'), (2, 'xxx')...
insert into 表名 values(1, 'xxx'), (2, 'xxx')...
commit;

3️⃣ 主键顺序插入

insert into 表名 values(1, 'xxx'), (4, 'xxx'), (2, 'xxx'), (3, 'xxx')...
改为使用👇
insert into 表名 values(1, 'xxx'), (2, 'xxx'), (3, 'xxx'), (4, 'xxx')...

4️⃣ 大批量数据插入时，使用数据库提供的load指令进行插入

# 客户端连接服务端时，加上参数 --local-infile
mysql --local-infile -u root -p
# 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile=1
# 执行load指令将准备好的数据加载到表结构中
load data local infile '/root/sql.log' into table `t_user` fields terminated by ',' lines terminated by '\n'

主键优化

✅ 优化方式
1️⃣ 满足业务需求的情况下，尽量降低主键长度
2️⃣ 插入数据时，尽量选择顺序插入，选择使用auto_increment自增主键。乱序插入会出现页分裂与页合并，影响效率。
3️⃣ 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。首先是长，其次是乱序。
4️⃣ 业务操作时，避免对主键的修改。主键修改，索引结构也跟着改变。

order by排序优化

排序优化的思路就是让它走索引，如果没有走索引，mysql会将查询到的数据在排序缓冲区中进行排序操作，再将有序结果返回，而走索引之后，根据索引顺序扫描获取到的数据就是有序数据，这样就少了一个排序操作。
🛑 需要注意的是索引的建立默认是升序的，这说明如果根据两个字段排序，只能是两个都升序或者都是降序(mysql会倒着查询)，如果要一个升序，一个倒序就需要建立索引时指定顺序。
🛑 如果两个排序我都需要呢？那就创建两个索引，一个默认，一个指定。

create index idx_uesr_age_phone on t_user(age, phone)
默认创建方式改为指定索引排序方式👇
create index idx_uesr_age_phone on t_user(age asc, phone desc)

✅ 优化方式
1️⃣ 根据排序字段建立索引，多字段排序要遵循最左前缀法则
2️⃣ 尽量使用覆盖索引，防止回表查询
3️⃣ 多字段排序，一个升序一个降序，创建索引时要指定顺序
4️⃣ 大数据排序时，如果不可避免的使用排序缓冲区时，可以适当的增加它的大小sort_buffer_size(默认256k)，如果缓冲区满了，会在磁盘文件中进行排序，速度更慢

group by优化

✅ 优化方式
1️⃣ 对分组的字段创建索引，提高效率
2️⃣ 索引的使用要满足最左前缀法则

limit优化

当我们分页查询数据时，查询的起始记录越大，速度就越慢

select * from t_user limit 1000000, 10
👆查询时间 > 👇查询时间
select * from t_user limit 10, 10

因为我们查询从1000000开始的10条数据时，前999999条数据也会扫描一遍，所以越往后查询时间越长，官方给出的方式是使用覆盖索引 + 子查询来优化

select * from t_business order by id limit 10, 10
改为使用👇
select b1.* from t_business b1, (select id from t_business order by id limit 10, 10) b2 where b1.id = b2.id

✅ 优化方式
1️⃣ 使用覆盖索引 + 子查询

count优化

🛑count计数时，如果值为NULL，不会计数
🛑 MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高，但是mysql现在默认使用的InnoDB引擎，它执行count(*)的时候会把数据全部读出来，累计计数。然而没有好的优化方法，但是它的使用方式不同，效率是不同的
1️⃣ count(主键)
InnoDB引擎会遍历整张表，把主键取出来，返回给服务层，服务层拿到之后，直接进行累加(主键不为null)
2️⃣ count(字段)
没有not null约束时，InnoDB会便利整张表，将字段取出，返回服务层，服务层拿到之后，判断是否为空，不为空进行累加，没有not null约束时，直接进行累加，少一步判断是否为空
3️⃣ count(1)
InnoDB引擎会便利整张表，但不取值。服务层对于返回的每一行，放一个数字"1"进去，直接按行进行累加
4️⃣ count(*)
InnoDB专门做了优化，并不会把全部字段取出来，不取值，服务层直接按行进行累加
✅ 总结
效率: count(字段) < count(主键) < count(i) ≈ count(*)， 所以尽量使用count(*)

update优化

在update使用时，要避免行级锁升级为表锁，update更新时更新的字段没有建立索引就会上升为表锁

begin # 开启事务
update t_user set uname = 'zdy' where uname = 'zzz' # 更新

正常来说，在客户端一没有执行commit操作之前，锁住的应该是uname='zzz’的这条数据，客户端二可以更新其他记录，但是由于uname没有建立索引，客户端二不管update哪条数据都会处于阻塞状态，客户端一commit之后，客户端二才能update，这样就会降低效率，解决方式就是建立索引。
✅ 优化方式
1️⃣ 对Update更新的字段建立索引

索引的使用

优化了一堆，发现很多优化都跟索引有关，那就了解一下如何高效使用索引吧

1️⃣ 不满足最左前缀法则，索引会失效
2️⃣ 不要对索引列上使用运算操作，否则索引会失效
3️⃣ 字符串要加引号，否则会进行隐式转换，索引将失效 ✅uname = ‘10’ ❎uname = 10
4️⃣ 模糊匹配不能使用尾部模糊匹配，否则索引会失效 ✅uname like = ‘zdy%’ ❎uname like = ‘%zdy’
5️⃣ 使用or连接的条件，如果其中一个没有建立索引，索引将失效，必须都建立索引
6️⃣ 如果mysql评估索引方式比全表查询要慢，就不会使用索引
7️⃣ 使用覆盖索引，即查询返回的列都建立了索引，减少select(*)的使用(返回的列没有建立索引时，需要进行回表查询，如果建立索引，那么需要的数据在索引中就能找到，不用进行回表查询)
8️⃣ 如果字符串作为索引时过长，会浪费磁盘空间，影响查询效率，此时可以只将字符串的一部分前缀做为索引，但是这一部分要保证足够的区分度
9️⃣ 推荐使用联合索引，而不是单列索引，即使条件中的多个字段都建立了单列索引，mysql也只会选择其中一个效率较高的作为索引，最后还需要回表查询

# 补充第八条 部分前缀最作为索引语法
create index 索引名称 on 表名(column(n))